Come creare intervalli denominati in Excel (una guida passo passo)

Cosa c'è nel nome?

Se stai lavorando con fogli di calcolo Excel, potrebbe significare molto risparmio di tempo ed efficienza.

In questo tutorial imparerai come creare intervalli denominati in Excel e come utilizzarlo per risparmiare tempo.

Intervalli denominati in Excel - Introduzione

Se qualcuno deve chiamarmi o fare riferimento a me, userà il mio nome (invece di dire che un maschio sta nel posto così e così con così e così altezza e peso).

Destra?

Allo stesso modo, in Excel, puoi dare un nome a una cella o a un intervallo di celle.

Ora, invece di usare il riferimento di cella (come A1 o A1:A10), puoi semplicemente usare il nome che gli hai assegnato.

Ad esempio, supponiamo di avere un set di dati come mostrato di seguito:

In questo set di dati, se devi fare riferimento all'intervallo che ha la Data, dovrai utilizzare A2: A11 nelle formule. Allo stesso modo, per Sales Rep e Sales, dovrai utilizzare B2:B11 e C2:C11.

Anche se va bene quando hai solo un paio di punti dati, ma nel caso in cui hai enormi set di dati complessi, l'utilizzo di riferimenti di cella per fare riferimento ai dati potrebbe richiedere molto tempo.

Excel Named Ranges semplifica il riferimento ai set di dati in Excel.

Puoi creare un intervallo denominato in Excel per ogni categoria di dati e quindi utilizzare quel nome invece dei riferimenti di cella. Ad esempio, le date possono essere denominate "Data", i dati del rappresentante di vendita possono essere denominati "Rappresentante di vendita" e i dati di vendita possono essere denominati "Vendite".

Puoi anche creare un nome per una singola cella. Ad esempio, se in una cella è presente la percentuale della commissione di vendita, è possibile denominare quella cella come "Commissione".

Vantaggi della creazione di intervalli denominati in Excel

Ecco i vantaggi dell'utilizzo di intervalli denominati in Excel.

Usa i nomi invece dei riferimenti di cella

Quando crei Intervalli denominati in Excel, puoi utilizzare questi nomi invece dei riferimenti di cella.

Ad esempio, puoi utilizzare =SUM(SALES) invece di =SUM(C2:C11) per il set di dati sopra.

Dai un'occhiata alle formule elencate di seguito. Invece di utilizzare i riferimenti di cella, ho utilizzato gli intervalli denominati.

• Numero di vendite con valore superiore a 500: =COUNTIF(Sales”,>500″)
• Somma di tutte le vendite effettuate da Tom: =SUMIF(SalesRep”,Tom”,Sales)
• Commissione guadagnata da Joe (vendite di Joe moltiplicate per la percentuale di commissione):
  =SOMMA.SE(Rappresentante di vendita”,Joe”,Vendite)*Commissione

Saresti d'accordo che queste formule sono facili da creare e facili da capire (specialmente quando le condividi con qualcun altro o le rivisita tu stesso.

Non è necessario tornare al set di dati per selezionare le celle

Un altro vantaggio significativo dell'utilizzo degli intervalli denominati in Excel è che non è necessario tornare indietro e selezionare gli intervalli di celle.

Puoi semplicemente digitare un paio di alfabeti di quell'intervallo denominato ed Excel mostrerà gli intervalli denominati corrispondenti (come mostrato di seguito):

Gli intervalli denominati rendono le formule dinamiche

Utilizzando gli intervalli denominati in Excel, puoi rendere dinamiche le formule di Excel.

Ad esempio, nel caso della commissione di vendita, invece di utilizzare il valore 2,5%, è possibile utilizzare l'intervallo denominato.

Ora, se la tua azienda decide in seguito di aumentare la commissione al 3%, puoi semplicemente aggiornare l'intervallo denominato e tutto il calcolo si aggiornerà automaticamente per riflettere la nuova commissione.

Come creare intervalli denominati in Excel

Ecco tre modi per creare intervalli denominati in Excel:

Metodo n. 1 - Utilizzo di Definisci nome

Ecco i passaggi per creare intervalli denominati in Excel utilizzando Definisci nome:

• Seleziona l'intervallo per il quale desideri creare un intervallo denominato in Excel.
• Vai su Formule -> Definisci nome.
• Nella finestra di dialogo Nuovo nome, digita il nome che desideri assegnare all'intervallo di dati selezionato. È possibile specificare l'ambito come l'intera cartella di lavoro o un foglio di lavoro specifico. Se si seleziona un foglio particolare, il nome non sarebbe disponibile su altri fogli.
• Fare clic su OK.

Questo creerà un RAPPORTO DI VENDITA di un intervallo denominato.

Metodo n. 2: utilizzo della casella del nome

• Seleziona l'intervallo per il quale desideri creare un nome (non selezionare le intestazioni).
• Vai alla casella Nome a sinistra della barra della formula e digita il nome del con cui vuoi creare l'intervallo con nome.
• Nota che il nome creato qui sarà disponibile per l'intera cartella di lavoro. Se desideri limitarlo a un foglio di lavoro, usa il Metodo 1.

Metodo n. 3: utilizzo dell'opzione Crea da selezione

Questo è il modo consigliato quando si hanno dati in formato tabulare e si desidera creare un intervallo denominato per ogni colonna/riga.

Ad esempio, nel set di dati di seguito, se si desidera creare rapidamente tre intervalli denominati (Date, Sales_Rep e Sales), è possibile utilizzare il metodo mostrato di seguito.

Ecco i passaggi per creare rapidamente intervalli denominati da un set di dati:

• Seleziona l'intero set di dati (incluse le intestazioni).
• Vai a Formule -> Crea da selezione (Scorciatoia da tastiera - Ctrl + Maiusc + F3). Si aprirà la finestra di dialogo "Crea nomi dalla selezione".
• Nella finestra di dialogo Crea nomi dalla selezione, controlla le opzioni in cui hai le intestazioni. In questo caso, selezioniamo la riga superiore solo poiché l'intestazione si trova nella riga superiore. Se hai intestazioni sia nella riga superiore che nella colonna di sinistra, puoi sceglierle entrambe. Allo stesso modo, se i tuoi dati sono disposti quando le intestazioni si trovano solo nella colonna di sinistra, allora selezioni solo l'opzione Colonna sinistra.

Verranno creati tre intervalli denominati: Data, Rappresentante_vendite e Vendite.

Nota che raccoglie automaticamente i nomi dalle intestazioni. Se ci sono spazi tra le parole, inserisce un trattino basso (poiché non puoi avere spazi negli intervalli denominati).

Convenzione di denominazione per intervalli denominati in Excel

Esistono alcune regole di denominazione che devi conoscere durante la creazione di intervalli denominati in Excel:

• Il primo carattere di un intervallo denominato deve essere una lettera e un carattere di sottolineatura (_) o una barra rovesciata (\). Se è qualcos'altro, mostrerà un errore. I caratteri rimanenti possono essere lettere, numeri, caratteri speciali, punti o trattini bassi.
• Non è possibile utilizzare nomi che rappresentano anche riferimenti di cella in Excel. Ad esempio, non puoi usare AB1 poiché è anche un riferimento di cella.
• Non puoi utilizzare gli spazi durante la creazione di intervalli denominati. Ad esempio, non puoi avere un rappresentante di vendita come intervallo denominato. Se vuoi combinare due parole e creare un intervallo con nome, usa un trattino basso, un punto o caratteri maiuscoli per crearlo. Ad esempio, puoi avere Sales_Rep, SalesRep o SalesRep.
  • Durante la creazione di intervalli denominati, Excel tratta le lettere maiuscole e minuscole allo stesso modo. Ad esempio, se crei un intervallo denominato SALES, non potrai creare un altro intervallo denominato come "vendite" o "vendite".
• Un intervallo con nome può essere lungo fino a 255 caratteri.

Troppi intervalli denominati in Excel? Non preoccuparti

A volte in set di dati di grandi dimensioni e modelli complessi, potresti finire per creare molti intervalli denominati in Excel.

Cosa succede se non ricordi il nome dell'intervallo denominato che hai creato?

Non preoccuparti - ecco alcuni consigli utili.

Ottenere i nomi di tutti gli intervalli denominati

Ecco i passaggi per ottenere un elenco di tutti gli intervalli denominati che hai creato:

• Vai alla scheda Formule.
• Nel gruppo Nome definito, fare clic su Usa in formula.
• Fare clic su "Incolla nomi".

Questo ti darà un elenco di tutti gli intervalli denominati in quella cartella di lavoro. Per utilizzare un intervallo denominato (nelle formule o in una cella), fai doppio clic su di esso.

Visualizzazione degli intervalli denominati corrispondenti

• Se hai un'idea del nome, digita alcuni caratteri iniziali e Excel mostrerà un menu a discesa dei nomi corrispondenti.

Come modificare gli intervalli denominati in Excel

Se hai già creato un intervallo denominato, puoi modificarlo utilizzando i seguenti passaggi:

• Vai alla scheda Formule e fai clic su Gestione nomi.
• La finestra di dialogo Gestione nomi elencherà tutti gli intervalli denominati in quella cartella di lavoro. Fare doppio clic sull'intervallo denominato che si desidera modificare.
• Nella finestra di dialogo Modifica nome, apportare le modifiche.
• Fare clic su OK.
• Chiudi la finestra di dialogo Gestione nomi.

Scorciatoie utili per l'intervallo con nome (la potenza di F3)

Ecco alcune utili scorciatoie da tastiera che ti torneranno utili quando lavori con gli intervalli denominati in Excel:

• Per ottenere un elenco di tutti gli intervalli denominati e incollarlo in Formula: F3
• Per creare un nuovo nome utilizzando la finestra di dialogo Gestione nomi: Ctrl + F3
• Per creare intervalli denominati dalla selezione: Ctrl + Maiusc + F3

Creazione di intervalli denominati dinamici in Excel

Finora in questo tutorial, abbiamo creato intervalli denominati statici.

Ciò significa che questi intervalli denominati farebbero sempre riferimento allo stesso set di dati.

Ad esempio, se A1:A10 è stato denominato "Vendite", farà sempre riferimento a A1:A10.

Se aggiungi più dati di vendita, dovresti andare manualmente e aggiornare il riferimento nell'intervallo denominato.

Nel mondo dei set di dati in continua espansione, questo potrebbe richiedere molto tempo. Ogni volta che si ottengono nuovi dati, potrebbe essere necessario aggiornare gli intervalli denominati in Excel.

Per affrontare questo problema, possiamo creare intervalli denominati dinamici in Excel che tengano conto automaticamente di dati aggiuntivi e li includano nell'intervallo denominato esistente.

Ad esempio, ad esempio, se aggiungo due punti dati di vendita aggiuntivi, un intervallo denominato dinamico farebbe automaticamente riferimento a A1:A12.

Questo tipo di intervallo con nome dinamico può essere creato utilizzando la funzione INDICE di Excel. Invece di specificare i riferimenti di cella durante la creazione dell'intervallo denominato, specifichiamo la formula. La formula viene aggiornata automaticamente quando i dati vengono aggiunti o eliminati.

Vediamo come creare intervalli denominati dinamici in Excel.

Supponiamo di avere i dati di vendita nella cella A2:A11.

Ecco i passaggi per creare intervalli denominati dinamici in Excel:

1. 1. Vai alla scheda Formula e fai clic su Definisci nome.
   2. Nella finestra di dialogo Nuovo nome digita quanto segue:
      • Nome: Vendite
      • Ambito: cartella di lavoro
      • Si riferisce a: =$A$2:INDICE($A$2:$A$100,CONTA.SE($A$2:$A$100,””&””))
   3. Fare clic su OK.

Fatto!

Ora hai un intervallo denominato dinamico con il nome "Vendite". Questo si aggiornerebbe automaticamente ogni volta che aggiungi dati o rimuovi dati da esso.

Come funzionano gli intervalli denominati dinamici?

Per spiegare come funziona, devi sapere qualcosa in più sulla funzione INDICE di Excel.

La maggior parte delle persone usa INDEX per restituire un valore da un elenco basato sul numero di riga e colonna.

Ma la funzione INDICE ha anche un altro aspetto.

Può essere usato per restituire un riferimento di cella quando viene utilizzato come parte di un riferimento di cella.

Ad esempio, ecco la formula che abbiamo usato per creare un intervallo denominato dinamico:

=$A$2:INDICE($A$2:$A$100,CONTA.SE($A$2:$A$100,""&""))

INDEX($A$2:$A$100,COUNTIF($A$2:$A$100,””&””) -> Questa parte della formula dovrebbe restituire un valore (che sarebbe il decimo valore dall'elenco, considerando che ci sono dieci elementi).

Tuttavia, se usato davanti a un riferimento (=$A$2:INDEX($A$2:$A$100,COUNTIF($A$2:$A$100,””&””))) restituisce il riferimento alla cella invece del valore.

Quindi, qui restituisce =$A$2:$A$11

Se aggiungiamo due valori aggiuntivi alla colonna delle vendite, restituirà =$A$2:$A$13

Quando aggiungi nuovi dati all'elenco, la funzione CONTA.SE di Excel restituisce il numero di celle non vuote nei dati. Questo numero viene utilizzato dalla funzione INDICE per recuperare il riferimento di cella dell'ultimo elemento nell'elenco.

Nota:

• Funzionerebbe solo se non ci sono celle vuote nei dati.
• Nell'esempio sopra, ho assegnato un numero elevato di celle (A2: A100) per la formula Intervallo denominato. Puoi modificarlo in base al tuo set di dati.

È inoltre possibile utilizzare la funzione OFFSET per creare intervalli denominati dinamici in Excel, tuttavia, poiché la funzione OFFSET è volatile, può portare a una cartella di lavoro di Excel lenta. INDEX, d'altra parte, è semi-volatile, il che lo rende una scelta migliore per creare intervalli denominati dinamici in Excel.

Potrebbero interessarti anche le seguenti risorse di Excel:

• Modelli Excel gratuiti.
• Formazione online gratuita su Excel (corso video online in 7 parti).
• Utili esempi di codici macro di Excel.
• 10 esempi di CERCA.VERT Excel avanzati.
• Creazione di un elenco a discesa in Excel.
• Creazione di un intervallo denominato in Fogli Google.
• Come fare riferimento a un altro foglio o cartella di lavoro in Excel