Jak korzystać z funkcji logicznych w programie Excel IF, AND, OR, XOR, NOT

Funkcje logiczne są jednymi z najbardziej popularnych i użytecznych w Excelu. Mogą testować wartości w innych komórkach i wykonywać działania zależne od wyniku testu. Pomaga nam to zautomatyzować zadania w naszych arkuszach kalkulacyjnych.

Jak korzystać z funkcji IF

Funkcja IF jest główną funkcją logiczną w Excelu i dlatego jest pierwszą, którą należy najpierw zrozumieć. Pojawi się wiele razy w tym artykule.

Rzućmy okiem na strukturę funkcji IF, a następnie zobaczmy kilka przykładów jej użycia.

Funkcja IF akceptuje 3 bity informacji:

= JEŻELI (test_logiczny, [wartość_jeżeli_prawda], [wartość_jej_fałszywa])

• test logiczny: Jest to warunek sprawdzania funkcji.
• value_if_true: Czynność do wykonania, jeśli warunek jest spełniony lub jest prawdziwy.
• value_if_false: Czynność do wykonania, jeśli warunek nie jest spełniony lub jest fałszywy.

Operatory porównania do użycia z funkcjami logicznymi

Podczas wykonywania testu logicznego z wartościami komórek musisz znać operatory porównania. Możesz zobaczyć ich podział w poniższej tabeli.

Teraz przyjrzyjmy się kilku przykładom z działania.

Funkcja IF Przykład 1: Wartości tekstowe

W tym przykładzie chcemy sprawdzić, czy komórka jest równa określonej frazie. Funkcja IF nie rozróżnia wielkości liter, więc nie bierze pod uwagę dużych i małych liter.

Następująca formuła jest używana w kolumnie C, aby wyświetlić "Nie", jeśli kolumna B zawiera tekst "Uzupełnione" i "Tak", jeśli zawiera coś jeszcze.

= JEŻELI (B2 = "Zakończone", "Nie", "Tak")

Chociaż w funkcji IF nie jest rozróżniana wielkość liter, tekst musi być dokładnie dopasowany.

IF Funkcja Przykład 2: Wartości liczbowe

Funkcja IF jest również świetna do porównywania wartości numerycznych.

W poniższym wzorze sprawdzamy, czy komórka B2 zawiera liczbę większą lub równą 75. Jeśli tak, to wyświetlamy słowo "Pass", a jeśli nie, słowo "Fail".

= JEŻELI (B2> = 75, "Pomiń", "Pomiń")

Funkcja IF to znacznie więcej niż wyświetlanie innego tekstu na wynik testu. Możemy również użyć go do wykonywania różnych obliczeń.

W tym przykładzie chcemy dać 10% zniżki, jeśli klient wyda określoną kwotę pieniędzy. Użyjemy 3000 funtów jako przykładu.

= JEŻELI (B2> = 3000, B2 * 90%, B2)

Część B2 * 90% formuły to sposób, w jaki można odjąć 10% od wartości w komórce B2. Jest na to wiele sposobów.

Ważne jest to, że możesz użyć dowolnej formuły w value_if_true lub value_if_false Sekcje. I uruchamianie różnych formuł zależnych od wartości innych komórek jest bardzo potężną umiejętnością.

IF Funkcja Przykład 3: Wartości daty

W tym trzecim przykładzie używamy funkcji JEŻELI do śledzenia listy terminów płatności. Chcemy wyświetlić słowo "Zaległe", jeśli data w kolumnie B jest w przeszłości. Ale jeśli data jest w przyszłości, oblicz liczbę dni do terminu.

Poniższy wzór jest użyty w kolumnie C. Sprawdzamy, czy termin w komórce B2 jest mniejszy niż dzisiejsza data (funkcja DZISIAJ zwraca dzisiejszą datę z zegara komputera).

= JEŻELI (B2 
 
Co to są formuły zagnieżdżone IF?
 
Być może słyszałeś wcześniej o terminach zagnieżdżonych IF. Oznacza to, że możemy napisać funkcję IF w ramach innej funkcji IF. Możemy to zrobić, jeśli mamy więcej niż dwie akcje do wykonania.
 
Jedna funkcja IF jest w stanie wykonać dwie akcje ( value_if_true i value_if_false ). Ale jeśli osadzimy (lub zagnieździmy) inną funkcję IF w value_if_false sekcja, możemy wykonać kolejną akcję.
 
Weź ten przykład tam, gdzie chcemy wyświetlić słowo "Doskonały", jeśli wartość w komórce B2 jest większa lub równa 90, wyświetl "Dobrze", jeśli wartość jest większa lub równa 75, i wyświetl "Zła", jeśli cokolwiek innego.
 
= IF (B2> = 90, "Excellent", IF (B2> = 75, "Good", "Poor"))
 
 
Teraz rozszerzyliśmy naszą formułę poza to, co może zrobić tylko jedna funkcja IF. W razie potrzeby możesz zagnieździć więcej funkcji JEŻELI.
 
Zwróć uwagę na dwa nawiasy zamykające na końcu formuły-jeden dla każdej funkcji IF.
 
Istnieją alternatywne formuły, które mogą być czystsze niż to zagnieżdżone podejście IF. Bardzo przydatną alternatywą jest funkcja SWITCH w programie Excel.
 
Funkcje logiczne AND i OR
 
Funkcje AND i OR są używane, gdy chcesz wykonać więcej niż jedno porównanie w formule. Funkcja IF może obsłużyć tylko jeden warunek lub porównanie.
 
Weźmy przykład, w którym obniżamy wartość o 10% w zależności od kwoty, jaką klient wydaje i od ilu lat są klientem.
 
Na własną rękę funkcje AND i OR zwrócą wartość TRUE lub FALSE.
 
Funkcja AND zwraca TRUE tylko wtedy, gdy wszystkie warunki są spełnione i w przeciwnym razie zwraca FALSE. Funkcja OR zwraca TRUE, jeśli spełniony jest jeden lub wszystkie warunki, i zwraca FALSE tylko wtedy, gdy nie są spełnione żadne warunki.
 
Te funkcje mogą testować do 255 warunków, więc z pewnością nie ograniczają się tylko do dwóch warunków, jak tutaj pokazano.
 
Poniżej znajduje się struktura funkcji AND i OR. Są napisane tak samo. Wystarczy zastąpić nazwę AND dla OR. To tylko ich logika jest inna.
 
= AND (logiczny1, [logiczny2] ...)
 
Zobaczmy przykład, że obaj oceniają dwa warunki.
 
AND Przykład funkcji
 
Funkcja AND jest używana poniżej, aby sprawdzić, czy klient wyda co najmniej 3 000 GBP i był klientem przez co najmniej trzy lata.
 
= ORAZ (B2> = 3000, C2> = 3)
 
 
Możesz zobaczyć, że zwraca FALSE dla Matta i Terry'ego, ponieważ chociaż oba spełniają jedno z kryteriów, muszą spełniać oba z funkcją AND.
 
LUB Przykład funkcji
 
Funkcja OR jest używana poniżej, aby sprawdzić, czy klient wyda co najmniej 3 000 GBP lub był klientem przez co najmniej trzy lata.
 
= OR (B2> = 3000, C2> = 3)
 
 
W tym przykładzie formuła zwraca TRUE dla Matta i Terry'ego. Tylko Julie i Gillian zawodzą w obu warunkach i zwracają wartość FALSE.
 
Używanie AND i OR z funkcją IF
 
Ponieważ funkcje AND i OR zwracają wartość TRUE lub FALSE, gdy są używane samodzielnie, rzadko używa się ich samodzielnie.
 
Zamiast tego zazwyczaj używasz ich z funkcją IF lub w ramach funkcji programu Excel, takiej jak formatowanie warunkowe lub sprawdzanie poprawności danych, aby wykonać pewne działanie retrospektywne, jeśli formuła ma wartość TRUE.
 
W poniższym wzorze funkcja AND jest zagnieżdżona w teście logicznym funkcji IF. Jeśli funkcja AND zwraca TRUE, wówczas 10% jest zdyskontowane od kwoty w kolumnie B; w przeciwnym razie nie udzielono rabatu, a wartość w kolumnie B powtórzono w kolumnie D.
 
= JEŻELI (AND (B2> = 3000, C2> = 3), B2 * 90%, B2)
 
 
Funkcja XOR
 
Oprócz funkcji OR istnieje również wyjątkowa funkcja OR. Nazywa się to funkcją XOR. Funkcja XOR została wprowadzona w wersji Excel 2013.
 
Ta funkcja może wymagać trochę wysiłku, aby ją zrozumieć, dlatego pokazano przykład praktyczny.
 
Struktura funkcji XOR jest taka sama jak funkcja OR.
 
= XOR (logiczny1, [logiczny2] ...)
 
Przy ocenie tylko dwóch warunków funkcja XOR zwraca:
 
 
TRUE, jeśli którykolwiek z warunków zostanie oceniony jako PRAWDA.
 
FALSE, jeśli oba warunki są TRUE lub żaden z warunków nie jest PRAWDZIWY.
 
 
Różni się to od funkcji OR, ponieważ zwróci ona wartość PRAWDA, jeśli oba warunki są PRAWDZIWE.
 
Ta funkcja staje się nieco bardziej zagmatwana, gdy dodawane są kolejne warunki. Następnie funkcja XOR zwraca:
 
 
PRAWDA, jeśli dziwny liczba warunków zwraca TRUE.
 
FALSE, jeśli parzysty liczba warunków daje PRAWDA lub, jeśli wszystko warunki są FAŁSZ.
 
 
Spójrzmy na prosty przykład funkcji XOR.
 
W tym przykładzie sprzedaż jest podzielona na dwie połowy roku. Jeśli sprzedawca sprzedaje 3000 funtów lub więcej w obu połówkach, wówczas otrzymują oni złoty standard. Osiąga się to za pomocą funkcji AND z IF jak wcześniej w artykule.
 
Ale jeśli sprzedadzą 3000 funtów lub więcej w jednej połowie, to chcemy przypisać im status Silver. Jeśli nie sprzedadzą 3000 funtów lub więcej w obu przypadkach, to nic.
 
Funkcja XOR jest idealna dla tej logiki. Poniższa formuła jest wprowadzana do kolumny E i pokazuje funkcję XOR z IF, aby wyświetlać "Tak" lub "Nie" tylko wtedy, gdy spełniony jest którykolwiek warunek.
 
= JEŻELI (XOR (B2> = 3000, C2> = 3000), "Tak", "Nie")
 
 
Funkcja NOT
 
Końcową funkcją logiczną do omówienia w tym artykule jest funkcja NOT i pozostawiliśmy najprostszą na końcu. Chociaż czasami może być trudno zobaczyć na początku funkcje "rzeczywistego świata" tej funkcji.
 
Funkcja NOT odwraca wartość jej argumentu. Więc jeśli wartość logiczna jest PRAWDA, to zwraca FALSE. A jeśli wartość logiczna jest FALSE, zwróci TRUE.
 
Będzie to łatwiej wyjaśnić na kilku przykładach.
 
Struktura funkcji NOT jest;
 
= NOT (logiczny)
 
NIE Funkcja Przykład 1
 
W tym przykładzie wyobraź sobie, że mamy główną siedzibę w Londynie, a następnie wiele innych regionalnych witryn. Chcemy wyświetlić słowo "Tak", jeśli strona jest czymkolwiek poza Londynem i "Nie", jeśli jest to Londyn.
 
Funkcja NOT została zagnieżdżona w teście logicznym funkcji IF poniżej w celu odwrócenia wyniku TRUE.
 
= JEŻELI (NIE (B2 = "Londyn"), "Tak", "Nie")
 
 
Można to również osiągnąć za pomocą operatora logicznego NOT. Poniżej znajduje się przykład.
 
= JEŻELI (B2 "Londyn", "Tak", "Nie")
 
NIE Funkcja Przykład 2
 
Funkcja NOT jest przydatna podczas pracy z funkcjami informacyjnymi w Excelu. To jest grupa funkcji w Excelu, które sprawdzają coś i zwracają PRAWDA, jeśli kontrola jest sukcesem, i FALSE, jeśli nie jest.
 
Na przykład funkcja ISTEXT sprawdzi, czy komórka zawiera tekst i zwraca PRAWDA, jeśli tak jest, i FALSE, jeśli nie. Funkcja NOT jest pomocna, ponieważ może odwrócić wynik tych funkcji.
 
W poniższym przykładzie chcemy zapłacić sprzedawcy 5% kwoty, którą sprzedają. Ale jeśli niczego nie upsell, słowo "Brak" znajduje się w komórce, a to spowoduje błąd w formule.
 
Funkcja ISTEXT służy do sprawdzania obecności tekstu. To zwraca TRUE, jeśli jest tekst, więc funkcja NOT odwraca to do FALSE. I IF wykonuje swoje obliczenia.
 
= JEŻELI (NIE (ISTEKT (B2)), B2 * 5%, 0)
 
 
Opanowanie funkcji logicznych da ci dużą przewagę jako użytkownik Excela. Możliwość testowania i porównywania wartości w komórkach oraz wykonywania różnych akcji na podstawie tych wyników jest bardzo przydatna.
 
 
W tym artykule omówiono najlepsze obecnie używane funkcje logiczne. W ostatnich wersjach programu Excel wprowadzono więcej funkcji dodanych do tej biblioteki, takich jak funkcja XOR wspomniana w tym artykule. Regularne zapoznawanie się z nowymi dodatkami pozwoli Ci wyprzedzić tłum.