Excel

Pomenované rozsahy v programe Excel

Named Ranges Excel

Pomenované rozsahy sú jednou z týchto chrumkavých starých funkcií v programe Excel, ktorým málokto rozumie. Novým používateľom môžu pripadať divné a strašidelné a dokonca sa im môžu vyhnúť aj staré ruky, pretože pôsobia nezmyselne a komplexne.



Ale pomenované rozsahy sú v skutočnosti celkom cool funkcia. Vďaka nim je možné vzorce * oveľa * jednoduchšie vytvárať, čítať a udržiavať. A ako bonus uľahčujú opakované používanie vzorcov (sú prenosnejšie).

V skutočnosti pri testovaní a prototypovaní vzorcov používam pomenované rozsahy stále. Pomáhajú mi rýchlejšie fungovať vzorce. Používam aj pomenované rozsahy, pretože som lenivý a nemám rád písanie zložitých odkazov :)





Základy pomenovaných rozsahov v programe Excel

Čo je pomenovaný rozsah?

Pomenovaný rozsah je iba čitateľný názov pre rozsah buniek v programe Excel. Ak napríklad pomenujem rozsah A1: A100 „údaje“, pomocou MAX môžem získať maximálnu hodnotu pomocou jednoduchého vzorca:

 
 = MAX (data) // max value

Jednoduchý pomenovaný rozsah tzv



Krása pomenovaných rozsahov spočíva v tom, že vo vzorcoch môžete používať zmysluplné názvy bez toho, aby ste mysleli na odkazy na bunky. Akonáhle budete mať pomenovaný rozsah, použite ho rovnako ako odkaz na bunku. Všetky tieto vzorce platia pre pomenovaný rozsah „údaje“:

 
= MAX (data) // max value = MIN (data) // min value = COUNT (data) // total values = AVERAGE (data) // min value

Video: Ako vytvoriť pomenovaný rozsah

Vytvorenie pomenovaného rozsahu je jednoduché

Vytvorenie pomenovaného rozsahu je rýchle a jednoduché. Stačí vybrať rozsah buniek a do poľa pre názov zadajte názov. Po stlačení klávesu Return sa vytvorí meno:

Rýchlo vytvorte pomenovaný rozsah pomocou poľa s názvom

Ak chcete rýchlo otestovať nový rozsah, zvoľte nový názov v rozbaľovacom zozname vedľa poľa s názvom. Excel vyberie rozsah v pracovnom hárku.

Excel môže vytvárať názvy automaticky (ctrl + shift + F3)

Ak máte dobre štruktúrované údaje so štítkami, môžete nechať Excel vytvoriť pomenované rozsahy za vás. Stačí vybrať údaje spolu s menovkami a použiť príkaz „Vytvoriť z výberu“ na karte Vzorce na páse s nástrojmi:

Vytvorte mená z príkazu výberu na páse s nástrojmi

Môžete tiež použiť klávesovú skratku + Shift + F3.

Pomocou tejto funkcie môžeme v jednom kroku vytvoriť pomenované rozsahy pre populáciu 12 štátov:

vzorec na odčítanie buniek v programe Excel

Vytvárajte názvy z výberu s vybranými údajmi a štítkami

Po kliknutí na tlačidlo OK sa vytvoria názvy. Všetky novovytvorené mená nájdete v rozbaľovacej ponuke vedľa poľa s názvom:

Nové mená sa tiež zobrazujú v rozbaľovacej ponuke poľa s názvom

Po vytvorení mien ich môžete použiť vo vzorcoch, ako je tento

 
= SUM (MN,WI,MI)

Aktualizujte pomenované rozsahy v Správcovi názvov (Control + F3)

Akonáhle vytvoríte pomenovaný rozsah, použite Správca mien (Control + F3) aktualizovať podľa potreby. Vyberte názov, s ktorým chcete pracovať, potom priamo zmeňte referenciu (t. J. Upravte „odkazuje na“) alebo kliknite na tlačidlo vpravo a vyberte nový rozsah.

Aktualizované pomenované rozsahy pomocou Správcu mien

Na aktualizáciu referencie nie je potrebné kliknúť na tlačidlo Upraviť. Keď kliknete na položku Zavrieť, názov rozsahu sa aktualizuje.

Poznámka: Ak v hárku vyberiete celý pomenovaný rozsah, môžete ho presunúť na nové miesto a referencia sa automaticky aktualizuje. Neviem však, ako upraviť odkazy na rozsah kliknutím a ťahaním priamo v pracovnom hárku. Ak poznáte spôsob, ako to urobiť, prihláste sa nižšie!

Zobraziť všetky pomenované rozsahy (klávesová skratka + F3)

Ak chcete rýchlo zobraziť všetky pomenované rozsahy v zošite, použite rozbaľovaciu ponuku vedľa poľa s názvom.

Ak chcete vidieť viac podrobností, otvorte Správcu mien (Ctrl + F3), v ktorom sú uvedené všetky mená s odkazmi a tiež je k dispozícii filter:

Správca mien zobrazuje všetky novovytvorené mená

Poznámka: Na počítačoch Mac neexistuje Správca mien, takže sa vám namiesto toho zobrazí dialógové okno Definovať meno.

Skopírujte a prilepte všetky pomenované rozsahy (F3)

Ak chcete trvalejší záznam pomenovaných rozsahov v zošite, môžete prilepiť úplný zoznam názvov kamkoľvek chcete. Prejdite na Vzorce> Použiť vo vzorci (alebo použite skratku F3), potom vyberte Prilepiť názvy> Prilepiť zoznam:

Dialógové okno prilepiť názvy

Keď kliknete na tlačidlo Prilepiť zoznam, zobrazia sa názvy a odkazy vložené do pracovného hárka:

Po vložení pomenovaných rozsahov do pracovného hárka

Prezrite si mená priamo v pracovnom liste

Ak nastavíte úroveň priblíženia na menej ako 40%, Excel zobrazí názvy rozsahov priamo v hárku:

Na úrovni priblíženia <40%zobrazí Excel názvy rozsahov

Ďakujem za tento tip, Felipe!

Mená majú pravidlá

Pri vytváraní pomenovaných rozsahov postupujte podľa týchto pravidiel:

  1. Názvy musia začínať písmenom, podčiarkovníkom (_) alebo spätnou lomkou ()
  2. Názvy nemôžu obsahovať medzery a väčšinu interpunkčných znamienok.
  3. Názvy nemôžu byť v konflikte s odkazmi na bunky - rozsah nemôžete pomenovať „A1“ alebo „Z100“.
  4. Jednotlivé písmená sú vhodné pre názvy („a“, „b“, „c“ atď.), Ale písmená „r“ a „c“ sú vyhradené.
  5. V názvoch sa nerozlišujú malé a veľké písmena-„domov“, „DOMOV“ a „HoMe“ sú v programe Excel rovnaké.

Pomenované rozsahy vo vzorcoch

Pomenované rozsahy sa dajú ľahko použiť vo vzorcoch

Povedzme napríklad, že bunku vo svojom zošite pomenujete „aktualizovaná“. Ide o to, že môžete do bunky vložiť aktuálny dátum (Ctrl +) a odkazovať na dátum kdekoľvek v zošite.

Použitie pomenovaného rozsahu v textovom vzorci

Vzorec v B8 vyzerá takto:

 
='Updated: '&  TEXT (updated, 'ddd, mmmm d, yyyy')

Tento vzorec môžete prilepiť kdekoľvek do zošita a zobrazí sa správne. Kedykoľvek zmeníte dátum v položke „aktualizované“, správa sa aktualizuje pri každom použití vzorca. Viď táto strana pre ďalšie príklady.

Pomenované rozsahy sa zobrazujú pri písaní vzorca

Keď vytvoríte pomenovaný rozsah, automaticky sa zobrazí vo vzorcoch pri zadaní prvého písmena názvu. Keď sa zhodujete, stlačením klávesu Tab zadajte meno a chcete, aby názov zadal Excel.

Pomenované rozsahy sa zobrazujú pri zadávaní vzorcov

Pomenované rozsahy môžu fungovať ako konštanty

Pretože pomenované rozsahy sú vytvárané v centrálnom umiestnení, môžete ich použiť ako konštanty bez odkazu na bunku. Môžete napríklad vytvoriť názvy ako „MPG“ (míle na galón) a „CPG“ (cena za galón) a priradiť pevné hodnoty:

Pomenované rozsahy môžu fungovať ako konštanty, bez odkazu na bunku

Potom môžete tieto názvy použiť kdekoľvek vo vzorcoch a aktualizovať ich hodnotu na jednom centrálnom mieste.

Použitie pomenovaného rozsahu ako konštanty vo vzorci

Pomenované rozsahy sú predvolene absolútne

V predvolenom nastavení sa pomenované rozsahy správajú ako absolútne referencie. V tomto pracovnom hárku by napríklad vzorec na výpočet paliva bol:

 
=C5/$D

Štandardný vzorec s absolútnou adresou

Odkaz na D2 je absolútny (uzamknutý), takže vzorec je možné skopírovať bez zmeny D2.

Ak pomenujeme D2 „MPG“, vzorec sa stane:

 
=C5/MPG

Použitie pomenovaného rozsahu ako konštanty vo vzorci

Pretože MPG je predvolene absolútne, vzorec je možné skopírovať do stĺpca D tak, ako je.

Pomenované rozsahy môžu byť tiež relatívne

Napriek tomu, že pomenované rozsahy sú predvolene absolútne, môžu byť aj relatívne. Relatívne pomenovaný rozsah sa týka rozsahu, ktorý je relatívny k polohe aktívnej bunky v čase vytvorenia rozsahu . Výsledkom je, že relatívne pomenované rozsahy sú užitočné pri vytváraní generických vzorcov, ktoré fungujú kdekoľvek sú presunuté.

Môžete napríklad vytvoriť generický pomenovaný rozsah „CellAbove“ takto:

  1. Vyberte bunku A2
  2. Ctrl + F3 otvorí Správcu mien
  3. Tabulátor zadajte do sekcie „Odkazuje na“ a zadajte: = A1

Program CellAbove teraz získa hodnotu z bunky vyššie, kdekoľvek sa použije.

Dôležité: Pred vytvorením názvu sa uistite, že je aktívna bunka na správnom mieste.

Aplikujte pomenované rozsahy na existujúce vzorce

Ak máte existujúce vzorce, ktoré nepoužívajú pomenované rozsahy, môžete požiadať Excel, aby pomenované rozsahy vo vzorcoch použil za vás. Začnite výberom buniek obsahujúcich vzorce, ktoré chcete aktualizovať. Potom spustite vzorce> Definovať mená> Použiť názvy.

Dialógové okno Použiť mená

Excel potom nahradí referencie, ktoré majú zodpovedajúci pomenovaný rozsah, samotným názvom.

Mená môžete použiť aj pomocou príkazu nájsť a nahradiť:

Použitie rozsahov mien pomocou funkcie nájsť a nahradiť

Dôležité: Uložte si zálohu pracovného hárka a vyberte iba bunky, ktoré chcete zmeniť, než použijete vo vzorcoch funkciu Hľadať a nahradiť.

Kľúčové výhody pomenovaných rozsahov

Pomenované rozsahy uľahčujú čítanie vzorcov

Najväčšou výhodou pomenovaných rozsahov je, že uľahčujú čítanie a údržbu vzorcov. Dôvodom je, že zamieňajú záhadné odkazy za zmysluplné názvy. Zoberme si napríklad tento pracovný list s údajmi o planétach v našej slnečnej sústave. Bez pomenovaných rozsahov je vzorec VLOOKUP na načítanie „polohy“ z tabuľky dosť záhadný:

 
= VLOOKUP ($H,$B:$E,2,0)

Bez pomenovaných rozsahov môžu byť vzorce záhadné

Ako nájdete kruhový odkaz v programe Excel

S B3: E11 s názvom „údaje“ a H4 s názvom „planéta“ však môžeme písať vzorce takto:

 
= VLOOKUP (planet,data,2,0) // position = VLOOKUP (planet,data,3,0) // diameter = VLOOKUP (planet,data,4,0) // satellites

S pomenovanými rozsahmi môžu byť vzorce jednoduché

Na prvý pohľad vidíte jediný rozdiel v týchto vzorcoch v stĺpcovom indexe.

Vďaka pomenovaným rozsahom sú vzorce prenosné a opakovane použiteľné

Pomenované rozsahy môžu výrazne uľahčiť opätovné použitie vzorca v inom pracovnom hárku. Ak názvy definujete v pracovnom hárku vopred, môžete vložiť vzorec, ktorý používa tieto názvy, a bude to „fungovať“. Je to vynikajúci spôsob, ako rýchlo získať vzorec.

Tento vzorec napríklad počíta jedinečné hodnoty v rozsahu číselných údajov:

 
= SUM (--( FREQUENCY (data,data)>0))

Ak chcete tento vzorec rýchlo „preniesť“ do vlastného pracovného hárka, pomenujte rozsah „údajov“ a vzorec prilepte do pracovného hárka. Pokiaľ „údaje“ obsahujú číselné hodnoty, vzorec bude fungovať okamžite.

Tip: Odporúčam vám, aby ste v cieľovom zošite najskôr vytvorili požadované názvy rozsahov *, * potom skopírujte vzorec iba ako text (tj. Nekopírujte bunku, ktorá obsahuje vzorec, do iného pracovného hárka, stačí skopírovať text vzorca ). To zastaví Excel v vytváraní mien za behu a l vám umožní plne ovládať proces vytvárania názvu. Ak chcete kopírovať iba text vzorca, skopírujte text z panela vzorcov alebo ho skopírujte cez inú aplikáciu (napríklad prehliadač, textový editor atď.).

Pomenované rozsahy je možné použiť na navigáciu

Pomenované rozsahy sú skvelé pre rýchlu navigáciu. Stačí vybrať rozbaľovaciu ponuku vedľa poľa s názvom a vybrať meno. Keď pustíte myš, zvolí sa rozsah. Keď pomenovaný rozsah existuje na inom hárku, budete naň automaticky presmerovaní.

Pomenované rozsahy umožňujú jednoduchú navigáciu

Pomenované rozsahy dobre fungujú s hypertextovými odkazmi

Pomenované rozsahy uľahčujú hypertextové odkazy. Ak napríklad v hárku 1 pomenujete „domov“, môžete vytvoriť hypertextový odkaz niekde inde, ktorý vás tam vráti.

Vytvorenie hypertextového odkazu na pomenovaný rozsah

Príklad hypertextového odkazu na pomenovaný rozsah v pracovnom hárku

Ak chcete používať pomenovaný rozsah vo funkcii HYPERLINK, pridajte pred pomenovaný rozsah symbol libry:

 
= HYPERLINK ('#home','take me home')

Poznámka: napodiv nemôžete na stránku hypertextovo odkazovať tak, ako na názov normálneho rozsahu. Môžete však definovať názov, ktorý sa rovná tabuľke (t. J. = Tabuľka1), a naň vytvoriť hypertextový odkaz. Ak niekto vie, ako prepojiť tabuľku priamo, dajte nám vedieť!

Pomenované rozsahy na overenie údajov

Rozsahy mien fungujú dobre na validáciu údajov, pretože vám umožňujú použiť logicky pomenovaný odkaz na validáciu vstupu pomocou rozbaľovacej ponuky. Nasleduje rad G4: G8 s názvom „statuslist“, potom použite validáciu údajov pomocou takto prepojeného zoznamu:

Použitie pomenovaného rozsahu na validáciu údajov pomocou zoznamu

Výsledkom je rozbaľovacia ponuka v stĺpci E, ktorá povoľuje iba hodnoty v pomenovanom rozsahu:

Overenie údajov s príkladom pomenovaného rozsahu

Dynamické pomenované rozsahy

Rozsahy mien sú mimoriadne užitočné, keď sa automaticky prispôsobujú novým údajom v hárku. Takto nastavený rozsah sa označuje ako „dynamický pomenovaný rozsah“. Existujú dva spôsoby, ako vytvoriť dynamický rozsah: vzorce a tabuľky.

Dynamický pomenovaný rozsah s tabuľkou

Tabuľka je najľahší spôsob, ako vytvoriť dynamický pomenovaný rozsah. Vyberte ľubovoľnú bunku v údajoch a potom použite skratku Ctrl + T:

Vytvorenie tabuľky programu Excel

Pri vytváraní tabuľky programu Excel sa automaticky vytvorí názov (napr. Tabuľka1), ale tabuľku môžete ľubovoľne premenovať. Keď vytvoríte tabuľku, automaticky sa rozbalí po pridaní údajov.

Tabuľky sa automaticky rozbalia a je možné ich premenovať

Dynamický pomenovaný rozsah so vzorcom

Dynamický pomenovaný rozsah môžete vytvoriť aj pomocou vzorcov pomocou funkcií ako OFFSET a INDEX. Aj keď sú tieto vzorce stredne zložité, poskytujú ľahké riešenie, keď nechcete používať tabuľku. Nasledujúce odkazy poskytujú príklady s úplným vysvetlením:

  • Príklad vzorca pre dynamický rozsah s INDEX
  • Príklad vzorca dynamického rozsahu s OFFSET

Názvy tabuliek pri overovaní údajov

Pretože tabuľky Excel poskytujú automatický dynamický rozsah, zdá sa, že sú prirodzené pre pravidlá overovania údajov, kde je cieľom overenie na základe zoznamu, ktorý sa môže vždy meniť. Jeden problém s tabuľkami však je, že nemôžete použiť štruktúrované odkazy priamo na vytváranie pravidiel overovania údajov alebo podmieneného formátovania. Inými slovami, názov tabuľky nemôžete používať v oblastiach zadávania podmieneného formátovania alebo overovania údajov.

Ako alternatívne riešenie však môžete definovať pomenovaný pomenovaný rozsah, ktorý ukazuje na tabuľku, a potom pomenovaný rozsah použiť na validáciu údajov alebo podmienené formátovanie. Nasledujúce video podrobne popisuje tento prístup.

Video: Ako používať pomenované rozsahy s tabuľkami

Odstraňujú sa pomenované rozsahy

Poznámka: Ak máte vzorce, ktoré odkazujú na pomenované rozsahy, možno budete chcieť vzorce pred odstránením názvov najskôr aktualizovať. V opačnom prípade uvidíte #NAME? chyby vo vzorcoch, ktoré stále odkazujú na odstránené názvy. Pred odstránením pomenovaných rozsahov si vždy uložte pracovný hárok v prípade, že máte problémy a potrebujete sa vrátiť k originálu.

Pomenované rozsahy sa upravujú pri odstraňovaní a vkladaní buniek

Keď odstránite * časť * pomenovaného rozsahu alebo ak vložíte bunky/riadky/stĺpce do pomenovaného rozsahu, referencia na rozsah sa zodpovedajúcim spôsobom upraví a zostane platná. Ak však vymažete všetky bunky, ktoré uzatvárajú pomenovaný rozsah, pomenovaný rozsah stratí referenciu a zobrazí sa chyba #REF. Ak napríklad pomenujem A1 „test“ a potom vymažem stĺpec A, správca mien zobrazí „odkazuje na“ ako:

 
=Sheet1!#REF!

Vymažte mená pomocou Správcu mien

Ak chcete ručne odstrániť pomenované rozsahy zo zošita, otvorte správcu mien, vyberte rozsah a kliknite na tlačidlo Odstrániť. Ak chcete odstrániť viac ako jedno meno súčasne, môžete stlačením klávesov Shift + Click alebo Ctrl + Click vybrať viac mien a potom ich v jednom kroku odstrániť.

Vymažte mená s chybami

Ak máte veľa mien s referenčnými chybami, môžete pomocou filtra v správcovi názvov filtrovať mená s chybami:

Ponuka filtra Správca mien

Potom shift+kliknutím vyberte všetky mená a odstráňte.

Pomenované rozsahy a rozsah

Pomenované rozsahy v programe Excel majú takzvaný „rozsah“, ktorý určuje, či je pomenovaný rozsah lokálny pre daný pracovný hárok alebo globálny v celom zošite. Globálne názvy majú rozsah „zošita“ a miestne názvy majú rozsah rovnaký ako názov hárka, na ktorom existujú. Rozsah pre miestny názov môže byť napríklad „List2“.

Účel rozsahu

Pomenované rozsahy s globálnym rozsahom sú užitočné, ak chcete, aby všetky hárky v zošite mali prístup k určitým údajom, premenným alebo konštantám. Môžete napríklad použiť globálny pomenovaný rozsah predpokladu daňovej sadzby použitý v niekoľkých niekoľkých pracovných hárkoch.

Miestny rozsah

Miestny rozsah znamená, že názov funguje iba na hárku, na ktorom bol vytvorený. To znamená, že v jednom zošite môžete mať viacero pracovných hárkov, ktoré všetky používajú rovnaký názov. Možno máte napríklad zošit s mesačnými sledovacími listami (jeden za mesiac), ktoré používajú pomenované rozsahy s rovnakým názvom, všetky sú určené lokálne. To vám umožní znova použiť rovnaké vzorce na rôznych listoch. Miestny rozsah umožňuje, aby názvy v každom hárku fungovali správne bez toho, aby došlo k stretu s názvami v ostatných hárkoch.

Ak chcete odkazovať na názov s miestnym rozsahom, môžete pred názov listu predponovať názov hárka:

 
Sheet1!total_revenue Sheet2!total_revenue Sheet3!total_revenue

Názvy rozsahov vytvorené pomocou kolónka na meno automaticky majú globálny rozsah. Ak chcete toto správanie prepísať, pri definovaní názvu pridajte názov hárka:

 
Sheet3!my_new_name

Globálny rozsah

Globálny rozsah znamená, že meno bude fungovať kdekoľvek v zošite. Bunke môžete napríklad dať názov „last_update“, do bunky zadajte dátum. Potom môžete pomocou nižšie uvedeného vzorca zobraziť dátum poslednej aktualizácie v ľubovoľnom pracovnom hárku.

 
=last_update

Globálne názvy musia byť v zošite jedinečné.

Miestny rozsah

Miestne vymedzené pomenované rozsahy majú zmysel pre pracovné hárky, ktoré používajú pomenované rozsahy iba pre miestne predpoklady. Môžete mať napríklad pracovný zošit s mesačnými sledovacími listami (jeden za mesiac), ktoré používajú pomenované rozsahy s rovnakým názvom, všetky sú určené lokálne. Miestny rozsah umožňuje, aby názvy v každom hárku fungovali správne bez toho, aby došlo k stretu s názvami v ostatných hárkoch.

Správa pomenovaného rozsahu rozsahu

Nové názvy vytvorené pomocou poľa názvov sú predvolene globálne a rozsah pomenovaného rozsahu po jeho vytvorení nemôžete upravovať. Ako alternatívne riešenie však môžete odstrániť a znova vytvoriť názov s požadovaným rozsahom.

Ak chcete zmeniť niekoľko názvov naraz z globálnych na miestne, niekedy má zmysel skopírovať list, ktorý obsahuje názvy. Keď duplikujete pracovný hárok, ktorý obsahuje pomenované rozsahy, Excel skopíruje pomenované rozsahy na druhý hárok a súčasne zmení rozsah na lokálny. Potom, čo budete mať druhý hárok s miestnymi názvami, môžete prvý hárok voliteľne odstrániť.

Jan Karel Pieterse a Charles Williams vyvinuli nástroj s názvom Správca mien, ktorý poskytuje mnoho užitočných operácií pre pomenované rozsahy. Môžeš tu si stiahnite nástroj Name Manager .

Autor Dave Bruns


^