Валентин Арьков - Бизнес-аналитика. Сводные таблицы. Часть 1

Рис. 10.16. Настройка диаграмм

Нажимаем ОКи получаем более понятную и информативную комбинированную диаграмму (рис. 10.17).

Рис. 10.17. Комбинированная диаграмма

Задание. Настройте комбинированную диаграмму.

График стал гораздо лучше. Теперь уточним кое-что по поводу наших итоговых показателей.

Первый показатель — CLOSE. Мы взяли цены закрытия и посчитали среднее значение. Получилась средняя цена закрытия. Вообще-то цена закрытия — это цена последней сделки торгового периода.

Если у нас дневные данные, то закрытие — это окончание торговой сессии на бирже. Когда на биржевых графиках «укрупняют» данные за месяц, то цена закрытия — это последняя сделка последней торговой сессии месяца. Но такого метода агрегирования у нас в распоряжении нет. Получается, что стандартные средства не позволяют делать нестандартные действия. Звучит по-идиотски смешно. Но это так и есть. Пока примем к сведению, что не всё можно сделать готовыми средствами.

Второй показатель — VOLUME. Мы взяли объёмы торгов и нашли среднее значение. Это средний объём торгов за один день. Что такое объём? Это количество акций, которые перешли из одних рук в другие за один торговый период. Может быть, они переходили от одного владельца к другому и обратно несколько раз за день. В объёмах торгов учтены все сделки. При укрупнении масштаба на биржевых графиках до месяца объёмы должны быть за месяц, а не в среднем за день. Значит, здесь должна быть сумма объёмов. Сумму в сводных таблицах мы можем посчитать.

Настроим вычисление суммы объёмов. Щёлкаем по графику. Появляется меню настройки полей PivotChart Fields. Щёлкаем по кнопке VOLв разделе Valuesи выбираем в меню следующий пункт:

Value Field Settings — Summarize Values By — Average.

Мы изменили настройку полей для сводного графика. При этом одновременно изменились цифры в сводной таблице (рис. 10.18). Стало быть, сводная таблица и сводный график здесь работают как одно целое.

Рис. 10.18. Общий объём торгов

Задание. Настройте подсчёт суммарных объёмов торгов. Обратите внимание на изменения в графике и в таблице.

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

СВОДНЫЙ ГРАФИК — это просто график, построенный по обобщённым данным из сводной таблицы.

Построим сводный график. Для этого возвращаемся на лист с исходными данными и щёлкаем по любой ячейке таблицы транзакций. В верхнем меню выбираем следующий пункт (рис. 11.1):

Insert — Charts — PivotChart.

Рис. 11.1. Вставка сводного графика

Задание. Наведите курсор на пункт PivotChartи прочитайте всплывающую подсказку.

Нам предлагается выбор (рис. 11.2):

— PivotChart — только сводный график;

— PivotChart & PivotTable — сводный график и сводная таблица.

Рис. 11.2. Выбор сводного графика

Для начала построим только сводный график.

Выбираем PivotChartи получаем диалоговое окно Create PivotChart(рис. 11.3). Всё очень похоже на построение сводной таблицы. Исходные данные в разделе Select a table or rangeуже выбраны — это наша таблица под названием «Транзакции». Программа это определила сама, потому что мы щёлкнули по этой таблице.

Размещение графика на новом листе:

Choose where you want the PivotChart to be placed — New Worksheet.

Рис. 11.3. Параметры сводного графика

Задание. Вставьте сводный график.

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

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

Аналогично показана работа со сводным графиком: сам график и настройка полей.

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

Рис. 11.4. Расположение таблицы и графика

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

Щёлкаем по сводной таблице и получаем меню PivotTable Fields. Щёлкаем по сводному графику и получаем меню PivotChart Fields. Внешне эти инструменты настройки полей очень похожи (рис. 11.5).

Однако, есть и различия. Вместо строк Rowsуказаны категории Axis (Categories). Вместо столбцов Columnsна графике указан ряд данных Legend (Series). Эти названия намекают, что выбранные поля будут выступать в роли координат по оси «икс» и по оси «игрек» на трафике.

Рис. 11.5. Настройка полей таблицы и графика

Задание. Сравните меню настройки полей для сводной таблицы и сводного графика.

Теперь настроим сводный график. Начинаем перетаскивать поля. Делаем первое, что приходит в голову (рис. 11.6) — магазины по оси «икс» и количество проданных товаров килограммах по оси «игрек»:

— Axis (Categories) — город и магазин

— Legend (Series) — килограммы.

Рис. 11.6. Настройка сводного графика

Программа нас о чём-то пытается предупредить (рис. 11.7). Мы получаем громоздкую таблицу и пустой график.

Рис. 11.7. Ограничения по данным

Задание. Создайте сводный график в соответствии с рис. 11.6 и обратите внимание на предупреждение и на вид сводной таблицы.

Попробуем исправить ситуацию. Перетаскиваем поле «Количество товара» из раздела Legend (Series)в раздел Values(рис. 11.8). Программа автоматически переходит к вычислению суммы. Сумма по данному полю — это общее количество проданных товаров в килограммах. Нас это устраивает.

Рис. 11.8. Настройка полей сводного графика

Теперь на экране появилась осмысленная таблица и соответствующий график (рис. 11.9). Мы настроили график, и вместе с ним изменилась таблица.

Рис. 11.9. Таблица и график

Задание. Настройте сводный график в соответствии с рис. 11.8.

А что будет при изменении сводной таблицы? Сейчас попробуем. Щёлкаем по сводной таблице. В меню PivotTable Fieldsубираем количество товаров из раздела Values и перетаскиваем сюда стоимость товаров. Снова видим сумму (рис. 11.10).

Рис. 11.10. Поля сводной таблицы

Вместе со сводной таблицей изменяется и сводный график (рис. 11.11). Теперь здесь тоже общая стоимость товаров в рублях. Конечно, оформление оставляет желать лучшего, но мы этим скоро займёмся.

Рис. 11.11. Сумма по стоимости

Задание. Измените настройки полей сводной таблицы в соответствии с рис. 11.10 и обратите внимание на изменение сводного графика.