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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Далее в диалоговом окне 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.

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