Функция консолидация в excel: Консолидация данных в Excel

Консолидация (объединение) данных из нескольких таблиц в одну

57932 27.11.2012 Скачать пример

Способ 1. С помощью формул 

Имеем несколько однотипных таблиц на разных листах одной книги. Например, вот такие:

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

Самый простой способ решения задачи «в лоб» — ввести в ячейку чистого листа формулу вида

=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3

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

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

=СУММ(‘2001 год:2003 год’!B3)

Фактически — это суммирование всех ячеек B3 на листах с 2001 по 2003, т. е. количество листов, по сути, может быть любым. Также в будущем возможно поместить между стартовым и финальным листами дополнительные листы с данными, которые также станут автоматически учитываться при суммировании.

Способ 2. Если таблицы неодинаковые или в разных файлах

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

Рассмотрим следующий пример. Имеем три разных файла (Иван.xlsx, Рита.xlsx и Федор.xlsx) с тремя таблицами:

Хорошо заметно, что таблицы не одинаковы — у них различные размеры и смысловая начинка. Тем не менее их можно собрать в единый отчет меньше, чем за минуту. Единственным условием успешного объединения (консолидации) таблиц в подобном случае является совпадение заголовков столбцов и строк.

Именно по первой строке и левому столбцу каждой таблицы Excel будет искать совпадения и суммировать наши данные.

Для того, чтобы выполнить такую консолидацию:

1. Заранее откройте исходные файлы
2. Создайте новую пустую книгу (Ctrl + N)
3. Установите в нее активную ячейку и выберите на вкладке (в меню) Данные — Консолидация (Data — Consolidate). Откроется соответствующее окно:

4. Установите курсор в строку Ссылка (Reference) и, переключившись в файл Иван.xlsx, выделите таблицу с данными (вместе с шапкой). Затем нажмите кнопку Добавить (Add) в окне консолидации, чтобы добавить выделенный диапазон в список объединяемых диапазонов.
5. Повторите эти же действия для файлов Риты и Федора. В итоге в списке должны оказаться все три диапазона:

 

 Обратите внимание, что в данном случае Excel запоминает, фактически, положение файла на диске, прописывая для каждого из них полный путь (диск-папка-файл-лист-адреса ячеек). Чтобы суммирование происходило с учетом заголовков столбцов и строк необходимо включить оба флажка Использовать в качестве имен (Use labels). Флаг Создавать связи с исходными данными (Create links to source data) позволит в будущем (при изменении данных в исходных файлах) производить пересчет консолидированного отчета автоматически.

После нажатия на ОК видим результат нашей работы:

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

Ссылки по теме

• Макрос для автоматической сборки данных с разных листов в одну таблицу 
• Макрос для сборки листов из нескольких файлов

 

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

Экономистам и финансистам приходится иметь дело с большим количеством данных и расчетов. Зачастую эта информация находится в разных таблицах, на разных листах программы Excel. Соответственно, возникает потребность создать сводную таблицу на основе объединения данных из разных источников. Из статьи вы узнаете, как быстро получить сводные итоги по нужным позициям, как консолидировать данные из нескольких таблиц.

КАК БЫСТРО ПОЛУЧИТЬ СВОДНЫЕ ИТОГИ ПО НУЖНЫМ ПОЗИЦИЯМ

Бывает так, что сформированная таблица (например, ведомость товаров на складе) содержит большое количество позиций, многие наименования в ней повторяются.

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

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

Предположим, есть табл. 1, в которой за определенный период времени обобщены данные о продажах в разрезе номенклатуры товаров и продавцов.

Таблица 1 довольно большая — 511 строк. Обработать вручную такой массив показателей не так просто, а для полноценного анализа нужны сводные данные о продажах в разрезе продавцов. Чтобы получить их, ставим курсор на первую ячейку с информацией, которую требуется объединить. Это будет ячейка «Продавец».

Выбираем: Вставка → Сводная таблица. Откроется меню «Создание сводной таблицы» (рис. 1).

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

После этого возникнет меню, в котором для завершения формирования новой таблицы нужно выбрать интересующие поля (рис. 2).

В нашем случае это поле «Продавец» и поле «Сумма продаж». Ставим соответствующие галочки и получаем следующий готовый результат (рис. 3).

Общий итог продаж за период составляет 158 690,04 руб. Выведены и данные продаж по каждому продавцу.

КАК КОНСОЛИДИРОВАТЬ ДАННЫЕ ИЗ НЕСКОЛЬКИХ ТАБЛИЦ

Объединить данные из нескольких таблиц можно и путем консолидации. Для этого нужно задействовать одноименную функцию: Данные → Консолидация.

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

Предположим, у нас есть три таблицы, содержащие поквартальные данные о зарплате работников (табл. 2–4). Они расположены на разных листах одного файла.


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

Займемся консолидацией. Выбираем диапазон каждой таблицы и нажимаем кнопку «Добавить» (рис. 4).

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

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

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

После проделанных действий получим сводную таблицу (рис. 5).

В консолидированной таблице корректно обобщены все необходимые данные.

 

Материал публикуется частично. Полностью его можно прочитать в журнале «Планово-экономический отдел» № 10, 2020.

Excel Объединить | CustomGuide

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

Excel может консолидировать информацию несколькими способами: по позиции или по категории.

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

Объединение данных

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

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

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

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

5. Выберите функцию из списка.

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

6. Нажмите кнопку Свернуть диалоговое окно .
7. Перейдите к рабочему листу с набором данных для консолидации.
8. Выберите данные, которые вы хотите включить в консолидацию.

   Убедитесь, что выбраны данные, а также все заголовки и итоги для включения.

9. Нажмите кнопку Развернуть диалоговое окно .
10. Нажмите кнопку Добавить .
11. Повторяйте шаги 6–10 , пока не будут добавлены все диапазоны данных, которые вы хотите объединить.
12. (Необязательно) Установите флажок Верхний ряд и/или Левый столбец, чтобы использовать метку из данных, на которые ссылаются, на листе, содержащем консолидированные данные. Это сообщает Excel, где находятся метки в исходных диапазонах.
13. (Необязательно) Если вы хотите, чтобы консолидация обновлялась автоматически при каждом изменении исходных данных, установите флажок Создать ссылки на исходные данные .
14. Щелкните OK .

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

БЕСПЛАТНЫЙ краткий справочник

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

Бесплатно для распространения с нашими комплиментами; мы надеемся, что вы рассмотрите наше платное обучение.

Рабочие листы Excel — Объединение

Если у вас есть группа таблиц (или списков), можно объединить (или консолидировать) все эти данные в одну таблицу (или список).
Это можно сделать с помощью диалогового окна (Данные > Консолидировать).
Вы можете использовать (Данные > Консолидировать) для объединения соответствующих значений из 255 различных рабочих листов в один рабочий лист.
Ваши данные могут быть объединены из нескольких разных источников либо на одном рабочем листе, либо на разных рабочих листах одной рабочей книги, либо даже в разных рабочих книгах.
Прежде чем объединять данные, вы должны сначала решить, куда вы хотите поместить консолидированные данные.
Вы можете поместить его на отдельный лист в существующей книге или на лист в другой книге.

Функция — позволяет выбрать тип функции консолидации, которую вы хотите использовать:

СУММА	Общее значение чисел в списке или диапазоне ячеек.
COUNT	Количество числовых значений в списке или массиве чисел.
COUNTA	Count Nums Количество непустых ячеек в списке или диапазоне ячеек.
СРЗНАЧ	Среднее арифметическое списка или массива чисел.
MAX	Наибольшее значение в списке или массиве чисел.
МИН	Наименьшее значение в списке или массиве чисел.
ПРОИЗВЕД	Произведение всех чисел в списке или диапазоне ячеек.
СТАНДОТКЛОН	Стандартное отклонение на основе выборки.
СТАНДОТКЛОН	Стандартное отклонение для всей совокупности.
VAR	Составная дисперсия на основе выборки.
VARP	Дисперсия на основе всей совокупности.

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




Типы консолидации

Существует несколько различных способов консолидации данных:
1) Консолидация по позициям — Когда исходные данные в рабочих листах находятся в одном и том же порядке и месте
2) Консолидация по категориям — Когда рабочие листы имеют одинаковые метки строк и столбцов, но данные расположены по-разному
3) Объединить по формуле — Когда нет согласованного макета, меток столбцов или категорий




Объединение диапазонов

Предположим, что все наши данные находятся на одном рабочем листе и расположены в виде простых таблиц, одна под другой.
Прежде всего создайте таблицу под другими таблицами, которая будет содержать консолидированные данные, в данном случае таблицу «B14:F17».
Выберите первую ячейку в этом диапазоне, т. е. «C15», а затем выберите («Данные» > «Консолидировать»), чтобы отобразить диалоговое окно «Консолидация».
В качестве альтернативы можно выделить весь диапазон ячеек «C15:F17», хотя Excel автоматически заполнит весь диапазон.

В этом примере мы будем использовать функцию консолидации SUM , которая используется по умолчанию.
В поле «Ссылка» выберите первый диапазон ячеек, который вы хотите использовать в консолидации, в данном случае «C3:F5».
Вы можете ввести диапазон ячеек напрямую, но убедитесь, что перед ним стоит имя рабочего листа, заключенное в одинарные кавычки.

Нажмите кнопку «Добавить», чтобы добавить этот диапазон в список «Все ссылки».
В поле «Ссылка» выберите второй диапазон ячеек, в данном случае «C9:F11″ и нажмите кнопку «Добавить».

Обзор — Это можно использовать, когда рабочая книга не открыта. Это позволит вам открыть соответствующую рабочую книгу.
Наверх строка — метки строк появятся на основном листе
Левый столбец — метки столбцов появятся на основном листе
Создать ссылки на исходные данные — этот параметр будет означать, что ваш сводная таблица данных будет автоматически обновляться при изменении любого из исходных значений.Эта опция вставляет необходимые формулы в сводную таблицу.

Нажмите OK, чтобы объединить выбранные диапазоны и создать сводную таблицу данных.
Когда вы связываете диапазоны на одном листе, вы не можете связать данные, хотя сводную таблицу можно очень легко обновить, выбрав (Данные > Объединить) и нажав OK.




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

Предположим, что у нас есть те же данные, что и выше, но на этот раз данные отображаются на отдельных рабочих листах в одной рабочей книге.
В этой книге есть 2 рабочих листа, один называется 2004, а другой называется 2005.
Сначала давайте создадим еще один рабочий лист в этой книге, который будет содержать консолидированные данные.

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

В этом примере мы будем использовать функцию консолидации усреднения, которую можно изменить в раскрывающемся списке Функция.
В поле «Ссылки» выберите вкладку «2005» и выберите диапазон, который вы хотите использовать при консолидации, в данном случае «C3:F5».
Нажмите кнопку «Добавить», чтобы добавить этот диапазон в «Список всех ссылок».
В поле «Ссылки» выберите вкладку «2004». Вы заметите, что соответствующий диапазон автоматически выделяется, поэтому просто нажмите «Добавить».

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




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

Это немного сложнее и объясняется на отдельной странице.
Для получения более подробной информации см. рабочие тетради Consolidation Workbooks стр.




Важно

Этот метод можно использовать для объединения до 255 рабочих листов в один рабочий лист. Количество рабочих листов, которые вы можете иметь в рабочей книге, ограничено только памятью на ПК (т.е. объемом оперативной памяти на ПК).
Вы также можете использовать отчеты сводных таблиц для консолидации данных. Дополнительные сведения см. в разделе сводных таблиц .