Как в кабинете

Объединить две таблицы в Excel

Из данной нам статьи Вы узнаете, как стремительно объединить данные из 2-ух таблиц Excel, когда в главных столбцах нет четких совпадений. К примеру, когда неповторимый идентификатор из первой таблицы представляет собой 1-ые 5 знаков идентификатора из 2-ой таблицы. Все предлагаемые в данной нам статье решения протестированы мной в Excel 2013, 2010 и 2007.

Итак, есть два листа Excel, которые необходимо объединить для предстоящего анализа данных. Представим, в одной таблице содержатся цены (столбец Price) и описания продуктов (столбец Beer), которые Вы продаёте, а во 2-ой отражены данные о наличии продуктов на складе (столбец In stock). Если Вы либо Ваши коллеги составляли обе таблицы по каталогу, то в обеих должен находиться как минимум один главный столбец с неповторимыми идентификаторами продуктов. Описание продукта либо стоимость могут изменяться, но неповторимый идентификатор постоянно остаётся постоянным.

Трудности начинаются, когда Вы получаете некие таблицы от производителя либо из остальных отделов компании. Дело может ещё усложниться, если вдруг вводится новейший формат неповторимых идентификаторов либо самую малость поменяются складские номенклатурные обозначения (SKU). И перед Вами стоит задачка объединить в Excel новейшую и старенькую таблицы с данными. Так либо по другому, возникает ситуация, когда в главных столбцах имеет пространство лишь частичное совпадение записей, к примеру, «12345» и «12345-новый_суффикс». Для вас-то понятно, что это этот же SKU, но комп не так догадлив! Это не четкое совпадение делает неосуществимым внедрение обыденных формул Excel для объединения данных из 2-ух таблиц.

И что совершенно плохо — соответствия могут быть совсем нечёткими, и «Некая компания» в одной таблице может перевоплотиться в «ЗАО „Некая Компания“» в иной таблице, а «Новенькая Компания (бывшая Некая Компания)» и «Древняя Компания» тоже окажутся записью о одной и той же фирме. Это понятно Для вас, но как это разъяснить Excel?

Выход есть постоянно, читайте дальше и Вы узнаете решение!

Замечание: Решения, описанные в данной нам статье, всепригодны. Вы сможете адаптировать их для предстоящего использования с хоть какими обычными формулами, таковыми как ВПР (VLOOKUP), ПОИСКПОЗ (MATCH), ГПР (HLOOKUP) и так дальше.

• Главный столбец в одной из таблиц содержит доп знаки
• Данные из главного столбца в первой таблице разбиты на два либо наиболее столбца во 2-ой таблице
• Данные в главных столбцах не совпадают (123-SDX и HFGT-23) либо есть частичное совпадение, меняющееся от ячейки к ячейке (Coca Cola и Coca-Cola Inc.)

Главный столбец в одной из таблиц содержит доп знаки

Разглядим две таблицы. Столбцы первой таблицы содержат номенклатурный номер (SKU), наименование пива (Beer) и его стоимость (Price). Во 2-ой таблице записан SKU и количество бутылок на складе (In stock). Заместо пива быть может хоть какой продукт, а количество столбцов в настоящей жизни быть может еще больше.

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

Главным в таблице в нашем примере является столбец A с данными SKU, и необходимо извлечь из него 1-ые 5 знаков. Добавим вспомогательный столбец и назовём его SKU helper:

• Наводим указатель мыши на заголовок столбца B, при всем этом он должен принять вид стрелки, направленной вниз:
• Кликаем по заголовку правой клавишей мыши и в контекстном меню избираем Вставить (Insert):

Тут A2 — это адресок ячейки, из которой мы будем извлекать знаки, а 5 — количество знаков, которое будет извлечено.

Готово! Сейчас у нас есть главные столбцы с четким совпадением значений — столбец SKU helper в главный таблице и столбец SKU в таблице, где будет производиться поиск.

Сейчас с помощью функции ВПР (VLOOKUP) мы получим подходящий итог:

Остальные формулы

• Извлечь 1-ые Х знаков справа: к примеру, 6 знаков справа из записи «DSFH-164900». Формула будет смотреться так:

Одним словом, Вы сможете употреблять такие функции Excel, как ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT), ПСТР (MID), НАЙТИ (FIND), чтоб извлекать любые части составного индекса. Если с сиим появились трудности — свяжитесь с нами, мы создадим всё вероятное, чтоб посодействовать Для вас.

Данные из главного столбца в первой таблице разбиты на два либо наиболее столбца во 2-ой таблице

Представим, таблица, в которой делается поиск, содержит столбец с идентификаторами. В ячейках этого столбца содержатся записи вида XXXX-YYYY, где XXXX — это кодовое обозначение группы продуктов (мобильники, телеки, камеры, камеры), а YYYY — это код продукта снутри группы. Основная таблица состоит из 2-ух столбцов: в одном содержатся коды товарных групп (Group), во 2-м записаны коды продуктов (ID). Мы не можем просто откинуть коды групп продуктов, потому что один и этот же код продукта может повторяться в различных группах.

Добавляем в главной таблице вспомогательный столбец и называем его Full ID (столбец C), подробнее о том, как это делается поведано ранее в данной нам статье.

В ячейке C2 запишем такую формулу:

Сейчас объединить данные из наших 2-ух таблиц не составит труда. Мы будем сопоставлять столбец Full ID первой таблицы со столбцом ID 2-ой таблицы. При обнаружении совпадения, записи из столбцов Description и Price 2-ой таблицы будут добавлены в первую таблицу.

Данные в главных столбцах не совпадают

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

Нехорошая новость: Данные, содержащиеся в этих 2-ух таблицах Excel, придётся обрабатывать вручную, чтоб в предстоящем было может быть объединить их.

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

1. Создаём вспомогательную таблицу для поиска.

Создаём новейший лист Excel и называем его SKU converter. Копируем весь столбец Our.SKU из листа Store на новейший лист, удаляем дубликаты и оставляем в нём лишь неповторимые значения.

Рядом добавляем столбец Supp.SKU и вручную отыскиваем соответствия меж значениями столбцов Our.SKU и Supp.SKU (в этом нам посодействуют описания из столбца Description). Это кислая работёнка, пусть Вас веселит идея о том, что её придётся выполнить лишь один раз :-).

В итоге мы имеем вот такую таблицу:

2. Обновляем главную таблицу с помощью данных из таблицы для поиска.

В главную таблицу (лист Store) вставляем новейший столбец Supp.SKU.

Дальше с помощью функции ВПР (VLOOKUP) сравниваем листы Store и SKU converter, используя для поиска соответствий столбец Our.SKU, а для обновлённых данных — столбец Supp.SKU.

Столбец Supp.SKU заполняется уникальными кодами производителя.

Замечание: Если в столбце Supp.SKU возникли пустые ячейки, то нужно взять все коды SKU, надлежащие сиим пустым ячейкам, добавить их в таблицу SKU converter и отыскать соответственный код из таблицы поставщика. Опосля этого повторяем шаг 2.

В нашей главной таблице есть главный столбец с четким совпадением с элементами таблицы поиска, так что сейчас эта задачка не вызовет сложностей

С помощью функции ВПР (VLOOKUP) объединяем данные листа Store с данными листа Wholesale Supplier 1, используя для поиска соответствий столбец Supp.SKU.

Вот вам наглядный пример обновлённых данных в столбце Wholesale Price:

Объединение таблиц в Power Query

Вы работали со сводными таблицами Excel? Если нет – необходимо срочно начинать. Это супер-инструмент, который дозволяет стремительно и просто обрабатывать огромные объемы данных. А если вы с ними все-же работали, то я как экстрасенс-капитан-очевидность могу буквально сказать, что вы сталкивались с ситуацией, когда необходимо выстроить сводную таблицу на базе нескольких источников данных. К примеру, при помощи нескольких схожих таблиц Excel, копируя их одну под иной. Либо дополняя свою таблицу новенькими столбцами и аналитиками.

Добавление либо объединение таблиц?

Почаще всего, чтоб объединить данные в Excel, юзеры копируют таблицы одну под другую. Либо пишут формулы по типу ВПР, если в таблицу необходимо добавить новейшие столбцы либо аналитики. Но вы наверное понимаете, что самый удачный инструмент для объединения данных в Excel – это Power Query. Там есть два принципных метода:

1. По вертикали – добавление таблицы под таблицу. Полезно, когда таблицы с схожей шапкой находятся в различных файлах либо на различных листах.
2. По горизонтали – слияние таблиц, схожее на ВПР. А тут еще есть варианты — не только лишь подобные ВПР, да и ВПР-наоборот, и ВПР-неВПР… Целых 6 видов объединения таблиц.

Разберем, чем эти методы различаются друг от друга.

Вариант 1. Добавление таблицы под таблицу

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

Как на базе таблиц с схожей шапкой выстроить единую базу? Добавив таблицы одну под другую

Ранее функцию прибавления можно было выполнить лишь при помощи копирования. Отсюда – много ручной работы при внесении новейших данных (ну, либо писали макросы). Но с момента, как в Excel возник Power Query, у нас возникла возможность добавлять таблицы одну под другую несколькими щелчками мыши:

Подробности про объединение данных при помощи Прибавления смотрите в видео.

Вариант 2. Объединение таблиц

Объединение таблиц применяется, когда у вас есть две таблицы (шапки у их быстрее всего различные), и одна таблица дополняет другую. К примеру, в отчете реализации показаны по городкам, а у вас требуют вывести информацию по областям. Тогда будет нужно уже объединение по горизонтали:

Одна таблица дополняет другую при помощи объединения по общему столбцу

В «обыкновенном» Excel это делается при помощи формулы ВПР либо остальных формул с схожим функционалом. Power Query дает нам больше методов объединения, чем ВПР. Используя этот инструмент, мы можем не «просто» связать таблицы, да и создать это по определенным условиям.

Подробности про Объединение данных смотрите в последующем видео.

Дальше в статье тщательно разбираются типы объединения данных. Чтоб для вас было удобнее читать, приложены файлы с примерами.

Типы объединения данных в Power Query

Power Query дает на выбор 6 разных методов объединения таблиц:

• Наружное соединение слева (все из первой таблицы, совпадающие из 2-ой)
• Наружное соединение справа (все из 2-ой таблицы, совпадающие из первой)
• Полное наружное (все строчки из обеих таблиц)
• Внутреннее (лишь совпадающие строчки)
• Анти-соединение слева (лишь строчки в первой таблице)
• Анти-соединение справа (лишь строчки во 2-ой таблице)

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

Наружное соединение слева

Наружное соединение справа

Разберемся, что все это означает и как работают различные методы объединения.

Наружное соединение слева

Наружное соединение слева – самый нередко встречающийся метод объединения таблиц. Это то, что мы делаем при помощи формулы ВПР в Excel – из одной таблицы «перетаскиваем» данные в другую.

Когда вы выбираете «Наружное соединение слева», к данным из первой таблицы добавляются все значения из 2-ой таблицы, надлежащие столбцу поиска. Если во 2-ой таблице нет разыскиваемых значений, вы получите null (null – это означает пусто).

Пример: объединим план продаж в штуках с плановыми ценами.

Добавим таблицы в Power Query: вкладка Данные → Получить данные → Из остальных источников → Из таблицы / спектра, либо для новейших версий Excel: вкладка Данные → Из таблицы / спектра. Объединяем запросы: вкладка Основная → Объединить запросы (Объединить запросы в новейший), избираем столбец слияния «снаряд» и тип объединения «Наружное соединение слева».

На некие продукты плановые цены еще не установлены, потому опосля объединения в столбце «стоимость» для этих продуктов будет проставлен null.

Хоть в диалоговом окне таблицы размещены одна под иной (просто так удобнее на техническом уровне), связь все равно горизонтальная. В диалоговом окне вы указываете столбец, по которому производится объединение.

Направьте внимание, в нижней части окна возникло информационное сообщение: «Выбор согласовал 6 из первых строк (9)». Соответственно, в таблице с плодами цены заполнены лишь в 6 строчках, а в 3-х стоит null.

Наружное соединение справа

Тип соединения «Наружное соединение справа» употребляется, если в первую таблицу необходимо не только лишь добавить новейшие столбцы, да и отфильтровать её. 2-ая таблица употребляется как источник данных и управляет содержимым первой таблицы.

Что происходит, когда вы выбираете «Наружное соединение справа»: из первой таблицы пропадут все не отысканные во 2-ой таблице данные. Из 2-ой таблицы будут добавлены те значения, которых нет в первой. Если поглядеть на картинки, то видно, что Наружное соединение справа работает так же, как и Наружное соединение слева – различается лишь порядок расположения таблиц.

Пример: в первой таблице — ожидаемые среднемесячные реализации на 1-го покупателя, во 2-ой – количество возможных покупателей.

Объединим таблицы, чтоб запланировать будущие реализации. Общий столбец для поиска — «город».

(*2*)

Смотрим, что вышло в итоге объединения: из таблицы «пропали» данные по городкам, в которых нет возможных покупателей – Алматы и Санкт-Петербург. Зато возник город, в котором есть вероятные покупатели – Астана. По этому городку будет нужно проставить сумму ожидаемых средних продаж, чтоб выполнить последующие расчеты.

Полное наружное соединение

Этот тип соединения делает таблицу, в которой все есть строчки и первой, и 2-ой таблицы.

Что происходит, когда вы выбираете «Полное наружное соединение»: при помощи данного общего столбца будут объединены все строчки из первой и 2-ой таблиц.

Пример: в первой таблице приведен размер производства по денькам, во 2-ой – размер брака.

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

Смотрим, что вышло: отчет с датами и цифрами из первой и 2-ой таблицы. В ячейках, где данные за подобные периоды были лишь в первой либо лишь во 2-ой таблице, проставлено null.

Внутреннее соединение

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

Что происходит, когда вы выбираете «Внутреннее соединение»: таблицы будут объединены при помощи данного общего столбца. При всем этом из первой таблицы пропадут все не отысканные в «общем» столбце 2-ой таблицы строчки. Из 2-ой таблицы – пропадут не отысканные в первой. В арифметике эта операция именуется «пересечением множеств».

Пример: есть два перечня покупателей, принявших роль в акциях А и Б – по одному для каждой из акций. При помощи Внутреннего соединения получим перечень покупателей, участвовавших и в той, и в иной акции.

Поиск исполняем по общему столбцу «Клиент». Итог — перечень покупателей, участвовавших в обеих акциях: