Foreversoft.ru

IT Справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Dax формулы excel

DAX-Урок 1. Что такое DAX. Основные понятия.

Что такое DAX?

DAX — язык формул (расшифровывается как Data Analysis eXpressions — выражения для анализа данных) был разработан компанией Microsoft. Данный язык не является самостоятельным, а предназначен исключительно для автоматизации расчетов в приложении PowerPivot для MS Excel.

Коротко говоря, DAX — это формулы для сводных таблиц. Большинство функций DAX имеют схожие имена и характеристики по сравнению с обычными функциями Excel, однако могут использоваться только в PowerPivot. Как известно, обычные Сводные таблицы могут содержать расчетные поля, однако в этих полях могут производиться простые арифметические действия — сложение-вычитание и умножение-деление. Чтобы увеличить функционал расчетных полей и был создан DAX. В целом, DAX не является сложным языком, как может показаться на первый взгляд, нужно только понять принципы его работы. Разница между формулами Excel и формулам DAX состоит в том, что в Excel мы оперируем отдельными ячейками и диапазонами, а в DAX можем обращаться только к целым таблицам и к их столбцам.

Чтобы работать с DAX кодом, нам нужно установить PowerPivot для Excel 2010 или перейти на одноименную вкладку в Excel 2013.

Основные функции DAX

Как и в Excel функции DAX относятся к определенным категориям, а именно:

  • Функции фильтров
  • Математические функции
  • Статистические функции
  • Логические функции
  • Функции даты и времени
  • Текстовые функции
  • Информационные функции

    Расчеты в PowerPivot

    В PowerPivot пользователь может создавать расчетные поля двух типов: Расчетные столбцы и Меры.

    Расчетный столбец — дополнительный столбец создается пользователем в любой таблице, которая загружена в PowerPivot.

    Мера — расчетное поле в Сводной таблицы. Мера используется в области Значение сводной таблицы. Чтобы разместить рассчитанные результаты в другие области сводной таблицы, нужно использовать Расчетный столбец.

    Одни и те же формулы могут вести себя по-разному, в зависимости от того, используются они в расчетном столбце или в мере. Если формула используется в расчетном столбце, она применяется к каждой строке столбца по всей таблице. Значение может зависеть от контекста строк. В мере результат вычислений еще больше зависит от контекста. Это означает, что конструкция сводной таблицы и выбор заголовков строк и столбцов влияют на значение, которые используются в вычислениях.

    Контекст в формулах DAX

    В языке DAX присутствует также такое поняння как контекст, который является очень важным для понимания функционирования формул. На формулы в PowerPivot могут влиять фильтры, примененные в сводной таблице, связи между таблицами и фильтры, имеющиеся в формулах. Контекст позволяет выполнять динамический анализ. При построении и устранении неполадок в формулах важно понимать предназначение контекста.

    Итак, в DAX существуют три типа контекста: контекст фильтра, контекст строки и контекст запроса.

    Контекст фильтра — это набор значений, разрешенных в каждом столбце в зависимости от ограничений фильтра, которые применяются к строке или определенных критериям фильтра в формуле.

    Контекст строки — фактически соответствует понятию текущей строки. Если создан вычисляемый столбец, то контекстом строки служат значение в каждой отдельной строке и значение в столбцах, связанных с текущей строкой.

    Контекст запроса — относится к подмножеству данных, которые неявным образом создаются для каждой ячейки сводной таблицы в зависимости от заголовков строки и столбца в сводной таблице.

    Exceltip

    Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

    Создание вычисляемого столбца с помощью формул DAX

    DAX (выражения анализа данных) — новый язык формул в PowerPivot, который включает в свой арсенал ряд стандартных формул Excel. Помимо них, DAX имеет много общего с другим языком, называемым MDX (многомерные выражения).

    Мера – это формула, которая специально создается для числовых значений и попадает в поле Значения сводной таблицы. Так, когда люди говорят DAX мера, они имеют в виду создание вычисляемого столбца с помощью формулы на языке DAX, для использования в сводной таблице в поле Значения.

    Вы могли сталкиваться с вычисляемыми столбцами ранее, при создании обычной сводной таблицы в Excel. Но возможности таких столбцов ограничены. DAX меры могут использовать данные других вычисляемых столбцов. Например, можно создать один вычисляемый столбец для извлечения номера из текстовой строки, а затем использовать это число в другом вычисляемом столбце.

    Добавление вычисляемого столбца

    Щелчок по иконке Добавить, находящегося в группе Столбцы вкладки Конструктор, это то же самое, если вы выделите правый столбец Добавление столбца. Таким образом, если вы хотите добавить новую колонку, просто выберите любую пустую ячейку в правом столбце.

    Формулы, используемые в вычисляемых столбцах, пишутся на языке DAX. После того, как вы ввели формулу и нажали клавишу Enter, вся колонка будет заполнена этой формулой.

    Колонка может содержать только одну формулу.

    Операторы языка DAX

    Язык DAX поддерживает несколько операторов. Многие из них аналогичны операторам Excel:

    • +-*/ объяснений не требуется, сложение, вычитание, умножение и деление
    • & конкатенация (сцепление текста)
    • ^ возведение в степень
    • =, >, =, операторы сравнения

    К тому же DAX поддерживает три дополнительных оператора для оценки логических выражений:

    • && создает оператор «И», между двумя выражениями со значениями ПРАВДА/ЛОЖЬ. ([Регион]=”Запад”)&&([Продукт]=”ABC”).
    • || создает оператор «ИЛИ», между двумя выражениями со значениями ПРАВДА/ЛОЖЬ. ([Регион]=”Запад”)||([Продукт]=”ABC”).
    • ! создает оператор «НЕ». Изменяет значение ПРАВДА/ЛОЖЬ на противоположенное. !([Регион]=”Запад”)
    Читать еще:  Типы данных в таблице excel

    Создание формул в PowerPivot

    Для начала ввода формулы, выделите столбец Добавление столбца и введите знак равенства «=».

    На этом этапе вы можете:

    Выбрать колонку и PowerPivot введет название колонки в формулу

    Щелкнуть по иконке fx, которая вызовет мастер вставки функции.

    Вы можете начать вводить формулу или название таблицы. Система автоматического заполнения предложит список возможных названий таблиц или формул в зависимости от букв, которые вы уже ввели.

    Добавление вычисляемого столбца с DAX формулой

    Недостатком сводных таблиц, созданных в PowerPivot, является невозможность группировки дат по месяцам, годам и т.д. Прежде чем создавать сводную таблицу, давайте добавим вычисляемое поле в таблицу Demo с использованием формулы DAX.

    1. Выберите лист Demo в окне PowerPivot.
    2. Щелкните по первой пустой ячейке правой колонки Добавить столбец.
    3. Щелкните по иконке fx, находящейся правее поля ввода формул. Появится диалоговое окно Вставить функцию с категориями Дата и время, Арифметические и тригонометрические операции, Статистические, Текст, Логические, Фильтр, Информация, Родители-потомки. Выберите категорию Дата и время из выпадающего списка. Вы обнаружите, что появившийся список не похож на список функций Excel. Первые 5 из 6 отображенных функций в списке — необычные и новые.
    4. К счастью, некоторые знакомые функции все же присутствуют в списке. Прокрутите вниз и выберите функцию YEAR (ГОД). Щелкните по первой ячейке в колонке Date. PowerPivot заполнит формулу =YEAR(demo[Date]. Закройте скобки и нажмите Enter, PowerPivot заполнит столбец формулами с соответствующими датами.
    5. Щелкните правой кнопкой мыши по вычисляемому столбцу, выберите Переименовать. И назовите колонку Год.
    6. Повторите процесс, чтобы добавить колонку с месяцем, используя формулу =MONTH(demo[Date])

    Мы добавили два вычисляемых столбца с использованием формул DAX. Следующим этапом в изучении PowerPivot будет создание сводной таблицы.

    Вам также могут быть интересны следующие статьи

    6 комментариев

    День добрый!
    У меня возникла проблема/ошибка/глюк при написании формул в Power Piviot:
    он не воспринимает знак » ; » как разделитель между аргументами в формуле, а требует знак » , » (как в американской версии Excel). При этом если я буду писать с » , » при завершении формулы он выдает ошибку, и тогда уже вручную надо все заменять на » ; «. Сталкивались ли Вы когда-нибудь с подобным? Что можно сделать?
    Различные способы переустановки ( через Панель Управления, с помощью спец.программы Microsoft) результата не дали.

    Ксения, с подобной проблемой не сталкивался, формулы воспринимают точку с запятой как должное, поэтому конкретного посоветовать не могу. Как вариант, попробуйте переустановить Excel

    Подскажите пожалуйста как выполнить условную функцию по дате?
    Цель: объединить данные в таблице с фактом с таблицой с планом в одну таблицу.
    Не зная как это сделать инчае, решил воспользоваться условной функцией if и отталкиваться при объединении данных из двух таблиц от текущей даты с помощью TODAY()
    =if(Табл 1[Дата]

      причем функцию прописываю в вычисляемом поле

      спасибо за статью.
      как что то вводное нормально, но свою типичную ситуацию я в ней не нашел

      например раньше, подключившись к внешнему многомиллионному источнику данных, создав сводную таблицу я запросто мог создать типичное вычисляемое поле

      , сейчас после добавления данных к «модели данных» возможность создавать вычисляемые поля исчезла

      а сделать предложенными вами способами нельзя, т.к. не будет группировки
      вот например предложенный вами пример создания вычисляемой колонки делает вот так,
      (a1/b1)+(a2/b2)+(a3/b3)+(a3/b3)+…..+(aN/bN)

      ,а что делать если нужно вот так?
      sum[(a1+a2+a3+a4+a5)/(b1+b2+b3+b4+b5)] с группировкой по категориям

      , это для того чтоб померить эффективность различных сотрудников в компании

      Добрый вечер, уважаемые. У меня проблема, есть таблицы с данными «план» и «факт», они имеют связь через таблицу дат (день, месяц, квартал, год). Подскажите пожалуйста как можно произвести простые вычисления план-факта в PowerPivot? нужно вычислить разницу между планом и фактом за отчетный период (план / факт и план — факт). Спасибо.

      Power Pivot: Оконные функции под соусом DAX

      [в связи со спорным переносом 1 части поста на geektimes (при том что 2-я часть осталась на хабре) возвращаю 1-ю часть на место]

      Работая в сфере аналитики и мониторя различные инструменты BI рано или поздно наталкиваешься на обзор или упоминание надстройки Power Pivot Excel. В моем случае знакомство с ним произошло на конференции Microsoft Data Day.

      Особых впечатлений после презентации инструмент не оставил: Да, бесплатен (в рамках лицензии Office), да — есть некий ETL функционал в части получения данных с разрозненных источников (БД,csv,xls, и т.д.), Join-ов этих источников и скармливания в оперативку записей на порядки выше 1 млн.строк в Excel. Короче, посмотрел и забыл.

      А вспомнить пришлось, когда появилась необходимость идентификации определённых явлений в данных

      На написание данной статьи сподвигло то что в рунете по этому инструменту не много какой либо детальной информации о конкретных приемах работы, все больше о звездах, поэтому решил, изучая этот инструмент, написать данный обзор.

      Читать еще:  Подсчет количества слов в excel

      Собственно, постановка задачи (на обезличенном примере) следующая:

      В исходных данных csv файла:

      Есть торговые точки, детализированные до строк накладных, при этом допускается для точек с одинаковым наименованием иметь разные адреса только в том случае если они расположены в разных городах, но в исходном массиве данных есть точки, у которых попадаются разные адреса в одном и том же городе при том, что названия точек одинаковые (имя торговой точки уникально, т.е. это единица сети или отдельно стоящая точка). Как частный случай в агрегированном виде:

      Поиску и очистке данных штатными средствами office мешают следующие обстоятельства:

      • Детализация данных до строк накладной
      • Количество записей в несколько миллионов строк
      • Отсутствие sql инструментария (К примеру: Access — не в комплекте)

      Конечно можно залить любую бесплатную СУБД (хоть десктоп версию, хоть серверную) но для этого во-первых нужны админские права, во-вторых статья была бы уже не про Power Pivot.

      Задача: для каждой атомарной записи требуется дополнительное вычисляемое поле, которое посчитает для каждого наименования торговой точки уникальное количество адресов в рамках того же города. Данное поле требуется для быстрого нахождения всех имен торговых точек в городе, где адресов больше 1.

      Думаю, удобнее всего решать и рассказывать итерационно, с допущением что у нас знания по DAX в зачаточном уровне.
      Поэтому предлагаю пока оторваться от задачи и рассмотреть некоторые базовые аспекты.

      Шаг 1. Чем отличается вычисляемый столбец от вычисляемой меры?
      Вот пример вычисляемого столбца для выделения НДС из поля отгрузки с НДС используя встроенные формулы DAX:

      =ROUND([Отгрузка с НДС]*POWER(1,18;-1)*0,18;2)

      Как видно из примера вычисляемый столбец (Назовем его НДС) работает с каждой атомарной записью по горизонтали.
      Теперь добавим вычисляемое поле для цены за штуку без НДС:

      =ROUND([Отгрузка с НДС]*POWER(1,18;-1)/[Отгрузка шт];2)

      Теперь для сравнения добавим в меру расчет средней цены за штуку:

      Средняя цена за штуку без НДС: =ROUND(AVERAGE([Поле_Цена за штуку без НДС]);2)

      Как видно из формулы, мера работает со столбцом исходных данных по вертикали, поэтому она всегда должна содержать в себе какую то работающую с множеством функцию (Сумму, среднюю, дисперсию и т.д.)

      При возврате в сводную таблицу Excel это выглядит так:

      Обратите внимание, если вычисляемое поле НДС на каждом уровне данных (зеленая обводка на уровне торговой точки, города или итого по таблице) показывает сумму, что в принципе – корректно, то сумма цен вычисляемого поля «Цена за штуку без НДС» (красная обводка) вызывает вопросы.
      А вот вычисляемая мера «Средняя цена за штуку без НДС» вполне имеет право на жизнь в рамках данного аналитического куба.

      Отсюда делаем вывод, что вычисляемое поле «Цена за штуку без НДС» является вспомогательным инструментом для расчета меры «Средняя цена за штуку без НДС» и дабы не смущать пользователя этим полем мы скроем его из списка клиентских средств, оставив меру средней цены.

      Еще одно отличие меры от столбца – она позволяет добавить визуализацию:

      К примеру, построим KPI степени разброса цен с целевой границей 35% путем деления корня из дисперсии на среднюю арифметическую.

      К_вар:=STDEV.P([Поле_Цена за штуку без НДС])/AVERAGE([Поле_Цена за штуку без НДС])

      В итоге видим такую таблицу в Excel (кстати расчетное вспомогательное поле цен уже не в списке доступных полей справа):

      Двойной клик на 80%-м коэффициенте показывает, что цены действительно колбасит вокруг средней:

      Cильнее чем при коэффициенте 15%:

      Итак, на данном шаге мы рассмотрели основные отличия мер от полей в рамках PowerPivot.

      Шаг 2. Усложняем: Посчитаем долю каждой записи в общих продажах.
      Вот первый пример сравнения подходов оконных функций MS SQL Server и DAX:

      Понятно, что в рамках сводных таблиц это делается буквально в 2 клика мышкой не касаясь клавиатуры, но для понимания попробуем это непосредственно в PowerPivot с применением формул.

      На sql я бы это написал так (за огрехи не пинать, ибо Word синтаксис SQL Server не проверяет):

      Здесь, как можно заметить окно открывается через все записи датасэта, попробуем аналогичную вещь в PowerPivot:

      =[Отгрузка шт]/CALCULATE(SUM([Отгрузка шт]);ALL(‘Таблица1’))

      Основное внимание обратим к знаменателю: Я уже упоминал выше что основное отличие вычисляемого поля от меры заключается в том что в поле формулы считают по горизонтали ( в рамках одной записи) а меры – по вертикали ( в рамках одного атрибута). Здесь мы смогли скрестить свойства поля и свойство меры через метод CALCULATE. И если ширину окна в SQL мы отрегулировали через Over() то здесь мы сделали это через All().

      Попробуем теперь, обладая данным навыком, сделать с нашими данными что –нибудь полезное, например, вспомнив что показатель разброса цен вокруг средних варьировался в широком диапазоне, попробуем выделить статистические выбросы цен через правило 3-х сигм.

      Оконные функции на sql будут смотреться так:

      А вот то же самое в DAX:

      =if(ABS([Поле_Цена за штуку без НДС]-CALCULATE(AVERAGE([Поле_Цена за штуку без НДС]);ALL(‘Таблица1’)))>(3*CALCULATE(STDEV.P([Поле_Цена за штуку без НДС]);all(‘Таблица1’)));1;0)

      Читать еще:  Vba excel чтение текстового файла

      Как видите, цена несколько высоковата при средней арифметической 40,03 руб.

      Шаг 3. Сужаем окна.
      Попробуем теперь посчитать в вычисляемом поле каждой записи общее количество записей в рамках того города, к которому принадлежит и данная запись.
      На MS sql Server оконные функции будут выглядеть так:

      В DAX:
      =CALCULATE(COUNTROWS(‘Таблица1’);ALLEXCEPT(‘Таблица1’;’Таблица1′[Город]))

      Обратите внимание на разницу в отображении данных в таблице, я специально бросил адреса в область мер что бы посчитать их количество и сравнить с новым полем которое вывел в заголовок строк после имени торговой точки.

      Отчетлива видна разница: если обычный расчет количества адресов идет для каждой точки в городе и потом только выводит промежуточный итог для агрегата «Город» то использование оконных функций позволяет присвоить каждой атомарной записи значение любого агрегата, либо использовать его в каких-то промежуточных расчетах вычисляемого поля ( как было показано выше).

      Возвращаемся к исходной задаче
      Итак, напомню, исходная постановка задачи: для каждой атомарной записи требуется дополнительное вычисляемое поле, которое посчитает для каждого наименования торговой точки уникальное количество адресов в рамках того же города. Не забываем, что датасэт у нас детализирован до строк накладной, поэтому перед подсчетом адресов внутри окна их необходимо сгруппировать.

      Запрос на SQL Server:

      Теперь нам ничего не мешает это сделать и в DAX:

      В итоге у нас появилась возможность отобрать подозрительные записи, где на одну и ту же точку в одном городе приходится более 1 адреса.

      Конечно в процессе изучения (пробежавшись взглядом на другие формулы) становится понятно что DAX в PowerPivot гораздо мощнее чем показано в данном топике, но объять необъятное за раз – точно не получится.

      Надеюсь было интересно.
      Продолжение статьи здесь

      DAX-Урок 1. Что такое DAX. Основные понятия.

      Что такое DAX?

      DAX — язык формул (расшифровывается как Data Analysis eXpressions — выражения для анализа данных) был разработан компанией Microsoft. Данный язык не является самостоятельным, а предназначен исключительно для автоматизации расчетов в приложении PowerPivot для MS Excel.

      Коротко говоря, DAX — это формулы для сводных таблиц. Большинство функций DAX имеют схожие имена и характеристики по сравнению с обычными функциями Excel, однако могут использоваться только в PowerPivot. Как известно, обычные Сводные таблицы могут содержать расчетные поля, однако в этих полях могут производиться простые арифметические действия — сложение-вычитание и умножение-деление. Чтобы увеличить функционал расчетных полей и был создан DAX. В целом, DAX не является сложным языком, как может показаться на первый взгляд, нужно только понять принципы его работы. Разница между формулами Excel и формулам DAX состоит в том, что в Excel мы оперируем отдельными ячейками и диапазонами, а в DAX можем обращаться только к целым таблицам и к их столбцам.

      Чтобы работать с DAX кодом, нам нужно установить PowerPivot для Excel 2010 или перейти на одноименную вкладку в Excel 2013.

      Основные функции DAX

      Как и в Excel функции DAX относятся к определенным категориям, а именно:

    • Функции фильтров
    • Математические функции
    • Статистические функции
    • Логические функции
    • Функции даты и времени
    • Текстовые функции
    • Информационные функции

      Расчеты в PowerPivot

      В PowerPivot пользователь может создавать расчетные поля двух типов: Расчетные столбцы и Меры.

      Расчетный столбец — дополнительный столбец создается пользователем в любой таблице, которая загружена в PowerPivot.

      Мера — расчетное поле в Сводной таблицы. Мера используется в области Значение сводной таблицы. Чтобы разместить рассчитанные результаты в другие области сводной таблицы, нужно использовать Расчетный столбец.

      Одни и те же формулы могут вести себя по-разному, в зависимости от того, используются они в расчетном столбце или в мере. Если формула используется в расчетном столбце, она применяется к каждой строке столбца по всей таблице. Значение может зависеть от контекста строк. В мере результат вычислений еще больше зависит от контекста. Это означает, что конструкция сводной таблицы и выбор заголовков строк и столбцов влияют на значение, которые используются в вычислениях.

      Контекст в формулах DAX

      В языке DAX присутствует также такое поняння как контекст, который является очень важным для понимания функционирования формул. На формулы в PowerPivot могут влиять фильтры, примененные в сводной таблице, связи между таблицами и фильтры, имеющиеся в формулах. Контекст позволяет выполнять динамический анализ. При построении и устранении неполадок в формулах важно понимать предназначение контекста.

      Итак, в DAX существуют три типа контекста: контекст фильтра, контекст строки и контекст запроса.

      Контекст фильтра — это набор значений, разрешенных в каждом столбце в зависимости от ограничений фильтра, которые применяются к строке или определенных критериям фильтра в формуле.

      Контекст строки — фактически соответствует понятию текущей строки. Если создан вычисляемый столбец, то контекстом строки служат значение в каждой отдельной строке и значение в столбцах, связанных с текущей строкой.

      Контекст запроса — относится к подмножеству данных, которые неявным образом создаются для каждой ячейки сводной таблицы в зависимости от заголовков строки и столбца в сводной таблице.

    • Ссылка на основную публикацию
      Adblock
      detector