ORAZ i LUB w Excelu: warunki, przedziały i JEŻELI

„Co najmniej 80 punktów i obecność” oraz „co najmniej 80 punktów lub obecność” brzmią podobnie tylko do chwili, gdy trzeba przyznać zaliczenie. Jedno słowo zmienia listę osób, które przejdą dalej. W Excelu za tę różnicę odpowiadają ORAZ i LUB, czyli angielskie AND i OR.

Ja zaczynam od zapisania reguły zwykłym zdaniem. Dopiero potem przekładam ją na formułę. Pokażę obie funkcje na tym samym małym zbiorze syntetycznym, a następnie przejdę do przedziałów liczbowych i zagnieżdżania warunków. Dzięki temu łatwiej zobaczyć, dlaczego wynik jest poprawny, zamiast tylko ufać brakowi komunikatu błędu.

Wszystkie warunki albo przynajmniej jeden

ORAZ zwraca PRAWDA, gdy każdy sprawdzany warunek jest prawdziwy. Jedno FAŁSZ wystarczy, żeby całe ORAZ zwróciło FAŁSZ. LUB potrzebuje co najmniej jednego PRAWDA. Jeśli oba warunki są prawdziwe, LUB też zwraca PRAWDA.

To ważne: zwykłe LUB nie oznacza „dokładnie jeden”. Jeśli ktoś spełni oba warunki, nadal spełnia regułę „co najmniej jeden”. Definicje potwierdzają opis ORAZ oraz opis LUB w dokumentacji Microsoft.

Nie musisz uczyć się tej tabeli na pamięć. Przy ORAZ pytam: czy znalazłem choć jeden niespełniony wymóg? Przy LUB: czy znalazłem choć jeden spełniony wariant? Te pytania wystarczają, żeby prześledzić mały przykład.

Jeden zbiór, dwie różne reguły

Dane są syntetyczne. Nagłówki umieść w A1:C1, rekordy w wierszach 2:5. W kolumnie C wpisz wartości logiczne PRAWDA i FAŁSZ, bez cudzysłowów. W H2 wpisz próg 80.

Pierwsza reguła wymaga co najmniej 80 punktów i obecności:

=ORAZ(B2>=$H$2;C2=PRAWDA)

Po skopiowaniu w dół tylko Ada dostaje PRAWDA. Ben ma punkty, ale nie ma obecności. Celina ma obecność, lecz za mało punktów. Dan nie spełnia żadnego warunku.

Druga, alternatywna reguła pozwala spełnić jeden z wymogów:

=LUB(B2>=$H$2;C2=PRAWDA)

Teraz PRAWDA dostają Ada, Ben i Celina. Dan nadal ma FAŁSZ. To inne zasady, więc zmiana funkcji bez uzgodnienia reguły zmieni sens raportu. Warto pokazać obie kolumny obok siebie i porównać konkretne osoby.

W polskich przykładach stosuję średniki. W angielskim Excelu użyjesz nazw AND i OR; separator argumentów zależy od ustawień regionalnych. Nie dopisuję operatorów słownych pomiędzy testami. Każdy test jest osobnym argumentem wewnątrz nawiasu funkcji.

Jak zwrócić czytelny status przez JEŻELI

Same wartości logiczne są wygodne przy sprawdzaniu reguły. Odbiorcy raportu możesz pokazać etykietę:

=JEŻELI(ORAZ(B2>=$H$2;C2=PRAWDA);"Zaliczone";"Niezaliczone")

ORAZ odpowiada na pytanie o warunki. JEŻELI wybiera tekst zgodnie z uzyskaną odpowiedzią. Te dwa kroki warto rozdzielić przy nauce, a czasem także w samym raporcie: osobna kolumna z testem pomaga wyjaśnić, skąd wzięła się etykieta.

W poradniku JEŻELI rozwijam dobór wyników i brakujące dane. Tutaj istotne jest to, że do JEŻELI przekazujesz jeden końcowy test, nawet jeśli ten test składa się z kilku porównań.

Próg H2 blokuję znakami $, bo przy kopiowaniu ma pozostać wspólny. Odwołania B2 i C2 zmieniają się wraz z wierszem. W pierwszej kopii zawsze sprawdzam, czy formuła zaczęła czytać dane kolejnej osoby. Zablokowany przez pomyłkę numer wiersza może sprawić, że kolejne testy nadal czytają dane pierwszej osoby.

Wewnątrz przedziału używam ORAZ

Załóżmy, że akceptowany wynik liczbowy mieści się od 80 do 100 włącznie. Potrzebujesz spełnienia dwóch granic jednocześnie:

=ORAZ(B2>=80;B2<=100)

Wartość 85 przechodzi, 70 odpada, 120 także odpada. Wartości 80 i 100 przechodzą, ponieważ oba porównania uwzględniają równość. Gdy któraś granica ma być otwarta, zmieniasz odpowiedni operator na > albo <.

Zapis 80<=B2<=100 nie jest bezpiecznym skrótem dwóch porównań w Excelu. Dzielę przedział na dwa jawne testy i łączę je funkcją. Dzięki temu każdy fragment formuły odpowiada jednemu wymogowi.

Błędne LUB(B2>=80;B2<=100) przepuściłoby każdą zwykłą liczbę: wartość poniżej 80 spełnia drugi warunek, a wartość powyżej 100 pierwszy. Taką pomyłkę łatwo wykryć, wpisując liczbę wyraźnie spoza przedziału, zamiast sprawdzać wyłącznie 85.

Poza przedziałem używam LUB

Jeśli chcesz zaznaczyć wartości nieprawidłowe, reguła brzmi: mniej niż 80 lub więcej niż 100.

=LUB(B2<80;B2>100)

Tu 70 i 120 dają PRAWDA, a 80, 85 i 100 dają FAŁSZ. Nie zmieniłem dopuszczalnego zakresu. Zmieniłem pytanie z „czy mieści się?” na „czy wymaga sprawdzenia?”. W nazwie kolumny powinno być widać, o które pytanie chodzi.

Grupowanie bardziej złożonych zasad

Rozważmy inną syntetyczną regułę: osoba musi być obecna i dodatkowo mieć co najmniej 80 punktów albo projekt ze statusem Gotowe w D2. Zapisuję ją tak:

=ORAZ(C2=PRAWDA;LUB(B2>=$H$2;D2="Gotowe"))

Zewnętrzne ORAZ utrzymuje obowiązek obecności. Wewnętrzne LUB daje dwa sposoby spełnienia drugiego wymogu. Przy braku obecności żaden projekt nie pomoże, bo zewnętrzny test nadal zawiera FAŁSZ.

Inna kolejność funkcji może wyrazić inną regułę. Na przykład LUB z projektem jako osobną alternatywą mogłoby dopuścić zaliczenie bez obecności. Zamiast zgadywać po nawiasach, rozpisuję najpierw obowiązkowe wymogi, potem warianty. Każdy poziom nawiasu powinien mieć odpowiednik w zdaniu.

Jeśli zdanie pozostaje niejasne, zatrzymuję się przed wpisaniem formuły. W arkuszu nie da się technicznie naprawić braku decyzji, czy projekt zwalnia z obecności. To pytanie do autora reguły, a nie do Excela.

Tekst, puste komórki i błędy

Microsoft podaje, że tekst i puste komórki w argumentach będących odwołaniami lub tablicami mogą być pomijane przez ORAZ i LUB. Zakres bez wartości logicznych może dać błąd. Dlatego unikam traktowania ORAZ(B2:C2) jako walidacji surowych danych.

Jawne porównania mówią więcej. C2=PRAWDA sprawdza konkretną wartość logiczną, natomiast słowo "Tak" jest tekstem i wymaga innego porównania. Ustalam jedną konwencję kolumny, zamiast mieszać checkboxy, puste pola i kilka wariantów odpowiedzi.

Brak wyniku w kolumnie punktów również potrzebuje decyzji. Do sprawdzenia typu możesz użyć CZY.LICZBA, a brak danych pokazać osobnym statusem. Nie zakładam też, że dodanie warunku o braku błędu do ORAZ zabezpieczy każde dalsze wyrażenie. Funkcje logiczne nie służą jako uniwersalny mechanizm przechwytywania błędów.

Ćwiczenie: znajdź przypadek, który zmienia decyzję

W tabeli zmień wynik Bena na 80. Przy ORAZ nadal nie zaliczy przez brak obecności, przy LUB nadal zaliczy przez punkty. Następnie zmień go na 79. Przy LUB straci zaliczenie. To pokazuje jednocześnie rolę granicy i rolę drugiego warunku.

Dopisz osobę z wynikiem 100 oraz osobę ze 101. Dla przedziału 80-100 pierwsza ma przejść, druga odpaść. Następnie sprawdź regułę „poza przedziałem”: odpowiedzi powinny się odwrócić. Przy prawidłowych liczbach te dwa testy opisują uzupełniające się sytuacje.

Gdy z warunków chcesz przejść do sumowania sprzedaży, przeczytaj SUMA.WARUNKÓW. Najpierw ustal jednak, co kwalifikuje pojedynczy wiersz. Dobra agregacja zaczyna się od dobrze opisanej populacji.

Wersja do oglądania