WARUNKI w Excelu: kolejność progów i wynik domyślny

Jeśli chcesz przypisać kategorię na podstawie kilku progów, funkcja WARUNKI pozwala zapisać kolejne pary: warunek i wynik. Jej angielska nazwa to IFS. Ja zaczynam od spisania granic na kartce, bo poprawnie wpisana formuła może nadać złą kategorię, gdy kolejność warunków nie odpowiada regule.

Polska nazwa to WARUNKI, a nie „JEŻELI.WARUNKI”. Potwierdza ją dokumentacja Microsoft. Historyczny adres tej strony pozostaje bez zmian, ale we wzorach używamy właściwej funkcji. Poniższe przykłady są syntetyczne.

Dostępność i składnia

WARUNKI jest dostępna w Microsoft 365 oraz Excelu 2019, 2021 i 2024. Nie zakładaj jej obecności w wieczystej wersji Excel 2016; przed przekazaniem pliku sprawdź wersję odbiorcy. Formuły PL używają polskich nazw i średników. Separator argumentów zależy od ustawień regionalnych, a nazwy funkcji od języka Excela.

=WARUNKI(test_logiczny1;wartość_jeśli_prawda1;[test_logiczny2;wartość_jeśli_prawda2];...)

Wynik odpowiada pierwszemu warunkowi, który ma wartość PRAWDA. To reguła wyboru odpowiedzi, więc kolejność par jest istotna. Funkcja obsługuje do 127 warunków, ale nie traktuję tej liczby jako celu projektowego. Zestaw reguł musi dać się zrozumieć i utrzymać. Kilkadziesiąt progów ukrytych w formule utrudnia aktualizację nawet wtedy, gdy mieści się w limicie.

Ocena wyniku na małej tabeli

Przyjmijmy syntetyczną punktację od 0 do 100, z poprawnymi wartościami liczbowymi. W A1 wpisz Punkty, w B1 Kategoria, a poniżej kolejno dane z tabeli. Kolumna B przedstawia oczekiwany wynik opisanej reguły.

W B2 wpisz i skopiuj w dół:

=WARUNKI(A2>=90;"A";A2>=75;"B";A2>=50;"C";PRAWDA;"D")

Dla 95 pierwszy warunek jest prawdziwy i daje A. Dla 89 pierwszy jest fałszywy, drugi prawdziwy, więc wynikiem jest B. Dla 74 pasuje dopiero próg 50, zatem dostajesz C. Dla 49 wszystkie porównania progowe są fałszywe, a końcowa para PRAWDA i D zapewnia odpowiedź domyślną.

Operatory >= włączają wartości graniczne. Dlatego 90 należy do A, 75 do B, a 50 do C. Zamiana >=90 na >90 przeniosłaby dokładnie 90 do następnej kategorii. Te pojedyncze wartości sprawdzam osobno, zamiast oceniać formułę wyłącznie na wygodnych przykładach ze środka przedziału. Granice często stanowią właśnie treść reguły biznesowej.

Dlaczego progi układam malejąco

Gdy sprawdzasz dolne granice przez >=, zacznij od najwyższego progu. Rozważ błędnie uporządkowaną regułę:

=WARUNKI(A2>=50;"C";A2>=75;"B";A2>=90;"A";PRAWDA;"D")

Dla 95 prawdziwe są wszystkie trzy porównania. Wynik to jednak C, ponieważ pierwsza pasująca para ma próg 50. Funkcja nie szuka największego spełnionego progu i nie porządkuje warunków za autora. Jeśli reguły mają inne priorytety, kolejność musi odzwierciedlać te priorytety, nie tylko wysokość liczb.

To samo dotyczy kategorii tekstowych i wyjątków. Szczególny przypadek trzeba umieścić tam, gdzie nie zostanie zasłonięty przez ogólniejszy warunek. Przed budowaniem formuły zapisuję dla każdego wiersza tabeli reguł, jakie wartości powinien obejmować. Potem wybieram przykład trafiający w każdą odpowiedź i sprawdzam go przez zwykłe porównanie z tą definicją.

Domyślna odpowiedź i brak dopasowania

=WARUNKI(A2>=90;"A";A2>=75;"B";A2>=50;"C")

Dla poprawnej liczby 49 żaden warunek nie jest prawdziwy, więc ten wariant zwraca #N/D (#N/A w EN). Końcowa para PRAWDA;"D" rozwiązuje właśnie brak dopasowania dla niższych punktacji. Microsoft opisuje takie użycie wartości domyślnej. Nie jest to osobny argument „w przeciwnym razie”: to dodatkowy warunek, który zawsze pasuje.

PRAWDA umieszczone na początku przejęłoby wszystkie poprawne wejścia. Dlatego domyślną parę trzymam na końcu. Nie opakowuję całej funkcji w JEŻELI.BŁĄD tylko po to, żeby ukryć brak ostatniej kategorii. Najpierw poprawiam kompletność reguł. Szeroka obsługa błędów może zasłonić również błędne odwołanie albo problem w danych; więcej o tym w haśle JEŻELI.BŁĄD.

Puste pola i niepoprawne wejście to osobna reguła

Tabela z punktacją zakłada liczbę od 0 do 100. Pusta komórka, tekst „brak”, ujemna liczba, wartość 120 albo błąd źródłowy nie spełniają tej umowy. Końcowe PRAWDA nie potwierdza poprawności danych. Zanim przyznasz kategorię, rozdziel rekordy poprawne i wymagające wyjaśnienia, stosując jawną kontrolę danych wejściowych.

W formularzu można ograniczyć wprowadzanie danych, a w imporcie sprawdzić typ i zakres wartości przed klasyfikacją. Nie traktuj pustego pola jako wyniku zero tylko dlatego, że tak wygodnie wygląda gotowa tabela. Zero oznacza tutaj rzeczywistą liczbę punktów, natomiast brak wyniku wymaga osobnego statusu. Ten wybór należy uzgodnić z osobą odpowiedzialną za proces oceniania.

Nie wstawiaj też ryzykownego obliczenia do późniejszej pary z założeniem, że nigdy nie będzie przeliczane. Reguła „pierwszy prawdziwy warunek wybiera wynik” opisuje odpowiedź funkcji, a nie ogólną gwarancję sposobu wykonywania dowolnych zagnieżdżonych obliczeń. W przykładzie wszystkie wyniki są prostymi etykietami, więc uwaga skupia się na wyborze kategorii i granicach.

JEŻELI lub tabela reguł

Tę samą klasyfikację można opisać zagnieżdżonym JEŻELI:

=JEŻELI(A2>=90;"A";JEŻELI(A2>=75;"B";JEŻELI(A2>=50;"C";"D")))

Dla poprawnych wejść z tabeli otrzymasz te same etykiety. Zaletą WARUNKI jest płaski zapis par, który bywa łatwiejszy do przeczytania. Nie wynika z tego automatyczna przewaga wydajności. Do dwóch odpowiedzi zwykłe JEŻELI często w zupełności wystarcza.

Jeśli progi regularnie się zmieniają, rozważ oddzielną tabelę reguł i odpowiednio dobrane wyszukiwanie. Dzięki temu osoba utrzymująca proces widzi progi bez rozplątywania formuły. Najpierw trzeba jednak ustalić porządek, rozłączność i pokrycie zakresu. Przeniesienie niejasnej reguły do tabeli samo w sobie nie czyni jej poprawną.

Ćwiczenie z granicami

Dla głównej formuły wyprowadź wyniki 89, 90 i 91: B, A, A. Następnie podnieś próg A do 92 i sprawdź te same wartości ponownie: wszystkie trafią do B. Na końcu usuń parę domyślną i rozważ 49. Zapisz, dlaczego pojawia się brak dopasowania i dlaczego końcowy warunek rozwiązuje ten problem, lecz nie zastępuje sprawdzenia poprawności wejścia.