Inhalte
In der Praxis gibt es sehr oft Fälle, in denen Sie und ich den nächsten Wert in einer Menge (Tabelle) in Bezug auf eine bestimmte Zahl finden müssen. Es könnte zum Beispiel sein:
- Rabattberechnung nach Volumen.
- Berechnung der Höhe der Boni in Abhängigkeit von der Umsetzung des Plans.
- Berechnung der Versandkosten je nach Entfernung.
- Auswahl geeigneter Behälter für Waren etc.
Darüber hinaus kann je nach Situation sowohl auf- als auch abgerundet werden.
Es gibt mehrere Möglichkeiten – offensichtliche und nicht so offensichtliche – um ein solches Problem zu lösen. Schauen wir sie uns der Reihe nach an.
Stellen wir uns zunächst einen Lieferanten vor, der Rabatte auf den Großhandel gewährt, und der Prozentsatz des Rabatts hängt von der Menge der gekauften Waren ab. Zum Beispiel gibt es beim Kauf von mehr als 5 Stück 2 % Rabatt, und beim Kauf von 20 Stück – schon 6 % usw.
Wie berechnet man schnell und schön den Rabattprozentsatz bei der Eingabe der Menge der gekauften Waren?
Methode 1: Verschachtelte IFs
Eine Methode aus der Reihe „Was gibt es zu denken – Du musst springen!“. Verschachtelte Funktionen verwenden IF (WENN) um nacheinander zu prüfen, ob der Zellenwert in jedes der Intervalle fällt, und einen Rabatt für den entsprechenden Bereich anzuzeigen. Aber die Formel kann sich in diesem Fall als sehr umständlich herausstellen:
Ich denke, es ist offensichtlich, dass das Debuggen einer solchen „Monsterpuppe“ oder der Versuch, ihr nach einiger Zeit ein paar neue Bedingungen hinzuzufügen, Spaß macht.
Darüber hinaus hat Microsoft Excel ein Verschachtelungslimit für die IF-Funktion – 7-mal in älteren Versionen und 64-mal in neueren Versionen. Was ist, wenn Sie mehr brauchen?
Methode 2. SVERWEIS mit Intervallansicht
Diese Methode ist viel kompakter. Um den Rabattprozentsatz zu berechnen, verwenden Sie die legendäre Funktion VPR (SVERWEIS) im ungefähren Suchmodus:
woher
- B4 – der Wert der Warenmenge in der ersten Transaktion, für die wir einen Rabatt suchen
- $G$4:$H$8 – ein Link zur Rabatttabelle – ohne „Kopfzeile“ und mit den mit dem $-Zeichen fixierten Adressen.
- 2 — die Ordnungszahl der Spalte in der Rabatttabelle, aus der wir den Rabattwert erhalten möchten
- TRUE – hier ist der „Hund“ begraben. If als letztes Funktionsargument VPR angeben LÜGNERISCH (FALSCH) oder 0, dann sucht die Funktion strenge Übereinstimmung in der Mengenspalte (und in unserem Fall wird ein #NV-Fehler ausgegeben, da es keinen Wert 49 in der Rabatttabelle gibt). Aber wenn stattdessen LÜGNERISCH schreiben TRUE (WAHR) oder 1, dann sucht die Funktion nicht nach dem genauen, sondern nächst kleinste Wert und geben uns den Prozentsatz des Rabatts, den wir benötigen.
Der Nachteil dieser Methode ist die Notwendigkeit, die Rabatttabelle in aufsteigender Reihenfolge nach der ersten Spalte zu sortieren. Wenn es keine solche Sortierung gibt (oder in umgekehrter Reihenfolge erfolgt), funktioniert unsere Formel nicht:
Dementsprechend kann dieser Ansatz nur verwendet werden, um den nächsten kleinsten Wert zu finden. Wenn Sie den nächstgrößten finden müssen, müssen Sie einen anderen Ansatz verwenden.
Methode 3. Finden des nächstgrößten mit den Funktionen INDEX und MATCH
Betrachten wir unser Problem nun von der anderen Seite. Angenommen, wir verkaufen mehrere Modelle von Industriepumpen mit unterschiedlichen Kapazitäten. Die Verkaufstabelle links zeigt die vom Kunden benötigte Leistung. Wir müssen eine Pumpe mit der nächsten maximalen oder gleichen Leistung auswählen, aber nicht weniger als für das Projekt erforderlich.
Die VLOOKUP-Funktion hilft hier nicht, also müssen Sie ihr Analogon verwenden – eine Reihe von INDEX-Funktionen (INDEX) und MEHR AUSGESETZT (PASSEN):
Hier arbeitet die MATCH-Funktion mit dem letzten Argument -1 im Modus, den nächstgrößten Wert zu finden, und die INDEX-Funktion extrahiert dann den von uns benötigten Modellnamen aus der angrenzenden Spalte.
Methode 4. Neue Funktion VIEW (XLOOKUP)
Wenn Sie eine Version von Office 365 mit allen installierten Updates haben, dann statt SVERWEIS (SVERWEIS) Sie können sein Analogon verwenden – die VIEW-Funktion (XVERWEIS), die ich bereits ausführlich analysiert habe:
Hier:
- B4 – der anfängliche Wert der Menge des Produkts, für das wir einen Rabatt suchen
- $G$4:$G$8 – der Bereich, in dem wir nach Streichhölzern suchen
- $H$4:$H$8 – den Ergebnisbereich, für den Sie den Rabatt zurückgeben möchten
- viertes Argument (-1) beinhaltet die Suche nach der nächsten kleinsten gewünschten Zahl anstelle einer exakten Übereinstimmung.
Die Vorteile dieser Methode liegen darin, dass die Rabatttabelle nicht sortiert werden muss und bei Bedarf nicht nur nach dem nächstkleineren, sondern auch nach dem nächstgrößten Wert gesucht werden kann. Das letzte Argument ist in diesem Fall 1.
Aber leider hat noch nicht jeder dieses Feature – nur glückliche Besitzer von Office 365.
Methode 5. Power-Abfrage
Wenn Sie mit dem leistungsstarken und völlig kostenlosen Power Query-Add-In für Excel noch nicht vertraut sind, dann sind Sie hier. Wenn Sie bereits vertraut sind, versuchen wir, damit unser Problem zu lösen.
Lassen Sie uns zunächst einige Vorarbeiten leisten:
- Konvertieren wir unsere Quelltabellen mithilfe einer Tastenkombination in dynamisch (intelligent). Ctrl+T oder Team Startseite – Als Tabelle formatieren (Home — Als Tabelle formatieren).
- Geben wir ihnen zur Verdeutlichung Namen. Sales и CityClass Rabatte Tab Bauherr (Design).
- Laden Sie jede der Tabellen der Reihe nach über die Schaltfläche in Power Query Aus Tabelle/Bereich Tab Datum (Daten — Aus Tabelle/Bereich). In neueren Versionen von Excel wurde diese Schaltfläche in umbenannt Mit Blättern (Vom Blatt).
- Wenn die Tabellen unterschiedliche Spaltennamen mit Mengen haben, wie in unserem Beispiel („Warenmenge“ und „Menge von …“), dann müssen sie in Power Query umbenannt und gleich benannt werden.
- Danach können Sie zu Excel zurückkehren, indem Sie den Befehl im Power Query-Editorfenster auswählen Home — Schließen und laden — Schließen und laden in… (Home — Schließen&Laden — Schließen&Laden nach…) und dann Option Stellen Sie einfach eine Verbindung her (Nur Verbindung herstellen).
- Dann beginnt das Interessanteste. Wenn Sie Erfahrung mit Power Query haben, dann gehe ich davon aus, dass der weitere Gedankengang in die Richtung gehen sollte, diese beiden Tabellen mit einer Join-Abfrage (Merge) a la SVERWEIS zusammenzuführen, wie es bei der vorigen Methode der Fall war. Tatsächlich müssen wir im Add-Modus zusammenführen, was auf den ersten Blick überhaupt nicht offensichtlich ist. Wählen Sie im Excel-Tab Daten – Daten abrufen – Anforderungen kombinieren – Hinzufügen (Daten – Daten abrufen – Abfragen kombinieren – Anhängen) und dann unsere Tische Sales и CityClass Rabatte im erscheinenden Fenster:
- Nach dem Klicken auf OK Unsere Tische werden zu einem Ganzen verklebt – untereinander. Bitte beachten Sie, dass die Spalten mit der Warenmenge in diesen Tabellen untereinander fallen, weil. Sie haben denselben Namen:
- Wenn Ihnen die ursprüngliche Zeilenreihenfolge der Umsatztabelle wichtig ist, dann fügen Sie unserer Tabelle mit dem Befehl eine nummerierte Spalte hinzu, um sie nach allen nachfolgenden Transformationen wiederherstellen zu können Hinzufügen einer Spalte – Indexspalte (Spalte hinzufügen – Indexspalte). Wenn Ihnen die Reihenfolge der Zeilen egal ist, können Sie diesen Schritt überspringen.
- Sortieren Sie die Tabelle nun mithilfe der Dropdown-Liste in der Kopfzeile der Tabelle nach Spalten quantity Aufsteigend:
- Und der Haupttrick: Klicken Sie mit der rechten Maustaste auf die Spaltenüberschrift Rabatt wähle ein Team Abfüllen (Abfüllen). Leere Zellen mit null automatisch mit den bisherigen Rabattwerten ausgefüllt:
- Es bleibt, die ursprüngliche Reihenfolge der Zeilen durch Sortieren nach Spalten wiederherzustellen Index (Sie können es später sicher löschen) und entfernen Sie unnötige Zeilen mit einem Filter null nach Spalte Transaktionscode:
- Verwenden der SVERWEIS-Funktion zum Suchen und Nachschlagen von Daten
- Bei der Verwendung von SVERWEIS (SVERWEIS) wird zwischen Groß- und Kleinschreibung unterschieden
- XNUMXD SVERWEIS (SVERWEIS)