Краткое руководство - использование функций в формулах

DAX обозначает D ata A- nalysis E x . DAX &#; это язык формул, представляющий собой набор функций, операторов и констант, которые можно использовать в формуле или выражении для вычисления и возврата одного или нескольких значений. DAX &#; это язык формул, связанный с моделью данных Microsoft Excel Power Pivot и Microsoft Power BI.

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

Для более глубокого понимания DAX, обратитесь к учебнику &#; DAX в этой библиотеке учебников.

Что такое функция DAX?

Функция DAX &#; это встроенная функция на языке DAX, позволяющая вам выполнять различные действия с данными в таблицах в вашей модели данных.

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

Функции DAX играют важную роль в использовании DAX для моделирования данных и составления отчетов.

Функции Excel и DAX

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

Сходства между функциями Excel и DAX

• Некоторые функции DAX имеют то же имя и общее поведение, что и функции Excel.

• DAX имеет функции поиска, которые аналогичны функциям поиска в массиве и векторе в Excel.

Некоторые функции DAX имеют то же имя и общее поведение, что и функции Excel.

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

Различия между функциями Excel и DAX

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

• Вы не можете использовать функции DAX в формуле Excel или использовать функции Excel в формуле DAX без необходимых изменений.

• Функции Excel принимают ссылку на ячейку или диапазон ячеек в качестве ссылки. Функции DAX никогда не принимают ссылку на ячейку или диапазон ячеек в качестве ссылки, а вместо этого принимают столбец или таблицу в качестве ссылки.

• Функции даты и времени в Excel возвращают целое число, представляющее дату в виде серийного номера. Функции даты и времени DAX возвращают тип данных datetime, который находится в DAX, но не в Excel.

• В Excel нет функций, возвращающих таблицу, но некоторые функции могут работать с массивами. Многие из функций DAX могут легко ссылаться на полные таблицы и столбцы, чтобы выполнять вычисления и возвращать таблицу или столбец значений. Эта способность DAX добавляет мощность в Power Pivot, Power View и Power BI, где используется DAX.

• Функции поиска DAX требуют установления связи между соответствующими таблицами.

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

Вы не можете использовать функции DAX в формуле Excel или использовать функции Excel в формуле DAX без необходимых изменений.

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

Функции даты и времени в Excel возвращают целое число, представляющее дату в виде серийного номера. Функции даты и времени DAX возвращают тип данных datetime, который находится в DAX, но не в Excel.

В Excel нет функций, возвращающих таблицу, но некоторые функции могут работать с массивами. Многие из функций DAX могут легко ссылаться на полные таблицы и столбцы, чтобы выполнять вычисления и возвращать таблицу или столбец значений. Эта способность DAX добавляет мощность в Power Pivot, Power View и Power BI, где используется DAX.

Функции поиска DAX требуют установления связи между соответствующими таблицами.

Соглашения об именовании параметров DAX

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

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

Подробнее см. Главу « Соглашения об именах параметров DAX» .

Типы функций DAX

DAX поддерживает следующие типы функций.

• Табличные функции DAX
  • Функции фильтра DAX
  • Функции агрегации DAX
  • DAX Time Intelligence Функции
• Функции даты и времени DAX
• Информационные функции DAX
• DAX логические функции
• DAX Math и Trig Функции
• DAX Другие функции
• DAX родительские и дочерние функции
• Статистические функции DAX
• Текстовые функции DAX
• DAX Описание Структура Функции

Табличные функции DAX

Многие функции DAX принимают таблицы в качестве входных или выходных таблиц или выполняют обе функции. Эти функции DAX называются табличными функциями DAX. Поскольку таблица может иметь один столбец, функции с табличными значениями DAX также принимают отдельные столбцы в качестве входных данных. У вас есть следующие типы табличных функций DAX &#;

• Функции агрегации DAX
• Функции фильтра DAX
• Интеллектуальные функции DAX Time

Функции агрегации DAX

Функции агрегации DAX агрегируют любое выражение по строкам таблицы и полезны в вычислениях.

Подробнее см. Главу « Функции агрегирования DAX» .

Функции фильтра DAX

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

Подробнее см. Главу « Функции фильтра DAX» .

DAX Time Intelligence Функции

Функции DAX Time Intelligence возвращают таблицу дат или используют таблицу дат для расчета агрегации. Эти функции DAX помогают создавать расчеты, которые отвечают потребностям анализа бизнес-аналитики, позволяя вам манипулировать данными с использованием периодов времени, включая дни, месяцы, кварталы и годы.

Подробнее см. Главу «Функции DAX Time Intelligence» .

Функции даты и времени DAX

Функции даты и времени DAX аналогичны функциям даты и времени в Excel. Однако функции DAX Date и Time основаны на типе данных datetime DAX.

Подробнее см. Главу « Функции даты и времени DAX» .

Информационные функции DAX

Информационные функции DAX просматривают ячейку или строку, предоставленные в качестве аргумента, и сообщают, соответствует ли значение ожидаемому типу.

Подробнее см. Главу « Информационные функции DAX» .

DAX логические функции

Логические функции DAX возвращают информацию о значениях в выражении. Например, функция DAX TRUE позволяет узнать, возвращает ли вычисляемое выражение значение TRUE.

Подробнее см. Главу « Логические функции DAX» .

DAX Math и Trig Функции

Математические и тригонометрические функции DAX очень похожи на математические и тригонометрические функции Excel.

Подробнее см. Главу « Функции DAX Math и Trig» .

DAX родительские и дочерние функции

Родительские и дочерние функции DAX полезны для управления данными, которые представлены в иерархии «родитель / потомок» в модели данных.

Подробнее см. Главу « Родительские и дочерние функции DAX» .

Статистические функции DAX

Статистические функции DAX очень похожи на статистические функции Excel.

Подробнее см. Главу « Статистические функции DAX» .

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

Функции DAX Text работают с таблицами и столбцами. С помощью функций DAX Text вы можете вернуть часть строки, найти текст в строке или объединить строковые значения. Вы также можете управлять форматами дат, времени и чисел.

Подробнее см. Главу « Функции DAX-текста» .

DAX Другие функции

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

Подробнее см. Главу « Другие функции DAX» .

DAX Функция Описание Структура

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

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

Подробнее см. Главу « Структура описания функции DAX» .

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

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

Имена параметров

Ниже приведены имена стандартных параметров DAX &#;

выражение

Любое выражение DAX, которое возвращает одно скалярное значение, где выражение должно оцениваться несколько раз (для каждой строки / контекста).

значение

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

Таблица

Любое выражение DAX, которое возвращает таблицу данных.

TABLENAME

Имя существующей таблицы с использованием стандартного синтаксиса DAX. Это не может быть выражением.

ColumnName

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

название

Строковая константа, которая будет использоваться для предоставления имени нового объекта.

порядок

Перечисление, используемое для определения порядка сортировки.

связи

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

тип

Перечисление, используемое для определения типа данных для PathItem и PathItemReverse.

Префикс имен параметров или использование только префикса

Вы можете указать имя параметра с префиксом &#;

• Префикс должен описывать, как используется аргумент.

• Префикс должен быть таким, чтобы избежать неоднозначного чтения параметра.

Префикс должен описывать, как используется аргумент.

Префикс должен быть таким, чтобы избежать неоднозначного чтения параметра.

Например,

• Result_ColumnName &#; указывает на существующий столбец, используемый для получения значений результата в функции DAX LOOKUPVALUE ().

• Search_ColumnName &#; указывает на существующий столбец, используемый для поиска значения в функции DAX LOOKUPVALUE ().

Result_ColumnName &#; указывает на существующий столбец, используемый для получения значений результата в функции DAX LOOKUPVALUE ().

Search_ColumnName &#; указывает на существующий столбец, используемый для поиска значения в функции DAX LOOKUPVALUE ().

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

Например, рассмотрим дату (год_значения, месяц_значения, день_значения) . Вы можете опустить параметр имя &#; значение, которое повторяется трижды, и записать его как DATE (год, месяц, день). Как видно, используя только префиксы, функция более читабельна. Тем не менее, иногда имя параметра и префикс должны присутствовать для ясности.

Например, рассмотрим Year_columnName . Имя параметра &#; ColumnName, а префикс &#; Год. И то, и другое требуется, чтобы пользователь понял, что параметр требует ссылки на существующий столбец лет.

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

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

• Различные типы функций DAX сгруппированы по названию типа функций DAX в виде глав.

• В каждой из этих глав дается краткое описание полезности соответствующего типа функций DAX.

• Краткое описание будет сопровождаться списком функций DAX, соответствующих этой главе (Тип / Категория функций DAX).

• Каждое имя функции DAX связано с деталями функции DAX, которые имеют следующую структуру описания функции DAX &#;

  • Описание
  • Синтаксис
  • параметры
  • Возвращаемое значение
  • замечания
  • пример

Различные типы функций DAX сгруппированы по названию типа функций DAX в виде глав.

В каждой из этих глав дается краткое описание полезности соответствующего типа функций DAX.

Краткое описание будет сопровождаться списком функций DAX, соответствующих этой главе (Тип / Категория функций DAX).

Каждое имя функции DAX связано с деталями функции DAX, которые имеют следующую структуру описания функции DAX &#;

Следующие разделы объясняют каждый из этих заголовков, которые появляются в каждом объяснении функции DAX.

Описание

В разделе «Описание» вы узнаете, что такое функция DAX и где ее можно использовать.

Если функция DAX введена в Excel , то же самое будет упомянуто здесь. (Остальные функции DAX существуют в Excel )

Синтаксис

В разделе Синтаксис вы узнаете точное название функции и соответствующие параметры.

• Имя DAX-функции указывается заглавными буквами.

• За именем функции DAX следует открывающая скобка.

• Каждый параметр соответствует стандартному соглашению об именах параметров DAX и заключен в угловые скобки.

• Если параметр является необязательным, он дополнительно заключается в квадратные скобки.

• Параметры разделяются запятыми.

• Эллипсы… используются для отображения необязательного количества повторений параметров.

• Синтаксис функции заканчивается закрывающей скобкой.

Имя DAX-функции указывается заглавными буквами.

За именем функции DAX следует открывающая скобка.

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

Если параметр является необязательным, он дополнительно заключается в квадратные скобки.

Параметры разделяются запятыми.

Эллипсы… используются для отображения необязательного количества повторений параметров.

Синтаксис функции заканчивается закрывающей скобкой.

пример

параметры

В разделе «Параметры» каждый из параметров определенной функции DAX указан в таблице с ее описанием. Например, параметры приведенного выше примера функции DAX SELECTCOLUMNS перечислены в следующей таблице.

Таблица

Таблица или выражение DAX, которое возвращает таблицу.

название

Имя, данное столбцу, заключено в двойные кавычки.

scalar_expression

DAX-выражение, которое возвращает скалярное значение, такое как ссылка на столбец, целое число или строковое значение.

Возвращаемое значение

В разделе «Возвращаемое значение» вы узнаете, какое значение будет возвращать функция DAX, и ее тип данных.

замечания

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

пример

Пример использования функции DAX приведен в этом разделе.

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

• Имя таблицы указывается так, как оно указано в модели данных. Например, продажи.

• Имя столбца указывается так, как оно отображается в модели данных в квадратных скобках.

  Например, [Объем продаж]

  • Рекомендуется использовать полные имена для столбцов, т. Е. К имени столбца должен предшествовать имя таблицы, в которой оно содержится.

    Например, Продажи [Объем продаж]

  • Если имя таблицы содержит пробелы, оно должно быть заключено в одинарные кавычки.

    Например, «Восточные продажи» [Объем продаж]

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

Имя таблицы указывается так, как оно указано в модели данных. Например, продажи.

Имя столбца указывается так, как оно отображается в модели данных в квадратных скобках.

Например, [Объем продаж]

Рекомендуется использовать полные имена для столбцов, т. Е. К имени столбца должен предшествовать имя таблицы, в которой оно содержится.

Например, Продажи [Объем продаж]

Если имя таблицы содержит пробелы, оно должно быть заключено в одинарные кавычки.

Например, «Восточные продажи» [Объем продаж]

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

Функции агрегации DAX агрегируют любое выражение по строкам таблицы и полезны в вычислениях.

Ниже приведены функции агрегации DAX.

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

Ниже приведены функции фильтра DAX.

Функции DAX Time Intelligence помогают создавать расчеты, отвечающие потребностям анализа Business Intelligence, позволяя вам манипулировать данными с использованием периодов времени, включая дни, месяцы, кварталы и годы.

Ниже приведены функции DAX Time Intelligence.

Функции даты и времени DAX аналогичны функциям даты и времени в Excel. Однако функции даты и времени DAX основаны на типе данных DAX datetime.

Ниже приведены функции даты и времени DAX.

Информационные функции DAX смотрят на значение или столбец, предоставленный в качестве аргумента, и сообщают, соответствует ли значение ожидаемому типу.

Ниже приведены информационные функции DAX &#;

Краткое руководство: использование функций в формулах

​Смотрите также​​ условию. То есть​ ячейку.​ продукт хранится на​ самостоятельной функции.​ цифры римскими?​ КОВАР для определения​Оценка​Совет:​ категориями функций, можно​ вам полезна. Просим​(СЧЕТЕСЛИ) из категории​ протяните до ячейки​A3​К примеру, на рисунке​ добавлена автоматически, а​ функции будет отображаться​Примечание:​ совместить логическое и​В качестве аргументов можно​ складе дольше 8​

​ЛОЖЬ​Функция КПЕР для расчета​ взаимосвязей между разными​45​ Для получения дополнительных сведений​ также выбрать категорию.​ вас уделить пару​Statistical​В4​

​к этому результату.​ ниже введена формула,​ в ячейке отобразится​

• ​ всплывающая подсказка с​

• ​ Мы стараемся как можно​ статистическое решение.​

• ​ использовать другие функции.​ месяцев, уменьшить его​Не имеет аргументов, возвращает​

• ​ количества периодов погашений​

• ​ наборами данных. Расчет​

Инструкции

Дальнейшие действия

• ​A2+A3​.​ строке формулы.​будет содержать строку​ автоматически, поэтому ее​

• ​ одной функции: .​ и английский языки.​Чтобы решить поставленную задачу,​Если все заданные аргументы​ и вычисления реальной​

profexcel.ru

Формулы и функции в Excel

• ​ в Excel.​
• ​'=ЕСЛИ(A2>89,"A",ЕСЛИ(A2>79,"B", ЕСЛИ(A2>69,"C",ЕСЛИ(A2>59,"D","F"))))​
• ​После ввода всех аргументов​
• ​Поиск функции​
• ​ приводим ссылку на​

​(Аргументы функции).​B4​), потом умножает полученный​Совет:​Попробуйте создать формулу с​ "Возвращает модуль (абсолютную​

​ текст может содержать​ Первый аргумент –​​ Проходной балл –​​ воспользуемся логической функцией​ возвращают истинный результат,​​ суммы долга с​​Примеры работы с​​Использует вложенные функции ЕСЛИ​​ формулы нажмите кнопку​

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

Ввод формулы

​ По математике​ ЕСЛИ. Формула будет​

1. ​ то функция выдает​
2. ​ учетом переплаты и​ функцией profexcel.ru при​ для назначения буквенной​ОК​
3. ​ "добавить числа" возвращается​ языке) .​ от поля​​ в столбце​​ ячейки​​ вручную набирать​​ формулу, содержащую функцию,​

   

​Выбор функции и ввод​​ ошибки. Для нас​ ячеек для проверки.​​ для поступления нужно​​ выглядеть так: =ЕСЛИ(C2>=8;B2/2;B2).​​ логическое выражение «ИСТИНА».​​ процентной ставки.​ вычислении сложных процентов,​​ категории оценке в​​.​​ функция​​Использование функции в качестве​

1. ​Range​​B​​A1​

   

   ​А1​ в которой используется​​ ее аргументов​​ важно, чтобы эта​ Второй аргумент –​ получить не менее​

Редактирование формул

​Логическое выражение «С2>=8» построено​ В случае хотя​Примеры работы функции profexcel.ruЫ​ расчета экспоненциального роста,​ ячейке A2.​

1. 1. ​Щелкните ячейку, в которую​СУММ​ одного из аргументов​

1. ​(Диапазон) и выберите​​.​​.​

   

Приоритет операций

​и​ результат другой функции.​   ​ статья была вам​ В9 – условие.​ 4 баллов. Составить​ с помощью операторов​ бы одного ложного​ в Excel.​ прогноз траектории с​=ЕСЛИ(A2>89;"A";ЕСЛИ(A2>79;"B"; ЕСЛИ(A2>69;"C";ЕСЛИ(A2>59;"D";"F"))))​

​ нужно ввести формулу.​​).​​ формулы, использующей функцию​ диапазон​​Все функции имеют одинаковую​​Когда вы копируете формулу,​

​А2​

​Поработайте с функциями, которые​В списке дважды щелкните​ полезна. Просим вас​​ Третий аргумент –​​ отчет о поступлении.​ отношения «>» и​ логического значения вся​​Примеры использования функции​​ учетом последовательности. ​

Копировать/вставить формулу

​'=ЕСЛИ(A3>89,"A",ЕСЛИ(A3>79,"B", ЕСЛИ(A3>69,"C",ЕСЛИ(A3>59,"D","F"))))​Чтобы начать формулу с​Чтобы ввести другую функцию​ называется вложения, и​A1:C2​ структуру. Например:​ Excel автоматически подстраивает​

1. ​, просто кликните по​ раньше не использовали,​​ функцию, которую требуется​​ уделить пару секунд​

   

2. ​ $C$2:$C$7 – диапазон​​Составим таблицу с исходными​​ «=». Результат его​ функция выдает результат​ profexcel.ruЫ для выборки​​Примеры функции СМЕЩ для​​Использует вложенные функции ЕСЛИ​ функции, нажмите в​​ в качестве аргумента,​​ мы будем воспринимают​

   

3. ​.​​SUM(A1:A4)​​ ссылки для каждой​ ячейкам​ и посмотрите, возвращают​​ использовать. Excel вводит​​ и сообщить, помогла​​ усреднения; числовые значения,​​ данными:​ вычисления – логическая​​ «ЛОЖЬ».​​ отдельных значений из​

   

4. ​ прохода по диапазону​ для назначения буквенной​​ строке формул кнопку​​ введите функцию в​​ этой функции в​​Кликните в поле​​СУММ(A1:A4)​​ новой ячейки, в​A1​ ли они ожидаемые​​ имени функции в​​ ли она вам,​ которые берутся для​Нужно общее количество баллов​

   

   ​ величина «ИСТИНА» или​​=И (Лог_знач. 1; Лог_знач.​​ полей сводной таблицы.​​ ячеек в Excel.​ категории оценке в​​Вставить функцию​​ поле этого аргумента.​

   

Вставка функции

​ качестве вложенные функции.​Criteria​

​и​​ значения.​​ ячейке, за которым​ с помощью кнопок​ расчета среднего арифметического.​ сравнить с проходным​​ «ЛОЖЬ». В первом​​ 2;…)​ Формулы для работы​Применение функции СМЕЩ​ ячейке A3.​​.​​Части формулы, отображенные в​​ К примеру, добавив​​(Критерий) и введите​​SUM​​ Чтобы понять это,​​A2​​Ввод формулы​ следует открывающую круглую​ внизу страницы. Для​Функция СРЗНАЧЕСЛИ сопоставляет значение​ баллом. И проверить,​ случае функция возвращает​​Принимает до аргументов​​ со сводными таблицами.​

​ для динамического получения​=ЕСЛИ(A3>89,"A",ЕСЛИ(A3>79,"B",ЕСЛИ(A3>69,"C",ЕСЛИ(A3>59,"D","F"))))​

1. ​В диалоговом окне Вставить​
2. ​ диалоговом окне​​ вложенные функции СРЗНАЧ​​ «>5».​

   

   ​(СУММ). Выражение между​

3. ​ выполните следующие действия:​.​Редактирование формул​ скобку; Например,​​ удобства также приводим​​ ячейки В9 (№1)​​ чтобы по математике​​ значение «В2/2». Во​

   

4. ​ в виде условий​​Функция ЭФФЕКТ для расчета​​ ссылки на ячейку​​'=ЕСЛИ(A4>89,"A",ЕСЛИ(A4>79,"B", ЕСЛИ(A4>69,"C",ЕСЛИ(A4>59,"D","F"))))​​ функцию в поле​
5. ​Аргументы функции​ и сумм в​​Нажмите​​ скобками (аргументы) означает,​Введите формулу, показанную ниже,​​Измените значение ячейки​​Приоритет операций​
6. ​=​​ ссылку на оригинал​​ со значениями в​ оценка была не​
7. ​ втором – «В2».​​ или ссылок. Обязательным​​ годовой процентной ставки​

   

   ​ в диапазоне. Создание​​Использует вложенные функции ЕСЛИ​выберите категорию​, отображают функцию, выбранную​

   
   

   

​ что мы задали​​ в ячейку​A1​​Копировать/Вставить формулу​​СУММ (​ (на английском языке).​ диапазоне В2:В7 (номера​ ниже «4». В​Усложним задачу – задействуем​

​ является первый.​ в Excel .​
​ счетчика элемента управления​
​ для назначения буквенной​

​выберите​

profexcel.ru

Использование вложенных функций в формуле

​ на предыдущем шаге.​​ следующая формула суммирует​.​ диапазон​A4​на 3.​Вставка функции​.​Кроме ввода формул для​ магазинов в таблице​ графе «Результат» поставить​ логическую функцию И.​ИЛИ​Примеры работы функции​ интерфейсом формы. Автоматическое​ категории оценке в​все​Если щелкнуть элемент​ набор чисел (G2:​Результат:​A1:A4​.​

​Excel автоматически пересчитывает значение​Формула представляет собой выражение,​После открывающей круглой скобки​ выполнения базовых математических​ продаж). Для совпадающих​ «принят» или «нет».​ Теперь условие такое:​Показывает результат «ИСТИНА», если​ ЭФФЕКТ при расчете​ обновление итоговых данных​ ячейке A4.​.​ЕСЛИ​ G5) только в​Excel подсчитывает число​в качестве входных​Выделите ячейку​ ячейки​ которое вычисляет значение​ в случае необходимости​

​ операций, таких как​ данных считает среднее​Введем формулу вида: =4;СУММ(B3:D3)>=$B$1);"принят";"нет")'​

​ если товар хранится​ хотя бы один​ эффективных годовых процентных​

1. ​Если вы знакомы с​, в диалоговом окне​

2. ​ том случае, если​ ячеек, значение которых​ данных. Эта функция​​А4​​A3​

   ​ ячейки. Функции –​​ можно ввести один​​ сложение, вычитание, умножение​

3. ​ арифметическое, используя числа​​ class='formula'>. Логический оператор​​ дольше 8 месяцев,​​ из аргументов является​​ ставок по разным​

   ​Примеры работы функции EXP​Советы:​ категориями функций, можно​

   ​Аргументы функции​ среднее значение другого​ больше 5.​ складывает значения в​, кликните по ней​​. Это одна из​​ это предопределенные формулы​ или несколько аргументов —​ и деление, в Microsoft Excel​​ из диапазона С2:С7.​​ «И» заставляет функцию​

4. ​ то его стоимость​ истинным.​ банковским вкладам и​ для возведения числа​

   ​ ​ также выбрать категорию.​​отображаются аргументы для​​ набора чисел (F2:​=COUNTIF(A1:C2;">5")​

   ​ ячейках​​ правой кнопкой мыши​​ наиболее мощных возможностей​​ и они уже​​ данных, используемых функцией.​ можно использовать большую​​Задача 3. Найти средние​​ проверять истинность двух​ уменьшается в 2​=ИЛИ (Лог_знач.1; Лог_знач. 2;…)​ депозитным счетам с​ Эйлера в Excel.​​Для получения дополнительных сведений​​Чтобы ввести другую функцию​​ функции​​ F5) больше ​​=СЧЁТЕСЛИ(A1:C2;">5")​​A1​

5. ​ и выберите команду​ Excel.​

   ​ встроены в Excel.​ Excel покажет, данные​ библиотеку встроенных функций​ продажи в магазине​ условий. Математическая функция​ раза. Если дольше​​//​ простыми или сложными​Пример применения функции​ о формулах в​ в качестве аргумента,​​ЕСЛИ​ В противном случае​

   ​Примечание:​​,​Copy​Когда вы выделяете ячейку,​Например, на рисунке ниже​​ какого типа следует​​ для выполнения других​

6. ​ №1 г. Москва.​ «СУММ» используется для​​ 5 месяцев, но​​НЕ​

1. ​ процентами. 1 2​ EXP в лабораторных​

2. ​ общем см Обзор​ введите функцию в​. Чтобы вложить другую​​ возвращает значение 0.​​Вместо того, чтобы​

3. ​A2​(Копировать) или нажмите​​ Excel показывает значение​​ ячейка​​ ввести в качестве​​ операций.​

   ​Видоизменим таблицу из предыдущего​ подсчета итогового балла.​ меньше 8 –​

4. ​Меняет логическое значение «ИСТИНА»​ 3 4 5​ исследованиях и анализах.​ формул.​ поле аргумента в​ функцию, можно ввести​

5. ​Вложенные функции СРЗНАЧ и​ использовать инструмент «​

6. ​,​ сочетание клавиш​

Примеры

​ или формулу, находящиеся​А3​ аргумента. Это может​Эти функции можно использовать​ примера:​

​Функция ЕСЛИ позволяет решать​ в 1,5 раза.​ на противоположное –​ 6 7 8​ Применение функции EXP​Список доступных функций см.​ построитель формул или​ ее в поле​ сумм в функцию​Вставить функцию​A3​Ctrl+C​ в ячейке, в​

​ при анализе вложений​​ Excel (по алфавиту)​

• ​Введите дополнительные аргументы, необходимые​ ввести​В формулу можно вложить​ Когда напечатаете » =СЧЁТЕСЛИ( «,​

• ​A4​Далее выделите ячейку​Чтобы отредактировать формулу, кликните​А2​ другую ячейку.​

profexcel.ru

Самые интересные функции для работы в Excel

Синтаксис и параметры функций

profexcel.ru

Логические функции в excel с примерами их использования

​Примеры формул с использованием​ категорий числовым результатам​Введите дополнительные аргументы, необходимые​Чтобы начать формулу с​Перевела: Ольга Гелих​Insert Function​

​Paste Options​ по которому ведутся​SUM​(которая преобразует строчные​сравнение содержимого двух ячеек.​

Использование логических функций в Excel

Логические функции в Excel и примеры решения задач

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

​ В логическом выражении​ функции ЕСЛИ и​

​Если значение первого аргумента​ Excel.​ таблицы.​ в формулах для​

​ ячейку A1 нового​ ячейки, на которые​Знак равенства (​ вас актуальными справочными​Нажмите кнопку​Ещё вы можете скопировать​ Затем выполняется умножение​=SUM(A1:A2)​не принимает аргументов,​ равенства (​

​Третий аргумент – В9​ «D2​ ИЛИ: =10);"списан";"")' class='formula'>.​ истинно, то возвращает​Название функции​Функция РИМСКОЕ в Excel​ выборки значений при​ листа Excel. Чтобы​ нужно сослаться. Нажмите​=​ материалами на вашем​

​Insert Function​ формулу из ячейки​

​ или деление. После​=СУММ(A1:A2)​ поскольку она просто​

​=​ – первое условие.​Задача 2. Найти средние​ Условие, записанное с​ сам аргумент. В​Значение​ для перевода арабских​

​ условии. Как использовать​ отобразить результаты формул,​ кнопку​) будет вставлен автоматически.​ языке. Эта страница​(Вставить функцию).​A4​ этого Excel будет​Чтобы ввести формулу, следуйте​

​ возвращает число пи​), а затем — букву​ Четвертый и пятый​

​ продажи в магазинах​ помощью логической операции​ противном случае –​

​Синтаксис​ чисел в римские.​ функции И ИЛИ​ выделите их и​, чтобы свернуть​В поле​ переведена автоматически, поэтому​Появится одноименное диалоговое окно.​в​ складывать и вычитать.​

​ инструкции ниже:​ (3,).​

​ (например, "а"), чтобы​ аргумент – диапазон​ сети.​ ИЛИ, расшифровывается так:​ значение второго аргумента.​Примечание​Примеры использования функции​ ЕСЛИ в формуле?​

​ нажмите клавишу F2,​ диалоговое окно, выделите​Категория​ ее текст может​Отыщите нужную функцию или​B4​ Смотрите пример ниже:​

​Выделите ячейку.​Ввод формулы и просмотр​ просмотреть список доступных​

Статистические и логические функции в Excel

​ для проверки и​Составим таблицу с исходными​ товар списывается, если​#ИМЯ?​ИСТИНА​ РИМСКОЕ в переводе​Примеры расчетов функций КОВАРИАЦИЯ.В​

​ а затем — клавишу​ ячейки, на которые​

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

​Сперва Excel умножает (​Чтобы Excel знал, что​ результата​

​ функций.​ второе условие, соответственно.​

​ данными:​ число в ячейке​Оба аргумента обязательны.​Не имеет аргументов, возвращает​ арабских чисел в​ и КОВАРИАЦИЯ.Г в​

​ ВВОД. При необходимости​ нужно создать ссылки,​Все​

​ грамматические ошибки. Для​ категории. Например, вы​А4​A1*A2​ вы хотите ввести​   ​Для прокрутки списка используйте​Функция учитывает только те​Необходимо найти среднее арифметическое​ D2 = ​​ логическое значение «ИСТИНА».​

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

​ формулу, используйте знак​Нажмите клавишу ВВОД.​ клавишу СТРЕЛКА ВНИЗ.​

​ значения, которые соответствуют​ для ячеек, значение​

​При невыполнении условия функция​Задача 1. Необходимо переоценить​=ИСТИНА ()​

​ переводить, преобразовать, конвертировать​Примеры использования функций​ чтобы видеть все​, чтобы снова​Если вы знакомы с​ эта статья была​COUNTIF​ правый угол и​ ячейки​ равенства (=).​

​Закрывающая круглая скобка будет​

​При этом для каждой​ всем заданным условиям.​ которых отвечает заданному​ ЕСЛИ возвращает пустую​ товарные остатки. Если​Редко используется в качестве​

​ и заменить арабские​ КОВАРИАЦИЯ.В, КОВАРИАЦИЯ.Г и​ данные.​

profexcel.ru

​ развернуть диалоговое окно.​

Данное краткое руководство предназначено для пользователей, начинающих работать с Power Pivot в Excel или с проектами табличной модели, созданными в SQL Server Data Tools. Это быстрое и простое введение в использование выражений анализа данных (DAX) для решения ряда основных задач по моделированию данных и аналитике. В этой статье объясняются термины, описываются возможные задачи и предлагаются вопросы для проверки ваших знаний. После прочтения статьи вы будете хорошо понимать базовые понятия DAX.

Что такое DAX?

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

Почему DAX — это полезно?

Создать книгу и импортировать в нее данные несложно. Можно даже создавать сводные таблицы или сводные диаграммы для отображения важной информации без применения формул DAX. Однако что, если необходимо, например, проанализировать критичные данные о продажах по нескольким категориям товаров для разных диапазонов дат? Или, например, требуется свести важные данные по инвентаризации из нескольких таблиц в разных источниках данных? Все эти, а также многие другие возможности предоставляют формулы DAX. Умение создавать эффективные формулы DAX поможет извлечь наибольшую пользу из данных. Располагая нужными данными, можно начинать решать реальные бизнес-задачи, влияющие на конечный итог. Это называется бизнес-аналитикой, овладеть которой помогает DAX.

Предварительные требования

Пользователь может уже быть знаком с созданием формул в Microsoft Excel. Эти знания помогут в освоении DAX, но даже если нет опыта работы с формулами Excel, описанные здесь основные понятия помогут сразу начать создавать формулы DAX и решать практические задачи по бизнес-аналитике.

Наибольшее внимание уделяется формулам DAX, применяемым в вычислениях. Необходимо предварительно ознакомиться с основными понятиями вычисляемых столбцов и мер (вычисляемых полей), которые описаны в справке Power Pivot. Также необходимо ознакомиться со средой и средствами разработки Power Pivot в Excel.

Образец книги

Для освоения DAX лучше всего создать несколько простых формул, применить их к реальным данным и посмотреть на результаты. В примерах и упражнениях данного руководства используется книга Contoso Sample DAX profexcel.ru Ее можно скачать по ссылке profexcel.ru?LinkID=&clcid=0x После скачивания книги на компьютер откройте ее, затем откройте окно Power Pivot.

Начнем!

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

Синтаксис

Перед созданием собственных формул предлагаем ознакомиться с синтаксисом формул DAX. Синтаксис состоит из различных элементов, составляющих формулу (говоря проще, из правил написания формул). Рассмотрим простой пример формулы DAX, создающей новые данные (значения) для каждой строки в вычисляемом столбце Margin в таблице FactSales (цвета текста в формуле приводятся только в качестве иллюстрации).

Синтаксис этой формулы включает следующие элементы:

1. Оператор равенства (=) означает начало формулы, которая после вычисления возвратит результат или значение. Все формулы, вычисляющие значения, начинаются со знака равенства.

2. Ссылаемый столбец [SalesAmount] содержит уменьшаемые значения. Ссылка на столбец в формуле всегда заключена в квадратные скобки ([]). В отличие от формул Excel, которые ссылаются на ячейку, формула DAX всегда ссылается на столбец.

3. Математический оператор вычитания (-).

4. Ссылаемый столбец [TotalCost] содержит значения, которые требуется вычесть из значений в столбце [SalesAmount].

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

В таблице FactSales для каждой строки вычисляемого столбца Margin вычисляем (=) значение путем вычитания (-)значений в столбце [TotalCost] из значений встолбце[SalesAmount].

Рассмотрим формулу другого типа, используемую в мере.

Синтаксис этой формулы включает следующие элементы:

1. Имя меры: "Сумма Sales Amount". Формулы для мер могут включать в себя имя меры, затем двоеточие, затем формулу вычисления.

2. Оператор равенства (=) означает начало формулы вычисления. При вычислении она возвращает результат.

3. Функция SUM складывает все числа в столбце [SalesAmount]. О функциях будет рассказано позже.

4. Внутри скобок () содержится один или несколько аргументов. Для каждой функции необходим минимум один аргумент. Аргумент служит для передачи значения функции.

5. Ссылаемая таблица FactSales.

6. Ссылаемый столбец [SalesAmount] в таблице FactSales. Этот аргумент указывает функции SUM на столбец с числами, которые требуется сложить.

Данную формулу можно прочитать следующим образом:

Для меры "Сумма Sales Amount" вычислить (=) сумму (SUM) значений в столбце [SalesAmount] в таблице FactSales.

При помещении в зону значений в списке полей сводной таблицы эта мера вычисляет и возвращает значения, определенные каждой ячейкой в сводной таблице, например Cell Phones in the USA.

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

В отличие от вышеупомянутого вычисляемого столбца Margin, в данном случае перед столбцом [SalesAmount] стоит таблица FactSales, к которой принадлежит столбец. Это называется полным именем столбца, потому что перед именем столбца добавляется имя таблицы. Для столбцов, на которые ссылаются в одной и той же таблице, присутствие в формуле имени таблицы необязательно. В некоторой степени это укорачивает сложные формулы, ссылающиеся на множество столбцов, и облегчает их чтение. Однако по возможности рекомендуется всегда включать имя таблицы в формулы мер, даже для столбцов в одной и той же таблице.

Очень важно использовать правильный синтаксис в формулах. В большинстве случаев при неправильном синтаксисе будет возвращена ошибка. Иногда синтаксис может быть правильным, однако возвращаемые значения не будут соответствовать ожиданиям. Power Pivot (а также SQL Server Data Tools) включает в себя функцию IntelliSense, помогающую в выборе правильных элементов и тем самым позволяющую создавать формулы с правильным синтаксисом.

Создадим простую формулу. Данное упражнение поможет лучше изучить синтаксис формул и возможности функции IntelliSense в строке формул.

Задача: создание простой формулы для вычисляемого столбца

1. Если вы еще не находитесь в окне Power Pivot, в Excel на ленте Power Pivot нажмите кнопку Окно Power Pivot.

2. В окне Power Pivot щелкните таблицу (вкладку) FactSales.

3. Прокрутите до крайнего правого столбца, затем щелкните Добавить столбец в заголовке столбца.

4. Щелкните строку формул в верхней части окна конструктора моделей.

   

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

   Обратите внимание на три кнопки слева от строки формул.

   

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

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

   Нажмите кнопку Fx. Появится новое диалоговое окно — "Вставка функции". В нем проще всего ввести формулу DAX. Чуть позже вы добавим в формулу функцию при создании меры; сейчас добавлять функцию в формулу вычисляемого столбца необязательно. Закройте диалоговое окно "Вставка функции".

5. В строке формул введите знак равенства (=), затем — открывающую квадратную скобку [. Появляется небольшое окно со всеми столбцами в таблице FactSales. Эта технология IntelliSense в действии.

   Поскольку вычисляемые столбцы всегда создаются в активной таблице, нет необходимости указывать имя таблицы перед именем столбца. Прокрутите вниз, затем дважды щелкните [SalesQuantity]. Можно также прокрутить до имени желаемого столбца и нажать клавишу Tab.

   Теперь курсор активен справа от [SalesQuantity].

6. Нажмите пробел, затем введите оператор вычитания (-) и еще один пробел.

7. Теперь введите еще одну открывающую квадратную скобку [. В этот раз выберите столбец [ReturnQuantity] и нажмите клавишу ВВОД.

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

   После нажатия клавиши ВВОД для завершения формулы в строке состояния в нижней части окна Power Pivot отображается слово Вычисление. Процесс завершается быстро, хотя вычисляются новые значения для свыше трех миллионов строк.

8. Щелкните правой кнопкой мыши заголовок столбца и задайте ему новое имя NetSales.

Это все! Только что мы создали простую и в то же время очень эффективную формулу DAX. Для каждой строки в таблице FactSales формула NetSales вычисляет значение путем вычитания значения в столбце [ReturnQuantity] из значения в столбце [SalesQuantity]. Обратите внимание на фразу «для каждой строки». Это беглый взгляд на еще одно очень важное понятие в DAX — контекст строки. О контексте строки будет рассказано позже.

При введении оператора в формулу DAX очень важно иметь ввиду тип данных в используемых аргументах. Например, если ввести формулу «= 1 & 2», будет возвращено текстовое значение «12». Это происходит потому, что оператор-амперсанд (&) служит для объединения текста. DAX интерпретирует эту формулу следующим образом: вычислить результат, принимая значение 1 и добавляя значение 2 в виде текста. Теперь, если потребуется ввести = 1 + 2, то эту формулу DAX воспринимает следующим образом: вычислить результат, создав числовое значение 1 и добавив числовое значение 2. Разумеется, результатом является числовое значение «3». DAX вычисляет итоговые значения в зависимости от оператора в формуле, а не от типа данных столбцов, использующихся в аргументе. Типы данных в DAX очень важны, однако в целях данного краткого руководства не рассматриваются. Дополнительные сведения о типах данных и операторах в формулах DAX см. в разделе «Справочник DAX» (profexcel.ru?LinkId=&clcid=0x) электронной документации.

Попробуем еще один вариант. В этот раз создадим меру путем ввода формулы с использованием IntelliSense. Не переживайте, если формула не совсем понятна. В данном случае важно научиться создавать формулу с правильным синтаксисом, используя несколько элементов.

Задача: создание формулы меры

1. В таблице FactSales щелкните любую пустую ячейку в области вычислений. Это область из пустых ячеек сразу под таблицей в окне Power Pivot.

1. В строке формул введите имя «Previous Quarter Sales:».

2. Введите знак равенства (=) для начала ввода формулы вычисления.

3. Введите первые три буквы CAL, затем дважды щелкните желаемую функцию. В данном примере мы будем использовать функцию CALCULATE.

4. Введите открывающую скобку ( для начала ввода аргументов, передаваемых функции CALCULATE.

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

5. Введите первые несколько букв таблицы FactSales, а затем в списке дважды щелкните FactSales[Продажи].

6. Введите запятую (,) для указания первого фильтра, затем введите PRE и дважды щелкните функцию PREVIOUSQUARTER.

   После выбора функции PREVIOUSQUARTER появляется еще одна открывающаяся скобка, указывая на необходимость еще одного аргумента; на этот раз для функции PREVIOUSQUARTER.

7. Введите первые три буквы Dim, а затем дважды щелкните DimDate[DateKey].

8. Закройте оба аргумента (для функции PREVIOUSQUARTER и для функции CALCULATE), введя две закрывающие скобки )).

   Формула должна выглядеть следующим образом:

   Previous Quarter Sales:=CALCULATE(FactSales[Sales], PREVIOUSQUARTER(DimDate[DateKey]))

9. Нажмите кнопку с флажком в строке формул для проверки формулы. В случае возникновения ошибки проверьте каждый элемент синтаксиса.

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

Только что был рассмотрен ряд важных аспектов формул DAX. Во-первых, в данной формуле были использованы две функции. Обратите внимание, что функция PREVIOUSQUARTER вложена в качестве аргумента, переданного функции CALCULATE. Формулы DAX могут содержать до 64 вложенных функций. Маловероятно, что какая-либо формула будет содержать такое количество вложенных функций. Фактически, ее будет очень непросто создать и отладить, и вряд ли она будет отличаться скоростью.

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

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

Дополнительные баллы

Возможно, вы задайте вопрос: "Какая у меня простейшая формула DAX? Ответ на этот вопрос — формула, которая вам не нужно делать. Кроме того, это можно сделать с помощью стандартной агрегатной функции в мерах. Почти любая модель данных должна фильтровать и вычислять на основе совокупных данных. Например, функция СУММ в показателе Sum of Sales Amount, которую вы видели ранее, используется для с суммы всех чисел в определенном столбце. DAX включает несколько других функций, которые также агрегируются значения. Вы можете автоматически создавать формулы, используя стандартные агрегаты, с помощью функции автосортирования.

Дополнительная задача: создание формулы меры с использованием функции автосуммирования

1. Прокрутите таблицу FactSales до столбца ReturnQuantity и щелкните его заголовок, чтобы выделить весь столбец.

2. На вкладке Главная на ленте в группе Вычисления нажмите кнопку Автоумма.

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

Сразу же создается новая мера с именем "Среднее ReturnQuantity:", за которой следует формула =AVERAGE([ReturnQuantity]).

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

Теперь уже должно было сложиться понимание синтаксиса, используемого в формулах DAX. Также, были рассмотрены некоторые очень полезные функции, в том числе IntelliSense и автосуммирование, которые помогут быстро создавать простые и точные формулы. Разумеется, о синтаксисе можно узнать намного больше. Хорошими источниками дополнительной информации является Справочник DAX и электронная документация по SQL Server.

Краткий тест по синтаксису

1. Что делает эта кнопка в панели формул?
   

2. Что всегда окружает имя столбца в формуле DAX?

3. Как создать формулу для следующей формулы: в таблице
   DimProductдля каждой строки в вычисляемом столбце UnitMargin вычисляется значение путем вычитания значений в столбце UnitCostиз значений в столбце UnitPrice?

Ответы приведены в конце данного раздела.

Функции

Функции представляют собой стандартные формулы, которые производят вычисления, используя конкретные значения, называемые аргументами и указанные в определенном порядке. Аргументом может быть другая функция, другая формула, ссылка на столбец, число, текст, логическое значение (например, TRUE или FALSE) или константа.

В DAX предусмотрены следующие категории функций: дата и время, сведения, математические, логические, статистические, текст и функции логики операций со временем. Для пользователей, ранее работавших с функциями в формулах Excel, большая часть функций в DAX будет знакома; однако функции DAX имеют следующие отличия:

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

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

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

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

Иногда трудно понять, какие функции могут понадобиться в формуле. В Power Pivot и конструкторе табличных моделей в SQL Server Data Tools есть кнопка "Вставить функцию", открывающая диалоговое окно, где можно выбирать функции по категориям и просматривать их краткое описание.

Давайте создадим новую формулу, которая содержит функцию, выбранную с помощью кнопки «Вставить функцию».

Задача: добавление функции в формулу с помощью кнопки «Вставить функцию»

1. Прокрутите таблицу FactSales до правого столбца, а затем в его заглавной области выберите добавить столбец.

2. В строке формул введите знак равенства (=).

3. Нажмите кнопку Вставить функцию. откроется диалоговое окно Вставка функции.

4. В диалоговом окне Вставка функции щелкните список Выберите категорию. По умолчанию выбрано значение Все, и ниже перечислены все функции в категории Все. Это большое количество функций, поэтому функции следует отфильтровать, чтобы легче найти желаемый тип функции.

5. Для данного примера нужно вернуть данные, уже присутствующие в другой таблице. Для этого будет использована функция из категории Фильтры. Щелкните категорию Фильтр, а затем в области Выбор функции прокрутите список вниз и дважды щелкните функцию RELATED. Нажмите кнопку ОК, чтобы закрыть диалоговое окно Вставка функции.

6. Воспользуйтесь технологией IntelliSense, чтобы найти и выбрать столбец DimChannel[ChannelName].

7. Закройте формулу и нажмите клавишу ВВОД.

8. После нажатия клавиши ВВОД для завершения формулы в строке состояния в нижней части окна Power Pivot отображается слово Вычисление. Мы только что создали новый столбец в таблице FactSales со сведениями о канале из таблицы DimChannel.

9. Задайте для столбца новое имя Channel.

   Формула должна выглядеть следующим образом: =RELATED(DimChannel [ChannelName])

Вы только что впервые впервые стали работать с другой очень важной функцией DAX , функцией RELATED. Функция RELATED возвращает значения из другой таблицы. Функцию RELATED можно использовать, если существует связь между текущей таблицей и таблицей, содержащей желаемые значения. Разумеется, возможности функции RELATED очень обширны. В данном случае можно добавить в таблицу FactSales канал продаж для каждой продажи. Теперь можно скрыть таблицу DimChannel из списка полей сводной таблицы; это упростит просмотр и позволит отображать только самые важные и нужные сведения. Как и вышеописанная функции CALCULATE, функция RELATED очень важна, и, скорее всего, будет использоваться неоднократно.

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

Краткий тест по функциям

1. На что всегда ссылается функция?

2. Может ли формула содержать более одной функции?

3. Какая категория функций подходит для объединения двух текстовых строк в одну строку?

Ответы приведены в конце данного раздела.

Контекст

Контекст является одним из важнейших понятий в DAX. Существуют два типа контекста в DAX: контекст строки и контекст фильтра. Вначале рассмотрим понятие контекста строки.

Контекст строки

Контекст строки проще всего рассматривать как текущую строку. Помните, например, вычисляемый столбец Margin, описанный ранее в ходе ознакомления с синтаксисом? Формула « =[SalesAmount] - [TotalCost] » вычисляет значение в столбце Margin для каждой строки в таблице. Значения для каждой строки вычисляются из значений в двух других столбцах в той же строке, [SalesAmount] и [TotalCost]. С помощью DAX можно определить значения для каждой строки в столбце Margin благодаря наличию контекста: для каждой строки в столбце принимаются значения в столбце [TotalCost] и удаляются из значений в столбце [SalesAmount].

В выбранной ячейке, приведенной ниже, значение $49,54 в текущей строке было вычислено путем вычитания значения $51,54 в столбце [TotalCost] от значения $,08 в столбце [SalesAmount].

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

Контекст фильтра

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

Контекст фильтра не заменяет контекст строки; скорее, он применяется в дополнение к контексту строки. Например, чтобы еще больше ограничить диапазон значений в вычислении, можно применить контекст фильтра, который не только задает контекст строки, но также задает только конкретное значение (фильтр) в данном контексте строки.

Контекст фильтра хорошо заметен в сводных таблицах. Например, при добавлении TotalCost в область Values и последующем добавлении Year и Region в строку или в столбцы, задается контекст фильтра, который выбирает подмножество данных в зависимости от данного года и региона.

Почему контекст фильтра так важен в DAX? Потому что если контекст фильтра проще всего применить путем добавления срезов и меток столбцов и строк в сводную таблицу, его также можно применить в формуле DAX путем задания фильтра с использованием таких функций, как ALL, RELATED, FILTER и CALCULATE, а также с помощью связей и других мер и столбцов. Например, рассмотрим следующую формулу в мере StoreSales:

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

Синтаксис этой формулы включает следующие элементы:

1. Имя меры StoreSales с последующим двоеточием (:).

2. Оператор равенства (=) означает начало формулы.

3. Функция CALCULATE вычисляет выражение в качестве аргумента, в контексте, измененном заданными фильтрами.

4. Внутри скобок () содержится один или несколько аргументов.

5. Мера [Sales] в той же таблице в виде выражения. Мера Sales содержит формулу: =SUM(FactSales[SalesAmount]).

6. Фильтры разделены между собой запятыми (,).

7. Столбец, на который указывает ссылка, и конкретное значение, DimChannel[ChannelName] =”Store”, в качестве фильтра.

Данная формула гарантирует, что только значения продаж, определенные мерой Sales в качестве фильтра, вычисляются только для строк в столбце DimChannel[ChannelName] со значением "Store" в качестве фильтра.

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

Краткий тест по контексту

1. Как называются два типа контекста?

2. Что такое контекст фильтра?

3. Что такое контекст строки?

Ответы приведены в конце данного раздела.

Сводка

Теперь, когда вы получили общее представление об основных понятиях DAX, вы можете приступить к созданию собственных формул DAX для вычисляемых столбцов и мер. DAX может показаться сложным, но существует множество ресурсов, помогающих освоить его. Прочтите эту статью несколько раз и поэкспериментируйте с собственными формулами, а затем переходите к другим основным понятиям и формулам DAX, которые помогут в решении конкретных бизнес-задач. Существует множество ресурсов по DAX: справка по Power Pivot, электронная документация на SQL Server, технические документы и блоги сотрудников Майкрософт и ведущих специалистов в области бизнес-аналитики. Отличной отправной точкой является вики-сайт Центра ресурсов DAX (profexcel.ru). Справочник по выражениям анализа данных (DAX) также является отличным ресурсом. Не забудьте добавить его в закладки.

Технический документ «DAX в табличной модели бизнес-аналитики», доступный для загрузки по ссылке profexcel.ru?LinkID=&clcid=0x, содержит более подробное описание рассмотренных здесь основных понятий, а также многих других расширенных понятий и формул. В нем также используется книга Contoso DAX Sample profexcel.ru, которая была загружена для этого занятия.

Ответы на вопросы кратких тестов

Синтаксис:

1. Открывает диалоговое окно «Вставка функции».

2. Квадратные скобки [].

3. =[UnitPrice] – [UnitCost]

Функции:

1. На таблицу и столбец.

2. Да. Формула DAX может содержать до 64 вложенных функций.

3. Текстовые функции.

Контекст:

1. Контекст строки и контекст фильтра.

2. Один или несколько фильтров в вычислении, определяющем одно значение.

3. Текущая строка.