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

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

Способ 1. С помощью формул

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

Необходимо объединить их все в одну общую таблицу, просуммировав совпадающие значения по кварталам и наименованиям.

Самый простой способ решения задачи «в лоб» — ввести в ячейку чистого листа формулу вида

=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3

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

Если листов очень много, то проще будет разложить их все подряд и использовать немного другую формулу:

=СУММ(‘2001 год:2003 год’!B3)

Фактически — это суммирование всех ячеек B3 на листах с 2001 по 2003, т.е. количество листов, по сути, может быть любым. Также в будущем возможно поместить между стартовым и финальным листами дополнительные листы с данными, которые также станут автоматически учитываться при суммировании.

Способ 2. Если таблицы неодинаковые или в разных файлах

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

Рассмотрим следующий пример. Имеем три разных файла (Иван.xlsx, Рита.xlsx и Федор.xlsx) с тремя таблицами:

Хорошо заметно, что таблицы не одинаковы — у них различные размеры и смысловая начинка. Тем не менее их можно собрать в единый отчет меньше, чем за минуту. Единственным условием успешного объединения (консолидации) таблиц в подобном случае является совпадение заголовков столбцов и строк. Именно по первой строке и левому столбцу каждой таблицы Excel будет искать совпадения и суммировать наши данные.

Для того, чтобы выполнить такую консолидацию:

  1. Заранее откройте исходные файлы
  2. Создайте новую пустую книгу (Ctrl + N)
  3. Установите в нее активную ячейку и выберите на вкладке (в меню) Данные — Консолидация(Data — Consolidate) . Откроется соответствующее окно:

  • Установите курсор в строку Ссылка(Reference) и, переключившись в файл Иван.xlsx, выделите таблицу с данными (вместе с шапкой). Затем нажмите кнопку Добавить(Add) в окне консолидации, чтобы добавить выделенный диапазон в список объединяемых диапазонов.
  • Повторите эти же действия для файлов Риты и Федора. В итоге в списке должны оказаться все три диапазона:

    Обратите внимание, что в данном случае Excel запоминает, фактически, положение файла на диске, прописывая для каждого из них полный путь (диск-папка-файл-лист-адреса ячеек). Чтобы суммирование происходило с учетом заголовков столбцов и строк необходимо включить оба флажка Использовать в качестве имен (Use labels) . Флаг Создавать связи с исходными данными (Create links to source data) позволит в будущем (при изменении данных в исходных файлах) производить пересчет консолидированного отчета автоматически.

    После нажатия на ОК видим результат нашей работы:

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

    Источник:
    http://www.planetaexcel.ru/techniques/2/138/

    Как сделать слияние таблиц в excel?

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

    Инструкция

    Устанавливаем себе надстройку ЁXCEL . Читаем справку.

    Приступаем к решению. Переходим во вкладку ЁXCEL главного меню, нажимаем кнопку «Таблицы», в выпавшем списке выбираем команду «Объединить таблицы»:

    В открывшемся диалоговом окне выделяем листы с таблицами, которые необходимо объединить и нажимаем «ОК»:

    Программа сформирует запрос — объединит таблицы и выведет информационное сообщение:

    Переходим на итоговый лист (тот где хотим вывести общую таблицу), устанавливаем курсор в ячейку «A1«. Переходим в главном меню во вкладку «Данные» в разделе «Получение внешних данных» нажимаем кнопку «Существующие подключения»:

    В открывшемся диалоговом окне выбираем «Подключения в этой книге» — «Запрос из Excel Files» и нажимаем «Открыть»:

    В открывшемся диалоговом окне устанавливаем переключатели в положения «Таблица» и «Имеющийся лист», нажимаем «ОК»:

    В активном листе будет создана таблица, которая будет объединять таблицы, расположенные на указанных нами листах:

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

    В итоговой таблице появятся строчки, добавленные в выбранный вами лист.

    Чтобы обновление итоговой таблицы происходило автоматически вставьте в модуль каждого листа (кроме Итогового) следующий код (Как вставлять макросы?):

    Видео-пример

    Важно:

    • Количество столбцов во всех таблицах должно быть одинаково;
    • Кроме таблиц на листах не должно быть никакой информации;
    • Если вы переместили файл в другую папку или отправили файл коллеге по электронной почте — необходимо заново связать таблицы (в запросе прописывается абсолютный путь к файлу).

    Чтобы запрос работал не зависимо от того в какой папке лежит файл вставьте в модуль «ЭтаКнига» следующий код:

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

    Чтобы оценить всю прелесть — выньте файл из архива и при загрузке файла включите макросы.

    Возможные ошибки при использовании этого метода:

    • В таблицах одинаковое количество столбцов, но при попытке использовать запрос получаем сообщение: «В таблицах или запросах, выбранных в запросе на объединение, не совпадает число столбцов.» Причина: в одном или нескольких листах с таблицами есть пустые столбцы (в них раньше были данные) и MS Excel считает их столбцами таблиц. Решение: Выделить и удалить все пустые столбцы на листах с таблицами.
    • Все столбцы удалили, но ошибка осталась. Причина: файл сохранен на сетевом диске или открыт из почтовой программы. Решение: сохраните файл у себя на локальном компьютере, выполните запрос, вставьте в него макрос меняющий путь в запросе и выложите обратно в сеть (если нужно).
    • В полученном запросе в некоторых ячейках пропадают данные. Причина: в ваших таблицах встречаются столбцы, которые одновременно содержатся и числовые и текстовые значения. MS Excel считает, что эти столбцы должны содержать только числа и не выводит текст. Решение:преобразуйте все числовые значения в этих столбцах во всех таблицах в текстовые. Обновите запрос — текстовые данные появятся.

    Источник:
    http://e-xcel.ru/index.php/khitrosti/kak-ob-edinit-dve-tablitsy-i-bolee-v-odnu

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

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

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

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

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

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

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

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

    Ключевой столбец в одной из таблиц содержит дополнительные символы

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

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

    Ключевым в таблице в нашем примере является столбец A с данными SKU, и нужно извлечь из него первые 5 символов. Добавим вспомогательный столбец и назовём его SKU helper:

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

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

  • Копируем эту формулу во все ячейки нового столбца.
  • Готово! Теперь у нас есть ключевые столбцы с точным совпадением значений – столбец SKU helper в основной таблице и столбец SKU в таблице, где будет выполняться поиск.

    Теперь при помощи функции ВПР (VLOOKUP) мы получим нужный результат:

    Другие формулы

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

    =ПРАВСИМВ(A2;6)
    =RIGHT(A2,6)
    Пропустить первые Х символов, извлечь следующие Y символов: например, нужно извлечь “0123” из записи “PREFIX_0123_SUFF”. Здесь нам нужно пропустить первые 8 символов и извлечь следующие 4 символа. Формула будет выглядеть так:

    =ПСТР(A2;8;4)
    =MID(A2,8,4)
    Извлечь все символы до разделителя, длина получившейся последовательности может быть разной. Например, нужно извлечь “123456” и “0123” из записей “123456-суффикс” и “0123-суффикс” соответственно. Формула будет выглядеть так:

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

    Данные из ключевого столбца в первой таблице разбиты на два или более столбца во второй таблице

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

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

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

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

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

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

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

    Плохая новость: Данные, содержащиеся в этих двух таблицах 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:

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

    Источник:
    http://office-guru.ru/excel/kak-obedinit-dve-tablicy-excel-po-chastichnomu-sovpadeniyu-jacheek-435.html

    Консолидация данных в Excel

    Консолидация данных – опция в Excel, которая позволяет объединять данные из разных таблиц в одну, а также объединять листы, расположенные в одном или разных файлах, в один. Чем же так полезна консолидация для интернет-маркетолога?

    Разберем два наглядных примера.

    Пример №1

    У нас есть статистика по ключевым словам из контекстной рекламы с привязкой к основным показателям – кликам, расходам, транзакциям и доходу.

    Специальный отчет в Google Analytics

    Выгрузив данные в Excel, мы увидим, что в таблице есть строки, которые «по сути» являются дублями.

    «Дубли» ключевых слов в статистике

    Это связано с тем, что статистика выгружена из двух рекламных систем (Яндекс.Директ и Google Ads), и в Google Рекламе для ключевых слов используется модификатор широкого соответствия (знак «+»).

    То есть, вместо двух строчек доставка цветов москва и +доставка +цветов +москва мы должны получить одну с объединенными данными. Ведь это один ключ и нам нужно оценить его эффективность.

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

    Суммирование данных вручную

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

    Но перед этим нужно не забыть воспользоваться функцией Заменить, убрав все + из таблицы.

    Удаляем + в ключевых словах перед консолидацией

    — переходим на соседний лист (так удобнее);

    — выделяем ячейку, в которую хотим вставить данные;

    — переходим в Данные — Консолидация

    В открывшемся окне нас интересуют следующие настройки:

    • ФункцияСумма (поскольку хотим суммировать данные);
    • Ссылка – выбираем весь диапазон данных на соседнем листе;
    • Использовать в качестве имен – ставим галочки для подписи верхней строки и значения левого столбца.

    Нажимаем ОК. Если все сделали верно, то на текущем листе получите результат в виде объединенных (консолидированных) данных:

    Итоговая таблица после консолидации

    Таблица наша также уменьшилась. До консолидации было 252 строки, после объединения данных их стало 212:

    ДО и ПОСЛЕ консолидации

    Видео консолидации примера №1:

    Консолидация данных в Excel

    Пример №2

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

    Да, можно просто выгрузить данные из рекламных кабинетов Яндекс.Директ и Google Ads за общий период. Но что делать, когда данные по показам, кликам, расходам и конверсиям привязаны к бизнес-показателям, которых нет в интерфейсах, но которые завязаны на контекстной рекламе? А если в каждый из этих месяцев работали разные рекламные кампании (в один месяц – 10, в другой – 5 и т.д.)? Опять ручной процесс сведения данных.

    И для таких случаев пригодится Консолидация в Excel. Давайте сведем данные с помощью данной настройки на примере выгрузки интернет-магазина за 3 месяца в один отчет. Статистика по месяцам расположена на разных вкладках (сентябрь — ноябрь — декабрь).

    Статистика по 3 месяцам на разных вкладках файла

    Мы хотим свести данные в одну таблицу. Воспользуемся консолидацией.

    В открывшемся окне нас интересуют следующие настройки:

    • ФункцияСумма (поскольку хотим суммировать данные);
    • Ссылка – выбираем весь диапазон данных на листах поочередно (с помощью кнопки Добавить);
    • Использовать в качестве имен – ставим галочки для подписи верхней строки и значения левого столбца.

    Галочка Создавать связи с исходными данными позволит производить пересчет консолидированного отчета автоматически при изменении данных в исходных файлах (в нашем примере – это листы сентябрь, октябрь, ноябрь). Нажимаем ОК.

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

    Слева появятся новые значки:

    Связи с исходными данными

    • 1 – свернуть все связи с исходными данными;
    • 2 – развернуть все связи с исходными данными.

    При нажатии на + раскроется соответствующая строка, в которой будут отображены данные по каждому из исходных листов.

    Видео консолидации примера №2:

    Консолидация данных в Excel

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

    Понравился эксперимент с видео? Поставь 5.0 статье ->

    Источник:
    http://osipenkov.ru/consolidate-excel/

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

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

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

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

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

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

    Если Вы не являетесь профессионалом по 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. Адрес второй ячейки.

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

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

    Теперь можно легко объединить данные с нескольких электронных таблиц. Мы сравниваем колонку «Полный идентификатор» с колонкой «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. Хотя оно и коммерческое, но первые дни его можно использовать бесплатно. Если нет необходимости постоянно объединять таблицы с разными данными, то и нет смысла изучать эту кучу формул. Достаточно раз воспользоваться дополнением. А если нужно регулярно объединять таблицы, этот аддон поможет сэкономить много времени.

    Источник:
    http://office-faq.ru/kak-obedinit-tabliczy-s-chastichnym-sovpadeniem/

    Как сделать слияние таблиц в excel?

    Если вы ещё не знакомы со сводными таблицами, то начните с этой статьи.

    Бывает так, что анализируемые данные попадают к нам в виде отдельных таблиц, которые, тем не менее, нужно связать. Это легко может сделать MS Access, а в Excel для этого приходилось всегда использовать формулы типа ВПР (VLOOKUP). Однако, начиная с Excel 2013, у нас появилась возможность при построении сводной таблицы в качестве источника использовать несколько таблиц, связанных между собой по ключевым полям.

    В нашем примере мы располагаем 4-мя таблицами: Заказы , Строки заказов , Товары , Клиенты .

    Таблица Строк заказов:

    Исходные таблицы оформлены в виде умных таблиц: Orders , OrderLines , Goods и Clients .

    Вполне очевидно, что таблицы Orders и OrderLines могут быть связаны по полю ID_Заказа , таблицы Orders и Clients — по полю ID_клиента , таблицы OrderLines и Goods — по полю ID_товара .

    Скачать пример

    Создание модели данных

    Создадим сводную таблицу на основе любой из имеющихся таблиц.

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

    В появившейся панеле Поля сводной таблицы вы видите надпись ДРУГИЕ ТАБЛИЦЫ.

    Нажмём её. Появится такой вопрос:

    Отвечаем Да и видим, что в список полей добавились все наши таблицы:

    Если вы начнёте выбирать поля, то через некоторое время в списке полей появится кнопка СОЗДАТЬ.

    Нажмём её и создадим связи между нашими таблицами. Так создаётся связь между таблицей Orders и OrderLines . Обратите внимание, что Excel умеет создавать связь типа » один к одному » или » один ко многим «. Причём первой надо указывать таблицу, где «много», в противном случае Excel ругается и предлагает поменять их местами.

    Аналогично создаём другие связи.


    В диалоговое окно Управление связями можно попасть через ленту АНАЛИЗ команда Отношения

    Чтобы видеть больше полей на панеле Поля сводной таблицы , можно через кнопку Сервис (в виде шестерёнки) выбрать это представление:

    Результат будет таким:

    В результате все наши таблицы теперь связаны и вы можете сформировать, к примеру, такой отчёт:

    Источник:
    http://perfect-excel.ru/publ/excel/svodnye_tablicy/svodnaja_tablica_na_osnove_dvukh_i_bolee_svjazannykh_tablic/5-1-0-67