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

Предположим, у нас есть таблицы:

  1. Клиенты – полный перечень клиентов магазина, которые есть в базе данных
  2. Продажи – список продаж за отчетный период

объединить таблицы

Задача: из первой таблицы во вторую перенести город и статус клиента. А из второй в первую – величину заказа. Приступаем.

Для удобства, я присвоил имена используемым массивам:

  • Заголовки_1 – шапка первой таблицы (A2:D2)
  • Заголовки_2 – шапка второй таблицы (H2:K2)
  • Фамилии_1 – список фамилий в первой таблице (A3:A27)
  • Фамилии_2 – список фамилий во второй таблице (H3:H10)
  • Данные_1 – информация из первой таблицы (A3:D27)
  • Данные_2 – информация из второй таблицы (H3:K10)

Если вам не знакомо именование диапазонов, изучите тему в этой статье.

Перенос данных во вторую таблицу

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

Алгоритм таков:

  1. Формула для поиска номера строки (фамилии) в первой таблице: =ПОИСКПОЗ($H3;фамилии_1;0)
  2. Для описка номера столбца, в котором содержится нужная колонка (город или статус) – вот так: =ПОИСКПОЗ(I$2;заголовки_1;0)
  3. Для заполнения искомых данных, в функцию ИНДЕКС вставим формулы из первых двух пунктов:

=ИНДЕКС(данные_1;ПОИСКПОЗ($H3;фамилии_1;0);ПОИСКПОЗ(I$2;заголовки_1;0))

таблицы объединены

Сейчас мы получили универсальную формулу, которую копируем во все ячейки с названием города и статусом клиента, получаем правильный результат, т.к. ПОИСКПОЗ сама определяет, какие данные подставлять.

Обратите внимание, что некоторые аргументы в ПОИСКПОЗ имеют знаки $ перед буквой столбца или номером строки. Это нужно для того, чтобы при копировании формулы ссылка на столбец или строку не изменялась, обеспечивая правильную работу функции в каждой ячейке. Подробнее об этом читайте здесь.

Как видно на картинке выше, формула правильно определила и города и статусы.

Загрузка данных в первую таблицу

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

расчет с ошибками

Чтобы это исправить, воспользуемся функцией обработки ошибок ЕСЛИОШИБКА. Тогда формула поиска примет вид:

=ЕСЛИОШИБКА(ИНДЕКС(данные_2;ПОИСКПОЗ($A3;фамилии_2;0);ПОИСКПОЗ(D$2;заголовки_2;0));0)

Теперь в тех сроках, где не было продаж, формула вернёт ноль вместо ошибки.

расчет без ошибок

Как видите, мы заполнили все пустые ячейки достоверными данными, таблицы объединены. Так что, у меня всё. С радостью отвечу на ваши вопросы, комментируйте!


Вам так же может быть интересно