Лайфхак для Excel. Как настроить поиск по нескольким критериям и сэкономить уйму времени

Excel — это действительно сильный инструмент для анализа, расчета и поиска определенных значений в базе данных. Однако иногда поиск нужной информации может быть затруднен — так часто происходит, если синтаксис позволяет искать лишь одно значение. 

Как же быть, если нужно выполнить поиск по нескольким условиям? Ответ на этот вопрос вы узнаете из статьи. 

Кроме того, эксперты учебного центра РУНО собрали самые важные аспекты работы в Excel в одном образовательном комплексном курсе, который можно пройти в дистанционном формате: Microsoft Excel 2016/2019/2021. Уровень 3. Эксперт. Анализ и прогнозирование данных. 

Содержание:

1. Зачем нужен поиск по нескольким критериям в Excel
2. Как настроить поиск по нескольким критериям
3. Как в таблицах Excel применить двусторонний поиск

Excel предлагает множество функций для работы с данными, и одной из самых мощных и универсальных является функция поиска по нескольким критериям. Такая функция позволяет находить нужные данные в больших массивах информации, учитывая несколько условий одновременно.

Например, представим себе ситуацию, когда у вас есть база данных с информацией о клиентах: имена, фамилии, возраст, города, и т.д. Вам может понадобиться найти всех клиентов определенного города, определенного возраста, и т.д. Функция поиска по нескольким критериям поможет вам справиться с такой задачей.

Хотите расти в IT и дизайне? Подписывайтесь на наш Telegram-канал — разборы, советы и вдохновение каждый день

Подписаться!

Важно знать! 

Использование функции поиска по нескольким критериям является важным навыком для тех, кто работает с данными в Excel. Выборочная фильтрация больших объемов информации по различным параметрам обеспечивает более точный анализ.

Таким образом, функция поиска по нескольким критериям в программе Excel позволяет существенно упростить и ускорить процесс обработки данных, делая его более гибким и эффективным.

Курс Microsoft Excel 2016/2019. Уровень 1+2. Профессионал

Курс Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности

При обработке больших массивов данных можно столкнуться с ситуацией, когда требуется найти что-то важное, но при этом отсутствует однозначный уникальный критерий для поиска. Таких критериев в общем объеме данных может быть множество. В таких случаях поиск с использованием нескольких условий становится единственным и самым эффективным выходом, который поможет сэкономить массу времени.

Поиск значений в таблице на основе значений из двух и более столбцов возможен с использованием текстового оператора сцепки & и формулы массива. 

ПРИМЕР: Определить номер строки в таблице для указанного Кода клиента и Кода сотрудника. 

{=ПОИСКПОЗ(H2&H3;A2:A25&E2:E25;0)} — определение позиции в таблице (номер строки), в которой одновременно находится Код клиента HANAR (значение ячейки Н2) и Код сотрудника MED (значение ячейки Н3). 

Искомое_значение объединяется текстовым оператором & — H2&H3 и Просматриваемый_массив в такой же последовательности A2:A25&E2:E25, формула завершается как формула массива нажатием клавиш Ctrl + Shift + Enter. 

Формула объединяет содержимое Кода клиента и Кода сотрудника, затем находит этот текст в массиве, состоящем из соответствующего объединенного текста в диапазонах Код клиента и Код сотрудника. Такое объединение данных не исключает и другие комбинации, которые будут содержать такой же ответ. 

Это приведет к неправильному определению позиции и, как следствие, неправильному ответу на поставленную задачу. Необходимо объединять данные 2-х и более критериев с каким-нибудь символом, которого нет в самих данных, например: #, ; и т.д. 

{=ПОИСКПОЗ(H2&";"&H3;A2:A25&";"&E2:E25;0)} — определение позиции в таблице (номер строки), в которой одновременно находится Код клиента HANAR (значение ячейки Н2) и Код сотрудника MED (значение ячейки Н3). 

ПРИМЕР: Определить Стоимость заказа для указанного Кода клиента и Кода сотрудника. 

{=ИНДЕКС(C2:C25;ПОИСКПОЗ(H2&";"&H3;A2:A25&";"&E2:E25;0))} — определение значения Стоимости заказа (ячейки C2:C25) из строки, в которой одновременно находится Код клиента HANAR (значение ячейки Н2) и Код сотрудника MED (значение ячейки Н3). 

Если в своей профессиональной работе вы регулярно сталкиваетесь с вопросами связанными с оперированием большими массивами и хотели бы научиться использовать программные возможности для оптимизации своей деятельности в полном объеме, то дистанционные курсы excel анализ и прогнозирование данных — идеальное решение.

Если представить ситуацию, что номер строки определяется по двум или более критериям и в любой момент времени потребуется получить данные то одного столбца, то другого, то номер строки так же нужно будет определять по функциям. 

В таких случаях можно использовать двусторонний многокритериальный поиск. 

ПРИМЕР: Определить для указанного Кода клиента и Кода сотрудника данные по столбцу, название которого указывается (например, в ячейке предлагается выбор нужного заголовка столбца).

{=ИНДЕКС(C2:D25;ПОИСКПОЗ(H2&";"&H3;A2:A25&";"&E2:E25;0);ПОИСКПОЗ( G5;C1:D1;0))} — определение значения из диапазона ячеек C2:D25, получаемого на пересечении номера строки, в которой одновременно находится Код клиента HANAR (значение ячейки Н2) и Код сотрудника MED (значение ячейки Н3) и номером столбца, определяемым в зависимости от выбранного заголовка Код заказа или Стоимость заказа (значение ячейки G5).

Профессиональный совет: Курсы Excel онлайн — возможность получить самые важные навыки по работе с  Excel  на продвинутом уровне.  Эксперты центра “РУНО” на примерах научат вас самым эффективным стратегиям анализа и прогнозирования данных в Excel, которые помогут ускорить вашу работу и сделать её более эффективной.  Приглашаем на курс Microsoft Excel 2016/2019/2021. Уровень 3. Эксперт. Анализ и прогнозирование данных.  ПО ОКОНЧАНИИ ОБУЧЕНИЯ ВЫ БУДЕТЕ УМЕТЬ:  делать сложные расчёты и двухсторонний поиск (когда не справляется ВПР) — формулы массивов  работать с интерактивными диаграммами для создания функциональных отчётов  применять инструменты для проведения регрессионного анализа и прогнозирования  использовать поиск решения и диспетчер сценариев для анализа и выбора оптимальной бизнес-стратегии Получите бесплатный ПРОБНЫЙ ДОСТУП на нашем сайте! Получить доступ       Учебная программа