Riepilogo: in questo tutorial imparerai come utilizzare una formula di Excel per trovare l'ultima occorrenza di un elemento in un elenco.
Di recente, stavo lavorando a un ordine del giorno di una riunione.
Ho un elenco in Excel con un elenco di persone e le date in cui erano "presidenti della riunione".
Devo anche sapere quando una persona è stata l'ultima "presidente della riunione" a causa della duplicazione nell'elenco (il che significa che una persona è stata presidente della riunione più volte).
Questo perché devo assicurarmi che il presidente più recente non venga riassegnato.
Quindi ho deciso di utilizzare alcune funzioni magiche di Excel per fare il lavoro.
Di seguito è riportato il risultato finale, posso 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, sai che non esiste alcuna funzione di Excel in grado di farlo.Qui facciamo accadere miracoli.
In questo tutorial, ti mostrerò tre modi per farlo.
Contenuti
Trova l'ultima occorrenza - usando la funzione MAX
Ecco la formula di Excel che restituirà l'ultimo valore nell'elenco:
=INDEX($B$2:$B$14,SUMPRODUCT(MAX(行($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 perché è sulla riga 11.Poiché la nostra lista inizia dalla seconda riga, viene sottratto 1.Pertanto, l'ultima occorrenza di Glen è 10 nella nostra lista.
- SUMPRODUCT viene utilizzato per assicurarsi che non sia necessario utilizzare Control + Shift + Invio perché SUMPRODUCT può gestire formule di matrice.
- La funzione INDICE viene ora utilizzata per trovare la data dell'ultimo nome corrispondente.
Trova l'ultima occorrenza - usando la funzione CERCA
Ecco un'altra formula che fa lo stesso lavoro:
=LOOKUP(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 un nome corrispondente e un errore in caso contrario.Quindi finisci con un array.例如，查找值為 Glen，數組將為 {#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 parametro ([result_vector]) è l'intervallo in cui fornisce il risultato, in questo caso la data.
Il motivo per cui questa formula funziona è che la funzione CERCA utilizza una tecnica di corrispondenza approssimativa.Ciò significa che se riesce a trovare una corrispondenza esatta, verrà restituita, ma in caso contrario esaminerà l'intero array fino alla fine e restituirà il valore successivo più grande inferiore al valore di ricerca.
In questo caso, il valore di ricerca è 2 e nel nostro array otteniamo solo 1 o un errore.Quindi esegue la scansione dell'intero array e restituisce la posizione dell'ultimo 1, l'ultimo valore corrispondente per il nome.
Trova l'ultima occorrenza - Utilizzo della funzione personalizzata (VBA)
Lascia che ti mostri anche un altro modo.
Possiamo creare funzioni personalizzate (note anche come funzioni definite dall'utente) utilizzando VBA.
Il vantaggio della creazione di funzioni personalizzate è la facilità d'uso.Non devi preoccuparti di creare formule complesse ogni volta perché la maggior parte del lavoro avviene nel back-end VBA.
Ho creato una formula semplice (molto simile alla formula CERCA.VERT).
Per creare funzioni personalizzate, è necessario il codice VBA nell'editor VB.Ti darò il codice e i passaggi per inserirlo nell'editor VB in seguito, ma lascia che ti mostri prima come funziona:
Ecco la formula che ti darà il risultato:
=Ricerca ultimo oggetto($D$3,$A$2:$B$14,2)
La formula accetta tre parametri:
- Trova il valore (questo sarà il nome nella cella D3)
- Trova l'intervallo (questo sarà l'intervallo con nome e data – A2:B14)
- numero di colonna (questa è la colonna per la quale vogliamo il risultato)
Dopo aver creato la formula e inserito il codice nell'editor VB, puoi utilizzarlo come qualsiasi altra normale funzione del foglio di lavoro di Excel.
Ecco il codice della formula:
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) Exit Function End If Next i End Function
Ecco i passaggi per inserire questo codice nell'editor VB:
- Vai alla scheda sviluppatore.
- Fare clic su Opzioni di Visual Basic.Questo aprirà l'editor VB nel backend.
- Nel riquadro Esplora progetti dell'editor VB fare clic con il pulsante destro del mouse su qualsiasi oggetto nella cartella di lavoro in cui si desidera inserire il codice.Se non vedi Project Explorer, vai alla scheda Visualizza e fai clic su Project Explorer.
- Vai su Inserisci e fai clic su Moduli.Questo inserirà un oggetto modulo per la tua cartella di lavoro.
- Copia e incolla il codice nella finestra del modulo.
Ora la formula sarà disponibile in tutti i fogli della cartella di lavoro.
Tieni presente che dovrai salvare la cartella di lavoro in formato .XLSM poiché contiene macro.Inoltre, se si desidera che questa formula sia disponibile in tutte le cartelle di lavoro utilizzate, è possibile salvarla nella cartella di lavoro macro personale o creare un componente aggiuntivo da essa.
