Bendroji formulė
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Santrauka
Norėdami rasti 2D masyvo vertės padėtį, galite naudoti funkciją SUMPRODUCT. Parodytame pavyzdyje formulės, naudojamos masyvo maksimalios vertės eilutės ir stulpelio numeriams surasti, yra šios:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
kur „duomenys“ yra įvardytas diapazonas C5: G14.
Pastaba: šiame pavyzdyje mes savavališkai randame didžiausios vertės vietą duomenyse, tačiau galite pakeisti duomenis = MAX (duomenys) bet kokiu kitu loginiu bandymu, kuris išskirs nurodytą vertę. Taip pat atkreipkite dėmesį, kad šios formulės nepavyks, jei masyve yra pasikartojančių reikšmių.
Paaiškinimas
Norint gauti eilutės numerį, duomenys lyginami su didžiausia verte, kuri sukuria TIKRŲ NETIKRŲ rezultatų masyvą. Jie padauginami iš eilės (duomenys), generuojančio eilutės numerių, susietų su pavadintu diapazonu „duomenys“, masyvo:
=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))
Dauginimo operacija priverčia „Excel“ priversti TIKROSIOS NETIESOS reikšmes pirmajame masyve į 1s ir 0s, todėl galime vizualizuoti tokį tarpinį žingsnį:
=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))
Tada SUMPRODUCT pateikia rezultatą 9, kuris atitinka 9-ą darbalapio eilutę. Norėdami gauti indeksą, palyginti su pavadintu diapazonu „duomenys“, naudojame:
-ROW(data)+1
Galutinis rezultatas yra masyvas (5; 4; 3; 2; 1; 0; -1), iš kurio rodoma tik pirmoji reikšmė (5).
Stulpelio padėties nustatymo formulė veikia taip pat.
Pastaba: su šiuo požiūriu susidūriau komentuodamas Mike'o Ericksono tinklalapyje MrExcel.com. Toje gijoje taip pat yra keletas kitų gerų idėjų, įskaitant masyvo formulės parinktį.
