Обща формула
=SUMPRODUCT(costs,--(range="x"))
Обобщение
За да изчислите ценообразуването на пакета продукти, като използвате просто „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 във втория израз на единици и нули, без да е необходимо двойно отрицателно.
