Nächste Übereinstimmung suchen
Um in einem numerischen Datensatz in Excel nach der nächstgelegenen Übereinstimmung eines Suchwerts zu suchen, können Sie die INDEX-, VERGLEICH-, ABS- und MIN-Funktionen gemeinsam verwenden.
Wie findet man die nächstgelegene Übereinstimmung in Excel?
Um herauszufinden, welcher Verkäufer den Umsatz am nächsten am Ziel von $20.000 erreicht hat, unterstützt Sie eine Formel, die die Funktionen INDEX, VERGLEICH, ABS und MIN geschickt kombiniert: Die ABS-Funktion wandelt alle Differenzen zwischen dem jeweiligen Verkäuferumsatz und dem Umsatzziel in positive Werte um. Anschließend ermittelt MIN die kleinste Differenz – also die nächstgelegene Übereinstimmung. Mithilfe der VERGLEICH-Funktion lokalisieren Sie nun die Position dieser Übereinstimmung und rufen mit INDEX den zugehörigen Wert ab.
Allgemeine Syntax
=INDEX()return_range,MATCH(MIN(ABS()))lookup_array-lookup_value)),ABS(lookup_array-lookup_value),0))
√ Hinweis: Dies ist eine Matrixformel, für deren Eingabe Sie Strg+Umschalt+Eingabedrücken müssen.
- Rückgabebereich: Der Bereich, aus dem die Kombinationsformel den Verkäufernamen zurückgeben soll. Hierbei handelt es sich um den Namensbereich.
- Suchmatrix: Der Zellbereich mit den Werten, die mit dem Suchwert verglichen werden sollen. Hier bezieht sich dies auf den Umsatzbereich.
- Suchwert: Der Wert, mit dem verglichen wird, um die nächstgelegene Übereinstimmung zu finden – in diesem Fall das Umsatzziel.
Um zu ermitteln, welcher Verkäufer den Umsatz am nächsten am Ziel von $20,000 erreicht hat, kopieren oder geben Sie bitte die folgende Formel in die Zelle F5 ein und drücken Sie Strg+Umschalt+Eingabe, um das Ergebnis zu erhalten:
=INDEX()B5:B10,VERGLEICH(MIN(ABS()))C5:C10-20000)),ABS(C5:C10-20000),0))
Alternativ können Sie Eine Zellreferenz verwenden, um die Formel dynamisch zu gestalten:
=INDEX()B5:B10,VERGLEICH(MIN(ABS()))C5:C10-F4)),ABS(C5:C10-F4),0))
Erklärung der Formel
=INDEX()B5:B10,MATCH()MIN()ABS(C5:C10-F4)),ABS(C5:C10-F4),0))
- ABS(C5:C10-F4): Der Teil C5:C10-F4 berechnet alle Differenzwerte zwischen jedem Umsatz im Bereich C5:C10 und dem Zielumsatz $20.000 in der Zelle F4 in einem Array folgender Form: {-4322;2451;6931;-1113;6591;-4782}. Die ABS-Funktion wandelt alle negativen Zahlen in positive um, sodass Folgendes entsteht: {4322;2451;6931;1113;6591;4782}.
- MIN()ABS(C5:C10-F4))=MIN(){4322;2451;6931;1113;6591;4782}):Die MIN-Funktion ermittelt die kleinste Zahl aus dem Array {4322;2451;6931;1113;6591;4782} – das ist die geringste Differenz und somit die nächstgelegene Übereinstimmung. Daher gibt die Funktion 1113 zurück.
- VERGLEICH()MIN()ABS(C5:C10-F4)),ABS(C5:C10-F4) Der Vergleichstyp 0 zwingt die VERGLEICH-Funktion, die Position der exakten Zahl 1113 im Array {4322;2451;6931;1113;6591;4782} zu finden. Die Funktion gibt 4 zurück, da sich die Zahl an der 4. Position befindet.
- INDEX()B5:B10,VERGLEICH()MIN()ABS(C5:C10-F4)),ABS(C5:C10-F4)Die INDEX-Funktion gibt den 4. Wert im Bereich B5:B10 zurück – nämlich Bale.
Zugehörige Funktionen
Die Excel-INDEX-Funktion gibt den Angezeigter Wert basierend auf einer angegebenen Position aus einem Bereich oder einem Array zurück.
Die Excel-VERGLEICH-Funktion sucht nach einem bestimmten Wert in einem Zellbereich und gibt die relative Position dieses Werts zurück.
Die ABS-Funktion gibt den Absoluter Wert einer Zahl zurück. Negative Zahlen werden durch diese Funktion in positive Zahlen umgewandelt, während positive Zahlen und Null unverändert bleiben.
Zugehörige Formeln
Nächste Übereinstimmung mit mehreren Kriterien suchen
In einigen Fällen müssen Sie möglicherweise den nächsten oder ungefähren Übereinstimmungswert basierend auf mehr als einem Kriterium suchen. Mit der Kombination der Funktionen INDEX, VERGLEICH und WENN können Sie dies in Excel schnell erreichen.
Ungefähre Übereinstimmung mit INDEX und VERGLEICH
Es gibt Situationen, in denen wir in Excel ungefähre Übereinstimmungen finden müssen, um beispielsweise die Leistung von Mitarbeitern zu bewerten, Schülerergebnisse zu benoten oder Portokosten basierend auf dem Gewicht zu berechnen. In diesem Tutorial erläutern wir, wie Sie mithilfe der Funktionen INDEX und VERGLEICH die gewünschten Ergebnisse abrufen können.
Nächste Übereinstimmung mit mehreren Kriterien suchen
In einigen Fällen müssen Sie möglicherweise den nächsten oder ungefähren Übereinstimmungswert basierend auf mehr als einem Kriterium suchen. Mit der Kombination der Funktionen INDEX, VERGLEICH und WENN können Sie dies in Excel schnell erreichen.
Die besten Office-Produktivitätswerkzeuge
Kutools für Excel – Hilft Ihnen, sich von der Masse abzuheben
Kutools für Excel bietet über 300 Funktionen und stellt sicher, dass das, was Sie benötigen, nur einen Klick entfernt ist...
Office Tab – Aktiviert tabbasiertes Lesen und Bearbeiten in Microsoft Office (inklusive Excel)
- Mit einem Klick zwischen Dutzenden geöffneter Dokumente wechseln!
- Reduziert täglich Hunderte Mausklicks für Sie – verabschieden Sie sich von der Maus-Hand.
- Steigert Ihre Produktivität um 50 %, wenn Sie mehrere Dokumente gleichzeitig anzeigen und bearbeiten.
- Bringt effiziente Tabs in Office (inklusive Excel) – genau wie bei Chrome, Edge und Firefox.