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

2.1. Перейдите на шестой лист рабочей книги. Переименуйте его в Итог. Поскольку номер и название товара у нас остаются неизменными и естественно не должны быть подвергнуты операции сложения, то можно скопировать эту часть таблицы из рабочего листа, например для Киева. Для этого выделите в листе Киев диапазон ячеек А3-В13, выберите команду Копировать в меню Правка, перейдите в лист Итог, поставьте курсор на ячейку А3 и выберите команду Вставить из меню Правка. Отформатируйте ячейки с данными.

2.2. Поместите указатель на ячейку С3 и выберите в меню Данные, Работа с данными команду Консолидация.

Рис. 2.176

В открывшемся окне в списке Функция выберите операцию, которая будет выполняться над консолидируемыми данными. Выберите операцию сложения (элемент Сумма).

2.3. В поле ввода Ссылка следует указать диапазоны ячеек, данные из которых должны быть подвергнуты процессу консолидации. Поместите курсор ввода в поле Ссылка, выполните щелчок мышью на ярлычке листа Киев, выделите диапазон ячеек D3-F13 и нажмите кнопку Добавить. Указанный диапазон будет представлен в поле Список диапазонов. Ненужный диапазон может быть удален с помощью кнопки Удалить.

2.4. После включения в диапазон консолидации первого диапазона выполните аналогичные операции над другими диапазонами. С помощью щелчка мышью на ярлычке перейдите в лист с данными для следующего филиала. Excel автоматически выделит тот же диапазон ячеек, что и в предыдущем листе. Нажмите кнопку Добавить. Аналогичным образом укажите в поле Список диапазонов нужные диапазоны листов других филиалов.

2.5. Установите необходимые опции, находящиеся в окне Консолидация. Если верхняя строка и/или левый столбец содержат заголовки столбцов или строк, которые нужно скопировать в итоговую таблицу, следует включить соответствующие опции в группе Использовать в качестве имен. Так как верхняя строка содержит заголовки столбцов, то нужно активизировать опцию Подписи верхней строки. Включите опцию Создавать связи с исходными данными. Нажмите кнопку ОК.

2.6. В ячейке А1 введите название таблицы – Итоговые данные. В этой таблице можно проследить, какой товар больше всего способствовал росту объема продаж во всех филиалах вместе.

2.7. Определите сумму по всем филиалам. Для этого укажите в ячейке, находящейся в конце данных (В64), слово Всего, а в ячейке Е64 поместите формулу вычисления суммы. Excel сам выделит нужный диапазон и после нажатия Enter выдаст сумму значений выделенного диапазона.

2.8. Для более наглядного представления данных в столбце F приведите значения долей отдельных продуктов в общем объеме продаж.

В ячейку F3 введите заголовок Доля продаж. В ячейку F9 введите формулу =E9/$E$64. Скопируйте ее в остальные ячейки столбца F (до F64) и задайте процентный формат. Полученные результаты позволяют сделать вывод о популярности товара. Итоговая таблица представлена на рис. 2.177

Рис. 2.177 Таблица итоговых данных

2.9. Структурирование документа позволяет добиться представления на экране только необходимой информации и скрыть детали. Слева от таблицы представлены символы структуры. Цифрами обозначены уровни структуры. Нажатие кнопки со знаком плюс позволит расшифровать данные высшего уровня структуры.

2.10. Нажмите на кнопку с плюсом около девятой строки. Поскольку ячейки с данными отдельных заказов для первого товара не были учтены при определении долей, то скопируйте формулу из ячейки F9 в ячейки F4:F8. Скрыть детали можно посредством щелчка на кнопке с изображением знака минус.

3. Сводная таблица на основе диапазонов консолидации

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

3.2. Поместите указатель на ячейку А1 листа Таблица. Создайте сводную таблицу с помощью инструмента «Мастер сводных таблиц и диаграмм». Для использования мастера сводных таблиц выведите его на панель быстрого доступа. Нажмите на кнопку «Настройка панели быстрого доступа» и выберите «Другие команды…»:

В открывшемся окне «Параметры Excel» на вкладке «Настройки» в поле «Выбрать команды из:» выберите «Все команды»:

Найдите и выделите в списке строку «Мастер сводных таблиц», нажмите «Добавить»:

Получилось:

Запустите Мастер сводных таблиц и диаграмм.

В открывшемся окне выберите в качестве источника данных элемент В нескольких диапазонах консолидации. Нажмите кнопку Далее.

3.3. В открывшемся окне выберите элемент Создать поля страницы, что позволит присвоить имя диапазонам для каждого филиала.

Нажмите кнопку Далее.

3.4. В открывшемся окне активизируйте опцию 1, расположенную под полем Список диапазонов. После выполнения этой операции становится доступным поле ввода Первое поле. Затем переведите курсор ввода в поле Диапазон и с помощью мыши выделите диапазон ячеек В3:F13 в рабочем листе Киев. Нажмите кнопку Добавить и аналогичным образом представьте в поле Список диапазонов адреса диапазонов с данными по другим филиалам.

3.5. После указания всех диапазонов присвойте им имена. Для этого выделите первый диапазон в поле Список диапазонов и введите имя, соответствующее городу филиала для данного диапазона, в поле Первое поле. Затем выберите следующий диапазон и введите имя в поле Первое поле и т.д. для всех филиалов. После введения всех имен нажмите кнопку Далее.

3.6. В следующем окне нажмите кнопку Готово. Сводная таблица представлена на рис. 2.178.

Рис. 2.178Сводная таблица на основе консолидации

3.7. Сделайте анализ, какой вид товара принес наибольший оборот по всем филиалам, а также для каждого отдельного филиала. Чтобы посмотреть данные по филиалу, необходимо в области Страницы1 выбрать нужный филиал.

3.8. Находясь в сводной таблице, переместите поле Строка в область Страницы, а поле Страница1 – в область строк (рис. 2.179). Проанализируйте, какой филиал достиг лучших результатов работы и какой товар в каком городе пользовался самым большим спросом.

Рис. 2.179. Сводная таблица после изменения

Умные таблицы

Рассмотрим пример превращения диапазонов данных в умные таблицы и составления по ним сводных таблиц.

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

Решение

В раскрывшемся списке стилей выбираем любой вариант заливки на наш вкус и цвет и в окне подтверждения выделенного диапазона жмем ОК.

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

Созданная Таблица получает имя Таблица 1,2,3 и т.д. которое, можно изменить на более адекватное на вкладке Конструктор. Это имя можно использовать в любых формулах, выпадающих списках и функциях, например, в качестве источника данных для сводной таблицы или массива поиска для функции ВПР.

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

В шапке Таблицы автоматически включается Автофильтр (можно принудительно отключить на вкладке Данные).

При добавлении новых строк в них автоматически копируются все формулы.

При создании нового столбца с формулой - она будет автоматически скопирована на весь столбец - не надо тянуть формулу черным крестом автозаполнения.

При прокрутке Таблицы вниз заголовки столбцов (A, B, C…) меняются на названия полей, т.е. уже можно не закреплять шапку диапазона как раньше.

Включив флажок Показать итоговую строку на вкладке Конструктор мы получаем автоматическую строку итогов в конце Таблицы с возможностью выбора функции (сумма, среднее, количество и т.д.) по каждому столбцу.

К данным в Таблице можно адресоваться, используя имена отдельных ее элементов. Например, для суммирования всех чисел в столбце НДС можно воспользоваться формулой =СУММ(Таблица1[НДС]) вместо =СУММ(F2:F200) и не думать уже про размеры таблицы, количество строк и корректность диапазонов выделения. Также возможно использовать еще следующие операторы (предполагается, что таблица имеет стандартное имя Таблица1)

=Таблица1[#Все] - ссылка на всю таблицу, включая заголовки столбцов, данные и строку итогов

=Таблица1[#Данные] - ссылка только на данные (без строки заголовка)

=Таблица1[#Заголовки] - ссылка только на первую строку таблицы с заголовками столбцов

=Таблица1[#Итоги] - ссылка на строку итогов (если она включена)

=Таблица1[#Эта строка] - ссылка на текущую строку, например формула =Таблица1[[#Эта строка];[НДС]] - будет ссылаться на значение НДС из текущей строки таблицы.

Для создания сводной таблицы необходимо сделать активной ячейку в таблице с исходными данными и нажать кнопку Сводная таблица на вкладке Вставка.

Отобразится мастер создания сводных таблиц.

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

Изменяя названия строк, столбцов или значения можно получать отчеты различного вида.

Можно также добавить в поле фильтр необходимые категории, по которым желаем сделать отчет, например, "Менеджер продаж".

Однако начиная с версии 2010 есть более изящный способ включить фильтр - "Добавить срезы" на вкладке "Анализ", которая появляется при щелчке на сводную таблицу.

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

При нажатии на панель появляется вкладка "Параметры", на которой можно настраивать внешний вид панели.

Панель можно подключить к нескольким сводным таблицам нажав кнопку "Подключения к отчетам".

Другой интересной возможностью фильтрации является добавление временной шкалы.

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

На основе сводных таблиц также можно строить динамические сводные диаграммы (на вкладке "Анализ").

Поиск по сайту: