Как сопоставить данные двух таблиц в Excel 2007-2010. Функции ИНДЕКС и ПОИСКПОЗ

Как сопоставить данные двух таблиц в Excel 2007-2010. Функции ИНДЕКС и ПОИСКПОЗ

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

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

Главное условие — наличие абсолютно схожих колонок (например, код, наименование, сумма и пр.).

Формула будет выглядеть следующим образом:

=ИНДЕКС(диапазон таблицы 2, из которой берутся данные;ПОИСКПОЗ(ячейка в таблице 1, по которой производится сопоставление;столбец таблицы 2 с указанием сопоставляемых значений;0);номер столбца из таблицы 2)

Приведенную формулу можно скопировать и вставить в ячейку Excel. Части формулы, выделенные разными цветами, подлежат изменению.

Пошаговая инструкция как сопоставить данные двух таблиц в Excel

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

Нам необходимо подтянуть данные по остаткам товара из таблицы с остатками в прайс-лист.

В данном случае видим, что абсолютно совпадает колонка с указанием кода. По ней и будем сопоставлять.

Добавляем в Таблицу 1 столбец для перенесения данных. Копируем формулу и вставляем ее в первую ячейку нового столбца.

Шаг 1. Заходим в строку формул и выделяем первый цветной отрезок формулы — красный:

Далее сразу (не нажимая больше никуда, это правило распространяется до полного заполнения формулы) выделяем весь диапазон Таблицы 2, в котором будет происходить поиск значений.

Шаг 2. Выделяем синий отрезок.

Выбираем ячейку с указанием кода в Таблице 1.

Данный код формула будет искать в Таблице 2.

Шаг 3. Выделяем зеленый отрезок.

Выделяем столбец в Таблице 2 с указанием кодов.

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

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

Шаг 4. Выделяем коричневый отрезок.

Пишем номер столбца, содержимое которого хотим подтянуть в Таблицу 1. В данном случае остаток товара указан в столбце 3 Таблицы 2.

Видим, что в ячейке появилось значение, соответствующее коду 101 в Таблице 2.

Теперь нужно скопировать формулу и протянуть до конца списка.

Есть один важный нюанс, без которого данная операция не принесет нужного результата.

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

После закрепления областей, копируем формулу и протягиваем до конца списка. Получаем:

Видим, что формула нашла все имеющиеся значения. В случае отсутствия нужного кода формула вывела #Н/Д. Такие ячейки можно отфильтровать с помощью Автофильтра и далее проверять вручную.

Может оказаться, что #Н/Д появится и в ячейках, значения которых имеются в Таблице 2. В этом случае рекомендуется использовать функцию СЖПРОБЕЛЫ для избавления от лишних пробелов в ячейках с кодами, которые делают ячейки не абсолютно похожими.