intervalele numite sunt una dintre aceste caracteristici vechi cruste în Excel pe care puțini utilizatori le înțeleg. Utilizatorii noi le pot găsi ciudate și înfricoșătoare și chiar mâinile vechi le pot evita, deoarece par inutile și complexe.
dar intervalele numite sunt de fapt o caracteristică destul de mișto. Ele pot face formule * mult* mai ușor de creat, citit și întreținut. Și ca bonus, fac formulele mai ușor de reutilizat (mai portabile).de fapt, folosesc intervale numite tot timpul la testarea și prototiparea formulelor. Mă ajută să fac formulele să funcționeze mai repede., De asemenea, folosesc intervale numite pentru că sunt leneș și nu-mi place să introduc referințe complexe 🙂
elementele de bază ale intervalelor numite în Excel
ce este un interval numit?
un interval numit este doar un nume care poate fi citit de om pentru o serie de celule din Excel. De exemplu, dacă am numele intervalul A1:A100 „date”, pot folosi MAX pentru a obține valoarea maximă cu o formulă simplă:
=MAX(data) // max value
frumusețea numit game este că puteți folosi nume semnificative în formule fără să mă gândesc la referințe de celule., Odată ce aveți un interval numit, trebuie doar să-l utilizați la fel ca o referință de celule. Toate aceste formule sunt valabile cu o zonă denumită „date”:
=MAX(data) // max value=MIN(data) // min value=COUNT(data) // total values=AVERAGE(data) // min valueVideo: Cum de a crea o zonă denumită
Crearea unui nume gamă este ușor
Crearea unui nume gamă este rapidă și ușoară. Trebuie doar să selectați o gamă de celule și să introduceți un nume în caseta nume. Când apăsați return, numele este creat:
pentru a testa rapid noul interval, alegeți noul nume în meniul derulant de lângă caseta nume. Excel va selecta intervalul din foaia de lucru.,
Excel poate crea nume automat (ctrl + shift + F3)
Dacă aveți date bine structurate cu etichete, puteți avea Excel crea intervale numite pentru tine. Doar selectați datele, împreună cu etichetele, și utilizați comanda „Creați Din selecție” din fila Formule a panglicii:
puteți utiliza, de asemenea, comanda rapidă de la tastatură control + shift + F3.folosind această caracteristică, putem crea intervale numite pentru populația a 12 state într-un singur pas:
când faceți clic pe OK, numele sunt create., Veți găsi toate nou-create nume din meniul drop-down de lângă caseta nume:
Cu nume l-a creat, le puteți utiliza în formule de genul asta
=SUM(MN,WI,MI)Actualizare numit variază în Name Manager (Ctrl + F3)
după ce ați creat un nume de serie, utilizați Numele de Manager (Ctrl + F3) să actualizați așa cum este necesar. Selectați numele cu care doriți să lucrați, apoi schimbați referința direct (adică editați „se referă la”) sau faceți clic pe butonul din dreapta și selectați un nou interval.,
nu este nevoie să faceți clic pe butonul Editare pentru a actualiza o referință. Când faceți clic pe Închidere, numele intervalului va fi actualizat.Notă: Dacă selectați un întreg interval numit pe o foaie de lucru, puteți trage într-o locație nouă, iar referința va fi actualizată automat. Cu toate acestea, nu cunosc o modalitate de a ajusta referințele de gamă făcând clic și glisând direct pe foaia de lucru. Dacă știți o modalitate de a face acest lucru, chime în jos!
vedeți toate intervalele numite (control + F3)
pentru a vedea rapid toate intervalele numite într-un registru de lucru, utilizați meniul derulant de lângă caseta nume.,
Dacă doriți să vedeți mai multe detalii, deschide Name Manager (Ctrl + F3), care listează toate numele cu referințe, și oferă un filtru precum:
Notă: pe un Mac, nu există nici un Nume de Manager, astfel încât veți vedea Definire Nume dialog în loc.dacă doriți o înregistrare mai persistentă a intervalelor numite într-un registru de lucru, puteți lipi lista completă de nume oriunde doriți., Du-te la Formule > Utilizați în Formula (sau de a folosi comenzi rapide F3), apoi selectați Inserați numele > Inserați Lista:
atunci Când faceți clic pe Inserați codul butonul Listă, veți vedea numele și referințele inserate în foaia de lucru:
a se Vedea numele direct pe foaia de lucru
Dacă setați nivelul de zoom la mai puțin de 40%, Excel va afișa gama nume direct pe foaia de lucru:
Multumesc pentru acest sfat, Felipe!,când creați intervale numite, urmați aceste reguli:
intervale numite în formule
intervalele numite sunt ușor de utilizat în formule
de exemplu, să spunem că numiți o celulă din registrul de lucru „actualizat”. Ideea este că puteți pune data curentă în celulă (Ctrl + 😉 și se referă la data în altă parte în registrul de lucru.
formula în B8 pare ca acest lucru:
="Updated: "& TEXT(updated, "ddd, mmmm d, yyyy")puteți să-l lipiți această formulă oriunde în registrul de lucru și se va afișa corect., Ori de câte ori schimbați data în „actualizat”, mesajul se va actualiza oriunde se utilizează formula. Consultați această pagină pentru mai multe exemple.
intervalele numite apar atunci când tastați o formulă
după ce ați creat un interval numit, acesta va apărea automat în formule atunci când tastați prima literă a numelui. Apăsați tasta tab pentru a introduce numele atunci când aveți o potrivire și doriți ca Excel să introducă numele.,
Nume variază de muncă constante
Pentru intervale denumite sunt create într-o locație centrală, le puteți folosi ca constante, fără o referință de celulă. De exemplu, puteți crea nume ca „MPG” (mile pe galon) și „CPG” (cost per galon) cu și atribui valori fixe:
Apoi, puteți utiliza aceste nume oriunde doriți în formule, și să actualizeze valoarea lor într-o singură locație centrală.,
intervalele numite sunt absolute în mod implicit
în mod implicit, intervalele numite se comportă ca referințe absolute. De exemplu, în această foaie de lucru, formula pentru a calcula combustibil ar fi:
=C5/$D$2
referința la D2 este absolută (blocat) deci formula poate fi copiat fără D2 schimbare.,
Dacă numele D2 „MPG” formula devine:
=C5/MPG
Deoarece MPG este absolut implicit, formula poate fi copiat coloana D ca-este.
intervalele numite pot fi, de asemenea, relative
deși intervalele numite sunt absolute în mod implicit, ele pot fi, de asemenea, relative. Un interval relativ numit se referă la un interval care este relativ la poziția celulei active la momentul creării intervalului. Ca rezultat, intervalele relative numite sunt formule generice de construcție utile care funcționează oriunde sunt mutate.,
De exemplu, puteți crea un generic „CellAbove” numit gama de genul asta:
- Selectați celula A2
- Control + F3 pentru a deschide Manager Nume
- Tab-ul într ‘se Referă la secțiunea, apoi tastați: =A1
CellAbove va prelua valoarea din celula de mai sus ori de câte ori este folosit.
Important: asigurați-vă că celula activă se află în locația corectă înainte de a crea numele.
aplicați intervale numite formulelor existente
Dacă aveți formule existente care nu utilizează intervale numite, puteți solicita Excel să aplice intervalele numite în formulele pentru dvs., Începeți prin selectarea celulelor care conțin formule pe care doriți să le actualizați. Apoi executați formulele >definiți nume > aplicați nume.
Excel va înlocui apoi referințele care au un interval numit corespunzător cu numele în sine.
de asemenea, puteți aplica cu numele find and replace:
Important: Salvați o copie de rezervă a foii de lucru, și selectați doar celulele pe care doriți să modificați înainte de a utiliza găsi și înlocui pe formule.,beneficiile cheie ale intervalelor numite
gamele numite fac formulele mai ușor de citit
cel mai mare beneficiu unic pentru intervalele numite este faptul că formulele sunt mai ușor de citit și de întreținut. Acest lucru se datorează faptului că înlocuiesc referințele criptice cu nume semnificative. De exemplu, luați în considerare această foaie de lucru cu date despre planetele din sistemul nostru solar., Fără nume variază, o formula VLOOKUP pentru a aduce „Poziția” de masă este destul de criptic:
=VLOOKUP($H$4,$B$3:$E$11,2,0)
cu toate Acestea, cu B3:E11 numit „date”, și H4 numit „planeta”, putem scrie formulele de genul asta:
=VLOOKUP(planet,data,2,0) // position=VLOOKUP(planet,data,3,0) // diameter=VLOOKUP(planet,data,4,0) // satellites
La o privire, puteți vedea singura diferență în aceste formule în coloana index.
game numite faceți formulele portabile și reutilizabile
intervalele numite pot face mult mai ușor reutilizarea unei formule într-o altă foaie de lucru., Dacă definiți numele înainte de timp într-o foaie de lucru, puteți lipi într-o formulă care utilizează aceste nume și va „lucra doar”. Aceasta este o modalitate foarte bună de a obține rapid o formulă de lucru.
De exemplu, această formulă contează valori unice într-o serie de date numerice:
=SUM(--(FREQUENCY(data,data)>0))La rapid „port” această formulă la propria foaie de lucru, numele o serie de „date” și inserați formula în foaia de lucru. Atâta timp cât” date ” conține valori numerice, formula va funcționa imediat.,
Sfat: vă recomand să creați numele intervalului necesar *mai întâi* în registrul de lucru destinație, apoi copiați în formulă doar ca text (adică nu copiați celula care conține formula într-o altă foaie de lucru, copiați doar textul formulei). Acest lucru împiedică Excel să creeze nume din mers și vă permite să controlați complet procesul de creare a numelui. Pentru a copia numai textul formulei, copiați textul din bara de formule sau copiați printr-o altă aplicație (adică browser, editor de text etc.).).
intervalele numite pot fi utilizate pentru navigare
intervalele numite sunt excelente pentru navigarea rapidă., Doar selectați meniul derulant de lângă caseta Nume și alegeți un nume. Când eliberați mouse-ul, intervalul va fi selectat. Când un interval numit există pe o altă foaie, veți fi dus automat la acea foaie.
intervalele numite funcționează bine cu hyperlink-urile
intervalele numite facilitează hyperlink-urile. De exemplu, dacă numiți A1 în Sheet1 „acasă”, puteți crea un hyperlink în altă parte care vă duce acolo.,
Pentru a utiliza un nume gamă în funcția HYPERLINK, se adaugă o jumătate de kilogram simbol în fața numit raza de actiune:
=HYPERLINK("#home","take me home")Notă: în mod ciudat, nu puteți hyperlink la o masă ca tine poate-un interval normal de nume. Cu toate acestea, puteți defini un nume egal cu un tabel (adică =Table1) și hyperlink la asta. Dacă cineva știe o modalitate de a lega direct o masă, chime în!,
intervale numite pentru validarea datelor
intervalele de nume funcționează bine pentru validarea datelor, deoarece vă permit să utilizați o referință numită logic pentru a valida intrarea cu un meniu derulant. Mai jos, gama G4:G8 este numit „statuslist”, apoi se aplică de validare a datelor cu o Listă legată de genul asta:
rezultatul este un meniu vertical în coloana E, care permite doar valori în intervalul:
Dinamic Numit Variază
Nume game sunt extrem de utile atunci când vor ajusta în mod automat la noile date într-o foaie de lucru., O gamă configurată în acest fel este denumită „interval dinamic numit”. Există două moduri de a face o gamă dinamică: formule și tabele.
interval dinamic numit cu un tabel
Un tabel este cel mai simplu mod de a crea un interval dinamic numit. Selectați orice celulă din date, apoi utilizați comanda rapidă Control + T:
când creați un tabel Excel, un nume este creat automat (de exemplu, Table1), dar puteți redenumi tabelul după cum doriți. După ce ați creat un tabel, acesta se va extinde automat atunci când se adaugă date.,
interval dinamic numit cu o formulă
puteți crea, de asemenea, un interval dinamic numit cu formule, folosind funcții precum OFFSET și INDEX. Deși aceste formule sunt moderat complexe, ele oferă o soluție ușoară atunci când nu doriți să utilizați un tabel., Link-urile de mai jos oferă exemple cu explicații complete:
- Exemplu de interval dinamic formula cu INDEX
- Exemplu de interval dinamic formula cu OFFSET
numele de Masă în validarea datelor
Din Tabele Excel furnizează automat o gamă dinamică, acestea ar părea să fie o potrivire naturală de date, reguli de validare, în cazul în care obiectivul este de a valida împotriva o listă care poate fi mereu în schimbare. Cu toate acestea, o problemă cu tabelele este că nu puteți utiliza Referințe structurate direct pentru a crea reguli de validare a datelor sau formatare condiționată., Cu alte cuvinte, nu puteți utiliza un nume de tabel în formatarea condiționată sau în zonele de introducere a validării datelor.cu toate acestea, ca soluție alternativă, puteți defini un interval numit care indică un tabel și apoi puteți utiliza intervalul numit pentru validarea datelor sau formatarea condiționată. Videoclipul de mai jos trece prin această abordare în detaliu.
Video: Cum se utilizează intervalele numite cu tabele
ștergerea intervalelor numite
Notă: Dacă aveți formule care se referă la intervalele numite, poate doriți să actualizați formulele mai întâi înainte de a elimina numele. În caz contrar, veți vedea #NAME?, erori în formulele care se referă în continuare la numele șterse. Salvați întotdeauna foaia de lucru înainte de a elimina intervalele numite în cazul în care aveți probleme și trebuie să reveniți la original.
intervalele numite se ajustează la ștergerea și inserarea celulelor
când ștergeți * o parte* dintr-un interval numit sau dacă inserați celule/rânduri/coloane într-un interval numit, referința intervalului se va ajusta în consecință și va rămâne validă. Cu toate acestea, dacă ștergeți toate celulele care includ un interval numit, intervalul numit va pierde referința și va afișa o eroare #REF., De exemplu, dacă am numele A1 „test”, apoi șterge O coloană, cu numele de manager va afișa „se referă la” a fi:
=Sheet1!#REF!Ștergere nume cu Numele de Manager
Pentru a elimina nume variază de la un registru de lucru manual, deschideți manager nume, selectați un interval, și faceți clic pe butonul Șterge. Dacă doriți să eliminați mai multe nume în același timp, puteți schimba + clic sau Ctrl + clic pentru a selecta mai multe nume, apoi ștergeți într-un singur pas.,dacă aveți o mulțime de nume cu erori de referință, puteți utiliza butonul Filtru din Managerul de nume pentru a filtra numele cu erori:
apoi shift+faceți clic pentru a selecta toate numele și a șterge.
intervalele numite și domeniul de aplicare
intervalele numite în Excel au ceva numit „domeniu de aplicare”, care determină dacă un interval numit este local pentru o foaie de lucru dată sau global pe întregul registru de lucru. Numele globale au un domeniu de „registru de lucru”, iar numele locale au un domeniu de aplicare egal cu numele foii pe care există., De exemplu, domeniul de aplicare pentru un nume local ar putea fi „Sheet2”.
scopul domeniului de aplicare
intervalele numite cu un domeniu de aplicare global sunt utile atunci când doriți ca toate foile dintr-un registru de lucru să aibă acces la anumite date, variabile sau constante. De exemplu, puteți utiliza o gamă globală numită o ipoteză a ratei de impozitare utilizată în mai multe foi de lucru.
Local scope
Local scope înseamnă că un nume funcționează numai pe foaia pe care a fost creat. Aceasta înseamnă că puteți avea mai multe foi de lucru în același registru de lucru care utilizează același nume., De exemplu, poate că aveți un registru de lucru cu foi de urmărire lunare (una pe lună) care utilizează intervale numite cu același nume, toate vizate local. Acest lucru vă poate permite să reutilizați aceleași formule în foi diferite. Domeniul local permite ca numele din fiecare foaie să funcționeze corect fără a se ciocni cu numele din celelalte foi.
pentru a se referi La un nume cu o aplicare locală, puteți prefix numele foii la gama nume:
Sheet1!total_revenueSheet2!total_revenueSheet3!total_revenueGama nume creat cu numele cutie automat de aplicare la nivel mondial., Pentru a suprascrie acest comportament, adăugați numele foii atunci când definiți numele:
Sheet3!my_new_nameGlobal scope
Global scope înseamnă că un nume va funcționa oriunde într-un registru de lucru. De exemplu, puteți numi o celulă „last_update”, introduceți o dată în celulă. Apoi puteți utiliza formula de mai jos pentru a afișa data ultimei actualizări în orice foaie de lucru.
=last_updatedenumirile globale trebuie să fie unice într-un registru de lucru.
Local scope
local scope named range are sens pentru foile de lucru care utilizează intervale numite doar pentru ipoteze locale., De exemplu, poate că aveți un registru de lucru cu foi de urmărire lunare (una pe lună) care utilizează intervale numite cu același nume, toate vizate local. Domeniul local permite ca numele din fiecare foaie să funcționeze corect fără a se ciocni cu numele din celelalte foi.
gestionarea domeniului de aplicare numit
în mod implicit, numele noi create cu caseta de nume sunt globale și nu puteți edita domeniul de aplicare al unui interval numit după ce a fost creat. Cu toate acestea, ca soluție, puteți șterge și recrea un nume cu domeniul de aplicare dorit.,dacă doriți să schimbați mai multe nume simultan de la global la local, uneori are sens să copiați foaia care conține numele. Când duplicați o foaie de lucru care conține intervale numite, Excel copiază intervalele numite în a doua foaie, schimbând domeniul de aplicare în local în același timp. După ce aveți a doua foaie cu nume de domeniu local, puteți șterge opțional prima foaie.Jan Karel Pieterse și Charles Williams au dezvoltat un utilitar numit managerul de nume care oferă multe operații utile pentru intervalele numite. Puteți descărca utilitarul Name Manager aici.,
