Skip to content
В статье показаны способы поиска (ВПР) нескольких значений в Excel на основе одного или нескольких условий и возврата нескольких результатов в столбце, строке или в отдельной ячейке.
При использовании Microsoft Excel для анализа данных вы часто можете оказаться в ситуации, когда вам нужно получить все совпадающие значения для определенного имени, наименования, артикула или какого-либо другого уникального идентификатора. Первое решение, которое приходит на ум, — это использование функции Excel ВПР (VLOOKUP). Но проблема в том, что она может возвращать только одно значение.
Поиск нескольких значений в Excel может быть выполнен с помощью совместного использования ряда функций. Если вы не являетесь экспертом в Excel, не спешите покидать эту страницу. Я постараюсь объяснить логику поиска, чтобы даже новичок мог понять формулы и настроить их для решения подобных задач.
Поиск нескольких позиций в Excel с помощью формулы
Как было сказано ранее, невозможно заставить функцию ВПР Excel возвращать несколько значений. Проблему можно решить, используя следующие функции в формуле массива:
- ЕСЛИ – оценивает условие и возвращает одно значение, если условие выполняется, и другое значение, если условие не выполняется.
- НАИМЕНЬШИЙ– получает N-е наименьшее значение в массиве.
- ИНДЕКС — возвращает элемент массива на основе указанных вами номеров строк и столбцов.
- СТРОКА — возвращает номер строки.
- СТОЛБЕЦ — возвращает номер столбца.
- ЕСЛИОШИБКА – перехватывает ошибки.
Ниже вы найдете несколько примеров таких формул. Их часто называют формулами ВПР нескольких значений, хотя сама функция ВПР здесь не используется. Дело в том, что часто под термином ВПР подразумевают любой поиск в Excel, какими бы формулами и функциями он ни осуществлялся.
Поиск нескольких значений и возврат результатов в столбец
Допустим, у вас есть имена продавцов в столбце А и товары, которые они продали, в столбце В. Таблица содержит несколько записей для каждого продавца. Ваша цель — получить список всех товаров, относящихся к данному человеку. Чтобы это сделать, выполните следующие действия:
- Введите список имен продавцов в какую-нибудь пустую строку того же или другого рабочего листа. В этом примере имена вводятся в ячейки D2:H2:
Совет. Чтобы быстро записать все имеющиеся в списке имена, вы можете использовать эту инструкцию – как получить список уникальных значений в Excel.
- Под именем выберите количество пустых ячеек, равное или превышающее максимально возможное количество совпадений, введите одно из следующих выражений и нажмите
Ctrl + Shift + Enterдля ввода его как формулы массива (в этом случае вы сможете редактировать формулу только сразу во всем диапазоне, где она введена). Или же вы можете записать это в левую верхнюю ячейку, также использовавCtrl + Shift + Enter, а затем перетащить вниз еще на несколько ячеек (в этом случае вы сможете редактировать формулу в каждой ячейке отдельно).
=ЕСЛИОШИБКА(ИНДЕКС($B$3:$B$13; НАИМЕНЬШИЙ(ЕСЛИ(D$2=$A$3:$A$13; СТРОКА($B$3:$B$13)-2;””); СТРОКА()-2));””)
или
=ЕСЛИОШИБКА(ИНДЕКС($B$3:$B$13;НАИМЕНЬШИЙ(ЕСЛИ(D$2=$A$3:$A$13;СТРОКА($A$3:$A$13)-МИН(СТРОКА($A$3:$A$13))+1;””); СТРОКА()-2));””)
Как видите, первая формула немного компактнее, а вторая более универсальна и требует меньше модификаций (подробнее о синтаксисе и логике мы поговорим чуть дальше).
- Скопируйте формулу в соседние столбцы справа. Для этого перетащите маркер заполнения (небольшой квадрат в правом нижнем углу выбранного диапазона) вправо.
Результат ВПР нескольких значений в столбце будет выглядеть примерно так:
Здесь мы использовали первую формулу:
=ЕСЛИОШИБКА(ИНДЕКС($B$3:$B$13; НАИМЕНЬШИЙ(ЕСЛИ(D$2=$A$3:$A$13; СТРОКА($B$3:$B$13)-2;””); СТРОКА()-2));””)
Как это работает.
Это пример использования Excel от среднего до продвинутого уровня, который подразумевает базовые знания формул массива и функций Excel. Итак, разберём пошагово:
- Функция ЕСЛИ
В основе поиска – функция ЕСЛИ, чтобы получить позиции всех вхождений искомого значения в диапазоне поиска: ЕСЛИ(D$2=$A$3:$A$13; СТРОКА($B$3:$B$13) )-2;””)
ЕСЛИ сравнивает искомое значение (D2) с каждым значением в диапазоне поиска (A3:A13) и, если совпадение найдено, возвращает относительную позицию строки; пустое значение (“”) в противном случае.
Относительная позиция вычисляется путем вычитания 2 из СТРОКА($B$3:$B$13), чтобы первая позиция с формулой имела порядковый номер 1 (то есть, 3-2=1). Если ваш диапазон вывода начинается со строки 2, тогда вычтите 1 и так далее. Результатом этой операции является массив {1;2;3;4;5;6;7;8;9;10;11}, который поступает в аргумент значение_если_истина функции ЕСЛИ.
Вместо приведенного выше вычисления вы можете использовать следующее выражение:
СТРОКА(столбец_просмотра) – МИН(СТРОКА(столбец_просмотра))+1
Оно возвращает тот же результат, но не требует каких-либо изменений независимо от местоположения возвращаемого столбца. В этом примере это будет СТРОКА($A$3:$A$13)-МИН(СТРОКА($A$3:$A$13))+1.
Итак, на данный момент у вас есть массив, состоящий из чисел (позиций совпадений) и пустых строк (несовпадений). Для ячейки D3 в этом примере у нас есть следующий массив:
=ЕСЛИОШИБКА(ИНДЕКС($B$3:$B$13; НАИМЕНЬШИЙ({1:””:””:””:5:””:””:8:””:””:””}; СТРОКА()-2));””)
Если вы сверитесь с исходными данными, вы увидите, что «Сергей» (значение поиска в D2) появляется на 1- й , 5 -й и 8 -й позициях в диапазоне поиска (A3: A13).
- Функция НАИМЕНЬШИЙ
Затем вступает в действие функция НАИМЕНЬШИЙ(массив; k), чтобы определить, какие совпадения должны быть возвращены в конкретной ячейке.
С уже установленным в предыдущем шаге массивом давайте определим аргумент k , т. е. k-е наименьшее возвращаемое значение. Для этого вы делаете своего рода «инкрементный счетчик» СТРОКА()-n, где «n» — это номер строки первой ячейки формулы минус 1. В этом примере мы ввели формулу в ячейки D3:D7, поэтому СТРОКА()-2 возвращает «1» для ячейки D3 (строка 3 минус 2), «2» для ячейки D4 (строка 4 минус 2) и т. д.
В результате функция НАИМЕНЬШИЙ извлекает первый наименьший элемент массива в ячейку D3, второй наименьший элемент в ячейку D4 и так далее. И это превращает первоначальную длинную и сложную формулу в очень простую, например:
=ЕСЛИОШИБКА(ИНДЕКС($B$3:$B$13;{1});””)
Совет. Чтобы увидеть значение, вычисленное определенной частью формулы, выделите эту часть в самой формуле и нажмите F9.
- Функция ИНДЕКС
Здесь все просто. Вы используете функцию ИНДЕКС, чтобы вернуть значение элемента массива на основе его номера.
- Функция ЕСЛИОШИБКА
И, наконец, вы оборачиваете формулу в функцию ЕСЛИОШИБКА для обработки возможных ошибок, которые неизбежны, потому что вы не можете знать, сколько совпадений будет возвращено для того или иного искомого значения. Ведь вы копируете формулу в число ячеек явно большее, чем количество возможных совпадений, то есть «с запасом». Чтобы не пугать пользователей кучей ошибок, просто замените их пустой строкой (пустой ячейкой).
Примечание. Обратите внимание на правильное использование абсолютных и относительных ссылок на ячейки в формуле. Все ссылки фиксированы, за исключением ссылки на относительный столбец в искомом значении (D$2), которая должна изменяться в зависимости от относительного положения столбцов, в которые копируется формула, чтобы возвращать совпадения для других имён.
На скриншоте ниже вы можете видеть, как работает вторая формула
=ЕСЛИОШИБКА(ИНДЕКС($B$3:$B$13;НАИМЕНЬШИЙ(ЕСЛИ(D$2=$A$3:$A$13;СТРОКА($A$3:$A$13)-МИН(СТРОКА($A$3:$A$13))+1;””); СТРОКА()-2));””)
Обобщив эти два решения, мы получим следующие общие формулы для ВПР нескольких значений в Excel, которые будут выведены в столбец:
Вариант 1 :
=ЕСЛИОШИБКА(ИНДЕКС(диапазон_возвращаемых_значений; НАИМЕНЬШИЙ(ЕСЛИ(искомое_значение = диапазон_искомых_значений; СТРОКА(диапазон_возвращаемых_значений)-m;””); СТРОКА()-n));””)
Вариант 2:
=ЕСЛИОШИБКА(ИНДЕКС(диапазон_возвращаемых_значений;НАИМЕНЬШИЙ(ЕСЛИ(искомое_значение = диапазон_искомых_значений;СТРОКА(диапазон_искомых_значений)-МИН(СТРОКА(диапазон_искомых_значений))+1;””); СТРОКА()-n));””)
где:
- m — номер строки первой ячейки в возвращаемом диапазоне минус 1.
- n — номер строки первой ячейки с формулой минус 1.
Примечание. В приведенном выше примере и n, и m равны 2, потому что наш диапазон возвращаемых значений начинается, да и сама формула расположена, в строке 3. В ваших таблицах Эксель это вполне могут быть и другие числа.
Поиск нескольких совпадений и возврат результатов в строке
Если вы хотите вернуть несколько найденных значений в строках, а не в столбцах, измените приведенные выше формулы Excel следующим образом:
=ЕСЛИОШИБКА(ИНДЕКС($B$3:$B$13; НАИМЕНЬШИЙ(ЕСЛИ($D3=$A$3:$A$13;СТРОКА($B$3:$B$13)-2;””); СТОЛБЕЦ()-4));””)
или
=ЕСЛИОШИБКА(ИНДЕКС($B$3:$B$13;НАИМЕНЬШИЙ(ЕСЛИ($D3=$A$3:$A$13;СТРОКА($A$3:$A$13)-МИН(СТРОКА($A$3:$A$13))+1;””);СТОЛБЕЦ()-4)); “”)
Как и в предыдущем примере, обе они являются формулами массива, поэтому не забудьте нажать комбинацию Ctrl + Shift + Enter, чтобы записать их правильно.
Формулы работают с той же логикой, что и в предыдущем примере, за исключением того, что вы используете функцию СТОЛБЕЦ вместо СТРОКА. Чтобы определить, какое совпадающее значение должно быть возвращено в конкретной ячейке, используем: СТОЛБЕЦ()-n где n — номер столбца первой ячейки, в которую вводится формула, минус 1. В этом примере результаты выводятся в диапазон E2:H2. Поскольку E является пятым столбцом, n равно 4 (5-1=4).
Примечание. Чтобы формула правильно копировалась вправо и вниз, обратите внимание на ссылки на значения поиска, где используется абсолютный адрес столбца и относительный адрес строки, например $D3.
И вот общие формулы для ВПР в Excel нескольких значений, возвращаемых по строке:
Формула 1 :
=ЕСЛИОШИБКА(ИНДЕКС(диапазон_возвращаемых_значений; НАИМЕНЬШИЙ(ЕСЛИ(искомое_значение = диапазон_искомых_значений; СТРОКА(диапазон_возвращаемых_значений)-m;””); СТОЛБЕЦ()-n));””)
Формула 2:
=ЕСЛИОШИБКА(ИНДЕКС(диапазон_возвращаемых_значений;НАИМЕНЬШИЙ(ЕСЛИ(искомое_значение = диапазон_искомых_значений;СТРОКА(диапазон_искомых_значений)-МИН(СТРОКА(диапазон_искомых_значений))+1;””); СТОЛБЕЦ()-n));””)
где:
- m — номер строки первой ячейки в возвращаемом диапазоне, минус 1.
- n — номер столбца первой ячейки, в которой записана формула, минус 1.
Поиск нескольких значений на основе нескольких условий
Вы уже знаете, как выполнять поиск нескольких значений в Excel на основе одного условия. Но что, если вы хотите вернуть несколько совпадений сразу на основе двух или более критериев? Продолжая предыдущие примеры – что, если у вас в таблице есть дополнительный столбец «Месяц» и вы хотите получить список всех товаров, проданных конкретным продавцом в определенном месяце?
Если вы знакомы с формулами массивов, то, возможно, помните, что они позволяют использовать знак умножения (*) в качестве логического оператора И. Таким образом, вы можете просто взять выражения, рассмотренные в двух предыдущих примерах, и заставить их проверять несколько условий, как показано ниже.
Как вернуть несколько значений в столбце.
Выведем искомые значения, соответствующие одновременно нескольким условиям, в привычном нам виде – вертикально в одном столбце.
В общем виде это выглядит так:
=ЕСЛИОШИБКА(ИНДЕКС(диапазон_возвращаемых_значений; НАИМЕНЬШИЙ(ЕСЛИ(1=((–(искомое_значение1 = диапазон_искомых_значений1)) * (–(искомое_значение2 = диапазон_искомых_значений2))) ; СТРОКА(диапазон_возвращаемых_значений)-m;””); СТРОКА()-n));””)
где:
- m — номер строки первой ячейки в возвращаемом диапазоне, минус 1.
- n — номер строки первой слева ячейки с формулой, минус 1.
Предположим, что список продавцов (диапазон_искомых_значений1) находится в A3:A30, список месяцев (диапазон_искомых_значений2) находится в B3: B30, интересующий продавец (искомое_значение1) указан в ячейке E3, а нужный месяц (искомое_значение2) – в ячейке F3. Тогда формула поиска принимает следующий вид:
=ЕСЛИОШИБКА(ИНДЕКС($C$3:$C$30; НАИМЕНЬШИЙ(ЕСЛИ(1=((–($E$3=$A$3:$A$30))*(–($F$3=$B$3:$B$30))); СТРОКА($C$3:$C$30)-2;””); СТРОКА()-2));””)
Таким образом, мы вводим имя в E3, месяц в F3, и получаем список товаров в столбце G:
Как вернуть несколько результатов в строке.
Если вы хотите получить по горизонтали несколько искомых значений на основе нескольких критериев, то есть когда результаты размещаются в одной строке, то используйте следующий общий шаблон:
=ЕСЛИОШИБКА(ИНДЕКС(диапазон_возвращаемых_значений; НАИМЕНЬШИЙ(ЕСЛИ(1=((–(искомое_значение1 = диапазон_искомых_значений1)) * (–(искомое_значение2 = диапазон_искомых_значений2))) ; СТРОКА(диапазон_возвращаемых_значений)-m;””); СТОЛБЕЦ()-n));””)
где:
- m — номер строки первой ячейки в возвращаемом диапазоне, минус 1.
- n — номер столбца первой слева ячейки, в которой записана формула, минус 1.
Для нашего примера набора данных формула выглядит следующим образом:
=ЕСЛИОШИБКА(ИНДЕКС($C$3:$C$30; НАИМЕНЬШИЙ(ЕСЛИ(1=((–($E3=$A$3:$A$30))*(–($F3=$B$3:$B$30))); СТРОКА($C$3:$C$30)-2;””); СТОЛБЕЦ()-6));””)
И результат ВПР нескольких значений по нескольким условиям может выглядеть так:
Аналогичным образом вы можете выполнять ВПР с тремя, четырьмя и более условиями.
Как это работает?
По сути, формулы для ВПР нескольких значений с несколькими условиями работают с уже знакомой логикой, объясненной в самом первом нашем примере. Единственное отличие состоит в том, что функция ЕСЛИ теперь проверяет несколько условий:
1=((–(искомое_значение1 = диапазон_искомых_значений1)) * (–(искомое_значение2 = диапазон_искомых_значений2)) * …..)
Результатом каждого сравнения (искомое_значение = диапазон_искомых_значений) является массив логических значений ИСТИНА (условие выполнено) и ЛОЖЬ (условие не выполнено). Двойное отрицание (–) переводит логические значения в единицы и нули. А поскольку умножение на ноль всегда дает ноль, в правой части этого равенства у вас будет получаться 1 только для тех элементов, которые удовлетворяют всем указанным вами условиям.
Вы просто сравниваете окончательный массив нулей и единиц с числом 1, чтобы функция СТРОКА вернула порядковые номера строк, удовлетворяющих всем условиям, в противном случае — пустоту.
Напоминание. Все формулы поиска, обсуждаемые в этой статье, являются формулами массива. Таким образом, каждая из них перебирает все элементы массива каждый раз, когда исходные данные изменяются или рабочий лист пересчитывается. На больших листах, содержащих сотни или тысячи позиций, это может значительно замедлить работу Excel.
Как вернуть несколько значений ВПР в одну ячейку
Мы продолжаем работать с набором данных, который использовали в предыдущем примере. Но на этот раз мы хотим добиться другого результата — вместо того, чтобы извлекать несколько совпадений в отдельные ячейки, мы хотим, чтобы они отображались в одной текстовой ячейке, разделенные запятой, пробелом или другим разделителем по вашему выбору.
Используем выражение, которое мы рассматривали чуть выше и которая позволяет получить несколько результатов ВПР с условиями в одной строке:
=ЕСЛИОШИБКА(ИНДЕКС($C$3:$C$30; НАИМЕНЬШИЙ(ЕСЛИ(1=((–($E3=$A$3:$A$30))*(–($F3=$B$3:$B$30))); СТРОКА($C$3:$C$30)-2;””); СТОЛБЕЦ()-6));””)
Внесем в нее небольшие изменения.
Чтобы обработать сразу несколько результатов, в функцию СТОЛБЕЦ добавим аргумент – диапазон ячеек, в который мы ранее копировали формулу. То есть, вместо СТОЛБЕЦ() у нас теперь будет СТОЛБЕЦ(G3:K3). Это позволит формуле массива получить сразу несколько номеров столбцов.
Затем применим крайне полезную при работе с текстовыми значениями функцию ОБЪЕДИНИТЬ (доступна в Excel 2019 и более поздних версиях). Она позволит нам объединить несколько текстовых значений, отделив их друг от друга выбранным нами разделителем. К примеру, запятой с пробелом после нее.
Вот что у нас получится:
=ОБЪЕДИНИТЬ(“, “; ИСТИНА; ЕСЛИОШИБКА(ИНДЕКС($C$3:$C$30; НАИМЕНЬШИЙ(ЕСЛИ(1=((–($E3=$A$3:$A$30))*(–($F3=$B$3:$B$30))); СТРОКА($C$3:$C$30)-2;””); СТОЛБЕЦ(G3:K3)-6));””))
И видим результат ВПР нескольких значений в одной ячейке на этом скриншоте:
Еще один, более простой вариант, чтобы подтянуть несколько значений и вывести результат в одной ячейке:
=ОБЪЕДИНИТЬ(“, “;ИСТИНА;ЕСЛИ(($A$3:$A$30=E3)*($B$3:$B$30=F3)=1;$C$3:$C$30;””))
Ее также нужно вводить как формулу массива:
Как найти несколько значений без дубликатов
А если так случится, что в результатах поиска будет несколько одинаковых значений? Пример таких данных вы можете видеть на скриншоте ниже. Естественно, выводить в ячейке несколько дубликатов было бы не совсем хорошо.
Если вы хотите получить в одной ячейке результаты поиска нескольких значений без повторов, попробуйте так:
=ОБЪЕДИНИТЬ(“, “; ИСТИНА; ЕСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ($C$3:$C$13; ЕСЛИ(E3=$A$3:$A$13; $C$3:$C$13; “”); 0);””)=ПОИСКПОЗ(СТРОКА($C$3:$C$13); СТРОКА($C$3:$C$13)); $C$3:$C$13; “”))
Вставьте это выражение в нужную ячейку, не забыв завершить ввод комбинацией Ctrl+Shift+Enter, так как это формула массива. Затем можете скопировать вниз по столбцу, чтобы получить данные по другим критериям выбора.
Как видите, мы получили в одной ячейке несколько значений, среди которых нет одинаковых, хотя в исходных данных таковые имеются.
Надеюсь, эти примеры будут вам полезны для поиска сразу нескольких значений в Excel. Благодарю вас за чтение.
Функция ЕСЛИОШИБКА – примеры формул – В статье описано, как использовать функцию ЕСЛИОШИБКА в Excel для обнаружения ошибок и замены их пустой ячейкой, другим значением или определённым сообщением. Покажем примеры, как использовать функцию ЕСЛИОШИБКА с функциями визуального… 
5 способов – поиск значения в массиве Excel – В статье предлагается несколько различных формул для выполнения поиска в двумерном массиве значений Excel. Просмотрите эти варианты и выберите наиболее для вас подходящий. При поиске данных в электронных таблицах Excel… Поиск ИНДЕКС ПОИСКПОЗ по нескольким условиям – В статье показано, как выполнять быстрый поиск с несколькими условиями в Excel с помощью ИНДЕКС и ПОИСКПОЗ. Хотя Microsoft Excel предоставляет специальные функции для вертикального и горизонтального поиска, опытные пользователи… ИНДЕКС ПОИСКПОЗ как лучшая альтернатива ВПР – В этом руководстве показано, как использовать ИНДЕКС и ПОИСКПОЗ в Excel и чем они лучше ВПР. В нескольких недавних статьях мы приложили немало усилий, чтобы объяснить основы функции ВПР новичкам и предоставить… 
Поиск в массиве при помощи ПОИСКПОЗ – В этой статье объясняется с примерами формул, как использовать функцию ПОИСКПОЗ в Excel. Также вы узнаете, как улучшить формулы поиска, создав динамическую формулу с функциями ВПР и ПОИСКПОЗ. В Microsoft… 
Функция ИНДЕКС в Excel — 6 примеров использования – В этом руководстве вы найдете ряд примеров формул, демонстрирующих наиболее эффективное использование ИНДЕКС в Excel. Из всех функций Excel, возможности которых часто недооцениваются и используются недостаточно, ИНДЕКС определенно занимает место… 
Как объединить две или несколько таблиц в Excel – В этом руководстве вы найдете некоторые приемы объединения таблиц Excel путем сопоставления данных в одном или нескольких столбцах. Как часто при анализе в Excel вся необходимая информация собирается на одном… 
Вычисление номера столбца для извлечения данных в ВПР – Задача: Наиболее простым способом научиться указывать тот столбец, из которого функция ВПР будет извлекать данные. При этом мы не будем изменять саму формулу, поскольку это может привести в случайным ошибкам.…
Поиск и подстановка по нескольким условиям
Постановка задачи
Если вы продвинутый пользователь Microsoft Excel, то должны быть знакомы с функцией поиска и подстановки ВПР или VLOOKUP (если еще нет, то сначала почитайте эту статью, чтобы им стать). Для тех, кто понимает, рекламировать ее не нужно 🙂 – без нее не обходится ни один сложный расчет в Excel. Есть, однако, одна проблема: эта функция умеет искать данные только по совпадению одного параметра. А если у нас их несколько?
Предположим, что у нас есть база данных по ценам товаров за разные месяцы:
Нужно найти и вытащить цену заданного товара (Нектарин) в определенном месяце (Январь), т.е. получить на выходе152, но автоматически, т.е. с помощью формулы. ВПР в чистом виде тут не поможет, но есть несколько других способов решить эту задачу.
Способ 1. Дополнительный столбец с ключом поиска
Это самый очевидный и простой (хотя и не самый удобный) способ. Поскольку штатная функция ВПР (VLOOKUP) умеет искать только по одному столбцу, а не по нескольким, то нам нужно из нескольких сделать один!
Добавим рядом с нашей таблицей еще один столбец, где склеим название товара и месяц в единое целое с помощью оператора сцепки (&), чтобы получить уникальный столбец-ключ для поиска:
Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и 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 и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).
Ссылки по теме
- Как искать и подставлять данные с помощью функции ВПР (VLOOKUP)
- Что такое формулы массива и как их использовать
- Как использовать связку функций ИНДЕКС и ПОИСКПОЗ вместо ВПР
- Как извлечь сразу все значения, а не только первое с помощью ВПР
ВПР, как и ГПР являются одними из самых полезных функций в Excel. На моём канале вы можете найти статьи про каждую из них( кликай – ВПР, ГПР).
Однако бывают ситуации, когда поиск требуется произвести по двум и более условиям. Стандартный принцип работы уже не подойдет, и в этой статье я покажу три варианта решения такой нестандартной задачи.
Заранее скажу, что если вы используйте Microsoft Office старее 2019, то можете столкнуться с крайней медленной обработкой ваших формул. Все будет зависеть от:
- Мощности вашего ПК.
- Количества ячеек, которое просматривают нижеприведенные формулы.
- Количества ячеек, в которых прописана данная формула.
Лишь в 2019 офисе(и естественно в офисе 365) исправили алгоритмы работы, благодаря чему можно использовать все формулы массивов без каких-либо ограничений.
Способ Первый – через ЕСЛИ внутри ВПР
=ВПР(D1;ЕСЛИ(B6:B20=D2;A6:C20;””);3;0)
Суть способа – заставить формулу ЕСЛИ(кликни для перехода на видео по формуле) проверять определенные столбцы на соответствие заданному признаку и в случае совпадения становится вторым аргументом в формуле ВПР.
Если у вас старая версия офиса(старее 2019), то для корректной работы формулы потребуется нажать на горячие клавиши CTRL+SHIFT+ENTER одновременно для заключения формулы в массив. В этом случае формула будет дополнительно заключена в скобки {}.
У такого способа есть только один недостаток – он удобен только в случае, когда критериев поиска два. Если критериев больше то потребуется городить большое количество формул ЕСЛИ внутри ЕСЛИ. Переходим ко второму способу.
Способ Второй – ВПР по нескольким критериям с помощью связи ИНДЕКС+ПОИСКПОЗ
На моём канале я уже рассказывал(кликни для перехода на статью) о такой связке, как ИНДЕКС+ПОИСКПОЗ. По моему субъективному мнению она лучше всего подходит для нахождения ячейки по нескольким критериями, если итог поиска не является числом.
=ИНДЕКС(D8:D22;ПОИСКПОЗ(1;(A8:A22=E1)*(B8:B22=E2)*(C8:C22=E3);0))
Если потребуется добавить критериев – просто добавляем еще одну скобку, в которую вписываем столбец в котором будем искать и ячейку с критерием поиска).
И ещё раз напоминаю – если старая версия Office – нужно нажать на горячие клавиши CTRL+SHIFT+ENTER одновременно для заключения формулы в массив {}.
Способ Третий – СУММПРОИЗВ
Я не просто так упомянул в предыдущем способе о том, что он лучший если итог поиска не число а например текст. Если же мы ищем число то, самой рациональной формулой будет СУММПРОИЗ(Сумма произведения).
=СУММПРОИЗВ((A8:A22=$E$1)*(B8:B22=$E$2)*($C$8:$C$22=E3)*(D8:D22))
При применении данной формулы не потребуется нажимать CTRL+SHIFT+ENTER.
Надеюсь моя статья была вам полезна. Спасибо за прочтение.
Совет: Попробуйте использовать новые функции ПРОСМОТРX и XMATCH, а также улучшенные версии функций, описанные в этой статье. Эти новые функции работают в любом направлении и возвращают точные совпадения по умолчанию, что упрощает и упрощает работу с ними по сравнению с предшественниками.
Предположим, у вас есть список номеров офисов, и вам нужно знать, какие сотрудники работают в каждом из них. Таблица очень угрюмая, поэтому, возможно, вам кажется, что это сложная задача. С функцией подытов на самом деле это довольно просто.
Функции ВВ., а также ИНДЕКС и ВЫБОРПОЗ — одни из самых полезных функций в Excel.
Примечание: Мастер подметок больше не доступен в Excel.
Ниже в качестве примера по выбору вы можете найти пример использования в этой области.
=ВПР(B2;C2:E7,3,ИСТИНА)
В этом примере B2 является первым аргументом —элементом данных, который требуется для работы функции. В случае СРОТ ВЛ.В.ОВ этот первый аргумент является искомой значением. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, таким как “кузьмина” или 21 000. Вторым аргументом является диапазон ячеек C2–:E7, в котором нужно найти и найти значение. Третий аргумент — это столбец в диапазоне ячеек, содержащий ищите значение.
Четвертый аргумент необязателен. Введите истина или ЛОЖЬ. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести ЛОЖЬ, функция будет соответствовать значению, заведомо первому аргументу. Другими словами, если оставить четвертый аргумент пустым или ввести ИСТИНА, это обеспечивает большую гибкость.
В этом примере показано, как работает функция. При вводе значения в ячейку B2 (первый аргумент) в результате поиска в ячейках диапазона C2:E7 (2-й аргумент) выполняется поиск в ней и возвращается ближайшее приблизительное совпадение из третьего столбца в диапазоне — столбца E (третий аргумент).
Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. Иначе потребуется ввести одно из значений в столбец C или D, чтобы получить какой-либо результат.
Если вы хорошо разучились работать с функцией ВГТ.В.В., то в равной степени использовать ее будет легко. Вы вводите те же аргументы, но выполняется поиск в строках, а не в столбцах.
Использование индекса и MATCH вместо ВРОТ
При использовании функции ВПРАВО существует ряд ограничений, которые действуют только при использовании функции ВПРАВО. Это означает, что столбец, содержащий и look up, всегда должен быть расположен слева от столбца, содержащего возвращаемого значения. Теперь, если ваша таблица не построена таким образом, не используйте В ПРОСМОТР. Используйте вместо этого сочетание функций ИНДЕКС и MATCH.
В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения “Воронеж” в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.
Дополнительные примеры использования индексов и MATCH вместо В ПРОСМОТР см. в статье билла Https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Билла Джилена (Bill Jelen), MVP корпорации Майкрософт.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функциями подытовки, прежде чем попробовать их с собственными данными, вот примеры данных.
Пример работы с ВЛОКОНПОМ
Скопируйте следующие данные в пустую таблицу.
Совет: Прежде чем врезать данные в Excel, установите для столбцов A–C ширину в 250 пикселей и нажмите кнопку “Перенос текста” (вкладка “Главная”, группа “Выравнивание”).
|
Плотность |
Вязкость |
Температура |
|
0,457 |
3,55 |
500 |
|
0,525 |
3,25 |
400 |
|
0,606 |
2,93 |
300 |
|
0,675 |
2,75 |
250 |
|
0,746 |
2,57 |
200 |
|
0,835 |
2,38 |
150 |
|
0,946 |
2,17 |
100 |
|
1,09 |
1,95 |
50 |
|
1,29 |
1,71 |
0 |
|
Формула |
Описание |
Результат |
|
=ВПР(1,A2:C10,2) |
Используя приблизительное соответствие, функция ищет в столбце A значение 1, находит наибольшее значение, которое меньше или равняется 1 и составляет 0,946, а затем возвращает значение из столбца B в той же строке. |
2,17 |
|
=ВПР(1,A2:C10,3,ИСТИНА) |
Используя приблизительное соответствие, функция ищет в столбце A значение 1, находит наибольшее значение, которое меньше или равняется 1 и составляет 0,946, а затем возвращает значение из столбца C в той же строке. |
100 |
|
=ВПР(0,7,A2:C10,3,ЛОЖЬ) |
Используя точное соответствие, функция ищет в столбце A значение 0,7. Поскольку точного соответствия нет, возвращается сообщение об ошибке. |
#Н/Д |
|
=ВПР(0,1,A2:C10,2,ИСТИНА) |
Используя приблизительное соответствие, функция ищет в столбце A значение 0,1. Поскольку 0,1 меньше наименьшего значения в столбце A, возвращается сообщение об ошибке. |
#Н/Д |
|
=ВПР(2,A2:C10,2,ИСТИНА) |
Используя приблизительное соответствие, функция ищет в столбце A значение 2, находит наибольшее значение, которое меньше или равняется 2 и составляет 1,29, а затем возвращает значение из столбца B в той же строке. |
1,71 |
Пример ГВ.Г.В.В.
Скопируйте всю таблицу и вставьте ее в ячейку A1 пустого листа Excel.
Совет: Прежде чем врезать данные в Excel, установите для столбцов A–C ширину в 250 пикселей и нажмите кнопку “Перенос текста” (вкладка “Главная”, группа “Выравнивание”).
|
Оси |
Подшипники |
Болты |
|
4 |
4 |
9 |
|
5 |
7 |
10 |
|
6 |
8 |
11 |
|
Формула |
Описание |
Результат |
|
=ГПР(“Оси”;A1:C4;2;ИСТИНА) |
Поиск слова “Оси” в строке 1 и возврат значения из строки 2, находящейся в том же столбце (столбец A). |
4 |
|
=ГПР(“Подшипники”;A1:C4;3;ЛОЖЬ) |
Поиск слова “Подшипники” в строке 1 и возврат значения из строки 3, находящейся в том же столбце (столбец B). |
7 |
|
=ГПР(“П”;A1:C4;3;ИСТИНА) |
Поиск буквы “П” в строке 1 и возврат значения из строки 3, находящейся в том же столбце. Так как “П” найти не удалось, возвращается ближайшее из меньших значений: “Оси” (в столбце A). |
5 |
|
=ГПР(“Болты”;A1:C4;4) |
Поиск слова “Болты” в строке 1 и возврат значения из строки 4, находящейся в том же столбце (столбец C). |
11 |
|
=ГПР(3;{1;2;3:”a”;”b”;”c”;”d”;”e”;”f”};2;ИСТИНА) |
Поиск числа 3 в трех строках константы массива и возврат значения из строки 2 того же (в данном случае — третьего) столбца. Константа массива содержит три строки значений, разделенных точкой с запятой (;). Так как “c” было найдено в строке 2 того же столбца, что и 3, возвращается “c”. |
c |
Примеры индекса и match
В последнем примере функции ИНДЕКС и MATCH совместно возвращают номер счета с наиболее ранней датой и соответствующую дату для каждого из пяти городов. Так как дата возвращается как число, для ее формата используется функция ТЕКСТ. Функция ИНДЕКС использует результат, возвращенный функцией ПОИСКПОЗ, как аргумент. Сочетание функций ИНДЕКС и ПОИСКПОЗ используется в каждой формуле дважды — сперва для возврата номера счета, а затем для возврата даты.
Скопируйте всю таблицу и вставьте ее в ячейку A1 пустого листа Excel.
Совет: Перед тем как вировать данные в Excel, установите для столбцов A–D ширину в 250 пикселей и нажмите кнопку “Перенос текста” (вкладка “Главная”, группа “Выравнивание”).
|
Счет |
Город |
Дата выставления счета |
Счет с самой ранней датой по городу, с датой |
|
3115 |
Казань |
07.04.12 |
=”Казань = “&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(“Казань”,$B$2:$B$33,0),1)& “, Дата выставления счета: ” & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(“Казань”,$B$2:$B$33,0),3),”m/d/yy”) |
|
3137 |
Казань |
09.04.12 |
=”Орел = “&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(“Орел”,$B$2:$B$33,0),1)& “, Дата выставления счета: ” & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(“Орел”,$B$2:$B$33,0),3),”m/d/yy”) |
|
3154 |
Казань |
11.04.12 |
=”Челябинск = “&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(“Челябинск”,$B$2:$B$33,0),1)& “, Дата выставления счета: ” & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(“Челябинск”,$B$2:$B$33,0),3),”m/d/yy”) |
|
3191 |
Казань |
21.04.12 |
=”Нижний Новгород = “&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(“Нижний Новгород”,$B$2:$B$33,0),1)& “, Дата выставления счета: ” & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(“Нижний Новгород”,$B$2:$B$33,0),3),”m/d/yy”) |
|
3293 |
Казань |
25.04.12 |
=”Москва = “&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(“Москва”,$B$2:$B$33,0),1)& “, Дата выставления счета: ” & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(“Москва”,$B$2:$B$33,0),3),”m/d/yy”) |
|
3331 |
Казань |
27.04.12 |
|
|
3350 |
Казань |
28.04.12 |
|
|
3390 |
Казань |
01.05.12 |
|
|
3441 |
Казань |
02.05.12 |
|
|
3517 |
Казань |
08.05.12 |
|
|
3124 |
Орел |
09.04.12 |
|
|
3155 |
Орел |
11.04.12 |
|
|
3177 |
Орел |
19.04.12 |
|
|
3357 |
Орел |
28.04.12 |
|
|
3492 |
Орел |
06.05.12 |
|
|
3316 |
Челябинск |
25.04.12 |
|
|
3346 |
Челябинск |
28.04.12 |
|
|
3372 |
Челябинск |
01.05.12 |
|
|
3414 |
Челябинск |
01.05.12 |
|
|
3451 |
Челябинск |
02.05.12 |
|
|
3467 |
Челябинск |
02.05.12 |
|
|
3474 |
Челябинск |
04.05.12 |
|
|
3490 |
Челябинск |
05.05.12 |
|
|
3503 |
Челябинск |
08.05.12 |
|
|
3151 |
Нижний Новгород |
09.04.12 |
|
|
3438 |
Нижний Новгород |
02.05.12 |
|
|
3471 |
Нижний Новгород |
04.05.12 |
|
|
3160 |
Москва |
18.04.12 |
|
|
3328 |
Москва |
26.04.12 |
|
|
3368 |
Москва |
29.04.12 |
|
|
3420 |
Москва |
01.05.12 |
|
|
3501 |
Москва |
06.05.12 |
Во второй части нашего учебника по функции ВПР (VLOOKUP) в Excel мы разберём несколько примеров, которые помогут Вам направить всю мощь ВПР
на решение наиболее амбициозных задач Excel. Примеры подразумевают, что Вы уже имеете базовые знания о том, как работает эта функция. Если нет, возможно, Вам будет интересно начать с первой части этого учебника, в которой объясняются синтаксис и основное применение ВПР. Что ж, давайте приступим.
Поиск в Excel по нескольким критериям
Функция ВПР в Excel – это действительно мощный инструмент для выполнения поиска определённого значения в базе данных. Однако, есть существенное ограничение – её синтаксис позволяет искать только одно значение. Как же быть, если требуется выполнить поиск по нескольким условиям? Решение Вы найдёте далее.
Пример 1: Поиск по 2-м разным критериям
Предположим, у нас есть список заказов и мы хотим найти Количество товара (Qty.), основываясь на двух критериях – Имя клиента (Customer) и Название продукта (Product). Дело усложняется тем, что каждый из покупателей заказывал несколько видов товаров, как это видно из таблицы ниже:
Обычная функция ВПР не будет работать по такому сценарию, поскольку она возвратит первое найденное значение, соответствующее заданному искомому значению. Например, если Вы хотите узнать количество товара Sweets, заказанное покупателем Jeremy Hill, запишите вот такую формулу:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE) =ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
Есть простой обходной путь – создать дополнительный столбец, в котором объединить все нужные критерии. В нашем примере это столбцы Имя клиента (Customer) и Название продукта (Product). Не забывайте, что объединенный столбец должен быть всегда крайним левым в диапазоне поиска, поскольку именно левый столбец функция ВПР просматривает при поиске значения.
Итак, Вы добавляете вспомогательный столбец в таблицу и копируете по всем его ячейкам формулу вида: =B2&C2. Если хочется, чтобы строка была более читаемой, можно разделить объединенные значения пробелом: =B2&» «&C2. После этого можно использовать следующую формулу:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE) =ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
=VLOOKUP(B1,$A$7:$D$18,4,FALSE) =ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
Пример 2: ВПР по двум критериям с просматриваемой таблицей на другом листе
Если Вам необходимо обновить основную таблицу (Main table), добавив данные из второй таблицы (Lookup table), которая находится на другом листе или в другой рабочей книге Excel, то Вы можете собрать искомое значение непосредственно в формуле, которую вставляете в основную таблицу.
Как и в предыдущем примере, Вам понадобится в таблице поиска (Lookup table) вспомогательный столбец с объединенными значениями. Этот столбец должен быть крайним левым в заданном для поиска диапазоне.
Итак, формула с ВПР может быть такой:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE) =ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Чтобы сделать формулу более читаемой, Вы можете задать имя для просматриваемого диапазона, и тогда формула станет выглядеть гораздо проще:
Чтобы формула работала, значения в крайнем левом столбце просматриваемой таблицы должны быть объединены точно так же, как и в критерии поиска. На рисунке выше мы объединили значения и поставили между ними пробел, точно так же необходимо сделать в первом аргументе функции (B2&» «&C2).
Соглашусь, добавление вспомогательного столбца – не самое изящное и не всегда приемлемое решение. Вы можете сделать то же самое без вспомогательного столбца, но в таком случае потребуется гораздо более сложная формула с комбинацией функций INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ).
Извлекаем 2-е, 3-е и т.д. значения, используя ВПР
Вы уже знаете, что ВПР может возвратить только одно совпадающее значение, точнее – первое найденное. Но как быть, если в просматриваемом массиве это значение повторяется несколько раз, и Вы хотите извлечь 2-е или 3-е из них? А что если все значения? Задачка кажется замысловатой, но решение существует!
Предположим, в одном столбце таблицы записаны имена клиентов (Customer Name), а в другом – товары (Product), которые они купили. Попробуем найти 2-й, 3-й и 4-й товары, купленные заданным клиентом.
Простейший способ – добавить вспомогательный столбец перед столбцом Customer Name и заполнить его именами клиентов с номером повторения каждого имени, например, John Doe1, John Doe2 и т.д. Фокус с нумерацией сделаем при помощи функции COUNTIF (СЧЁТЕСЛИ), учитывая, что имена клиентов находятся в столбце B:
=B2&COUNTIF($B$2:B2,B2) =B2&СЧЁТЕСЛИ($B$2:B2;B2)
После этого Вы можете использовать обычную функцию ВПР, чтобы найти нужный заказ. Например:
- Находим 2-й товар, заказанный покупателем Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Находим 3-й товар, заказанный покупателем Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
На самом деле, Вы можете ввести ссылку на ячейку в качестве искомого значения вместо текста, как представлено на следующем рисунке:
Если Вы ищите только 2-е повторение, то можете сделать это без вспомогательного столбца, создав более сложную формулу:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"") =ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
- $F$2 – ячейка, содержащая имя покупателя (она неизменна, обратите внимание – ссылка абсолютная);
- $B$ – столбец Customer Name;
- Table4 – Ваша таблица (на этом месте также может быть обычный диапазон);
- $C16 – конечная ячейка Вашей таблицы или диапазона.
Эта формула находит только второе совпадающее значение. Если же Вам необходимо извлечь остальные повторения, воспользуйтесь предыдущим решением.
Если Вам нужен список всех совпадений – функция ВПР тут не помощник, поскольку она возвращает только одно значение за раз – и точка. Но в Excel есть функция INDEX (ИНДЕКС), которая с легкостью справится с этой задачей. Как будет выглядеть такая формула, Вы узнаете в следующем примере.
Извлекаем все повторения искомого значения
Как упоминалось выше, ВПР не может извлечь все повторяющиеся значения из просматриваемого диапазона. Чтобы сделать это, Вам потребуется чуть более сложная формула, составленная из нескольких функций Excel, таких как INDEX (ИНДЕКС), SMALL (НАИМЕНЬШИЙ) и ROW (СТРОКА)
Например, формула, представленная ниже, находит все повторения значения из ячейки F2 в диапазоне B2:B16 и возвращает результат из тех же строк в столбце C.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")} {=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Если Вам интересно понять, как она работает, давайте немного погрузимся в детали формулы:
Часть 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"") ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
Результатом функции IF (ЕСЛИ) окажется вот такой горизонтальный массив: {1,"",3,"",5,"","","","","","",12,"","",""}
Часть 2:
ROW()-3 СТРОКА()-3
Часть 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)) НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Часть 4:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)) ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Часть 5:
IFERROR() ЕСЛИОШИБКА()
Двумерный поиск по известным строке и столбцу
Выполнение двумерного поиска в Excel подразумевает поиск значения по известному номеру строки и столбца. Другими словами, Вы извлекаете значение ячейки на пересечении конкретной строки и столбца.
Итак, давайте обратимся к нашей таблице и запишем формулу с функцией ВПР, которая найдет информацию о стоимости проданных в марте лимонов.
Существует несколько способов выполнить двумерный поиск. Познакомьтесь с возможными вариантами и выберите наиболее подходящий.
Функции ВПР и ПОИСКПОЗ
Вы можете использовать связку из функций ВПР (VLOOKUP) и ПОИСКПОЗ (MATCH), чтобы найти значение на пересечении полей Название продукта (строка) и Месяц (столбец) рассматриваемого массива:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE) =ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
MATCH("Mar",$A$1:$I$1,0) ПОИСКПОЗ("Mar";$A$1:$I$1;0)
- Ищем символы «Mar» – аргумент lookup_value (искомое_значение);
- Ищем в ячейках от A1 до I1 – аргумент lookup_array (просматриваемый_массив);
- Возвращаем точное совпадение – аргумент match_type (тип_сопоставления).
Использовав 0 в третьем аргументе, Вы говорите функции ПОИСКПОЗ искать первое значение, в точности совпадающее с искомым значением. Это равносильно значению FALSE (ЛОЖЬ) для четвёртого аргумента ВПР.
Вот так Вы можете создать формулу для поиска по двум критериям в Excel, что также известно, как двумерный поиск или поиск в двух направлениях.
Функция СУММПРОИЗВ
Функция СУММПРОИЗВ (SUMPRODUCT) возвращает сумму произведений выбранных массивов:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9) =СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
В следующей статье я буду объяснять эти функции во всех деталях, так что сейчас можете просто скопировать эту формулу:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0)) =ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Если Вы не в восторге от всех этих сложных формул Excel, Вам может понравиться вот такой наглядный и запоминающийся способ:
- Выделите таблицу, откройте вкладку Formulas (Формулы) и нажмите Create from Selection (Создать из выделенного).
- Отметьте галочками Top row (в строке выше) и Left column (в столбце слева). Microsoft Excel назначит имена диапазонам из значений в верхней строке и левом столбце Вашей таблицы. Теперь Вы можете осуществлять поиск, используя эти имена, напрямую, без создания формул.
- В любой пустой ячейке запишите =имя_строки имя_столбца, например, так:
=Lemons Mar … или наоборот: =Mar Lemons
Помните, что имена строки и столбца нужно разделить пробелом, который в данном случае работает как оператор пересечения.
При вводе имени, Microsoft Excel будет показывать подсказку со списком подходящих имен, так же, как при вводе формулы.
- Нажмите Enter и проверьте результат
В целом, какой бы из представленных выше методов Вы ни выбрали, результат двумерного поиска будет одним и тем же:
Используем несколько ВПР в одной формуле
Бывает так, что основная таблица и таблица поиска не имеют ни одного общего столбца, и это мешает использовать обычную функцию ВПР. Однако, существует ещё одна таблица, которая не содержит интересующую нас информацию, но имеет общий столбец с основной таблицей и таблицей поиска.
Давайте разберем следующий пример. У нас есть основная таблица (Main table) со столбцом SKU (new), куда необходимо добавить столбец с соответствующими ценами из другой таблицы. Кроме этого, у нас есть 2 таблицы поиска. Первая (Lookup table 1) содержит обновленные номера SKU (new) и названия товаров, а вторая (Lookup table 2) – названия товаров и старые номера SKU (old).
Чтобы добавить цены из второй таблицы поиска в основную таблицу, необходимо выполнить действие, известное как двойной ВПР или вложенный ВПР.
- Запишите функцию ВПР, которая находит имя товара в таблице Lookup table 1, используя SKU, как искомое значение:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Здесь New_SKU – именованный диапазон $A:$B в таблице Lookup table 1, а 2 – это столбец B, который содержит названия товаров (смотрите на рисунке выше)
- Запишите формулу для вставки цен из таблицы Lookup table 2 на основе известных названий товаров. Для этого вставьте созданную ранее формулу в качестве искомого значения для новой функции ВПР:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Здесь Price – именованный диапазон $A:$C в таблице Lookup table 2, а 3 – это столбец C, содержащий цены.
На рисунке ниже виден результат, возвращаемый созданной нами формулой:
Динамическая подстановка данных из разных таблиц при помощи ВПР и ДВССЫЛ
В начале разъясним, что мы подразумеваем под выражением «Динамическая подстановка данных из разных таблиц», чтобы убедиться правильно ли мы понимает друг друга.
Бывают ситуации, когда есть несколько листов с данными одного формата, и необходимо извлечь нужную информацию с определенного листа в зависимости от значения, которое введено в заданную ячейку. Думаю, проще это объяснить на примере.
Представьте, что имеются отчеты по продажам для нескольких регионов с одинаковыми товарами и в одинаковом формате. Требуется найти показатели продаж для определенного региона:
Если у Вас всего два таких отчета, то можно использовать до безобразия простую формулу с функциями ВПР и ЕСЛИ (IF), чтобы выбрать нужный отчет для поиска:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE) =ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
- $D$2 – это ячейка, содержащая название товара. Обратите внимание, здесь мы используем абсолютные ссылки, чтобы избежать изменения искомого значения при копировании формулы в другие ячейки.
- $D3 – это ячейка с названием региона. Используем абсолютную ссылку для столбца и относительную ссылку для строки, поскольку планируем копировать формулу в другие ячейки того же столбца.
- FL_Sales и CA_Sales – названия таблиц (или именованных диапазонов), в которых содержаться соответствующие отчеты о продажах. Вы, конечно же, можете использовать обычные названия листов и ссылки на диапазоны ячеек, например ‘FL Sheet’!$A$3:$B$10, но именованные диапазоны гораздо удобнее.
Однако, когда таких таблиц много, функция ЕСЛИ – это не лучшее решение. Вместо нее можно использовать функцию ДВССЫЛ (INDIRECT), чтобы возвратить нужный диапазон поиска.
Как Вы, вероятно, знаете, функция ДВССЫЛ используется для того, чтобы вернуть ссылку, заданную текстовой строкой, а это как раз то, что нам сейчас нужно. Итак, смело заменяем в представленной выше формуле выражение с функцией ЕСЛИ на ссылку с функцией ДВССЫЛ. Вот такая комбинация ВПР и ДВССЫЛ отлично работает в паре:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE) =ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
- $D$2 – это ячейка с названием товара, она неизменна благодаря абсолютной ссылке.
- $D3 – это ячейка, содержащая первую часть названия региона. В нашем примере это FL.
- _Sales – общая часть названия всех именованных диапазонов или таблиц. Соединенная со значением в ячейке D3, она образует полное имя требуемого диапазона. Ниже приведены некоторые подробности для тех, кто не имеет опыта работы с функцией ДВССЫЛ.
Как работают ДВССЫЛ и ВПР
Во-первых, позвольте напомнить синтаксис функции ДВССЫЛ (INDIRECT):
INDIRECT(ref_text,[a1]) ДВССЫЛ(ссылка_на_текст;[a1])
- A1, если аргумент равен TRUE (ИСТИНА) или не указан;
- R1C1, если FALSE (ЛОЖЬ).
В нашем случае ссылка имеет стиль A1, поэтому можно не указывать второй аргумент и сосредоточиться на первом.
Итак, давайте вернемся к нашим отчетам по продажам. Если Вы помните, то каждый отчёт – это отдельная таблица, расположенная на отдельном листе. Чтобы формула работала верно, Вы должны дать названия своим таблицам (или диапазонам), причем все названия должны иметь общую часть. Например, так: CA_Sales, FL_Sales, TX_Sales и так далее. Как видите, во всех именах присутствует «_Sales».
Функция ДВССЫЛ соединяет значение в столбце D и текстовую строку «_Sales», тем самым сообщая ВПР в какой таблице искать. Если в ячейке D3 находится значение «FL», формула выполнит поиск в таблице FL_Sales, если «CA» – в таблице CA_Sales и так далее.
Результат работы функций ВПР и ДВССЫЛ будет следующий:
Если данные расположены в разных книгах Excel, то необходимо добавить имя книги перед именованным диапазоном, например:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE) =ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Урок подготовлен для Вас командой сайта office-guru.ru Источник: https://www.ablebits.com/office-addins-blog/2014/07/29/vlookup-formula-examples/ Перевел: Андрей Антонов Правила перепечатки Еще больше уроков по Microsoft Excel
