Funkcje testujce w Excelu cz. 1. Funkcje: JEŻELI, ORAZ i LUB

Przy funkcjach testujących ważne są dla nas testy logiczne, operacje porównania. Większość z nich ma standardowe znaki, z wyjątkiem znaku różne (<>), który tworzą dwa znaki większe niż i mniejsze niż połączone razem. Istotne jest, że przy takich operacjach porównywania Excel nie rozróżnia małych i wielkich liter. Standardowe znaki porównania i ich wyniki zostały przedstawione na Rysunku 1.

Omawianie tematu zaczniemy od funkcji JEŻELI. Przykładowe dane zostały przedstawione na Rysunku 2.

Aby odpowiedzieć na pytanie, czy danemu pracownikowi należy się premia, zaczniemy od prostego testu logicznego. Wynik będziemy chcieli uzyskać w postaci odpowiedzi „tak” lub „nie”, a następnie otrzymać kwotę tej premii. Mamy ustalony próg 25 tys. zł, od którego sprzedawcy należy się premia. Chcąc sprawdzić, czy wartość sprzedaży danego pracownika jest większa bądź równa tej kwocie, musimy napisać prosty test logiczny =B6>=$D$2. Dodatkowo należy pamiętać o zablokowaniu bezwzględnie komórki z wartością progu bonusu (D2), gdyż w innym przypadku będziemy kopiować formułę w dół. Formułę zatwierdzamy przyciskiem Enter i kopiujemy na pozostałe komórki. Otrzymamy wyniki w postaci wartości logicznych PRAWDA i FAŁSZ, przedstawione na Rysunku 3.

Jako wynik otrzymujemy wartość logiczną PRAWDA, kiedy danemu sprzedawcy należy się premia. Wartość logiczną FAŁSZ otrzymujemy, gdy dany sprzedawca nie przekroczył wymaganego progu 25 tys. zł.

Na tych wynikach będzie bazować funkcja JEŻELI. Pierwszym jej argumentem jest test_logiczny, w którym możemy napisać taki test jak w kolumnie C (Premia?) lub wykorzystać wcześniej przygotowane wyniki, czyli wpisać odwołanie do komórki z wynikiem tego testu. Drugi argument funkcji to wartość_jeżeli_prawda, czyli wartość, jaka zostanie zwrócona, jeśli test logiczny zostanie spełniony. W naszym przykładzie chcemy otrzymać słowo „Tak” (do formuły musimy je wpisać w podwójnych cudzysłowach). Trzeci argument funkcji to wartość_jeżeli_fałsz, który będzie zwracany wtedy, gdy test logiczny nie zostanie spełniony. My chcemy w takim przypadku otrzymać słowo „Nie”. Cały zapis funkcji powinien wyglądać następująco:

=JEŻELI(C6;”Tak”;”Nie”)

Powyższą formułę zatwierdzamy i kopiujemy w dół. Otrzymamy wyniki przedstawione na Rysunku 4.

Dla wartości logicznej FAŁSZ funkcja JEŻELI zwróciła swój trzeci argument (Nie), a dla wartości logicznej PRAWDA – drugi (Tak).

Teraz napiszemy kolejny raz formułę funkcji JEŻELI dla kolumny E (Ile premii?), ale tym razem test logiczny będzie w funkcji. W pierwszym jej argumencie sprawdzamy, czy wartość Sprzedaży jest większa od Progu bonusu (B6>$D$2); komórkę D2 blokujemy bezwzględnie za pomocą klawisza F4 , czyli wartość komórki E2 (blokujemy ją bezwzględnie). W przeciwnym wypadku, gdy nie należy się premia, chcemy, by Excel zwrócił wartość 0. Zapis formuły powinien wyglądać następująco:

=JEŻELI(B6>$D$2;$E$2;0)

Formułę zatwierdzamy i kopiujemy na komórki poniżej. Otrzymamy wyniki funkcji przedstawione na Rysunku 5.

Podsumowując, dzięki funkcji JEŻELI możemy otrzymać ciąg tekstowy z informacją, która więcej mówi użytkownikowi o wyniku testu, lub wartości liczbowe, np. wysokość premii.

Omówiliśmy sytuację dla jednego testu logicznego, a często potrzebujemy funkcji, która pozwoli sprawdzić więcej testów logicznych jednocześnie. Przejdźmy na kolejny arkusz (ORAZ), by przedstawić działanie funkcji ORAZ. Zagadnienie to omówimy na przykładowych danych z Rysunku 6.

Tym razem nasze zadanie będzie polegało na sprawdzeniu, czy student zdał (przechodzi na następny semestr). Mamy ustalone warunki, jakie muszą zostać spełnione na zasadzie ORAZ, czyli muszą być spełnione oba jednocześnie. Pierwszy warunek to otrzymanie minimum 60 punktów z egzaminu, a drugi to liczba zaliczonych ćwiczeń (>1). Skorzystamy tutaj z funkcji ORAZ. Pierwszym argumentem funkcji jest logiczna1, czyli pierwszy test logiczny. W naszym przykładzie musimy sprawdzić, czy liczba punktów, jaką otrzymał dany student, jest większa bądź równa 60 (B7>=$B$4). Drugi argument funkcji to logiczna2, czyli drugi test logiczny, w którym sprawdzamy, czy liczba dodatkowych projektów jest większa od 1 (C7>$C$4). Cały zapis formuły powinien wyglądać następująco:

=ORAZ(B7>=$B$4;C7>$C$4)

Powyższą formułę zatwierdzamy i kopiujemy w dół. Otrzymamy wyniki przedstawione na Rysunku 7