Cum se creează game numite în Excel (Un ghid pas cu pas)

Ce este numele?

Dacă lucrați cu foi de calcul Excel, aceasta ar putea însemna mult timp și economie.

În acest tutorial, veți afla cum să creați domenii numite în Excel și cum să îl utilizați pentru a economisi timp.

Domenii numite în Excel - o introducere

Dacă cineva trebuie să mă sune sau să se refere la mine, îmi va folosi numele (în loc să spună că un bărbat stă într-un loc așa și așa cu înălțimea și greutatea).

Dreapta?

În mod similar, în Excel, puteți da un nume unei celule sau unei game de celule.

Acum, în loc să utilizați referința celulei (cum ar fi A1 sau A1: A10), puteți utiliza pur și simplu numele pe care i l-ați atribuit.

De exemplu, să presupunem că aveți un set de date așa cum se arată mai jos:

În acest set de date, dacă trebuie să faceți referire la intervalul care are Data, va trebui să utilizați A2: A11 în formule. În mod similar, pentru vânzări și vânzări, va trebui să utilizați B2: B11 și C2: C11.

Deși este bine când aveți doar câteva puncte de date, dar în cazul în care aveți seturi de date imense și complexe, utilizarea referințelor de celule pentru a vă referi la date ar putea consuma mult timp.

Intervalele numite Excel facilitează trimiterea la seturi de date în Excel.

Puteți crea un interval denumit în Excel pentru fiecare categorie de date și apoi utilizați numele respectiv în locul referințelor de celulă. De exemplu, datele pot fi denumite „Data”, datele reprezentanților de vânzări pot fi denumite „SalesRep”, iar datele de vânzări pot fi denumite „Vânzări”.

De asemenea, puteți crea un nume pentru o singură celulă. De exemplu, dacă aveți procentul de comision de vânzări într-o celulă, puteți denumi celula respectivă drept „Comisie”.

Avantajele creării gamelor numite în Excel

Iată avantajele utilizării intervalelor denumite în Excel.

Utilizați nume în loc de referințe celulare

Când creați domenii numite în Excel, puteți utiliza aceste nume în loc de referințele celulei.

De exemplu, puteți utiliza = SUM (SALES) în loc de = SUM (C2: C11) pentru setul de date de mai sus.

Aruncați o privire la formulele enumerate mai jos. În loc să folosesc referințe de celulă, am folosit gamele numite.

• Număr de vânzări cu o valoare mai mare de 500: = COUNTIF (Vânzări, "> 500")
• Suma tuturor vânzărilor realizate de Tom: = SUMIF (SalesRep, „Tom”, Vânzări)
• Comision câștigat de Joe (vânzări cu Joe înmulțite cu procentul de comision):
  = SUMIF (SalesRep, „Joe”, Vânzări) * Comision

Ați fi de acord că aceste formule sunt ușor de creat și ușor de înțeles (mai ales atunci când le împărtășiți cu altcineva sau le revizitați singur.

Nu este nevoie să reveniți la setul de date pentru a selecta celulele

Un alt avantaj semnificativ al utilizării gamelor numite în Excel este că nu trebuie să vă întoarceți și să selectați intervalele de celule.

Puteți să tastați doar câteva alfabete ale acelei zone denumite și Excel va afișa intervalele numite potrivite (așa cum se arată mai jos):

Gamele denumite fac formule dinamice

Utilizând Intervalele denumite în Excel, puteți face dinamice formulele Excel.

De exemplu, în cazul comisionului de vânzări, în loc să utilizați valoarea 2,5%, puteți utiliza gama numită.

Acum, dacă compania dvs. decide mai târziu să crească comisionul la 3%, puteți actualiza pur și simplu gama numită și toate calculele se vor actualiza automat pentru a reflecta noul comision.

Cum se creează game numite în Excel

Iată trei moduri de a crea domenii numite în Excel:

Metoda nr. 1 - Utilizarea Definește numele

Iată pașii pentru a crea domenii numite în Excel folosind Definire nume:

• Selectați intervalul pentru care doriți să creați un domeniu numit în Excel.
• Accesați Formule -> Definiți numele.
• În caseta de dialog Nume nou, tastați numele pe care doriți să-l atribuiți intervalului de date selectat. Puteți specifica domeniul de aplicare ca întregul registru de lucru sau o foaie de lucru specifică. Dacă selectați o anumită foaie, numele nu ar fi disponibil pe alte foi.
• Faceți clic pe OK.

Aceasta va crea o gamă denumită SALESREP.

Metoda # 2: Utilizarea casetei de nume

• Selectați intervalul pentru care doriți să creați un nume (nu selectați anteturi).
• Mergeți la caseta de nume din stânga barei de formule și tastați numele cu care doriți să creați gama numită.
• Rețineți că numele creat aici va fi disponibil pentru întregul registru de lucru. Dacă doriți să o restrângeți la o foaie de lucru, utilizați Metoda 1.

Metoda # 3: Utilizarea opțiunii Creați din selecție

Acesta este modul recomandat atunci când aveți date sub formă de tabel și doriți să creați un interval denumit pentru fiecare coloană / rând.

De exemplu, în setul de date de mai jos, dacă doriți să creați rapid trei intervale denumite (Data, Sales_Rep și Sales), puteți utiliza metoda prezentată mai jos.

Iată pașii pentru a crea rapid intervale denumite dintr-un set de date:

• Selectați întregul set de date (inclusiv antetele).
• Accesați Formule -> Creați din selecție (Comandă rapidă de la tastatură - Control + Shift + F3). Se va deschide caseta de dialog „Creați nume din selecție”.
• În caseta de dialog Creați nume din selecție, verificați opțiunile în care aveți anteturile. În acest caz, selectăm rândul de sus numai deoarece antetul se află în rândul de sus. Dacă aveți anteturi atât în ​​rândul de sus, cât și în coloana din stânga, le puteți alege pe ambele. În mod similar, dacă datele dvs. sunt aranjate atunci când anteturile sunt doar în coloana din stânga, atunci bifați doar opțiunea Coloana din stânga.

Aceasta va crea trei domenii numite - Data, Sales_Rep și Sales.

Rețineți că preia automat numele din anteturi. Dacă există spațiu între cuvinte, acesta introduce o subliniere (deoarece nu puteți avea spații în intervale denumite).

Convenția de denumire pentru domeniile denumite în Excel

Există anumite reguli de numire pe care trebuie să le cunoașteți în timp ce creați domenii numite în Excel:

• Primul caracter al unui Named Range ar trebui să fie o literă și un caracter de subliniere (_) sau o bară inversă (\). Dacă este altceva, va apărea o eroare. Celelalte caractere pot fi litere, cifre, caractere speciale, punct sau subliniere.
• Nu puteți utiliza nume care reprezintă, de asemenea, referințe de celule în Excel. De exemplu, nu puteți utiliza AB1, deoarece este și o referință de celulă.
• Nu puteți utiliza spații în timp ce creați intervale denumite. De exemplu, nu puteți avea reprezentant de vânzări ca interval numit. Dacă doriți să combinați două cuvinte și să creați un domeniu numit, utilizați caractere de subliniere, punct sau majuscule pentru a-l crea. De exemplu, puteți avea Sales_Rep, SalesRep sau SalesRep.
  • În timp ce creează intervale denumite, Excel tratează majuscule și minuscule în același mod. De exemplu, dacă creați un interval denumit VÂNZĂRI, atunci nu veți putea crea un alt interval denumit, cum ar fi „vânzări” sau „Vânzări”.
• Un domeniu numit poate avea până la 255 de caractere.

Prea multe game numite în Excel? Nu vă faceți griji

Uneori, în seturi de date mari și modele complexe, puteți ajunge să creați o mulțime de domenii numite în Excel.

Ce se întâmplă dacă nu vă amintiți numele gamei numite pe care ați creat-o?

Nu vă faceți griji - iată câteva sfaturi utile.

Obținerea numelor tuturor domeniilor numite

Iată pașii pentru a obține o listă cu toate intervalele numite pe care le-ați creat:

• Accesați fila Formule.
• În grupul Defined Named, faceți clic pe Use in Formula.
• Faceți clic pe „Lipiți nume”.

Acest lucru vă va oferi o listă cu toate gamele numite din acel registru de lucru. Pentru a utiliza un interval numit (în formule sau o celulă), faceți dublu clic pe el.

Afișarea intervalelor denumite potrivite

• Dacă aveți o idee despre nume, tastați câteva caractere inițiale, iar Excel va afișa o listă verticală a numelor potrivite.

Cum se editează domeniile denumite în Excel

Dacă ați creat deja un domeniu numit, îl puteți edita urmând pașii următori:

• Mergeți la fila Formule și faceți clic pe Manager de nume.
• Caseta de dialog Manager nume va lista toate intervalele numite din acel registru de lucru. Faceți dublu clic pe zona numită pe care doriți să o editați.
• În caseta de dialog Editați numele, efectuați modificările.
• Faceți clic pe OK.
• Închideți caseta de dialog Manager nume.

Comenzi rapide utile pentru gama numită (puterea lui F3)

Iată câteva comenzi rapide utile de la tastatură care vă vor fi la îndemână atunci când lucrați cu domenii numite în Excel:

• Pentru a obține o listă cu toate gamele numite și a le lipi în Formula: F3
• Pentru a crea un nume nou folosind caseta de dialog Manager de nume: Control + F3
• Pentru a crea domenii numite din selecție: Control + Shift + F3

Crearea gamelor dinamice numite în Excel

Până acum, în acest tutorial, am creat domenii statice numite.

Aceasta înseamnă că aceste domenii numite se vor referi întotdeauna la același set de date.

De exemplu, dacă A1: A10 a fost denumit „Vânzări”, se va referi întotdeauna la A1: A10.

Dacă adăugați mai multe date despre vânzări, atunci va trebui să mergeți manual și să actualizați referința în intervalul numit.

În lumea seturilor de date în continuă expansiune, acest lucru ar putea să-ți ocupe mult timp. De fiecare dată când obțineți date noi, poate fi necesar să actualizați gamele numite în Excel.

Pentru a rezolva această problemă, putem crea intervale dinamice numite în Excel, care ar contabiliza automat date suplimentare și le vor include în intervalul numit existent.

De exemplu, De exemplu, dacă adaug două puncte de date suplimentare despre vânzări, un interval dinamic denumit se va referi automat la A1: A12.

Acest tip de gamă dinamică denumită poate fi creat utilizând funcția Excel INDEX. În loc să specificăm referințele de celulă în timp ce creăm gama numită, specificăm formula. Formula se actualizează automat la adăugarea sau ștergerea datelor.

Să vedem cum să creăm game dinamice numite în Excel.

Să presupunem că avem datele despre vânzări în celula A2: A11.

Iată pașii pentru a crea game dinamice numite în Excel:

1. 1. Mergeți la fila Formula și faceți clic pe Definire nume.
   2. În caseta de dialog Nume nou, tastați următoarele:
      • Nume: Vânzări
      • Domeniu: Caiet de lucru
      • Se referă la: = $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))
   3. Faceți clic pe OK.

Terminat!

Acum aveți un interval dinamic denumit cu numele „Vânzări”. Acest lucru se va actualiza automat ori de câte ori adăugați date sau eliminați date din acesta.

Cum funcționează gamele dinamice numite?

Pentru a explica cum funcționează acest lucru, trebuie să știți mai multe despre funcția Excel INDEX.

Majoritatea oamenilor folosesc INDEX pentru a returna o valoare dintr-o listă bazată pe numărul rândului și al coloanei.

Dar funcția INDEX are și o altă latură.

Poate fi obișnuit returnează o referință de celulă atunci când este utilizat ca parte a unei referințe de celulă.

De exemplu, iată formula pe care am folosit-o pentru a crea un interval denumit dinamic:

= $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, "" & ""))

INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””) -> Se așteaptă ca această parte a formulei să returneze o valoare (care ar fi a 10-a valoare din listă, având în vedere că există zece itemi).

Cu toate acestea, atunci când este utilizat în fața unei referințe (=$ A $ 2:INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))) returnează referința la celulă în loc de valoare.

Prin urmare, aici returnează = $ A $ 2: $ A $ 11

Dacă adăugăm două valori suplimentare la coloana de vânzări, atunci se va returna = $ A $ 2: $ A $ 13

Când adăugați date noi la listă, funcția Excel COUNTIF returnează numărul de celule ne-goale din date. Acest număr este utilizat de funcția INDEX pentru a prelua referința la celulă a ultimului element din listă.

Notă:

• Acest lucru ar funcționa numai dacă nu există celule goale în date.
• În exemplul luat mai sus, am atribuit un număr mare de celule (A2: A100) pentru formula Named Range. Puteți ajusta acest lucru pe baza setului de date.

De asemenea, puteți utiliza funcția OFFSET pentru a crea o gamă dinamică numită în Excel, cu toate acestea, deoarece funcția OFFSET este volatilă, poate duce la un registru de lucru Excel lent. INDEX, pe de altă parte, este semi-volatil, ceea ce îl face o alegere mai bună pentru a crea game dinamice numite în Excel.

Este posibil să vă placă și următoarele resurse Excel:

• Șabloane Excel gratuite.
• Instruire online gratuită Excel (Curs video online în 7 părți).
• Exemple utile de coduri macro pentru Excel.
• 10 Exemple avansate de VLOOKUP Excel.
• Crearea unei liste derulante în Excel.
• Crearea unei game denumite în Foi de calcul Google.
• Cum să faceți referire la o altă foaie sau registru de lucru în Excel