Бизнес-аналитика. Сводные таблицы. Часть 1 - страница 3
Рис. 4.2. Включение надстройки
Убедимся, что надстройка активирована. В верхнем меню выбираем Data и в разделе Analysis находим кнопку Data Analysis (рис. 4.3). Это и есть кнопка вызова нашей статистической надстройки.
Рис. 4.3. Надстройка в меню
Задание. Включите надстройку «Анализ данных» и убедитесь, что в разделе Analysis появилась кнопка вызова надстройки.
5. Имитационное моделирование
Мы смоделируем исходные данные для анализа с помощью генератора случайных чисел.
Исходными данными будет «учётная» база данных транзакций, то есть сделок. В нашей работе мы сформируем таблицу транзакций, в которой будут фиксироваться основные сведения о каждой покупке в каждом магазине нашей торговой сети. По каждой сделке будем учитывать дату, категорию и наименование товара, город и название магазина, цену, вес и общую стоимость товара.
Задание. Сделайте зарисовку таблицы транзакций в соответствии с описанием — оформите шапку таблицы и заполните произвольными данными пару строк.
Перечисленные сведения можно найти на любом кассовом чеке. Они хранятся в реляционной базе данных в виде нескольких таблиц, связанных по ключевым полям. В нашей работе мы создадим «игрушечную» базу данных с помощью электронной таблицы. Здесь тоже будут справочники и связи между таблицами.
Задание. Возьмите любой кассовый чек и сделайте зарисовку логической модели данных (структуры базы данных).
5.1. Даты
Приступим к созданию таблицы транзакций. Первая строка содержит заголовки столбцов. Первый столбец — Дата. Данные будут расположены по столбцам.
Напомним, что дата выглядит для пользователя как три целых числа: год, месяц и день. Но в электронной таблице дата хранится просто как порядковый номер дня. Причём день номер 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. Номер дня
Задание. Используйте ссылки на ячейки и выясните номера дней, соответствующие началу и концу моделирования.
Вызываем генератор случайных чисел с помощью надстройки:
