CALCULATE() — более мощный аналог СУММЕСЛИМН()

1. Более понятный синтаксис;
2. Он не ограничивается лишь подсчётом суммы по условию. К примеру в Excel, СУММЕСЛИМН() используется для подсчёта суммы по условию, СЧЕТЕСЛИМН() для подсчёта количества по условию а СРЗНАЧЕСЛИМН() для подсчёта среднего значения. Однако в Excel нету функций МАКСЕСЛИМН(), МИНЕСЛИМН() или же СТДОТКЛЕСЛИМН(). В этом смысле CALCULATE() безграничен. Он позволяет использовать любую функцию агрегирования (либо комплексную формулу) и рассчитывать её по указанным условиям;
3. Он используется для создания мер а СУММЕСЛИМН() не может быть использован в сводных таблицах.

Синтаксис функции CALCULATE()

CALCULATE(,,. )
пример: CALCULATE(SUM(t_sales[Маржа]), t_sales[Год]=2001)
пример: CALCULATE([ПродажиЗаДень], t_sales[Год]=2002, t_sales[КодПродукта]=313)

CALCULATE() в действии — несколько быстрых примеров

Начнём с простой сводной таблицы. Переместим «Год» в поле «Строки», а [ИтогоПродаж] в поле «Значения».



Теперь создадим меру, которая высчитывает сумму продаж за 2002 год:

1. Мы использовали имя другой меры в качестве аргумента для CALCULATE(). То есть в качестве логического выражения в CALCULATE() может быть использована как формула так и уже существующая мера;
2. В первом фильтр-аргументе 2002 не в кавычках. Это потому, что формат столбца числовой. Если бы формат столбца был текстовым, тогда фильтр-аргумент был бы равен =»2002″;
3. В этой мере был использован лишь один фильтр-аргумент, но при необходимости можно добавить ещё столько фильтр-аргументов, сколько захотим.



Как видите, значения [Продажи_2002] и [ИтогоПродаж] в строке 2002 совпадают. Однако уверен что Вы недоумеваете почему в строках 2001, 2003, 2004 вместо нулей также отображается сумма продаж за 2002 год. Объясню чуть позже. А пока давайте заменим в поле «Строки» сводной таблицы «Год» на «НомерМесяца».



Теперь, как и обещал объясню ситуацию с первым примером.
При использовании CALCULATE() нужно учитывать три главные особенности этой функции:

1. Фильтр-аргументы функции CALCULATE() действуют в той фазе вычисления меры в которой применяются фильтры. Эти аргументы изменяют фильтр-контекст сводной таблицы;
2. Если фильтр-аргументы применяются к столбцу который уже находится в сводной таблице, то они изменяют контекст сводной для этого столбца. Именно поэтому в первом приведённом примере, функция CALCULATE() отображала одинаковое значение для всех строк столбца Год;
3. Если фильтр-аргумент применяется к столбцу, не находящемуся в сводной таблице, то он дополняет существующий фильтр-контекст сводной. Во втором примере, у нас имелась сводная таблица в которой указывалась разбивка продаж по номерам месяца. В этом примере фильтр-аргумент меры [Продажи_2002] дополнил существующий фильтр-контекст сводной и отображал продажи по каждому месяцу в 2002 году.

Два полезных примера использования функции CALCULATE()

В нашей таблице продаж, в отдельном столбце «ТипТранзакции», указаны три типа транзакций, характерных для розничного бизнеса:

[ОбычныеПродажи]=CALCULATE([ИтогоПродаж],t_sales[ТипТранзакции]=1)
[РекламныеПродажи]=CALCULATE([ИтогоПродаж],t_sales[ТипТранзакции]=3)
[Возвраты]=CALCULATE([ИтогоПродаж],t_sales[ТипТранзакции]=2)*-1

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

И с помощью этих мер мы можем вычислить сумму чистых продаж:



Или же узнать какой процент общих продаж составляют РекламныеПродажи:



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

А теперь создадим меру рассчитывающую количество активных клиентов в самый первый год начала продаж, т.е. в 2001:



Теперь, на основе этих двух мер мы можем рассчитать процент прироста клиентов по отношению к первому году начала продаж:

Комбинирование фильтр-аргументов

Как Вы уже наверное поняли, функция CALCULATE() может принимать неограниченное количество фильтр-аргументов. Однако, по умолчанию все они применяются по принципу «И тот фильтр-аргумент И этот». Если же нужно, чтобы фильтр-аргументы применялись по принципу «ИЛИ» нужно использовать оператор «||».

И помните, при использовании оператора «||» возможно сравнивать лишь значения одного столбца — в нашем случае ТипТранзакции. Нельзя использовать оператор «||» для сравнения двух разных столбцов.

Формула сумм в excel примеры несколько условий. Функция суммесли. первый из трех китов excel. Как работает суммеслимн
Этот метод очень ясен и рекомендуется для общего случая. Однако, если вы хотите получить этот результат через одну ячейку, вы можете использовать. Аналогичные функции можно использовать аналогичным образом.
[expert_bq id=»1570″]Очевидно, что в этом примере и и легко понять, что, просто добавьте строку 3 и линия Давайте посмотрим, как написать формулу, которая делает эту вещь без нас выбрать один для ячеек, которые будут добавлены. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Параметры — числовое значение, определяющее поведение функции. Опция опции параметра выглядит следующим образом. При определении функции все значения параметров будут указаны как подсказка. Предположим, мы запустили книжные магазины, и на листе мы сохранили записи заказов, таких как ниже.


Как Начать Использовать СЧЕТЕСЛИ, СУММЕСЛИ и СРЗНАЧЕСЛИ в Excel

• Диапазон. Диапазон, в котором содержатся условия поиска. Заполнять обязательно. Для 1ой задачи столбец Товар.
• Критерий. Можно заполнить числом (85), выражением («>85»), ссылкой на ячейку (B1), функцией (СЕГОДНЯ()). Определяет условие, по которому суммируются (!). Все текстовые условия заключаются в кавычки («) «>85». Заполнять обязательно. Для 1ой задачи столбец =Товар1
• Диапазон_суммирования. Ячейки, для суммирования, если они отличаются от ячеек в Диапазоне. Для 1ой задачи столбец Выручка.

Если критерием является число, двойные кавычки не нужны. Фактические ячейки для добавления, если вы хотите добавить другие ячейки, которые не заданы в диапазоне аргументов. Вы можете использовать подстановочные знаки — знак вопроса и звездочку — в аргументе критерия. Вопросительный знак соответствует одному символу. Звездочка соответствует последовательности символов.
[expert_bq id=»1570″]Диапазоном в данном случае будет являться список всех должностей сотрудников, потому что нам нужно будет определить сумму заработных плат. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Имеем таблицу, в которой указаны фамилии сотрудников, их пол и зарплата, начисленная за январь-месяц. Если нам нужно просто посчитать общее количество денег, которые требуется выдать работникам, мы используем функцию СУММ, указав диапазоном все заработные платы.

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

• диапазон_суммирования – неотклонимый аргумент, принимающий ссылку на ячейки с числами, для которых будет вычислено суммарное значение с учетом установленных критериев;
• диапазон_условия1 – неотклонимый аргумент, принимающий ссылку на ячейки, в каких содержатся данные для проверки на соответствие данному аспекту;
• условие1 – неотклонимый аргумент, принимающий ссылку на ячейку с условием поиска, текстовую запись логического выражения (к примеру, «>=100» либо «0»), проверяемый текст (к примеру, «Продавец_1», «Товар_1») либо числовое значение (К примеру, 20), определяющие ячейки в спектре условия1, для которых будут отобраны и просуммированы данные из спектра суммирования.

Задача1. Существует статистика по товарам, городам и по тому, какие показатели были достигнуты по этим позициям. Необходимо рассчитать «на какую сумму продано номенклатуры Товар1?»

Дополнительные данные → Похожие темы → Все про Exel → Как вставить значения → Как объединить ячейки → Как вставить форматы → Дополнительные данные → Вставить формулы→ Аргументы функции