Валентин Арьков - Бизнес-аналитика. Извлечение, преобразование и загрузка данных

Закономерности в поведении случайной величины описываются, прежде всего, РАСПРЕДЕЛЕНИЕМ. Это частота (вероятность) появления разных значений. Оценка формы кривой распределения делается с помощью гистограммы. А гистограмма строится по результатам группировки данных.

Мы проведём группировку данных по объёмам для каждой бумаги и построим гистограммы распределения.

Используем правило Стёрджеса:

n = 1 +3,322 lg N, где

n — число интервалов группировки;

N — объём выборки;

lg — десятичный логарифм.

В нашем примере мы загрузили по 253 строки для каждой бумаги.

Получаем:

n = 1 +3,322 * lg 253 = 1 +3,322 * 2,4 = 9.

Получается такая рекомендация: группировать данные по 9 интервалов.

Задание. Определите число интервалов группировки.

Следующий шаг группировки данных — определить наименьшее и наибольшее значения, а также размер интервала.

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

Щёлкаем по объединённой таблице и выбираем в верхнем меню вставку сводной таблицы (рис. 6.1):

Insert — Tables — PivotTable.

Рис. 6.1. Вставка сводной таблицы

Настраиваем поля сводной таблицы следующим образом:

— Rows — по строкам — тикер;

— Values — значения — объём торгов.

В поле Значениямы перетаскиваем объёмы VOLдва раза.

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

Рис. 6.2. Минимум и максимум

Задание.Найдите наибольшие и наименьшие значения по каждой бумаге.

Вычисляем размер интервалов.

Вводим простую формулу:

(Vmax — Vmin) / 9.

Excel автоматически вставляет вызов функции, которая ссылается на ячейки сводной таблицы (рис. 6.3):

GETPIVOTDATA(рис. 6.3).

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

Однако, все результаты оказались одинаковыми.

Единственный адрес, который есть в формуле, — это адрес сводной таблицы. Данные из этой сводной таблицы извлекаются по значению поля, а не по адресу ячейки.

Рис. 6.3. Копирование формулы

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

Копировать формулы не удалось. Значит, нам придётся вручную сформировать каждую формулу (рис. 6.4).

Теперь в каждой формуле появляются ссылки на разные значения поля «Тикер».

Рис. 6.4. Ссылки на ячейки сводной таблицы

Задание. Введите формулы вычисления интервала вручную. Обратите внимание на ссылки на ячейки сводной таблицы.

Переходим к группировке данных.

Создаём новую сводную таблицу.

Настраиваем поля следующим образом (рис. 6.5):

— Rows — VOL;

— Values — VOL;

— Filters — TICKER.

По строкам у нас будут значения объёмов. Далее мы их сгруппируем по интервалам.

Обобщённый показатель — количество значений по полю « Объём». Метод агрегирования выбираем вручную.

Фильтрация по полю « Тикер» позволит нам выбрать данные только по одной ценной бумаге.

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

Задание. Создайте сводную таблицу (рис. 6.5).

Устанавливаем фильтрацию по выбранному значению тикера (рис. 6.6).

Теперь мы обрабатываем данные только по одной акции.

Рис. 6.6. Фильтр по одной бумаге

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

Щёлкаем по заголовку любой строки и вызываем группировку в контекстном меню:

Group(рис. 6.7).

Рис. 6.7. Вызов группировки данных

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

Нам остаётся вручную ввести величину интервала группировки. Введём округлённое значение (рис. 6.8).

Нажимаем ОК.

Рис. 6.8. Параметры группировки данных

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

Рассмотрим результаты группировки (рис. 6.9).

Длинные заголовки пока не будем трогать. При желании можно перевести числа в миллионы вместо единиц.

В графе Countвыводится количество заполненных ячеек. Общее количество равно 253, как и было в исходных данных.

Рис. 6.9. Результаты группировки данных

Переводим абсолютные частоты (количество заполненных ячеек) в относительные частоты, выраженные в процентах (рис. 6.10).

Щёлкаем правой кнопкой по ячейке в графе « Количество» и выбираем в контекстном меню вывод в процентах от общего итога:

Show Values As — %of Grand Total.

Рис. 6.10. Относительные частоты

Задание. Настройте вывод частоты в процентах.

Зададим уникальное, краткое и информативное название нашей сводной таблице:

PivotTable Tools — Analyze — PivotTable — PivotTable Name.

Укажем, что это объёмы торгов акциями Газпрома (рис. 6.11):

GAZP_VOL.

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

Задание. Присвойте короткое информативное название сводной таблице.

Рассмотрим список именованных объектов (рис. 6.12):

Formulas — Defined Names — Name Manager.

Как ни странно, ни одной сводной таблицы в этом списке нет. Здесь есть только «обычные» таблицы Excel, которые были отформатированы как таблицы.

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

Рис. 6.12. Названия объектов

Задание. Просмотрите список названий объектов (таблиц). Присвойте таблицам Excel простые, короткие, понятные названия. Обратите внимание на отсутствие сводных таблиц в этом списке.

Мы провели группировку данных. Теперь по результатам группировки построим гистограмму (рис. 6.13).

Выбираем в верхнем меню вставку столбиковой диаграммы:

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

Рис. 6.13. Вставка гистограммы

Щёлкаем правой кнопкой по пустому прямоугольнику будущего графика и выбираем в контекстном меню:

Select Data.

Появляется диалоговое окно

Select Data Source.

В разделе

Legend Entries (Series)

нажимаем кнопку

Add(рис. 6.14).

Рис. 6.14. Добавление данных для графика

Выбираем столбец относительных частот в диалоговом окне

Edit Series(рис. 6.15).

Итоговое значение 100% нам не требуется.

Заголовок столбца тоже не выделяем.

Рис. 6.15. Выбор относительных частот

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

Обратим внимание на строку

Series Values(рис. 6.15).

Здесь указано название страницы и выбранный диапазон адресов ячеек.