Учебное пособие по Excel: объединение нескольких книг / листов в одну
В Excel объединение листов - обычная, но довольно сложная задача для большинства пользователей Excel, особенно для «зеленых». В этом руководстве перечислены почти все сценарии комбинирования, с которыми вы можете столкнуться, и представлены соответствующие профессиональные решения. Вы можете быстро найти ответ, щелкнув ссылку в списке навигации ниже, или вы можете изучить навыки комбинирования, перейдя по приведенным ниже ссылкам одну за другой, если вас это интересует.
В Excel объединение можно разделить на два типа: один - только объединение содержимого, другой - объединение содержимого и выполнение некоторых вычислений.
Внимание
В этом руководстве я создаю несколько листов и данных для лучшего объяснения методов. Вы можете изменить ссылки по своему усмотрению, когда используете приведенный ниже код VBA или формулы, или вы можете напрямую загрузить образцы для опробования методов.
Объединить содержимое
Вот рабочая тетрадь с 4 листами, которые необходимо объединить в один лист.
 |
 |
 |
 |
Нажмите, чтобы загрузить образец файла
1.1 Объедините все листы в один лист с кодом VBA
В Excel, кроме традиционного метода - копирования и вставки, вы можете использовать код VBA для быстрого объединения всех листов в один лист.
1. Нажмите F11 и другой ключи для включения Microsoft Visual Basic для приложений окно.
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
В приведенном выше коде сценарий «Комбинированный» - это имя листа, на котором размещается объединенное содержимое, вы можете изменить этот сценарий на другой по своему усмотрению.
4. нажмите F5 Клавиша для запуска кода, лист с именем Combined был создан перед всеми листами, чтобы разместить все содержимое листов.
Если вы работаете в Excel 2016 или более поздних версиях, функция запроса позволяет одновременно объединять все созданные таблицы или именованные диапазоны в одну.
Нажмите, чтобы загрузить образец файла
Во-первых, убедитесь, что диапазоны были созданы как таблицы или именованные диапазоны, для создания таблицы и именованного диапазона см. Как преобразовать диапазон в таблицу или наоборот в Excel и Определение и использование имен в формулах.
1. Откройте книгу, в которой вы хотите объединить все листы, щелкните Данные > Новый запрос > Из других источников > Пустой запрос.
2. Во всплывающем Редактор запросов окна, перейдите к строке формул, введите в нее формулу ниже.
= Excel.CurrentWorkbook ()
Нажмите Enter key, все таблицы в текущей книге были перечислены.
3. Нажмите кнопку "Развернуть" рядом с Содержание и проверить Расширьте вариант и Выбрать все столбцы флажок.
4. Нажмите OK. Все таблицы перечислены одна за другой.
Вы можете щелкнуть правой кнопкой мыши заголовок столбца, чтобы выполнить другие операции с выбранным столбцом.
5. Затем нажмите Файл > Закрыть и загрузить в….
6. в Загрузить в диалог, проверьте Настольные вариант, затем выберите место для загрузки объединенной таблицы в Выберите, куда должны быть загружены данные разделом, щелкните нагрузка.
Теперь создается новый лист для размещения всех объединенных таблиц.
1.3 Объедините все листы в один с помощью удобного инструмента
Если описанные выше методы не могут вас удовлетворить, вы можете попробовать полезный и удобный инструмент, Kutools for Excel, его Сочетать Эта мощная функция не только может объединять все листы в один лист, но также может выполнять расширенные комбинированные задания, такие как объединение листов в одну книгу, объединение листов с одинаковыми именами, объединение только выбранных листов, объединение листов в файлах и т. д.
1. Включите книгу, листы которой вы хотите объединить, щелкните Кутулс Плюс > Сочетать. Появится диалоговое окно, напоминающее вам о некоторых уведомлениях о пароле. Если книги, которые вы хотите использовать, не касаются пароля, просто нажмите OK для продолжения.
2. в Объедините рабочие листы - шаг 1 из 3 окно, проверка Объедините несколько листов из книг в один лист , нажмите Следующая .
3. в Шаг 2 из 3 окно, по умолчанию текущая книга была указана и проверена в Список рабочих книг панели, и все листы текущей книги были перечислены и отмечены в Список рабочих листов панели, нажмите Следующая для продолжения.
Примечание: если вы открыли несколько книг, все открытые книги перечислены в списке книг, пожалуйста, отметьте только ту книгу, которую хотите использовать.
4. На последнем шаге выберите нужный режим комбинирования, вы можете выбрать Объединить по строкам or Объединить по столбцу; затем укажите другие параметры по мере необходимости. Нажмите Завершить .
5. Появится диалоговое окно, в котором вы можете выбрать одну папку для размещения объединенной книги, вы можете переименовать ее в Имя файла панель, щелкните Сохранить в конце.
6. Теперь все листы объединены вместе, и появляется диалоговое окно, в котором вас спросят, сохранить ли настройки как сценарий, нажмите Да or Нет как тебе нравится.
Появится новая книга с перечисленным результатом объединения, щелкните ссылку Выходной файл, чтобы проверить объединенный лист.
Объединить по строкам
Объединить по столбцу
1.4 Дополнение к объединению листов в один лист
1.41 С помощью удобного инструмента, чтобы объединить только выбранные листы в один лист
Если вы хотите объединить только некоторые листы в книге в один лист, встроенная функция в Excel не может этого добиться. Но Сочетать особенность Kutools for Excel может сделать это.
1. Включите используемую книгу и нажмите Кутулс Плюс > Сочетать > OK для включения мастера объединения.
2. в Шаг 1 из 3 окно, проверка Объедините несколько листов из книг в один лист , нажмите Следующая .
3. В окне Шаг 2 из 3 по умолчанию текущая книга была указана и отмечена на панели списка рабочих книг, а все листы текущей книги перечислены и отмечены на панели списка рабочих листов, снимите отметки с листов, которые не нужно объединять, нажмите «Далее», чтобы продолжить.
4. На последнем шаге укажите необходимые параметры. Нажмите Завершить .
5. Появится диалоговое окно, в котором вы можете выбрать одну папку для размещения объединенной книги, вы можете переименовать ее в Имя файла панель, щелкните Сохранить в конце.
6. Теперь только отмеченные листы были объединены вместе, и появится диалоговое окно, в котором вас спросят, сохранить ли настройки как сценарий, нажмите Да or Нет как тебе нравится.
1.42 С помощью удобного инструмента для объединения листов из нескольких книг в один лист
Например, вы хотите объединить все листы book1, book2 и book3 в один лист, как показано на скриншоте ниже. Сочетать особенность Kutools for Excel также может вам помочь.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Включите используемые книги и нажмите Кутулс Плюс > Сочетать > OK для Сочетать Мастер.
2. в Шаг 1 из 3 окно, проверка Объедините несколько листов из книг в один лист , нажмите Следующая .
3. в Шаг 2 из 3 окно, все открытые книги были перечислены и отмечены в Список рабочих книг панели, и все листы открытых книг были перечислены и отмечены в Список рабочих листов панели, нажмите Следующая для продолжения.
Совет: если вы хотите добавить книги для объединения, нажмите кнопку «Добавить», чтобы выбрать путь для добавления книг.
4. На последнем шаге выберите нужные вам параметры. Нажмите Завершить .
5. Появится диалоговое окно, в котором вы можете выбрать одну папку для размещения объединенной книги, вы можете переименовать ее в Имя файла панель, щелкните Сохранить в конце.
6. Теперь все листы в открытых книгах были объединены вместе, и появится диалоговое окно, в котором вас спросят, сохранить ли настройки как сценарий, нажмите Да or Нет как тебе нравится.
Откройте объединенную книгу, которую вы сохранили, листы книг были объединены в один лист.
Вот три книги, которые необходимо объединить в одну книгу.
| Января | фев | Вт |
 |
 |
 |
2.1 Объединение книг в одну с помощью команды "Переместить" или "Копировать"
Только для объединения листов в нескольких книгах Excel Переместить или скопировать Команда может оказать вам услугу.
1. Откройте первые две книги, которые вы хотите объединить, активируйте первую книгу, выберите листы, которые вы хотите переместить, а затем щелкните правой кнопкой мыши, чтобы включить контекстное меню, и нажмите Переместить или скопировать.
Советы
1) Щелкните правой кнопкой мыши на одной вкладке и выберите Выбрать все листы из контекстного меню сначала, если вы хотите переместить все листы, затем используйте Переместить или скопировать команда.
2) Холдинг Ctrl Клавиша выбора нескольких несмежных листов.
3) Выберите первый лист и удерживайте Shift Клавиша выбора последнего листа для выбора нескольких соседних листов.
2. в Переместить или скопировать диалог, в Бронировать раскрывающийся список, выберите книгу, в которую вы хотите переместить листы, здесь выберите Янв, затем укажите место, в которое вы хотите поместить листы. Перед листом разделом, щелкните OK.
Примечание:
1) Вам лучше проверить Создать копию, или исходная книга потеряет лист после перемещения.
2) Если вы хотите поместить все книги в новую книгу, выберите (новая книга) in Бронировать выпадающий список.
Как листы были перемещены в основную книгу.
Повторите шаги, описанные выше, чтобы переместить все книги в одну.
2.2 Объединение нескольких книг (в одной папке) в одну книгу с кодом VBA
Чтобы объединить множество книг, которые все находятся в одной папке, вы можете использовать код VBA.
1. Включите книгу, в которую вы хотите объединить все книги, затем нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
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 \ объединить листы \ объединить листы в одну книгу \"- это путь к папке, в которой находятся книги, которые вы хотите объединить, измените его в соответствии с вашими потребностями.
Если книги, которые вы хотите объединить, находятся в разных папках, сначала скопируйте их в одну папку.
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 for Excel предоставляет возможность объединить книги из нескольких папок в одну.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Включите Excel, щелкните Кутулс Плюс > Сочетать, Появится диалоговое окно, напоминающее вам о некоторых уведомлениях о пароле. Если книги, которые вы хотите использовать, не касаются пароля, просто нажмите OK для продолжения.
2. в Объедините рабочие листы - шаг 1 из 3 окно, проверка Объедините несколько листов из книг в одну книгу , нажмите Следующая .
3. в Шаг 2 из 3 в окне щелкните стрелку рядом с Добавить кнопку, чтобы отобразить раскрывающееся меню, щелкните Папка.
4. Затем в Выбор папки диалоговом окне выберите одну папку, которую вы хотите использовать, щелкните Выбор папки добавить это к Список рабочих книг .
5. Повторите описанный выше шаг, чтобы добавить все папки и перечислить все книги в Список рабочих книг, нажмите Следующая.
6. На последнем шаге выберите нужные вам параметры. Нажмите Завершить .
7. Появится диалоговое окно, в котором вы можете выбрать одну папку для размещения объединенной книги, вы можете переименовать ее в Имя файла панель, щелкните Сохранить в конце.
8. Теперь все листы объединены вместе, и появляется диалоговое окно, в котором вас спросят, сохранить ли настройки как сценарий, нажмите Да or Нет как тебе нравится.
Появится новая книга, в которой перечислены результаты объединения, щелкните ссылку Выходной файл, чтобы проверить объединенный лист.
Объединить результат
Примечание: в объединенной книге первый лист с именем Kutools for Excel содержит некоторую информацию об исходных листах и окончательных комбинированных листах, вы можете удалить его, если вам не нужно.
Если вы хотите объединить только несколько листов книг в одну книгу, а не все листы каждой книги, вы можете попробовать следующие методы.
четверть-1.xlsx четверть-2.xlsx четверть-3.xlsx
3.1 Объединение определенных листов из книг (все книги в одной папке) в одну книгу с кодом VBA
1. Откройте книгу, чтобы найти объединенные листы, затем нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
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 \ объединить листы \ объединить определенные листы из нескольких книг \"- это путь, по которому находятся книги, которые вы хотите объединить, измените его в соответствии с вашими потребностями.
2) В коде VBA скрипт "А, В"- это имена отдельных листов из книг, которые я хочу объединить, измените их по своему усмотрению, используя запятые для разделения каждого имени листа.
3) Если книги, которые вы хотите объединить, находятся в разных папках, сначала скопируйте их в одну папку.
4. Нажмите F5 ключ для запуска кода, то только определенные рабочие листы будут скопированы в конец текущей книги.
Если книги, которые вы хотите объединить, находятся во многих разных папках, вы можете попробовать Kutools for ExcelАвтора Сочетать функцию.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Включите Excel, щелкните Кутулс Плюс > Сочетать, Появится диалоговое окно, напоминающее вам о некоторых уведомлениях о пароле. Если книги, которые вы хотите использовать, не касаются пароля, просто нажмите OK для продолжения.
2. в Объедините рабочие листы - шаг 1 из 3 окно, проверка Объедините несколько листов из книг в одну книгу , нажмите Следующая .
3. в Шаг 2 из 3 в окне щелкните стрелку рядом с Добавить кнопку, чтобы отобразить раскрывающееся меню, щелкните Папка.
4. Затем в Выбор папки диалоговом окне выберите одну папку, которую вы хотите использовать, щелкните Выбор папки добавить это к Список рабочих книг .
5. Повторите вышеуказанный шаг, чтобы добавить все папки и перечислить все книги в Список рабочих книг.
6. Тогда оставайтесь в Шаг 2 из 3 в окне выберите одну книгу в Список рабочих книг, и проверяйте только те листы, которые хотите использовать в Список рабочих листовзатем нажмите Тот же лист кнопка. Теперь все одноименные листы рабочих тетрадей в Список рабочих книг были проверены. Нажмите Следующая для продолжения.
7. На последнем шаге выберите нужные вам параметры. Нажмите Завершить .
8. Появится диалоговое окно, в котором вы можете выбрать одну папку для размещения объединенной книги, вы можете переименовать ее в Имя файла панель, щелкните Сохранить заканчивать
9. Теперь отдельные листы были объединены вместе, и появляется диалоговое окно, в котором вас спросят, сохранить ли настройки как сценарий, нажмите Да or Нет как тебе нравится.
Появится новая книга, в которой перечислены результаты объединения, щелкните ссылку Выходной файл, чтобы проверить объединенный лист.
Объединить результат
Примечание: в объединенной книге первый лист с именем Kutools for Excel содержит некоторую информацию об исходных листах и окончательных комбинированных листах, вы можете удалить его, если вам не нужно.
Как показано на скриншоте ниже, есть две таблицы на двух листах, вы хотите объединить эти две таблицы в одну на основе одного ключевого столбца в Excel.
| Table1 | Table2 |
 |
 |
| Комбинированный стол |
 |
Ассоциация запрос Функция в Excel 2016 или более поздних версиях очень мощная, она поддерживает объединение двух таблиц на основе ключевого столбца.
Перед использованием запрос убедитесь, что диапазоны, которые вы хотите объединить, были созданы в виде таблиц.
объединить две таблицы на основе ключевого столбца запроса.xlsx
1. Щелкните любую ячейку первой таблицы, щелкните Данные > Из таблицы в Получить и преобразовать группа.
2. в Редактор запросов диалоговое окно, нажмите Файл > Закрыть и загрузить к команда. Смотрите скриншот:
3. Затем во всплывающем Загрузить в диалог, проверьте Только создать соединение вариант. Нажмите нагрузка.
Теперь вы можете увидеть Запросы к книге отображение панели, и таблица была указана на панели в виде ссылки.
Повторите шаги, указанные выше, чтобы добавить вторую таблицу в Запросы к книге панель.
4. Затем нажмите Данные > Новый запрос > Объединить запросы > идти.
5. в идти В двух раскрывающихся списках отдельно выберите две таблицы, которые вы хотите объединить. Таблица внизу будет объединена с таблицей выше.
6. Щелкните ключевой столбец, на основе которого вы хотите объединить две таблицы, щелкните OK.
7. Merge1 - редактор запросов всплывающее окно, нажмите кнопку "Развернуть" рядом с Новая колонка, затем отметьте все столбцы, кроме ключевого, снимите флажок Использовать исходное имя столбца в качестве префикса флажок, щелкните OK.
Теперь две таблицы были объединены в одну на основе указанного ключевого столбца.
8. Нажмите Файл > Закрыть и загрузить в, В Загрузить в диалог, проверьте Настольные вариант и укажите место, которое вы хотите загрузить. Нажмите нагрузка.
Теперь две таблицы были объединены на основе ключевого столбца.
4.2 Объединение двух листов на основе одного ключевого столбца с помощью функций Excel
Если вы хотите переместить только один или два столбца с одного листа на другой и определить местоположение на основе ключевого столбца, функции Excel могут вам помочь.
Например, переместите полные данные с листа 2 на лист 1 и на основе столбца A найдите данные.
объединить два листа на основе ключевого столбца functions.xlsx
| Sheet1 | Sheet2 |
 |
 |
4.21 Объединение двух листов по столбцу с помощью функции ВПР.
1. Скопируйте и вставьте формулу ниже рядом с таблицей на листе 1:
= ВПР (A2; Лист2! $ A $ 2: $ B $ 5,2; ЛОЖЬ)
Объяснение:
A2: первое значение поиска (ключевой столбец);
Sheet2! $ A $ 2: $ B $ 5: массив таблицы, таблица содержит два или более столбца, в которых находятся столбец значения поиска и столбец значения результата;
2: индекс столбца, конкретный номер столбца (это целое число) table_array, из которого вы вернете совпадающее значение.
2. Нажмите Enter ключ, чтобы получить первый результат.
3. Затем перетащите маркер автозаполнения вниз, чтобы заполнить все данные.
4. Не снимая выделения с ячеек формулы, щелкните Главная вкладка и перейдите к форматированию ячеек так, как вам нужно, в Номер регистрации группа.
Для получения дополнительной информации о функции ВПР щелкните здесь.
4.22 Объединение двух листов по столбцу с помощью формулы объединение функций ПОИСКПОЗ и ИНДЕКС
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 Объединение двух листов на основе одного ключевого столбца с помощью удобного инструмента
Кроме функции объединения, есть еще одна мощная функция - Слияние таблиц in Kutools for Excel, который может быстро и легко объединить два листа на основе одного ключевого столбца.
Комбинировать два листа на основе ключевого столбца удобный инструмент.xlsx
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Нажмите Кутулс Плюс >Слияние таблиц.
2. В TСлияние способностей - шаг 1 из 5 в окне выберите диапазоны таблиц в Выберите основную таблицу и Выберите таблицу поиска раздел отдельно, нажмите Следующая.
3. в Шаг 2 из 5 в окне, отметьте ключевой столбец, на основе которого вы хотите объединить, щелкните Следующая.
4. в Шаг 3 из 5 в окне вы можете проверить столбцы, данные в которых вы хотите обновить, на основе таблицы поиска, если вам не нужны данные для обновления, просто щелкните непосредственно Следующая.
5. в Шаг 4 из 5 в окне, здесь отметьте столбцы, которые вы хотите объединить в основную таблицу, затем нажмите Следующая.
6. На последнем шаге укажите нужные вам параметры, нажмите Завершить.
Эта функция поддерживает объединение двух листов в разных книгах.
Предположим, есть две таблицы на листе Sheet1 и Sheet2 отдельно, теперь, чтобы переместить данные в столбце End_Dates с листа2 на лист1 на основе столбца Project и столбца Start_Date, как показано ниже:
| Sheet1 | Sheet2 |
 |
 |
| Комбинированный лист |
 |
Нажмите, чтобы загрузить образец файла
В Excel встроенные функции не поддерживают эту операцию, но Слияние таблиц of Kutools for Excel может справиться с этим.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Нажмите Кутулс Плюс > Слияние таблиц.
2. в Объединение таблиц - шаг 1 из 5 в окне выберите диапазоны таблиц в Выберите основную таблицу и Выберите вкладку поискараздел e отдельно, нажмите Следующая.
3. в Шаг 2 из 5 в окне отметьте два ключевых столбца, на основе которых вы хотите объединить, щелкните Следующая.
Примечание: автоматически соответствующие столбцы в таблице поиска будут сопоставлены, вы можете щелкнуть имя столбца в столбцах таблицы поиска, чтобы изменить их по своему усмотрению.
4. в Шаг 3 из 5 в окне вы можете проверить столбцы, данные в которых вы хотите обновить, на основе таблицы поиска, если вам не нужны данные для обновления, просто щелкните непосредственно Следующая.
5. в Шаг 4 из 5 в окне, отметьте здесь столбцы, которые вы хотите объединить в основную таблицу, затем щелкните Следующая.
6. На последнем шаге укажите нужные вам параметры, нажмите Завершить.
Затем столбцы, которые вы хотите добавить, были добавлены в конец основной таблицы.
Эта функция поддерживает объединение двух листов в разных книгах.
Чтобы объединить несколько листов с одинаковым заголовком, как показано на скриншоте ниже:
 |
 |
 |
 |
| Комбинированный лист |
 |
Нажмите, чтобы загрузить образец файла
6.1 Объедините все листы с одинаковыми заголовками с помощью VBA
Существует код VBA, который может объединить все листы книги с одинаковыми заголовками.
1. Включите книгу, в которой вы хотите объединить листы с одинаковым заголовком, затем нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
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 for Excel также может справиться с этой задачей и поддерживает объединение листов в книгах.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Включите книгу, листы которой вы хотите объединить с одинаковыми заголовками, щелкните Кутулс Плюс > Сочетать.
2. Появится диалоговое окно с напоминанием о пароле. Если книги, которые вы используете, не содержат паролей, щелкните OK продолжить. В появлении Объедините рабочие листы - шаг 1 из 3 окно, проверка Объедините несколько листов из книг в один лист , нажмите Следующая.
3. в Шаг 2 из 3 окно, все листы перечислены и зарегистрированы Список рабочих листов разделом, щелкните Следующая продолжать.
Советы:
1) Если вы хотите объединить только некоторые листы, просто отметьте нужные имена листов и оставьте другие не отмеченными.
2) Если вы хотите добавить больше книг для объединения, щелкните Добавить для добавления файлов или папок в Список рабочих книг .
4. в Шаг 3 из 3, проверить Объединить по строкам вариант и введите количество строк заголовка в поле Количество строк заголовка текстовое поле, укажите другие параметры по своему усмотрению. Нажмите Завершить.
5. Выберите папку и дайте имя новой книге в Укажите имя файла и расположение для объединенной книги диалоговое окно, нажмите Сохранить.
6. Появится диалоговое окно, в котором вас спросят, сохранить ли настройки как сценарий, нажмите Да or Нет для вас нужно. Появится рабочая книга со списком ссылок на исходную книгу и новую книгу, щелкните ссылку пути к новой книге, чтобы открыть ее для проверки.
Консолидировать
Например, есть три листа с одинаковыми заголовками строк и столбцов, теперь вы хотите объединить их с одинаковыми заголовками и суммировать данные, как показано на скриншотах ниже.
 |
 |
 |
| Результат |
 |
Нажмите, чтобы загрузить образец файла
1.1 Объединяйте листы и выполняйте вычисления с помощью функции "Объединить"
В Excel Консолидировать функция поддерживает объединение листов и выполнение расчетов.
1. Включите книгу, листы которой вы хотите объединить, и выберите ячейку, в которую вы хотите поместить объединенные данные, щелкните Данные > Консолидировать.
2. в Консолидировать диалоговое окно, выполните следующие настройки:
1). Функция в раскрывающемся меню выберите расчет, который нужно выполнить после объединения листов.
2) Щелкните значок выбора рядом с ЛИСТАТЬ СПИСКИ кнопку, чтобы выбрать диапазон для объединения, и нажмите кнопку Добавить до Все ссылки .
Повторите этот шаг, чтобы добавить все диапазоны, которые необходимо объединить в Все ссылки .
3). Используйте ярлыки в раздел, проверьте Верхний ряд и Левая колонка флажки, если диапазоны имеют как заголовок строки, так и заголовок столбца.
4) Если вы хотите, чтобы объединенное содержимое изменялось по мере изменения данных источников, установите флажок Создавать ссылки на исходные данные флажок.
3. Нажмите OK. Диапазоны объединены и суммированы по заголовкам.
Примечание: если диапазоны, которые вы хотите объединить, находятся в других книгах, нажмите кнопку «Обзор» в диалоговом окне «Объединить», чтобы выбрать книгу, а затем введите имя листа и диапазон в текстовое поле и нажмите «Добавить», чтобы добавить диапазон в раздел «Все ссылки».
1.2 Объединяйте листы и проводите расчеты с помощью удобного инструмента
Ассоциация Сочетать особенность Kutools for Excel поддерживает объединение листов в нескольких книгах и выполнение вычислений на одном листе.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Включите книги, которые вы хотите объединить, щелкните Кутулс Плюс > Сочетать.
2. Появится диалоговое окно с напоминанием о пароле. Если книги, которые вы используете, не содержат паролей, щелкните OK продолжить. В появлении Объедините рабочие листы - шаг 1 из 3 окно, проверка Объединяйте и вычисляйте значения из нескольких книг на одном листе , нажмите Следующая.
3. в Шаг 2 из 3 окно, все листы открытых книг перечислены и проверены Список рабочих листов разделом, щелкните Следующая продолжать.
Совет: если вы хотите объединить только некоторые листы, просто отметьте нужные имена листов и оставьте другие не отмеченными. Если вы хотите добавить больше книг для объединения, нажмите «Добавить», чтобы добавить файлы или папки в раздел «Список книг».
4. в Шаг 3 из 3, укажите расчет, нужные вам ярлыки. Нажмите Завершить.
5. Выберите папку и дайте имя новой книге в Укажите имя файла и расположение для объединенной книги диалоговое окно, нажмите Сохранить.
6. Появится диалоговое окно, в котором вас спросят, сохранить ли настройки как сценарий, нажмите Да or Нет для вас нужно. Появится рабочая книга со списком ссылок на исходную книгу и новую книгу, щелкните ссылку пути к новой книге, чтобы открыть ее для проверки.
Если ваши данные имеют простую структуру, как показано на скриншоте ниже, вы можете напрямую объединить листы в сводную таблицу.
 |
 |
 |
Нажмите, чтобы скачать образец
1. Включите рабочую книгу, которую вы будете использовать, щелкните Настройка панели быстрого доступа > Дополнительные команды.
2. Во всплывающем Параметры Excel окно, выберите Все команды из Выберите команды из раздел, затем перетащите полосу прокрутки, чтобы выбрать Мастер сводных таблиц и диаграмм.
3. Нажмите Добавить добавить Мастер сводных таблиц и диаграмм до Настройка панели быстрого доступа, нажмите OK.
4. Нажмите Мастер сводных таблиц и диаграмм на панели инструментов и в Шаг 1 из 3, проверить Несколько диапазонов консолидации и сводная таблица параметры, щелкните Следующая.
5. в Шаг 2а из 3, отметьте, что я создам опцию полей страницы, нажмите Следующая.
6. в Шаг 2b из 3, щелкните значок выбора, чтобы выбрать диапазон, который вы хотите объединить, щелкните Добавить добавить его в Все диапазоны раздел, повторите этот шаг, чтобы добавить все диапазоны, используемые для объединения. Отметьте 0 в Сколько полей страницы вы хотите. Нажмите Следующая.
7. Выберите место, в котором вы хотите создать сводную таблицу. Шаг 3 из 3, нажмите Завершить.
Теперь сводная таблица создана, укажите настройки в Поля сводной таблицы панель, как вам нужно.
Примечание: если структура данных сложная, на мой взгляд, объедините листы с общим списком методов выше, а затем преобразуйте лист результатов в сводную таблицу.
Таблица Google
Предположим, что необходимо объединить три листа 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 были скопированы.
В формуле:
А, В, С имена листов, A2: B диапазон данных на каждом листе, A2: A - это первый столбец каждого диапазона данных.
1.2 Объедините листы Google в один лист с помощью функции ИМПОРТРАНЖ
Эта формула часто используется для объединения двух диапазонов в таблице Google.
Выберите ячейку под первым диапазоном данных, введите формулу ниже:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/13K4vuZukmS4-x0qJs0EAXQkneIdNqelruDBF5ff5xNQ/edit","B!A2:B4")
Затем данные из листа B были скопированы ниже.
В формуле
https://docs.google.com/spreadsheets/d/13K4vuZukmS4-x0qJs0EAXQkneIdNqelruDBF5ff5xNQ/edit
это расположение листа, вы можете найти его в строке веб-адреса.
В! А2: В4 - это диапазон ячеек на листе B, который вы хотите скопировать в первый диапазон.
Примечание: если ячейка, в которой размещается формула, была отредактирована ранее, формула вернет # ССЫЛКА!
Если вы хотите объединить листы из нескольких книг в Google Sheet, нет быстрого способа, как Excel.
Чтобы объединить листы Google из книг в одну книгу, вы можете использовать Скопировать в команда контекстного меню.
Щелкните правой кнопкой мыши лист, который вы хотите использовать, щелкните Скопировать в > Новая таблица or Существующая таблица.
Если вы выбираете Новая таблица, появится диалоговое окно с напоминанием о том, что лист был скопирован, щелкните OK. Теперь текущий лист скопирован в новую книгу. Вы можете нажать Открыть таблицу Проверять.
Если вы выбираете Существующая таблица, Выберите электронную таблицу, в которую нужно скопировать этот лист появляется диалоговое окно.
1) Выберите, где находится существующая электронная таблица;
2) Выберите книгу, в которую вы хотите скопировать лист. Файлы;
3) Или вы можете напрямую ввести веб-адрес книги, в которую хотите скопировать, в поле Или вставить веб-адрес здесь .
4) Нажмите Выберите.
Если лист копируется в новую электронную таблицу, имя листа в новой книге совпадает с исходным именем, если лист копируется в существующую электронную таблицу, к имени листа в объединенной книге будет добавлен префикс Копия .
Расширение
Предположим, есть два листа, на которых есть несколько дубликатов, нам нужно объединить данные и удалить дубликаты, как показано на скриншоте ниже:
 |
 |
| Общий результат | Удалить дубликаты |
 |
 |
Нажмите, чтобы скачать образец
1.1 Объедините листы, затем удалите дубликаты, удалив дубликаты
В Excel Удалить дубликаты функция поможет вам быстро удалить повторяющиеся данные.
После использования вышеуказанных методов для объединения листов, затем выберите объединенные данные, нажмите Данные > Удалить дубликаты.
В Удалить дубликаты диалоговом окне выберите столбцы, из которых вы хотите удалить дубликаты, вы можете проверить У моих данных есть заголовки чтобы игнорировать заголовки, нажмите OK.
Затем повторяющиеся строки были удалены.
1.2 Объедините листы и удалите дубликаты с помощью удобного инструмента
Если нужно объединить только два диапазона и удалить дубликаты, Слияние таблиц of Kutools for Excel может напрямую удалять дубликаты при объединении.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Включите книгу, содержащую данные, которые вы хотите объединить, щелкните Кутулс Плюс > Слияние таблиц.
2. В TСлияние способностей - шаг 1 из 5 выберите два диапазона, которые вы хотите объединить, и, если в таблицах есть два заголовка, установите флажок Основная таблица имеет заголовок и Таблица поиска имеет заголовок флажки, щелкните Следующая.
3. в Шаг 2 из 5 в окне, отметьте ключевой столбец, на основе которого вы хотите объединить диапазоны, щелкните Следующая.
4. в Шаг 3 из 5 в окне вы можете проверить столбцы, данные в которых вы хотите обновить, на основе таблицы поиска, щелкните Следующая перейти к последнему шагу.
Совет: если в таблице поиска есть новый столбец, который необходимо добавить в основную таблицу, появится окно шага 4 из 5 для добавления столбцов.
5. в Шаг 5 из 5 окно, в Добавить параметры раздел, проверка Добавить несовпадающие строки в конец основной таблицы флажок в Варианты обновления раздел, проверка Обновляйте ячейки только при наличии данных в таблице поиска флажок. Нажмите Завершить.
Затем две таблицы объединяются в основную таблицу без дубликатов.
Предположим, что существует несколько книг с листами с одинаковыми именами, такими как лист1, лист2, теперь, чтобы объединить все листы с именем лист1 в один лист, все листы с именем лист2 в один лист, как показано ниже, вы можете использовать Kutools for ExcelАвтора Сочетать функция, чтобы быстро справиться с этой работой.
 |
 |
 |
 |
 |
 |
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Включите книги, которые вы объедините, нажмите Kутулс плюс > Сочетать.
2. Появится диалоговое окно с напоминанием о пароле. Если книги, которые вы используете, не содержат паролей, щелкните OK продолжить. В появлении Объедините рабочие листы - шаг 1 из 3 окно, проверка Объедините все листы с одинаковыми именами в один лист , нажмите Следующая.
3. в Шаг 2 из 3 окно, все листы открытых книг перечислены и проверены Список рабочих листов разделом, щелкните Следующая продолжать.
Советы:
Если вы хотите объединить только некоторые листы, просто отметьте нужные имена листов и оставьте другие не отмеченными.
Если вы хотите добавить больше книг для объединения, щелкните Добавить для добавления файлов или папок в Список рабочих книг .
4. в Шаг 3 из 3, укажите нужные вам настройки. Нажмите Завершить.
5. Выберите папку и дайте имя новой книге в Укажите имя файла и расположение для объединенной книги диалоговое окно, нажмите Сохранить.
6. Появится диалоговое окно, в котором вас спросят, сохранить ли настройки как сценарий, нажмите Да or Нет для вас нужно. Появится рабочая книга со списком ссылок на исходную книгу и новую книгу, щелкните ссылку пути к новой книге, чтобы открыть ее для проверки.
Объединить по строкам
Объединить по столбцу
Если вы хотите объединить одни и те же диапазоны на листах в один лист, например, объедините только диапазон A1: B5 книги A и книги B в один лист, функция объединения Kutools for Excel будет хорошим выбором.
| A | B | Комбинированный реуслт |
 |
 |
 |
1. Включите книги, которые вы будете использовать, щелкните Кутулс Плюс > Сочетать.
2. Появится диалоговое окно с напоминанием о пароле. Если книги, которые вы используете, не содержат паролей, щелкните OK продолжить. В появлении Объедините рабочие листы - шаг 1 из 3 окно, проверка Объедините несколько листов из книг в один лист , нажмите Следующая.
3. в Шаг 2 из 3 окно, все листы открытых книг перечислены и проверены Список рабочих листов раздел, щелкните значок выбора в Список рабочих листов, затем выберите диапазон, который вы хотите использовать. Затем нажмите Тот же диапазон кнопку, чтобы установить диапазон всех листов A1: B5. Нажмите Следующая.
Советы:
1) Если вы хотите объединить только некоторые листы, просто отметьте нужные имена листов и оставьте другие не отмеченными.
2) Если вы хотите добавить больше книг для объединения, щелкните Добавить для добавления файлов или папок в Список рабочих книг .
4. в Шаг 3 из 3, укажите нужные вам настройки. Нажмите Завершить.
5. Выберите папку и дайте имя новой книге в диалоговом окне «Укажите имя файла и расположение для объединенной книги», нажмите Сохранить.
6. Появится диалоговое окно, в котором вас спросят, сохранить ли настройки как сценарий, нажмите Да or Нет для вас нужно. Появится рабочая книга со списком ссылок на исходную книгу и новую книгу, щелкните ссылку пути к новой книге, чтобы открыть ее для проверки.
Вы также можете быть заинтересованы в |
|
Объединение / импорт нескольких файлов CSV в несколько листов Объедините ячейки и сохраните форматирование ячеек в Excel Объедините содержимое нескольких ячеек без потери данных в Excel Объединить дату и время в одну ячейку в Excel |
Лучшие инструменты для работы в офисе
Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
- Объединить ячейки / строки / столбцы и хранение данных; Разделить содержимое ячеек; Объедините повторяющиеся строки и сумму / среднее значение... Предотвращение дублирования ячеек; Сравнить диапазоны...
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
- Избранные и быстро вставляйте формулы, Диапазоны, диаграммы и изображения; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
- Группировка сводной таблицы по номер недели, день недели и другое ... Показать разблокированные, заблокированные ячейки разными цветами; Выделите ячейки, у которых есть формула / имя...
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
