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

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

Как мы уже отметили, третий показатель не имеет смысла. Третье поле — цена товара (рис. 8.4). Попробуем удалить это поле из списка.

Рис. 8.4. Поля для подсчёта итогов

Чтобы удалить поле из списка показателей, берём выбранное поле и перетаскиваем его обратно в список полей таблицы исходных данных (рис. 8.5).

Рис. 8.5. Удаление поля из списка показателей

Получаем приемлемый вид сводной таблицы.

Остаётся только отредактировать названия строк и столбцов (рис. 8.6).

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

Рис. 8.6. Окончательный вид сводной таблицы

Задание. Удалите лишнее поле и отредактируйте таблицу.

Мы описали использование рекомендованных сводных таблиц. Теперь пришло время попрактироваться в использовании готовых шаблонов.

Задание. Создайте сводные таблицы по всем рекомендованным шаблонам. Под каждой таблицей укажите, насколько она соответствует зравому смыслу и какие изменения в неё пришлось внести.

Наши исходные данные были привязаны ко времени. Точнее, к датам. Обобщённые показатели в сводных таблицах тоже привязаны ко времени. Познакомимся с этой стороной сводных таблиц.

Создадим новую сводную таблицу. Для этого переходим на страницу таблицы транзакций. Щёлкаем по любой ячейке таблицы. Выбираем в верхнем меню:

Insert — Tables — PivotTable(рис. 9.1).

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

Задание. Создайте новую незаполненную сводную таблицу.

Сформируем сводную таблицу. Для этого перетаскиваем поля в соответствующие разделы меню настройки полей (рис. 9.2). Мы перетащили поле «Дата» в раздел столбцов. Однако, здесь мы сразу же обнаруживаем три параметра: годы, кварталы и даты.

Рис. 9.2. Дата в сводной таблице

Задание. Установите поля для сводной таблицы, как показано на рис. 9.2.

Сводная таблица выводится на экран в свёрнутом виде (рис. 9.3). Это верхний уровень иерархии по датам. Перед нами только годы. Судя по кнопке [+], эти столбцы можно развернуть.

Рис. 9.3. Даты в сводной таблице

Развернём столбец показателей за 2015 год. Второй уровень иерархии — кварталы. Развернём кварталы — получаем месяцы (рис. 9.4). Здесь нет дней, возможность детализации уровня месяцев уже отсутствует.

Рис. 9.4. Детализация даты

Задание. Ознакомьтесь с уровнями иерархии по времени.

Удалим поля «Квартал» и «Дата» из списка полей. Теперь у нас в сводной таблице остались только годы (рис. 9.5). Нет никакой возможности детализации. Поэтому кнопок [+]больше не наблюдается.

Рис. 9.5. Годы без детализации

Задание. Удалите кварталы и даты из сводной таблицы.

Теперь изменим выбор полей для нашей сводной таблицы. Перетащим поля таким образом:

— по столбцам — названия городов;

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

Получаем похожие результаты: год — квартал — месяц (рис. 9.6).

Рис. 9.6. Даты по строкам

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

На самом деле, группировку по датам можно настроить как нам заблагорассудится. Щёлкнем правой кнопкой по любому заголовку строки. Появляется контекстное меню. Выбираем пункт Group(рис. 9.7).

Рис. 9.7. Группировка по выбранному полю

На экране появляется диалоговое окно настройки параметров группировки Grouping(рис. 9.8).

Мы можем указать даты начала и конца интервала для анализа данных в разделе Auto:

— Starting at;

— Ending at.

Ниже видим окно By — признаки для группировки. Здесь нам предлагают список вариантов для группировки:

— Seconds (Секунды);

— Minutes (Минуты);

— Hours (Часы);

— Days (Дни);

— Months (Месяцы);

— Quarters (Кварталы);

— Years (Годы).

Для выбора щёлкаем по интересующему пункту. Для снятия выбора щёлкнем по нему ещё раз.

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

Рис. 9.8. Варианты группировки по времени

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

Если выбрать только группировку по дням, у нас появляется возможность указать количество дней Number of days(рис. 9.9). Указываем период группировки 7 дней. Теперь каждая строка — это показатели за 7 дней, например, с 1 по 7 января, затем с 8 до 14 января и т. д. В английской версии программы дата выводится в американском формате: месяц — день — год.

Рис. 9.9. Группировка по дням

Задание. Установите группировку по 15 дней.

Установим привычный формат даты на русском языке. Для этого щёлкаем по любой ячейке заголовка строки и выбираем в контекстном меню Format Cells(рис. 9.10). Вроде бы уже выбран правильный формат.

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

Рис. 9.10. Настройка формата даты

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

Мы познакомились с группировкой дат. Теперь поработаем с реальными биржевыми данными. Методику загрузки биржевых данных на сайте компании «Финам» мы с вами уже рассмотрели в предыдущих работах [4—6].

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

Напомним, что базу расчёта индекса можно найти на сайте Московской биржи:

www.moex.com — Индексы — Все индексы — Индексы акций — Индекс голубых фишек — База расчёта.

Для нулевого варианта будем рассматривать акции сети «Магнит». Загрузим ежедневные данные по биржевым котировкам обыкновенных акций (сокращённо — ао) «Магнита» за четыре года, указанных в варианте задания. Тикер акции MGNT.

Для загрузки исторических данных переходим на сайт «Финама» и выбираем:

www.finam.ru — Теханализ — Экспорт котировок —Мосбиржа акции — Магнит ао.

Настраиваем параметры экспорта (рис. 10.1) и скачиваем файл на компьютер.

Рис. 10.1. Параметры экспорта котировок

Задание. Скачайте исторические биржевые данные в соответствии с вариантом задания.

Загружаем полученный файл в Excel. Методика загрузки тоже описана в предыдущих работах, и мы считаем, что студенты с ней уже знакомы.

После загрузки файла переместим рабочий лист в наш отчёт. Щёлкаем правой кнопкой по вкладке с названием загруженного файла. В контекстном меню выбираем Move or Copy.