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

Далее в диалоговом окне Move or Copyуказываем рабочую книгу с нашим отчётом по работе

To book.

Выбираем вариант вставки

move to end.

Рис. 10.2. Перемещение листа в отчёт

Задание. Загрузите данные и перенесите в отчёт.

Рассмотрим загруженный материал. В наших данных есть три неинформативных столбца:

;

;

.

В них записано одно и то значение. Это много данных. Естественно, это не добавляет нам никакой полезной информации. Кстати, перед нами пример явного отличия данных от информации.

Удаляем эти ненужные столбцы. Тикер и период уже указаны в названии вкладки.

Выделяем полученный массив и объявляем его таблицей с заголовками (рис. 10.3). Задаём информативное название для таблицы как объекта Excel.

Мы это уже проделывали, тут всё должно быть знакомо.

Рис. 10.3. Таблица исторических данных

Задание. Оформите таблицу исторических данных.

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

Выбираем следующие поля для анализа:

— по строкам — даты;

— по столбцам — цены закрытия и объёмы торгов .

Настраиваем расчёт средних значений, вводим простые заголовки таблицы и задаём формат вывода (рис. 10.4).

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

Рис. 10.4. Сводка по акциям Магнита

Задание. Постройте сводную таблицу по котировкам акции, как описано выше.

В сводной таблице время по умолчанию сгруппировано так же, как и в предыдущем примере: год — квартал — месяц. Что будет, если оставить только месяцы? Останутся только месяцы (рис. 10.5). Но теперь данные за все январи оказались в одной строке. Конечно, это нам не подходит.

Рис. 10.5. Результаты группировки по месяцам

Задание. Настройте группировку по месяцам. Убедитесь, что данные не имеют смысла.

Итак, мы убедились, что рассматривать только месяцы неправильно. Настроим группировку, чтобы были годы и месяцы. Отключаем подсчёт общих итогов и итогов по годам (рис. 10.6).

Рис. 10.6. Группировка по годам и месяцам

Задание. Настройте группировку по годам и месяцам.

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

Insert — Charts — Inset Scatter (X, Y) — Scatter —Scatter with Straight Lines.

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

Рис. 10.7. График по данным из сводной таблицы

Задание. Попробуйте построить «линейчатый» график (X, Y) по данным из сводной таблицы. Обратите внимание на сообщение программы.

Нам предложили попробовать другой тип графика. Щёлкаем по любой ячейке с показателями. Выбираем вставку столбиковой диаграммы:

Insert — Charts — Insert Column or Bar Chart — 2D Column —Clustered Column.

На этот раз график появился (рис. 10.8).

Рис. 10.8. График по итоговым показателям

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

Судя по оформлению, это не простой график. В нём есть дополнительные элементы:

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

Кнопки «Годы» и «Дата» явно предлагают вызвать дополнительные функции.

В правом нижнем углу кнопки [+]и [-]напоминают уже знакомые нам кнопки работы с иерархией.

Нажимаем кнопку «Годы» и получаем меню настройки фильтра по годам (рис. 10.9).

Нажимаем кнопку «Дата» и получаем меню настройки фильтра по месяцам.

Рис. 10.9. Меню фильтрации

Задание. Ознакомьтесь с меню фильтрации по годам и по датам.

Теперь поработаем с кнопками [+]и [-]. Подводим курсор к кнопке [-]и видим всплывающую подсказку, что это Collapse Entire Field. То есть сворачивание детализации (обобщение данных). Нажимаем на эту кнопку и получаем график только по годам (рис. 10.10). Сводная таблица тоже свернулась до показателей по годам.

Рис. 10.10. Обобщение данных

Задание. Нажмите кнопку [-]и получите обобщение данных на графике.

Вторая кнопка для работы с иерархией [+]. Всплывающая подсказка говорит, что это Expand Entire Field.То есть это разворачивание детализации. Нажимаем кнопку [+]и видим детализацию на графике и в сводной таблице (рис. 10.11).

Рис. 10.11. Детализация на графике

Задание. Разверните детализацию на графике.

Получается, что график по данным из сводной таблицы позволяет нам работать с иерархией. Свернём детализацию, нажав правой кнопкой на заголовок строки сводной таблицы. В контекстном меню выбираем Expand/Collapse — Collapse Entire Field(рис. 10.12).

Рис. 10.12. Управление детализацией через сводную таблицу

Задание. Ознакомьтесь с управлением детализацией графика через сводную таблицу.

Сворачивание детализации данных в сводной таблице одновременно сворачивает детализацию на графике.

Теперь щёлкнем правой кнопкой по столбику на графике и вызовем в контекстном меню Expand/Collapse — Expand Entire Field(рис. 10.13). Во всех случаях мы видим тот же список из шести инструментов для управления детализацией.

Рис. 10.13. Детализация на графике

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

Но и это ещё не всё. Щёлкнем по графику и обратим внимание на меню настройки в правой части окна Excel. Заголовок меню PivotChart Fieldsговорит о том, что мы работаем со сводным графиком (рис. 10.14).

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

Обратим внимание на настройку полей для сводного графика:

— Axis (Categories) — Years, ;

— Legend (Series) — Values;

— Values — CLOSE, VOL.

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

Задание. Ознакомьтесь с настройкой полей сводного графика. В разделе Valuesвызовите пункт Value Field Settingsи выясните, какой метод агрегирования указан для полей CLOSE и VOL.

Попробуем немного улучшить график. Щёлкнем правой кнопкой по любому столбику на графике и в контекстном меню вызовем пункт Change Series Chart Type(рис. 10.15).

Рис. 10.15. Изменение типа диаграммы

В диалоговом окне Change Chart Typeвыбираем комбинированную диаграмму Combo. Для ряда данных CLOSEнастраиваем тип графика Lineи дополнительную ось координат Secondary Axis(рис. 10.16). В окне предварительного просмотра мы сразу же видим результаты настройки Custom Combination.