Как объединить в экселе в таблицы. Консолидация (объединение) данных из нескольких таблиц в одну

Объединение пары таблиц Excel в одну

Если Для вас потребовалось объеденить несколько таблиц Excel в одну, то совсем не непременно для этого растрачивать свое время, открывая каждую из их и копируя её вручную. Для этого существует дополнение под Excel, с заглавием “RDBMerge”, которое автоматизирует данный процесс.

Объединение таблиц с помощью RDBMerge

На момент написания статьи, было представлено две версии данного дополнения – версия 1.3 (для Excel 97-2003) и версия 1.4 (для Excel 2007-2019). В данном материале речь пойдет конкретно про внедрение версии 1.4 в Microsoft Excel 2019, но описанные деяния так же применимы и к предшествующим версиям данной программы. Скачать их можно или с официального веб-сайта , или по ссылкам ниже.

RDBMerge 1.3, для Excel 97-2003
(зеркало на Яндекс)

RDBMerge 1.4, для Excel 2007-2019
(зеркало на Яндекс)

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

Установка в Excel 2010-2019

  1. Распаковываем подходящую версию дополнения в всякую директорию, на неизменное хранение – к примеру в папку ДокументыExcel.
  2. Нажимаем на пункт меню “Файл“, а в нем подпункт “Параметры“.
  3. Открываем пункт “Надстройки“.
  4. В самом низу находим пункт “Управление”, избираем в выпадающем меню значение “Надстройки Excel” (должно быть выбрано по умолчанию), жмем на клавишу “Перейти“.
  5. Откроется окно “Надстройки“. Тут необходимо надавить на клавишу “Обзор“, и выбрать ранее распакованный файл.
  6. После этого необходимо надавить клавишу “ОК“.
  7. После установки, на вкладке “Данные” должен покажется новейший пункт – “RDBMerge Add-in“. По нажатию на него будет вызвано основное окно дополнения.

Использование

  1. И так, представим, что необходимо объединить несколько файлов таблиц Excel в одну. Для начала, следует скопировать данные файлы в отдельную папку.
  2. Затем, вызываем окно “RDBMerge”, нажав на подобающую кнопку.
  3. В самом верху жмем клавишу “Folder location“, и избираем ранее сделанную папку с таблицами Excel. Ежели в данной папке есть подпапки с файлами, которые так же нужно объединить, отмечаем галочкой пункт “Include sub folders“.
  4. В разделе “Which Worksheet(s)” переключаем на “Merge All worksheets”. В разделе “Which Range” переключаем на First Cell. Ниже снимаем галочку у пт “Add file name”.
  5. По готовности жмем клавишу “Merge“.
  6. После этого раскроется таблица с данными из объединяемых файлов.

Была ли эта статья Для вас полезна?


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

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

Читайте также  Устройство для флешки в машину. FM-трансмиттеры (модуляторы)

Итак, есть два листа 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”. Формула будет смотреться так:

  • Пропустить 1-ые Х знаков, извлечь последующие Y символов: к примеру, необходимо извлечь “0123” из записи “PREFIX_0123_SUFF”. Тут нам необходимо пропустить 1-ые 8 знаков и извлечь последующие 4 знака. Формула будет смотреться так:

  • Извлечь все знаки до разделителя, длина получившейся последовательности может быть разной. К примеру, необходимо извлечь “123456” и “0123” из записей “123456-суффикс” и “0123-суффикс” соответственно. Формула будет смотреться так:

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

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

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

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

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

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

Читайте также  Синий экран смерти как исправить. Смертельный экран BSoD на Windows 7 – как решить проблему?

Теперь объединить данные из наших 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:

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

Оцените качество статьи. Нам принципиально ваше мнение:

Оставьте комментарий