Generieke formule
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Samenvatting
Om de positie van een waarde in een 2D-array te lokaliseren, kunt u de SOMPRODUCT-functie gebruiken. In het getoonde voorbeeld zijn de formules die worden gebruikt om de rij- en kolomnummers van de maximale waarde in de array te lokaliseren:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
waarbij "data" het benoemde bereik C5: G14 is.
Opmerking: voor dit voorbeeld vinden we willekeurig de locatie van de maximale waarde in de gegevens, maar u kunt data = MAX (data) vervangen door elke andere logische test die een bepaalde waarde isoleert. Merk ook op dat deze formules mislukken als er dubbele waarden in de array staan.
Uitleg
Om het rijnummer te krijgen, worden de gegevens vergeleken met de maximale waarde, wat een array van TRUE FALSE-resultaten genereert. Deze worden vermenigvuldigd met het resultaat van ROW (data) die een array van rijnummers genereert die zijn gekoppeld aan het benoemde bereik "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))
De vermenigvuldigingsbewerking zorgt ervoor dat Excel de TRUE FALSE-waarden in de eerste array dwingt tot 1s en 0s, dus we kunnen een tussenstap als volgt visualiseren:
=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))
SOMPRODUCT retourneert vervolgens een resultaat van 9, wat overeenkomt met de 9e rij op het werkblad. Om een index te krijgen met betrekking tot het benoemde bereik "data", gebruiken we:
-ROW(data)+1
Het uiteindelijke resultaat is de array (5; 4; 3; 2; 1; 0; -1), waarvan alleen de eerste waarde (5) wordt weergegeven.
De formule om de kolompositie te bepalen, werkt op dezelfde manier.
Opmerking: ik kwam deze benadering tegen in een opmerking van Mike Erickson op MrExcel.com. Er zijn ook enkele andere goede ideeën in die thread, waaronder een optie voor matrixformules.
