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

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

Объединение нескольких таблиц в одну

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

Для этого на выбранном листе:

Нажимаем на вкладке меню «Данные» кнопку «Консолидация»

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

То же самое можно сделать и для отдельных файлов, но об этом в следующей статье!

Объединение двух или нескольких таблиц

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

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

Объединение двух таблиц с помощью функции ВЛОП

В приведенного ниже примере вы увидите две таблицы с другими именами: «Синяя» и «Оранжевая». В таблице «Синяя» каждая строка представляет собой позицию заказа. Например, заказ № 20050 содержит две позиции, № 20051 — одну, № 20052 — три и т. д. Мы хотим объединить столбцы «Код продажи» и «Регион» с таблицей «Синяя» с учетом соответствия значений в столбце «Номер заказа» таблицы «Оранжевая».

Значения «ИД заказа» повторяются в таблице «Синяя», но значения «ИД заказа» в таблице «Оранжевая» уникальны. Если просто скопировать и ввести данные из таблицы «Оранжевая», значения «ИД продаж» и «Регион» для второй строки заказа 20050 будут отключены на одну строку, что изменит значения в новых столбцах таблицы «Синяя».

Вот данные для таблицы «Синяя», которую можно скопировать на пустой лист. После в таблицы нажмите CTRL+T, чтобы преобразовать ее в таблицу, а затем переименуйте таблицу Excel синюю.

Вот данные для таблицы «Оранжевая». Скопируйте его на тот же самый таблицу. После в таблицы нажмите CTRL+T, чтобы преобразовать ее в таблицу, а затем переименуйте таблицу в Оранжевая.

Нам необходимо обеспечить правильное выравнивание значений «ИД продаж» и «Регион» для каждого заказа с каждым уникальным элементом строки заказа. Для этого впустим заголовки таблицы «ИД продажи» и «Регион» в ячейки справа от таблицы «Синяя», а затем с помощью формулЫ ВЗ ПРОСМОТР выберем правильные значения из столбцов «ИД продажи» и «Регион» таблицы «Оранжевая».

Вот как это сделать.

1. Скопируйте заголовки «ИД продажи» и «Регион» в таблице «Оранжевая» (только эти две ячейки).
2. В ячейку справа от заголовка «ИД товара» таблицы «Синяя». Теперь таблица «Синяя» содержит пять столбцов, включая новые — «Код продажи» и «Регион».
3. В таблице «Синяя», в первой ячейке столбца «Код продажи» начните вводить такую формулу: =ВПР(
4. В таблице «Синяя» выберите первую ячейку столбца «Номер заказа» — 20050. Частично заполненная формула выглядит так: Выражение [@[Номер заказа]] означает, что нужно взять значение в этой же строке из столбца «Номер заказа». Введите точку с запятой и выделите всю таблицу «Оранжевая» с помощью мыши. В формулу будет добавлен аргумент Оранжевая[#Все].
5. Введите точку с запятой, число 2, еще раз точку с запятой, а потом 0, вот так: ;2;0
6. Нажмите клавишу ВВОД, и законченная формула примет такой вид: Выражение Оранжевая[#Все] означает, что нужно просматривать все ячейки в таблице «Оранжевая». Число 2 означает, что нужно взять значение из второго столбца, а 0 — что возвращать значение следует только в случае точного совпадения. Обратите внимание: Excel заполняет ячейки вниз по этому столбцу, используя формулу ВПР.
7. Вернитесь к шагу 3, но в этот раз начните вводить такую же формулу в первой ячейке столбца «Регион».
8. На шаге 6 вместо 2 введите число 3, и законченная формула примет такой вид: Между этими двумя формулами есть только одно различие: первая получает значения из столбца 2 таблицы «Оранжевая», а вторая — из столбца 3. Теперь все ячейки новых столбцов в таблице «Синяя» заполнены значениями. В них содержатся формулы ВПР, но отображаются значения. Возможно, вы захотите заменить формулы ВПР в этих ячейках фактическими значениями.
9. Выделите все ячейки значений в столбце «Код продажи» и нажмите клавиши CTRL+C, чтобы скопировать их.
10. На вкладке Главная щелкните стрелку под кнопкой Вставить.
11. В коллекции параметров вставки нажмите кнопку Значения.
12. Выделите все ячейки значений в столбце «Регион», скопируйте их и повторите шаги 10 и 11. Теперь формулы ВПР в двух столбцах заменены значениями.

Дополнительные сведения о таблицах и функции ВПР

• Как добавить или удалить строку или столбец в таблице
• Использование структурированных ссылок в формулах таблиц Excel
• Использование функции ВПР (учебный курс)

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

покупка

Как объединить две таблицы, сопоставив столбец в 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 Появится диалоговое окно с напоминанием о необходимости сохранения этого сценария объединения, щелкните любые параметры по своему усмотрению.

Теперь листы были объединены в один лист на основе заголовков столбцов.

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

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

Инструкция

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

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

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

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

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

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

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

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

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

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

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

Private Sub Worksheet_Change(ByVal Target As Range) ActiveWorkbook.RefreshAll End Sub

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

Важно:

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

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

Private Sub Workbook_Open() Dim q As String On Error Resume Next q = Application.ThisWorkbook.Path & "\" & Application.ThisWorkbook.Name 'Определяем текущий путь к файлу With ActiveWorkbook.Connections("Запрос из Excel Files").ODBCConnection 'Имя запроса .Connection = "ODBC;DSN=Excel Files;DBQ=" & q & _ ";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;" 'Меняем строку подключения End With End Sub

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

Файлы для скачивания:

Файл	Описание	Размер файла:	Скачивания
Пример	21 Кб	2888

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

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

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

Добавить комментарий

Комментарии

# Евгений 18.07.2023 10:13

Добрый день. помогите сформировать одну таблицу из множеста разных книг(таблицы одинаковые). В каждой книге несколько вкладок с одинаковыми названиями.

# Иркэ 24.06.2023 20:49

Доброго времени. Есть две таблицы с данными-1: ФИО, место госпитализации, 2: ФИО ,группа инвалидности. Не все ФИО в 1 и 2 таблицах не однозначны. Как объединить таблицы так,чтобы они объединились по ФИО, место госпитализации, группа инвалидности?
Благодарю

# Виктория Р. 20.04.2023 18:26

Здравствуйте, есть одна общая таблица на весь коллектив, когда один из работников вносит в нее данные другой работник не может вывести в неё данные. Как можно решить эту проблему?