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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как это на самом деле работает:

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

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

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

Ссылки по теме

www.planetaexcel.ru

Как в Excel искать значение по двум столбцам — Трюки и приемы в Microsoft Excel

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

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

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

Диапазон Название

F2:F12	Код
B1	Марка
B2	Модель
D2:D12	Диапазон1
E2:E12	Диапазон2

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

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

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

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

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

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

excelexpert.ru

Поиск позиции элемента в списке с ПОИСКПОЗ (MATCH)

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

Синтаксис этой функции следующий:

=ПОИСКПОЗ(Что_ищем; Где_ищем; Режим_поиска)

где

Что_ищем - это значение, которое надо найти

Где_ищем - это одномерный диапазон или массив (строка или столбец), где производится поиск

Режим_поиска - как мы ищем: точно (0), с округлением в большую строну (-1) или в меньшую сторону (1)

Давайте рассмотрим несколько полезных вариантов ее применения на практике.

Точный поиск

Классический сценарий - поиск точного текстового совпадения для нахождения позиции нужного нам текста или числа в списке:

Поиск первой или последней текстовой ячейки

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

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

Поиск ближайшего числа или даты

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

Например, нам нужно выбрать генератор из прайс-листа для расчетной мощности в 47 кВт. Если последний аргумент задать равным 1 и отсортировать таблицу по возрастанию, то мы найдем ближайшую наименьшую по мощности модель (Зверь):

Если же третий аргумент равен -1 и таблица отсортирована по убыванию, то мы найдем ближайшую более мощную модель (Бомба):

Связка функций ПОИСКПОЗ и ИНДЕКС

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

Так, в предыдущем примере получить не номер, а название модели генератора можно очень легко:

Ну, и поскольку Excel внутри хранит и обрабатывает даты как числа, то подобный подход на 100% работает и с датами. Например, мы можем легко определить на каком этапе сейчас находится наш проект:

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

Ссылки по теме

www.planetaexcel.ru

Диапазоны

Сравнение двух таблиц

Как сравнить между собой две таблицы (прайс-листа) и наглядно отобразить отличия между ними - добавленные и удаленные товары, изменение цены и т.д. Разбор 3 способов: с помощью функции ВПР (VLOOKUP), сводной таблицей и через надстройку Power Query.

Трансформация столбца в таблицу

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

Самая длинная победная серия

Как подсчитать максимальное количество идущих подряд значений, удовлетворяющих заданному условию? Например, самое большое количество рабочих дней подряд в табеле? Или самую длинную победную серию в спорте?

Слияние двух списков без дубликатов

Как соединить два списка, чтобы в итоге получить один обобщенный список, причем без повторений. Подробный разбор нескольких способов (вручную, формулами, через Power Query).

Левый ВПР

Как реализовать в Excel аналог функции ВПР (VLOOKUP), который будет выдавать значения левее поискового столбца.

5 вариантов использования функции ИНДЕКС (INDEX)

Подробный разбор всех пяти вариантов применения мегамощной функции ИНДЕКС (INDEX): от простого поиска данных в столбце, до двумерного поиска в нескольких таблицах и создания авторастягивающихся диапазонов.

Создание базы данных в Excel

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

Обратный порядок элементов в списке

Как расположить элементы списка в обратном порядке - от последнего до первого. Разбор нескольких способов - от простой сортировки до формулы массива и макросов.

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

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

Суммирование только видимых ячеек

Как при помощи функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ просуммировать (а также вычислить среднее, минимум, максимум, количество и т.д.) только видимых ячеек оставшихся после применения фильтра, скрытия строк/столбцов, сворачивания группировкой, а также проигнорировать ячейки с ошибками при расчетах.

Подстановка "из ниоткуда"

Как использовать массивы констант и имена, чтобы встроить поисковые таблицы прямо внутрь функций ВПР, ИНДЕКС, ПОИСКПОЗ и им подобных, чтобы подставлять данные на лету и без использования отдельных таблиц.

Динамический диапазон с автоподстройкой размеров

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

Суммирование каждой 2-й, 3-й... N-й ячейки

Несколько способов произвести вычисления (просуммировать, выбрать максимум/минимум, посчитать среднее арифметическое) с каждой 2-ой, 3-ей... n-ой ячейкой в заданном диапазоне, т.е. например сложить каждую четную ячейку или ячейки через одну.

Расширенный фильтр и немного магии

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

Подробнее... Хитрости сортировки

Как корректно отсортировать столбец, в котором содержатся одновременно и текстовые и числовые значения? Как правильно сортировать месяцы, дни недели и должности, чтобы они выстроились не по алфавиту, а в нужном порядке по смыслу?

Суперфильтр на VBA

Быстрая и удобная альтернатива стандартному Автофильтру с помощью небольшого макроса. Умеет искать по маске и одному/двум сложным условиям (с И и ИЛИ). Без мыши. В реальном времени.

Умные таблицы Excel 2007-2013

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

ВПР (VLOOKUP) с интервальным просмотром

Как проверить попадание параметра в один из заданных диапазонов и, при этом, обойтись без громоздких вложенных проверок с помощью кучи функций ЕСЛИ (IF)?

Улучшаем функцию ВПР (VLOOKUP)

Встроенная функция ВПР (VLOOKUP) имеет один существенный недостаток - ищет только первое вхождение искомого значения в таблицу и только в крайнем левом столбце. А если нужно найти 2-е, 3-е и не в крайнем левом?

Подробнее... Случайная выборка

Как случайным образом выбрать из большого массива данных заданное количество элементов?

Поиск нужных данных в диапазоне

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

Подробнее... Формулы массива в Excel

Что такое формула массива? Как ее вводить, редактировать и удалять? Как формулы массива могут упростить и облегчить жизнь пользователя в больших таблицах?

Типы ссылок на ячейки в формулах Excel

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

Многоразовый ВПР (VLOOKUP)

Как извлечь ВСЕ данные из таблицы по заданному критерию. Функция ВПР (VLOOKUP) находит значение только по первому совпадению, а нужно вытащить все. Поможет хитрая формула массива!

Подробнее... Сортировка формулой

Несколько способов отсортировать числовой или текстовый список не через меню Данные - Сортировка, а с помощью формул "на лету".

Подробнее... ВПР (VLOOKUP) с учетом регистра

Как при помощи формулы массива сделать аналог функции ВПР (VLOOKUP), который при поиске будет учитывать регистр и различать строчные и прописные символы.

Двумерный поиск в таблице (ВПР 2D)

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

Редизайнер таблиц

Специальный макрос, позволяющий переконструировать Вашу "красивую" таблицу в тот вид, который понимает и с которым может работать Excel.

Динамическая гиперссылка

Как с помощью функций ГИПЕРССЫЛКА и ПОИСКПОЗ сделать "живую" ссылку на определенную строку в другой таблице, содержащую нужные вам данные, чтобы потом быстро переходить из одной таблицы в другую.

www.planetaexcel.ru

О классификации задач Поиска в EXCEL. Примеры и методы

В этой статье изложены попытки классификации задач по поиску значений в MS EXCEL. Статья может быть интересна для продвинутых пользователей EXCEL.

В этой обзорной статье рассмотрены 4 типа задач поиска значений в списках, даны ссылки на статьи-описания решения типовых примеров.

Совет. Так же на эту тему можно посмотреть статью Функция ВПР() в MS EXCEL.

1. Поиск в списке неповторяющихся значений

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

Следствием того, что поиск производится в списке неповторяющихся значений, является тот факт, что условиям поиска может соответствовать только одно значение.

По сути, пользователя интересует адрес искомого значения (номер строки или позиция значения в списке). Типичная задача: поиск позиции значения с выводом соответствующего значения из соседнего столбца (см. статью Справочник).

Наиболее часто используемые функции для этого вида поиска: ВПР() с аргументом ЛОЖЬ, ИНДЕКС(), ПОИСКПОЗ(), ПРОСМОТР().

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

2. Поиск ближайшего числового значения

Критерием поиска служит число, как правило, но не обязательно принадлежащее интервалу значений списка, по которому осуществляется поиск.

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

Пользователя может интересовать как само значение из списка (ближайшее), так и его позиция в списке (в этом случае выводится соответствующее значение из соседнего столбца). Типичная задача: Поиск ближайшего события. Если пользователя интересует само значение из списка (ближайшее), то список может содержать повторяющиеся значения (например, если в списке два значения 295 ближайших к 300, то совершенно не важно, какое из них будет выведено). Поиск существенно упрощается если список предварительно отсортирован. Если пользователя интересует позиция числа, ближайшего к критерию (для вывода соответствующего значения из соседнего столбца), то список должен содержать неповторяющиеся значения, т.к. при наличии повторов непонятно какому значению должен отдаваться приоритет (если требуется вывести все значения соответствующие ближайшим, то см. тип поиска 4).

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

Списки могут содержать числа и даты.

3. Поиск характеристики списка

Под характеристикой списка здесь понимается: минимальное/ максимальное значение списка, среднее, первое/ последнее слово по алфавиту. Список может содержать повторяющиеся значения. Хотя условиям поиска могут соответствовать несколько значений (например, два числа равны максимальному), но выводится только одно (максимальное).

Строго говоря, этот тип поиска не относится к "поиску значений в списке", т.к. пользователя не интересует какое-то конкретное значение из списка, а ему нужно найти определенную харастику списка, например, среднее значение, которое в общем случае может и не совпадать ни с одним значением списка. Типичная задача: поиск максимального значения по условию. К этому типу задач можно отнести сложение и подсчет с условиями (например, сумма всех положительных значений списка или количество значений в списке могут считаться такими же важными характеристиками списка как и минимальное/ максимальное значение).

Наиболее часто используемые функции для этого типа задач: МАКС(), МИН(), НАИБОЛЬШИЙ(), СРЗНАЧ(), СУММЕСЛИ(), СЧЁТ() и др.

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

4. Поиск нескольких значений

Поиск осуществляется в списках с повторяющимися значениями.

Критерием поиска может быть:

одно значение из списка;
набор (интервал) значений, не обязательно принадлежащих списку;
позиция значений в списке.

Т.к. условиям поиска могут соответствовать несколько значений, то их как правило требуется вывести в отдельный диапазон ячеек. Для этого используются как обычные формулы (например, на основе функций ИНДЕКС(), НАИМЕНЬШИЙ(), ЕСЛИ(), СТРОКА()), так и формулы массива. Также для вывода найденных значений может применяться Расширенный фильтр с опцией «Поместить результат в диапазон» или Сводные таблицы. Если выводить значения в отдельный диапазон не требуется, то можно воспользоваться стандартным Фильтром (CTRL+SHIFT+L или ).

Списки могут содержать значения любых типов: числа, текстовые значения и даты. При решении задач этого типа имеет смысл использовать подстановочные знаки (*, ?) (если диапазон поиска список текстовых значений).

Типичные задачи:

Критерий - одно значение из списка;

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

Критерий - набор (интервал) значений, не обязательно принадлежащих списку;

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

Критерий - позиция значений в списке.

В статьях Последнее значение в диапазоне, Третий повтор, Последние пять значений в столбце приведены примеры вывода значений исходной таблицы в зависимости от их позиции в списке.

excel2.ru