Come si crea un intervallo denominato dinamico in Excel?
Normalmente, gli intervalli denominati sono estremamente utili per gli utenti di Excel: puoi definire una serie di valori in una colonna, assegnarle un nome e poi fare riferimento a quell’intervallo usando il nome anziché i riferimenti di cella. Tuttavia, nella maggior parte dei casi, in futuro dovrai aggiungere nuovi dati per espandere l’intervallo a cui fai riferimento. In tal caso, dovrai tornare su Formule > Gestione nomi e ridefinire l’intervallo in modo da includere i nuovi valori. Per evitare questa operazione manuale ogni volta che aggiungi una riga o una colonna al tuo elenco, puoi creare un intervallo denominato dinamico: un intervallo che si aggiorna automaticamente senza dover modificare manualmente i riferimenti di cella.
Creare un intervallo denominato dinamico in Excel mediante la creazione di una tabella
Creare un intervallo denominato dinamico in Excel con una funzione
Creare un intervallo denominato dinamico in Excel con codice VBA
Creare un intervallo denominato dinamico in Excel mediante la creazione di una tabella
Se utilizzi Excel 2007 o versioni successive, il modo più semplice per creare un intervallo denominato dinamico è creare una tabella Excel con nome.
Immagina di avere un intervallo di dati come quello seguente, che devi trasformare in un intervallo denominato dinamico.
1. Innanzitutto, definirò un nome per questo intervallo di celle. Seleziona l’intervallo A1:A6 e digita il nome Data nella Casella nome, quindi premi Invio. Allo stesso modo, assegna il nome PrezzoVendita all’intervallo B1:B6. Contemporaneamente, inserisci la formula =SOMMA(PrezzoVendita) in una cella vuota; vedi lo screenshot:
2. Seleziona l’intervallo e fai clic su Inserisci > Tabella; vedi lo screenshot:
3. Nella finestra di dialogo Crea tabella, seleziona l’opzione La tabella include intestazioni (deselezionala se l’intervallo non contiene intestazioni), quindi fai clic sul pulsante OK; i dati dell’intervallo verranno così convertiti in una tabella. Vedere gli screenshot:
 |  |  |
4. Inoltre, quando si inseriscono nuovi valori dopo i dati esistenti, l’intervallo denominato viene regolato automaticamente e la formula creata viene aggiornata di conseguenza. Vedere gli screenshot seguenti:
 | |  |
Note:
1. I nuovi dati inseriti devono essere adiacenti a quelli esistenti, senza righe o colonne vuote tra di essi.
2. All’interno della tabella è possibile inserire dati tra i valori esistenti.
Creare un intervallo denominato dinamico in Excel con una funzione
In Excel 2003 o versioni precedenti, il primo metodo non è disponibile; ecco quindi un’alternativa. La seguente funzione OFFSET( ) può svolgere questa operazione, sebbene sia un po’ complessa. Supponiamo di avere un intervallo di dati con nomi di celle già definiti, ad esempio A1:A6, il cui nome è Data, e B1:B6, il cui nome è PrezzoVendita; contemporaneamente, creiamo una formula per il PrezzoVendita. Vedere lo screenshot:
È possibile trasformare il Nome della cella in un Nome della cella dinamico seguendo questi passaggi:
1. Fare clic su Formule > Gestione nomi; vedere lo screenshot:
2. Nella finestra di dialogo Gestione nomi, seleziona l’elemento desiderato e fai clic sul pulsante Modifica.
3. Nella finestra di dialogo Modifica nome visualizzata, inserisci la formula =OFFSET(Foglio1!$A$1; 0; 0; CONTA.VALORI($A:$A); 1) nella casella di testo Si riferisce a; vedi lo screenshot:
4. Fare quindi clic su OK e ripetere i passaggi 2 e 3 per copiare questa formula =OFFSET(Foglio1!$B$1; 0; 0; CONTA.VALORI($B:$B)), 1 nella casella di testo Si riferisce a relativa al nome della cella PrezzoVendita.
5. Gli intervalli denominati dinamici sono ora pronti! Ogni volta che aggiungi nuovi valori dopo i dati esistenti, l’intervallo denominato si adatta automaticamente e la formula collegata viene aggiornata di conseguenza. Guarda gli screenshot:
| | |
Nota: Se nell’intervallo sono presenti celle vuote, il risultato della formula sarà errato. Questo accade perché le celle non vuote non vengono conteggiate, riducendo così la lunghezza effettiva dell’intervallo ed escludendo le ultime celle.
Suggerimento: spiegazione della formula:
- =OFFSET(riferimento,righe,colonne,[altezza],[larghezza])
- =OFFSET(Foglio1!$A$1; 0; 0; CONTA.VALORI($A:$A); 1)
- riferimentocorrisponde alla cella iniziale; in questo esempio Foglio1!$A$1;
- riga indica il numero di righe da spostare verso il basso rispetto alla cella iniziale (o verso l’alto se si utilizza un valore negativo). In questo esempio, 0 indica che l’elenco partirà dalla prima riga in giù.
- colonna corrisponde al numero di colonne da spostare a destra rispetto alla cella iniziale (o a sinistra se si utilizza un valore negativo). Nella formula riportata sopra, 0 indica di non espandere alcuna colonna a destra.
- [altezza] corrisponde all’altezza (ovvero al numero di righe) dell’intervallo a partire dalla posizione aggiustata. $A:$A conterà tutti gli elementi inseriti nella colonna A.
- [larghezza] indica la larghezza (ossia il numero di colonne) dell’intervallo a partire dalla posizione aggiustata. Nella formula indicata sopra, l’elenco avrà una larghezza di 1 colonna.
È possibile personalizzare questi argomenti in base alle proprie esigenze.
Creare un intervallo denominato dinamico in Excel con codice VBA
Se si dispone di più colonne, è possibile ripetere e inserire una formula distinta per ciascuna delle colonne rimanenti, ma si tratterebbe di un processo lungo e ripetitivo. Per semplificare l’operazione, è possibile utilizzare un codice per creare automaticamente l’intervallo denominato dinamico.
1. Attiva il foglio di lavoro.
2. Tenere premuti i tasti ALT + F11 per aprire la finestra di Microsoft Visual Basic, Applications.
3. Fare clic su Inserisci > Modulo e incollare il codice seguente nella finestra del modulo.
Codice VBA: creare un intervallo denominato dinamico
Sub CreateNamesxx()
'Update 20131128
Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String
Const Rowno = 1
Const Colno = 1
Const Offset = 1
On Error Resume Next
Set wb = ActiveWorkbook
Set ws = ActiveSheet
lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address
wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno & ")"
wb.Names.Add Name:="myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
For i = Colno To lcol
myName = Replace(Cells(Rowno, i).Value, " ", "_")
If myName <> "" Then
wb.Names.Add Name:=myName, RefersToR1C1:="=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
End If
Next
End Sub
4. Premere quindi il tasto F5 per eseguire il codice: verranno generati alcuni intervalli denominati dinamici, denominati in base ai valori della prima riga, e verrà creato anche un intervallo dinamico denominato MieiDati che comprende l’intero insieme di dati.
5. Quando si inseriscono nuovi valori dopo le righe o le colonne, l’intervallo si espande automaticamente. Guarda gli screenshot:
 |
 |
 |
Note:
1. Con questo codice, i nomi delle celle non vengono visualizzati nella Casella nome; per visualizzarli e utilizzarli comodamente, ho installato Kutools per Excel, che grazie alla sua funzionalità Navigazione, elenca i nomi dinamici delle celle creati.
2. Grazie a questo codice, l’intero intervallo di dati può espandersi sia in verticale che in orizzontale; tuttavia, ricorda che non devono esserci righe o colonne vuote tra i dati quando inserisci nuovi valori.
3. Quando si utilizza questo codice, l’intervallo dati deve iniziare dalla cella A1.
Articolo correlato:
Come aggiornare automaticamente un grafico dopo aver inserito nuovi dati in 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