Консолидация
Консолидация данных, операции с группой листов





Обычная СУММ и команда Консолидация позволяют получить итоговый отчёт на основании нескольких исходных таблиц данных. Они могут быть идентичными ли отличаться друг от друга.

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс Excel, самоучитель Excel, конспекты тренинга Excel, без регистрации.

Консолидация данных, операции с группой листов

Обычная СУММ и команда Консолидация позволяют получить итоговый отчёт на основании нескольких исходных таблиц данных. Они могут быть идентичными ли отличаться друг от друга. Специальные приёмы позволяют обрабатывать сразу несколько листов (переделка таблиц, подготовка к печати и др.) и создавать трёхмерные ссылки, суммирующие данные с листов очень компактно и правильно. Команда Консолидация безошибочно разберётся с отличающимися друг от друга таблицами.

Консолидация данных, сравнение отчетов, операции с группой листов

Для занятия Вам потребуется книга Excel с заготовками таблиц, по которым составлен конспект - этот файл прикреплен в самом низу страницы (без проблем откроется в Excel версии 2007 и новее)

 

Трехмерная формула вместо консолидации

На этом уроке мы вплотную подошли к вопросу периодической отчетности. В самом деле, пора поговорить о том, как быстро и правильно составить таблицу итоговых значений, используя данные из большого количества исходных листов или файлов. Ведь сидеть каждую пятницу (или любой другой день – в каждой компании он свой) и собирать нужные данные вручную «ячейка+ячейка+…» совсем не хочется. Во-первых, это долго; во-вторых, велика вероятность ошибки – можно пропустить нужные данные или выделить не ту ячейку, задвоить расчет и т.д. и т.п.; в-третьих, монстр-формула, которая в итоге получается, мягко говоря наводит панику и если потребуется добавить еще один лист в расчет… Предлагаю научиться решать вопрос таким образом, чтобы все вышеописанные страшилки больше не имели бы к нам отношения :-)

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

Выберите ячейку для первого расчета – у меня это B4 на листе ОТЧЕТ  (таблица Вариант 1) – и вставьте автосумму, например, нажмите сочетание клавиш Alt + = – в ячейке увидите =СУММ(l)  Далее перейдите на первый из нужных листов Склад 1 и щелкните по ячейке, данные из которой нужно брать для расчетов – B3

Теперь нажмите и удерживайте клавишу Shift и выполните щелчок левой кнопкой мыши по названию последнего нужно листа Склад 4 – Excel не перейдет на этот лист, но обратите внимание на строку формул – в ней хорошо видно, что через двоеточие указан диапазон листов

Оказывается, программа умеет работать даже вот таким образом :-) Не переключаясь на другие листы, нажмите Enter, чтобы завершить ввод формулы и скопируйте ее на все ячейки таблицы самым обычным образом. Для быстрого копирования с сохранием форматов таблицы я скопировала ячейку с первым результатом, нажала Shift и щелкнула левой кнопкой мыши по ячейке F12 (нижний правый угол диапазона данных), а после произвела вставку через контекстное меню и команду значения – иконка 123, как на рисунке

Итак, задача решена – быстро, компактно, правильно. Но это еще не все – попробуйте сделать копию любого листа внутри группы (можно перетащить ярлык любого листа с данными по складам при нажатой клавише Ctrl – сначала отпускать кнопку мыши, потом уже Ctrl) и увидите, что данные с таких листов тут же попадают в расчет, автоматически. Аналогично, если передвинуть лист за пределы указанной группы, то его данные тут же перестанут учитыавться в расчете (часто требуется показать картину, например, без какого-либо региона/филиала/склада/магазина, чтобы он не влиял на общий показатель)

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

 

Встроенный инструмент для консолидации идентичных друг другу таблиц

Чтобы применить встроенный инструмент Excel для консолидации таблиц, выберите ячейку, начиная с которой должны выводиться итоговые значения (то есть ту ячейку, в которой Вы бы стали писать первую формулу) – в моем примере это ячейка B20 листа ОТЧЕТ (таблица Вариант 2). Далее выполните команду Данные – Работа с данными – Консолидации [DataData_ToolsConsolidate] и окажетесь в одноименном диалоговом окне настроек:

Из списка в верхнем левом углу этого окна можно выбрать функцию для обработки данных, но я оставлю предлагаемую по умолчанию Сумму [Sum] – а вот список диапазон для суммирования нужно сформировать в центральноq части окна и для этого помещаем курсор в поле Ссылка [Reference], переходим на лист Склад 1 с первой исходной таблицей и выделяем диапазон ячеек, которые нужно будет суммировать B3:F11, и жмем кнопку Добавить [Add] в правой части окошка – как результат адрес первого диапазона будет зафиксирован в центральном списке:

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

Чтобы увидеть, как обновлять такой результат, на Складе 1 удалите любое значение – я убрала данные по Ананасам – Москва (ячейка B3) – автоматического пересчета не произойдет, потому мы сделали результат без сохранения связей с исходными ячейками. Выберите в консолидированной таблице первую ячейку с данными и просто выведите результат заново, поверх старого

Сделать такую консолидацию на ссылках можно, но есть у нее один существенный недочет. Встаньте на любую ячейку под таблицами, чтобы не повредить их (я выбрала ячейку B35 листа ОТЧЕТ) и еще раз вызовите окно консолидации Данные – Работа с данными – Консолидации [DataData_ToolsConsolidate]. Дополнительно установите флажок в нижей его части Создавать связи с исходными данными [Create links to source data] и нажмите ОК – в ячейках будет выведен похожий результат, однако получен он совсем иным образом. Появилась структура, отобразите первый раздел и увидите, что Excel добавил столько строк, сколько было листов с исходными данными, установил прямую ссылку на требуемую ячейку, и потом суммировал их. Вроде бы все хорошо. НО – нет ссылки на пустую ячейку, а это означает, что при заполнении пустых ячеек нужно помнить о том, что в консолидацию они не подтягиваются, и добавлять их вручную

 

Как выделить отличия по двум таблицам (условное форматирование)

В развитие темы хочется подумать над вопросом, как быстро выделить те ячейки, в которых произошли изменения по отношению к прошлому отчету. В моем примере пусть таблица по Варианту 2 показывает данные прошлой недели, а таблица по Варианту 1 – текущей. Чтобы данные разнились, обнулю на Складе 1 некоторые ячейки, однако визуально увидеть, по каким позициям случились изменения в итоговом отчетет – очень сложно. Такими показателями может выступать и выручка по группам товаров, и дебиторская задолженность, и величина остатков на складах и многое-многое другое

Поможет нам условное форматирование. Выбираем ячейки той таблицы, в которой нужно найти и выделить отличающиеся ячейки, - выделю B4:F12 листа ОТЧЕТ – и выполняем команду Главная – Стили – Условное форматирование – Создать правило [HomeStylesConditional_FormattingNew_Rule]. Далее работаем по второму типу правила Форматировать только те ячейки, которые содержат [Format_only_cells_that_contain]: Значение ячейки [Cell_Value] – не равно [not_equal_to] B20 в моем примере, а в общем случае нужно сделать относительную ссылку на ячейку с аналогичными данными другой таблицы (избавляемся от возникших знаков доллара при помощи F4)

Не забываем указать заливку для ячеек, отвечающих установленному условию, используя унопку Формат [Format] и можно нажимать ОК

 

Как отредактировать сразу несколько листов

Бывают рабочие ситуации, когда таблицы жестко регламентированы по форме для большого количества участников (отделов, филиалов и других структурных единиц). Необходимость внести изменения в большое количество таблиц – добавить новую позицию, исправить формулу, добавить строк с заголовком, скрыть или удалить устаревшие разделы, изменить форматирование, формулировки и прочее бесчисленное множество мелких или крупных правок – никогда никого не радовала. Ведь только подумать – внимательно перебрать 10, 40 или 100 листов! Но выход, оказывается, есть – внесенные на одном из листов изменения могут сразу появиться во всех остальных :-)

В моем примере буду добавлять новую строку над исходными таблицами на каждом из листов Склад и вводить текущую дату при помощи функции СЕГОДНЯ() / TODAY() Прежде чем приступать к правкам, все нужные листы сначала выделяем следующим образом: перейдите на первый лист группы Склад 1, нажмите и удерживайте клавишу Shift и щелкните левой кнопкой мыши по названию листа Склад 4 – клавишу Shift можно отпустить. Ярылки всех листов начиная со Склад 1 и заканчивая листом Склад 4 выделены белым цветом, а в строке заголовка в квадратных скобках появилось слово Группа [Group] – это означает, что все действия, которые Вы сейчас выполните на одном листе, будут синхронно осуществляться на всех выделенных листах

Стоит помнить, что режим этот ограничивает функционал вкладки Данные [Data], например, и многие другие возможности, но для добавления-удаления-скрытия-отображения-форматирования-ввода данных/формул его можно смело применять

После совершения всех требуемых действий не забудьте выйти из режима группового выделения листов – для этого щелкните по названию любого листа вне этой группы, либо правой кнопкой мыши вызовите контекстное меню к ярлыку любого сгруппированного листа и выберите в нем команду Разгруппировать листы  [Ungroup_Sheets]

 

Консолидация таблиц разного размера и с разным набором данных

Но что же делать с таблицами, в которых разное количество столбцов и/или строк и порядок следования записей разный? Консолидировать! На примере файла Ex-РВ-Урок-21-Консолидация-Разные таблицы сейчас научимся, каким именно образом, а также разберем ситуации, когда исходных данных больше, чем должно остаться в консолидированном отчете

На листе ОТЧЕТ на этот раз отсутствует заготовка таблицы, потому что Excel создаст ее сам. Нам остается только выбрать ячейку, начиная с которой будет располагаться итоговая таблица, и выполнить уже знакомую команду Данные – Работа с данными – Консолидации [DataData_ToolsConsolidate]. Помещаем курсор в поле Ссылка [Reference], идем к первой исходной табличке на лист Склад 1 и выделяем диапазон данных, только на этот раз захватив заголовки столбцов и названия строк, потому что для Excel задача будет сводиться буквально к следующему: для каждого значения определить эти заголовки и названия и складывать между собой только те значения, для которых они совпадают:

После выделения ячеек нажимайте кнопку Добавить [Add] и переходите на следующий лист. Если следующий диапазон оказывается выделен больше предыдущего – можно инорировать этот факт и продолжать добавлять диапазоны в список, пустые ячейки Excel в работу не возьмет, но не забываем в случае необходимости выделить таблицу полностью, если пунктирная рамка не захыватывает ее всю. Когда все нужные диапазоны ячеек окажутся в списке, установите в нижней части окна два флажка из группы Использовать в качестве имен [Use_labels_in]: Подписи верхней строки [Top_row] и Значения левого столбца [Left_column], можно нажимать ОК:

Итоговая таблица готова! Осталось разобраться с названиями строк – в исходных данных оказалось две похожих формулировки: Помидор и Помидорка. Как вариант, можно скопировать диапазон ячеек с данными по Помидору B11:F11, встать на ячейку B12, нажать правую кнопку мыши, выбрать команду Специальная вставка [Paste_special], и далее в открывшемся окне установить переключатель сложить [sum] – после нажатия ОК скопированные данные будут суммированы с данными выбранного диапазона, а лишнюю строку можно будет просто удалить:

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

А еще можно было выполнить замену ненужного нам названия на нужное и потом просто вывести заново результат консолидации. При использовании окна Найти и заменить [Find_and_Replace] - его можно вызвать комбинацией клавиш Ctrl + H – воспользуйтесь кнопкой Параметры [Options], чтобы, во-первых, сразу проверить всю книгу – из списка нужно выбрать Книга [Workbook] вместо Лист [Sheet], а еще я бы поставила флажок Ячейка целиком [Match_entire_cell_contents], иначе результат может оказаться весьма забавными и неожиданным

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

Похожие материалы

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

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

Создание кнопок ПЛЮС и МИНУС для скрытия и отображения строк и столбцов
Группировка строк и столбцов
Как сделать кнопки плюс и минус для скрытия и отображения данных в Excel? Как настроить, чтобы они были в строках сверху или в столбах слева?

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

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

Оставьте ваше имя и адрес электронной почты, в ближайшее время я свяжусь
с вами для консультации и отвечу на вопросы!



Оставляя заявку, вы соглашаетесь на обработку ваших персональных данных.