Ako vytvoriť tabuľku a pomenovať ju v programe Excel

V tomto článku sa naučíme, ako vytvoriť tabuľku a pomenovať ju v programe Excel.

Scenár :

Pri práci na Exceli ste už určite počuli o pomenovaných rozsahoch v Exceli. Možno od priateľa, kolegu alebo z nejakého online tutoriálu. Mnohokrát som to spomenul vo svojich článkoch. V tomto článku sa dozvieme o pomenovanom rozsahu v programe Excel a preskúmame jeho všetky aspekty.

Čo sú pomenované tabuľky v programe Excel?

Pomenované rozsahy nie sú nič iné ako niektoré rozsahy programu Excel, ktoré majú nejaký zmysluplný názov. Ak máte napríklad bunku, povedzme B1, obsahujúcu každodenné ciele, môžete túto bunku pomenovať konkrétne ako „Cieľ“. Teraz môžete použiť „Cieľ“ na odkaz na A1 namiesto písania B1.

Stručne povedané, pomenovaný rozsah je iba pomenovanie rozsahov.

Ako pomenovať rozsah v programe Excel?

Manuálne definujte meno:

Na definovanie názvu pre rozsah môžete použiť skratku CTRL + F3. Alebo môžete postupovať podľa týchto krokov.

Ísť do Vzorec Tab
Vyhľadajte časť Definované mená a kliknite na položku Definovať mená. Tým sa otvorí Meno Manger.
Kliknite na Nový.
Napíšte Meno.
Vyberte rozsah (zošit alebo list)
Ak chceš, napíš komentár.
Do poľa Odkazuje na napíšte referenciu alebo vyberte rozsah pomocou myši.
Kliknite na OK. Hotovo.

Teraz sa na neho môžete odvolať zadaním jeho názvu.

Pri vytváraní mien je potrebné dodržiavať niekoľko pravidiel. Tu sú nejaké.

Názvy by nemali začínať inými číslicami alebo špeciálnymi znakmi ako podčiarkovník (_) a spätné lomítko (\).
Mená nemôžu obsahovať medzery a žiadne špeciálne znaky okrem _ a \.
Rozsah by nemal byť pomenovaný ako odkazy na bunky. Napríklad A1, B1 alebo AZ100 atď.
Rozsah nemôžete pomenovať ako „r“ a „c“, pretože sú vyhradené pre odkazy na riadky a stĺpce.
Dva pomenované rozsahy nemôžu mať v zošite rovnaký názov.
Rovnaký rozsah môže mať viacero mien.

Automaticky definujte meno

Väčšinu času budete pracovať s tabuľkami štruktúrovaných dát. Budú mať stĺpce a riadky s nadpismi stĺpcov a nadpismi riadkov. A väčšinou tieto názvy majú pre údaje význam a vy by ste chceli svoj rozsah pomenovať ako tieto nadpisy stĺpcov. Excel poskytuje nástroj na automatické pomenovanie rozsahov pomocou nadpisov. Nasleduj tieto kroky.

Rozsahy, ktoré chcete pomenovať ako nadpisy
Stlačte CTRL+SHIFT+F3, alebo vyhľadajte sekciu Definované mená na karte Vzorec a kliknite na položku Vytvoriť z výberu.
Zobrazí sa nižšie uvedené pole s možnosťami. Vybral som iba horný riadok, pretože chcem pomenovať tento rozsah ako nadpis a nechcem pomenovať riadky.
Kliknite na tlačidlo OK.

Teraz je každý stĺpec pomenovaný ako nadpis. Kedykoľvek zadáte vzorec, tieto názvy budú uvedené ako možnosti, ku ktorým je prístup.

Pomenovanie rozsahu pomocou tabuliek programu Excel

Keď tablise údaje v programe Excel pomocou CTRL + T, hlavička stĺpca je automaticky priradená ako názov príslušného stĺpca. Mali by ste preskúmať tabuľky programu Excel a ich výhody.

Ako zobraziť všetky pomenované rozsahy?

Nastanú chvíle, kedy by ste chceli v zošite vidieť všetky dostupné pomenované rozsahy. Ak chcete zobraziť všetky rozsahy mien, stlačte kombináciu klávesov CTRL+F3. Alebo môžete ísť na Tabuľka vzorcov > Správca mien. Zobrazí sa zoznam všetkých pomenovaných rozsahov, ktoré sú k dispozícii v zošite. Dostupné dostupné pomenované rozsahy môžete upravovať, odstraňovať a pridávať nové názvy.

Jeden rozsah viac mien

Excel umožňuje používateľom pomenovať rovnaký rozsah rôznymi názvami. Napríklad rozsah A2: A10 je možné pomenovať „zákazníci“ a „klienti“ súčasne. Oba názvy budú odkazovať na rovnaký rozsah A2: A10.

Ale nemôžete mať rovnaké názvy pre dva rôzne rozsahy. Toto je dobré. Tým sa eliminuje šanca na nejednoznačnosť.

Získajte zoznam pomenovaných rozsahov na hárku

Ak teda chcete mať zoznam pomenovaných rozsahov a rozsahov, ktoré pokrývajú, môžete použiť túto skratku na ich vloženie na miesto v hárku.

Vyberte bunku, do ktorej chcete získať zoznam pomenovaných rozsahov.
Stlačte kláves F3. Tým sa otvorí a prilepiť zoznam dialógové okno.
Kliknite na prilepiť zoznam tlačidlo.
Zoznam bude prilepený do vybraných buniek a ďalej.

Ak dvakrát kliknete na názov pomenovaného rozsahu v poli pre vloženie názvu, zapíšu sa do bunky ako vzorce. Skús to.

Aktualizujte pomenované rozsahy ručne

Keď vložíte bunku do pomenovaného rozsahu, automaticky sa aktualizuje a rozšíri. Ak však na koniec tabuľky pridáte údaje, budete musieť pomenovaný rozsah aktualizovať. Ak chcete aktualizovať pomenované rozsahy, postupujte podľa týchto krokov.

Stlačte CTRL+F3 otvoriť správca mien.
Kliknite na pomenovaný rozsah, ktorý chcete upraviť. Kliknite na Upraviť.
Do stĺpca Odkazuje na stĺpec zadajte rozsah, do ktorého chcete rozšíriť, a kliknite na tlačidlo OK.

A je hotovo. Toto je manuálna aktualizácia pomenovaných rozsahov. Môžeme to však urobiť dynamickým pomocou niektorých vzorcov.

Aktualizovať pomenované rozsahy dynamicky

Je múdre, aby boli vaše pomenované rozsahy dynamické, aby ste ich nemuseli upravovať vždy, keď vaše údaje pretečú preddefinovaným rozsahom.

Pokryl som to v samostatnom článku s názvom Dynamické pomenované rozsahy. Tu sa môžete podrobne naučiť a porozumieť jeho výhodám.

Odstránenie pomenovaných rozsahov

Keď odstránite súčtovú časť pomenovaného rozsahu, rozsah sa automaticky upraví. Keď však vymažete, celý rozsah mien zmizne zo zoznamu mien. Akýkoľvek vzorec v závislosti od týchto rozsahov zobrazí chybu #REF alebo poskytne nesprávny výstup (funkcie počítania).

Ak chcete z akéhokoľvek dôvodu odstrániť pomenované rozsahy, postupujte podľa týchto krokov.

Stlačte kombináciu klávesov CTRL+F3. Otvorí sa správca mien.
Vyberte Pomenované rozsahy, ktoré chcete odstrániť.
Kliknite na tlačidlo Odstrániť alebo kliknite na tlačidlo Odstrániť na klávesnici.

Pozor: Pred odstránením pomenovaných rozsahov sa uistite, že na týchto názvoch nie sú závislé žiadne vzorce. Ak nejaké existujú, najskôr ich preveďte na rozsahy. V opačnom prípade sa zobrazí chyba #REF.

Vymazanie mien s chybami

Excel poskytuje nástroje na odstránenie iba tých názvov, ktoré obsahujú chyby. Nie je potrebné identifikovať každého z nich sami. Ak chcete odstrániť mená s chybami, postupujte takto:

Otvorte Správcu mien (CTRL+F3).
Kliknite na rozbaľovaciu ponuku Filter v pravom hornom rohu.
Vyberte „Meno s chybami“
Vyberte všetko a kliknite na tlačidlo Odstrániť.

A sú preč. Všetky mená s chybami budú ihneď vymazané zo záznamu. Pomenované rozsahy pomocou vzorcov

Najlepšie použitie pomenovaných rozsahov sa skúma pomocou vzorcov. Vzorce sú s pomenovanými rozsahmi skutočne flexibilné a čitateľné. Pozrime sa ako.

Jednoduché písanie vzorcov Teraz povedzme, že ste rozsah pomenovali ako „položky“. Teraz je zoznam položiek, ktoré chcete počítať, „ceruzky“. S menami je ľahké napísať tento vzorec COUNTIF. Stačí napísať

= COUNTIF (Položka„Ceruzka“)
Hneď ako napíšete úvodnú zátvorku vzorca, zobrazí sa zoznam dostupných pomenovaných rozsahov

Bez názvu by ste napísali príkaz na zadanie rozsahu pre funkciu COUNTIF programu Excel, pre ktorú sa budete musieť najskôr pozrieť na rozsah a potom ho vybrať alebo napísať do vzorca. Excel slúži na dostupné rozsahy názvov.

Pomenované rozsahy sa zobrazia ako návrhy pri zadávaní akéhokoľvek písmena. Ako ukazuje excel zoznam vzorcov. Ak napríklad zadáte = u, každý vzorec a pomenovaný rozsah sa zobrazí počnúc písmenom u, aby ste ich mohli ľahko použiť.

Vykonávajte konštanty pomocou pomenovaných rozsahov

Doteraz sme sa dozvedeli o pomenovaní rozsahov, ale v skutočnosti môžete pomenovať aj hodnoty. Ak je napríklad vaše klientske meno Sunder Pichai, môžete vytvoriť meno „klient“ a bude odkazovať na zápis „Sundar Pichai“. Teraz, kedykoľvek budete písať = Klient v ľubovoľnej bunke, zobrazí sa Sundar Pichai.

Nielen text, ale tiež môžete priradiť čísla ako konštanty k práci. Napríklad definujete cieľ. Alebo hodnota niečoho, čo sa nezmení.

Absolútne a relatívne referencie s pomenovanými rozsahmi

Referencie s pomenovanými rozsahmi v sú veľmi flexibilné. Ak napríklad napíšete názov pomenovaného rozsahu do relatívnej bunky k pomenovanému rozsahu, bude sa správať ako relatívna referencia. Viď obrázok nižšie.

Ale keď ho použijete so vzorcami, bude sa správať ako absolútny. Väčšinu času ich budete používať so vzorcami, takže Môžete povedať, že sú predvolene absolútne ale v skutočnosti sú flexibilné.

Ale môžeme ich tiež urobiť relatívnymi.

Ako vytvoriť relatívne pomenované rozsahy v programe Excel?

Povedzme, že ak chcem pomenovať rozsah „Befor“, ktorý bude odkazovať na bunku ponechanú kdekoľvek, kde je napísaná. Ako to urobím? Nasleduj tieto kroky:

Stlačte kombináciu klávesov CTRL+F3
Kliknite na Nový
Do sekcie „Názov“ zadajte „Befor“.
V časti „Odkazuje na:“ napíšte adresu bunky vľavo. Ak ste napríklad v bunke B1, napíšte „= A2“ do sekcie „Odkazuje na:“. Uistite sa, že neobsahuje znak $.

Teraz, kdekoľvek vo vzorci napíšete „predtým“, bude to odkazovať na bunku, ktorá je mu ponechaná.

Tu som to predtým použil vo funkcii COLUMN. Vzorec vráti číslo stĺpca ľavej bunky, kde je napísaný. Na moje prekvapenie, A1 zobrazuje číslo stĺpca posledného stĺpca. To znamená, že list je v obehu. Myslel som si, že sa zobrazí chyba #REF.

Pomenujete často používané vzorce?

Teraz je tento úžasný. V pracovnom hárku mnohokrát použijete rovnaký vzorec znova a znova. Môžete napríklad chcieť skontrolovať, či je meno vo vašom zozname zákazníkov alebo nie. A táto potreba sa môže vyskytnúť mnohokrát. Za týmto účelom napíšete zakaždým rovnaký komplexný vzorec.

= IF (COUNTIF (Zákazník, I3), „V zozname“, „Nie je v zozname“)

Čo keby ste do bunky zadali „= IsInCustomer“ a zobrazilo by sa vám, či je hodnota v ľavej bunke v zozname zákazníkov alebo nie?

Tu som napríklad pripravil stôl. Teraz chcem iba napísať „= IsInCustomer“ do J5 a chcel by som zistiť, či je hodnota v I5 je v zozname zákazníkov alebo nie. Postupujte podľa týchto krokov.

Stlačte kombináciu klávesov CTRL+F3
Kliknite na Nový
Do poľa Name napíšte „IsInCustomer“

Do poľa „Vzťahuje sa na“ napíšte svoj vzorec. = IF (COUNTIF (Zákazník(I5), „V zozname“, „Nie je v zozname“)

Kliknite na tlačidlo OK.

Teraz, kdekoľvek zadáte „IsInCustomer“, skontroluje hodnotu v ľavej bunke v zozname zákazníkov.

To vám zabráni opakovať sa znova a znova.

Aplikujte pomenované rozsahy na vzorce

Mnohokrát definujeme názvy pre naše rozsahy potom, čo sme už napísali vzorce na základe rozsahov. Mám napríklad celkovú cenu ako bunky = E2*F2. Ako to môžeme zmeniť? Jednotky*Unit_Cost.

Vyberte vzorce.
Prejdite na kartu vzorcov. Kliknite na rozbaľovaciu ponuku Definovať meno.
Kliknite na položku Použiť mená.
Zobrazí sa zoznam všetkých pomenovaných rozsahov. Vyberte správne mená a kliknite na tlačidlo OK.

A názvy sú teraz použité. Môžete to vidieť na paneli vzorcov.

Ľahko čitateľné vzorce s pomenovanými rozsahmi

Ako ste videli, pomenované rozsahy uľahčujú čítanie vzorcov. Ak napíšem = COUNTIF („A2: A100“, B2), nikto nepochopí, čo sa pokúšam spočítať, kým neuvidí údaje alebo im to niekto nevysvetlí.

Ale ak napíšem = COUNTIF (región, „východ“), väčšina používateľov okamžite pochopí, že počítame výskyty „východu“ v regióne s názvom rozsah.

Prenosné vzorce

Pomenované rozsahy uľahčujú kopírovanie a vkladanie vzorcov bez starostí so zmenou referencií. Môžete tiež prevziať jeden vzorec z jedného zošita do druhého a bude fungovať, kým a pokiaľ nebude mať cieľový zošit rovnaký názov.

Napríklad, ak máte vzorec = COUNTIF (región, východ) v Distribučnej tabuľke a máte ďalší zošit zákazníkov ktorý má tiež pomenovaný rozsah „Región“. Teraz, keď skopírujete tento vzorec priamo kdekoľvek v tomto zošite, zobrazí sa vám správne informácie. Na štruktúre dát nezáleží. Nezáleží na tom, kde je sakra ten stĺpček vo vašom zošite. Bude to fungovať správne.

Na vyššie uvedenom obrázku som použil presne ten istý vzorec v dvoch rôznych súboroch na spočítanie počtu alebo východov vyskytujúcich sa v zozname regiónov. Teraz sú v rôznych stĺpcoch, ale pretože obidva sú pomenované ako regióny, bude to fungovať perfektne.

Jednoduchá navigácia v zošite

Navigácia v zošite s pomenovanými rozsahmi je teraz jednoduchšia. Stačí zadať názov do poľa s názvom. Program Excel vás prevedie rozsahom bez ohľadu na to, kde sa v zošite nachádzate. Vzhľadom na to, že pomenovaný rozsah je v rozsahu zošita.

Ak ste napríklad na hárku 10 a chcete získať zoznam zákazníkov, a neviete, na ktorom hárku sa nachádza. Choďte do poľa s názvom a zadajte „zákazník“. V zlomku sekundy budete presmerovaní na pomenovaný rozsah.

Zníži to námahu zapamätať si rozsahy.

Navigujte pomocou hypertextových odkazov s pomenovaným rozsahom

Keď je váš hárok veľký a často idete z jedného bodu do druhého, radi sa v ňom ľahko pohybujete pomocou hypertextových odkazov. Dobre pomenované rozsahy môžu perfektne fungovať s hypertextovými odkazmi. Ak chcete pridať hypertextové odkazy pomocou pomenovaných rozsahov, postupujte podľa týchto krokov.

Vyberte bunku, kde chcete hypertextový odkaz
Stlačte CTRL+K alebo prejdite na kartu Vložiť> HyperLink a otvorte dialógové okno Vložiť hypertextový odkaz.
Kliknite na položku Miesto v tomto dokumente.
Posuňte sa nadol a v zozname definovaných mien zobrazte dostupné pomenované rozsahy
Vyberte Pomenovaný rozsah, do ktorého chcete vložiť hypertextový odkaz.

A je hotovo. Máte hypertextový odkaz na vami zvolený pomenovaný rozsah. Pomocou toho môžete vytvoriť register pomenovaných rozsahov, ktoré môžete vidieť a kliknúť na ne, aby ste sa k nim priamo dostali. Vďaka tomu bude váš zošit skutočne užívateľsky prívetivý.

Pomenovaný rozsah a validácia údajov

Pomenované rozsahy a validácia údajov sú ako stvorené pre seba. Pomenované rozsahy robia validáciu údajov vysoko prispôsobiteľnou. Pridanie overenia zo zoznamu pomocou pomenovaného rozsahu je oveľa jednoduchšie. Pozrime sa, ako…

Prejdite na kartu Údaje
Kliknite na Overenie údajov
Vyberte položku Zoznam v časti „Povoliť:“
V časti „Zdroj:“ zadajte „= zákazník“ (napíšte ľubovoľný pomenovaný rozsah)
Kliknite na OK

Teraz bude mať táto bunka mená zákazníkov, ktorí sú súčasťou rozsahu s názvom Zákazník. Ľahké, nie?

Závislé alebo kaskádové overenie údajov s pomenovanými rozsahmi

Teraz čo keď chcete kaskádové alebo závislé overenie údajov. Ak napríklad chcete rozbaľovací zoznam s kategóriami, ovocie a zelenina. Ak teraz vyberiete ovocie, v ďalšej rozbaľovacej ponuke by sa mala zobraziť iba možnosť ovocia a ak zvolíte zeleninu, potom iba zeleninu.

To sa dá ľahko dosiahnuť pomocou pomenovaných rozsahov. Zistite ako.

Závislá rozbaľovacia ponuka pomocou pomenovaného rozsahu
Iné spôsoby kaskádovej validácie údajov

Žiadne overenie údajov s názvami tabuľkových údajov

Tabuľky programu Excel síce poskytujú štruktúrované názvy, ale nemožno ich použiť na validáciu údajov a podmienené formátovanie. Neviem, prečo to Excel neumožňuje.

To však neznamená, že sa to nedá. Rozsahy môžete pomenovať v tabuľke a potom ich použiť na overenie. Excel s ním nemá žiadny problém.

Rozsah pomenovaných rozsahov

Doteraz sme hovorili o pomenovaných rozsahoch, ktoré mali rozsah zošita. Čo? Nediskutovali sme o tom? Dobre, poďme rýchlo pochopiť, čo je rozsah pomenovaných rozsahov.

Aký je rozsah rozsahu mien?

Rozsah dobre definuje, kde je možné rozpoznať rozsah názvov. Akékoľvek meno nemôže byť rozpoznané mimo jeho rozsah. Napríklad meno v zošite 1 nemožno rozpoznať v rôznych zošitoch. Excel ponúka dve možnosti pre rozsah pomenovaných rozsahov hárok a zošit.

Ako definovať rozsah pomenovaného rozsahu?

Keď vytvoríte nový rozsah názvov, zobrazí sa sekcia „Rozsah:“. Kliknite na rozbaľovaciu ponuku a vyberte rozsah rozsahu svojich mien. Po vytvorení pomenovaného rozsahu nemôžete rozsah zmeniť. Preto je lepšie to urobiť predtým. Štandardne je to pracovný zošit.

Dúfam, že tento článok o tom, ako vytvoriť tabuľku a pomenovať ju v programe Excel, je vysvetľujúci. Tu nájdete ďalšie články o výpočte hodnôt a súvisiacich vzorcoch programu Excel. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľmi na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku.

Ako používať skratku na prepínanie medzi absolútnymi a relatívnymi referenciami v programe Excel : F4 skratka na konverziu absolútnej hodnoty na relatívnu referenciu a rovnaké skratky sa používajú aj naopak v programe Excel.

Ako používať klávesové skratky na zlúčenie a zarovnanie na stred v programe Excel : Použite Alt a potom nasleduj h, m a c na zlúčenie a vycentrovanie buniek v programe Excel.

Ako vybrať celý stĺpec a riadok pomocou klávesových skratiek v programe Excel : Použiť Ctrl + Vesmír vyberte celý stĺpec a Smena + Vesmír vyberte celý riadok pomocou klávesovej skratky v programe Excel

Prilepte špeciálnu skratku pre Mac a Windows : V oknách klávesová skratka pre vložiť špeciálne je Ctrl + Alt + V.. Zatiaľ čo v systéme Mac používajte Ctrl + PRÍKAZ + V. kombináciu klávesov na otvorenie súboru vložiť špeciálne dialóg v programe Excel.

Ako vložiť skratku riadkov do programu Excel : Použite Ctrl + Smena + = otvoriť Vložiť dialógové okno, do ktorého môžete vložiť riadok, stĺpec alebo bunky v programe Excel.

Populárne články:

Ako používať funkciu IF v programe Excel : Príkaz IF v programe Excel skontroluje podmienku a vráti konkrétnu hodnotu, ak je podmienka PRAVDA, alebo vráti inú konkrétnu hodnotu, ak je NEPRAVDA.

Ako používať funkciu VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.

Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.

Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.