Объединить таблицы с помощью функций ИНДЕКС и ПОИСКПОЗ

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

Как объединить таблицы с помощью функций ИНДЕКС и ПОИСКПОЗ?

Чтобы объединить таблицу 1 и таблицу 2 и собрать всю информацию в новой таблице, как показано на скриншоте выше, сначала скопируйте данные из таблицы 1 или таблицы 2 в новую таблицу (в данном примере я скопировал данные из таблицы 1 — см. скриншот ниже). Возьмём первый идентификатор учащегося 23201 в новой таблице в качестве примера: функции ИНДЕКС и ПОИСКПОЗ помогут вам получить его оценку и место в рейтинге следующим образом. Функция ПОИСКПОЗ возвращает номер строки с идентификатором учащегося, совпадающим со значением 23201 в таблице 2. Эта информация о строке передаётся функции ИНДЕКС, чтобы получить значение на пересечении этой строки и указанного столбца (столбца оценки или рейтинга).

Общий синтаксис

=INDEX(return_table,MATCH(lookup_value,lookup_array,0),col_num)

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

Чтобы получить соответствующие данные из таблицы 2 и объединить всю информацию в новой таблице, скопируйте или введите приведённые ниже формулы в ячейки F16 и G16 и нажмите клавишу Enter, чтобы получить результаты:

Ячейка F16 (Оценка)
=ИНДЕКС()$F$5:$H$11;ПОИСКПОЗ()C16;$F$5:$F$11;0);2)
Ячейка G16 (Место)
=ИНДЕКС()$F$5:$H$11;ПОИСКПОЗ()C16;$F$5:$F$11;0);3)

√ Примечание: Знаки доллара ($) выше указывают на абсолютные ссылки, что означает, что return_tableи lookup_arrayв формуле не изменятся при перемещении или копировании формул в другие ячейки. Однако знаки доллара не добавлены к lookup_value, поскольку это значение должно быть динамическим. После ввода формулы перетащите маркер заполнения вниз, чтобы применить её к ячейкам ниже.

Пояснение формулы

В качестве примера возьмём следующую формулу:

=INDEX($F$5:$H$11,MATCH(C16,$F$5:$F$11,0),2)

• MATCH(C16,$F$5:$F$11,0):Параметр match_type 0заставляет функцию MATCH выполнять точное сопоставление. Функция возвращает позицию найденного значения 23201(значение в ячейке)C16) в массиве поиска $F$5:$F$11. Таким образом, функция вернёт значение 3, поскольку соответствующее значение находится на 3-й позиции в диапазоне.
• INDEX()$F$5:$H$11,MATCH(C16,$F$5:$F$11,0),2) = INDEX($F$5:$H$11Функция INDEX возвращает значение на пересечении 3-й строки и 2-го столбца диапазона $F$5:$H$11, то есть 91.

Связанные функции

Связанные формулы