Come trovare e restituire il penultimo valore in una specifica riga o colonna in Excel?
Quando si lavora con fogli di lavoro Excel di grandi dimensioni, spesso non basta identificare l’ultimo valore in una riga o colonna: serve il penultimo. Ad esempio, come illustrato nell’immagine seguente, dato l’intervallo A1:E16, potresti aver bisogno di recuperare il penultimo valore nella sesta riga o nella colonna B. Questa esigenza è frequente quando si tracciano modifiche recenti, si monitorano serie temporali o si analizzano voci recenti ma non più aggiornate in un set di dati. Rispetto alla ricerca dell’ultimo valore, trovare il penultimo può risultare meno immediato, soprattutto se i dati contengono celle vuote o vengono aggiornati regolarmente. Questo articolo ti offre soluzioni chiare e dettagliate per eseguire questa operazione in modo efficiente ed evitare errori comuni.
- Trova e restituisci il penultimo valore in una determinata riga o colonna utilizzando formule
- Codice VBA – Utilizza una macro per trovare il penultimo valore in una riga o colonna specificata
- Altri metodi integrati di Excel – Filtra tutte le celle vuote e identifica manualmente il penultimo valore
Trova e restituisci il penultimo valore in una determinata riga o colonna utilizzando formule
Come illustrato nell’immagine sopra, per trovare e restituire il penultimo valore nella sesta riga o nella colonna B all’interno dell’intervallo A1:E16, le formule Excel offrono una soluzione dinamica ed efficiente. Sono la scelta ideale quando i tuoi dati vengono aggiornati di frequente o quando la posizione del penultimo valore può variare a seguito di inserimenti o eliminazioni. A differenza dei metodi manuali, le formule assicurano che il risultato si aggiorni automaticamente in base ai dati più recenti.
Trova e restituisci il penultimo valore nella colonna B
1. Seleziona una cella vuota in cui desideri visualizzare il penultimo valore. Inserisci la seguente formula matriciale nella Barra delle formule, quindi premi Ctrl + Maiusc + Invio (per le versioni legacy di Excel) per confermarla come formula matriciale. In Excel 365 o Excel 2021, ti basta premere Invio, poiché le formule dinamiche vengono gestite automaticamente.
=INDEX(B:B,LARGE(IF(B:B<>"",ROW(B:B)),2))
Nota: in questa formula, B:B si riferisce alla colonna B. Se devi ottenere il penultimo valore in un’altra colonna, modifica semplicemente B:Bcon il riferimento alla colonna desiderata (ad esempio, sostituiscilo con)C:C per la colonna C). Questo metodo funziona per intervalli con o senza celle vuote, ma se sono presenti valori nascosti o dati esclusi da un filtro, potrebbero verificarsi discrepanze. Verifica sempre attentamente il tuo intervallo dati se non ottieni il valore previsto.
Trova e restituisci il penultimo valore nella riga 6
Seleziona una cella vuota per visualizzare il risultato relativo alla sesta riga. Quindi inserisci la seguente formula nella Barra delle formule e premi Invio per confermare.
=OFFSET($A$6,0, COUNTA(6:6)-2,1,1)
Nota: nella formula sopra, $A$6 è la prima cella della riga 6, mentre 6:6indica l’intera riga 6. Modifica questi riferimenti per puntare a righe diverse, se necessario. La formula conta dinamicamente il numero di celle non vuote nella riga 6, calcolando quindi uno scostamento dalla cella iniziale per individuare la penultima cella compilata. Se la tua riga contiene formule che restituiscono stringhe vuote ()""), la funzione CONTA.VALORI potrebbe comunque considerarle come non vuote, influenzando il risultato. Per intervalli che includono sia costanti che formule, verifica attentamente i risultati per evitare errori.
Suggerimenti per l’uso delle formule:
- Con set di dati di grandi dimensioni, l'uso di riferimenti a intere colonne (ad esempio,)
B:B) può compromettere le prestazioni. Se possibile, limita l'intervallo all'area effettivamente necessaria (ad esempio,B1:B100). - Se i tuoi dati includono righe nascoste o sono filtrati, le formule continueranno a includere nel calcolo anche le celle nascoste o escluse dal filtro. Per gestire correttamente i dati filtrati, considera l’uso di funzioni SUBTOTALE dedicate o di colonne di supporto.
- Se tutti i valori in una riga o colonna di destinazione sono vuoti, queste formule potrebbero generare un errore o un risultato imprevisto: assicurati che i tuoi dati includano almeno due valori non vuoti.
Codice VBA – Utilizza una macro per trovare il penultimo valore in una riga o colonna specificata
Per chi ha spesso la necessità di individuare il penultimo valore in intervalli variabili o di grandi dimensioni, automatizzare il processo con una macro VBA può far risparmiare un sacco di tempo—soprattutto quando si lavora con tabelle dinamiche o complesse. Le macro si rivelano particolarmente utili quando il set di dati cambia dimensione e si vuole evitare di aggiornare manualmente le formule ogni volta, oppure quando occorre estrarre valori da più posizioni diverse con lo stesso metodo. Di seguito trovi una soluzione in codice VBA che ti permette di selezionare semplicemente la riga o la colonna di destinazione e ottieni automaticamente il penultimo valore, senza dover contare o apportare aggiustamenti manuali.
1. Vai agli Strumenti sviluppo nella Barra multifunzione di Excel, fai clic su Visual Basic per aprire l’editor VBA. Nell’editor, fai clic su Inserisci > Modulo. Quindi, incolla il seguente codice VBA nel modulo appena creato:
Sub GetSecondToLastValue()
Dim rng As Range
Dim arr As Variant
Dim values As Collection
Dim i As Long
Dim secondLast As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.InputBox("Select the row or column range to analyze", xTitleId, Selection.Address, Type:=8)
If rng Is Nothing Then Exit Sub
arr = rng.Value
Set values = New Collection
If rng.Rows.Count = 1 Then
For i = 1 To rng.Columns.Count
If arr(1, i) <> "" Then
values.Add arr(1, i)
End If
Next i
ElseIf rng.Columns.Count = 1 Then
For i = 1 To rng.Rows.Count
If arr(i, 1) <> "" Then
values.Add arr(i, 1)
End If
Next i
Else
MsgBox "Please select a single row or single column range.", vbExclamation
Exit Sub
End If
If values.Count < 2 Then
MsgBox "There are less than two non-blank values in the selected range.", vbInformation
Exit Sub
End If
secondLast = values(values.Count - 1)
MsgBox "The second-to-last value is: " & secondLast, vbInformation
End Sub2. Dopo aver inserito il codice, torna a Excel ed esegui la macro usando il pulsante 
Esegui, oppure premendo Alt + F8 e selezionando GetSecondToLastValue dall’elenco. Ti verrà chiesto di selezionare un intervallo costituito da una singola riga o colonna (ad esempio B1:B16 per una colonna o A6:E6 per una riga). Dopo aver confermato la selezione, la macro mostrerà il penultimo valore in una finestra di dialogo.
Note:
- Questa macro conta esclusivamente le celle non vuote, ignorando automaticamente quelle vuote presenti nella riga o colonna selezionata.
- Se la tua selezione contiene meno di due valori non vuoti, riceverai un messaggio di avviso e nessun valore verrà restituito.
- La macro è progettata per selezioni su una singola riga o una singola colonna. Se selezioni contemporaneamente più righe e colonne, ti verrà chiesto di modificare la tua selezione.
- Per automatizzare ulteriormente il processo, puoi modificare il codice in modo che il risultato venga copiato direttamente in una cella specifica del foglio di lavoro, anziché essere visualizzato in una finestra di messaggio.
Questa soluzione VBA è ideale per chi lavora con tabelle pivot o set di dati in continua evoluzione, eliminando errori manuali e automatizzando attività ripetitive.
Altri metodi integrati di Excel – Filtra tutte le celle vuote e identifica manualmente il penultimo valore
Sebbene formule e macro offrano metodi automatizzati per individuare il penultimo valore, a volte potresti preferire un approccio visivo e immediato—soprattutto con dati irregolari o non contigui, o quando devi verificare un valore solo occasionalmente. Grazie agli strumenti di filtro integrati di Excel, puoi nascondere temporaneamente celle vuote o irrilevanti, individuando così con facilità il valore cercato senza dover modificare o scrivere alcuna formula.
Metodo del filtraggio:
- Seleziona l’intervallo dati per la tua colonna o riga (per le colonne, scegli le celle B1:B16; per le righe, seleziona A6:E6 nel tuo foglio di lavoro).
- Nella barra multifunzione di Excel, fai clic su Dati > Filtro per attivare le frecce del filtro. Nell’intestazione della colonna o accanto al tuo intervallo selezionato, fai clic sul menu a discesa del filtro.
- Deseleziona (Celle vuote) per nascondere le celle vuote. Ora l’elenco visualizzato conterrà solo valori non vuoti.
- Per una colonna, scorri fino in fondo all’elenco filtrato e visualizza il penultimo valore. Per una riga (dopo aver applicato il filtro, se possibile), conta da destra per individuare il penultimo valore non vuoto.
Note e suggerimenti:
- Il filtraggio è il metodo più rapido per elenchi di piccole o medie dimensioni, oppure quando devi confermare visivamente la posizione dei valori.
- Se il tuo set di dati è molto grande, il filtraggio potrebbe rallentare le prestazioni e non è la scelta ideale per automazioni o utilizzi ripetuti.
- Quando applichi dei filtri, assicurati di tenere sempre presente il contesto corretto: gli elenchi filtrati potrebbero infatti saltare righe o colonne nascoste.
- Per rimuovere i filtri, fai clic su Cancella nella scheda Dati.
Questo metodo è meno adatto per analisi dinamiche continue o set di dati di grandi dimensioni, ma garantisce comunque un controllo manuale trasparente del penultimo valore non vuoto, ideale per verifiche rapide o troubleshooting.
Articoli correlati:
- Come trovare la posizione del primo o dell’ultimo numero all’interno di una stringa di testo in Excel?
- Come trovare il primo o l’ultimo venerdì di ogni mese in Excel?
- Come usare CERCA.VERT per trovare il primo, il secondo o l’ennesimo valore corrispondente in Excel?
- Come trovare il valore con la frequenza più alta in un intervallo di Excel?
Migliori Strumenti per la Produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un’efficienza mai vista prima.Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e Risparmia tempo.Clicca qui per ottenere la funzionalità di cui hai più bisogno...
Office Tab Porta l'interfaccia a schede in Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede all’interno della stessa finestra, invece che in finestre separate.
- Aumenta la tua produttività del 50 % e risparmia centinaia di clic del mouse ogni giorno!
Tutti i componenti aggiuntivi di Kutools in un unico programma di installazione.
Kutools for Office è la suite che include componenti aggiuntivi per Excel, Word, Outlook e PowerPoint, oltre a Office Tab Pro: la soluzione ideale per i team che lavorano su diverse app di Office.
- Suite completa— componenti aggiuntivi per Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Un unico programma di installazione, una sola licenza— configurazione in pochi minuti (pronto per MSI)
- Funziona meglio insieme— produttività ottimizzata tra le app di Office
- Prova gratuita di 30 giorni con tutte le funzionalità— nessuna registrazione, nessuna carta di credito
- Miglior rapporto qualità-prezzo— risparmia rispetto all’acquisto dei singoli componenti aggiuntivi