pojmenované rozsahy jsou jednou z těchto křupavých starých funkcí v Excelu, kterým rozumí jen málo uživatelů. Noví uživatelé je mohou považovat za divné a děsivé a dokonce i staré ruce se jim mohou vyhnout, protože se zdají zbytečné a složité.
ale pojmenované rozsahy jsou ve skutečnosti docela cool funkce. Mohou vytvořit vzorce * hodně * snadnější vytvářet, číst a udržovat. A jako bonus usnadňují opětovné použití vzorců (přenosnější).
ve skutečnosti používám pojmenované rozsahy po celou dobu při testování a prototypování vzorců. Pomáhají mi, aby vzorce fungovaly rychleji., Používám také pojmenované rozsahy, protože jsem líný a nemám rád psaní složitých odkazů 🙂
základy pojmenovaných rozsahů v Excelu
jaký je pojmenovaný rozsah?
pojmenovaný rozsah je jen lidsky čitelný název pro řadu buněk v Excelu. Například, pokud jsem název rozsah A1:A100 „data“, můžu použít MAX získat maximální hodnotu s jednoduchý vzorec:
=MAX(data) // max value
krása pojmenované oblasti je, že můžete používat smysluplné názvy ve vašich vzorcích, aniž by přemýšlel o odkazy na buňky., Jakmile máte pojmenovaný rozsah, použijte jej stejně jako odkaz na buňku. Všechny tyto vzorce jsou platné s pojmenovanou oblast „data“:
=MAX(data) // max value=MIN(data) // min value=COUNT(data) // total values=AVERAGE(data) // min valueVideo: Jak vytvořit pojmenovanou oblast
Vytvořit pojmenovanou oblast je snadné
Vytvořit pojmenovanou oblast je rychlé a snadné. Stačí vybrat řadu buněk a do pole Název zadejte název. Po stisknutí klávesy return se vytvoří Název:
Chcete-li rychle otestovat nový rozsah, vyberte nový název v rozevíracím seznamu vedle pole Jméno. Excel vybere rozsah v listu.,
Excel může vytvářet jména automaticky (ctrl + shift + F3)
Pokud máte dobře strukturovaná data se štítky, můžete mít Excel vytvořit pojmenované rozsahy pro vás. Stačí si vybrat data, spolu s popisky, a používat „Vytvořit Výběr“ příkaz na kartě Vzorce pásky:
můžete také použít klávesovou zkratku control + shift + F3.
pomocí této funkce můžeme vytvořit pojmenované rozsahy pro populaci 12 států V jednom kroku:
při klepnutí na OK se vytvoří jména., Najdete zde všechny nově vytvořené názvy v drop-down menu vedle pole název:
S názvy vytvořené, můžete je použít ve vzorcích, jako je tento,
=SUM(MN,WI,MI)Aktualizace pojmenované oblasti Správce názvů (Ctrl + F3)
po vytvoření pojmenované oblasti, použijte Jméno Správce (Ctrl + F3) aktualizovat podle potřeby. Vyberte název, se kterým chcete pracovat, změňte odkaz přímo (tj. upravit“ odkazuje na“), nebo klikněte na tlačítko vpravo a vyberte nový rozsah.,
není třeba kliknout na tlačítko Upravit pro aktualizaci odkazu. Po klepnutí na tlačítko Zavřít bude název rozsahu aktualizován.
Poznámka: Pokud v pracovním listu vyberete celý pojmenovaný rozsah, můžete přetáhnout na nové místo a odkaz bude automaticky aktualizován. Neznám však způsob, jak upravit odkazy na rozsah kliknutím a přetažením přímo na pracovní list. Pokud víte, jak to udělat, zvonit níže!
Zobrazit všechny pojmenované rozsahy (control + F3)
Chcete-li rychle zobrazit všechny pojmenované rozsahy v sešitu, použijte rozbalovací nabídku vedle pole Název.,
Pokud chcete vidět více detailů, otevřený na Jméno Správce (Ctrl + F3), který uvádí všechny názvy s odkazy, a poskytuje filtr:
Poznámka: na Mac, tam je žádné Jméno Správce, takže uvidíte v dialogovém okně Definovat Název, místo.
zkopírujte a vložte všechny pojmenované rozsahy (F3)
Pokud chcete trvalejší záznam jmenovaných rozsahů v sešitu, můžete vložit úplný seznam jmen kdekoli se vám líbí., Jít do Vzorce > Použít ve Vzorci (nebo použijte klávesovou zkratku F3), pak zvolte Vložit jména > Vložit Seznam:
Když kliknete na Vložit Seznam tlačítko, zobrazí názvy a odkazy vložené do listu:
Viz jména přímo na listu
Pokud se vám nastavit úroveň přiblížení na méně než 40%, aplikace Excel bude zobrazovat názvy přímo na listu:
Díky za tip, Felipe!,
Jména mají pravidla
Při vytváření pojmenovaných oblastí, postupujte podle těchto pravidel:
Pojmenované oblasti ve vzorcích
Pojmenované oblasti jsou snadno použitelné ve vzorcích
například, řekněme, že název buňky v sešitu „aktualizováno“. Myšlenka je, že můžete dát aktuální datum do buňky (Ctrl+;) a odkazovat na datum jinde v sešitu.
vzorce v B8 vypadá takto:
="Updated: "& TEXT(updated, "ddd, mmmm d, yyyy")můžete vložit tento vzorec kdekoli v sešitu, a to bude zobrazovat správně., Kdykoli změníte datum v „Aktualizováno“, zpráva se aktualizuje všude tam, kde je použit vzorec. Další příklady naleznete na této stránce.
pojmenované rozsahy se objeví při psaní vzorce
jakmile vytvoříte pojmenovaný rozsah, objeví se automaticky ve vzorcích při psaní prvního písmene jména. Stisknutím klávesy tab zadejte název, když máte shodu, a chcete, aby Excel zadal název.,
Pojmenované oblasti může pracovat jako konstanty
Protože pojmenovaných oblastí, které jsou vytvořeny v centrálním umístění, můžete je použít jako konstanty bez buňku. Například, můžete vytvořit názvy jako „MPG“ (míle za galon) a „CPG“ (cena za galon) a přiřazení pevné hodnoty:
Pak můžete použít tyto názvy kdekoliv se vám líbí ve vzorcích, a aktualizovat jejich hodnoty v jednom centrálním umístění.,
jmenované rozsahy jsou ve výchozím nastavení absolutní
, pojmenované rozsahy se chovají jako absolutní odkazy. Například, v tomto listu, vzorec pro výpočet palivo by mělo být:
=C5/$D$2
odkaz na D2 je absolutní (zamčené), takže vzorec lze kopírovat dolů, aniž D2 mění.,
Pokud jsme jméno D2 „MPG“ vzorec se stane:
=C5/MPG
Vzhledem k tomu MPG je absolutní ve výchozím nastavení, vzorce lze kopírovat dolů sloupec D-je.
pojmenované rozsahy mohou být také relativní
ačkoli pojmenované rozsahy jsou ve výchozím nastavení absolutní, mohou být také relativní. Relativní pojmenovaný rozsah označuje rozsah, který je vzhledem k poloze aktivní buňky v okamžiku vytvoření rozsahu. Výsledkem je, že relativní pojmenované rozsahy jsou užitečné při vytváření generických vzorců, které fungují všude tam, kde jsou přesunuty.,
například, můžete vytvořit generické „CellAbove“ ujednaném rozsahu jako toto:
- Vyberte buňky A2
- Ctrl + F3 otevřít Jméno Správce
- Karta do „znamená“ oddíl, potom zadejte: =A1
CellAbove bude nyní načíst hodnotu z buňky nad všude tam, kde je to použito.
důležité: před vytvořením názvu se ujistěte, že je aktivní buňka na správném místě.
Použít pojmenované oblasti, aby existující vzorce
Pokud máte existující vzorce, které nechcete používat pojmenované oblasti, můžete se zeptat Excel použít pojmenované oblasti ve vzorcích pro vás., Začněte výběrem buněk, které obsahují vzorce, které chcete aktualizovat. Poté spusťte vzorce > Definujte Názvy > použít jména.
Excel pak nahradí odkazy, které mají odpovídající pojmenovaný rozsah, samotným jménem.
můžete také použít jména s najít a nahradit:
Důležité: Uložte záložní kopii listu, a označte buňky, které chcete změnit dříve, než pomocí najít a nahradit na vzorce.,hlavní výhody pojmenované oblasti
Pojmenované oblasti, aby vzorce čitelnější
největší přínos pro pojmenované oblasti je, že se vzorce snazší číst a udržovat. Je to proto, že nahrazují kryptické odkazy smysluplnými jmény. Zvažte například tento list s údaji o planetách v naší sluneční soustavě., Bez pojmenovaných oblastí, VLOOKUP vzorce, aby přinesla „Pozice“ z tabulky je docela záhadné:
=VLOOKUP($H$4,$B$3:$E$11,2,0)
Nicméně, s B3:E11 s názvem „data“, a H4 s názvem „planet“, můžeme psát vzorce, jako je tento:
=VLOOKUP(planet,data,2,0) // position=VLOOKUP(planet,data,3,0) // diameter=VLOOKUP(planet,data,4,0) // satellites
Na první pohled, můžete vidět, jediný rozdíl v těchto vzorcích ve sloupci index.
pojmenované rozsahy činí vzorce přenosnými a opakovaně použitelnými
pojmenované rozsahy mohou usnadnit opětovné použití vzorce v jiném listu., Pokud předem definujete jména v listu, můžete vložit do vzorce, který používá tato jména, a bude „fungovat“. To je skvělý způsob, jak rychle získat vzorec práce.
například, tento vzorec počítá jedinečné hodnoty v rozsahu číselných údajů:
=SUM(--(FREQUENCY(data,data)>0))rychle „port“ tento vzorec do vlastního listu, název řady „data“ a vložit vzorec do listu. Dokud“ data “ obsahují číselné hodnoty, vzorec bude fungovat rovně.,
Tip: doporučuji si vytvořit potřebný rozsah jmen *první* v cílovém sešitu, zkopírujte vzorec jako pouze text (tj. ne kopírovat buňky, která obsahuje vzorec, v jiném listu, stačí zkopírovat text, vzorce). To zastaví Excel od vytváření jmen on-the-fly a umožňuje plně ovládat proces vytváření jmen. Chcete-li kopírovat pouze text vzorce, zkopírujte text z lišty vzorců nebo zkopírujte pomocí jiné aplikace (tj.).
pojmenované rozsahy lze použít pro navigaci
jmenované rozsahy jsou skvělé pro rychlou navigaci., Stačí vybrat rozbalovací nabídku vedle pole název a vybrat jméno. Po uvolnění myši bude vybrán rozsah. Pokud existuje jmenovaný rozsah na jiném listu, budete automaticky přesměrováni na tento list.
pojmenované rozsahy fungují dobře s hypertextovými odkazy
pojmenované rozsahy usnadňují hypertextové odkazy. Například, pokud pojmenujete A1 v Sheet1 „home“, můžete vytvořit hypertextový odkaz někde jinde, který vás vezme zpět.,
Chcete-li použít pojmenovanou oblast uvnitř hypertextového ODKAZU funkce, přidat libru symbol před jménem rozsah:
=HYPERLINK("#home","take me home")Poznámka: je zvláštní, nemůžeš hypertextový odkaz na tabulku jako můžete normálním rozsahu jméno. Můžete však definovat název rovnající se tabulce (tj. =Table1) a hypertextový odkaz na to. Pokud někdo zná způsob, jak propojit přímo tabulku,zvonit!,
pojmenované rozsahy pro validaci dat
rozsahy jmen fungují dobře pro validaci dat, protože vám umožňují použít logicky pojmenovaný odkaz k ověření vstupu pomocí rozbalovací nabídky. Pod rozsah G4:G8 se jménem „statuslist“, pak použít ověření dat Seznam spojeny, jako je tento:
výsledkem je rozbalovací menu, ve sloupci E, že umožňuje pouze hodnoty v ujednaném rozsahu:
Dynamické Pojmenované oblasti
Jména rozsahy jsou velmi užitečné, když se automaticky nastaví tak, aby nová data v listu., Rozsah nastavený tímto způsobem je označován jako „dynamický rozsah“. Existují dva způsoby, jak vytvořit dynamický rozsah: vzorce a tabulky.
dynamický pojmenovaný rozsah s tabulkou
tabulka je nejjednodušší způsob, jak vytvořit dynamický pojmenovaný rozsah. Vyberte libovolnou buňku v datech a poté použijte klávesovou zkratku Control + t:
při vytváření tabulky Excel se automaticky vytvoří název (např. Jakmile vytvoříte tabulku, automaticky se rozbalí po přidání dat.,
dynamický pojmenovaný rozsah se vzorcem
můžete také vytvořit dynamický pojmenovaný rozsah se vzorci pomocí funkcí, jako je OFFSET a INDEX. Přestože jsou tyto vzorce mírně složité, poskytují lehké řešení, když nechcete používat tabulku., Níže uvedené odkazy poskytují příklady s plnou vysvětlení:
- Příklad dynamického rozsahu vzorce s INDEXEM
- Příklad dynamického rozsahu vzorec s ODSAZENÍ
názvy tabulek v ověření dat
Vzhledem k tomu, Excel Tabulky poskytují automatický dynamický rozsah, se zdá být přirozeným pro pravidla ověření dat, kde cílem je ověřit proti seznamu, který může být neustále mění. Jedním z problémů s tabulkami je však to, že nemůžete použít strukturované odkazy přímo k vytvoření validace dat nebo podmíněných pravidel formátování., Jinými slovy, nelze použít název tabulky v podmíněné formátování nebo validace dat vstupních oblastech.
jako řešení však můžete definovat pojmenovaný rozsah, který ukazuje na tabulku, a poté použít pojmenovaný rozsah pro validaci dat nebo podmíněné formátování. Níže uvedené video podrobně prochází tímto přístupem.
Video: Jak používat pojmenované oblasti s tabulkami
Odstranění pojmenovaných oblastí
Poznámka: Pokud máte vzorce, které odkazují na pojmenované oblasti, možná budete chtít aktualizovat vzorce první před odstraněním jména. Jinak uvidíte #jméno?, chyby ve vzorcích, které stále odkazují na smazané názvy. Před odstraněním jmenovaných rozsahů vždy uložte svůj pracovní list, pokud máte problémy a potřebujete se vrátit k originálu.
Pojmenované rozsahy nastavení při mazání a vkládání buněk
Když odstraníte *část* pojmenované oblasti, nebo-li vložit buňky/řádky/sloupce uvnitř pojmenovaný rozsah, referenční rozmezí upraví odpovídajícím způsobem, a zůstávají v platnosti. Pokud však odstraníte všechny buňky, které uzavřou pojmenovaný rozsah, pojmenovaný rozsah ztratí odkaz a zobrazí chybu #REF., Například, pokud jsem název A1 „test“, pak odstranit sloupci název správce se zobrazí „odkazuje na“ jako:
=Sheet1!#REF!Odstranit jména s Názvem Manager
odstranit pojmenované oblasti ze sešitu ručně, otevřete správce názvů, vyberte oblast a klepněte na tlačítko Odstranit. Pokud chcete odstranit více než jedno jméno současně, můžete posunout + kliknout nebo Ctrl + kliknout a vybrat více jmen, poté odstranit V jednom kroku.,
Odstranit jména s chyb
Pokud máte spoustu jmen s odkazem chyby, můžete použít tlačítko filtr na jméno správce filtr na názvy s chybami:
Pak shift+klepněte pro výběr všech jmen a odstranit.
pojmenované rozsahy a rozsah
pojmenované rozsahy v Excelu mají něco, co se nazývá „rozsah“, což určuje, zda je pojmenovaný rozsah Lokální pro daný pracovní list nebo globální v celém sešitu. Globální názvy mají rozsah „sešitu“ a místní názvy mají rozsah rovnající se názvu listu, na kterém existují., Například prostor pro místní název může být „Sheet2“.
účel rozsahu
pojmenované rozsahy s globálním rozsahem jsou užitečné, pokud chcete, aby všechny listy v sešitu měly přístup k určitým datům, proměnným nebo konstantám. Můžete například použít globální pojmenovaný rozsah předpoklad daňové sazby používaný v několika pracovních listech.
Local scope
Local scope znamená, že název je funkční pouze na listu, na kterém byl vytvořen. To znamená, že můžete mít více listů ve stejném sešitu, které všechny používají stejný název., Například, možná, že máte sešit s měsíční sledování povlečení (jednou za měsíc), které používají pojmenované oblasti se stejným názvem, všechny rozsahem lokálně. To vám může umožnit znovu použít stejné vzorce v různých listech. Místní rozsah umožňuje, aby názvy v každém listu fungovaly správně, aniž by se srazily se jmény v ostatních listech.
odkazovat Na jméno s místní působnosti, můžete prefix list název rozsah název:
Sheet1!total_revenueSheet2!total_revenueSheet3!total_revenueRozsah jmen vytvořil se pole název automaticky mít globální rozsah., Na toto chování potlačit, přidejte název listu při definování jméno:
Sheet3!my_new_nameGlobální rozsah
Globální působnosti se rozumí název, bude pracovat kdekoli v sešitu. Můžete například pojmenovat buňku „last_update“, zadat datum v buňce. Poté můžete pomocí níže uvedeného vzorce zobrazit datum poslední aktualizace v libovolném listu.
=last_updateglobální názvy musí být v sešitu jedinečné.
Local scope
Lokálně scoped pojmenované rozsahy mají smysl pro pracovní listy, které používají pojmenované rozsahy pouze pro místní předpoklady., Například, možná, že máte sešit s měsíční sledování povlečení (jednou za měsíc), které používají pojmenované oblasti se stejným názvem, všechny rozsahem lokálně. Místní rozsah umožňuje, aby názvy v každém listu fungovaly správně, aniž by se srazily se jmény v ostatních listech.
Správa pojmenovaného rozsahu rozsah
ve výchozím nastavení jsou nová jména vytvořená s názvem namebox globální a po jeho vytvoření nemůžete upravit rozsah pojmenovaného rozsahu. Jako řešení však můžete odstranit a znovu vytvořit název s požadovaným rozsahem.,
Pokud chcete změnit několik jmen najednou z globálního na místní, někdy má smysl kopírovat list, který obsahuje jména. Když duplikujete list, který obsahuje pojmenované rozsahy, Excel zkopíruje pojmenované rozsahy do druhého listu a současně změní rozsah na místní. Poté, co máte druhý list s lokálně prověřenými názvy, můžete volitelně odstranit první list.
Jan Karel Pieterse a Charles Williams vyvinuli nástroj s názvem Správce názvů, který poskytuje mnoho užitečných operací pro pojmenované oblasti. Nástroj Správce jmen si můžete stáhnout zde.,
