Generička formula
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Sažetak
Da biste locirali položaj vrijednosti u 2D nizu, možete koristiti funkciju SUMPRODUCT. U prikazanom primjeru, formule korištene za lociranje brojeva redaka i stupaca maksimalne vrijednosti u polju su:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
gdje je "podatak" imenovani raspon C5: G14.
Napomena: u ovom primjeru proizvoljno pronalazimo mjesto maksimalne vrijednosti u podacima, ali podatke = MAX (podaci) možete zamijeniti bilo kojim drugim logičkim testom koji će izolirati zadanu vrijednost. Također imajte na umu da ove formule neće uspjeti ako u nizu postoje dvostruke vrijednosti.
Obrazloženje
Da bi se dobio broj retka, podaci se uspoređuju s maksimalnom vrijednošću, koja generira niz TRUE FALSE rezultata. Oni se množe rezultatom ROW (podaci) koji generira i niz brojeva redaka povezanih s imenovanim rasponom "podaci":
=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))
Operacija množenja uzrokuje da Excel prisiljava TRUE FALSE vrijednosti u prvom polju na 1s i 0s, tako da možemo vizualizirati međukorak poput ovog:
=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 tada vraća rezultat 9, što odgovara 9. redu na radnom listu. Da bismo dobili indeks u odnosu na imenovani raspon "podaci", koristimo:
-ROW(data)+1
Konačni rezultat je niz (5; 4; 3; 2; 1; 0; -1), iz kojeg se prikazuje samo prva vrijednost (5).
Formula za određivanje položaja stupca djeluje na isti način.
Napomena: Na ovaj sam pristup naišao u komentaru Mikea Ericksona na MrExcel.com. Postoje i neke druge dobre ideje u toj niti, uključujući opciju formule niza.
