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

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

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

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

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

=ИНДЕКС(возвращаемая_таблица;ПОИСКПОЗ(искомое_значение;массив_поиска;0);номер_столбца)

√ Примечание: Поскольку мы уже заполнили информацию из таблицы 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)

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

Объяснение формулы

Здесь мы используем приведенную ниже формулу в качестве примера:

=ИНДЕКС($F$5:$H$11;ПОИСКПОЗ(C16;$F$5:$F$11;0);2)

• ПОИСКПОЗ(C16;$F$5:$F$11;0): Тип совпадения 0 заставляет функцию ПОИСКПОЗ выполнить точное совпадение. Функция затем возвращает позицию значения совпадения 23201 (значение в C16) в массиве поиска $F$5:$F$11. Таким образом, функция вернет 3, так как его значение совпадения находится на 3-й позиции в диапазоне.
• ИНДЕКС($F$5:$H$11$F$5:$H$11;ПОИСКПОЗ(C16;$F$5:$F$11;0);22) = ИНДЕКС($F$5:$H$11$F$5:$H$11;3;22): Функция ИНДЕКС возвращает значение на пересечении 3-й строки и 2-го столбца возвращаемой таблицы $F$5:$H$11, которое равно 91.

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

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