Jak korzystać z wielu instrukcji IF w programie Microsoft Excel

Instrukcja IF w programie Excel uruchamia test logiczny, który zwraca jedną wartość, jeśli warunek jest spełniony, i inną wartość, jeśli nie jest. Użycie pojedynczej instrukcji IF programu Excel może obsługiwać tylko podstawowe operacje, ale jeśli zagnieżdżysz wiele instrukcji IF, możesz wykonywać skomplikowane zadania. Jedynym minusem jest to, że musisz być ostrożny podczas konstruowania formuły, aby uniknąć błędów. Alternatywne funkcje programu Excel mogą wykonać tę samą pracę przy mniejszym ryzyku błędu. Poznaj podstawy zagnieżdżonych instrukcji JEŻELI w programie Excel oraz alternatywy, aby znaleźć najlepsze podejście do swoich potrzeb.

Funkcja JEŻELI w programie Excel zasadniczo mówi „jeśli to prawda, zwróć wartość x, ale jeśli nie, zwróć wartość y”. Na przykład, jeśli sprawdzasz, czy różne działy w Twojej firmie były poniżej lub powyżej budżetu, możesz porównać ich wydatki z budżetami za pomocą funkcji i zwrócić "Przekroczenie budżetu", jeśli dział wydał za dużo lub "W ramach budżetu", jeśli nie. Ogólnie rzecz biorąc, do tworzenia zestawienia używasz operatorów porównania, takich jak:

• = (równe)

• (Lepszy niż)

• = (większe lub równe)

• < (mniejszy niż)
• <= (mniejszy lub równy)
• <> (nie równe)

Zrozumienie składni instrukcji JEŻELI w programie Excel jest kluczowym krokiem w kierunku nauki prawidłowego korzystania z niej. Format funkcji to JEŻELI(test_logiczny; wartość_jeśli_prawda; [wartość_jeśli_fałsz]) gdzie nawiasy kwadratowe wskazują, że ten ostatni argument jest opcjonalny. Są one łatwe do zinterpretowania: test logiczny oznacza stwierdzenie, które chcesz przetestować, wartość_jeśli_prawda jest miejscem, w którym program Excel ma pokazać, czy test jest prawdziwy, oraz [wartość_jeśli_fałsz] jest opcjonalnym miejscem, które ma wyświetlać program Excel, jeśli test jest fałszywy.

To wystarczy, aby użyć formuły Excela dla zestawień if-then, co można wykazać na wcześniejszym przykładzie „Przekroczenie budżetu/w ramach budżetu”. Wyobraź sobie, że masz kwotę wydaną przez różne działy w kolumnie B i kwotę zabudżetowaną w kolumnie C, z wartościami zaczynającymi się w drugim wierszu. W komórce D2 należy wpisać =JEŻELI(B2>C2, "Przekroczenie budżetu", "W ramach budżetu") aby zwrócić wynik, którego szukasz (zauważ, że musisz użyć cudzysłowów, aby zwrócić tekst w środku). Pierwsza część mówi „jeśli wartość w B2 jest większa niż wartość w C2” (jeśli wydatki są większe niż budżet), druga część mówi „wtedy zwróć tekst Przekracza budżet a trzecia część mówi „jeśli nie, to wróć W budżecie."

Optymalne wykorzystanie narzędzia instrukcji JEŻELI programu Excel oznacza możliwość łączenia wielu instrukcji JEŻELI, gdy masz zadanie, które jest do tego odpowiednie. Możesz dołączyć drugie stwierdzenie IF do oryginalnego oświadczenia IF i możesz dołączyć do niego inne stwierdzenie IF i tak dalej. Jest to szczególnie przydatne, jeśli chcesz podzielić coś na jedną z kilku grup za pomocą jednej formuły. Na przykład możesz użyć zagnieżdżonych instrukcji JEŻELI, aby przekonwertować wyniki uzyskane przez uczniów na teście na ocenę.

Aby poprawnie używać wielu instrukcji JEŻELI, zastanów się nad logiką tego, o co prosisz program Excel. Zasadniczo, po warunku (pierwszym argumencie w funkcji), możesz dodać kolejną instrukcję IF do wartość_jeśli_prawda lub value_if_false spacje, aby poinformować program Excel, co ma dalej robić. Pomyśl o tym, jak skłonienie programu Excel do sprawdzenia komórki pod kątem drugiego kryterium, jeśli dana komórka nie spełnia spełnisz pierwsze kryterium (lub rzeczywiście, jeśli spełnia pierwsze) i możesz kontynuować ten proces przez długi czas czas. Innymi słowy, zamieniasz „jeśli to, to tamto” na „jeśli to oraz to, to tamto” lub „jeśli nie to, ale to, to tamto”.

Wyobraź sobie, że dajesz uczniom test, w którym wynik 85 procent lub wyższy to A, między 70 a 85 procent to B, od 55 do 70 procent to C, od 40 do 55 procent to D, a każda niższa wartość to a Ponieść porażkę. Wyniki znajdują się w kolumnie B, od rzędu 2 do rzędu 11. Możesz użyć starannie skonstruowanej funkcji Excel IF z wieloma warunkami, aby kategoryzować wyniki uczniów.

Pierwsza część jest łatwa (dla wyniku w B2 w tym przykładzie): =JEŻELI(B2>=85; "A") mówi programowi Excel, aby zwrócił ocenę A, jeśli uczeń uzyskał wynik 85 lub wyższy. Zagnieżdżanie umożliwia dodawanie innych ocen, wykorzystując trzeci argument (co zrobić, jeśli warunek jest fałszywy), aby dodać dodatkowe warunki: =JEŻELI(B2>=85, "A", JEŻELI(B2>=70, "B")), który mówi programowi Excel, aby zwrócił A w przypadku wyniku 85 lub wyższego, ale jeśli nie, zwróci B, jeśli wynik przekroczy 70. Kontynuuj w ten sposób, aby uzupełnić formułę, używając za każdym razem trzeciego argumentu dla nowej instrukcji JEŻELI: =JEŻELI(B2>=85, "A", JEŻELI(B2>=70, "B",JEŻELI(B2>=55, "C", JEŻELI(B2>=40, "D", "Niepowodzenie"))) ))

To wygląda na skomplikowane, więc rozbicie go powinno utrwalić pomysł. Pierwsze dwa argumenty mówią: „jeśli wynik na poziomie B2 wynosi 85 lub więcej, uczeń otrzymuje ocenę A”, a trzeci mówi: „w przeciwnym razie sprawdź następujący warunek”. ten drugie IF prowadzi do tego stwierdzenia: „(dla uczniów, którzy uzyskali mniej niż 85 punktów) jeśli wynik w B2 wynosi 70 lub wyższy, to uczeń otrzymuje B, a jeśli nie, sprawdź następujący warunek." Trzecia IF mówi: "(dla uczniów, którzy uzyskali mniej niż 70), jeśli wynik w B2 wynosi 55 lub wyższy, uczeń, który uzyskał ocenę C, a jeśli nie, sprawdź następujący warunek”, a czwarty mówi „(dla uczniów, którzy uzyskali mniej niż 55 punktów), jeśli wynik w B2 jest wyższy niż 40, uczeń otrzymuje ocenę D, jeśli nie, uczeń zawodzi”.

Jeśli poprawnie wprowadzisz formułę instrukcji IF, zwróci ona jedną z opcji podanych w programie Excel. Jeśli jednak popełnisz błąd, formuła może zwrócić a 0 lub #NAZWA? w komórce zawierającej formułę.

A 0 błąd oznacza, że ​​albo wartość_jeśli_prawda lub value_if_false argument jest pusty. Rozwiąż ten problem, upewniając się, że wartość jest wprowadzona w obu argumentach lub przynajmniej w wartość_jeśli_prawda argument. ten value_if_false argument może zostać całkowicie usunięty i zwróci FALSE, jeśli nie umieścisz drugiego przecinka po wartość_jeśli_prawda pole.

A #NAZWA? błąd informuje, że prawdopodobnie popełniłeś błąd ortograficzny lub wpisałeś coś w formule. Może to być trochę trudniejsze do naprawienia, ponieważ może to być dowolna część formuły, ale najlepszą radą jest ponowne sprawdzenie wszystkiego lub ponowne zapisanie formuły.

Poprzedni przykład zagnieżdżonej formuły funkcji JEŻELI jest dość długi, co jest jedną z głównych wad używania wielu instrukcji JEŻELI w programie Excel. Mogą stać się skomplikowane i łatwo o pomyłkę, ponieważ musisz uważnie przestrzegać logiki, aby upewnić się, że wszystko działa zgodnie z zamierzeniami. Microsoft zwraca uwagę, że niepoprawnie zagnieżdżona formuła może w większości przypadków zwracać poprawny wynik, ale w rzadkich przypadkach wyniki nieprawidłowe. Trudno je zauważyć.

Nieułożenie warunków we właściwej kolejności to częsty błąd. W przykładzie testów z oceną, jeśli zacząłeś z niższym wynikiem =JEŻELI(B2>=40, "D", …" i przystąpił do wyliczania pozostałych w porządku rosnącym, nawet 100 punktów dałoby D. Dzieje się tak, ponieważ pierwszy warunek jest sprawdzany jako pierwszy, więc jeśli zacząłeś od „jeśli komórka B2 ma 40 lub więcej, zwróć a D” zaklasyfikowałby każdy wynik 40 lub więcej jako D bez sprawdzania czegokolwiek innego, ponieważ spełnia pierwszy stan: schorzenie. To doskonały przykład, dlaczego musisz podążać za logiką swojej formuły.

Musisz także dodać odpowiednią liczbę nawiasów na końcu wielu instrukcji IF, aby poprawnie zamknąć formułę. Excel pomaga w tym, koordynując kolory i podkreślając nawiasy podczas pracy.

Z technicznego punktu widzenia można zagnieździć do 64 funkcji JEŻELI w jednej formule, ale firma Microsoft nie zaleca używania takiej funkcji z powyższych powodów. Sprawy szybko się komplikują i są szanse, że jeśli próbujesz zagnieździć więcej niż 10 instrukcji IF, prawdopodobnie istnieje już lepsze narzędzie do zadania zawartego w programie Excel.

Najwygodniejszą alternatywą dla funkcji IF jest funkcja IFS, która jest wersją funkcja, która wykonuje to samo zadanie, co zagnieżdżanie w jednej funkcji i może obsłużyć do 127 warunki. Funkcja IFS ma prostszą składnię: = WARUNKI(test_logiczny1, wartość_jeśli_prawda1, [test_logiczny2, wartość_jeśli_prawda2], [test_logiczny3, wartość_jeśli_prawda3]…) i tak dalej. Zasadniczo działa to jak zagnieżdżone instrukcje JEŻELI, z wyjątkiem tego, że kolejne instrukcje JEŻELI zajmują miejsce po „wartość, jeśli prawda” bez konieczności ponownego wywoływania funkcji.

Formuła w przykładzie oceny z testu może być znacznie prostsza za pomocą funkcji IFS. Zamiast:

=JEŻELI(B2>=85, "A", JEŻELI(B2>=70, "B",JEŻELI(B2>=55, "C", JEŻELI(B2>=40, "D", "Niepowodzenie"))) ))

po prostu napisz

=IFS(B2>=85, "A", B2>=70, "B", B2>=55, "C", B2>=40, "D", PRAWDA, "Niepowodzenie")

ten PRAWDA w miejscu przed ostatnim mówi „jeśli żadna z pozostałych wartości nie jest spełniona, zwróć następną wartość”.

Ta funkcja jest dostępna tylko w programie Excel 2019 lub Excel dla usługi Office 365.

Funkcja WYSZUKAJ.PIONOWO w programie Excel jest często prostszą alternatywą dla wielu instrukcji JEŻELI i jest dostępna w dowolnej wersji programu Excel od 2007 roku. Nie jest to idealne rozwiązanie we wszystkich sytuacjach, ponieważ zwraca wyniki dla dokładnego dopasowania lub dla najbliższy numer, więc nie można go używać do ocen, jak w poprzednim przykładzie. Jednak w wielu sytuacjach może zaoszczędzić wiele pracy na pisaniu formuły, chociaż musisz skonstruować tabelę z możliwymi wartościami w jednym kolumnę, np. produkty, które sprzedaje Twój sklep i co ma zwracać funkcja, np. cena produktu, w innej kolumnie, za przykład.

Składnia funkcji WYSZUKAJ.PIONOWO to = WYSZUKAJ.PIONOWO(wyszukaj_wartość; tablica_tabeli; nr_indeksu_kolumn; [przeszukaj_zakres]) a poznanie każdego z komponentów pokazuje, jak z niego korzystać. ten szukana_wartość argument jest wartością, którą chcesz sprawdzić, która musi być komórką w tej samej kolumnie co początek tabeli, z którą chcesz ją sprawdzić, powyżej lub poniżej. ten tablica_tabeli to zakres komórek zawierających tabelę informacji, takich jak produkty i odpowiadające im ceny, oraz col_index_num jest kolumną, w której program Excel ma szukać wartości do zwrócenia, z 1 wskazując skrajną lewą kolumnę, 2 wskazując następny i tak dalej. Wreszcie, [Zakres wyszukiwania] jest opcjonalne, ale wpisz „PRAWDA”, jeśli chcesz, aby program Excel opierał odpowiedź na najbliższej wartości, lub „FAŁSZ”, jeśli chcesz tylko dokładnego dopasowania.