Formula generică
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
rezumat
Pentru a localiza poziția unei valori într-o matrice 2D, puteți utiliza funcția SUMPRODUCT. În exemplul prezentat, formulele utilizate pentru a localiza numerele de rânduri și coloane ale valorii maxime din matrice sunt:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
unde „date” este intervalul numit C5: G14.
Notă: pentru acest exemplu, găsim în mod arbitrar locația valorii maxime în date, dar puteți înlocui date = MAX (date) cu orice alt test logic care va izola o anumită valoare. De asemenea, rețineți că aceste formule vor eșua dacă există valori duplicate în matrice.
Explicaţie
Pentru a obține numărul rândului, datele sunt comparate cu valoarea maximă, care generează o serie de rezultate ADEVĂRATE. Acestea sunt înmulțite cu rezultatul ROW (date) care generează și o serie de numere de rând asociate cu intervalul numit „date”:
=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))
Operația de multiplicare face ca Excel să constrângă valorile TRUE FALSE din prima matrice la 1s și 0s, astfel încât să putem vizualiza un pas intermediar ca acesta:
=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 returnează apoi un rezultat de 9, care corespunde celui de-al 9-lea rând din foaia de lucru. Pentru a obține un index relativ la intervalul numit „date”, folosim:
-ROW(data)+1
Rezultatul final este matricea (5; 4; 3; 2; 1; 0; -1), din care este afișată doar prima valoare (5).
Formula pentru a determina poziția coloanei funcționează în același mod.
Notă: Am dat peste această abordare într-un comentariu de Mike Erickson pe MrExcel.com. Există și alte idei bune în acel fir, inclusiv o opțiune de matrice.
