Руководство по Excel: объединение нескольких книг/листов в одну

В Excel объединение листов — распространённая, но довольно сложная задача для большинства пользователей, особенно новичков. В этом руководстве описаны практически все возможные сценарии объединения, с которыми вы можете столкнуться, и предложены соответствующие профессиональные решения. Нужный ответ можно быстро найти, просто щёлкнув по ссылке в навигационном списке ниже, либо последовательно изучить все методы объединения, если вам это интересно.

В Excel объединение можно разделить на два типа: простое объединение содержимого и объединение содержимого с последующими вычислениями.

Примечание

В этом руководстве я подготовил несколько листов и примеров данных, чтобы нагляднее продемонстрировать описываемые методы. Используя приведённый ниже код VBA или формулы, вы можете адаптировать ссылки под свои нужды либо скачать образцы и сразу применить описанные методы на практике.

Объединение содержимого

Вот книга с четырьмя листами, которые нужно объединить на одном.

Нажмите, чтобы скачать пример файла

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 Объединение всех таблиц или именованных диапазонов с помощью запроса (Excel 2016 и более поздние версии)

Если вы используете Excel 2016 или более позднюю версию, функция «Запрос» позволяет объединить все созданные таблицы или именованные диапазоны за один раз.

Нажмите, чтобы скачать пример файла

Сначала убедитесь, что диапазоны преобразованы в таблицы или имеют имена. Инструкции по созданию таблиц и именованных диапазонов см. в разделах Как преобразовать диапазон в таблицу и обратно в Excel и Определение и использование имён в формулах.

1. Откройте книгу, листы которой требуется объединить, и нажмите Данные > Новый запрос > Из других источников > Пустой запрос.

2. В открывшемся окне Редактор запросов перейдите в строку Строка формул и введите в неё следующую формулу.

=Excel.CurrentWorkbook()

Нажмите клавишу Enter — и все таблицы из текущей книги появятся в списке.

3. Нажмите кнопку расширения рядом с надписью Content, установите флажок напротив параметра Expand и выберите пункт Выбрать все столбцы.

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 > Объединить > ОК, чтобы запустить мастер Объединения.

2. В окне Шаг 1 из 3установите флажок напротив параметра Копировать данные из нескольких книг в один листи нажмите кнопку Далее.

3. В окне Шаг 2 из 3 все открытые книги уже перечислены и отмечены в области Список книг, а все листы открытых книг — в области Список листов. Нажмите кнопку Далее, чтобы продолжить.

Совет: чтобы добавить книги для объединения, нажмите кнопку «Добавить» и укажите путь к нужным файлам.

4. На последнем шаге выберите нужные параметры и нажмите кнопку Готово.

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

6. Теперь все листы из открытых книг объединены, и появится диалоговое окно с предложением сохранить настройки как сценарий — нажмите Да или Нет по своему усмотрению.

Откройте сохранённую объединённую книгу — листы из разных книг теперь сведены в один.

Вот три книги, которые нужно объединить в одну.

Янв	Фев	Мар

jan.xlsx  feb.xlsx  mar.xlsx

2,1 Объединение книг в одну с помощью команды «Переместить или скопировать»

Если нужно объединить листы всего из нескольких книг, воспользуйтесь встроенной командой Excel Переместить или скопировать.

1. Откройте первые две книги, которые хотите объединить, активируйте первую из них, выделите нужные листы, щёлкните правой кнопкой мыши, чтобы вызвать контекстное меню, и выберите команду Переместить или скопировать.

Советы

1) Чтобы переместить все листы, сначала щёлкните правой кнопкой мыши по любой вкладке и выберите в контекстном меню пункт Выделить все листы, а затем воспользуйтесь командой Переместить или скопировать.

2) Удерживайте клавишу Ctrl, чтобы выделить несколько несмежных листов.

3) Выделите первый лист и, удерживая клавишу Shift, щёлкните последний лист, чтобы выделить несколько смежных листов.

2. В диалоговом окне Переместить или скопировать в поле В книгу раскрывающемся списке выберите книгу, в которую нужно переместить листы (в данном случае — Jan), затем укажите место размещения листов в разделе Перед листом и нажмите кнопку ОК.

Примечание:

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

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 для приложений.

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, чтобы выполнить код. После этого выбранные листы будут скопированы в конец книги «Текущая книга».

3,2 Объединение определённых листов из нескольких книг (из разных папок) с помощью удобного инструмента

Если книги, которые вы хотите объединить, находятся в разных папках, воспользуйтесь функцией Kutools для Excel — «Объединить».

После бесплатной установкиKutools для Excel выполните следующие действия:

1. Запустите Excel и нажмите KUTOOLS PLUS > Объединить. Появится диалоговое окно с уведомлением о паролях. Если используемые вами книги не защищены паролем, просто нажмите ОК, чтобы продолжить.

2. В окне Объединить листы – Шаг 1 из 3установите флажок напротив параметра Копировать листы из нескольких книг в одну книгуи нажмите кнопку Далее.

3. В окне Шаг 2 из 3 щёлкните стрелку рядом с кнопкой Добавить, чтобы отобразить выпадающее меню, и выберите пункт Папка.

4. Затем в диалоговом окне Выбор папки выберите нужную папку и нажмите кнопку Выбрать папку, чтобы добавить её в раздел Список книг.

5. Повторите предыдущий шаг, чтобы добавить все папки и отобразить все книги в разделе Список книг.

6. В окне Шаг 2 из 3 выберите одну книгу в разделе Список книг и установите флажки только напротив тех листов, которые нужно использовать, в разделе Список листов. Затем нажмите кнопку Одинаковые листы — так будут автоматически отмечены все листы с одинаковыми именами из книг, указанных в разделе Список книг. После этого нажмите кнопку Далее, чтобы продолжить.

7. На последнем шаге выберите нужные параметры и нажмите кнопку Готово.

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

9. Теперь выбранные листы объединены, и появляется диалоговое окно с вопросом, сохранить ли настройки как сценарий. Нажмите Да или Нет — по своему усмотрению.

Откроется окно «Новая рабочая книга» с результатами объединения. Нажмите ссылку «Выходной файл», чтобы просмотреть объединённый лист.

Результат объединения

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

Как показано на рисунке ниже, на двух листах расположены две таблицы, которые необходимо объединить в одну по ключевому столбцу в Excel.

Таблица1	Таблица2

Объединённая таблица

4,1 Объединение двух таблиц на основе одного Ключевой столбец с использованием запроса (Excel 2016 и более поздние версии)

Функция Запрос в Excel 2016 и более поздних версиях очень мощная: она позволяет объединять две таблицы на основе ключевого столбца.

Перед использованием функции Запрос убедитесь, что диапазоны, которые вы хотите объединить, уже преобразованы в таблицы.

combine-two-tables-based-on-a-key-column-query.xlsx

1. Щёлкните любую ячейку первой таблицы и выберите Данные > Из таблицы/диапазона в группе Получить и преобразовать данные.

2. В диалоговом окне Редактор запросов выберите команду Файл > Закрыть и загрузить В…. См. рисунок:

3. В появившемся диалоговом окне Загрузить в установите флажок напротив параметра Только создать подключение и нажмите кнопку Загрузить.

Теперь вы увидите область Запросы книги, где таблица отображается в виде ссылки.

Повторите описанные выше действия, чтобы добавить вторую таблицу в область Запросы книги.

4. Затем выберите Данные > Новый запрос > Объединить запросы > Слияние.

5. В окне Слияние выберите две таблицы, которые нужно объединить, в двух отдельных полях раскрывающегося списка. Таблица, расположенная внизу, будет добавлена к таблице, находящейся выше.

6. Затем щёлкните столбец Ключевой столбец, на основе которого требуется объединить две таблицы, и нажмите кнопку ОК.

7. Появится окно Merge1 – Query Editor. Нажмите кнопку развертывания рядом с NewColumn, установите флажки для всех столбцов, кроме ключевого, снимите флажок Use original column name as prefix и нажмите 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: the first lookup value (Ключевой столбец);

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: the first lookup value (in the Ключевой столбец);

Sheet2!$A$2:$A$5: Ключевой столбец, на основе которого выполняется объединение двух листов.

2. Нажмите клавишу Enter, чтобы получить первый результат.

3. Затем перетащите маркер автозаполнения вниз, чтобы заполнить все данные.

4. Выделите ячейки с формулами, перейдите на вкладку Главнаяи отформатируйте ячейки по своему усмотрению в группе Число.

Подробнее об ИНДЕКС (INDEX).

Подробнее о ПОИСКПОЗ (MATCH).

4,3 Объединение двух листов на основе одного Ключевой столбец с помощью удобного инструмента

Помимо функции «Объединить», в продукте Kutools для Excel есть ещё одна мощная функция — Объединить таблицы, которая позволяет быстро и легко объединить два листа на основе одного ключевого столбца.

combine-two-sheets-based-on-a-key-column-handy-tool.xlsx

После бесплатной установкиKutools для Excel выполните следующие действия:

1. Нажмите KUTOOLS PLUS > Объединить таблицы.

2. В окне Tables Merge – Шаг 1 из 5выберите диапазоны таблиц в разделах Выберите основную таблицуи Выберите таблицу поискасоответственно и нажмите Далее.

3. В окне Шаг 2 из 5 установите флажок напротив ключевого столбца, на основе которого нужно выполнить объединение, и нажмите Далее.

4. В окне Шаг 3 из 5 вы можете выбрать столбцы, данные в которых требуется обновить на основе справочной таблицы. Если обновление не нужно, просто нажмите Далее.

5. В окне Шаг 4 из 5 отметьте столбцы, которые нужно добавить в основную таблицу, и нажмите Далее.

6. На последнем шаге задайте необходимые параметры и нажмите Готово.

Эта функция позволяет объединять два листа из разных книг.

Допустим, на листах Sheet1 и Sheet2 размещены две таблицы. Необходимо перенести данные из столбца End_Dates с листа Sheet2 на лист Sheet1, используя для сопоставления столбцы 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 для приложений.

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 Объединение листов с одинаковыми заголовками с помощью удобного инструмента

Функция Combine из Kutools для Excel также справляется с этой задачей и позволяет объединять листы из разных книг.

После бесплатной установкиKutools для Excel выполните следующие действия:

1. Активируйте книгу, листы которой требуется объединить (с одинаковыми заголовками), и нажмите KUTOOLS PLUS > Combine.

2. Появится диалоговое окно с напоминанием о пароле. Если используемые книги не защищены паролем, нажмите OK, чтобы продолжить. В появившемся окне Объединить листы – Шаг 1 из 3 установите флажок Копировать данные из нескольких книг в один лист и нажмите Далее.

3. В окне Шаг 2 из 3 все листы перечислены и отмечены в разделе Список листов; нажмите Далее, чтобы продолжить.

Советы:

1) Если вы хотите объединить только некоторые листы, отметьте нужные имена листов и снимите отметки с остальных.

2) Чтобы добавить дополнительные книги для объединения, нажмите Добавить, чтобы выбрать файлы или папки и добавить их в раздел Добавить файлы.

4. В окне Шаг 3 из 3 установите флажок Объединить данные по строкам, введите количество строк заголовков в текстовое поле Количество строк заголовков и при необходимости задайте другие параметры. Нажмите Готово.

5. В диалоговом окне Укажите местоположение и имя файла для сохранения объединенных результатов выберите папку и задайте имя для новой рабочей книги, затем нажмите Сохранить.

6. Появится диалоговое окно с предложением сохранить настройки как сценарий; нажмите Да или Нет в зависимости от ваших потребностей. После этого откроется книга со списком исходной книги и ссылок «Новая рабочая книга»; нажмите новую ссылку «Путь к книге», чтобы открыть её для проверки.

Консолидация

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

Результат

Нажмите, чтобы скачать пример файла

1,1 Объединение листов и выполнение вычислений с помощью функции консолидации

В Excel функция Consolidate позволяет объединять листы и выполнять вычисления.

1. Активируйте книгу, листы которой нужно объединить, выберите ячейку для размещения объединённых данных и нажмите Данные > Consolidate.

2. В диалоговом окне Consolidate выполните следующие настройки:

1) В раскрывающемся меню Function выберите требуемое вычисление после объединения листов.

2) Нажмите значок выбора рядом с кнопкой Browse, чтобы указать диапазон для объединения, затем нажмите Add, чтобы добавить его в раздел All references.

Повторите этот шаг, чтобы добавить все необходимые диапазоны в раздел All references.

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

4) Если вы хотите, чтобы объединённые данные автоматически обновлялись при изменении исходных данных, установите флажок Создать ссылки на исходные данные.

3. Нажмите OK. Диапазоны будут объединены и просуммированы на основе заголовков.

Примечание: если диапазоны для объединения находятся в других книгах, нажмите Browse в диалоговом окне Consolidate, выберите книгу, затем введите имя листа и диапазон в текстовое поле и нажмите Add, чтобы добавить диапазон в раздел All references.

1,2 Объединение листов и выполнение вычислений с помощью удобного инструмента

Функция Combine из Kutools для Excel позволяет объединять листы из нескольких книг и выполнять вычисления на одном листе.

После бесплатной установкиKutools для Excel выполните следующие действия:

1. Активируйте книгу(и), которые требуется консолидировать, и нажмите KUTOOLS PLUS > Combine.

2. Появится диалоговое окно с напоминанием о пароле. Если используемые книги не защищены паролем, нажмите OK, чтобы продолжить. В появившемся окне Объединить листы – Шаг 1 из 3 установите флажок Объединить и вычислить данные из нескольких книг в один лист и нажмите Далее.

3. В окне Шаг 2 из 3 все листы открытых книг перечислены и отмечены в разделе Список листов. Нажмите Далее, чтобы продолжить.

Совет: если вы хотите объединить только некоторые листы, отметьте нужные имена листов и снимите отметки с остальных. Чтобы добавить дополнительные книги для объединения, нажмите Добавить, чтобы выбрать файлы или папки и добавить их в раздел Добавить файлы или Список книг.

4. В окне Шаг 3 из 3 укажите требуемые вычисления и метки. Нажмите Готово.

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

6. Появится диалоговое окно с предложением сохранить настройки как сценарий; нажмите Даили Нетв зависимости от ваших потребностей. Откроется книга со списком исходной книги и ссылок Новая рабочая книга; нажмите новую ссылку Путь к книге, чтобы открыть её для проверки.

Если ваши данные имеют простую структуру, как показано на приведённом ниже снимке экрана, вы можете объединить листы непосредственно в сводную таблицу.

Нажмите, чтобы скачать образец

1. Активируйте используемую книгу и нажмите Настроить панель быстрого доступа > Другие команды.

2. В появившемся окне Параметры Excel в разделе Выберите команды из выберите Все команды, прокрутите полосу прокрутки и найдите Мастер сводных таблиц и сводных диаграмм.

3. Щелкните Добавить, чтобы добавить Мастер сводных таблиц и сводных диаграмм на панель Настройка панели быстрого доступа, затем нажмите ОК.

4. Нажмите команду Мастер сводных таблиц и сводных диаграмм на панели инструментов, а в окне Шаг 1 из 3 установите флажок Несколько диапазонов консолидации и сводная таблица, затем нажмите Далее.
 

5. В окне Шаг 2a из 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 были скопированы.

В формуле:

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 Таблицах, здесь нет такого быстрого способа, как в Excel.

Чтобы объединить листы Google из разных книг в одну, воспользуйтесь командой контекстного меню Копировать в.

Щелкните правой кнопкой мыши лист, который хотите использовать, и выберите Копировать в → Новую таблицу или Существующую таблицу.

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

Если вы выбрали Существующую таблицу, откроется диалоговое окно Выберите таблицу, в которую нужно скопировать этот лист.

1) Выберите расположение существующей таблицы;

2) Выберите книгу, в которую нужно скопировать лист, в разделе Файлы;

3) Или вы можете напрямую ввести веб-адрес книги, в которую нужно скопировать данные, в поле Или вставьте здесь веб-адрес.

4) Нажмите Выбрать.

Если лист копируется в новую таблицу, его имя в новой рабочей книге остаётся таким же, как исходное. Если же лист копируется в существующую таблицу, к имени листа в объединённой книге добавляется префикс «Копия ».

Расширение

Предположим, что есть два листа с некоторыми дубликатами, и нам нужно объединить данные и удалить дубликаты, как показано на приведенном ниже снимке экрана:

Общий результат	Удалить дубликаты

Нажмите, чтобы скачать образец

1,1 Объединение листов с последующим Удалить дубликаты с помощью Удалить дубликаты

В Excel функция Удалить дубликаты позволяет быстро избавиться от повторяющихся данных.

После объединения листов описанными выше методами выделите объединённые данные и нажмите Данные > Удалить дубликаты.

В диалоговом окне Удалить дубликаты выберите столбцы, по которым нужно удалить дубликаты, установите флажок Включить заголовки, чтобы игнорировать заголовки, и нажмите ОК.

Таким образом Дублирующая строка были удалены.

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’s Объединить — и выполните эту задачу всего за несколько кликов!

2020.xlsx 2021.xlsx

После бесплатной установкиKutools для Excel выполните следующие действия:

1. Откройте книги, которые вы хотите объединить, и нажмите Kutools Plus > Объединить.

2. Появится диалоговое окно с напоминанием о пароле. Если книги, которые вы используете, не защищены паролем, нажмите ОК, чтобы продолжить. В появившемся окне Объединить листы – Шаг 1 из 3 установите флажок напротив параметра Объединить данные из нескольких книг с одинаковым именем листа и нажмите кнопку Далее.

3. В окне Шаг 2 из 3 все листы открытых книг перечислены и отмечены в разделе Список листов. Нажмите кнопку Далее, чтобы продолжить.

Советы:

Если вы хотите объединить только некоторые листы, отметьте нужные имена листов и снимите отметки с остальных.

Если вы хотите добавить ещё книги для объединения, нажмите кнопку Добавить, чтобы выбрать файлы — добавьте файлы или папки и поместите их в раздел Список книг.

4. В окне Шаг 3 из 3 задайте необходимые параметры и нажмите кнопку Готово.

5. В диалоговом окне Укажите местоположение и имя файла для сохранения объединенных результатов выберите папку и задайте имя для новой рабочей книги, затем нажмите кнопку Сохранить.

6. Появится диалоговое окно с вопросом, сохранить ли параметры как сценарий. Нажмите кнопку Да или Нет в зависимости от ваших потребностей. После этого откроется книга со списком исходных книг и ссылок «Новая рабочая книга»; щёлкните новую ссылку «Путь к книге», чтобы открыть её и проверить результат.

Объединить данные по строкам

Объединить данные по столбцам

Если вы хотите объединить одинаковые диапазоны с разных листов в один лист (например, только диапазон A1:B5 из книги A и книги B), функция «Объединить» в Kutools для Excel станет отличным решением.

A	B	Объединённый результат

A.xlsx B.xlsx

1. Откройте книги, с которыми будете работать, и нажмите KUTOOLS PLUS > Объединить.

2. Появится диалоговое окно с напоминанием о пароле. Если книги, которые вы используете, не защищены паролем, нажмите ОК, чтобы продолжить. В появившемся окне Объединить листы – Шаг 1 из 3 установите флажок напротив параметра Копировать данные из нескольких книг в один лист и нажмите кнопку Далее.

3. В окне Шаг 2 из 3 все листы открытых книг перечислены и отмечены в разделе Список листов. Щёлкните значок выбора в столбце Список листов и выберите нужный диапазон. Затем нажмите кнопку Одинаковый диапазон, чтобы задать для всех листов диапазон A1:B5. Нажмите кнопку Далее.

Советы:

1) Если вы хотите объединить только некоторые листы, отметьте нужные имена листов и снимите отметки с остальных.

2) Если вы хотите добавить дополнительные книги для объединения, нажмите кнопку Добавить, чтобы выбрать файлы — добавьте файлы или папки и поместите их в раздел Список книг.

4. В окне Шаг 3 из 3задайте необходимые параметры. Нажмите кнопку Готово.

5. Укажите Выберите папку и задайте имя для Новая рабочая книга в диалоговом окне Укажите местоположение и имя файла для сохранения объединенных результатов и нажмите кнопку Сохранить.

6. Появится диалоговое окно с вопросом, сохранить ли параметры как сценарий. Нажмите кнопку Даили Нетв зависимости от ваших потребностей. Откроется книга со списком исходных книг и ссылок Новая рабочая книга; щёлкните новую ссылку Путь к книге, чтобы открыть её и проверить результат.

---

Лучшие инструменты для повышения продуктивности в офисе

Kutools для Excel решает большинство ваших задач и повышает продуктивность на 80 %

• Супер строка формул (удобное редактирование многострочного текста и формул); Режим чтения (удобный просмотр и редактирование большого количества ячеек); Вставка в диапазон фильтрации…
• Объединение ячеек, строк или столбцов с сохранением данных; разделение содержимого ячеек;Объединение дублирующихся строк с суммированием или усреднением… предотвращение дублирования записей в ячейках;Сравнение диапазонов…
• Выбор дубликатов или уникальных строк; выбор пустых строк (все ячейки пустые); улучшенный и нечёткий поиск по множеству книг; случайный выбор…
• Точная копия нескольких ячеек без изменения ссылок в формулах; автоматическое создание ссылок на несколько листов; вставка маркеров, флажков и многое другое…
• Избранные и быстрая вставка формул, диапазонов, диаграмм и изображений; Защита ячеек паролем; Создание списка рассылкии отправка электронных писем…
• Извлечение текста, добавление текста, удаление символов в определённой позиции, удаление пробелов; создание и печать статистики по странице данных; преобразование между содержимым ячеек и примечаниями…
• Суперфильтр (сохранение и применение схем фильтрации к другим листам); расширенная сортировка по месяцам, неделям, дням, частоте и другим параметрам; специальный фильтр по полужирному, курсиву…
• Объединение книг и листов; объединение таблиц на основе ключевого столбца; разделение данных на несколько листов; пакетное преобразование XLS, XLSX и PDF…
• Сводная таблица: группировка по номеру недели, дню недели и многому другому… Показ разблокированных и заблокированных ячеек разными цветами; Выделение ячеек, содержащих формулы или имена…

Подробнее…Полный функционал, бесплатная пробная версия на 30 дней…Купить… 

---

Office Tab — добавляет вкладки в Office и значительно упрощает вашу работу

• Включите вкладки для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
• Открывайте и создавайте несколько документов во вкладках одного окна — это удобнее, чем использовать отдельные окна.
• Повышает продуктивность на 50 % и экономит сотни кликов мышью каждый день!

Подробнее…Полный функционал, бесплатная пробная версия на 30 дней…Купить…