Как соединить две таблицы 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) и так дальше.

Изберите пригодный пример, чтоб сходу перейти к подходящему решению:

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

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

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

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

Наводим указатель мыши на заголовок столбца B, при всем этом он должен принять вид стрелки, направленной вниз:
Кликаем по заголовку правой клавишей мыши и в контекстном меню избираем Вставить (Insert):
Даём столбцу имя SKU helper.
Чтоб извлечь 1-ые 5 знаков из столбца SKU, в ячейку B2 вводим такую формулу:

Тут 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 запишем такую формулу:

Тут A2 – это адресок ячейки, содержащей код группы; знак “–” – это разделитель; B2 – это адресок ячейки, содержащей код продукта. Скопируем формулу в другие строчки.

Сейчас соединить данные из наших 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.

3. Переносим данные из таблицы поиска в главную таблицу

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

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

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

Всё просто, не так ли? Задавайте свои вопросцы в комментах к статье, я постараюсь ответить, как можно быстрее.

Как в excel связать ячейки на одном листе

Обычно мы можем применить формулу = Sheet1! A1 для отражения содержимого ячейки в ячейке A1 листа Sheet1 на остальных листах. Но в данной нам статье представлены еще наиболее обыкновенные и комфортные методы пакетного зеркалирования либо связывания ячеек / диапазонов на листах в Excel.

Зеркальное отражение / связывание ячеек на листах при помощи функции From Microsoft Query
Отразите одну и ту же ячейку на нескольких листах при помощи Kutools for Excel

Зеркальное отражение / связывание ячеек на листах при помощи функции From Microsoft Query

Этот способ проведет вас через таблицу зеркального отображения иной книжки при помощи функции From Microsoft Query в Excel.

1. Нажмите Данные > Из остальных источников > Из Microsoft Query. Смотрите снимок экрана:

2. В диалоговом окне Выбор источника данных изберите Файлы Excel * в левом поле и щелкните OK клавишу.

3. В диалоговом окне «Выбор книжки» сделайте последующие деяния:
(1) Изберите диск, содержащий книжку, данные с которой вы будете зеркалировать в Диски раскрывающийся перечень;
(2) Два раза щелкните, чтоб открыть папку, содержащую книжку, из которой вы будете зеркалировать в Справочники коробка;
(3) Щелкните, чтоб выделить книжку в Имя базы данных пт.
(4) Щелкните значок OK клавишу.

4. Покажется диалоговое окно с предупреждением, как показано на снимке экрана ниже. Щелкните значок OK клавишу, чтоб продолжить.

5. Раскроется Мастер запросов. Щелкните значок Функции клавишу, чтоб открыть клавишу Характеристики таблицы, а потом проверьте Системные таблицы и нажмите OK клавиша. Смотрите снимок экрана:

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

Внимание: вы сможете отразить / добавить несколько столбцов из обозначенного рабочего листа в Столбцы в вашем запросе поле и продолжайте последующие шаги.

7. Нажмите Next пару раз, пока не покажется диалоговое окно Импорт данных. В диалоговом окне «Импорт данных» установите флаг Настольные , потом укажите мотивированной спектр, в который вы разместите зеркальные данные, и щелкните OK клавиша. Смотрите снимок экрана:

И сейчас обозначенные столбцы из обозначенной книжки зеркалируются и связываются с текущей книжкой.

Примечание: Когда вы изменяете значения ячеек, добавляете / удаляете строчки в начальной книжке, зеркальные данные в мотивированном спектре поменяются автоматом опосля нажатия Данные > Обновить все.

Просто объединяйте несколько листов / книжек в один лист / книжку

Объединение 10-ов листов из различных книжек в один может оказаться мучительным занятием. Но с Kutools for Excel’s Соединить (рабочие листы и рабочие тетради) утилиту, вы сможете создать это всего за несколько кликов! Полнофункциональная бесплатная 30-дневная пробная версия!

Kutools for Excel — Включает наиболее 300 комфортных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

Отразите одну и ту же ячейку на нескольких листах при помощи Kutools for Excel

Этот способ представит Kutools for Excel’s Динамически обращаться к рабочим листам Утилита для пакетного зеркалирования одной и той же ячейки на нескольких листах на новейший лист в Excel.

1. Сделайте новейший рабочий лист, щелкнув значок НОВИНКИ клавиша or на панели вкладок листа.

2. Изберите ячейку на новеньком листе, содержимое ячейки которой вы скопируете с другого листа, и нажмите Kutools > Еще (в Формула группа)> Динамически ссылаться на рабочий лист. Смотрите снимок экрана:

3. В открывшемся диалоговом окне Fill Workbooks References, пожалуйста:
(1) Изберите Заполнить вертикально ячейку за ячейкой из Заполнить заказ раскрывающийся перечень;
(2) В Перечень рабочих листов раздел, проверьте рабочий лист, из которого вы будете отражать содержимое ячейки;
(3) Щелкните значок Спектр наполнения клавиши и Закрыть клавишу поочередно.

Тогда и вы увидите, что одни и те же ячейки из всех обозначенных листов зеркально отражаются на новеньком листе. Смотрите снимок экрана выше:

Зеркальное отражение нескольких диапазонов на нескольких листах при помощи Kutools for Excel

Этот способ представит Kutools for Excel’s Мастер печати множественных выделений утилита для отражения нескольких диапазонов из почти всех листов в виде изображения на новеньком листе.

1. Нажмите Предприятие > печать > Мастер печати множественных выделений. Смотрите снимок экрана:

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

Тогда и вы увидите, что все обозначенные спектры из нескольких листов отражаются как изображения на новеньком листе.

Связывание таблиц в Excel.

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

Изменение содержимого ячейки на одном листе либо таблице (источнике) рабочей книжки приводит к изменению связанных с ней ячеек в листах либо таблицах (приемниках). Этот принцип различает связывание листов от обычного копирования содержимого ячеек из 1-го листа в иной.

Зависимо от техники выполнения связывание бывает “прямым“и через командуСПЕЦИАЛЬНАЯ ВСТАВКА.

1 Метод – «Прямое связывание ячеек»

Прямое связываниелистов употребляется конкретно при вводе формулы в ячейку, когда в качестве 1-го из частей формулы употребляется ссылка на ячейку другого листа. К примеру, если в ячейке таблицы В4 на рабочем Листе2 содержится формула, которая употребляет ссылку на ячейку А4 другого рабочего листа (к примеру, Листа1) и оба листа загружены данными, то такое связывание листов именуется “прямым”.

Термин “прямое” связываниеобозначает, чтоюзер сам конкретно при вводе формулыпоказываетимя листаиабсолютный адресок ячейки, разбитые восклицательным знаком «!».

Примеры формул: = C5*Лист1! A4

= Лист1! A1- Лист2! A1

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

2 Метод – Связывание ячеек через команду «Особая вставка»

Связывание через команду СПЕЦИАЛЬНАЯ ВСТАВКАделается, если какая или ячейка таблицы на одном рабочем листе обязана содержать значение ячейки из другого рабочего листа.

Чтоб отразить в ячейке С4 на листе Стоимость значение ячейки Н4 на начальном листеЗакупка, необходимо поместить курсор на ячейку Н4 начального листа и выполнить командуПравка–Копировать. На листеСтоимостьпоставить курсор на ячейку С4, которую нужно связать с начальной, и выполнить командуПравка–Особая вставка–Вставить связь(см рис. 8). Тогда на листеСтоимостьпокажется указание на ячейку начального листа Закупка, к примеру:= Закупка!$Н$4

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

Задание. Свяжите ячейки С4, С5, С6, С7, С8 в таблицеРасходы на закупкуна листеСтоимостьс надлежащими ячейками на листеЗакупка, используя разные методы связывания ячеек (рис. 8).

Рис. 8 Связывание ячеек разных рабочих листов

! При связывании ячеек обусловьте, какие ячейки являются начальными.

! Для одной связываемой таблицы начальными могут быть ячейки из различных таблиц на разных рабочих листах либо на текущем листе.

Задания для самостоятельной работы.

Перед выполнением самостоятельного задания, разглядим на примере таблиц листа Стоимостьсвязывание ячеек.

на листе Стоимость в таблицеРасходы на закупкуячейки А4:А8 соединены с ячейками таблицыКоличество закупленной продукциина листеЗакупка;

ячейки В4:В8 являются начальными, т.к. содержат начальные сведения о ценах закупленного продукта;

ячейки С4:С8 соединены с ячейками Н4:Н8 на листе Закупка;

ячейки D4:D8 содержат формулы подсчета затраченных средств на обретенный продукт и ссылаются на ячейки своей таблицы (к примеру, формула в ячейкеD4 имеет вид =В4*С4, что значит умножение цены продукта на его количество);

ячейка D9 является суммой ячеекD4:D8;

во 2-ой таблице Расчет ценна этом же листе ячейки А14:А18 соединены аналогично п.1;

ячейки В14:В18 являются связанными с начальными ячейками текущего листа В4:В8;

ячейки С4:С8 являются начальными, т.к. содержат начальные сведения о наценке салона на закупленный продукт;

ячейки D14:D18 содержат формулы расчета цены реализации продукта и ссылаются на ячейки своей таблицы (к примеру, формула в ячейкеD14 имеет вид =В14*С14+В14, что значит умножение закупочной цены на установленный процент наценки, что дает сумму наценки, которую нужно прибавить к закупочной стоимости);

Опосля выполнения всех операций с этими таблицами произведите проверку их «работоспособности».

Изменитенаименование продукта –Диванчикв ячейке А4 на листеЗакупкана другое – к примеруСофа.

Изменитеколичество закупленного продуктаСофав июне (в ячейкеG4 на листеЗакупкавведите число 11).

Изменитестоимость закупки Софы в ячейке В4 на листеСтоимостьна другую – 2500,00 р.

Изменитепроцент наценки Софы в ячейке С14 на листеСтоимость с 50% на 32%.

Проверьте, произошли конфигурации в связанных таблицах либо нет?

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

Внимание! При связывании ячеек через СПЕЦИАЛЬНУЮ ВСТАВКУ. копирование на примыкающие ячейки становится проблематическим из-за абсолютной адресации ячеек.

Задание 1. Сделайте связывание ячеек других таблиц рабочей книжки, используя разные методы.

Указание. В таблицах по расчету выручки и дохода за 1 квартал употребляется формула суммирования начальных ячеек лишь 1 квартала.

Задание 2. Сделайте на листах Выручка и Доход таблицы по расчету за 2 квартал. Свяжите эти таблицы с надлежащими начальными данными.

Указание. В таблицах по расчету выручки и дохода за 2 квартал используйте начальные ячейки лишь 2 квартала.

Задание 3. Постройте радиальную диаграмму на листе Доход и проанализируйте распределение дохода по видам продукции.

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

Указание. В таблицах по расчету выручки и дохода за 2 квартал используйте начальные ячейки лишь 2 квартала.

Интересно почитать: Как в excel запаролить ячейки