Поиск в excel по нескольким условиям в excel: Примеры функций ИНДЕКС и ПОИСКПОЗ по нескольким критериям Excel

Содержание

Excel выделение цветом ячеек по условиям, Эксель условное форматирование

Как сделать «красиво в Excel»? Основные уловки

Ищем пропажу. В Excel пропали листы или лента, панель команд?

Нужно выделить повторяющиеся значения в столбце? Надо выбрать первые 5 максимальных ячеек? Необходимо сделать термальную шкалу для наглядности (цвет меняется в зависимости от увеличения/уменьшения значения ячеек)? В Excel выделение цветом ячеек по условиям можно сделать очень быстро и просто. За выделение цветом ячеек отвечает специальная функция «Условное форматирование». Настоятельно рекомендую! Подробнее читаем дальше:

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

Содержание

  • Условное форматирование, где найти?
  • Excel выделение цветом ячеек по условиям. Простые условия
  • Выделение повторяющихся значений, в т.ч. по нескольким столбцам
  • Выделение цветом первых/последних значений. Опять же условное форматирование
  • Построение термальной диаграммы и гистограммы
  • Выделение цветом ячеек, содержащих определенный текст
  • Excel выделение цветом. Фильтр по цвету
  • Проверка условий форматирования
  • Похожие статьи

Условное форматирование, где найти?

Для начала, на ленте задач в главном меню найдите раздел Стили и нажмите на кнопку Условное форматирование.

При нажатии откроется меню, с разными вариантами этого редактирования. Как вы видите, возможностей здесь действительно много.

Теперь подробнее о самых полезных:

Excel выделение цветом ячеек по условиям. Простые условия

Для этого зайдите в пункт Правила выделения ячеек. Если к примеру, вам нужно выделить все ячейки больше 100, нажмите кнопку Больше. В окне:

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

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

Чтобы выделить все повторяющиеся значения выберите соответствующее меню Повторяющиеся значения.

Далее снова появиться окошко с форматированием. Настройте как вам удобно. Можно выделить, например, только уникальные. Значения и курсивом (пользовательский формат)

Что делать если необходимо найти повторения по двум и более столбцам, например когда ФИО в разных столбцах? Сделайте еще один столбец и объедините значения формулой =СЦЕПИТЬ(), т.е. в отдельной ячейке у вас будет написано ИвановИванИваныч. По такому столбцу вы уже легко сможете выделить повторяющиеся значения. Важно понимать, что если порядок слов будет различаться, то Excel сочтет такие строки неповторяющимися (например, ИванИванычИванов).

Выделение цветом первых/последних значений. Опять же условное форматирование

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

Построение термальной диаграммы и гистограммы

Классная функция для визуализации данных — термальная/температурная диаграмма. Суть в том, что в зависимости от величины значения в столбце или строке, ячейка подсвечивается определенным оттенком цвета, чем больше, тем краснее, например. Таблицы воспринимаются гораздо лучше на глаз, а принимать решение становится проще. Ведь один из лучших анализаторов зачастую — это наш глаз, соответственно, мозг, а не машина!

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

Рекомендую. Для презентаций и аналитики — гистограммы в ячейках и термальные диаграммы основа простой визуализации при помощи Excel.

Выделение цветом ячеек, содержащих определенный текст

Очень часто нужно найти ячейки, которые содержат определенный набор символов, можно конечно воспользоваться функцией = ПОИСК(), но проще и быстрее применить условное форматирование, пройдите — Правила отбора ячеек — Текст содержит

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

Excel выделение цветом. Фильтр по цвету

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

Подробнее о фильтрах в этой статье.

Проверка условий форматирования

Чтобы проверить какие условные форматирования у Вас заданы, пройдите Главная — Условное форматирование — Управление правилами. Здесь вы сможете отредактировать уже заданные условия, диапазон применения, а также выбрать приоритет заданного форматирования (кто выше, тот главнее, изменить можно кнопками — стрелками).

Неверный диапазон условного форматирования

Важно! Условное форматирование при неправильном использовании зачастую является причиной сильных тормозов Excel. Происходит задвоение форматирований, для примера если вы много раз копируете ячейки с выделением цветом. Тогда у вас появится множество условий с цветом. Я сам видел более 3 тысяч условий — тормозил файл безобразно. Также файл может тормозить, когда задан диапазон как на картинке выше, лучше, указывать A:A — для всего диапазона.

Подробнее о тормозах Excel и их причинах читайте здесь. Эта статья помогла не одной сотне людей 😉

Надеюсь был полезен, не прощаюсь!

Как сделать «красиво в Excel»? Основные уловки

Ищем пропажу. В Excel пропали листы или лента, панель команд?

Посчитать количество ячеек если строка соблюдает внутренние критерии в Excel статья

=СУММПРОИЗВ(—(логическое выражение))

Для подсчета строк в таблице, которые соответствуют внутренним, рассчитанным критериям, без использования вспомогательного столбца, вы можете использовать функцию СУММПРОИЗВ.

Контекст

Представьте, что у вас есть таблица показателей продаж для нескольких продуктов. У вас есть столбцы для продажи в прошлом месяце и столбец для продаж в текущем месяце. Вы хотите считать продукты (строки), где текущие продажи меньше, чем продажи в прошлом месяце. Вы не можете использовать СЧЁТЕСЛИМН для этого, потому что СЧЁТЕСЛИМН работает только с парой диапазонов критериев. Одним из вариантов: добавить вспомогательный столбец, который вычитает продажи в прошлом месяце от продаж в этом месяце, а затем использовать СЧЁТЕСЛИ для подсчета отрицательных результатов. Но что, если вы не хотите (или не можете) добавить вспомогательный столбец? В этом случае, вы можете использовать СУММПРОИЗВ.

В показанном примере, формула в ячейке G5 является:

=СУММПРОИЗВ(—(C5:C10<D5:D10))

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

В этом случае мы просто сравним значения в столбце С со значениями в столбце D, используя логическое выражение:

C5:C10<D5:D10

Поскольку мы имеем дело с диапазонами (массивами), результат представляет собой массив значений ИСТИНА, ЛОЖЬ:

{ЛОЖЬ, ИСТИНА; ЛОЖЬ, ИСТИНА; ЛОЖЬ; ЛОЖЬ}

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

—(C5:C10<D5:D10)

Который переведет массив в формат 1 и 0:

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

Который затем обрабатывается СУММПРОИЗВ. Поскольку существует только один массив, СУММПРОИЗВ просто суммирует элементы массива и возвращает число.

Содержание

  1. Количество, если строка соответствует нескольким внутренним критериям
  2. Количество ячеек, если совпадают два критерия.
  3. Количество нескольких критериев с НЕ логикой
  4. Количество строк, соответствующих сразу нескольким критериям

Количество, если строка соответствует нескольким внутренним критериям

=СУММПРОИЗВ((логическое1)*(логическое2))

Для подсчета строк в таблице, которые соответствуют нескольким критериям, некоторые из которых зависит от логических тестов, которые работают на уровне строк, вы можете использовать функцию СУММПРОИЗВ.

Контекст

У вас есть таблица, содержащая результаты спортивных матчей. У вас есть четыре столбца: хозяева поля, команда гостей, счет команды хозяев, счет команды гостей. Для данной команды, вы хотите, чтобы рассчитывались только матчи (строки), где команда выиграла у себя дома. Легко подсчитать матчи (строки), где командой была команда хозяев поля, но как рассчитать только выигрыши?

Функция СУММПРОИЗВ может обрабатывать операции над массивами (вспомним операции, которые имеют дело с диапазонами) изначально.

В примере, формула в ячейке Н5:

=СУММПРОИЗВ((B5:B10=G5)*(D5:D10>E5:E10))

Функция СУММПРОИЗВ запрограммирована для обработки массивов изначально. Это поведение по умолчанию заключается в умножении соответствующих элементов в одном или более массивов вместе, а затем суммируются результаты. Когда дается один массив, она рассчитывает сумму элементов в массиве.

В этом примере мы используем два логических выражения внутри одного аргумента массива. Мы могли бы поместить каждое выражение в отдельный аргумент, но тогда мы должны были бы превращать логические значения ИСТИНА, ЛОЖЬ в единицы и нули с другим оператором.

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

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

=СУММПРОИЗВ(({ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА})*({ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА}))

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

=СУММПРОИЗВ({0;1;0;0;0;1})

СУММПРОИЗВ просто суммирует элементы массива и рассчитывает сумму.

Количество ячеек, если совпадают два критерия.

=СЧЁТЕСЛИМН(диапазон1;критерий1;диапазон2;критерий2)

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

В приведенном примере мы хотим подсчитать количество заказов с цветом «синий» и количеством > 15. Формула в ячейке F5 является:

=СЧЁТЕСЛИМН(B5:B12;»синий»;C5:C12;»>15″)

Функция СЧЁТЕСЛИМН принимает несколько критериев в парах — каждая пара содержит один диапазон и соответствующий критерий для этого диапазона. Чтобы создать счет, все условия должны совпадать. Чтобы добавить дополнительные условия, просто добавьте еще одну пару диапазон / критерий.

СУММПРОИЗВ альтернатива

Вы можете также использовать функцию СУММПРОИЗВ для подсчета строк, которые соответствуют нескольким условиям. Эквивалентная формула:

= СУММПРОИЗВ ((B5:B12 = «Синий») * (C5:C12>15))

СУММПРОИЗВ является более мощным и гибким, чем СЧЁТЕСЛИМН, и он работает со всеми версиями Excel, но это не так быстро, с большими наборами данных.

Сводная таблица альтернативы

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

Количество нескольких критериев с НЕ логикой

=СУММПРОИЗВ((rng1=crit1)*ЕНД(ПОИСКПОЗ (rng2;crit2;0)))

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

В показанном примере формула в G9 является:

=СУММПРОИЗВ((C5:C12=F5)*ЕНД(ПОИСКПОЗ(D5:D12; G5:G6; 0)))

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

Первое выражение внутри СУММПРОИЗВ сравнивает значения в столбце C «Пол» со значением в F5, «Мужской»:

(Пол = F5)

Результатом является массив значений ИСТИНА, ЛОЖЬ:

{ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ}

Где ИСТИНА соответствует «Мужской».

Второе выражение внутри СУММПРОИЗВ проверяет значения в столбце D, группы со значениями в G5:G6 — «A» и «Б». Этот тест обрабатывается с ПОИСКПОЗ и ЕНД:

ЕНД(ПОИСКПОЗ (Группа; G5:G6;0))

Функция ПОИСКПОЗ используется для сопоставления каждого значения в указанном диапазоне «Группы» со значениями в G5:G6 — «A» и «Б». Если совпадение завершается успешно, ПОИСКПОЗ рассчитывает число. Если совпадение не найдено, ПОИСКПОЗ возвращает # N/A. Результатом является массив:

{1; 2; # N / A; 1; 2; # N / A; 1; 2; # N / A}

Так как значения # N/A соответствуют «не А или Б», ЕНД используется для «обратного» массива:

{ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА}

Теперь ИСТИНА соответствует «не А или Б».

Внутри СУММПРОИЗВ, оба результата массива перемножаются, который создает единый числовой массив внутри СУММПРОИЗВ:

СУММПРОИЗВ ({0; 0; 1; 0; 0; 1; 0; 0; 0})

СУММПРОИЗВ затем рассчитывает сумму, 2, представляющую «Два мужчины не в группе А или Б».

Количество строк, соответствующих сразу нескольким критериям

=СУММПРОИЗВ(—((критерий1)+(критерий2)>0))

Для подсчета строк с использованием нескольких критериев в разных колонках — логике ИЛИ — вы можете использовать функцию СУММПРОИЗВ.

В показанном примере формула в H5 является:

= СУММПРОИЗВ (- ((С5: С11 = «синий») + (D5: D11 = «собака»)> 0))

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

Функция СУММПРОИЗВ работает с массивами изначально, для первого критерия мы используем:

(C5:C11 = «синий»)

Это возвращает массив значений ИСТИНА, ЛОЖЬ:

{ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА}

Для второго критерия, мы используем:

(D5:D11 = «собака»)

Который возвращает:

{ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ}

Эти два массива затем соединяются с добавлением (+), который автоматически превращает истинные значения ИСТИНА в 1 и 0, чтобы создать массив вроде этого:

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

Мы не можем просто добавить эти значения с СУММПРОИЗВ потому, что это удвоит подсчет строк с «синий» и «собака». Таким образом, мы используем «> 0» вместе с двойным минусом (-), чтобы превратить все значения в 1 или 0:

— ({2; 0; 1; 1; 1; 0; 1}> 0)

Что превращает этот массив в:

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

СУММПРОИЗВ затем рассчитывает сумму всех элементов.

Другие логические тесты

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

= СУММПРОИЗВ (- (ЕЧИСЛО(ПОИСК ( «красный»; A1: A10)) + ЕЧИСЛО (ПОИСК( «синий»; B1: B10))> 0))

Более логические тесты

Вы также можете добавить больше, чем два теста внутри функции СУММПРОИЗВ.

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

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

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

Excel INDEX MATCH с несколькими критериями

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

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

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

Где:

  • Диапазон_возврата — это диапазон, из которого возвращается значение.
  • Критерии1 , критерии2 , … условия, которые необходимо выполнить.
  • Range1 , 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. ключ, чтобы увидеть, что оценивает каждое выражение:

Операция умножения преобразует значения ИСТИНА и ЛОЖЬ в 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, результирующий массив содержит 1 только в строках, соответствующих всем критериям:

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

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

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

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

И это дает результат $115, то есть 3 rd значение в массиве D2:D13.

Формула INDEX MATCH без массива с несколькими критериями

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

ИНДЕКС( диапазон_возврата , ПОИСКПОЗ(1, ИНДЕКС(( критерий1 = диапазон1 ) * ( критерий2 = диапазон2 ) * (..), 0, 1), 0))

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

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

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

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

.

ИНДЕКС({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_value2

4 ,

3 9 3 lookup_row2 , 0))}

Где:

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

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, и вы увидите, что оценивает каждый аргумент:

.

MATCH("Северный поставщик 2", {"Северный поставщик 1", "Северный поставщик 2", "Южный поставщик 1", "Южный поставщик 2"}, 0)

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

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

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

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

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

Практическая рабочая тетрадь для скачивания

Excel ИНДЕКС СООТВЕТСТВУЕТ нескольким критериям (файл .xlsx)

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

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

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

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

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

Это недостижимо ни с одной другой формулой поиска без вставки вспомогательных столбцов😲

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

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

Содержание