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

При работе в Microsoft Office Excel могут быть созданы однотипные таблицы, в которых совпадает шапка, строки и настройки форматирования, но отличаются значения. Редактор обладает встроенной функцией объединения данных из нескольких мест в одно. Сегодня рассмотрим такой инструмент как консолидация в excel.

Что это такое

Консолидация данных представляет собой совмещение значений нескольких листов на отдельном рабочем листе excel. Рассмотрим структуру:

1. На Панели управления переходите во вкладку Данные и ищете одноименную кнопку.

1. Открывается новое диалоговое окно, которое содержит настройки инструмента.

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

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

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

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

Блок настроек подписи содержит три позиции значение каждой из них следующее:

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

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

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

Примеры использования

Задача: Посчитать количество товаров проданных за год по шести позициям и годовую выручку с их реализации на основе квартальных продаж.

Решение:

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

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

Важно! Таблица должна быть пустой снизу и справа от выбранной ячейки.

1. Нажимаете ОК и видите результат консолидации – годовое количество проданных товаров и объем выручки за год по каждой единице.

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

Данная пошаговая инструкция наглядно показывает принцип и методы работы с инструментом редактора excel.

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

• Результат идентичен

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

Стоит отметить, что принцип работы и местонахождение инструмента в excel 2010, 2013 и 2016 годов идентичны. Вариантов применения этого инструмента достаточно много.

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

Источник: https://mir-tehnologiy.ru/konsolidatsiya-v-excel/

Составим консолидированный ОДДС с помощью Excel

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

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

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

Методика консолидации оддс в табличном редакторе excel

Чтобы создать сводный по группе компаний (ГК) отчет о движении денежных средств в файле Excel, нужно учитывать несколько важных моментов.

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

Представим распространенный вариант структуры бизнеса группы компаний:

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

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

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

Во-вторых, в консолидированном ОДДС в обязательном порядке должны быть выделены внутригрупповые перемещения денег. Это нужно, чтобы видеть суммы внутренних оборотов, которые не влияют на общую сумму денежных средств ГК, иметь возможность проверить корректность отражения внутренних движений денег между компаниями группы.

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

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

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

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

Формат сводного ОДДС группы компаний также разрабатывается индивидуально — в зависимости от специфики бизнеса и потребностей пользователей управленческой информации.

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

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

Теоретические аспекты составления консолидированного оддс в excel

1. Всем известный табличный редактор Excel теоретически позволяет консолидировать данные о движении денежных средств из разных отчетов тремя основными способами:
2. • написание формул в ячейках (самый простой способ);
3. • использование механизма консолидации;
4. • создание сводной таблицы.

Использование функционала формул

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

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

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

Использование механизма консолидации данных

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

• Функция — для консолидации ОДДС здесь следует указать функцию «СУММА»;
• Ссылка — выбираем поочередно таблицы на каждом из листов с отчетами компаний группы;
• Список диапазонов — выбрав через ссылку нужную таблицу с отчетом, нажимаем кнопку «Добавить». В итоге получаем перечень всех таблиц с отчетами, которые хотим консолидировать в сводный ОДДС.

При использовании этого способа консолидации данных нужно соблюдать несколько условий:

•  таблицы всех объединяемых ОДДС компаний группы должны быть одинаковы;
• названия столбцов всех консолидируемых таблиц должны быть одинаковыми;
• в консолидируемых таблицах не должно быть пустых строк и столбцов.

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

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

Использование механизма сводных таблиц

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

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

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

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

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

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

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

Пример формирования консолидированного оддс группы компаний

• Перейдем от теории к практике и составим консолидированый ОДДС группы компаний, прописывая формулы в ячейках листа со сводными данными о движении денежных средств группы. Для примера возьмем группу компаний, которая состоит из следующих предприятий:
• • производственная компания (ПК) — производство продукции;
• • торговая компания (ТД) — реализация продукции;
• • управляющая компания (УК) — транспортные услуги и управление группой;
• • индивидуальный предприниматель (ИП) — юридические услуги.

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

• бухгалтерские службы компаний ежедневно формируют отчеты о движении денежных средств в учетных программах, сохраняют их в формате и отправляют экономисту управляющей компании;
• экономист управляющей компании ежедневно заполняет в отдельной книге лист с отчетом о движении денежных средств группы. По строкам отчета отражаются статьи движения денежных средств, по столбцам — компании группы и общая сумма по холдингу;
• на отдельном листе той же книги создаются еженедельные отчеты о движении денежных средств ГК. Показатели отчетов автоматически формируются на основе формул типа СУММ из ячеек ежедневных отчетов;
• на этом же листе книги автоматически формируется сводный ОДДС группы компаний, показатели которого на основе формул типа СУММ собираются из ячеек еженедельных отчетов;
• дополнительно к сводному отчету на основе ежедневных данных о движении денежных средств автоматически формируются расшифровки о внутригрупповом перемещении денег между компаниями группы.

А. А. Гребенников, финансовый директор ООО «Ипатовский пивзавод»

Источник: https://www.profiz.ru/peo/11_2019/konsolidirovannyj_ODDS/

Консолидация в Excel: пошаговая инструкция

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

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

Есть два способа, как сделать консолидацию данных в Excel. Во-первых, можно объединить данные по расположению. Этот способ подойдет, если необходимо соединить информацию из нескольких листов в программе Excel. Важно, чтобы таблицы на разных листах были идентичными: одинаковые заголовки в таблице и пр.

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

Получить бесплатный доступ

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

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

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

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

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

Скачать модели в Excel

 Чтобы объединить данные из всех таблиц, используем одну из самых простых функций – «СУММ». Сложим показатель по зарплате для одного отдельного сотрудника. Формулу условно можно представить так:

= Лист 1 + Лист 2 +…

Или для нашего примера: =январь!B2+февраль!B2+март!B2+апрель!B2+май!B2

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

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

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

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

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

В этом примере будем использовать именно специальную функцию, которую предлагает Excel – «Консолидация». В прошлом же примеры для объединения мы использовали формулу.

Шаг 1.

Шаг 2.

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

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

Шаг 3.

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

Консолидация данных с помощью формул в Excel

Еще один способ объединить данные – применить специальную функцию «СУММ». Логику того, как собрать информацию в одном месте, мы уже показали в первом примере, когда говорили о консолидации данных с нескольких листов. Напомним, что в общем виде формула выглядит так:

=СУММ(число 1; число 2;…)

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

Этот вариант объединения информации называется консолидация по категориям.

Источник: https://www.fd.ru/articles/159468-konsolidatsiya-v-excel-poshagovaya-instruktsiya

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

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

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

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

​ ссылками на исходные​ таблица» вместо «Консолидации​ строк и столбцов,​ исходные данные. Когда​» в Excel, чтобы​

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

​ но на другом​ данными, размещенными в​ размеры и смысловая​ ячейки каждого листа.​ данных».​ сколько нужно.​ исходные данные изменятся,​ объединить листы, расположенные​ увидите, что указанные​Одним из главных достоинств​ нажмите​ те же шаги,​Разные рабочие книги​

1. ​ появляются в диапазоне​Свернуть​
2. ​: данные в исходных​ в Эксель является​ листе, то жмем​ разных таблицах, листах​
3. ​ начинка. Тем не​​ В нашем примере​​Консолидация данных по расположению​​Переходим на вкладку «Данные».​​ консолидированные данные обновятся.​ в одной или​ ранее ссылки сохранились.​​ инструмента​​ОК​
4. ​ чтобы добавить второй​Обновляем консолидацию​

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

   • ​ (по позициям) подразумевает,​
   • ​ В группе «Работа​
   • ​Нажмите​
   • ​ нескольких книгах, в​
   • ​ Внесите изменения, добавив​
   • ​Консолидация​
   • ​.​
   • ​ и все остальные​
   • ​Представьте такую ситуацию: у​
   • ​Если данные для консолидации​
   • ​ панели и выбрать​

   ​ в одном и​ благодаря которому можно​​ справа от поля​​ для удобства восприятия​

5. ​ собрать в единый​​ В2. Формула для​​ что исходные таблицы​ с данными» нажимаем​OK​ один. На рисунках​ или удалив диапазоны,​является то, что​Когда нажмете​ наборы данных к​ Вас есть данные​ находятся в разных​ данные на листе.​
6. ​ том же порядке,​ собрать воедино информацию​ ввода данных.​ лучше собрать информацию​ отчет меньше, чем​ суммы: ='1 квартал'!B2+'2​ абсолютно идентичны. Одинаковые​ кнопку «Консолидация».​.​
7. ​ ниже представлены три​ или изменив их​ данные могут располагаться​ОК​ списку диапазонов.​ за двенадцать месяцев​​ ячейках разных листов:​​Щелкните лист, содержащий данные,​

   ​ но имеют одинаковые​ расположенную не только​Переходим на тот лист,​

   ​ воедино. В Microsoft​ за минуту. Единственным​ квартал'!B2+'3 квартал'!B2.​ не только названия​

   ​Открывается диалоговое окно вида:​Результат:​ разных документа.​ размер, и нажмите​ в разных рабочих​​, Excel обобщит все​​Если у Вас на​ ведения бизнеса, информация​Введите формулу со ссылками​ которые требуется консолидировать​ метки. Используйте этот​ в разных таблицах​ где расположена таблица,​ Excel с этой​ условием успешного объединения​

8. ​Копируем формулу на весь​ столбцов, но и​На картинке открыт выпадающий​

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

   ​ задачей можно справиться​ (консолидации) таблиц в​ столбец:​ наименования строк (см.​ список «Функций». Это​ командой сайта office-guru.ru​Если рабочие листы​​, чтобы создать консолидацию​ Вас есть несколько​​ новом листе.

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

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

9. ​Источник: http://www.excel-easy.com/examples/consolidate.html​ идентичны, гораздо проще​ заново.​ рабочих книг, содержащих​ левого края экрана​ расположенные на одинаковой​ листе рабочей книги​

   ​ на каждый лист).​Развернуть диалоговое окно​ листов с разными​ размещенную в других​ жмем опять на​

• ​ инструмента, который называется​ совпадение заголовков столбцов​ формул удобна, когда​ в диапазоне 1​ может выполнять команда​Перевел: Антон Андронов​ создать внутренние ссылки​Если Вы включаете параметр​ одинаково организованные данные​ Вы увидите инструменты​ позиции, то все,​ Excel. Если каждый​ Например, чтобы консолидировать​
• ​справа, чтобы вернуться​
• lumpics.ru

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

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

Именно​ объединяемые данные находятся​ «тахта» занимает шестую​ «Консолидация» при работе​ Антон Андронов​ (если все в​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. 1. ​ книгу. После поиска​
   2. ​ Консолидация по категории,​​ автоматически, поэтому ее​​«Добавить»​​ в одну, а​​Создайте новую пустую книгу​Если в книге включено​ таблицы, которые находятся​

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

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

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

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

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

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

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

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

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

Как быстро суммировать данные из разных таблиц

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

Имеется 3 таблицы (каждая — в отдельном файле) от трех разных менеджеров. Таблицы содержат данные о продажах товаров в разрезе артикулов и месяцев.

Исходная таблица

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

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

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

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

Команда на ленте

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

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

1) Функция консолидации. Это выпадающий список, в котором можно выбрать функцию, которая будет применена при объединении таблиц. Это может быть подсчет суммы, количества, среднего, минимального и максимального значения и т.д. Как видите, перечень довольно обширный, но все функции — математические. А значит работают только с числами, но не с текстом.

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

3) Обзор. Используйте эту кнопку, когда книга с диапазоном для объединения закрыта. Это избавит Вас от необходимости вводить адрес книги в строку Ссылка вручную, но указать адреса ячеек внутри книги придется всё же руками. Поэтому удобнее заранее открыть все нужные файлы.

4) Список диапазонов. Это общий список всех диапазонов/таблиц, которые Вы собираете консолидировать.

5) Добавить. Эта кнопка используется для переноса указанного в строке «Ссылка» диапазона в общий список диапазонов консолидации.

6) Удалить. Эта кнопка используется для удаления диапазона из общего списка диапазонов консолидации.

7) Использовать в качестве имен. Самая важная настройка.

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

Это называется консолидация по позиции. Если используете такой вариант — при указании диапазонов не указывайте заголовки строк и столбцов.

Если поставлена одна или обе галочки, то Excel будет ориентироваться на название строки/столбца.

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

Это называется консолидация по категории. При использовании этого варианта — указывайте вместе с диапазоном заголовки строк и столбцов.

Для работы инструмента консолидируемые заголовки должны совпадать. Обращайте внимание на наличие лишних пробелов в названиях строк/столбцов. Регистр при этом не важен (Январь и ЯНВАРЬ — одинаковые названия).

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

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

Для нашего примера настройки объединения будут выглядеть вот так:

Видеопример:

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

Итоговый результат

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

Таблица в «развернутом» виде

Это происходит для удобства работы с формулами, которые добавляются при создании обновляемой таблицы. Ячейки, спрятанные под «плюсик» имеют формулы вида =[Петров.xlsx]Лист1!$C$2, а итоговые ячейки подержат простые формулы суммы: =СУММ(D3:D5).

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

Необновляемая итоговая таблица

Весь процесс создания итоговой таблицы занял не более пары минут. Согласитесь, это довольно быстро.

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

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

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

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

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

• Поддержать наш проект и его дальнейшее развитие можно вот здесь.
• Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot
• С уважением, команда tDots.ru

Источник: https://zen.yandex.ru/media/id/59affb7afd96b11e8eadd771/5aa232b8fd96b174366402fc

Импорт и консолидация таблиц Excel через Power Query

Power Query умеет подключаться к разным источникам. Далее рассмотрим, как получить данные из книги Excel.

Таблицы Excel

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

Чтобы загрузить таблицу в редактор Power Query, достаточно выделить любую ее ячейку и нажать Данные → Получить и преобразовать данные → Из таблицы/диапазона.

Примечание. В вашей версии Excel расположение кнопок и их названия могут отличаться.

Если то же самое проделать с обычным диапазоном, то Excel вначале преобразует диапазон в таблицу Excel, а потом запустит Power Query.

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

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

Именованный диапазон Excel

Источником для Power Query может быть не только таблица Excel. Например, вы получили красивый отформатированный отчет и не хотите вносить в него изменения. Тогда нужно использовать именованный диапазон. Самый простой способ создать именованный диапазон – это выделить область на листе и ввести название в поле Имя.

Либо выполнить команду Формулы → Определенные имена → Присвоить имя. В Excel будет создан новый объект, к которому можно обращаться, например, в формулах. Диапазон виден в Диспетчере имен.

Здесь перечислены все именованные диапазоны, формулы и таблицы. Среди них есть и только что созданный Отчет.

Теперь можно стать на любую ячейку внутри именованного диапазона (или выбрать его из выпадающего списка в поле Имя) и вызвать ту же команду: Данные → Получить и преобразовать данные → Из таблицы/диапазона. Произойдет загрузка данных в Power Query.

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

Динамический именованный диапазон Excel

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

• Внести статичное имя в поле Имя на этот раз не получится. Поэтому заходим в Формулы → Определенные имена → Задать имя (или нажимаем Создать в Диспетчере имен), указываем название будущего динамического диапазона ДинамОтчет и внизу вместо ссылки записываем формулу:
• =$A$2:ИНДЕКС($B:$B;ПОИСКПОЗ(99^99;$B:$B))
• Ко всем ссылкам этой формулы Excel еще автоматически добавит название листа.

Смысл формулы следующий. Верхняя левая ячейка диапазона фиксируется ($A$2), а правая нижняя определяется формулой, которая возвращает адрес последней заполненной строки в столбце B.

Но не все так просто. Excel видит это имя лишь как формулу, а не диапазон. Как же его увидит Power Query? Делаем ход конем.

1. Создаем пустой запрос Power Query Данные → Получить и преобразовать данные → Получить данные → Из других источников → Пустой запрос. Открывается пустой запрос, где в строке формул нужно ввести:
2. = Excel.CurrentWorkbook()
3. После ввода формулы (нажатием Enter) Power Query обратится к текущей книге и выведет все объекты, среди которых есть и наш динамический диапазон ДинамОтчет.
4. Название запроса не подхватывается, поэтому придется изменить самостоятельно.
5. Чтобы извлечь содержимое объекта, в этой же строке правой кнопкой мыши кликаем по Table, далее выбираем Детализация.
6. Power Query разворачивает таблицу и даже делает некоторые шаги обработки: повышает заголовки и задает нужный формат для столбцов.
7. Теперь в запрос будут попадать новые строки, несмотря на то, что исходные данные не являются таблицей Excel.

Вот такие приемы импорта данных в Power Query из книги Excel. Самый распространенный из них – это импорт из таблицы Excel. Тем не менее, в случае необходимости можно прибегнуть к альтернативам, создав именованный или динамический именованный диапазон.

Консолидация данных из разных таблиц Excel

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

До появления Power Query это была довольно трудоемкая операция, особенно, если процесс требовал автоматизации. Хотя в эксель есть специальная команда Данные → Работа с данными → Консолидация, пользоваться ей не удобно.

Мне, по крайней мере. Появление Power Query в корне изменило представление о том, как нужно объединять таблицы.

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

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

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

• Процесс начинается с запуска пустого запроса: Данные → Получить и преобразовать данные → Создать запрос → Из других источников → Пустой запрос
• Затем в строке формул вводим знакомую команду
• = Excel.CurrentWorkbook()
• Power Query показывает все таблицы в текущей книге.
• Их нужно развернуть кнопкой с двумя стрелками в названии поля Content (на скриншоте ниже выделено красным кружком).

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

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

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

Визуально мы наблюдаем и месяц, и год. Но Power Query такое название воспринимает, как текст. Поэтому делаем следующее.

Удалим нижнее подчеркивание. Правой кнопкой мыши по названию столбца Name → Замена значений.

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

Power Query распознает дату и меняет формат колонки. Мы также переименовываем столбец на Период.

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

Но что-то пошло не так. Во-первых, внизу таблицы пустая строка; во-вторых, при выгрузке произошла одна ошибка. Обновим запрос (справа от названия запроса значок обновления).

Что-то еще больше пошло не так. Даты исчезли, снизу таблицы добавились новые строки, а количество ошибок уже 19. Спокойствие, только спокойствие! Дело вот в чем.

Помните, на первом шаге мы получили все таблицы из файла? Так ведь и выгруженная таблица – это тоже таблица! Получается, Power Query взял 3 исходных таблицы, обработал, выгрузил на лист Excel и на следующем круге видит уже 4 таблицы!

При повторном обновлении запрос захватывает их все, а т.к. таблица выхода имеет другую структуру, то возникают ошибки.

Короче, из запроса нужно исключить таблицу, которая получается на выходе (Запрос1). Есть разные подходы, самый простой – это добавить шаг фильтрации. Выделяем в правой панели первый шаг Источник, открываем фильтр в колонке с названиями, снимаем галку с таблицы Запрос1 → Ok.

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

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

1. А вот, как это выглядит при использовании Power Query.
2. Достаточно два раза нажать кнопку Обновить все (первый раз – для обновления запроса, второй – для сводной таблицы).
3. На добавление в отчет новых данных вместе с их обработкой потребовалось несколько секунд.
4. Вот за это мы так любим Power Query.

Поделиться в социальных сетях:

Источник: https://statanaliz.info/excel/power-query/import-i-konsolidatsiya-tablits-excel-cherez-power-query/