Часть 4

Предыдущие части статьи были посвящены обзору различных методов работы с многомерными данными посредством сводных таблиц Microsoft Excel. Здесь же мы поговорим об альтернативном подходе — наборе методов КУБ().

Прежде чем перейти к рассказу о возможностях функций семейства КУБ(), сделаем небольшое отступление и перечислим некоторые существенные ограничения стандартных сводных отчетов Microsoft Excel.

Естественные ограничения сводных отчетов

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

В качестве примера рассмотрим классическую задачу по подготовке отчетности о результатах деятельности компании. Допустим, речь идет об итогах I квартала, то есть требуется заполнить таблицу следующего вида (табл. 1).

Зададимся теперь вопросом: можно ли воспроизвести подобную структуру средствами сводной таблицы? К сожалению, ответ будет отрицательным — в лучшем случае нам удастся создать отчет следующего формата (табл. 2).

Табл. 2 получается путем последовательного размещения в области столбцов сводного отчета измерений «Дата» и «Категория». Если поменять порядок их следования, макет отчета изменится и примет форму, показанную в табл. 3.

Использование каких­ли-бо фильт­ров не решит проблему. Безусловно, можно исключить элемент «План» из отчета, но тогда он будет удален для всех месяцев не только I, но и II квартала.

Методы создания сводных отчетов с расширенной функциональностью

Одно из очевидных решений проблемы заключается в создании пользовательских иерархий из разнородных атрибутов типа «Месяц — Категория» (рис. 1).

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

В принципе, любую комбинацию атрибутов в OLAP-источнике можно объединить в иерархию. Для небольших кубов такой подход может быть даже оправданным, но для существенных объемов данных объединение атрибутов, не составляющих «естественную» (Natural) иерархию, серьезно понижает производительность всей системы.

Когда в аналитическом кубе имеется иерархия «Месяц — Категория», создание отчета о результатах деятельности из начала статьи становится минутным делом. Достаточно поместить данное измерение в область столбцов отчета, затем для месяцев I квартала оставить только подчиненные значения «Факт», а для месяцев II квартала — «План».

Повторимся еще раз: атрибут «Категория» в данном случае уже не является самостоятельным измерением, это всего лишь нижний уровень иерархии. Следовательно, его можно определять отдельно для каждого элемента верхнего уровня — «Месяц», то есть точно так же, как если бы мы выбирали месяцы I квартала без оглядки на II квартал.

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

Во­первых, при таком подходе для любой комбинации атрибутов требуется создание отдельной иерархии. Допустим, мы хотим немного изменить исходный макет отчета и сделать таблицу следующего вида (табл. 4).

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

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

Альтернативные интерфейсы доступа к многомерным данным

В программе Microsoft Excel версии 2007 был добавлен альтернативный интерфейс доступа к многомерным данным — семейство функций КУБ(). Изначально при помощи методов КУБ() можно было обратиться только к аналитическому серверу от компании Microsoft, что существенно сужало круг их потенциальных пользователей.

С выходом Microsoft Excel 2010 ситуация изменилась в лучшую сторону. Последняя версия Microsoft Excel комплектуется сервисом PowerPivot, в котором реализована поддержка двух основных методов КУБ(): КУБЭЛЕМЕНТ() и КУБЗНАЧЕНИЕ(). Благодаря сервису PowerPivot функции КУБ() наконец стали общедоступными. По своим возможностям они прекрасно дополняют базовую функциональность сводных таблиц.

Покажем, как с помощью методов КУБ() можно составить интересующий нас отчет. Начнем с создания обычного сводного отчета на базе OLAP-куба (рис. 3).

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

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

Оставшаяся часть таблицы состоит из набора формул двух типов. Элементы из полей строк и столбцов сводной таблицы (измерения OLAP-куба) были преобразованы в формулы КУБЭЛЕМЕНТ(), а поля из области значений (мера OLAP-куба) — в формулы КУБЗНАЧЕНИЕ().

Каждая из формул имеет весьма простую и ясную структуру. Рассмотрим для начала синтаксис формулы КУБЗНАЧЕНИЕ():

КУБЗНАЧЕНИЕ (подключение, выражение_элемента1, выражение_элемента2…)

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

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

Для примера заменим формулы КУБЭЛЕМЕНТ() элементов измерения «План_Факт» строчными значениями — для этого в первых трех колонках наберем «[Факт]», а последних трех — «[План]». Как видно из рис. 5, подобная операция сразу приводит отчет к асимметричному виду — формату, который был целью данной статьи.

Рис. 6. Ограничение множества допустимых значений ячейки перечнем элементов

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

Рис. 6 демонстрирует, как данная идея может быть воплощена на практике. Сначала фильтры исходного отчета были размещены рядом с областью данных отчета, затем весь полученный диапазон был скопирован и повторно помещен на лист книги Microsoft Excel. Формулы нижнего раздела были сориентированы на использование соответствующих фильтров.

Итогом такой работы стал отчет, содержащий сразу две проекции одного многомерного пространства. Причем у этих проекций есть как общие измерения — «План_Факт», «Дата», так и независимые — «Сервис». Кроме того, каждая проекция располагает собственным набором фильтров.

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

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

Чтобы завершить рассказ о главных представителях семейства КУБ(), нам осталось разобраться с работой функции КУБЭЛЕМЕНТ(), имеющей следующий синтаксис:

В Microsoft Excel для записи такого кортежа придется использовать довольно экзотический синтаксис:

При этом функция КУБЭЛЕМЕНТ() в целом примет следующий вид:

Условно говоря, последний вызов эквивалентен созданию внутри программы Microsoft Excel вычисляемого элемента «Абон. плата — план», то есть конструкции вида:

with member [Сервис].[Сервис].[Абон. плата — план] as ([Сервис].[Сервис].&[1],[План_Факт].[План_Факт].&[1])

К счастью, в функции КУБЭЛЕМЕНТ() предусмотрен альтернативный подход к заданию аргумента Выражение_элемента — вместо текстовой строки допускается указывать ссылку на диапазон ячеек.

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

Рис. 7. Заполнение именованного
диапазона формулами, задающими координаты
элемента на отдельных измерениях

Следует отметить, что каждая строка в диапазоне «Кортеж» может быть произвольным MDX-выражением. Например, для формул на рис. 7 существует эквивалентное написание:

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

Во­вторых, базовые функции КУБ() являются статическими по своей природе. Оператор КУБЭЛЕМЕНТ() оптимально подходит для создания отчетов с фиксированной структурой, но малопригоден для работы с динамическими наборами.

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

​Все эти и​ смогу посмотреть скорее​Про имена забыл​ знаков текстовой строкой​ аргументе​можно использовать подстановочные​ в примере 1​В этой статье описаны​ пока поле не​ о необходимости установления​Нажмите клавишу​. В этом случае​ их вручную несколькими​​KuklP​​ Восстанавливаем и открываем​ массу вариантов поиска​ исключением формул условного​ другие опасности должна​

Сводные таблицы Excel

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

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

Приветствую всех, дорогие читатели блога TutorExcel.Ru!

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

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

При анализе данных перед нами может встать ряд вопросов, к примеру:

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

Давайте пошагово разберем как создать сводную таблицу.

Создание сводной таблицы в Excel

Для начала встанем в любую ячейку нашей таблицы с данными. Далее в панели вкладок перейдем Вставка -> Сводная таблица:

В данном меню мы можем настроить 2 основных момента — на основе каких данных построить таблицу и где ее разместить.

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

Далее определим куда мы поместим сводную таблицу — либо она создается на новом листе, либо добавляется на каком-то из существующих. В зависимости от предпочтений выбираем подходящий вариант.

Нажимаем OK и перед нами появляется следующий конструктор:

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

В правой части мы видим следующие элементы (список полей и области):

• Список полей;
  Список всех заголовков столбцов исходной таблицы с данными.
• Фильтры;
  Добавление дополнительного среза для детализации данных.
• Строки;
  Поля таблицы вынесенные в строки.
• Столбцы;
  Поля таблицы вынесенные в столбцы;
• Значения.
  Вычисляемые числовые данные по соответствующим полям из строк и столбцов (единственный вычисляемый элемент в таблице).

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

Список полей и области сводной таблицы

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

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

Также необходимо, чтобы в таблице отсутствовали пустые строки со столбцами и объединенные ячейки, в этом случае Excel не понимает структуру исходных данных и может свести данные некорректно.

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

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

С элементами разобрались, теперь перейдем непосредственно к построению.

Построение отчета сводной таблицы

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

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

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

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

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

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

И еще один пример. Проанализируем помесячные продажи в разрезе моделей (даты отправляются в строки, а продажи в штуках и деньгах — в значения):

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

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

Как обновить сводную таблицу в Excel?

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

В этом случае встаем в любую из ячеек сводной таблицы (в панели вкладок появится блок Работа со сводными таблицами) и далее выбираем Анализ -> Данные -> Обновить:

Однако же, если в начальные данные добавили новые строки (к примеру, появились данные за новые периоды), то такой способ уже не сработает.

Потому что при создании сводной таблицы мы задали фиксированный диапазон с исходными данными и новые данные в него уже не попадают. Поэтому в случае добавления новых данных нужно обновить диапазон в качестве источника для таблицы. Для этого в панели вкладок переходим в Анализ -> Данные -> Источник данных и задаем новый диапазон.

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

Эту проблему можно решить задав в качестве источника данных не фиксированный диапазон (как в примере выше мы использовали ссылку $A$1:$G$820), а целиком выделить все столбцы, не ограничивая таблицу по высоте (т.е. использовать ссылку $A:$G).

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

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

Применение умных таблиц

Давайте вернемся к исходной таблице с данными, встанем в любую ячейки таблицы и в панели вкладок перейдем на Главная -> Стили -> Форматировать как таблицу:

Среди стилей оформления выбираем любой понравившийся (внешний вид на функциональности не влияет), далее выбираем диапазон для таблицы (по умолчанию Excel автоматически его определяет) и нажимаем OK:

Полученная умная таблица обладает большим спектром удобных свойств, но в данном случае нам интересны 2:

• Умная таблица получает имя (в примере выше Таблица1, его можно легко поменять), которое можно использовать для определения диапазона в качестве источника для сводной таблицы;
• Умная таблица автоматически изменяет размер при добавлении или удалении новых строк или столбцов.

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

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

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

Пошаговая инструкция построения сводной таблицы в Excel

• Формируем таблицу с данными (проверяем, что все столбцы имеют заголовки);
• При возможности делаем таблицу умной (Главная ->Стили ->Форматировать как таблицу), чтобы упростить дальнейший процесс обновления;
• В панели вкладок переходим Вставка ->Сводная таблица и строим таблицу;
• Задаем диапазон для таблицы (если курсор находится внутри таблицы, то Excel автоматически определит диапазон для таблицы) и место куда поместить сводную таблицу;
• В макете перетаскиваем поля из списка в области строк, столбцов и значений, также добавляем фильтр в случае необходимости;
• Анализируем данные таблицы;
• При добавлении новых данных обновляем таблицу (Анализ ->Данные ->Обновить) и если таблица обычная (не умная), то также обновляем источник данных (Анализ ->Данные ->Источник данных).

Спасибо за внимание!
Если у вас есть вопросы или мысли по теме статьи — обязательно спрашивайте и пишите в комментариях, не стесняйтесь.

Давайте вернемся к исходной таблице с данными, встанем в любую ячейки таблицы и в панели вкладок перейдем на Главная -> Стили -> Форматировать как таблицу:

Как сделать сводную таблицу в Excel?.

• Список полей;
  Список всех заголовков столбцов исходной таблицы с данными.
• Фильтры;
  Добавление дополнительного среза для детализации данных.
• Строки;
  Поля таблицы вынесенные в строки.
• Столбцы;
  Поля таблицы вынесенные в столбцы;
• Значения.
  Вычисляемые числовые данные по соответствующим полям из строк и столбцов (единственный вычисляемый элемент в таблице).

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