Idąc z psem na spacer, każdy z nas ma średnio trzy nogi. Ten stary dowcip potwierdza, że bazowanie na średniej mocno wykrzywia obraz rzeczywistości. Tymczasem średnia bez wartości skrajnych może dać nam wyniki bliższe prawdy. W Excelu można ją zrobić na dwa sposoby. Jak? O tym przeczytasz w tym artykule.
Formuła CSE vs. funkcja Excela
Pierwszy sposób to funkcja ŚREDNIA.WARUNKÓW, która łączy się z MIN.K i MAX.K. Zadaniem tych dwóch ostatnich jest wyeliminowanie n wartości skrajnych. Jest to funkcja tablicowa Ctrl+Shift+Enter.
Drugi sposób polega na użyciu wbudowanej funkcji ŚREDNIA.WEWN. Pozwala ona na odrzucenie określonego procenta wartości skrajnych. Taki sam procent danych odrzuca od góry i od dołu.
Screen z rozwiązaniem
Rysunek poniżej bardzo ładnie pokazuje różnice i pozwala zapoznać się z algorytmem działania obu formuł.
Dwie kwestie są tutaj bardzo ważne:
- Liczba maksymalna (40) występuje na liście dwa razy.
- Chcemy odrzucić po jednej wartości skrajnej z obu stron. Czyli jedna 40-powinna zostać uwzględniona w obliczeniach.
ŚREDNIA.WARUNKÓW
- Komórka C8. Odrzuciliśmy wartości minimalne i maksymalne. Specjalnie użyłem liczby mnogiej ponieważ odrzucamy dwie liczby 40 i najmniejszą cyfrę 9. W efekcie uzyskaliśmy średnią dla zakresu A2:A8… Nie taki był nasz zamysł. Ale patrząc na sprawę nieco inaczej – usunęliśmy wartości minimalne i maksymalne. Wszystko zależy od celu i punktu widzenia.
- Komórka C9. Zwraca taki sam wynik co ŚREDNIA(A3:A8). Usuwa po dwie wartości z dolnego i górnego zakresu. Oznacza to, że funkcja MAX.K z drugim argumentem ustawionym na 2, usuwa dwie 40-ki (zostawia 35). Tak naprawdę w górnym zakresie nic się nie dzieje – nadal eliminowane są dwie liczby. Zmiana dotyczy natomiast zakresu dolnego – usunęliśmy również liczbę 11, dlatego średnia podskoczyła nam z 22,86 na 24,83.
- Komórka C10. Trzecia formuła usuwa trzy wartości skrajne po obu stronach i zwraca ten sam wynik co ŚREDNIA(A4:A7).
- Komórka C11. Średnia bez wartości skrajnych (odrzucamy po cztery liczby z każdej strony) to tak naprawdę średnia arytmetyczna z dwóch liczb (24 + 28).
ŚREDNIA.WEWN
Nieco inaczej działa funkcja ŚREDNIA.WEWN, która usuwa określony procent wartości skrajnych.
Przyznam, że w praktyce częściej potrzebuję wyeliminować tyle samo liczb po obu stronach, dlatego korzystam z niej częściej niż z poprzedniej konstrukcji.
- Komórka C13. Zwraca średnią dla całego zakresu, czyli tak naprawdę nie eliminuje żadnych liczb. W przypadku większego zakresu wyeliminowałaby 5% liczb z zakresu dolnego i 5% liczb z zakresu górnego. Dla liczb od 1 do 100 ta formuła zwróciłaby wartość 50,5 (bez liczb z zakresu 1-5 i 96-100).
- Komórka C14. Zwraca wynik, o który mi chodzi (25). Eliminuje po jednej wartości skrajnej z góry i z dołu.
- Komórka C15. Zwraca ten sam wynik, ponieważ nie jesteśmy w stanie wyeliminować 15% liczb z jednego i 15% liczb z drugiego zakresu. Oba mają po 5 liczb.
- Komórka C16. Drugi argument ustawiony na 40% pozwala nam na usunięcie dwóch wartości skrajnych z każdego zakresu. W rezultacie otrzymujemy taki sam wynik co dla średniej A3:A8.
- Komórka C17. Analogicznie, drugi argument ustawiony na 60% eliminuje w sumie sześć wartości skrajnych (zwraca ten sam wynik co średnia A4:A7).
