Excel-Funktionen können sehr leistungsfähig sein, wenn Sie den Dreh raus haben, verschiedene Formeln zu kombinieren.Was einst unmöglich schien, kann plötzlich wie ein Kinderspiel werden.
Ein solches Beispiel ist die Suche nach dem nächstgelegenen Wert in einem Excel-Datensatz.
Es gibt mehrere nützliche Nachschlagefunktionen in Excel (z. B. SVERWEIS und INDEXVERGLEICH), die den nächsten finden können值(wie im Beispiel unten gezeigt).
Aber das Beste daran ist, dass Sie diese Nachschlagefunktionen mit anderen Excel-Funktionen kombinieren können, um so viel mehr zu tun (einschließlich der Suche nach der besten Übereinstimmung mit einem Nachschlagewert in einer unsortierten Liste).
In diesem Lernprogramm zeige ich Ihnen, wie Sie eine Nachschlageformel verwenden, um die beste Übereinstimmung mit einem Nachschlagewert in Excel zu finden.
Es gibt viele verschiedene Szenarien, in denen Sie die engste Übereinstimmung (oder den engsten Übereinstimmungswert) finden müssen.
Hier ist ein Beispiel, das ich in diesem Artikel behandeln werde:
- Finden Sie Provisionssätze basierend auf Verkäufen
- Finden Sie den besten Kandidaten (basierend auf der engsten Erfahrung)
- Finden Sie den nächsten Veranstaltungstermin
Lasst uns beginnen!
Inhalte
Provisionssätze finden (nach dem nächstgelegenen Verkaufswert suchen)
Angenommen, Sie haben einen Datensatz wie den folgenden, in dem Sie die Provisionssätze für alle Verkäufer finden möchten.
Provisionen werden basierend auf dem Verkaufswert verteilt.Diese wird anhand der Tabelle rechts berechnet.
Wenn der Gesamtumsatz des Verkäufers beispielsweise 5000 beträgt, beträgt die Provision 0 %, und wenn sein Gesamtumsatz 15000 beträgt, beträgt die Provision 5 %.
Um einen Provisionssatz zu erhalten, müssen Sie den nächstgelegenen Verkaufsbereich finden, der knapp unter dem Verkaufswert liegt.Beispielsweise beträgt die Provision für 15000 Verkäufe 10,000 (oder 5 %) und für 25000 Verkäufe 20,000 (oder 7 %).
Um den nächstgelegenen Verkaufswert zu finden und einen Provisionssatz zu erhalten, können Sie den ungefähren Abgleich in VLOOKUP verwenden.
Die folgende Formel kann dies tun:
= VLOOKUP (B2, $ E $ 2: $ F $ 6,2,1)
Beachten Sie, dass in dieser Formel der letzte Parameter 1 ist, was der Formel mitteilt, die ungefähre Suche zu verwenden.Das bedeutet, dass die Formel den Verkaufswert in Spalte E durchgeht und den Wert direkt unter dem Nachschlagewert findet.
Die VLOOKUP-Formel gibt dann den Provisionssatz für diesen Wert an.
注意: Dazu müssen Sie die Daten in aufsteigender Reihenfolge sortieren.
Finden Sie den besten Kandidaten (basierend auf der engsten Erfahrung)
Im obigen Beispiel müssen die Daten in aufsteigender Reihenfolge sortiert werden.Es kann jedoch Fälle geben, in denen die Daten nicht sortiert sind.
Nehmen wir also ein Beispiel und sehen, wie wir Formelkombinationen verwenden können, um die beste Übereinstimmung in Excel zu finden.
Unten ist ein Beispieldatensatz, in dem ich die Namen von Mitarbeitern finden muss, deren Berufserfahrung dem gewünschten Wert am nächsten kommt.Der Erwartungswert beträgt in diesem Fall 2.5 Jahre.
Beachten Sie, dass die Daten nicht sortiert sind.Darüber hinaus können die engsten Erfahrungen weniger oder mehr als eine gegebene Erfahrung sein.Beispielsweise sind 2 Jahre und 3 Jahre gleich nah (0.5 Jahre Unterschied).
Hier ist die Formel, die uns das Ergebnis liefert:
=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
Der Trick bei dieser Formel besteht darin, das Lookup-Array und die Lookup-Werte zu ändern, um die kleinste empirische Differenz zwischen den gewünschten und den tatsächlichen Werten zu finden.
Lassen Sie uns zuerst verstehen, wie Sie es manuell machen würden (und dann werde ich erklären, wie diese Formel funktioniert).
Wenn Sie dies manuell tun, gehen Sie jede Zelle in Spalte B durch und finden den Unterschied zwischen der gewünschten Erfahrung und der Erfahrung, die Sie haben.Sobald Sie alle Unterschiede haben, finden Sie den kleinsten und erhalten den Namen dieser Person.
Genau das tun wir mit dieser Formel.
Lassen Sie mich erklären.
Der Suchwert in der MATCH-Formel ist MIN(ABS(D2-B2:B15)).
Dieser Abschnitt gibt Ihnen den Mindestunterschied zwischen der angegebenen Erfahrung (dh 2.5 Jahre) und allen anderen Erfahrungen an.In diesem Fall gibt es 0.3 zurück
Beachten Sie, dass ich ABS verwende, um sicherzustellen, dass ich nach dem nächsten suche (wahrscheinlich mehr oder weniger als die gegebene Erfahrung).
Jetzt wird dieser Mindestwert zu unserem Nachschlagewert.
Das Sucharray in der MATCH-Funktion ist ABS(D2-$B$2:$B$15).
Dies gibt uns eine Reihe von Zahlen, von denen 2.5 (Erfahrung erforderlich) abgezogen wird.
所以現在我們有一個查找值 (0.3) 和一個查找數組 ({6.8;0.8;19.5;21.8;14.5;11.2;0.3;9.2;2;9.8;14.8;0.4;23.8;2.9})
Die MATCH-Funktion findet in diesem Array die Position 0.3, die die Position des Namens der Person mit der größten Erfahrung ist.
Die INDEX-Funktion verwendet dann die Jobnummer, um den Namen der Person zurückzugeben.
Hinweis: Wenn es mehrere Kandidaten mit derselben Mindesterfahrung gibt, ergibt die obige Formel den Namen des ersten passenden Mitarbeiters.
Finden Sie den nächsten Veranstaltungstermin
Hier ist ein weiteres Beispiel, bei dem Sie eine Suchformel verwenden können, um das nächste Datum eines Ereignisses basierend auf dem aktuellen Datum zu finden.
Unten ist der Datensatz, in dem ich Ereignisnamen und Ereignisdaten habe.
Was ich möchte, ist der Name des nächsten Ereignisses und das Ereignisdatum dieses bevorstehenden Ereignisses.
Hier ist die Formel, die den Namen des bevorstehenden Ereignisses angibt:
=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
Die folgende Formel gibt die bevorstehenden Veranstaltungstermine an:
=INDEX($B$2:$B$11,MATCH(E1,$B$2:$B$11,1)+1)
Lassen Sie mich erklären, wie diese Formel funktioniert.
Um das Ereignisdatum zu erhalten, sucht die MATCH-Funktion in Spalte B nach dem aktuellen Datum.In diesem Fall suchen wir nicht nach einer genauen Übereinstimmung, sondern nach einer ungefähren Übereinstimmung.Daher ist der letzte Parameter der MATCH-Funktion 1 (es findet den größten Wert, der kleiner oder gleich dem Suchwert ist).
Die MATCH-Funktion gibt also die Position der Zelle zurück, deren Datum kleiner oder gleich dem aktuellen Datum ist.In diesem Fall steht also das nächste Ereignis in der nächsten Zelle (da die Liste aufsteigend sortiert ist).
Um also das bevorstehende Ereignisdatum zu erhalten, fügen Sie einfach eins zu der von der MATCH-Funktion zurückgegebenen Zellenposition hinzu, und Sie erhalten die Zellenposition des nächsten Ereignisdatums.
Dieser Wert wird dann von der INDEX-Funktion angegeben.
Verwenden Sie zum Abrufen des Ereignisnamens dieselbe Formel und ändern Sie den Bereich in der INDEX-Funktion von Spalte B in Spalte A.
Dieses Beispiel kam mir in den Sinn, als ein Freund eine Anfrage stellte.Er listet die Geburtstage all seiner Freunde/Verwandten in einer Spalte auf und möchte wissen, wann der nächste Geburtstag ansteht (und wie die Person heißt).
Die obigen drei Beispiele zeigen, wie Sie die Nachschlageformel verwenden, um den nächsten Wert in Excel zu finden.
