Бизнес-аналитика. Сводные таблицы. Часть 2 - [5]

y (t) = T * S * E.

Рис. 5.2. Мультипликативная модель

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

Задание. Сгенерируйте количество товара.

Проверим, что получилось в результате моделирования. Построим диаграмму разброса «Дата — Количество» (рис. 5.3). Тренд — общая тенденция от 2 до 4. Сезонность присутствует. Случайный разброс увеличивается вместе с ростом среднего значения. Пока грубых ошибок не обнаружено. Вычисляем стоимость в рублях.

Убираем всё лишнее. Для этого копируем нужные колонки и вставляем как значения. Удаляем вспомогательные столбцы.

Рис. 5.3. Данные по количеству

Задание. Сгенерируйте данные по товарам.

5.3. Города

Следующий этап — ФО, регионы, города.

Начинаем с идентификатора города. В нулевом варианте это должно быть целое число от 1 до 12.

Генерируем равномерно распределённые числа. Опять с новым начальным состоянием. Округляем. Копируем и вставляем как значения.

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

Удаляем вспомогательные столбцы.

Задание. Сгенерируйте данные по городам.

5.4. Таблица транзакций

Все столбцы в таблице транзакций заполнены.

Нам остаётся оформить этот диапазон ячеек как таблицу Excel. И конечно же, присвоить таблице осмысленное название.

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

Задание. Оформите таблицу транзакций.

Построим диаграмму разброса «Дата-Стоимость» (рис. 5.4). Убедимся, что грубых ошибок нет.

На графике можно увидеть несколько «полос». Такое явление можно назвать «расслоением» графика или расслоением характеристик. Интересно, почему это произошло?

Рис. 5.4. Данные по стоимости

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

6. Условное форматирование

Мы построили сводную таблицу. С одной стороны, это инструмент для вычисления обобщённых, итоговых показателей. А с другой стороны, это первое средство визуализации. Самое простое. Просто вывести цифры на экран. Если цифр немного, да если их красиво расставить, то сразу можно увидеть общую картину. То есть как обстоят дела на предприятии. Ну а если сюда картинки добавить, то совсем хорошо станет. Из таких визуальных элементов и составляют информационные панели — мы к ним ещё вернёмся в следующих работах.

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

Задание. Откройте страницу данного учебного пособия на сайте Ридеро. На что вы обратили внимание в первую секунду — на картинку или на описание книги?

Для начала построим простую сводную таблицу. В предыдущей работе мы подробно рассмотрели, как это делается.

Щёлкнем по таблице транзакций и выберем в верхнем меню

Insert — Tables — PivotTable.

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

Create PivotTable.

На это раз программа сразу предлагает нам использовать данные из текущей таблицы Excel (рис. 6.1). Расположение новой сводной таблицы нам предлагается сделать на новом листе. Нажимаем ОК и получаем новый рабочий лист и меню настройки полей сводной таблицы.

Рис. 6.1. Создание сводной таблицы

Задание. Создайте сводную таблицу «ФО — Год — Количество товара».

У нас получается небольшая таблица. Сделаем заголовки проще и попонятнее (рис. 6.2). Это нужно, чтобы читателю не пришлось разгадывать наши ребусы.

Рис. 6.2. Сводная таблица

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

Выделяем диапазон ячеек с итоговыми показателями по годам. Выбираем в верхнем меню

Home — Styles — Conditional Formatting

Начало — Стили — Условное форматирование.

Для начала просто укажем на этот пункт меню и почитаем всплывающую подсказку (рис. 6.3). Нам сообщают, что если выделить особо важные значения показателей, то будет легче обнаружить тренды и закономерности.

Рис. 6.3. Условное форматирование

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

6.1. Выделение цветом

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

Highlight Cell Rules.

Для работы с числовыми значениями нам предлагают следующие варианты (рис. 6.4):

— Greater Than (Больше, чем);

— Less Than (Меньше, чем);

— Between (Между);

— Equal To (Равно).

Рис. 6.4. Правила выделения значений

Задание. Ознакомьтесь со списком правил выделения ячеек и выясните, как можно выделять текстовые значения и даты.

Разберём первый способ выделения — «Больше, чем заданное значение». Выбираем пункт

Greater Than.

На экране появляется одноимённое диалоговое окно (рис. 6.5). Программа автоматически выбирает пороговое значение, выше которого срабатывает выделение цветом. В нашем примере это число

5171,863.

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

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