Если вы работаете с информацией, распределенной по нескольким листам, например бюджетами из разных регионов или отчетами, созданными несколькими участниками, вы можете объединить эти данные в одном месте. Excel предлагает несколько способов сделать это в зависимости от того, хотите ли вы суммировать значения или просто объединять списки.
Подготовка
Убедитесь, что исходные данные хорошо структурированы.
-
Используйте формат списка (нет полностью пустых строк или столбцов).
-
Поддерживайте согласованность меток (заголовков столбцов) на разных листах.
-
Если в вашей версии Excel нет функции консолидации данных >, возможно, вы используете Excel для Интернета или платформу, которая не поддерживает эту функцию. В этом случае см. раздел "Вариант 2. Объединение или добавление данных вместо их суммирования".
Вариант 1. Суммирование данных с помощью функции консолидации
Используйте консолидацию, если требуется вычислить итоги, средние значения, подсчеты или другие суммарные результаты на листах. Можно консолидировать по позиции (тот же макет) или по категории (соответствующие метки).
Консолидация по позиции
Используйте его, если на каждом листе используется один и тот же макет ячейки.
-
Откройте исходные листы и убедитесь, что данные отображаются в одном и том же расположении на каждом листе.
-
Перейдите на лист, где вы хотите получить объединенные результаты.
-
Выберите верхнюю левую ячейку диапазона, в котором должны отображаться объединенные данные.
-
Убедитесь, что есть место для расширения данных вниз и вправо.
-
-
Выберите Данные >
-
Выберите функцию (например, Sum, Average или Count).
-
На каждом исходном листе:
-
Выберите диапазон данных.
-
Выберите Добавить , чтобы включить его во все ссылки.
-
-
Нажмите кнопку ОК , чтобы создать объединенный отчет.
Консолидация по категориям
Используйте его, если листы совместно используют одни и те же метки, даже если данные расположены не одинаково. Обратите внимание, что если на одном листе используется "Среднее", а на другом - "Среднее", сначала необходимо стандартизировать метки, чтобы Excel смог правильно их сопоставить.
-
Убедитесь, что на каждом листе используются соответствующие метки в верхней строке или левом столбце.
-
На целевом листе выберите левую верхнюю ячейку диапазона, в котором должны отображаться объединенные данные.
-
Убедитесь, что есть место для расширения данных вниз и вправо.
-
-
Перейдите в раздел Данные >
-
Выберите функцию (например, Sum, Average или Count).
-
Установите флажки в разделе Использовать метки в (верхняя строка, левый столбец или и то, и другое).
-
На каждом исходном листе:
-
Выберите диапазон данных.
-
Выберите Добавить , чтобы включить его во все ссылки.
-
-
Нажмите кнопку ОК , чтобы создать объединенный отчет.
Если метка отображается на одном листе, но не на другом, Excel по-прежнему включает ее. В результате создается новая строка или столбец.
Вариант 2. Объединение или добавление данных вместо их суммирования
Если вам нужно объединить или сложить строки из нескольких листов, а не вычислять итоги, вам потребуется другой подход.
Копирование и вставка
Это быстрый и ручной способ объединения данных. Он лучше всего подходит, если требуется объединить только несколько листов.
-
Создайте новый лист.
-
Скопируйте весь список с первого листа и вставьте его.
-
Повторите попытку для других листов, вставив непосредственно под существующие данные.
-
При необходимости удалите повторяющиеся заголовки.
Использование формулы VSTACK для стека данных
Если листы имеют одинаковую структуру столбцов, их можно динамически сложить с помощью функции VSTACK. В следующем примере будут объединены данные с трех листов.
=VSTACK(Sheet1!A1:D50, Sheet2!A1:D50, Sheet3!A1:D50)
При этом создается объединенный список, который обновляется при изменении данных на исходных листах.
Использование Power Query
Power Query позволяет автоматически импортировать и объединять данные из нескольких таблиц или листов, даже в разных книгах. Это лучше всего подходит для больших наборов данных и непрерывного слияния.
-
Выберите каждый диапазон данных и нажмите клавиши CTRL+T , чтобы преобразовать его в таблицу.
-
Перейдите в раздел Данные > получить> данных из других источников > пустой запрос.
-
Используйте Excel.CurrentWorkbook() в строке формул для просмотра таблиц.
-
Разверните и объедините их с помощью значка с двойной стрелкой.
-
Нажмите кнопку Закрыть & Загрузить , чтобы создать объединенный лист.
Этот подход создает динамический объединенный набор данных, который можно обновлять при каждом изменении данных.
Устранение неполадок и советы
На основе отзывов пользователей ниже приведены наиболее распространенные блоки преткновения.
Не удается найти "Консолидация"
Вы можете использовать Excel для Интернета или версию, которая не поддерживает его. Вместо этого используйте Power Query или формулы.
Диалоговое окно Консолидация не разрешает выбор диапазонов
Убедитесь, что диалоговое окно остается активным. Если он блокирует щелчок в других окнах, попробуйте изменить размер или переместить его.
Результаты консолидации выглядят неправильно
Убедитесь, что:
-
Метки точно соответствуют (например, "Среднее", а не "Среднее").
-
Нет пустой структуры строк или столбцов, разбиваемого списка.
-
Вы выбрали правильную функцию (сумма и среднее значение).
Данные отображаются в несогласованных строках или столбцах
Если листы не выровнены, используйте команду Консолидация по категориям , а не по позиции.
Вы хотите добавить данные, а не суммировать их
Вместо этого используйте VSTACK или Power Query. Они лучше подходят для слияния.
