Тема 6. Способы консолидации данных. Сводные таблицы.

КРАТКАЯ СПРАВКА

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

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

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

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

Примеры выполненных  заданий:

Пример №1:   Консолидация на рабочем листе

Создайте рабочую книгу  Пример1.хls,  состоящую из одного листа: Сводка.

На листе Сводка постройте таблицу, представленную на рис.6.1. В поле Сумма вставьте соответствующую формулу. Подведите итоги по столбцу Сумма.

Рис.6.1.   Содержимое листа Сводка

Укажите верхнюю левую ячейку конечной области консолидируемых данных. В нашем задании, например, А22.

В меню Данные выберите команду Консолидация.

Выберите из раскрывающегося списка Функция функцию, которую следует использовать для обработки данных.

Перейдите в поле Ссылка и укажите с помощью мыши исходную область консолидируемых данных или введите с клавиатуры ссылку на нее:   Сводка!$А$4:$D$7.   Нажмите   кнопку      ссылка  будет введена в Список диапазонов.

Повторите шаги 4 и 5 для всех консолидируемых исходных областей:  Сводка!$А$9:$D$13, Сводка!$А$15:$D$18

Выберите Использовать в качестве имен пункт значения левого столбца (рис. 6.2.)

Рис.6.2.   Окно «Консолидация»

Снимите флажок Создавать связи с исходными данными, если он установлен.

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

Пример№2: Консолидация рабочих листов.

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

Создайте рабочую книгу Пример2.хls, состоящую из четырех листов: Январь, Февраль, Март, 1 квартал.

Внесите заголовки столбцов одновременно в несколько листов. Для этого:

выделите все листы с названиями месяцев: перейдите на лист Январь, нажмите клавишу Shift и, не отпуская ее, щелкните по ярлычку листа Март. Будут выделены все листы рабочей книги, при этом активным листом останется Январь;

введите в ячейку А3 - Фамилия И.О., в ячейку В3 - Сделки, в С3 -._    Объем;

щелкните по ярлыку листа I квартал, выделение листов будет снято;

убедитесь, что в ранее выделенные листы внесен один и тот же текст в ячейки А3, В3, С3. Для иллюстративных целей поменяйте на листе Февраль содержимое ячеек: в В3 - Объем, а в С3 - Сделки.

Введите в листы с названиями месяцев информацию в соответствии с таблицей (рис. 6.З.).

 Рис. 6.3. Содержимое листов
                Январь, Февраль, Март.

Обратите внимание, что фамилии в листах идeт в полном беспорядке, заголовки столбцов тоже перепутаны (но фамилии всегда в первом столбце!).

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

В меню Данные выберите команду Консолидация. Появится диалоговое окно Консолидация.

Выберите из раскрывающегося списка Функция необходимую функцию которую следует использовать для обработки данных.

Перейдите в поле Ссылка. Щелкните мышью по ярлычку листа Январь (в поле ввода появится Январь! - формируется адрес). Выделите блок А3:С6 (в поле ввода - Январь!$А$3:$С$6) - вокруг блока бегущая пунктирная рамка. Нажмите кнопку     ссылка будет введена в Список диапазонов. Аналогично добавим диапазоны Февраль!$А$3:$С$5     и     Март!$А$3:$С$7.     Список     диапазонов консолидации сформирован.

В диалоговом окне имеется блок «Использовать в качестве имен» из двух флажков «подписи верхней строки» и «значения левого столбца». Установите оба флажка.

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

Флажок Создавать связи с исходными данными устанавливать пока не будем.

После щелчка по кнопке ОК на рабочем листе 1 квартал появится таблица (рис. 6.4.):

Рис. 6.4. Результат выполнения консолидации

Обратите внимание, что заголовок Фамилия И.О. отсутствует.

Внесите изменения в один из диапазонов, например, на листе Март у
Иванова И.И. увеличьте Объем до 250. Таблица на листе 1 квартал не
изменится,  т.к.   не  был  установлен   флажок  Создавать  связи  с
исходными данными. Выполните команду Данные /Консолидация, в
диалоговом окне ничего не меняйте, только щелкните по кнопке ОК,
произойдет обновление таблицы.

Установите связи. Выделите на листе 1 квартал ячейку А1, Откройте диалоговое окно Консолидация и установите флажок Создавать связи с исходными данными. Таблица изменилась (рис. 6.5).

Рис. 6.5. Консолидация в режиме связей с исходными данными

Выполните подгонку ширины столбцов. Столбец В пустой, столбцы Сделки и Объем переместились в столбцы С и D. Слева появились символы структуры.

Раскроем второй уровень структуры. В столбце В появились имена текущей рабочей книги (можно консолидировать данные из разных рабочих книг), а в столбцах С и D вы увидите, из каких исходных данных сложились итоговые данные. Если теперь изменить количество, например, сделок на листе Март, то итоговые данные будут обновлены автоматически.

Пример №3. Использование сводных таблиц для консолидации

Откройте рабочую книгу Пример2.xls. Подведем итоги работы агентов за первый квартал, используя сводную таблицу.

Вставьте в книгу новый рабочий лист Сводка.

Выделите ячейку А1 и вызовите Мастер сводных таблиц (рис. 6.6).

На первом шаге установите переключатель «в нескольких диапазонах консолидации».

Рис. 6.6.  Окно «Мастер сводных таблиц и диаграмм

На втором шаге установите  переключатель Создавать одно поле страницы.

Рис. 6.7.  Шаг 2

На третьем шаге укажите диапазоны консолидации:

Рис. 6.8.  Шаг 3

На четвертом шаге сконструируйте макет сводной таблицы.

Рис. 6.9.  Шаг 4

Пример №4. Построение сводных таблиц.

Дана следующая таблица(рис.6.10)

Рис. 6.10.  Образец  таблицы

Определите с помощью сводной таблицы эффективность работы каждого продавца.

Вывести в сводной таблице сумму продаж каждого продавца по регионам.

Запуск мастера сводных таблиц осуществляется командой Данные — Сводная таблица.

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

Рис. 6.11.  Шаг 1

Если в качестве источника данных выбран список Excel, то на втором шаге определяется диапазон ячеек, содержащий данные. Для того чтобы Excel правильно определил положение списка, перед запуском мастера выделите одну ячейку списка. Если необходимо изменить диапазон, сделайте это самостоятельно.

Рис. 6.12.  Шаг 2

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

Рис. 6.13.  Шаг 3

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

Рис. 6.14.  Создание макета сводной таблицы

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

Рис. 6.15.  Вызов окна для изменения функции

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

Рис. 6.16.  Шаг 4

На этом же шаге определяется название сводной таблицы и другие параметры.

После нажатия на кнопку Готово Excel создаст сводную таблицу.

Рис. 6.17.  Полученная сводная таблица

Пример №5. Форматирование сводной таблицы

Взять готовую сводную таблицу из примера №4

Для удаления заголовков строк (столбцов) перетащить имя заголовка за пределы таблицы.

Рис. 6.18.  Удаление заголовка

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

Рис. 6.19.  Перемещение заголовка

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

Рис. 6.20.  Отформатированная  сводная таблица

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

Рис. 6.21.  Просмотр данных таблицы

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

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

Скачано с www.znanio.ru