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

Как в Excel разделить положительные и отрицательные числа на два столбца?

Функции суммирования в DAX: SUMX и SUM в Power BI и Power Pivot

Антон БудуевПриветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы разберем функции суммирования в Power BI и Power Pivot, входящие в категорию агрегационных функций в DAX, а конкретно SUMX и SUM, рассмотрим их параметры, особенности работы и примеры формул.

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

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

DAX функция SUM в Power BI и Power Pivot

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

Синтаксис: SUM ([Столбец])

Пример формулы DAX с использованием функции SUM: В Power BI имеется исходная таблица заказов на товары

Исходная таблица

Напишем формулу для расчета суммы затрат по всем товарам:

Поэтому SUM даст сумму всего столбца [Стоимость] равную 43000:

Результат работы функции SUM

Теперь мы попробуем использовать SUM для вычисления суммы прибыли, т.е. нам нужно вычесть затраты из продаж, а затем сложить всю прибыль. Давайте попробуем написать пример формулы:

Читайте так же:
Как вернуть первую / последнюю непустую ячейку в строке или столбце?

Однако Power BI выдал следующую ошибку:

Ошибка DAX при использовании функции SUM

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

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

DAX функция SUMX в Power BI и Power Pivot

SUMX () — вычисляет сумму результатов построчного выполнения выражения.

В данном случае SUMX(‘Table’; Expression), где:

  • ‘Table’ — исходная таблица или табличное выражение, строки которого будут использоваться для вычисления выражения для второго параметра функции
  • Expression — любое выражение, которое будет выполняться над строками таблицы, входящими в состав первого параметра функции.

S UMX работает в 2 этапа. Для каждой строки в таблице он вычисляет выражение из второго параметра функции. После вычисления строки за строкой результаты временно сохраняются в некоторой виртуальной таблице, а во втором действии SUMX сложит все результаты из этой виртуальной таблицы, чтобы получить итог. После этого временная виртуальная таблица будет стерта.

Давайте посмотрим, как SUMX работает на практике, подправив формулу с учетом ошибки из примера, приведенного выше:

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

Для начала, SUMX произведет расчет прибыли индивидуально по каждой строке таблицы ‘Товары’ и запишет результаты во временную виртуальную таблицу. Табличная модель этого процесса будет выглядеть так:

Табличная модель работы первого этапа DAX функции SUMX

Затем, во втором действии, SUMX сложит все значения из этой временной виртуальной таблицы и выдаст окончательный результат суммы прибыли, равный 82000. После вычисления суммы удалите виртуальную таблицу.

Читайте так же:
Как автоматически увеличивать ссылку на лист в Excel?

Результат работы DAX функции SUMX

SUM, SUMX и другие функции DAX (FILTER)

Функции S UMX или SUM чаще всего используются в формулах вместе с другими функциями DAX. Например, SUMX может быть встроена в параметры другой функции (например, CALCULATE) или, наоборот, какая-то функция может быть встроена в параметры SUMX.

Рассмотрим взаимодействие между SUMX и FILTER. Поскольку SUMX — это таблица, ее можно легко отфильтровать с помощью FILTER.

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

При взаимодействии SUMX и FILTER результатом является значение прибыли 60000:

Результат формулы взаимодействия функций SUMX и FILTER

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

DAX функция SUMIF (СУММЕСЛИ) в Power BI и Power Pivot

Я заметил, что у некоторых пользователей DAX в Power BI или Excel (Powerpivot) возникает соблазн использовать функцию SUMIF по аналогии с SUMMESLY в Excel. Однако дело в том, что в DAX (по крайней мере, на момент написания статьи) такой возможности нет.

По сути, она и не нужна, так как посчитать сумму одновременно с каким-либо условием в DAX совершенно не составит никакого труда. Сама сумма считается при помощи уже знакомых нам SUM или SUMX. А все остальные условия можно соорудить из других функций, к примеру добавить условие «если» при помощи функции IF. Или же вычислить сумму совместно с функцией FILTER, как это мы делали в примере выше.

Ну, или же самый мощный вариант — вычислить сумму внутри CALCULATE и в рамках уже этой функции наложить различные фильтры на SUM или SUMX.

Читайте так же:
Как быстро извлечь строки, соответствующие критериям в Excel?

На этом я завершаю статью о функциях обобщения в Power Pivot и Power BI. Удачи вам, друзья!

Подробное ВИДЕО «SUM и SUMX — функции суммы в DAX (Power BI и Power Pivot)»

Ссылки из видео:
1) [Регистрируйтесь в бесплатном экспресс-курсе] Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot: зарегистрироваться
2) [Скачивайте PDF] Справочник DAX функций для Power BI и Power Pivot на русском языке: скачать

Оцените статью :

  1. 5
  2. 4
  3. 3
  4. 2
  5. 1

[Экспресс-видеокурс] Быстрый старт в языке DAX

Антон БудуевУспехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

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

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

Понравился материал статьи?
Избранные закладкиДобавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D

Что еще посмотреть / почитать?

DAX функции BLANK, FIXED, LEN, REPT и TRIM в Power BI

Текстовые функции в DAX: BLANK, FIXED, LEN, REPT и TRIM в Power BI (Power Pivot)

DAX функции EOMONTH и EDATE

DAX функции EOMONTH и EDATE в Power BI и Power Pivot

DAX функции PREVIOUSYEAR, PREVIOUSQUARTER, PREVIOUSMONTH и PREVIOUSDAY

Как распечатать даты предыдущего периода в Power BI и Power Pivot? Функции DAX: ПРЕДЫДУЩИЙ ГОД, ПРЕДЫДУЩИЙ КВАРТАЛ, ПРЕДЫДУЩИЙ МЕСЯЦ и ПРЕДЫДУЩИЙ ДЕНЬ.

Как в Excel применять функцию ЕСЛИ()

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

Давайте начнем с простейшей возможности использования формулы. Синтаксис: ЕСЛИ (лог_выражение; значение_если истина), где лог_выражение — логическое выражение, то есть выражение возвращающее значение ИСТИНА или ЛОЖЬ (или эквивалентные численные значения: 0, если ЛОЖЬ, и любое положительное значение — ИСТИНА, и где значение_если истина — значение, возвращаемое функцией, в случае если лог_выражение имеет значение ИСТИНА.

Читайте так же:
Как быстро распечатать комментарии в Excel?

Например, разберем следующую формулу: =ЕСЛИ(A1>1000;»много») . Логическое выражение А1>100 0 используется для проверки. Допустим, вы добавите данную формулу в ячейку В1. Если логическое выражение является истинным (оно является таковым, когда число в A1 больше 1000), функция возвращает значение «много», и именно это вы увидите в ячейке В1 (в случае если А1 меньше 1000, вы увидите значение ЛОЖЬ).

Еще одно простое использование функции — проверка вывода предупреждений. Например, если у вас есть книга с результатами процентного увеличения продаж различных товаров. Было бы полезно каким-то образом отметить те товары, продажи которых снижаются. Основная формула для такой маркировки: = ЕСЛИ(ячейка .

Ячейка здесь представляет собой адрес ячейки для проверки. Знак представляет собой текст, который будет отображаться для обозначения отрицательного значения. Вот пример: =If(B1 .

Несколько улучшенная версия знака, в которой он будет зависеть от значения отрицательной величины, выглядит следующим образом: =Repeat(» . С помощью этого выражения процентное значение умножается на 100 (минус указывается для того, чтобы значение стало положительным), а затем указывается количество цифр. Вот формула: = ЕСЛИ(B1. Пример 4.15 показывает, как это будет выглядеть на практике.

Рис. 4.15. Пример использования функции ЕСЛИ()

Рисунок 4.15. Пример использования функции IF()

Обработка ложного результата

Как видно на рис. 4.15, функция FALSE возвращается, если функция IF() принимает отрицательное значение. Хотя это и не является очевидным недостатком, создается впечатление, что рабочие листы «тяжелее», чем если бы результат возвращал, например, пустую строку.

I F(log_expression; value_if_truth; value_if_false) — это синтаксис, который вам нужно использовать. Первые два аргумента мы с вами уже знаем, а последний аргумент value_if_false задает значение, которое будет возвращать IF(), если результат вычисления log_expression окажется ложным.

Например, рассмотрим следующую формулу: =ЕСЛИ(A1>1000;»много»;»мало») . Если на этот раз ячейка A1 содержит число, меньшее или равное 1000, формула выведет строку «мало». Для примера с отметкой отрицательных объемов продаж (см. выше рис. 4.15) необходимо использовать следующую формулу: =ЕСЛИ(B1 .

Читайте так же:
Как быстро найти и отфильтровать ячейки по формату курсива в Excel?

Рис. 4.16. Обработка ложного результата

Рис. 4.16. Обработка ложных результатов

Как видно на рисунке 4.16, теперь электронная таблица выглядит гораздо лучше. Как видно из рисунка 4.16, теперь электронная таблица выглядит гораздо лучше.

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

Как упоминалось выше, функция возвращает ошибку #ДЕЛ/0!, если формула пытается разделить любое значение на 0. Вы можете использовать функцию IF(), чтобы проверить деление и даже исключить возможность такой ошибки.

Например, простейшее вычисление процента валовой прибыли по формуле (Доходы — Расходы)/Доходы. Для уверенности в том, что значение Доходы не равно нулю, используйте следующую формулу (предполагается, что вместо слов Доходы и Расходы будут подставлены необходимые значения): =ЕСЛИ(Доходы <> 0; (Доходы — Расходы)/Доходы; «Доходы равны 0!») . Если логическое выражение Доходы <> 0 верно, значит, произойдет вычисление по формуле. В противном случае функция вернет предупреждающее сообщение вместо ошибки.

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector