5. Сводные таблицы и диаграммы. Построение диаграмм.

Начиная с Excel 2000 в этом табличном процессоре появилось новое средство, позволяющее очень быстро представить данные сводной таблицы в графической форме. Нажмите кнопку «Мастер диаграмм» на панели инструментов «Сводные таблицы» и Excel автоматически (ничего не спрашивая) построит сводную диаграмму на отдельном листе. Результат автоматического построения диаграммы приведен на рис. 11.

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

Сводные диаграммы обладают рядом достоинств:

·        Построение сводной диаграммы происходит автоматически

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

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

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

Рис. 11. Автоматически построенная сводная диаграмма

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

Рис. 12. Диаграмма "Динамика объемов операций"

Задание 8. Консолидация данных

Задача 1.Консолидация данных, расположенных на разных листах рабочей книги

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

2.            Добавить в книгу рабочий лист «Сводка»

3.            На нем провести Консолидацию указанных данных.

Рис. 1. Диапазоны консолидации.

4.            Сохранить результаты работы.

Задача 2.Создание сводной таблицы с консолидацией данных

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

2.     Добавить в книгу рабочий лист "Сводка".

3.     Построить Сводную таблицу, выбрав в качестве источника исходных данных вариант "в нескольких диапазонах консолидации".

4. Сохранить результаты работы.

Задача 3.Консолидация данных, расположенных в разных рабочих книгах

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

2.     В отдельной рабочей книге провести Консолидацию указанных данных. Сохранить книгу под названием Итоги за 1 квартал.

3.     Сгруппировать файлы, созданные по итогам выполнения данной задачи в одну папку.

Методические указания для выполнения задания 8

1. Выполнение консолидации данных.

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

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

В выпадающем списке «Функция» выставлено «Сумма», т.е. выбранные данные будут суммироваться. Но можно выбрать и другую итоговую функцию: посмотрите список.

Следующее поле: «Ссылка». Выполним последовательность действий: установив фокус ввода в этом поле, будем по очереди выделять диапазоны для консолидации; когда в этом поле появится очередной диапазон, щелкнем кнопку «Добавить» - адрес диапазона переместится в окно «Список диапазонов».

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

Щелкнем мышью в поле «Ссылка», перейдем на лист «Январь» и выделим блок, содержащий список с данными (включая заголовки столбцов). Щелкнем кнопку «Добавить» - адрес диапазона окажется в поле «Список диапазонов». Аналогично добавим диапазоны листов «Февраль» и «Март»", таким образом, список диапазонов консолидации сформирован.

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

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

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

	Кол-во	Сумма
Кофе	150	1800
Чай	200	4200

Рис. 2.

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

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

Для установления связей достаточно открыть диалоговое окно «Консолидация» и установите флажок «создавать связи с исходными данными». Таблица изменит свой вид (рис. 3) – появится пустой столбец, слева появились символы структуры (знаки «плюс» и номера уровней структуры). Раскроем второй уровень структуры. В ранее пустом столбце появятся слова «Консолидация», а в столбцах правее мы увидим, из каких исходных данных сложились итоговые данные. Если теперь изменить количество или сумму товара на листах «Январь», «Февраль» или «Март», то итоговые данные будут обновлены автоматически.

		Кол-во	Сумма
	Консолидация	10	100
		40	400
	Консолидация	10	100
		40	800
	Консолидация	10	100
		40	300
Кофе		150	1800
	Консолидация	20	1000
		30	300
		50	700
	Консолидация	20	500
		30	300
	Консолидация	30	1000
		20	400
Чай		200	4200

Рис. 3.

Отметим, что в окне «Консолидация» кроме ссылок на диапазоны оного или разных листов одной рабочей книги допустимы ссылки на области в разных книгах. Для этого можно ввести ссылки вручную, указав имя книги, имя листа, а затем - имя или ссылку на диапазон. Например, чтобы включить диапазон с именем «Продажи» с листа «Дальний Восток» книги «2002.xls», введите: [2002.xls]Дальний Восток!Продажи. Чтобы задать описание источника данных, не нажимая клавиш клавиатуры, укажите поле Ссылка, а затем выделите исходную область. Чтобы задать исходную область в другой книге, нажмите кнопку Обзор. Чтобы убрать диалоговое окно «Консолидация» на время выбора исходной области, нажмите кнопку с красной стрелкой «Свернуть диалоговое окно».