IF Formuła Excel - Instrukcje If Then

Pobierz przykładowy skoroszyt

Pobierz przykładowy skoroszyt

Ten samouczek pokazuje, jak używać Funkcja JEŻELI Excel w programie Excel, aby utworzyć instrukcje If Then.

JEŚLI Przegląd funkcji

Funkcja IF Sprawdza, czy warunek jest spełniony. Jeśli PRAWDA zrób jedną rzecz, jeśli FAŁSZ zrób inną.

Aby użyć funkcji arkusza kalkulacyjnego IF Excel, zaznacz komórkę i wpisz:

(Zwróć uwagę, jak pojawiają się dane wejściowe formuły)

Funkcja IF Składnia i wejścia:

1	=JEŻELI(test_logiczny;wartość_jeśli_prawda;wartość_jeśli_fałsz)

test logiczny - Wyrażenie logiczne. Przykład: A1 > 4.

wartość_jeśli_prawda - Wartość lub Obliczenie do wykonania, jeśli wyrażenie logiczne ma wartość PRAWDA.

value_if_false - Wartość lub Obliczenie do wykonania, jeśli wyrażenie logiczne ma wartość FAŁSZ.

JEŻELI jest funkcją „warunkową”. Oznacza to, że definiujesz test logiczny, który zwróci jedną wartość, jeśli wynik testu jest prawdziwy, a inną wartość, jeśli jest fałszywy

Jak korzystać z funkcji JEŻELI?

Oto bardzo prosty przykład, więc możesz zobaczyć, o co mi chodzi. Spróbuj wpisać następujące polecenie w programie Excel:

1	=JEŻELI( 2 + 2 = 4,"To prawda", "To fałsz!")

Ponieważ 2 + 2 w rzeczywistości równa się 4, Excel zwróci „To prawda!”. Jeśli użyliśmy tego:

1	=JEŻELI( 2 + 2 = 5,"To prawda", "To fałsz!")

Teraz Excel zwróci „To fałsz!”, ponieważ 2 + 2 nie równa się 5.

Oto, jak możesz użyć IF w arkuszu kalkulacyjnym.

1	=JEŻELI(C4-D4>0,C4-D4,0)

Prowadzisz bar sportowy i ustalasz indywidualne limity zakładek dla różnych klientów. Skonfigurowałeś ten arkusz kalkulacyjny, aby sprawdzić, czy każdy klient przekroczył swój limit, w którym to przypadku odetniesz go, dopóki nie zapłaci swojej karty.

Sprawdzasz, czy C4-D4 (ich aktualna kwota karty minus ich limit) jest większe niż 0. To jest twój test logiczny. Jeśli to prawda, IF zwraca „Tak” - należy je odciąć. Jeśli to nieprawda, IF zwraca „Nie” – pozwalasz im dalej pić.

Co JEŚLI może zwrócić?

Powyżej zwróciliśmy ciąg tekstowy „Tak” lub „Nie”. Ale możesz też zwracać liczby, a nawet inne formuły.

Załóżmy, że niektórzy z Twoich klientów mają duże karty. Aby temu zapobiec, zaczniesz naliczać odsetki klientom, którzy przekroczą swój limit.

Możesz do tego użyć IF:

1	=JEŻELI(C4>D4,C4*0,03,0)

Jeśli zakładka jest wyższa niż limit, zwróć zakładkę pomnożoną przez 0,03, co daje 3% zakładki. W przeciwnym razie zwróć 0: nie przekraczają swojej karty, więc nie naliczysz odsetek.

Używanie JEŻELI z AND

Możesz połączyć JEŻELI z funkcją I Excela <>. Używasz tego w teście logicznym, który umożliwia określenie dwóch lub więcej warunków do przetestowania. Excel zwróci TRUE tylko wtedy, gdy WSZYSTKIE testy są prawdziwe.

Więc wprowadziłeś swoje oprocentowanie. Ale niektórzy z twoich stałych bywalców narzekają. W przeszłości zawsze płacili rachunki, dlaczego teraz się z nimi rozprawiasz? Wymyślasz rozwiązanie: nie naliczasz odsetek niektórym zaufanym klientom.

Tworzysz nową kolumnę w arkuszu kalkulacyjnym, aby zidentyfikować zaufanych klientów, i aktualizujesz wyciąg IF za pomocą funkcji AND:

1	=JEŻELI(ORAZ(C4>D4, F4="Nie");C4*0,03,0)

Przyjrzyjmy się części AND osobno:

1	AND(C4>D4, F4="Nie")

Zwróć uwagę na dwa warunki:

• C4>D4: sprawdzanie, czy przekraczają limit tabulacji, jak poprzednio
• F4=”Nie”: to jest nowy bit, sprawdzający, czy nie jest zaufanym klientem

Więc teraz zwracamy stopę procentową tylko wtedy, gdy klient jest na swojej karcie, ORAZ mamy „Nie” w kolumnie zaufanego klienta. Twoi stali klienci znów są szczęśliwi.

Przeczytaj więcej na stronie głównej programu Excel i funkcji <>.

Używanie IF z OR

OR to kolejna logiczna funkcja programu Excel. Podobnie jak AND, umożliwia zdefiniowanie więcej niż jednego warunku. Ale w przeciwieństwie do AND, zwróci TRUE, jeśli JAKIKOLWIEK ze zdefiniowanych testów jest prawdziwy.

Może to nie jedyny powód, dla którego odciąłeś się od klientów. Może dajesz niektórym ludziom tymczasowy zakaz z innych powodów, być może hazard w lokalu.

Dodajesz więc nową kolumnę, aby zidentyfikować zbanowanych klientów i aktualizujesz „Cięcie?” kolumna z testem OR:

1	=JEŻELI(LUB(C4>D4,E4="Tak");"Tak","Nie")

Patrząc tylko na część OR:

1	LUB(C4>D4,E4="Tak")

Istnieją dwa warunki:

• C4>D4: sprawdzanie, czy przekraczają limit zakładek
• F4=”Tak”: nowa część, sprawdzanie, czy są aktualnie zbanowane

To zostanie ocenione jako prawda, jeśli przekroczą swoją zakładkę lub jeśli w kolumnie E jest „Tak”. Jak widać, Harry jest teraz odcięty, nawet jeśli nie przekroczył limitu zakładek.

Przeczytaj więcej na stronie głównej funkcji Excel LUB <>.

Używanie IF z XOR

XOR to kolejna funkcja logiczna, która zwraca „Exclusive Or”. Jest to nieco mniej intuicyjne niż poprzednie, które omawialiśmy.

W prostych przypadkach definiujesz dwa warunki, a XOR zwróci:

• TRUE, jeśli którykolwiek argument jest prawdziwy (tak samo jak normalny OR)
• FALSE, jeśli oba argumenty są prawdziwe
• FALSE jeśli oba argumenty są fałszywe

Przykład może to wyjaśnić. Wyobraź sobie, że chcesz zacząć dawać comiesięczne premie swoim pracownikom:

• Jeśli sprzedają ponad 800 $ w jedzeniu lub ponad 800 $ w napojach, dasz im połowę premii
• Jeśli sprzedają w obu przypadkach powyżej 800 $, dasz im pełny bonus
• Jeśli sprzedają poniżej 800 $ w obu przypadkach, nie otrzymają żadnej premii.

Wiesz już, jak obliczyć, czy otrzymają pełny bonus. Po prostu użyjesz JEŻELI z AND, jak opisano wcześniej.

1	=JEŻELI(ORAZ(C4>800;D4>800);"Tak";"Nie")

Ale jak byś obliczyć, kto otrzyma połowę premii? Tutaj wkracza XOR:

1	=JEŻELI(XOR(C4>=800;D4>=800);"Tak","Nie")

Jak widać, sprzedaż napojów Woody'ego wyniosła ponad 800 USD, ale nie sprzedaż żywności. Więc dostaje połowę premii. W przypadku trenera jest odwrotnie. Diane i Carla sprzedały ponad 800 dolarów za oba, więc nie dostają połowy premii (oba argumenty są PRAWDĄ), a Rebecca zarobiła poniżej progu dla obu (oba argumenty FAŁSZ), więc formuła ponownie zwraca „Nie”.

Przeczytaj więcej na stronie głównej funkcji Excel XOR <>.

Używanie JEŻELI z NIE

NOT to kolejna logiczna funkcja Excela, która jest bardzo często używana z IF.

NOT odwraca wynik testu logicznego. Innymi słowy, sprawdza, czy warunek nie został spełniony.

Możesz go użyć z IF w ten sposób:

1	=JEŻELI(ORAZ(C3>=1985;NIE(D3="Steven Spielberg")),"Obserwuj", "Nie oglądaj")

Tutaj mamy tabelę z danymi dotyczącymi niektórych filmów z lat 80. Chcemy zidentyfikować filmy wydane w 1985 roku lub później, które nie zostały wyreżyserowane przez Stevena Spielberga.

Ponieważ NOT jest zagnieżdżony w funkcji AND, program Excel najpierw to oceni. Następnie użyje wyniku jako części AND.

Przeczytaj więcej na stronie głównej funkcji Excel NIE <>.

Zagnieżdżone instrukcje IF

Możesz również zwrócić wyciąg IF w swoim wyciągu IF. Umożliwia to wykonywanie bardziej złożonych obliczeń.

Wróćmy do stołu naszych klientów. Wyobraź sobie, że chcesz klasyfikować klientów na podstawie poziomu ich zadłużenia do Ciebie:

• 0 USD: Brak
• Do 500 USD: Niski
• 500 do 1000 USD: Średni
• Ponad 1000 USD: wysoki

Możesz to zrobić, „zagnieżdżając” instrukcje IF:

1	=JEŻELI(C4=0,"Brak",JEŻELI(C4<=500,"Niski",JEŻELI(C4<=1000,"Średni",JEŻELI(C4>1000;"Wysoki"))))

Łatwiej to zrozumieć, jeśli umieścisz instrukcje IF w osobnych wierszach (ALT + ENTER w systemie Windows, CTRL + COMMAND + ENTER na komputerach Mac):

12345

=JEŻELI(C4=0;"Brak",JEŻELI(C4<=500;"Niski",JEŻELI(C4<=1000,"Średni",JEŻELI(C4>1000,"Wysoki", "Nieznany"))))

JEŻELI C4 wynosi 0, zwracamy „Brak”. W przeciwnym razie przechodzimy do następnej instrukcji IF. JEŚLI C4 jest równe lub mniejsze niż 500, zwracamy „Niski”. W przeciwnym razie przechodzimy do następnej instrukcji IF… i tak dalej.

Upraszczanie złożonych instrukcji IF za pomocą kolumn pomocniczych

Jeśli masz wiele zagnieżdżonych instrukcji IF i dodajesz również funkcje logiczne, twoje formuły mogą być bardzo trudne do odczytania, przetestowania i aktualizacji.

Należy o tym pamiętać, jeśli inne osoby będą korzystać z arkusza kalkulacyjnego. To, co ma sens w twojej głowie, może nie być tak oczywiste dla innych.

Kolumny pomocnicze są świetnym sposobem na obejście tego problemu.

Jesteś analitykiem w dziale finansowym dużej korporacji. Zostałeś poproszony o utworzenie arkusza kalkulacyjnego, który sprawdza, czy każdy pracownik kwalifikuje się do emerytury firmowej.

Oto kryteria:

Więc jeśli masz mniej niż 55 lat, musisz mieć za sobą 30 lat pracy, aby się kwalifikować. Jeśli masz od 55 do 59 lat, potrzebujesz 15-letniej służby. I tak dalej, aż do wieku 65 lat, gdzie kwalifikujesz się bez względu na to, jak długo tam pracowałeś.

Możesz użyć pojedynczej, złożonej instrukcji IF, aby rozwiązać ten problem:

1	=JEŻELI(LUB(F4>=65,I(F4>=62,G4>=5),I(F4>=60,G4>=10),I(F4>=55,G4>=15),G4 >30), „Kwalifikuje się”, „Nie kwalifikuje się”)

Uff! Trochę trudno to sobie wyobrazić, prawda?

Lepszym rozwiązaniem może być użycie kolumn pomocniczych. Mamy tutaj pięć testów logicznych, odpowiadających każdemu wierszowi w tabeli kryteriów. Łatwiej to zobaczyć, jeśli dodamy podziały wierszy do formuły, o czym mówiliśmy wcześniej:

12345678

=JEŻELI(LUB(F4>=65,I(F4>=62;G4>=5),I(F4>=60;G4>=10),I(F4>=55;G4>=15),G4>30), „Kwalifikuje się”, „Nie kwalifikuje się”)

Możemy więc podzielić te pięć testów na osobne kolumny, a następnie po prostu sprawdzić, czy którykolwiek z nich jest prawdziwy:

Każda kolumna w tabeli od E do I zawiera każde z naszych kryteriów osobno. Następnie w J4 mamy następującą formułę:

1	=JEŻELI(LICZ.JEŻELI(E4:I4;PRAWDA);"Dopuszczalne";"Nieodpowiednie")

Tutaj mamy instrukcję JEŻELI, a test logiczny używa LICZ.JEŻELI <> do policzenia liczby komórek w obrębie E4:I4, które zawierają PRAWDA.

Jeśli LICZ.JEŻELI nie znajdzie wartości TRUE, zwróci 0, co JEŻELI interpretuje jako FAŁSZ, więc JEŻELI zwraca „Nie kwalifikuje się”.

Jeśli LICZ.JEŻELI znajdzie jakiekolwiek wartości TRUE, zwróci ich liczbę. JEŚLI interpretuje dowolną liczbę inną niż 0 jako PRAWDA, więc zwraca „Kwalifikowane”.

Podział testów logicznych w ten sposób sprawia, że ​​formuła jest łatwiejsza do odczytania, a jeśli coś jest z nią nie tak, znacznie łatwiej jest wykryć, gdzie jest błąd.

Używanie grupowania do ukrywania kolumn pomocniczych

Kolumny pomocnicze ułatwiają zarządzanie formułą, ale gdy już je założysz i wiesz, że działają poprawnie, często po prostu zajmują miejsce w arkuszu kalkulacyjnym bez dodawania żadnych przydatnych informacji.

Możesz ukryć kolumny, ale może to prowadzić do problemów, ponieważ ukryte kolumny są trudne do wykrycia, chyba że przyjrzysz się uważnie nagłówkom kolumn.

Lepszą opcją jest grupowanie.

Wybierz kolumny, które chcesz pogrupować, w naszym przypadku E:I. Następnie naciśnij ALT + SHIFT + STRZAŁKA W PRAWO w systemie Windows lub COMMAND + SHIFT + K na komputerze Mac. Możesz także przejść do zakładki "Dane" na wstążce i wybrać "Grupa" z sekcji "Konspekt".

Zobaczysz grupę wyświetlaną nad nagłówkami kolumn, w następujący sposób:

Następnie wystarczy nacisnąć przycisk „-”, aby ukryć kolumny:

Funkcja IFS

Zagnieżdżone instrukcje JEŻELI są bardzo przydatne, gdy trzeba wykonać bardziej złożone porównania logiczne i trzeba to zrobić w jednej komórce. Jednak mogą się one komplikować, gdy stają się dłuższe i mogą być trudne do odczytania i aktualizacji na ekranie.

W programach Excel 2022 i Excel 365 firma Microsoft wprowadziła inną funkcję, IFS, aby ułatwić zarządzanie tą funkcją. Powyższy przykład zagnieżdżonego IF można uzyskać za pomocą IFS w następujący sposób:

1234567

=JEŻELI(C4=0,"Brak",C4<=500,"Niski",C4<=1000,"Średni",C4>1000,"Wysoki",PRAWDA, „Nieznany”,)

Możesz o tym przeczytać na stronie głównej funkcji IFS Excel <>.

Używanie JEŻELI z formatowaniem warunkowym

Funkcja formatowania warunkowego programu Excel umożliwia formatowanie komórki na różne sposoby w zależności od jej zawartości. Ponieważ funkcja JEŻELI zwraca różne wartości na podstawie naszego testu logicznego, warto zastosować formatowanie warunkowe, aby te różne wartości były lepiej widoczne.

Wróćmy więc do wcześniejszej tabeli premii dla pracowników.

Zwracamy „Tak” lub „Nie” w zależności od tego, jaki bonus chcemy dać. To mówi nam, co musimy wiedzieć, ale informacje nie wyskakują z nas. Spróbujmy to naprawić.

Oto jak możesz to zrobić:

• Wybierz zakres komórek zawierający instrukcje IF. W naszym przypadku jest to E4:F8.
• Kliknij "Formatowanie warunkowe" w sekcji "Style" na karcie "Strona główna" na wstążce.
• Kliknij „Wyróżnij reguły komórek”, a następnie „Równe”.
• Wpisz "Tak" (lub dowolną wartość zwrotu, której potrzebujesz) w pierwszym polu, a następnie wybierz żądane formatowanie z drugiego pola. (Wybiorę do tego zielony).
• Powtórz dla wszystkich zwracanych wartości (ustawię też wartości „Nie” na czerwony)

Oto wynik:

Używanie IF w formułach tablicowych

Tablica to zakres wartości, aw programie Excel tablice są reprezentowane jako wartości oddzielone przecinkami, ujęte w nawiasy klamrowe, takie jak:

1	{1,2,3,4,5}

Piękno tablic polega na tym, że umożliwiają one wykonanie obliczeń na każdej wartości w zakresie, a następnie zwrócenie wyniku. Na przykład funkcja SUMPRODUCT pobiera dwie tablice, mnoży je i sumuje wyniki.

Więc ta formuła:

1	=PROD.SUMA({1,2,3};{4,5,6})

… zwraca 32. Dlaczego? Przeanalizujmy to przez:

12345

1 * 4 = 42 * 5 = 103 * 6 = 184 + 10 + 18 = 32

Możemy wprowadzić do tego obrazu wyrażenie JEŻELI, tak aby każde z tych mnożeń miało miejsce tylko wtedy, gdy test logiczny zwróci prawdę.

Na przykład weź te dane:

https://www.automateexcel.com/excel/wp-content/uploads/2020/07/SUMPRODUCT-Example-Range.png"no"> 1 =PRODUKTSUMA(JEŻELI($C$2:$C$10=$G2,$D$2:$D$10*$E$2:$E$10))

Uwaga: W programie Excel 2022 i wcześniejszych musisz nacisnąć klawisze CTRL + SHIFT + ENTER, aby przekształcić to w formułę tablicową.

Skończyłoby się na czymś takim:

https://www.automateexcel.com/excel/wp-content/uploads/2020/07/SUMPRODUCTS-IF-Results-Table.png"no"> 1 $C$2:$C$10=$G2

W języku angielskim, jeśli nazwa w kolumnie C jest równa temu, co jest w G2 („Olivia”), POmnóż wartości w kolumnach D i E dla tego wiersza. W przeciwnym razie nie mnoż ich. Następnie zsumuj wszystkie wyniki.

Możesz dowiedzieć się więcej o tej formule na stronie głównej Formuły SUMPRODUCT IF <>.

JEŚLI w Arkuszach Google

Funkcja IF działa dokładnie tak samo w Arkuszach Google, jak w Excelu:

Dodatkowe uwagi

Użyj funkcji IF, aby sprawdzić, czy warunek jest PRAWDZIWY. Jeśli warunek jest PRAWDZIWY, zrób jedną rzecz. Jeśli to FAŁSZ, zrób inny. Warunek musi być wyrażeniem logicznym (np.: a1 >5), odwołaniem do komórki zawierającej PRAWDA lub FAŁSZ lub tablicą zawierającą wszystkie wartości logiczne.

Funkcja IF może testować tylko jeden warunek na raz. Możesz jednak „zagnieździć” inne funkcje logiczne w warunku JEŻELI, aby przetestować wiele warunków jednocześnie:

=jeśli(ORAZ(a1>0,a2>0);PRAWDA;FAŁSZ)
=jeżeli(LUB(a1>0,a2>0);PRAWDA;FAŁSZ)
=jeśli(XOR(a1>0,a2>0);PRAWDA;FAŁSZ)
LUB Test funkcji, jeśli jeden lub więcej warunki są spełnione.
Test funkcji AND, jeśli wszystko warunki są spełnione.
Test funkcji XOR, jeśli jeden i tylko jeden warunek są spełnione.

Możesz także „zagnieździć” funkcję IF w funkcji IF:

1	=if(a1<0,if(a2<0,"Oba","tylko 1"),"tylko jeden")

A teraz kilka konkretnych przykładów działania funkcji JEŻELI w praktyce:

1. Rozpocznij nową książkę pracy.

2. W komórce A1 wprowadź wartość 10 (i naciśnij Enter)

3. Następnie w komórce B1 wprowadź następującą formułę:

1	=JEŻELI(A1 > 5;"WIĘKSZY NIŻ 5";"MNIEJ NIŻ 5")

4. Ekran powinien teraz wyglądać tak:

5. Jeśli poprawnie wpisałeś formułę, w komórce B1 pojawi się komunikat „Większy niż 5”.

6. Formuła, którą wprowadziłeś w komórce B1 wykonuje test „A1>5”, tzn. sprawdza, czy wartość w komórce A1 jest większa niż 5. Obecnie wartość w komórce A1 wynosi 10 - więc warunek to PRAWDA i komunikat Pojawia się „WIĘKSZE NIŻ 5”

7. Jeśli teraz zmienimy wartość w komórce A1 na 2:

Wiadomość w komórce B2 ma teraz wartość „MNIEJ NIŻ 5”, ponieważ warunek jest FAŁSZ.

8. Możesz zmieniać wartość w komórce A1, a wiadomość w komórce B2 odpowiednio się dostosuje.

9. Oczywiście zdarzają się sytuacje, w których stan może dawać psotne wyniki:

• Co się stanie, jeśli w komórce A1 wpiszemy wartość 5?

• A jeśli zostawimy komórkę A1 pustą?

• A jeśli umieścimy jakiś tekst w komórce A1, np. frazę DOG

Więcej o funkcji Excel IF

Przyjrzymy się teraz bardziej szczegółowo funkcji JEŻELI. Może być używany do bardzo łatwej analizy dużych ilości danych.

Wyobraź sobie, że jesteś regionalnym kierownikiem sprzedaży i masz zespół sprzedaży. W prostym arkuszu kalkulacyjnym Excel można zapisać całkowitą sprzedaż, którą dokonuje każda osoba:

Załóżmy, że kryterium premii było to, że Sprzedaż dokonana przez tę osobę przekroczyła 40 000 GBP. Mógłbyś po prostu „przyjrzeć się” danym i stwierdzić, że tylko Anton, Newton i Monique osiągnęli cel.

Jest to dość łatwe, gdy masz tylko kilka imion. Jeśli jednak masz ich kilka, istnieje możliwość popełnienia błędu. Na szczęście dzięki funkcji IF Excela można to zrobić znacznie szybciej i bezpieczniej.

Skonfiguruj nowy skoroszyt i wpisz dane jak powyżej. Następnie w komórce D4 wpisz następującą formułę: –

1	=JEŻELI(C4>40000;"PŁATNA PREMIA";"BRAK PREMII")

abyś miał:

Zwróć uwagę, jak Excel pokazuje strukturę formuły JEŻELI - co jest przydatnym wspomnieniem.

Po wpisaniu formuły naciśnij ENTER, a zobaczysz jej ocenę dla pierwszego wiersza:

Formuła została oceniona dla Martina - ponieważ zarobił mniej niż 40 000 funtów, nie przysługuje mu żadna premia.

Następnie przeciągamy formuły w dół, klikając prawy dolny róg i przeciągając w dół możemy określić, czy każda osoba jest uprawniona do premii:

Widzimy, że Excel ustalił, którzy sprzedawcy mają prawo do premii.

Wróć do listy wszystkich funkcji w Excelu

Oświadczenia VBA IF

Możesz także użyć instrukcji If w VBA. Kliknij link, aby dowiedzieć się więcej, ale oto prosty przykład:

1234567

Podtest_IF ()Jeśli Zakres("a1").Wartość < 0 wtedyZakres("b1").Value = "Ujemna"Zakończ, jeśliZakończ, jeśli

Ten kod sprawdzi, czy wartość komórki jest ujemna. Jeśli tak, w następnej komórce zostanie napisane „ujemne”.