Newcomposers.ru

IT Мир
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Соединение двух таблиц в excel

Быстрое объединение больших таблиц (VLOOKUP2D, ВПР2D)

Если вы знакомы с функцией VLOOKUP (ВПР) или ее горизонтальным аналогом HLOOKUP (ГПР) , то должны помнить, что эти замечательные функции ищут информацию только по одному параметру, то есть в одномерном массиве — по строке или по столбцу. А если нам необходимо выбирать данные из двумерной таблицы по стечению сразу двух параметров — и по строке и по столбцу одновременно? Давайте рассмотрим несколько вариантов объединения таблиц.

Вариант 1. Соединение двух таблиц (используем функции INDEX и MATCH)

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

Мы можем начать использовать функцию VLOOKUP , чтобы соединить каждый отдельный столбец, однако если у нас таблицы имеют чрезвычайно много строк и столбцов, то это занятие может превратиться в настоящую муку. Однако из этой ситуации есть достаточно простой выход, поскольку Excel имеет следующие две замечательные функции, как INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ) из категории Ссылки и массивы (Lookup and Reference) , что в паре работают как 2D VLOOKUP.

Хорошо, что нам необходимо сделать, чтобы быстро соединить две таблицы? Давайте для начала скопируем шапку второй таблицы (ту, что мы будем присоединять) и вставим ее рядом с шапкой первой таблице. По названию шапки функция MATCH (ПОИСКПОЗ) будет выдавать нам порядковый номер колонки, а по номеру сделки — необходим нам порядковый номер строки.

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

  • Во-первых, нам нужно определить номер строки, соответствующей выбранной кредитной сделке. Это поможет сделать функция MATCH (ПОИСКПОЗ) из категории Ссылки и массивы (Lookup and Reference ) . В частности, формула MATCH (A14;K2:K18;0) даст нам нужен результат (для соглашения «№ 5398» это будет порядкоий номер 13). Первый аргумент этой функции — искомое значение (соглашение «№ 5398» из ячейки А14 ), второй — диапазон ячеек, где мы ищем соглашение (столбец с номерами сделок в таблице — К2:К18 ), третий аргумент задает тип поиска ( — точное совпадение наименования, приблизительный поиск запрещен).
  • Во-вторых, совершенно аналогичным образом мы должны определить порядковый номер столбца в таблице с нужным нам содержанием. Функция MATCH (F1;J1:M1;0) сделает это и выдаст, например, для Collateralname, выбранного пользователем в ячейке F1 значение 3.
  • И, наконец, в-третьих, нам нужна функция, которая умеет выдавать содержимое ячейки из таблицы по номеру строки и столбца — функция INDEX (ИНДЕКС) из той же категории Ссылки и массивы (Lookup and Reference) . Первый аргумент этой функции — диапазон ячеек (в нашем случае это вся таблица, т.е. J2:M18), второй — номер строки, третий — номер столбца (а их мы определим с помощью функций MATCH).

Итак, сочетая все вышеперечисленное в одну формулу, получаем для ячейки F14 следующую формулу:

=INDEX( J2:M18 ;MATCH( A14 ; K2:K18 ;0);MATCH( F1 ; J1:M1 ;0))

або =ИНДЕКС(J2:M18;ПОИСКПОЗ(A14;K2:K18;0);ПОИСКПОЗ(F1;J1:M1;0))

Таким образом, растягивая нашу формулу на весь диапазон, мы правильно присоединим дополнительные поля. Также нужно обратить внимание, что при растягивании формулы, нам нужно зафиксировать диапазоны знаком доллара ($) (для фиксирования можно также использовать клавишу F4 ), для поиска по шапке фиксируем только строку ( F$1 ), для поиска по номерам сделок — только колонку ( $A14 ).

Вариант 2. Соединение двух таблиц (используем функцию VLOOKUP)

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

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

Итак, формула для нашего второго варианта будет следующей:

=VLOOKUP( $A3 ; $J$3:$M$19 ; E$1 ;0)

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

Соединение двух таблиц в excel

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

Инструкция

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Через что смотреть видео

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

Важно:

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

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

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

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

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

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

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

Способ 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) позволит в будущем (при изменении данных в исходных файлах) производить пересчет консолидированного отчета автоматически.

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

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

    Как объединить две таблицы 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:

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

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

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

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

    Как построить сводную таблицу на основе данных нескольких источников? Есть несколько вариантов.

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

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

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

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

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

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

    Power Query предлагает на выбор шесть различных способов объединения:

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

    Разберемся, какие именно способы объединения таблиц есть в Power Query.

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

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

    Внешнее соединение слева
    Left Outer Join

    Ссылка на основную публикацию
    Adblock
    detector