Объединение двух или нескольких таблиц
Можно объединить (объединить) строки из одной таблицы в другую, просто вставив данные в первые пустые ячейки под целевой таблицей. Таблица увеличится в размере, включив в нее новые строки. Если строки в обеих таблицах совпадают, можно объединить столбцы одной таблицы с другой, вставив их в первые пустые ячейки справа от таблицы. В этом случае таблица также будет увеличиваться, чтобы вместить новые столбцы.
Объединение строк на самом деле довольно просто, но объединение столбцов может быть сложным, если строки одной таблицы не соответствуют строкам в другой таблице. Некоторых проблем с выравниванием можно избежать, если воспользоваться функцией ВПР.
Объединение двух таблиц с помощью функции ВПР
В приведенном ниже примере вы увидите две таблицы, которые ранее имели другие имена : «Blue» и «Orange». В таблице «Синяя» каждая строка представляет собой позицию заказа. Например, заказ № 20050 содержит две позиции, № 20051 — одну, № 20052 — три и т. д. Мы хотим объединить столбцы «Код продажи» и «Регион» с таблицей «Синяя» с учетом соответствия значений в столбце «Номер заказа» таблицы «Оранжевая».
Значения идентификатора заказа повторяются в синей таблице, но значения Идентификатора заказа в оранжевой таблице уникальны. Если бы мы просто скопировать и вставить данные из таблицы Orange, значения Sales ID и Region для второй строки заказа 20050 были бы отключены на одну строку, что приведет к изменению значений в новых столбцах в таблице Blue.
Ниже приведены данные для таблицы Blue, которую можно скопировать на пустой лист. Вставив его на лист, нажмите клавиши CTRL+T, чтобы преобразовать его в таблицу, а затем переименуйте таблицу Excel синим .
Ниже приведены данные для таблицы Orange. Скопируйте его на тот же лист. Вставив его на лист, нажмите клавиши CTRL+T, чтобы преобразовать его в таблицу, а затем переименуйте таблицу Orange.
Необходимо убедиться, что значения Идентификатора продаж и Региона для каждого заказа соответствуют каждому уникальному элементу строки заказа. Для этого давайте вставьте заголовки таблицы Sales ID и Region в ячейки справа от синей таблицы и использовать формулы ВПР, чтобы получить правильные значения из столбцов Sales ID и Region таблицы Orange.
Вот как это сделать.
- Скопируйте заголовки Идентификатор продаж и Регион в оранжевую таблицу (только эти две ячейки).
- Вставьте заголовки в ячейку справа от заголовка Product ID таблицы Blue. Теперь таблица «Синяя» содержит пять столбцов, включая новые — «Код продажи» и «Регион».
- В таблице «Синяя», в первой ячейке столбца «Код продажи» начните вводить такую формулу: =ВПР(
- В таблице «Синяя» выберите первую ячейку столбца «Номер заказа» — 20050. Частично завершенная формула выглядит следующим образом:
Выражение [@[Номер заказа]] означает, что нужно взять значение в этой же строке из столбца «Номер заказа». Введите точку с запятой и выделите всю таблицу «Оранжевая» с помощью мыши. В формулу будет добавлен аргумент Оранжевая[#Все]. - Введите точку с запятой, число 2, еще раз точку с запятой, а потом 0, вот так: ;2;0
- Нажмите клавишу ВВОД, и законченная формула примет такой вид:
Выражение Оранжевая[#Все] означает, что нужно просматривать все ячейки в таблице «Оранжевая». Число 2 означает, что нужно взять значение из второго столбца, а 0 — что возвращать значение следует только в случае точного совпадения. Обратите внимание: Excel заполняет ячейки вниз по этому столбцу, используя формулу ВПР. - Вернитесь к шагу 3, но в этот раз начните вводить такую же формулу в первой ячейке столбца «Регион».
- На шаге 6 вместо 2 введите число 3, и законченная формула примет такой вид:
Между этими двумя формулами есть только одно различие: первая получает значения из столбца 2 таблицы «Оранжевая», а вторая — из столбца 3. Теперь все ячейки новых столбцов в таблице «Синяя» заполнены значениями. В них содержатся формулы ВПР, но отображаются значения. Возможно, вы захотите заменить формулы ВПР в этих ячейках фактическими значениями. - Выделите все ячейки значений в столбце «Код продажи» и нажмите клавиши CTRL+C, чтобы скопировать их.
- На вкладке Главная щелкните стрелку под кнопкой Вставить.
- В коллекции параметров вставки нажмите кнопку Значения.
- Выделите все ячейки значений в столбце «Регион», скопируйте их и повторите шаги 10 и 11. Теперь формулы ВПР в двух столбцах заменены значениями.
Дополнительные сведения о таблицах и функции ВПР
- Как добавить или удалить строку или столбец в таблице
- Использование структурированных ссылок в формулах таблиц Excel
- Использование функции ВПР (учебный курс)
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Как объединить таблицы в excel
Объединение нескольких таблиц в одну
Для того, чтобы быстро просуммировать показатели из разных периодов при наличии одинаковых по структуре отчетов, можно воспользоваться встроенным функционалом «Консолидация».
Для этого на выбранном листе:
-
Нажимаем на вкладке меню «Данные» кнопку «Консолидация»
Выполняем то же самое для каждого отчета. В окне «Консолидация» для нашего примера внизу ставим галки на «Подписи верхней строки» и «Значения левого столбца». Также можно установить параметр «Создавать связи с исходными данными» — тогда показатели в нашей новой таблице будут изменяться при корректировке параметров в исходных данных.
То же самое можно сделать и для отдельных файлов, но об этом в следующей статье!
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
Объединение данных с нескольких листов
Если данные, которые требуется проанализировать, представлены на нескольких листах или в нескольких книгах, их можно объединить на одном листе с помощью команды «Консолидация». Например, если есть отдельный лист расходов для каждого регионального представительства, с помощью консолидации можно создать на базе этих данных корпоративный лист расходов. Такой лист может содержать итоговые и средние данные по продажам, текущим уровням запасов и наиболее популярным продуктам в рамках всей организации.
Тип консолидации следует выбирать с учетом того, как выглядят объединяемые листы. Если данные на листах расположены единообразно (названия строк и столбцов могут при этом различаться), воспользуйтесь консолидацией по расположению. Если же на листах для соответствующих категорий используются одинаковые названия строк и столбцов (данные при этом могут быть расположены по-разному), используйте консолидацию по категории.
Консолидация по расположению
Для консолидации по расположению диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов.
- Откройте каждый исходный лист и убедитесь, что данные на каждом листе расположены в одинаковом положении.
- На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.
Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
Консолидация по категории
Для консолидации по категории диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов. Кроме того, категории должны быть последовательно помечены. Например, если один из столбцов называется Сред. , а другой — Среднее, консолидация не просуммирует эти столбцы.
- Откройте каждый из исходных листов.
- На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.
Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
Примечание: Любые названия, не совпадающие с названиями в других исходных областях, могут привести к появлению в консолидированных данных отдельных строк или столбцов.
Важно: Microsoft Office для Mac 2011 больше не поддерживается. Перейдите на Microsoft 365, чтобы работать удаленно с любого устройства и продолжать получать поддержку.
Консолидация по расположению
Для консолидации по расположению диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов.
- Откройте каждый из исходных листов и убедитесь в том, что данные на них расположены одинаково.
- На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.
Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
Консолидация по категории
Для консолидации по категории диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов. Кроме того, категории должны быть названы одинаково. Например, если один из столбцов называется Сред. , а другой — Среднее, консолидация не просуммирует эти столбцы.
- Откройте каждый из исходных листов.
- На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.
Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
Примечание: Любые названия, не совпадающие с названиями в других исходных областях, могут привести к появлению в консолидированных данных отдельных строк или столбцов.
покупка
Как объединить две таблицы, сопоставив столбец в Excel?
Предположим, у вас есть две таблицы на двух разных листах, одна — основная таблица, а другая — новая таблица данных. Теперь вы хотите объединить эти две таблицы с помощью соответствующего столбца и обновить данные, как показано на скриншоте ниже, как вы можете быстро решить эту проблему в Excel? В этой статье я расскажу о приемах быстрого объединения двух таблиц по столбцу.
Объединение двух таблиц по столбцу с помощью ВПР
Объединение двух таблиц по столбцу с помощью функции объединения таблиц
Объедините несколько таблиц по заголовкам столбцов в один лист с помощью Kutools for Excel
Объединение двух таблиц по столбцу с помощью ВПР
Чтобы объединить две таблицы по сопоставлению столбцов, вы можете применить функцию ВПР.
1. Выберите пустую ячейку рядом с основной таблицей, например C2. Смотрите скриншот:
2. Введите эту формулу =IFERROR(VLOOKUP($A2,Sheet2!$A$1:$C$6,COLUMN(A1),FALSE),»») в нее, затем перетащите маркер автозаполнения вправо, пока не появится пустая ячейка, а затем перетащите маркер заполнения вниз к ячейкам, в которых требуется эта формула. Смотрите скриншот:
3. Вы можете добавить новые заголовки столбцов в объединенную таблицу. Смотрите скриншот:
Объединение двух таблиц по столбцу с помощью функции объединения таблиц
Функция ПРОСМОТР не доставит вам хлопот, но, к счастью, есть полезная функция. Слияние таблиц in Kutools for Excel который может быстро добавлять и обновлять новые элементы в основной таблице на основе другой таблицы.
1. Выберите основную таблицу, которую вы хотите использовать для обновления и объединения с другой, щелкните Кутулс Плюс > Слияние таблиц.
2. Затем в Слияние таблиц в диалоговом окне мастера выберите таблицу поиска в Выберите таблицу поиска текстовое поле, проверьте параметры, если у таблиц есть заголовки.
3. Нажмите Следующаяна шаге 2 мастера отметьте ключевой столбец, на основе которого вы хотите объединить.
4. Нажмите Следующаяна шаге 3 мастера отметьте столбцы, в которых вы хотите обновить данные.
5. Нажмите Следующая к следующему шагу отметьте новые столбцы, которые вы хотите добавить к основным данным.
6. Нажмите Следующая, на последнем шаге вам нужно установить некоторые параметры для новых элементов, например, выделить обновленные с помощью цвета фона.
7. Нажмите Завершить. Основная таблица обновлена и дополнена новыми элементами.
Демо
Объедините несколько таблиц по заголовкам столбцов в один лист с помощью Kutools for Excel
Если вы хотите объединить несколько таблиц на листах или книг по заголовкам столбцов в один лист, как показано на скриншоте ниже, вы можете применить мощный Сочетать Функция Kutools for Excel чтобы быстро ее решить.
После установки Kutools for Excel, пожалуйста, сделайте, как показано ниже 🙁 Скачать бесплатно Kutools for Excel сейчас!)
1. Включите Excel и щелкните Кутулс Плюс > Сочетать, и в появившемся диалоговом окне отметьте Объедините несколько листов из книг в один лист. Смотрите скриншот:
2. Затем нажмите Следующая идти на Шаг 2 из 3 диалоговое окно и добавьте книги, нажав Добавить кнопку, затем выберите имена листов, из которых вы хотите объединить Список рабочих листов раздел. Смотрите скриншот:
3. Нажмите Следующая идти на Шаг 3 из 3 диалог. На последнем шаге диалога укажите в нем номер строки заголовка, вот он, также вы можете указать другие параметры настройки по своему усмотрению. Смотрите скриншот:
4. Нажмите Завершить, появится диалоговое окно для выбора папки для размещения объединенной новой книги. И нажмите Save. перейти к первому Kutools for Excel диалоговое окно, которое напоминает вам, открывать ли объединенную книгу, нажмите Да открывать или нет, а потом второй Kutools for Excel Появится диалоговое окно с напоминанием о необходимости сохранения этого сценария объединения, щелкните любые параметры по своему усмотрению.
Теперь листы были объединены в один лист на основе заголовков столбцов.