Scarica la cartella di lavoro di esempio
Questo tutorial ti insegnerà come cercare l'ultimo valore in colonna o riga in Excel.
Ultimo valore in colonna
Puoi usare la funzione CERCA per trovare l'ultima cella non vuota in una colonna.
| 1 | =CERCA(2,1/(B:B""),B:B) |
Esaminiamo questa formula.
La parte della formula B:B "" restituisce un array contenente i valori True e False: {FALSE, TRUE, TRUE,… }, verificando che ogni cella nella colonna B sia vuota (FALSE).
| 1 | =CERCA(2,1/({FALSO;VERO;VERO;VERO;VERO;VERO;FALSO;… ),B:B) |
Questi valori booleani vengono convertiti in 0 o 1 e vengono utilizzati per dividere 1.
| 1 | =CERCA(2,{#DIV/0!;1;1;1;1;1;#DIV/0!;,B:B) |
Questo è il lookup_vector per la funzione LOOKUP. Nel nostro caso, lookup_value è 2, ma il valore più grande in lookup_vector è 1, quindi la funzione LOOKUP corrisponderà all'ultimo 1 nell'array e restituirà il valore corrispondente in result_vector.
Se sei sicuro di avere solo valori numerici nella colonna, i tuoi dati iniziano dalla riga 1 e il tuo intervallo di dati in modo continuo, puoi utilizzare una formula leggermente più semplice con le funzioni INDICE e COUNT.
| 1 | =INDICE(B:B,CONTEGGIO(B:B)) |
La funzione COUNT restituisce il numero di celle riempite con i dati nell'intervallo continuo (4) e la funzione INDEX fornisce quindi il valore della cella in questa riga corrispondente (4°).
Per evitare possibili errori quando l'intervallo di dati contiene una combinazione di valori numerici e non numerici o anche alcune celle vuote, è possibile utilizzare la funzione CERCA insieme alle funzioni ISBLANK e NOT.
| 1 | =CERCA(2,1/(NOT(VAL.VUOTO(B:B))),B:B) |
La funzione ISBLANK restituisce un array contenente i valori True e False, corrispondenti a 1 e 0. La funzione NOT cambia True (cioè 1) in False e False (cioè 0) in True. Se invertiamo questo array risultante (quando dividiamo 1 per questo array), otteniamo un array di risultati contenente di nuovo #DIV/0! errori e 1, che possono essere utilizzati come array di ricerca (lookup_vector) nella nostra funzione LOOKUP. La funzionalità della funzione LOOKUP è quindi la stessa del nostro primo esempio: restituisce il valore del vettore risultato nella posizione dell'ultimo 1 nell'array di ricerca.
Quando hai bisogno del numero di riga con l'ultima voce da restituire, puoi modificare la formula utilizzata nel nostro primo esempio insieme alla funzione ROW nel tuo vector_risultato.
| 1 | =CERCA(2,1/(B:B""),RIGA(B:B)) |
Ultimo valore nella riga
Per ottenere il valore dell'ultima cella non vuota in una riga piena di dati numerici, potresti voler utilizzare un approccio simile ma con funzioni diverse: la funzione OFFSET insieme alle funzioni CONFRONTA e MAX.
| 1 | =OFFSET(Riferimento, Righe, Colonne) |
| 1 | =OFFSET(B2,0,CONFRONTA(MAX(B2:XFD2)+1,B2:XFD2,1)-1) |
Vediamo come funziona questa formula.
Funzione CONFRONTA
Usiamo la funzione MATCH per "contare" quanti valori di cella sono inferiori a 1 + il massimo di tutti i valori nella riga2 a partire da B2.
| 1 | =CONFRONTA(valore_ricerca, matrice_ricerca, [tipo_corrispondenza]) |
| 1 | =CONFRONTA(MAX(B2:XFD2)+1,B2:XFD2,1) |
Il lookup_value della funzione MATCH è il massimo di tutti i valori nella riga2 + 1. Poiché questo valore ovviamente non esiste nella riga2 e il match_type è impostato su 1 (minore o uguale a lookup_value), la funzione MATCH restituirà il la posizione dell'ultima cella "controllata" nell'array, ovvero il numero di celle riempite con i dati nell'intervallo B2:XFD2 (XFD è l'ultima colonna nelle versioni più recenti di Excel).
Funzione OFFSET
Quindi usiamo la funzione OFFSET per ottenere il valore di questa cella, la cui posizione è stata restituita dalla funzione MATCH.
| 1 | =OFFSET(B2,0,C4-1) |
