Come creare un intervallo denominato dinamico in Excel?
Normalmente, Gamme nominate sono molto utili per gli utenti di Excel, puoi definire una serie di valori in una colonna, dare a quella colonna un nome e poi puoi fare riferimento a quell'intervallo per nome invece che per i suoi riferimenti di cella. Ma la maggior parte delle volte, è necessario aggiungere nuovi dati per espandere i valori dei dati dell'intervallo indicato in futuro. In questo caso, devi tornare a Formule > Nome Manager e ridefinire l'intervallo per includere il nuovo valore. Per evitare ciò, puoi creare un intervallo denominato dinamico, il che significa che non è necessario regolare i riferimenti di cella ogni volta che aggiungi una nuova riga o colonna all'elenco.
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 si utilizza Excel 2007 o versioni successive, il modo più semplice per creare un intervallo denominato dinamico è creare una tabella Excel con nome.
Supponiamo che tu abbia una serie di dati seguenti che devono diventare un intervallo denominato dinamico.
1. In primo luogo, definirò i nomi degli intervalli per questo intervallo. Seleziona l'intervallo A1: A6 e inserisci il nome Data nella Nome casella, Quindi premere entrare chiave. Per definire un nome per l'intervallo B1: B6 come prezzo scontato allo stesso modo. Allo stesso tempo, creo una formula = sum (prezzo scontato) in una cella vuota, vedi screenshot:
2. Seleziona l'intervallo e fai clic inserire > Table, vedi screenshot:
3. Nel Crea tabella casella di richiesta, selezionare La mia tabella ha intestazioni (se l'intervallo non ha intestazioni, deselezionalo), fai clic su OK e i dati dell'intervallo sono stati convertiti in tabella. Vedi screenshot:
 |
 |
 |
4. E quando inserisci nuovi valori dopo i dati, l'intervallo denominato verrà regolato automaticamente e anche la formula creata verrà modificata. Vedi le seguenti schermate:
 |
|
 |
Note:
1. I nuovi dati immessi devono essere adiacenti ai dati sopra, significa che non ci sono righe o colonne vuote tra i nuovi dati e quelli 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. Il seguente COMPENSARE( ) la funzione può farti questo favore, ma è un po 'fastidioso. Supponendo di avere un intervallo di dati che contiene i nomi degli intervalli che ho definito, ad esempio, LA1: LA6 il nome dell'intervallo è Datae B1: B6 il nome dell'intervallo è Prezzo di vendita, allo stesso tempo, creo una formula per il Prezzo di vendita. Vedi screenshot:
Puoi modificare i nomi degli intervalli in nomi degli intervalli dinamici con i seguenti passaggi:
1. Vai a fare clic Formule > Nome Manager, vedi screenshot:
2. Nel Nome Manager finestra di dialogo, selezionare l'elemento che si desidera utilizzare e fare clic Modifica pulsante.
3. Nel saltò fuori Modifica nome finestra di dialogo, immettere questa formula = OFFSET (Foglio1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1) nella Si riferisce a casella di testo, vedi screenshot:
4. Quindi fare clic OKe quindi ripetere i passaggi 2 e 3 per copiare questa formula = OFFSET (Foglio1! $ B $ 1, 0, 0, COUNTA ($ B: $ B), 1) nel file Si riferisce a casella di testo per il Prezzo di vendita nome dell'intervallo.
5. E gli intervalli denominati dinamici sono stati creati. Quando si immettono nuovi valori dopo i dati, l'intervallo denominato verrà regolato automaticamente e verrà modificata anche la formula creata. Vedi screenshot:
|
|
|
Nota: Se ci sono celle vuote al centro dell'intervallo, il risultato della formula sarà sbagliato. Questo perché le celle non vuote non vengono conteggiate, quindi il tuo intervallo sarà più breve di quanto dovrebbe e le ultime celle dell'intervallo verranno tralasciate.
Suggerimento: spiegazione per questa formula:
- = OFFSET (riferimento, righe, colonne, [altezza], [larghezza])
- = OFFSET (Foglio1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1)
- riferimento corrisponde alla posizione della cella iniziale, in questo esempio Foglio1! $ A $ 1;
- fila si riferisce al numero di righe che stai per spostare 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 sposterai a destra, rispetto alla cella iniziale (oa sinistra, utilizzando un valore negativo.), nella formula di esempio sopra, 0 indica espandere 0 colonne a destra.
- [altezza] corrisponde all'altezza (o al numero di righe) dell'intervallo a partire dalla posizione regolata. $ A: $ A, conterà tutti gli elementi inseriti nella colonna A.
- [larghezza] corrisponde alla larghezza (o al numero di colonne) dell'intervallo a partire dalla posizione regolata. Nella formula sopra, l'elenco sarà largo 1 colonna.
Puoi modificare questi argomenti in base alle tue necessità.
Crea un intervallo denominato dinamico in Excel con codice VBA
Se hai più colonne, puoi ripetere e inserire una formula individuale per tutte le colonne rimanenti, ma sarebbe un processo lungo e ripetitivo. Per semplificare le cose, puoi utilizzare un codice per creare automaticamente l'intervallo denominato dinamico.
1. Attiva il tuo foglio di lavoro.
2. Tieni premuto il ALT + F11 chiavi e apre il file Finestra di Microsoft Visual Basic, Applications Edition.
3. Clic inserire > Modulie incolla il codice seguente nel file Finestra del modulo.
Codice VBA: crea 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. Quindi premere F5 chiave per eseguire il codice e verranno generati alcuni intervalli denominati dinamici che sono denominati con i valori della prima riga e crea anche un intervallo dinamico chiamato I miei dati che copre tutti i dati.
5. Quando inserisci nuovi valori dopo le righe o le colonne, anche l'intervallo verrà espanso. Vedi screenshot:
 |
|
 |
Note:
1. Con questo codice, i nomi degli intervalli non vengono visualizzati nel file Nome casella, per visualizzare e utilizzare comodamente i nomi degli intervalli, ho installato Kutools for Excel, Con la sua Riquadro di spostamento, vengono elencati i nomi dell'intervallo dinamico creato.
2. Con questo codice, l'intero intervallo di dati può essere espanso verticalmente o orizzontalmente, ma per ricordare che non dovrebbero esserci righe o colonne vuote tra i dati quando si immettono nuovi valori.
3. Quando si utilizza questo codice, l'intervallo di dati dovrebbe iniziare dalla cella A1.
Articolo correlato:
Come aggiornare automaticamente un grafico dopo aver inserito nuovi dati in Excel?
I migliori strumenti per la produttività in ufficio
Potenzia le tue competenze di Excel con Kutools per Excel e sperimenta l'efficienza come mai prima d'ora. Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e risparmiare tempo. Fai clic qui per ottenere la funzionalità di cui hai più bisogno...
Office Tab porta l'interfaccia a schede in Office e semplifica notevolmente il tuo lavoro
- 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 della stessa finestra, piuttosto che in nuove finestre.
- Aumenta la produttività del 50% e riduce ogni giorno centinaia di clic del mouse!
