Создание формул для вычислений
В этом разделе описан базовый процесс построения формулы в PowerPivot для Excel, показан пример создания вычисляемого столбца и описаны способы работы с таблицами. Раздел содержит следующие подразделы.
Меры и вычисляемые столбцы
Основные сведения о формулах
Работа с таблицами и столбцами
Устранение неполадок в формулах
После прочтения этого раздела см. дополнительные сведения в следующих разделах.
Меры и вычисляемые столбцы
В книге PowerPivot можно использовать формулы в вычисляемых столбцах и мерах.
Вычисляемый столбец — это столбец, добавляемый в существующую таблицу PowerPivot. Вместо вставки или импорта значений в столбец создается формула DAX, которая определяет значения столбца. Если включить таблицу PowerPivot в сводную таблицу (или сводную диаграмму), вычисляемый столбец можно использовать так же, как любой другой столбец данных.
Мера — это формула, которая создается специально для использования в сводной таблице или сводной диаграмме, использующей данные PowerPivot. Меры могут быть основаны на стандартных агрегатных функциях, например COUNT или SUM, либо на пользовательских формулах на языке выражений анализа данных (DAX). Мера используется в области Значения сводной таблицы. Чтобы разместить вычисленные результаты в другой области сводной таблицы, необходимо использовать вычисляемый столбец.
Дополнительные сведения см. в подразделе «Вычисляемые столбцы и меры» раздела Общие сведения по выражениям анализа данных (DAX).
Основные сведения о формулах
PowerPivot для Excel предоставляет DAX, новый язык формул для создания пользовательских вычислений. С помощью DAX пользователи могут определять специализированные вычисления в таблицах PowerPivot и сводных таблицах Excel. В DAX входят некоторые функции, которые используются в формулах Excel, и дополнительные функции, предназначенные для работы с реляционными данными и создания динамических агрегатов. Дополнительные сведения см. в разделе Общие сведения по выражениям анализа данных (DAX).
Формулы могут быть сложными, но в следующей таблице показаны простые формулы, которые можно использовать в вычисляемом столбце PowerPivot.
Формула |
Описание |
=TODAY() |
Вставляет текущую дату в каждую строку столбца. |
=3 |
Вставляет значение 3 в каждую строку столбца. |
=[Column1] + [Column2] |
Добавляет значения [Column1] и [Column2] и вставляет результат в вычисляемый столбец той же строки. |
Формулы PowerPivot, создаваемые для вычисляемых столбцов, во многом аналогичны формулам в Microsoft Excel. Формулы для мер создаются с помощью одного из следующих диалоговых окон. Диалоговое окно «Параметры меры» (стандартный агрегат) или Диалоговое окно «Параметры меры» (нестандартный агрегат).
Используйте следующие шаги при построении формулы.
Каждая формула должна начинаться со знака равенства.
Введите или выберите имя функции либо введите выражение.
Введите несколько первых букв имени нужной функции, и автозаполнение отобразит список доступных функций, таблиц и столбцов. Чтобы добавить элемент из списка автозаполнения в формулу, нажмите клавишу TAB.
Нажмите кнопку Fx, чтобы отобразить список доступных функций. Чтобы выбрать функцию из раскрывающегося списка, выделите ее с помощью клавиш со стрелками, затем нажмите кнопку «OК», чтобы добавить функцию в формулу.
Укажите аргументы функции, выбрав их из раскрывающегося списка возможных таблиц и столбцов или путем ввода значений.
Проверьте наличие синтаксических ошибок: убедитесь, что закрыты все скобки и правильно указаны ссылки на столбцы, таблицы и значения.
Нажмите клавишу ВВОД, чтобы принять формулу.
.gif "Примечание")
ПримечаниеКак только формула будет принята, вычисляемый столбец заполняется значениями. В мере нажатие клавиши ВВОД приводит к сохранению определения меры, и если мера новая, PowerPivot автоматически добавляет меру в область «Значения» сводной таблицы.
Создание простой формулы
В следующем примере показано, как создать вычисляемый столбец с помощью простой формулы на основе следующих данных:
SalesDate |
Subcategory |
Товар |
Продажи |
Количество |
|---|---|---|---|---|
1/5/2009 |
Accessories |
Carrying Case |
254995 |
68 |
1/5/2009 |
Accessories |
Mini Battery Charger |
1099.56 |
44 |
1/5/2009 |
Digital |
Slim Digital |
6512 |
44 |
1/6/2009 |
Accessories |
Telephoto Conversion Lens |
1662.5 |
18 |
1/6/2009 |
Accessories |
Tripod |
938.34 |
18 |
1/6/2009 |
Accessories |
USB Cable |
1230.25 |
26 |
Создание вычисляемого столбца с простой формулой |
|
Советы по использованию автозаполнения
Функцию автозаполнения формул можно использовать в середине существующей формулы со вложенными функциями. Текст, расположенный непосредственно перед точкой вставки, используется для отображения значений раскрывающегося списка, а остальной текст остается без изменений.
PowerPivot не добавляет закрывающие скобки функций и не выполняет автоматический подбор скобок. Необходимо убедиться, что каждая функция синтаксически правильна, иначе формулу нельзя сохранить или использовать. PowerPivot выделяет скобки, поэтому легче проверить, что они правильно закрыты.
Дополнительные сведения об использовании автозаполнения см. в разделах Вычисляемые столбцы и Меры в PowerPivot.
Работа с таблицами и столбцами
Таблицы PowerPivot внешне похожи на таблицы Excel, но отличаются тем, что работают с данными и формулами.
Формулы работают только с таблицами и столбцами, а не с отдельными ячейками, ссылками на диапазоны и массивами.
В формулах можно использовать связи для получения значений из связанных таблиц. Возвращаемые значения всегда связаны со значением в текущей строке.
Нельзя вставлять формулы на языке выражений анализа данных (DAX) в книгу Excel и наоборот.
Нельзя иметь неупорядоченные или неоднородные данные, как это можно делать на листе Excel. Каждая строка в таблице должна содержать одинаковое количество столбцов. Однако некоторые столбцы могут иметь пустые значения. Таблицы данных Excel и таблицы данных PowerPivot не являются взаимозаменяемыми, но можно устанавливать связи с таблицами Excel из PowerPivot и вставлять данные Excel в PowerPivot. Дополнительные сведения см. в разделах Добавление данных с помощью связанных таблиц Excel и Копирование и вставка данных в PowerPivot.
Ссылки на таблицы и столбцы в формулах и выражениях
На любую таблицу и любой столбец можно ссылаться по имени. Например, следующая формула показывает, как ссылаться на столбцы из двух таблиц с помощью полного имени:
=SUM('New Sales'[Amount]) + SUM('Past Sales'[Amount])
При вычислении формулы PowerPivot для Excel вначале проверяет общий синтаксис, а затем сравнивает указанные имена столбцов и таблиц с возможными столбцами и таблицами в текущем контексте. Если имя, использованное в формуле, определено неоднозначно либо столбец или таблица не найдены, будет выдана ошибка (строка #ERROR вместо значения данных в ячейках, в которых произошла ошибка). Дополнительные сведения о требованиях к именованию таблиц, столбцов и других объектов см. в подразделе «Требования к именам» раздела Спецификация синтаксиса DAX для PowerPivot.
| Примечание |
|---|
Контекст является важной характеристикой книг PowerPivot, позволяющей строить динамические формулы. Контекст определяется таблицами в книге, связями между таблицами и применяемыми фильтрами. Дополнительные сведения см. в разделе Контекст в формулах DAX. |
Связи между таблицами
Таблицы могут быть связаны с другими таблицами. Создание связей дает возможность выполнять уточняющие запросы данных из другой таблицы и сложные вычисления с помощью связанных значений. Например, можно использовать вычисляемый столбец для поиска всех заказов, связанных с текущим посредником, а затем суммировать их стоимость. Это действие эквивалентно параметризованному запросу: для каждой строки текущей таблицы можно вычислить различные суммы.
Многие функции DAX требуют наличия связи между двумя или несколькими таблицами, чтобы найти столбцы, на которые сделана ссылка, и возвратить осмысленные результаты. Некоторые функции пытаются определить такую связь, но для получения наилучших результатов нужно всегда создавать связь, если это возможно. Дополнительные сведения см. в разделах:
При работе со сводными таблицами особенно важно связывать все таблицы, используемые сводной таблицей, для правильного вычисления сводных данных. Дополнительные сведения см. в разделе Работа со связями в сводных таблицах.
Устранение неполадок в формулах
Если во время определения вычисляемого столбца выводится ошибка, значит формула может содержать синтаксическую ошибку или семантическую ошибку.
Синтаксические ошибки устранять проще всего. Они обычно вызваны пропущенной скобкой или запятой. Справку по синтаксису отдельных функций см. в разделе Справочник по функциям DAX.
Ошибки другого типа происходят, когда синтаксис задан правильно, но значение упоминаемого столбца не имеет смысла в контексте формулы. Семантические ошибки могут вызываться следующими причинами.
Формула ссылается на несуществующий столбец, таблицу или функцию.
Формула составлена правильно, но подсистема обработки данных PowerPivot при выборке данных обнаруживает несоответствие типов и возвращает ошибку.
Формула передает функции неверное число или тип параметров.
Формула ссылается на другой столбец, который содержит ошибку, поэтому ее значения недопустимы.
Формула ссылается на столбец, который не обработан. Такое может случаться, если книга изменяется в ручном режиме, а после изменения не выполняется обновление данных и обновление вычислений.
В первых четырех случаях DAX помечает весь столбец, содержащий недопустимую формулу. В последнем случае DAX выделяет имя столбца серым цветом, чтобы показать, что он находится в необработанном состоянии.