Come fare per ottenere sempre il valore dalla cella soprastante ogni volta che si inserisce o si elimina una riga in Excel?
In Excel, fare riferimento al valore della cella immediatamente sopra è un’esigenza comune per creare totali progressivi, confronti mese per mese o semplicemente per mantenere la coerenza dei dati man mano che il foglio di lavoro evolve. Di solito, puoi fare riferimento alla cella soprastante con una formula diretta come =D5, ma questa soluzione ha dei limiti: se inserisci o elimini righe, la formula potrebbe non puntare più alla nuova cella «soprastante» come ti aspetteresti. Come illustrato di seguito, l’inserimento di una nuova riga sopra può interrompere la continuità o far sì che il riferimento rimanga legato alla cella originale invece di adattarsi automaticamente alla nuova posizione.
Per risolvere questo problema e assicurarti che la tua formula recuperi sempre il valore dalla cella immediatamente sopra—anche dopo aver inserito o eliminato righe—puoi scegliere tra diversi approcci. Ogni metodo comporta compromessi diversi, a seconda della complessità del tuo foglio, della necessità di un aggiornamento automatico o manuale e del tuo livello di dimestichezza con VBA e le macro.
Indice:
- Ottieni sempre il valore dalla cella soprastante quando inserisci o elimini righe con una formula
- Aggiorna automaticamente il valore della cella da quella immediatamente sopra tramite una macro VBA basata su eventi (sempre dinamica)
Ottieni sempre il valore dalla cella soprastante quando inserisci o elimini righe con una formula
Per risolvere questo problema in modo semplice, senza dover ricorrere a macro né a configurazioni complesse, puoi utilizzare una formula che faccia riferimento in modo dinamico alla cella soprastante, indipendentemente da eventuali modifiche alle righe. La formula sfrutta le funzioni INDIRETTO e INDIRIZZO di Excel, in modo che il riferimento “segua” sempre la cella immediatamente sopra, anche se le righe vengono spostate a causa di inserimenti o eliminazioni. Questo la rende ideale per fogli di lavoro in cui modifichi spesso la struttura delle righe, ad esempio aggiungendo nuovi dati all’inizio o nel mezzo di un elenco.
Inserisci la seguente formula direttamente nella cella in cui desideri ottenere sempre il valore da quella soprastante (ad esempio, in cella B6 se vuoi fare riferimento a B5):
=INDIRECT(ADDRESS(ROW()-1,COLUMN()))Premi Invio dopo aver digitato la formula. La cella corrente mostrerà immediatamente il valore della cella immediatamente sopra, come illustrato di seguito:
Ora, se inserisci una nuova riga in qualsiasi punto sopra la cella che contiene la formula, quest’ultima verrà ricalcolata automaticamente e mostrerà sempre il valore della cella immediatamente soprastante. In questo modo, le tue formule resteranno sempre aggiornate, indipendentemente dall’inserimento o dall’eliminazione di righe. Consulta lo screenshot seguente come riferimento:
Spiegazione dei parametri e suggerimenti:
- Questa formula recupera il valore della cella direttamente sopra quella in cui è inserita: se la usi in B6, mostrerà sempre il valore di B5, anche dopo aver inserito o eliminato righe sopra.
- Se utilizzi questa formula nella prima riga dei tuoi dati (ad esempio A1), potrebbe tentare di recuperare dati da una riga inesistente e restituire l’errore
#RIF!. Per evitarlo, aggiungi una gestione degli errori, ad esempio con=IF(ROW()=1,"",INDIRECT(ADDRESS(ROW()-1,COLUMN()))), in modo da visualizzare una cella vuota nella prima riga. - Tieni presente che INDIRETTO è una funzione volatile; pertanto, in fogli di lavoro molto grandi, un uso eccessivo potrebbe rallentare i calcoli.
- Questa formula è ideale quando vuoi mantenere un legame preciso con la posizione della riga, indipendentemente da eventuali modifiche alla struttura del tuo foglio.
Suggerimenti per la risoluzione dei problemi e sintesi:
Se la tua formula non si aggiorna come previsto dopo aver inserito o eliminato righe, ricontrolla che sia inserita nella cella corretta. Assicurati inoltre di non utilizzare riferimenti di cella assoluti (come $A$1), che sono statici. Se incontri errori #RIF! nella prima riga, considera l’uso di una formula condizionale come indicato in precedenza. Per un’automazione avanzata, o se desideri copiare il valore anziché limitarti a farvi riferimento, consulta la soluzione con macro VBA basata su eventi riportata di seguito per un approccio dinamico basato su codice.
Aggiorna automaticamente il valore della cella da quella immediatamente sopra tramite una macro VBA basata su eventi (sempre dinamica)
Se hai bisogno di una soluzione in cui il valore di una cella si aggiorni automaticamente per rispecchiare sempre quello della cella soprastante — anche quando inserisci o elimini righe — e desideri ottenere questo comportamento senza dover copiare manualmente formule, una macro VBA basata su eventi è la scelta ideale. Questo approccio, perfetto per chi è a proprio agio con l’abilitazione delle macro, garantisce un aggiornamento dinamico: ogni volta che selezioni una cella o un intervallo specifico, o modifichi un valore, la macro imposta istantaneamente la cella di destinazione in modo che corrisponda sempre al valore soprastante. Il risultato? Coerenza assoluta nei dati immessi, indipendentemente dalle modifiche apportate al foglio. È particolarmente efficace nei modelli in cui ogni nuova riga deve ereditare automaticamente un valore predefinito o un riferimento fisso.
Ecco come configurarla utilizzando l’evento Worksheet_Change:
1. Fai clic con il pulsante destro del mouse sulla scheda del foglio di lavoro in cui desideri questa funzionalità e seleziona Visualizza codice. Si aprirà l’editor di Microsoft Visual Basic for Applications nel modulo corretto del foglio di lavoro.
2.Copia e incolla il seguente codice VBA nella finestra del modulo del foglio di lavoro:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
On Error Resume Next
' Set the range you want to monitor (for example, B2:B100)
Set WatchRange = Intersect(Target, Me.Range("B2:B100"))
If Not WatchRange Is Nothing Then
Application.EnableEvents = False
Dim cell As Range
For Each cell In WatchRange
' Avoid the first row, or adjust as needed
If cell.Row > 1 Then
cell.Value = Me.Cells(cell.Row - 1, cell.Column).Value
End If
Next cell
Application.EnableEvents = True
End If
End SubNote sui parametri: Sostituisci "B2:B100" in Me.Range("B2:B100")con l’intervallo effettivo in cui desideri applicare questo comportamento (puoi impostarlo su un’intera colonna, ad esempio)"B:B", ma limitare l’intervallo migliora le prestazioni ed evita sovrascritture involontarie).
3.Chiudi l’editor VBA. Da ora in poi, ogni volta che una cella viene modificata, inserita o il foglio di lavoro viene aggiornato all’interno del tuo intervallo limitato, Excel aggiornerà automaticamente tali celle per riflettere il valore della cella soprastante. Ad esempio, se inserisci una riga alla riga 5, tutte le celle monitorate nella colonna B da quel punto in poi assumeranno il valore della cella direttamente sopra la loro nuova posizione.
- Attenzione: questo codice sovrascriverà i valori inseriti manualmente all’interno dell’intervallo monitorato. Usalo con cautela se le tue celle contengono formule o se desideri conservare i valori originali.
- Se desideri continuare a utilizzare questa macro VBA nella cartella di lavoro in futuro, salva il file come cartella di lavoro abilitata alle macro (.xlsm).
- Il codice basato su eventi funziona esclusivamente nel modulo del foglio di lavoro in cui viene incollata la macro (non in tutti i fogli, a meno che lo stesso codice non venga aggiunto al modulo di ciascun foglio).
- Se preferisci che l’aggiornamento avvenga alla selezione di una cella anziché al cambiamento di un valore, puoi utilizzare Worksheet_SelectionChange con una logica simile.
Suggerimenti per la risoluzione dei problemi e sintesi:
Se lo script VBA non sembra funzionare dopo la copia, assicurati che le macro siano abilitate nella tua cartella di lavoro e che tu abbia incollato il codice nel modulo del foglio corretto (non in un modulo standard). Se riscontri errori o Excel si blocca, ricontrolla che Application.EnableEvents sia impostato su False prima delle modifiche automatiche alle celle e reimpostato su True dopo, per evitare cicli ricorsivi. Per altri comportamenti avanzati o un controllo più accurato, considera la scrittura di uno script personalizzato in base alla struttura dei tuoi dati.
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