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

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

В школе нас учили, что прямую можно провести только через две точки. И это правда. С одной оговоркой: если линия должна пройти через точки в точности. А вот регрессия проходит В СРЕДНЕМ.

Рис. 7.18. Линия тренда

Задание. Постройте диаграмму разброса и нанесите на неё линию тренда.

Мы получили значения, лежащие на линии тренда. Если выразиться точнее, это наши ОЦЕНКИ. Ведь мы вычисляли параметры уравнения по реальным данным. Для другого набора данных мы получим немного другое уравнение.

Вернёмся к исходному, «теоретическому» уравнению тренда из раздела 4.4:

y = —116,25 +0,0027435 t .

Сравним его с нашей оценкой:

y = —133,11 +0,0031316 t .

Числа получились близкие, похожие. Но всё-таки есть небольшая разница. Это говорит о том, что у нас по крайней мере нет ГРУБЫХ ОШИБОК. Грубые ошибки — это разница в десятки раз и более или это плюс вместо минуса. Здесь такого нет.

Если ещё раз смоделировать набор данных с другой настройкой генератора случайных чисел, мы получим немного другое уравнение. Хотя тоже очень похожее.

Это и есть то, что называется словом «оценки». Вычисления точные, а результат получается приблизительный. Да ещё и разный каждый раз.

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

Теперь найдём отклонения от нашей экспериментально полученной линии тренда.

Вставляем новую колонку «Колебания» в таблицу транзакций.

Вводим формулу для вычисления отклонений. Это просто разность. Из текущего значения количества товара вычитаем значение на линии тренда (рис. 7.19).

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

Рис. 7.19. Отклонения от тренда

Задание. Вычислите отклонения от тренда.

Построим новую сводную таблицу «ФО — Год — Колебания спроса».

Способ агрегирования — среднее значение.

Если новое поле «Колебания» не появилось в списке полей, вызываем обновление сводной таблицы.

Настраиваем вывод данных по кварталам.

Удаляем колонку общих итогов.

Задаём формат числового вывода.

Для каждой строки используем свой тип спарклайна.

Рис. 7.20. Типы спарклайнов

Задание. Создайте описанную выше сводную таблицу и вставьте разные типы спарклайнов.

Сравним полученные мини-графики.

Форма колебаний во всех случаях одинаковая.

Каждый тип графика акцентирует внимание на определённой стороне явления. В зависимости от решаемой задачи.

Первые два типа графиков — столбики и линии — учитывают и величину, и знак. Это нужно, если нас интересует само значение.

График «выигрыш-проигрыш» подчёркивает только переход от положительных значений к отрицательным. Величина здесь значения не имеет.

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

Мы познакомились со спарклайнами в самых общих чертах. С автоматическим выбором параметров графиков. По умолчанию.

Теперь посмотрим, что можно изменить и настроить в наших мини-графиках.

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

В верхнем меню появляется группа инструментов:

Sparkline Tools — Design(рис. 7.21).

Рис. 7.21. Инструменты настройки спарклайнов

Познакомимся с нашими орудиями труда.

Нажимаем на первую кнопку в группе Design:

Sparkline — Edit Data.

Здесь мы можем изменить данные для мини-графиков:

— Edit Group Location & Data — для группы спарклайнов;

— Edit Single Sparkline’s Data — для одного графика;

— Hidden and Empty Cells — работа с незаполненными и скрытыми ячейками.

Наши графики в этом примере не сгруппированы. То есть вся группа состоит из одного графика.

Нажимаем первую кнопку

Рис. 7.22. Управление данными

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

Edit Sparklines(рис. 7.23).

Здесь мы можем изменить диапазон ячеек для графика Data Range.

При желании мы можем задать новую ячейку для размещения графика Location Range.

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

Рис. 7.23. Изменение данных для спарклайна

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

Для следующего опыта выбираем второй спарклайн.

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

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

Теперь рассмотрим группу кнопок Show(Показать). Здесь мы можем указать, какие значения на графике нам хотелось бы выделить:

— High Point(Максимальное значение);

— Low Point(Минимальное значение);

— Negative Points(Отрицательные значения);

— First Point(Первое значение);

— Last Point (Последнее значение);

— Markers(Маркеры).

Названия достаточно понятные.

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

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

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

Следующий раздел настройки — Style(Стиль). Здесь мы можем выбрать цветовое оформление диаграммы и маркеров.

Самая простая настройка — это выбор из готового списка (рис. 7.24).

Обратим внимание, как меняются готовые шаблоны оформления графика при выборе инструментов в разделе Show. На шаблонах графиков появляются выбранные маркеры.

Рис. 7.24. Шаблоны оформления

Если недостаточно готовых цветовых схем, можно сделать индивидуальные настройки (рис. 7.25).

Кнопка Sparkline Color позволяет установить любой цвет и толщину линии для выбранного графика.

Рис. 7.25. Индивидуальные настройки спарклайна

Задание. Выберите цвет маркера из стандартной палитры Standard Colors.

Задание. Настройте толщину линии для графика.

Кнопка Marker Colorпозволяет настроить цвета для выделения тех самых минимальных, максимальных и других особых значений — которые мы рассмотрели в разделе Show.

Здесь предлагается стандартный цветов набор (рис. 7.26)

Рис. 7.26. Выбор цвета маркера

При большом желании можно настроить любой цвет — буквально из миллионов цветов. Палитра RGB позволяет многое.