Generische Formel
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Zusammenfassung
Um die Position eines Werts in einem 2D-Array zu ermitteln, können Sie die Funktion SUMPRODUCT verwenden. In dem gezeigten Beispiel lauten die Formeln zum Suchen der Zeilen- und Spaltennummern des Maximalwerts im Array:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
wobei "Daten" der benannte Bereich C5: G14 ist.
Hinweis: In diesem Beispiel wird die Position des Maximalwerts in den Daten willkürlich ermittelt. Sie können jedoch data = MAX (data) durch einen anderen logischen Test ersetzen, der einen bestimmten Wert isoliert. Beachten Sie auch, dass diese Formeln fehlschlagen, wenn das Array doppelte Werte enthält.
Erläuterung
Um die Zeilennummer zu erhalten, werden die Daten mit dem Maximalwert verglichen, wodurch ein Array von TRUE FALSE-Ergebnissen generiert wird. Diese werden mit dem Ergebnis von ROW (Daten) multipliziert, das ein Array von Zeilennummern generiert, die dem benannten Bereich "Daten" zugeordnet sind:
=SUMPRODUCT((FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE)*(5;6;7;8;9;10;11))
Die Multiplikationsoperation bewirkt, dass Excel die TRUE FALSE-Werte im ersten Array auf 1s und 0s zwingt, sodass wir einen Zwischenschritt wie diesen visualisieren können:
=SUMPRODUCT((0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,1,0,0;0,0,0,0,0;0,0,0,0,0)*(5;6;7;8;9;10;11))
SUMPRODUCT gibt dann ein Ergebnis von 9 zurück, das der 9. Zeile im Arbeitsblatt entspricht. Um einen Index relativ zu dem benannten Bereich "Daten" zu erhalten, verwenden wir:
-ROW(data)+1
Das Endergebnis ist das Array (5; 4; 3; 2; 1; 0; -1), von dem nur der erste Wert (5) angezeigt wird.
Die Formel zur Bestimmung der Spaltenposition funktioniert auf die gleiche Weise.
Hinweis: Ich bin auf diesen Ansatz in einem Kommentar von Mike Erickson auf MrExcel.com gestoßen. Es gibt auch einige andere gute Ideen in diesem Thread, einschließlich einer Array-Formeloption.
