Как проверить, существует ли значение в другом столбце, а затем суммировать в Excel?

CALCULATE и CALCULATETABLE в Power BI и Power Pivot — самые важные функции языка DAX

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В этой статье мы разберем, пожалуй, самые важные и главные функции в языке DAX — это CALCULATE и CALCULATETABLE. Эти функции используются практически во всех формулах и вычислениях, которые мы составляем в Power BI или Excel (Powerpiwot).

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

По всем свойствам обе функции CALCULATE и CALCULATETABLE полностью идентичны, и различаются лишь тем, что CALCULATE — работает с выражениями, возвращающими одно конкретное скалярное значение, а CALCULATETABLE — с табличными выражениями. Но, обо всем по порядку.

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

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

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

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

CALCULATE () — вычисляет выражение, измененное внутренними фильтрами.

Синтаксис: CALCULATE (Выражение; Фильтр 1; Фильтр 2; …; Фильтр N), где:

• Выражение — то выражение, которое нужно вычислить (обязательный параметр для CALCULATE, без него эта функция работать не будет)
• Фильтр — условия фильтров (необязательный параметр, количество фильтров может быть от 0 до многих и все они сочетаются в режиме «и»)

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

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

Пример использования функции CALCULATE

Чтобы понять, как работают формулы DAX на основе CALCULATE, рассмотрим несколько примеров их работы в Power BI Desktop.

Имеется исходная таблица «Общие Продажи»:

Задача — вычислить сумму продаж только по первому отделу. Создадим в Power BI Desktop во вкладке «Моделирование» меру по следующему коду формулы с участием функций SUM и CALCULATE:

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

Результатом этого кода формулы будет сумма продаж для первого отдела 49000:

Причем созданный функцией CALCULATE фильтр, будет заменять любой другой фильтр, который может наложить пользователь во вкладке «Отчеты» в Power BI.

В этом можно убедиться на примере ниже, где на срезе «Отделы» установлен пользовательский фильтр «Второй отдел», но CALCULATE полностью заменяет его своим условием и формула высчитывает сумму опять же только по первому отделу, то есть сумма продаж равна 49000:

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

Более того, второй и последующие параметры CALCULATE можно использовать для объединения сложных формул и расчетов в Power BI и Power Pivot.

В этой вводной статье мы пока не будем рассматривать сложные примеры, но вот еще один пример формулы, который немного сложнее предыдущего.

Используйте функцию COUNT для подсчета количества менеджеров, чей общий объем продаж превышает 20 000. При условии, что менеджеры не принадлежат к первому отделу. Условия для этой формулы будут определены с помощью фильтров CALCULATE:

В этой формуле COUNT считает количество менеджеров, находясь под действием сразу двух фильтров от CALCULATE, где столбец [Отдел] неравен значению «Первый отдел» и одновременно с этим в столбце [Продажи] все значения должны быть более 20000. Как итог, формула рассчитает количество менеджеров, равное 2:

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

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

CALCULATETABLE () — вычисляет табличное выражение, измененное внутренними фильтрами.

Синтаксис: CALCULATETABLE (Табличное выражение; Фильтр 1; Фильтр 2; …; Фильтр N), где:

• Табличное выражение — то табличное выражение, которое нужно вычислить (обязательный параметр для CALCULATETABLE, без него эта функция работать не будет)
• Фильтр — условия фильтров (необязательный параметр, количество фильтров может быть от 0 до многих и все они сочетаются в режиме «и»)

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

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

В этом примере также рассчитаем сумму продаж, но только по второму отделу. В данном случае фильтры мы будем устанавливать функцией CALCULATETABLE, а сумму рассчитаем при помощи второй функции суммирования в DAX — SUMX (подробный разбор функции SUMX Вы можете прочитать в этой статье):

В данной формуле CALCULATETABLE возвращает таблицу, отфильтрованную по условию «Второй отдел», причем этот фильтр будет заменять любой другой пользовательский фильтр, наложенный на этот же столбец [Отдел]. А SUMX рассчитает по этой отфильтрованной таблице сумму по столбцу [Продажи]. В итоге, получится сумма продаж именно по второму отделу, равная 40500:

Вот и все в этой статье о разборе CALCULATE и CALCULATETABLE в Power BI и Power Pivot.

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

Использовать Power BI, BI-аналитику и, в частности, функции и формулы DAX очень просто. Главное — все понять и не просто прочитать статью, а повторить все самостоятельно на практике. Когда придет результат, долго ждать не придется!

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

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

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

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

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

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

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

Язык MS DAX для Power BI и Excel (Powerpivot)

TOTALYTD, TOTALQTD и TOTALMTD — DAX функции категории time intelligence, вычисляющие выражение в текущем периоде в Power BI и Power Pivot

Как в Power BI (Power Pivot) найти текст в составе другого текста? DAX функции FIND и SEARCH

Считаем сумму в столбце в Экселе

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

Способ 1: Просмотр общей суммы

Например, если вам просто нужно узнать общее значение столбца, содержащего определенную информацию, но вам не нужно постоянно держать перед собой формулу, сделайте следующее:

1. С помощью мыши выделите диапазон ячеек в столбце, сумму значений в котором вы хотите рассчитать. В нашем примере это будут ячейки с 3 по 9 в столбце G.

Примечание: Слева от суммы указано количество выделенных ячеек и среднее значение всех чисел, находящихся в них.

Способ 2: Автосумма

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

Нажмите левой кнопкой мышки (ЛКМ) по пустой ячейке, расположенной под теми, сумму которых требуется посчитать.

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

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

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

Вы можете выбрать диапазон ячеек, используя немного другой метод — сначала щелкните первую ячейку, затем, удерживая нажатой клавишу «SHIFT», щелкните последнюю.

Способ 3: Автосумма нескольких столбцов

Иногда необходимо вычислить сумму более чем в одном столбце рабочего листа Excel. Это делается практически так же, как и выше, но с одним небольшим отличием.

Щелкните ЛКМ по той ячейке, в которую планируете выводить сумму всех значений.

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

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

Способ 4: Суммирование вручную

В Microsoft Excel «Sum» имеет свой синтаксис, и поэтому его можно написать вручную. Почему это необходимо? Если ни по какой другой причине, то потому, что таким образом вы сможете избежать случайных ошибок при выборе диапазона для суммирования и указании ячеек для включения. Более того, даже если вы допустите ошибку на каком-либо из этапов, ее будет так же легко исправить, как и банальную опечатку в тексте, а значит, вам не придется делать всю работу заново, как это иногда приходится делать.

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

Формула суммы обладает следующим синтаксисом

= SUMM(общее число ячеек или диапазон ячеек)

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

Для диапазона: А1:A10 – это сумма 10 первых ячеек в столбце A.

Теперь перейдем непосредственно к ручному суммированию значений в столбцах электронной таблицы Excel.

Выделите нажатием ЛКМ ту ячейку, которая будет содержать итоговую формулу.

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

Выборочные вычисления по одному или нескольким критериям

Например, вы можете иметь таблицу продаж следующего вида:

Задание: обобщить все заказы, которые менеджер Григорьев сделал для магазина «Копейка».

Способ 1. Функция СУММЕСЛИ, когда одно условие

Для решения нашей задачи нам понадобилось бы использовать функцию SUMIF из категории Math&Trig в Excel, если бы в ней было только одно условие (например, все заказы Петрова или все заказы Копейки). Выберите пустую ячейку для результата, нажмите кнопку fx в строке формул и найдите в списке функцию СУММЕСЛИ:

Нажмите OK и введите аргументы:

• Диапазон — это ячейки, которые мы проверяем на соответствие критериям. В нашем случае это диапазон с фамилиями менеджеров по продажам.
• Критерий — это то, что мы ищем в указанном выше диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) в качестве масок или подстановочных знаков. Звездочка заменяет любое число любого символа, знак вопроса заменяет любой символ. Так, например, чтобы найти все продажи менеджеров с фамилией из пяти букв, можно использовать критерий . А чтобы найти все продажи менеджеров, чья фамилия начинается на «П» и заканчивается на «В», используйте критерий П*В. Нет разницы между прописными и строчными буквами.
• Range_amounts — это ячейки, значения которых мы хотим суммировать, т.е. в нашем случае — значения заказов.

Способ 2. Функция СУММЕСЛИМН, когда условий много

Поскольку функция SUMIF не проверяет наличие более одного условия (например, нахождение суммы всех заказов Григорьева для Копейки), ваш поиск будет безуспешным. Поэтому в Excel 2007 в набор функций была добавлена функция СУММЕСЛИМС (SUMIFS), а количество ее условий увеличено до 127! Эта функция также является математической, но имеет больше аргументов.

Используйте полосу прокрутки в правой части окна, чтобы указать третью пару (Range_Condition3-Condition3), затем четвертую и так далее, если это необходимо. — при необходимости.

Если у вас все еще стоит старая версия Excel 2003, но вам нужно устранить несколько неполадок, сделать несколько интересных вещей, обратитесь к следующим методам.

Способ 3. Столбец-индикатор

Добавим в нашу таблицу еще один столбец, который будет служить своего рода индикатором: если заказ достался Копейке и Григорьеву, то ячейка в этом столбце будет иметь значение 1, иначе — 0. Формула, которую нужно ввести в эту колонку, очень проста:

=(A2=»Копейка»)*(B2=»Григорьев»)

Логические уравнения в круглых скобках дают значения TRUE или FALSE, что для Excel равно 1 и 0. Результат будет таковым, только если оба условия выполнены, поскольку мы перемножаем выражения. После умножения значений продаж на значения результирующего столбца остается просуммировать выборку в зеленой ячейке:

Способ 4. Волшебная формула массива

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

=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)

После ввода этой формулы нажмите Ctrl + Shift + Enter вместо Enter, Excel примет ее как матричную формулу и добавит скобки. Вводить скобки на клавиатуре не обязательно. Легко видеть, что этот метод (как и предыдущий) легко расширяется до трех, четырех и т.д. условий без каких-либо ограничений.

Способ 4. Функция баз данных БДСУММ

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

Excel works!

Вы столкнулись с проблемой, когда Excel не вычисляет формулу? Если да, то вам, вероятно, следует разделить проблему на две основные причины:

Первый вариант — вы ввели формулу, она отображается, но не считается.

Второй вариант — вы ввели формулу, она считается, но выводится ошибка.

Более подробно о каждом случае можно прочитать ниже:

Excel не считает формулу в ячейке

Формулу вы ввели, но расчет не происходит, в чем дело? Скорее всего формат ячейки скорее всего Текстовый (Правой кнопкой мыши — Формат Ячеек — вкладка Число — Текстовый)

Поставьте формат Общий или Числовой. Теперь необходимо зайти в ячейку и активировать формулу — т.е. выбрать ее, войти внутрь (F2) и нажать Enter.

«Хах», — скажете вы — «Так у меня таких строк тыща». Резонно. В случаях для первого типа проблемы, в левом верхнем углу ячейке будет зеленый треугольник, если Excel посчитает, что введенный текст в ячейке относится к числовому формату. Тогда при выборе такой ячейки появится окошко «Число сохранено как текст»:

Теперь выделите все такие ячейки и нажмите на пункт меню — преобразовать в число. Готово. Этот же инструмент доступен в Формулы — Проверка наличия ошибок.

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

Просто умножьте каждое число на 1 в соседнем столбце/ячейке (например, =A1*1), и вы получите число. Теперь у вас есть числовые значения, которые вы можете копировать куда угодно 😉

Excel считает формулу, но выдает ошибку

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

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

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

Формула не учитывается. Другие случаи:

Так же я встречался со случаями, когда формула не рассчитывается, потому что был случайно включен режим просмотра формул (Лента задач — Формулы — Раздел Зависимости формул — Показать формулы)

Довольно часто на экране появляется ошибка

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

Функция ЗНАЧЕН()

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

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