Можно ли связать результат консолидации с источниками

Обновлено: 04.05.2024

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

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

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

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

Основные способы консолидации в программе Excel

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

В первом варианте данные в исходных областях находятся в том же порядке, в котором применяются идентичные метки. Выполните сведение по положению, чтобы объединить данные из 3-4 листков, которые базируются на одном шаблоне, к примеру, финансовая отчетность подходит для проверки этого способа.
Во втором варианте: данные располагаются в произвольном порядке, но имеют идентичные метки. Выполните консолидацию по категории, чтобы объединить данные из нескольких листков с различными макетами, но идентичными метками данных.

Важно! Этот способ имеет много общего с формированием сводной таблицы. Однако в сводной таблице можно реорганизовывать категории.

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

Пошаговая инструкция по выполнению консолидации в Excel

Далее будет рассмотрен самый простой и быстрый способ консолидации.
Итак, как объединить несколько таблиц:

Сначала необходимо создать новый лист, после чего программное обеспечение автоматическим образом добавит его с правой стороны. Если необходимо, можно перетащить лист в другое место (к примеру, в конец перечня) посредством левой кнопки мышки.
Добавленный лист встаньте в ячейку, с которой собираетесь работать. Потом перейдите во вкладку «Данные», найдите раздел «Работа с данными», нажмите на пункт под названием «Консолидация».
На мониторе появится маленькое окно с настройками.
Далее нужно будет выбрать подходящую функцию для объединения данных.
После выбора функции перейдите к полю «Ссылка», щелкнув мышкой внутри него. Здесь вам необходимо будет поочередно выбрать диапазон ячеек. Для этого сначала переключитесь на лист с первой табличкой.
Потом выделите табличку вместе с шапкой. Убедитесь, что все сделали правильно, потом кликните на значок «Добавить». Кстати, обновить/сменить координаты можно и собственноручно посредством клавиатуры, но это неудобно.
Для выбора диапазона из нового документа сначала откройте его в Эксель. После этого запустите процесс объединения в первой книжке и переключитесь на вторую, выберите в ней подходящий лист, а после этого выделите определенную часть ячеек.
В итоге в «Перечне диапазонов» сформируется первая запись.
Вернитесь в поле «Ссылка», уберите все сведения, которые в нем содержатся, потом добавьте в перечень диапазонов координаты остальных табличек.
Поставьте галочки напротив следующих функций: «Подписи верхней строки», «Значения левого столбика», «Сформировать связи с исходными данными».
После нажмите «OK».
Эксель выполнит процедуру и создаст новый документ согласно установленным параметрам и выбранным функциям.

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

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

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

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

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

Несвязанная консолидация

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

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

Порядок выполнения несвязанной консолидации

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

Связанная консолидация

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

Порядок выполнения связанной консолидации практически такой же, как и при несвязанной консолидации. Разница в том, что надо поставить еще одну “галочку” в пункте Создавать связи с исходными данными:

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

Работаем с несколькими наборами данных

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

Для этого откройте пустой лист в рабочей книге Excel (добавьте новый, если необходимо) и кликните в нём по любой ячейке. На вкладке Data (Данные) нажмите Consolidate (Консолидация), чтобы открылось диалоговое окно Consolidate (Консолидация). Выберите функцию для анализа данных и ссылки на диапазоны, которые нужно свести. В нашем случае мы хотим просуммировать значения, поэтому в поле Function (Функция) выберем Sum (Сумма).

Вы можете выбрать любую из 11 операций: Sum (Сумма), Count (Количество), Average (Среднее), Max (Максимум), Min (Минимум), Product (Произведение), Count Numbers (Количество чисел), StdDev (Смещенное отклонение), StdDevp (Несмещенное отклонение), Var (Смещенная дисперсия) и Varp (Несмещенная дисперсия).

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

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

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

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

Полезный совет

Вы можете присвоить диапазонам имена, прежде чем начинать процесс консолидации. Для этого выделите диапазон и задайте ему имя в поле Имя слева от строки формул. Когда Вы дадите имена всем диапазонам, то при настройке консолидации поставьте курсор в поле Reference (Ссылка), нажмите F3 и в открывшемся окне Paste Name (Вставка имени) выберите нужный диапазон. Таким образом, Вы можете дать каждому диапазону понятное имя, и тогда позже не придётся вспоминать, что за данные скрываются на листе Лист1 в ячейках A3:F40.

Как видите, в области All References (Список диапазонов) листы располагаются в алфавитном порядке. Прежде чем продолжить, убедитесь, что указали ссылки на все требуемые диапазоны. Отметьте галочкой параметры Use labels in (Использовать в качестве имен): Top Row (Подписи верхней строки) и Left Column (Значения левого столбца). Поставьте галочку также для Create links to source data (Создавать связи с исходными данными) и нажмите ОК.

Консолидированные данные

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

Если был выбран пункт Create links to source data (Создавать связи с исходными данными), то полученные данные ссылаются на содержащие их исходные ячейки. Кликнув по ячейке с данными (не по ячейке с суммой), Вы увидите ссылку на лист и ячейку, содержащую эти данные.

Если вы не отметили параметр Create links to source data (Создавать связи с исходными данными), то полученная консолидация – это просто обобщение данных без каких-либо подробностей, без группировки и содержащее только результаты суммирования.

Полезный совет

Так как эти данные содержат ссылки, Вы можете использовать инструмент Trace Precedents (Влияющие ячейки), чтобы перейти к исходной ячейке, содержащей данные. Для этого кликните по ячейке, содержащей интересующие Вас данные. Откройте вкладку Formulas (Формулы) и найдите кнопку Trace Precedents (Влияющие ячейки). Поскольку исходная ячейка находится на другом листе, наведите указатель мыши на появившуюся чёрную стрелку, чтобы указатель принял вид пустой белой стрелки. Дважды щелкните, чтобы открыть диалоговое окно Go To (Переход) – ссылка на ячейку будет указана в этом окне. Кликните по ссылке и далее нажмите ОК, чтобы перейти к нужному месту.

Форматируем данные

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

Разные рабочие книги

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

Для этого потребуется выполнить те же самые действия, как мы делали только что: выберите пустой лист или добавьте новый, нажав Insert Sheet (Вставить лист) на вкладке Insert (Вставка). Нажмите команду Consolidate (Консолидация). На этот раз вместо того, чтобы выбрать лист в текущей рабочей книге, нажмите кнопку Browse (Обзор), чтобы открыть другую рабочую книгу.

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

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

Если включить параметр Create Links to Source Data (Создавать связи с исходными данными), то, когда сведение будет выполнено, все изменения в исходных листах и рабочих книгах будут отображаться и в консолидированных данных. Второй столбец в обобщенных данных все также будет отображать название рабочей книги, а команда Trace Precedents (Влияющие ячейки) быстро перенесет Вас к ячейкам, связанным ссылкой, если соответствующая рабочая книга открыта, но не сработает, если она закрыта.

Обновляем консолидацию

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

На вкладке Data (Данные) нажмите Consolidate (Консолидация) – Вы увидите, что указанные ранее ссылки сохранились. Внесите изменения, добавив или удалив диапазоны, или изменив их размер, и нажмите ОК, чтобы создать консолидацию заново.

Предостережение!

Если Вы включаете параметр Create Links to Source Data (Создавать связи с исходными данными), то Вы не сможете обновить консолидированные данные, нажав на вкладке Data (Данные) команду Consolidate (Консолидация), не удалив предварительно старые результаты. Причина в том, что в таком случае вместо обновления будет сделана попытка вставить одну консолидацию в другую, что в результате приведет к полной чепухе. Вы можете обновить диапазоны, изменяя формулы вручную, но более целесообразно будет создать консолидацию заново.

Если же Вы не включили параметр Create Links to Source Data (Создавать связи с исходными данными), то Ваши сведенные данные представляют из себя просто обобщение, без каких-либо подробностей. В таком случае, чтобы обновить консолидацию, кликните по верхней ячейке, содержащей результаты, затем нажмите на вкладке Data (Данные) команду Consolidate (Консолидация), внесите все необходимые изменения и нажмите ОК.

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

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

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

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

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

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

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

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

Заключение

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

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

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

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

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

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

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

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

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

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Читайте также: