Excel-Funktionen können sehr mächtig sein, wenn Sie verschiedene Formeln kombinieren können. Dinge, die unmöglich schienen, würden plötzlich wie ein Kinderspiel aussehen.
Ein solches Beispiel ist das Finden der engsten Übereinstimmung eines Nachschlagewerts in einem Dataset in Excel.
Es gibt einige nützliche Nachschlagefunktionen in Excel (wie SVERWEIS & INDEX ÜBEREINSTIMMUNG), die in einigen einfachen Fällen die beste Übereinstimmung finden können (wie ich mit Beispielen unten zeigen werde).
Aber das Beste daran ist, dass Sie diese Nachschlagefunktionen mit anderen Excel-Funktionen kombinieren können, um viel mehr zu tun (einschließlich der Suche nach der nächsten Übereinstimmung eines Nachschlagewerts in einer unsortierten Liste).
In diesem Tutorial zeige ich Ihnen, wie Sie mit Nachschlageformeln die beste Übereinstimmung eines Nachschlagewerts in Excel finden.
Finden Sie die beste Übereinstimmung in Excel
Es kann viele verschiedene Szenarien geben, in denen Sie nach der nächsten Übereinstimmung (oder dem nächstgelegenen Übereinstimmungswert) suchen müssen.
Im Folgenden sind die Beispiele, die ich in diesem Artikel behandeln werde:
- Finden Sie den Provisionssatz basierend auf dem Umsatz
- Finden Sie den besten Kandidaten (basierend auf der engsten Erfahrung)
- Den nächsten Veranstaltungstermin finden
Lass uns anfangen!
Klicken Sie hier, um die Beispieldatei herunterzuladen
Provisionssatz ermitteln (Suche nach dem nächstgelegenen Verkaufswert)
Angenommen, Sie haben einen Datensatz wie unten gezeigt, in dem Sie die Provisionssätze aller Vertriebsmitarbeiter finden möchten.
Die Provision wird basierend auf dem Verkaufswert zugewiesen. Und dies wird anhand der Tabelle rechts berechnet.
Wenn ein Verkäufer beispielsweise den Gesamtumsatz von 5000 tätigt, beträgt die Provision 0 % und wenn er/sie den Gesamtumsatz von 15000 tätigt, beträgt die Provision 5 %.
Um den Provisionssatz zu erhalten, müssen Sie den nächstgelegenen Verkaufsbereich finden, der knapp unter dem Verkaufswert liegt. Bei einem Verkaufswert von 15.000 beträgt die Provision beispielsweise 10.000 (das sind 5%) und für einen Verkaufswert von 25.000 beträgt der Provisionssatz 20.000 (das sind 7%).
Um den nächstgelegenen Verkaufswert zu finden und den Provisionssatz zu erhalten, können Sie die ungefähre Übereinstimmung in SVERWEIS verwenden.
Die folgende Formel würde dies tun:
=SVERWEIS(B2,$E$2:$F$6,2,1)
Beachten Sie, dass das letzte Argument in dieser Formel 1 ist, was die Formel anweist, eine ungefähre Suche zu verwenden. Dies bedeutet, dass die Formel die Verkaufswerte in Spalte E durchläuft und den Wert findet, der knapp unter dem Nachschlagewert liegt.
Dann gibt die SVERWEIS-Formel den Provisionssatz für diesen Wert an.
Notiz: Damit dies funktioniert, müssen die Daten aufsteigend sortiert sein.Klicken Sie hier, um die Beispieldatei herunterzuladen
Finden Sie den besten Kandidaten (basierend auf der engsten Erfahrung)
Im obigen Beispiel mussten die Daten in aufsteigender Reihenfolge sortiert werden. Es kann jedoch Fälle geben, in denen die Daten nicht sortiert sind.
Lassen Sie uns also ein Beispiel behandeln und sehen, wie wir mit einer Kombination von Formeln die beste Übereinstimmung in Excel finden können.
Unten ist ein Beispieldatensatz, in dem ich den Mitarbeiternamen finden muss, dessen Berufserfahrung dem gewünschten Wert am nächsten kommt. Der gewünschte Wert in diesem Fall in 2,5 Jahren.
Beachten Sie, dass die Daten nicht sortiert sind. Außerdem kann die nächste Erfahrung entweder weniger oder mehr als die Geben-Erfahrung sein. Zum Beispiel liegen 2 Jahre und 3 Jahre gleich nahe beieinander (Differenz von 0,5 Jahren).
Unten 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 in dieser Formel besteht darin, das Lookup-Array und den Lookup-Wert zu ändern, um den minimalen Erfahrungsunterschied zwischen erforderlichen und tatsächlichen Werten zu finden.
Lassen Sie uns zuerst verstehen, wie Sie es manuell tun 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 in der Erfahrung zwischen dem, was erforderlich ist, und dem, den eine Person hat. Sobald Sie alle Unterschiede haben, finden Sie den Mindestwert und rufen den Namen dieser Person ab.
Genau das machen wir mit dieser Formel.
Lassen Sie mich erklären.
Der Nachschlagewert in der MATCH-Formel ist MIN(ABS(D2-B2:B15)).
Dieser Teil gibt Ihnen den minimalen Unterschied zwischen der angegebenen Erfahrung (die 2,5 Jahre beträgt) und allen anderen Erfahrungen. In diesem Beispiel gibt es 0.3 . zurück
Beachten Sie, dass ich ABS verwendet habe, um sicherzustellen, dass ich nach der nächsten suche (die mehr oder weniger als die angegebene Erfahrung sein kann).
Dieser Mindestwert wird nun zu unserem Nachschlagewert.
Das Lookup-Array in der MATCH-Funktion ist ABS(D2-$B$2:$B$15).
Dies gibt uns eine Reihe von Zahlen, von denen 2,5 (die erforderliche Erfahrung) abgezogen wurde.
Jetzt haben wir also einen Lookup-Wert (0,3) und ein Lookup-Array ({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 die Position 0,3 in diesem Array, die auch die Position des Namens der Person ist, die die engste Erfahrung hat.
Diese Positionsnummer wird dann von der INDEX-Funktion verwendet, um den Namen der Person zurückzugeben.
Hinweis: Falls es mehrere Kandidaten mit der gleichen Mindesterfahrung gibt, gibt die obige Formel den Namen des ersten passenden Mitarbeiters an.
Finden Sie das nächste Veranstaltungsdatum
Dies ist ein weiteres Beispiel, bei dem Sie Suchformeln 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 will, ist der Name der nächsten Veranstaltung und das Veranstaltungsdatum für diese bevorstehende Veranstaltung.
Unten ist die Formel, die den Namen des bevorstehenden Ereignisses ergibt:
=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
Und die folgende Formel gibt das bevorstehende Ereignisdatum 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 nach dem aktuellen Datum in Spalte B. In diesem Fall suchen wir nicht nach einer genauen Übereinstimmung, sondern nach einer ungefähren Übereinstimmung. Daher ist das letzte Argument der MATCH-Funktion 1 (die den größten Wert findet, der kleiner oder gleich dem Lookup-Wert ist).
Die MATCH-Funktion würde also die Position der Zelle zurückgeben, deren Datum gerade kleiner oder gleich dem aktuellen Datum ist. Das nächste Ereignis wäre in diesem Fall also in der nächsten Zelle (da die Liste in aufsteigender Reihenfolge sortiert ist).
Um 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.
Um den Ereignisnamen zu erhalten, wird dieselbe Formel verwendet und der Bereich in der INDEX-Funktion wird von Spalte B in Spalte A geändert.
Klicken Sie hier, um die Beispieldatei herunterzuladen
Die Idee zu diesem Beispiel kam mir, als sich ein Freund mit einer Bitte näherte. Er hatte eine Liste aller Geburtstage seiner Freunde/Verwandten in einer Spalte und wollte den nächsten Geburtstag wissen (und den Namen der Person).Dies sind drei Beispiele, die zeigen, wie Sie mithilfe von Nachschlageformeln den am besten passenden Wert in Excel finden.
Die folgenden Excel-Tipps/Tutorials könnten Ihnen auch gefallen
- Holen Sie sich die letzte Nummer aus einer Liste mit der SVERWEIS-Funktion.
- Erhalten Sie mehrere Lookup-Werte ohne Wiederholung in einer einzelnen Zelle.
- SVERWEIS vs. INDEX/SPIEL
- Excel INDEX MATCH
- Finden Sie das letzte Vorkommen eines Nachschlagewerts in einer Liste in Excel
- Duplikate in Excel suchen und entfernen
- Bedingte Formatierung.
