Microsoft Excel 2013 предоставляет разнообразные возможности бизнес-аналитики, которые позволяют создавать функциональные отчеты, системы показателей и панели мониторинга. Новые улучшенные возможности позволяют создавать вычисляемые объекты (например, вычисляемые показатели, элементы и поля). Прочтите эту статью, чтобы узнать о таких объектах и о том, поддерживаются ли они службами Excel.
Вычисляемые объекты в службах Excel
В Excel можно создавать такие вычисляемые объекты: вычисляемые показатели, вычисляемые элементы и вычисляемые поля. Они позволяют задавать и использовать настраиваемые вычисления и наборы элементов, не существующие в базах данных, которые используются для создания отчетов сводной диаграммы и сводной таблицы.
Если книга содержит вычисляемые объекты, вы можете предоставить к ней общий доступ, передав ее в библиотеку SharePoint. В зависимости от настроек среды SharePoint, в окне браузера можно просматривать книги, содержащие вычисляемые объекты, а также работать с ними. Но в отдельных случаях эта возможность не поддерживается.
Если ваша организация использует Office Web Apps сервер вместе с SharePoint Server 2013 (локальным), то для отрисовки книг в окне браузера используется либо службы Excel (SharePoint server 2013), либо Excel Web App (Office Web Apps Server). Это решение может повлиять на то, будут ли книги, которые содержат вычисляемые поля (созданные с помощью Power Pivot для Excel), просматриваться в окне браузера.
В таблице ниже приведены сведения о том, поддерживаются ли вычисляемые элементы в службах 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 в SharePoint с Excel Web App
-
Бизнес-аналитика в 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, пока не будут добавлены все элементы формулы.Например, если вы создаете вычисляемую меру с именем "доход", то на панели MDX может быть запрос, похожий на [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 до тех пор, пока не создадите формулу со всеми нужными элементами.Например, если вы создаете вычисляемый элемент «основные продукты», включающий все категории продуктов (кроме двух), в области многомерных выражений может появиться запрос, подобный [Product].[Product Categories]-[Product].[Product Categories].[Category].&[4]-[Product].[Product Categories].[Category].&[3]
-
Нажмите кнопку Проверить MDX, чтобы убедиться, что запрос работает правильно.
-
Чтобы создать вычисляемый элемент, нажмите кнопку ОК.
-
Чтобы добавить вычисляемый элемент в отчет сводной таблицы или сводной диаграммы, сделайте следующее:
-
убедитесь, что для отчета выбран хотя бы один показатель;
-
в списке Поля сводной таблицы или Поля сводной диаграммы раскройте родительское измерение, указанное в шаге 4;
-
установите флажок рядом с иерархией измерения, соответствующей той иерархии, которая использовалась для создания вычисляемого элемента. В отчете отображаются сведения для всех элементов измерения в группе, включая созданный вычисляемый элемент.
-
-
(Это действие не является обязательным.) Чтобы в отчете отображались только сведения вычисляемого элемента, сделайте следующее:
-
в списке Поля сводной таблицы или Поля сводной диаграммы наведите указатель мыши на иерархию измерения, содержащую вычисляемый элемент;
-
когда появится стрелка вниз, щелкните или коснитесь ее, чтобы открыть диалоговое окно Выбор поля;
-
снимите флажки всех элементов, кроме созданного вычисляемого элемента.
-
Вычисляемые поля
Вычисляемые поля удобно использовать, когда нужно создать вычисляемый объект в сводной таблице или отчете, в которых используются не многомерные данные, хранящиеся в службах аналитики, а модель данных, созданная с помощью Power Pivot в Excel. Значения в вычисляемых полях могут изменяться в зависимости от контекста. Контекст определяется выбранными строками, столбцами, фильтрами или пользовательской формулой Data Analysis Expressions (DAX), созданной в PowerPivot.
Подобно вычисляемым показателям и элементам вычисляемые поля отображаются в списке "Поля сводной таблицы" (или "Поля сводной диаграммы"), как правило, в группе "Значения". Создать вычисляемое поле можно несколькими способами.
Создание вычисляемого поля в Excel
Подробнее о создании и использовании вычисляемых полей см. в статьях:
