Gli intervalli denominati sono una di queste vecchie caratteristiche di Excel che pochi utenti capiscono. I nuovi utenti potrebbero trovarli strani e spaventosi, e anche le vecchie mani potrebbero evitarli perché sembrano inutili e complessi.
Ma gli intervalli denominati sono in realtà una caratteristica piuttosto interessante. Possono rendere le formule * molto * più facili da creare, leggere e mantenere. E come bonus, rendono le formule più facili da riutilizzare (più portabili).
In effetti, uso intervalli denominati tutto il tempo durante il test e la prototipazione delle formule. Mi aiutano a far funzionare le formule più velocemente., Uso anche intervalli con nome perché sono pigro e non mi piace digitare riferimenti complessi 🙂
Le basi degli intervalli con nome in Excel
Cos’è un intervallo con nome?
Un intervallo denominato è solo un nome leggibile per un intervallo di celle in Excel. Ad esempio, se nomino l’intervallo A1:A100 “dati”, posso usare MAX per ottenere il valore massimo con una semplice formula:
=MAX(data) // max value
La bellezza degli intervalli denominati è che puoi usare nomi significativi nelle tue formule senza pensare ai riferimenti di cella., Una volta che hai un intervallo con nome, usalo come un riferimento di cella. Tutte queste formule sono valide con l’intervallo denominato “data”:
=MAX(data) // max value=MIN(data) // min value=COUNT(data) // total values=AVERAGE(data) // min valueVideo: Come creare un intervallo denominato
Creare un intervallo denominato è facile
Creare un intervallo denominato è facile e veloce. Basta selezionare un intervallo di celle e digitare un nome nella casella nome. Quando si preme Invio, viene creato il nome:
Per testare rapidamente il nuovo intervallo, scegliere il nuovo nome nel menu a discesa accanto alla casella nome. Excel selezionerà l’intervallo sul foglio di lavoro.,
Excel può creare automaticamente i nomi (ctrl + maiusc + F3)
Se si dispone di dati ben strutturati con etichette, è possibile che Excel crei intervalli denominati per te. Basta selezionare i dati, insieme alle etichette, e utilizzare il comando” Crea dalla selezione ” nella scheda Formule della barra multifunzione:
È inoltre possibile utilizzare la scorciatoia da tastiera control + shift + F3.
Utilizzando questa funzione, possiamo creare intervalli denominati per la popolazione di 12 stati in un unico passaggio:
Quando si fa clic su OK, i nomi vengono creati., Troverai tutti i nuovi nomi nel menu a discesa accanto alla casella nome:
Con i nomi creati, è possibile utilizzarli in formule come questa
=SUM(MN,WI,MI)Aggiornamento intervalli denominati in Nome Manager (ctrl + F3)
una Volta che si crea un intervallo denominato, utilizzare il Nome del Manager (ctrl + F3) per l’aggiornamento come necessario. Selezionare il nome con cui si desidera lavorare, quindi modificare direttamente il riferimento (ad esempio modificare “si riferisce a”), oppure fare clic sul pulsante a destra e selezionare un nuovo intervallo.,
Non è necessario fare clic sul pulsante Modifica per aggiornare un riferimento. Quando si fa clic su Chiudi, il nome dell’intervallo verrà aggiornato.
Nota: se si seleziona un intero intervallo denominato in un foglio di lavoro, è possibile trascinare in una nuova posizione e il riferimento verrà aggiornato automaticamente. Tuttavia, non conosco un modo per regolare i riferimenti all’intervallo facendo clic e trascinando direttamente sul foglio di lavoro. Se si conosce un modo per farlo, carillon in seguito!
Visualizza tutti gli intervalli con nome (control + F3)
Per visualizzare rapidamente tutti gli intervalli con nome in una cartella di lavoro, utilizzare il menu a discesa accanto alla casella nome.,
Se vuoi vedere più dettagli, apri il Gestore nomi (Control + F3), che elenca tutti i nomi con riferimenti e fornisce anche un filtro:
Nota: su un Mac, non esiste un Gestore nomi, quindi vedrai la finestra di dialogo Definisci nome.
Copia e incolla tutti gli intervalli con nome (F3)
Se si desidera un record più persistente di intervalli con nome in una cartella di lavoro, è possibile incollare l’elenco completo dei nomi ovunque si desideri., Vai a Formule > Utilizzare nella Formula (o usare la combinazione di tasti F3), quindi scegliere Incolla nomi > Incolla:
Quando si fa clic sul pulsante Incolla Elenco, potete vedere i nomi e i riferimenti incollato nel foglio di lavoro:
Vedere i nomi direttamente sul foglio di lavoro
Se si imposta il livello di zoom per meno di 40%, Excel mostrerà i nomi di intervallo direttamente sul foglio di lavoro:
Grazie per questo suggerimento, Felipe!,
I nomi hanno regole
Quando crei intervalli con nome, segui queste regole:
Intervalli con nome nelle formule
Gli intervalli con nome sono facili da usare nelle formule
Ad esempio, diciamo che chiami una cella nella tua cartella di lavoro “aggiornata”. L’idea è che puoi inserire la data corrente nella cella (Ctrl + 😉 e fare riferimento alla data altrove nella cartella di lavoro.
La formula in B8 è simile a questa:
="Updated: "& TEXT(updated, "ddd, mmmm d, yyyy")Puoi incollare questa formula ovunque nella cartella di lavoro e verrà visualizzata correttamente., Ogni volta che cambi la data in “aggiornato”, il messaggio si aggiornerà ovunque venga utilizzata la formula. Vedere questa pagina per ulteriori esempi.
Intervalli denominati appaiono quando si digita una formula
Una volta creato un intervallo denominato, apparirà automaticamente nelle formule quando si digita la prima lettera del nome. Premi il tasto tab per inserire il nome quando hai una corrispondenza e vuoi che Excel inserisca il nome.,
Gli intervalli denominati possono funzionare come costanti
Poiché gli intervalli denominati vengono creati in una posizione centrale, è possibile utilizzarli come costanti senza un riferimento di cella. Ad esempio, è possibile creare nomi come “MPG (miglia per gallone) e “CPG” (costo per gallone) e di assegnare valori fissi:
è possibile utilizzare questi nomi ovunque nelle formule, e aggiornare il loro valore in una posizione centrale.,
Gli intervalli denominati sono assoluti per impostazione predefinita
Per impostazione predefinita, gli intervalli denominati si comportano come riferimenti assoluti. Per esempio, in questo foglio di lavoro, la formula per calcolare il combustibile dovrebbe essere:
=C5/$D$2
riferimento a D2 è assoluta (bloccato) in modo che la formula può essere copiata senza D2 cambiando.,
Se chiamiamo D2 “MPG” la formula diventa:
=C5/MPG
Poiché MPG è assoluto per impostazione predefinita, la formula può essere copiata nella colonna D così com’è.
Gli intervalli denominati possono anche essere relativi
Sebbene gli intervalli denominati siano assoluti per impostazione predefinita, possono anche essere relativi. Un intervallo relativo denominato si riferisce a un intervallo relativo alla posizione della cella attiva al momento della creazione dell’intervallo. Di conseguenza, gli intervalli con nome relativo sono utili per creare formule generiche che funzionano ovunque vengano spostate.,
Ad esempio, puoi creare un intervallo generico “CellAbove” denominato in questo modo:
- Seleziona la cella A2
- Control + F3 per aprire la scheda Name Manager
- nella sezione ‘Si riferisce a’, quindi digita: =A1
CellAbove ora recupererà il valore dalla cella sopra ovunque sia usato.
Importante: assicurarsi che la cella attiva si trovi nella posizione corretta prima di creare il nome.
Applica intervalli denominati alle formule esistenti
Se si dispone di formule esistenti che non utilizzano intervalli denominati, è possibile chiedere a Excel di applicare gli intervalli denominati nelle formule per l’utente., Inizia selezionando le celle che contengono le formule che desideri aggiornare. Quindi eseguire Formule> Definire nomi> Applicare nomi.
Excel sostituirà quindi i riferimenti che hanno un intervallo con nome corrispondente con il nome stesso.
Puoi anche applicare nomi con trova e sostituisci:
Importante: salva un backup del tuo foglio di lavoro e seleziona solo le celle che vuoi cambiare prima di usare trova e sostituisci sulle formule.,Vantaggi chiave degli intervalli denominati
Gli intervalli denominati rendono le formule più facili da leggere
Il più grande vantaggio degli intervalli denominati è che rendono le formule più facili da leggere e mantenere. Questo perché sostituiscono riferimenti criptici con nomi significativi. Ad esempio, considera questo foglio di lavoro con dati sui pianeti nel nostro sistema solare., Senza intervalli denominati, una formula VLOOKUP a prendere “Posizione” della tabella è abbastanza criptica:
=VLOOKUP($H$4,$B$3:$E$11,2,0)
Tuttavia, con B3:E11 denominato “dati”, e H4 denominato “pianeta”, possiamo scrivere le formule come questa:
=VLOOKUP(planet,data,2,0) // position=VLOOKUP(planet,data,3,0) // diameter=VLOOKUP(planet,data,4,0) // satellites
In sintesi, si può vedere l’unica differenza in queste formule in l’indice di colonna.
Gli intervalli con nome rendono le formule portabili e riutilizzabili
Gli intervalli con nome possono rendere molto più facile riutilizzare una formula in un foglio di lavoro diverso., Se si definiscono i nomi in anticipo in un foglio di lavoro, è possibile incollare una formula che utilizza questi nomi e “funzionerà”. Questo è un ottimo modo per far funzionare rapidamente una formula.
Ad esempio, questa formula conta valori univoci in un intervallo di dati numerici:
=SUM(--(FREQUENCY(data,data)>0))Per “portare” rapidamente questa formula nel proprio foglio di lavoro, denominare un intervallo “dati” e incollare la formula nel foglio di lavoro. Finché “dati” contiene valori numerici, la formula funzionerà immediatamente.,
Suggerimento: ti consiglio di creare i nomi degli intervalli necessari *prima* nella cartella di lavoro di destinazione, quindi copiare nella formula solo come testo (cioè non copiare la cella che contiene la formula in un altro foglio di lavoro, basta copiare il testo della formula). Ciò impedisce a Excel di creare nomi al volo e consente di controllare completamente il processo di creazione del nome. Per copiare solo il testo della formula, copiare il testo dalla barra della formula o copiare tramite un’altra applicazione (ad esempio browser, editor di testo, ecc.).
Gli intervalli denominati possono essere utilizzati per la navigazione
Gli intervalli denominati sono ottimi per una navigazione rapida., Basta selezionare il menu a discesa accanto alla casella nome e scegliere un nome. Quando si rilascia il mouse, verrà selezionato l’intervallo. Quando un intervallo con nome esiste su un altro foglio, verrai portato automaticamente a quel foglio.
Gli intervalli denominati funzionano bene con i collegamenti ipertestuali
Gli intervalli denominati semplificano i collegamenti ipertestuali. Ad esempio, se nomini A1 in Sheet1 “home”, puoi creare un collegamento ipertestuale da qualche altra parte che ti riporta lì.,
Per utilizzare un intervallo denominato all’interno del collegamento funzione, aggiungere il simbolo del cancelletto davanti l’intervallo denominato:
=HYPERLINK("#home","take me home")Nota: stranamente, non è possibile il collegamento ipertestuale a una tabella come è possibile che un normale nome di intervallo. Tuttavia, è possibile definire un nome uguale a una tabella (cioè =Table1) e un collegamento ipertestuale a quello. Se qualcuno conosce un modo per collegare direttamente un tavolo, carillon in!,
Intervalli di nomi per la convalida dei dati
Gli intervalli di nomi funzionano bene per la convalida dei dati, poiché consentono di utilizzare un riferimento con nome logico per convalidare l’input con un menu a discesa. Di seguito, la gamma di G4:G8 è denominato “statuslist”, quindi applicare la convalida dei dati con una Lista collegata come questo:
Il risultato è un menu a discesa nella colonna E che solo consente di valori nell’intervallo denominato:
Dinamica di Intervalli con Nome
Nomi intervalli sono estremamente utili quando si regola automaticamente per i nuovi dati in un foglio di lavoro., Un intervallo impostato in questo modo viene definito “intervallo dinamico con nome”. Esistono due modi per rendere dinamico un intervallo: formule e tabelle.
Intervallo con nome dinamico con una tabella
Una tabella è il modo più semplice per creare un intervallo con nome dinamico. Selezionare qualsiasi cella nei dati, quindi utilizzare il controllo di scelta rapida + T:
Quando si crea una tabella Excel, viene creato automaticamente un nome (ad esempio Table1), ma è possibile rinominare la tabella come si desidera. Una volta creata una tabella, si espanderà automaticamente quando vengono aggiunti i dati.,
Intervallo con nome dinamico con una formula
È anche possibile creare un intervallo con nome dinamico con formule, utilizzando funzioni come OFFSET e INDICE. Sebbene queste formule siano moderatamente complesse, forniscono una soluzione leggera quando non si desidera utilizzare una tabella., I collegamenti seguenti forniscono esempi con spiegazioni complete:
- Esempio di formula di intervallo dinamico con INDICE
- Esempio di formula di intervallo dinamico con OFFSET
Nomi di tabelle nella convalida dei dati
Poiché le tabelle di Excel forniscono un intervallo dinamico automatico, sembrano essere una scelta naturale per le regole di convalida dei dati, dove l’obiettivo è convalidare contro una lista che potrebbe cambiare sempre. Tuttavia, un problema con le tabelle è che non è possibile utilizzare i riferimenti strutturati direttamente per creare regole di convalida dei dati o di formattazione condizionale., In altre parole, non è possibile utilizzare un nome di tabella nelle aree di input di formattazione condizionale o convalida dei dati.
Tuttavia, come soluzione alternativa, è possibile definire denominato un intervallo denominato che punta a una tabella e quindi utilizzare l’intervallo denominato per la convalida dei dati o la formattazione condizionale. Il video qui sotto attraversa questo approccio in dettaglio.
Video: Come utilizzare intervalli denominati con tabelle
Eliminazione di intervalli denominati
Nota: se si dispone di formule che si riferiscono a intervalli denominati, è possibile aggiornare le formule prima di rimuovere i nomi. Altrimenti, vedrai # NOME?, errori nelle formule che si riferiscono ancora a nomi eliminati. Salva sempre il tuo foglio di lavoro prima di rimuovere gli intervalli denominati nel caso in cui tu abbia problemi e debba tornare all’originale.
Gli intervalli con nome si regolano quando si eliminano e si inseriscono celle
Quando si elimina *parte* di un intervallo con nome, o se si inseriscono celle/righe / colonne all’interno di un intervallo con nome, il riferimento all’intervallo si regola di conseguenza e rimane valido. Tuttavia, se si eliminano tutte le celle che racchiudono un intervallo denominato, l’intervallo denominato perderà il riferimento e visualizzerà un errore # REF., Ad esempio, se nomino A1 “test”, quindi elimini la colonna A, il gestore nomi mostrerà “si riferisce a” come:
=Sheet1!#REF!Elimina nomi con Name Manager
Per rimuovere manualmente gli intervalli denominati da una cartella di lavoro, aprire il gestore nomi, selezionare un intervallo e fare clic sul pulsante Elimina. Se si desidera rimuovere più di un nome allo stesso tempo, è possibile spostare + Clic o Ctrl + Clic per selezionare più nomi, quindi eliminare in un unico passaggio.,
Elimina nomi con errori
Se hai molti nomi con errori di riferimento, puoi usare il pulsante filtra nel gestore nomi per filtrare i nomi con errori:
Quindi shift+click per selezionare tutti i nomi ed eliminare.
Gli intervalli e l’ambito con nome
Gli intervalli con nome in Excel hanno qualcosa chiamato “ambito”, che determina se un intervallo con nome è locale per un determinato foglio di lavoro o globale nell’intera cartella di lavoro. I nomi globali hanno un ambito di” cartella di lavoro ” e i nomi locali hanno un ambito uguale al nome del foglio in cui esistono., Ad esempio, l’ambito per un nome locale potrebbe essere “Sheet2”.
Lo scopo dell’ambito
Gli intervalli denominati con un ambito globale sono utili quando si desidera che tutti i fogli in una cartella di lavoro abbiano accesso a determinati dati, variabili o costanti. Ad esempio, è possibile utilizzare un intervallo di nome globale un’ipotesi di aliquota fiscale utilizzata in diversi fogli di lavoro.
Ambito locale
Ambito locale significa che un nome funziona solo sul foglio su cui è stato creato. Ciò significa che puoi avere più fogli di lavoro nella stessa cartella di lavoro che usano tutti lo stesso nome., Ad esempio, forse si dispone di una cartella di lavoro con fogli di monitoraggio mensili (uno al mese) che utilizzano intervalli denominati con lo stesso nome, tutti con ambito locale. Ciò potrebbe consentire di riutilizzare le stesse formule in fogli diversi. L’ambito locale consente ai nomi di ciascun foglio di funzionare correttamente senza scontrarsi con i nomi degli altri fogli.
Per fare riferimento a un nome con un ambito locale, è possibile anteporre il nome del foglio al nome dell’intervallo:
Sheet1!total_revenueSheet2!total_revenueSheet3!total_revenueI nomi degli intervalli creati con la casella nome hanno automaticamente un ambito globale., Per ignorare questo comportamento, aggiungere il nome del foglio quando si definisce il nome:
Sheet3!my_new_nameAmbito globale
Ambito globale significa che un nome funzionerà ovunque in una cartella di lavoro. Ad esempio, è possibile denominare una cella “last_update”, inserire una data nella cella. Quindi è possibile utilizzare la formula seguente per visualizzare la data dell’ultimo aggiornamento in qualsiasi foglio di lavoro.
=last_updateI nomi globali devono essere univoci all’interno di una cartella di lavoro.
Ambito locale
Gli intervalli denominati con ambito locale hanno senso per i fogli di lavoro che utilizzano intervalli denominati solo per le ipotesi locali., Ad esempio, forse si dispone di una cartella di lavoro con fogli di monitoraggio mensili (uno al mese) che utilizzano intervalli denominati con lo stesso nome, tutti con ambito locale. L’ambito locale consente ai nomi di ciascun foglio di funzionare correttamente senza scontrarsi con i nomi degli altri fogli.
Gestione dell’ambito dell’intervallo denominato
Per impostazione predefinita, i nuovi nomi creati con il namebox sono globali e non è possibile modificare l’ambito di un intervallo denominato dopo la sua creazione. Tuttavia, come soluzione alternativa, è possibile eliminare e ricreare un nome con l’ambito desiderato.,
Se si desidera modificare più nomi contemporaneamente da globale a locale, a volte ha senso copiare il foglio che contiene i nomi. Quando si duplica un foglio di lavoro che contiene intervalli denominati, Excel copia gli intervalli denominati nel secondo foglio, cambiando contemporaneamente l’ambito in locale. Dopo aver ottenuto il secondo foglio con nomi con ambito locale, è possibile eliminare facoltativamente il primo foglio.
Jan Karel Pieterse e Charles Williams hanno sviluppato un’utilità chiamata Name Manager che fornisce molte operazioni utili per intervalli denominati. È possibile scaricare l’utilità Name Manager qui.,
