Консолидация данных в Excel. Практические примеры.
Сегодня мы рассмотрим вариант использования инструмента «консолидация данных» в Excel, и поговорим о других способах консолидирования.
Несколько вариантов консолидирования на практических примерах.
Всем добрый день! На текущем занятии мы наглядно изучим, как выполняется консолидация данных в Excel на практических примерах. Существует несколько способов выполнения консолидации. Важно отметить, что для применения любого из инструментов консолидации необходимо, чтобы исходные таблицы соответствовали ряду требований, которые зависят от способа консолидации.
- Исходные таблицы не должны содержать объединенных ячеек, особенно – объединенных ячеек в заголовке. Не допускаются пустые ячейки в заголовке. Не допускаются пустые ячейки в заголовки. Исключение – пример 1, приведенный ниже. Там заголовки столбцов не используются.
- Нельзя использовать повторяющиеся значения в заголовках столбцов таблицы.
- При консолидации нескольких таблиц их заголовки должны совпадать. Исключение – пример 1. Позиции же в строках могут различаться, опять же исключая вариа7нт, рассмотренный в первом примере.
- Если исходные таблицы находятся в разных файлах, то эти файлы либо нужно предварительно открыть, либо для нужных таблиц в исходных файлах должны быть указаны имена
- Наконец, для итоговых таблиц с консолидацией лучше использовать отдельные листы, так как располагать их на одном листе с исходными диапазонами нежелательно.
В прошлых материалах подробно рассматривался специальный инструмент, который так и называешься – консолидация. Однако он имеет важный недостаток – нет возможности отследить формулу, проверить и при необходимости исправить ссылки на исходные данные. Консолидация выводит общие итоги и не дает возможности вывести и проверить их для каждой из исходных таблиц. Поэтому сегодня мы рассмотрим не совсем стандартный вариант использования инструмента «консолидация», а также поговорим о других способах консолидирования данных из нескольких диапазонов.
Перейдем к практике и изучим несколько вариантов консолидирования на практических примерах. Сегодня мы рассмотрим, как настраивается и выполняется консолидация данных в Excel в различных ситуациях, в чем преимущества и недостатки каждого способа.
Пример 1. Имеются несколько диапазонов, имеющих одинаковую структуру. Необходимо подвести общий итог выбранного расчета, например – суммирование, среднее значение и так далее, для всех данных из указанных диапазонов. Наиболее удобным вариантом в данном случае будет использование трехмерной формулы. На листе, где будут подводится итоги, щелкаем в первую ячейку, в которой ожидается результат. Допускается использование любой функции агрегирования – СУММ, МИН, МАКС, СРЗНАЧ и так далее. Пишем название функции и открываем скобку. Переходим на первый лист с данными, щелкаем ячейку, которую надо обработать. Затем, удерживая клавишу Shift, нажимаем ярлычок последнего листа с исходными данными, закрываем скобку и нажимаем Enter. Расчет готов.
В примере ниже необходимо найти общую сумму выплаченных налогов по 28 форме с нескольких филиалов. Конечно, можно это сделать и стандартным способом, применив обычную формулу.
=’Форма 28 (1)’!B11+’Форма 28 (2)’!B11+’Форма 28 (3)’!B11
Такой вариант показан на изображении. 
Рисунок 1
Но гораздо удобнее использовать трехмерную формулу, что показана на изображении ниже. Приведем ее.
=СУММ(‘Форма 28 (1):Форма 28 (3)’!B11)
Рисунок 2
Такой способ кроме своей простоты, имеет важное преимущество. Если между первым и последним листом, заданным в формуле, вставить дополнительные листы, то их данные тоже будет использованы для расчета. Другими словами, уже не требуется переписывать формулу заново, как в случае обычного вычисления. Но кроме преимуществ способ имеет и недостатки. Перечислим их. Прежде всего необходимо иметь строго определенную структуру исходных данных. Если не соблюдается порядок позиций в строках и столбцах, если различается хранимая информация, например, в одном случае столбец содержит значение прибыли, а в другом информацию по расходам, то способ становится бесполезным. Кроме этого, способ бесполезен, если исходные данные находятся в разных книгах. Однако если имеем дело с одинаковыми отчетами, перенесенными или скопированными в один файл, то, наверное, это будет лучший инструмент для обработки данных.
Пример 2. Необходимо получить информацию итоговые данные с нескольких листов, которые содержат информацию по реализации товаров в нескольких городах. При этом требуется получить только итоговую информацию, независимо от ее источника. В таком случае удобнее использовать стандартный инструмент «Консолидация», запускающийся с вкладки данные.
Вначале создаем новый лист, выбираем на нем произвольную ячейку, на вкладке «Данные нажимаем кнопку «Консолидация».
Рисунок 3
Открывается окно настройки консолидации.
Рисунок 4
В выпадающем списке функций выбираем нужную, в данном случае выбран расчет суммы. Теперь нажимаем в строке ссылка. Выделяем требуемую для расчета таблицу вместе с ее заголовком. Начинать выделения требуется с колонки, по позициям которой нужна консолидации. После выделения нажимаем кнопку «Добавить» для добавления таблицы в «список диапазонов». Если таблица добавлена неправильно, выделяем ее в «Списке диапазонов», нажимаем кнопку «Удалить», после чего выделяем и добавляем заново нужны диапазон. Если таблицы находятся в разных файлах и если нужный файл открыт, то переходим в него для выбора таблицы. Если файл закрыт, с помощью кнопки «Обзор» надо его выбрать, после чего дописать в конце адреса файла имя нужной таблицы в выбранной книге.
После того, как все диапазоны добавлены в список, ставим обе галочки «Использовать в качестве имен». Галочку «Создавать связи…» ставим при необходимости и нажимаем ОК.
Рисунок 5
Преимущество способа – простота и скорость выполнения консолидации. В то же время способ тоже имеет ряд недостатков. В частности, в результате видно только общий итог. Информацию по отдельному вычислению по каждой позиции в отдельной таблице получить нельзя. Другими словами, мы получили итоговые данные по всем городам, но не информацию по конкретным товарам в отдельном городе. Еще один недостаток – неудобство проверки исходных адресов. Обновление информации не происходит автоматически даже при включении опции «Создавать связи…». Последняя опция может при определенной ситуации принести вред, если используются одни и те же таблицы в качестве исходных данных для разных периодов.
Пример 3. Необходимо получить информацию по итоговым данным с нескольких листов, которые содержат информацию по реализации товаров в нескольких городах. При этом требуется не просто получить общий итог, но вывести его отдельно по каждому городу. Применим инструмент «Консолидация», но предварительно переименуем нужные столбцы, добавив в наименование название города.
Рисунок 6
После этого добавляем новый лист и с вкладки «Данные» запускаем консолидацию. Добавляем все диапазоны с информацией по городам, ставим оба флажка под надписью: «Использовать в качестве имен» и нажимаем ОК.
Рисунок 7
Выведется общий итог по всем колонкам исходный таблиц, но колонки с итогами будут показаны отдельно с информацией по каждому городу.
Рисунок 8
Остается только удалить ненужные столбцы. Кстати, обратите внимание, что не все товары продаются в каждом городе, о чем свидетельствуют пустые ячейки.
Рисунок 9
Преимущество способа – возможность получения данных не только из текущей книги, но из любого файла с нужной таблицей. Допускаются любые функции агрегирования для консолидации. При этом размер таблиц значения не имеет, главное, чтобы имелись нужные заголовки в исходных данных. Минус в том, что не всегда возможно переименование нужных столбцов, да и если таких переименований необходимо, то сделать эти переименования в нескольких таблицах по нескольким столбцам вручную неудобно. В тоже время при работе с несколькими колонками таблица с консолидацией становится громоздкой
Пример 4. Ситуация та же, но теперь необходимо создать таблицу, которая будет выводить общий итог по выбранной колонке каждого города отдельно. Используем комбинацию функции СУММЕСЛИ и ДВССЫЛ, а также имена, точнее – поля умных таблиц. Приступим к работе. Прежде всего в отдельной ячейке создадим выпадающий список, содержащий наименования из заголовка исходных таблиц. На отдельном листе выберем пустую ячейку. В нашем примере это ячейка А2. На вкладке «Данные» откроем инструмент «Проверка данных», тип данных указываем «Список», в качестве источника укажем заголовок любой из исходных таблиц.
Рисунок 10
Рисунок 11
На следующем шаге выберем ячейка рядом, например, С2 и запустим консолидацию. Необходимо консолидировать только наименования товаров и первый столбец. Это требуется только для того, чтобы не копировать наименования вручную.
Рисунок 12
Удаляем значения в колонке D и добавляем в качестве заголовков будущей таблицы названия листов с информацией по городам.
Теперь переходим на каждый лист с исходной таблицей по отдельному городу и преобразуем их в умные таблицы. Чтобы это сделать, выбираем произвольную ячейку в таблице со значениями по выбранному городу, нажимаем сочетание Ctrl+T (английская), затем нажимаем Enter. Откроется окно «Конструктор», где необходимо поменять имя созданной таблицы на название соответствующего города, после чего снова нажать ОК. Так необходимо сделать с каждой таблицей каждого города.
Рисунок 13
Теперь на листе с консолидацией, подготовленном предварительно, вводим формулу
=ЕСЛИОШИБКА(СУММЕСЛИ(ДВССЫЛ(СЦЕПИТЬ(D$2;”[ТОВАР]”));$C3;ДВССЫЛ(СЦЕПИТЬ(D$2;”[“;$A$2;”]”)));0)
Рассмотрим ее подробнее. Обратите внимание на адресацию ячеек!
- D$2 – ячейка из заголовка таблицы с консолидацией. Соответствует названию нужной умной таблицы с выбранным городом;
- $C3 – содержит информацию по товару, сумма по которому должна быть получена;
- $A$2 – выпадающий список с названиями нужных полей, по которым выполнится суммирование;
- ДВССЫЛ(СЦЕПИТЬ(D$2;”[ТОВАР]”)) – функция СЦЕПИТЬ формирует строку из названия нужной умной таблицы и ее поля «товар», после чего функция ДВССЫЛ преобразует полученную строку в ссылку на диапазон проверки условия функции СУММЕСЛИ;
- $C3 – это и есть условие для функции СУММЕСЛИ;
- ДВССЫЛ(СЦЕПИТЬ(D$2;”[“;$A$2;”]”)) – здесь формируется строка из названия таблицы и поля, по которому требуется расчет. Затем ДВССЫЛ преобразует эту строку и ссылку на диапазон суммирования для функции СУММЕСЛИ;
- СУММЕСЛИ – выполнит суммирование по заданному товару, используя в качестве диапазонов проверки и суммирования результаты работы ДВССЫЛ;
- Наконец, ЕСЛИОШИБКА проверяет работу функции СУММЕСЛИ, и если та вызывает ошибку, то вместо этой ошибки выводит ноль.
Рисунок 14
Явным преимуществом способа является вывод итоговых данных по выбранной колонке для каждого города. Сама работа с итоговыми данными становится наглядной и удобной. Для изменения области расчета достаточно поменять значение в выпадающем списке ячейки A2. Кроме функции СУММЕСЛИ, можно применить СЧЕТЕСЛИ, СРЗНАЧЕСЛИ, формулы массива (в частности, для вывода минимального значения по товару в старых версиях) и так далее. Недостаток – неудобство использования таблиц из разных файлов. В этом случае придется дополнительно каким-то образом указывать еще и названия исходных файлов.
Подведем итог. На нашем занятии были рассмотрены различные примеры того, как может быть выполнена консолидация данных в Excel с помощью стандартного инструмента, так и на основе формул, комбинации функций, умных таблиц и так далее. Чтобы закрепить материал, предлагаем скачать исходный файл для повторения примеров, разобранных на уроке.
Конечно, таких способов гораздо больше, особенно в последних версиях Excel. Можно применить сводную таблицу по разным диапазонам консолидации. Можно использовать инструменты Power Query для получения таблиц из файлов, расположенных и в одной папке. Можно объединить с помощью Power Query несколько таблиц из файлов, находящихся в разных папках, в том числе в облачных хранилищах, и так далее. Наконец, если вам требуется выполнять непосредственно интерактивный анализ данных с возможностью отслеживания изменений в реальном времени, то примените дополнительный инструмент – Power BI. Оно не относится к Excel, но успешно обрабатывает таблицы и данные из этого табличного редактора.
Все это мы изучаем на наших семинарах и вебинарах на практических примерах. Вы можете подать заявку или связаться с нами в случае возникновения вопросов.
На этом наше занятие подошло к концу. Желаем всем хорошего настроения и продуктивной работы в Excel.
