Nächstgelegenes Ergebnis suchen
Um das nächstgelegene Ergebnis eines Suchwerts in einem numerischen Datensatz in Excel zu finden, können Sie die Funktionen INDEX, VERGLEICH, ABS, und MIN zusammen verwenden.
Wie finde ich das nächstgelegene Ergebnis in Excel?
Um herauszufinden, welcher Verkäufer den Umsatz am nächsten an dem Ziel von 20.000 USD gebracht hat, wie oben gezeigt, hilft eine Formel, die die Funktionen INDEX, VERGLEICH, ABS und MIN kombiniert: Die ABS-Funktion wandelt alle Differenzwerte zwischen dem Umsatz jedes Verkäufers und dem Umsatzziel in positive Werte um, dann findet MIN den kleinsten Unterschied, was dem nächstgelegenen Treffer entspricht. Nun können wir die VERGLEICH-Funktion verwenden, um die Position des nächstgelegenen Treffers zu lokalisieren und mit INDEX den Wert an der entsprechenden Position abzurufen.
Allgemeine Syntax
=INDEX(Rückgabebereich;VERGLEICH(MIN(ABS(Suchbereich-Suchwert));ABS(Suchbereich-Suchwert);0))
√ Hinweis: Dies ist eine Array-Formel, die Sie mit Strg + Umschalt + Enter eingeben müssen.
- Rückgabebereich: Der Bereich, aus dem die Kombinationsformel den Verkäufer zurückgeben soll. Hier bezieht es sich auf den Namenbereich.
- Suchbereich: Der Zellbereich mit den Werten, die mit dem Suchwert verglichen werden sollen. Hier bezieht es sich auf den Umsatzbereich.
- Suchwert: Der Wert, mit dem verglichen wird, um sein nächstgelegenes Gegenstück zu finden. Hier bezieht es sich auf das Umsatzziel.
Um herauszufinden, welcher Verkäufer den Umsatz am nächsten an dem Ziel von 20.000 USD gebracht hat, kopieren Sie bitte die folgende Formel in die Zelle F5 oder geben Sie sie ein, und drücken Sie Strg + Umschalt + Enter, um das Ergebnis zu erhalten:
=INDEX(B5:B10;VERGLEICH(MIN(ABS(C5:C10-20000));ABS(C5:C10-20000);0))
Oder verwenden Sie einen Zellbezug, 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;VERGLEICH(MIN(ABS(C5:C10-F4));ABS(C5:C10-F4);0))
- ABS(C5:C10-F4): Der Teil C5:C10-F4 erhält alle Differenzwerte zwischen jedem Umsatz im Bereich C5:C10 und dem Zielumsatz von 20.000 USD in Zelle F4 in einem Array wie diesem: {-4322;2451;6931;-1113;6591;-4782}. Die ABS-Funktion wandelt alle negativen Zahlen in positive um, sodass das Array so aussieht: {4322;2451;6931;1113;6591;4782}.
- MIN(MIN(ABS(C5:C10-F4))) = MIN(MIN({4322;2451;6931;1113;6591;4782})): Die MIN-Funktion findet die kleinste Zahl aus dem Array {4322;2451;6931;1113;6591;4782}, was dem kleinsten Unterschied oder dem nächstgelegenen Treffer entspricht. Daher gibt die Funktion 1113 zurück.
- VERGLEICH(MIN(ABS(C5:C10-F4));ABS(C5:C10-F4);0) = VERGLEICH(1113;{4322;2451;6931;1113;6591;4782};0): Der Übereinstimmungstyp 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 die Zahl an der 4. Position steht.
- INDEX(B5:B10B5:B10,,,VERGLEICH(MIN(ABS(C5:C10-F4)ABS(C5:C10-F4))),,,ABS(C5:C10-F4)ABS(C5:C10-F4);0))) = INDEX(B5:B10B5:B10,,,4): Die INDEX-Funktion gibt den 4. Wert im Namenbereich B5:B10 zurück, welcher Bale ist.
Zugehörige Funktionen
Die Excel INDEX-Funktion gibt den angezeigten Wert basierend auf einer bestimmten 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 des Werts zurück.
Die ABS-Funktion gibt den absoluten Wert einer Zahl zurück. Negative Zahlen werden mit dieser Funktion in positive Zahlen umgewandelt, während positive Zahlen und Null unverändert bleiben.
Zugehörige Formeln
Nächstgelegenes Ergebnis mit mehreren Kriterien suchen
In einigen Fällen müssen Sie möglicherweise das nächstgelegene oder ungefähre Ergebnis basierend auf mehr als einem Kriterium suchen. Mit der Kombination der Funktionen INDEX, VERGLEICH und WENN können Sie dies schnell in Excel erledigen.
Ungefähre Übereinstimmung mit INDEX und VERGLEICH
Es gibt Zeiten, in denen wir in Excel ungefähre Übereinstimmungen finden müssen, um die Leistung von Mitarbeitern zu bewerten, die Noten von Schülern zu berechnen, die Portokosten basierend auf dem Gewicht zu berechnen usw. In diesem Tutorial sprechen wir darüber, wie man die Funktionen INDEX und VERGLEICH verwendet, um die benötigten Ergebnisse abzurufen.
Nächstgelegenes Ergebnis mit mehreren Kriterien suchen
In einigen Fällen müssen Sie möglicherweise das nächstgelegene oder ungefähre Ergebnis basierend auf mehr als einem Kriterium suchen. Mit der Kombination der Funktionen INDEX, VERGLEICH und WENN können Sie dies schnell in Excel erledigen.
Die besten Tools zur Büroproduktivität
Kutools für Excel - Hilft Ihnen, aus der Menge hervorzustechen
Kutools für Excel bietet über 300 Funktionen, sodass das, was Sie benötigen, nur einen Klick entfernt ist...
Office Tab - Aktiviert tabbed Lesen und Bearbeiten in Microsoft Office (inklusive Excel)
- In einer Sekunde zwischen Dutzenden offener Dokumente wechseln!
- Reduziert Hunderte von Mausklicks für Sie jeden Tag, verabschieden Sie sich von der Maushand.
- Steigert Ihre Produktivität um 50 % beim Anzeigen und Bearbeiten mehrerer Dokumente.
- Bringt effiziente Tabs ins Office (inklusive Excel), genau wie bei Chrome, Edge und Firefox.