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

Наглядное применение консолидации в Excel.

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

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

Рисунок 1

Хотелось бы заметить, что в исходной таблице было почти 5 тысяч строк,10 граф. Однако после консолидации все несущественные столбцы были удалены, число же строк после консолидирования данных в Excel по каждой дате поступлений сократилось до 15. Другими словами, вместо большой таблицы мы имеем короткую, сжатую и понятную итоговую информацию

Консолидация по сути очень похожа на инструмент ПРОМЕЖУТОЧНЫЙ ИТОГ, но имеет важные отличия.

1. ПРОМЕЖУТОЧНЫЙ ИТОГ может работать только с одной таблицей, а количество таблиц в консолидации может быть до 255
2. ПРОМЕЖУТОЧНЫЙ ИТОГ требует обязательной сортировки колонки с критериями, для которых ведется расчет. КОНСОЛИДАЦИЯ сама группирует данные независимо от их расположения в колонке
3. ПРОМЕЖУТОЧНЫЙ ИТОГ выводит результат по умолчанию выводит результат только в последней графе, остальные нужно указывать вручную. Консолидация подводит итоги во всех столбцах, кроме первого.
4. ПРОМЕЖУТОЧНЫЙ ИТОГ выводи результат непосредственно в таблице, консолидация может использовать данные из любого диапазона.

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

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

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

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

Создание консолидации в Excel – процесс несложный и доступный даже новичку. Разберем его на примере консолидирования одной таблицы. Делаем следующее:

1. Выбираем на отдельном чистом листе ячейку для начала будущей таблицы консолидации и на вкладке ВСТАВКА нажимаем кнопку КОНСОЛИДАЦИЯ.

Рисунок 2

Если в книге пустого листа нет, его надо создать. Выбираем функцию для вычисления. Выбор довольно большой, есть фактически все необходимые варианты. Присутствует сумма, количество (количество позиций), максимум, минимум, среднее, отклонение и другие варианты для расчета. Надо учесть, что консолидация не умеет вычислять математические функции с ТЕКСТОВЫМИ ДАННЫМИ. По сути, это не умеет делать никакая программа. Так что если вы будете обрабатывать нечисловые данные, то максимум что вы сможете сделать, это подсчитать количество значений.

рисунок 3
После выбора нажимаем кнопку ДОБАВИТЬ, и наша выделенная таблица попадает в список диапазонов. Если что-то выбрано неправильно, то можно нажать кнопку УДАЛИТЬ и повторить процесс заново. Кроме этого, не забудьте, что после создания таблицы консолидации на этой странице Excel «запомнит» исходные данные. Поэтому вы всегда можете запустить на ней создание консолидации и поменять в частности расчет. Сам адрес исходный диапазон будет сохранен

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

2. Теперь ОБЯЗАТЕЛЬНО ставим внизу ОБА флажка (галочки) под надписью ИСПОЛЬЗОВАТЬ В КАЧЕСТВЕ ИМЕН. Если не включить в качестве имен заголовки, у вас будут безымянные колонки. Итог получится примерно такой.

Рисунок 4

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

Рисунок 5

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

Флажок же СОЗДАВАТЬ СВЯЗИ поставьте если только это действительно необходимо. К примеру, если вы собираете с помощью консолидации ежемесячный отчет, то вряд ли вам понравится, когда из-за включенной связи данные за март попадут в отчет за январь. С другой стороны, если вы собираете постоянно итоговый отчет на текущий момент, эта галочка будет полезна. С другой стороны, несомненным плюсом создания связей является возможность обновления данных. Достаточно, находясь на нужном листе, на вкладке ДАННЫЕ нажать кнопку ОБНОВИТЬ ВСЕ, и вы получите свежие данные из исходной таблицы. Однако при добавлении данных в новые строки исходной таблицы вам в любом случае придется создавать консолидацию заново. После создания можно удалить ненужные колонки. Например, можно по каждому наименованию получить таблицу консолидации по ценам, но при выборе диапазона захватить также и столбец с количеством единиц товара. Для цен вывести среднее значение, затем создать новую консолидацию рядом с предыдущей. Так как она на одном листе с консолидацией цен, то ничего дополнительно выделять уже не нужно. В этой консолидации просуммировать количество единиц по каждому наименованию. Наконец, удалить ненужные столбцы, получить итоговую таблицу консолидирования с средними ценами и общим количеством по каждому наименованию товара.

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

Рисунок 6

Кстати, именно из-за этого говорят, что таблицы для консолидации должны иметь абсолютно одинаковый размер и начинаться с одной ячейки. На самом деле это ПОЛНАЯ ЧУШЬ. Таблица для создания таблицы консолидации может начинаться откуда угодно и иметь абсолютно разное количество строк. А вот заголовок у всех таблиц действительно должен быть одинаковым. В следующем примере показан консолидированный годовой отчет по городам Актау, Караганда и Семей. Сразу отмечу, что не во всех городах есть продажи по каждому из наименований за каждый месяц. Тем не менее такая ситуация никак не влияет на создание таблицы консолидации в Excel. Обратите внимание на адреса диапазонов!

Рисунок 7

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

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

Второй способ заключается в том, что файл должен быть предварительно открыт. Тогда можно выбрать его на панели задач рабочего стола, а затем указать нужную таблицу. В этом случае надо быть особо внимательным, так как когда файлов много, то после добавления диапазона, при попытке выбрать новый диапазон, Excel попытается вернуться в ту же книгу, откуда уже был добавлен диапазон. Обойти это можно удалением содержимого строки ИСТОЧНИК после КАЖДОГО ДОБАВЛЕНИЯ ДИАПАЗОНА.

Рисунок 8

Обратите внимание, что на примере выше первый диапазон был в том же файле, что и создаваемая таблица консолидации. Второй диапазон был получен из файла Алматы, который предварительно был открыт. Ну а третий был получен из закрытого файла Нур-султан.xlsx по имени диапазона. Для удобства этот диапазон имеет такое же имя, как и сам файл, но из-за ограничений на структуру имен в нем дефис заменен на знак подчеркивания.  

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

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

Желаю всем успешной работы и хорошего настроения! Если остались вопросы, пишите в комментариях