INDEX MATCH MATCH в Excel для двумерного поиска
В учебнике демонстрируется несколько различных формул для выполнения двумерного поиска в Excel. Просто просмотрите варианты и выберите свой любимый 🙂
При поиске чего-либо в электронных таблицах Excel большую часть времени вы будете искать вертикально в столбцах или горизонтально в строках. Но иногда вам нужно просматривать как строки, так и столбцы. Другими словами, вы стремитесь найти значение на пересечении определенной строки и столбца. Это называется матричным поиском (он же двухмерный или двусторонний поиск), и в этом руководстве показано, как это сделать четырьмя различными способами.
Формула Excel ИНДЕКС ПОИСКПОЗ ПОИСКПОЗ
Самый популярный способ выполнить двусторонний поиск в Excel — использовать ИНДЕКС ПОИСКПОЗ ПОИСКПОЗ. Это разновидность классической формулы ПОИСКПОЗ ИНДЕКС, к которой вы добавляете еще одну функцию ПОИСКПОЗ, чтобы получить номера строк и столбцов:
ИНДЕКС (data_arrayСООТВЕТСТВИЕ (vlookup_value, lookup_column_range0), ПОИСКПОЗ (значение hlookup, lookup_row_range0))
В качестве примера давайте составим формулу для извлечения популяции определенного животного в заданный год из таблицы ниже. Для начала определим все аргументы:
- Массив_данных – B2:E4 (ячейки данных, не включая заголовки строк и столбцов)
- ВПР_значение – H1 (целевое животное)
- Lookup_column_range – A2:A4 (заголовки строк: названия животных) – A3:A4
- Hlookup_value – H2 (целевой год)
- Lookup_row_range – B1:E1 (заголовки столбцов: годы)
Соедините все аргументы вместе, и вы получите следующую формулу для двустороннего поиска:
=ИНДЕКС(B2:E4, ПОИСКПОЗ(H1, A2:A4, 0), ПОИСКПОЗ(H2, B1:E1, 0))
Как работает эта формула
Хотя на первый взгляд это может показаться немного сложным, логика формулы действительно проста и понятна. Функция ИНДЕКС извлекает значение из массива данных на основе номеров строк и столбцов, а две функции ПОИСКПОЗ предоставляют эти числа:
ИНДЕКС(B2:E4, номер_строки, номер_столбца)
Здесь мы используем возможности ПОИСКПОЗ(искомое_значение, искомый_массив, [match_type]), чтобы вернуть относительное положение искомое_значение в lookup_array.
Итак, чтобы получить номер строки, мы ищем интересующее животное (H1) в заголовках строк (A2:A4):
ПОИСКПОЗ(H1, A2:A4, 0)
Чтобы получить номер столбца, мы ищем целевой год (H2) в заголовках столбцов (B1:E1):
ПОИСКПОЗ(H2, B1:E1, 0)
В обоих случаях мы ищем точное совпадение, присваивая третьему аргументу значение 0.
В этом примере первое ПОИСКПОЗ возвращает 2, потому что наше значение vlookup (белый медведь) находится в ячейке A3, которая является второй ячейкой в A2:A4. Второе ПОИСКПОЗ возвращает 3, так как значение hlookup (2000) находится в ячейке D1, которая является третьей ячейкой в B1:E1.
С учетом вышеизложенного формула сводится к:
ИНДЕКС(B2:E4, 2, 3)
И вернуть значение на пересечении 2-й строки и 3-го столбца в массиве данных B2:E4, которое является значением в ячейке D3.
Формула ВПР и ПОИСКПОЗ для двустороннего поиска
Другой способ выполнить двумерный поиск в Excel — использовать комбинацию функций ВПР и ПОИСКПОЗ:
ВПР(vlookup_value, таблица_массивСООТВЕТСТВИЕ(hlookup_value, lookup_row_range0), ЛОЖЬ)
Для нашего образца таблицы формула принимает следующий вид:
=ВПР(H1, A2:E4, ПОИСКПОЗ(H2, A1:E1, 0), ЛОЖЬ)
Где:
- Таблица_массив – A2:E4 (ячейки данных, включая заголовки строк)
- ВПР_значение – H1 (целевое животное)
- Hlookup_value – H2 (целевой год)
- Lookup_row_range – A1:E1 (заголовки столбцов: годы)
Как работает эта формула
Ядром формулы является функция ВПР, настроенная на точное совпадение (последний аргумент имеет значение ЛОЖЬ), которая ищет искомое значение (H1) в первом столбце массива таблиц (A2:E4) и возвращает значение из другого столбца. столбец в той же строке. Чтобы определить, из какого столбца вернуть значение, вы используете функцию ПОИСКПОЗ, которая также настроена на точное совпадение (последний аргумент равен 0):
ПОИСКПОЗ(H2, A1:E1, 0)
ПОИСКПОЗ ищет значение в H2 в заголовках столбцов (A1:E1) и возвращает относительное положение найденной ячейки. В нашем случае целевой год (2010) находится в E1, который является 5-м в массиве поиска. Итак, цифра 5 идет на col_index_num аргумент ВПР:
ВПР(H1, A2:E4, 5, ЛОЖЬ)
Функция ВПР берет его оттуда, находит точное совпадение с искомым значением в A2 и возвращает значение из 5-го столбца в той же строке, которая является ячейкой E2.
Важная заметка! Чтобы формула работала корректно, таблица_массив (A2:E4) функции ВПР и lookup_array ПОИСКПОЗ (A1:E1) должно иметь одинаковое количество столбцов, иначе число, переданное ПОИСКПОЗ в col_index_num будет некорректным (не будет соответствовать положению столбца в таблица_массив).
Функция XLOOKUP для поиска в строках и столбцах
Недавно Microsoft представила еще одну функцию в Excel, которая призвана заменить все существующие функции поиска, такие как ВПР, ГПР и ПОИСКПОЗ ПО ИНДЕКСУ. Помимо прочего, XLOOKUP может смотреть на пересечение определенной строки и столбца:
XLOOKUP(vlookup_value, vlookup_column_rangeПРОСМОТР(hlookup_value, hlookup_row_range, data_array))
Для нашего примера набора данных формула выглядит следующим образом:
=XLOOKUP(H1, A2:A4, XLOOKUP(H2, B1:E1, B2:E4))
Примечание. В настоящее время XLOOKUP — это бета-функция, доступная только подписчикам Office 365, которые участвуют в программе предварительной оценки Office.
Как работает эта формула
В формуле используется функция XLOOKUP для возврата всей строки или столбца. Внутренняя функция ищет целевой год в строке заголовка и возвращает все значения для этого года (в данном примере для 1980 года). Эти значения идут в return_array аргумент внешнего XLOOKUP:
XLOOKUP(H1, A2:A4, {22000;25000;700}))
Внешняя функция XLOOKUP ищет целевое животное в заголовках столбцов и возвращает значение в той же позиции из массива return_array.
Формула СУММПРОИЗВ для двустороннего поиска
Функция СУММПРОИЗВ похожа на швейцарский нож в Excel — она может делать так много вещей, выходящих за рамки ее предназначения, особенно когда речь идет об оценке нескольких критериев.
Чтобы найти два критерия в строках и столбцах, используйте эту общую формулу:
СУММПРОИЗВ(vlookup_column_range знак равно vlookup_value) * (hlookup_row_range знак равно hlookup_value), data_array)
Чтобы выполнить двусторонний поиск в нашем наборе данных, формула выглядит следующим образом:
=СУММПРОИЗВ((A2:A4=H1) * (B1:E1=H2), B2:E4)
Приведенный ниже синтаксис также будет работать:
=СУММПРОИЗВ((A2:A4=H1) * (B1:E1=H2) * B2:E4)
Как работает эта формула
В основе формулы мы сравниваем два значения поиска с заголовками строк и столбцов (целевое животное в H1 со всеми именами животных в A2: A4 и целевой год в H2 со всеми годами в B1: E1):
(A2:A4=H1) * (B1:E1=H2)
Это приводит к 2 массивам значений TRUE и FALSE, где TRUE представляют совпадения:
{ЛОЖЬ; ЛОЖЬ; ИСТИНА} * {ЛОЖЬ, ИСТИНА, ЛОЖЬ, ЛОЖЬ}
Операция умножения преобразует значения TRUE и FALSE в 1 и 0 и создает двумерный массив из 4 столбцов и 3 строк (строки разделяются точкой с запятой, а каждый столбец данных — запятой):
{0,0,0,0;0,0,0,0;0,1,0,0}
Функция СУММПРОИЗВ умножает элементы приведенного выше массива на элементы B2:E4 в тех же позициях:
{0,0,0,0;0,0,0,0;0,1,0,0} * {22000,13800,8500,3500;25000,23000,22000,20000;700,2000,2300,2500 }
И поскольку умножение на ноль дает ноль, выживает только элемент, соответствующий 1 в первом массиве:
СУММПРОИЗВ({0,0,0,0;0,0,0,0;0,2000,0,0})
Наконец, СУММПРОИЗВ складывает элементы результирующего массива и возвращает значение 2000.
Примечание. Если в вашей таблице несколько заголовков строк и/или столбцов с одинаковыми именами, итоговый массив будет содержать более одного числа, отличного от нуля, и все эти числа будут суммированы. В результате вы получите сумму значений, удовлетворяющую обоим критериям. Это то, что отличает формулу СУММПРОИЗВ от ПОИСКПОЗ ПОИСКПОЗ ПОИСКПОЗ и ВПР, которые возвращают первое найденное совпадение.
Поиск матрицы с именованными диапазонами (явное пересечение)
Еще один удивительно простой способ поиска матрицы в Excel — использование именованных диапазонов. Вот как:
Часть 1. Назовите столбцы и строки
Самый быстрый способ назвать каждую строку и каждый столбец в вашей таблице:
- Выделите всю таблицу (в нашем случае A1:E4).
- На Формулы вкладка, в Определенные имена группу, нажмите «Создать из выделения» или нажмите сочетание клавиш Ctrl + Shift + F3.
- в Создать имена из выделения диалоговом окне выберите Верхний ряд и Левый столбец и нажмите кнопку ОК.
Это автоматически создает имена на основе заголовков строк и столбцов. Однако есть пара предостережений:
- Если заголовки ваших столбцов и/или строк являются числами или содержат определенные символы, которые не разрешены в именах Excel, имена для таких столбцов и строк не будут созданы. Чтобы увидеть список созданных имён, откройте Диспетчер имён (Ctrl+F3). Если некоторые имена отсутствуют, определите их вручную, как описано в разделе Как назвать диапазон в Excel.
- Если некоторые из ваших заголовков строк или столбцов содержат пробелы, пробелы будут заменены символами подчеркивания, например: Полярный медведь.
Для нашего образца таблицы Excel автоматически создал только имена строк. Имена столбцов должны быть созданы вручную, потому что заголовки столбцов являются числами. Чтобы преодолеть это, вы можете просто предварять числа символами подчеркивания, например _1990.
В результате имеем следующие именованные диапазоны:
Часть 2. Создание формулы поиска по матрице
Чтобы получить значение на пересечении данной строки и столбца, просто введите одну из следующих общих формул в пустую ячейку:
знак равноrow_name имя_столбца
Или наоборот:
знак равноимя_столбца row_name
Например, чтобы получить популяцию синих китов в 1990 году, формула проста:
=Синий_кит _1990
Если кому-то нужны более подробные инструкции, выполните следующие действия:
- В ячейке, в которой вы хотите отобразить результат, введите знак равенства (=).
- Начните вводить имя целевой строки, например, Синий кит. После того, как вы введете пару символов, Excel отобразит все существующие имена, соответствующие вашему вводу. Дважды щелкните нужное имя, чтобы ввести его в формулу:
- После имени строки введите пробел, который в данном случае работает как оператор пересечения.
- Введите имя целевого столбца ( _1990 в нашем случае).
- Как только будут введены имена строки и столбца, Excel выделит соответствующую строку и столбец в вашей таблице, и вы нажмете Enter, чтобы завершить формулу:
Ваш поиск по матрице выполнен, и на снимке экрана ниже показан результат:
Вот как искать в строках и столбцах в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Доступные загрузки
Образец рабочей книги с двумерным поиском
