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

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

Задание. Изучите карту России в Википедии в статье «Федеральные округа Российской Федерации».

Карту мы успешно посмотрели, теперь можно подготовить справочник городов. Выбираем три округа и выясняем, какие в них входят республики и области. А в этих регионах какие есть города. Попутно узнаем, административные центры этих ФО:

– Центральный (ЦФО) – Москва;

– Северо-Западный (СЗФО) – Санкт-Петербург;

– Южный (ЮФО) – Ростов-на-Дону.

Наша задача очень условная и не строгая. Мы просто попытаемся смоделировать федеральную сеть продовольственных магазинов.

Чтобы узнать состав каждого ФО, перейдем по ссылкам и ознакомимся с описанием соответствующего ФО (рис. 4.5). Точно так же можно узнать состав каждой области, перейдя по ссылкам.

Рис. 4.5. Состав ФО

Задание. Выберите ФО и ознакомьтесь с их составом.

Задание. Выберите регионы в составе ФО и ознакомьтесь с их административно-территориальным делением.

Мы посмотрели на карту местности и теперь можем переходить к творчеству. Сформируем справочник регионов и городов (рис. 4.6). Назначим простое и понятное название нашему справочнику – для дальнейшей работы.

Мы получили 3 ФО по 2 региона по 2 города, то есть общее количество городов равно

3*2*2=12.

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

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

Рис. 4.6. Справочник городов

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

ДИНАМИКА – это изменение чего-то во времени. Какого-то статистического признака. Отдельные значения прявязаны к моментам времени. В нашем случае сведения о каждой покупке привязаны к конкретным датам.

В динамике выделяют три компонента, три составные части:

– тренд;

– сезонные колебания;

– случайная составляющая.

Компоненты динамики – это составные части. Это «кирпичики», из которых строится конкретная модель. Компоненты – это разные части. Очень разные. Они не похожи друг на друга. Ихтрудно перепутать. Только если очень постараться.

Для анализа, пронозирования и имитационного моделирования динамики используют два вида моделей:

Аддитивная модель – сумма компонентов.

Мультипликативная модель – произведение компонентов.

Задание. Прочитайте в учебнике «Теория статистики» раздел «Динамика» и выясните, как выглядят компоненты динамики и модели динамики.

Задание. Сделайте зарисовки графиков трёх компонентов динамики и двух видов моделей динамики.

Далее сформируем ТРЕНД. Это долгосрочная тенденция. Общее направление изменений. В нашей модели тренд – это количество товара в одном чеке, в одной покупке. Будем моделировать постепенный рост покупок в течение нескольких лет.

Для нашей модели тренда нам понадобится два значения из параметров задания:

– Начало – начальное значение на линии тренда;

– Конец – последнее значение на линии тренда;

– Период – интервал времени в годах.

В нулевом варианте мы получили такие параметры:

– Начало = 2;

– Конец = 4;

– Период = 2.

Мы будем моделировать данные за два последних года. На момент написания пособия текущий год 2020. Так что возьмём данные за 2018—2019 годы. То есть наш интервал времени такой:

01.01.2018 – 31.12.2019.

Задание. Определите интервал дат для моделирования.

Напомним, что дата в пакете Excel хранится как порядковый номер дня. Мы будем моделировать даты как целые случайные числа. Но для этого нам нужно определить, какие номера дней соответствуют нашим датам.

Запишем даты начала и конца интервала моделирования. Будем вводить даты так, что Excel догадался, что это даты, а не просто какой-то текст:

2018-01-01

2019-12-31.

Даты распознали как даты, и они выводятся в формате даты.

Скопируем даты в соседние ячейки и установим общий формат (рис. 4.7):

Format Cells – Number – Category – General.

Получаем номера дней:

– начало интервала = 43101

– конец интервала = 43830.

Рис. 4.7. Даты интервала моделирования

Задание. Определите номера дней для своего интервала.

Сделаем зарисовку —как должен выглядеть график. Как должна проходить линия тренда. Рисуем от руки на бумаге, фотографируем и вставляем в наш отчёт (рис. 4.8).

Работу с зарисовками мы уже обсуждали в предыдущих работах. Главное – нужно мысленно представлять себе, что мы ожидаем получить. Тогда можно будет обнаружить грубые ошибки.

Рис. 4.8. Зарисовка линии тренда

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

Далее нам понадобится построить уравнение тренда для моделирования. Добавим в нашу табличку новую колонку – значения на линии тренда:

Начальное значение = 2

Конечное значение = 4.

Выделяем колонки дней и значений и строим график:

Insert – Charts – Insert Scatter (X, Y) or Bubble Chart – Scatter – Scatter with Straight Lines.

Всплывающая подсказка сообщает, что такой график используют, когда есть два набора данных (рис. 4.9). У нас как раз два набора – дни и значения.

Рис. 4.9. Вставка диаграммы

Задание. Постройте график тренда.

Появляется график (рис. 4.10). Рассмотрим его поподробней. Вид довольно странный. Это не совсем то, что мы хотели и что мы ожидали. Даже совсем не то. Мы хотели увидеть номера дней по оси «иксов», а значения по оси «игреков».

Вот для чего нужна зарисовка! И просто мысленное представление будущих результатов. Мы сразу заметили отличие от того, что должно быть.

В таблице данных для графика выделены две строки. То есть Excel решил, что у нас данные расположены по строкам, а не по столбцам.