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

Содержание

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

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

При работе с однотипными данными, размещенными в разных таблицах, листах или даже книгах, для удобства восприятия лучше собрать информацию воедино. В Microsoft Excel с этой задачей можно справиться с помощью специального инструмента, который называется «Консолидация». Он предоставляет возможность собрать разрозненные данные в одну таблицу. Давайте узнаем, как это делается.

Скачать последнюю версию Excel

Условия для выполнения процедуры консолидации

Естественно, что не все таблицы можно консолидировать в одну, а только те, которые соответствуют определенным условиям:

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

Создание консолидированной таблицы

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

  1. Открываем отдельный лист для консолидированной таблицы.
  2. На открывшемся листе отмечаем ячейку, которая будет являться верхней левой ячейкой новой таблицы.
  3. Находясь во вкладке «Данные» кликаем по кнопке «Консолидация», которая расположена на ленте в блоке инструментов «Работа с данными».
  4. Открывается окно настройки консолидации данных.

    В поле «Функция» требуется установить, какое действие с ячейками будет выполняться при совпадении строк и столбцов. Это могут быть следующие действия:

    • сумма;
    • количество;
    • среднее;
    • максимум;
    • минимум;
    • произведение;
    • количество чисел;
    • смещенное отклонение;
    • несмещенное отклонение;
    • смещенная дисперсия;
    • несмещенная дисперсия.

    В большинстве случаев используется функция «Сумма».

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

    Как видим, после этого диапазон добавляется в список.

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

    Если же нужный диапазон размещен в другой книге (файле), то сразу жмем на кнопку «Обзор…», выбираем файл на жестком диске или съемном носителе, а уже потом указанным выше способом выделяем диапазон ячеек в этом файле. Естественно, файл должен быть открыт.

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

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

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

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

    Когда все настройки выполнены, жмем на кнопку «OK».

  9. Консолидированный отчет готов. Как видим, данные его сгруппированы. Чтобы посмотреть информацию внутри каждой группы, кликаем на плюсик слева от таблицы.

    Теперь содержимое группы доступно для просмотра. Аналогичным способом можно раскрыть и любую другую группу.

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

Помогла ли вам эта статья?

ДА НЕТ

Источник: http://lumpics.ru/data-consolidation-in-excel/

Функция консолидация в excel примеры

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

​Смотрите также​ не одинаковы -​ вводим формулу со​ Используется инструмент «Сводная​ «Консолидация» заполнит столько​ создаст ссылки на​Консолидация​Consolidate​ в будущем Вы​(Создавать связи с​ к списку диапазонов​​Консолидированные данные​​ консолидировать, должны иметь​ссылка​ из одного шаблона.​ группу.​ этом же файле,​

​При работе с однотипными​ у них различные​

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

​ макетами, но одинаковыми​​ файлах (книгах). Делается​ кнопку расположенную справа​«Консолидация»​ и строк.

Именно​ объединяемые данные находятся​ «тахта» занимает шестую​ «Консолидация» при работе​ Антон Андронов​ (если все в​Create Links to Source​ и охватывающие различные​ группировки, которые можно​ что Вам нужно​ лист содержит информацию​ данные из листов​ в диалоговое окно​ метками данных.​ это относительно просто​ от поля, куда​. Он предоставляет возможность​

​ по первой строке​ в разных ячейках​ строку, то в​ с данными. Выберем​В этой статье приводится​ одной книге) или​ Data​ периоды времени, Вы​ использовать, чтобы отображать​ делать – это​ по одному месяцу​ “Продажи” (в ячейке​Консолидация​Консолидация данных по категории​ и быстро.​

​ был внесен адрес​ собрать разрозненные данные​ и левому столбцу​ на разных листах.​ диапазоне 2, 3​ «Сумму» (значения в​ перечень контрольных заданий​ внешние ссылки (если​(Создавать связи с​ можете консолидировать их​ и скрывать данные.​ щелкать по ярлычку​ и если информация​ B4), “Кадры” (в​.​ похоже на создание​

​ Максим Тютюшев​​ ячеек.​ в одну таблицу.​ каждой таблицы Excel​ Например, в ячейке​ и 4 это​ исходных диапазонах будут​ по теме Консолидация.​ книг несколько), чтобы​ исходными данными), то​ при помощи этого​Если был выбран пункт​ очередного листа, и​

Способов консолидации данных

​ на всех листах​ ячейке F5) и​Если лист, содержащий​

​ сводной таблицы. Со​​Примечание:​Вернувшись в окно настроек​ Давайте узнаем, как​ будет искать совпадения​ В5 на листе​ значение должно занимать​ суммироваться).​ За основу взяты​ объединить данные.​ Вы не сможете​

​ инструмента.​​Create links to source​ диапазон будет выбран​ организована единообразно, то​ “Маркетинг” (в ячейке​ данные, которые необходимо​ сводной таблицей тем​ Мы стараемся как можно​ консолидации, чтобы добавить​ это делается.​ и суммировать наши​ «Магазин», в ячейке​

  • ​ тоже шестую строку.​Переходим к заполнению следующего​ реальные Контрольные задания,​Как видите, листы не​ обновить консолидированные данные,​Для этого потребуется выполнить​ data​ автоматически. Так что​ с помощью инструмента​ B9) в ячейке​ объединить в другой​

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

Как для консолидации

​ поля – «Ссылка».​ которые  предлагается решить в​ идентичны. Тем не​ нажав на вкладке​

  1. ​ те же самые​(Создавать связи с​ эта задача гораздо​Консолидация​

    • ​ A2 основного листа,​ книге, нажмите кнопку​ можете легко переупорядочение​ актуальными справочными материалами​ ячейки в список​ Excel​Для того, чтобы выполнить​ «Склад» и т.п.​ объединения данных, т.к.​Ставим в поле курсор.​ ВУЗах, техникумах, школах​ менее, прелесть инструмента​
    • ​Data​ действия, как мы​ исходными данными), то​ проще, чем кажется​Вы можете обобщить​ введите следующее:​Обзор​ категории. Если вам​ на вашем языке.​
    • ​ диапазонов, жмем на​Естественно, что не все​
  2. ​ такую консолидацию:​Скачать все примеры консолидации​ исходные диапазоны идеальны​ Открываем лист «1​ и других обучающих​

    ​ «​​(Данные) команду​ делали только что:​ полученные данные ссылаются​ на первый взгляд.​ данные на одном​Совет:​

  3. ​, чтобы найти необходимую​​ нужна более гибкие​​ Эта страница переведена​​ кнопку​​ таблицы можно консолидировать​​Заранее откройте исходные файлы​​ данных в Excel​

  4. ​ для консолидации. Объединим​​ квартал». Выделяем таблицу​​ заведениях по теме​Консолидация​Consolidate​ выберите пустой лист​ на содержащие их​

    ​Диапазоны для сведения, находящиеся​ листе за весь​ Чтобы указать ссылку на​

  5. ​ книгу. После поиска​

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

    ​ вместе с шапкой.​ MS EXCEL. Желающие​» в том, что​(Консолидация), не удалив​​ или добавьте новый,​​ исходные ячейки. Кликнув​ на разных листах,​​ год.​​ ячейку — например,​
    ​ и нажмите кнопку​ можно создать сводную​ текст может содержать​.

    ​ только те, которые​​ (Ctrl + N)​ автоматическое вычисление формул,​ в разных книгах.​​ В поле «Ссылка»​​ могут самостоятельно потренироваться и​​ он может с​​ предварительно старые результаты.

    ​ нажав​ по ячейке с​ не обязательно должны​Для этого откройте пустой​ продажи! B4 —​

    ​ОК​ таблицу .​ неточности и грамматические​

  6. ​Как видим, после этого​​ соответствуют определенным условиям:​​Установите в нее активную​​ то при изменении​​Созданы книги: Магазин 1,​ появится первый диапазон​ воспользоваться решением приведенном​

  7. ​ легкостью складывать, подсчитывать,​​ Причина в том,​Insert Sheet​ данными (не по​ быть одинакового размера.​ лист в рабочей​​ в формуле, не​, Excel в поле​​Примечание:​ ошибки. Для нас​ диапазон добавляется в​

    ​столбцы во всех таблицах​​ ячейку и выберите​

    • ​ данных в исходных​ Магазин 2 и​ для консолидации. Нажимаем​ в файле примера.​
    • ​ усреднять и т.д.​ что в таком​(Вставить лист) на​ ячейке с суммой),​ Число строк и/или​ книге Excel (добавьте​ вводя, введите формулу​ссылка​ В этой статье были​ важно, чтобы эта​ список.​ должны иметь одинаковое​ на вкладке (в​ диапазонах объединенная таблица​ Магазин 3. Структура​ кнопку «Добавить»​

Источник: http://my-excel.ru/formuly/funkcija-konsolidacija-v-excel-primery.html

Обобщаем с умом: внешние ссылки и консолидация в Excel

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

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

Консолидация в Excel — это трудоемкая, кропотливая и неприятная работа, но в этой статье я расскажу, как ее облегчить.

Для сбора информации из разных источников есть два подхода: внешние ссылки на ячейки, или инструмент «Консолидация».

Внешние ссылки в Excel

Думаю, каждый из Вас знает, что внешняя ссылка в Эксель – это ссылка на ячейку, находящуюся в другой книге. И самый распространенный способ собрать данные из разных источников в одну – как раз внешние ссылки.

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

Существует много способов сослаться на ячейку с другого листа, или книги. Я расскажу Вам самый простой:

  1. Откройте все файлы – свод и исходники
  2. Кликните по ячейке, в которую нужно вставить ссылку, и введите знак равно (как вы это делаете, когда создаете формулы).
  3. Выберите файл с исходным значением и кликните на этом значении. В строке формул появится ссылка на это значение.
  4. Если к этому значению нужно прибавить другие из остальных источников – допишите «+» и повторяйте п.3-4 до полного заполнения.
  5. Когда все ссылки на ячейки сделаны, нажмите Enter, будет просчитан сводный результат.

Внешняя ссылка имеет специфический внешний вид: имя файла в квадратных скобках, имя листа, восклицательный знак, координаты ячейки:

Когда Вы закроете исходные файлы, к ссылке слева добавится еще и адрес файла-исходника:

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

Не перемещайте файлы-исходники, т.к. это разорвет связи, и внешние ссылки работать больше не будут.

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

Управление внешними ссылками

Чтобы управлять ссылками на источники в других книгах, нажмите на ленте Данные – Подключения – Изменить связи. В открывшемся окне отобразится весь список подключенных файлов и кнопки-команды:

:

  • Обновить – принудительное обновление всех ссылок
  • Изменить – указать новую книгу Excel, в которой располагаются подключенные данные
  • Открыть – открывает документ-источник
  • Разорвать связь – удаляет внешние ссылки из этой связи, заменяет их на значение
  • Состояние – обновляет статус доступности файлов с данными
  • Запрос на обновление связей – укажите программе, нужно ли обновлять данные при открытии файла

Если у Вас большой файл с несколькими листами, может понадобиться много времени на поиски внешних ссылок. Так как быстро найти внешние ссылки в документе Excel? Предложу рецепт.

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

Нажмите Ctrl+F для открытия окна поиска, в поле «Найти» введите квадратную скобку и нажмите «Найти все». В результатах поиска точно будут все внешние ссылки.

Применение внешних ссылок – самый простой и очевидный способ собрать данные из нескольких таблиц в одну. Но если данных достаточно много, этот метод неприменим. Тогда пользуемся инструментом «Консолидация».

Консолидация данных в одну таблицу

Этот инструмент упрощает сведение информации в одну таблицу данных, это удачный способ автоматизации процесса.

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

Давайте выполнять. Открываем все файлы-исходники и свод. Активируем «Свод». Ищем на ленте Данные – Работа с данными – Консолидация. Откроется окно подключения внешних данных.

Теперь пошагово выполняем:

  1. В поле «Функция» выбираем «Сумма» (или что-то другое, если нужно)
  2. В поле «Ссылка» указываем ссылку на данные в первом файле-источнике (вместе с шапкой). Лучше всего, указать столбцы полностью, например A:D. Тогда не нужно будет переопределять диапазон при добавлении новых данных.
  3. Нажимаем «Добавить», ссылка появляется в поле «Список диапазонов»
  4. Повторяйте п. 2-3, пока не добавите ссылки на все файлы-исходники
  1. Установите галочки «Подписи верхней строки» и «Значения левого столбца», чтобы Эксель сам подобрал заголовки шапки и категории в строках
  2. Установите галку «Создавать связи с исходными данными», тогда консолидация будет сделана с помощью ссылок на ячейки. Иначе – будут просто вставлены итоговые значения.
  3. Нажмите Ок для завершения настройки консолидации.

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

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

Такой вот простой инструмент, решающий одну из самых противных задач в Эксель. Я предпочитаю применять именно его, ручная работа по внесению ссылок – не для меня. Рекомендую и Вам пользоваться. Хотя, более удачным решением будет применение сводной таблицы (когда это возможно), или создание собственной VBA-программы с детальными настройками сведения.

На этом оставлю Вас экспериментировать с Консолидацией, и приступаю к написанию поста о защите данных в Эксель. Читайте, эта информация сейчас, как никогда актуальна. До встречи!

, добавить в закладки или распечатать статью

Источник: http://officelegko.com/2017/02/02/obobshhaem-s-umom-vneshnie-ssyilki-i-konsolidatsiya-v-excel/

Как в Excel использовать консолидацию для объединения данных из разных книг — Трюки и приемы в Microsoft Excel

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

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

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

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

Вот два способа объединения данных, применяемых в Excel:

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

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

Объединение по позициям

Если книги, с которыми вы работаете, имеют одинаковую структуру построения, объединение по позициям — это наиболее правильный способ объединения данных. Например, обратите внимание на три созданные книги — Баланс1 Баланс2 и Баланс3 на рис. 3.7.

Рис. 3.7. Исходные книги для объединения по позициям

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

Рис. 3.8. Книга-шаблон для будущего объединения

Итак, данное объединение следует начать с создания новой книги с таким же дизайном и расположением элементов (см. рис. 3.8).

Вы можете сделать это, просто сохранив под новым именем любую из книг для объединения, удалив предварительно все данные (при этом формулы для расчета всех сумм и балансов удалять не следует). В данном примере мы объединяем данные из трех книг.

Диапазоны значений для продаж будут выглядеть следующим образом:
[Баланс1.хlsx]Лист1!B3:M5[Баланс2.хlsx]Лист1!B3:M5[Баланс3.хlsx]Лист1!B3:M5
Имея это в виду, выполните следующие шаги для объединения данным способом:

  1. Выберите верхний левый угол диапазона, куда будут занесены данные. В данном примере (см. рис. 3.8) это будет ячейка B3.
  2. Перейдите на вкладку Данные ленты инструментов Excel, затем в группе Работа с данными нажмите кнопку Консолидация. В результате на экране появится диалоговое окно Консолидация — см. рис. 3.9.

    Рис. 3.9. Окно «Консолидация»

    Вверху окна вы видите раскрывающееся меню с выбором функции для работы. В нашем примере необходимо использовать функцию СУММА, однако обратите внимание, что также вы можете вычислять средние и максимальные значения и многое другое. В поле Ссылка вам необходимо ввести путь к книге с диапазоном. Вот способы это сделать:

    • Ввести диапазон вручную. Если данные находятся в другой книге, убедитесь, что вы включили сюда название книги, заключенное в квадратные скобки. Если книга находится в другом каталоге или на другом диске, обязательно также следует ввести полный путь.
    • Если книга открыта, переключитесь на нее и затем мышью выделите необходимый диапазон.
    • Если книга не открыта, используйте кнопку Обзор, выберите файл и затем допишите имя листа и необходимый диапазон.
  3. Нажмите на кнопку Добавить. Excel занесет введенные вами данные в Список диапазонов (см. рис. 3.9).
  4. Повторите шаги 4-5, добавляя все необходимые вам книги и данные.
  5. Если вы хотите, чтобы данные в объединенной книге изменялись по мере изменений в книгах-источниках, поставьте галочку возле пункта Создавать связи с исходными данными.
  6. Нажмите ОК. Excel запросит необходимые данные и занесет их в объединенную книгу (см. рис. 3.10).

Рис. 3.10. Объединенная по позициям книга

Если вы не создадите связь с исходными данными. Excel просто единовременно внесет данные из книг. При создании же связей произойдет следующее:

  1. Добавятся связи ко всем ячейкам.
  2. Объединение данных будет происходить с помощью функции СУММ().
  3. Произойдет скрытие ячеек со связями, как вы можете видеть на рис. 3.10.

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

Объединение по категориям

Если ваши рабочие книги содержат неодинаковую структуру (например, если разные магазины продают разные группы товаров), объединение по диапазонам даст неверные вычисления. В этом случае необходимо использовать объединение по категориям. На рис. 3.11 вы видите пример трех книг с разными категориями.

Рис. 3.11. Исходные книги для объединения по категориям

Как вы можете видеть, в Баланс1 находится информация от какого-то отдела по продаже Дисков и Книг, в Баланс2 — Мороженого, Книг и Кассет, а в Баланс3 — Дисков, Мороженого, Кассет и Приборов. Далее выполните следующие операции:

  1. Создайте пустой документ или сделайте небольшую заготовку. При этом задавать месяцы или названия товара необязательно.
  2. Перейдите на вкладку Данные ленты инструментов Excel, затем в группе Работа с данными нажмите кнопку Консолидация.
  3. Вы увидите окно Консолидация — см. выше рис. 3.8. Выберите необходимую функцию для объединения (в нашем примере — СУММА).
  4. Далее введите, как это было показано выше, необходимые диапазоны из документов. При этом убедитесь, что вы включаете в диапазон названия товаров и заголовки (в данном примере это названия месяцев).
  5. Если вы хотите, чтобы Excel использовал данные из левой части строк для объединения (как, например, это необходимо в нашем рассматриваемом примере), выберите галочку Значения левого столбца. В случае необходимости использования заголовков используйте галочку Подписи верхней строки.
  6. Нажмите ОК. Полученный результат вы можете видеть на рис. 3.12 Как и раньше, раскрывая категории, вы сможете увидеть информацию по каждой книге в отдельности.

Рис. 3.12. Результат объединения по категориям

Источник: http://excelexpert.ru/kak-v-excel-ispolzovat-konsolidaciyu-dlya-obedineniya-dannyx-iz-raznyx-knig

Консолидированная таблица в excel как сделать

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

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

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

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

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

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

  • макеты всех таблиц были одинаковыми;
  • названия столбцов – идентичными (допускается перестановка колонок);
  • нет пустых строк и столбцов.

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

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

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

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

Открывается диалоговое окно вида:

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

Переходим к заполнению следующего поля – «Ссылка».

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

Открываем поочередно второй, третий и четвертый квартал – выделяем диапазоны данных. Жмем «Добавить».

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

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

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

Для автоматического обновления объединенного отчета при внесении новых данных в исходные таблицы – напротив «создавать связи с исходными данными».

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

Для выхода из меню «Консолидации» и создания сводной таблицы нажимаем ОК.

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

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

Программа Microsoft Excel позволяет выполнять разные виды консолидации данных:

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

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

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

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

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

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

Консолидированная таблица:

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

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

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

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

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

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

Копируем формулу на весь столбец:

Консолидация данных с помощью формул удобна, когда объединяемые данные находятся в разных ячейках на разных листах. Например, в ячейке В5 на листе «Магазин», в ячейке Е8 на листе «Склад» и т.п.

Скачать все примеры консолидации данных в Excel

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

При работе с однотипными данными, размещенными в разных таблицах, листах или даже книгах, для удобства восприятия лучше собрать информацию воедино. В Microsoft Excel с этой задачей можно справиться с помощью специального инструмента, который называется «Консолидация». Он предоставляет возможность собрать разрозненные данные в одну таблицу. Давайте узнаем, как это делается.

Консолидация данных в Excel. Пример консолидации бюджетных заявок

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

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

Используйте пошаговые руководства:

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

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

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

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

Не путать с консолидацией в МСФО! В Excel это правильнее было бы назвать агрегированием, потому что в классике консолидация предполагает элиминирование внутренних оборотов.

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

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

Мы можем консолидировать данные из «смеси» файлов Excel: стандартных, с расширением .XLSX, с поддержкой макросов .XLSM, бинарных .XLSB и старых файлов с расширением XLS.
При выполнении консолидации в Excel необходимо учитывать требование к консолидируемым таблицам, а именно: первый столбец и первая строка должны содержать подписи данных, все остальное – числовые значения.

Общие шаги по консолидации данных в Excel

Шаг №1. Открыть все файлы, которые требуется консолидировать (если требуемые данные находятся в разных файлах), если все в одном файле, то, соответственно, других файлов открывать не нужно.

Шаг №2. Перейти в файл, в который «собираются» данные и выполнить следующие действия:
вкладка ДАННЫЕ → Консолидация

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

Шаг №4. Нажать «Ok» и получить консолидированную таблицу.

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

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

Второй файл, соответственно, расходы на управление.

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

  • В бюджетах разное количество строк. И это нормально, главное, при выделении таблиц указать их все.
  • Бюджеты содержат три методологически одинаковых статьи: канцтовары, оплата труда, амортизация. Ошибка в том, что в бюджете административных расходов написано «зарплата». Дальше мы увидим, что всё должно называться одинаково.
  • В шапке тоже не все верно: во-первых, вместо «фев» в административных расходах указано полное наименование месяца, во-вторых, «апр» в бюджете коммерческих расходов идет с пробелом на конце.
  • В административных расходах амортизация за июль стоит в текстовом формате.

Проводим консолидацию данных в Excel. Для этого встаем на ячейку в файле, куда мы собираем таблицы.
Идем на вкладку ДАННЫЕ → Консолидация. Функцию консолидации оставляем «Сумма» (если вам не нужно что-нибудь другое). И мышкой указываем первую из таблиц, значения которой нам требуется собрать – Бюджет административных расходов.

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

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

Выпадет «просто» диапазон чисел – без подписей. Одна ячейка в текстовом формате. Наверное, вы уже догадались, что значат эти галочки, давайте их поставим. Теперь это будет выглядеть так:

Кстати, иногда необходимо обновить отчет. Как это сделать? Очень просто: нужно встать на первую ячейку, откуда он начинается (в нашем случае – это A3). Пройти: ДАННЫЕ → Консолидация → Ok (список диапазонов уже не исчезает, снова его создавать не нужно).

Подписи появились. Всё здорово, НО: все текстовые отличия задвоились! Исправим исходные файлы и повторим все действия: Данные → Консолидация → Ставим галочку еще и на «Создавать связи…» → Ok.

Все исправилось. А благодаря последней галочке, слева появилась группировка данных. А в области значений появились ФОРМУЛЫ, ссылающиеся на лист с исходными данными.
 

Так, легко и быстро мы можем «стягивать» информацию из разных файлов и разных диапазонов в один. Если не ставить галочку «Создавать связи…», то данные нужно обновлять с помощью повторного выполнения консолидирующих шагов, но уже без указания диапазонов: ДАННЫЕ → Консолидация → Ok. Перед этим важно встать на начало диапазона, иначе данные добавятся туда, где у вас стоит активная ячейка.

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

Источник: https://fd.ru/articles/50220-konsolidatsiya-dannyh-v-excel-byudjetnyh-zayavok

Поделиться:
Нет комментариев

    Добавить комментарий

    Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.