Formuła ogólna
=SUMPRODUCT(costs,--(range="x"))
Podsumowanie
Aby obliczyć cenę pakietu produktów za pomocą prostego „x” w celu uwzględnienia lub wykluczenia produktu, możesz użyć formuły opartej na funkcji SUMPRODUCT. W pokazanym przykładzie formuła w D11 to:
=SUMPRODUCT($C$5:$C$9,--(D5:D9="x"))
Wyjaśnienie
Funkcja SUMPRODUCT mnoży razem zakresy lub tablice i zwraca sumę iloczynów. Brzmi to nudno, ale SUMPRODUCT to elegancka i wszechstronna funkcja, którą ten przykład ładnie ilustruje.
W tym przykładzie SUMPRODUCT jest skonfigurowany z dwiema tablicami. Pierwsza tablica to zakres zawierający ceny produktów:
$C$5:$C$9
Zwróć uwagę, że odwołanie jest bezwzględne, aby zapobiec zmianom, gdy formuła jest kopiowana w prawo. Zakres ten daje następującą tablicę:
(99;69;129;119;49)
Druga tablica jest generowana za pomocą tego wyrażenia:
--(D5:D9="x")
Wynikiem D5: D9 = "x" jest tablica wartości TRUE FALSE w następujący sposób:
(TRUE;TRUE;FALSE;FALSE;FALSE)
Podwójnie ujemne (-) konwertuje te wartości PRAWDA FAŁSZ na 1 i 0:
(1;1;0;0;0)
Tak więc w SUMPRODUCT mamy:
=SUMPRODUCT((99;69;129;119;49),(1;1;0;0;0))
Funkcja SUMPRODUCT następnie mnoży razem odpowiednie elementy w każdej tablicy:
=SUMPRODUCT((99;69;0;0;0))
i zwraca sumę iloczynów, w tym przypadku 168.
W rzeczywistości druga tablica działa jako filtr dla wartości w pierwszej tablicy. Zera w tablicy2 anulują elementy w tablicy1, a jedynki w tablicy2 pozwalają wartościom z tablicy1 przejść do wyniku końcowego.
Z pojedynczą tablicą
SUMPRODUCT jest skonfigurowany do akceptowania wielu tablic, ale możesz nieco uprościć tę formułę, podając pojedynczą tablicę na początku:
=SUMPRODUCT($C$5:$C$9*(D5:D9="x"))
Operacja matematyczna (mnożenie) automatycznie przekształca wartości PRAWDA FAŁSZ w drugim wyrażeniu na jedynki i zera, bez potrzeby podwójnego ujemnego wyniku.
