JEŻELI w Excelu: warunek, próg i brak danych

Masz limit 100 sztuk. W jednej komórce wpisujesz wynik, a obok chcesz zobaczyć, czy cel został osiągnięty. Możesz ręcznie dopisywać statusy, ale wtedy każda zmiana liczby wymaga pamiętania o drugiej komórce. Ja zapisuję regułę przez JEŻELI i pozwalam arkuszowi wybrać właściwy wynik.

Najtrudniejsza część często pojawia się przed wpisaniem formuły: czy dokładnie 100 oznacza sukces? Co zrobić z brakiem danych? Czy wynik ma być liczbą, czy opisem? Przejdę przez te decyzje na małej tabeli syntetycznej, bez dokładania dziesięciu funkcji, których do tego zadania nie potrzebujesz.

JEŻELI wybiera jeden z dwóch wyników

Polskie JEŻELI odpowiada angielskiemu IF. Funkcja sprawdza warunek i wybiera wynik dla prawdy albo wynik dla fałszu. Tak opisuje ją dokumentacja Microsoft.

=JEŻELI(test_logiczny;wynik_dla_prawdy;wynik_dla_fałszu)

W przykładach używam polskich nazw i średników. W angielskiej wersji zobaczysz IF i zwykle przecinki; separator zależy od ustawień regionalnych. Funkcja jest dostępna również w starszych wydaniach wymienionych w dokumentacji, więc do zwykłego wyboru dwóch wyników nie potrzebujesz najnowszych funkcji tablicowych.

Test można najpierw wpisać osobno. =B2>=100 zwraca PRAWDA albo FAŁSZ. Dopiero gdy ten warunek odpowiada Twojej regule, warto obudować go JEŻELI. W przeciwnym razie dostaniesz elegancki komunikat oparty na błędnym założeniu.

Trzy wyniki i jedna granica

Poniższe dane są syntetyczne. Wpisz nagłówki w A1:B1 i dane do wierszy 2:4. To liczby, nie tekst zawierający słowo „sztuk”. W E2 wpisz próg 100.

W C2 wpisuję:

=JEŻELI(B2>=$E$2;"Cel osiągnięty";"Poniżej celu")

Kopiuję formułę do C4. Ada ma wynik 90, więc dostaje „Poniżej celu”. Ben ma dokładnie 100 i dostaje „Cel osiągnięty”. Celina ma 120 i także osiąga cel. Gdybym użył > zamiast >=, Ben trafiłby do drugiej grupy.

Znaki $ utrzymują próg w E2 podczas kopiowania. Odwołanie B2 ma się zmieniać na B3 i B4, bo sprawdzam kolejne osoby. Microsoft opisuje różnicę między odwołaniami względnymi i bezwzględnymi. Zablokowanie obu komórek sprawiłoby, że każdy status dotyczyłby Ady.

Zawsze sprawdzam co najmniej trzy przypadki: poniżej granicy, na granicy i powyżej. Przykład z samymi wynikami 120 i 150 nie ujawni pomyłki między większe i większe lub równe. Najlepszym miejscem na znalezienie takiego błędu jest mała tabelka, zanim statusy trafią do raportu.

Tekst w cudzysłowie, liczby bez cudzysłowu

Wynikiem może być również obliczenie. Załóżmy czysto ćwiczeniową regułę: dla wyniku co najmniej 100 zwracam premiowe punkty równe dziesięciu procentom wyniku, a w pozostałych przypadkach zero.

=JEŻELI(B2>=$E$2;B2*10%;0)

Dla Ady dostaję 0, dla Bena 10, dla Celiny 12. To syntetyczny mechanizm naliczania punktów, bez związku z rzeczywistym wynagrodzeniem czy regulaminem. Chodzi o różnicę między wynikiem liczbowym i etykietą.

Nie zapisuję "0", jeśli potrzebuję liczby zero. Cudzysłowy oznaczają tekst. Podobnie "B2*10%" jest napisem, podczas gdy B2*10% wykonuje obliczenie. Microsoft wskazuje potrzebę ujmowania tekstu w cudzysłowy w opisie JEŻELI.

Lubię utrzymywać spójny typ kolumny. Statusy są tekstem, punkty liczbami. Mieszanie kwot z napisami „brak” utrudnia późniejsze sortowanie i agregację. Jeśli raport wymaga opisu problemu, dodaję osobną kolumnę kontroli zamiast wkładać wszystkie informacje do jednego pola.

Pusta komórka potrzebuje własnej reguły

Dopisz czwartą osobę w wierszu 5 i pozostaw B5 bez wyniku. Czy to brak pomiaru, czy rzeczywiste zero? W moim przykładzie brak pomiaru wymaga komunikatu „Brak danych”. Nie chcę automatycznie nazywać go nieosiągnięciem celu.

=JEŻELI(B2="";"Brak danych";JEŻELI(B2>=$E$2;"Cel osiągnięty";"Poniżej celu"))

Po skopiowaniu formuły w dół wiersz 5 dostanie osobny status. Pierwszy test wychwytuje pustą komórkę oraz pusty tekst "". Komórka zawierająca spację nie jest pustym tekstem. W importowanych danych warto sprawdzić także takie pozornie puste wartości.

Jeżeli potrzebuję sprawdzić, czy komórka rzeczywiście nie zawiera ani wartości, ani formuły, używam CZY.PUSTA. Komórka z formułą zwracającą "" nie jest wtedy pusta. Oba podejścia opisuje pomoc Microsoft o sprawdzaniu pustych komórek.

Zwrócenie "" przez JEŻELI może wizualnie schować wynik, ale formuła nadal pozostaje w komórce. Nie traktuję tego jako usunięcia danych. W raportach, gdzie brak wyniku jest istotną informacją, wolę pokazać czytelny status.

Liczba zapisana jako tekst wymaga kontroli

Jeżeli kolumna B pochodzi z eksportu, najpierw potwierdzam typy. Porównywanie błędnie zaimportowanego tekstu z progiem liczbowym może dać mylący status. Samo podobieństwo wyglądu komórek nie wystarcza.

Do kontrolowania typu służy CZY.LICZBA, czyli ISNUMBER. Wariant zwracający komunikat zamiast statusu dla wartości nieliczbowych wygląda tak:

=JEŻELI(CZY.LICZBA(B2);JEŻELI(B2>=$E$2;"Cel osiągnięty";"Poniżej celu");"Sprawdź dane")

Ta wersja celowo traktuje również pustą komórkę jako dane do sprawdzenia. Jeśli potrzebujesz oddzielnego komunikatu o braku pomiaru, zachowaj także wcześniejszy test pustki. Funkcje informacyjne opisuje Microsoft.

Nie ukrywam wszystkich problemów przez zamianę błędu na zero. Literówka w odwołaniu, nieprawidłowy format importu i brak pomiaru mają inne znaczenie. Obsługa błędów wymaga określenia, jakie sytuacje dopuszczasz, zanim wybierzesz komunikat zastępczy.

Dwa warunki i granica rozsądnego zagnieżdżania

Jeśli osiągnięcie celu wymaga także statusu Zatwierdzone w D2, test łączę przez ORAZ:

=JEŻELI(ORAZ(B2>=$E$2;D2="Zatwierdzone");"Cel osiągnięty";"Nie spełnia reguły")

Wtedy sama odpowiednia liczba nie wystarczy. Gdy alternatywne warunki mają dawać ten sam wynik, potrzebuję LUB. Mechanizm rozwijam w osobnym poradniku ORAZ i LUB, żeby tutaj skupić się na samej decyzji.

Dodatkowe JEŻELI można umieścić w jednym z wyników, jak przy obsłudze braku danych. Przy wielu progach kolejność staje się częścią reguły. Jeśli pierwszy warunek przechwytuje wszystkie wyniki od 100, późniejszy warunek dla wyników od 120 może już nigdy nie zadecydować o etykiecie.

Gdy warunków przybywa, rozważam WARUNKI (IFS) albo tabelę z progami i osobną funkcję wyszukiwania. WARUNKI zwraca wynik pierwszego spełnionego warunku, więc nadal trzeba przemyśleć kolejność. Zmiana nazwy funkcji sama nie porządkuje zasad.

Ćwiczenie: zmień regułę, potem formułę

Ustaw próg w E2 na 120. W pierwszym przykładzie Ada i Ben powinni znaleźć się poniżej celu, Celina osiągnąć cel. Następnie zmień regułę na „ściśle powyżej 120”. Teraz Celina też nie spełnia warunku. Zauważ, że zmieniło się znaczenie zdania, więc musi zmienić się operator.

Dodaj pusty wynik i liczbę zapisaną jako tekst. Wybierz świadomie, czy obie sytuacje oznaczysz do sprawdzenia, czy rozdzielisz je na dwa statusy. Zapisz te zasady nad tabelą. Osoba czytająca raport powinna rozumieć etykiety bez otwierania edytora formuły.

Jeżeli zamiast statusu potrzebujesz sumy wielu transakcji, zajrzyj do SUMA.WARUNKÓW. JEŻELI dobrze sprawdza się jako decyzja dla jednego wiersza. Dobór miary i porządne dane wejściowe nadal należą do Ciebie.

Wersja do oglądania