Generisk formel
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Resumé
For at finde placeringen af en værdi i et 2D-array kan du bruge SUMPRODUCT-funktionen. I det viste eksempel er formlerne, der bruges til at lokalisere række- og kolonnetallene for den maksimale værdi i arrayet:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
hvor "data" er det navngivne område C5: G14.
Bemærk: i dette eksempel finder vi vilkårligt placeringen af den maksimale værdi i dataene, men du kan erstatte data = MAX (data) med enhver anden logisk test, der isolerer en given værdi. Bemærk også, at disse formler mislykkes, hvis der er duplikatværdier i arrayet.
Forklaring
For at få række nummeret sammenlignes dataene med den maksimale værdi, der genererer en matrix med SAND FALSKE resultater. Disse ganges med resultatet af ROW (data), der genererer og en række rækkenumre, der er knyttet til det navngivne interval "data":
=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))
Multiplikationsoperationen får Excel til at tvinge de SANDE FALSKE værdier i den første matrix til 1s og 0s, så vi kan visualisere et mellemliggende trin som dette:
=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 returnerer derefter et resultat på 9, hvilket svarer til 9. række på regnearket. For at få et indeks i forhold til det navngivne interval "data" bruger vi:
-ROW(data)+1
Det endelige resultat er arrayet (5; 4; 3; 2; 1; 0; -1), hvorfra kun den første værdi (5) vises.
Formlen til bestemmelse af søjlepositionen fungerer på samme måde.
Bemærk: Jeg løb ind i denne tilgang i en kommentar fra Mike Erickson på MrExcel.com. Der er også nogle andre gode ideer i den tråd, herunder en matrixformelindstilling.
