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

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

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

Существуют различные способы сбора данных в одном месте, в книге или на листе. Копирование данных несколько раз, а затем вставка их в нужное место не рассматривается по очевидным причинам, а объединение данных с помощью таблиц pivot также является отдельным вопросом. Давайте остановимся на наиболее эффективном методе, когда вам нужно собрать большое количество данных с разных листов или книг на одном листе.

Сбор данных с нескольких листов Excel на итоговый лист

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

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

1) Используемыми диапазонами являются диапазоны, начинающиеся с первой используемой ячейки и заканчивающиеся последней используемой ячейкой;

2) Предварительно выбранные диапазоны на каждом листе — диапазоны, выделенные пользователем самостоятельно в соответствии с его потребностями консолидации данных;

3) Области с одинаковым именем — области с одинаковым адресом в каждом листе, указанном в активном листе;

Диапазоны содержат ячейки, начиная с выделенной ячейки и заканчивая концом листа (последней использованной ячейкой).

С помощью диалогового окна Add-In можно выбрать, с каких листов собирать данные. Вы можете выбрать обработку всех листов, видимых, скрытых, непустых, с указанными именами, по маске имени, в диапазоне чисел, с указанным значением в определенном диапазоне.

В диалоговом окне Add-On можно также указать, как будут располагаться данные; в зависимости от выбранного варианта диапазоны будут располагаться либо друг под другом (вертикальное расположение), либо прямо рядом друг с другом (горизонтальное расположение).

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

Часто копируемые диапазоны содержат формулы, ссылающиеся на ячейки, адреса которых находятся за пределами копируемого диапазона или нежелательны на сводном листе, поэтому в таких случаях добавлена опция «Заменить формулы результатами вычислений». Невозможно заменить формулы результатами вычислений, если копируемые диапазоны содержат конкатенированные ячейки.

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

Если сводный лист содержит ненужные пустые строки, их можно автоматически удалить с помощью надстройки Selective Row Removal.

Как правило, после переноса данных на сводный лист, сами исходные листы становятся ненужными, и в этом случае их можно удалить полностью или выборочно с помощью модуля удаления листов.

Видео по сбору данных со всех листов на итоговый лист

Сбор данных с нескольких книг Excel на один лист

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

Используя дополнение для объединения рабочих книг в одну, соберите все листы в один файл;

2) Используя надстройку для сбора данных на сводный лист, объедините данные из различных листов.

Консолидация данных в Excel с примерами использования

Несколько пользователей Microsoft Excel могут иметь один и тот же тип таблицы в одном файле или нескольких книгах одновременно.

Данные необходимо консолидировать. Соберите их в одном отчете, чтобы получить общую картину. Инструмент под названием «Консолидация» позаботится об этой задаче.

Как сделать консолидацию данных в Excel

Одну и ту же структуру разделяют 4 файла. Например, рассмотрим квартальные продажи мебели.

Нам нужно сделать общий отчет с помощью «Консолидации данных». Во-первых, давайте убедимся, что

• Все таблицы имели одинаковое расположение,
• Названия столбцов были одинаковыми (столбцы можно переставлять),
• Не было пустых строк или столбцов.

Диапазоны с исходными данными должны быть открыты.

Консолидированные данные назначаются новому листу или новой книге. Откройте его. Установите курсор в первую ячейку объединенного диапазона.

Внимание. Ячейки справа и ниже должны быть пустыми. Когда вы запустите Consolidate, вы сможете заполнить столько строк и столбцов, сколько вам нужно.

Перейдите на вкладку «Данные». В группе «Работа с данными» нажмите на кнопку «Консолидировать».

Появляется следующее диалоговое окно

На рисунке показан раскрывающийся список «Функции». Это типы вычислений, которые команда «Консолидация» может выполнять при работе с данными. Выберите «Сумма» (значения исходных диапазонов будут суммированы).

Перейдем к следующему полю — «Link».

Поместите курсор в поле. Откройте лист «1 квартал». Выделите таблицу с заголовком. Первый объединяемый диапазон появляется в поле Link (Ссылка). Нажмите на кнопку «Добавить».

С помощью второй, третьей и четвертой четвертей выберите нужные диапазоны. Нажмите «Добавить».

Таблицы для консолидации отображаются в поле «Список диапазона».

Установите флажок «подписи верхней строки» для автоматического создания заголовков столбцов. Команда должна суммировать все записи значений для самого левого столбца — напротив «значения левого столбца». Автоматически обновлять сводный отчет при добавлении новых данных в исходные таблицы — напротив «создавать ссылки на исходные данные».

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

Нажмите кнопку OK, чтобы выйти из меню «Консолидация» и создать сводную таблицу.

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

Консолидация данных в Excel: практическая работа

В Microsoft Excel возможны разные типы консолидации данных:

1. По местоположению. Консолидированные данные имеют такое же представление и порядок, как и исходные данные.
2. По категориям. Данные организованы в соответствии с различными принципами. Но в сводной таблице используются те же заголовки строк и столбцов.
3. По формуле. Используется, когда нет постоянных категорий. Содержит ссылки на ячейки в других листах.
4. В отношении сводной таблицы. Вместо консолидации данных используется инструмент сводной таблицы.

В результате консолидации на основе местоположения (по позиции) все исходные таблицы идентичны. Помимо имен столбцов, имена строк также одинаковы (см. пример выше). В диапазоне от 2 до 4 это значение также должно занимать шестую строку, если «тахта» занимает шестую строку в диапазоне 1.

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

Создаются следующие книги: Магазин 1, Магазин 2 и Магазин 3. Структура одинаковая. Расположение данных идентично. Давайте объединим их по элементам.

1. Откройте три книги. Плюс пустая рабочая книга, в которой будет размещена сводная таблица. В пустой рабочей книге выберите левый верхний угол пустого листа. Откройте меню для инструмента консолидации.
2. Для составления сводного отчета с помощью функции «Среднее».
3. Чтобы отобразить путь к книгам с диапазонами источников, установите курсор в поле «Ссылка». На вкладке «Вид» нажмите на кнопку «Переключиться в другое окно».
4. Выберите имена файлов по одному, выделите диапазоны в открытых книгах и нажмите «Добавить».

Примечание. Нажав на кнопку «Обзор», вы также можете показать программе исходные диапазоны. Или переключившись на открытую книгу.

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

1. Чтобы создать объединенный диапазон, откройте меню «Консолидация». Выберите функцию «Сумма» (например). Добавьте исходные диапазоны, используя один из описанных выше методов. Отметьте «значения в левой колонке» и «заголовки в верхней строке».
2. Нажмите OK.

По категориям Excel объединил данные из трех магазинов. В отчете есть данные по всем товарам. Независимо от розничного продавца, будь то один магазин или все три.

Примеры консолидации данных в Excel

На листе консолидированного отчета введите названия строк и столбцов консолидируемых диапазонов. Лучший способ сделать это — скопировать.

В первую ячейку объединенной таблицы значений введите формулу со ссылками на исходные ячейки в каждом листе. В нашем примере он находится в ячейке B2. Формула для суммы: = ‘Q1’!B2+ ‘Q2’!B2+ ‘Q3’!B2.

Для всего столбца скопируйте формулу:

Консолидация данных с помощью формул полезна, когда данные, которые необходимо консолидировать, находятся в разных ячейках на разных листах. В ячейке B5 листа магазина содержится номер магазина, в ячейке E8 листа склада — номер склада и т.д.

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