Note: The other languages of the website are Google-translated. Back to English

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

Scheda Office Abilita la modifica a schede e la navigazione in Office e semplifica notevolmente il lavoro ...
Kutools per Excel risolve la maggior parte dei tuoi problemi e aumenta la tua produttività dell'80%
  • Riutilizza qualsiasi cosa: Aggiungi le formule, i grafici e qualsiasi altra cosa più usati o complessi ai tuoi preferiti e riutilizzali rapidamente in futuro.
  • Più di 20 funzioni di testo: Estrai numero dalla stringa di testo; Estrai o rimuovi parte di testi; Converti numeri e valute in parole inglesi.
  • Unisci strumenti: Più cartelle di lavoro e fogli in uno; Unisci più celle / righe / colonne senza perdere dati; Unisci righe duplicate e somma.
  • Strumenti di divisione: Suddivisione dei dati in più fogli in base al valore; Una cartella di lavoro su più file Excel, PDF o CSV; Da una colonna a più colonne.
  • Incolla Salto Righe nascoste / filtrate; Count And Sum di Background Color; Invia e-mail personalizzate a più destinatari in blocco.
  • Super filtro: Crea schemi di filtri avanzati e applicali a qualsiasi foglio; Riordina per settimana, giorno, frequenza e altro; Filtro in grassetto, formule, commenti ...
  • Più di 300 potenti funzionalità; Funziona con Office 2007-2019 e 365; Supporta tutte le lingue; Facile distribuzione nella tua azienda o organizzazione.

freccia blu freccia destra 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.

doc-range-dinamico1

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 Entra 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:

doc-range-dinamico2

2. Seleziona l'intervallo e fai clic inserire > tavolo, vedi screenshot:

doc-range-dinamico3

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:

doc-range-dinamico4 -2 doc-range-dinamico5

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:

doc-range-dinamico6 -2 doc-range-dinamico7

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.


freccia blu freccia destra 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:

doc-range-dinamico2

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:

doc-range-dinamico8

2. Nel Nome Manager finestra di dialogo, selezionare l'elemento che si desidera utilizzare e fare clic Modifica pulsante.

doc-range-dinamico9

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:

doc-range-dinamico10

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:

doc-range-dinamico6 -2 doc-range-dinamico7

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])
  • -1
  • = 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à.


freccia blu freccia destra 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 > Moduloe 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:

doc-range-dinamico12
-1
doc-range-dinamico13

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

Kutools per Excel risolve la maggior parte dei tuoi problemi e aumenta la tua produttività dell'80%

  • Riutilizzo: Inserisci rapidamente formule complesse, grafici e tutto ciò che hai usato prima; Crittografa celle con password; Crea mailing list e invia email ...
  • Bar Super Formula (modifica facilmente più righe di testo e formula); Layout di lettura (leggi e modifica facilmente un gran numero di celle); Incolla su intervallo filtrato...
  • Unisci celle / righe / colonne senza perdere dati; Contenuto delle celle divise; Combina righe / colonne duplicate... Impedisci celle duplicate; Confronta intervalli...
  • Seleziona Duplica o Unico Righe; Seleziona Righe vuote (tutte le celle sono vuote); Super Find e Fuzzy Find in molte cartelle di lavoro; Selezione casuale ...
  • Copia esatta Più celle senza modificare il riferimento della formula; Riferimenti di creazione automatica a più fogli; Inserisci punti elenco, Caselle di controllo e altro ...
  • Estrai testo, Aggiungi testo, Rimuovi per posizione, Rimuovi spazio; Creare e stampare totali parziali di paging; Converti contenuto e commenti tra celle...
  • Super filtro (salva e applica schemi di filtri ad altri fogli); Ordinamento avanzato per mese / settimana / giorno, frequenza e altro; Filtro speciale in grassetto, corsivo ...
  • Combina cartelle di lavoro e fogli di lavoro; Unisci tabelle in base a colonne chiave; Suddividi i dati in più fogli; Conversione in batch xls, xlsx e PDF...
  • Più di 300 potenti funzionalità. Supporta Office / Excel 2007-2019 e 365. Supporta tutte le lingue. Facile distribuzione nella tua azienda o organizzazione. Funzionalità complete Prova gratuita di 30 giorni. Garanzia di rimborso di 60 giorni.
scheda kte 201905

Scheda Office porta l'interfaccia a schede a Office e semplifica notevolmente il 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 centinaia di clic del mouse ogni giorno!
fondo officetab
Commenti (4)
Ancora nessuna valutazione. Puoi essere il primo a votare!
Questo commento è stato fatto dal moderatore sul sito
Grazie per il buon articolo
Questo commento è stato fatto dal moderatore sul sito
Sei un ottimo insegnante: 1) approccio graduale; 2) non annoiare lo studente con materiale o conclusioni evidenti; 3) ma includi tutto il materiale necessario. Non vedo l'ora di ricevere altri tutorial da te.
Questo commento è stato fatto dal moderatore sul sito
per favore aiutami sto cercando di creare un intervallo denominato dinamico su excel 2016 digitando "=offset(DATAENTRY!$B$6,,,counta(DATAENTRY!$B$6:$B$13)))" ma mi dà comunque un errore nel dirlo non è una formula
Questo commento è stato fatto dal moderatore sul sito
davvero, davvero non utile
Non ci sono ancora commenti pubblicati qui
Lasciate i vostri commenti
Pubblicazione come ospite
×
Valuta questo post:
0   Personaggi
Posizioni suggerite