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

У нас всё готово для моделирования исходных данных. Как и в предыдущей работе, мы создаём таблицу транзакций. Во всех вариантах у нас будет 10000 записей, то есть строк.

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

Random Seed — Случайное рассеивание.

Создаём таблицу транзакций в привычном порядке.

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

Data — Analysis — Data Analysis — Random Number Generation.

Задаём параметры генератора (рис. 5.1).

Рис. 5.1. Настройки генератора

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

Задание. Сгенерируйте столбец дат.

Следующий этап — товары. Начинаем с идентификатора товара. Целое число от 1 до 6. Не забываем установить новое состояние генератора случайных чисел.

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

Далее используем функцию

VLOOKUP

ВПР.

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

Задание. Подставьте данные из справочника товаров.

Мы подставили постоянные цены на товары. Добавим к ценам сезонные колебания (рис. 5.2). Это элемент мультипликативной модели. Мы умножаем постоянное значение цены на сезонную составляющую.

Рис. 5.2. Сезонные колебания цен

Задание. Сгенерируйте сезонность цен.

Затем генерируем количество товара с помощью мультипликативной модели:

y (t) = T * S * E.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Insert — Tables — PivotTable.

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

Create PivotTable.

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

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

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

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

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

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

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

Home — Styles — Conditional Formatting

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

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

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

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

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

Highlight Cell Rules.

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

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

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

— Between(Между);

— Equal To (Равно).