Excela

Obțineți locația valorii în matrice 2D

Get Location Value 2d Array

Formula Excel: obțineți locația valorii în matrice 2DFormula 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 gama denumită 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.





Excel găsește valoarea maximă în coloană
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,FALSEFALSE,FALSE,FALSE,FALSE,FALSEFALSE,FALSE,FALSE,FALSE,FALSEFALSE,FALSE,FALSE,FALSE,FALSEFALSE,FALSE,TRUE,FALSE,FALSEFALSE,FALSE,FALSE,FALSE,FALSEFALSE,FALSE,FALSE,FALSE,FALSE}*{567891011})

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:



cum se introduce tabelul pivot în Excel
 
= SUMPRODUCT ({0,0,0,0,00,0,0,0,00,0,0,0,00,0,0,0,00,0,1,0,00,0,0,0,00,0,0,0,0}*{567891011})

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 în raport cu intervalul numit „date”, folosim:

 
- ROW (data)+1

Rezultatul final este matricea {543210-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 întâlnit această abordare într-un comentariu de Mike Erickson peste pe MrExcel.com. Există și alte idei bune în acel fir, inclusiv o opțiune de formulă matrice.

Autor Dave Bruns


^