Rückgabe eines Werts, der NÄCHSTE ist, an einen Wert, der in einem großen Datenbereich gefunden wird

524
r.phill

Ich habe eine Spalte, A1:A20deren Werte ich in einer großen Datentabelle finden muss.

Für die Zwecke dieses Beispiels sage ich, dass die Datentabelle von reicht D1:Z25.

Im Idealfall hätte ich eine Excel-Formel, die angibt, ob der genaue Wert aus der Spalte Ain der Tabelle enthalten ist. Wenn nicht, geben Sie den Wert CLOSEST an die Tabelle zurück .

Ich kann die Formel verwenden

=IF(SUM(IF(D1:Z25=$A1,1,0))>=1,$A1,"")+ ctrl, shift, enterUnd es wird die Rückkehr EXACT Wert, wenn sie in der Tabelle befindet.

Meine Frage ist: Wenn sich der EXACT- Wert nicht in der Tabelle befindet, gibt es eine Möglichkeit, den CLOSEST- Wert an ihn zurückzugeben?

Ich möchte keine Rundung verwenden, da meine Werte genau sein müssen.

1
Mit engstem Abstand meinen Sie den kleinsten Unterschied in beide Richtungen? In diesem Fall können Sie die SMALL-Funktion mit der ABS-Funktion kombinieren, die auf die Differenz zwischen den Ziel- und Array-Werten angewendet wird. Wenn es eine exakte Übereinstimmung gibt, ist die Differenz von Null die kleinste mögliche Differenz. Ich habe keine Zeit, um eine Lösung zu entwickeln, aber zum Beispiel würde die minimale Differenz ermittelt werden für die genaue Übereinstimmung von A1 + diff und A1-diff. fixer1234 vor 6 Jahren 0
Könnten Sie einige Beispieldaten freigeben, um den EXACT-Wert zu ermitteln? Rajesh S vor 6 Jahren 0

2 Antworten auf die Frage

0
FezzikMontoya

Hier ist ein alternativer Weg für Ihre Ausgabe. Leider funktioniert es nicht in zweidimensionalen Arrays wie in Ihrem Fall. Wenn Sie Ihre Werte in eine eindimensionale Arrayform konvertieren können, z. B. D1: Z575. Die folgende Formel gibt den gewünschten Wert zurück.

=INDEX(D1:Z575,MATCH(MIN(ABS(D1:Z575-A1)),ABS(D1:Z575-A1),0)) 

Wichtig: Drücken Sie die Ctrl+ Shift+ EnterTastenkombination und nicht Enternur diese. Weil die Formel nur funktioniert, wenn es sich um eine Array-Formel handelt .

Die Logik besteht darin, ähnlich dem Kommentar von fixer1234, die Position der minimalen Differenz zu ermitteln und den Wert mithilfe dieser Position in der Funktion INDEX zurückzugeben .

Hier ist die detaillierte Erklärung der Formel: Wie finde ich die beste Übereinstimmung?

0
fixer1234

Es gibt wahrscheinlich einen eleganteren Weg, dies zu tun, aber hier ist mindestens eine unelegante Lösung. Die Formel wird groß, also erkläre ich sie in Stücken.

enter image description here

Ich wollte veranschaulichen, wie das funktioniert, also habe ich die Reichweite überschaubar gehalten. Skalieren Sie es nach Ihren Bedürfnissen.

Mein Datenfeld ist D1: F10. Ich habe es einfach mit fortlaufenden Zahlen gefüllt, weil das einfach war, und die Illustration klar gemacht. Die Formel ist nicht darauf angewiesen, dass die Zahlen Ganzzahlen oder eine bestimmte Reihenfolge sind. Ich habe einige der Werte durch Duplikate einiger übereinstimmender Werte ersetzt, um zu überprüfen, ob die Formel funktioniert.

In Spalte A sind einige Zielwerte angegeben - einer, bei dem die engste Übereinstimmung abgerundet wird, eine exakte Übereinstimmung, einer, bei dem die engste Übereinstimmung aufgerundet wird, und einer, bei dem das Ziel den gleichen Abstand zwischen zwei engsten Übereinstimmungen aufweist. Ich habe Zielwerte ausgewählt, um in jeder Spalte eine Übereinstimmung zu haben.

Spalte B enthält die engste Übereinstimmung im Array für jeden Zielwert (die Lösung).

Die Formel setzt sich aus Komponenten zusammen, daher erkläre ich die Komponenten, die in diesem Raster dargestellt sind:

enter image description here

Die Zielwerte werden in Spalte A repliziert. Spalte B ermittelt die minimale Differenz zwischen dem Zielwert und dem nächsten Wert in der Tabelle. Formel in B16:

{=MIN(ABS($D$1:$F$10-A1))} 

Dies ist eine Matrixformel. Es ist eine Komponente der endgültigen Formel, daher muss die letzte Formel mit eingegeben werden Ctrl Shift Enter.

Wir wissen nicht, in welche Richtung der Unterschied liegt, ob wir addieren oder subtrahieren müssen, um mit dem nächsten Wert in der Tabelle übereinzustimmen, also versuchen wir beide. Um den Übereinstimmungswert durch "Abrunden" zu finden, wird die Formel in Spalte C verwendet. Um den Übereinstimmungswert durch "Abrunden" zu finden, wird die Formel in Spalte D verwendet. Ein Wert wird abgezogen und der Unterschied wird addiert:

=SUMPRODUCT(($D$1:$F$10=A1-B16)*$D$1:$F$10) =SUMPRODUCT(($D$1:$F$10=A1+B16)*$D$1:$F$10) 

Wenn die erste Klammer wahr ist, wird dies mit ausgewertet 1, andernfalls wird es angezeigt 0. SUMPRODUCT multipliziert dies mit dem zugehörigen Zellenwert und addiert die Ergebnisse.

Beachten Sie jedoch einige merkwürdige Ergebnisse.

  • Mehrere Ziele geben null oder keine Übereinstimmung zurück. Dies sind Fälle, in denen die Übereinstimmung durch Rundung in die andere Richtung gefunden wird.
  • Das Ziel, das sich in der Mitte zwischen den beiden engsten Werten befand, gibt die Abrundung des unteren Übereinstimmungswerts und die Aufrundung des höheren Übereinstimmungswerts zurück. Wenn Sie darauf achten, welche ausgewählt wird, ordnen Sie die Formelkomponenten so an, dass die Rundung in der Vorzugsrichtung ausgewählt wird.
  • Einige Werte sind doppelt so hoch wie sie sein sollten. Dies sind die Fälle, in denen ich den nächsten Wert dupliziert habe; Die Formel fügt in jedem hinzu.

Um mit diesen Fällen umzugehen, zählen wir, wie viele Übereinstimmungen gefunden wurden. Dies geschieht in den Spalten E und F:

{=SUM($D$1:$F$10=A1-B16)} {=SUM($D$1:$F$10=A1+B16)} 

Diese Komponenten sind auch Arrayformeln. Die Formel in Spalte E wertet aus, ob jeder Zellenwert gleich dem Ziel ist, abzüglich der Differenz (entweder True oder False ( 1oder 0)) und summiert diese Werte. Die Formel in Spalte F ist die gleiche, außer dass sie mit dem Ziel und der Differenz verglichen wird.

Wir können diese Ergebnisse auf zwei Arten verwenden: zum Testen auf keine Übereinstimmung (Nullergebnis), um das zu verwendende Ergebnis auszuwählen, und als Anzahl der Übereinstimmungen, um den überhöhten Wert mehrerer Übereinstimmungen im vorherigen Schritt zu korrigieren.

Da wir die engste Übereinstimmung finden, wird es immer eine "nächste" geben. Wenn Sie also auf- oder abrunden, kann das nicht beide Null ergeben. Wir müssen nur eines der Ergebnisse testen. Wenn es Null ist, verwenden wir den anderen. Wenn es nicht Null ist, verwenden wir diesen Wert, da der andere Wert entweder Null, derselbe oder ein anderes gültiges Ergebnis ist, das über die Reihenfolge des Tests ausgewählt werden kann.

Für das ausgewählte gültige Ergebnis wird die Anzahl als Divisor verwendet. Die Formel für das Ergebnis lautet also:

=IF(E16=0,D16/F16,C16/E16) 

So funktioniert es. Wenn Sie dies in eine einzige, konsolidierte Formel umwandeln, müssen Sie die Zellverweise durch die Formel in der referenzierten Zelle ersetzen. Die konsolidierte Formel sieht folgendermaßen aus (Ich füge Abstand und Zeilenvorschub hinzu, um sie lesbarer zu machen; Sie sollten diese entfernen, wenn Sie die Formel kopieren und einfügen möchten):

{=IF(SUM($D$1:$F$10=A1-MIN(ABS($D$1:$F$10-A1)))=0,  SUMPRODUCT(($D$1:$F$10=A1+MIN(ABS($D$1:$F$10-A1)))*$D$1:$F$10)/SUM($D$1:$F$10=A1+MIN(ABS($D$1:$F$10-A1))),  SUMPRODUCT(($D$1:$F$10=A1-MIN(ABS($D$1:$F$10-A1)))*$D$1:$F$10)/SUM($D$1:$F$10=A1-MIN(ABS($D$1:$F$10-A1))))}