Bugsp.ru

Ваша компьютерная помощь
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

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

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

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

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

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

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

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

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

Если вы еще не сделали этого, для этого нужно сделать следующее:

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

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

Убедитесь, что каждый диапазон имеет одинаковый макет.

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

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

Нажмите кнопку>консолидировать (в группе Инструменты для работы с данными).

Читайте так же:
Как поменять пароль на роутере Ростелеком

Группа "Работа с данными" на вкладке "Данные"

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

Вот пример, в котором выбраны три диапазона:

Диалоговое окно "Консолидация данных"

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

Кнопка "Свернуть" в диалоговом окне "Консолидация данных"

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

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

Вот пример, в котором выбраны три диапазона:

Диалоговое окно "Консолидация данных"

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

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

Связи невозможно создать, если исходная и конечная области находятся на одном листе.

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

Нажмите кнопкуОК, Excel сгенерировать консолидацию. При желании можно применить форматирование. Форматирование требуется только один раз, если консолидация не будет повторно отформатна.

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

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

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

Введите формулу со ссылками на ячейки других листов, по одной на каждый лист. Например, чтобы консолидировать данные из листов «Продажи» (в ячейке B4), «Кадры» (в ячейке F5) и «Маркетинг» (в ячейке B9), в ячейке A2 основного листа, введите следующее:

Ссылка на несколько листов в формуле Excel

Совет: Чтобы ввести ссылку на ячейку, например «Продажи»! B4— в формуле, не вводя текст, введите формулу до нужной точки, а затем перейдите на вкладку и щелкните ячейку. Excel заполнит имя листа и адрес ячейки. ПРИМЕЧАНИЕ. Формулы в таких случаях могут быть непреднамерены, так как можно легко случайно выбрать неправильная ячейка. Ошибку также сложно обнаружить после ввода сложной формулы.

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

Читайте так же:
Как сделать обложку для актуального в Инстаграм

Введите формулу с трехмерной ссылкой, которая указывает на диапазон имен листов. Например, чтобы консолидировать данные в ячейках A2 от «Продажи» до «Маркетинг» включительно, в ячейку E5 нужно ввести следующую ячейку:

Объемная ссылка на листы в формуле Excel

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

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

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

Пример №1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте так же:
Телевизор не видит компьютер через HDMI

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

Пример №2

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

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

И для таких случаев пригодится Консолидация в Excel. Давайте сведем данные с помощью данной настройки на примере выгрузки интернет-магазина за 3 месяца в один отчет. Статистика по месяцам расположена на разных вкладках (сентябрь — ноябрь — декабрь).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Требования к таблицам
  • Выполняем консолидацию
  • Заключение
Читайте так же:
Сложение систем счисления в онлайн-калькуляторе

Требования к таблицам

Обращаем Ваше внимание на то, что воспользоваться “Консолидацией” получается не всегда. Чтобы это было возможно, исходные таблицы (могут быть расположены на одном листе или на разных листах/книгах) должны соответствовать следующим требованиям:

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

Выполняем консолидацию

В результате применения функции “Консолидация” формируется новая, так называемая, консолидированная таблица. Давайте пошагово разберем на практическом примере, как ее собрать из 3 таблиц, соответствующих требованиям, перечисленным выше. Итак, выполняем следующие действия:

  1. Жмем по значку создания нового листа, после чего программа автоматически добавит его справа от текущего и переключит нас на него. Подробная информация по данной операции представлена в нашей отдельной публикации – “Как добавить лист в Excel”.Добавление нового листа в ЭкселеПримечание: Можно переместить добавленный лист в удобное для нас место (например в конец списка) с помощью зажатой левой кнопки мыши, “зацепив” его за вкладку с названием. Также лист можно переименовать при желании (режим редактирования запускается двойным щелчком по имени, завершается нажатием Enter).
  2. В добавленном листе встаем в ячейку, с которой планируем вставить консолидированную таблицу (в нашем случае оставляем выбранную по умолчанию). Затем переходим во вкладку “Данные”, находим группу инструментов “Работа с данным”, где щелкаем по значку “Консолидация”.Запуск функции Консолидация в Экселе
  3. На экране отобразится небольшое окошко с настройками инструмента.Настройка параметров консолидации в Excel
  4. Здесь представлены следующие параметры:
    • Функция – кликнув по текущему варианту мы откроем список возможных действий, среди которых выбираем то, которое требуется выполнить для консолидируемых данных:
      • Сумма (выбрана по умолчанию; используется чаще всего, поэтому оставляем ее в качестве примера);
      • Количество;
      • Среднее;
      • Максимум;
      • Минимум;
      • Произведение;
      • Количество чисел;
      • Стандартное отклонение;
      • Несмещенное отклонение;
      • Дисперсия;
      • Несмещенная дисперсия.Выбор функции при консолидации данных в Экселе
  5. Переходим к полю “Ссылка”, щелкнув внутри него. Здесь мы поочередно выбираем диапазон ячеек исходных таблиц, которые должны быть обработаны. Для этого:
    • Переключаемся на лист с первой таблицей.

Заключение

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

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

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

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

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

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

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

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

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

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

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

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

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

consolidation2.png

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

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

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

consolidation3.png

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

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

consolidation5.png

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

голоса
Рейтинг статьи
Ссылка на основную публикацию