Способ консолидации данных который не предусмотрен в ms excel
Обновлено: 07.07.2024
При сведении данных из различных однотипных отчетов, таблиц, диапазонов ячеек в один общий, сводный отчет можно использовать различные способы, от простых формул, до мощных и гибких инструментов по консолидации данных.
Консолидация в Excel
Объединение или сведение данных из разных диапазонов ячеек в один выходной диапазон, с использованием какой-либо функции (например, суммирования) называется консолидацией.
Простой пример консолидированной отчетности - любой квартальный отчет, полученный суммированием трех месячных отчетов.
Различают консолидацию по расположению и консолидацию по категориям. Различие заключается в степени упорядоченности исходных данных.
Консолидация по расположению
Для обобщения данных из различных таблиц, все эти таблицы должны быть одинаковыми.
Консолидация по категориям
Для сведения данных, расположенных в разном порядке, но имеющих одинаковые подписи строк и столбцов используется консолидацию по категориям значений.
Сведение данных при помощи формул
Консолидирование данных подразумевает использование какой-либо функции, например, сумма или произведение значений, поиск средних, минимальных и максимальных значений. Простой свод данных из нескольких однотипных таблиц можно сделать обычными, стандартными формулами при помощи функций «СУММ», «ПРОИЗВЕД», «МАКС», «МИН» и т.д.
Стандартная консолидация
Для решения задач по сбору разрозненных данных в обобщенную таблицу, существует специальный инструмент – «Консолидация» (кнопку можно найти на вкладке Данные/Работа с данными/Консолидация). Инструмент достаточно мощный, позволяет осуществлять консолидацию как по расположению, так и по категориям, имеет более десяти используемых функций, позволяет создавать связи с исходными данными. Все достаточно просто, нажатие на кнопку вызывает окно «Консолидация», в котором выбирается функция, создается список из диапазонов исходных данных. При нарушении очередности строк и/или столбцов в исходных диапазонах, задаются адреса диапазонов, включающие в себя подписи строк и столбцов, а также ставятся флажки в полях «Подписи верхней строки» и «Значения левого столбца».
Консолидация при помощи надстройки
При обработке больших объемов информации, использование формул и стандартной консолидации может быть неудобным. Формулы могут быть слишком громоздкими и в них легко может закрасться ошибка, диапазонов в списке для консолидации также может оказаться очень много, что сделает использование этого инструмента неприемлемым.
Для решения задач, выходящих за рамки возможностей стандартных средств Excel, можно использовать надстройки – процедуры и функции, написанные на встроенном в приложения Microsoft Office, языке программирования. Надстройки легко интегрируются в приложения и расширяют их стандартные возможности.
При помощи надстройки можно быстро консолидировать данные из большого количества рабочих книг, по различным листам и диапазонам.
Надстройка позволяет:
1. Быстро создавать список исходных рабочих книг для консолидации;
2. Гибко настраивать листы, содержащие исходные данные, по их видимости, номерам, именам, наличию определенных значений и так далее;
3. Задавать адреса на итоговом (активном) листе как для одного, так и для нескольких диапазонов ячеек;
4. Выбирать одну из наиболее используемых функций (сумма, произведение, максимум, минимум);
5. Выбирать тип сведения данных (по расположению или по категориям).
консолидации данных
В табличном редакторе Microsoft Excel предусмотрено несколько способов
консолидации:
· С помощью трехмерных
ссылок, что является наиболее предпочтительным способом. При использовании
трехмерных ссылок отсутствуют ограничения по расположению данных в исходных
областях.
· По расположению, если
данные исходных областей находятся в одном и том же месте и размещены в одном и
том же порядке. Используйте этот способ для консолидации данных нескольких
листов, созданных на основе одного шаблона.
· Если данные, вводимые
с помощью нескольких листов-форм, необходимо выводить на отдельные листы,
используйте мастер шаблонов с функцией автоматического сбора данных.
· По категориям, если
данные исходных областей не упорядочены, но имеют одни и те же заголовки.
Используйте этот способ для
консолидации данных листов, имеющих разную структуру, но одинаковые заголовки.
· С помощью сводной
таблицы. Этот способ сходен с консолидацией по категориям, но обеспечивает
большую гибкость при реорганизации категорий.
Консолидация данных с использованием трехмерных ссылок
Для консолидации данных с использованием трехмерных ссылок
необходимо проделать следующие шаги:
1.
На листе консолидации скопируйте или
задайте надписи для данных консолидации.
2. Укажите ячейку, в
которую следует поместить данные консолидации.
3. Введите формулу. Она должна
включать ссылки на исходные ячейки каждого листа, содержащего данные, для
которых будет выполняться консолидация.
4. Повторите шаги 2 и 3
для каждой ячейки, в которой требуется вывести результаты обработки данных.
Использование
трехмерных ссылок для объединения данных
Если исходные
листы имеют различные шаблоны и подписи или если требуется создать собственный
шаблон, или применить собственные формулы объединения данных, при консолидации
следует использовать трехмерные ссылки. Трехмерные ссылки обновляются
автоматически при изменении исходных данных.
Добавление
данных для объединения
Если все
исходные листы имеют одинаковый шаблон, в трехмерных формулах можно
использовать диапазон имен листов. Чтобы ввести в объединение новый лист,
скопируйте его в диапазон, на который ссылается формула.
Что нада объединить?
Ячейки? листы? книги?
пожалуйста опиши проблему конкретнее
Условия для выполнения процедуры консолидации
Естественно, что не все таблицы можно консолидировать в одну, а только те, которые соответствуют определенным условиям:
- столбцы во всех таблицах должны иметь одинаковое название (допускается лишь перестановка столбцов местами);
- не должно быть столбцов или строк с пустыми значениями;
- шаблоны у таблиц должны быть одинаковыми.
Создание консолидированной таблицы
Рассмотрим, как создать консолидированную таблицу на примере трех таблиц, имеющих одинаковый шаблон и структуру данных. Каждая из них расположена на отдельном листе, хотя по такому же алгоритму можно создать консолидированную таблицу из данных расположенных в разных книгах (файлах).
- Открываем отдельный лист для консолидированной таблицы.
В поле «Функция» требуется установить, какое действие с ячейками будет выполняться при совпадении строк и столбцов. Это могут быть следующие действия:
- сумма;
- количество;
- среднее;
- максимум;
- минимум;
- произведение;
- количество чисел;
- смещенное отклонение;
- несмещенное отклонение;
- смещенная дисперсия;
- несмещенная дисперсия.
Как видим, после этого диапазон добавляется в список.
Аналогичным образом, добавляем все другие диапазоны, которые будут участвовать в процессе консолидации данных.
Для того, чтобы автоматически добавить название столбцов в шапку, ставим галочку около параметра «Подписи верхней строки». Для того, чтобы производилось суммирование данных устанавливаем галочку около параметра «Значения левого столбца». Если вы хотите, чтобы при обновлении данных в первичных таблицах обновлялась также и вся информация в консолидированной таблице, то обязательно следует установить галочку около параметра «Создавать связи с исходными данными». Но, в этом случае нужно учесть, что, если вы захотите в исходную таблицу добавить новые строки, то придется снять галочку с данного пункта и пересчитать значения вручную.
Как видим, консолидация данных в Эксель является очень удобным инструментом, благодаря которому можно собрать воедино информацию расположенную не только в разных таблицах и на разных листах, но даже размещенную в других файлах (книгах). Делается это относительно просто и быстро.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Имеем несколько однотипных таблиц на разных листах одной книги. Например, вот такие:
Необходимо объединить их все в одну общую таблицу, просуммировав совпадающие значения по кварталам и наименованиям.
Самый простой способ решения задачи "в лоб" - ввести в ячейку чистого листа формулу вида
=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3
которая просуммирует содержимое ячеек B2 с каждого из указанных листов, и затем скопировать ее на остальные ячейки вниз и вправо.
Если листов очень много, то проще будет разложить их все подряд и использовать немного другую формулу:
=СУММ('2001 год:2003 год'!B3)
Фактически - это суммирование всех ячеек B3 на листах с 2001 по 2003, т.е. количество листов, по сути, может быть любым. Также в будущем возможно поместить между стартовым и финальным листами дополнительные листы с данными, которые также станут автоматически учитываться при суммировании.
Способ 2. Если таблицы неодинаковые или в разных файлах
Если исходные таблицы не абсолютно идентичны, т.е. имеют разное количество строк, столбцов или повторяющиеся данные или находятся в разных файлах, то суммирование при помощи обычных формул придется делать для каждой ячейки персонально, что ужасно трудоемко. Лучше воспользоваться принципиально другим инструментом.
Рассмотрим следующий пример. Имеем три разных файла (Иван.xlsx, Рита.xlsx и Федор.xlsx) с тремя таблицами:
Хорошо заметно, что таблицы не одинаковы - у них различные размеры и смысловая начинка. Тем не менее их можно собрать в единый отчет меньше, чем за минуту. Единственным условием успешного объединения (консолидации) таблиц в подобном случае является совпадение заголовков столбцов и строк. Именно по первой строке и левому столбцу каждой таблицы Excel будет искать совпадения и суммировать наши данные.
Для того, чтобы выполнить такую консолидацию:
- Заранее откройте исходные файлы
- Создайте новую пустую книгу (Ctrl + N)
- Установите в нее активную ячейку и выберите на вкладке (в меню) Данные - Консолидация(Data - Consolidate) . Откроется соответствующее окно:
Обратите внимание, что в данном случае Excel запоминает, фактически, положение файла на диске, прописывая для каждого из них полный путь (диск-папка-файл-лист-адреса ячеек). Чтобы суммирование происходило с учетом заголовков столбцов и строк необходимо включить оба флажка Использовать в качестве имен (Use labels) . Флаг Создавать связи с исходными данными (Create links to source data) позволит в будущем (при изменении данных в исходных файлах) производить пересчет консолидированного отчета автоматически.
После нажатия на ОК видим результат нашей работы:
Наши файлы просуммировались по совпадениям названий из крайнего левого столбца и верхней строки выделенных областей в каждом файле. Причем, если развернуть группы (значками плюс слева от таблицы), то можно увидеть из какого именно файла какие данные попали в отчет и ссылки на исходные файлы:
Читайте также: