Excel funkcje logiczne
W artykule przedstawiam informacje na temat podstawowych funkcji logicznych w programie MS Excel.
Zastosowanie funkcji logicznych
Funkcje logiczne Excela służą przede wszystkim do przetestowania warunków, które mają być spełnione przez dane zapisane w komórkach np. możemy sprawdzić, czy wartość z komórki A1 równa się wartości z komórki A2. Jeśli wartości są równe funkcja zwróci wartość PRAWDA. Jeśli wartości są różne funkcja zwróci wartość FAŁSZ.
Funkcja JEŻELI()
Za pomocą funkcji JEŻELI() sprawdzamy warunek logiczny podany w jej pierwszym argumencie. Jeśli warunek jest spełniony (PRAWDA), w wyniku otrzymujemy wartość z drugiego argumentu. Jeśli warunek nie jest spełniony (FAŁSZ), w wyniku otrzymujemy wartość trzeciego argumentu.
Składnia funkcji JEŻELI():
- test_logiczny – argument, w którym sprawdzany jest warunek logiczny. Podajemy tutaj wartość lub wyrażenie, którego wynik możemy oszacować. W warunku logicznym często wykorzystujemy operatory porównania:
- równy (=)
- różny (<>)
- większy niż (>)
- mniejszy niż (<)
- większy równy (>=)
- mniejszy równy (<=)
- wartość_jeżeli_prawda –jest to wartość zwracana przez funkcję, gdy pierwszy argument test_logiczny jest spełniony (zwraca wartość PRAWDA)
- wartość_jeżeli_fałsz – trzeci argument funkcji jest to wartość zwracana gdy pierwszy argument nie jest spełniony (zwraca wartość FAŁSZ)
Przykład:
W przykładzie w kolumnie B mamy informację o liczbie punktów, które kandydat otrzymał w trakcie rozmowy rekrutacyjnej. Za pomocą funkcji JEŻELI() sprawdzamy, czy liczba punktów jest większa lub równa 15. Jeśli liczba punktów jest większa lub równa 15 (wynik sprawdzenia PRAWDA), w wyniku zwracany jest tekst „ok”. W przeciwnym razie w wyniku zwracany jest tekst „nieprzyjęty”.
W funkcji JEŻELI() w formie podstawowej możemy wprowadzić tylko jeden warunek logiczny. Możemy ją rozszerzyć za pomocą funkcji ORAZ() i LUB().
Funkcja ORAZ()
Funkcja ORAZ() w Excelu pozwala na wprowadzenie maksymalnie 30 warunków logicznych. Aby zwracała ona w wyniku wartość PRAWDA, każdy z jej warunków musi zwracać PRAWDĘ. Jeśli chociaż jeden z warunków przyjmie wartość FAŁSZ, funkcja zwróci w wyniku FAŁSZ.
Przykład:
Za pomocą funkcji ORAZ() sprawdzamy, czy kandydat ubiegający się o pracę otrzymał 15 lub więcej punktów, zna język angielski oraz program Excel. Wszystkie trzy warunki muszą zwracać wartość PRAWDA, aby funkcja ORAZ() zwróciła wynik PRAWDA. Jeśli chociaż jeden z warunków w formule nie zostanie spełniony, zwrócony zostanie wynik FAŁSZ.
Poniżej używamy dwóch funkcji – funkcja ORAZ() zagnieżdżona jest w funkcji JEŻELI(). W funkcji JEŻELI() sprawdzamy, czy wynikiem działania funkcji ORAZ() jest PRAWDA. Jeśli tak, funkcja JEŻELI() zwraca tekst „ok”, a jeśli nie, zwraca ona tekst „nieprzyjęty”.
Funkcja LUB()
Funkcja LUB() podobnie jak ORAZ() sprawdza prawdziwość warunków logicznych. Aby zwracała ona w wyniku wartość logiczną PRAWDA, przynajmniej jeden z jej warunków musi być spełniony. Jeśli wszystkie warunki zwracają FAŁSZ, funkcja zwróci w wyniku FAŁSZ.
Przykład:
W przykładzie za pomocą funkcji LUB() sprawdzamy, czy kandydat do pracy zna angielski lub niemiecki. Wystarczy, że w komórce arkusza jedna wartość oznaczona zostanie na „tak”, aby funkcja LUB() zwróciła w wyniku wartość PRAWDA.
Poniżej używamy dwóch funkcji – funkcja LUB() zagnieżdżona jest w funkcji JEŻELI(). Za pomocą funkcji JEŻELI() sprawdzamy, czy wynikiem działania funkcji LUB() jest PRAWDA. Jeśli tak, funkcja JEŻELI() zwraca tekst „ok”, a jeśli nie, zwraca ona tekst „nieprzyjęty”.
Zagnieżdżanie funkcji JEŻELI()
W funkcji JEŻELI() możemy zagnieździć inną funkcję JEŻELI().
Przykład:
W przykładzie wyliczamy wysokość rabatu:
- w pierwszej funkcji JEŻELI() sprawdzamy, czy wartość zakupów jest mniejsza od 100. Jeśli tak, mnożymy wartość zakupów razy 5%. Jeśli wartość zakupów nie jest mniejsza od 100 wykona się trzeci argument funkcji, w którym jest zagnieżdżona druga funkcja JEŻELI()
- w drugiej funkcji JEŻELI() sprawdzamy, czy wartość zakupów jest większa lub równa 100. Jeśli tak, mnożymy wartość zakupów razy 10%. Jeśli nie, wstawiamy zero.
Funkcje zagnieżdżone możemy uprościć opisaną wyżej funkcją ORAZ i LUB.
Funkcja JEŻELI.BŁĄD()
Funkcja JEŻELI.BŁĄD() zwraca określoną przez użytkownika wartość, jeśli wynikiem jej działania jest błąd. W przeciwnym wypadku zwraca wynik formuły.
Przykład:
W przykładzie funkcja JEŻELI.BŁĄD() zwraca wartość zero, jeśli wynikiem jej działania będzie błąd.
Funkcja JEŻELI.ND()
Funkcja JEŻELI.ND() wprowadzona została od wersji Excela 2013. Zwraca ona określoną przez użytkownika wartość, jeśli wynikiem jej działania jest błąd. W przeciwnym wypadku zwraca wynik formuły.
Przykład:
W przykładzie:
- za pomocą funkcji WYSZUKAJ.PIONOWO() wyszukujemy nazwiska klientów. Jeśli znajdziemy nazwisko przypisujemy do niego imię klienta. Jeśli nazwisko nie zostanie znalezione w wyniku otrzymujemy wartość #N/D
- za pomocą funkcji JEŻELI.ND() sprawdzamy jaka wartość jest w kolumnie B. Jeśli wynikiem działania funkcji JEŻELI.ND jest błąd typu #N/D wstawiona zostanie podana przez użytkownika wartość (w przykładzie wstawiony będzie tekst „brak danych”)
Więcej informacji na temat funkcji WYSZUKAJ.PIONOWO() znajdziesz w artykule Excel funkcja WYSZUKAJ.PIONOWO
Funkcja WARUNKI()
Funcja WARUNKI() dostępna jest od wersji Excela 2019 oraz w subskrypcji Office 365. Ma ona za zadanie uprościć pisanie formuł, w których musimy używać wielu warunków i wielokrotnie zagnieżdżać funkcję JEŻELI().
Funkcja WARUNKI() umożliwia przetestowanie maksymalnie 127 warunków.
Przykład:
W przykładzie sprawdzamy wartość sprzedaży w roku 2021 względem sprzedaży z 2020 roku:
- jeśli sprzedaż w 2021 jest większa od sprzedaży w 2020 roku wpisujemy tekst „wzrost”
- jeśli sprzedaż w 2021 jest mniejsza od sprzedaży w 2020 roku wpisujemy tekst „spadek”
- argument PRAWDA oznacza, że żaden z wcześniejszych warunków nie jest prawdziwy. Wówczas wpisywany jest tekst „brak zmian”
Zamiast argumentu PRAWDA możesz użyć argumentu C3=B3. Wynik działania funkcji w poniższym przykładzie jest zgodny z wynikiem, w którym używaliśmy argumentu PRAWDA.
Funkcja NIE()
Za pomocą funkcji NIE() możemy odwrócić wartość logiczną argumentu.
Przykład:
W przykładzie:
- dla pracowników z działu Administracji, którzy pracują dłużej niż 5 lat wstawiamy opis „podwyżka 5%”
- dla pozostałych pracowników wstawiamy opis „bez zmian”
Funkcja LAMBDA()
Stosując funkcję Lambda() możesz utworzyć funkcję do wielokrotnego użytku. Jest to funkcja dostępna w ramach Office 365 . Pozwala ona tworzyć w programie Excel nowe funkcje bez używania języka VBA.
Przykład:
W przykładzie tworzymy funkcję, za pomocą której można obliczać cenę produktu podwyższoną o podatek VAT.
Aby utworzyć funkcję:
- kliknij na kartę Formuły
- wybierz polecenie Definiuj nazwę
- w oknie Menadżera nazw kliknij Nowy
- podaj nazwę funkcji
- podaj odwołanie =LAMBDA(liczba;ZAOKR(liczba*1,23;2)). Liczba reprezentuje argument funkcji. W jego miejsce podstawiona zostanie wartość z komórki, dla której wywołamy funkcję.
- kliknijprzycisk OK
- kliknij przycisk Zamknij
Aby wywołać funkcję, wpisz w komórce Excela znak równości i początek nazwy funkcji. Excel podpowie nazwę funkcji.
Kliknij na podpowiedź. Zobaczysz wówczas kolejną podpowiedź, w której w nawiasie będzie informacja, jakich argumentów wymaga funkcja. W przypadku funkcji VAT wymaganym argumentem jest liczba.
Kliknij komórkę A2, dla której użyjesz funkcji VAT.
Wynik funkcji zobaczysz w komórce B2.
Formatowanie warunkowe a funkcje logiczne
W Excelu o funkcje może zostać oparte formatowanie warunkowe. Aby wykorzystać formułę do formatowania komórek:
- zaznacz komórki, które chcesz sformatować
- kliknij kartę Narzędzia główne, wybierz opcję Formatowanie warunkowe i następnie polecenie Nowa reguła
- kliknij opcję Użyj formuły do określenia komórek, które należy sformatować i wpisz B2<30. Jeśli przy adresie komórki pojawią się znaki dolara $B$2, usuń je
- kliknij przycisk Formatuj
- w oknie dialogowym Formatowanie komórek wybierz format i kliknij przycisk OK
- w oknie Nowa reguła formatowania kliknij przycisk OK
Sformatowane komórki w Excelu:
Przykład:
W poniższym przykładzie w formatowaniu warunkowym używamy funkcji logicznej ORAZ(), aby zaznaczyć kolorem cały wiersz, jeśli cena jest mniejsza od 30 i produkt to Tofu:
- zaznaczamy cały obszar z danymi
- wybieramy formatowanie warunkowe i wpisujemy formułę =ORAZ($B2<30;$A2="Tofu")
- wybieramy rodzaj formatowania i klikamy OK
Barbara Lelusz
Analityk biznesowy
