Как объединить таблицы с частичным совпадением в Excel

В этом руководстве будет описано, какие действия необходимо предпринимать для быстрого объединения данных двух таблиц, не имеющих совпадений в ключевых колонках. То есть, что делать, если идентификатор одной таблицы совпадает лишь с 5 первыми значениями идентификатора второй таблицы. Все способы, которые будут описаны сегодня, были протестированы на Excel 2007 версии и других, вплоть до самых современных.

Выше приводится два документа. В первом содержатся описания продаваемой компанией продукции и их стоимость, а во второй – информация о наличии их на складе. Каждый имеет свой уникальный идентификатор, который не меняется вне зависимости от того, сколько раз будет скорректировано поле «цена» или поле «описание».

Могут быть проблемы, если вторая таблица была отправлена другим отделом или компанией. В таком случае может быть другим формат идентификатора. Например, могут сохраниться только первые цифры, а потом — добавиться префикс. Совпадение получается частичным. Конечно, человек может понять, что это по сути один и тот же идентификатор, но компьютер не настолько умный. Это значительно усложняет объединение данных с двух файлов.

Ситуацию может усугубить то, что в одном файле компания может называться «Продукты», а в другом — «Корпорация «Продукты». Сверх того, одно предприятие у вас подписано как «Новая организация», а другое – «Старая компания”. Но это уже одна и та же фирма. Вот только об этом знаете лишь вы — но не слепой компьютерный алгоритм.

К сведению! Решения, описанные в этой статье, могут использоваться в абсолютно любой формуле, такой как ВПР(), ПОИСКПОЗ(), ГПР().

Если Вы не являетесь профессионалом по Excel и не хотите себе усложнять жизнь дополнительными инструкциями, можно воспользоваться дополнением <Merge Tables Wizard for Excel>, чтобы объединить данные с двух документов.

Оно платное, но зато позволяет быстро реализовать рассматриваемую в этой статье задачу. Это единственная вещь, которую дополнение делает, но оно справляется с ней очень хорошо. Вместо того, чтобы писать длинные формулы, вам достаточно запустить виртуального помощника и выполнить 5 шагов, чтобы получить желаемый результат. 

Этот аддон можно использовать в течение 15 дней, после этого нужно заплатить за него. Но дополнение стоит тех денег, которые вы за него платите, поскольку оно может сохранить огромное количество времени и нервов: около часа на изучение использования формул. А в ряде случаев – вплоть до 20 часов, направленных на изучение всех моментов и исправление всех ошибок.

Но для разового использования достаточно и бесплатной версии.

Если колонка с идентификатором содержит дополнительные символы

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

В первой таблице первая колонка – это уникальный идентификатор, от которого нам нужны первые 5 знаков. Мы добавляем дополнительную колонку к правой с названием «SKU helper». Далее нужно следовать такой инструкции:

1. Разместить курсор мыши рядом с названием колонки. Он станет похожим на стрелочку, как показано на скриншоте.
2. Нажать правой кнопкой мыши по нему, чтобы вызвать контекстное меню, и далее выбрать пункт «Вставить».
3. Назвать колонку «SKU helper».
4. Чтобы получить первые пять знаков с колонки SKU, необходимо в новообразованном столбце найти необходимую ячейку и ввести формулу =ЛЕВСИМВ(A2;5). В описанном случае A2 – это ссылка на ячейку, в которой будет осуществляться поиск, а второй аргумент – количество символов, которые программа должна извлекать.
5. Далее эта функция копируется во все остальные ячейки путем перетаскивания клеточки, находящейся в правом нижнем углу, на такое количество строк, которое необходимо. Или же можно выборочно копировать ее стандартным способом (с помощью комбинации клавиш Ctrl+C – Ctrl + V).

Это все! Теперь у вас есть готовая колонка, значения которой точно соответствуют содержимому другой таблицы. Поиск данных будет осуществляться в колонке SKU, а результат будет выдаваться в SKU helper. Ну и, наконец, нужно использовать функцию ВПР для получения требуемого результата.

Какие еще формулы можно использовать?

1. Чтобы извлечь определенное количество символов справа, необходимо использовать функцию =ПРАВСИМВ. Синтаксис аналогичный. Например, формула будет иметь следующий вид: =ПРАВСИМВ(A2;6). 
2. Можно использовать формулу =ПСТР, чтобы извлечь определенную комбинацию знаков из середины. Например, можно пропустить первые 8 символов и извлечь 4 последующих. В результате, формула будет выглядеть приблизительно так.  =ПСТР(A2;8;4)
3. Можно достать все символы, которые находятся до определенного знака. Количество цифр, которые при этом будут копироваться, может отличаться. Например, программа может извлечь 233 и 34 из значений «233-ррр» и «34-рр» соответственно. Для этого необходимо модифицировать формулу =ЛЕВСИМВ, добавив в аргумент формулу «НАЙТИ». 

Таким образом, для нахождения частей индекса можно использовать формулы «ЛЕВСИМВ», «ПРАВСИМВ», «ПСТР», «НАЙТИ». 

Если информация в ключевом столбце первого документа разделена на несколько колонок в другом

Например, в первой таблице есть главная колонка с ID. В ячейке описываются значения вида XXXX-YYYY, где XXXX – это идентификатор группы товаров, а YYYY – это код конкретного товара в этой группе. У нас нет возможности игнорировать принадлежность товара к определенной группе, поскольку один и тот же идентификатор может быть в продукции разных категорий.

Чтобы это сделать, необходимо добавить дополнительную колонку, назвав ее «Полный идентификатор» так, как было описано выше. В нашем примере она должна оказаться в колонке C. Далее необходимо использовать формулу =СЦЕПИТЬ в ячейке C2. Функция имеет три аргумента:

1. Адрес первой ячейки.
2. Разделитель.
3. Адрес второй ячейки.

Выглядит это следующим образом:

=СЦЕПИТЬ(A2;»-«;B2)

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

Теперь можно легко объединить данные с нескольких электронных таблиц. Мы сравниваем колонку «Полный идентификатор» с колонкой «ID» искомой таблицы. Если параметры совпадают, мы добавляем записи с колонок с описанием и ценой, которые находятся в искомой таблице.

Если данные в разных ключевых колонках вообще не совпадают

Допустим, у вас есть небольшой магазин, и вы получаете продукцию от одного или нескольких поставщиков. Каждый из них идентифицирует товары по-своему. Поэтому может оказаться так, что товар, кодированный записью «Case-Ip4S-01» в документе поставщика будет кодироваться, как «SPK-A1403». Причем нет никакой логики, по которой можно задать правило автоматического преобразования идентификатора одного формата в другой.

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

1. Создать новый документ с названием «SKU converter» или любым другим наименованием. Далее необходимо скопировать колонку Our.SKU на новый лист, а также удалить все повторяющиеся части значений.
2. После этого добавляется столбец Supp.SKU и вручную ищутся соответствия между вашими столбцами и колонкой с идентификаторами производителей.
3. Результат будет таким, как на скриншоте.
4. Теперь эта таблица должна быть обновлена с использованием информации, указанной в листе для поиска.

После этого наступает второй этап, в ходе которого выполняются следующие действия:

1. После первой колонки вставляется столбец Supp.SKU.
2. Далее используется функция ВПР, с помощью которой сравниваются листы Store и SKU converter. Соответствия ищутся в столбце Our.SKU, а обновленные данные будут храниться в столбце Supp.SKU. Если после применения формулы некоторые ячейки остались пустыми, следует взять все коды SKU, которые соответствуют этим ячейкам, добавить их в таблицу SKU converter, после чего найти необходимый код в таблице поставщика. После этого повторяется этот шаг.
3. Информация из поисковой таблицы переносится в главную, где есть ключевой столбец с соответствующими элементами таблицы поиска.

В результате, получится такая таблица.

Скажите ведь, все просто, правильно? Главное – немного приловчиться. Конечно, последний случай самый тяжелый, но и здесь можно частично автоматизировать процесс.

Если же не хочется разбираться со всеми моментами объединения таблиц с разными данными, можно воспользоваться приведенным выше дополнением к стандартному пакету Excel. Хотя оно и коммерческое, но первые дни его можно использовать бесплатно. Если нет необходимости постоянно объединять таблицы с разными данными, то и нет смысла изучать эту кучу формул. Достаточно раз воспользоваться дополнением. А если нужно регулярно объединять таблицы, этот аддон поможет сэкономить много времени.