Добрый день, уважаемый читатель TopExcel.ru!
Продолжая раскрывать тайны использования функций в Excel, я предлагаю вашему вниманию возможность, произвести выбор значений в Excel, когда возвращается не только первое найденное значение по условию, а абсолютно все найденные значения. Я уже раннее описывал разные возможности выбора значений с помощью функции ВПР, это и интервальный просмотр значений, и увеличение функционала с помощью макроса, и использование в функции несколько условий. Даже описывал работу горизонтальную версию, функции ГПР. Так что можете перейти по соответствующим ссылкам и ознакомится со всеми материалами.
А вот теперь ближе к теме. Собственно для чего это нужно, выбрать все значения, а это необходимо когда вам нужно сгруппировать значения по определённым признакам или же выбрать по определенным признакам и многое другое. А пригодится, это может многим специалистам бухгалтерского учёта, экономистам, кадровикам, профсоюзу, торговым отделам и многим другим. Аналогичную операцию возможно также сделать с помощью фильтров или же срезов сводной таблицы, почитаете об этом отдельно, а сейчас рассмотрим выбор значений Excel при помощи комбинации функций.
Итак, для примера создадим таблицу с перечнем сотрудником, она будет наиболее информативна, вот такого вида: А теперь попробуем получить перечень всех сотрудников на производстве готовой продукции. И выполнить поставленную задачу при помощи чистого функционала функции ВПР невозможно в принципе, поэтому будем использовать комбинации функций. Таких комбинаций возможно три:
- Комбинация функций №1: это создание промежуточных столбиков, в которых производить слияние значений для дальнейшего отбора, с помощью функции СЦЕПИТЬ;
- Комбинация функций №2: при помощи функционала макросов VBA или же прописав собственный код функции в редакторе;
- Комбинация функций №3: наличие каждого значения в индивидуальной ячейке, полное разделение данных. Использование только возможностей функций Excel.
Для чистоты эксперимента подойдет только последний вариант, но как я писал ранее, чистыми возможностями функции ВПР сделать выбор значений в Excel невозможно, поэтому будем использовать соединение и комбинации разных функций, таких как ИНДЕКС и ПОИСКПОЗ.
В итоге наша формула будет выглядеть так:
{=ЕСЛИОШИБКА (ИНДЕКС ($A$3:$G$14; НАИМЕНЬШИЙ (ЕСЛИ ($I$3=$C$3:$C$14; СТРОКА ($C$3:$C$14) -2); СТРОКА(B1)); 2);»»)}, где:
- Аргумент ($A$3:$G$14) – указывается таблица с данными для поиска;
- Аргумент ($I$3) – ячейка, в которой указывается нужный выбор критерия отбора;
- Аргумент ($C$3:$C$14) – указывается диапазон с перечнем отделов для отбора.
Как видите, в формуле часто использован знак «$», которым создаются абсолютный ссылки, для качественной работы формулы, это обязательное условие.
Для отображений значений без ошибки #ЧИСЛО! я использовал функцию ЕСЛИОШИБКА. Вы вольны поступать как вам угодно, а вот мне нравятся чистые ячейки.
Поскольку формула большая и для большинства пользователей Excel непонятна, сделаю ее описание более детальным и разложу вычисления отдельным фрагментам:
- Для начала, как видно из показанных фигурных скобок, формулу нужно вводить как формулу массива. Устанавливаете курсор в ячейку «K3» и вставляете формулу с помощью Ctrl+Shift+Enter. После этого копируете формулу простым протягиванием до конца нужного диапазона;
- Поиском ФИО занимается код: ЕСЛИ ($I$3=$C$3:$C$14; СТРОКА ($C$3:$C$14) -2). Исходя из кода, видно, что происходит сравнение указанного значения в ячейке «$I$3» с перечнем отделов «$C$3:$C$14» в таблице. Когда происходит совпадение, то формула возвращает на каком номере найдено значение СТРОКА ($C$3:$C$14). Но сам номер строки рабочего листа вам не нужен, нужно получить номер записи значения в таблице, поэтому отсчитываем все лишние строки с помощью аргумента «-2», то есть перескакиваете 2 пустые строки и переходите сразу к таблице.
- После того, как функция ЕСЛИ, после проверки, вернула массу значений типа ЛОЖЬ и числовые значения при совпадениях условия, тогда используя функцию НАИМЕНЬШИЙ, вы отбираете только числовые значения, игнорируя всё остальное;
- Заключающим этапом будет передача полученного значения, которое получено в предыдущем этапе в функцию ИНДЕКС.
- Отдельно, как видите, я для красоты и ради искусства добавил обработку программных ошибок с помощью функции ЕСЛИОШИБКА, о чём уже писал.
Я надеюсь, что выбор значений в Excel с помощью функций у вас получился, и вы могли быстро собрать нужные данные в ваших таблицах, а также научились создавать удобные и классные отчёты. Если у вас есть чем дополнить меня пишите комментарии, я буду их ждать с нетерпением, ставьте лайки и делитесь полезной статьей в соц.сетях!
Не забудьте подкинуть автору на кофе…
