Поиск в 2d таблицы с использованием индекса & MATCH функции

Posted on: 2020年10月22日 2020年11月12日
Tags: counting

В этой статье мы узнаем, как искать значения в 2-мерной таблице с помощью функции ИНДЕКС-ПОИСКПОЗ-ПОИСКПОЗ в Excel.

Сценарий:

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

Эта формула называется формулой INDEX MATCH MATCH, или, скажем, формулой двумерного поиска.

Как решить проблему?

Чтобы формула сначала поняла, нам нужно немного пересмотреть следующие функции. link: / lookup-formulas-excel-index-function [INDEX function]

link: / lookup-formulas-excel-match-function [MATCH function]

link: / lookup-formulas-excel-index-function [Функция INDEX] возвращает значение по заданному индексу в массиве.

link: / lookup-formulas-excel-match-function [MATCH function] возвращает индекс первого появления значения в массиве (одномерном массиве).

Теперь мы составим формулу, используя указанные выше функции. Функция соответствия вернет индекс искомого значения1 в поле заголовка строки. А другая функция ПОИСКПОЗ вернет индекс искомого значения2 в поле заголовка столбца. Номера индексов теперь будут загружены в функцию ИНДЕКС, чтобы получить значения под поисковым значением из данных 2D-таблицы.

Общая формула:

= INDEX ( data , MATCH ( lookup_value1, row_headers, 0 , MATCH ( lookup_value2, column_headers, 0 ) ) )

Данные: массив значений внутри таблицы без заголовков lookup_value1: значение для поиска в row_header.

row_headers: массив индекса строки для поиска.

lookup_value1: значение для поиска в column_header.

column_headers: индексный массив столбца для поиска.

Пример:

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

Значение Student1 должно соответствовать массиву Row_header, а значение Subject2 должно соответствовать массиву Column_header.

Используйте формулу в ячейке J6:

= INDEX ( table , MATCH ( J5, row, 0 , MATCH ( J4, column, 0 ) ) )

Пояснение:

Функция ПОИСКПОЗ сопоставляет значение Student в ячейке J4 с массивом заголовков строки и возвращает его позицию 3 * в виде числа.

Функция ПОИСКПОЗ сопоставляет значение Subject в ячейке J5 с массивом заголовков столбца и возвращает его позицию 4 * в виде числа.

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

Здесь значения формулы даны как ссылки на ячейки, а row_header, table и column_header даны как именованные диапазоны.

Как вы можете видеть на снимке выше, мы получили оценку, полученную студентом Гэри по предметным общественным наукам, как 36.

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

Теперь мы будем использовать приблизительное совпадение с заголовками строк и столбцов в виде чисел. Приблизительное совпадение принимает только числовые значения, так как оно не применяется к текстовым значениям. Здесь у нас есть цена значений в соответствии с высотой и шириной продукта.

Нам нужно найти цену для определенной высоты (34) и ширины (21), как показано на снимке ниже.

Значение Height1 должно соответствовать массиву Row_header, а значение Width2 должно соответствовать массиву Column_header.

Используйте формулу в ячейке K6:

= INDEX (data , MATCH (K4, Height, 1 , MATCH ( K5, Width, 1 ) ) )

Пояснение:

Функция ПОИСКПОЗ сопоставляет значение высоты в ячейке K4 с массивом заголовков строки и возвращает его позицию 3 * в виде числа.

Функция ПОИСКПОЗ сопоставляет значение ширины в ячейке K5 с массивом заголовков столбца и возвращает его позицию 2 * в виде числа.

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

Здесь значения формулы даны как ссылки на ячейки, а row_header, data и column_header даны как именованные диапазоны, как указано на снимке выше.

Как вы можете видеть на снимке выше, у нас есть цена, полученная по высоте (34) и ширине (21) как 53,10. Это доказывает, что формула работает нормально, а в случае сомнений см. Примечания ниже для более полного понимания.

Примечания:

Функция возвращает ошибку #NA, если аргумент массива поиска для функции ПОИСКПОЗ представляет собой двумерный массив, который является полем заголовка данных.
Функция соответствует точному значению, поскольку аргумент типа соответствия функции ПОИСКПОЗ равен 0.
Значения поиска могут быть заданы как ссылка на ячейку или напрямую с использованием символа кавычки («) в формуле в качестве аргументов.

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

Мы обязательно вам поможем.

Поиск в 2d таблицы с использованием индекса & MATCH функции

Сценарий:

Как решить проблему?

Общая формула:

Пример:

Похожие статьи

Популярные статьи