Vor langer Zeit arbeitete ich für den Vice President of Sales in einem Unternehmen. 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 in diesem Tipp gezeigte 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 testet: "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.
Wenn Sie Excel 2003 verwenden, nähert sich Ihre Formel bereits dem Grenzwert. Mit dieser Version können 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 niemals so viele verschachteln, 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 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 nur 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 mit dem Wert verknüpft ist, der in diesem Fall nur geringer ist, nämlich den Rabatt von 1% für die Stufe 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 für eine ungefähre Übereinstimmung sortiert werden.
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. Verwenden Sie den Cursor, um das gesamte zweite Argument auszuwählen: $ 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. Siehe Grundlegendes zu Array-Konstanten.
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.
