Opanowanie zagnieżdżonych instrukcji IF w programie Excel — przewodnik krok po kroku
Chociaż w programie Excel funkcja JEŻELI jest niezbędna w podstawowych testach logicznych, złożone warunki często wymagają zagnieżdżonych instrukcji JEŻELI w celu usprawnienia przetwarzania danych. W tym obszernym przewodniku szczegółowo omówimy podstawy zagnieżdżonego JEŻELI, od składni po praktyczne zastosowania, w tym kombinacje zagnieżdżonych JEŻELI z warunkami AND/OR. Ponadto podzielimy się sposobami poprawy czytelności zagnieżdżonych funkcji JEŻELI, a także kilkoma wskazówkami dotyczącymi zagnieżdżonych JEŻELI, a także odkryjemy potężne alternatywy, takie jak WYSZUKAJ.PIONOWO, IFS i inne, dzięki którym złożone operacje logiczne będą łatwiejsze w użyciu i bardziej wydajne.
- Składnia zagnieżdżonego JEŻELI
- Praktyczne przykłady zagnieżdżonych JEŻELI
- Zagnieżdżone, jeśli z warunkiem AND/OR
- Uczynienie zagnieżdżonego IF łatwym do odczytania
- Kolejność zagnieżdżonych funkcji JEŻELI
- Liczby i tekst należy traktować inaczej
- Ograniczenia zagnieżdżonego JEŻELI
Funkcja JEŻELI w programie Excel a zagnieżdżone instrukcje JEŻELI
Funkcja JEŻELI i zagnieżdżone instrukcje JEŻELI w programie Excel służą podobnym celom, ale różnią się znacznie pod względem złożoności i zastosowania.
- Składnia jest:
=IF (logical_test, [value_if_true], [value_if_false]) - Ograniczenie: Może obsłużyć tylko jeden warunek na raz, co czyni go mniej odpowiednim w przypadku bardziej złożonych scenariuszy podejmowania decyzji, które wymagają oceny wielu kryteriów.
- Składnia jest:
=IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 )) - Złożoność: Może obsłużyć wiele warunków, ale może stać się skomplikowany i trudny do odczytania przy zbyt wielu warstwach zagnieżdżenia.
Użycie zagnieżdżonego JEŻELI
W tej sekcji przedstawiono podstawowe użycie zagnieżdżonych instrukcji JEŻELI w programie Excel, w tym składnię, praktyczne przykłady i sposób ich używania z warunkami AND lub OR.
Składnia zagnieżdżonego JEŻELI
Zrozumienie składni funkcji jest podstawą jej prawidłowego i efektywnego zastosowania w Excelu. Zacznijmy od składni zagnieżdżonych instrukcji if.
Składnia:
=IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, result4)))
Argumenty:
- Condition1, Condition2, Condition3: These are the conditions you want to test. Each condition is evaluated in order, starting with Condition1.
- Result1: This is the value returned if Condition1 is TRUE.
- Result2: This value is returned if Condition1 is FALSE and Condition2 is TRUE. It's important to note that Result2 is only evaluated if Condition1 is FALSE.
- Result3: This value is returned if both Condition1 and Condition2 are FALSE, and Condition3 is TRUE. Essentially, for Result3 to be evaluated, the previous conditions (Condition1 and Condition2) must both be FALSE.
- Result4: This result is returned if all the conditions (Condition1, Condition2, and Condition3) are FALSE.
In short, this expression can be interpreted as follows:Test condition1, if TRUE, return result1, if FALSE,
test condition2, if TRUE, return result2, if FALSE,
test condition3, if TRUE, return result3, if FALSE,
return result4
Pamiętaj, że w zagnieżdżonej strukturze JEŻELI każdy kolejny warunek jest oceniany tylko wtedy, gdy wszystkie poprzednie warunki mają wartość FAŁSZ. To sprawdzanie sekwencyjne ma kluczowe znaczenie dla zrozumienia działania zagnieżdżonych elementów IF.
Praktyczne przykłady zagnieżdżonych JEŻELI
Przyjrzyjmy się teraz zastosowaniu zagnieżdżonej funkcji JEŻELI na dwóch praktycznych przykładach.
Przykład 1: System oceniania
Jak pokazano na zrzucie ekranu poniżej, załóżmy, że masz listę wyników uczniów i chcesz przypisywać oceny na podstawie tych wyników. Aby wykonać to zadanie, możesz użyć zagnieżdżonego JEŻELI.
Wybierz pustą komórkę (w tym przypadku C2), wprowadź poniższą formułę i naciśnij Wchodzę aby uzyskać wynik. Następnie przeciągnij Wypełnij uchwyt w dół, aby uzyskać resztę wyników.
=IF(B2>=90,$F$2,IF(B2>=80,$F$3,IF(B2>=70,$F$4,IF(B2>=60,$F$5,$F$6))))
- Możesz bezpośrednio określić poziom oceny we wzorze, dzięki czemu formułę można zmienić na:
=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))
- Ta formuła służy do przypisywania ocen (A, B, C, D lub F) na podstawie wyniku w komórce A2, przy użyciu standardowych progów ocen. Jest to typowy przypadek użycia zagnieżdżonych instrukcji IF w akademickich systemach oceniania.
- Wyjaśnienie wzoru:
- A2>=90: Jest to pierwszy warunek sprawdzany przez formułę. Jeśli wynik w komórce A2 jest większy lub równy 90, formuła zwraca wartość „A”.
- A2>=80: Jeśli pierwszy warunek jest fałszywy (wynik jest mniejszy niż 90), sprawdza, czy A2 jest większy lub równy 80. Jeśli jest prawdziwy, zwraca „B”.
- A2>=70: Podobnie, jeśli wynik jest mniejszy niż 80, sprawdza, czy jest większy lub równy 70. Jeśli ma wartość true, zwraca „C”.
- A2>=60: Jeśli wynik jest mniejszy niż 70, formuła sprawdza, czy jest większy lub równy 60. Jeśli jest prawdziwy, zwraca „D”.
- "F„: Na koniec, jeśli żaden z powyższych warunków nie jest spełniony (co oznacza, że wynik jest mniejszy niż 60), formuła zwraca „F”.
Przykład 2: Obliczanie prowizji od sprzedaży
Wyobraź sobie scenariusz, w którym przedstawiciele handlowi otrzymują różne stawki prowizji w zależności od ich osiągnięć sprzedażowych. Jak pokazano na zrzucie ekranu poniżej, chcesz obliczyć prowizję sprzedawcy w oparciu o te różne progi sprzedażowe, a zagnieżdżone instrukcje IF mogą Ci w tym pomóc.
- 20% w przypadku sprzedaży powyżej 20,000 XNUMX USD
- 15% w przypadku sprzedaży o wartości od 10,000 20,000 do XNUMX XNUMX USD
- 10% w przypadku sprzedaży poniżej 10,000 XNUMX USD
Wybierz pustą komórkę (w tym przypadku C2), wprowadź poniższą formułę i naciśnij Wchodzę aby uzyskać wynik. Następnie przeciągnij Wypełnij uchwyt w dół, aby uzyskać resztę wyników.
=B2*IF(B2>20000,$F$2,IF(B2>=10000,$F$3,$F$4))
- Możesz bezpośrednio określić stawkę prowizji we wzorze, dzięki czemu formułę można zmienić na:
=B2*IF(B2>20000, 20%, IF(B2>=10000, 15%, 10%))
- Podany wzór służy do obliczenia prowizji sprzedawcy na podstawie kwoty sprzedaży, stosując różne stawki prowizji dla różnych progów sprzedaży.
- Wyjaśnienie wzoru:
- B2: reprezentuje kwotę sprzedaży sprzedawcy, która służy jako podstawa do obliczenia prowizji.
- JEŻELI(B2>20000, "20%", ...): To jest pierwszy sprawdzany warunek. Sprawdza, czy kwota sprzedaży w B2 jest większa niż 20,000 20. Jeśli tak, formuła wykorzystuje stawkę prowizji w wysokości XNUMX%.
- JEŻELI(B2>=10000; "15%", "10%"): Jeśli pierwszy warunek jest fałszywy (sprzedaż nie jest większa niż 20,000 10,000), formuła sprawdza, czy sprzedaż jest równa lub większa niż 15 10,000. Jeśli to prawda, stosuje stawkę prowizji w wysokości 10%. Jeśli kwota sprzedaży jest mniejsza niż XNUMX XNUMX, formuła domyślnie przyjmuje stawkę prowizji wynoszącą XNUMX%.
Zagnieżdżone, jeśli z warunkiem AND/OR
W tej sekcji modyfikuję powyższy pierwszy przykład „system oceniania”, aby zademonstrować, jak połączyć zagnieżdżone warunki JEŻELI z warunkiem AND lub OR w programie Excel. W poprawionym przykładzie oceniania wprowadziłem dodatkowy warunek oparty na „Wskaźniku frekwencji”.
Używanie zagnieżdżonego if z warunkiem AND
Jeśli uczeń spełni zarówno kryteria punktacji, jak i frekwencji, otrzyma ocenę podwyższoną. Na przykład, ocena ucznia, który uzyskał co najmniej 60 punktów i frekwencję na poziomie 95% lub więcej, zostanie podniesiona o jeden poziom, na przykład z A do A+, z B do B+ i tak dalej. Jeżeli jednak frekwencja będzie niższa niż 95%, ocena będzie przeprowadzana według pierwotnych kryteriów punktowych. W takich przypadkach musimy użyć zagnieżdżonej instrukcji JEŻELI z warunkiem AND.
Wybierz pustą komórkę (w tym przypadku D2), wprowadź poniższą formułę i naciśnij Wchodzę aby uzyskać wynik. Następnie przeciągnij Wypełnij uchwyt w dół, aby uzyskać resztę wyników.
=IF(AND(B2>=60, C2>=95%),IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", "D+"))),IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))))
- ORAZ sprawdzenie stanu:
ORAZ(B2>=60, C2>=95%): Warunek ORAZ sprawdza najpierw, czy oba warunki są spełnione — wynik ucznia wynosi 60 lub więcej, a jego frekwencja wynosi 95% lub więcej. - Nowe zadanie klasowe:
JEŻELI(B2>=90, „A+”, JEŻELI(B2>=80, „B+”, JEŻELI(B2>=70, „C+”, „D+”))): Jeśli oba warunki w instrukcji AND są prawdziwe, formuła sprawdza wynik ucznia i podnosi jego ocenę o jeden poziom.- B2>=90: Jeśli wynik wynosi 90 lub więcej, ocena to „A+„.Nowe zadanie na ocenę:
- B2>=80: Jeśli wynik wynosi 80 lub więcej (ale mniej niż 90), ocena to „B+".
- B2>=70: Jeśli wynik wynosi 70 lub więcej (ale mniej niż 80), ocena to „C+”.
- B2>=60: Jeśli wynik wynosi 60 lub więcej (ale mniej niż 70), ocena to „D+”.
- Regularne przypisanie klasy:
JEŻELI(B2>=90, „A”, JEŻELI(B2>=80, „B”, JEŻELI(B2>=70, „C”, JEŻELI(B2>=60, „D”, „F”))) ): Jeśli warunek ORAZ nie jest spełniony (albo wynik jest poniżej 80, albo frekwencja jest poniżej 95%), formuła przypisuje oceny standardowe.- B2>=90: Wynik 90 lub wyższy oznacza „A”.
- B2>=80: Wynik 80 lub więcej (ale mniej niż 90) powoduje otrzymanie oceny „B”.
- B2>=70: Wynik 70 lub więcej (ale mniej niż 80) powoduje otrzymanie „C”.
- B2>=60: Wynik 60 lub więcej (ale mniej niż 70) powoduje otrzymanie „D”.
- Wyniki poniżej 60 otrzymują „F”.
Używanie zagnieżdżonego if z warunkiem OR
W takim przypadku ocena ucznia zostanie podniesiona o jeden poziom, jeśli jego wynik wyniesie 95 lub więcej lub jeśli jego frekwencja wyniesie 95% lub więcej. Oto, jak możemy to osiągnąć, używając zagnieżdżonych warunków JEŻELI i LUB.
Wybierz pustą komórkę (w tym przypadku D2), wprowadź poniższą formułę i naciśnij Wchodzę aby uzyskać wynik. Następnie przeciągnij Wypełnij uchwyt w dół, aby uzyskać resztę wyników.
=IF(OR(B2>=95, C2>=95%),IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", IF(B2>=60, "D+", "F+")))),IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))))
- LUB Kontrola stanu:
LUB(B2>=95, C2>=95%): Formuła najpierw sprawdza, czy którykolwiek z warunków jest spełniony — wynik ucznia wynosi 95 lub więcej albo jego frekwencja wynosi 95% lub więcej. - Przypisanie oceny z premią:
JEŻELI(B2>=90, „A+”, JEŻELI(B2>=80, „B+”, JEŻELI(B2>=70, „C+”, JEŻELI(B2>=60, „D+”, „F+”))) ): Jeśli którykolwiek z warunków w instrukcji OR jest prawdziwy, ocena ucznia zostanie podniesiona o jeden poziom.- B2>=90: Jeśli wynik wynosi 90 lub więcej, ocena to „A+”.
- B2>=80: Jeśli wynik wynosi 80 lub więcej (ale mniej niż 90), ocena to „B+”.
- B2>=70: Jeśli wynik wynosi 70 lub więcej (ale mniej niż 80), ocena to „C+”.
- B2>=60: Jeśli wynik wynosi 60 lub więcej (ale mniej niż 70), ocena to „D+”.
- W przeciwnym razie ocena to „F+”.
- Regularne przypisanie klasy:
JEŻELI(B2>=80, "B", JEŻELI(B2>=70, "C", JEŻELI(B2>=60, "D", "F")))): Jeśli żaden z warunków OR nie jest spełniony (punktacja poniżej 95, a frekwencja poniżej 95%), formuła przypisuje oceny standardowe.- B2>=90: Wynik 90 lub wyższy oznacza „A”.
- B2>=80: Wynik 80 lub więcej (ale mniej niż 90) powoduje otrzymanie oceny „B”.
- B2>=70: Wynik 70 lub więcej (ale mniej niż 80) powoduje otrzymanie „C”.
- B2>=60: Wynik 60 lub więcej (ale mniej niż 70) powoduje otrzymanie „D”.
- Wyniki poniżej 60 otrzymują „F”.
Wskazówki i porady dotyczące zagnieżdżonych JEŻELI
W tej sekcji omówiono cztery przydatne porady i wskazówki dotyczące zagnieżdżonych JEŻELI.
Uczynienie zagnieżdżonego IF łatwym do odczytania
Typowa zagnieżdżona instrukcja JEŻELI może wyglądać zwięźle, ale może być trudna do rozszyfrowania.
W poniższym wzorze trudno jest szybko określić, gdzie kończy się jeden warunek, a zaczyna inny, zwłaszcza gdy wzrasta jego złożoność.
=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))
Rozwiązanie: Dodawanie podziałów wierszy i wcięć
Aby zagnieżdżone JEŻELI były łatwe do odczytania, możesz podzielić formułę na wiele wierszy, przy czym każdy zagnieżdżony JEŻELI będzie w nowym wierszu. W formule po prostu umieść kursor przed JEŻELI i naciśnij klawisze Alt + Enter.
Po rozbiciu powyższej formuły wyświetli się ona następująco:
=IF(A2>=90, "A",
IF(A2>=80, "B",
IF(A2>=70, "C",
IF(A2>=60, "D", "F")))
)
Dzięki temu formatowi jest wyraźniej widoczne, gdzie znajdują się poszczególne warunki i odpowiadające im dane wyjściowe, co zwiększa czytelność formuły.
Kolejność zagnieżdżonych funkcji JEŻELI
Kolejność warunków logicznych w zagnieżdżonej formule JEŻELI jest kluczowa, ponieważ określa, w jaki sposób Excel ocenia te warunki, a tym samym wpływa na końcowy wynik formuły.
Prawidłowa formuła
W przykładzie System ocen używamy poniższej formuły do przypisywania ocen na podstawie wyników.
=IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F"))))
Program Excel ocenia warunki w zagnieżdżonej formule JEŻELI sekwencyjnie, od pierwszego do ostatniego warunku. Ta formuła sprawdza najpierw najwyższy próg wyniku (>= 90 dla „A”), a następnie przechodzi do niższych progów. Zapewnia porównanie wyniku z najwyższą oceną, do której się kwalifikuje. Jeśli pierwszy warunek jest prawdziwy (A2>=90), zwraca „A” i nie ocenia żadnych dalszych warunków.
Nieprawidłowo uporządkowana formuła
Jeśli kolejność warunków zostanie odwrócona, zaczynając od najniższego progu, zwrócone zostaną nieprawidłowe wyniki.
=IF(B2>=60, "D", IF(B2>=70, "C", IF(B2>=80, "B", IF(B2>=90, "A", "F"))))
W tym błędnym wzorze wynik 95 od razu spełniałby pierwszy warunek B2>=60 i zostałby błędnie przypisany do oceny „D”.
Liczby i tekst należy traktować inaczej
W tej sekcji dowiesz się, jak liczby i tekst są traktowane inaczej w zagnieżdżonych instrukcjach JEŻELI.
Liczby
Liczby służą do porównań arytmetycznych i obliczeń. W zagnieżdżonych instrukcjach JEŻELI można bezpośrednio porównywać liczby za pomocą operatorów takich jak >, <, =, >= i <=.
Tekst
W zagnieżdżonych instrukcjach IF tekst powinien brzmieć: ujęte w podwójny cudzysłów. Zobacz A, B, C, D i F w następującym wzorze:
=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))
Ograniczenia zagnieżdżonego JEŻELI
W tej sekcji wymieniono kilka ograniczeń i wad zagnieżdżonych JEŻELI.
Złożoność i czytelność:
Chociaż Excel pozwala na zagnieżdżenie do 64 różnych funkcji JEŻELI, nie jest to wcale wskazane. Im więcej poziomów zagnieżdżenia, tym bardziej złożona staje się formuła. Może to prowadzić do tworzenia formuł trudnych do odczytania, zrozumienia i utrzymania.
Podatne na błędy:
Co więcej, złożone zagnieżdżone instrukcje IF mogą być podatne na błędy i trudne do debugowania lub modyfikacji.
Trudne do rozszerzenia lub skalowania:
Jeśli zmieni się Twoja logika lub będziesz musiał dodać więcej warunków, głęboko zagnieżdżone elementy IF mogą być trudne do modyfikacji lub rozszerzenia.
Zrozumienie tych ograniczeń jest kluczem do efektywnego korzystania z zagnieżdżonych instrukcji JEŻELI w programie Excel. Często łączenie zagnieżdżonych IF z innymi funkcjami lub poszukiwanie alternatywnych podejść może prowadzić do bardziej wydajnych i łatwiejszych w utrzymaniu rozwiązań.
Alternatywy dla zagnieżdżonych JEŻELI
W tej sekcji wymieniono kilka funkcji programu Excel, których można użyć jako alternatywy dla zagnieżdżonych instrukcji JEŻELI.
Korzystanie z funkcji WYSZUKAJ.PIONOWO
Aby wykonać powyższe dwa praktyczne przykłady, możesz użyć funkcji WYSZUKAJ.PIONOWO zamiast zagnieżdżonych instrukcji JEŻELI. Oto jak możesz to zrobić:
Przykład 1: System oceniania z funkcją WYSZUKAJ.PIONOWO
Tutaj pokażę, jak używać WYSZUKAJ.PIONOWO do przypisywania ocen na podstawie wyników.
Krok 1: Utwórz tabelę przeglądową dla ocen
Po pierwsze, musisz utworzyć tabelę przeglądową (w tym przypadku E1:F6) dla zakresu wyników i odpowiadających im ocen. Note: Wyniki w pierwszej kolumnie tabeli należy posortować rosnąco.
Krok 2: Zastosuj funkcję WYSZUKAJ.PIONOWO, aby przypisać oceny
Wybierz pustą komórkę (w tym przypadku C2), wprowadź poniższą formułę i naciśnij Wchodzę klucz do uzyskania pierwszej klasy. Wybierz tę komórkę z formułą i przeciągnij ją Wypełnij uchwyt w dół, aby uzyskać resztę ocen.
=VLOOKUP(B2,$E$2:$F$6,2,TRUE)
- Wartość 95 w komórce B2 jest tym, czego szuka funkcja WYSZUKAJ.PIONOWO w pierwszej kolumnie tabeli przeglądowej ($E$2:$F$6). Jeśli zostanie znaleziony, zwraca odpowiednią ocenę z drugiej kolumny tabeli, znajdującej się w tym samym wierszu, co dopasowana wartość.
- Pamiętaj, aby odwołanie do tabeli przeglądowej było bezwzględne (dodaj znaki dolara ($) przed odniesieniami), co oznacza, że odwołanie nie ulegnie zmianie, jeśli formuła zostanie skopiowana do innej komórki.
- Aby dowiedzieć się więcej o funkcji WYSZUKAJ.PIONOWO, odwiedź tę stronę.
Przykład 2: Obliczanie prowizji od sprzedaży za pomocą funkcji WYSZUKAJ.PIONOWO
Możesz także użyć WYSZUKAJ.PIONOWO, aby obliczyć prowizję od sprzedaży w programie Excel. Wykonaj następujące czynności.
Krok 1: Utwórz tabelę przeglądową dla ocen
Po pierwsze, musisz utworzyć tabelę przeglądową sprzedaży i odpowiadającą jej stawkę prowizji, np. E2:F4 w tym przypadku. Note: Sprzedaż w pierwszej kolumnie tabeli należy posortować rosnąco.
Krok 2: Zastosuj funkcję WYSZUKAJ.PIONOWO, aby przypisać oceny
Wybierz pustą komórkę (w tym przypadku C2), wprowadź poniższą formułę i naciśnij klawisz Enter, aby otrzymać pierwszą prowizję. Wybierz tę komórkę z formułą i przeciągnij jej uchwyt wypełnienia w dół, aby uzyskać pozostałe wyniki.
=B2*VLOOKUP(B2,$E$2:$F$4,2,TRUE)
- W obu przykładach funkcja WYSZUKAJ.PIONOWO służy do wyszukiwania wartości w tabeli na podstawie wartości wyszukiwania (wyniku lub kwoty sprzedaży) i zwraca wartość w tym samym wierszu z określonej kolumny (ocena lub stawka prowizji). Czwarty parametr TRUE wskazuje przybliżone dopasowanie, które jest odpowiednie w scenariuszach, w których dokładna wartość wyszukiwania może nie znajdować się w tabeli.
- Aby dowiedzieć się więcej o funkcji WYSZUKAJ.PIONOWO, odwiedź tę stronę.
Korzystanie z IFS
Połączenia Funkcja IFS upraszcza proces, eliminując potrzebę zagnieżdżania i sprawia, że formuły są łatwiejsze do odczytania i zarządzania nimi. Zwiększa czytelność i usprawnia obsługę wielu kontroli warunkowych. Aby korzystać z funkcji IFS, upewnij się, że używasz programu Excel 2019 lub nowszego albo masz subskrypcję Office 365. Zobaczmy, jak można to zastosować na praktycznych przykładach.
Przykład 1: System oceniania z IFS
Zakładając te same kryteria oceniania co poprzednio, funkcję IFS można zastosować w następujący sposób:
Wybierz pustą komórkę, np. C2, wprowadź poniższą formułę i naciśnij Wchodzę aby uzyskać pierwszy wynik. Wybierz tę komórkę wynikową i przeciągnij ją Wypełnij uchwyt w dół, aby uzyskać resztę wyników.
=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",B2<60,"F")
- Każdy warunek jest oceniany w kolejności. Gdy tylko warunek zostanie spełniony, zwracany jest odpowiadający mu wynik, a formuła przestaje sprawdzać dalsze warunki. W tym przypadku formuła służy do przypisywania ocen na podstawie wyniku z B2, według typowej skali ocen, gdzie wyższa ocena odpowiada ocenie lepszej.
- Aby dowiedzieć się więcej o funkcji IFS, odwiedź tę stronę.
Przykład 2: Obliczanie prowizji od sprzedaży za pomocą IFS
W scenariuszu naliczania prowizji od sprzedaży funkcję IFS stosuje się w następujący sposób:
Wybierz pustą komórkę, np. C2, wprowadź poniższą formułę i naciśnij Wchodzę aby uzyskać pierwszy wynik. Wybierz tę komórkę wynikową i przeciągnij ją Wypełnij uchwyt w dół, aby uzyskać resztę wyników.
=B2*IFS(B2>20000,20%,B2>=10000,15%,TRUE,10%)
Używając WYBIERZ i DOPASUJ
Podejście WYBIERZ i DOPASUJ może być bardziej wydajne i łatwiejsze w zarządzaniu w porównaniu z zagnieżdżonymi instrukcjami JEŻELI. Ta metoda upraszcza formułę i sprawia, że aktualizacje lub zmiany są prostsze. Poniżej pokażę, jak używać kombinacji funkcji WYBIERZ i DOPASUJ, aby poradzić sobie z dwoma praktycznymi przykładami w tym artykule.
Przykład 1: System oceniania z WYBIERZ i DOPASUJ
Możesz użyć kombinacji funkcji WYBIERZ i DOPASUJ, aby przypisać oceny na podstawie różnych wyników.
Krok 1: Utwórz tablicę odnośników z wyszukiwanymi wartościami
Najpierw musisz utworzyć zakres komórek zawierający wartości progowe, które będzie przeszukiwać funkcja MATCH, np. w tym przypadku $E$2:$E$6. Note: Aby funkcja DOPASUJ działała poprawnie w przypadku użycia przybliżonego typu dopasowania, liczby w tym zakresie muszą być posortowane rosnąco.
Krok 2: Zastosuj WYBIERZ i DOPASUJ, aby przypisać oceny
Wybierz pustą komórkę (w tym przypadku C2), wprowadź poniższą formułę i naciśnij Wchodzę klucz do uzyskania pierwszej klasy. Wybierz tę komórkę z formułą i przeciągnij ją Wypełnij uchwyt w dół, aby uzyskać resztę wyników.
=CHOOSE(MATCH(B2, $E$2:$E$6, 1), "F", "D", "C", "B", "A")
- DOPASUJ(B2; $E$2:$E$6; 1): ta część formuły szuka wyniku (95) w komórce B2 w zakresie $E$2:$E$6. Wartość 1 wskazuje, że funkcja MATCH powinna znaleźć przybliżone dopasowanie, co oznacza, że znajduje największą wartość w zakresie mniejszą lub równą B2.
- WYBIERZ(..., "F", "D", "C", "B", "A"): Na podstawie pozycji zwróconej przez funkcję DOPASUJ, WYBIERZ wybiera odpowiednią ocenę.
- Aby dowiedzieć się więcej o Funkcja DOPASUJ, odwiedź tę stronę.
- Aby dowiedzieć się więcej o WYBIERZ funkcję, odwiedź tę stronę.
Przykład 2: Obliczanie prowizji od sprzedaży za pomocą IFS
Korzystanie z kombinacji WYBIERZ i DOPASUJ do obliczenia prowizji od sprzedaży może być również skuteczne, zwłaszcza gdy stawki prowizji opierają się na określonych progach sprzedaży. Zobaczmy, jak możemy to zrobić.
Krok 1: Utwórz tablicę odnośników z wyszukiwanymi wartościami
Najpierw musisz utworzyć zakres komórek zawierający wartości progowe, które będzie przeszukiwać funkcja MATCH, np. w tym przypadku $E$2:$E$4. Note: Aby funkcja DOPASUJ działała poprawnie w przypadku użycia przybliżonego typu dopasowania, liczby w tym zakresie muszą być posortowane rosnąco.
Krok 2: Zastosuj WYBIERZ i DOPASUJ, aby uzyskać wyniki
Wybierz pustą komórkę (w tym przypadku C2), wprowadź poniższą formułę i naciśnij Wchodzę klucz do uzyskania pierwszej klasy. Wybierz tę komórkę z formułą i przeciągnij ją Wypełnij uchwyt w dół, aby uzyskać resztę wyników.
=B2*CHOOSE(MATCH(B2, $E$2:$E$4, 1), 10%, 15%, 20%)
- Aby dowiedzieć się więcej o Funkcja DOPASUJ, odwiedź tę stronę.
- Aby dowiedzieć się więcej o WYBIERZ funkcję, odwiedź tę stronę.
Podsumowując, opanowanie zagnieżdżonych instrukcji IF w programie Excel to cenna umiejętność, która zwiększa zdolność radzenia sobie ze złożonymi logicznymi scenariuszami w analizie danych i procesach podejmowania decyzji. Chociaż zagnieżdżone elementy IF są przydatne w przypadku złożonych operacji logicznych, ważne jest, aby pamiętać o ich ograniczeniach. Prostsze alternatywy, takie jak WYSZUKAJ.PIONOWO, IFS i WYBIERZ z funkcją MATCH, mogą w niektórych scenariuszach zapewnić bardziej usprawnione rozwiązania. Uzbrojeni w te spostrzeżenia, możesz teraz bez obaw stosować najodpowiedniejsze techniki programu Excel do zadań związanych z analizą danych, zapewniając przejrzystość, dokładność i wydajność swoich arkuszy kalkulacyjnych. Dla tych, którzy chcą głębiej zagłębić się w możliwości Excela, nasza strona internetowa oferuje mnóstwo samouczków. Tutaj znajdziesz więcej porad i wskazówek dotyczących Excela.
Powiązane artykuły
Użyj funkcji JEŻELI z ORAZ, LUB i NIE w programie Excel
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.
Warunkowa lista rozwijana z instrukcją IF
W tym samouczku przedstawiono 5 metod, które pomogą Ci krok po kroku utworzyć warunkową listę rozwijaną w programie Excel.
Power Query: Instrukcja if - zagnieżdżone if i wiele warunków
W Excelu Power Query, instrukcja JEŻELI jest jedną z najpopularniejszych funkcji sprawdzających warunek i zwracających określoną wartość w zależności od tego, czy wynikiem jest PRAWDA, czy FAŁSZ. Istnieją pewne różnice między instrukcją if a funkcją JEŻELI w programie Excel. W tym samouczku przedstawię składnię tej instrukcji if oraz kilka prostych i złożonych przykładów.
Najlepsze narzędzia biurowe
Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas. Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...
Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę
- Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
- Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
- Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
Spis treści
- Funkcja JEŻELI a zagnieżdżone instrukcje JEŻELI
- Użycie zagnieżdżonego JEŻELI
- Składnia zagnieżdżonego JEŻELI
- Praktyczne przykłady zagnieżdżonych JEŻELI
- Zagnieżdżone, jeśli z warunkiem AND/OR
- Wskazówki i porady dotyczące zagnieżdżonych JEŻELI
- Uczynienie zagnieżdżonego IF łatwym do odczytania
- Kolejność zagnieżdżonych funkcji JEŻELI
- Liczby i tekst należy traktować inaczej
- Ograniczenia zagnieżdżonego JEŻELI
- Alternatywy dla zagnieżdżonych JEŻELI
- Korzystanie z funkcji WYSZUKAJ.PIONOWO
- Korzystanie z IFS
- Używając WYBIERZ i DOPASUJ
- Powiązane artykuły
- Najlepsze narzędzia biurowe
- Komentarze