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

Щёлкнем по любому маркеру наших данных на графике — по любой точке.

В сводной таблице выделяются диапазоны ячеек, которые использованы в графике. Можно видеть, что теперь нашим вниманием охвачены не все ячейки (рис. 6.33).

Рис. 6.33. Диапазон данных для графика

Задание.Рассмотрите выделенный диапазон данных для графика.

Исправим это упущение. Растянем выделение диапазона за уголок на все интересующие нас ячейки (рис. 6.34).

Рис. 6.34. Полный диапазон данных

Графа «Всего» в нижней строке и крайней правой колонке нам ничего полезного не сообщает. Удалим её.

Щёлкаем правой кнопкой мыши по заголовку строки «Всего» и выбираем в контекстном меню удаление общего итога (рис. 6.35):

Remove Grand Total.

Аналогично поступим с итоговой колонкой.

Рис. 6.35. Удаление итогов

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

Таблица стала более компактной.

Щёлкаем по графику и видим выделение наших данных (рис. 6.36).

Теперь вроде всё хорошо.

Но как говорят Сибирские сказители: «Хорошо — хорошо, да не очень-то».

Рис. 6.36. Сводная таблица без итоговых показателей

Задание.Рассмотрите выделенный диапазон данных и выясните, что необходимо исправить.

А теперь внимание, правильный ответ: здесь не нужны итоговые значения по годам. Мы обрабатываем только данные за месяц. Среднее за год нас пока не интересует.

Щёлкаем правой кнопкой по заголовку строки «Годы» и снимаем в контекстном меню выбор промежуточных итогов (рис. 6.37):

Subtotal «Years».

Рис. 6.37. Удаление промежуточных итогов

Задание.Удалите промежуточные годовые итоги.

Рассмотрим график (рис. 6.37) ещё раз.

Наши данные должны занимать всё поле графика.

Точки не должны ютиться в дальнем углу.

Ценность графика в том, чтобы увидеть картину в целом, распознать закономерность — если она, конечно, есть.

Настроим масштаб по осям.

Щёлкаем по оси.

Появляется боковое меню настройки параметров оси:

Format Axis — Axis Options — Axis Options — Bounds — Minimum / Maximum.

Выбираем пределы изменения значений по осям так, чтобы наши точки занимали весь график (рис. 6.38).

Рис. 6.38. Масштаб по осям

Задание.Настройте масштаб по осям.

Рассмотрим полученную диаграмму разброса (рис. 6.38).

Точки разбросаны широко. Небольшая корреляция, конечно присутствует.

Здесь может быть ещё кое-что. Добавим соединение точек прямыми отрезками.

Щёлкаем по диаграмме правой кнопкой и выбираем в контекстном меню изменение типа диаграммы (рис. 6.39):

Change Chart Type — X Y (Scatter) — Scatter with Straight Lines and Markers.

Рис. 6.39. Выбор типа диаграммы

Задание.Настройте тип графика.

Второй вариант — настройка графика. Щёлкаем по любому маркеру и переходим в раздел

Format Data Series — Fill & Line — Line — Solid line.

Теперь можно увидеть «траекторию» движения (рис. 6.40).

Рис. 6.40. Диаграмма с прямыми линиями

Задание.Измените тип диаграммы и обратите внимание на возможную закономерность.

Добавим на наш график исходные ежедневные данные.

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

Select Data.

В диалоговом окне

Select Data Source

в разделе

Legend Entries (Series)

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

Add.

Выбираем в загруженной объединённой таблице столбец цен закрытия Газпрома по оси «иксов» и Норникеля по оси «игреков».

Получаем комбинированную диаграмму.

Настраиваем маркеры (серые, небольшого размера) и убираем линии (рис. 6.41):

Format Data Series — Series Options — Fill & Line — Line — No line.

Format Data Series — Series Options — Fill & Line — Marker — Marker Options — Built-in — Size — 2.

Fill — Solid fill — Color — Light Gray.

Border — No line.

Можно видеть траекторию, на которой одна бумага отстаёт от изменений второй. В этом конкретном примере «металлы» и «газ» изменяются почти «в противофазе». Это явление обычно учитывают в моделях динамики за счёт введения запаздывания.

Рис. 6.41. Комбинированная диаграмма

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

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

Построим новую сводную таблицу на новом листе.

В этот раз оставим группировку по годам и дням. То есть данные не будут сгруппированы вообще.

Удаляем общие и промежуточные годовые итоги.

Мы получили слегка изменённую объединённую таблицу (рис. 6.42).

Теперь у нас котировки по каждой бумаге расположены в отдельных колонках. Так будет немного легче работать.

Рис. 6.42. Данные по бумагам без группировки

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

Построим диаграмму динамики

Insert — Charts — Insert Line or Area Chart — 2D Line — Line.

В сводной таблице отключаем промежуточные годовые итоги.

Выбираем данные для графика.

Это будут два разных набора данных их разных колонок.

По оси «иксов» пока ничего не указываем. Здесь отображаются порядковые номера точек.

Поскольку уровни цен разные, настроим раздельные оси для каждого графика.

Format Data Series — Series Options — Series Options — Plot Series On — Secondary Axis.

Задаём масштаб по каждой оси отдельно, чтобы на графике не было пустого места (рис. 6.43).

Горизонтальную ось отключаем:

Primary Horizontal

На таком графике можно видеть развитие событий во времени.

Рис. 6.43. Оформление графика

Задание.Постройте диаграмму динамики.

Мы рассмотрели процедуру анализа для одной пары бумаг.

Теперь нужно проделать описанные шаги для остальных возможных сочетаний. Для начала попробуйте определить, сколько вариантов нам предстоит рассмотреть. СОЧЕТАНИЕ — это понятие из области комбинаторики.

Задание. Прочитайте в Википедии статью «Сочетание» и определите количество сочетаний из пяти бумаг по две акции.

В специализированных пакетах программ есть возможность проводить анализ взаимосвязи сразу по списку из нескольких переменных. Результаты такого анализа — «матрица» из нескольких графиков — гистограмм и диаграмм разброса.

Задание.Найдите в Картинках Яндексаизображения на тему correlation matrix plot.

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

Задание. Проведите анализ взаимосвязи для трёх сочетаний (по две акции).

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

Функции ETL настраивают в виде сценариев или процедур так, чтобы затем многократно их использовать. Когда в источнике данных происходят изменения, эти новые данные загружаются в систему бизнес-аналитики автоматически. Или по требованию пользователя: