Как объединить несколько листов с одинаковыми заголовками в Excel?
Объединение данных из нескольких листов Excel с одинаковыми заголовками — распространённая, но зачастую трудоёмкая задача. Например, как показано на снимках экрана ниже, у вас может быть несколько рабочих листов — скажем, для разных месяцев, отделов или продуктов — в одной книге, и все они содержат информацию, структурированную по идентичным заголовкам столбцов. Когда возникает необходимость просмотреть, проанализировать или подготовить отчёт по всем этим данным в сводном виде, ручное копирование и вставка быстро превращаются в неэффективный и ошибкоопасный процесс, особенно по мере роста числа листов. К счастью, Excel предлагает несколько практичных методов, позволяющих объединять такие данные быстро, точно и с минимальными усилиями. Понимая преимущества каждого подхода, вы сможете выбрать решение, идеально соответствующее вашему рабочему процессу — будь то разовое объединение или динамическая консолидация с автоматическим обновлением.
Доступные решения:
Объединение листов с одинаковыми заголовками с помощью VBA
Консолидация листов Excel с одинаковыми заголовками с помощью функции «Консолидация»
Объединение или консолидация листов Excel с одинаковыми заголовками с помощью Kutools для Excel 
Объединение данных с использованием формул Excel (ИНДЕКС, ПОИСКПОЗ, ФИЛЬТР)
Объединение и добавление данных с помощью Power Query (Получение и преобразование)
Объединение листов с одинаковыми заголовками с помощью VBA
Если вы хотите быстро объединить все листы с одинаковыми заголовками столбцов в один — без вычислений и суммирования, — VBA предлагает автоматизированное решение. Этот подход идеально подходит тем, кто готов использовать макросы и регулярно сталкивается с задачей объединения данных в одной книге, где информация на всех листах начинается с ячейки A1 и имеет идентичную структуру.
Шаги выполнения:
1. Активируйте книгу, в которой нужно объединить листы. Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic для приложений.
2. В окне VBA выберите Вставка > Модуль. Затем вставьте приведённый ниже код VBA в окно кода.
VBA: объединение листов с одинаковыми заголовками
Sub Combine()
'UpdateByKutools20151029
Dim i As Integer
Dim xTCount As Variant
Dim xWs As Worksheet
On Error Resume Next
LInput:
xTCount = Application.InputBox("The number of title rows", "", "1")
If TypeName(xTCount) = "Boolean" Then Exit Sub
If Not IsNumeric(xTCount) Then
MsgBox "Only can enter number", , "Kutools for Excel"
GoTo LInput
End If
Set xWs = ActiveWorkbook.Worksheets.Add(Sheets(1))
xWs.Name = "Combined"
Worksheets(2).Range("A1").EntireRow.Copy Destination:=xWs.Range("A1")
For i = 2 To Worksheets.Count
Worksheets(i).Range("A1").CurrentRegion.Offset(CInt(xTCount), 0).Copy _
Destination:=xWs.Cells(xWs.UsedRange.Cells(xWs.UsedRange.Count).Row + 1, 1)
Next
End Sub3. Нажмите F5 или щёлкните кнопку Выполнить, чтобы запустить код. Появится запрос на ввод количества строк с заголовками (например, введите «1», если заголовки занимают только первую строку). См. снимок экрана:
4. Нажмите OK. Код автоматически объединит все данные с рабочих листов из текущей книги (исключая указанные строки заголовков) на новый лист с именем «Combined».
Советы и примечания:
- Ваши данные должны начинаться с ячейки A1 на каждом листе — в противном случае код будет работать некорректно.
- Все листы, подлежащие объединению, должны иметь одинаковую структуру — идентичные заголовки в том же порядке и, по возможности, в тех же столбцах, — чтобы избежать смещения данных.
- Этот код объединяет все рабочие листы только в текущей книге и не затрагивает листы из других книг.
- Обязательно сохраните книгу перед запуском кода: если есть несохранённые изменения, ошибка при выполнении макроса VBA может привести к их случайной потере.
Преимущества: Идеально подходит для быстрого разового объединения прямо в книге. Не требует формул или сторонних надстроек.
Ограничения: Код необходимо запускать вручную для каждого объединения. Не подходит, если нужно комбинировать данные из нескольких книг или требуется регулярное автоматическое обновление.
Объединение листов Excel с одинаковыми заголовками с помощью функции «Объединить»
Когда ваша цель — объединить и свести данные с одинаковыми заголовками строк и столбцов (например, итоги по нескольким регионам или отделам), встроенная в Excel функция «Объединить» предлагает структурированное решение. С её помощью можно выполнять различные сводные вычисления — суммирование, усреднение или подсчёт значений — используя метки заголовков для точного сопоставления данных из разных диапазонов.
Шаги выполнения:
1. Откройте все книги, содержащие листы, которые вы хотите объединить.
2. Установите курсор в пустую ячейку, куда вы хотите поместить объединённые (сведённые) данные.
3. Перейдите на вкладку «Данные» и нажмите кнопку «Объединить».
4. В диалоговом окне «Объединить»:
- Выберите нужный расчёт из списка Функция (например, Сумма, Среднее, Количество).
- Щёлкните в поле Ссылка, затем выделите диапазон, который нужно консолидировать с каждого рабочего листа. После выбора каждого диапазона нажмите Добавить, чтобы добавить его в список.
- Повторяйте предыдущий шаг, чтобы добавить все нужные диапазоны со всех листов, подлежащих объединению.
- Установите флажки Верхняя строка и Самый левый столбец в разделе Позиция метки, если ваши данные содержат заголовки в этих областях. Это обеспечит корректное объединение по меткам.
- Если вы хотите, чтобы результат оставался связанным с исходными данными (чтобы обновления в исходных листах автоматически отражались здесь), включите параметр Создать ссылки на исходные данные.
5. Нажмите кнопку «OK». Excel выполнит сводку и выравнивание диапазонов «Выбрать данные», используя указанную функцию, и объединит их в выбранной вами ячейке.
Практические советы:
- Используйте опцию Создать ссылки на исходные данные, если хотите, чтобы консолидированные результаты автоматически обновлялись при изменении исходных данных.
- Убедитесь, что заголовки во всех диапазонах полностью идентичны — включая написание и пробелы, — иначе консолидация по меткам не сработает корректно.
- Перед консолидацией рекомендуется скопировать исходные данные на резервный лист, особенно если они объёмные или сложные.
Преимущества: Универсальна и встроена во все современные версии Excel. Отлично подходит для сводных вычислений и периодической отчётности.
Ограничения: Не является по-настоящему динамическим: если вы позже добавите новые строки в источник, придётся повторить шаги объединения. Может вызывать сложности при несогласованной маркировке или некорректно заданных диапазонах.
Объединение или сведение листов Excel с одинаковыми заголовками с помощью Kutools для Excel
Если ваш рабочий процесс предполагает объединение листов с идентичными заголовками или одновременное сведение и вычисление данных из нескольких источников, Kutools для Excel предлагает гибкое и удобное решение. Kutools предоставляет специальный мастер «Объединить», который поддерживает многоуровневое объединение данных и может работать как с открытыми, так и с закрытыми книгами — значительно упрощая процесс и минимизируя риск ошибок.
Объединение листов Excel с одинаковыми заголовками
Сведение нескольких листов с одинаковыми заголовками
После установки Kutools для Excel выполните следующие действия:（Бесплатно скачать Kutools для Excel прямо сейчас!)
Объединение листов Excel с одинаковыми заголовками
1. Перейдите на вкладку KUTOOLS PLUS и нажмите кнопку «Объединить», чтобы запустить мастер объединения. В мастере выберите пункт Копировать данные из нескольких книг в один лист. См. скриншоты ниже для справки:
2. Нажмите кнопку «Далее >>», чтобы перейти к мастеру Объединить листы — Шаг 2 из 3, затем:
2) Выберите книгу(и), содержащую(ие) листы, которые необходимо объединить; можно выбрать несколько файлов.
3) Отметьте листы, которые требуется включить.
4) Нажмите
, чтобы указать Диапазон данных для каждого листа. Если все листы используют один и тот же диапазон ячеек, достаточно выбрать его на одном листе и нажать Тот же диапазон, чтобы применить его ко всем листам.3. Нажмите кнопку «Далее >>», чтобы перейти к мастеру «Объединение листов» — Шаг 3 из 3, затем установите флажок напротив опции «Объединить данные по строкам» и укажите значение 1 для параметра «Номер строки заголовков» (или 0, если ваши данные не содержат заголовков).
4. Нажмите кнопку «Готово». Если появится запрос о сохранении сценария объединения для последующего использования, выберите «Да» (чтобы сохранить) или «Нет» (чтобы отменить).
Мастер создаст новую рабочую книгу, в которой будут объединены все указанные листы с сохранением выравнивания заголовков.
Сведение нескольких листов с одинаковыми заголовками
После установки Kutools для Excel выполните следующие действия:（Бесплатно скачать Kutools для Excel прямо сейчас!)
1. Выберите пункт KUTOOLS PLUS → «Объединить», чтобы открыть мастер объединения, и выберите вариант «Объединить и вычислить данные из нескольких книг в один лист». См. скриншот:
2. Нажмите кнопку «Далее > >», чтобы перейти к мастеру Объединить листы — Шаг 2 из 3. Выполните следующие действия:
2) Установите флажки напротив книг, с которыми хотите работать (можно выбрать несколько файлов).
3) Выберите нужные листы.
4) Нажмите
, чтобы определить, какие Диапазон следует объединить. Используйте Тот же диапазондля единообразного расположения диапазонов на всех листах.3. Продолжайте, нажимая кнопку «Далее>>»: выберите функцию (например, суммирование или усреднение), которую вы хотите использовать при объединении диапазонов, и укажите, какие метки (Верхняя строка и/или Самый левый столбец) применяются к вашим объединённым данным. См. скриншот:
4. Нажмите кнопку «Готово». Вам предложат сохранить сценарий для последующего использования — выберите «Да» или «Нет» в зависимости от необходимости.
Затем выберите диапазон, который будет сводиться и выводиться в новую рабочую книгу.
Главное преимущество функции «Объединить» в Kutools — возможность легко справляться со сложными задачами объединения данных из нескольких открытых или даже закрытых книг, что делает её идеальным решением как для разовых операций, так и для регулярных рабочих процессов, требующих интеграции информации с разных листов.
Объединение данных с помощью формул Excel (ИНДЕКС, ПОИСКПОЗ, ФИЛЬТР)
Для пользователей, которые хотят сохранить динамическую связь между исходными листами и объединёнными данными, Excel предлагает гибкое решение на основе формул. Этот метод использует такие функции, как ИНДЕКС, ПОИСКПОЗ или ФИЛЬТР (доступна в Microsoft 365 и Excel 2021+), чтобы объединять и выравнивать данные из нескольких листов с одинаковыми заголовками. Он особенно полезен, когда исходные листы регулярно обновляются — ведь результаты на объединённом листе автоматически отражают все изменения.
Применимый сценарий: Лучше всего подходит, когда вам нужно, чтобы объединённые данные автоматически обновлялись при изменении исходных листов — без использования макросов или надстроек.
Пример формулы — простое объединение с помощью ФИЛЬТР (Excel 365/2021+)
1. На новом листе «Объединённый» выберите первую ячейку, в которую хотите поместить данные (например, ячейку)A2), и введите следующую формулу для объединения данных с Лист1 (предполагая, что заголовки находятся в строке 1):
=FILTER(Sheet1!A2:D100,Sheet1!A2:A100<,>,"")Эта формула извлекает все непустые значения с Лист1. При необходимости скорректируйте диапазон A2:D100.
Чтобы объединить данные из дополнительных листов, используйте функцию ВСТАК(в современных версиях Excel):
=VSTACK(FILTER(Sheet1!A2:D100,Sheet1!A2:A100<,>,""),FILTER(Sheet2!A2:D100,Sheet2!A2:A100<,>,""),FILTER(Sheet3!A2:D100,Sheet3!A2:A100<,>,""))2. Нажмите клавишу Enter — и результаты автоматически заполнят ячейки вниз, объединяя все выбранные листы. Любые обновления или новые строки в исходных листах будут отображаться автоматически.
Советы:
- Убедитесь, что все объединяемые листы имеют идентичные заголовки, а диапазон достаточно велик, чтобы охватить все возможные строки. При увеличении объёма данных скорректируйте ссылки на диапазоны.
- Если вы используете Excel 2019 или более раннюю версию, функции ФИЛЬТР (FILTER) и ВСТАК (VSTACK) недоступны. Вместо них используйте ИНДЕКС/ПОИСКПОЗ (INDEX/MATCH), чтобы извлекать отдельные строки, либо копируйте и вставляйте как значения.
- Если вы хотите оставить заголовки только один раз, вручную скопируйте строку заголовков над результатом формулы.
Преимущества: Объединённые данные автоматически обновляются при изменении исходных листов — никакого VBA или сторонних инструментов не требуется!
Ограничения: Современные функции, такие как ФИЛЬТР и ВСТАК, доступны только в последних версиях Excel. Работа с большими наборами данных может замедлять вычисления. По мере роста объёма данных формулы требуют тщательного управления диапазонами.
Объединение и добавление данных с помощью Power Query (Получить и преобразовать)
Power Query (также известный как «Получить и преобразовать») — это мощная встроенная функция, доступная в Excel 2016 и более поздних версиях, а также в Microsoft 365. Она позволяет подключаться к данным, преобразовывать их и объединять из нескольких листов или книг без изменения исходных данных. Такой подход идеально подходит для регулярных задач сведения, работы с большими наборами данных или очистки и преобразования данных перед объединением.
Применимый сценарий: Настоятельно рекомендуется для сложных задач объединения, особенно если данные часто обновляются, файлы имеют большой объём или требуется очистка и преобразование данных в процессе объединения.
Шаги выполнения:
- Щёлкните в любом месте первой таблицы данных. Перейдите в меню Данные > Из таблицы/диапазона. Если ваш лист ещё не отформатирован как таблица, Excel предложит создать её — нажмите OK.
- Нажмите Закрыть и загрузить > Закрыть и загрузить, чтобы загрузить результат на новый лист. Теперь у вас появится новый лист с таблицей.
- Повторите описанные выше действия для каждого рабочего листа, чтобы создать отдельный запрос для каждого из них.
- Объединение запросов:В области Запросы и подключениящёлкните правой кнопкой мыши по таблице и выберите пункт Добавить в конец (Append) из контекстного меню.
- В диалоговом окне Добавить в конец, если нужно объединить более двух таблиц, выберите опцию Три или более таблиц. Добавьте каждую таблицу по отдельности в поле Таблицы для добавления, затем нажмите OK.
- Завершение и загрузка: Все данные из указанных рабочих листов успешно объединены. После проверки и, при необходимости, корректировки порядка строк или столбцов нажмите Главная > Закрыть и загрузить, чтобы вывести объединённые данные на новый лист.
Советы:
- Данные не обязательно должны находиться в одной и той же книге — Power Query может подключаться к нескольким книгам и объединять данные из них.
- Если вы добавляете новые данные в исходные листы или файлы, просто щёлкните правой кнопкой мыши по итоговой таблице и выберите пункт Обновить, чтобы автоматически обновить объединение.
- Вы можете использовать инструменты преобразования Power Query для очистки, фильтрации и форматирования данных во время загрузки, что особенно полезно, если некоторые листы содержат дополнительные столбцы или требуют согласования форматов.
Преимущества: Обрабатывает крупные и сложные объединения с минимальным ручным вмешательством. Обеспечивает высокую масштабируемость, поддержку преобразования данных при объединении и автоматизацию будущих обновлений.
Ограничения: Первоначальная настройка требует выполнения нескольких шагов и знакомства с инструментами Power Query. В некоторых версиях Excel может отсутствовать полный функционал «Получить и преобразовать».
Краткое содержание и советы по устранению неполадок:
- Для быстрого однократного объединения, когда данные начинаются с одинаковых позиций на каждом листе, VBA или Kutools могут быть очень эффективными.
- Если для вас важны регулярные динамические обновления и подключение к постоянно пополняемым наборам данных, рассмотрите решения на основе формул Excel или Power Query.
- Всегда проверяйте, чтобы исходные данные имели одинаковые заголовки и структуру — это поможет избежать ошибок при объединении.
- По возможности используйте такие функции, как Обновить в Power Query или автоматическое обновление формул для регулярных задач — вместо ручного копирования и вставки.
- Перед любым объединением обязательно создайте резервные копии исходных данных — это надёжная защита от случайной перезаписи и ошибок.
- Если появляются ошибки «#ССЫЛ!» или «#ЗНАЧ!», внимательно проверьте диапазоны в формулах и убедитесь, что все указанные листы существуют и содержат корректные данные.
- Если вы используете VBA или надстройки, убедитесь, что настройки антивируса и макросов разрешают их работу.
- Протестируйте решение на примере тестовой книги перед тем, как применять его к важным или рабочим файлам.
Связанные статьи
- Как объединить несколько ячеек с ведущими нулями в одну ячейку в Excel?
- Как сгенерировать список всех возможных комбинаций из 4 цифр в Excel?
- Как сохранить каждый лист как отдельный Текстовый файл из книги?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек