Несколько вариантов консолидирования на практических примерах.
Всем добрый день! На текущем занятии мы наглядно изучим, как выполняется консолидация данных в 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.
