Microsoft Excel 2013 предлагает различные возможности бизнес-аналитики, которые позволяют создавать мощные отчеты, системы показателей и панели мониторинга. Новые улучшенные возможности позволяют создавать вычисляемые объекты (например, вычисляемые показатели, элементы и поля). Прочтите эту статью, чтобы узнать о вычисляемых элементах и о том, поддерживаются ли они в службы Excel.
Вычисляемые объекты в службах Excel
В Excel пользователи могут создавать вычисляемые элементы, которые включают вычисляемые меры, вычисляемые элементы и вычисляемые поля. Они позволяют задавать и использовать настраиваемые вычисления и наборы элементов, не существующие в базах данных, которые используются для создания отчетов сводной диаграммы и сводной таблицы.
Если книга содержит вычисляемые объекты, вы можете предоставить к ней общий доступ, передав ее в библиотеку SharePoint. В зависимости от настроек среды SharePoint, в окне браузера можно просматривать книги, содержащие вычисляемые объекты, а также работать с ними. Но в отдельных случаях эта возможность не поддерживается.
В следующей таблице показано, поддерживаются ли вычисляемые элементы в службы Excel (SharePoint Server 2013), Excel Web App (Office веб-приложения Server) и Excel для Интернета (в SharePoint).
| Вычисляемый объект | службы Excel (SharePoint Server 2013, локально) | Excel Web App (Office веб-приложения, локальная среда) | Excel для Интернета (в SharePoint Online) |
|---|---|---|---|
| Вычисляемые показатели | Да | Да | Да, если используемые источники данных поддерживаются в SharePoint. См . статью Использование внешних данных в книгах в SharePoint. |
| Вычисляемые элементы | Да | Да | Да, если используемые источники данных поддерживаются в SharePoint. См . статью Использование внешних данных в книгах в SharePoint. |
| Вычисляемые поля | Да | Нет. Возможности Power Pivot, включая вычисляемые поля и модели данных, не поддерживаются в Office веб-приложения Server (локально). |
Да, если используемые источники данных поддерживаются в 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 до тех пор, пока не создадите формулу со всеми нужными элементами.
Например, при создании вычисляемой меры с именем Revenue в области многомерных выражений может появиться запрос, похожий на
[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 Tools>Вычисляемый элемент многомерных выражений. Откроется диалоговое окно Создание вычисляемого элемента.
Введите имя вычисляемого элемента в поле Имя.
С помощью списка Родительская иерархия укажите, как вычисляемый элемент будет располагаться в списке Поля сводной таблицы или Поля сводной диаграммы.
Обратите внимание на выбранное расположение. Его необходимо знать, чтобы использовать вычисляемый элемент в отчете сводной таблицы или сводной диаграммы.Перетащите элемент (например, иерархию измерения) с вкладки Поля и элементы на панель MDX.
Затем на панель Многомерное выражение добавьте операцию, например "+", "-", "/", или "*".
Перетащите второй элемент с вкладки Поля и элементы на панель MDX.
Повторяйте действия 5–7 до тех пор, пока не создадите формулу со всеми нужными элементами.
Например, если вы создаете вычисляемый элемент с именем Core Products, который включает все категории продуктов, кроме двух, в области многомерных выражений может быть запрос, похожий на
[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
Подробнее о создании и использовании вычисляемых полей см. в статьях: