Общая формула
=SUMPRODUCT(costs,--(range="x"))
Резюме
Чтобы рассчитать цены на пакеты продуктов с использованием простого символа «x» для включения или исключения продукта, вы можете использовать формулу, основанную на функции СУММПРОИЗВ. В показанном примере формула в D11:
=SUMPRODUCT($C$5:$C$9,--(D5:D9="x"))
Объяснение
Функция СУММПРОИЗВ умножает диапазоны или массивы и возвращает сумму произведений. Звучит скучно, но СУММПРОИЗВ - это элегантная и универсальная функция, что хорошо иллюстрирует этот пример.
В этом примере SUMPRODUCT настроен с двумя массивами. Первый массив - это диапазон, содержащий цены на товары:
$C$5:$C$9
Обратите внимание, что ссылка является абсолютной, чтобы предотвратить изменения, поскольку формула копируется вправо. Этот диапазон оценивается как следующий массив:
(99;69;129;119;49)
Второй массив создается с помощью этого выражения:
--(D5:D9="x")
Результат D5: D9 = "x" представляет собой массив ИСТИННЫХ ЛОЖНЫХ значений, подобных этому:
(TRUE;TRUE;FALSE;FALSE;FALSE)
Двойной минус (-) преобразует эти ИСТИННО ЛОЖНЫЕ значения в 1 и 0:
(1;1;0;0;0)
Итак, внутри SUMPRODUCT есть:
=SUMPRODUCT((99;69;129;119;49),(1;1;0;0;0))
Затем функция СУММПРОИЗВ умножает соответствующие элементы в каждом массиве:
=SUMPRODUCT((99;69;0;0;0))
и возвращает сумму произведений, в данном случае 168.
Фактически, второй массив действует как фильтр для значений в первом массиве. Нули в array2 отменяют элементы в array1, а единицы в array2 позволяют значениям из array1 проходить в окончательный результат.
С одним массивом
SUMPRODUCT настроен для приема нескольких массивов, но вы можете немного упростить эту формулу, указав в начале один массив:
=SUMPRODUCT($C$5:$C$9*(D5:D9="x"))
Математическая операция (умножение) автоматически переводит значения ИСТИНА ЛОЖЬ во втором выражении в единицы и нули без необходимости в двойном отрицании.
