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