Ostatnio jeden z wiernych czytelników bloga (pozdrawiam cię Piotrek:)) potrzebował obliczyć wartość średnią, jednak bez skrajnych wartości. Wymyślił, że można to zrobić funkcją ŚREDNIA.WARUNKÓW. Oczywiście można, jednak nie jest to takie oczywiste, jak może się wydawać. Pomyślałam, że jest to temat warty wspomnienia na blogu: komuś z was też może się przydać. Jak nie ten konkretny przykład, to choćby ciekawy/nie do końca intuicyjny sposób podawania kryteriów tej funkcji. Tej i innych z grupy COŚTAM.JEŻELI, COŚTAM.WARUNKÓW. Oczywiście mam tutaj na myśli np. SUMĘ.JEŻELI CZY SUMĘ.WARUNKÓW 🙂 W nich wszystkich kryteria wpisujemy dokładnie tak samo, czyli… tekstowo.

A żeby temat urozmaicić, kolorem zaznaczę jeszcze wartości skrajne.

Mój przykład będzie liczył średnią ocenę zawodników. Jest 6 sędziów, każdy daje swoją notę, 2 skrajne się odrzuca, resztę uśrednia i wychodzi wynik końcowy. Formatka wygląda tak:

Formatka

Formuła

Formuła będzie trochę skomplikowana, ale nie przerażajcie się.

Najpierw obliczę średnią bez skrajnych (część formuły z funkcją ŚREDNIA.WARUNKÓW), a potem jej wynik zaokrąglę funkcją ZAOKR do 2 miejsc po przecinku (tutaj możesz więcej poczytać o tej funkcji). Oto formuła (wpisz ją w komórce H4):

=ZAOKR(ŚREDNIA.WARUNKÓW(B4:G4;B4:G4;"<>"&MAX(B4:G4);B4:G4;"<>"&MIN(B4:G4));2)

Zobaczcie, że funkcja ŚREDNIA.WARUNKÓW, jako kryteria (3 i 5 argument) przyjęła dość dziwny zapis. Jest tam znak “<>” (w cudzysłowie), co oznacza, że komórki z argumentu 2 (potem 4) mają być różne od tego, co zwróci funkcja MAX (lub odpowiednio MIN). Żeby to jednak zadziałało, należy połączyć obie części operatorem &. W efekcie dostajemy “<>”&MAX(B4:G4). Tak właśnie wyglądają kryteria do funkcji typu ŚREDNIA.WARUNKÓW. Mają być tekstem. Trochę to mało intuicyjne, ponieważ np. w funkcji JEŻELI piszemy całe porównanie (np: B5<>5). W funkcji  ŚREDNIA.WARUNKÓW zaś za pierwszą część naszego wyrażenia służy argument z zakresem (np. 2 i 4 w moim przykładzie). Tak to po prostu działa i już. Reklamacje do Microsoftu 🙂

Jakby przepuścić to przez narzędzie Szacuj formułę, to zobaczcie, jak ładnie to wygląda:

Narzędzie szacuj formułę

Czyli funkcja MAX wyliczyła maksymalną wartość (8,8 na obrazku), a to zostało połączone z operatorem <>, i dostajemy w wyniku tekstowe kryterium: “<>8,8”.

Po skopiowaniu formuły do pozostałych komórek, otrzymujemy taki wynik:

Wynik formuły

Zaznaczanie wartości skrajnych kolorem

A teraz równie ciekawa sprawa, czyli zaznaczanie kolorem wartości skrajnych. Chcę, aby dla każdego zawodnika wartości skrajne (najmniejsza i największa) zostały zaznaczone na czerwono. Zadanie z pozoru trudne, ponieważ ciężko to wyklikać z menu żeby się nie narobić. Znów najlepiej napisać formułę. Jak znamy funkcje logiczne, a konkretnie LUB, to już z górki :).

1. Najpierw trzeba zaznaczyć zakres, który będziemy chcieli formatować, czyli B4:G10.

2. Następnie wchodzimy do formatowania warunkowego, czyli: Narzędzia główne/ Formatowanie warunkowe/ Nowa reguła i tam wybieramy na liście Użyj formuły do określenia komórek, które należy sformatować.

Wybór opcji formatowania warunkowego

To okienko wygląda nieco inaczej po wybraniu tej opcji.

3. Następnie w okienko wpisz taką formułę:

=LUB(B4=MIN($B4:$G4);B4=MAX($B4:$G4))

Oznacza ona, że jeśli wartość komórki będie równa wartości minimalnej lub maksymalnej z danego zakresu – zostanie sformatowana na czerwono. A całe okienko, po wpisaniu formuły wygląda tak:

Reguła formatowania warunkowego

Efekt jest taki:

Wartości skrajne zaznaczone kolorem

I tyle 🙂

Oczywiście zaproponowane przeze mnie rozwiązanie nie jest jedynym. Macie pomysł na inne? Dajcie znać, jestem ciekawa, co wymyślicie. Ja mam już ze 2 inne rozwiązania w głowie 🙂

Tradycyjnie plik źródłowy do pobrania tutaj:

I wersja wideo dla leniuszków:)

I zobaczcie jeszcze koniecznie propozycję Łukasza – kolegi trenera i oczywiście miłośnika Excela. Łukasz zaproponował świetne rozwiązanie, które zadziała w każdym przypadku – nawet wtedy, gdy będzie kilka wartości minimalnych czy maksymalnych (moje rozwiązanie by je wszystkie odrzuciło).