На работе довольно часто приходится объединять таблицы. Это требуется при работе с контрагентами, прайс-листами, отчётами по продажам и многим другим.
Примеры: надо добавить телефон клиента к отчёту по оборотам или добавить цену к таблице о движении товаров по полям артикул и производитель.
Рассмотрим такую задачу: у нас запросили цены на определённый список товаров. У нас есть прайс-лист товаров в Excel. Есть несколько способов решить эту задачу.
1. Объединение таблиц с помощью формул
Если таблицы надо объединить по одному полю и оно в первом столбце, причём всё упорядочено по нему. Тогда можно воспользоваться формулой, если у вас случай сложнее и к этому случаю изменить таблицы затруднительно, то он Вам не подходит.
Во всех версиях MS Excel есть функция ВПР. Подробное описание функции ВПР с видео на сайте Microsoft. У нас на работе установлена не самая свежая версия офиса, поэтому буду показывать все варианты на её примере, но в свежих версиях это тоже будет работать, только интерфейс незначительно отличается.
В новой версии MS Office (в т.ч. 365) добавили функцию XLOOKUP, которая немного лучше, но не особо. Подробное описание функции ПРОСМОТРX с видео на сайте Microsoft.
Пара особенностей пользования функцией ВПР. Сначала Таблицы надо упорядочить по полю, по которому будут сравниваться. Затем в любое место листа с таблицей, к которой надо добавить данные, вставьте таблицу, из которой надо добавить данные (ВПР с другого листа не возьмёт).
На картинке видно как напишет, если не найдено соответствие. Функцию ПРОСМОТР не рекомендую (2ой слайд), так как она работает как ВПР с параметром ИСТИНА, и находит то, чего нет или путает буквы с цифрами.
2. Объединение таблиц с помощью инструмента запросов баз данных.
В MS Office до 2010 сделать это можно только с использованием другой программы: Access.
Сначала нужно скопировать таблицы из Excel в новые таблицы в Acess (в одном файле Создание->Таблица). Если просто копировать, то в таблицы Excel перед копированием надо вставить пустой столбец, чтобы в него потом записался ключ. Если импортировать, то этого не надо делать.
Затем надо настроить связи таблиц: столбцы, по которым будем сравнивать (Работа с базами данных->Схема данных). И тут уже появляется преимущество, потому что можно сравнивать по нескольким столбцам (в 1ом способе пришлось бы использовать дополнительные формулы вроде СЦЕПИТЬ и ЕСЛИ).
Затем надо запустить Мастер запросов (в разделе Создание). Мастер запросов интуитивно понятен, но предлагает не все варианты, которые могут потребоваться в работе. Но это поправимо, если переключиться в режим SQL. В нашем примере получается запрос:
SELECT Таблица1.Артикул, Таблица1.Производитель, Таблица1.Номенклатура, Таблица1.Колво, Таблица2.Цена
FROM Таблица1 INNER JOIN Таблица2 ON (Таблица1.Артикул=Таблица2.Артикул) AND (Таблица1.Производитель=Таблица2.Бренд);
Но он не выводит строки, для которых не найдено соответствие во второй таблице. Чтобы это решить надо поменять INNER JOIN на LEFT JOIN. Основные виды объединения проиллюстрирую ниже, но язык запросов SQL обладает многими возможностями и JOIN лишь одна из них.
С версии MS Office 2010 можно воспользоваться Power Query (или Power BI, если в Вашей организации он установлен). После установки появится вкладка с новыми функциями. Но для объединения таблиц доступны только 6 вариантов:
Картинку, которая наглядно иллюстрирует отличия различных вариантов объединения, взял тут. В той же статье можно посмотреть пример как работать с Power Query с различными вариантами объединения, поэтому повторяться не буду.
3. Макрос для объединения таблиц
Делать с помощью макроса не советую. Это самый трудный способ, так как макрос придётся переделывать при каждом изменении исходных данных. Но если у вас всё время требуется повторять одинаковую операцию, то может быть полезным.
Если Вы не знакомы с макросами, то я уже писал про простой способ для записи их в предыдущей заметке.
Писать готовый скрипт макроса не буду, так как у Вас любая таблица может отличаться и с таким же успехом можно поискать что-то похожее в Яндексе. Но сразу скажу, что самый лучший вариант записи макроса будет такой: надо включить запись макроса и сделать способ с формулами, затем исправить макрос, чтобы искало не по фиксированным диапазонам, а до конца таблиц.