ВПР по двум и более критериям
Наверняка все, кто знаком с функцией ВПР знают, что она осуществляет поиск заданных значений исключительно в левом столбце указанной таблицы(подробнее про ВПР можно прочитать в статье: Как найти значение в другой таблице или сила ВПР). Так же многие знают, что ВПР ищет только на основании одного значения.
Для большего понимания смысла статьи рекомендую сначала скачать файл с примером данных и формул:
Скачать пример:
Tips_All_VlookupMany.xls (62,5 KiB, 11 095 скачиваний)
Например, имеется файл с таблицей такого вида:
И необходимо получить сумму выручки не только на основании салона, но и на основании бренда. При этом делать это автоматически, например для получения данных в такую табличку:
Т.е. в ячейке
Сумму по двум критериям можно найти при помощи той же
Подробнее про поиск сумм по двум и более критериям можно прочитать в статье Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям.
А вот с комментарием сложнее - там содержится текст и СУММЕСЛИМН ничего не найдет. Да и в случае, если в исходных данных есть задвоения и суммировать по определению ничего не надо СУММЕСЛИМН будет не лучшим вариантом - ведь она вернет сумму всех ячеек, удовлетворяющих условиям. А это, повторюсь - не всегда нужно.
Здесь на помощь придет родственная с ВПР функция -
Эта функция является формулой массива. Это означает, что вводить её в ячейки надо нажатием не просто Enter, а сочетанием трех клавиш - Ctrl+Shift+Enter. Теперь рассмотрим подробнее принцип работы этой формулы, чтобы можно было применить на любых данных. На самом деле принцип не так уж сложен. Основной упор сделан на эти две "связки":
B2 &B3 'Отчет сентябрь'!$A$2:$A$67&'Отчет сентябрь'!$B$2:$B$67 - а здесь мы последовательно объединяем в одну строку значения двух столбцов исходных данных - Салон и Бренд. Т.е. в результате мы получим массив объединенных значений: МоскваBMW, ВоронежBMW, СамараЛексус и т.д.(именно это объединение требует, чтобы формула вводилась как формула массива) . И уже среди этих значений ищем "Санкт-ПетербургНиссан". При нахождении совпадения ПОИСКПОЗ вернет позицию строки, в которой нашел. И передаст её в ИНДЕКС. А т.к. ИНДЕКС в нашем случае возвращает значение из заданной строки указанного массива, то мы получаем требуемое.
По шагам это будет выглядеть так:
=ИНДЕКС('Отчет сентябрь'!$D$2:$D$67;ПОИСКПОЗ(
=ИНДЕКС('Отчет сентябрь'!$D$2:$D$67;ПОИСКПОЗ(
=ИНДЕКС('Отчет сентябрь'!$D$2:$D$67;ПОИСКПОЗ(
=ИНДЕКС('Отчет сентябрь'!$D$2:$D$67;ПОИСКПОЗ(
=ИНДЕКС('Отчет сентябрь'!$D$2:$D$67;4)
только допы
Здесь важно следить за тем, чтобы значения в исходном массиве для поиска объединялись в том же порядке, что и искомые значения. На приложенном примере, если первым аргументом(искомое_значение) в ПОИСКПОЗ мы указали сначала
Так же стоит учитывать, что такая формула будет рассчитываться дольше обычной, поэтому не стоит указывать столбцы полностью(
При этом очевидно, что просматривать значения можно не только из смежных столбцов, а из любых и уж точно они могут быть и первыми, и последними. И значения возвращаемые могут так же располагаться в любом столбце таблицы. И также хочу заметить, что это в примере используется только два критерия - реально их можно сделать и три, и пять, и десять. Объединяем сколько надо, указываем нужное кол-во столбцов так же с объединением и все. Но тут надо понимать, что в некоторых случаях будет оптимальнее добавить в исходные данные(те, где ищем) еще один столбец, в котором записать все данные, объединив их. Т.е. опять же на примере файла из статьи можно на листе "Отчет сентябрь" добавить формулу в столбец E:
и затем искать можно будет обычной формулой:
Больше нюансов, о которых я бы хотел рассказать, нет. Все стандартно, как для обычной связки
В приложенном к статье примере приведен пример и с функцией СУММЕСЛИМН и с функцией ПОИСКПОЗ.
Tips_All_VlookupMany.xls (62,5 KiB, 11 095 скачиваний)
P.S. Если кого-то пугает тот факт, что формулу надо вводить как формулу массива(Ctrl+Shift+Enter), то можно модифицировать формулу так:
Здесь вместо ПОИСКПОЗ роль поиска номера строки в массиве
В результате перемножения этих двух выражений мы получим массив 1 и 0(1 будет там, где бренд и салон совпали, 0 - где различаются), т.к. ИСТИНА для Excel по сути =1, а ЛОЖЬ =0.
Далее полученный массив единиц и нулей перемножаем на выражение:
Полученный массив строк перемножаются на массив единиц и нулей салона и бренда. В результате получим номер строки, умноженный на 1 там, где салон и бренд равны искомым, и на ноль там, где отличаются. И в итоге массив номеров строки и нулей. И из этого отбирается максимальный номер строки.
По сути, это уже решает проблему поиска, но если значений, подходящих под условия больше одного, то именно такой результат может быть(даже скорее всего будет) неверным. Чтобы этого избежать мы применяем функцию
Так же рекомендую ознакомиться со статьей Как просмотреть этапы вычисления формул, чтобы можно было самостоятельно поразбираться в принципах работы формул.
В сети можно найти так же решения при помощи макросов(VBA), но далеко не всегда есть возможность использовать макросы в файлах.
Также см.:
Как найти значение в другой таблице или сила ВПР
ВПР с возвратом всех значений
ВПР с поиском по нескольким листам
ВПР_МН
ВПР_ВСЕ_КНИГИ
Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям
Статья помогла? Поделись ссылкой с друзьями!
Видеоуроки
