Excel поиск по двум критериям

Поиск по нескольким критериям в таблицах Excel

Добрый день уважаемый читатель!

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

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

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

Для этой операции подойдёт формула:

В обязательном порядке формулу необходимо вносить не просто так, а как формулу массива, о чём свидетельствуют фигурные скобки. Произвести это возможно с помощью комбинации горячих клавиш Shift+Ctrl+Enter. Как же собственно работает формула? Рассмотрим поподробнее… Используемый «амперсанд» в функции ПОИСКПОЗ соединяет нужные значения «Нагаев А.В.» и «Апельсин» в одно значение «Нагаев А.В.Апельсин» и производит просмотр массива данных таблицы «B2:B6» и «C2:C6», которые предварительно были условно сцеплены формулой, типа «Нагаев А.В.Банан», «Сидоров А.С.Лимон» и т.д.

Следующим этапом после нахождения функцией ПОИСКПОЗ номера строки с необходимыми условиями, передается функции ИНДЕКС, которая с диапазона «D2:D6» вытянет нужную сумму по указанному адресу номера строки.

Ну, вот результат и готов!

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

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

Не забудьте подкинуть автору на кофе…

Microsoft Excel

трюки • приёмы • решения

Как в Excel искать значение по двум столбцам

В некоторых ситуациях может потребоваться поиск на основе значений в двух столбцах (рис. 122.1). Таблица содержит марки и модели автомобилей и соответствующие коды для каждого из них. Метод, описанный здесь, позволяет искать значение, основываясь на марке и модели автомобиля.

Рис. 122.1. В таблице выполняется поиск с использованием информации из двух столбцов (D и Е)

Лист использует именованные диапазоны, как показано в табл. 122.1.

Следующая формула массива отображает соответствующий код для марки и модели автомобиля:
=ИНДЕКС(Код;ПОИСКПОЗ(Марка&Модель;Диапазон1&Диапазон2;0))

При вводе формулы массива нажмите Ctrl+Shift+Enter (а не просто Enter).

Формула объединяет содержимое диапазонов Марка и Модель, а затем находит этот текст в массиве, состоящем из соответствующего объединенного текста, в диапазонах Диапазон1 и Диапазон2.

Альтернативный подход заключается в создании новой таблицы поиска из двух столбцов, как показано на рис. 122.2. Таблица содержит ту же информацию, что и исходная таблица, но столбец Н включает объединенные данные из столбцов D и Е.

Рис. 122.2. Избегайте поиска по двум столбцам, комбинируя два столбца в один

После создания новой таблицы вы можете использовать простую формулу для выполнения поиска:
=ВПР(Марка&Модель;H2:I12;2)

Читать еще:  Количество символов в ячейке в эксель

Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

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

Функции ВПР и ГПР вместе с функцией индекс и ПОИСКПОЗявляются наиболее полезными функциями в Excel.

Примечание: Функция «Мастер подСтановок» больше не доступна в Excel.

Вот пример того, как использовать функцию ВПР.

В этом примере ячейка B2 является первым аргументом— элементом данных, для работы которого необходима функция. Для функции ВПР первым аргументом является значение, которое нужно найти. Этим аргументом может быть ссылка на ячейку или фиксированное значение, например «Иванов» или «21 000». Второй аргумент — это диапазон ячеек с ячейкой C2: E7, в которой нужно найти искомое значение. Третьим аргументом является столбец в этом диапазоне ячеек, который содержит искомое значение.

Четвертый аргумент является необязательным. Введите значение истина или ложь. Если ввести значение TRUE или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в первом аргументе. Если введено значение ложь, функция будет соответствовать значению, предоставленному первым аргументом. Другими словами, если оставить четвертый аргумент пустым — или ввести значение TRUE, вы получите большую гибкость.

В этом примере показано, как работает функция. При вводе значения в ячейке B2 (первый аргумент) функция ВПР ищет ячейки в диапазоне C2: E7 (второй аргумент) и возвращает ближайшее приблизительное совпадение из третьего столбца в диапазоне, столбец E (Третий аргумент).

Четвертый аргумент пустой, поэтому функция возвращает приблизительное совпадение. Если это не так, необходимо было бы ввести одно из значений в столбцах C или D, чтобы получить результат.

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

Использование функции индекс и СОВПАДЕНИе вместо функции ВПР

Существуют определенные ограничения с использованием функции ВПР — функция ВПР может искать значение слева направо. Это означает, что столбец, содержащий искомое значение, всегда должен располагаться слева от столбца, содержащего возвращаемое значение. Теперь, если электронная таблица не построена таким образом, не используйте функцию ВПР. Вместо этого используйте сочетание функций индекс и ПОИСКПОЗ.

В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения «Воронеж» в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.

Дополнительные примеры использования функции индекс и MATCH вместо ВПР см. в статье https://www.MrExcel.com/Excel-Tips/Excel-VLOOKUP-index-Match/ Билл Джилена, Microsoft MVP.

Попробуйте попрактиковаться

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

Пример функции ВПР на работе

Скопируйте следующие данные в пустую электронную таблицу.

Совет: Прежде чем вставлять данные в Excel, задайте ширину столбцов для столбцов от A до 250 пикселей и нажмите кнопку Перенос текста (вкладка «Главная «, Группа » Выравнивание «).

Читать еще:  Обучение макросам в эксель

Функция ВПР с несколькими условиями критериев поиска в Excel

Функция ВПР (Вертикальный ПРосмотр) ищет по таблице с данными и на основе критериев запроса поиска, возвращает соответствующее значение с определенного столбца. Очень часто необходимо в запросе поиска использовать сразу несколько условий. Но по умолчанию данная функция не может обработать более одного условия. Поэтому следует использовать весьма простую формулу, которая позволит расширить возможности функции ВПР по нескольким столбцам одновременно.

Работа функции ВПР по нескольким критериям

Для наглядности разберем формулу ВПР с примером нескольких условий. Для примера будем использовать схематический отчет по выручке торговых представителей за квартал:

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

  1. – Дата сдачи выручки в кассу.
  2. – Фамилия торгового представителя.

Для решения данной задачи будем использовать функцию ВПР по нескольким условиям и составим следующую формулу:

  1. В ячейке С1 введите первое значение для первого критерия поискового запроса. Например, дата: 22.03.2017.
  2. В ячейку C2 введите фамилию торгового представителя (например, Новиков). Это значение будет использоваться в качестве второго аргумента поискового запроса.
  3. В ячейке C3 мы будем получать результат поиска, для этого там следует ввести формулу:
  4. После ввода формулы для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве.

Результат поиска в таблице по двум условиям:

Найдена сумма выручки конкретного торгового представителя на конкретную дату.

Разбор принципа действия формулы для функции ВПР с несколькими условиями:

Первым аргументом функции =ВПР() является первым условием для поиска значения по таблице отчета выручки торговых представителей. Во втором аргументе находится виртуальная таблица создана в результате массивного вычисления логической функцией =ЕСЛИ(). Каждая фамилия в диапазоне ячеек B6:B12 сравнивается со значением в ячейке C2. Таким образом в памяти создается условный массив данных с элементами значений ИСТИНА и ЛОЖЬ.

Потом благодаря формуле, в памяти программы каждый истинный элемент заменяется на 3-х элементный набор данных:

  1. элемент – Дата.
  2. элемент – Фамилия.
  3. элемент – Выручка.

А каждый ложный элемент в памяти заменяется на 3-х элементный набор пустых текстовых значений («»). В результате создается в памяти программы новая таблица, с которой уже будет работать функция ВПР. Она игнорирует все пустые наборы данных элементов. А непустые элементы сопоставляются со значением ячейки C1, использованного в качестве первого критерия поискового запроса (Дата). Одним словом, таблица в памяти проверена функцией ВПР с одним условием поиска. При положительном результате сопоставления функция возвращает значение элемента из третьего столбца (выручка) условной таблицы. Это происходит потому, что в третьем аргументе указывается номер столбца 3 из которого берутся значения. Стоит отметить что для просмотра в аргументах функции указывается целая таблица (во втором аргументе), но сам поиск всегда идет по первому столбцу в указанной таблицы.

А из какого столбца брать возвращаемое значение указывается уже в третьем аргументе.

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

Поиск и подстановка по нескольким условиям

Постановка задачи

Если вы продвинутый пользователь Microsoft Excel, то должны быть знакомы с функцией поиска и подстановки ВПР или VLOOKUP (если еще нет, то сначала почитайте эту статью, чтобы им стать). Для тех, кто понимает, рекламировать ее не нужно 🙂 — без нее не обходится ни один сложный расчет в Excel. Есть, однако, одна проблема: эта функция умеет искать данные только по совпадению одного параметра. А если у нас их несколько?

Читать еще:  Восстановить несохраненный файл excel

Предположим, что у нас есть база данных по ценам товаров за разные месяцы:

Нужно найти и вытащить цену заданного товара (Нектарин) в определенном месяце (Январь), т.е. получить на выходе152, но автоматически, т.е. с помощью формулы. ВПР в чистом виде тут не поможет, но есть несколько других способов решить эту задачу.

Способ 1. Дополнительный столбец с ключом поиска

Это самый очевидный и простой (хотя и не самый удобный) способ. Поскольку штатная функция ВПР (VLOOKUP) умеет искать только по одному столбцу, а не по нескольким, то нам нужно из нескольких сделать один!

Добавим рядом с нашей таблицей еще один столбец, где склеим название товара и месяц в единое целое с помощью оператора сцепки (&), чтобы получить уникальный столбец-ключ для поиска:

Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:

Плюсы : Простой способ, знакомая функция, работает с любыми данными.

Минусы : Надо делать дополнительный столбец и потом, возможно, еще и прятать его от пользователя. При изменении числа строк в таблице — допротягивать формулу сцепки на новые строки (хотя это можно упростить применением умной таблицы).

Способ 2. Функция СУММЕСЛИМН

Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS) , появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

Плюсы : Не нужен дополнительный столбец, решение легко масштабируется на большее количество условий (до 127), быстро считает.

Минусы : Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

Способ 3. Формула массива

О том, как спользовать связку функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) в качестве более мощной альтернативы ВПР я уже подробно описывал (с видео). В нашем же случае, можно применить их для поиска по нескольким столбцам в виде формулы массива. Для этого:

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

  • Нажмите в конце не Enter, а сочетание Ctrl+Shift+Enter, чтобы ввести формулу не как обычную, а как формулу массива.
  • Как это на самом деле работает:

    Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.

    Плюсы : Не нужен отдельный столбец, работает и с числами и с текстом.

    Минусы : Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны «с запасом» или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).

    Похожие статьи

    Ссылка на основную публикацию
    Похожие публикации
    ×
    ×
    Adblock
    detector