Formula generica
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Sommario
Per individuare la posizione di un valore in una matrice 2D, è possibile utilizzare la funzione SUMPRODUCT. Nell'esempio mostrato, le formule utilizzate per individuare i numeri di riga e colonna del valore massimo nella matrice sono:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
dove "data" è l'intervallo denominato C5: G14.
Nota: per questo esempio, troviamo arbitrariamente la posizione del valore massimo nei dati, ma puoi sostituire data = MAX (dati) con qualsiasi altro test logico che isolerà un dato valore. Notare inoltre che queste formule falliranno se sono presenti valori duplicati nella matrice.
Spiegazione
Per ottenere il numero di riga, i dati vengono confrontati con il valore massimo, che genera una matrice di risultati VERO FALSO. Questi sono moltiplicati per il risultato di ROW (data) che genera e array di numeri di riga associati all'intervallo denominato "data":
=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))
L'operazione di moltiplicazione fa sì che Excel costringa i valori VERO FALSO nel primo array a 1 e 0, quindi possiamo visualizzare un passaggio intermedio come questo:
=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 restituisce quindi un risultato di 9, che corrisponde alla nona riga del foglio di lavoro. Per ottenere un indice relativo all'intervallo denominato "dati", utilizziamo:
-ROW(data)+1
Il risultato finale è l'array (5; 4; 3; 2; 1; 0; -1), da cui viene visualizzato solo il primo valore (5).
La formula per determinare la posizione della colonna funziona allo stesso modo.
Nota: mi sono imbattuto in questo approccio in un commento di Mike Erickson su MrExcel.com. Ci sono anche altre buone idee in quel thread, inclusa un'opzione di formula di matrice.
