В этой статье мы рассмотрим основы создания формул вычислений для вычисляемых столбцов и мер в Power Pivot. Если вы не знакомы с DAX, обязательно проверка краткое руководство. Изучение основ DAX за 30 минут.
Основные сведения о формулах
Power Pivot предоставляет выражения анализа данных (DAX) для создания пользовательских вычислений в Power Pivot таблицах и в сводных таблицах Excel. DAX включает в себя некоторые функции, используемые в формулах Excel, и дополнительные функции, предназначенные для работы с реляционными данными и выполнения динамической агрегации.
Ниже приведены некоторые основные формулы, которые можно использовать в вычисляемом столбце:
|
Формула |
Описание |
|
Вставляет текущую дату в каждую строку столбца. |
|
Вставляет значение 3 в каждую строку столбца. |
|
Добавляет значения в одну строку [Столбец1] и [Столбец2] и помещает результаты в ту же строку вычисляемого столбца. |
Формулы Power Pivot для вычисляемых столбцов можно создавать так же, как и формулы в Microsoft Excel.
При создании формулы выполните следующие действия.
-
Каждая формула должна начинаться со знака равенства.
-
Можно ввести или выбрать имя функции или ввести выражение.
-
Начните вводить первые несколько букв нужной функции или имени, а функция автозаполнения отображает список доступных функций, таблиц и столбцов. Нажмите клавишу TAB, чтобы добавить элемент из списка автозаполнения в формулу.
-
Нажмите кнопку Fx , чтобы отобразить список доступных функций. Чтобы выбрать функцию из раскрывающегося списка, используйте клавиши со стрелками, чтобы выделить элемент, а затем нажмите кнопку ОК , чтобы добавить функцию в формулу.
-
Предоставьте аргументы функции, выбрав их из раскрывающегося списка возможных таблиц и столбцов или введя значения или другую функцию.
-
Проверьте наличие синтаксических ошибок: убедитесь, что все круглые скобки закрыты и что столбцы, таблицы и значения указаны правильно.
-
Нажмите клавишу ВВОД, чтобы подтвердить формулу.
Примечание: В вычисляемом столбце, как только вы принимаете формулу, столбец заполняется значениями. В мере нажатие клавиши ВВОД сохраняет определение меры.
Create простую формулу
|
Создание вычисляемого столбца с простой формулой
Затем значения заполняются в новом вычисляемом столбце для всех строк. |
Советы по использованию функции автозаполнения
-
Функцию автозаполнения формул можно использовать в середине существующей формулы со вложенными функциями. Текст, расположенный непосредственно перед точкой вставки, используется для отображения значений раскрывающегося списка, а остальной текст остается без изменений.
-
Power Pivot не добавляет закрывающая круглая скобка функций или автоматически сопоставляет круглые скобки. Необходимо убедиться, что каждая функция синтаксически правильна, иначе нельзя сохранить или использовать формулу. Power Pivot выделяет скобки, что упрощает проверка, если они должным образом закрыты.
Работа с таблицами и столбцами
Power Pivot таблицы похожи на таблицы Excel, но отличаются тем, как они работают с данными и формулами:
-
Формулы в Power Pivot работать только с таблицами и столбцами, а не с отдельными ячейками, ссылками на диапазоны или массивами.
-
Формулы могут использовать связи для получения значений из связанных таблиц. Извлекаемые значения всегда связаны с текущим значением строки.
-
Нельзя вставить Power Pivot формулы на лист Excel и наоборот.
-
Вы не можете иметь нерегулярные или неоднородные данные, как на листе Excel. Каждая строка в таблице должна содержать одинаковое количество столбцов. Однако в некоторых столбцах могут быть пустые значения. Таблицы данных Excel и таблицы данных Power Pivot не являются взаимозаменяемыми, но можно связать с таблицами Excel из Power Pivot и вставить данные Excel в Power Pivot. Дополнительные сведения см. в разделах Добавление данных листа в модель данных с помощью связанной таблицы и Копирование и вставка строк в модель данных в Power Pivot.
Ссылки на таблицы и столбцы в формулах и выражениях
Вы можете ссылаться на любую таблицу и столбец, используя их имя. Например, в следующей формуле показано, как ссылаться на столбцы из двух таблиц с использованием полного имени:
=СУММ('Новые продажи'[Сумма]) + СУММ('Прошлые продажи'[Сумма])
При вычислении формулы Power Pivot сначала проверяет общий синтаксис, а затем имена столбцов и таблиц, которые вы предоставляете, с возможными столбцами и таблицами в текущем контексте. Если имя неоднозначно или столбец или таблицу не удается найти, вы получите ошибку в формуле (строка #ERROR вместо значения данных в ячейках, где возникает ошибка). Дополнительные сведения о требованиях к именованию для таблиц, столбцов и других объектов см. в разделе Требования к именованию в спецификации синтаксиса DAX для Power Pivot.
Примечание: Контекст — важная функция моделей данных Power Pivot, которая позволяет создавать динамические формулы. Контекст определяется таблицами в модели данных, связями между таблицами и примененными фильтрами. Дополнительные сведения см. в статье Контекст в формулах DAX.
Связи таблиц
Таблицы могут быть связаны с другими таблицами. Создавая связи, вы получаете возможность искать данные в другой таблице и использовать связанные значения для выполнения сложных вычислений. Например, можно использовать вычисляемый столбец для поиска всех записей доставки, связанных с текущим торговым посредником, а затем суммировать затраты на доставку для каждого из них. Результат похож на параметризованный запрос: можно вычислить разную сумму для каждой строки в текущей таблице.
Для многих функций DAX требуется наличие связи между таблицами или между несколькими таблицами, чтобы найти столбцы, на которые вы ссылались, и вернуть результаты, которые имеют смысл. Другие функции будут пытаться определить связь; однако для достижения наилучших результатов всегда следует создавать связь там, где это возможно.
При работе со сводными таблицами особенно важно подключить все таблицы, используемые в сводной таблице, чтобы можно было правильно вычислить сводные данные. Дополнительные сведения см. в статье Работа со связями в сводных таблицах.
Устранение ошибок в формулах
Если при определении вычисляемого столбца возникает ошибка, формула может содержать синтаксическую ошибку или семантическую ошибку.
Синтаксические ошибки устранять проще всего. Они обычно вызваны пропущенной скобкой или запятой. Сведения о синтаксисе отдельных функций см. в справочнике по функциям DAX.
Ошибки другого типа возникают, когда синтаксис задан правильно, но значение упоминаемого столбца не имеет смысла в контексте формулы. Такие семантические ошибки могут быть вызваны любой из следующих проблем:
-
Формула ссылается на несуществующий столбец, таблицу или функцию.
-
Формула выглядит правильной, но когда Power Pivot извлекает данные, обнаруживает несоответствие типов и выдает ошибку.
-
Формула передает функции неверное число или тип параметров.
-
Формула ссылается на другой столбец, который содержит ошибку, поэтому ее значения недопустимы.
-
Формула ссылается на столбец, который не был обработан. Это может произойти, если вы перевели книгу в ручной режим, внесли изменения, а затем никогда не обновляли данные или не обновляли вычисления.
В первых четырех случаях DAX помечает весь столбец, содержащий недопустимую формулу. В последнем случае DAX выделяет имя столбца серым цветом, чтобы показать, что он находится в необработанном состоянии.
