Pobierz przykładowy skoroszyt
Ten samouczek pokaże, jak obliczyć „sumproduct if”, zwracając sumę iloczynów tablic lub zakresów na podstawie kryteriów.
SUMPRODUCT Funkcja
Funkcja SUMPRODUCT służy do mnożenia tablic liczb, sumując tablicę wynikową.
Aby utworzyć „Sumproduct If”, użyjemy funkcji SUMPRODUCT wraz z funkcją IF w formule tablicowej.
SUMA PRODUKT JEŚLI
Łącząc SUMPRODUCT i JEŻELI w formule tablicowej, możemy zasadniczo utworzyć funkcję „SUMPRODUCT JEŻELI”, która działa podobnie do działania wbudowanej funkcji SUMA.JEŻELI. Przejdźmy przez przykład.
Mamy listę sprzedaży osiągniętej przez menedżerów w różnych regionach z odpowiednimi stawkami prowizyjnymi:
Załóżmy, że jesteśmy proszeni o obliczenie wysokości prowizji dla każdego menedżera w następujący sposób:
Aby to osiągnąć, możemy zagnieździć funkcję JEŻELI z menedżer jako nasze kryteria wewnątrz funkcji SUMPRODUCT, jak na przykład:
=PROD.SUMA(JEŻELI(=,*))
=PRODUKTSUMA(JEŻELI($C$2:$C$10=$G2,$D$2:$D$10*$E$2:$E$10))
W przypadku korzystania z programu Excel 2022 i starszego należy wprowadzić formułę, naciskając CTRL + SHIFT + ENTER aby uzyskać nawiasy klamrowe wokół wzoru (patrz górny obraz).
Jak działa formuła?
Formuła działa, oceniając każdą komórkę w naszym zakresie kryteriów jako PRAWDA lub FAŁSZ.
Obliczanie całkowitej prowizji dla Olivii:
=PRODUKTSUMA(JEŻELI($C$2:$C$10=$G2,$D$2:$D$10*$E$2:$E$10))
= PRODUKT SUMA (JEŻELI({PRAWDA; PRAWDA;FAŁSZ; FAŁSZ; FAŁSZ; PRAWDA; FAŁSZ; FAŁSZ; FAŁSZ}, {928.62; 668,22;919.695; 447,384; 697,620; 480,564; 689,325; 752,366; 869,61}))
Następnie funkcja IF zastępuje każdą wartość FALSE, jeśli jej warunek nie jest spełniony.
= PRODUKT SUMA({928.62; 668,22; FAŁSZ; FAŁSZ; FAŁSZ; 480,564; FAŁSZ; FAŁSZ; FAŁSZ})
Teraz funkcja SUMPRODUCT pomija wartości FALSE i sumuje pozostałe wartości (2 077,40).
SUMPRODUCT IF z wieloma kryteriami
Aby użyć funkcji SUMPRODUCT IF z wieloma kryteriami (podobnie jak działa wbudowana funkcja SUMIFS), po prostu zagnieżdż więcej funkcji IF w funkcji SUMPRODUCT w następujący sposób:
=PROD.SUMA(JEŻELI(=, JEŻELI(=, *))
(CTRL + SHIFT + ENTER)
=PRODUKTSUMA(JEŻELI($B$2:$B$10=$G2,JEŻELI($C$2:$C$10=$H2,$D$2:$D$10*$E$2:$E$10)))
(CTRL + SHIFT + ENTER)
Kolejne podejście do SUMPRODUCT IF
Często w programie Excel istnieje wiele sposobów uzyskania pożądanych wyników. Innym sposobem obliczania „produktu sumarycznego, jeśli” jest uwzględnienie kryteriów w ciągu funkcja SUMPRODUCT jako tablica przy użyciu podwójnej jednoargumentowej, jak na przykład:
=PRODUKTSUMA(--($B$2:$B$10=$G2),--($C$2:$C$10=$H2),$D$2:$D$10*$E$2:$E$10)
Ta metoda używa podwójnej liczby jednoargumentowej (-), aby przekonwertować tablicę TRUE FALSE na zera i jedynki. SUMPRODUCT mnoży następnie przekonwertowane tablice kryteriów:
=PRODUKCJASUMA({1;1;0;0;0;1;0;0;0};{1;0;1;0;1;0;0;0;0};{928.62;668.22;919.695; 447,384; 697,620; 480,564; 689,325; 752,366; 869,61})
Porady i wskazówki:
- Tam, gdzie to możliwe, zawsze zablokuj odwołanie (F4) do zakresów i danych wejściowych formuł, aby umożliwić automatyczne wypełnianie.
- Jeśli używasz programu Excel 2022 lub nowszego, możesz wprowadzić formułę bez kombinacji klawiszy Ctrl + Shift + Enter.
SUMA PRODUKT JEŚLI w Arkuszach Google
Funkcja SUMPRODUCT IF działa dokładnie tak samo w Arkuszach Google, jak w programie Excel:
