In questo articolo impareremo come usare i nomi in una formula in Excel.
Scenario :
Mentre lavori su Excel, devi aver sentito parlare di intervalli denominati in Excel. Magari da un amico, collega o da qualche tutorial online. Ne ho parlato molte volte nei miei articoli. In questo articolo impareremo a conoscere l'intervallo denominato in Excel e ne esploreremo ogni singolo aspetto.
Intervalli denominati in Excel
Formule -> Gestore nomi -> Definisci nome
Bene, gli intervalli denominati non sono altro che alcuni intervalli Excel a cui viene assegnato un nome significativo. Ad esempio, se hai una cella dire B1, contenente obiettivi quotidiani, puoi nominare quella cella come specificamente "Obiettivo". Ora puoi usare "Target" per fare riferimento ad A1 invece di scrivere B1.
In poche parole, l'intervallo denominato è solo un nome di intervalli.
Come nominare un intervallo in Excel?
Definisci il nome manualmente:
Per definire un nome per un intervallo puoi usare la scorciatoia CTRL+F3. Oppure puoi seguire questi passaggi.
- Vai a Formula tab
- Individua la sezione Nomi definiti e fai clic su Definisci nomi. Questo aprirà il Nome Mangiatoia.
- Fare clic su Nuovo.
- Digita il nome.
- Seleziona l'ambito (cartella di lavoro o foglio)
- Scrivi un commento se vuoi.
- Nella casella Riferito a scrivere il riferimento o selezionare un intervallo utilizzando il mouse.
- Premi OK. E 'fatto.
Ora puoi fare riferimento ad esso semplicemente digitando il suo nome.
Ci sono alcune regole da seguire durante la creazione dei nomi. Qui ce ne sono alcuni.
- I nomi non devono iniziare da cifre o caratteri speciali diversi dal carattere di sottolineatura (_) e dalla barra rovesciata (\).
- I nomi non possono contenere spazi e caratteri speciali eccetto _ e \.
- L'intervallo non deve essere denominato come riferimenti di cella. Ad esempio A1, B1 o AZ100 ecc.
- Non puoi denominare un intervallo come "r" e "c" perché sono riservati per riferimenti di righe e colonne.
- Due intervalli denominati non possono avere lo stesso nome in una cartella di lavoro.
- Lo stesso intervallo può avere più nomi.
Bene, la maggior parte delle volte lavorerai con tabelle di dati strutturati. Avranno colonne e righe con intestazioni di colonna e intestazioni di riga. E la maggior parte delle volte questi nomi sono significativi per i dati e vorresti nominare il tuo intervallo come queste intestazioni di colonna. Excel fornisce uno strumento per denominare automaticamente gli intervalli utilizzando le intestazioni. Segui questi passi.
- Gli intervalli che vuoi nominare come loro intestazioni
- premere CTRL+MAIUSC+F3, oppure Individua la sezione Nomi definiti nella scheda Formula e fai clic su Crea da selezione.
- Apparirà la casella di opzione sottostante. Ho selezionato solo la riga superiore, poiché voglio nominare questi intervalli come intestazione e non voglio nominare le righe.
- Fare clic su OK.
Ora ogni colonna è denominata come intestazione. Ogni volta che si digita una formula, questi nomi verranno elencati come opzioni a cui è possibile accedere.
Quando tabilizziamo un dato in excel usando CTRL + T, l'intestazione della colonna viene assegnata automaticamente come nome della rispettiva colonna. Dovresti esplorare le tabelle di Excel e i loro vantaggi.
Bene, ci saranno momenti in cui vorresti vedere tutti gli intervalli denominati disponibili nella cartella di lavoro. Per vedere tutti gli intervalli di nomi, premere CTRL+F3. Oppure puoi andare su Scheda Formula > Nome Manager. Verranno elencati tutti gli intervalli denominati disponibili nella cartella di lavoro. Puoi modificare gli intervalli denominati disponibili, eliminarli, aggiungere nuovi nomi.
Un intervallo Più nomi
Excel consente agli utenti di denominare lo stesso intervallo con nomi diversi. Ad esempio, l'intervallo A2:A10 può essere denominato "Clienti" e "Clienti" contemporaneamente. Entrambi i nomi faranno riferimento allo stesso intervallo A2:A10.
Ma non puoi avere gli stessi nomi per due intervalli diversi. Questo è buono. Questo elimina la possibilità di ambiguità.
Ottieni l'elenco degli intervalli denominati sul foglio
Quindi, se vuoi avere un elenco di intervalli denominati e gli intervalli che stanno coprendo, puoi utilizzare questa scorciatoia per incollarli in posizione in un foglio.
- Seleziona una cella in cui desideri ottenere un elenco di intervalli denominati.
- Premi F3. Questo si aprirà un incolla la lista finestra di dialogo.
- Clicca sulla pasta elenco pulsante.
- L'elenco verrà incollato sulle celle selezionate e successive.
Se fai doppio clic sul nome degli intervalli denominati nella casella del nome incolla, verranno scritti come formule nella cella. Provalo.
Aggiorna manualmente gli intervalli denominati
Bene, quando inserisci una cella all'interno di un intervallo denominato, si aggiorna automaticamente e la espande. Ma se aggiungi dati alla fine della tabella, dovrai aggiornare l'intervallo denominato. Per aggiornare gli intervalli denominati, segui questi passaggi.
- premere CTRL+F3 per aprire il nome manager.
- Fare clic sull'intervallo denominato che si desidera modificare. Fare clic su Modifica.
- Nella colonna Fa riferimento a digitare l'intervallo in cui si desidera espandere e premere OK.
Ed è fatto. Questo è l'aggiornamento manuale degli intervalli denominati. Tuttavia, possiamo renderlo dinamico utilizzando alcune formule.
Aggiorna gli intervalli denominati in modo dinamico
È consigliabile rendere dinamici gli intervalli denominati in modo da non doverli modificare ogni volta che i dati superano l'intervallo predefinito.
L'ho trattato in un articolo separato chiamato Intervalli con nome dinamico. Puoi imparare e comprenderne i vantaggi in dettaglio qui.
Eliminazione di intervalli denominati
Quando elimini la parte della somma dell'intervallo denominato, regola automaticamente il suo intervallo. Ma quando elimini l'intero intervallo di nomi scompare dall'elenco dei nomi. Qualsiasi formula, dipendente da tali intervalli, mostrerà l'errore #REF o fornirà un output errato (funzioni di conteggio).
Per qualsiasi motivo, se desideri eliminare gli intervalli denominati, segui questi passaggi.
- Premi CTRL+F3. Si aprirà Gestione nomi.
- Seleziona gli intervalli denominati che desideri eliminare.
- Fare clic sul pulsante Elimina o premere il pulsante Elimina sulla tastiera.
Attenzione: Prima di eliminare gli intervalli denominati, assicurati che nessuna formula dipenda da questi nomi. Se ce ne sono, convertili prima in intervalli. Altrimenti vedrai l'errore #REF.
Eliminazione di nomi con errori
Excel fornisce strumenti per eliminare i nomi che contengono solo errori. Non è necessario identificare ciascuno di essi da soli. Per eliminare i nomi con errori, procedi nel seguente modo:
- Apri Gestione nomi (CTRL+F3).
- Fare clic sul menu a discesa Filtro nell'angolo in alto a destra.
- Seleziona "Nome con errori"
- Seleziona Tutto e premi il pulsante Elimina.
E se ne sono andati. Tutti i nomi con errori verranno immediatamente cancellati dal registro.
Intervalli denominati con formule
Il miglior uso degli intervalli denominati viene esplorato con le formule. Le formule diventano davvero flessibili e leggibili con gli intervalli denominati. Vediamo come.
Formule facili da scrivere
Ora supponiamo che tu abbia denominato un intervallo come "Articoli". Ora l'elenco degli elementi che vuoi contare "Matite". Con i nomi è facile scrivere questa formula CONTA.SE. Scrivi e basta
=CONTA.SE(Articolo,"Matita")
Non appena scrivi la parentesi di apertura della formula, apparirà l'elenco degli intervalli denominati disponibili
Senza nome scriveresti un intervallo per la funzione CONTA.SE di Excel, per la quale potresti dover prima guardare l'intervallo, quindi selezionare l'intervallo o digitarlo nella formula.
Excel serve gli intervalli di nomi disponibili.
Gli intervalli denominati vengono visualizzati come suggerimenti quando si digita una lettera. Come excel mostra l'elenco delle formule. Ad esempio, se digiti =u, ogni formula e intervallo denominato verranno visualizzati iniziando con u, in modo da poterli utilizzare facilmente.
Crea costanti usando gli intervalli denominati
Finora, abbiamo imparato a denominare gli intervalli, ma in realtà puoi anche nominare i valori. Ad esempio, se il nome del tuo cliente è Sunder Pichai, puoi creare un nome "Cliente" e si riferisce a scrivere "Sundar Pichai". Ora ogni volta che scriverai =Client in qualsiasi cella, mostrerà Sundar Pichai.
Non solo testo, ma puoi anche assegnare numeri come costanti con cui lavorare. Ad esempio, definisci un obiettivo. O il valore di qualcosa che non cambierà.
Riferimenti assoluti e relativi con intervalli denominati
Il riferimento con gli intervalli denominati in è molto flessibile. Ad esempio, se scrivi il nome di un intervallo denominato in una cella relativa all'intervallo denominato, si comporterà come un riferimento relativo. Vedi sotto l'immagine.
Ma quando lo usi con le formule si comporterà come assoluto. Beh, la maggior parte delle volte li utilizzerai con le formule, quindi puoi dire che sono di default Assoluti ma in realtà sono flessibili.
Ma possiamo anche renderli relativi.
Come creare intervalli denominati relativi in Excel?
Diciamo se voglio nominare un intervallo "Befor" che si riferirà alla cella a sinistra ovunque sia scritto. Come lo faccio? Segui questi passi:
- Premi CTRL+F3
- Fare clic su Nuovo
- Digita "Prima" nella sezione "Nome".
- Nella sezione "Riferito a:" scrivi l'indirizzo della cella a sinistra. Ad esempio, se ti trovi nella cella B1, scrivi "= A2" nella sezione "Riferito a:". Assicurati che non abbia il segno $.
Ora ovunque scriverai il "Prima" nella formula, farà riferimento alla cella a sinistra.
Qui, l'ho usato prima nella funzione COLUMN. La formula restituisce il numero di colonna della cella sinistra in cui è scritta. Con mia sorpresa, A1 mostra il numero di colonna dell'ultima colonna. Il che significa che il foglio è circolare. Ho pensato che mostrerà un errore #REF.
Dare un nome alle formule usate spesso?
Ora questo è incredibile. Molte volte usi la stessa formula ancora e ancora in un foglio di lavoro. Ad esempio, potresti voler controllare se un nome è presente o meno nell'elenco dei tuoi clienti. E questa necessità può verificarsi molte volte. Per questo scriverai la stessa formula complessa ogni volta.
| =SE(CONTA.SE(Cliente,I3),"In elenco","Non in elenco") |
Che ne dici, se digiti semplicemente "= IsInCustomer" in una cella e ti mostrerà se il valore nella cella di sinistra è nell'elenco dei clienti o no?
Ad esempio, ho preparato una tabella qui. Ora voglio solo digitare "=IsInCustomer" in J5 e vorrei vedere se il valore in I5 è nell'elenco dei clienti oppure no. Per farlo segui questi passaggi.
- Premi CTRL+F3
- Fare clic su Nuovo
- In nome scrivi "IsInCustomer"
- In "Riferito a" scrivi la tua formula. =SE(CONTA.SE(Cliente,I5),"In elenco","Non in elenco")
- Premi il pulsante OK.
Ora, ovunque digiti "IsInCustomer", controllerà il valore nella cella di sinistra nell'elenco dei clienti.
Questo ti impedisce di ripeterti ancora e ancora.
Applicare intervalli denominati alle formule
Tante volte, definiamo i nomi dei nostri intervalli dopo aver già scritto formule basate sugli intervalli. Ad esempio, ho il prezzo totale come celle = E2 * F2. Come possiamo cambiarlo in Unità*Costo_Unità.
- Seleziona le formule.
- Vai alla scheda della formula. Fare clic sul menu a discesa Definisci nome.
- Fare clic su Applica nomi.
- Apparirà l'elenco di tutti gli intervalli denominati. Scegli i nomi giusti e premi ok.
E i nomi sono ora applicati. Puoi vederlo nella barra della formula.
Formule facili da leggere con intervalli denominati
Come hai visto, gli intervalli denominati semplificano la lettura delle formule. Se scrivo =COUNTIF(“A2:A100”,B2), nessuno capirà cosa sto cercando di contare, finché non vedrà i dati o qualcuno glielo spiegherà.
Ma se scrivo = COUNTIF (regione, "est"), la maggior parte degli utenti capirà immediatamente che stiamo contando le occorrenze di "est" nella regione denominata intervallo.
Formule portatili
Gli intervalli denominati semplificano la copia e l'incollaggio delle formule senza preoccuparsi di modificare i riferimenti. E puoi prendere una formula da una cartella di lavoro a un'altra e funzionerà bene fino a quando ea meno che la cartella di lavoro di destinazione non abbia lo stesso nome.
Ad esempio se hai una Formula =CONTA.SE(regione,est) nella tabella di distribuzione e hai un'altra cartella di lavoro dire clienti che ha anche un intervallo denominato "Regione". Ora, se copi questa formula direttamente in qualsiasi punto della cartella di lavoro, ti mostrerà le informazioni corrette. La struttura dei dati non avrà importanza. Non importa dove diavolo sia quella colonna nella tua cartella di lavoro. Questo funzionerà correttamente.
Nell'immagine sopra ho usato la stessa identica formula in due file diversi per contare il numero o l'est che si verificano nell'elenco delle regioni. Ora sono in colonne diverse ma poiché entrambi sono denominati come regioni funzionerà perfettamente.
Naviga facilmente nella cartella di lavoro
Diventa più facile navigare in una cartella di lavoro con intervalli denominati. Devi solo digitare il nome del nome nella casella del nome. Excel ti porterà nell'intervallo, non importa dove ti trovi nella cartella di lavoro. Dato che l'intervallo denominato è nell'ambito della cartella di lavoro.
Ad esempio, se sei sul foglio 10 e vuoi ottenere un elenco di clienti e non sai su quale foglio si trova. Basta andare alla casella del nome e digitare "cliente". Verrai indirizzato all'intervallo denominato in una frazione di secondo.
Ridurrà lo sforzo di ricordare gli intervalli.
Navigare utilizzando i collegamenti ipertestuali con l'intervallo denominato
Quando il tuo foglio è grande e vai spesso da un punto all'altro, ti piace usare i collegamenti ipertestuali per navigare facilmente. Gli intervalli ben denominati possono funzionare perfettamente con i collegamenti ipertestuali. Per aggiungere collegamenti ipertestuali utilizzando intervalli denominati, seguire questi passaggi.
- Scegli una cella in cui desideri il collegamento ipertestuale
- Premi CTRL+K o vai a Inserisci scheda> Collegamento ipertestuale per aprire la finestra di dialogo Inserisci collegamento ipertestuale.
- Fare clic su Inserisci in questo documento.
- Scorri verso il basso per vedere gli intervalli denominati disponibili in Nomi definiti
- Selezionare l'intervallo denominato per inserire un collegamento ipertestuale a quell'intervallo.
Ed è fatto. Hai il tuo collegamento ipertestuale all'intervallo denominato scelto. Usando questo puoi creare un indice di intervalli denominati che puoi vedere e fare clic per accedervi direttamente. Questo renderà la tua cartella di lavoro davvero facile da usare.
Intervallo denominato e convalida dei dati
Gli intervalli denominati e la convalida dei dati sono fatti l'uno per l'altro. Gli intervalli denominati rendono la convalida dei dati altamente personalizzabile. Diventa molto più semplice aggiungere una convalida da un elenco utilizzando l'intervallo denominato. Vediamo come…
- Vai alla scheda Dati
- Fare clic su Convalida dati
- Seleziona Elenco nella sezione "Consenti:"
- Nella sezione "Origine:", digita "=Cliente" (scrivi l'intervallo di nome che hai)
- premi OK
Ora questa cella avrà i nomi dei clienti che fanno parte dell'intervallo denominato Cliente. Facile, non è vero.
Convalida dei dati dipendente o a cascata con intervalli denominati
Ora cosa succede se si desidera una convalida dei dati a cascata o dipendenti. Ad esempio, se desideri un elenco a discesa con categorie, Frutta e Verdura. Ora, se scegli la frutta, un altro menu a discesa dovrebbe mostrare solo l'opzione frutta e se scegli Verdura, solo verdure.
Ciò può essere facilmente ottenuto utilizzando gli intervalli denominati. Impara come.
- Drop down dipendente utilizzando l'intervallo denominato
- Altri modi per la convalida dei dati a cascata
Spero che questo articolo su Come utilizzare i nomi in una formula in Excel sia esplicativo. Trova altri articoli sugli intervalli di denominazione e le relative formule di Excel qui. Se ti è piaciuto il nostro blog, condividilo con i tuoi amici su Facebook. E puoi anche seguirci su Twitter e Facebook. Ci piacerebbe avere tue notizie, facci sapere come possiamo migliorare, completare o innovare il nostro lavoro e renderlo migliore per te. Scrivici al sito di posta elettronica.
La casella del nome in Excel : Excel Name Box non è altro che una piccola area di visualizzazione in alto a sinistra del foglio Excel che mostra il nome della cella o degli intervalli attivi in Excel. Puoi rinominare una cella o una matrice per i riferimenti.
Come ottenere il nome del foglio di lavoro in Excel : La funzione CELL in Excel ti fornisce le informazioni relative a qualsiasi foglio di lavoro come col, contenuto, nome file, ecc. Scopri come ottenere il nome del foglio usando la funzione CELL qui.
Come ottenere il numero di riga sequenziale in Excel: A volte abbiamo bisogno di ottenere un numero di riga sequenziale in una tabella, può essere per un numero di serie o qualsiasi altra cosa. In questo articolo impareremo come numerare le righe in Excel dall'inizio dei dati.
Incrementa un numero in una stringa di testo in excel: Se hai un lungo elenco di elementi e devi aumentare l'ultimo numero del testo del vecchio testo in excel, avrai bisogno dell'aiuto delle due funzioni TEXT e RIGHT.
Articoli popolari:
Come usare la funzione SE in Excel : l'istruzione IF in Excel controlla la condizione e restituisce un valore specifico se la condizione è VERA o restituisce un altro valore specifico se FALSO.
Come utilizzare la funzione CERCA.VERT in Excel : questa è una delle funzioni più utilizzate e popolari di excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
Come utilizzare la funzione SOMMA.SE in Excel : Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.
Come utilizzare la funzione CONTA.SE in Excel : Conta i valori con le condizioni utilizzando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.