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

Содержание:

Консолидация данных в Excel
Что это такое
Встроенная отправка
Консолидация в Excel, данные и формулы
- - Простая консолидация
  - Консолидация в Excel. Специальный инструмент
Дополнительная информация об использовании диапазонов, добавлении и удалении ссылок
Консолидация данных по категориям
Задание исходных областей консолидируемых данных
Изменение итоговой таблицы консолидации данных
Консолидация данных в Excel
Для чего может использоваться консолидация данных
- Представителям каких профессий пригодится функция консолидации данных
Финансовая сфера
Консолидация (объединение) данных из нескольких таблиц в одну
- Способ 1. С помощью формул
- Способ 2. Если таблицы неодинаковые или в разных файлах
Создание консолидированной таблицы
Примеры консолидации данных в Excel
Консолидация в бизнесе
КАК БЫСТРО ПОЛУЧИТЬ СВОДНЫЕ ИТОГИ ПО НУЖНЫМ ПОЗИЦИЯМ

Консолидация данных в Excel

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

Разберем два наглядных примера.

Пример №1

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

Специальный отчет в Google Analytics

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

«Дубли» ключевых слов в статистике

Это связано с тем, что статистика выгружена из двух рекламных систем (Яндекс.Директ и Google Ads), и в Google Рекламе для ключевых слов используется модификатор широкого соответствия (знак «+»).

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

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

Суммирование данных вручную

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

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

Удаляем + в ключевых словах перед консолидацией

— переходим на соседний лист (так удобнее);

— выделяем ячейку, в которую хотим вставить данные;

— переходим в Данные — Консолидация

В открывшемся окне нас интересуют следующие настройки:

Функция – Сумма (поскольку хотим суммировать данные);
Ссылка – выбираем весь диапазон данных на соседнем листе;
Использовать в качестве имен – ставим галочки для подписи верхней строки и значения левого столбца.

Нажимаем ОК. Если все сделали верно, то на текущем листе получите результат в виде объединенных (консолидированных) данных:

Итоговая таблица после консолидации

Таблица наша также уменьшилась. До консолидации было 252 строки, после объединения данных их стало 212:

ДО и ПОСЛЕ консолидации

Видео консолидации примера №1:

Консолидация данных в Excel

Пример №2

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

Да, можно просто выгрузить данные из рекламных кабинетов Яндекс.Директ и Google Ads за общий период. Но что делать, когда данные по показам, кликам, расходам и конверсиям привязаны к бизнес-показателям, которых нет в интерфейсах, но которые завязаны на контекстной рекламе? А если в каждый из этих месяцев работали разные рекламные кампании (в один месяц – 10, в другой – 5 и т.д.)? Опять ручной процесс сведения данных.

Статистика по 3 месяцам на разных вкладках файла

Мы хотим свести данные в одну таблицу. Воспользуемся консолидацией.

В открывшемся окне нас интересуют следующие настройки:

Функция – Сумма (поскольку хотим суммировать данные);
Ссылка – выбираем весь диапазон данных на листах поочередно (с помощью кнопки Добавить);
Использовать в качестве имен – ставим галочки для подписи верхней строки и значения левого столбца.

Галочка Создавать связи с исходными данными позволит производить пересчет консолидированного отчета автоматически при изменении данных в исходных файлах (в нашем примере – это листы сентябрь, октябрь, ноябрь). Нажимаем ОК.

Если все сделали правильно, то на текущем листе получите результат в виде объединенных (консолидированных) данных с трех таблиц в одной:

Слева появятся новые значки:

Связи с исходными данными

1 – свернуть все связи с исходными данными;
2 – развернуть все связи с исходными данными.

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

Видео консолидации примера №2:

Консолидация данных в Excel

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

Понравился эксперимент с видео? Поставь 5.0 статье ->

Что это такое

Как сделать график в экселе

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

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

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

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

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

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

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

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

Встроенная отправка

Как вычитать в экселе

Если у вас еще старый добрый Excel 2003, то все просто. Открываете нужную книгу/лист и выбираете в меню Файл – Отправить – Сообщение (File – Send To – Mail Recipient). Откроется окошко, в котором можно выбрать один из двух вариантов отправки:

В первом случае текущая книга будет добавлена в сообщение как вложение, во втором – содержимое текущего листа попадет прямо в текст сообщения как текстовая таблица (без формул).

Кроме этого в меню Файл – Отправить (File – Send To) есть еще несколько более экзотических вариантов отправки:

Сообщение (для ознакомления) (Mail Recipient for Review) – отправляется вся книга целиком и при этом для нее включается отслеживание изменений, т.е. начинает явно фиксироваться – кто, когда и в каких ячейках какие изменения производил. Отобразить внесенные изменения потом можно в меню Сервис – Исправления – Выделить исправления (Tools – Track changes – Highlight changes)или на вкладке Рецензирование – Исправления (Reveiw – Track Changes) Выглядеть это будет примерно так: Цветные рамочки помечают изменения, внесенные в документ (для каждого пользователя – свой цвет). При наведении мыши всплывает похожее на примечание окошко с подробным описанием кто, что и когда изменил в этой ячейке. Весьма удобно для рецензирования документов, когда, например, вы правите отчет своих подчиненных или шеф правит ваш.
По маршруту (Routing Recipient) – сообщение, куда будет вложена ваша книга, отправится по цепочке получателей, каждый из которых автоматически будет пересылать его дальше, как эстафетную палочку. При желании можно задать, чтобы в конце цепочки сообщение вернулось к вам обратно. Можно включить режим отслеживания изменений, чтобы видеть правки, внесенный каждым человеком в цепочке.

В новых Excel 2007/2010 ситуация немного другая. В этих версиях для отправки книги по почте нужно выбрать кнопку Офис (Office Button) или вкладку Файл (File) и команду Отправить (Send). Далее, пользователю предлагается набор вариантов отправки:

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

Зато появилась полезная возможность отправлять в известном формате PDF и менее известном XPS (аналог PDF, но не требует Acrobat Reader для чтения – открывается прямо в Internet Explorer). Команду отправки книги для ознакомления можно вытащить как дополнительную кнопку на панель быстрого доступа через Файл – Параметры – Панель быстрого доступа – Все команды – Отправить на проверку (File – Options – Quick Access Toolbar – All Comands – Send for Review).

Консолидация в Excel, данные и формулы

Анализ данных эксель

Консолидация — это объединение, слияние двух или нескольких форм и/или данных. Используется, если нужно собрать и объединить данные из разных мест в одном. Для чего это может пригодиться? Проанализировать несколько таблиц с разных файлов/листов. Собрать данные в одну таблицу из разных источников. Далее рассмотрим подробнее, что такое консолидация в Excel.

Простая консолидация

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

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

Консолидация в Excel. Специальный инструмент

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

В открывшемся окне:

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

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

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

Значения левого столбца — значения сгруппируются по левому столбцу.

В итоге все данные сгруппируются по левому столбцу:

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

Важно!

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

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

Дополнительная информация об использовании диапазонов, добавлении и удалении ссылок

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

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

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

Консолидация данных по категориям

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

1. Выберите из раскрывающегося
списка «Функция» функцию, которую следует использовать для обработки
данных.

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

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

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

Примечание:

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

Задание исходных областей консолидируемых данных

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

При описании исходных областей следуйте следующим
рекомендациям.

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

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

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

¨ Бухгалтерия», введите
Бухгалтерия!Бюджет.

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

¨ ‘Дальний
Восток’!Продажи

¨ Если исходные области и
область назначения находятся в разных книгах разных каталогов диска,
используйте полный путь к файлу книги, имя книги, имя листа, а затем — имя или
ссылку на диапазон. Например, чтобы включить диапазон «Оборот» листа «Февраль»
в книге «Отдел продаж», которая находится в папке «Бюджет», введите:

‘Февраль’!Оборот

Примечание. Если диапазонам назначены уникальные, не
присвоенные автоматически имена, то в ссылке можно не указывать имена листов.
Например »!Продажи или »!Оборот в
предыдущих примерах.

Совет.

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

Изменение итоговой таблицы консолидации данных

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

конечная область

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

Примечание.

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

Консолидация данных в Excel

Разберем два наглядных примера.

Пример №1

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

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

— переходим на соседний лист (так удобнее);

— выделяем ячейку, в которую хотим вставить данные;

— переходим в Данные — Консолидация

В открывшемся окне нас интересуют следующие настройки:

Функция – Сумма (поскольку хотим суммировать данные);
Ссылка – выбираем весь диапазон данных на соседнем листе;
Использовать в качестве имен – ставим галочки для подписи верхней строки и значения левого столбца.

Таблица наша также уменьшилась. До консолидации было 252 строки, после объединения данных их стало 212:

Видео консолидации примера №1:

Пример №2

Мы хотим свести данные в одну таблицу. Воспользуемся консолидацией.

В открывшемся окне нас интересуют следующие настройки:

Функция – Сумма (поскольку хотим суммировать данные);
Ссылка – выбираем весь диапазон данных на листах поочередно (с помощью кнопки Добавить);
Использовать в качестве имен – ставим галочки для подписи верхней строки и значения левого столбца.

Слева появятся новые значки:

1 – свернуть все связи с исходными данными;
2 – развернуть все связи с исходными данными.

Видео консолидации примера №2:

Понравился эксперимент с видео? Поставь 5.0 статье ->

Для чего может использоваться консолидация данных

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

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

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

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

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

Финансовая сфера

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

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

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

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

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

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

Существуют различные способы проведения такой консолидации:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Флаг Создавать связи с исходными данными (Create links to source data) позволит в будущем (при изменении данных в исходных файлах) производить пересчет консолидированного отчета автоматически.

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

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

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

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

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

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

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

Открываем отдельный лист для консолидированной таблицы.

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

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

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

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

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

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

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

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

Консолидация в бизнесе

Развитие бизнеса зависит не только от имеющихся ресурсов, но и от того, насколько эффективно им руководят. Далеко не все, кто начинает бизнес, могут добиться успеха. Однако если удалось достигнуть значимых результатов, то дальше необходимо проводить расширение. Одним из способов является консолидация. В этом случае несколько фирм объединяются под общим руководством. Тогда образуется холдинг, в котором осуществляется общее управление.

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

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

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

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

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

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

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

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

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

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

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

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