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.
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:
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))))}