Generisk formel
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Sammanfattning
För att hitta positionen för ett värde i en 2D-array kan du använda SUMPRODUCT-funktionen. I exemplet som visas är formlerna som används för att lokalisera rad- och kolumnnumren för maxvärdet i matrisen:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
där "data" är det namngivna intervallet C5: G14.
Obs: i det här exemplet hittar vi godtyckligt platsen för det maximala värdet i data, men du kan ersätta data = MAX (data) med något annat logiskt test som isolerar ett visst värde. Observera också att dessa formler misslyckas om det finns dubbla värden i matrisen.
Förklaring
För att få radnumret jämförs data med maxvärdet, vilket genererar en matris med SANT FALSKA resultat. Dessa multipliceras med resultatet av ROW (data) som genererar och arrangerar radnummer associerade med det nämnda intervallet "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))
Multiplikationsåtgärden får Excel att tvinga de sanna FALSKA värdena i den första matrisen till 1s och 0s, så att vi kan visualisera ett mellansteg så här:
=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 returnerar sedan resultatet 9, vilket motsvarar den nionde raden i kalkylbladet. För att få ett index i förhållande till det namngivna intervallet "data" använder vi:
-ROW(data)+1
Det slutliga resultatet är matrisen (5; 4; 3; 2; 1; 0; -1), från vilken endast det första värdet (5) visas.
Formeln för att bestämma kolumnpositionen fungerar på samma sätt.
Obs: Jag stötte på detta tillvägagångssätt i en kommentar av Mike Erickson på MrExcel.com. Det finns också några andra bra idéer i den tråden, inklusive ett matrisformelalternativ.
