Üldine valem
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Kokkuvõte
2D massiivi väärtuse asukoha leidmiseks võite kasutada funktsiooni SUMPRODUCT. Näidatud näites on massiivi maksimaalse väärtuse rea- ja veerunumbrite leidmiseks kasutatavad valemid järgmised:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
kus "andmed" on nimega vahemik C5: G14.
Märkus. Selle näite jaoks leiame meelevaldselt andmetest maksimaalse väärtuse asukoha, kuid võite asendada andmed = MAX (andmed) mis tahes muu loogilise testiga, mis eraldab antud väärtuse. Pange tähele ka, et need valemid nurjuvad, kui massiivis on duplikaatväärtusi.
Selgitus
Ridanumbri saamiseks võrreldakse andmeid maksimaalse väärtusega, mis loob massiivi TÕENE VÄÄR tulemusi. Need korrutatakse ROW (data) tulemusega, mis genereerib ja nimetab vahemikku "data" seotud rea numbreid:
=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))
Korrutamisoperatsioon sunnib Exceli sundima esimese massiivi TRUE FALSE väärtusi väärtusele 1s ja 0s, nii et saame visualiseerida sellise vaheetapi:
=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))
Seejärel tagastab SUMPRODUCT tulemuse 9, mis vastab töölehe 9. reale. Nimelise vahemiku "andmed" suhtes indeksi saamiseks kasutame järgmist:
-ROW(data)+1
Lõpptulemuseks on massiiv (5; 4; 3; 2; 1; 0; -1), millest kuvatakse ainult esimene väärtus (5).
Veeru asukoha määramise valem töötab samamoodi.
Märkus. Sellele lähenemisele sattusin Mike Ericksoni kommentaaris MrExcel.com-is. Ka selles lõimes on mõned muud head ideed, sealhulgas massiivivalemi valik.
