Formula generica
=LOOKUP(2,1/(ISNUMBER(FIND(filename,range))),range)
Sommario
Per cercare l'ultima versione del file in un elenco, è possibile utilizzare una formula basata sulla funzione CERCA insieme alle funzioni NUMERO E TROVA. Nell'esempio mostrato, la formula nella cella G7 è:
=LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files)
dove "file" è l'intervallo denominato B5: B11.
Contesto
In questo esempio, abbiamo un numero di versioni di file elencate in una tabella con una data e un nome utente. Notare che i nomi dei file vengono ripetuti con un contatore alla fine come numero di revisione - 001, 002, 003, ecc.
Dato un nome di file, vogliamo recuperare il nome dell'ultima o dell'ultima revisione. Ci sono due sfide:
- La sfida è che i codici di versione alla fine dei nomi dei file rendono più difficile abbinare il nome del file.
- Per impostazione predefinita, le formule di corrispondenza di Excel restituiranno la prima corrispondenza, non l'ultima.
Per superare queste sfide, dobbiamo utilizzare alcune tecniche complicate.
Spiegazione
Questa formula utilizza la funzione CERCA per trovare e recuperare l'ultimo nome di file corrispondente. Il valore di ricerca è 2 e lookup_vector viene creato con questo:
1/(ISNUMBER(FIND(G6,files)))
All'interno di questo frammento, la funzione TROVA cerca il valore in G6 all'interno dell'intervallo denominato "file" (B5: B11). Il risultato è un array come questo:
(1;#VALUE!;1;1;#VALUE!;#VALUE!;1)
Qui, il numero 1 rappresenta una corrispondenza e l'errore #VALORE rappresenta un nome file non corrispondente. Questo array entra nella funzione ISNUMBER e risulta così:
(TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE)
I valori di errore ora sono FALSE e il numero 1 è ora TRUE. Questo supera la sfida n. 1, ora abbiamo un array che mostra chiaramente quali file nell'elenco contengono il nome del file di interesse.
Successivamente, la matrice viene utilizzata come denominatore con 1 come numeratore. Il risultato è questo:
(1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;1)
che va in LOOKUP come lookup_vector. Questa è una soluzione complicata per la sfida n. 2. La funzione CERCA opera solo in modalità di corrispondenza approssimativa e ignora automaticamente i valori di errore. Ciò significa che con 2 come valore di ricerca, CERCA.VERT proverà a trovare 2, fallirà e tornerà al numero precedente (in questo caso corrispondendo all'ultimo 1 nella posizione 7). Infine, CERCA utilizza 7 come un indice per recuperare il settimo file nell'elenco dei file.
Gestione delle ricerche vuote
Stranamente, la funzione TROVA restituisce 1 se il valore di ricerca è una stringa vuota (""). Per proteggerti da una falsa corrispondenza, puoi racchiudere la formula in IF e testare una ricerca vuota:
=IF(G6"",LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files),"")
