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

ng>. Прочитайте в учебнике раздел «Динамика»

      В первой части работы [1] по сводным таблицам мы познакомились с «базовыми» функциями этого инструмента – в самых общих чертах, хотя и довольно подробно. С большим количеством примеров, заданий и упражнений.

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

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

      Мы будем использовать последнюю версию Microsoft Excel 365, доступную на момент написания данного учебного пособия. Эта версия соответствует выпуску Microsoft Excel 2019. В других версиях программы могут немного различаться названия упоминаемых пунктов меню и кнопок, а также их расположение.

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

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

      Целью работы является знакомство с продвинутыми функциями сводных таблиц Excel.

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

      1) получим исходные данные путём имитационного моделирования;

      2) построим сводные таблицы по смоделированным данным;

      3) загрузим реальные данные из интернет;

      4) построим сводные таблицы по реальным данным.

      2. План работы

      В данной работе мы рассмотрим следующие приёмы работы и инструменты сводных таблиц:

      1. Мы сгенерируем исходные данные, в которых заложим три компонента динамики: тренд, сезонность и случайность по разным видам товаров.

      2. В исходные данные мы также заложим корреляцию между количеством товаров и их ценой (функция регрессии плюс случайность).

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

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

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

      6. И конечно же, мы возьмём реальные данные и применим к ним уже рассмотренные и освоенные техники анализа.

      3. Отчёт

      Отчёт по лабораторной работе оформляется в виде рабочей книги Excel. Вся процедура оформления отчёта подробно описана в предыдущей работе [1].

      В отчёте должны быть следующие элементы:

      – титульный лист;

      – оглавление;

      – пронумерованные листы.

      Зарисовки вставляем как сфотографированные или отсканированные рисунки.

      Название файла должно быть коротким и понятным.

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

      Задание. Оформите титульный лист и оглавление отчёта и сохраните в файле с коротким информативным названием.

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

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

      Задание. Выясните свой номер варианта и укажите его на титульном листе.

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

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

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

      4.1. Справочник товаров

      Вначале посмотрим на товары. У нас должно быть 3 категории по 2 товара. Сразу же придумаем их и запишем. Для этого создадим на новом листе справочник товаров (рис. 4.1). В первом столбце дадим идентификатор товара. Это целое число от 1 и (в нашем случае) до 3*2=6.

      Рис. 4.1. Справочник товаров

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

      Справочник