Как собрать данные из нескольких листов в один основной лист в Excel?

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

Сбор данных с нескольких листов в один с помощью функции Консолидация

Сбор данных с нескольких листов в один с помощью кода VBA

Сбор данных с нескольких листов в один с мощной функцией Объединение

Сбор данных с нескольких листов в один с помощью функции Консолидация

Если вы хотите собрать данные из нескольких листов в один лист в той же книге, вы можете применить функцию Консолидация в Excel.

1. На новом листе книги, из которой вы хотите собрать данные, нажмите Данные > Консолидировать.

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

(1) Выберите одну операцию, которую вы хотите выполнить после объединения данных, из выпадающего списка Функция;

(2) Нажмите чтобы выбрать диапазон каждого листа, который вы хотите собрать;

(3) Нажмите кнопку Добавить, чтобы добавить диапазон данных в поле со списком Все ссылки;

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

3. Нажмите ОК. Теперь данные собраны и суммированы на одном листе.

Легко объединяйте несколько листов/книг в один лист или книгу

Объединение нескольких листов или книг в один лист или книгу может быть утомительным в Excel, но с функцией Объединить в Kutools для Excel вы можете объединить десятки листов/книг в один лист или книгу. Кроме того, вы можете консолидировать листы в один всего за несколько кликов. Нажмите здесь, чтобы получить полнофункциональную 30-дневную бесплатную пробную версию!

Kutools for Excel: более 300 удобных надстроек для Excel, которые можно бесплатно опробовать без ограничений в течение 30 дней.

Сбор данных с нескольких листов в один с помощью кода VBA

Вот код VBA, который собирает данные со всех листов в новый лист текущей книги.

1. Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic for Applications.

2. Нажмите Вставить > Модуль и скопируйте и вставьте приведенный ниже код в сценарий Модуля.

VBA: Собрать все данные с листов в один.

Sub Combine()
'UpdatebyExtendoffice20180205
    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

3. Нажмите F5 для запуска кода, теперь все данные всей книги собраны в новый лист Combined.

Сбор данных с нескольких листов в один с мощной функцией Объединение

Если вам часто нужно объединять листы в один, позвольте представить вам мощную функцию объединения — это утилита Kutools для Excel под названием Combine, которая имеет четыре основных функции:

Объединить несколько рабочих листов из книги в один рабочий лист

Объединить все рабочие листы с одинаковым именем в один рабочий лист

Объединить несколько рабочих листов из книг в одну книгу

Консолидировать и вычислить значения из нескольких книг в один рабочий лист

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

Если вы просто хотите объединить листы в один без дополнительных операций, выполните следующие шаги:

1. Откройте Excel, нажмите Kutools Plus > Объединить, может появиться одно или два диалоговых окна с напоминаниями, просто нажмите ОК и Да для продолжения. Смотрите скриншот:

2. Нажмите ОК для перехода в окно Объединить окно, отметьте Объединить несколько рабочих листов из книги в рабочий лист опцию.

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

4. Нажмите Далее для перехода к последнему шагу, где вы можете указать некоторые настройки результата объединения по мере необходимости. Если этот шаг не требуется, пропустите его.

5. Нажмите Завершить и выберите папку для размещения новой книги, содержащей объединенные данные из листов.

6. Нажмите Сохранить. Теперь все данные собраны из листов разных книг в один основной лист.

Если вы хотите консолидировать рабочие листы из разных книг в один, выполните следующие действия:

1. В окне Объединить отметьте Консолидировать и вычислить значения из нескольких рабочих листов в один рабочий лист опцию.

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

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

4. Нажмите Завершить, чтобы сохранить новую книгу в папке.

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