In questo tutorial imparerai come trovare l'ultima occorrenza di un elemento in un elenco utilizzando le formule di Excel.
Di recente, stavo lavorando alla definizione dell'ordine del giorno per una riunione.
Avevo un elenco in Excel in cui avevo un elenco di persone e le date in cui fungevano da "presidente della riunione".
Poiché c'era una ripetizione nell'elenco (il che significa che una persona è stata presidente della riunione più volte), avevo anche bisogno di sapere quando è stata l'ultima volta che una persona ha agito come "presidente della riunione".
Questo perché dovevo assicurarmi che qualcuno che presiedeva di recente non fosse assegnato di nuovo.
Quindi ho deciso di usare la magia della funzione Excel per farlo.
Di seguito è riportato il risultato finale in cui sono in grado di selezionare un nome dal menu a discesa e mi dà la data dell'ultima occorrenza di quel nome nell'elenco.
Se hai una buona conoscenza delle funzioni di Excel, saprai che non esiste una funzione di Excel in grado di farlo.
Ma sei nella sezione Formula Hack, e qui facciamo accadere la magia.
In questo tutorial, ti mostrerò tre modi per farlo.
Trova l'ultima occorrenza - Utilizzo della funzione MAX
Il merito di questa tecnica va a un articolo di Excel MVP Charley Kyd.
Ecco la formula di Excel che restituirà l'ultimo valore dall'elenco:
=INDICE($B$2:$B$14,SOMMA.PRODOTTO(MAX(RIGA($A$2:$A$14)*($D$3=$A$2:$A$14))-1))
Ecco come funziona questa formula:
- La funzione MAX viene utilizzata per trovare il numero di riga dell'ultimo nome corrispondente. Ad esempio, se il nome è Glen, restituirà 11, poiché è nella riga 11. Poiché la nostra lista inizia dalla seconda riga in poi, è stato sottratto 1. Quindi la posizione dell'ultima occorrenza di Glen è 10 sulla nostra lista.
- SUMPRODUCT viene utilizzato per garantire che non sia necessario utilizzare Control + Shift + Invio, poiché SUMPRODUCT può gestire formule di matrice.
- La funzione INDEX viene ora utilizzata per trovare la data dell'ultimo nome corrispondente.
Trova l'ultima occorrenza - Utilizzo della funzione CERCA
Ecco un'altra formula per fare lo stesso lavoro:
=CERCA(2,1/($A$2:$A$14=$D$3),$B$2:$B$14)
Ecco come funziona questa formula:
- Il valore di ricerca è 2 (vedrai perché… continua a leggere)
- L'intervallo di ricerca è 1/($A$2:$A$14=$D$3): restituisce 1 quando trova il nome corrispondente e un errore quando non lo trova. Quindi finisci per ottenere un array. Ad esempio, se il valore di ricerca è Glen, l'array sarà {#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!}.
- Il terzo argomento ([result_vector]) è l'intervallo da cui fornisce il risultato, che in questo caso sono le date.
Il motivo per cui questa formula funziona è che la funzione CERCA utilizza la tecnica di corrispondenza approssimativa. Ciò significa che se riesce a trovare il valore esatto corrispondente, lo restituirà, ma in caso contrario, eseguirà la scansione dell'intero array fino alla fine e restituirà il valore successivo più grande che è inferiore al valore di ricerca.
In questo caso, il valore di ricerca è 2 e nel nostro array otterremo solo 1 o errori. Quindi esegue la scansione dell'intero array e restituisce la posizione dell'ultimo 1, che è l'ultimo valore corrispondente del nome.
Trova l'ultima occorrenza - Utilizzo della funzione personalizzata (VBA)
Lascia che ti mostri anche un altro modo per farlo.
Possiamo creare una funzione personalizzata (chiamata anche Funzione definita dall'utente) usando VBA.
Il vantaggio di creare una funzione personalizzata è che è facile da usare. Non devi preoccuparti di creare una formula complessa ogni volta, poiché la maggior parte del lavoro avviene nel backend VBA.
Ho creato una formula semplice (che è molto simile alla formula CERCA.VERT).
Per creare una funzione personalizzata, devi avere il codice VBA nell'editor VB. Ti darò il codice e i passaggi per inserirlo nell'editor VB tra un po', ma lascia che ti mostri prima come funziona:
Questa è la formula che ti darà il risultato:
=Ultimo elemento ricerca($D$3,$A$2:$B$14,2)
La formula accetta tre argomenti:
- Valore di ricerca (questo sarebbe il nome nella cella D3)
- Intervallo di ricerca (questo sarebbe l'intervallo con i nomi e le date - A2: B14)
- Numero colonna (questa è la colonna da cui vogliamo il risultato)
Dopo aver creato la formula e inserito il codice in VB Editor, puoi utilizzarlo come qualsiasi altra normale funzione del foglio di lavoro di Excel.
Ecco il codice della formula:
'Questo è un codice per una funzione che trova l'ultima occorrenza di un valore di ricerca e restituisce il valore corrispondente dalla colonna specificata 'Codice creato da Sumit Bansal (https://trumpexcel.com) Funzione LastItemLookup(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long For i = LookupRange.Columns(1).Cells.Count To 1 Step -1 If Lookupvalue = LookupRange.Cells(i, 1) Then LastItemLookup = LookupRange.Cells(i, ColumnNumber) Esci dalla funzione End If Next i End Function
Ecco i passaggi per inserire questo codice nell'editor VB:
- Vai alla scheda Sviluppatore.
- Fare clic sull'opzione Visual Basic. Questo aprirà l'editor VB nel backend.
- Nel riquadro Project Explorer nell'editor VB, fare clic con il pulsante destro del mouse su qualsiasi oggetto per la cartella di lavoro in cui si desidera inserire il codice. Se non vedi Esplora progetti, vai alla scheda Visualizza e fai clic su Esplora progetti.
- Vai su Inserisci e fai clic su Modulo. Questo inserirà un oggetto modulo per la tua cartella di lavoro.
- Copia e incolla il codice nella finestra del modulo.
Ora la formula sarebbe disponibile in tutto il foglio di lavoro della cartella di lavoro.
Si noti che è necessario salvare la cartella di lavoro nel formato .XLSM poiché contiene una macro. Inoltre, se desideri che questa formula sia disponibile in tutte le cartelle di lavoro che utilizzi, puoi salvarla nella cartella di lavoro macro personale o creare un componente aggiuntivo da essa.
