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

Находим строки, которые раньше были продублированы.

Теперь дубликатов нет (рис. 5.33).

Очистка данных прошла успешно.

Рис. 5.33. Дубликаты отсутствуют

Задание. Изучите объединённую таблицу и убедитесь, что дубликатов больше нет.

Поработаем ещё немного с запросами.

Сохраним и закроем файл. Откроем его снова.

Чтобы перейти к запросам, нажмём кнопку просмотра списка запросов:

Data — Queries & Connections — Queries & Connections.

Прочитаем всплывающую подсказку (рис. 5.34).

Здесь нам поясняют разницу между двумя инструментами ETL.

Рис. 5.34. Запросы и соединения

Задание. Прочитайте всплывающую подсказку и выясните, что такое Queries, что такое Connectionsи для чего их используют.

Запросы к источникам данных записаны в виде программы. Это последовательность шагов извлечения, преобразования и загрузки данных.

Познакомимся с текстом запроса.

Вызываем редактор запросов.

Затем вызываем в верхнем меню продвинутую версию редактора запросов (рис. 5.35).

Home — Query — Advanced Editor.

Чтобы закрыть окно «улучшенного» редактора, можно нажать кнопку

Done.

Рис. 5.35. Текст запроса к данным

Задание. Запустите просмотр текста запроса и ознакомьтесь с программой. Сравните строки программы запроса и строки в списке применённых шагов запроса Applied Steps. Обратите внимание на параметры каждой команды.

Мы загрузили данные, теперь их можно анализировать.

У нас есть котировки и объёмы торгов по каждой бумаге. Мы будем проводить анализ распределения и взаимосвязей по этим данным с помощью сводных таблиц.

Это задание основано на знаниях и навыках, полученных в работах [6—10].

Напомним, что ОБЪЁМ торгов — это количество акций, которые сменили хозяев за рассматриваемый период. То есть измеряется объём в количестве акций, в «штуках». Стало быть, по определению это целое число. В нашем примере мы используем так называемые ДНЕВНЫЕ данные. То есть данные за один торговый день, это около девяти часов.

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

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

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

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

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).

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

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

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