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

Аналогично с городами и магазинами. В нулевом варианте мы сформируем списки из трёх городов по три магазина в каждом городе. Итого получим в общей сложности 3*3=9 магазинов.

Период — это продолжительность записи смоделированных данных — в годах. Начало моделирования — 1 января 2015 года. Окончание периода — 31 декабря. Соответственно, в нулевом варианте мы смоделируем данные за четыре года. То есть за период с 01.01.2015 по 31.12.2018. Мы будем моделировать только дату и не будем учитывать время.

В нашей «базе данных» будет 10000 строк (записей). Это будет 10000 покупок отдельных товаров разными покупателями. Мы выбираем не слишком большое и не слишком маленькое количество данных, чтобы познакомиться с работой системы. Это количество данных уже невозможно оперативно обрабатывать с приемлемой скоростью. С другой стороны, слишком большие объёмы данных не сможет обработать сам пакет Excel. Такой эксперимент мы проделаем чуть позже, чтобы увидеть явное замедление работы программы.

Кроме того, для упрощения будем считать, что в каждой покупке участвовал только один-единственный товар. Все эти «упрощения» нужны для того, чтобы познакомиться с ключевыми, главными шагами анализа. Более сложный, продвинутый вариант работы мы предлагаем магистрантам.

Задание. На новой странице отчёта опишите параметры своего задания.

При выполнении работы для имитационного моделирования используется генератор случайных чисел, который нам предлагает статистическая надстройка «Анализ данных». Чтобы активировать надстройку, необходимо вызвать в верхнем меню

File — Options.

В диалоговом окне Excel Optionsвыбираем вкладку Add-ins. Затем в выпадающем списке Manageвыбираем Excel Add-insи нажимаем кнопку Go(рис. 4.1).

Рис. 4.1. Управление надстройками

В диалоговом окне Add-insвыбираем «Пакет анализа»: Analysis ToolPak(см. рис. 4.2). Нажимаем OK.

Рис. 4.2. Включение надстройки

Убедимся, что надстройка активирована. В верхнем меню выбираем Dataи в разделе Analysisнаходим кнопку Data Analysis(рис. 4.3). Это и есть кнопка вызова нашей статистической надстройки.

Рис. 4.3. Надстройка в меню

Задание. Включите надстройку «Анализ данных» и убедитесь, что в разделе Analysis появилась кнопка вызова надстройки.

Мы смоделируем исходные данные для анализа с помощью генератора случайных чисел.

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

Задание. Сделайте зарисовку таблицы транзакций в соответствии с описанием — оформите шапку таблицы и заполните произвольными данными пару строк.

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

Задание. Возьмите любой кассовый чек и сделайте зарисовку логической модели данных (структуры базы данных).

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

Напомним, что дата выглядит для пользователя как три целых числа: год, месяц и день. Но в электронной таблице дата хранится просто как порядковый номер дня. Причём день номер 1 — это вовсе не начало нашей эры. Поэтому нам предстоит выяснить порядковые номера дней, а затем сгенерировать случайные числа в нужном диапазоне.

Выясним, какая дата будет первым днём по версии создателей электронной таблицы. Введём число 1 в ячейку таблицы. Щёлкнем правой кнопкой по этой ячейке и вызовем контекстное меню. Установим формат вывода — дата (рис. 5.1):

Format Cells — Number — Category — Date — Locale — Russian.

Type — 14-мар-2012.

При этом в разделе Sampleможно увидеть соответствующую дату «Дня Первого»:

1-янв-1900.

Рис. 5.1. «День Первый»

Нажимаем ОКи видим отображение даты в выбранном формате. При этом в строке формул выводится дата в американском стиле: месяц/день/год(рис. 5.2).

Рис. 5.2. Формат даты

Задание. Проверьте, какая дата соответствует числу 1.

Нам предстоит сгенерировать колонку целых чисел и превратить их в случайные даты в выбранном диапазоне. Вначале напомним, что в нулевом варианте мы работаем с данными за период с 01.01.2015 по 31.12.2018. Введём две указанные даты таким образом:

2015-1-1

2018-12-31

Excel распознал, что это даты и переключил формат отображения (рис. 5.3). При необходимости установите формат даты, принятый в нашей стране.

Рис. 5.3. Ввод даты

Выделим ячейки с датами в первом столбце и скопируем в буфер обмена:

Ctrl + C.

Выбираем ячейку B2и вставляем данные из буфера/ Для этого нажимаем правую кнопку мыши и выбираем в контекстном меню:

Paste Options — Values.

В этом случае вставляются только значения (рис. 5.4).

Рис. 5.4. Вставка значений

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

Можно поступить по-другому и использовать ссылки на ячейки. Введём во второй колонке ссылки на соседние ячейки, чтобы продемонстрировать номер соответствующего дня. Например, ячейка B2ссылается на ячейку A2(рис. 5.5). Копируем формулу в остальные ячейки второй колонки.

Выделяем второй столбец. Устанавливаем общий формат вывода на экран:

Format Cells — Number — Category — General.

После настройки формата вывода выясняем, что начало нашего периода — это день номер 42005, а окончание — 43465.

Рис. 5.5. Номер дня

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

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

Data — Analysis — Data Analysis — Random Number Generation.

Появляется диалоговое окно настройки генератора Random Number Generation(рис. 5.6).

Указываем число переменных, то есть количество столбцов случайных чисел. Нам пока что понадобится один столбец. Поэтому вводим число 1:

Number of Variables = 1.

Далее указываем заданное количество случайных чисел:

Number of Random Numbers = 10000.