Бизнес-аналитика. Сводные таблицы. Часть 2. Учебное пособие

Валентин Юльевич Арьков

Данная работа посвящена дальнейшему изучению методов бизнес-аналитики на примере «продвинутых» возможностей функций сводных таблиц.Мы продолжаем использовать общий подход к практическому освоению программного пакета: моделирование и исследование. Мы поработаем с генератором случайных чисел и сформируем реалистичные наборы данных для анализа.

5. Имитационное моделирование

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

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

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

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

5.1. Даты

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

Data — Analysis — Data Analysis — Random Number Generation.

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

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

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

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

5.2. Товары

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

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

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

VLOOKUP

ВПР.

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

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

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

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

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

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

y (t) = T * S * E.

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

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

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

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

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

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

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

5.3. Города

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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