Вычисляемые ячейки в Excel
DAX (выражения анализа данных) — новый язык формул в PowerPivot, который включает в свой арсенал ряд стандартных формул Excel. Помимо них, DAX имеет много общего с другим языком, называемым MDX (многомерные выражения).
Мера — это формула, которая специально создается для числовых значений и попадает в поле Значения
сводной таблицы. Так, когда люди говорят DAX мера, они имеют в виду создание вычисляемого столбца с помощью формулы на языке DAX, для использования в сводной таблице в поле Значения.
Вы могли сталкиваться с вычисляемыми столбцами ранее, при создании обычной сводной таблицы в Excel. Но возможности таких столбцов ограничены. DAX меры могут использовать данные других вычисляемых столбцов. Например, можно создать один вычисляемый столбец для извлечения номера из текстовой строки, а затем использовать это число в другом вычисляемом столбце.
Добавление вычисляемого столбца
Щелчок по иконке Добавить, находящегося в группе Столбцы вкладки Конструктор, это то же самое, если вы выделите правый столбец Добавление столбца. Таким образом, если вы хотите добавить новую колонку, просто выберите любую пустую ячейку в правом столбце.
Формулы, используемые в вычисляемых столбцах, пишутся на языке DAX. После того, как вы ввели формулу и нажали клавишу Enter, вся колонка будет заполнена этой формулой.
Колонка может содержать только одну формулу.
Операторы языка DAX
Язык DAX поддерживает несколько операторов. Многие из них аналогичны операторам Excel:
- ±*/ объяснений не требуется, сложение, вычитание, умножение и деление
- & конкатенация (сцепление текста)
- ^ возведение в степень
- =, >, <, >=, <=, <> операторы сравнения
К тому же DAX поддерживает три дополнительных оператора для оценки логических выражений:
- && создает оператор «И», между двумя выражениями со значениями ПРАВДА/ЛОЖЬ. ([Регион]=»Запад»)&&([Продукт]=»ABC«).
- || создает оператор «ИЛИ», между двумя выражениями со значениями ПРАВДА/ЛОЖЬ. ([Регион]=»Запад»)||([Продукт]=»ABC«).
- ! создает оператор «НЕ». Изменяет значение ПРАВДА/ЛОЖЬ на противоположенное. !([Регион]=»Запад«)
Создание формул в PowerPivot
Для начала ввода формулы, выделите столбец Добавление столбца и введите знак равенства «=».
На этом этапе вы можете:
Выбрать колонку и PowerPivot введет название колонки в формулу
Ввести оператор
Щелкнуть по иконке fx, которая вызовет мастер вставки функции.
Вы можете начать вводить формулу или название таблицы. Система автоматического заполнения предложит список возможных названий таблиц или формул в зависимости от букв, которые вы уже ввели.
Добавление вычисляемого столбца с DAX формулой
Недостатком сводных таблиц, созданных в PowerPivot, является невозможность группировки дат по месяцам, годам и т.д. Прежде чем создавать сводную таблицу, давайте добавим вычисляемое поле в таблицу Demo с использованием формулы DAX.
- Выберите лист Demo в окне PowerPivot.
- Щелкните по первой пустой ячейке правой колонки Добавить столбец.
- Щелкните по иконке fx, находящейся правее поля ввода формул. Появится диалоговое окно Вставить функцию скатегориями Дата и время, Арифметические и тригонометрические операции, Статистические, Текст, Логические, Фильтр, Информация, Родители-потомки. Выберите категорию Дата и время из выпадающего списка. Вы обнаружите, что появившийся список не похож на список функций Excel. Первые 5 из 6 отображенных функций в списке — необычные и новые.
- К счастью, некоторые знакомые функции все же присутствуют в списке. Прокрутите вниз и выберите функцию YEAR (ГОД). Щелкните по первой ячейке в колонке Date. PowerPivot заполнит формулу =YEAR(demo[Date]. Закройте скобки и нажмите Enter, PowerPivot заполнит столбец формулами с соответствующими датами.
- Щелкните правой кнопкой мыши по вычисляемому столбцу, выберите Переименовать. И назовите колонку Год.
- Повторите процесс, чтобы добавить колонку с месяцем, используя формулу =MONTH(demo[Date])
Мы добавили два вычисляемых столбца с использованием формул DAX. Следующим этапом в изучении PowerPivot будет создание сводной таблицы.
Файл с примером данных для создания вычисляемых столбцов.
