Come creare un intervallo denominato dinamico in Excel?

Normalmente, gli intervalli denominati sono molto utili per gli utenti di Excel. È possibile definire una serie di valori in una colonna, dare un nome a quella colonna e poi fare riferimento a quell'intervallo tramite il nome invece che tramite i riferimenti delle celle. Ma la maggior parte delle volte, è necessario aggiungere nuovi dati per espandere i valori dei dati dell'intervallo a cui si fa riferimento in futuro. In questo caso, bisogna tornare su Formule > Gestione Nomi e ridefinire l'intervallo per includere il nuovo valore. Per evitare ciò, è possibile creare un intervallo denominato dinamico, il che significa che non è necessario regolare i riferimenti delle celle ogni volta che si aggiunge una nuova riga o colonna alla lista.

Crea un intervallo denominato dinamico in Excel creando una tabella

Crea un intervallo denominato dinamico in Excel con Funzione

Crea un intervallo denominato dinamico in Excel con codice VBA

Crea un intervallo denominato dinamico in Excel creando una tabella

Se stai utilizzando Excel 2007 o versioni successive, il modo più semplice per creare un intervallo denominato dinamico è quello di creare una tabella Excel denominata.

Supponiamo di avere un intervallo di dati seguenti che deve diventare un intervallo denominato dinamico.

1. Innanzitutto, definirò i nomi degli intervalli per questo intervallo. Seleziona l'intervallo A1:A6 e inserisci il nome Data nella Casella Nome, quindi premi il tasto Invio. Per definire un nome per l'intervallo B1:B6 come PrezzoVendita nello stesso modo. Allo stesso tempo, creo una formula =somma(PrezzoVendita) in una cella vuota, vedi screenshot:

2. Seleziona l'intervallo e fai clic su Inserisci > Tabella, vedi screenshot:

3. Nella finestra di dialogo Crea Tabella, seleziona La mia tabella ha intestazioni (se l'intervallo non ha intestazioni, deselezionala), fai clic sul pulsante OK, e i dati dell'intervallo saranno convertiti in tabella. Vedi screenshot:

4. E quando inserisci nuovi valori dopo i dati, l'intervallo denominato si adatterà automaticamente e anche la formula creata verrà modificata. Vedi gli screenshot seguenti:

Note:

1. I nuovi dati inseriti devono essere adiacenti ai dati precedenti, il che significa che non ci devono essere righe o colonne vuote tra i nuovi dati e i dati esistenti.

2. Nella tabella, è possibile inserire dati tra i valori esistenti.

Crea un intervallo denominato dinamico in Excel con Funzione

In Excel 2003 o versioni precedenti, il primo metodo non sarà disponibile, quindi ecco un altro modo per te. La seguente funzione OFFSET() può fare questo favore per te, ma è un po' complicato. Supponiamo di avere un intervallo di dati che contiene i nomi degli intervalli che ho definito, ad esempio, A1:A6 il nome dell'intervallo è Data, e il nome dell'intervallo B1:B6 è PrezzoVendita, allo stesso tempo, creo una formula per PrezzoVendita. Vedi screenshot:

È possibile cambiare i nomi degli intervalli in nomi di intervalli dinamici con i seguenti passaggi:

1. Vai su Formule > Gestione Nomi, vedi screenshot:

2. Nella finestra di dialogo Gestione Nomi, seleziona l'elemento che desideri utilizzare e fai clic sul pulsante Modifica.

3. Nella finestra di dialogo Modifica Nome visualizzata, inserisci questa formula =OFFSET(Foglio1!$A$1, 0, 0, CONTA.VALORI($A:$A), 1) nella casella di testo Si riferisce a, vedi screenshot:

4. Poi fai clic su OK, e ripeti il passaggio 2 e il passaggio 3 per copiare questa formula =OFFSET(Foglio1!$B$1, 0, 0, CONTA.VALORI($B:$B), 1) nella casella di testo Si riferisce a per il nome dell'intervallo PrezzoVendita.

5. E gli intervalli denominati dinamici sono stati creati. Quando inserisci nuovi valori dopo i dati, l'intervallo denominato si adatterà automaticamente e anche la formula creata verrà modificata. Vedi gli screenshot:

Nota: Se ci sono celle vuote nel mezzo del tuo intervallo, il risultato della tua formula sarà errato. Questo perché le celle non vuote non vengono conteggiate, quindi il tuo intervallo sarà più corto di quanto dovrebbe essere, e le ultime celle nell'intervallo verranno lasciate fuori.

Suggerimento: spiegazione di questa formula:

• =OFFSET(riferimento,righe,colonne,[altezza],[larghezza])
• 
• =OFFSET(Foglio1!$A$1, 0, 0, CONTA.VALORI($A:$A), 1)

• riferimento corrisponde alla posizione della cella iniziale, in questo esempio Foglio1!$A$1;
• riga si riferisce al numero di righe che ti muoverai verso il basso, rispetto alla cella iniziale (o verso l'alto, se usi un valore negativo). In questo esempio, 0 indica che l'elenco inizierà dalla prima riga verso il basso.
• colonna corrisponde al numero di colonne che ti muoverai a destra, rispetto alla cella iniziale (o a sinistra, usando un valore negativo). Nella formula sopra riportata, 0 indica espandere 0 colonne a destra.
• [altezza] corrisponde all'altezza (o al numero di righe) dell'intervallo che inizia dalla posizione regolata. $A:$A, conterà tutti gli elementi inseriti nella colonna A.
• [larghezza] corrisponde alla larghezza (o al numero di colonne) dell'intervallo che inizia dalla posizione regolata. Nella formula sopra riportata, l'elenco sarà largo 1 colonna.

Puoi modificare questi argomenti secondo necessità.

Crea un intervallo denominato dinamico in Excel con codice VBA

Se hai più colonne, potresti ripetere e inserire formule individuali per tutte le colonne rimanenti, ma sarebbe un processo lungo e ripetitivo. Per semplificare le cose, puoi usare un codice per creare automaticamente l'intervallo denominato dinamico.

1. Attiva il tuo foglio di lavoro.

2. Tieni premuti i tasti ALT + F11, e si aprirà la finestra Microsoft Visual Basic for Applications.

3. Fai clic su Inserisci > Modulo, e incolla il seguente codice nella Finestra Modulo.

Codice VBA: crea 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. Poi premi il tasto F5 per eseguire il codice, e verranno generati alcuni intervalli denominati dinamici che sono nominati con i valori della prima riga e crea anche un intervallo dinamico chiamato MyData che copre l'intero set di dati.

5. Quando inserisci nuovi valori dopo le righe o le colonne, l'intervallo verrà espanso. Vedi gli screenshot:

Note:

1. Con questo codice, i nomi degli intervalli non vengono visualizzati nella Casella Nome, per visualizzare e utilizzare comodamente i nomi degli intervalli, ho installato Kutools per Excel, con il suo Riquadro di Navigazione, i nomi degli intervalli dinamici creati sono elencati.

2. Con questo codice, l'intero intervallo dei dati può essere espanso verticalmente o orizzontalmente, ma ricorda che non ci devono essere righe o colonne vuote tra i dati quando inserisci nuovi valori.

3. Quando utilizzi questo codice, il tuo intervallo di dati deve iniziare dalla cella A1.

Articolo correlato:

Come aggiornare automaticamente un grafico dopo aver inserito nuovi dati in Excel?