как в эксель собрать данные из нескольких листов на один

By /

Чтобы собрать данные из нескольких листов Excel на одном, есть несколько способов, в зависимости от того, как именно нужно объединить эти данные. Рассмотрим несколько методов, начиная с простых и заканчивая более сложными.

1. **Использование функции СЦЕПИТЬ или СЦЕПИТЬ.ТЕКСТ

Если вам нужно просто собрать данные из ячеек (например, текст из разных листов) в одну ячейку на другом листе, можно использовать функцию СЦЕПИТЬ или её более новую версию СЦЕПИТЬ.ТЕКСТ (для Excel 365 и Excel 2021).

Пример:

Допустим, у вас есть два листа: Лист1 и Лист2, и вам нужно собрать данные из ячейки A1 с этих листов на новом листе.

  1. Перейдите на новый лист, где хотите собрать данные.

  2. В ячейке, где хотите увидеть результат, используйте формулу:

excel
=СЦЕПИТЬ(Лист1!A1; " "; Лист2!A1)

или для более новых версий Excel:

excel
=СЦЕПИТЬ.ТЕКСТ(Лист1!A1; " "; Лист2!A1)

Это объединит значения ячеек A1 с Листа1 и A1 с Листа2 с пробелом между ними.

2. Использование функции INDIRECT (НЕПОСРЕДСТВЕННЫЙ)

Если вам нужно динамично ссылаться на ячейки с разных листов, можно использовать функцию НЕПОСРЕДСТВЕННЫЙ (INDIRECT). Эта функция позволяет строить ссылку на ячейку как строку, что даёт больше гибкости при работе с данными из нескольких листов.

Пример:

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

  1. На листе, где вы хотите собрать данные, введите название листа в ячейке (например, B1) и укажите:

excel
=НЕПОСРЕДСТВЕННЫЙ(B1 & "!A1")

Теперь, если в ячейке B1 будет название другого листа, формула будет подтягивать данные с ячейки A1 этого листа.

3. Использование функции СУММЕСЛИМН (SUMIFS) для агрегирования данных

Если вам нужно собрать числовые данные из разных листов и выполнить вычисления (например, суммировать, найти среднее), можно использовать функции вроде СУММЕСЛИМН (SUMIFS).

Пример:

Допустим, на разных листах у вас есть данные о продажах, и вы хотите собрать сумму продаж из нескольких листов:

  1. На новом листе используйте формулу типа:

excel
=СУММЕСЛИМН(Лист1!B:B; Лист1!A:A; "Условия") + СУММЕСЛИМН(Лист2!B:B; Лист2!A:A; "Условия")

Это суммирует значения в столбце B на Листах1 и 2, где в столбце A выполняется определённое условие.

4. Использование Power Query для более сложных задач

Для более сложных сценариев, например, если вам нужно собрать данные из нескольких листов с различной структурой, лучший вариант — использовать Power Query. Это инструмент для обработки и объединения данных, который встроен в Excel начиная с версии 2016.

Шаги:

  1. Откройте Power Query:

    • Перейдите в раздел Данные на ленте Excel.

    • Нажмите Получить данные → Из других источников → Из таблицы/диапазона.

  2. Импортируйте данные из каждого листа:

    • Выберите первый лист, из которого хотите собрать данные.

    • После этого Power Query откроется в редакторе, где вы можете очистить или преобразовать данные, если это нужно.

    • Нажмите Закрыть и загрузить, чтобы загрузить данные в новый лист.

  3. Повторите для всех других листов:

    • Сделайте то же самое для всех остальных листов.

  4. Объедините данные:

    • В Power Query выберите Главная → Объединить запросы. Выберите запросы (данные с других листов), которые хотите объединить.

    • Power Query позволит вам выбрать метод объединения (по ключевым столбцам, например, по ID).

  5. Закройте и загрузите итоговые данные в Excel.

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

5. Использование сводных таблиц для агрегации

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

Пример:

  1. Перейдите на новый лист и выберите Вставка → Сводная таблица.

  2. В поле Источник данных выберите Использовать внешний источник данных.

  3. Нажмите Выбрать соединение → выберите Использовать Power Query или Вставить диапазоны с нескольких листов.

  4. Добавьте данные в сводную таблицу.

Сводная таблица помогает собрать и агрегировать данные, такие как суммы или средние значения, без необходимости копировать все данные вручную.

6. Использование макросов VBA для автоматизации

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

Пример:

vba
Sub СобиратьДанные()
    Dim ws As Worksheet
    Dim wsResult As Worksheet
    Dim LastRow As Long
    Dim i As Integer

    ' Создать новый лист для результатов
    Set wsResult = ThisWorkbook.Sheets.Add
    wsResult.Name = "Результаты"

    ' Перебор всех листов
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> wsResult.Name Then
            LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
            ws.Range("A1:B" & LastRow).Copy wsResult.Cells(wsResult.Cells(wsResult.Rows.Count, "A").End(xlUp).Row + 1, 1)
        End If
    Next ws
End Sub

Этот макрос перебирает все листы в книге и копирует данные из столбцов A и B с каждого листа в новый лист Результаты.

Заключение

Выбор метода зависит от ваших целей:

  • Если вам нужно просто собрать данные из нескольких ячеек — используйте СЦЕПИТЬ или НЕПОСРЕДСТВЕННЫЙ.

  • Для агрегирования числовых данных — используйте СУММЕСЛИМН или Сводные таблицы.

  • Для более сложных операций или регулярного сбора данных — воспользуйтесь Power Query или VBA.

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

Scroll to Top

Карта сайта