Przejdź do głównej zawartości

Jak używać funkcji IF z AND, OR i NOT w programie Excel?

Funkcja JEŻELI w Excelu jest świadectwem mocy i wszechstronności operacji logicznych w przetwarzaniu danych. Istotą funkcji JEŻELI jest jej zdolność do oceny warunków i zwrócenia określonych wyników na podstawie tych ocen. Działa w oparciu o podstawową logikę:

=IF(condition, value_if_true, value_if_false)

W połączeniu z operatorami logicznymi, takimi jak AND, OR i NOT, możliwości funkcji JEŻELI znacznie się rozszerzają. Siła tej kombinacji polega na możliwości jednoczesnego przetwarzania wielu warunków, zapewniając wyniki, które można dostosować do różnorodnych i złożonych scenariuszy. W tym samouczku dowiemy się, jak skutecznie wykorzystać te zaawansowane funkcje programu Excel, aby odblokować nowe wymiary analizy danych i usprawnić proces podejmowania decyzji. Zagłębmy się w szczegóły i odkryjmy praktyczne zastosowania tych niesamowitych funkcji programu Excel!

Zagnieżdżona formuła JEŻELI ORAZ LUB NIE

JEŻELI ORAZ formuła

Aby ocenić wiele warunków i dostarczyć konkretny wynik gdy wszystkie warunki są spełnione (TRUE)i inny wynik, gdy nie jest spełniony żaden warunek (FAŁSZ), możesz włączyć funkcję AND do testu logicznego instrukcji JEŻELI. Struktura tego jest następująca:

=IF(AND(condition1, condition2, …), value_if_all_true, value_if_any_false)

Wyobraź sobie na przykład, że jesteś nauczycielem analizującym oceny uczniów. Chcesz ustalić, czy uczeń zdał egzamin, na podstawie dwóch kryteriów: wynik powyżej 70 ROLNICZE frekwencja ponad 80%.

  1. Zacznij od sprawdzenia danych pierwszego ucznia, jego wyniku w komórce B2 i frekwencji w komórce C2. W przypadku tego ucznia zastosuj poniższą formułę w D2: 
    =IF(AND(B2>70, C2>80%), "Pass", "Fail")
    Wskazówka: Wzór ten sprawdza, czy wynik w B2 jest powyżej 70, a frekwencja w C2 przekracza 80%. Jeśli oba warunki są spełnione, zwraca „Pass”; w przeciwnym razie zwraca „Niepowodzenie”.
  2. Przeciągnij formułę w dół kolumny, aby ocenić wyniki i frekwencję każdego ucznia.

    JEŻELI ORAZ formuła

JEŚLI LUB Formuła

Aby ocenić wiele warunków i zwrócić określony wynik gdy spełniony jest którykolwiek z warunków (TRUE), a inny wynik, gdy żaden z warunków nie jest spełniony (FAŁSZ), funkcja LUB może zostać użyta w teście logicznym instrukcji JEŻELI. Formuła ma następującą strukturę:

=IF(OR(condition1, condition2, …), value_if_any_true, value_if_all_false)

Na przykład w kontekście edukacyjnym rozważ bardziej elastyczne kryterium zaliczenia ucznia. W tym przypadku uznaje się, że student zdał egzamin, jeśli je zaliczy albo uzyskasz wynik powyżej 90 OR mieć frekwencję wyższą niż 95%.

  1. Rozpocznij od oceny wyników pierwszego ucznia, wpisując jego wynik w komórce B2 i frekwencję w komórce C2. Zastosuj formułę w sąsiedniej komórce, np. D2, aby ocenić: 
    =IF(OR(B2>90, C2>95%), "Pass", "Fail")
    Wskazówka: Wzór ten pozwala ocenić, czy uczeń uzyskał wynik powyżej 90 na poziomie B2 lub jego frekwencja na poziomie C95 przekraczała 2%. Jeśli którykolwiek z warunków jest spełniony, zwraca „Pass”; jeśli nie, „Niepowodzenie”.
  2. Skopiuj tę formułę w dół kolumny, aby zastosować ją do każdego ucznia na liście, umożliwiając szybką ocenę, czy każdy uczeń kwalifikuje się do zaliczenia na podstawie tych kryteriów.

    Formuła JEŚLI LUB

JEŚLI NIE Formuła

Aby ocenić warunek i zwrócić określony wynik jeśli warunek NIE jest spełniony (FAŁSZ)i inny wynik, jeśli warunek jest spełniony (TRUE), rozwiązaniem jest funkcja NOT w instrukcji JEŻELI. Struktura tej formuły jest następująca:

=IF(NOT(condition), value_if_false, value_if_true)

Jako praktyczny przykład rozważmy scenariusz miejsca pracy, w którym premie pracowników są ustalane na podstawie ich frekwencji. Pracownicy mają prawo do premii, jeśli tak NIE były nieobecne dłużej niż 3 dni.

  1. Aby ocenić to dla pierwszego pracownika, którego dni nieobecności znajdują się w komórce B2, użyj wzoru: 
    =IF(NOT(B2>3), "Eligible", "Not Eligible")
    Wskazówka: Formuła ta sprawdza liczbę dni nieobecności w B2. Jeśli NIE jest więcej niż 3, zwraca „Eligible”; w przeciwnym razie „Nie kwalifikuje się”.
  2. Skopiuj tę formułę w dół kolumny, aby zastosować ją do każdego pracownika.

    JEŚLI NIE formuła

Zaawansowane scenariusze z funkcjami JEŻELI i logicznymi

W tej sekcji omówimy zawiłe wykorzystanie funkcji JEŻELI programu Excel z operatorami logicznymi, takimi jak AND, OR i NOT. W tej sekcji omówiono wszystko, od oceny uwzględniającej wielkość liter po zagnieżdżone instrukcje IF, prezentując wszechstronność programu Excel w złożonej analizie danych.

Jeśli Twój warunek jest spełniony, wykonaj obliczenia

Oprócz zapewniania predefiniowanych wyników funkcja JEŻELI w programie Excel w połączeniu z operatorami logicznymi, takimi jak AND, OR i NOT, umożliwia wykonywanie różnych obliczeń w zależności od tego, czy ustawione warunki są prawdziwe, czy fałszywe. Tutaj użyjemy kombinacji JEŻELI ORAZ jako przykładu, aby zaprezentować tę funkcjonalność.

Wyobraź sobie, że zarządzasz zespołem sprzedaży i chcesz obliczyć premie. Ty decydujesz, że jesteś pracownikiem otrzymuje 10% premii od sprzedaży Jeśli oni sprzedaż przekracza 100 dolarów ROLNICZE pracowałeś więcej niż 30 godzin tygodniowo.

  1. Aby dokonać wstępnej oceny, spójrz na dane Alicji z jej sprzedażą w komórce B2 i przepracowanymi godzinami w komórce C2. Zastosuj tę formułę w D2: 
    =IF(AND(B2>100, C2>30), B2*0.1, 0)
    Wskazówka: Ta formuła oblicza premię 10% od sprzedaży Alicji, jeśli jej sprzedaż przekracza 100 USD, a jej przepracowane godziny przekraczają 30. Jeśli spełnione są oba warunki, oblicza premię; w przeciwnym razie zwraca 0.
  2. Rozszerz tę formułę na resztę zespołu, kopiując ją w dół kolumny. Dzięki takiemu podejściu premia każdego pracownika jest obliczana w oparciu o te same kryteria.

    JEŚLI Następnie oblicz

Uwaga: W tej sekcji skupimy się na wykorzystaniu funkcji JEŻELI z AND do obliczeń opartych na określonych warunkach. Koncepcję tę można również rozszerzyć o LUB i NIE, a także zagnieżdżone funkcje logiczne, umożliwiające różnorodne obliczenia warunkowe w Excelu.

Instrukcje AND, OR i NOT uwzględniają wielkość liter

Chociaż w programie Excel funkcje logiczne, takie jak AND, OR i NOT, zazwyczaj nie uwzględniają wielkości liter, istnieją scenariusze, w których rozróżnianie wielkości liter w danych tekstowych ma kluczowe znaczenie. Integrując DOKŁADNA funkcja za pomocą tych operatorów logicznych można skutecznie obsługiwać takie warunki, w których rozróżniana jest wielkość liter. W tej sekcji zademonstrujemy użycie funkcji JEŻELI i LUB na przykładzie podejścia uwzględniającego wielkość liter.

Wyobraź sobie scenariusz sprzedaży detalicznej, w którym produkt kwalifikuje się do promocji, jeśli również taki jest sprzedaż przekracza 100 dolarów OR jego kod dokładnie pasuje do „ABC” przy sprawdzaniu rozróżniania wielkości liter.

  1. W przypadku pierwszego produktu wymienionego w wierszu 2, z jego sprzedażą w komórce B2 i kodem produktu w komórce C2, użyj tej formuły w D2: 
    =IF(OR(B2>100, EXACT(C2,"ABC")), "Promotion Eligible", "Not Eligible")
    Wskazówka: Ta formuła ocenia, czy wartość sprzedaży w B2 przekracza 100 USD lub czy kod produktu w C2 to dokładnie „ABC”. Spełnienie któregokolwiek z tych warunków powoduje, że produkt kwalifikuje się do promocji; brak obu powoduje, że nie kwalifikuje się.
  2. Powtórz tę formułę w całej kolumnie dla wszystkich produktów, aby jednolicie ocenić ich kwalifikowalność do promocji na podstawie kryteriów sprzedaży i kodu produktu uwzględniającego wielkość liter.

    Formuła JEŻELI LUB uwzględnia wielkość liter

Uwaga: W tej sekcji zilustrowaliśmy użycie funkcji JEŻELI i LUB z funkcją DOKŁADNE w ocenie uwzględniającej wielkość liter. W podobny sposób możesz zastosować funkcję DOKŁADNY w formułach JEŻELI w połączeniu z AND, OR, NOT lub zagnieżdżonymi funkcjami logicznymi, aby spełnić różnorodne wymagania programu Excel dotyczące rozróżniania wielkości liter.

Integracja JEŻELI z zagnieżdżonymi instrukcjami AND, OR, NOT

Funkcja JEŻELI programu Excel, zagnieżdżona z operatorami AND, OR i NOT, zapewnia usprawnione podejście do obsługi bardziej warstwowych warunków. W tej sekcji przedstawiono przykład pokazujący zastosowanie tych zagnieżdżonych funkcji w handlu detalicznym.

Załóżmy, że nadzorujesz zespół odpowiedzialny za różne kategorie produktów i chcesz ustalić, czy kwalifikują się oni do premii. Pracownikowi przysługuje premia, jeśli: osiągnąć sprzedaż powyżej 100 USD, ORAZ albo pracować więcej niż 30 godzin tygodniowo OR NIE są w dziale elektroniki.

  1. Najpierw oceń wyniki Anny, jej sprzedaż w komórce B2, godziny przepracowane w komórce C2 i dział w komórce D2. Formuła w E2 wyglądałaby następująco: 
    =IF(AND(B2>100, OR(C2>30, NOT(D2="Electronics"))), "Eligible", "Not Eligible")
    Wskazówka: Ta formuła sprawdza, czy Anne ma sprzedaż przekraczającą 100 USD i czy pracuje dłużej niż 30 godzin, czy nie pracuje w dziale elektroniki. Jeśli spełnia te kryteria, jest uważana za „Kwalifikującą się”; jeśli nie, „Nie kwalifikuje się”.
  2. Skopiuj tę formułę w dół kolumny dla każdego pracownika, aby jednolicie ocenić uprawnienia do premii, biorąc pod uwagę jego sprzedaż, przepracowane godziny i dział.

    Zagnieżdżona formuła JEŻELI ORAZ LUB NIE

Zagnieżdżone funkcje JEŻELI z AND, OR, NOT

Gdy analiza danych obejmuje wiele kontroli warunkowych, zagnieżdżone funkcje JEŻELI w programie Excel stanowią potężne rozwiązanie. Metoda ta polega na konstruowaniu oddzielnych instrukcji JEŻELI dla różnych warunków, w tym logiki AND, OR i NOT, a następnie integrowaniu ich w jedną uproszczoną formułę.

Rozważ miejsce pracy, w którym wydajność pracownika jest oceniana jako „Doskonała”, „Dobra” lub „Zadowalająca” oparte na sprzedaż, przepracowane godziny i przestrzeganie zasad:

  • „Doskonały” przy sprzedaży powyżej 150 USD ORAZ przepracowanych ponad 35 godzin.
  • W przeciwnym razie „Dobra” w przypadku sprzedaży powyżej 100 USD LUB naruszenia zasad NIE więcej niż 1.
  • „Dostateczny”, jeśli żaden z tych warunków nie jest spełniony.

Aby ocenić wydajność każdego pracownika zgodnie z powyższymi warunkami, wykonaj następujące czynności:

  1. Rozpocznij od oceny Anny, której sprzedaż znajduje się w komórce B2, przepracowane godziny w komórce C2, a naruszenia zasad w komórce D2. Zagnieżdżona formuła JEŻELI w E2 to: 
    =IF(AND(B2>150, C2>35), "Excellent", IF(OR(B2>100, NOT(D2>1)), "Good", "Fair"))
    Wskazówka: Ta formuła sprawdza najpierw, czy sprzedaż i godziny pracy Ani spełniają kryteria „Doskonałe”. Jeśli nie, ocenia, czy kwalifikuje się do kategorii „Dobra”. Jeśli żaden z warunków nie jest spełniony, jest klasyfikowana jako „Dobra”.
  2. Rozszerz tę zagnieżdżoną formułę JEŻELI na każdego pracownika, aby konsekwentnie oceniać jego wyniki według wielu kryteriów.

    Zagnieżdżone funkcje JEŻELI

Używanie JEŻELI z ORAZ LUB NIE: Często zadawane pytania

W tej sekcji omówiono często zadawane pytania dotyczące używania funkcji JEŻELI z AND, OR i NOT w programie Microsoft Excel.

Ile warunków mogą obsługiwać funkcje AND, OR i NOT?
  • Funkcje AND i OR mogą obsługiwać do 255 indywidualnych warunków. Zaleca się jednak użycie tylko kilku, aby uniknąć zbyt skomplikowanych formuł, które są trudne w utrzymaniu.
  • Funkcja NIE przyjmuje tylko jeden warunek.
Czy w tych funkcjach mogę używać operatorów takich jak , =?

Oczywiście w funkcjach AND, OR i NOT programu Excel można używać operatorów takich jak mniej niż (), równe (=), większe lub równe (>=) i więcej w celu ustalenia warunków.

Dlaczego w tych funkcjach pojawia się błąd #VALUE?

Błąd #WARTOŚĆ w funkcjach AND, OR i NOT programu Excel często pojawia się, jeśli formuła nie spełnia żadnego określonego warunku lub jeśli występuje problem ze strukturą formuły. Wskazuje, że program Excel nie jest w stanie poprawnie zinterpretować danych wejściowych lub warunków formuły.

Powyżej znajduje się cała istotna treść związana z używaniem funkcji JEŻELI z funkcjami AND, OR i NOT w programie Excel. Mam nadzieję, że tutorial okaże się pomocny. Jeśli chcesz poznać więcej porad i wskazówek dotyczących programu Excel, kliknij tutaj aby uzyskać dostęp do naszej obszernej kolekcji ponad tysięcy samouczków.