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

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

В нижней части окна Excel находится так называемая строка состояния (рис. 5.46). После выделения столбца чисел нам сразу же подсчитали следующие показатели:

— Average — Среднее значение;

— Count — Количество заполненных (непустых) ячеек;

— Sum — Сумма значений.

Рис. 5.46. Итоговые показатели в строке состояния

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

Нужные показатели в строке состояния можно выбрать из довольно большого списка Customize Status Bar. Для этого щёлкнем по строке состояния правой кнопкой мыши. В длинном списке имеется раздел сводных показателей (рис. 5.47). Для уже упомянутых трёх показателей здесь даны их числовые значения. Кроме того, здесь есть

— Numerical Count — Количество ячеек с числовыми значениями;

— Minimum — Минимальное значение;

— Maximum — Максимальное значение.

Рис. 5.47. Настройка строки состояния

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

Сводные таблицы (английское название Pivot Tables) — это инструмент электронных таблиц для сводки и группировки данных. В сводной таблице выводятся обобщенные показатели. Такая таблица гораздо меньше, чем исходная. Это всего лишь десятки или сотни строк, а не миллионы или миллиарды — как в учётной базе данных.

Имеется более десяти способов обобщения (агрегирования) данных. Это, прежде всего, сумма. Наверное, самый частый вопрос звучит так: «Сколько всего…?» Сколько всего рублей составила наша выручка за 2018 год? Сколько всего килограммов овощей продано за февраль 2017 года?

Можно считать, что сводная таблица — это экранное представление многомерного OLAP-куба. Название OLAP — OnLine Analytical Processing можно перевести как «оперативный анализ данных» или «интерактивная аналитическая обработка».

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

Можно сказать, что сводная таблица — это интерфейс к многомерному кубу, который хранится на сервере. Такой сервер называют ХРАНИЛИЩЕ ДАННЫХ. Английское название — Data Warehouse. Это особый вид базы данных, в которой хранятся только обработанные, обобщённые, агрегированные данные в форме многомерных таблиц (кубов) OLAP. Ещё там могут хранить данные, загруженные и подготовленные для многомерного анализа. Сводная таблица может обращаться к такому серверу с помощью SQL-запросов и получать из хранилища готовые OLAP-кубы.

Задание. Прочитайте в Википедии начало статьи «Хранилище данных» и выясните предназначение хранилища данных.

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

Создадим новый рабочий лист. Озаглавим вкладку «Сводка». Сделаем заголовок «Сводная таблица».

Вначале выбираем ячейку, которая станет левым верхним углом сводной таблицы.

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

Insert — Tables — Pivot Table(рис. 6.1).

Рис. 6.1. Вставка сводной таблицы

Появляется диалоговое окно Create PivotTable. Далее нам нужно выбрать источник данных — таблицу транзакций:

Choose the data that you want to analyze — Select a table or range — Table/Range.

Переходим на вкладку «Транз» и выделяем всю таблицу. Появляется ссылка на таблицу, а не на диапазон адресов (рис. 6.2).

Рис. 6.2. Выбор таблицы транзакций

Следующий шаг — указать расположение сводной таблицы:

Choose where you want the PivotTable report to be placed — Existing Worksheet — Location.

По умолчанию это ячейка, на которую мы указали перед началом вставки сводной таблицы.

Нажимаем ОК.

Появляется макет будущей сводной таблицы — начиная с указанной ячейки. Кроме того, в правой части окна выводится меню настройки полей сводной таблицы PivotTable Fields(рис. 6.3).

В верхней части этого меню имеется список полей таблицы транзакций:

Choose fields to add to report.

Ниже можно обнаружить четыре области для размещения полей при настройке сводной таблицы:

Drag fields between areas below.

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

Рис. 6.3. Настройка сводной таблицы

Задание. Вставьте сводную таблицу и ознакомьтесь с меню настройки полей сводной таблицы.

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

Перетаскиваем поле «Город» в раздел Columns. Теперь города — это заголовки столбцов (рис. 6.4).

Рис. 6.4. Города по столбцам

Далее перетаскиваем поле «Категория товара» в раздел Rows. В сводной таблице появились соответствующие заголовки строк (рис. 6.5). Пока что у нас есть только заголовки, а никаких показателей не появилось.

Рис. 6.5. Категории товаров по строкам

Наконец, перетаскиваем поле «Колич товара кг» в раздел Values. Теперь во всех ячейках сводной таблицы подсчитаны значения суммы — общего количества каждого вида товара по каждому городу за все годы. В общем заголовке сводной таблицы указано, что здесь находятся суммы значений поля «Количество товара кг»:

Sum of Колич товара кг.

Сумма — это самый простой и самый популярный метод сводки. Поэтому в заголовке раздела Valuesизображён значок суммы — заглавная греческая буква сигма (рис. 6.6).

Рис. 6.6. Сумма значений

Задание. Настройте поля сводной таблицы.

Мы построили первую сводную таблицу. Можно сказать, что это плоское изображение многомерного OLAP куба. Здесь имеется два измерения и одна мера.

ИЗМЕРЕНИЕ — это поле, по которому сгруппированы данные. В нашем примере измерения — это «Город» и «Категория товара». Ещё можно сравнить измерение с одной из осей координат на графике. Только в нашей сводной таблице по каждой оси будут не числовые значения, а текстовые метки. Сколько осей — столько измерений.