Jak używać sumy częściowej z sumą iloczynu w programie excel

Aby połączyć funkcje SUMA CZĘŚCIOWA i ILOCZYNNIK w programie Excel, możesz użyć następującej formuły:

 =SUMPRODUCT( C2:C11 ,SUBTOTAL(9,OFFSET( D2:D11 ,ROW( D2:D11 )-MIN(ROW( D2:D11 )),0,1)))

Ta konkretna formuła pozwala zsumować iloczyn wartości zakresu C2:C11 i zakresu D2:D11 nawet po tym, jak ten zakres komórek został w jakiś sposób przefiltrowany.

Poniższy przykład pokazuje, jak zastosować tę formułę w praktyce.

Przykład: Jak używać PODSUMY z SUMPRODUCT w Excelu

Załóżmy, że mamy następujący zbiór danych zawierający informacje o sprzedaży różnych produktów w dwóch różnych sklepach spożywczych:

Następnie przefiltrujmy dane, aby wyświetlić tylko wiersze, których wartość w kolumnie Sklep wynosi B.

Aby to zrobić, zaznacz zakres komórek A1:D11 . Następnie kliknij kartę Dane na górnej wstążce i kliknij przycisk Filtruj .

Następnie kliknij strzałkę rozwijaną obok opcji Sklep i upewnij się, że zaznaczone jest tylko pole obok B , a następnie kliknij OK :

Dane zostaną automatycznie przefiltrowane, aby wyświetlić tylko wiersze, w których kolumna Store ma wartość B :

Jeśli spróbujemy użyć funkcji SUMRPODUCT() do zsumowania iloczynu wartości kolumn Sprzedaż i Cena, w rzeczywistości zwróci ona sumę iloczynu tych dwóch kolumn w oryginalnym zbiorze danych:

Zamiast tego musimy zastosować następującą formułę:

 =SUMPRODUCT( C2:C11 ,SUBTOTAL(9,OFFSET( D2:D11 ,ROW( D2:D11 )-MIN(ROW( D2:D11 )),0,1)))

Poniższy zrzut ekranu pokazuje, jak zastosować tę formułę w praktyce:

Ta formuła zwraca poprawną sumę 77,5 .

Możemy potwierdzić, że jest to prawidłowe, ręcznie obliczając sumę iloczynu wartości pomiędzy kolumnami Sprzedaż i Cena:

Suma iloczynu wartości sprzedaży i ceny: (3*2) + (12*2,5) + (5*3,5) + (8*3) = 77,5 .

Dodatkowe zasoby

Poniższe samouczki wyjaśniają, jak wykonywać inne typowe operacje w programie Excel:

Jak usunąć filtrowane wiersze w programie Excel
Jak liczyć filtrowane wiersze w programie Excel
Jak sumować filtrowane wiersze w programie Excel