Впр по двум критериям excel: ВПР по двум и более критериям

ВПР по двум и более критериям

Хитрости »

18 Январь 2017       Дмитрий       120987 просмотров




Основные понятия (26)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (22)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (4)

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

Для большего понимания смысла статьи рекомендую сначала скачать файл с примером данных и формул:
Скачать пример:

  Tips_All_VlookupMany. xls (62,5 KiB, 10 150 скачиваний)

Например, имеется файл с таблицей такого вида:

И необходимо получить сумму выручки не только на основании салона, но и на основании бренда. При этом делать это автоматически, например для получения данных в такую табличку:

Т.е. в ячейке B2 из выпадающего списка выбирается Салон, а из B3 — Бренд(подробнее про выпадающие списки можно прочесть в статье Выпадающие списки). И в зависимости от выбора должна рассчитаться сумма Выручки и должен проставиться комментарий.

В приложенном к статье файле исходная таблица находится на листе «Отчет сентябрь», а вторая(с выбором) на листе «Выбор». На самом деле это не сильно принципиально, просто будет более понятна логика формул, приведенных ниже.
Сумму по двум критериям можно найти при помощи той же СУММЕСЛИМН(SUMIFS):
=СУММЕСЛИМН(‘Отчет сентябрь’!$C$2:$C$67;’Отчет сентябрь’!$A$2:$A$67;B2;’Отчет сентябрь’!$B$2:$B$67;B3)
=SUMIFS(‘Отчет сентябрь’!$C$2:$C$67,’Отчет сентябрь’!$A$2:$A$67,Выбор!B2,’Отчет сентябрь’!$B$2:$B$67,Выбор!B3)
Подробнее про поиск сумм по двум и более критериям можно прочитать в статье Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям.
А вот с комментарием сложнее — там содержится текст и СУММЕСЛИМН ничего не найдет. Да и в случае, если в исходных данных есть задвоения и суммировать по определению ничего не надо СУММЕСЛИМН будет не лучшим вариантом — ведь она вернет сумму всех ячеек, удовлетворяющих условиям. А это, повторюсь — не всегда нужно.
Здесь на помощь придет родственная с ВПР функция — ПОИСКПОЗ(MATCH):
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(B2&B3;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0))
=INDEX(‘Отчет сентябрь’!$D$2:$D$67,MATCH(B2&B3,’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67,0))
Эта функция является формулой массива. Это означает, что вводить её в ячейки надо нажатием не просто Enter, а сочетанием трех клавиш — Ctrl+Shift+Enter. Теперь рассмотрим подробнее принцип работы этой формулы, чтобы можно было применить на любых данных. На самом деле принцип не так уж сложен. Основной упор сделан на эти две «связки»:

  • B2&B3 — здесь объединяем значение выбранного Салона(Санкт-Петербург) и Бренда(Ниссан) в одну строку, чтобы получилось «Санкт-ПетербургНиссан». За объединение двух значений отвечает амперсанд(&).
  • ‘Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67 — а здесь мы последовательно объединяем в одну строку значения двух столбцов исходных данных — Салон и Бренд. Т.е. в результате мы получим массив объединенных значений: МоскваBMW, ВоронежBMW, СамараЛексус и т.д.(именно это объединение требует, чтобы формула вводилась как формула массива). И уже среди этих значений ищем «Санкт-ПетербургНиссан». При нахождении совпадения ПОИСКПОЗ вернет позицию строки, в которой нашел. И передаст её в ИНДЕКС. А т.к. ИНДЕКС в нашем случае возвращает значение из заданной строки указанного массива, то мы получаем требуемое.

По шагам это будет выглядеть так:

сначала искомые значения будут объединены в один
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(B2&B3;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0))
=>
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(Санкт-Петербург&Ниссан;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0))
=>
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(Санкт-ПетербургНиссан;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0))
=>
далее так же объединяем построчно значения всех столбцов для поиска значений
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(Санкт-ПетербургНиссан;{«МоскваBMW»:»ВоронежBMW»:»СамараЛексус»:. ..};0))
=>
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;4)
=>
только допы

Здесь важно следить за тем, чтобы значения в исходном массиве для поиска объединялись в том же порядке, что и искомые значения. На приложенном примере, если первым аргументом(искомое_значение) в ПОИСКПОЗ мы указали сначала Салон(B2), а потом Бренд(B3), то и вторым аргументом(просматриваемый_массив) мы должны указать два столбца в том же порядке — сначала Салон(‘Отчет сентябрь’!$A$2:$A$67), а уже потом Бренд(‘Отчет сентябрь’!$B$2:$B$67). Если их перепутать ошибки как таковой это не вызовет, но и значения найдены не будут.
Так же стоит учитывать, что такая формула будет рассчитываться дольше обычной, поэтому не стоит указывать столбцы полностью(‘Отчет сентябрь’!$A:$A), т.к. это может повлечь за собой расчет всего одной формулы непозволительно долгое время.
При этом очевидно, что просматривать значения можно не только из смежных столбцов, а из любых и уж точно они могут быть и первыми, и последними. И значения возвращаемые могут так же располагаться в любом столбце таблицы. И также хочу заметить, что это в примере используется только два критерия — реально их можно сделать и три, и пять, и десять. Объединяем сколько надо, указываем нужное кол-во столбцов так же с объединением и все. Но тут надо понимать, что в некоторых случаях будет оптимальнее добавить в исходные данные(те, где ищем) еще один столбец, в котором записать все данные, объединив их. Т.е. опять же на примере файла из статьи можно на листе «Отчет сентябрь» добавить формулу в столбец E: =A2&B2
и затем искать можно будет обычной формулой:
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(B2&B3;’Отчет сентябрь’!$E$2:$E$67;0))
=INDEX(‘Отчет сентябрь’!$D$2:$D$67,MATCH(B2&B3,’Отчет сентябрь’!$E$2:$E$67,0))
Больше нюансов, о которых я бы хотел рассказать, нет. Все стандартно, как для обычной связки ИНДЕКС(ПОИСКПОЗ.

В приложенном к статье примере приведен пример и с функцией СУММЕСЛИМН и с функцией ПОИСКПОЗ.
Скачать пример:

  Tips_All_VlookupMany.xls (62,5 KiB, 10 150 скачиваний)

P.S. Если кого-то пугает тот факт, что формулу надо вводить как формулу массива(Ctrl+Shift+Enter), то можно модифицировать формулу так:
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;СУММПРОИЗВ(МАКС((B2=’Отчет сентябрь’!$A$2:$A$67)*(B3=’Отчет сентябрь’!$B$2:$B$67)*(СТРОКА($B$2:$B$67)-1))))
=INDEX(‘Отчет сентябрь’!$D$2:$D$67,SUMPRODUCT(MAX((B2=’Отчет сентябрь’!$A$2:$A$67)*(B3=’Отчет сентябрь’!$B$2:$B$67)*(ROW($B$2:$B$67)-1))))
Здесь вместо ПОИСКПОЗ роль поиска номера строки в массиве ‘Отчет сентябрь’!$D$2:$D$67 играет СУММПРОИЗВ. Сам принцип работы СУММПРОИЗВ я описывал в этой статье — Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям. К этому могу добавить лишь некоторые разъяснения(хотя если хотите понять сам принцип — лучше потратить пару минут на статью).
(B2=’Отчет сентябрь’!$A$2:$A$67) — здесь значение выбранного салона сверяется со списком салонов на листе Отчет сентябрь. Где значения равны получаем значение ИСТИНА(TRUE), где отличаются — ЛОЖЬ(FALSE).
(B3=’Отчет сентябрь’!$B$2:$B$67) — здесь значение выбранного бренда сверяется со списком брендов на листе Отчет сентябрь. Где значения равны получаем значение ИСТИНА(TRUE), где отличаются — ЛОЖЬ(FALSE).
В результате перемножения этих двух выражений мы получим массив 1 и 0(1 будет там, где бренд и салон совпали, 0 — где различаются), т.к. ИСТИНА для Excel по сути =1, а ЛОЖЬ =0.
Далее полученный массив единиц и нулей перемножаем на выражение: СТРОКА($B$2:$B$67). Это выражение дает нам массив номеров строк {2:3:4:5:6:7:8 … и т.д.}т.к. в выражении берется номер строки на листе, а нам нужен номер в диапазоне ‘Отчет сентябрь’!$D$2:$D$67, то мы еще вычитаем 1(т.к. диапазон у нас начинается со второй строки). С таким же успехом можно было не вычитать, а либо все диапазоны взять с первой строки, либо указать так: СТРОКА($B$1:$B$66)
Полученный массив строк перемножаются на массив единиц и нулей салона и бренда. В результате получим номер строки, умноженный на 1 там, где салон и бренд равны искомым, и на ноль там, где отличаются. И в итоге массив номеров строки и нулей. И из этого отбирается максимальный номер строки.
По сути, это уже решает проблему поиска, но если значений, подходящих под условия больше одного, то именно такой результат может быть(даже скорее всего будет) неверным. Чтобы этого избежать мы применяем функцию МАКС(MAX), чтобы в результате из всех строк была отобрана только максимальная. Т.е. в результате мы получим не первое совпадение из всех возможных, а последнее.
Так же рекомендую ознакомиться со статьей Как просмотреть этапы вычисления формул, чтобы можно было самостоятельно поразбираться в принципах работы формул.

В сети можно найти так же решения при помощи макросов(VBA), но далеко не всегда есть возможность использовать макросы в файлах.

Также см.:
Как найти значение в другой таблице или сила ВПР
ВПР с возвратом всех значений
ВПР с поиском по нескольким листам
ВПР_МН
ВПР_ВСЕ_КНИГИ
Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам


Accessapple watchMultexPower Query и Power BIVBA управление кодамиБесплатные надстройкиДата и времяЗапискиИПНадстройкиПечатьПолитика КонфиденциальностиПочтаПрограммыРабота с приложениямиРазработка приложенийРосстатТренинги и вебинарыФинансовыеФорматированиеФункции Excelакции MulTExссылкистатистика

Функция ПРОСМОТРХ (XLOOKUP) — Справочник

Функция ПРОСМОТРХ (XLOOKUP) использует возможности динамических массивов и призвана заменить сразу функции ВПР и ГПР. В октябре 2019, до официального анонса, Microsoft обновила синтаксис функции.

Старый синтаксис — ПРОСМОТРХ(искомое_значение; просматриваемый_массив; возвращаемый_массив; [режим_сопоставления]; [режим_поиска]
Новый синтаксис — ПРОСМОТРХ(искомое_значение; просматриваемый_массив; возвращаемый_массив; [если_ничего_не_найдено];[режим_сопоставления]; [режим_поиска]

Описание функции ПРОСМОТРХ

Функция XLOOKUP анонсирована Microsoft практически через год после анонса динамических массивов которые Microsoft анонсировала 24 сентября 2018 года. Данная функция, как и возможность использования динамических массивов не должна быть доступна пользователям, купившим Office 2019 и, тем более, более ранним версиям.

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

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

Синтаксис ПРОСМОТРХ

=ПРОСМОТРХ(искомое_значение; просматриваемый_массив; возвращаемый_массив; [если_ничего_не_найдено];[режим_сопоставления]; [режим_поиска])

Аргументы

искомое_значениепросматриваемый_массиввозвращаемый_массивесли_ничего_не_найденорежим_сопоставлениярежим_поиска

Обязательный. Значение, которое необходимо найти

Обязательный. Массив или диапазон для поиска

Обязательный. Массив или диапазон, который нужно вернуть

Необязательный. Значение, которое будет возвращено функцией, в случае, если ничего не будет найдено. Параметр необязательный и в случае его отсутствия функция будет возвращать ошибку, если ничего не будет найдено, которую можно как и прежде обработать функцией ЕСЛИОШИБКА.

Необязательный. Используются следующие варианты для аргумента:

  • 0 — точное совпадение. Если ничего не найдено будет возвращена ошибка #N/А. Данный параметр используется по умолчанию.
  • -1 — точное совпадение. Если ничего не найдено, будет возвращено следующий меньший элемент.
  • 1 — точное совпадение. Если ничего не найдено, будет возвращено следующий более крупный элемент.
  • 2 — использование подстановочных знаков при поиске.

Необязательный. Используются следующие режимы поиска:

  • 1 — стандартный поиск с начала списка. Данный параметр используется по умолчанию.
  • -1 — обратный поиск с конца списка.
  • 2 — двоичный поиск (поиск по числам). Список будет должен будет отсортирован по возрастанию
  • -2 — двоичный поиск (поиск по числам). Список будет должен будет отсортирован по убыванию

Замечания

  • Функция ВПР по умолчанию, возвращала приблизительное совпадение, если пользователь не передал необязательный параметр «интервальный_просмотр». По заявлению Microsoft, непредвиденные результаты вводили в заблуждение неопытных пользователей, ведь для приблизительного соответствия массив должен быть отсортирован. Новая функция по умолчанию возвращает точное соответствие, это такая особенность работы и ее следует учитывать.
  • если в поле «режим_сопоставления» ввести значение 2, то в в поле «искомое_значение» можно будет использовать подстановочные знаки, где «?» — означает замену одного символа, а «*» — любое количество символов. На самом деле, по умолчанию передавать подстановочные знаки в ВПР можно, в новой функции для этого есть специальный переключатель. Скорее всего, это сделано по соображениям повышения производительности.
  • Размерность массивов, которые передаются в параметры «просматриваемый_массив» и «возвращаемый_массив» должна быть одинаковой, иначе функция вернет ошибку #ЗНАЧ!. В случае использование ВПР (ГПР) и поиск и возврат значения осуществлялся по сути из одного массива, поэтому не правильно указать размерность попросту было невозможно, здесь же на это следует обратить внимание.
  • ПРОСМОТРX как и ВПР, ищет первое значение в списке, если нужно найти все совпадения используйте функцию ФИЛЬТР

Основные преимущества перед ВПР (и ГПР)

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

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

    • Одним из популярных недостатков ВПР — это поиск значения исключительно в первом столбце диапазона. Решение в виде изменения источника данных таким образом, чтобы искомый столбец находился первым рассматривать не правильно.

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

  • Передача в функцию бесполезной информации

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

    Справедливости ради, следует отметить, что Microsoft буквально за месяц перед анонсом новой функции произвела усовершенствование производительности алгоритмов ВПР (ГПР). Но это не отменяет того факта, что всегда нужно передавать «бесполезный» пласт информации, который, к тому же, может затруднить читаемость функций.

  • Жесткое закрепление возвращаемого массива

    • Возвращаемый массив в функцию должен передаваться числом, т.е. функция ВПР читается как «найти значение «А» в первом столбце и вернуть соответствующее ему значение из столбца «n», где n — это число, например, 3″.

    Для больших таблиц это просто не удобно для пользователя высчитывать номер столбца с которого нужно вернуть результат, например, если вместо 3 будет 23 или 33. Но еще большее неудобство возникает, если в исходный диапазон будет добавлен еще столбец, или удален существующий. Если новый/удаленный столбец находятся между первым и искомым столбцом, придется изменить формулу.

    В новой функции ПРОСМОТРX диапазон с которого возвращается результат указывается диапазоном и добавление/удаление столбцов никак не скажется на результатах вычисления формулы.

  • Возвращение результата не одного диапазона

    • Использую ВПР вы сможете найти, например, сотрудника и вернуть его зарплату, если нужно еще вернуть подразделение в котором он работает, придется написать еще одну формулу. С помощью ПРОСМОТРX можно вернуть сразу же двумерный массив.

    Опять таки, с в версиях Excel, с поддержкой динамических массивов, в ВПР тоже можно указать не просто номер столбца, а передать туда массив чисел и функция вернет несколько значений. Но это появилось с поддержкой динамических массивов.

  • Работа как с вертикальными так и горизонтальными массивами

    • Поскольку ПРОСМОТРX призвана заменить сразу 2 функции, то очевидно, что она должна работать с массивами любой ориентации.

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

  • Возможность поиска в обратном порядке

    • В случае использования ВПР (ГПР) поиск всегда осуществлялся с начала списка и обойти это ограничение без переформатирования самого исходного диапазона было невозможно.

    Теперь же поиск можно произвести с «конца списка», например, если список отсортирован по показателям продаж по дням, можно сразу же найти и лучший, и худший результат по определенному сотруднику.

  • Больше возможностей в режимах сопоставления

    • В случае использования старых функций был вариант не точного поиска, если диапазон отсортирован и точного значения не находилось, то возвращалось максимальное значение, которое меньше искомого. В случае использование ПРОСМОТРX можно найти как значение меньше искомого, так и больше, если точного совпадения не будет.

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

    Пример

    Видео работы функции

    Дополнительные материалы

    Microsoft представила замену ВПР, ГПР а также ПОИСКПОЗ

    Microsoft добавила динамические массивы в Excel и новые функции.

    Формулы поиска Excel с несколькими критериями

    Обновлено 16.12.2022: Будьте в курсе последних новостей из Excel и загружайте шаблоны Excel уже сегодня.

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

    Microsoft Excel

    Получите лучшее представление о своих данных.

    Узнать больше

    Использование двух критериев для возврата значения из таблицы

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

    Критериями являются «Имя» и «Продукт», и вы хотите, чтобы они возвращали значение «Кол-во» в ячейке C18. Поскольку значение, которое вы хотите вернуть, является числом, вы можете использовать простую формулу СУММПРОИЗВ(), чтобы найти Имя «Джеймс Аткинсон» и Продукт «Молочный пакет», чтобы вернуть количество. Формула СУММПРОИЗВ в ячейке C18 выглядит так:

    =СУММПРОИЗВ((B3:B13=C16)*(C3:C13=C17)*(D3:D13))

    Он ищет в диапазоне B3:B13 значение в ячейке C16 и в диапазоне C3:C13 для значения в ячейке C17. Когда он находит оба, он возвращает значение в столбце D из той же строки, где он соответствует обоим критериям. Вот как это будет выглядеть:

    Возвращает значение 1, которое соответствует значению в ячейке D4 («Джеймс Аткинсон» в строке 4, а также «Молочный пакет» в той же строке), тем самым возвращая значение в столбце D из тот ряд. Давайте изменим значение в ячейке C5 с «Бутылка вина» на «Пакет молока», чтобы посмотреть, что произойдет с формулой в ячейке C18:

    Так как наша формула нашла две строки, в которых удовлетворяются оба критерия, она суммирует значения в столбце D в обеих строках, что дает нам Qty, равное 6.

    Этот метод нельзя использовать, если вы хотите найти два критерия и вернуть текстовый результат. Например, это не сработает:

    Вы будете искать Имя «Джеймс Аткинсон», где Кол-во равно 1, и вы хотите вернуть название Продукта, которое соответствует этим двум критериям. Эта формула выдаст нам ошибку #ЗНАЧ! Вместо этого вы можете использовать формулу с комбинацией функций СУММПРОИЗВ, ИНДЕКС и СТРОКА, например такую:

    =ИНДЕКС(C3:C13,СУММПРОИЗВ((B3:B13=C16)*(D3:D13=C18)*СТРОКА(C3:C13)),0)

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

    Использование полей поиска и многозначных полей в запросах

    Узнать больше

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

    =ПРОСМОТР(2,1/(B3:B13=C16)/(D3:D13=C18),(C3:C13) ))

    Результат будет таким же, как и в предыдущем решении. Что делает эта формула, так это делит 1 на массив значений True/False (B3:B13=C16), а затем на другой массив значений True/False (D3:D13=C18). Это вернет либо 1, либо #DIV/0! ошибка. Если вы используете 2 в качестве значения поиска, то формула сопоставит его с последним числовым значением в диапазоне, то есть с последней строкой, в которой оба условия имеют значение True. Это «векторная форма» ПРОСМОТРА, поэтому вы можете использовать ее для получения соответствующего значения, возвращаемого из C3:C13. Я использовал 2 в качестве значения ПРОСМОТР, но это может быть любое число, начиная с 1. Если формулы не найдут совпадения, вы, конечно же, получите ошибку #Н/Д!

    Вы также можете использовать формулу массива, используя функцию ПОИСКПОЗ, например:

    {=ИНДЕКС(C3:C13,ПОИСКПОЗ(1,(B3:B13=C16)*(D3:D13=C18),0 ))}

    С помощью этого метода вы можете использовать функцию ПОИСКПОЗ, чтобы найти строку, в которой выполняются оба условия. Это возвращает значение 1, которое сопоставляется с 1, которое используется в качестве значения поиска функции ПОИСКПОЗ, таким образом, возвращая нас к строке, в которой выполняются условия. Используя значение ИНДЕКС, вы можете найти значение в диапазоне C3:C13, которое находится в строке, возвращенной функцией ПОИСКПОЗ. В данном случае это была строка 2, которая соответствует второй строке в диапазоне C3:C13.

    Управление задачами в Microsoft 365

    Совместная работа над общими документами Office, включая Excel, Word и PowerPoint.

    Начните сегодня

    Использование нескольких критериев для возврата значения из таблицы

    Во всех этих примерах показано, как использовать два критерия для поиска. Эти формулы также легко использовать, если у вас более двух критериев — вы просто добавляете их в формулы. Вот как будут выглядеть формулы, если добавить еще один критерий:

    =СУММПРОИЗВ((B3:B13=C16)*(C3:C13=C17)*(E3:E13=C18)*(D3:D13))

    =ИНДЕКС(C3:C13,СУММПРОИЗВ(( B3:B13=C16)*(D3:D13=C18)*(E3:E13=C18)*СТРОКА(C3:C13)),0)

    =ПРОСМОТР(2,1/(B3:B13=C16) )/(D3:D13=C18)/(E3:E13=C18),(C3:C13))

    {=ИНДЕКС(C3:C13,ПОИСКПОЗ(1,(B3:B13=C16)*(D3 :D13=C18) *(E3:E13=C18),0))}

    Подробнее

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

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

    Смотреть видео — Как использовать функцию ВПР с несколькими критериями из указанной строки.

    Но часто возникает необходимость использовать ВПР Excel с несколькими критериями.

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

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

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

    В таких случаях это руководство для вас.

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

    • Использование вспомогательного столбца.
    • Использование функции ВЫБОР.

    ВПР с несколькими критериями — использование вспомогательного столбца

    Я поклонник вспомогательных столбцов в Excel.

    Я нахожу два существенных преимущества использования вспомогательных столбцов по сравнению с формулами массива:

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

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

    Возвращаясь к рассматриваемому вопросу, вспомогательный столбец необходим для создания уникального квалификатора. Затем этот уникальный квалификатор можно использовать для поиска правильного значения. Например, в данных есть три Matt, но есть только одна комбинация Matt и Unit Test или Matt и Mid-Term.

    Вот шаги:

    • Вставьте вспомогательный столбец между столбцами B и C.
    • Используйте следующую формулу во вспомогательном столбце:=A2&”|”&B2
      • Это создаст уникальные квалификаторы для каждого экземпляра, как показано ниже.
    • Используйте следующую формулу в G3 =VLOOKUP($F3&”|”&G$2,$C$2:$D$19,2,0)
    • Копировать для всех ячеек.

    Как это работает?

    Мы создаем уникальные квалификаторы для каждого экземпляра имени и экзамена. В используемой здесь функции ВПР значение поиска было изменено на $F3&”|”&G$2 , так что оба критерия поиска объединены и используются как одно значение поиска. Например, искомое значение для функции ВПР в G2 — Matt|Unit Test. Теперь это значение поиска используется для получения оценки от C2: D19..

    Уточнения:

    У вас может возникнуть несколько вопросов, поэтому я решил попытаться ответить на них здесь:

    • Почему я использовал | символ при объединении двух критериев? — В некоторых исключительно редких (но возможных) условиях у вас могут быть два разных критерия, но в конечном итоге они дают один и тот же результат при объединении. Вот очень простой пример (простите меня за отсутствие творчества здесь):

    Обратите внимание, что, хотя A2 и A3 разные, а B2 и B3 разные, комбинации в конечном итоге будут одинаковыми. Но если использовать разделитель, то даже комбинация будет другой (D2 и D3).

    • Почему я вставил вспомогательный столбец между столбцами B и C, а не в крайнем левом углу? – Нет ничего плохого в том, чтобы вставить вспомогательный столбец в крайнее левое положение. На самом деле, если вы не хотите изменять исходные данные, это должно быть выходом. Я сделал это, так как это позволяет мне использовать меньшее количество ячеек в функции ВПР. Вместо того, чтобы иметь 4 столбца в массиве таблиц, я мог обойтись только двумя столбцами. Но это только я.

    Теперь не существует универсального размера. Некоторые люди могут предпочесть не использовать какой-либо вспомогательный столбец при использовании функции ВПР с несколькими критериями.

    Итак, вот вам метод без вспомогательных столбцов.

    Загрузить файл примера

    ВПР с несколькими критериями — использование функции ВЫБОР в рабочей тетради.

    Учитывая тот же набор данных, который использовался выше, вот формула, которая даст вам результат:

    =ВПР($E3&”|”&F$2,ВЫБОР({1,2},$A$2:$A$19 &”|”&$B$2:$B$19,$C$2:$C$19),2,0)

    Так как это формула массива, используйте ее с Control + Shift + Enter, а не просто Enter.

    Как это работает?

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

    Позвольте мне показать вам, что я имею в виду под данными виртуального помощника.

    На приведенном выше рисунке, когда я выбираю часть формулы ВЫБОР и нажимаю F9, отображается результат, который даст формула ВЫБОР.

    Результат {«Matt|Unit Test», 91; «Bob|Unit Test», 52;……}

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

    Читайте также: