Czy masz formułę programu Excel zagnieżdżającą wiele funkcji JEŻELI? Kiedy dojdziesz do punktu ze zbyt dużą liczbą zagnieżdżonych instrukcji JEŻELI, musisz sprawdzić, czy całość stałaby się prostsza dzięki prostej funkcji WYSZUKAJ.PIONOWO. Widziałem, jak 1000-znakowe formuły zostały uproszczone do 30-znakowej formuły VLOOKUP. Jest to łatwe w utrzymaniu, gdy trzeba później dodać nowe wartości.
Dawno temu pracowałem jako wiceprezes ds. Sprzedaży w pracy. Zawsze modelowałem jakiś nowy program bonusowy lub plan prowizyjny. Przyzwyczaiłem się do zlecania planów z różnymi warunkami. Ten poniżej jest dość oswojony.
Normalnym podejściem jest rozpoczęcie tworzenia zagnieżdżonej formuły JEŻELI. Zawsze zaczynasz od górnego lub dolnego końca zakresu. „Jeśli sprzedaż przekracza 500 000 USD, wówczas rabat wynosi 20%; Inaczej… ." Trzeci argument funkcji JEŻELI to zupełnie nowa funkcja JEŻELI, która sprawdza drugi poziom: Jeśli sprzedaż przekracza 250 000 USD, rabat wynosi 15%; Inaczej,… "
Te formuły stają się coraz dłuższe, ponieważ jest więcej poziomów. Najtrudniejszą częścią takiej formuły jest zapamiętanie, ile nawiasów zamykających należy umieścić na końcu formuły.
Mini wskazówka bonusowa
Dopasowanie do nawiasów
Excel przełącza się między różnymi kolorami dla każdego nowego poziomu nawiasów. Podczas gdy Excel ponownie wykorzystuje kolory, używa czarnego tylko jako nawias otwierający i pasujący nawias zamykający. Gdy kończysz formułę poniżej, po prostu kontynuuj wpisywanie nawiasów zamykających, aż wpiszesz czarny nawias.
Powrót do porady dotyczącej zagnieżdżonych formuł
Jeśli używasz programu Excel 2003, Twoja formuła już zbliża się do limitu. Wtedy nie można było zagnieżdżać więcej niż 7 funkcji JEŻELI. To był brzydki dzień, kiedy uprawnienia zmieniły plan prowizji i potrzebowałeś sposobu na dodanie ósmej funkcji IF. Obecnie można zagnieżdżać 64 funkcje JEŻELI. Nigdy nie powinieneś tego robić, ale dobrze jest wiedzieć, że nie ma problemu z zagnieżdżeniem 8 lub 9.
Zamiast używać zagnieżdżonej funkcji JEŻELI, spróbuj użyć nietypowego użycia funkcji WYSZUKAJ.PIONOWO. Gdy czwarty argument funkcji WYSZUKAJ.PIONOWO zmienia się z Fałsz na Prawda, funkcja nie szuka już dokładnego dopasowania.
Cóż, najpierw WYSZUKAJ.PIONOWO próbuje znaleźć dokładne dopasowanie. Ale jeśli dokładne dopasowanie nie zostanie znalezione, program Excel ustawia się w wierszu tylko mniej niż to, czego szukasz.
Rozważ poniższą tabelę. W komórce C13 program Excel będzie szukał dopasowania za 28 355 USD w tabeli. Gdy nie może znaleźć 28355, program Excel zwróci rabat powiązany z wartością, która jest po prostu mniejsza. W tym przypadku 1% zniżki za poziom 10 000 $.
Konwertując reguły do tabeli w E13: F18, musisz zacząć od najniższego poziomu i przejść do najwyższego poziomu. Chociaż nie było to określone w zasadach, jeśli ktoś ma sprzedaż poniżej 10 000 USD, rabat wyniesie 0%. Musisz dodać to jako pierwszy wiersz w tabeli.
Uwaga
Gdy używasz „prawdziwej” wersji WYSZUKAJ.PIONOWO, twoja tabela musi być posortowana rosnąco. Wiele osób uważa, że wszystkie tabele wyszukiwania muszą zostać posortowane. Ale tabelę należy posortować tylko w przypadku dopasowania przybliżonego.
A jeśli twój menedżer chce całkowicie samodzielnej formuły i nie chce widzieć tabeli premiowej po prawej stronie? Po utworzeniu formuły możesz osadzić tabelę bezpośrednio w formule.
Umieść formułę w trybie edycji, klikając dwukrotnie komórkę lub zaznaczając komórkę i naciskając klawisz F2.
Zaznacz kursorem cały drugi argument: $ E $ 13: $ F $ 18.
Naciśnij klawisz F9. Excel osadzi tabelę przeglądową jako stałą tablicową. W stałej tablicowej średnik wskazuje nowy wiersz, a przecinek wskazuje nową kolumnę.
Naciśnij enter. Skopiuj formułę do innych komórek.
Możesz teraz usunąć tabelę. Ostateczny wzór przedstawiono poniżej.
Dziękuję Mike'owi Girvinowi za nauczenie mnie pasujących nawiasów. Technikę WYSZUKAJ.PIONOWO zasugerowali Danny Mac, Boriana Petrova, Andreas Thehos i @mvmcos.
Obejrzyj wideo
- Dzięki wielopoziomowym programom prowizji, premii lub rabatów często musisz zagnieżdżać funkcje IF
- Limit programu Excel 2003 wynosił 7 zagnieżdżonych instrukcji IF.
- Możesz teraz zagnieździć 32, ale nie sądzę, że powinieneś kiedykolwiek zagnieździć 32
- Czy kiedykolwiek używałbyś wersji z przybliżonym dopasowaniem funkcji WYSZUKAJ.PIONOWO? To jest czas.
- Przetłumacz program rabatowy na tabelę przeglądową
- WYSZUKAJ.PIONOWO w większości przypadków nie znajdzie odpowiedzi.
- Mówiąc, prawda na końcu powie WYSZUKAJ.PIONOWO, aby znaleźć mniejszą wartość.
- To jedyny przypadek, gdy tabela WYSZUKAJ.PIONOWO musi zostać posortowana.
- Nie chcesz, aby tabela WYSZUKAJ.PIONOWO była przesunięta na bok? Osadź go w formule.
- F2, aby edytować formułę. Wybierz tabelę przeglądową. Naciśnij klawisz F9. Wchodzić.
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2030 - Zastąpiono zagnieżdżony IF funkcją WYSZUKAJ.PIONOWO!
Będę podcastował całą książkę, kliknij „i” w prawym górnym rogu, aby przejść do listy odtwarzania!
Hej, witaj z powrotem w netcastie, jestem Bill Jelen. W porządku, jest to naprawdę powszechne w przypadku programu prowizyjnego, programu rabatowego lub programu bonusowego, w którym mamy poziomy, różne poziomy, prawda? Jeśli masz powyżej 10000 USD, otrzymasz 1% zniżki, 50000 USD 5% zniżki. Musisz zachować ostrożność podczas tworzenia tej zagnieżdżonej instrukcji IF, zaczynasz ją od góry, jeśli szukasz większego niż, lub od dołu, jeśli szukasz mniej niż. Więc B10> 50000, 20%, w przeciwnym razie inny IF, B10> 250000, 25% i tak dalej i tak dalej. To jest po prostu długa i skomplikowana formuła, a jeśli tabela jest większa, w programie Excel 2003 nie można było zagnieździć więcej niż 7 instrukcji JEŻELI, to prawie zbliżamy się do granicy. Możesz teraz iść do 32, myślę, że nie powinieneś kiedykolwiek iść do 32, a nawet jeśli krzyczysz „Hej, czekaj,A co z nową funkcją, która właśnie pojawiła się w programie Excel 2016, wydaniu z lutego 2016 r., z funkcją IFS? ” Jasne, jest tu mniej nawiasów i 87 znaków zamiast 97 znaków, nadal jest bałagan, pozbądźmy się tego i zróbmy to za pomocą WYSZUKAJ.PIONOWO!
W porządku, oto kroki, przyjmujesz te zasady i odwracasz je do góry nogami. Pierwszą rzeczą, którą musimy powiedzieć, jest to, że jeśli miałeś 0 USD w sprzedaży, otrzymasz 0% rabatu, jeśli miałeś 10000 USD na sprzedaży, otrzymasz 1% zniżki, jeśli masz 50000 USD na sprzedaży, otrzymasz 5% zniżki . 100000 USD, 10% zniżki, 250000 USD, 15% zniżki, a na koniec wszystko, co> 500000 USD, otrzymuje 20% zniżki. Teraz wiele razy, za każdym razem, gdy mówię o WYSZUKAJ.PIONOWO, mówię, że każde WYSZUKAJ.PIONOWO, które kiedykolwiek utworzysz, kończy się na FAŁSZ, a ludzie powiedzą „Cóż, chwileczkę, po co w ogóle jest PRAWDA wersja?” Dotyczy to tego samego przypadku, w którym szukamy zakresu, więc szukamy tej wartości, zwykłe WYSZUKAJ.PIONOWO, oczywiście, 2, FALSE nie znajdzie 550000, zwróci # N / A!Więc w tym jednym bardzo szczególnym przypadku zmienimy FALSE na TRUE, a to powie programowi Excel „Idź, poszukaj 550000, jeśli nie możesz go znaleźć, podaj nam wartość, która jest po prostu mniejsza”. Więc daje nam 20%, to właśnie robi, w porządku? I to jest jedyny raz, kiedy twoja tabela WYSZUKAJ.PIONOWO musi zostać posortowana, za każdym razem z FALSE, może to być, jak chcesz. I tak, możesz zostawić wyłączoną TRUE, ale zawsze umieszczam ją tam, aby przypomnieć sobie, że jest to jedna z tych dziwnych, niesamowicie niebezpiecznych funkcji WYSZUKAJ.PIONOWO i nie chcę kopiować tej formuły gdzie indziej. W porządku, więc skopiujemy i wklejamy specjalne formuły, dobrze.w porządku? I to jest jedyny raz, kiedy twoja tabela WYSZUKAJ.PIONOWO musi zostać posortowana, za każdym razem z FALSE, może to być, jak chcesz. I tak, możesz zostawić wyłączoną TRUE, ale zawsze umieszczam ją tam, aby przypomnieć sobie, że jest to jedna z tych dziwnych, niesamowicie niebezpiecznych funkcji WYSZUKAJ.PIONOWO i nie chcę kopiować tej formuły gdzie indziej. W porządku, więc skopiujemy i wklejamy specjalne formuły, dobrze.w porządku? I to jest jedyny raz, kiedy twoja tabela WYSZUKAJ.PIONOWO musi zostać posortowana, za każdym razem z FALSE, może to być, jak chcesz. I tak, możesz zostawić wyłączoną TRUE, ale zawsze umieszczam ją tam, aby przypomnieć sobie, że jest to jedna z tych dziwnych, niesamowicie niebezpiecznych funkcji WYSZUKAJ.PIONOWO i nie chcę kopiować tej formuły gdzie indziej. W porządku, więc skopiujemy i wklejamy specjalne formuły, dobrze.
Następna skarga: Twój menedżer nie chce widzieć tabeli przeglądowej, chce, aby „Po prostu pozbądź się tej tabeli przeglądowej”. W porządku, możemy to zrobić, osadzając tabelę odnośników, sprawdź to, świetna sztuczka, którą dostałem od Mike'a Girvina z ExcelIsFun. F2, aby przełączyć formułę w tryb edycji, a następnie bardzo ostrożnie wybierz tylko zakres do tabeli przeglądowej. Naciśnij F9, pobiera tę tabelę i umieszcza ją w nomenklaturze tablicowej, a następnie po prostu naciskam Enter. Skopiuj to, Wklej specjalne formuły, a potem mogę pozbyć się tabeli przeglądowej, która nadal działa w dół wiersza. To ogromny problem, jeśli musisz wrócić i edytować to, musisz naprawdę patrzeć na to z dużą jasnością. Przecinek oznacza, że przechodzimy do następnej kolumny, średnik oznacza, że przechodzimy do następnego wiersza, w porządku,ale teoretycznie możesz tam wrócić i zmienić tę formułę, jeśli jedna z liczb łańcucha ulegnie zmianie.
W porządku, więc użycie funkcji WYSZUKAJ.PIONOWO zamiast zagnieżdżonej instrukcji JEŻELI to wskazówka nr 32 na naszej drodze do 40 „40 najlepszych wskazówek dotyczących programu Excel wszechczasów”, możesz mieć całą książkę. Kliknij „i” w prawym górnym rogu, 10 USD za e-book, 25 USD za drukowaną książkę, w porządku. Dlatego dzisiaj próbujemy rozwiązać problem z prowizją warstwową lub programem rabatowym. Zagnieżdżone IF są naprawdę powszechne, uważaj, 7 to wszystko, co możesz mieć w programie Excel 2003, dziś możesz mieć 32, ale nigdy nie powinieneś mieć 32. Więc kiedy użyć przybliżonej wersji MATCH funkcji WYSZUKAJ.PIONOWO, to jest to. Weź ten program rabatowy, odwróć go do góry nogami, przekształć w tabelę przeglądową, zaczynając od najmniejszej liczby i przechodząc do największej liczby. WYSZUKAJ.PIONOWO oczywiście nie znajdzie odpowiedzi, ale zmieniając WYSZUKAJ.PIONOWO na, PRAWDA na końcu, powie mu, aby znaleźć mniejszą wartość,jest to jedyny przypadek, w którym należy posortować tabelę. Jeśli nie chcesz widzieć tej tabeli, możesz osadzić ją w formule, używając sztuczki Mike'a Girvina, F2, aby edytować formułę, wybierz tabelę przeglądową, naciśnij klawisz F9, a następnie Enter.
W porządku hej, chcę ci podziękować za zatrzymanie się, do zobaczenia następnym razem z kolejnym netcastem od!
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2030.xlsx
