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

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

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

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

Учебное пособие

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

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

ISBN 978-5-4498-1987-1

Создано в интеллектуальной издательской системе Ridero

Введение

Сводные таблицы (Pivot Tables) – это средство оперативного анализа данных с помощью статистических методов сводки и группировки. Обобщенные итоговые показатели подсчитываются автоматически в виде сумм, средних значений и т. п. Современные электронные таблицы позволяют создавать сводные таблицы в диалоговом режиме [1—3].

Сводные таблицы предоставляют конечному пользователю диалоговый интерфейс к многомерным OLAP-кубам – основному инструменту бизнес-аналитики. В качестве исходных данных можно использовать обычную таблицу Excel. Кроме того, сводные таблицы могут получать исходные данные для анализа, обращаясь к серверу базы данных с помощью SQL-запросов.

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

Исходные данные, подготовленные для анализа, должны располагаться в одной «плоской» таблице по столбцам. При этом в первой строке должны находиться заголовки столбцов. Пример: первая колонка – дата, вторая колонка – время, третья колонка – сумма в чеке. Чтобы описание таблицы стало более реальным, нужно будет сделать зарисовку. А потом вставить её в отчёт. Как вставлять фотографии в отчёт, мы обсуждали в первой работе [4]. И использовали в последующих работах [5, 6]. Если нужно, всегда можно этот материал освежить в памяти. Далее в нашей работе мы будем все зарисовки вставлять в отчёт – даже если не сказано «и вставьте её в отчёт». Не забывайте это делать. Это наши действия по умолчанию.

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

1. Цель и задачи работы

Целью работы является общее знакомство с технологиями анализа данных с помощью сводных таблиц. Мы познакомимся с этим инструментом в рамках программного пакета типа электронных таблиц. Однако все рассмотренные методы и технологии реализованы и в специализированных программных пакетах статистического анализа и бизнес-аналитики.

Для достижения поставленной цели мы решим следующие задачи.

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

2. Затем мы построим сводные таблицы с помощью стандартных средств Excel. Здесь мы используем только диалог и визуальное конструирование. То есть обойдёмся без программирования и даже без вызова функций электронных таблиц.

7. После этого мы познакомимся с иерархией, которую можно сворачивать и разворачивать. Примеры: «Город – Магазин» или «Категория – Товар». В каждом городе может быть несколько магазинов, а нас интересуют данные по каждому городу в целом. В следующий момент мы захотим увидеть более подробную картину и развернём таблицу до сведений по каждому магазину в отдельности. Так пользователь управляет степенью детализации своего отчёта.

3. Далее мы поработаем с шаблонами (макетами) сводных таблиц. Это готовые рекомендации, которые могут немного ускорить работу по созданию сводных таблиц.

4. Следующим шагом станет построение сводных графиков. По сути, это сводная таблица плюс график, построенный по данным этой таблицы.

5. Мы также рассмотрим выборку из сводной таблицы по различным параметрам. Для этого мы используем такие инструменты, как фильтры, срезы и шкалу времени (Timeline).

6. Кроме искусственно смоделированных случайных чисел, мы поработаем с реальными данными и посмотрим, что с ними можно сделать с помощью сводных таблиц.

Задание. Прочитайте в Википедии статью «Business Intelligence» на русском и английском языках и выясните, что является источником данных для систем бизнес-аналитики.

2. Отчёт

Отчёт по лабораторной работе оформляется в виде одной рабочей книги пакета Excel. То есть одним файлом *.XLSX. Технологию оформления отчёта мы подробно рассматривали в рамках первой лабораторной работы – см. первое учебное пособие данной серии [4].

Файл следует сохранить под коротким информативным названием, и название файла должно позволять однозначно определить, что находится в файле и кто его создал. Также надо учесть, что длинные названия не всегда хорошо отображаются на экране в разных программах и разных системах.

Задание: Создайте новую рабочую книгу Excel и сохраните в файле с коротким информативным названием.

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

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

Задание. Заполните титульный лист всей необходимой информацией.

Второй лист отчёта – оглавление документа. Все подробности создания оглавления – в первой работе [4]. Когда в отчёте больше 5—6 страниц, оглавление поможет легко ориентироваться в материале – и автору, и читателям. А в нашем отчёте может быть достаточно много страниц.

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

3. Варианты заданий

Каждый студент работает по своему варианту задания. Номер варианта – последняя цифра номера зачётки. Если это цифра ноль – нужно взять вариант 10. Нулевой вариант мы будем использовать для демонстрации технологии выполнения работы.

Задание. Выберите свой вариант задания и укажите номер варианта на обложке отчёта.

В таблице 1 приводятся параметры заданий. Эти числа означают следующее.

Мы будем генерировать данные с нужным количеством товаров в каждой категории. Вариант задания указывает, сколько разных категорий товаров нужно сформировать. Например, в нулевом варианте мы сформируем 3 категории разных товаров по 2 товара в каждой категории. Всего получим 3*2=6 товаров.

Для упрощения работы мы будем работать только с товарами, которые продаются на вес. Это могут быть, например, овощи, фрукты, крупы и т. п. Количество товара будем измерять в килограммах.

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

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

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

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

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

4. Надстройка «Анализ данных»

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

File – Options.

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

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