Как быстро суммировать данные из разных таблиц

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

Имеется 3 таблицы (каждая - в отдельном файле) от трех разных менеджеров. Таблицы содержат данные о продажах товаров в разрезе артикулов и месяцев.  

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

Задачу можно решить множеством способов, но мы разберем самый быстрый (мы же любим эффективность и продуктивность). Воспользуемся встроенным в Excel инструментом "Консолидация". Он находится на вкладке "Данные". 

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

 1) Функция консолидации. Это выпадающий список, в котором можно выбрать функцию, которая будет применена при объединении таблиц. Это может быть подсчет суммы, количества, среднего, минимального и максимального значения и т.д. Как видите, перечень довольно обширный, но все функции - математические. А значит работают только с числами, но не с текстом. 

2) Ссылка. Это строка, в которой нужно указать адрес одной из таблиц (одного из диапазонов) для объединения. Сюда можно ввести адрес диапазона в этом же файле или в другом файле (открытом или закрытом). Можно вводить именованные диапазоны. Ввод осуществляется либо вручную, либо путем указания диапазона (для выбора нужного - нажмите на стрелку в конце строки). 

3) Обзор. Используйте эту кнопку, когда книга с диапазоном для объединения закрыта. Это избавит Вас от необходимости вводить адрес книги в строку Ссылка вручную, но указать адреса ячеек внутри книги придется всё же руками. Поэтому удобнее заранее открыть все нужные файлы.

4) Список диапазонов. Это общий список всех диапазонов/таблиц, которые Вы собираете консолидировать. 

5) Добавить. Эта кнопка используется для переноса указанного в строке "Ссылка" диапазона в общий список диапазонов консолидации.

6) Удалить. Эта кнопка используется для удаления диапазона из общего списка диапазонов консолидации. 

7) Использовать в качестве имен. Самая важная настройка. Если не установлена ни одна галочка, Excel будет суммировать ячейки из таблиц просто по соответствующей позиции (каждую третью ячейку второй строки суммирует с каждой третьей ячейкой второй строки всех таблиц и т.д.). Это называется консолидация по позиции. Если используете такой вариант - при указании диапазонов не указывайте заголовки строк и столбцов. 

Если поставлена одна или обе галочки, то Excel будет ориентироваться на название строки/столбца. Например, если поставить обе галочки, то при консолидации будут суммироваться ячейки, которые находятся на пересечении строк и столбцов с одинаковыми названиями. Это называется консолидация по категории. При использовании этого варианта - указывайте вместе с диапазоном заголовки строк и столбцов. 

Для работы инструмента консолидируемые заголовки должны совпадать. Обращайте внимание на наличие лишних пробелов в названиях строк/столбцов. Регистр при этом не важен (Январь и ЯНВАРЬ - одинаковые названия). 

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

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

Для нашего примера настройки объединения будут выглядеть вот так: 

 Видеопример: 

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

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

Это происходит для удобства работы с формулами, которые добавляются при создании обновляемой таблицы. Ячейки, спрятанные под "плюсик" имеют формулы вида =[Петров.xlsx]Лист1!$C$2, а итоговые ячейки подержат простые формулы суммы: =СУММ(D3:D5).

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

Весь процесс создания итоговой таблицы занял не более пары минут. Согласитесь, это довольно быстро. 

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

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

Поддержать наш проект и его дальнейшее развитие можно вот здесь.

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

С уважением, команда tDots.ru