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

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

Хитрости »

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

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

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

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

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

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

Т.е. в ячейке 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, 9 709 скачиваний)

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ссылкистатистика

ВПР Два критерия — voxt.

ru

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

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

Что означают два критерия?

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

Вы можете скачать этот шаблон Excel с двумя критериями Vlookup здесь — шаблон Excel с двумя критериями Vlookup

Например, посмотрите на приведенную ниже таблицу на листе Excel.

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

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

Мы применили ВПР для сотрудника «Питер» в третьем квартале, но посмотрите на результат, который ВПР дала нам результат «Q1» (ячейки желтого цвета) вместо результата «Q3» (ячейки зеленого цвета).

Это связано с тем, что функция VLOOKUP находит первое значение для «Peter» как числа «Q1», поэтому возвращается то же самое, VLOOKUP всегда дает результат из первого доступного значения. Итак, вот где концепция Два критерия является важным. Нам нужно создать новый столбец, чтобы иметь уникальный список значений.

Создать столбец уникального значения поиска

У нас есть повторяющиеся имена, но для каждого повторяющегося имени у нас есть разные номера кварталов, поэтому, используя эти два столбца, мы можем создать уникальный столбец под названием «Emp & Qtr».

• Для столбца «Emp & Qtr» нам нужно объединить имя сотрудника и квартал вместе, чтобы получить столбец с уникальным значением.

Мы использовали символ амперсанда (&), чтобы связать имя сотрудника и квартал, также между именем сотрудника и кварталом, мы использовали символ подчеркивания (_), чтобы различать имя сотрудника и квартал.

Теперь нам нужно применить исправленную функцию ВПР по двум критериям. На этот раз значение поиска — «Emp & Qtr», а не только имя сотрудника.

• Откройте функцию ВПР в ячейке h3.

• Теперь нам нужно немного потрудиться при выборе поисковое значение, сначала выберите ячейку имени сотрудника «F2».

• Введите символ конкатенации амперсанд (&).

• Теперь в таблице мы использовали символ подчеркивания (_) для разделения имени сотрудника и четверти, поэтому используем то же самое здесь, прежде чем мы выберем четвертную ячейку G2.

• Теперь снова поставьте символ амперсанда и выберите четверть ячейки G2.

• Теперь это значение поиска похоже на то, которое мы создали в таблице, теперь выберите массив таблицы из столбца A в столбец D.

• Укажите номер столбца в таблице как 4 и поиск диапазона как 0 (Ложь).

• Ладно, все. Закройте скобку и нажмите клавишу ввода, чтобы получить результат.

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

• Мы создали выпадающий список для имени и квартала сотрудника. На основе выбора из раскрывающегося списка будут показаны сведения о доходах для выбранного сотрудника и квартала.

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

Два критерия Vlookup с использованием функции выбора

Мы также можем использовать еще один метод для соответствия более чем одному критерию в VLOOKUP, то есть с помощью функции ВЫБОР в Excel.

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

• Для выбора Таблица Массив, откройте функцию ВЫБРАТЬ сейчас.

• Войти Индекс как 1, 2 в фигурных скобках.

• За Значение1, сначала выберите столбец Сотрудник.

• Затем, используя амперсанд, объедините подчеркивание и четверть столбца.

• Для Значение 2 в столбце выберите столбец «Доход».

• Мы закончили с функцией ВЫБРАТЬ, чтобы выбрать Таблица Массив, закройте скобку функции ВЫБРАТЬ, а для Номер столбца и поиск диапазона введите обычные значения.

• Хорошо, мы закончили с формулой. Прежде чем мы закроем формулу, мы должны помнить одну вещь: это тоже форумы для массивов. Нам нужно закрыть формулу, удерживая три клавиши, т.е. «Ctrl + Shift + Enter».

То, что нужно запомнить

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

УЗНАТЬ БОЛЬШЕ >>

Post Views:
439

Похожие записи

Прокрутить вверх

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

Этот пост написан JP Pinto, лауреатом премии Great White Shark Award за лучшую статью, написанную о VLOOLUP во время недели VLOOKUP. Мы попросили JP поделиться с вами своими знаниями об Excel.

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

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

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

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

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

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

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

Так как наша формула нашла две строки, в которых удовлетворяются оба критерия, она суммирует значения в столбце D в обеих строках, что дает нам количество, равное 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.

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

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

=СУММПРОИЗВ((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,MATCH(1,(B3:B13=C16)*(D3:D13=C18) *(E3:E13=C18),0))}

Как видите, в зависимости от того, что находится в вашем таблицы данных, вы можете использовать несколько различных методов, используя различные функции Excel, для поиска значений. Наслаждайтесь применением их к вашим собственным электронным таблицам 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;……}

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

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