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

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

Проверяем, что у нас получилось (рис. 7.8).

Любой спарклайн выделяется отдельно. Никаких синих рамочек не появляется. К тому же, кнопки Groupи Ungroupстаи неактивными. Они стали бледно-серыми, и нажать на них не получится. Значит каждый отдельный спарклайн не больше не считается членом группы.

Рис. 7.9. Успешная разгруппировка

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

Спарклайны — это не просто маленькие графики. Это фоновое изображение. И оно никак не связано с содержимым ячейки. Мы можем, например ввести в ячейку какой-нибудь текст. Или вообще оставить ячейку пустой.

Создадим группу спарклайнов за пределами сводной таблицы (рис. 7.10). Введём в эти ячейки какой-нибудь текст. Пусть это будут названия федеральных округов.

Настроим цвет и размеры ячеек.

Рис. 7.10. Спарклайны и текст

Задание. Постройте спарклайны в свободных ячейках и введите в эти ячейки текст.

Итак, спарклайны — это не содержимое ячейки. Попробуем удалить график. Щёлкаем по ячейке с последним мини-графиком за пределами таблицы нажимаем клавишу Delete. Мы удалили текст, а график остался.

Задание. Попробуйте удались спарклайн, нажав Delete.

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

Выделяем наши последние мини-графики за пределами сводной таблицы. Щёлкаем по ним правой кнопкой мыши.

Выбираем в контекстном меню пункт

Sparklines — Clear Selected Sparklines(рис. 7.11).

Этот пункт называется даже не «удалить», а «очистить».

Это название намекает на то, что спарклайн — это не содержимое ячейки, а что-то другое.

Рис. 7.11. Удаление спарклайнов через контекстное меню

Задание. Удалите спарклайны, построенные по строкам, используя контекстное меню.

Спарклайны можно удалить через верхнее меню, то есть через ленту. Чтобы очистить спарклайны, используем следующий пункт:

Sparkline Tools — Design — Group — Clear — Clear Selected Sparklines(рис. 7.12).

Можно просто нажать кнопку Clear, не углубляясь в подробности.

Оба способа очистки работают одинаково хорошо.

Рис. 7.12. Очистка спарклайнов через верхнее меню

Задание. Удалите спарклайны, построенные по столбцам, используя верхнее меню.

Нам осталось познакомиться с третьим типом спарклайнов — Win/Loss. Буквально это означает «выигрыш или проигрыш».

Фактически здесь изображают данные очень упрощённо, в духе «плюс или минус». Положительные числа будут показаны столбиками, которые растут вверх. Отрицательные числа — столбики направлены вниз, ниже нуля. Высота столбиков будет одинаковой. Так что здесь даётся самая общая картина, вообще без подробностей.

Построим тестовую табличку (рис. 7.13).

Пусть у нас будут самые разные числа — большие и маленькие, положительные, отрицательные и нули.

Построим мини-график Win/Loss.

Немного увеличим размер ячейки.

Положительные числа изображены синими столбиками.

Отрицательные — красными.

Столбики одинаковой высоты, независимо от числового значения.

Нулевое значение — столбик отсутствует.

Рис. 7.13. Спарклайн Win/Loss

Задание. Создайте тестовую табличку и постройте спарклайн Win/Loss. Обратите внимание на одинаковую высоту столбиков.

На последнем мини-графике нет осей координат. Поэтому можно и не заметить отсутствие последнего столбика.

Добавим на спарклайн горизонтальную ось.

Щёлкнем по спарклайну.

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

Sparkline Tools — Design — Group — Axis — Show Axis(рис. 7.14).

На графике появляется горизонтальная ось. Без масштаба. Только ось.

Нам этого будет вполне достаточно.

Рис. 7.14. Отображение оси спарклайна

Задание. Включите отображение оси на спарклайне.

Вернёмся к нашей сводной таблице. Здесь только положительные числа.

Проведём несложные расчёты и исключим тренд из наших данных. Тогда мы сможем рассмотреть сезонные колебания и случайные отклонения.

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

Нас интересует количество товара в каждой сделке.

Построим уравнение тренда с помощью регрессионного анализа. Эту тему мы уже обсуждали в работе [2]. При необходимости можно освежить в памяти этот материал.

Вызываем надстройку «Анализ данных»:

Data — Analysis — Data Analysis — Regression.

Указываем исходные данные. Это наши столбцы «Дата» и «Количество». Выделяем столбцы вместе с заголовками.

Указываем, что у наших данных есть заголовки Labels(рис. 7.15).

Рис. 7.15. Параметры регрессионного анализа

После настройки параметров нажимаем ОК.

На экран выводятся результаты регрессионного анализа.

Сейчас нас интересуют только значения коэффициентов (рис. 7.16).

Рис. 7.16. Коэффициенты уравнения регрессии

Из полученных коэффициентов мы составим уравнение тренда.

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

Второй коэффициент — в строке «Дата». Это коэффициент при переменной «Дата». Другое название — коэффициент регрессии. Как мы помним, дата только выводится на экран в формате даты. На самом деле это просто порядковый номер дня. Целое положительное число.

Для дальнейшей работы оставляем по пять значащих разрядов в каждом числе.

Таким образом, уравнение тренда выглядит так:

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

Коэффициент при t положительный. Значит, с увеличением t результат возрастает.

Задание. Постройте уравнение тренда и запишите его в отчёт.

Проверим, насколько правильное у нас получилось уравнение тренда. Соответствует ли оно исходным данным.

Добавим колонку «Тренд» в таблицу транзакций.

Введём формулу — наше уравнение тренда (рис. 7.17).

Программа решила, что результат работы с датами — тоже дата. Настроим формат вывода:

Format Cells — Number —Category — Number — Decimal places = 3.

Рис. 7.17. Уравнение тренда

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

Построим диаграмму разброса:

Insert — Charts — Insert Scatter… — Scatter — Scatter.

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

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

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

Рассматриваем график (рис. 7.18).

Линия тренда проходит в среднем по множеству точек.