Jak używać sumy częściowej ze średną jeżeli w excelu

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

 =AVERAGE(IF(SUBTOTAL(2,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),0)),IF( B2:B11 ="Guard", C2:C11 )))

Ta konkretna formuła pozwala obliczyć średnią wartości z zakresu C2:C11, gdzie odpowiadająca wartość z zakresu B2:B11 jest równa „Zachowaj”, nawet po tym, jak ten zakres komórek został przefiltrowany w taki czy inny sposób. inny.

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

Przykład: Jak używać PODSUMY ze ŚREDNĄ JEŻELI w Excelu

Załóżmy, że mamy następujący zbiór danych zawierający informacje o różnych koszykarzach:

Następnie przefiltrujmy dane, aby wyświetlić tylko wiersze, w których znajdują się gracze Konferencji Zachodniej .

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

Następnie kliknij strzałkę rozwijaną obok opcji Konferencja i upewnij się, że zaznaczone jest tylko pole wyboru Zachód , a następnie kliknij OK :

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

Jeśli spróbujemy użyć funkcji ŚREDNIA.JEŻELI() do obliczenia średniej wartości kolumny Punkty, gdzie wartość kolumny Pozycja jest równa „Straż”, w rzeczywistości zwróci ona średnią punktów dla wierszy równych „Straż”. w oryginalnym zbiorze danych:

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

 =AVERAGE(IF(SUBTOTAL(2,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),0)),IF( B2:B11 ="Guard", C2:C11 )))

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

Ta formuła zwraca prawidłową średnią wynoszącą 21,25 .

Możemy potwierdzić, że jest to prawidłowe, ręcznie uśredniając wartości punktowe dla wierszy, w których kolumna Pozycja ma wartość „Straż”:

Średnie punkty ochronne: (12 + 28 + 30 + 15) / 4 = 21,25 .

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 dodać filtrowane wiersze w programie Excel