Индексный поиск позиции в excel по двум критериям из 2-х таблиц

Обновлено: 25.06.2024

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

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

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

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

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

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

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

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

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

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

Диапазон возврата (продажи) – D2:D13
Критерии 1 (целевой регион) – G1
Критерий 2 (целевой месяц) – G2
Criteria3 (целевой элемент) – 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))

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

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

Искомое_значение: 1
Искомый_массив: (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13)
Match_type: 0

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

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

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

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

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

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

И это дает результат 115 долларов США, что является третьим значением в массиве D2:D13.

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

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

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

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

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

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

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

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

INDEX MATCH с несколькими критериями в строках и столбцах

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

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

<=INDEX(table_array, MATCH(vlookup_value, lookup_column, 0), MATCH(hlookup_value1 & hlookup_value2, lookup_row1 и lookup_row2, 0))>

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

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

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

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

Искомая_строка1, искомая_строка2, … — диапазоны строк для поиска, обычно это заголовки столбцов.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Другие способы поиска с несколькими условиями в Excel

— для этого требуется добавить вспомогательный столбец слева от вашей таблицы и объединить все критерии в этом столбце. Это не очень элегантное решение, но вы можете захотеть узнать обо всех доступных вариантах. – это недавнее дополнение к семейству функций поиска и ссылок Excel (в настоящее время доступно только для подписчиков Office 365) изначально обрабатывает массивы. Это означает, что она работает как обычная формула, а не как формула массива! - объединить 5 различных функций в одну формулу, чтобы оценить несколько критериев и вернуть все совпадения либо в столбце, либо в строке. - визуальный способ ВПР в Excel с одним или несколькими критериями.

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

Небольшое примечание, чтобы поблагодарить вас за инструкции! Хотя я постепенно осваиваю классные приемы в Excel, сомневаюсь, что когда-нибудь догадался бы об этом самостоятельно. Пришлось настроить, чтобы иметь возможность перетаскивать формулу вниз по нескольким ячейкам, но удалось сопоставить данные из нескольких электронных таблиц с тысячами строк. Только что сделал =ИНДЕКС(Лист9!C:C, ПОИСКПОЗ(1, ИНДЕКС((A2=Лист9!A:A) * (B2=Лист9!B:B), 0, 1), 0)) что было страшно прялкой медленно и уверен, что есть более чистые способы сделать, но это сработало. Большое спасибо!!

Как мне написать формулу, способную проверять несколько листов, чтобы найти конкретную дату, а затем извлекать данные из конкретного листа, на котором указана дата?

Я провел поиск по многочисленным сайтам и форумам и не смог найти ничего подходящего.

Я использую Excel 2010. Извлекаемые данные — это просто числа с точностью до одной десятичной точки.

Что у меня есть.
Первый лист (последний 31 день) — это место, куда я извлекаю данные. У меня есть формула, которая обновляет даты, отображаемые каждый день, так что отдельные ячейки в столбце B показывают последние 31 день.
Второй лист (2022 г.) содержит данные за 2022 г. с разбивкой по месяцам (фактические и фиктивные данные).
Третий лист (2023 г.) содержит данные за 2023 г. с разбивкой по месяцам (фиктивные данные).
Все 3 листа имеют одинаковый формат и макет.

Что бы я хотел, чтобы формула делала, так это проверяла лист «2022» на требуемую дату и извлекала данные из этого листа, если они были найдены, но если она не находила требуемую дату, то переходила бы к «2023» лист, чтобы найти нужную дату и извлечь данные из этого листа, если они найдены.

Формулы у меня такие работают индивидуально.
=ИНДЕКС('2022'!$C$3:$C$389, ПОИСКПОЗ(B3, '2022'!$B$3:$B$389, 0))
=ИНДЕКС('2023'!$ C$3:$C$389, MATCH(B3, '2023'!$B$3:$B$389, 0))

B3 — ячейка, содержащая дату на листе "Последний 31 день".
'2022'!$B$3:$B$389 - диапазон ячеек, в котором ищется дата на листе "2022".
'2022'!$C$3:$C$389 — диапазон ячеек, из которого извлекаются данные на листе "2022".
'2023'!$B$3:$B$389 - диапазон ячеек, в котором ищется дата на листе "2023".
'2023'!$C$3:$C$389 — диапазон ячеек, из которого извлекаются данные на листе "2023".

Заранее спасибо.

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

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

Желаемый результат: 2

Текущая формула =COUNT(FILTER(SalesOrderNumber!A:A,( (Тип упаковки!C:C="Ящик")*(Клиент!E:E="Майк")*(Количество поддонов!D:D> =22) )))

SalesOrderNumber ItemNumber Тип упаковки Количество поддонов Клиент
16590 14590056 Барабан 23, Эми
17950 14590033 Ящик 23, Майк
17806 33202332 Ящик 10, Майк
18900 33202332 Ящик 11, Майк
18900 33202332 Дело 11 Майк

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

Спасибо за эту замечательную статью! Есть ли способ извлечь данные из другого столбца, если формула не смогла ничего найти в исходном столбце? т.е. если 'Список устройств'!E2:E190=0, извлекать данные из 'Списка устройств'!F2:F190 вместо этого?

=INDEX('Список устройств'!E2:E190,MATCH('Сведения о пользователе'!A2&'Сведения о пользователе'!D2,'Список устройств'!H2:H190&'Список устройств'!K2:K190,0))

Здравствуйте!
Я рекомендую прочитать это руководство: ВПР с оператором ЕСЛИ в Excel.
Надеюсь, эта информация окажется для вас полезной.

Спасибо, Александр! Это было именно то, что мне было нужно, чтобы иметь возможность выбирать данные из одного из нескольких столбцов на основе двух значений в заголовках столбцов.

С 2022 годом, команда Ablebits!

У меня есть следующая формула:

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

Что я хотел бы сделать, так это добавить вторую проверку на основе 4 букв (например, "ABCD" или "TFSF"), и по какой-то причине я не могу заставить INDEX MATCH работать правильно.

Лист DATA – это отдельный индексированный лист, который анализирует данные из третьего листа с объединенными ячейками.

Как правило, найдите первое число с пробелом в столбце D таблицы DATA, где также есть ABCD в столбце C, а затем перейдите к следующей строке.

спасибо за любую помощь в этом!

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

Большое спасибо.

Здравствуйте!
Чтобы найти частичное совпадение текста, используйте функцию ПОИСК.
Вот пример формулы:

Надеюсь, это то, что вам нужно.

Как формула работает с несколькими условиями для совпадений X и Y?

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

Привет, Алекс,
Я пытаюсь применить формулу из приведенного выше примера, но с дополнительными критериями выбора максимального значения. Например, если C5 заменить на яблоки, какая формула даст G4 как 130 долларов США

Здравствуйте!
Чтобы найти максимальное значение по нескольким критериям, используйте функцию МАКСИМС.
Если вам нужна дополнительная помощь, не стесняйтесь спрашивать.

У меня возникли проблемы с применением этого. Я пытаюсь сопоставить ряд данных с одного листа с точным набором переменных с другого листа и выбрать связанную с ним 8-ю запись — сумму заказа.

Например, я хочу найти запись, которая точно соответствует этим данным с листа 1 на листе 2, и где они точно совпадают, подобрать сумму заказа, указанную на листе 2. Это столбцы и строки. Итак, если запись, на которую я смотрю, это «Лист1! A3: G3» и я ищу эту точную запись в «Листе 2!» A1: G1000». Допустим, они совпадают в строке 15 листа 2, тогда я хочу подобрать запись в "Лист2!H15

Лист1 1 запись
продукт, вес, тип, производитель, поставщик, в наличии, цена, заказ
кабачок, 8 кг, коробка, органический, викрам, да, 30, (необходима информация)

Лист 2
товар, вес, тип, производитель, поставщик, в наличии, цена, заказ
кабачок, 8кг, коробка, органик, викрам, да, 30, 3

Поэтому я хочу получить заказ на сумму 3 из Sheet2, используя этот подход.

Я пробовал, но у меня не получается. Не могли бы вы мне помочь?

Большое спасибо,

Здравствуйте!
Вы можете использовать что-то вроде этого:

=ИНДЕКС('Лист1 (2)'!H2:H10,ПОИСКПОЗ(1,(Лист1!A2='Лист1 (2)'!A2:A10)*(Лист1!B2='Лист1 (2)'! B2:B10),0))

Эта статья оказалась очень полезной для таких новичков, как я! У меня есть вопрос, я пытаюсь создать формулу для определения кратчайшего расстояния между двумя почтовыми индексами, и у меня есть таблица, настроенная в виде матрицы. Как мне получить наименьшее число, когда таблица выглядит так, как показано ниже?
Для 21607, 21640, 21864 - мне нужна формула, чтобы определить, какое число в каждой строке является наименьшим. Заранее спасибо.

12110 87110 18034 50010
21607 272 374 100 950
21640 274 522 105 955
21864 336 487 170 999

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

Примечание. Это формула массива, которую необходимо вводить с помощью клавиш Ctrl + Shift + Enter, за исключением Excel 365.

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

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

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

Здесь мы сравниваем элемент в H5 со всеми элементами, размер в H6 со всеми размерами и цвет в H7 со всеми цветами. Первоначальный результат — это три массива результатов TRUE/FALSE, например:

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

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

После умножения у нас есть один такой массив:

который передается в функцию ПОИСКПОЗ как массив поиска со значением поиска 1:

На данный момент используется стандартная формула INDEX MATCH. Функция ПОИСКПОЗ возвращает 3 в ИНДЕКС:

и ИНДЕКС возвращает окончательный результат в размере 17 долларов США.

Визуализация массива

Описанные выше массивы сложно визуализировать. На изображении ниже показана основная идея. Столбцы B, C и D соответствуют данным в примере. Столбец F создается путем умножения трех столбцов вместе. Это массив, переданный в ПОИСКПОЗ.

Версия без массива

К этой формуле можно добавить еще один ИНДЕКС, избегая необходимости вводить формулу массива с помощью Ctrl + Shift + Enter:

Функция ИНДЕКС изначально может работать с массивами, поэтому второй ИНДЕКС добавляется только для того, чтобы "поймать" массив, созданный с помощью логической операции, и снова вернуть тот же массив в ПОИСКПОЗ. Для этого ИНДЕКС настроен с нулевым количеством строк и одним столбцом. Трюк с нулевой строкой заставляет INDEX возвращать столбец 1 из массива (который в любом случае уже является одним столбцом).

Зачем вам нужна версия без массива? Иногда люди забывают ввести формулу массива с помощью клавиш Ctrl + Shift + Enter, и формула возвращает неверный результат. Итак, формула без массива более «пуленепробиваемая». Однако компромисс представляет собой более сложную формулу.

Примечание. В Excel 365 нет необходимости вводить формулы массива особым образом.

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

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

Факт или бюджет
Доход или прибыль
Название приложения

Это становится матричным поиском, однако, в отличие от обычных случаев, у этого есть более одного заголовка (строка 20: Фактический или Бюджет, Строка 21: Доход или Прибыль).

Подход с индексированием

При использовании подхода Index Match первое, что вы идентифицируете, — это карта или область, содержащая ответ. Поскольку этот отчет должен отображать соответствующий номер из критериев, используется массив C22:F31. Функция в этот момент записывается как:

Если требуется отобразить значение, отличное от чисел, вам также потребуется включить ячейки, содержащие эти значения. Например, если вам также нужно отобразить подразделение, массив станет A22:F31 вместо C22:F31, поскольку A21:A31 содержит значения подразделения.

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

Есть три варианта аргумента типа соответствия:

1 = меньше
0 = точное соответствие
-1 = больше

Поскольку нам нужно точное совпадение, мы используем 0:

Найдите столбец, соответствующий обоим критериям, выбранным в ячейках I20 и I21. Здесь можно использовать другую функцию ПОИСКПОЗ(). В отличие от обычного случая, искомое значение получается из двух ячеек. Объедините эти критерии с помощью символа &.

Массив поиска для этого значения поиска находится по адресу C20:F21, который имеет две строки вместо одной.

Изначально вы могли подумать об использовании C20:F21 в качестве массива поиска и написать формулу так:

Однако вместо отображения значения, соответствующего критериям, возникает ошибка. Это связано с тем, что ПОИСКПОЗ() может обрабатывать только одну строку или столбец и не может обрабатывать комбинацию строк и столбцов, например C20:F21, поскольку не будет знать, в каком направлении двигаться. Если бы это была одна строка, например C20:F20 или C21:F21, все было бы нормально.

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

Способ 1. Использование вспомогательных ячеек

Поскольку функция ПОИСКПОЗ() может обрабатывать только отдельные строки и столбцы, самый простой способ решить проблему, с которой мы столкнулись ранее, — использовать вспомогательные ячейки, которые объединяют значения в строках 20 и 21 в одну строку вместо двух. Эта комбинация становится уникальным идентификатором каждого столбца.

Вы увидите, что C19 теперь содержит ActualRevenue. Перетащите эту формулу вправо до ячейки F19.

Вернитесь к последней функции ПОИСКПОЗ(), которая использовала ячейки I20 и I21 в качестве значения поиска. Вместо использования C20:F21 в качестве массива поиска теперь можно использовать новые вспомогательные ячейки C19:F19.

Эта формула отображает значение, соответствующее выбранным критериям в ячейках I20, I21 и H22.

Способ 2: использование CTRL + SHIFT + ENTER (CSE)

Есть еще один подход, исключающий использование вспомогательных клеток. Это включает создание массива для функции ПОИСКПОЗ() нажатием клавиш CTRL + SHIFT + ENTER (CSE).

ИНДЕКС()
СУММПРОИЗВ()
ВПР()
ГПР()
ОБЪЕДИНИТЬ()

Тем не менее, если щелкнуть СПП в функции ПОИСКПОЗ(), она сможет обрабатывать массивы. Напишите ту же функцию, что и выше, только вместо использования вспомогательных ячеек измените последнюю функцию ПОИСКПОЗ(), чтобы объединить ячейки C20:F20 и C21:F21, используя символ &.

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

Нажмите на ячейку I26.
Перейти к ФОРМУЛАМ
Нажмите "ОЦЕНИТЬ ФОРМУЛУ".

Нажмите ЗАКРЫТЬ, чтобы закрыть окно. Вернитесь в камеру I26. Щелкните функцию в строке формул и вместо нажатия клавиши ВВОД нажмите CTRL + SHIFT + ВВОД.

Обратите внимание, что он помещает < >в начало и конец функции и становится таким:

Теперь отображается значение, соответствующее трем выбранным критериям.

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

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

Способ 3: использование двух функций INDEX()

Третий подход не требует ни вспомогательных ячеек, ни CSE, но заменяет последнюю функцию ПОИСКПОЗ() функцией ДВССЫЛ().

Начните с большей части тех же функций, за исключением последней функции ПОИСКПОЗ():

Чтобы избежать использования CSE, нам нужно использовать функцию, которая может обрабатывать массивы. Функцию ИНДЕКС() можно использовать, поместив всю область поиска внутрь функции ИНДЕКС(). Для этого замените последнюю функцию ПОИСКПОЗ() в методе 2 на:

Важно соблюдать синтаксис функции ИНДЕКС(). Аргумент row_num является обязательным. Вместо того, чтобы оставлять формулу до C21:F21, вам нужно указать номер строки. В этом случае вы хотите, чтобы функция брала каждую строку. Есть два способа сделать это:

Используйте разделитель Excel (,) и оставьте его пустым.

Используйте 0 в качестве номера строки.

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

Это дает тот же результат, что и первые два метода. Есть и другие способы решить эту проблему, но эти три способа я считаю самыми простыми.

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

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

Содержание

1. ИНДЕКС СООТВЕТСТВУЕТ нескольким критериям

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

В формуле используются два условия: одно указано в ячейке B13, а другое — в ячейке C13. Он ищет первое условие в диапазоне ячеек от B3 до B10 и второе условие в диапазоне от C3 до C10. Если оба условия выполняются в одной и той же строке, возвращается соответствующее имя из диапазона ячеек D3:D10.

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

1.1 Объяснение формулы в ячейке D13

Шаг 1. Объединение значений поиска

Символ амперсанда объединяет оба значения, которые вы хотите найти.

и возвращает "EastA".

Шаг 2. Объединение столбцов подстановки

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

Шаг 3. Превратите формулу массива в обычную формулу

Функция ИНДЕКС позволяет преобразовать некоторые формулы массива в обычные формулы, это одна из них.

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

и возвращает 6. Значение находится на 6-й позиции в массиве.

Шаг 4. Возврат значения из той же строки

ИНДЕКС(D3:D10, ПОИСКПОЗ(B13&C13, ИНДЕКС(B3:B10&C3:C10, ), 0))

и возвращает Дженнифер в ячейку D13.

1.2 Альтернатива формуле массива

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

Чтобы ввести формулу массива, одновременно нажмите и удерживайте клавиши CTRL + SHIFT, а затем один раз нажмите Enter. Отпустите все ключи.

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

2. ИНДЕКСНОЕ ПОИСКПОЗ – частичное текстовое несколько условий Excel 365

Формула в ячейке D13 проверяет, содержит ли ячейка в B3:B10 текст, указанный в ячейке B13, и в той же строке, если соответствующая ячейка в C3:C10 содержит указанный текст в C13.

Формула возвращает значение из D3:D10, если обе ячейки находятся в одной строке и содержат заданные текстовые строки.

Формула Excel 365 в ячейке D13:

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

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

2.1 Объяснение формулы в ячейке D13

Шаг 1. Поиск части текста в диапазоне ячеек B3:B10

Функция ПОИСК возвращает номер символа, в котором находится определенный символ или текстовая строка, читаемая слева направо (без учета регистра)

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

Обратите внимание, что функция ПОИСК возвращает значение ошибки, если строка не найдена в значении ячейки.

Шаг 2. Поиск второго частичного текстового условия в диапазоне ячеек C3:C10

Шаг 3. Умножение массивов

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

ПОИСК($B$13, $B$3:$B$10)*ПОИСК($C$13, $C$3:$C$10))

Число, умноженное на значение ошибки, возвращает значение ошибки, однако число, умноженное на число, возвращает число.

Шаг 4. Проверка, является ли значение в массиве числом

Функция ЕЧИСЛО возвращает ИСТИНА или ЛОЖЬ в зависимости от содержания аргумента.

ISNUMBER(ПОИСК($B$13, $B$3:$B$10)*ПОИСК($C$13, $C$3:$C$10))

Шаг 4. Извлечение значений на основе логического массива

Функция ФИЛЬТР возвращает значения/строки на основе условия или критериев. Она доступна только подписчикам Excel 365.

ФИЛЬТР(D3:D10, ISNUMBER(ПОИСК($B$13, $B$3:$B$10)*ПОИСК($C$13, $C$3:$C$10)))

3. INDEX MATCH — частичное совпадение нескольких столбцов

Формула массива в ячейке D13 извлекает значения из столбца D, если соответствующее значение в диапазоне ячеек B3:C3 содержит указанную строку в ячейке B13.

Условие в ячейке B13 находится в ячейках B4, B6 и C8. Соответствующие значения в столбце D находятся в D4, D6 и D8.

Формула массива в ячейке D13:

=ИНДЕКС($D$3:$D$10, МАЛЕНЬКИЙ(ЕСЛИ(МНОЖЕЧИСЛО(ЧИСЛО(ПОИСК($B$13, $B$3:$C$10))*1, ТРАНСП(СТОЛБЦ($B$3:$C $10)^0)), ПОИСКПОЗ(СТРОКА($D$3:$D$10), СТРОКА($D$3:$D$10)), ""), СТРОКИ($A$1:A1)))

3.1 Объяснение формулы в ячейке D13

Шаг 1. Поиск строки в диапазоне ячеек $B$3:$C$10

Шаг 2. Определение чисел в массиве

Функция ЕЧИСЛО возвращает ИСТИНА или ЛОЖЬ в зависимости от содержания аргумента.

Функция МУМНОЖ не может вычислять логические значения, такие как ИСТИНА и ЛОЖЬ, нам нужно преобразовать их в числовые эквиваленты. TRUE – 1 и FALSE – 0 (ноль)

Шаг 3. Создайте массив, содержащий 1

Функция СТОЛБЦ возвращает номера строк на основе диапазона ячеек.

Шаг 4. Добавьте значения по столбцам

Функция МУМНОЖ вычисляет матричное произведение двух массивов, массива с тем же количеством строк, что и массив1, и столбцов, как массив2.

MMULT(ISNUMBER(ПОИСК($B$13, $B$3:$C$10))*1, ТРАНСП(СТОЛБЦ($B$3:$C$10)^0))

Шаг 5. Создайте массив от 1 до n

Функция СТРОКА позволяет создавать числа, представляющие строки на основе диапазона ячеек.

Функция ПОИСКПОЗ находит относительное положение заданной строки в массиве или диапазоне ячеек. Это создаст массив от 1 до n, где n — количество строк в диапазоне ячеек $D$3:$D$10.

и возвращает . В $D$3:$D$10 восемь строк.

Шаг 6. Замените 1 соответствующим номером строки

Функция ЕСЛИ возвращает одно значение, если логическая проверка ИСТИНА, и другое значение, если логическая проверка ложна.

ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь])

ЕСЛИ(МУМНОЖ(ЧИСЛО(ПОИСК($B$13, $B$3:$C$10))*1, ТРАНСП(СТОЛБЦ($B$3:$C$10)^0)), ПОИСКПОЗ(СТРОКА($D $3:$D$10), СТРОКА($D$3:$D$10)), "")

Шаг 7. Извлечение k-го наименьшего номера строки

Функция НАИМЕНЬШИЙ возвращает k-е наименьшее значение из группы чисел.

МАЛЕНЬКИЙ(ЕСЛИ(МНОЖЕЧИСЛО(ЧИСЛО(ПОИСК($B$13, $B$3:$C$10))*1, ТРАНСП(СТОЛБЦ($B$3:$C$10)^0)), MATCH(СТРОКА( $D$3:$D$10), СТРОКА($D$3:$D$10)), ""), СТРОКА($A$1:A1))

Шаг 8. Получение значений на основе номера строки

Функция ИНДЕКС возвращает значение из диапазона ячеек, которое вы указываете на основе номера строки и столбца.

ИНДЕКС($D$3:$D$10, МАЛЕНЬКИЙ(ЕСЛИ(МНОЖЕЧИСЛО(ЧИСЛО(ПОИСК($B$13, $B$3:$C$10))*1, ТРАНСП(СТОЛБЦ($B$3:$C$10) )^0)), ПОИСКПОЗ(СТРОКА($D$3:$D$10), СТРОКА($D$3:$D$10)), ""), СТРОКА($A$1:A1)))

и возвращает "София".

Следующая формула представляет собой формулу динамического массива Excel 365:

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

4. Получить файл Excel

Получить файл Excel

INDEX-MATCH-multiple-conditionsv2.xlsx

Еженедельный блог EMAIL

[newsletter_signup_form Я Оскар и здесь, чтобы помочь вам.

Не стесняйтесь оставлять комментарии и задавать вопросы по Excel.

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

Статьи по теме

В этой статье показано, как получить значение из набора данных на основе нескольких условий в нескольких столбцах. С.Бабу спрашивает: […]

В этой статье показано, как использовать функции ИНДЕКС и ПОИСКПОЗ для поиска и возврата нескольких результатов. Искомое значение […]

ИНДЕКС и ПОИСКПОЗ более универсальны, чем функция ВПР, с точки зрения поиска, однако она получает только первый […]

В этой статье показано, как использовать функции ИНДЕКС и ПОИСКПОЗ для сопоставления нескольких условий и возврата нескольких результатов. Excel […]

В этой статье показано, как извлекать несколько чисел на основе условия и возвращать отсортированный список от малого до […]

На рисунке выше показана формула в ячейке F3, которая позволяет вам искать значение в столбце B […]

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

Оставить ответ

Как комментировать

Как добавить формулу в комментарий
Вставьте сюда формулу.

Преобразование знаков меньше и больше
Использование символов HTML вместо знаков меньше и больше.
становится >

Как добавить код VBA в комментарий
[vb 1="vbnet" language=","]
Поместите здесь код VBA.
[/vb]

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