Если требуется выполнить простые арифметические операции с несколькими диапазонами ячеек, суммировать результаты и использовать условия для определения ячеек для включения в вычисления, рекомендуется использовать функцию СУММПРОИЗВ.
Функция СУММПРОИЗВ использует массивы и арифметические операторы в качестве аргументов. В качестве условий можно использовать массивы, оцениваемые как "Истина" или "Ложь" (1 или 0), используя их в качестве множителей (умножая их на другие массивы).
Предположим, что нужно вычислить чистые продажи для конкретного торгового агента путем вычитания расходов из валовых продаж, как в этом примере.
- Щелкните ячейку за пределами оцениваемых диапазонов. Вот куда идет ваш результат.
- Введите =СУММПРОИЗВ(.
- Введите (, введите или выберите диапазон ячеек для включения в вычисления, затем введите ). Например, чтобы включить столбец "Продажи" из таблицы "Таблица1", введите (Таблица1[Продажи]).
- Введите арифметический оператор: *, /, +, -. Это операция, которую вы будете выполнять с ячейками, которые соответствуют всем указанным условиям. Вы можете включить дополнительные операторы и диапазоны. Умножение — это операция по умолчанию.
- Повторите шаги 3 и 4, чтобы ввести дополнительные диапазоны и операторы для вычислений. После добавления последнего диапазона, который требуется включить в вычисления, добавьте набор скобок, охватывающих все задействованные диапазоны, чтобы включить все вычисления. Например, ((Таблица1[Продажи])+(Таблица1[Расходы])).
Возможно, потребуется включить в вычисления дополнительные скобки для группировки различных элементов в зависимости от арифметических операций. - Чтобы указать диапазон для использования в качестве условия, введите *, введите ссылку на диапазон обычным образом, затем после ссылки на диапазон, но перед правой скобкой введите =", затем значение, которому нужно соответствовать, затем ". Например, *(Таблица1[Агент]="Джонс"). Это приводит к тому, что ячейки оцениваются как 1 или 0, поэтому при умножении на другие значения формулы результат будет либо тем же, либо нулевым, что фактически включает или исключает соответствующие ячейки в любых вычислениях.
- Если у вас есть другие условия, при необходимости повторите шаг 6. После последнего диапазона введите ).
Завершенная формула может выглядеть так, как показано в примере выше: =СУММПРОИЗВ(((Таблица1[Продажи])-(Таблица1[Расходы]))*(Таблица1[Агент]=B8))), где ячейка B8 содержит имя агента.
Функция СУММПРОИЗВСумма на основе нескольких условий с функцией СУММЕСЛИМНПодсчет на основе нескольких условий с помощью функции СЧЁТЕСЛИМНСреднее на основе нескольких условий с помощью функции СРЗНАЧЕСЛИМН