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

В сводной таблице выделяются диапазоны ячеек, которые использованы в графике. Можно видеть, что теперь нашим вниманием охвачены не все ячейки (рис. 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).

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