Vispārēja formula
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Kopsavilkums
Lai atrastu vērtības pozīciju 2D masīvā, varat izmantot funkciju SUMPRODUCT. Parādītajā piemērā formulas, ko izmanto, lai atrastu masīva maksimālās vērtības rindu un kolonnu numurus, ir šādas:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
kur "dati" ir nosauktais diapazons C5: G14.
Piezīme. Šajā piemērā mēs patvaļīgi atrodam maksimālās vērtības atrašanās vietu datos, bet datus = MAX (dati) varat aizstāt ar jebkuru citu loģisku pārbaudi, kas izolēs konkrēto vērtību. Ņemiet vērā arī, ka šīs formulas neizdosies, ja masīvā ir vērtību dublikāti.
Paskaidrojums
Lai iegūtu rindas numuru, dati tiek salīdzināti ar maksimālo vērtību, kas ģenerē TRUE FALSE masīvu. Tie tiek reizināti ar ROW (dati) rezultātu, kas ģenerē un rindu numuru masīvu, kas saistīts ar nosaukto diapazonu "dati":
=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))
Reizināšanas darbība liek programmai Excel piespiest TRUE FALSE vērtības pirmajā masīvā uz 1s un 0s, tāpēc mēs varam vizualizēt šādu starpposmu:
=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))
Pēc tam SUMPRODUCT atgriež rezultātu 9, kas atbilst darblapas 9. rindai. Lai iegūtu indeksu attiecībā pret nosaukto diapazonu "dati", mēs izmantojam:
-ROW(data)+1
Gala rezultāts ir masīvs (5; 4; 3; 2; 1; 0; -1), no kura tiek parādīta tikai pirmā vērtība (5).
Kolonnas pozīcijas noteikšanas formula darbojas tāpat.
Piezīme: Es saskāros ar šo pieeju Maika Ēriksona komentārā vietnē MrExcel.com. Šajā pavedienā ir arī dažas citas labas idejas, tostarp masīva formulas opcija.
