Formuła ogólna
=SUMPRODUCT(expression,range)
Podsumowanie
Aby odfiltrować wyniki funkcji SUMPRODUCT według określonych kryteriów, można zastosować proste wyrażenia logiczne bezpośrednio do tablic w funkcji zamiast używać funkcji JEŻELI. W pokazanym przykładzie formuły w H5: H7 to:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
gdzie zdefiniowano następujące nazwane zakresy:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Jeśli wolisz unikać nazwanych zakresów, użyj zakresów wprowadzonych powyżej jako odniesień bezwzględnych. Wyrażenia logiczne w H6 i H7 można łączyć, jak wyjaśniono poniżej.
Wyjaśnienie
Ten przykład ilustruje jedną z kluczowych zalet funkcji SUMPRODUCT - możliwość filtrowania danych za pomocą podstawowych wyrażeń logicznych zamiast funkcji JEŻELI. Wewnątrz SUMPRODUCT pierwsza tablica jest logicznym wyrażeniem filtrującym według koloru „czerwony”:
--(color="red")
W rezultacie powstaje tablica lub wartości TRUE FALSE, które są przekształcane na jedynki i zera za pomocą operacji podwójnego ujemnego (-). Wynikiem jest ta tablica:
(1;0;1;0;0;0;1;0;0;0)
Zwróć uwagę, że tablica zawiera 10 wartości, po jednej dla każdego wiersza. Jedynka wskazuje wiersz, w którym kolor jest „czerwony”, a zero oznacza wiersz z dowolnym innym kolorem.
Następnie mamy jeszcze dwie tablice: jedną dla ilości i jedną dla ceny. Wraz z wynikami z pierwszej tablicy otrzymujemy:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
Rozwijając tablice mamy:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),(10;6;14;9;11;10;8;9;11;10),(15;18;15;16;18;18;15;16;18;16))
Podstawowym zachowaniem SUMPRODUCT jest mnożenie, a następnie sumowanie tablic. Ponieważ pracujemy z trzema tablicami, możemy wizualizować operację, jak pokazano w poniższej tabeli, gdzie kolumna wyników jest wynikiem pomnożenia tablica1 * tablica2 * tablica3 :
| tablica1 | tablica2 | tablica3 | wynik |
|---|---|---|---|
| 1 | 10 | 15 | 150 |
| 0 | 6 | 18 | 0 |
| 1 | 14 | 15 | 210 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 18 | 0 |
| 1 | 8 | 15 | 120 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 16 | 0 |
Zauważ, że tablica1 działa jak filtr - zerowe wartości tutaj „zerują” wartości w wierszach, w których kolor nie jest „czerwony”. Odkładając wyniki z powrotem do SUMPRODUCT, mamy:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Co daje końcowy wynik 480.
Dodanie dodatkowych kryteriów
Kryteria można rozszerzyć, dodając kolejne wyrażenie logiczne. Na przykład, aby znaleźć łączną sprzedaż, której kolor to „Czerwony”, a stan to „TX”, H6 zawiera:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Uwaga: SUMPRODUCT nie rozróżnia wielkości liter.
Upraszczanie za pomocą jednej tablicy
Specjaliści od Excela często upraszczają nieco składnię w SUMPRODUCT, mnożąc tablice bezpośrednio w array1 w następujący sposób:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
Działa to, ponieważ operacja matematyczna (mnożenie) automatycznie przekształca wartości PRAWDA i FAŁSZ z pierwszych dwóch wyrażeń na jedynki i zera.
