Utilizzo di INDEX per estrarre un valore da un risultato di formule array di Excel

Non tutte le formule di matrice restituiscono matrici con più colonne e/o più righe nel foglio di lavoro. Ma quando lo fanno, può succedere che tu sia interessato a vedere solo un valore nell'array. Puoi usare la funzione INDICE di Excel per aiutarti.

Ad esempio, REGR.LIN è una delle funzioni del foglio di lavoro che funzionerà correttamente solo se si immette in matrice la formula che contiene la funzione. Ma supponiamo di voler accedere a un solo valore di cella nei risultati di REGR.LIN, magari per adattare il layout di un foglio di lavoro in un report di routine. In tal caso, non vuoi necessariamente l'intero set di risultati REGR.LIN e puoi utilizzare la funzione INDICE di Excel per estrarre e visualizzare solo quello che ti interessa mostrare.

Ad esempio, ecco come potresti inserire in array REGR.LIN per una regressione multipla:

=LINEST(A2:A51,B2:D51,,VERO)

Se si immette la formula in un intervallo di 5 righe per 4 colonne, l'intersezione della terza riga e della prima colonna di tale intervallo contiene il valore R quadrato della regressione. Quindi, se selezioni solo una singola cella e inserisci la seguente formula, otterrai solo il valore R al quadrato:

=INDICE(LINEST(A2:A51,B2:D51,,VERO),3,1)

Qui, stai fornendo INDEX con l'array di valori restituiti dalla funzione LINEST. Questo è il primo argomento per INDEX. Il secondo e il terzo argomento di INDEX sono i numeri 3 e 1, che indicano a INDEX di trovare il valore nella terza riga e nella prima colonna della matrice e di restituirlo al foglio di lavoro.

È possibile immettere la formula INDICE completa come fornita normalmente, con una matrice di risultati REGR.LIN come primo argomento, senza la combinazione Ctrl e Maiusc e Invio, ovvero senza inserirla nell'array. (Prova in entrambi i modi, sia l'array che lo inserisce che lo immette normalmente.)

Eppure, se provi a inserire la seguente formula di matrice a cella singola, produce l'errore #VALORE! se provi a inserirlo normalmente:

=SE(H44639:H44644>0,G44639:G44644,0)

Quando la formula chiama una funzione che Excel si aspetta che accetti una matrice come argomento, la formula può essere immessa normalmente. È il caso di questa formula:

=INDICE(LINEST(A2:A51,B2:D51,,VERO),3,1)

I risultati di REGR.LIN sono nidificati all'interno della funzione INDICE, dove fungono da primo argomento. Excel si aspetta che INDEX prenda una matrice di valori come primo argomento: l'analisi di una matrice è ciò per cui è nato INDEX. Quindi la formula come data non ha bisogno di essere inserita nell'array.

Al contrario, questa formula di matrice a cella singola deve essere immessa in matrice:

=MEDIA(SE(A2:A25="Zig",B2:B25,""))

In questo caso, Excel non si aspetta che la funzione SE prenderà una matrice di valori come argomento, ma qui stiamo presentando non una ma due matrici di valori a SE: l'intervallo A23: A25 e B2: B25. (Puoi persino assumere la posizione che ci sia una matrice di 24 istanze di "" implicita nei primi due argomenti.) Poiché la formula non soddisfa l'aspettativa iniziale di Excel degli argomenti su SE, devi attirare l'attenzione di Excel sulla situazione, e lo fai inserendo la formula nell'array.