Загальна формула
=SUMPRODUCT(costs,--(range="x"))
Резюме
Щоб розрахувати ціни на набори продуктів за допомогою простого «х», щоб включити або виключити товар, ви можете використовувати формулу на основі функції SUMPRODUCT. У наведеному прикладі формула в D11 має вигляд:
=SUMPRODUCT($C$5:$C$9,--(D5:D9="x"))
Пояснення
Функція SUMPRODUCT множить діапазони або масиви разом і повертає суму добутків. Це звучить нудно, але SUMPRODUCT - це елегантна та універсальна функція, яку цей приклад чудово ілюструє.
У цьому прикладі SUMPRODUCT налаштовано з двома масивами. Перший масив - це асортимент, що містить ціни на продукцію:
$C$5:$C$9
Зверніть увагу, що посилання є абсолютним, щоб запобігти змінам, оскільки формула копіюється праворуч. Цей діапазон визначається таким масивом:
(99;69;129;119;49)
Другий масив генерується з таким виразом:
--(D5:D9="x")
Результат D5: D9 = "x" - це масив TRUE FALSE значень, як це:
(TRUE;TRUE;FALSE;FALSE;FALSE)
Подвійний мінус (-) перетворює ці значення TRUE FALSE в 1s і 0s:
(1;1;0;0;0)
Отже, всередині SUMPRODUCT ми маємо:
=SUMPRODUCT((99;69;129;119;49),(1;1;0;0;0))
Потім функція SUMPRODUCT множить відповідні елементи в кожному масиві разом:
=SUMPRODUCT((99;69;0;0;0))
і повертає суму продуктів, 168 у цьому випадку.
Фактично, другий масив діє як фільтр для значень у першому масиві. Нулі в array2 скасовують елементи в array1, а 1s в array2 дозволяють значення з array1 проходити в кінцевий результат.
З одним масивом
SUMPRODUCT налаштовано для прийому декількох масивів, але ви можете трохи спростити цю формулу, надавши на початку один масив:
=SUMPRODUCT($C$5:$C$9*(D5:D9="x"))
Математична операція (множення) автоматично примушує значення TRUE FALSE у другому виразі до одиниць та нулів, не потребуючи подвійного від’ємного значення.
