Haben Sie eine Excel-Formel, die mehrere IF-Funktionen verschachtelt? Wenn Sie mit zu vielen verschachtelten IF-Anweisungen auf den Punkt kommen, müssen Sie sehen, ob das Ganze mit einer einfachen VLOOKUP-Funktion einfacher wird. Ich habe gesehen, dass Formeln mit 1000 Zeichen zu einer VLOOKUP-Formel mit 30 Zeichen vereinfacht wurden. Es ist einfach zu warten, wenn Sie später neue Werte hinzufügen müssen.
Vor langer Zeit habe ich für den Vice President of Sales at Work gearbeitet. Ich habe immer ein neues Bonusprogramm oder einen neuen Provisionsplan modelliert. Ich habe mich ziemlich daran gewöhnt, Pläne mit allen möglichen Bedingungen in Auftrag zu geben. Der unten ist ziemlich zahm.
Der normale Ansatz besteht darin, eine verschachtelte IF-Formel zu erstellen. Sie beginnen immer entweder am oberen oder am unteren Ende des Bereichs. „Wenn der Umsatz über 500.000 USD liegt, beträgt der Rabatt 20%. Andernfalls… ." Das dritte Argument der IF-Funktion ist eine völlig neue IF-Funktion, die für die zweite Ebene getestet wird: Wenn der Umsatz über 250.000 USD liegt, beträgt der Rabatt 15%; Andernfalls,… "
Diese Formeln werden immer länger, je mehr Ebenen vorhanden sind. Der schwierigste Teil einer solchen Formel besteht darin, sich daran zu erinnern, wie viele schließende Klammern am Ende der Formel stehen sollen.
Mini Bonus Tipp
Übereinstimmende Klammern
Excel durchläuft für jede neue Klammerstufe eine Vielzahl von Farben. Während Excel die Farben wiederverwendet, wird Schwarz nur für die öffnende Klammer und für die übereinstimmende schließende Klammer verwendet. Wenn Sie die folgende Formel fertiggestellt haben, geben Sie einfach so lange schließende Klammern ein, bis Sie eine schwarze Klammer eingeben.
Zurück zum verschachtelten Formeltipp
Wenn Sie Excel 2003 verwenden, nähert sich Ihre Formel bereits dem Grenzwert. Damals konnten Sie nicht mehr als 7 IF-Funktionen verschachteln. Es war ein hässlicher Tag, an dem die Befugnisse den Provisionsplan änderten und Sie eine Möglichkeit brauchten, eine achte IF-Funktion hinzuzufügen. Heute können Sie 64 IF-Funktionen verschachteln. Sie sollten dies niemals tun, aber es ist schön zu wissen, dass es kein Problem gibt, 8 oder 9 zu verschachteln.
Verwenden Sie nicht die verschachtelte IF-Funktion, sondern die ungewöhnliche Verwendung für die VLOOKUP-Funktion. Wenn das vierte Argument von VLOOKUP von False in True wechselt, sucht die Funktion nicht mehr nach einer genauen Übereinstimmung.
Nun, zuerst versucht VLOOKUP, eine genaue Übereinstimmung zu finden. Wenn jedoch keine genaue Übereinstimmung gefunden wird, wird Excel in der Zeile knapp unter dem gesuchten Wert angezeigt.
Betrachten Sie die folgende Tabelle. In Zelle C13 sucht Excel nach einer Übereinstimmung für 28.355 USD in der Tabelle. Wenn 28355 nicht gefunden werden kann, gibt Excel den Rabatt zurück, der dem Wert zugeordnet ist, der nur geringer ist. In diesem Fall der Rabatt von 1% für das Niveau von 10.000 USD.
Wenn Sie die Regeln in die Tabelle in E13: F18 konvertieren, müssen Sie von der kleinsten Ebene ausgehen und zur höchsten Ebene übergehen. Obwohl dies in den Regeln nicht angegeben war, beträgt der Rabatt 0%, wenn jemand einen Umsatz von weniger als 10.000 USD erzielt. Sie müssen dies als erste Zeile in der Tabelle hinzufügen.
Vorsicht
Wenn Sie die "True" -Version von VLOOKUP verwenden, muss Ihre Tabelle aufsteigend sortiert werden. Viele Leute glauben, dass alle Nachschlagetabellen sortiert werden müssen. Eine Tabelle muss jedoch nur sortiert werden, wenn eine ungefähre Übereinstimmung erzielt wird.
Was ist, wenn Ihr Manager eine vollständig in sich geschlossene Formel wünscht und die Bonustabelle nicht rechts sehen möchte? Nach dem Erstellen der Formel können Sie die Tabelle direkt in die Formel einbetten.
Versetzen Sie die Formel in den Bearbeitungsmodus, indem Sie auf die Zelle doppelklicken oder die Zelle auswählen und F2 drücken.
Wählen Sie mit dem Cursor das gesamte zweite Argument aus: $ E $ 13: $ F $ 18.
Drücken Sie die Taste F9. Excel bettet die Nachschlagetabelle als Array-Konstante ein. In der Array-Konstante gibt ein Semikolon eine neue Zeile und ein Komma eine neue Spalte an.
Drücken Sie Enter. Kopieren Sie die Formel in die anderen Zellen.
Sie können die Tabelle jetzt löschen. Die endgültige Formel ist unten gezeigt.
Vielen Dank an Mike Girvin, der mir die passenden Klammern beigebracht hat. Die VLOOKUP-Technik wurde von Danny Mac, Boriana Petrova, Andreas Thehos und @mvmcos vorgeschlagen.
Schau Video
- Bei einem abgestuften Provisions-, Bonus- oder Rabattprogramm müssen Sie häufig Ihre IF-Funktionen verschachteln
- Das Excel 2003-Limit betrug 7 verschachtelte IF-Anweisungen.
- Sie können jetzt 32 verschachteln, aber ich denke nicht, dass Sie jemals 32 verschachteln sollten
- Wann würden Sie jemals die ungefähre Match-Version von VLOOKUP verwenden? Das ist die Zeit.
- Übersetzen Sie das Rabattprogramm in eine Nachschlagetabelle
- VLOOKUP findet in den meisten Fällen keine Antwort.
- Wenn Sie am Ende True setzen, wird VLOOKUP angewiesen, den Wert nur weniger zu finden.
- Dies ist das einzige Mal, dass die VLOOKUP-Tabelle sortiert werden muss.
- Willst du den VLOOKUP-Tisch nicht zur Seite stellen? Betten Sie es in die Formel ein.
- F2, um die Formel zu bearbeiten. Wählen Sie die Nachschlagetabelle aus. Drücken Sie F9. Eingeben.
Video-Transkript
Lernen Sie Excel aus dem Podcast, Folge 2030 - Ersetzt Nested IF durch VLOOKUP!
Ich werde das gesamte Buch podcasten. Klicken Sie auf das "i" in der oberen rechten Ecke, um zur Wiedergabeliste zu gelangen!
Hey, willkommen zurück im Netcast, ich bin Bill Jelen. Okay, das ist wirklich üblich, entweder bei einem Provisionsprogramm oder einem Rabattprogramm oder einem Bonusprogramm, bei dem wir die Stufen haben, verschiedene Stufen, oder? Wenn Sie über 10000 $ sind, erhalten Sie 1% Rabatt, 50000 $ 5% Rabatt. Sie müssen vorsichtig sein, wenn Sie diese verschachtelte IF-Anweisung erstellen. Sie beginnen am oberen Ende, wenn Sie nach mehr als suchen, oder am unteren Ende, wenn Sie nach weniger als suchen. Also B10> 50000, 20%, sonst eine andere IF, B10> 250000, 25% und so weiter und so fort. Dies ist nur eine lange und komplizierte Formel. Wenn Ihre Tabelle in Excel 2003 größer ist und Sie nicht mehr als 7 IF-Anweisungen verschachteln können, sind wir hier fast am Limit. Sie können jetzt zu 32 gehen, ich denke nicht, dass Sie jemals zu 32 gehen sollten, und selbst wenn Sie schreien: „Hey, warte,Was ist mit der neuen Funktion, die gerade in Excel 2016, der Veröffentlichung vom Februar 2016, mit der IFS-Funktion veröffentlicht wurde? “ Ja klar, hier gibt es weniger Klammern und 87 statt 97 Zeichen, es ist immer noch ein Chaos. Lassen Sie uns das loswerden und es mit einem VLOOKUP machen!
Okay, hier sind die Schritte, du nimmst diese Regeln und drehst sie auf den Kopf. Das erste, was wir zu sagen haben, ist, wenn Sie einen Umsatz von 0 USD hatten, erhalten Sie einen Rabatt von 0%, wenn Sie einen Umsatz von 10000 USD hatten, erhalten Sie einen Rabatt von 1%, wenn Sie einen Umsatz von 50000 USD haben, erhalten Sie einen Rabatt von 5% . $ 100000, 10% Rabatt, $ 250000, 15% Rabatt und schließlich bekommt alles> $ 500000 den 20% Rabatt, in Ordnung. Jetzt sage ich oft, jedes Mal, wenn ich über VLOOKUP spreche, dass jeder VLOOKUP, den Sie jemals erstellt haben, mit FALSE endet, und die Leute werden sagen: "Moment mal, wofür ist die WAHRE Version überhaupt da?" Genau für diesen Fall, in dem wir nach einem Bereich suchen, suchen wir nach diesem Wert. Ein regulärer VLOOKUP, natürlich, 2, FALSE findet 550000 nicht, gibt die # N / A zurück!In diesem ganz besonderen Fall werden wir FALSE in TRUE ändern und Excel mitteilen: "Suchen Sie nach dem 550000, wenn Sie ihn nicht finden können, geben Sie uns den Wert, der nur geringer ist." Also gibt es uns die 20%, das ist es, okay? Und dies ist das einzige Mal, dass Ihre VLOOKUP-Tabelle sortiert werden muss, die anderen Male mit FALSE, es kann sein, wie Sie wollen. Und ja, Sie könnten das WAHRE weglassen, aber ich habe es immer dort abgelegt, um mich daran zu erinnern, dass dies einer dieser seltsamen, unglaublich gefährlichen VLOOKUPs ist, und ich möchte diese Formel nicht woanders kopieren. Okay, also werden wir spezielle Formeln kopieren und einfügen, okay.in Ordung? Und dies ist das einzige Mal, dass Ihre VLOOKUP-Tabelle sortiert werden muss, die anderen Male mit FALSE, es kann sein, wie Sie wollen. Und ja, Sie könnten das WAHRE weglassen, aber ich habe es immer dort abgelegt, um mich daran zu erinnern, dass dies einer dieser seltsamen, unglaublich gefährlichen VLOOKUPs ist, und ich möchte diese Formel nicht woanders kopieren. Okay, also werden wir spezielle Formeln kopieren und einfügen, okay.in Ordung? Und dies ist das einzige Mal, dass Ihre VLOOKUP-Tabelle sortiert werden muss, die anderen Male mit FALSE, es kann sein, wie Sie wollen. Und ja, Sie könnten das WAHRE weglassen, aber ich habe es immer dort abgelegt, um mich daran zu erinnern, dass dies einer dieser seltsamen, unglaublich gefährlichen VLOOKUPs ist, und ich möchte diese Formel nicht woanders kopieren. Okay, also werden wir spezielle Formeln kopieren und einfügen, okay.
Nächste Beschwerde: Ihr Manager möchte die Nachschlagetabelle nicht sehen, er möchte sie. "Entfernen Sie einfach diese Nachschlagetabelle." Okay, wir können das tun, indem wir die Nachschlagetabelle einbetten. Schauen Sie sich das an, ein großartiger Trick, den ich von Mike Girvin bei ExcelIsFun bekommen habe. F2, um die Formel in den Bearbeitungsmodus zu versetzen, und wählen Sie dann sehr sorgfältig nur den Bereich für die Nachschlagetabelle aus. Drücken Sie F9, und es nimmt diese Tabelle und fügt sie in die Array-Nomenklatur ein, und dann drücke ich einfach so die Eingabetaste. Kopieren Sie das nach unten, fügen Sie spezielle Formeln ein, und dann kann ich die Nachschlagetabelle entfernen, damit alles in der Reihe weiter funktioniert. Dies ist ein großer Aufwand, wenn Sie zurückkommen und dies bearbeiten müssen, müssen Sie es wirklich mit viel Klarheit anstarren. Das Komma bedeutet, dass wir zur nächsten Spalte gehen, das Semikolon bedeutet, dass wir zur nächsten Zeile gehen, in Ordnung,Aber Sie könnten theoretisch wieder hineinkommen und diese Formel ändern, wenn sich eine der Kettennummern ändert.
Okay, die Verwendung eines VLOOKUP anstelle einer verschachtelten IF-Anweisung ist Tipp Nr. 32 auf dem Weg zu 40, „40 besten Excel-Tipps aller Zeiten“. Sie können dieses gesamte Buch haben. Klicken Sie auf das "i" in der oberen rechten Ecke, 10 US-Dollar für ein E-Book, 25 US-Dollar für das gedruckte Buch. Deshalb versuchen wir heute, einen gestaffelten Provisionsbonus oder ein Rabattprogramm zu lösen. Verschachtelte IFs sind sehr häufig. Achten Sie darauf, dass Sie in Excel 2003 nur 7 haben können. Heute können Sie 32 haben, aber Sie sollten nie 32 haben. Wenn Sie also die ungefähre MATCH-Version von VLOOKUP verwenden möchten, ist dies das Richtige. Nehmen Sie dieses Rabattprogramm, stellen Sie es auf den Kopf, machen Sie es zu einer Nachschlagetabelle, die mit der kleinsten Zahl beginnt und zur größten Zahl geht. VLOOKUP findet natürlich keine Antwort, aber wenn Sie VLOOKUP am Ende in TRUE ändern, wird es angewiesen, den Wert nur weniger zu finden.Dies ist das einzige Mal, dass die Tabelle sortiert werden muss. Wenn Sie diese Tabelle nicht sehen möchten, können Sie sie mit dem Mike Girvin-Trick F2 in die Formel einbetten, um die Formel zu bearbeiten, die Nachschlagetabelle auswählen, F9 drücken und dann die Eingabetaste drücken.
Okay hey, ich möchte mich bei dir für deinen Besuch bedanken, wir sehen uns beim nächsten Mal für einen weiteren Netcast von!
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2030.xlsx
