Generisk formel
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Sammendrag
For å finne posisjonen til en verdi i en 2D-matrise, kan du bruke SUMPRODUCT-funksjonen. I eksemplet som vises er formlene som brukes til å finne rad- og kolonnetallene for maksverdien i matrisen:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
der "data" er det navngitte området C5: G14.
Merk: for dette eksemplet finner vi vilkårlig plasseringen av den maksimale verdien i dataene, men du kan erstatte data = MAX (data) med en hvilken som helst annen logisk test som vil isolere en gitt verdi. Vær også oppmerksom på at disse formlene vil mislykkes hvis det er dupliserte verdier i matrisen.
Forklaring
For å få radnummeret, sammenlignes dataene med maksverdien, som genererer en matrise med SANTE FALSKE resultater. Disse multipliseres med resultatet av ROW (data) som genererer og en rekke radnumre assosiert med det nevnte området "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))
Multiplikasjonsoperasjonen får Excel til å tvinge de SANNE FALSKE verdiene i den første matrisen til 1s og 0s, slik at vi kan visualisere et mellomtrinn 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 deretter et resultat på 9, som tilsvarer 9. rad på regnearket. For å få en indeks i forhold til det navngitte området "data", bruker vi:
-ROW(data)+1
Det endelige resultatet er matrisen (5; 4; 3; 2; 1; 0; -1), hvorfra bare den første verdien (5) vises.
Formelen for å bestemme kolonneposisjonen fungerer på samme måte.
Merk: Jeg kjørte inn i denne tilnærmingen i en kommentar fra Mike Erickson på MrExcel.com. Det er også noen andre gode ideer i den tråden, inkludert et arrayformelalternativ.
