În acest articol, vom învăța cum să utilizați numele într-o formulă în Excel.
Scenariu :
În timp ce lucrați la Excel, trebuie să fi auzit despre intervale numite în Excel. Poate de la un prieten, coleg sau vreun tutorial online. Am menționat-o de multe ori în articolele mele. În acest articol vom învăța despre Named Range în Excel și vom explora fiecare aspect al acestuia.
Domenii denumite în Excel
Formule -> Manager nume -> Definiți numele
Ei bine, intervalele denumite nu sunt altceva decât câteva intervale Excel cărora li se dă un nume semnificativ. De exemplu, dacă aveți o celulă, spuneți B1, care conține ținte de zi cu zi, puteți denumi acea celulă ca specific „Țintă”. Acum puteți utiliza „Țintă” pentru a vă referi la A1 în loc să scrieți B1.
Pe scurt, gama Named este doar denumirea intervalelor.
Cum se numește un interval în Excel?
Definiți manual numele:
Pentru a defini un nume într-un interval, puteți utiliza o comandă rapidă CTRL + F3. Sau puteți urma acești pași.
- Mergi la Formulă Tab
- Găsiți secțiunea Nume definite și faceți clic pe Definire nume. Aceasta va deschide fișierul Nume Manger.
- Faceți clic pe Nou.
- Tastați numele.
- Selectați domeniul de aplicare (registru de lucru sau foaie)
- Scrieți un comentariu dacă doriți.
- În caseta Se referă la scrieți referința sau selectați un interval folosind mouse-ul.
- Apăsați OK. Este gata.
Acum puteți face referire la el doar tastând numele acestuia.
Există câteva reguli de urmat în timpul creării de nume. Aici sunt câteva.
- Numele nu trebuie să înceapă de la cifre sau caractere speciale, altele decât subliniere (_) și bară inversă (\).
- Numele nu pot avea spații și niciun caracter special, cu excepția _ și \.
- Intervalul nu trebuie numit ca referințe de celulă. De exemplu A1, B1 sau AZ100 etc.
- Nu puteți denumi un interval ca „r” și „c”, deoarece acestea sunt rezervate pentru referințele la rânduri și coloane.
- Două intervale denumite nu pot avea același nume într-un registru de lucru.
- Același interval poate avea mai multe nume.
Ei bine, de cele mai multe ori veți lucra cu tabele de date structurate. Vor avea coloane și rânduri cu titluri de coloană și titluri de rând. Și de cele mai multe ori aceste nume sunt semnificative pentru date și doriți să vă denumiți intervalul ca aceste titluri de coloană. Excel oferă un instrument pentru denumirea automată a intervalelor folosind titluri. Urmați acești pași.
- Intervalele pe care doriți să le denumiți ca titluri
- presa CTRL + SHIFT + F3, sau Găsiți secțiunea Numele definite din fila Formula și faceți clic pe Creați din selecție.
- Va apărea caseta de opțiuni de mai jos. Am selectat numai rândul de sus, deoarece vreau să denumesc acest domeniu ca titlu și nu vreau să denumesc rânduri.
- Faceți clic pe OK.
Acum fiecare coloană este denumită ca antet. Ori de câte ori introduceți o formulă, aceste nume vor fi listate ca opțiuni care pot fi accesate.
Când stocăm date în Excel folosind CTRL + T, antetul coloanei este atribuit automat ca nume al coloanei respective. Ar trebui să explorați tabelele Excel și beneficiile acestora.
Ei bine, vor exista momente când doriți să vedeți toate intervalele numite disponibile în registrul de lucru. Pentru a vedea toate intervalele de nume Apăsați CTRL + F3. Sau poți merge la Fila Formula > Manager nume. Aceasta va lista toate intervalele denumite care sunt disponibile în registrul de lucru. Puteți edita intervalele numite disponibile, le puteți șterge, adăuga nume noi.
O gamă de nume multiple
Excel permite utilizatorilor să numească aceeași gamă cu nume diferite. De exemplu, gama A2: A10 poate fi denumită „Clienți” și „Clienți” ambele în același timp. Ambele nume se vor referi la același interval A2: A10.
Dar nu puteți avea aceleași nume pentru două game diferite. Este bun. Acest lucru elimină șansa de ambiguitate.
Obțineți lista de game numite pe foaie
Deci, dacă doriți să aveți o listă de intervale denumite și intervalele pe care le acoperă, puteți utiliza această comandă rapidă pentru a le lipi în loc într-o foaie.
- Selectați o celulă în care doriți să obțineți o listă de intervale denumite.
- Apăsați F3. Aceasta va deschide un lipiți lista caseta de dialog.
- Faceți clic pe pastă listă buton.
- Lista va fi lipită pe celulele selectate și în continuare.
Dacă faceți dublu clic pe numele intervalelor denumite în caseta de lipire a numelui, acestea vor fi scrise ca formule în celulă. Incearca-l.
Actualizați intervalele numite manual
Ei bine, atunci când introduceți o celulă într-un interval numit, aceasta se actualizează automat și o extinde. Dar dacă adăugați date la sfârșitul tabelului, va trebui să actualizați intervalul numit. Pentru a actualiza gamele numite, urmați acești pași.
- presa CTRL + F3 pentru a deschide manager de nume.
- Faceți clic pe intervalul numit pe care doriți să îl editați. Faceți clic pe Editare.
- În Se referă la coloană, tastați intervalul pe care doriți să îl extindeți și apăsați OK.
Și s-a făcut. Aceasta este actualizarea manuală a intervalelor denumite. Cu toate acestea, îl putem dinamiza folosind unele formule.
Actualizați dinamic gamele numite
Este înțelept să vă dinamizați intervalele numite, astfel încât să nu trebuie să le editați ori de câte ori datele dvs. depășesc intervalul predefinit.
L-am acoperit într-un articol separat numit Intervalele denumite dinamic. Puteți afla și înțelege în detaliu beneficiile acesteia.
Ștergerea gamelor denumite
Când ștergeți partea sumă a intervalului numit, acesta își reglează automat intervalul. Dar când ștergeți întregul nume de nume dispare din lista de nume. Orice formulă, dependentă de aceste intervale, va afișa o eroare #REF sau va da o ieșire incorectă (funcții de numărare).
Din orice motiv, dacă doriți să ștergeți intervale denumite, urmați acești pași.
- Apăsați CTRL + F3. Managerul de nume se va deschide.
- Selectați Domenii numite pe care doriți să le ștergeți.
- Faceți clic pe butonul Ștergeți sau apăsați butonul Ștergere de pe tastatură.
Prudență: Înainte de a șterge intervalele denumite, asigurați-vă că nicio formulă nu depinde de aceste nume. Dacă există, convertiți-le mai întâi în intervale. În caz contrar, veți vedea o eroare #REF.
Ștergerea numelor cu erori
Excel oferă instrumente pentru a șterge numele care au numai erori. Nu trebuie să le identificați pe fiecare singur. Pentru a șterge numele cu erori, urmați acești pași:
- Deschideți Manager nume (CTRL + F3).
- Faceți clic pe meniul derulant Filtru din colțul din dreapta sus.
- Selectați „Nume cu erori”
- Selectați Toate și apăsați butonul de ștergere.
Și au dispărut. Toate numele cu erori vor fi șterse imediat din înregistrare.
Gama denumită cu formule
Cea mai bună utilizare a intervalelor denumite este explorată cu formule. Formulele devin foarte flexibile și lizibile cu gamele Named. Să vedem cum.
Formule ușor de scris
Acum, să presupunem că ați numit un interval ca „Elemente”. Acum lista de articole pe care doriți să o numărați „Creioane”. Cu nume este ușor să scrieți această formulă COUNTIF. Doar scrie
= COUNTIF (Articol,"Creion")
De îndată ce scrieți paranteza de deschidere a formulei, va apărea lista intervalelor numite disponibile
Fără nume, ați scrie un interval pentru funcția COUNTIF a Excelului, pentru care poate fi necesar să vă uitați mai întâi la interval, apoi să selectați intervalul sau să îl tastați în formulă.
Excel servește intervalele de nume disponibile.
Intervalele denumite sunt afișate ca sugestii atunci când introduceți orice literă. Pe măsură ce Excel arată lista de formule. De exemplu, dacă tastați = u, fiecare formulă și interval denumit vor fi afișate începând cu u, astfel încât să le puteți utiliza cu ușurință.
Faceți constante folosind domenii numite
Până acum, am aflat despre domeniile de denumire, dar puteți numi și valori. De exemplu, dacă numele clientului dvs. este Sunder Pichai, atunci puteți crea un nume „Client” și se referă la scrierea „Sundar Pichai”. Acum, ori de câte ori veți scrie = Client în orice celulă, va apărea Sundar Pichai.
Nu numai text, dar puteți, de asemenea, să atribuiți numere ca constante pentru a lucra. De exemplu, definiți o țintă. Sau valoarea a ceva care nu se va schimba.
Referință absolută și relativă cu intervale denumite
Referința cu intervalele Named în este foarte flexibilă. De exemplu, dacă scrieți un interval numit într-o celulă relativă în intervalul numit, acesta se va comporta ca o referință relativă. Vedeți imaginea de mai jos.
Dar când îl utilizați cu formule, acesta se va comporta ca absolut. Ei bine, de cele mai multe ori le veți folosi cu formule, deci puteți spune că sunt implicit Absolute dar de fapt sunt flexibili.
Dar le putem face și ele relative.
Cum se fac intervale numite relative în Excel?
Să spunem dacă vreau să denumesc un interval „Befor” care se va referi la celula lăsată oriunde este scrisă. Cum să fac asta? Urmați acești pași:
- Apăsați CTRL + F3
- Faceți clic pe Nou
- Tastați „Befor” în secțiunea „Nume”.
- În secțiunea „Se referă la:” scrieți adresa celulei din stânga. De exemplu, dacă vă aflați în celula B1, scrieți „= A2” în secțiunea „Se referă la:”. Asigurați-vă că nu are un semn $.
Acum, oriunde veți scrie „Befor” în formulă, se va referi la celula care i-a rămas.
Aici, l-am folosit înainte în funcția COLUMN. Formula returnează numărul coloanei celulei din stânga unde este scrisă. Spre surprinderea mea, A1 arată numărul coloanei ultimei coloane. Ceea ce înseamnă că foaia circulă. Am crezut că va afișa o eroare #REF.
Dați nume formulelor utilizate adesea?
Acum acesta este uimitor. De multe ori utilizați aceeași formulă din nou și din nou într-o foaie de lucru. De exemplu, vă recomandăm să verificați dacă un nume este sau nu în lista dvs. de clienți. Și această nevoie poate apărea de multe ori. Pentru aceasta, veți scrie aceeași formulă complexă de fiecare dată.
| = IF (COUNTIF (Client, I3), „În listă”, „Nu este în listă”) |
Ce zici, dacă tastați doar „= IsInCustomer” într-o celulă și vă va arăta dacă valoarea din celula din stânga este sau nu în lista de clienți?
De exemplu, am pregătit un tabel aici. Acum vreau doar să scriu „= IsInCustomer” în J5 și aș dori să văd dacă valoarea este în I5 se află în lista de clienți sau Nu. Pentru a face acest lucru, urmați acești pași.
- Apăsați CTRL + F3
- Faceți clic pe Nou
- În numele Scrieți, „IsInCustomer”
- În „Se referă la” scrieți formula dvs. = IF (COUNTIF (Client, I5), „În listă”, „Nu este în listă”)
- Apăsați butonul OK.
Acum, oriunde tastați „IsInCustomer”, va verifica valoarea din celula din stânga din lista de clienți.
Acest lucru vă împiedică să vă repetați din nou și din nou.
Aplicați domenii numite la formule
De atâtea ori, definim nume în intervalele noastre după ce am scris deja formule bazate pe intervale. De exemplu, am Preț total ca celule = E2 * F2. Cum o putem schimba în Unități * Unit_Cost.
- Selectați formulele.
- Mergeți la fila formulă. Faceți clic pe meniul drop-down Define Name.
- Faceți clic pe Aplicare nume.
- Va apărea lista tuturor intervalelor denumite. Alegeți numele potrivite și apăsați ok.
Și numele sunt acum aplicate. O puteți vedea în bara de formule.
Formule ușor de citit cu game numite
După cum ați văzut că intervalele denumite facilitează citirea formulelor. Dacă scriu = COUNTIF („A2: A100”, B2), nimeni nu va înțelege ce încerc să număr, până nu văd datele sau cineva le explică.
Dar dacă scriu = COUNTIF (regiune, „est”), majoritatea utilizatorilor vor afla imediat că numărăm aparițiile „estului” în zona numită regiune.
Formule portabile
Intervalele denumite facilitează copierea și lipirea formulelor fără a vă face griji cu privire la modificarea referințelor. Și puteți lua o formulă dintr-un registru de lucru în altul și va funcționa bine până și cu excepția cazului în care registrul de lucru de destinație are același nume.
De exemplu, dacă aveți o formulă = COUNTIF (regiune, est) în Tabelul de distribuție și aveți un alt registru de lucru Clienți care are, de asemenea, o gamă denumită „Regiune”. Acum, dacă copiați această formulă direct oriunde pe acel registru de lucru, acesta vă va arăta informații corecte. Structura datelor nu va conta. Nu contează unde naiba se află acea coloană din registrul dvs. de lucru. Acest lucru va funcționa corect.
În imaginea de mai sus am folosit exact aceeași formulă în două fișiere diferite pentru a număra numărul sau estul care apare în lista regiunilor. Acum sunt în coloane diferite, dar din moment ce ambele sunt denumite regiuni, va funcționa perfect.
Navigați ușor în registrul de lucru
Este mai ușor să navigați într-un registru de lucru cu intervale denumite. Trebuie doar să tastați numele acestuia în caseta de nume. Excel vă va duce la gamă, nu contează unde vă aflați în registrul de lucru. Având în vedere că intervalul numit este de domeniul de lucru al registrului de lucru.
De exemplu, dacă vă aflați pe foaia 10 și doriți să obțineți o listă de clienți și nu știți pe ce foaie este. Mergeți la caseta de nume și tastați „client”. Veți fi direcționat către intervalul numit într-o fracțiune de secundă.
Acesta va reduce efortul de amintire a gamelor.
Navigați utilizând hyperlink-uri cu gama denumită
Când foaia dvs. este mare și mergeți adesea dintr-un punct în altul, vă place să utilizați hyperlinkuri pentru a naviga cu ușurință. Gamele bine denumite pot funcționa perfect cu hyperlinkuri. Pentru a adăuga hyperlinkuri folosind intervale denumite, urmați acești pași.
- Alegeți o celulă unde doriți hyperlink
- Apăsați CTRL + K sau accesați fila Insert> HyperLink pentru a deschide caseta de dialog Insert Hyperlink.
- Faceți clic pe Loc în acest document.
- Derulați în jos pentru a vedea Intervalele numite disponibile sub Nume definite
- Selectați zona denumită pentru a insera un hyperlink la acea zonă.
Și s-a făcut. Aveți hyperlinkul către intervalul numit ales. Utilizând acest lucru puteți crea un index de intervale denumite pe care le puteți vedea și faceți clic pentru a naviga direct la ele. Acest lucru va face ca registrul dvs. de lucru să fie foarte ușor de utilizat.
Denumirea intervalului și validarea datelor
Intervalele denumite și validarea datelor sunt făcute unul pentru celălalt. Intervalele denumite fac validarea datelor foarte personalizabilă. Este mult mai ușor să adăugați o validare dintr-o listă folosind intervalul denumit. Să vedem cum …
- Accesați fila Date
- Faceți clic pe Validarea datelor
- Selectați Listă în secțiunea „Permite:”
- În secțiunea „Sursă:”, tastați „= Client” (scrieți intervalul numit pe care îl aveți)
- Apăsați OK
Acum, această celulă va avea nume ale clienților care fac parte din gama numită Client. Ușor, nu-i așa?
Validarea datelor dependente sau în cascadă cu intervale denumite
Acum, dacă doriți o validare a datelor în cascadă sau dependentă. De exemplu, dacă doriți o listă verticală care să conțină categorii, Fructe și legume. Acum, dacă alegeți fructe, un alt meniu derulant ar trebui să afișeze opțiunea doar fructe, iar dacă alegeți Legume, atunci numai legume.
Acest lucru poate fi realizat cu ușurință utilizând intervale denumite. Afla cum.
- Meniul drop-down dependent folosind Named Range
- Alte modalități de validare a datelor în cascadă
Sper că acest articol despre Cum se utilizează numele într-o formulă în Excel este explicativ. Găsiți mai multe articole despre domeniile de denumire și formulele Excel conexe aici. Dacă ți-au plăcut blogurile noastre, împărtășește-le prietenilor tăi de pe Facebook. Și, de asemenea, ne puteți urmări pe Twitter și Facebook. Ne-ar plăcea să aflăm de la dvs., să ne anunțați cum putem îmbunătăți, completa sau inova munca noastră și să o îmbunătățim. Scrieți-ne pe site-ul de e-mail.
Caseta Nume din Excel : Caseta de nume Excel nu este altceva decât o mică zonă de afișare în partea stângă sus a foii Excel care arată numele celulei active sau a intervalelor din Excel. Puteți redenumi o celulă sau o matrice pentru referințe.
Cum se obține numele foii de lucru în Excel : Funcția CELL în Excel vă oferă informații despre orice foaie de lucru, cum ar fi col, conținut, numele fișierului, etc. etc. Aflați cum să obțineți numele foii folosind funcția CELL aici.
Cum se obține numărul de rând secvențial în Excel: Uneori trebuie să obținem un număr de rând secvențial într-un tabel, poate fi pentru un număr de serie sau orice altceva. În acest articol, vom învăța cum să numerotăm rânduri în excel de la începutul datelor.
Măriți un număr într-un șir de text în Excel: Dacă aveți o listă mare de articole și trebuie să măriți ultimul număr al textului vechiului text în Excel, veți avea nevoie de ajutor din cele două funcții TEXT și DREPT.
Articole populare:
Cum se utilizează funcția IF în Excel : Instrucțiunea IF din Excel verifică starea și returnează o valoare specifică dacă condiția este ADEVĂRATĂ sau returnează o altă valoare specifică dacă este FALSĂ.
Cum se utilizează funcția VLOOKUP în Excel : Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.
Cum se utilizează funcția SUMIF în Excel : Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.
Cum se utilizează funcția COUNTIF în Excel : Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.