Microsoft Excel 2013 предлагает обширные возможности бизнес-аналитики, позволяющие создавать эффективные отчеты, системы показателей и информационные панели. Новые улучшенные возможности позволяют создавать вычисляемые объекты (например, вычисляемые показатели, элементы и поля). В этой статье рассказывается о вычисляемых объектах и о том, поддерживаются ли они в службы Excel.
Вычисляемые объекты в службах Excel
В Excel можно создавать вычисляемые объекты, включающие вычисляемые меры, вычисляемые элементы и вычисляемые поля. Они позволяют задавать и использовать настраиваемые вычисления и наборы элементов, не существующие в базах данных, которые используются для создания отчетов сводной диаграммы и сводной таблицы.
Если книга содержит вычисляемые объекты, вы можете предоставить к ней общий доступ, передав ее в библиотеку SharePoint. В зависимости от настроек среды SharePoint, в окне браузера можно просматривать книги, содержащие вычисляемые объекты, а также работать с ними. Но в отдельных случаях эта возможность не поддерживается.
В следующей таблице кратко описано, поддерживаются ли вычисляемые элементы в службах Excel (SharePoint Server 2013), Excel Web App (сервер веб-приложений) и Excel для Интернета (в SharePoint).
| Вычисляемый объект | Службы Excel (локальной версии SharePoint Server 2013) | Excel Web App (Office веб-приложения, локальная версия) | Excel для Интернета (в SharePoint Online) |
|---|---|---|---|
| Вычисляемые показатели | Да | Да | Да, если используемые источники данных поддерживаются в SharePoint. См. статью "Использование внешних данных в книгах в SharePoint". |
| Вычисляемые элементы | Да | Да | Да, если используемые источники данных поддерживаются в SharePoint. См. статью "Использование внешних данных в книгах в SharePoint". |
| Вычисляемые поля | Да | Нет. Возможности Power Pivot, в том числе вычисляемые поля и модели данных, не поддерживаются на сервере веб-приложений Office (локально). |
Да, если используемые источники данных поддерживаются в SharePoint. См. статью "Использование внешних данных в книгах в SharePoint". |
Дополнительные сведения см. в указанных ниже ресурсах.
- Бизнес-аналитика в Excel и службах Excel (SharePoint Server) (это относится к локальным средам)
- Возможности бизнес-аналитики в Power BI для Microsoft 365, Excel и SharePoint (применимо к средам Microsoft 365)
Важно
Если при попытке просмотреть в окне браузера книгу, содержащую вычисляемые объекты (или неподдерживаемые функции), и вы получаете сообщение об ошибке, указывающее, что книга не поддерживается, попробуйте открыть книгу в Excel 2013.
Что такое вычисляемые показатели?
Вычисляемая мера — это настраиваемое вычисление, которое можно создать в Excel при работе с многомерными данными, хранящимися в службах SQL Server Analysis Services. Вычисляемые показатели удобно использовать при задании вычислений, которые могут отсутствовать в базе данных. Примеры настраиваемых вычислений:
- показатель торговой квоты, использующий определенную формулу;
- процент от общего итога для элементов группы;
- валовая прибыль, которая рассчитывается с помощью сложного запроса;
- показатель дохода, использующий сумму валового дохода и себестоимости продукции.
При создании вычисляемого показателя необходимо определить запрос многомерных выражений (MDX). Это легко сделать с помощью диалогового окна "Вычисляемый показатель" в Excel, которое позволяет настроить запрос с помощью функций перетаскивания.
Создание вычисляемой меры в Excel
Создайте отчет сводной таблицы или сводной диаграммы, используя данные из куба служб аналитики.
На вкладке "Анализ" в группе "Вычисления" выберите "Средства >OLAP— вычисляемая мера многомерных выражений". Откроется диалоговое окно Создание вычисляемой меры.
Введите имя вычисляемого показателя в поле Имя.
(Это действие не является обязательным.) Чтобы указать, как вычисляемый показатель должен располагаться в списке полей сводной таблицы или сводной диаграммы, выполните хотя бы одно из указанных ниже действий.
- С помощью списка Группа показателей укажите, как вычисляемый показатель будет располагаться в списке Поля сводной таблицы или Поля сводной диаграммы. Если группа показателей не указана, вычисляемый показатель будет отображаться в группе Значения.
- В поле Папка введите имя папки для вычисляемого показателя.
Перетащите элемент (например, показатель) с вкладки Поля и элементы на панель MDX.
Добавьте оператор, например +, -, / или *, после элемента на панели MDX.
Перетащите второй элемент с вкладки Поля и элементы на панель MDX.
Повторяйте действия 5–7 до тех пор, пока не создадите формулу со всеми нужными элементами.
Например, если вы создаете вычисляемую меру с именем "Доход", в области многомерных выражений у вас может быть запрос, похожий на
[Measures].[Product Cost]+[Measures].[Gross Profit]Нажмите кнопку Проверить MDX, чтобы убедиться, что запрос работает правильно.
Чтобы создать вычисляемый показатель, нажмите кнопку ОК.
Чтобы использовать вычисляемый показатель в отчете, выберите его в списке Поля сводной таблицы или Поля сводной диаграммы. Вы найдете его в расположении, указанном на шаге 4.
Примечание
Поскольку вычисляемая мера многомерных выражений в Excel используется для создания сеанса вычисления для источника данных SQL Server Analysis Services, используемая вычисляемая мера будет ограничена сеансом и подключением к источнику данных. Узнайте больше о создании вычисляемых элементов в области сеанса.
Что такое вычисляемые элементы?
Вычисляемый элемент — это набор элементов, который можно определить в Excel при работе с многомерными данными, хранящимися в службах Server Analysis Services. Вычисляемые элементы удобно использовать для определения наборов элементов, которых еще нет в базе данных. Примеры пользовательских наборов:
- территория, состоящая из таких географических единиц, как страны, регионы и штаты;
- Группа продуктов, которые учитываются в квоте торгового представителя
- набор рекламных задач, относящихся к определенной маркетинговой кампании.
По аналогии с вычисляемыми показателями при создании вычисляемого элемента определяется MDX-запрос. Это легко сделать с помощью диалогового окна "Вычисляемый элемент" в Excel, которое позволяет настроить запрос с помощью функций перетаскивания.
Примечание
При работе со сводной таблицей в Excel и добавлении вычисляемого элемента с помощью средств OLAP отменить его выбор в раскрывающемся списке списка полей не получится, если источник данных подключен к серверу под управлением SQL Server 2008 или более ранних версий. Если источник данных связан с сервером, на котором работает SQL Server 2008 R2 или более поздней версии, можно выбирать вычисляемый элемент и отменять его выбор в раскрывающемся списке фильтра.
Создание вычисляемого элемента в Excel
Создайте отчет сводной таблицы или сводной диаграммы, используя данные из куба служб аналитики.
На вкладке Анализ в группе Вычисления выберите пункт Средства OLAP Вычисляемый>элемент многомерных выражений. Откроется диалоговое окно Создание вычисляемого элемента.
Введите имя вычисляемого элемента в поле Имя.
С помощью списка Родительская иерархия укажите, как вычисляемый элемент будет располагаться в списке Поля сводной таблицы или Поля сводной диаграммы.
Обратите внимание на выбранное расположение. Его необходимо знать, чтобы использовать вычисляемый элемент в отчете сводной таблицы или сводной диаграммы.Перетащите элемент (например, иерархию измерения) с вкладки Поля и элементы на панель MDX.
Затем на панель Многомерное выражение добавьте операцию, например "+", "-", "/", или "*".
Перетащите второй элемент с вкладки Поля и элементы на панель MDX.
Повторяйте действия 5–7 до тех пор, пока не создадите формулу со всеми нужными элементами.
Например, если вы создаете вычисляемый элемент "Основные продукты", который включает все категории продуктов, кроме двух, в области многомерных выражений у вас может быть запрос, похожий на
[Product].[Product Categories]-[Product].[Product Categories].[Category].&[4]-[Product].[Product Categories].[Category].&[3]Нажмите кнопку Проверить MDX, чтобы убедиться, что запрос работает правильно.
Чтобы создать вычисляемый элемент, нажмите кнопку ОК.
Чтобы добавить вычисляемый элемент в отчет сводной таблицы или сводной диаграммы, сделайте следующее:
- убедитесь, что для отчета выбран хотя бы один показатель;
- в списке Поля сводной таблицы или Поля сводной диаграммы раскройте родительское измерение, указанное в шаге 4;
- установите флажок рядом с иерархией измерения, соответствующей той иерархии, которая использовалась для создания вычисляемого элемента. В отчете отображаются сведения для всех элементов измерения в группе, включая созданный вычисляемый элемент.
(Это действие не является обязательным.) Чтобы в отчете отображались только сведения вычисляемого элемента, сделайте следующее:
- в списке Поля сводной таблицы или Поля сводной диаграммы наведите указатель мыши на иерархию измерения, содержащую вычисляемый элемент;
- когда появится стрелка вниз, щелкните или коснитесь ее, чтобы открыть диалоговое окно Выбор поля;
- снимите флажки всех элементов, кроме созданного вычисляемого элемента.
Вычисляемые поля
Вычисляемые поля полезны при создании вычисляемого объекта в сводной таблице или отчете, который не использует многомерные данные, хранящиеся в службах Analysis Services, а использует данные из модели данных книги, созданной с помощью Power Pivot в Excel. Значения в вычисляемых полях могут изменяться в зависимости от контекста. Контекст определяется выбранными строками, столбцами, фильтрами или пользовательской формулой Data Analysis Expressions (DAX), созданной в PowerPivot.
Подобно вычисляемым показателям и элементам вычисляемые поля отображаются в списке "Поля сводной таблицы" (или "Поля сводной диаграммы"), как правило, в группе "Значения". Создать вычисляемое поле можно несколькими способами.
Создание вычисляемого поля в Excel
Подробнее о создании и использовании вычисляемых полей см. в статьях: