Учебник Excel: Объединение нескольких книг/листов в одну
В Excel объединение листов — это распространённая, но довольно сложная задача для большинства пользователей, особенно для новичков. В этом руководстве собраны практически все возможные сценарии объединения и предложены профессиональные решения для каждого случая. Вы можете быстро найти нужный ответ, перейдя по ссылке в списке навигации ниже, либо изучить все способы по порядку, если хотите освоить навыки объединения.
В Excel объединение можно разделить на два типа: только объединение содержимого и объединение с выполнением вычислений.
Примечание
В этом руководстве я создал несколько листов и данных для наглядного объяснения методов. Вы можете изменить ссылки по своему усмотрению при использовании приведённого ниже VBA-кода или формул, либо скачать примеры для непосредственного тестирования методов.
Объединить содержимое
Вот книга с4 листами, которые нужно объединить в один лист.
 |  |
 |  |
Нажмите, чтобы скачать пример файла
1.1 Объединить все листы в один лист с помощью VBA-кода
В Excel, кроме традиционного способа — Копировать и Вставить, вы можете использовать VBA-код для быстрого объединения всех листов в один.
1. Нажмите F11 и Alt клавиши для открытия Microsoft Visual Basic for Applications окна.
2. Затем во всплывающем окне нажмите Вставить > Модуль чтобы добавить новый пустой модуль.
3. Скопируйте и вставьте приведённый ниже код в новый модуль.
VBA: Объединить все листы в один
Sub CombineAllSheetsIntoOneSheet()
'UpdatebyExtendoffice
Dim I As Long
Dim xRg As Range
On Error Resume Next
Worksheets.Add Sheets(1)
ActiveSheet.Name = "Combined"
For I = 2 To Sheets.Count
Set xRg = Sheets(1).UsedRange
If I > 2 Then
Set xRg = Sheets(1).Cells(xRg.Rows.Count + 1, 1)
End If
Sheets(I).Activate
ActiveSheet.UsedRange.Copy xRg
Next
End Sub
В приведённом выше коде скрипт “Combined” — это имя листа, на который помещается объединённое содержимое. Вы можете изменить это имя по своему усмотрению.
4. Нажмите F5 для запуска кода — будет создан лист с именем Combined в начале всех листов, куда поместится всё содержимое.
1.2 Объединить все таблицы или именованные диапазоны с помощью Query (Excel2016 и новее)
Если вы работаете в Excel2016 или новее, функция Query позволяет объединить все созданные таблицы или именованные диапазоны в один за раз.
Нажмите, чтобы скачать пример файла
Сначала убедитесь, что диапазоны преобразованы в таблицы или именованные диапазоны. Для создания таблицы и именованного диапазона обратитесь к статьям How to convert range to table or vice versa in Excel и Define and use names in formulas.
1. Откройте книгу, которую хотите объединить, нажмите Данные > Новый запрос > Из других источников > Пустой запрос.
2. В появившемся окне Query Editor перейдите к строке формул и введите следующую формулу.
=Excel.CurrentWorkbook()
Нажмите Enter — все таблицы в текущей книге будут перечислены.
3. Нажмите кнопку развёртывания рядом с Текст и выберите Развернуть панель и Выбрать все столбцы флажок.
4. Нажмите OK. Все таблицы будут перечислены одна за другой.
Вы можете щёлкнуть правой кнопкой мыши по заголовку столбца, чтобы выполнить другие действия с выбранным столбцом.
5. Затем нажмите Файл > Закрыть и загрузить в….
6. В Загрузить в диалоге выберите Таблица и укажите место, куда нужно загрузить объединённую таблицу, в разделе Выберите, куда загружать данные нажмите Загрузить.
Теперь создан новый лист для размещения всех объединённых таблиц.
1.3 Объединить все листы в один лист с помощью удобного инструмента
Если вышеуказанные методы вам не подходят, попробуйте удобный инструмент Kutools для Excel. Его функция Объединить очень мощная: она не только объединяет все листы в один, но и выполняет расширенные задачи, такие как объединение листов в одну книгу, объединение листов с одинаковым именем, объединение только выбранных листов, объединение листов из разных файлов и многое другое.
1. Откройте книгу, листы которой хотите объединить, нажмите Kutools Plus > Объединить. Появится диалоговое окно с напоминанием о пароле. Если книги, которые вы используете, не защищены паролем, просто нажмите OK чтобы продолжить.
2. В Объединить данные – Шаг1 из3 окне выберите Копировать данные из нескольких книг в один лист и нажмите Далее кнопку.
3. На шаге2 из3 по умолчанию текущая книга уже отмечена в списке рабочих книг, а все листы текущей книги отмечены в списке листов. Нажмите Далее для продолжения.
Примечание: если у вас открыто несколько книг, все они будут отображаться в списке рабочих книг. Отметьте только ту, которую хотите использовать.
4. На последнем шаге выберите режим объединения по вашему усмотрению: Объединить данные по строкам или Объединить данные по столбцам; затем укажите другие параметры по необходимости. Нажмите Завершить кнопку.
5. Появится диалоговое окно для выбора папки, куда будет сохранена объединённая книга. Вы можете переименовать её в поле Имя файла и нажать Сохранить для завершения.
6. Теперь все листы объединены, и появится диалоговое окно с предложением сохранить параметры как сценарий. Нажмите Да или Нет по вашему желанию.
Откроется новая книга с результатом объединения. Перейдите по ссылке Выходной файл, чтобы проверить объединённый лист.
Объединить данные по строкам
Объединить данные по столбцам
1.4 Дополнительно об объединении листов в один лист
1.41 С помощью удобного инструмента объединить только выбранные листы в один лист
Если вы хотите объединить только некоторые листы книги в один, стандартных средств Excel для этого нет. Но функция Объединить в Kutools для Excel справится с этой задачей.
1. Откройте нужную книгу и нажмите Kutools Plus > Объединить > OK для запуска мастера объединения.
2. В Шаг1 из3 окне выберите Копировать данные из нескольких книг в один лист и нажмите Далее кнопку.
3. На шаге2 из3 по умолчанию текущая книга уже отмечена в списке рабочих книг, а все листы текущей книги отмечены в списке листов. Снимите отметки с листов, которые не нужно объединять, и нажмите Далее для продолжения.
4. На последнем шаге укажите параметры по необходимости. Нажмите Завершить кнопку.
5. Появится диалоговое окно для выбора папки, куда будет сохранена объединённая книга. Вы можете переименовать её в поле Имя файла и нажать Сохранить для завершения.
6. Теперь объединены только отмеченные листы, и появится диалоговое окно с предложением сохранить параметры как сценарий. Нажмите Да или Нет по вашему желанию.
1.42 С помощью удобного инструмента объединить листы из нескольких книг в один лист
Например, вы хотите объединить все листы из book1, book2 и book3 в один лист, как показано на скриншоте ниже, функция Объединить в Kutools для Excel также поможет вам.
После бесплатной установки Kutools для Excel выполните следующие действия:
1. Откройте нужные книги и нажмите Kutools Plus > Объединить > OK для запуска мастера объединения.
2. В Шаг1 из3 окне выберите Копировать данные из нескольких книг в один лист и нажмите Далее кнопку.
3. В Шаг2 из3 окне все открытые книги уже отмечены в Список рабочих книг и все листы открытых книг отмечены в Список листов нажмите Далее для продолжения.
Совет: чтобы добавить книги для объединения, нажмите кнопку Добавить и выберите путь для добавления книг.
4. На последнем шаге выберите нужные параметры. Нажмите Завершить.
5. Появится диалоговое окно для выбора папки, куда будет сохранена объединённая книга. Вы можете переименовать её в поле Имя файла и нажать Сохранить для завершения.
6. Теперь все листы из открытых книг объединены, и появится диалоговое окно с предложением сохранить параметры как сценарий. Нажмите Да или Нет по вашему желанию.
Откройте сохранённую объединённую книгу — листы из разных книг объединены в один лист.
Вот три книги, которые нужно объединить в одну книгу.
| Янв | Фев | Мар |
 |  |  |
2.1 Объединить книги в одну книгу с помощью команды Переместить или Копировать
Если нужно объединить листы из нескольких книг, команда Excel Переместить или Копировать поможет вам.
1. Откройте первые две книги, которые хотите объединить, активируйте первую книгу, выберите нужные листы, затем щёлкните правой кнопкой мыши для вызова контекстного меню и выберите Переместить или Копировать.
Советы
1) Если хотите переместить все листы, сначала щёлкните правой кнопкой мыши по вкладке и выберите Выбрать все листы в контекстном меню, затем используйте команду Переместить или Копировать.
2) Удерживайте клавишу Ctrl для выбора нескольких несмежных листов.
3) Выберите первый лист и удерживайте Shift, чтобы выбрать последний лист для выделения нескольких смежных листов.
2. В Переместить или Копировать диалоге, в В книгу выпадающем списке выберите книгу, в которую хотите переместить листы (например, Jan), затем укажите место для вставки листов в Перед листом разделе, нажмите OK.
Примечание:
1) Лучше отметить Создать копию, иначе исходная книга потеряет лист после перемещения.
2) Если хотите поместить все книги в новую книгу, выберите (новая книга) в В книгу выпадающем списке.
Все листы перемещены в основную книгу.
Повторите вышеуказанные шаги для перемещения всех книг в одну.
2.2 Объединить несколько книг (в одной папке) в одну книгу с помощью VBA-кода
Чтобы объединить много книг из одной папки, используйте VBA-код.
1. Откройте книгу, в которую хотите объединить все книги, затем нажмите Alt + F11 для открытия Microsoft Visual Basic for Applications окна.
2. В появившемся окне нажмите Вставить > Модуль чтобы добавить новый пустой модуль.
3. Скопируйте и вставьте приведённый ниже VBA-код в новый модуль.
VBA: Объединить несколько книг в текущую книгу
Sub GetSheets()
'Updated by Extendoffice
Path = "C:\Users\AddinTestWin10\Desktop\combine sheets\combine sheets into one workbook\"
Filename = Dir(Path & "*.xlsx")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub 
Примечание
В VBA-коде путь "C:\Users\AddinTestWin10\Desktop\combine sheets\combine sheets into one workbook\" — это папка, где находятся книги для объединения. Измените его по своему усмотрению.
Если книги для объединения находятся в разных папках, сначала скопируйте их в одну папку.
4. Нажмите F5 для запуска кода — все книги будут скопированы в конец текущей книги.
Совет: все скопированные листы сохранят свои исходные имена. Если хотите использовать имя книги как префикс, используйте следующий код:
Sub MergeWorkbooks()
'Updated by Extendoffice
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
On Error Resume Next
xStrPath = "C:\Users\AddinTestWin10\Desktop\combine sheets\combine sheets into one workbook\"
xStrFName = Dir(xStrPath & "*.xlsx")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
Do While Len(xStrFName) > 0
Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
xStrAWBName = ActiveWorkbook.Name
For Each xWS In ActiveWorkbook.Sheets
xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
xMWS.Name = xStrAWBName & "(" & xMWS.Name & ")"
Next xWS
Workbooks(xStrAWBName).Close
xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub 
2.3 Объединить несколько книг (из разных папок) в одну книгу с помощью удобного инструмента
Иногда требуется объединить книги из разных папок в одну. Например, объединить все книги из папок2020 и2021 в одну книгу — стандартными средствами Excel это сделать нельзя.
Однако функция Объединить в Kutools для Excel позволяет объединять книги из разных папок в одну.
После бесплатной установки Kutools для Excel выполните следующие действия:
1. Откройте Excel, нажмите Kutools Plus > Объединить, Появится диалоговое окно с напоминанием о пароле. Если книги, которые вы используете, не защищены паролем, просто нажмите OK для продолжения.
2. В Объединить данные – Шаг1 из3 окне выберите Копировать листы из нескольких книг в одну книгу и нажмите Далее кнопку.
3. В Шаг2 из3 окне нажмите стрелку рядом с кнопкой Добавить для отображения выпадающего меню, выберите Папка.
4. Затем в Выберите папку диалоге выберите нужную папку и нажмите Выберите папку чтобы добавить её в Список рабочих книг раздел.
5. Повторите этот шаг для добавления всех папок и отображения всех книг в Список рабочих книг, нажмите Далее.
6. На последнем шаге выберите нужные параметры. Нажмите Завершить кнопку.
7. Появится диалоговое окно для выбора папки, куда будет сохранена объединённая книга. Вы можете переименовать её в поле Имя файла и нажать Сохранить для завершения.
8. Теперь все листы объединены, и появится диалоговое окно с предложением сохранить параметры как сценарий. Нажмите Да или Нет по вашему желанию.
Откроется новая книга с результатом объединения. Перейдите по ссылке Выходной файл, чтобы проверить объединённый лист.
Результат объединения
Примечание: в объединённой книге первый лист с именем Kutools для Excel содержит информацию об исходных и итоговых листах. Вы можете удалить его, если он не нужен.
Если вы хотите объединить только некоторые листы из книг в одну книгу, а не все листы каждой книги, воспользуйтесь следующими методами.
quarter-1.xlsx quarter-2.xlsx quarter-3.xlsx
3.1 Объединить определённые листы из книг (все книги в одной папке) в одну книгу с помощью VBA-кода
1. Откройте книгу, в которую хотите поместить объединённые листы, затем нажмите Alt + F11 для открытия окна Microsoft Visual Basic for Applications.
2. В появившемся окне нажмите Вставить > Модуль чтобы добавить новый пустой модуль.
3. Скопируйте и вставьте приведённый ниже VBA-код в новый модуль.
VBA: Объединить определённые листы из нескольких книг в текущую книгу
Sub MergeSheets2()
'Updated by Extendoffice
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
Dim xI As Integer
On Error Resume Next
xStrPath = "C:\Users\AddinTestWin10\Desktop\combine sheets\combine specific sheets from multiple workbooks\"
xStrName = "A,B"
xArr = Split(xStrName, ",")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
xStrFName = Dir(xStrPath & "*.xlsx")
Do While Len(xStrFName) > 0
Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
xStrAWBName = ActiveWorkbook.Name
For Each xWS In ActiveWorkbook.Sheets
For xI = 0 To UBound(xArr)
If xWS.Name = xArr(xI) Then
xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
xMWS.Name = xStrAWBName & "(" & xArr(xI) & ")"
Exit For
End If
Next xI
Next xWS
Workbooks(xStrAWBName).Close
xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub 
Примечание
1) В VBA-коде путь " C:\Users\AddinTestWin10\Desktop\combine sheets\combine specific sheets from multiple workbooks\" — это папка, где находятся книги для объединения. Измените его по своему усмотрению.
2) В VBA-коде строка "A,B" — это имена листов, которые нужно объединить из книг. Измените их по необходимости, разделяя имена запятыми.
3) Если книги для объединения находятся в разных папках, сначала скопируйте их в одну папку.
4. Нажмите F5 для запуска кода — только выбранные листы будут скопированы в конец текущей книги.
Если книги для объединения находятся в разных папках, используйте функцию Объединить в Kutools для Excel.
После бесплатной установки Kutools для Excel выполните следующие действия:
1. Откройте Excel, нажмите Kutools Plus > Объединить, Появится диалоговое окно с напоминанием о пароле. Если книги, которые вы используете, не защищены паролем, просто нажмите OK для продолжения.
2. В Объединить данные – Шаг1 из3 окне выберите Копировать листы из нескольких книг в одну книгу и нажмите Далее кнопку.
3. В Шаг2 из3 окне нажмите стрелку рядом с кнопкой Добавить для отображения выпадающего меню, выберите Папка.
4. Затем в Выберите папку диалоге выберите нужную папку и нажмите Выберите папку чтобы добавить её в Список рабочих книг раздел.
5. Повторите этот шаг для добавления всех папок и отображения всех книг в Список рабочих книг.
6. Оставаясь на Шаг2 из3 окне, выберите книгу в Список рабочих книг, и отметьте только те листы, которые хотите использовать, в Список листов, затем нажмите Тот же лист кнопку. Теперь все листы с одинаковым именем из книг в Список рабочих книг отмечены. Нажмите Далее для продолжения.
7. На последнем шаге выберите нужные параметры. Нажмите Завершить кнопку.
8. Появится диалоговое окно для выбора папки, куда будет сохранена объединённая книга. Вы можете переименовать её в поле Имя файла и нажать Сохранить для завершения
9. Теперь выбранные листы объединены, и появится диалоговое окно с предложением сохранить параметры как сценарий. Нажмите Да или Нет по вашему желанию.
Откроется новая книга с результатом объединения. Перейдите по ссылке Выходной файл, чтобы проверить объединённый лист.
Результат объединения
Примечание: в объединённой книге первый лист с именем Kutools для Excel содержит информацию об исходных и итоговых листах. Вы можете удалить его, если он не нужен.
Как показано на скриншоте ниже, есть две таблицы на двух листах, и вы хотите объединить их в одну по ключевому столбцу в Excel.
| Таблица1 | Таблица2 |
 |  |
| Объединённая таблица |
 |
4.1 Объединить две таблицы по одному ключевому столбцу с помощью Query (Excel2016 и новее)
Функция Query в Excel2016 и новее очень мощная — она поддерживает объединение двух таблиц по ключевому столбцу.
Перед использованием функции Query убедитесь, что диапазоны для объединения преобразованы в таблицы.
combine-two-tables-based-on-a-key-column-query.xlsx
1. Щёлкните любую ячейку первой таблицы, выберите Данные > Из таблицы в группе Получить и преобразовать.
2. В Query Editor диалоге нажмите Файл > Закрыть и загрузить В команду. См. скриншот:
3. Затем во всплывающем окне Загрузить в диалоге выберите Только создать подключение и нажмите Загрузить.
Теперь вы увидите отображение панели Список запросов и таблица будет отображаться в панели как ссылка.
Повторите вышеуказанные шаги для добавления второй таблицы в Список запросов панель.
4. Затем нажмите Данные > Новый запрос > Объединить запросы > Объединить.
5. В Объединить окне выберите две таблицы для объединения в двух выпадающих списках. Таблица внизу будет объединена с таблицей сверху.
6. Щёлкните по ключевому столбцу, по которому нужно объединить таблицы, и нажмите OK.
7. В Merge1 – Query Editor окне нажмите кнопку развёртывания рядом с NewColumn, затем отметьте все столбцы, кроме ключевого, снимите отметку Использовать исходное имя столбца как префикс и нажмите OK.
Теперь две таблицы объединены в одну по указанному ключевому столбцу.
8. Нажмите Файл > Закрыть и загрузить вв Загрузить в диалоге выберите Таблица и укажите место для загрузки. Нажмите Загрузить.
Теперь две таблицы объединены по ключевому столбцу.
4.2 Объединить два листа по одному ключевому столбцу с помощью функций Excel
Если нужно перенести один или два столбца с одного листа на другой по ключевому столбцу, помогут функции Excel.
Например, перенести все данные с листа2 на лист1, сопоставляя по столбцу A.
combine-two-sheets-based-on-a-key-column-functions.xlsx
| Лист1 | Лист2 |
 |  |
4.21 Объединить два листа по столбцу с помощью VLOOKUP
1. Скопируйте и вставьте приведённую ниже формулу рядом с таблицей на листе1:
=VLOOKUP(A2,Sheet2!$A$2:$B$5,2,FALSE)
Пояснение:
A2: первое значение для поиска (ключевой столбец);
Sheet2!$A$2:$B$5: диапазон таблицы, содержащий столбцы для поиска и результата;
2: индекс столбца, номер конкретного столбца (целое число) в диапазоне таблицы, из которого будет возвращено совпадающее значение.
2. Нажмите Enter для получения первого результата.
3. Перетащите маркер автозаполнения вниз, чтобы заполнить все данные.
4. Оставьте ячейки с формулами выделенными, нажмите Главная вкладку и отформатируйте ячейки по необходимости в группе Число .
Для получения дополнительной информации о функции VLOOKUP нажмите здесь.
4.22 Объединить два листа по столбцу с помощью формулы, объединяющей функции MATCH и INDEX
1. Скопируйте и вставьте приведённую ниже формулу рядом с таблицей на листе1:
=INDEX(Sheet2!$B$2:$B$5,MATCH(Sheet1!A2,Sheet2!$A$2:$A$5,0))
Пояснение:
Sheet2!$B$2:$B$5: столбец с искомым значением;
Sheet1!A2: первое значение для поиска (в ключевом столбце);
Sheet2!$A$2:$A$5: ключевой столбец, по которому объединяются два листа.
2. Нажмите Enter для получения первого результата.
3. Перетащите маркер автозаполнения вниз, чтобы заполнить все данные.
4. Оставьте ячейки с формулами выделенными, нажмите Главная вкладку и отформатируйте ячейки по необходимости в группе Число .
4.3 Объединить два листа по одному ключевому столбцу с помощью удобного инструмента
Кроме функции Объединить, есть ещё одна мощная функция — Объединить таблицы в Kutools для Excel, которая быстро и просто объединяет два листа по ключевому столбцу.
combine-two-sheets-based-on-a-key-column-handy-tool.xlsx
После бесплатной установки Kutools для Excel выполните следующие действия:
1. Нажмите Kutools Plus >Объединить таблицы.
2. ВОбъединить таблицы – Шаг1 из5 окне выберите диапазоны таблиц в Выберите основную таблицу и Выберите таблицу поиска разделах соответственно, нажмите Далее.
3. В Шаг2 из5 окне отметьте ключевой столбец для объединения, нажмите Далее.
4. В Шаг3 из5 окне можно отметить столбцы для обновления данных по таблице поиска. Если обновление не требуется, просто нажмите Далее.
5. В Шаг4 из5 окне отметьте столбцы для объединения с основной таблицей, затем нажмите Далее.
6. На последнем шаге укажите параметры по необходимости, нажмите Завершить.
Эта функция поддерживает объединение двух листов из разных книг.
Допустим, есть две таблицы на листах1 и2, и нужно перенести данные из столбца End_Dates с листа2 на лист1 по столбцам Project и Start_Date, как показано на скриншоте ниже:
| Лист1 | Лист2 |
 |  |
| Объединённый лист |
 |
Нажмите, чтобы скачать пример файла
В Excel стандартные функции не поддерживают такую операцию, но функция Объединить таблицы в Kutools для Excel справится с этим.
После бесплатной установки Kutools для Excel выполните следующие действия:
1. Нажмите Kutools Plus > Объединить таблицы.
2. В Объединить таблицы – Шаг1 из5 окне выберите диапазоны таблиц в Выберите основную таблицу и Выберите таблицу поискаразделах соответственно, нажмите Далее.
3. На шаге2 из5 отметьте два ключевых столбца для объединения, нажмите Далее.
Примечание: соответствующие столбцы в таблице поиска будут сопоставлены автоматически, вы можете изменить их, нажав по имени столбца в Столбец таблицы поиска.
4. На шаге3 из5 можно отметить столбцы для обновления данных по таблице поиска. Если обновление не требуется, просто нажмите Далее.
5. В Шаг4 из5 окне отметьте столбцы для объединения с основной таблицей, затем нажмите Далее.
6. На последнем шаге укажите параметры по необходимости, нажмите Завершить.
Теперь выбранные столбцы добавлены в конец основной таблицы.
Эта функция поддерживает объединение двух листов из разных книг.
Чтобы объединить несколько листов с одинаковыми заголовками, как показано на скриншоте ниже:
 |  |
 |  |
| Объединённый лист |
 |
Нажмите, чтобы скачать пример файла
6.1 Объединить все листы с одинаковыми заголовками с помощью VBA
Существует VBA-код, который объединяет все листы книги с одинаковыми заголовками.
1. Откройте книгу, листы которой хотите объединить по заголовкам, затем нажмите Alt + F11 для открытия окна Microsoft Visual Basic for Applications.
2. В появившемся окне нажмите Вставить > Модуль чтобы добавить новый пустой модуль.
3. Скопируйте и вставьте приведённый ниже VBA-код в новый модуль.
VBA: Объединить листы с одинаковым заголовком
Sub Combine()
'Update by Extendoffice
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 Sub 
4. Нажмите F5 для запуска кода — появится диалоговое окно с запросом количества строк заголовка, введите число в поле. Нажмите OK.
Теперь все листы этой книги объединены в новый лист с именем Combined.
6.2 Объединить листы с одинаковыми заголовками с помощью удобного инструмента
Функция Объединить в Kutools для Excel также справится с этой задачей и поддерживает объединение листов из разных книг.
После бесплатной установки Kutools для Excel выполните следующие действия:
1. Откройте книгу, листы которой хотите объединить по заголовкам, нажмите Kutools Plus > Объединить.
2. Появится диалоговое окно с напоминанием о пароле. Если используемые книги не защищены паролем, нажмите OK для продолжения. В появившемся окне Объединить данные – Шаг1 из3 выберите Копировать данные из нескольких книг в один лист и нажмите Далее.
3. В Шаг2 из3 окне все листы отображаются и отмечены в Список листов разделе, нажмите Далее для продолжения.
Советы:
1) Если хотите объединить только некоторые листы, отметьте нужные имена листов, остальные оставьте неотмеченными.
2) Чтобы добавить больше книг для объединения, нажмите Добавить для добавления файлов или папок в Список рабочих книг раздел.
4. В Шаг3 из3выберите Объединить данные по строкам и введите количество строк заголовка в поле Количество строк заголовка укажите другие параметры по необходимости. Нажмите Завершить.
5. Выберите папку и задайте имя для новой книги в диалоге Укажите местоположение и имя файла для сохранения объединённых результатов нажмите Сохранить.
6. Появится диалоговое окно с предложением сохранить параметры как сценарий. Нажмите Да или Нет по необходимости. Откроется книга со списком ссылок на исходную и новую книги, перейдите по ссылке новой книги для проверки.
Консолидация
Например, есть три листа с одинаковыми заголовками строк и столбцов. Теперь вы хотите объединить их и просуммировать данные, как показано на скриншотах ниже.
 |  |  |
| Результат |
 |
Нажмите, чтобы скачать пример файла
1.1 Объединить листы и выполнить вычисления с помощью функции Consolidate
В Excel функция Consolidate поддерживает объединение листов с выполнением вычислений.
1. Откройте книгу, листы которой хотите объединить, и выберите ячейку для размещения объединённых данных, нажмите Данные > Консолидация.
2. В диалоге Консолидация выполните следующие настройки:
1) В Функция выберите вычисление, которое нужно выполнить после объединения листов.
2) Нажмите значок выбора рядом с Обзор для выбора диапазона для объединения и нажмите Добавить в Все ссылки раздел.
Повторите этот шаг для добавления всех диапазонов, которые нужно объединить, в Все ссылки раздел.
3) В Позиция метки разделе отметьте Верхняя строка и Самый левый столбец если диапазоны содержат как заголовки строк, так и столбцов.
4) Если хотите, чтобы объединённое содержимое обновлялось при изменении исходных данных, отметьте Создать ссылки на исходные данные флажок.
3. Нажмите OK. Диапазоны объединены и просуммированы по заголовкам.
Примечание: если диапазоны для объединения находятся в других книгах, нажмите Обзор в диалоге Консолидация для выбора книги, затем введите имя листа и диапазон в поле и нажмите Добавить для добавления диапазона в раздел Все ссылки.
1.2 Объединить листы и выполнить вычисления с помощью удобного инструмента
Функция Объединить в Kutools для Excel поддерживает объединение листов из нескольких книг с выполнением вычислений в один лист.
После бесплатной установки Kutools для Excel выполните следующие действия:
1. Откройте книгу(и), которые хотите консолидировать, нажмите Kutools Plus > Объединить.
2. Появится диалоговое окно с напоминанием о пароле. Если используемые книги не защищены паролем, нажмите OK для продолжения. В появившемся окне Объединить данные – Шаг1 из3 выберите Объединить и вычислить данные из нескольких книг в один лист и нажмите Далее.
3. В Шаг2 из3 окне все листы открытых книг отображаются и отмечены в Список листов разделе, нажмите Далее для продолжения.
Совет: если нужно объединить только некоторые листы, отметьте нужные имена листов, остальные оставьте неотмеченными. Чтобы добавить больше книг для объединения, нажмите Добавить для добавления файлов или папок в раздел Список рабочих книг.
4. В Шаг3 из3укажите вычисление и метки по необходимости. Нажмите Завершить.
5. Выберите папку и задайте имя для новой книги в диалоге Укажите местоположение и имя файла для сохранения объединённых результатов нажмите Сохранить.
6. Появится диалоговое окно с предложением сохранить параметры как сценарий. Нажмите Да или Нет по необходимости. Откроется книга со списком ссылок на исходную и новую книги, перейдите по ссылке новой книги для проверки.
Если ваши данные имеют простую структуру, как показано на скриншоте ниже, вы можете объединить листы напрямую в Сводную таблицу.
 |
 |
 |
1. Откройте нужную книгу, нажмите Настроить панель быстрого доступа > Другие команды.
2. В появившемся окне Параметры Excel выберите Все команды в Выберите команды из разделе, затем прокрутите список и выберите Мастер сводных таблиц и диаграмм.
3. Нажмите Добавить для добавления Мастер сводных таблиц и диаграмм на Панель быстрого доступанажмите OK.
4. Нажмите Мастер сводных таблиц и диаграмм на панели инструментов, и в Шаг1 из3выберите Несколько диапазонов для консолидации и Сводная таблица и нажмите Далее.
5. В Шаг2a из3выберите Я сам создам поля страницы, нажмите Далее.
6. В Шаг2b из3нажмите значок выбора для выбора диапазона для объединения, нажмите Добавить для добавления в Все диапазоны раздел, повторите этот шаг для добавления всех диапазонов для объединения. Установите0 в Сколько полей страницы вы хотите. Нажмите Далее.
7. Выберите место для создания Сводной таблицы в Шаг3 из3нажмите Завершить.
Теперь создана сводная таблица, настройте параметры в Поля сводной таблицы по необходимости.
Примечание: если структура данных сложная, рекомендуем сначала объединить листы одним из описанных выше способов, а затем преобразовать итоговый лист в Сводную таблицу.
Google Sheet
Допустим, есть три google-таблицы, которые нужно объединить в одну, как показано на скриншоте ниже:
 |  |  |
Нажмите, чтобы скачать пример файла
1.1 Объединить google-таблицы в один лист с помощью функции Фильтр
На листе, куда хотите поместить объединённые данные, выберите ячейку и введите следующую формулу:
=({filter(A!A2:B, len(A!A2:A)); filter(B!A2:B, len(B!A2:A)); filter('C'!A2:B, len('C'!A2:A)) })
Теперь данные из листов A, B и C скопированы.
В формуле:
A, B, C — имена листов, A2:B — диапазон данных на каждом листе, A2:A — первый столбец каждого диапазона данных.
1.2 Объединить google-таблицы в один лист с помощью функции IMPORTRANGE
Эта формула часто используется для объединения двух диапазонов в google-таблице.
Выберите ячейку под первым диапазоном данных и введите следующую формулу:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/13K4vuZukmS4-x0qJs0EAXQkneIdNqelruDBF5ff5xNQ/edit","B!A2:B4")
Теперь данные из листа B скопированы ниже.
В формуле
https://docs.google.com/spreadsheets/d/13K4vuZukmS4-x0qJs0EAXQkneIdNqelruDBF5ff5xNQ/edit
— это адрес листа, его можно найти в адресной строке браузера.
B!A2:B4 — диапазон ячеек на листе B, который нужно скопировать в первый диапазон.
Примечание: если ячейка, в которую вставлена формула, была ранее отредактирована, формула вернёт #REF!
Если нужно объединить листы из нескольких книг в Google Sheet, быстрого способа, как в Excel, нет.
Чтобы объединить google-таблицы из книг в одну книгу, используйте команду Копировать в из контекстного меню.
Щёлкните правой кнопкой мыши по листу, который хотите использовать, выберите Копировать в > Новая таблица или Существующая таблица.
Если выберете Новая таблица, появится диалоговое окно с уведомлением, что лист скопирован, нажмите OK. Теперь текущий лист скопирован в новую книгу. Можно нажать Открыть таблицу для проверки.
Если выберете Существующая таблица, появится диалоговое окно Выберите таблицу для копирования этого листа.
1) Выберите, где находится существующая таблица;
2) Выберите книгу, в которую хотите скопировать лист, в разделе Файлы;
3) Или введите веб-адрес книги, в которую хотите скопировать, в поле Или вставьте веб-адрес здесь.
4) Нажмите Выбрать.
Если лист скопирован в новую таблицу, его имя останется прежним. Если скопирован в существующую таблицу, к имени будет добавлен префикс Копия.
Дополнительно
Допустим, есть два листа с дубликатами, нужно объединить данные и удалить дубликаты, как показано на скриншоте ниже:
 |  |
| Общий результат | Удалить дубликаты |
 |  |
1.1 Объединить листы и затем удалить дубликаты с помощью Remove Duplicates
В Excel функция Remove Duplicates помогает быстро удалить дублирующиеся данные.
После объединения листов одним из описанных выше способов выделите объединённые данные, нажмите Данные > Удалить дубликаты.
В Удалить дубликаты диалоге выберите столбцы для удаления дубликатов, можно отметить Данные содержат заголовки для игнорирования заголовков, нажмите OK.
Дублирующиеся строки удалены.
1.2 Объединить листы и затем удалить дубликаты с помощью удобного инструмента
Если нужно объединить только два диапазона и удалить дубликаты, функция Объединить таблицы в Kutools для Excel может сделать это напрямую при объединении.
После бесплатной установки Kutools для Excel выполните следующие действия:
1. Откройте книгу с данными для объединения, нажмите Kutools Plus > Объединить таблицы.
2. ВОбъединить таблицы – Шаг1 из5 окне выберите два диапазона для объединения, если в таблицах два заголовка, отметьте В основной таблице есть заголовок и В таблице поиска есть заголовок флажки, нажмите Далее.
3. В Шаг2 из5 окне отметьте ключевой столбец для объединения диапазонов, нажмите Далее.
4. На шаге3 из5 можно отметить столбцы для обновления данных по таблице поиска, нажмите Далее для перехода к последнему шагу.
Совет: если в таблице поиска есть новый столбец, который нужно добавить в основную таблицу, появится окно шаг4 из5 для добавления столбцов.
5. В Шаг5 из5 окне, в Добавление разделе отметьте Добавить несоответствующие строки в конец основной таблицы флажок, в Обновление разделе отметьте Обновлять только тогда, когда ячейки в таблице поиска содержат данные флажок. Нажмите Завершить.
Теперь две таблицы объединены в основную таблицу без дубликатов.
Допустим, есть несколько книг с листами одинакового имени, например, sheet1, sheet2. Чтобы объединить все листы с именем sheet1 в один лист, а все листы с именем sheet2 — в другой, используйте функцию Объединить в Kutools для Excel.
 |  |  |
 |  |  |
После бесплатной установки Kutools для Excel выполните следующие действия:
1. Откройте книги для объединения, нажмите Kutools Plus > Объединить.
2. Появится диалоговое окно с напоминанием о пароле. Если используемые книги не защищены паролем, нажмите OK для продолжения. В появившемся окне Объединить данные – Шаг1 из3 выберите Объединить данные из нескольких книг с одинаковым именем листа и нажмите Далее.
3. В Шаг2 из3 окне все листы открытых книг отображаются и отмечены в Список листов разделе, нажмите Далее для продолжения.
Советы:
Если хотите объединить только некоторые листы, отметьте нужные имена листов, остальные оставьте неотмеченными.
Чтобы добавить больше книг для объединения, нажмите Добавить для добавления файлов или папок в Список рабочих книг раздел.
4. В Шаг3 из3укажите параметры по необходимости. Нажмите Завершить.
5. Выберите папку и задайте имя для новой книги в диалоге Укажите местоположение и имя файла для сохранения объединённых результатов нажмите Сохранить.
6. Появится диалоговое окно с предложением сохранить параметры как сценарий. Нажмите Да или Нет по необходимости. Откроется книга со списком ссылок на исходную и новую книги, перейдите по ссылке новой книги для проверки.
Объединить данные по строкам
Объединить данные по столбцам
Если нужно объединить одинаковые диапазоны на разных листах в один лист, например, только диапазон A1:B5 из книги A и книги B, функция Объединить в Kutools для Excel — отличный выбор.
| A | B | Объединённый результат |
 |  |  |
1. Откройте нужные книги, нажмите Kutools Plus > Объединить.
2. Появится диалоговое окно с напоминанием о пароле. Если используемые книги не защищены паролем, нажмите OK для продолжения. В появившемся окне Объединить данные – Шаг1 из3 выберите Копировать данные из нескольких книг в один лист и нажмите Далее.
3. В Шаг2 из3 окне все листы открытых книг отображаются и отмечены в Список листов разделе, нажмите значок выбора в Список листов, затем выберите нужный диапазон. Затем нажмите Тот же диапазон для установки диапазона A1:B5 для всех листов. Нажмите Далее.
Советы:
Если хотите объединить только некоторые листы, отметьте нужные имена листов, остальные оставьте неотмеченными.
Чтобы добавить больше книг для объединения, нажмите Добавить для добавления файлов или папок в раздел Список рабочих книг.
4. На шаге3 из3 укажите параметры по необходимости. Нажмите Завершить.
5. Выберите папку и задайте имя для новой книги в диалоге Укажите местоположение и имя файла для сохранения объединённых результатов, нажмите Сохранить.
6. Появится диалоговое окно с предложением сохранить параметры как сценарий. Нажмите Да или Нет по необходимости. Откроется книга со списком ссылок на исходную и новую книги, перейдите по ссылке новой книги для проверки.
Вас также может заинтересовать |
Объединить / Импортировать несколько CSV-файлов в несколько листов Объединить ячейки и сохранить форматирование ячеек в Excel Объединить содержимое нескольких ячеек без потери данных в Excel Объединить дату и время в одну ячейку в Excel |
Лучшие инструменты для повышения производительности Office
Kutools для Excel решает большинство ваших проблем и увеличивает вашу продуктивность на 80%
- Супер строка формул (легкое редактирование нескольких строк текста и формул); Режим чтения (удобное чтение и редактирование большого количества ячеек); Вставка в отфильтрованный диапазон...
- Объединение ячеек/строк/столбцов с сохранением данных; Разделение содержимого ячеек; Объединение дублирующихся строк с подсчетом суммы/среднего значения... Предотвращение дублирования ячеек; Сравнение диапазонов...
- Выбор дублирующихся или уникальных строк; Выбор пустых строк (все ячейки пустые); Супер поиск и нечеткий поиск во многих книгах; Случайный выбор...
- Точное копирование нескольких ячеек без изменения ссылок на формулы; Автоматическое создание ссылок на несколько листов; Вставка маркеров, флажков и многое другое...
- Избранные и быстрая вставка формул, диапазонов, диаграмм и изображений; Шифрование ячеек с паролем; Создание списка рассылки и отправка электронных писем...
- Извлечение текста, добавление текста, удаление по позиции, удаление пробелов; Создание и печать статистики страниц; Преобразование между содержимым ячеек и комментариями...
- Супер фильтр (сохранение и применение схем фильтрации к другим листам); Расширенная сортировка по месяцу/неделе/дню, частоте и другим параметрам; Специальный фильтр по жирному шрифту, курсиву...
- Объединение книг и листов; Объединение таблиц на основе ключевых столбцов; Разделение данных на несколько листов; Пакетное преобразование xls, xlsx и PDF...
- Группировка сводной таблицы по номеру недели, дню недели и другим параметрам... Отображение разблокированных, заблокированных ячеек разными цветами; Выделение ячеек, содержащих формулы/имена...
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов в новых вкладках одного окна, а не в новых окнах.
- Увеличивает вашу продуктивность на 50% и экономит сотни кликов мышью каждый день!
