Кубы в эксель что это

Куб является одним из распространенных методов комплексного многомерного анализа данных, получивших название OLAP (OnLine Analytical Processing). В его основе лежит представление данных в виде многомерных кубов, называемых также OLAP-кубами или гиперкубами.

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

Интерфейс

После добавления в узел визуализатора откроется главное окно (Рис. 1):

Рисунок 1. Главное окно Куба

Главное окно условно можно разделить на 6 областей:

Область свободных полей;
Область измерений в строках;
Область фактов;
Область измерений в колонках;
Область фильтрации по измерениям; .

Области со 2 по 5 представляют собой Область построения Куба.

Важно: во время настройки Куба, особенно при работе с большими объемами данных, обновление Области фактов (3) может занимать продолжительное время. В таких случаях рекомендуется временно отключать обновление данных кнопкой Приостановить автовычисление.

Настройка куба

Список столбцов, которые можно использовать в качестве измерений или фактов, отображается в Области свободных полей (1). Уже используемые в Кубе столбцы отображаются жирным шрифтом.

Измерения

Для построения отчета с помощью Куба нужно добавить требуемые измерения в строки или колонки. Добавить измерения в Куб можно двумя способами:

Перетащить (Drag-and-Drop) поле из Области свободных полей (1) в Область измерений колонок или строк (2 или 4);
Нажать кнопку нужной области и выбрать необходимое поле из списка.

Количество измерений ограничено количеством доступных полей. Рекомендуется использовать не более 5-7 измерений, чтобы отчет был понятным и интерпретируемым.

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

Так как визуализатор Куб представляет собой плоскую двухмерную таблицу, то при отображении нескольких измерений заголовки образуют иерархическую систему (дерево заголовков). По умолчанию все заголовки свернуты до самого внешнего измерения. Для раскрытия или сворачивания заголовков внутренних измерений нужно кликнуть по кнопке Развернуть или Свернуть на заголовке внешнего измерения. Существуют групповые операции раскрытия или сворачивания заголовков.

При нажатии на справа от названия измерения доступен выбор:

Свернуть все элементы до минимальной детализации измерения;
Развернуть все элементы до максимальной детализации измерения;
По возрастанию — отсортировать значения в порядке возрастания;
По убыванию — отсортировать значения в порядке убывания;
В исходном порядке — выстроить значения в порядке их появления в данных;
Удалить — исключить измерение из Куба. Удалить измерение также можно перетаскиванием (Drag-and-Drop) назад в Область свободных полей (1).

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

Множественный выбор — пользователь может выбрать произвольное количество значений измерения из списка доступных. Выбранные значения будут отображаться в Кубе.
Одиночный выбор — позволяет выбрать из списка только одно значение, которое будет отображаться в Кубе.
Комбинированный выбор — позволяет выбрать одно значение измерения из списка, сформированного ранее в режиме множественного выбора.

По умолчанию задан режим Множественный выбор. C помощью переключателя Режим включенных/исключенных значений задается разрешительный или запретительный тип фильтра. Это влияет на то, как будет обновлен фильтр, если Куб открывается на новых данных.

В режиме Множественный выбор доступны операции:

Отметить все / Снять все — выделяет все значения в списке или снимает выделение со всех значений списка.
Инвертировать выбор — меняет выбранные значения списка на невыбранные и наоборот.
Отметить по маске. — позволяет задать условие и метод отбора измерений.

Для настройки отбора открывается окно Отметить по маске (в заголовке окна также указано измерение, для которого выполняется настройка). Слева в выпадающем списке необходимо выбрать условие соответствия значений измерения маске. Справа от списка в поле ввода задается маска. Ко всем измерениям, которые соответствуют условию, будет применен выбранный метод отбора (Отметить, Добавить к отмеченным, Снять или Удалить из отмеченных). При отборе может учитываться регистр букв, для этого нужно установить флаг Учитывать регистр.

В режимах Одиночный выбор или Комбинированный выбор справа и слева от наименования измерения появляются кнопки и для перелистывания значений фильтра. При перелистывании Область фактов (3) будет перестраиваться под значение фильтра. Можно включить перелистывание через границы списка значений, нажав на кнопку Зациклить значения при перелистывании.

Примечание: над Областью измерений (4) Куба находится Область фильтрации (5). Настройка Области фильтрации аналогична настройке Области измерений. Измерения, добавленные в Область фильтрации, не будут отображаться в Кубе, но могут быть использованы в вычисляемых фактах и для фильтрации данных.

Факты

Добавить факты можно двумя способами:

Перетащить (Drag-and-Drop) нужное поле из Области свободных полей (1) в Область фактов (3);
Нажать кнопку Факты, выбрать необходимое поле и в появившемся окне выбрать нужный вариант агрегации и способ его отображения.

Вид агрегации
Сумма	•	•
Количество	•	•	•	•	•	•
Минимум	•	•	•	•	•
Максимум	•	•	•	•	•
Среднее	•	•	•
Стандартное отклонение	•	•	•
Количество уникальных	•	•	•	•	•	•
Количество пропусков	•	•	•	•	•	•
Первый	•	•	•	•	•	•
Последний	•	•	•	•	•	•

Нажав на кнопку рядом с Факты, можно открыть выпадающее меню с вызовом одного из окон:

Настроить факты… — открыть окно настройки фактов;
Добавить вычисляемый факт… — открыть окно для добавления вычисляемого факта в Куб;
Фильтровать факты… — открыть окно для фильтрации фактов;
Форматировать факты… — открыть окно для форматирования фактов и заголовков измерений.

Удалить факт можно снятием флага в вызываемом окне Настроить факты.

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

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

Финансы в Excel

Первый интерфейс сводных таблиц, называемых также сводными отчеты, был включен в состав Excel еще в 1993м году (версии Excel 5.0). Несмотря на множество полезных функциональных возможностей, он практически не применяется в работе большинством пользователей Excel. Даже опытные пользователи зачастую подразумевают под термином «сводный отчет» нечто построенное с помощью сложных формул. Попробуем популяризировать использование сводных таблиц в повседневной работе экономистов. В статье обсуждаются теоретические основы создания сводных отчетов, даются практические рекомендации по их использованию, а также приводится пример доступа к данным на основе нескольких таблиц.

Термины многомерного анализа данных

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

Многомерные данные, измерения

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

виды или группы товаров;
бренды или категории товаров;
периоды (месяц, квартал, год);
покупатели или группы покупателей;
регионы продаж
и т.п.

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

объем продаж;
цена продажи;
индивидуальная скидка
и т.п.

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

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

Аббревиатура OLAP (online analytical processing) в дословном переводе звучит как «аналитическая обработка в реальном времени». Определение не очень конкретное, под него можно подвести практически любой отчет любого программного продукта. По смыслу OLAP подразумевает технологию работы со специальными отчетами, включая программное обеспечение, для получения и анализа как раз многомерных структурированных данных. Одним из популярных программных продуктов, реализующих OLAP-технологии, является SQL Server Analysis Server. Некоторые даже ошибочно считают его единственным представителем программной реализации данной концепции.

Виртуальный куб данных

«Виртуальный куб» (многомерный куб, OLAP-куб) — это специальный термин, предложенный некоторыми поставщиками специализированного программного обеспечения. OLAP-системы обычно готовят и хранят данные в собственных структурах, а специальные интерфейсы анализа (например, сводные отчеты Excel) обращаются к данным этих виртуальных кубов. При этом использование подобного выделенного хранилища совсем не обязательно для обработки многомерной информации. В общем случае, виртуальный куб – это и есть массив специально оптимизированных многомерных данных, который используется для создания сводных отчетов. Он может быть получен как через специализированные программные средства, так и через простой доступ к таблицам базы данных или любой другой источник, например к таблице Excel.

Сводная таблица

«Сводный отчет» (сводная таблица, Pivot Table) — это пользовательский интерфейс для отображения многомерных данных. С помощью данного интерфейса можно группировать, сортировать, фильтровать и менять расположение данных с целью получения различных аналитических выборок. Обновление отчета производится простыми средствами пользовательского интерфейса, данные автоматически агрегируются по заданным правилам, при этом не требуется дополнительный или повторный ввод какой-либо информации. Интерфейс сводных таблиц Excel является, пожалуй, самым популярным программным продуктом для работы с многомерными данными. Он поддерживает в качестве источника данных как внешние источники данных (OLAP-кубам и реляционным базам данных), так и внутренние диапазоны электронных таблиц. Начиная с версии 2000 (9.0), Excel поддерживает также графическую форму отображения многомерных данных – сводная диаграмма (Pivot Chart).

Реализованный в Excel интерфейс сводных таблиц позволяет расположить измерения многомерных данных в области рабочего листа. Для простоты можно представлять себе сводную таблицу, как отчет, лежащий сверху диапазона ячеек (на самом деле есть определенная привязка форматов ячеек к полям сводной таблицы). Сводная таблица Excel имеет четыре области отображения информации: фильтр, столбцы, строки и данные. Измерения данных именуются полями сводной таблицы. Эти поля имеют собственные свойства и формат отображения.

Еще раз хочется обратить внимание, что сводная таблица Excel предназначена исключительно для анализа данных без возможности редактирования информации. Ближе по смыслу было бы повсеместное употребление термина «сводный отчет» (Pivot Report), и именно так этот интерфейс и назывался до 2000го года. Но почему-то в последующих версиях разработчики от него отказались.

Редактирование сводных таблиц

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

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

Подготовка многомерных данных

Подойдем к практическому применению сводных таблиц. Попробуем проанализировать данные о продажах в различных направлениях. Файл pivottableexample.xls состоит из нескольких листов. Лист Пример содержит основную информацию о продажах за определенный период. Для простоты примера будем анализировать единственный числовой показатель – объем продажи в кг. Имеются следующие ключевые измерения данных: продукция, покупатель и перевозчик (транспортная компания). Кроме того, имеются несколько дополнительных измерений данных, являющихся признаками продукта: тип, бренд, категория, поставщик, а также покупателя: тип. Эти данные собраны на листе Справочники. На практике подобных измерений может быть гораздо больше.

Лист Пример содержит стандартное средство анализа данных – автофильтр. Глядя на пример заполнения таблицы, очевидно, что нормальному анализу поддаются данные о продажах по датам (они расположены по столбцам). Кроме того, используя автофильтр можно попробовать просуммировать данные по сочетаниям одного или нескольких ключевых критериев. Совершенно отсутствует информация о брендах, категориях и типах. Нет возможности сгруппировать данные с автоматическим суммированием по определенному ключу (например, по покупателям). Кроме того, набор дат зафиксирован, и просмотреть итоговую информацию за определенный период, например, 3 дня, автоматическими средствами не удастся.

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

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

Таблица имеет форму журнала ввода информации. Здесь дата является равноправным измерением данных. Также следует заметить, что для последующего анализа в сводных таблицах совершенно безразлично относительное положение строк друг относительно друга (иначе говоря, сортировка). Этими свойствами обладают записи в реляционных базах данных. Именно на анализ больших объемов баз данных ориентирован в первую очередь интерфейс сводных таблиц. Поэтому необходимо придерживаться этих правил и при работе с источником данных в виде диапазонов ячеек. При этом никто не запрещает использовать в работе интерфейсные средства Excel – сводные таблицы анализируют только данные, а форматирование, фильтры, группировки и сортировки исходных ячеек могут быть произвольными.

От автофильтра к сводному отчету

Теоретически на данных листа Продажи уже можно проводить анализ в трех измерениях: товары, покупатели и перевозчики. Данные о свойствах продукции и покупателей на данном листе отсутствуют, что, соответственно, не позволит показать их и в сводной таблице. В нормальном режиме создания сводной таблицы для исходных данных Excel не позволяет связывать данные нескольких таблиц по определенным полям. Обойти это ограничение можно программными средствами – см. пример-дополнение к данной статье на нашем сайте. Чтобы не прибегать к программным методам обработки информации (тем более, что они и не универсальны), следует добавить дополнительные характеристики непосредственно в форму ввода журнала – см. лист ПродажиАнализ.

Применение функций VLOOKUP позволяет легко дополнить исходные данные недостающими характеристиками. Теперь, применяя автофильтр, можно анализировать данные в различных измерениях. Но остается нерешенной проблема группировок. Например, отследить сумму только по брендам на определенные даты достаточно проблематично. Если ограничиваться формулами Excel, то нужно строить дополнительные выборки, используя функцию SUMIF.

Теперь посмотрим какие возможности дает интерфейс сводных таблиц. На листе СводАнализ построено несколько отчетов на основе диапазона ячеек с данными листа ПродажиАнализ.

Первая таблица анализа построена через интерфейс Excel 2007 Лента \ Вставка \ Сводная таблица (в Excel 2000-2003 меню Данные \ Сводная таблица).

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

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

Свойства и форматирование

Кроме непосредственного отображения данных, имеется большой набор возможностей по отображению внешнего вида сводных таблиц. Лишние данные можно скрывать, используя фильтры. Для единичного элемента или поля проще пользоваться пунктом контекстного меню Удалить (в версии 2000-2003 Скрыть).

Для полей данных таблицы можно задать единый формат отображения. Это делается не через формат ячейки, а через специальный диалог настройки формата поля.

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

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

Кроме того, в Excel 2007 появилось множество предопределенных стилей отображения сводной таблицы:

Сводная диаграмма

Нажав кнопку на ленте «Сводная диаграмма», можно сформировать специальный тип диаграммы, отображающей данные сводной таблицы:

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

Доступ к внешним данным

Как уже отмечалось, пожалуй, наибольший эффект от применения сводных таблиц можно получить при доступе к данным внешних источников – OLAP-кубам и запросам к базам данных. Такие источники обычно хранят большие объемы информации, а также имеют предопределенную реляционную структуру, что позволяет легко определить измерения многомерных данных (поля сводной таблицы).

Excel поддерживает множество типов источников внешних данных:

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

MOLAP и ROLAP кубы с примерами

Начнём с терминологии. MOLAP — multidimensional online analytical processing. ROLAP — relational online analytical processing.

Что такое кубы OLAP

Говоря простым языком OLAP куб, а точнее его разновидности, это принцип работы BI слоя с информацией из базы данных. Давайте рассмотрим порядок работы с разными видами этих кубов. Допустим, у нас имеется база данных в которую загружены показатели с разных источников — реклама, продажи, crm, склад, логистика и т.д.

Что такое MOLAP куб?

При MOLAP (multidimensional online analytical processing) мы подключаемся к БД и выгружаем весь массив значений в кеш. Чаще всего это делают в Excel — ибо удобнее. И за счёт ресурсов компьютера работают со всем этим огромным количеством информации. Кратко можно назвать MOLAP кешированием всего объема данных.

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

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

Пример множества отчётов на основе MOLAP куба в Excel

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

Что такое ROLAP куб?

ROLAP куб (relational online analytical processing) видели все, кто хоть раз работали с Power BI или другими подобными инструментами. В данном кубе пользователь производя действия в дашборде (фильтры, сортировка и т.д.) автоматически отправляет в базу данных SQL запросы. В ответ база данных формирует информацию и отправляет её в кеш. Пользователь работает только с частичкой всей БД через кеш. Это и есть ROLAP куб, лежащий в БД.

ROLAP кубы на данный момент в тренде.

Но в моей практике, как маркетолога в агентстве, работа через ROLAP кубы по большей части невозможна, т.к. клиентская БД обычно закрыта для доступа, поскольку в ней лежит всё подряд. И остаётся подключать Excel через MOLAP к отдельным вьюхам и работать с этими данными.

Кубы в эксель что это