Formula generica
(=MAX(IF(criteria,ROW(rng)-MIN(ROW(rng))+1)))
Sommario
Per ottenere la posizione dell'ultima corrispondenza (ovvero l'ultima occorrenza) di un valore di ricerca, è possibile utilizzare una formula di matrice basata sulle funzioni IF, ROW, INDEX, MATCH e MAX. Nell'esempio mostrato, la formula in H6 è:
(=MAX(IF(names=H5,ROW(names)-MIN(ROW(names))+1)))
Dove "nomi" è l'intervallo denominato C4: C11.
Nota: questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO.
Spiegazione
L'essenza di questa formula è che costruiamo un elenco di numeri di riga per un dato intervallo, corrispondenti a un valore, e quindi utilizziamo la funzione MAX per ottenere il numero di riga più grande, che corrisponde all'ultimo valore corrispondente. Stiamo usando l'intervallo denominato "nomi" solo per comodità.
Lavorando dall'interno verso l'esterno, questa parte della formula genererà un insieme relativo di numeri di riga:
ROW(names)-MIN(ROW(names))+1
Il risultato dell'espressione precedente è un array di numeri come questo:
(1;2;3;4;5;6;7;8)
Notare che otteniamo 8 numeri, corrispondenti alle 8 righe nella tabella. Vedi questa pagina per i dettagli su come funziona questa parte della formula.
Ai fini di questa formula, vogliamo solo i numeri di riga per i valori corrispondenti, quindi usiamo la funzione SE per filtrare i valori in questo modo:
IF(names=H5,ROW(names)-MIN(ROW(names))+1)
Ciò si traduce in una matrice simile a questa:
(1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE)
Notare che questo array contiene ancora otto elementi. Tuttavia, sono sopravvissuti solo i numeri di riga in cui il valore nell'intervallo denominato "nomi" è uguale a "amy" (ad esempio 1, 4, 7). Tutti gli altri elementi nell'array sono FALSE, poiché non hanno superato il test logico nella funzione IF.
Infine, la funzione IF consegna questo array alla funzione MAX. MAX restituisce il valore più alto nell'array, il numero 7, che corrisponde all'ultimo numero di riga in cui il nome è "amy". Una volta che conosciamo l'ultimo numero di riga corrispondente, possiamo usare INDICE per recuperare un valore in quella posizione.
Penultimo, ecc.
Per ottenere la penultima posizione, la terzultima, ecc. Puoi passare dalla funzione MIN alla funzione LARGE in questo modo:
(=LARGE(IF(criteria,ROW(rng)-MIN(ROW(rng))+1),k))
dove k rappresenta "l'ennesimo più grande". Ad esempio, per ottenere la penultima corrispondenza nell'esempio precedente, puoi utilizzare:
(=LARGE(IF(names=H5,ROW(names)-MIN(ROW(names))+1),2))
Come prima, questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO.
