Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Еще…Меньше
Вы можете объединить строки из одной таблицы в другую, просто вклеив данные в первые пустые ячейки под целевой таблицей. Таблица увеличится в размере, чтобы включить в нее новые строки. Если строки в обеих таблицах совпадают, вы можете объединить столбцы одной таблицы с другой, вклеив их в первые пустые ячейки справа от таблицы. В этом случае таблица также увеличится вместить новые столбцы.
Слияние строк на самом деле очень просто, но слияние столбцов может быть непросто, если строки одной таблицы не соответствуют строкам в другой таблице. Некоторых проблем с выравниванием можно избежать, если воспользоваться функцией ВПР.
Объединение двух таблиц с помощью функции ВЛОП
В приведенного ниже примере вы увидите две таблицы с другими именами: «Синяя» и «Оранжевая». В таблице «Синяя» каждая строка представляет собой позицию заказа. Например, заказ № 20050 содержит две позиции, № 20051 — одну, № 20052 — три и т. д. Мы хотим объединить столбцы «Код продажи» и «Регион» с таблицей «Синяя» с учетом соответствия значений в столбце «Номер заказа» таблицы «Оранжевая».
Значения «ИД заказа» повторяются в таблице «Синяя», но значения «ИД заказа» в таблице «Оранжевая» уникальны. Если просто скопировать и ввести данные из таблицы «Оранжевая», значения «ИД продаж» и «Регион» для второй строки заказа 20050 будут отключены на одну строку, что изменит значения в новых столбцах таблицы «Синяя».
Вот данные для таблицы «Синяя», которую можно скопировать на пустой лист. После в таблицы нажмите CTRL+T, чтобы преобразовать ее в таблицу, а затем переименуйте таблицу Excel синюю.
|
Номер заказа |
Дата продажи |
Код продукта |
|---|---|---|
|
20050 |
02.02.2014 |
C6077B |
|
20050 |
02.02.2014 |
C9250LB |
|
20051 |
02.02.2014 |
M115A |
|
20052 |
03.02.2014 |
A760G |
|
20052 |
03.02.2014 |
E3331 |
|
20052 |
03.02.2014 |
SP1447 |
|
20053 |
03.02.2014 |
L88M |
|
20054 |
04.02.2014 |
S1018MM |
|
20055 |
05.02.2014 |
C6077B |
|
20056 |
06.02.2014 |
E3331 |
|
20056 |
06.02.2014 |
D534X |
Вот данные для таблицы «Оранжевая». Скопируйте его на тот же самый таблицу. После в таблицы нажмите CTRL+T, чтобы преобразовать ее в таблицу, а затем переименуйте таблицу в Оранжевая.
|
Номер заказа |
Код продажи |
Регион |
|---|---|---|
|
20050 |
447 |
Запад |
|
20051 |
398 |
Юг |
|
20052 |
1006 |
Север |
|
20053 |
447 |
Запад |
|
20054 |
885 |
Восток |
|
20055 |
398 |
Юг |
|
20056 |
644 |
Восток |
|
20057 |
1270 |
Восток |
|
20058 |
885 |
Восток |
Нам необходимо обеспечить правильное выравнивание значений «ИД продаж» и «Регион» для каждого заказа с каждым уникальным элементом строки заказа. Для этого впустим заголовки таблицы «ИД продажи» и «Регион» в ячейки справа от таблицы «Синяя», а затем с помощью формулЫ ВЗ ПРОСМОТР выберем правильные значения из столбцов «ИД продажи» и «Регион» таблицы «Оранжевая».
Вот как это сделать.
-
Скопируйте заголовки «ИД продажи» и «Регион» в таблице «Оранжевая» (только эти две ячейки).
-
В ячейку справа от заголовка «ИД товара» таблицы «Синяя».
Теперь таблица «Синяя» содержит пять столбцов, включая новые — «Код продажи» и «Регион».
-
В таблице «Синяя», в первой ячейке столбца «Код продажи» начните вводить такую формулу:
=ВПР(
-
В таблице «Синяя» выберите первую ячейку столбца «Номер заказа» — 20050.
Частично заполненная формула выглядит так:
Выражение [@[Номер заказа]] означает, что нужно взять значение в этой же строке из столбца «Номер заказа».
Введите точку с запятой и выделите всю таблицу «Оранжевая» с помощью мыши. В формулу будет добавлен аргумент Оранжевая[#Все].
-
Введите точку с запятой, число 2, еще раз точку с запятой, а потом 0, вот так: ;2;0
-
Нажмите клавишу ВВОД, и законченная формула примет такой вид:
Выражение Оранжевая[#Все] означает, что нужно просматривать все ячейки в таблице «Оранжевая». Число 2 означает, что нужно взять значение из второго столбца, а 0 — что возвращать значение следует только в случае точного совпадения.
Обратите внимание: Excel заполняет ячейки вниз по этому столбцу, используя формулу ВПР.
-
Вернитесь к шагу 3, но в этот раз начните вводить такую же формулу в первой ячейке столбца «Регион».
-
На шаге 6 вместо 2 введите число 3, и законченная формула примет такой вид:
Между этими двумя формулами есть только одно различие: первая получает значения из столбца 2 таблицы «Оранжевая», а вторая — из столбца 3.
Теперь все ячейки новых столбцов в таблице «Синяя» заполнены значениями. В них содержатся формулы ВПР, но отображаются значения. Возможно, вы захотите заменить формулы ВПР в этих ячейках фактическими значениями.
-
Выделите все ячейки значений в столбце «Код продажи» и нажмите клавиши CTRL+C, чтобы скопировать их.
-
На вкладке Главная щелкните стрелку под кнопкой Вставить.
-
В коллекции параметров вставки нажмите кнопку Значения.
-
Выделите все ячейки значений в столбце «Регион», скопируйте их и повторите шаги 10 и 11.
Теперь формулы ВПР в двух столбцах заменены значениями.
Дополнительные сведения о таблицах и функции ВПР
-
Как добавить или удалить строку или столбец в таблице
-
Использование структурированных ссылок в формулах таблиц Excel
-
Использование функции ВПР (учебный курс)
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Нужна дополнительная помощь?
Из этой статьи Вы узнаете, как быстро объединить данные из двух таблиц Excel, когда в ключевых столбцах нет точных совпадений. Например, когда уникальный идентификатор из первой таблицы представляет собой первые пять символов идентификатора из второй таблицы. Все предлагаемые в этой статье решения протестированы мной в Excel 2013, 2010 и 2007.
Итак, есть два листа Excel, которые нужно объединить для дальнейшего анализа данных. Предположим, в одной таблице содержатся цены (столбец Price) и описания товаров (столбец Beer), которые Вы продаёте, а во второй отражены данные о наличии товаров на складе (столбец In stock). Если Вы или Ваши коллеги составляли обе таблицы по каталогу, то в обеих должен присутствовать как минимум один ключевой столбец с уникальными идентификаторами товаров. Описание товара или цена могут изменяться, но уникальный идентификатор всегда остаётся неизменным.
Трудности начинаются, когда Вы получаете некоторые таблицы от производителя или из других отделов компании. Дело может ещё усложниться, если вдруг вводится новый формат уникальных идентификаторов или самую малость изменятся складские номенклатурные обозначения (SKU). И перед Вами стоит задача объединить в Excel новую и старую таблицы с данными. Так или иначе, возникает ситуация, когда в ключевых столбцах имеет место только частичное совпадение записей, например, «12345» и «12345-новый_суффикс«. Вам-то понятно, что это тот же SKU, но компьютер не так догадлив! Это не точное совпадение делает невозможным использование обычных формул Excel для объединения данных из двух таблиц.
И что совсем плохо – соответствия могут быть вовсе нечёткими, и «Некоторая компания» в одной таблице может превратиться в «ЗАО «Некоторая Компания»» в другой таблице, а «Новая Компания (бывшая Некоторая Компания)» и «Старая Компания» тоже окажутся записью об одной и той же фирме. Это известно Вам, но как это объяснить Excel?
Выход есть всегда, читайте далее и Вы узнаете решение!
Замечание: Решения, описанные в этой статье, универсальны. Вы можете адаптировать их для дальнейшего использования с любыми стандартными формулами, такими как ВПР (VLOOKUP), ПОИСКПОЗ (MATCH), ГПР (HLOOKUP) и так далее.
Выберите подходящий пример, чтобы сразу перейти к нужному решению:
- Ключевой столбец в одной из таблиц содержит дополнительные символы
- Данные из ключевого столбца в первой таблице разбиты на два или более столбца во второй таблице
- Данные в ключевых столбцах не совпадают (123-SDX и HFGT-23) или есть частичное совпадение, меняющееся от ячейки к ячейке (Coca Cola и Coca-Cola Inc.)
Содержание
- Ключевой столбец в одной из таблиц содержит дополнительные символы
- Другие формулы
- Данные из ключевого столбца в первой таблице разбиты на два или более столбца во второй таблице
- Данные в ключевых столбцах не совпадают
- 1. Создаём вспомогательную таблицу для поиска.
- 2. Обновляем главную таблицу при помощи данных из таблицы для поиска.
- 3. Переносим данные из таблицы поиска в главную таблицу
Ключевой столбец в одной из таблиц содержит дополнительные символы
Рассмотрим две таблицы. Столбцы первой таблицы содержат номенклатурный номер (SKU), наименование пива (Beer) и его цену (Price). Во второй таблице записан SKU и количество бутылок на складе (In stock). Вместо пива может быть любой товар, а количество столбцов в реальной жизни может быть гораздо больше.
В таблице с дополнительными символами создаём вспомогательный столбец. Можно добавить его в конец таблицы, но лучше всего вставить его следующим справа после ключевого столбца, чтобы он был на виду.
Ключевым в таблице в нашем примере является столбец A с данными SKU, и нужно извлечь из него первые 5 символов. Добавим вспомогательный столбец и назовём его SKU helper:
- Наводим указатель мыши на заголовок столбца B, при этом он должен принять вид стрелки, направленной вниз:
- Кликаем по заголовку правой кнопкой мыши и в контекстном меню выбираем Вставить (Insert):
- Даём столбцу имя SKU helper.
- Чтобы извлечь первые 5 символов из столбца SKU, в ячейку B2 вводим такую формулу:
=ЛЕВСИМВ(A2;5)
=LEFT(A2,5)Здесь 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-суффикс» соответственно. Формула будет выглядеть так:
=ЛЕВСИМВ(A2;НАЙТИ("-";A2)-1)
=LEFT(A2,FIND("-",A2)-1)
Одним словом, Вы можете использовать такие функции Excel, как ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT), ПСТР (MID), НАЙТИ (FIND), чтобы извлекать любые части составного индекса. Если с этим возникли трудности – свяжитесь с нами, мы сделаем всё возможное, чтобы помочь Вам.
Данные из ключевого столбца в первой таблице разбиты на два или более столбца во второй таблице
Предположим, таблица, в которой производится поиск, содержит столбец с идентификаторами. В ячейках этого столбца содержатся записи вида XXXX-YYYY, где XXXX – это кодовое обозначение группы товаров (мобильные телефоны, телевизоры, видеокамеры, фотокамеры), а YYYY – это код товара внутри группы. Главная таблица состоит из двух столбцов: в одном содержатся коды товарных групп (Group), во втором записаны коды товаров (ID). Мы не можем просто отбросить коды групп товаров, так как один и тот же код товара может повторяться в разных группах.
Добавляем в главной таблице вспомогательный столбец и называем его Full ID (столбец C), подробнее о том, как это делается рассказано ранее в этой статье.
В ячейке C2 запишем такую формулу:
=СЦЕПИТЬ(A2;"-";B2)
=CONCATENATE(A2,"-",B2)
Здесь 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:
Всё просто, не так ли? Задавайте свои вопросы в комментариях к статье, я постараюсь ответить, как можно скорее.
Оцените качество статьи. Нам важно ваше мнение:
Skip to content
В этом руководстве вы найдете некоторые приемы объединения таблиц Excel путем сопоставления данных в одном или нескольких столбцах.
Как часто при анализе в Excel вся необходимая информация собирается на одном листе? Сейчас это довольно редкое явление. Чаще всего разные фрагменты данных рассредоточены по множеству листов и книг. К счастью, есть несколько способов объединить информацию из нескольких таблиц в одну, и это руководство научит вас делать это быстро и эффективно.
Объединение нескольких таблиц — одна из самых сложных задач в Excel. Если вы решите сделать это вручную, вы можете потратить часы только на то, чтобы обнаружить, что испортили важную информацию. Если вы опытный специалист в области Excel, то можете положиться на формулы ВПР и ИНДЕКС ПОИСКПОЗ. Вы также можете использовать Power Query или дополнительные надстройки к Excel. Выбор остается за вами.
- Суммирование данных
- Использование ВПР
- Применение ИНДЕКС + ПОИСКПОЗ
- Формула объединения для нескольких ключевых столбцов
- Объединение таблиц при помощи Power Query
- Быстрый способ объединить таблицы путем сопоставления ключевых столбцов.
Как объединить две таблицы в Excel с помощью формул.
Если вам нужно выполнить какую-то задачу в Excel, то где вы в первую очередь ищете решение? Как и многие пользователи, я обычно открываю список функций. Слияние таблиц не исключение 
Суммирование.
Начнем с самого простого примера, когда нужно объединить данные нескольких таблиц путем их суммирования. Предположим, у нас есть сведения о продажах за 4 квартала. Нужно найти их итоговую сумму.
Сразу оговоримся, что этот метод подходит в основном для форм отчетности, когда таблицы имеют одинаковую структуру, и цифры в них расположены в совершенно одинаковом порядке.
Поэтому сложение значений соответствующих ячеек на каждом листе здесь подходит отлично.
Для создания отчета о годовых продажах можно использовать самую простую формулу сложения.
=’1кв’!C3+’2кв’!C3+’3кв’!C3+’4кв’!C3
Более технологичным вариантом является так называемая 3D сумма —
=СУММ(‘1кв:4кв’!C3)
Мы указываем первый и последний лист, а также координаты ячейки. Все промежуточные координаты Эксель находит сам. Если между первым и последним листом добавить еще несколько листов с аналогичными показателями, то они также будут просуммированы.
Конечно, это не в полном смысле объединение таблиц, так как набор показателей не меняется, новые строки и колонки не добавляются. Но при этом объединяются имеющиеся данные. Далее рассмотрим более сложные случаи — когда нужно объединить неодинаковые таблицы.
Используем ВПР.
Если вы хотите объединить две таблицы на основе одного столбца , то функция ВПР – то, что нужно.
Предположим, у вас есть две таблицы на двух разных листах: основная содержит коды заказов и покупателей (то есть, кому вы продаёте), а во второй записаны заказы и товары (то есть, что вы продаёте). Вы хотите объединить их, сопоставив данные в колонке Код заказа:
То есть, в случае совпадения заказов информация о товаре будет добавлена в первую таблицу в соответствующую строку.
Как видите, порядок следования заказов в основной таблице не соответствует второй. Поэтому простой метод копирования / вставки не сработает.
Чтобы нам было проще ориентироваться, используем именованные диапазоны. Первый назовём «Заказ», второй – «Товар».
Чтобы объединить две таблицы по соответствующему показателю (Код заказа), в начале добавляем дополнительные колонки E, F и G, которые будем заполнять нужной информацией.
Начнём с товара. Введите эту формулу в ячейку Е2, как показано на скриншоте ниже:
=ВПР(A2;товар;2;0)
или с обычными ссылками —
=ВПР(A2;Товар!$A$2:$D$200;2;0)
Далее в G2 запишем —
=ВПР(B2;товар;3;0)
В H2 аналогично —
=ВПР(B2;товар;4;0)
Копируем эти формулы вниз и получаем результат объединения:
Объединенная таблица состоит из основной и добавленных данных, извлеченных из массива поиска.
Имейте в виду, что ВПР в Excel имеет несколько ограничений, наиболее важными из которых являются:
1) невозможность извлечения данных, находящихся слева от столбца поиска,
2) жестко заданный номер столбца сломает формулу при добавлении или удалении колонок во второй таблице. С другой стороны, в случае подобных изменений вы можете легко изменить порядок возвращаемых колонок, просто изменив число в третьем аргументе функции.
Рассмотрим более сложный случай, когда сведения были получены нами из разных источников, и ключевой показатель (код заказа) у них не полностью совпадает. К примеру, к нему добавлены какие-то символы.
В случае использования функции ВПР, у нас нет другого выхода кроме создания вспомогательного столбца. В нём мы постараемся привести наш код заказа к стандартному виду с помощью различных функций работы с текстом.
Итак, вставим в нашу таблицу заказов вспомогательный столбец B. Для этого кликнем правой кнопкой мыши по заголовку колонки B и в появившемся контекстном меню выберем «Вставить». Получим пустую колонку, а все остальные сдвинутся вправо.
Здесь мы и «очистим» наши номера заказов от лишнего мусора. В нашем случае вполне подойдёт формула:
=ЛЕВСИМВ(A2;4)
A2 — это адрес ячейки, из которой мы извлекаем первые 4 символа.
Если, например, мы имеем “Б-1007”, то используйте формулу
=ПРАВСИМВ(A2;4)
Но ведь наши идентификаторы могут выглядеть и по-другому.
Скажем, нужно пропустить первые Х символов, извлечь следующие Y символов: например, нужно извлечь “0123” из записи “СББЛ-1007-ШКЛ”. Здесь нам нужно пропустить первые пять символов и извлечь следующие четыре. Формула будет выглядеть так:
=ПСТР(A2;5;4)
Если нужно извлечь все знаки до разделителя, причем количество их может быть разным (“1007-СРР 256” или “10071007-Б111НРР”), то действуем так —
=ЛЕВСИМВ(A2;НАЙТИ(«-«;A2)-1)
Получаем соответственно 1007 и 10071007.
Если нужно получить последнее слово после разделителя “Б-С-100777”, причем когда количество символов в нем может быть разным —
=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(«*»;ПОДСТАВИТЬ(A2;»-«;»*»;ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;»-«;»»)))))
Получим 100777.
Словом, текстовые функции помогут нам заполнить вспомогательный столбец.
А затем мы применяем те же формулы, что и ранее, только меняем ссылку на ячейку поиска:
=ВПР(B2;товар;2;0)
или
=ВПР(B2;Товар!$A$2:$D$200;2;0)
Как видите, при помощи дополнительной колонки мы решили проблему несовпадения ключевых кодов и успешно объединили данные.
Комбинация ИНДЕКС + ПОИСКПОЗ.
Если вы ищете более мощную и универсальную альтернативу функции ВПР, воспользуйтесь комбинацией функций ИНДЕКС и ПОИСКПОЗ.
ИНДЕКС( диапазон_возврата ; ПОИСКПОЗ ( значение_подстановки ; диапазон_просмотра ; 0))
Если вернуться к предыдущему примеру с функцией ВПР, то формулу
ВПР(A2;Товар!$A$2:$D$200;2;0) в ячейке Е2 можно заменить на
=ИНДЕКС(‘Товар’!$B$2:$B$200;ПОИСКПОЗ(A2;’Товар’!$A$2:$A$200;0))
Казалось бы, ничего особенного, только сложнее стало. Однако, здесь открывается гораздо больше возможностей. Если будет необходимо, мы сможем провести поиск в колонке B и вернуть соответствующее значение из А. То есть, порядок расположения столбцов в этом случае не важен. ВПР нам не позволить сделать такое.
Вот, например, если во второй таблице показатели будут расположены как на скриншоте внизу, то функция ВПР нам здесь не поможет.
Выражение в ячейке C2 выглядит так:
=ИНДЕКС(‘Товар’!$A$2:$A$20;ПОИСКПОЗ(A2;’Товар’!$B$2:$B$20;0))
Это так называемый «левый ВПР», который не зависит от взаимного расположения столбцов.
Обратите внимание на знак $, который фиксирует диапазоны, чтобы предотвратить их изменение при копировании формулы.
В случае нечёткого совпадения кодов заказов, который мы рассматривали выше, комбинация ИНДЕКС + ПОИСКПОЗ позволяет обойтись без вспомогательной колонки.
Нам поможет формула массива
{=ИНДЕКС(Товар!$B$2:$B$200;ПОИСКПОЗ(1;ПОИСК(ЛЕВСИМВ(A2;4);Товар!$A$2:$A$200;1);0))}
Как видите, мы берём первые 4 символа из номера заказа и ищем совпадения в товарах.
Естественно, вместо ЛЕВСИМВ мы можем использовать другие варианты текстовых функций. Мы о них подробно говорили выше, когда рассматривали возможное содержание вспомогательного столбца. При помощи формулы массива мы как бы вставляем его целиком прямо в формулу поиска.
Как объединить на основе нескольких столбцов.
Если две таблицы, которые вы хотите объединить, не имеют уникального идентификатора, такого как номер заказа, вы можете сопоставить значения в двух или более колонках как в ключевых, используя эту формулу:
ИНДЕКС( таблица_поиска ; ПОИСКПОЗ(1; ( искомое_значение1 = диапазон_поиска1 ) * ( искомое_значение2 = диапазон_поиска2 ); 0); номер_возвращаемого_столбца )
Это формула массива, поэтому не забудьте нажать Ctrl + Shift + Enter для правильного её ввода.
Предположим, у вас есть две таблицы, которые нужно объединить в одну. Поскольку показатель «Заказ» в массиве поиска отсутствует, единственный способ сопоставить их — по Покупателю и Товару:
Основываясь на приведенном выше скриншоте, давайте определим аргументы для нашей формулы:
- диапазон поиска — $F$3:$H$10
- искомое_значение1 — $B3
- диапазон_поиска1 — $F$3:$F$10
- искомое_значение2 — $C3
- диапазон_поиска2 — $G$3:$G$10
- номер_возвращаемого_столбца — 3
Обязательно добавьте абсолютные ссылки на ячейки, чтобы они не изменились при копировании формулы вниз:
=ИНДЕКС($F$3:$H$10; ПОИСКПОЗ(1; ($B3=$F$3:$F$10) * ($C3=$G$3:$G$10); 0); 3)
Введите формулу в D3, нажмите Ctrl + Shift + Enter. Cкопируйте её вниз и проверьте результат:
Таким образом, мы объединили значения в таблицах, если имеется совпадение сразу в двух колонках. Думаю, вы понимаете логику этой формулы и сможете при необходимости добавить условие совпадения еще в одном столбце.
Объединяем с помощью Excel Power Query
В ситуациях, когда вам нужно объединить две или более таблиц с разным количеством строк и колнок, Excel Power Query может пригодиться. Однако имейте в виду, что объединение с помощью Power Query нельзя выполнить всего за пару щелчков мышью. Вот только самые основные особенности:
- Power Query может объединить две таблицы, сопоставив один или несколько столбцов.
- Исходные сведения могут находиться на одном листе или на разных листах.
- Исходные данные не будут изменены. Они копируются в новую таблицу, которую можно импортировать в существующий или новый рабочий лист.
- В Excel 2016 и Excel 2019 Power Query — это встроенная функция. В Excel 2010 и Excel 2013 его можно загрузить как надстройку.
Проще говоря, Power Query (также известный как Get & Transform в Excel 2016 и Excel 2019) — это инструмент для объединения, очистки и преобразования данных из нескольких источников в нужный вам формат —обычную таблицу, сводную таблицу или сводную диаграмму.
Чтобы результаты соответствовали вашим ожиданиям, имейте в виду следующее:
- Объединяемые таблицы должны иметь по крайней мере, один общий столбец (также называемый как общий идентификатор или ключевой). Кроме того, эти общие столбцы должны содержать только уникальные значения без повторов.
- В отличие от формул, Power Query не переносит данные из одной таблицы в другую. Он создает совершенно новую таблицу, которая объединяет информацию из исходных таблиц.
- Результирующая таблица не обновляется автоматически. Вы должны явно указать Excel сделать это.
Источник данных
В качестве примера давайте объединим 3 таблицы на основе общих показателей «Код заказа» и «Код покупателя». Обратите внимание, что в каждой из них разное количество строк, первая и вторая имеют дубликаты покупателей и товаров, третья содержит только уникальные записи.
Наша задача — сопоставить данные в таблице 1 с соответствующими записями из двух других и объединить все это примерно следующим образом:
Прежде чем вы начнете присоединять, я бы посоветовал вам дать вашим диапазонам несколько описательных имен, чтобы вам было легче узнавать их и управлять ими позже. Кроме того, хотя мы говорим «таблицы», вам не нужно преобразовывать ваши данные в таблицу Excel. Ваши источники могут быть обычными диапазонами или именованными диапазонами, как в этом примере:
- первый называется Заказ,
- второй — Товар,
- третий — Скидка.
Создание подключений Power Query
Чтобы не загромождать вашу книгу копиями исходных данных, мы собираемся преобразовать их в соединения, выполнить слияние в редакторе Power Query, а затем загрузить только итоговый результат.
Чтобы создать соединение в Power Query, выполните следующие действия:
- Выберите свою первый диапазон (Заказ) или любую ячейку в нем.
- Перейдите на вкладку «Данные» и нажмите «Из таблицы / диапазона».
- В открывшемся редакторе Power Query щелкните стрелку раскрывающегося списка «Закрыть и загрузить» (а не саму кнопку!) и выберите параметр «Закрыть и загрузить в…» .
- В диалоговом окне «Импорт данных» выберите параметр «Только создать подключение» и нажмите « ОК» .
Это создаст соединение с именем вашего диапазона и отобразит его на панели запросов и подключений, которая появится в правой части вашей книги.
- Повторите вышеуказанные шаги для всех остальных таблиц, которые вы хотите объединить.
Когда закончите, вы увидите все подключения на правой панели:
Как объединить два соединения в одну таблицу.
Теперь давайте посмотрим, как вы можете объединить две таблицы в одну при помощи созданных подключений:
- На вкладке «Данные» нажмите кнопку «Получить данные», выберите «Объединить запросы» в раскрывающемся списке и нажмите «Объединить»:
- В диалоговом окне «Слияние» сделайте следующее:
- Выберите свой первый диапазон (Заказ) из первого раскрывающегося списка.
- Выберите второй (Товар) из второго раскрывающегося списка.
- В обоих предварительных просмотрах щелкните соответствующий столбец (код заказа), чтобы выбрать его. Он будет выделен зеленым.
- В раскрывающемся списке «Тип присоединения» оставьте значение по умолчанию: «Левый внешний» (все с первого, соответствие со второго).
- Щелкните ОК.
После выполнения вышеуказанных шагов редактор Power Query покажет вашу первую таблицу (Заказ) с одним дополнительным столбцом. Он будет назван как ваша вторая таблица (Товар) и добавлен в конец. В нём пока нет значений, только слово «Таблица» во всех ячейках. Но не расстраивайтесь, вы все сделали правильно, и мы скоро это исправим!
Выберите столбцы для добавления из второй таблицы
На текущий момент у вас есть результат, изображенный на скриншоте ниже. Чтобы завершить процесс слияния, выполните следующие действия в редакторе Power Query Editor:
- В последнем столбце (Товар) нажмите на двустороннюю стрелку в заголовке.
- В открывшемся окне сделайте следующее:
- Не снимайте флажок «Развернуть».
- Отмените весь выбор, а затем укажите только те, которые вы хотите скопировать. В этом примере мы выбираем только колонки Товар и Количество, потому что в исходной информации уже есть код заказа.
- Снимите флажок Использовать исходное имя столбца как префикс (если вы не хотите, чтобы имя столбца было с префиксом с именем таблицы, из которой он был взят).
- Щелкните ОК.
В результате вы получите новую таблицу, содержащую все записи из вашей первой и дополнительные колонки из второй:
Если вам нужно объединить только две таблицы, вы можете считать работу почти выполненной и загрузить полученный результат в Excel. Но нам нужно добавить еще информацию.
Как объединить больше таблиц.
Если вам нужно присоединиться к трем или более столам, вам предстоит еще кое-что подработать. Я кратко опишу шаги здесь, потому что вы уже выполняли все это при объединении первых двух диапазонов:
- Сохраните результат, полученный на предыдущем шаге (показанную на скриншоте выше), как соединение:
- В редакторе запросов питания, нажмите кнопку Закрыть и загрузить на стрелку раскрывающегося списка и выберите Закрыть и загрузить … .
- В диалоговом окне «Импорт данных» выберите «Только создать подключение» и нажмите « ОК» .
Это добавит еще одно соединение с именем Слияние1 на панель запросов и подключений. Вы можете переименовать это соединение, если хотите. Щелкните по нему правой кнопкой мыши и выберите «Переименовать» во всплывающем меню.
- Объедините Слияние1 с вашей третьей таблицей (Скидка), выполнив уже знакомые нам действия (вкладка Данные —Получить данные — Объединить запросы).
На скриншоте ниже показаны мои настройки:
- При нажатии кнопки ОК в диалоговом окне «Слияние» открывается редактор Power Query. В нем вы выбираете все нужное для добавления из третьей таблицы.
В этом примере нам нужен только показатель Скидка:
В результате вы получите новую объединенную таблицу, состоящую из первоначальной и дополнительных столбцов, скопированных из двух других.
Как импортировать объединенную таблицу в Excel.
С полученными результатами в редакторе Power Query вам остается сделать только одно — загрузить их в свою книгу Excel. И это самая легкая часть!
- В редакторе запросов нажмите Закрыть и загрузить.
- В диалоговом окне «Импорт данных» выберите параметры «Таблица» и «Новый лист».
- Щелкните ОК.
Новая таблица, объединяющая информацию из двух или более источников, появится на новом листе. Поздравляю, у вас получилось!
В качестве последнего штриха вы можете применить процентный формат к скидкам и, возможно, изменить стиль оформления по умолчанию на ваш любимый. После этих улучшений моя комбинированная таблица выглядит очень красиво:
Примечание. Если вы работаете с числовыми показателями (например, о динамике продаж), и вы хотите дальше их анализировать, вы можете загрузить полученный свод в качестве отчета сводной таблицы (это второй пункт в окне импорта).
Как сделать объединение на основе нескольких столбцов.
В предыдущем примере мы объединяли таблицы, сопоставляя один ключевой столбец в каждой из них. Но нет ничего, что могло бы помешать вам выбрать две или более пары колонок. Вот как это сделаем:
В диалоговом окне » Слияние» удерживайте клавишу Ctrl и щелкайте по ключевым столбцам один за другим, чтобы выбрать их. Важно, чтобы вы нажимали их в одном и том же порядке в обоих предварительных просмотрах, чтобы аналогичные столбцы имели одинаковые номера. Например, менеджер — это ключевой столбец №1, а товар — ключевой №2. Пустые ячейки или строки, которые Power Query не может сопоставить, показывают null :
После этого выполните точно такие же действия, как описано выше, и ваши таблицы будут объединены путем сопоставления значений по менеджерам и товарам.
Как обновить объединенную таблицу
Лучшее в Power Query — это то, что настраивать объединенную таблицу нужно только один раз. Когда вы вносите некоторые изменения в исходные файлы, вам не нужно снова повторять весь процесс. Просто нажмите кнопку «Обновить» на панели «Запросы и подключения», и объединенная таблица сразу же обновится:
Если панель исчезла из Excel, нажмите кнопку «Запросы и подключения», чтобы вернуть ее.
Кроме того, вы можете нажать кнопку «Обновить все» на вкладке «Данные» или кнопку «Обновить» в запросе (эта вкладка активируется после выбора любой ячейки в объединенной таблице).
А теперь рассмотрим применение специальной программы.
Быстрый способ объединить таблицы путем сопоставления ключевых столбцов.
Если вы еще не очень хорошо знакомы с формулами Excel и у вас нет времени разбираться в загадочных особенностях Power Query, мастер объединения таблиц Merge Two Tables может существенно сэкономить вам время и нервы. Ниже я покажу три наиболее популярных варианта использования его для объединения таблиц.
Пример 1. Объедините две таблицы по нескольким столбцам.
Если вы считаете, что формулу массива для столбцов трудно запомнить и применять, положитесь на специальную надстройку, которая сделает эту работу быстро и безупречно.
В этом примере мы будем использовать уже знакомые по предыдущим разделам этой статьи таблицы и объединять их на основе 2 столбцов: «Код покупателя» и « Товар».
Итак, у нас есть в основной таблице список заказов для нескольких покупателей. И есть таблица скидок для этих покупателей на некоторые из товаров. Нужно перенести в основную таблицу процент скидки. Обратите внимание, что основная таблица имеет строк больше, чем таблица поиска:
С помощью мастера объединения таблиц, вам нужно сделать следующее:
- Выберите любую ячейку в основной таблице и нажмите кнопку «Объединить две таблицы (Merge Two Tables)» на вкладке «Ablebits Data»:
- Убедитесь, что программа правильно выбрала диапазон ячеек, и нажмите Далее (Next).
- Выберите таблицу поиска и нажмите Далее:
- Укажите пары столбцов, которые нужно сопоставить в обеих таблицах, в нашем случае Продавец и Товар, и нажмите Далее:
Примечание. Если регистр текста в ключевых столбцах имеет значение, установите флажок Учитывать регистр, чтобы прописные и строчные буквы обрабатывались как разные символы.
- При желании выберите столбцы, которые нужно обновить значениями из таблицы поиска. Поскольку у нас нечего обновлять, мы оставляем все их как есть и просто нажимаем «Далее».
- Выберите столбцы, которые нужно добавить в основную таблицу, и нажмите « Далее». Добавляем скидку, как и планировали.
- На этом этапе вы указываете мастеру, как именно вы хотите объединить таблицы. Все опции имеют описательные надписи, поэтому я не буду вдаваться в подробные объяснения. Если вы не уверены в каком-либо варианте, щелкните знак вопроса рядом с ним, и небольшая диаграмма покажет вам, как таблицы будут объединены.
В нашем случае параметры по умолчанию работают нормально, поэтому мы нажимаем Finish, ничего не меняя:
Дайте мастеру несколько секунд на обработку и просмотрите результат:
Как видно на скриншоте выше, мастер сделал следующее:
- Добавлен столбец «Скидка» путем сопоставления имени продавца и наименования товара в обеих таблицах.
- В столбце имеются пустые ячейки, поскольку не для всех товаров этим продавцам были предоставлены скидки.
Пример 2. Объединение таблиц и обновление выбранных столбцов.
Если ваша основная таблица содержит устаревшие данные, вы можете обновить ее соответствующими значениями из таблицы поиска.
Продолжим рассматривать данные предыдущего примера. Предположим, некоторым из покупателей был изменен размер скидки, а также были добавлены дополнительные позиции в таблицу скидок. В связи с этим наша основная таблица устарела и нуждается в обновлении. Давайте снова объединим 2 таблицы по покупателю и наименованию товара и обновим значения в столбце Скидка.
Чтобы получить нужный результат, вам нужно сделать следующее:
Шаг 1. Выберите основную таблицу.
Шаг 2. Выберите таблицу поиска.
Шаг 3. Выберите «Код покупателя» и «Товар» как ключевые столбцы.
Шаг 4. Выберите столбец «Скидка» для обновления. То есть, в этот столбец основной таблицы будут перенесены обновленные значения из соответствующего столбца из таблицы поиска. Параметры этого добавления вы укажете чуть позже.
Шаг 5. Пропустите, потому что у нас нет столбцов для добавления. Нам нужно просто обновить информацию.
Шаг 6. Поскольку в столбце «Скидка» есть несколько пустот, мы выбираем обновление только в том случае, если ячейки в таблице поиска содержат данные. Таким образом мы обновим устаревшие значения, но не потеряем никакие данные, которые по какой-то причине исчезли из таблицы поиска. При желании вы можете выделить обновленные ячейки любым цветом по вашему выбору. На скриншоте ниже показаны настройки:
Совет. Чтобы предотвратить перезапись существующих данных, вы можете обновлять только пустые ячейки в основной таблице.
И вот результат:
Как видите, две скидки были добавлены и две — обновлены, так как изменились их величины.
Конечно, можно это сделать и при помощи формул, как это описано в начале этой статьи, но затраты времени будут на порядок больше.
Пример 3. Объединение по нескольким совпадающим значениям из двух таблиц.
В ситуациях, когда таблица поиска содержит несколько вхождений ключевого значения, вы можете захотеть перетащить их все в свою основную таблицу. Эту задачу можно выполнить с помощью одной из нестандартных формул массива. Или вы можете сделать это простым способом с помощью мастера объединения таблиц.
Предположим, ваша основная таблица содержит только по одному заказу от каждого покупателя, а таблица поиска содержит дополнительные заказы по множеству покупателей. Теперь вы хотите добавить в основную таблицу все заказы по уже имеющимся в ней покупателям и сгруппировать их по коду продавца. К примеру, найти во второй таблице все заказы покупателя с кодом «Красный» и поместить их в первую сразу после уже имеющейся записи по этому покупателю (то есть, начиная с 3-й строки).
Похоже, предстоит много работы? Нет, если в вашем распоряжении есть Мастер слияния таблиц Merge Two Tables
А теперь рассмотрим все действия по объединению таблиц пошагово.
Шаг 1. Выберите основную таблицу.
Шаг 2. Выберите таблицу поиска.
Здесь вы видите архив записей по продажам. Из него нам нужно извлечь в основную таблицу только записи по имеющимся в ней 4 покупателям.
Шаг 3. Выберите Код покупателя в соответствующем столбце.
Эта операция нам уже знакома – выбираем ключевой столбец, в котором будем искать совпадения в обеих таблицах.
Шаг 4. Выбираем столбцы, информацию в которых нужно обновить.
Нам нужны все столбцы, поэтому можно просто нажать на чекбокс в шапке таблицы, чтобы выбрать все сразу.
Шаг 5. Нет столбцов для добавления. Пропускаем.
Шаг 6. Вставляем дополнительные совпадающие строки после строки с тем же значением ключа. При желании установите цвет фона для добавленных строк, чтобы сразу просмотреть изменения:
Чтобы не потерять уже имеющиеся в основной таблице значения, активируем опцию «Обновлять в основной таблице только пустые ячейки».
И вот результат:
Как видите, 4 строки остались, и к каждой из них добавлены снизу данные по этому покупателю, извлечённые из таблицы поиска.
В приведенных выше примерах показаны только 3 из множества возможных способов объединения таблиц в Excel. Вы можете загрузить 14-дневную пробную версию и попробовать объединение таблиц на своих данных.
Если будут вопросы или замечания, не стесняйтесь использовать комментарии.
Мы познакомили вас с основными способами соединения двух и более таблиц. Думаю, вы сможете выбрать наиболее подходящий для вас исходя из имеющейся информации и решаемых задач.
Поиск ВПР нескольких значений по нескольким условиям — В статье показаны способы поиска (ВПР) нескольких значений в Excel на основе одного или нескольких условий и возврата нескольких результатов в столбце, строке или в отдельной ячейке. При использовании Microsoft… 
Функция ЕСЛИОШИБКА – примеры формул — В статье описано, как использовать функцию ЕСЛИОШИБКА в Excel для обнаружения ошибок и замены их пустой ячейкой, другим значением или определённым сообщением. Покажем примеры, как использовать функцию ЕСЛИОШИБКА с функциями визуального… 
Как сделать диаграмму Ганта — Думаю, каждый пользователь Excel знает, что такое диаграмма и как ее создать. Однако один вид графиков остается достаточно сложным для многих — это диаграмма Ганта. В этом кратком руководстве я постараюсь показать… 
Как сделать автозаполнение в Excel — В этой статье рассматривается функция автозаполнения Excel. Вы узнаете, как заполнять ряды чисел, дат и других данных, создавать и использовать настраиваемые списки в Excel. Эта статья также позволяет вам убедиться, что вы… 
Быстрое удаление пустых столбцов в Excel — В этом руководстве вы узнаете, как можно легко удалить пустые столбцы в Excel с помощью макроса, формулы и даже простым нажатием кнопки. Как бы банально это ни звучало, удаление пустых… 
Как быстро объединить несколько файлов Excel — Мы рассмотрим три способа объединения файлов Excel в один: путем копирования листов, запуска макроса VBA и использования инструмента «Копировать рабочие листы» из надстройки Ultimate Suite. Намного проще обрабатывать данные в…
Содержание
- 0.1 Способ 1. С помощью формул
- 0.2 Способ 2. Если таблицы неодинаковые или в разных файлах
- 0.3 Ссылки по теме
- 1 Если таблицы одинаковые
- 2 Если таблицы разные
- 3 Видео в помощь
- 3.1 Другие формулы
- 3.2 1. Создаём вспомогательную таблицу для поиска.
- 3.3 2. Обновляем главную таблицу при помощи данных из таблицы для поиска.
- 3.4 3. Переносим данные из таблицы поиска в главную таблицу
Способ 1. С помощью формул
Имеем несколько однотипных таблиц на разных листах одной книги. Например, вот такие:
Необходимо объединить их все в одну общую таблицу, просуммировав совпадающие значения по кварталам и наименованиям.
Самый простой способ решения задачи «в лоб» — ввести в ячейку чистого листа формулу вида
=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3
которая просуммирует содержимое ячеек B2 с каждого из указанных листов, и затем скопировать ее на остальные ячейки вниз и вправо.
Если листов очень много, то проще будет разложить их все подряд и использовать немного другую формулу:
=СУММ(‘2001 год:2003 год’!B3)
Фактически — это суммирование всех ячеек B3 на листах с 2001 по 2003, т.е. количество листов, по сути, может быть любым. Также в будущем возможно поместить между стартовым и финальным листами дополнительные листы с данными, которые также станут автоматически учитываться при суммировании.
Способ 2. Если таблицы неодинаковые или в разных файлах
Если исходные таблицы не абсолютно идентичны, т.е. имеют разное количество строк, столбцов или повторяющиеся данные или находятся в разных файлах, то суммирование при помощи обычных формул придется делать для каждой ячейки персонально, что ужасно трудоемко. Лучше воспользоваться принципиально другим инструментом.
Рассмотрим следующий пример. Имеем три разных файла (Иван.xlsx, Рита.xlsx и Федор.xlsx) с тремя таблицами:
Хорошо заметно, что таблицы не одинаковы — у них различные размеры и смысловая начинка. Тем не менее их можно собрать в единый отчет меньше, чем за минуту. Единственным условием успешного объединения (консолидации) таблиц в подобном случае является совпадение заголовков столбцов и строк. Именно по первой строке и левому столбцу каждой таблицы Excel будет искать совпадения и суммировать наши данные.
Для того, чтобы выполнить такую консолидацию:
- Заранее откройте исходные файлы
- Создайте новую пустую книгу (Ctrl + N)
- Установите в нее активную ячейку и выберите на вкладке (в меню) Данные — Консолидация (Data — Consolidate). Откроется соответствующее окно:
- Установите курсор в строку Ссылка (Reference) и, переключившись в файл Иван.xlsx, выделите таблицу с данными (вместе с шапкой). Затем нажмите кнопку Добавить (Add) в окне консолидации, чтобы добавить выделенный диапазон в список объединяемых диапазонов.
- Повторите эти же действия для файлов Риты и Федора. В итоге в списке должны оказаться все три диапазона:
Обратите внимание, что в данном случае Excel запоминает, фактически, положение файла на диске, прописывая для каждого из них полный путь (диск-папка-файл-лист-адреса ячеек). Чтобы суммирование происходило с учетом заголовков столбцов и строк необходимо включить оба флажка Использовать в качестве имен (Use labels). Флаг Создавать связи с исходными данными (Create links to source data) позволит в будущем (при изменении данных в исходных файлах) производить пересчет консолидированного отчета автоматически.
После нажатия на ОК видим результат нашей работы:
Наши файлы просуммировались по совпадениям названий из крайнего левого столбца и верхней строки выделенных областей в каждом файле. Причем, если развернуть группы (значками плюс слева от таблицы), то можно увидеть из какого именно файла какие данные попали в отчет и ссылки на исходные файлы:
Ссылки по теме
- Макрос для автоматической сборки данных с разных листов в одну таблицу
- Макрос для сборки листов из нескольких файлов
Работа в Excel – это, вне сомнения, бесценный опыт, который пригодится каждому пользователю. Разумеется, я не утверждаю, что владеть этим инструментом в совершенстве должен каждый юзер, но все же чем большими знаниями вы обладаете, тем лучше. В предыдущих своих статьях я уже неоднократно рассказывал о возможностях Экселя, о тонкостях построения диаграмм, графиков и таблиц. Кроме того, не единожды речь заходила и о применении тех или иных функций на практике.
В этот раз предлагаю обсудить вопрос касательно того, как объединить таблицы в excel. Если быть точнее, это называется «консолидация» таблиц, и такая функция весьма востребована. Ну что ж, не будем ходить вокруг да около, давайте разберемся, что это такое и зачем нужно вообще.
Если таблицы одинаковые
Как вы понимаете, консолидация таблиц здорово упрощает работу. Она позволяет создать сводную таблицу, объединив данные всех остальных в одну. Рассмотрим для начала первый вариант, когда таблицы, в общем-то, однотипные, различие лишь в цифрах. Свести их в таком случае воедино очень просто, нужно лишь суммировать значения в ячейках. Как это делается, я уже рассказывал: используется формула СУММ, которая просуммируют все значения, которые совпадают по кварталам и наименованиям (в конкретном случае). Вот и всё, принцип действий очень простой.
Если таблицы разные
Нередкой является ситуация, когда исходники отличаются по многим критериям либо же выполнены на разных файлах. Как вы понимаете, воспользоваться предыдущим вариантом вряд ли получится, уж больно трудоемкий процесс. А раз суммирование ячеек отпадает, нужно искать другие варианты.
К счастью, такой вариант имеется! Представим, что у нас есть три разноплановые таблицы, которые нужно объединить.
Они могут быть абсолютно разными, но есть одно условие: в таких табличках должны совпадать заголовки строчек и столбиков. Дело в том, что первая строка и левый столбик каждой таблицы – это тот критерий, по которому поисковая система Excel будет находить совпадения и суммировать их между собой.
Как выполнить консолидацию? Все очень просто:
- Откройте предварительно все исходники, после чего создайте пустую книгу, воспользовавшись комбинацией +.
- Выберите ячейку, затем откройте вкладку «Данные» и отыщите кнопочку «Консолидация».
- В открывшемся окне необходимо установить соответствующие параметры: в поле «Ссылка» перейдите на одну из таблиц и выделите ее полностью, включая шапку. Теперь кликните по кнопке «Добавить», которая расположена в окошке консолидации, после чего в поле «Список диапазонов» будет добавлен диапазон, выделенный ранее.
- Проделайте те же действия для оставшихся таблиц.
- Поставьте галочки в обоих пунктах «Использовать в качестве имен», а также напротив «Создавать связи с исходными данными». Нажмите «ОК».
- Теперь можно наслаждаться результатом: все файлы просуммировались по названиям левого столбика и верхней строчки выделенных областей каждой из таблиц.
Надеюсь, этот небольшой ликбез поможет вам быстро и качественно объединять несколько таблиц в Excel в одну.
Видео в помощь
Из этой статьи Вы узнаете, как быстро объединить данные из двух таблиц Excel, когда в ключевых столбцах нет точных совпадений. Например, когда уникальный идентификатор из первой таблицы представляет собой первые пять символов идентификатора из второй таблицы. Все предлагаемые в этой статье решения протестированы мной в Excel 2013, 2010 и 2007.
Итак, есть два листа Excel, которые нужно объединить для дальнейшего анализа данных. Предположим, в одной таблице содержатся цены (столбец Price) и описания товаров (столбец Beer), которые Вы продаёте, а во второй отражены данные о наличии товаров на складе (столбец In stock). Если Вы или Ваши коллеги составляли обе таблицы по каталогу, то в обеих должен присутствовать как минимум один ключевой столбец с уникальными идентификаторами товаров. Описание товара или цена могут изменяться, но уникальный идентификатор всегда остаётся неизменным.
Трудности начинаются, когда Вы получаете некоторые таблицы от производителя или из других отделов компании. Дело может ещё усложниться, если вдруг вводится новый формат уникальных идентификаторов или самую малость изменятся складские номенклатурные обозначения (SKU). И перед Вами стоит задача объединить в Excel новую и старую таблицы с данными. Так или иначе, возникает ситуация, когда в ключевых столбцах имеет место только частичное совпадение записей, например, «12345» и «12345-новый_суффикс«. Вам-то понятно, что это тот же SKU, но компьютер не так догадлив! Это не точное совпадение делает невозможным использование обычных формул Excel для объединения данных из двух таблиц.
И что совсем плохо – соответствия могут быть вовсе нечёткими, и «Некоторая компания» в одной таблице может превратиться в «ЗАО «Некоторая Компания»» в другой таблице, а «Новая Компания (бывшая Некоторая Компания)» и «Старая Компания» тоже окажутся записью об одной и той же фирме. Это известно Вам, но как это объяснить Excel?
Выход есть всегда, читайте далее и Вы узнаете решение!
Замечание: Решения, описанные в этой статье, универсальны. Вы можете адаптировать их для дальнейшего использования с любыми стандартными формулами, такими как ВПР (VLOOKUP), ПОИСКПОЗ (MATCH), ГПР (HLOOKUP) и так далее.
Выберите подходящий пример, чтобы сразу перейти к нужному решению:
- Ключевой столбец в одной из таблиц содержит дополнительные символы
- Данные из ключевого столбца в первой таблице разбиты на два или более столбца во второй таблице
- Данные в ключевых столбцах не совпадают (123-SDX и HFGT-23) или есть частичное совпадение, меняющееся от ячейки к ячейке (Coca Cola и Coca-Cola Inc.)
Ключевой столбец в одной из таблиц содержит дополнительные символы
Рассмотрим две таблицы. Столбцы первой таблицы содержат номенклатурный номер (SKU), наименование пива (Beer) и его цену (Price). Во второй таблице записан SKU и количество бутылок на складе (In stock). Вместо пива может быть любой товар, а количество столбцов в реальной жизни может быть гораздо больше.
В таблице с дополнительными символами создаём вспомогательный столбец. Можно добавить его в конец таблицы, но лучше всего вставить его следующим справа после ключевого столбца, чтобы он был на виду.
Ключевым в таблице в нашем примере является столбец A с данными SKU, и нужно извлечь из него первые 5 символов. Добавим вспомогательный столбец и назовём его SKU helper:
- Наводим указатель мыши на заголовок столбца B, при этом он должен принять вид стрелки, направленной вниз:
- Кликаем по заголовку правой кнопкой мыши и в контекстном меню выбираем Вставить (Insert):
- Даём столбцу имя SKU helper.
- Чтобы извлечь первые 5 символов из столбца SKU, в ячейку B2 вводим такую формулу:
=ЛЕВСИМВ(A2;5)=LEFT(A2,5)Здесь A2 – это адрес ячейки, из которой мы будем извлекать символы, а – количество символов, которое будет извлечено.
- Копируем эту формулу во все ячейки нового столбца.
Готово! Теперь у нас есть ключевые столбцы с точным совпадением значений – столбец 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-суффикс» соответственно. Формула будет выглядеть так:
=ЛЕВСИМВ(A2;НАЙТИ("-";A2)-1)=LEFT(A2,FIND("-",A2)-1)
Одним словом, Вы можете использовать такие функции Excel, как ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT), ПСТР (MID), НАЙТИ (FIND), чтобы извлекать любые части составного индекса. Если с этим возникли трудности – свяжитесь с нами, мы сделаем всё возможное, чтобы помочь Вам.
Данные из ключевого столбца в первой таблице разбиты на два или более столбца во второй таблице
Предположим, таблица, в которой производится поиск, содержит столбец с идентификаторами. В ячейках этого столбца содержатся записи вида XXXX-YYYY, где XXXX – это кодовое обозначение группы товаров (мобильные телефоны, телевизоры, видеокамеры, фотокамеры), а YYYY – это код товара внутри группы. Главная таблица состоит из двух столбцов: в одном содержатся коды товарных групп (Group), во втором записаны коды товаров (ID). Мы не можем просто отбросить коды групп товаров, так как один и тот же код товара может повторяться в разных группах.
Добавляем в главной таблице вспомогательный столбец и называем его Full ID (столбец C), подробнее о том, как это делается рассказано ранее в этой статье.
В ячейке C2 запишем такую формулу:
=СЦЕПИТЬ(A2;"-";B2)=CONCATENATE(A2,"-",B2)
Здесь 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:
Всё просто, не так ли? Задавайте свои вопросы в комментариях к статье, я постараюсь ответить, как можно скорее.
Урок подготовлен для Вас командой сайта office-guru.ru
Источник: /> Перевел: Антон Андронов
Правила перепечаткиЕще больше уроков по Microsoft Excel
Оцените качество статьи. Нам важно ваше мнение:
Как объединить две таблицы Excel по частичному совпадению ячеек
Смотрите также структурой, но разными Workbooks.Open Filename:=PathS & него следует? В и верхней строки персонально, что ужасно группу. Для того, все данные неМастера функций производим все те из тех вариантов, образом будет выделенУрок подготовлен для ВасСоздаём новый лист Excel товара внутри группы.вводим такую формулу: но как это
Из этой статьи Вы данными (Иванов, Петров, WrkbkNmS ActiveWorkbook.ActiveSheet.Cells(1, 1).Activate файле две таблички: выделенных областей в трудоемко. Лучше воспользоваться чтобы её спрятать, только размещены в. Нам следует переместиться же действия, о о которых шел весь диапазон, расположенный командой сайта office-guru.ru и называем его Главная таблица состоит=ЛЕВСИМВ(A2;5) объяснить Excel? узнаете, как быстро Сидоров) LastRowI = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row1. Исходная каждом файле. Причем, принципиально другим инструментом. достаточно нажать на одной строке, но в блок операторов
которых был разговор разговор в между этими двумяИсточник: https://www.ablebits.com/office-addins-blog/2013/09/20/merge-worksheets-excel-partial-match/SKU converter из двух столбцов:=LEFT(A2,5)Выход есть всегда, читайте объединить данные из2. Данные в InputDataArrV = ActiveCell.CurrentRegion.Offset(1,2. Та что если развернуть группыРассмотрим следующий пример. Имеем пиктограмму в виде и разделены между«Текстовые» выше. После этогоСпособе 1 секторами.Перевел: Антон Андронов. Копируем весь столбец в одном содержатсяЗдесь далее и Вы двух таблиц Excel, таблицах регулярно меняются 0).Resize(LastRowI — 1, присылает Вам юзер (значками плюс слева три разных файла символа собой пробелом.. Далее находим и
строчки в границах. Затем перемещаемся воПосле того, как необходимыйАвтор: Антон АндроновOur.SKU коды товарных группA2 узнаете решение! когда в ключевыхЗадача: 3) If OutputDataArrVВопрос: А что от таблицы), то («минус»Есть также альтернативный вариант выделяем название таблицы будут объединены. вкладку диапазон выделен, можноПри работе с таблицами
из листа (Group), во втором– это адрес
Замечание: столбцах нет точных1. Создать четвёртую Is Nothing Then делать-то надо, если можно увидеть изИван.xlsx, расположенную слева от провести указанную процедуру«СЦЕПИТЬ» При этом сохранятся«Главная» непосредственно приступать к иногда приходится менятьStore
записаны коды товаров ячейки, из которойРешения, описанные в
- совпадений. Например, когда таблицу, в которой OutputDataArrV = InputDataArrV
- у Вас уже какого именно файла, вертикальной панели координат. по объединению данных
- . Затем клацаем по только данные, расположенныеи щелкаем по процедуре объединения. Для их структуру. Однимна новый лист, (ID). Мы не мы будем извлекать
Ключевой столбец в одной из таблиц содержит дополнительные символы
этой статье, универсальны. уникальный идентификатор из были бы собраны Workbooks(WrkbkNmS).Close WrkbkNmS = всё есть? какие данные попалиРита.xlsxДля того, чтобы снова из нескольких строчек кнопке в левой верхней кнопке на ленте этого кликаем правой из вариантов данной удаляем дубликаты и
можем просто отбросить символы, а Вы можете адаптировать первой таблицы представляет данные из трёх Dir Loop EndВ первом посте: в отчет ии показать сгруппированные элементы,
в одну без«OK» ячейке объединенного диапазона.«Объединить и поместить в кнопкой мыши в процедуры является объединение оставляем в нём коды групп товаров,5 их для дальнейшего собой первые пять исходных (Общая _
- Sub Какие «все эти ссылки на исходныеФедор нужно нажать на потерь. При этом
- .Объединение в границах таблицы центре» любом месте выделения. строк. При этом, только уникальные значения.
- так как один– количество символов, использования с любыми
- символов идентификатора из Что нужно)DYm00n таблицы», где они? файлы:.xlsx
знак
не нужно дажеПоявляется окошко аргументов функции можно также выполнить. Она располагается в Открывается контекстное меню. объединенные объекты превращаютсяРядом добавляем столбец и тот же которое будет извлечено. стандартными формулами, такими
- второй таблицы. Все2. Данные должны:
Как «слить», поDYm00n) с тремя таблицами:«+» будет использовать функцию,СЦЕПИТЬ через инструменты на блоке инструментов Переходим в нем в одну строчку.
Supp.SKU код товара можетКопируем эту формулу во как
Другие формулы
- предлагаемые в этой меняться вместе сanalyst каким критериям и: Изначально есть однаХорошо заметно, что таблицыобразовавшийся в том
а можно обойтись
. По числу аргументов - ленте.«Выравнивание» по пункту Кроме того, существуети вручную ищем повторяться в разных все ячейки новогоВПР статье решения протестированы изменением в исходных, а без скрипта в какую форму?
таблица, с такими
не одинаковы - - же месте, где обычной формулой. можно использовать доПроизводим выделение нужных строк.«Формат ячеек» возможность группировки близлежащих соответствия между значениями группах.
столбца.
(VLOOKUP),
мной в Excel (т.е. подправил Иванов обойтись нельзя? Как «соотнести» и столбцами у них различные ранее находился символУстанавливаем знак «=» в 255 полей с в таблице любымПосле этого выделенный диапазон. строчных элементов. Давайте столбцовДобавляем в главной таблицеГотово! Теперь у насПОИСКПОЗ 2013, 2010 и
Данные из ключевого столбца в первой таблице разбиты на два или более столбца во второй таблице
— изменилось вSerge 007 какие значения определяющие?номер дела, фио, размеры и смысловая«-» строчку, куда будет названием из тех двух строк будет объединенВыполняется активация окна форматирования. выясним, какими способамиOur.SKU вспомогательный столбец и есть ключевые столбцы(MATCH), 2007. «Общая _ Что: Можно. Если нормальныйЗЫ Вот что123, иванов начинка. Тем не. выводиться результат. Кликаем«Текст» вариантов, которые были до конца листа. Производим перемещение во можно провести подобныеи
называем его с точным совпадениемГПРИтак, есть два листа нужно») пример сделать и бы все этиИ соответственно эти менее их можно
Урок: Как сделать группировку по первому элементу, но для воплощения
описаны выше. Затем
При этом все
вкладку виды объединения вSupp.SKUFull ID значений – столбец(HLOOKUP) и так Excel, которые нужно3. Если данные нормально объяснить, «что вопросы не задавать столбцы заполнены. Данная собрать в единый
в Экселе столбца. После того, поставленной задачи нам во вкладке записи, которые будут«Выравнивание» программе Microsoft Excel.(в этом нам(столбец C), подробнееSKU helper далее. объединить для дальнейшего не редактируются, а есть» и «что я и написал: таблица рассылается людям, отчет меньше, чемКак видим, способ объедения
Данные в ключевых столбцах не совпадают
как его адрес понадобится столько, сколько«Главная» вноситься в эту. Затем в группеСкачать последнюю версию помогут описания из о том, какв основной таблицеВыберите подходящий пример, чтобы анализа данных. Предположим, добавляются новые - надо». … которые создают еще за минуту. Единственным строк в одну отобразится в строке строк имеет таблица.
кликаем по кнопке объединенную строчку, расположатся настроек Excel столбца это делается рассказано и столбец
сразу перейти к в одной таблице также должны добавлятьсяDYm00nDYm00n один столбик - условием успешного объединения зависит от того, формул и в В данном случае«Объединить и поместить в
1. Создаём вспомогательную таблицу для поиска.
по центру.«Отображение»Читайте также:Description ранее в этойSKU нужному решению: содержатся цены (столбец в «Общая _: А как еще: Ладно, тогда так
статус и, возможно (консолидации) таблиц в какой именно вид ячейке вывода результата, их 6. Устанавливаем центре»Но далеко не воследует установить галочкуКак объединить столбцы). Это скучная работёнка, статье.в таблице, гдеКлючевой столбец в одной Price) и описания Что нужно» надо объяснить? Я объясню. Я рассылаю дополняют таблицу новыми
подобном случае является объединения пользователю нужен,
2. Обновляем главную таблицу при помощи данных из таблицы для поиска.
набираем на клавиатуре курсор в поле. всех случаях требуется, около параметра
в Excel пусть Вас радуетВ ячейке будет выполняться поиск. из таблиц содержит товаров (столбец Beer),4. Нужно сохранить пример выложил, что всем файл - строками . В совпадение заголовков столбцов и что он следующее выражение:
«Текст1»Или щелкаем по треугольнику, чтобы текст помещался«Объединение ячеек»
Как объединить ячейки мысль о том,C2Теперь при помощи функции дополнительные символы которые Вы продаёте, целостность строк, т.е. было, что добавилось, Исходные данные, в итоге от каждого и строк. Именно хочет получить в&» «&и, произведя зажим расположенному слева от
3. Переносим данные из таблицы поиска в главную таблицу
по центру. Что. После этого можно в Экселе что её придётсязапишем такую формулу:ВПРДанные из ключевого столбца
а во второй если в исходной что стало. Что котором много разных из них приходит по первой строке итоге. Можно произвестиПосле этого кликаем по левой кнопки мыши, этой кнопки, с же делать, если
клацать по кнопкеКак уже было сказано выполнить только один=СЦЕПИТЬ(A2;»-«;B2)
(VLOOKUP) мы получим в первой таблице отражены данные о у Петрова: «Петров-Ель-Салатов еще мне нужно фио. Каждый пользователь
таблица, только с и левому столбцу
объединение строк до
второму элементу столбца
клацаем по первому
office-guru.ru
Объединение строк в Microsoft Excel
последующим щелчком по его нужно разместить«OK» выше, существуют два раз :-).=CONCATENATE(A2,»-«,B2) нужный результат: разбиты на два наличии товаров на П.Е.-32154-думает», то и написать или объяснить? открывает его, правит еще одним столбцом каждой таблицы Excel конца листа, в
и опять вводим элементу, содержащему наименование
пункту
в стандартном виде?в нижней части
основных вида объединенияВ результате мы имеем
Виды объединения
ЗдесьИзвлечь первые или более столбца складе (столбец In в итоговой связкаSerge 007 (описывает состояние дела — статус. будет искать совпадения пределах таблицы, выполнить вышеуказанное выражение. Таким техники в столбце«Объединить ячейки»Производим выделение строк, которые окна. строк – когда вот такую таблицу:A2Х во второй таблице stock). Если Вы должна сохраняться. Не: Где?! — в третьемНужно, все эти и суммировать наши процедуру без потери образом, обрабатываем все«Устройство»раскрывшегося меню. нужно соединить. ПеремещаемсяВслед за этим выделенные несколько строчек преобразуютсяВ главную таблицу (лист
Способ 1: объединение через окно форматирования
– это адрессимволов справа: например,Данные в ключевых столбцах или Ваши коллеги перемешиваться.Смотрите пример примера. столбике), если каких таблицы слить в данные.
- данных при помощи ячейки, данные в. После этого адресОбъединение будет произведено согласно во вкладку строчки будут объединены. в одну и Store) вставляем новый ячейки, содержащей код 6 символов справа не совпадают (123-SDX составляли обе таблицы5. В Общей
Сможете корректно сформулировать либо его дел одну и дополненныеДля того, чтобы выполнить функции или формулы, которых нужно поместить выделенного объекта отобразится тому типу, который«Главная» Причем объединение ячеек когда происходит их столбец группы; символ « из записи «DSFH-164900». и HFGT-23) или по каталогу, то таблице периодически юзается
- вопрос — помогу, нет в списке, строки и столбцы такую консолидацию: а также провести в одну строку. в поле окна. пользователь выбрал.. Щелкаем на ленте произойдет до самого группировка. В первомSupp.SKU
- — Формула будет выглядеть есть частичное совпадение, в обеих должен поиск, так что как уже не добавляет в свой соотнести со значениямиЗаранее откройте исходные файлы группировку строчек. Кроме В нашем случае Точно таким жеВсе перечисленные выше способы по треугольнику, который конца листа.
- случае, если строчные.» – это разделитель; так: меняющееся от ячейки
присутствовать как минимум простыми формулами не единожды помогал, не список (выделено красным). в первоначальной ячейки.Создайте новую пустую книгу того, существуют отдельные получилось такое выражение: образом вносим адреса объединения подразумевают, что размещен справа отСуществуют также альтернативные варианты элементы были заполненыДалее при помощи функцииB2=ПРАВСИМВ(A2;6) к ячейке (Coca
один ключевой столбец взлетит сможете — адьё, Потом каждый пользовательДля наглядности вложил (Ctrl + N) варианты выполнения данных=A4&» «&A5&» «&A6&» «&A7&» последующих строчных элементов после завершения процедуры кнопки перехода к окну данными, то ониВПР– это адрес=RIGHT(A2,6) Cola и Coca-Cola с уникальными идентификаторами
6. Крайне желательно ждите телепатов. присылаем мне свои пример, на первомУстановите в нее активную задач, но на «&A8&» «&A9 столбца будут уничтожены все«Объединить и поместить в форматирования. Например, после все теряются, кроме(VLOOKUP) сравниваем листы
ячейки, содержащей кодПропустить первые Inc.) товаров. Описание товара обойтись без кнопок,DYm00n таблички и я
Способ 2: использование инструментов на ленте
листе таблица которая ячейку и выберите их выбор уже
- Для вывода результата на«Устройство» данные в объединяемых центре» выделения строк, находясь тех, которые былиStore товара. Скопируем формулуХРассмотрим две таблицы. Столбцы или цена могут т.е. открыл Общую: их должен свести рассылается, а на на вкладке (в влияют только предпочтения
- экран жмем на, соответственно в поля элементах, кроме тех,. Открывается список различных во вкладке расположены в самоми в остальные строки.
символов, извлечь следующие первой таблицы содержат изменяться, но уникальный и там сразуSerge 007 в одну - втором листе, которая
- меню) пользователя с точки кнопку«Текст2» которые разместились в действий. Выбираем наименование«Главная» верхнем элементе. ВоSKU converterТеперь объединить данные изY номенклатурный номер (SKU), идентификатор всегда остаётся данные из трёх
- , вот же уже Конечный результат. Т.е. должна получится (упорядочиваниеДанные — Консолидация зрения удобства.Enter, верхней левой ячейке
Способ 3: объединение строк внутри таблицы
«Объединить ячейки», можно кликнуть по втором случае, физически, используя для поиска наших двух таблицсимволов: например, нужно наименование пива (Beer) неизменным.
- исходных. Есть понимание, формулировал, я не в последней таблицы идет по ФИО),(Data — Consolidate)Автор: Максим Тютюшев. Как видим, несмотря«Текст3» области. Но иногда. значку строки остаются в
соответствий столбец не составит труда. извлечь «0123» из и его ценуТрудности начинаются, когда Вы что обновление будет знаю даже как должны содержаться данные красным я выделил. Откроется соответствующее окно:Имеем несколько однотипных таблиц на то, что, требуется без потерьПосле этого строчки будут«Формат» прежнем виде, просто
- Our.SKU Мы будем сопоставлять записи «PREFIX_0123_SUFF». Здесь (Price). Во второй получаете некоторые таблицы происходить только при по другому объяснить из всех присланных новые строки, которыеУстановите курсор в строку на разных листах в данном случае«Текст4» объединить определенные значения, объединены в одну,, расположенному на ленте они объединяются в
, а для обновлённых столбец нам нужно пропустить таблице записан SKU
- от производителя или закрытии/открытии Общего, это уже Из нескольких мне таблиц. На сам пользователь добавляетСсылка одной книги. Например, была использована другая, расположенные в разных а текст или
в блоке инструментов группы, объекты в данных – столбецFull ID первые 8 символов и количество бутылок из других отделов
- нормально таблиц слепить одну сей раз думаю
Способ 4: объединение информации в строках без потери данных
в свою таблицу.(Reference) вот такие: формула, конечное значение«Текст5» строчках таблицы. Сделать числовые значения разместятся«Ячейки» которых можно скрыватьSupp.SKUпервой таблицы со и извлечь следующие на складе (In компании. Дело можетФайлы во вложении.Serge 007 ясно объяснил. В номерах дел
и, переключившись вНеобходимо объединить их все отображается точно таки это можно, воспользовавшись так, как это. Из раскрывшегося списка кликом по значку.
столбцом
4 символа. Формула stock). Вместо пива ещё усложниться, еслиТри бубна порвали: Пример не корректный.DYm00n зависимости не какой файл Иван.xlsx, выделите в одну общую же, как и«Текст6» специально предназначенной для присуще для их действий следует выбрать
в виде символаСтолбецID будет выглядеть так: может быть любой вдруг вводится новый ничего не выходит. Вы мой «пример: Кто нибудь подскажет? нет, данные номера таблицу с данными
- таблицу, просуммировав совпадающие при использовании функции. Затем, когда адреса таких целей функцией числового формата по пункт«минус»
- Supp.SKUвторой таблицы. При=ПСТР(A2;8;4) товар, а количество формат уникальных идентификаторов Буду очень благодарен примера» смотрели? Вашanalyst написал для примера. (вместе с шапкой). значения по кварталамСЦЕПИТЬ
- всех объектов отобразилисьСЦЕПИТЬ умолчанию.«Формат ячеек…». Есть ещё вариантзаполняется оригинальными кодами обнаружении совпадения, записи=MID(A2,8,4) столбцов в реальной или самую малость за помощь. пример расходится с: Откуда в итоговомanalyst Затем нажмите кнопку и наименованиям.. в полях окна,.Но далеко не всегда. соединения без потери производителя. из столбцовИзвлечь все символы до жизни может быть изменятся складские номенклатурныеЗаранее Спасибо! тем что Вы файле взялась строка: А что уДобавитьСамый простой способ решенияУрок: Функция СЦЕПИТЬ в выполняем клик поФункция требуется объединять строчкиТакже, в той же данных с помощьюЗамечание:Description разделителя, длина получившейся гораздо больше. обозначения (SKU). ИMrDobryi написали.
- с номером дела вас конкретно не(Add) задачи «в лоб» Экселе кнопкеСЦЕПИТЬ до конца листа. вкладке формулы, о которомЕсли в столбцеи последовательности может бытьВ таблице с дополнительными
- перед Вами стоит: ещё 2 файлаВобщем, не хотите 2007? получается?в окне консолидации, — ввести вКроме того, можно сгруппировать«OK»относится к категории
Намного чаще соединение«Главная» мы расскажем отдельно.Supp.SKUPrice разной. Например, нужно символами создаём вспомогательный задача объединить вназвания файлов поменялись, — как хотите,Для всех номеровDYm00n чтобы добавить выделенный ячейку чистого листа
- строки без потери. текстовых операторов. Её производится внутри определенногоможно кликнуть по Именно, исходя из
появились пустые ячейки,второй таблицы будут извлечь «123456» и столбец. Можно добавить Excel новую и Общий_Что нужно - удачи Вам в дела из тех,: Да я просто диапазон в список
- формулу вида их структурной целостности.После этого все данные задачей является объединение табличного массива. Давайте косой стрелочке, которая указанных видов преобразований, то необходимо взять добавлены в первую «0123» из записей его в конец
старую таблицы с -_.xlsx решении Вашего вопроса. что были в вообще не знаю, объединяемых диапазонов.=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3 Посмотрим, как это функция выведет одной нескольких текстовых строчек рассмотрим, как это
расположена на ленте формируются различные способы - все коды таблицу. «123456-суффикс» и «0123-суффикс» таблицы, но лучше данными. Так илиkrosav4ig Всего хорошего. исходных данных, но как сводятся таблицыПовторите эти же действиякоторая просуммирует содержимое ячеек сделать. строкой. Но, как в один элемент.
сделать. в правом нижнем
Способ 5: группировка
объединения строчек. ОстановимсяSKUВот пример: Вы владелец соответственно. Формула будет всего вставить его
- иначе, возникает ситуация,: кладете исходные файлыanalyst не было вSerge 007 для файлов Риты B2 с каждогоПрежде всего, выделяем те видим, между наименованиями Синтаксис этой функцииВыделяем все ячейки строк углу блока инструментов на них подробнее., соответствующие этим пустым небольшого магазина, получаете выглядеть так: следующим справа после когда в ключевых в 1 папку: Дмитрий, без скрипта
- обработанных данных статус: Нарисуйте пример, как и Федора. В из указанных листов, смежные строчные элементы, различных товаров пробел имеет следующий вид: таблицы, которые мы«Выравнивание»Прежде всего, давайте рассмотрим ячейкам, добавить их товар от одного=ЛЕВСИМВ(A2;НАЙТИ(«-«;A2)-1)
- ключевого столбца, чтобы столбцах имеет местов файле создал обойтись можно. «ok»? по вашему это итоге в списке и затем скопировать которые нужно будет отсутствует, а это=СЦЕПИТЬ(текст1;текст2;…)
- хотим объединить. Это. Причем в этом возможность объединения строчек в таблицу или нескольких поставщиков.=LEFT(A2,FIND(«-«,A2)-1) он был на только частичное совпадение подключениеКопируете все данные
Когда применяется статус всё должно выглядеть
должны оказаться все ее на остальные сгруппировать. Можно выделять нас не устраивает.Аргументы группы также можно сделать случае переход будет на листе черезSKU converter У каждого изОдним словом, Вы можете виду. записей, например, «строка подключения которые вам прислали «да»? и из чего три диапазона: ячейки вниз и отдельные ячейки в Для того, чтобы«Текст» двумя способами. Первый
произведен непосредственно во
lumpics.ru
Консолидация (объединение) данных из нескольких таблиц в одну
Способ 1. С помощью формул
окно форматирования. Нои найти соответствующий них принята собственная использовать такие функции
Ключевым в таблице в12345200?’200px’:»+(this.scrollHeight+5)+’px’);»>DSN=Excel Files;DefaultDir=U:;DriverId=1046;MaxBufferSize=2048;PageTimeout=5; пользователи в динDYm00n
складываться, а ужОбратите внимание, что в вправо. строках, а не решить данную проблему,
могут представлять собой
из них состоит вкладку прежде, чем приступить код из таблицы номенклатура, отличающаяся от Excel, как нашем примере является
» и «запрос лист. А потом: Я же уже мы тогда подскажем
данном случае Excel
Если листов очень много, обязательно строчки в выделяем строку, содержащую либо отдельный текст, в том, что«Выравнивание» к непосредственной процедуре поставщика. После этого Вашей. В результатеЛЕВСИМВ столбец12345-новый_суффиксКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>select *from (SELECT удаляете дубликаты!
Способ 2. Если таблицы неодинаковые или в разных файлах
3 раза писал, как это сделать, запоминает, фактически, положение то проще будет целом. После этого формулу, и опять либо ссылки на вы зажимаете левуюокна форматирования, то объединения, нужно выделить повторяем шаг 2. возникают ситуации, когда(LEFT),
A«. Вам-то понятно, что * from [U:566635.xlsx].`Лист1$`DYm00n что выделено красным, а то сейчас файла на диске, разложить их все перемещаемся во вкладку жмем на кнопку
элементы листа, в кнопку мыши и есть, пользователю не близлежащие строки, которыеВ нашей главной таблице Ваша запись «Case-Ip4S-01»ПРАВСИМВс данными SKU, это тот же union all SELECT: А как удалить то значит пользователь не понятно ничего… прописывая для каждого подряд и использовать«Данные»«Вставить функцию» которых он расположен. обводите курсором всю придется совершать дополнительный
планируется объединить. есть ключевой столбец
- соответствует записи «SPK-A1403»
- (RIGHT), и нужно извлечь
- SKU, но компьютер * from [U:1814525.xlsx].`Лист1$` дубликаты? Вся проблема сам добавит. РазныеDYm00n из них полный немного другую формулу:
- . Щелкаем по кнопке. Именно последнее свойство область, которая подлежит переход между вкладками.Для выделения строчек, которые с точным совпадением в файле Excel,ПСТР из него первые не так догадлив! union all SELECT в том, что статусы я написал
- : В первом посте путь (диск-папка-файл-лист-адреса ячеек).=СУММ(‘2001 год:2003 год’!B3)«Группировать»Запускается снова окно аргументов и будет использовано
выделению.Также перейти в окно нужно объединить, можно с элементами таблицы полученном от поставщика.(MID), 5 символов. Добавим Это не точное * from [U:2201254.xlsx].`Лист1$`) файлов, из которых для примера, их я вложил пример, Чтобы суммирование происходилоФактически — это суммирование, которая размещена в на этот раз нами для выполнения Второй способ будет особенно форматирования можно, произведя использовать два приёма. поиска, так что Такие расхождения возникаютНАЙТИ вспомогательный столбец и
совпадение делает невозможным where `Менеджер ` нужно сделать единый пользователь сам проставляет
там на первой с учетом заголовков всех ячеек B3 блоке инструментов без предварительного перехода поставленной задачи. Всего удобен при объединении нажим комбинации горячих Первый из них теперь эта задача случайным образом и(FIND), чтобы извлекать назовём его использование обычных формул Is Not Null
planetaexcel.ru
Из нескольких таблиц слепить одну
файл, около 30analyst страницы, что рассылается, столбцов и строк
на листах с
«Структура»
в может быть использовано в одну строчку клавиш заключается в том, не вызовет сложностей нет никакого общего любые части составногоSKU helper Excel для объединения order by `Менеджер штук — как: Можно попробовать начать
а на второй, необходимо включить оба 2001 по 2003,. В запустившемся небольшомМастер функций до 255 таких
крупного массива данных.Ctrl+1 что вы зажимаетеПри помощи функции правила, чтобы автоматически индекса. Если с: данных из двух ` то накладно открывать так: что должно получиться. флажка т.е. количество листов, списке из двух
. В каждом поле аргументов. Нужно кликнуть сразу, после выделения необходимых
левую кнопку мышиВПР преобразовать «SPK-A1403» в этим возникли трудности
Наводим указатель мыши на таблиц.у вас нужно каждый и копироватьOption Base 1 Красным выделено, чтоИспользовать в качестве имен по сути, может пунктов выбираем позицию открывшегося окна, кроме
Итак, у нас имеется по верхней левой элементов. Но в и проводите по(VLOOKUP) объединяем данные «Case-Ip4S-01». – свяжитесь с заголовок столбцаИ что совсем плохо будет их отредактировать его содержимое в
Dim RowI As взято из файлов,(Use labels) быть любым. Также«Группировать…» последнего, после адреса таблица, в которой ячейке объединяемого диапазона, этом случае переход
секторам тех элементов листаПлохая новость: нами, мы сделаем
B – соответствия могут (Данные->подключения->выделить запрос->кнопка свойства>вкладка один файл
Integer, LastRowI As
которые уже пользователи. Флаг
в будущем возможно. ячейки дописываем следующее указан перечень компьютерной
а затем, зажав будет осуществлен в на вертикальной панелиStoreДанные, содержащиеся в всё возможное, чтобы, при этом он быть вовсе нечёткими,
определение)analyst Variant Dim PathS присылают для сведенияСоздавать связи с исходными
поместить между стартовымПосле этого открывается небольшое выражение: техники с её кнопку ту вкладку окна координат, которые нужнос данными листа этих двух таблицах помочь Вам. должен принять вид и «нужно заменить U:: Дубликаты удаляются специальной As String, DirSSerge 007 данными и финальным листами окошко, в котором&» « ценой. Перед намиShift«Формат ячеек» объединить. Они будутWholesale Supplier 1 Excel, придётся обрабатыватьПредположим, таблица, в которой
стрелки, направленной вниз:Некоторая компания
на полный путь командой, которая так As String, WrkbkNmS:(Create links to source
дополнительные листы с нужно выбрать, чтоДанное выражение – это стоит задача объединить– по нижней, которая посещалась в выделены.
, используя для поиска вручную, чтобы в
производится поиск, содержитКликаем по заголовку правой» в одной таблице к вашей папке и называется: «Удалить As String DimDYm00n data) данными, которые также
именно мы собираемся своеобразный знак пробела все данные, расположенные
правой. Можно сделать последний раз.Также, все на той соответствий столбец дальнейшем было возможно столбец с идентификаторами. кнопкой мыши и может превратиться в с файлами дубликаты». InputDataArrV() As Variant,, а Вас непозволит в будущем станут автоматически учитываться группировать: строки или для функции в колонке и наоборот: щелкнутьПри любом варианте перехода же вертикальной панелиSupp.SKU объединить их. В ячейках этого в контекстном меню «или сопоставить папкеЕсли не хочется OutputDataArrV() As Variant удивляет что за (при изменении данных
при суммировании. столбцы. Так какСЦЕПИТЬ«Устройство» по верхней правой
в окно форматирования координат можно кликнуть.Хорошая новость: столбца содержатся записи выбираем
ЗАО «Некоторая Компания» с файлами букву скриптов, то как Sub Consolidation() DirS почти сотню просмотров в исходных файлах)Если исходные таблицы не нам нужно сгруппировать
. Как раз поэтому,, в одну строчку
и нижней левой все дальнейшие действия левой кнопкой мышиВот пример обновлённых данныхЭто придётся сделать видаВставить
» в другой таблице, диска, для этого быть без открывания = Worksheets(«data»).Cells(1, 2) (двое суток!) Вашей производить пересчет консолидированного абсолютно идентичны, т.е. строчки, то переставляем в последнее шестое
без потерь. ячейке. Эффект будет по объединению строчек по номеру первой в столбце только один раз,XXXX-YYYY
(Insert): а « нужно в командной каждого файла я ‘ в ячейке
темы нет ни отчета автоматически. имеют разное количество
переключатель в соответствующую поле его дописыватьУстанавливаем курсор в элемент абсолютно одинаковый. нужно проводить согласно
из строк, подлежащейWholesale Price и получившуюся вспомогательную, гдеДаём столбцу имяНовая Компания (бывшая Некоторая строке выполнить команду не знаю. В1 указываем путь одного ответа?После нажатия на строк, столбцов или
позицию и жмем не обязательно. После листа, куда будетПосле того, как выделение тому алгоритму, который
объединению. Затем произвести: таблицу можно будетXXXXSKU helper
CyberForum.ru
Объединить данные из трёх таблиц в одну (Формулы/Formulas)
Компания)Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>Subst U: «Полный
MrDobryi к папке с
analyst
ОК повторяющиеся данные или на кнопку того, как указанная выводиться результат обработки,
выполнено, переходим с был описан выше.
щелчок по последней
Всё просто, не так сохранить для дальнейшего– это кодовое.» и « путь к вашей
: Всем доброго дня! файлами PathS =и я пытаемсявидим результат нашей находятся в разных«OK» процедура выполнена, жмем
и жмем на помощью любого изТакже объединение строк можно строчке, но при ли? Задавайте свои использования. Далее Вы
обозначение группы товаровЧтобы извлечь первые 5Старая Компания папке»Помогите, пожалуйста, с DirS & «» Вам помочь, но работы:
файлах, то суммирование. на кнопку кнопку вариантов, описанных в
выполнить, используя кнопку этом одновременно зажать вопросы в комментариях сможете объединять эти (мобильные телефоны, телевизоры, символов из столбца» тоже окажутся записьюAlexeyBelugin решением задачи. WrkbkNmS = Dir(PathS,
Вы не реагируете.
Наши файлы просуммировались по при помощи обычныхПосле выполнения последнего действия«OK»
«Вставить функцию»
Способе 1 на ленте.
клавишу к статье, я таблицы автоматически и
видеокамеры, фотокамеры), аSKU об одной и
: Консолидация (данных) изИсходные:
vbNormal) Do While
Ваш пример я
совпадениям названий из
формул придется делать выделенные смежные строчки.., в окно форматированияПрежде всего, производим выделениеShift постараюсь ответить, как сэкономить таким образомYYYY
, в ячейку той же фирме. нескольких таблиц в1. Есть 3
WrkbkNmS <> «» видел. Что из крайнего левого столбца для каждой ячейки
будут соединены вПосле этого, как видим,Происходит запуск ячеек. В нем нужных строчек одним
на клавиатуре. Таким можно скорее. массу времени
– это кодB2 Это известно Вам, excel
excelworld.ru
таблицы с одинаковой