Excel поиск значения по нескольким условиям в excel: Excel поиск значения по нескольким условиям в excel

Excel. Подсчет и суммирование ячеек, отвечающих критерию условного форматирования

Ранее я описал, как с помощью пользовательской функции найти сумму значений в ячейках, выделенных цветом. К сожалению, эта функция не работает, если ячейки раскрашены с помощью условного форматирования. Я обещал «доработать» функцию. Но за два года, прошедшие с публикации той заметки, я не смог ни самостоятельно, ни с помощью информации из Интернета написать удобоваримый код… (Дополнение от 29 марта 2017 г. Спустя еще пять лет, код мне всё же удалось написать; см. заключительную часть заметки). И вот недавно я наткнулся на идею, содержащуюся в книге Д.Холи, Р. Холи «Excel 2007. Трюки», которая позволяет обойтись вовсе без кода.

Пусть есть список чисел от 1 до 100, размещенных в диапазоне А1:А100 (рис. 1; см. также лист «СУММЕСЛИ» Excel-файла) [1]. На диапазон наложено условное форматирование, помечающее ячейки, содержащие числа больше 10 и меньше или равно 20.

Рис. 1. Диапазон чисел; условным форматированием выделены ячейки, содержащие значения от 10 до 20

Скачать заметку в формате Word, примеры в формате Excel

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

Чтобы сложить диапазон ячеек, отвечающих одному критерию, можно использовать функцию СУММЕСЛИ (рис. 2).

Рис. 2. Суммирование ячеек, отвечающих одному условию

Если у вас несколько условий, можно использовать функцию СУММЕСЛИМН (рис. 3).

Рис. 3. Суммирование ячеек, отвечающих нескольким условиям

Для подсчета числа ячеек, отвечающих одному критерию, можно использовать функцию СЧЁТЕСЛИ.

Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию СЧЁТЕСЛИМН.

В Excel предусмотрена еще одна функция, которая позволяет указать несколько условий. Эта функция входит в набор функций баз данных Excel и называется БДСУММ. Чтобы проверить ее, используйте тот же набор чисел в диапазоне А2:А100 (рис. 4; см. также лист «БДСУММ» Excel-файла).

Рис. 4. Использование функций баз данных

Выделите ячейки C1:D2 и присвойте этому диапазону имя Критерий, введя его в поле имени слева от строки формул. Теперь выделите ячейку С1 и введите =$А$1, то есть ссылку на первую ячейку на листе, содержащую имя базы данных. Введите =$А$1 в ячейку D1 и вы получите две копии заголовка столбца А. Эти копии будут использоваться как заголовки для условий БДСУММ (C1:D2), который вы назвали Критерий. В ячейке С2 введите >10. В ячейке D2 введите <=20. В ячейке, где должен быть результат, введите следующую формулу:

=БДСУММ($А$1:$А$101,1,Критерий)

Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию БСЧЁТ.

Дополнение от 29 марта 2017 г.

Читая книгу Джона Уокенбаха Excel 2010. Профессиональное программирование на VBA я узнал, что, начиная с версии Excel 2010 в VBA появилось новое свойство DisplayFormat (см. , например, Range.DisplayFormat Property). Т.е., VBA может считывать формат, отображаемый на экране. При этом не важно, как он был получен, прямыми настройками пользователя, или с помощью условного форматирования. К сожалению, разработчики MS сделали так, что свойство DisplayFormat работает только в процедурах, вызываемых из VBA, а пользовательские функции на основе этого свойства выдают ошибку #ЗНАЧ! Тем не менее, получить сумму значений в диапазоне по ячейкам определенного цвета, можно с помощью процедуры (макроса, но не функции). Откройте Excel-файл с примером (содержит код VBA). Пройдите по меню Вид -> Макросы -> Макросы; в окне Макрос, выделите строку СумЦветУсл, и нажмите Выполнить. Запустится макрос, выберите диапазон суммирования и критерий. Ответ появится в окне.

Код процедуры

Sub СумЦветУсл()
Application.Volatile True
Dim SumColor As Double
Dim i As Range
Dim UserRange As Range
Dim CriterionRange As Range
SumColor = 0
‘ Запрос диапазона
Set UserRange = Application. InputBox( _
Prompt:=»Выберите диапазон суммирования», _
Title:=»Выбор диапазона», _
Default:=ActiveCell.Address, _
Type:=8)
‘ Запрос критерия
Set CriterionRange = Application.InputBox( _
Prompt:=»Выберите критерий суммирования», _
Title:=»Выбор критерия», _
Default:=ActiveCell.Address, _
Type:=8)
‘ Суммирование «правильных» ячеек
For Each i In UserRange
If i.DisplayFormat.Interior.Color = _
CriterionRange.DisplayFormat.Interior.Color Then
SumColor = SumColor + i
End If
Next
MsgBox SumColor
End Sub

Хотя пользовательская функция и дает ошибку, но в процессе ее вызова можно «подсмотреть» ответ. В ячейке начните набирать формулу =su…

Воспользуйтесь подсказкой, кликнув на нее, а затем нажмите знак функции в строке формул:

Введите аргументы, и увидите ответ. К сожалению, нажав, ОК, получите в ячейке значение ошибки.

[1] Массив я создал с помощью функции =СЛУЧМЕЖДУ(1;100)

Индекс поискпоз с несколькими условиями

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

Выборка значений формулой ИНДЕКС и ПОИСКПОЗ с несколькими условиями

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

В результате должен получить значение ячейки из соответственной строки и столбца таблицы. Как в таблице Пифагора:

Большинству пользователей Excel хорошо известна формула из функций ИНЕДКС и ПОИСКПОЗ. В отличие от других формул, здесь используется сразу две функции ПОИСКПОЗ во втором и третьем аргументе функции ИНДЕКС. Так как поиск выполняется по двум условиям. В третьем аргументе «Номер столбца» функции ИНДЕКС нет постоянного числа (константы), а вместо него функция ПОИСКПОЗ, которая динамически изменяет значение.

Поисковая функция ПОИСКПОЗ возвращает позицию найденного значения в списке. На рисунке сейчас выбран регион «Северный», значит функция возвращает значение 3, так как этот регион находится на третьем месте в списке. Это же число на данный момент является значением второго аргумента функции ИНДЕКС. Год 2011 найден в строке заголовка таблицы. Так как это вторая позиция в списке, то функция ПОИСКПОЗ возвращает число 2 – для третьего аргумента. Функция ИНДЕКС на основе чисел 3 и 2 возвращаемых через функцию ПОИСКПОЗ возвращает соответственное значение указанным критериям выборки пользователем.

Альтернативная формула для ИНДЕКС и ПОИСКПОЗ по нескольким условиям

Можно существенно расширить возможности выше приведенной формулы. Данные по продажам можно выводить из таблицы сразу по нескольким условиям выборки значений. Так, чтобы пользователь мог указать 1 вариант из четырех условий:

1. Указаны регион и год (как в предыдущем примере).
2. Указывать только регион.
3. Указывать только год.
4. Вообще ничего не указывать ни одного критерия выборки.

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

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

Общая структура модифицированной формулы такая же, как и в предыдущем примере. Изменено только несколько деталей. Диапазон, определенный функцией ИНДЕКС, теперь охватывает и строку листа №9 и столбец F. Так же модифицированы обе функции ПОИСКПОЗ и дополнительно расположены в аргументах функций ЕСЛИОШИБКА. Эта же функция в формуле позволяет возвращать общую сумму чисел по строкам или по столбцам благодаря охвату итоговых значений в строке B9:F9 и в столбце F3:F9.

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

На рисунке изображена та же таблица, но пользователь не указал критерий выборки «Год» в ячейке B12. Так как заголовки строк и столбцов не содержат пустых ячеек старая формула возвращает ошибку с кодом #Н/Д! В тоже время в новой измененной формуле контроль над ситуацией принимает функция ЕСЛИОШИБКА и возвращает значение из своего второго аргумента «Значение если ошибка». Таким способом в функцию ИНДЕКС просто передается номер последнего столбца. Если же не будет указан регион, а год будет указан функция ИНДЕКС будет принимать от функции ЕСЛИОШИБКА номер последней строки в исходной таблице и отображать содержимое ячейки F7 с итоговой суммой.

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

В этой статье описаны наиболее распространенные причины появления ошибки «#N/A» в результате использования функций индекси ПОИСКПОЗ .

Примечание: Если вы хотите, чтобы функция index или Match возвращала осмысленное значение вместо #N/A, используйте функцию ЕСЛИОШИБКА , а затем вложите в нее функции индекс и ПОИСКПОЗ . Замена #N/A на свое собственное значение только идентифицирует ошибку, но не ее разрешение. Таким образом, прежде чем использовать ЕСЛИОШИБКА, убедитесь, что формула правильно работает, как вы планируете.

Проблема: Нет соответствий

Если функция ПОИСКПОЗ не находит искомое значение в массиве подстановок, она возвращает ошибку #N/a.

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

Ячейка содержит непредвиденные символы или скрытые пробелы.

К ячейке применен неправильный формат данных. Например, ячейка содержит числовое значение, но отформатирована как текстовая.

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

Вы использовали формулу массива, но не нажали клавиши CTRL+SHIFT+ВВОД

При использовании массива в указателе , совпаденииили сочетании этих двух функций необходимо нажать клавиши CTRL + SHIFT + ВВОД на клавиатуре. Формула будет автоматически заключена в фигурные скобки <>. Если вы попытаетесь ввести их вручную, Excel отобразит формулу как текст.

Примечание: Если у вас установлена текущая версия Office 365, вы можете просто ввести формулу в выходную ячейку, а затем нажать клавишу Ввод , чтобы подтвердить формулу как формулу динамических массивов. В противном случае необходимо ввести формулу в качестве устаревшей формулы массива, сначала выбрав диапазон вывода, введя формулу в выходную ячейку, а затем нажав клавиши CTRL + SHIFT + ВВОД , чтобы подтвердить его. В начале и конце формулы Excel вставляет фигурные скобки. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

Проблема: Несоответствие типа сопоставления и порядка сортировки данных

Если вы используете Match, должно существовать соответствие между значением аргумента тип_сопоставления и порядком сортировки значений в массиве подстановки. Если синтаксис отклоняется от приведенных ниже правил, возникает ошибка #Н/Д.

Если тип_сопоставления равен 1 или не указан, значения в аргументе просматриваемый_массив должны находиться в возрастающем порядке. Примеры: -2, -1, 0, 1, 2…; А, Б, В…; ЛОЖЬ, ИСТИНА и т. д.

Если тип_сопоставления равен-1, значения в аргументе просматриваемый_массив должны находиться в порядке убывания.

В приведенном ниже примере функция ПОИСКПОЗ

Аргумент тип_сопоставления в синтаксисе имеет значение-1, что означает, что порядок значений в ячейках B2: B10 должен быть в порядке убывания, чтобы формула работала. Но значения находятся в возрастающем порядке, и это приводит к ошибке #N/A.

РЕШЕНИЯ Либо измените аргумент тип_сопоставления на 1, либо отсортируйте таблицу в убывающем формате. Затем повторите попытку.

У вас есть вопрос об определенной функции?

Помогите нам улучшить Excel

У вас есть предложения по улучшению следующей версии Excel? Если да, ознакомьтесь с темами на портале пользовательских предложений для Excel.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Способ 3.

Формула массива

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

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

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

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

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

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

Excel ИНДЕКС ПОИСКПОЗ с несколькими критериями

В этом руководстве показано, как выполнять поиск по нескольким критериям в Excel с использованием ИНДЕКС и ПОИСКПОЗ, а также несколькими другими способами.

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

• ИНДЕКС СОВПАДЕНИЕ с несколькими критериями
• INDEX MATCH без массива с двумя или более критериями
• INDEX MATCH несколько критериев в строках и столбцах (матричный поиск)

Excel ИНДЕКС ПОИСКПОЗ с несколькими критериями

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

Чтобы найти значение на основе нескольких критериев в отдельных столбцах, используйте следующую общую формулу:

{=ИНДЕКС( return_range , MATCH(1, ( критерии1 = диапазон1 ) * ( условия2 = диапазон2 ) * (…), 0))}

Где:

• Диапазон_возврата — это диапазон, из которого возвращается значение.
• Критерии1 , критерии2 , … условия, которые необходимо выполнить.
• Диапазон1 , range2 , … — это диапазоны, на которых должны проверяться соответствующие критерии.

Важное примечание! Это формула массива, и она должна быть завершена с помощью Ctrl + Shift + Enter. Это заключит вашу формулу в {фигурные скобки}, что является визуальным признаком формулы массива в Excel. Не пытайтесь вводить фигурные скобки вручную, это не сработает!

Формула представляет собой расширенную версию легендарного ПОИСКПОЗ ИНДЕКС, которая возвращает совпадение на основе одного критерия. Чтобы оценить несколько критериев, мы используем операцию умножения, которая работает как оператор И в формулах массива. Ниже вы найдете реальный пример и подробное объяснение логики.

INDEX MATCH с несколькими критериями — пример формулы

В этом примере мы будем использовать таблицу в так называемом формате «плоского файла» с каждой отдельной комбинацией критериев (регион-месяц-элемент в нашем случае) на своем собственный ряд. Наша цель — получить данные о продажах определенного товара в определенном регионе и месяце.

С исходными данными и критериями в следующих ячейках:

• Диапазон_возврата (продажи) — D2:D13
• Критерий1 (целевой регион) — G1
• Критерии2 (целевой месяц) — G2
• Критерии3 (целевой элемент) — G3
• Диапазон1 (регионы) — A2:A13
• Диапазон2 (месяцы) — B2:B13
• Диапазон3 (шт.) — C2:C13

Формула имеет следующий вид:

=ИНДЕКС(D2:D13, ПОИСКПОЗ(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

Введите формулу, скажем в G4, завершите ее, нажав Ctrl+Shift+Enter и получите следующий результат:

Как работает эта формула

Самая сложная часть — это функция ПОИСКПОЗ, поэтому давайте сначала разберемся с ней:

ПОИСКПОЗ(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2 :C13), 0))

Как вы помните, ПОИСКПОЗ(искомое_значение, искомый_массив, [тип_совпадения]) ищет искомое значение в искомом массиве и возвращает относительное положение этого значения в массиве.

В нашей формуле аргументы следующие:

• Lookup_value : 1
• Lookup_array : (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13)
• Match_type : 0

Аргумент 1 ^st предельно ясен — функция ищет число 1. Аргумент 3 ^rd , установленный в 0, означает «точное совпадение», т. е. формула возвращает первое найденное значение, которое точно равно значение поиска.

Вопрос — почему мы ищем «1»? Чтобы получить ответ, давайте подробнее рассмотрим массив поиска, где мы сравниваем каждый критерий с соответствующим диапазоном: целевой регион в G1 со всеми регионами (A2:A13), целевой месяц в G2 со всеми месяцами (B2:B13). ) и целевой элемент в G3 против всех элементов (C2:C13). Промежуточный результат — это 3 массива ИСТИНА и ЛОЖЬ, где ИСТИНА представляет значения, соответствующие проверенному условию. Чтобы визуализировать это, вы можете выбрать отдельные выражения в формуле и нажать клавишу F9.ключ, чтобы увидеть, что оценивает каждое выражение:

Операция умножения преобразует значения TRUE и FALSE в 1 и 0 соответственно:

{1;1;1;1;1;1;0;0;0;0;0;0} * {0; 0;1;1;0;0;0;0;1;1;0;0} * {1;0;1;0;1;0;1;0;1;0;1;0}

А поскольку умножение на 0 всегда дает 0, результирующий массив содержит единицы только в строках, соответствующих всем критериям:

{0;0;1;0;0;0;0;0;0;0;0 ;0}

Приведенный выше массив переходит в lookup_array аргумент ПОИСКПОЗ. При lookup_value , равном 1, функция возвращает относительное положение строки, для которой все критерии ИСТИННЫ (строка 3 в нашем случае). Если в массиве несколько единиц, возвращается позиция первой.

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

=ИНДЕКС(D2:D13, 3)

115 долларов, что составляет 3 ^rd значение в массиве D2:D13.

Формула ИНДЕКС ПОИСКПОЗ без массива с несколькими критериями

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

= ИНДЕКС (D2: D13, ПОИСКПОЗ (1, ИНДЕКС ((G1 = A2: A13) * (G2 = B2: B13) * (G3 = C2: C13), 0, 1), 0))

Как работает эта формула

Поскольку функция ИНДЕКС может обрабатывать массивы изначально, мы добавляем еще один ИНДЕКС для обработки массива 1 и 0, созданного путем умножения двух или более массивов ИСТИНА/ЛОЖЬ. Второй ИНДЕКС настроен на 0 row_num аргумент для формулы, возвращающей весь массив столбцов, а не одно значение. Так как в любом случае это массив из одного столбца, мы можем безопасно поставить 1 вместо column_num :

INDEX({0;0;1;0;0;0;0;0;0;0;0;0}, 0, 1) возвращает {0;0;1;0;0;0;0;0;0;0;0;0}

Этот массив передается функции ПОИСКПОЗ:

ПОИСКПОЗ(1, { 0;0;1;0;0;0;0;0;0;0;0;0}, 0)

ПОИСКПОЗ находит номер строки, для которого все критерии ИСТИНА (точнее, относительное положение этой строки в указанном массиве) и передает это число в row_num аргумент первого ИНДЕКС:

=ИНДЕКС(D2:D13, 3)

ПОИСКПОЗ ИНДЕКС с несколькими критериями в строках и столбцах

В этом примере показано, как выполнить поиск путем проверки двух или более критериев в строках и столбцы. На самом деле это более сложный случай так называемого «матричного поиска» или «двустороннего поиска» с более чем одной строкой заголовка.

Вот общая формула ПОИСКПОЗ ИНДЕКС с несколькими критериями в строках и столбцах:

{=ИНДЕКС( table_array , MATCH( vlookup_value , lookup_column , 0), MATCH( hlookup_value1 & hlookup_value2 , lookup_row1 & lookup_row2 , 0))}

Where:

Table_array — the карта или область для поиска, т. е. все значения данных, кроме заголовков столбцов и строк.

Vlookup_value — значение, которое вы ищете по вертикали в столбце.

Lookup_column — диапазон столбцов для поиска, обычно это заголовки строк.

Hlookup_value1, hlookup_value2, … — значения, которые вы ищете по горизонтали в строках.

Lookup_row1, lookup_row2, … — диапазоны строк для поиска, обычно заголовки столбцов.

Важное примечание! Чтобы формула работала корректно, ее необходимо ввести как формулу массива с помощью Ctrl+Shift+Enter.

Это разновидность классической формулы двустороннего поиска, которая ищет значение на пересечении определенной строки и столбца. Разница в том, что вы объединяете несколько значений и диапазонов hlookup для оценки нескольких заголовков столбцов. Чтобы лучше понять логику, рассмотрим следующий пример.

Поиск по матрице с несколькими критериями — пример формулы

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

• Table_array — B3:E4
• Vlookup_value (целевой элемент) — h2
• Lookup_column (заголовки строк: элементы) — A3:A4
• Hlookup_value1 (целевой регион) — h3
• Hlookup_value2 (целевой поставщик) — h4
• Lookup_row1 (заголовки столбцов 1: регионы) — B1:E1
• Lookup_row2 (заголовки столбцов 2: поставщики) — B2:E2

Теперь введите аргументы в общую формулу, описанную выше, и вы получите следующий результат:

=ИНДЕКС(B3:E5, ПОИСКПОЗ(h2,A3:A5,0), ПОИСКПОЗ(h3&h4,B1:E1&B2) :E2,0))

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

Как работает эта формула

Так как мы ищем по вертикали и по горизонтали, нам нужно указать номера строк и столбцов для функции ИНДЕКС(массив, номер_строки, номер_столбца).

Row_num доставляется функцией MATCH(h2, A3:A5, 0), которая сравнивает целевой элемент (яблоки) в h2 с заголовками строк в A3:A5. Это дает результат 1, потому что «Яблоки» — это первый элемент в указанном диапазоне.

Column_num вычисляется путем объединения 2 значений поиска и 2 массивов поиска: MATCH(h3&h4, B1:E1&B2:E2, 0))

Ключевым фактором успеха является то, что значения поиска должны точно соответствовать заголовкам столбцов и быть объединены в том же порядке. Чтобы визуализировать это, выберите первые два аргумента в формуле ПОИСКПОЗ, нажмите F9, и вы увидите, чему соответствует каждый аргумент:

ПОИСКПОЗ("Северный поставщик 2", {"Северный поставщик 1", "Северный поставщик 2", "Южный поставщик 1", "SouthVendor 2"}, 0)

Поскольку «NorthVendor 2» является вторым элементом в массиве, функция возвращает 2.

В этот момент наша длинная двумерная формула ПОИСКПОЗ ИНДЕКС преобразуется в эту простую один:

=ИНДЕКС(B3:E5, 1, 2)

И возвращает значение на пересечении 1-й строки и 2-го столбца в диапазоне B3:E5, которое является значением в ячейке C3.

Вот как можно найти несколько критериев в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Практическая рабочая тетрадь для загрузки

Excel INDEX MATCH несколько критериев (файл .xlsx)

Как найти несколько условий в Excel

Vlookup несколько совпадений в Excel с одним или несколькими критериями

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

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

Vlookup для нескольких значений может быть выполнен с помощью комбинированного использования нескольких функций. Если вы далеки от эксперта в Excel, не спешите покидать эту страницу. Я постараюсь объяснить лежащую в основе логику, чтобы даже новичок мог понять формулы и настроить их для решения подобных задач. Более того, я покажу вам еще одно возможное решение, которое требует всего нескольких щелчков мыши и вообще не требует знания формул Excel!

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

Как выполнить множественный ВПР в Excel с помощью формулы

Как упоминалось в начале этого руководства, невозможно заставить Excel ВПР возвращать несколько значений. Задачу можно решить, используя следующие функции в формуле массива:

• ЕСЛИ — оценивает условие и возвращает одно значение, если условие выполняется, и другое значение, если условие не выполняется.
• SMALL — получает k-е наименьшее значение в массиве.
• ИНДЕКС — возвращает элемент массива на основе указанных вами номеров строк и столбцов.
• ROW — возвращает номер строки.
• COLUMN — возвращает номер столбца.
• ЕСЛИОШИБКА — перехватывает ошибки.

Ниже вы найдете несколько примеров таких формул.

Формула 1. Поиск нескольких совпадений и возврат результатов в столбце

Допустим, у вас есть имена продавцов в столбце A и товары, которые они продали, в столбце B, столбец A содержит несколько вхождений каждого продавца. Ваша цель — получить список всех товаров, проданных данным человеком. Для этого выполните следующие действия:

1. Введите список уникальных имен в какую-нибудь пустую строку того же или другого рабочего листа. В этом примере имена вводятся в ячейки D2:G2:9.0392

   Совет. Чтобы быстро получить все разные имена в списке, вы можете использовать формулу для извлечения различных значений в Excel.

2. Под именем выберите количество пустых ячеек, равное или превышающее максимальное количество возможных совпадений, введите одну из следующих формул массива в строке формул и нажмите 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,""), ROW()-2)),"")

   Как видите, формула 1 ^st немного компактнее, но формула 2 ^nd более универсальна и требует меньше модификаций (подробнее о синтаксисе и логике мы поговорим чуть дальше).

3. Скопируйте формулу в другие столбцы. Для этого выберите диапазон ячеек, в который вы только что ввели формулу, и перетащите маркер заполнения (небольшой квадрат в правом нижнем углу выбранного диапазона) вправо.

Результат будет примерно таким:

Как работает эта формула

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

1. Функция ЕСЛИ

   В основе формулы используется функция ЕСЛИ, чтобы получить позиции всех вхождений искомого значения в диапазоне поиска: ЕСЛИ(D$2=$A$3:$A$13, ROW($B$3:$B $13)-2,»»)

   ЕСЛИ сравнивает искомое значение (D2) с каждым значением в диапазоне поиска (A3:A13) и, если совпадение найдено, возвращает относительную позицию строки; пустая строка («») в противном случае.

   Относительное расположение строк вычисляется путем вычитания 2 из ROW($B$3:$B$13), чтобы первая строка имела позицию 1. Если ваш диапазон возврата начинается со строки 2, вычтите 1 и так далее. Результатом этой операции является массив {1;2;3;4;5;6;7;8;9;10;11}, который переходит к аргументу value_if_true функции ЕСЛИ.

   Вместо приведенного выше вычисления можно использовать следующее выражение: СТРОКА(столбец_просмотра)- MIN(СТРОКА(столбец_просмотра))+1, которое возвращает тот же результат, но не требует каких-либо изменений независимо от местоположения возвращаемого столбца. В этом примере это будет ROW($A$3:$A$13)-MIN(ROW($A$3:$A$13))+1.

   Итак, на данный момент у вас есть массив, состоящий из чисел (позиций совпадений) и пустых строк (несовпадений). Для ячейки D3 в этом примере у нас есть следующий массив:

   Если вы сверитесь с исходными данными, вы увидите, что «Адам» (значение поиска в D2) появляется на позициях 3 ^rd , 8 ^th и 10 ^th в диапазоне поиска (A3:A13).

2. Функция МАЛЕНЬКИЙ
   Затем вступает в действие функция МАЛЕНЬКИЙ(массив, k), чтобы определить, какие совпадения должны быть возвращены в конкретной ячейке.

   С уже установленным массивом давайте выработаем аргумент k , т. е. k-е наименьшее возвращаемое значение. Для этого вы делаете своего рода «инкрементный счетчик» ROW()-n, где «n» — это номер строки первой ячейки формулы минус 1. В этом примере мы ввели формулу в ячейки D3: D7, поэтому ROW()-2 возвращает «1» для ячейки D3 (строка 3 минус 2), «2» для ячейки D4 (строка 4 минус 2) и т. д.

   В результате функция НАИМЕНЬШИЙ извлекает 1 ^st наименьший элемент массива в ячейке D3, 2-й наименьший элемент в ячейке D4 и так далее. И это превращает первоначальную длинную и сложную формулу в очень простую, например:

   .

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

3. Функция ИНДЕКС

   Эта часть проста. Вы используете функцию ИНДЕКС, чтобы вернуть значение элемента массива на основе его номера строки.

4. ЕСЛИ ОШИБКА функция

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

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

Объединяя все это вместе, мы получаем следующие общие формулы для просмотра нескольких значений в Excel:0003 return_range , SMALL(IF( lookup_value = lookup_range , ROW( return_range )- m ,»»), ROW() — n )),»») 6 2

5 9 :

ЕСЛИОШИБКА(ИНДЕКС( диапазон_возврата , МАЛЕНЬКИЙ(ЕСЛИ( искомое_значение = диапазон_искомого_изображения , СТРОКА(диапазон_искомого_переменного_) — МИН(СТРОКА(диапазон_исправления()»)-«», СТРОКА+) n )),»»)

Где:

• m — номер строки первой ячейки в возвращаемом диапазоне минус 1.
• n — номер строки первой ячейки формулы минус 1.

Примечание. В приведенном выше примере как n , так и m равны «2», потому что наш диапазон возврата и диапазон формулы начинаются в строке 3. В ваших таблицах это могут быть разные числа.

Формула 2. Поиск нескольких совпадений и возврат результатов в строке

Если вы хотите вернуть несколько значений в строках, а не в столбцах, измените приведенные выше формулы следующим образом:

=ЕСЛИОШИБКА(ИНДЕКС($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)- MIN(СТРОКА($A$3:$A$13))+1,""),COLUMN()-4)), "")

Как и в предыдущем примере, обе являются формулами массива, поэтому не забудьте нажать сочетание клавиш Ctrl + Shift + Enter, чтобы завершить их правильно.

Формулы работают по той же логике, что и в предыдущем примере, за исключением того, что вы используете функцию COLUM вместо ROW, чтобы определить, какое совпадающее значение должно быть возвращено в конкретной ячейке: COLUMN()-n. Где n — номер столбца первой ячейки, в которую вводится формула, минус 1. В этом примере формула вводится в ячейки E2:h3. С E, являющимся 5 ^{-й столбец} , n равен «4» (5-1=4).

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

Подводя итог, вот общие формулы для Vlookup с несколькими результатами, возвращаемыми в строках:

Формула 1 :

ЕСЛИОШИБКА(ИНДЕКС( return_range , SMALL(IF( lookup_value 9004 , ROWUP90 lookup_value

04 =

4 return_range ) — m , «»), COLUMN() — n )), «»)

Формула 2 :

ЕСЛИОШИБКА(ИНДЕКС( return_range , SMALL(IF( return_range , SMALL(IF( return_range) , SMALL(IF( return_range , SMALL(IF( return_range , SMALL) диапазон_просмотра , СТРОКА( диапазон_просмотра ) — МИН(СТРОКА( диапазон_просмотра ))+1,»»),СТОЛБЦ() — n )), «»)

Где:

м

39 номер строки первой ячейки в возвращаемом диапазоне минус 1.



• n — номер столбца первой ячейки формулы минус 1.

Формула 3. Виртуальный поиск нескольких совпадений на основе нескольких условий

Вы уже знаете, как выполнять визуальный поиск нескольких значений в Excel на основе одного условия. Но что, если вы хотите вернуть несколько совпадений на основе двух или более критериев? Продолжая предыдущие примеры, что, если у вас есть дополнительный столбец Month , и вы хотите получить список всех товаров, проданных данным продавцом в определенном месяце?

Если вы знакомы с формулами массивов, то, возможно, помните, что они позволяют использовать звездочку (*) в качестве оператора И. Таким образом, вы можете просто взять формулы, рассмотренные в двух предыдущих примерах, и заставить их проверять несколько условий, как показано ниже.

Возвращает несколько совпадений в столбце

ЕСЛИОШИБКА(ИНДЕКС( return_range , SMALL(IF(1=((—( искомое_значение1=искомое_диапазон1 )) * ( —( искомое_значение2 = искомое_диапазон2 )) ), ROW( return_range )- m ,»»), ROW()- n )),»»)

Где:

• m — номер строки первой ячейки в диапазон возврата минус 1.
• n — номер строки первой ячейки формулы минус 1.

Предположим, что список продавцов ( lookup_range1 ) находится в A3:A30, список месяцев ( lookup_range2 ) находится в B3:B30, интересующий продавец ( lookup_value1 ) находится в ячейке E3, а интересующий месяц ( lookup_value2 ) находится в ячейке F3, формула принимает следующий вид:

=ЕСЛИОШИБКА(ИНДЕКС($C$3:$C$30, МАЛЕНЬКИЙ(ЕСЛИ(1=((--($E$3=$A $3:$A$30)) * (--($F$3=$B$3:$B$30))), ROW($C$3:$C$30)-2,""), ROW()-2) ),"")

Этот макет может быть полезен для создания информационной панели, например. ваши пользователи могут ввести имя в E3, месяц в F3 и получить список продуктов в столбце G:

Вернуть несколько результатов в строке

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

ЕСЛИОШИБКА(ИНДЕКС( диапазон_возврата , МАЛЕНЬКИЙ(ЕСЛИ(1 = ((—( искомое_значение1=искомое_диапазон1 )) * (—( искомое_значение2 = искомое_диапазон2 ))), ROW( return_range ) — м , «»), COLUMN() — 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, чтобы функция СТРОКА вернула количество строк, удовлетворяющих всем условиям, в противном случае — пустую строку.

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

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

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

Сразу скажу — я не знаю формулы для дубликатов ВПР, которая выводит несколько совпадений в одной ячейке. Тем не менее, я знаю способ сделать это без формул (читай «без стресса»:) с помощью двух надстроек, включенных в наш Ultimate Suite for Excel. Если у вас его еще нет в Excel, вы можете скачать бесплатную 14-дневную пробную версию здесь, а затем выполнить шаги, описанные ниже.

Исходные данные и ожидаемый результат

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

Вытяните строки с несколькими совпадениями в основную таблицу

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

1. Выберите основную таблицу или щелкните любую ячейку в ней, а затем нажмите кнопку Объединить две таблицы на ленте:
2. Надстройка достаточно умна, чтобы идентифицировать и выбрать всю таблицу, поэтому просто нажмите Далее :
   

   Подсказка. При первом использовании инструмента имеет смысл выбрать поле Создать резервную копию рабочего листа на случай, если что-то пойдет не так.

3. Выберите таблицу поиска и нажмите Далее .
4. Выберите одну или несколько совпадающих пар столбцов, которые необходимо сравнить в основной таблице и таблице поиска (в данном примере это столбцы Продавец и Месяц ), а затем нажмите Далее .
5. Выберите столбцы, из которых вы хотите получить совпадающие значения ( Product в этом примере), и нажмите Next .
6. Сообщите надстройке, как именно вы хотите расположить несколько совпадений в основной таблице. Для этого примера нам нужна следующая опция: Вставить строки с повторяющимися совпадающими значениями после строки с таким же значением . Убедитесь, что не выбран другой вариант, и нажмите Готово .

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

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

Объединение повторяющихся строк в одну строку

Чтобы объединить «повторяющиеся строки» в одну строку, мы воспользуемся другим инструментом — Мастером объединения строк.

1. Выберите таблицу, созданную с помощью инструмента «Объединить таблицы» (см. снимок экрана выше) или любую ячейку в таблице, и нажмите кнопку Объединить строки на ленте:
2. Убедитесь, что надстройка правильно воспроизвела таблицу, и нажмите Далее :
3. Выберите ключевой столбец или столбцы ( Продавец и Месяц в этом примере) и нажмите Далее :
4. Выберите столбцы, которые содержат несколько совпадений ( Product в этом примере), выберите нужный разделитель (точка с запятой, запятая, пробел или разрыв строки) и нажмите Finish .