Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » funkce » dynamicke-jmeno-oblasti-ms-excel

Definovaný název dynamické oblasti - Excel

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak pojmenovat (definovat název) dynamicky měněné oblasti v Excelu? Oblast která se zvětšuje (zmenšuje). Jak efektivně toto pojmenování provést, je sepsáno a na praktických příkladech předvedeno v tomto článku.

Úvodem do dynamicky pojmenované oblasti (definovaných názvů)

Základy o definované oblasti. Je v článku: Definované názvy - Pojmenováni oblasti základy.

V Excelu můžete určitou oblast pojmenovat (buňku, oblast, řádek, sloupec). Což přináší při práci v Excelu obrovské výhody. Problém nastane, pokud se tato oblast dynamicky mění (uživatele přidávají data, načítáte soubor, který má různý počet řádku), jak tedy na takovouto oblast? Nic jednoduššího než použít dynamickou oblast. Nebudete přece při každé změně měnit již jednou pojmenovanou oblast.

Cílem článku je ukázat jak jednou oblast nastavit a její velikost se automaticky změní, na základě skutečné velikosti této oblasti.

Využití dynamicky pojmenované oblasti

Využijete při tvorbě grafů, kdy nevíte přesný počet záznamů, tvorba seznamů, automatické ověřování zadávaných dat, atd.

První ukázka

Představte si, prodávate rozné věcí, které můžete rozdělit do sekcí:

  • Auto
  • Kolo
  • Moto
  • Kolečkové brusle

Předpokládáte, že do budoucna třeba se rozšíří druhy o lodě, kamiony, karavany, stany. Takže máte připravenou takovouto tabulku:

Prodávané druhy
Auto
Kolo
Moto
Kolečkové brusle
MS Excel 2010 - tabulka

První ukázka

Seznam pojmenujte například ProdavaneDruhy

V nabídce Vzorce v sekci Definované názvy ikona Definovat název.

MS Excel 2010 - karta vzorce -definované názvy

Obdržíte okno.

MS Excel 2010 - definované názvy

Do textového pole Název zadejte ProdavaneDruhy a do textového pole Odkaz na zadejte tento vzorec:

=POSUN($A$2;0;0;POČET2($A$2:$A$200);1)

Pokud máte vše hotovo, stačí klik na OK a dynamicky definovaná oblast je hotova.

Poznámka: Podrobnější popis vzorců (funkcí) POSUN, POČET, POČET2 jsem sepsal v článcích o vzorcích (funkcích).

Použití dynamicky pojmenované oblasti

Jako ukázku použití naši pojmenované oblast zvolíme v buňce E3 ověření. Podrobnější popis jak funguje Ověření dat v Excelu. Tj. na kartě Data sekce Datové nástroje ikona Ověření dat. V okně:

MS Excel 2010 - ověření dat

Vyplníte pro ověřovací kritérium povolit: Seznam a pro zdroj zadáte =ProdavaneDruhy - odkaz na vámi vytvořenou dynamicky pojmenovanou oblast. A potvrdíte OK. Vyplňování zpráv při zadávání a chybových hlášení není cílem tohoto článku.

Seznam

Klikem na buňku E3 se rozbalí seznam možných hodnot, které lze vložit do buňky (odpovídá vámi zadaným datům)

MS Excel 2010 - seznam

Stačí do tabulky doplnit:

Zubačka
Pěškobus

Dynamická oblast se zvětší a seznam bude větší:

MS Excel 2010 - seznam

Závěrem

Podobně lze dynamicky definovanou oblast využít při tvorbě grafů atd. Používáte dynamicky definované oblasti nebo tuto záležitost řešíte v projektech jinak?

Článek byl aktualizován: 19.09.2020 10:57

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře


Radek

Přidáno: 21.07.12 11:27

Proč se dává ten vzorec na počet, posun? Pokud si zvolím oblast už předtím, tak jakýmkoli dopsáním se ověřeí dat doplní

Pavel Lasák

Přidáno: 21.07.12 20:31

To Radek: Ta oblast ohraničuje oblast dat, která není předem známa (tak abych neměl "prazná pole" pokud ji vytvořím větší). Hojně využívám například pro automatickou tvorbu grafu, kdy načítám naměřená data a předem nevím jejich počet Takto se tato oblast automaticky zvětšuje podle vyplněných buněk.

Libor

Přidáno: 05.09.13 08:28

Dobrý den, používám dynamické seznamy dle Vašeho návodu, vše funkční, bez problémů. Bohužel totéž není funkční u vnořeného seznamu (=NEPŘÍMÝ.ODKAZ(XN)). Vnořený seznam funguje, ale po změně "statického" seznamu na dynamický už ne. Prosím o radu, děkuji.

Pavel Lasák

Přidáno: 05.09.13 21:12

To Libor: Co má být cílem v požadovaném řešení? Třeba se nemusí řešit přes NEPŘÍMY.ODKAZ.

Libor

Přidáno: 06.09.13 13:27

V prvním seznamu jsou střediska, v dalších jména. Při výběru konkrétního střediska se zobrazí v druhém seznamu jména pouze pro dané středisko. Jména se průběžně mění ( i počet ). Jsou-li seznamy statické ( což dnes jsou ), je nutné při každé změně ručně změnit rozsah daného seznamu. Jmen je celkem ve všech seznamech 50, proto nevyhovuje jeden jmenný seznam, který by problém vyřešil. Šlo by zřejmě vyřešit pomocí KDYŽ, ale při větším počtu středisek to není přiliš elegantní.

Libor

Přidáno: 09.09.13 11:45

dodatek k textu z 6.9. Řešení k popsanému problému bez programování, pracné, ale funkční : Vytvořit dynamický seznam s dostatečným rozsahem, z tohoto seznamu Kontingenční tabulku se sortováním od A-Z a rozsahem dat "-1", aby se v nabídce prvního seznamu poté nezobrazovala hodnota "prázdné". V prvním rozevíracím seznamu definovat pomocí fce KDYŽ odkazy na KT : =KDYŽ(A1="alfa";KTalfa;KDYŽ(A1="beta";KTbeta;KDYŽ ... Určitě by se ale našlo řešení jednodušší ... ?

Pavel Lasák

Přidáno: 09.09.13 16:56

To Libor: Děkuji za nástin možného řešení, zkusím popřemýšlet o nějakém elegantnějším.

Tonda

Přidáno: 25.09.13 16:25

Diky tento navod jsem potreboval.

Migi

Přidáno: 27.12.13 17:43

Pridavam se k Liborovi, kdyz je to staticky, tak seznamy pracuji korektne, ale pri dynamickem to hazi chybu, skloubit obe reseni by bylo super. Aby clovek mohl pridavat promenne dle libosti a zaroven aby nemusel zvetsovat oblast. Jedno reseni ja bych mel, neni tak ciste, ale da se pouzit, kdyz je nutna rezerva, tzn. sice pouzit staticke reseni, ale dat si oblast o trosku vetsi, nevyhodou je, ze "prazdne" bunky jsou ve vybiracim menu, coz to trochu zneprehlednuje. Diky za pomoc.

Pavel Lasák

Přidáno: 29.12.13 10:28

To Migi: Dynamicky funguje oblast s použitím vzorce =POSUN($A$2;0;0;POČET2($A$2:$A$200);1) jak je popsáno výše (pokud je více jak 200 položek lze zvětšit číslo ve vzorci třeba na 2000).
Hodně záleží co člověk od seznamu čeká a jak vkládá (maže) příslušné buňky

Migi

Přidáno: 29.12.13 15:51

jo to je v pohode, ale kdyz pak chci pouzit tenhle dymamicky seznam pres overovani dat (seznam), tak pri volbe neprimy.odkaz(treba A2) a v A2 bude Strediska, kde bude dalsi oblast s nazvem strediska a budou tam podstrediska, tak tohle nefunguje, ale to jiz psal Libor v prispevku vyse ;) Proto skloubeni obou funkcionalit by bylo super, seznam by byl prehledny a zamezi se zapsani chybu uzivatelem. Ale to reseni ja osobne neznam, proto jsem napsal prispevek :)

Radek

Přidáno: 30.01.14 19:25

narazil jsem na problém, mám seznam zákazníků pojmenovaný dynamicky, na jiném listě ho volám přez seznam v buňce, to funguje bez problémů, ale když volám přez funkci svyhledat další položky přiřazené výběru, tak dynamická oblast hlásí chybu, ale statická funguje

Karel

Přidáno: 24.07.14 09:24

stav: 1. data jsou v listu DataIN (pocet radku se prubezne meni), celkem 9 sloupcu, pocet radku pres 4 tis., prvni radek nadpisy sloupcu 2. nastavena oblast s nazvem TABUL se vzorcem =POSUN($A$1;0;0;POČET2($A$1:$I$10000);9) ta hodnota 10000 (pocet radku) je s dostatecnou rezervou 3. pri kontrole nastaveni TABUL (kliknutim na vzorec) se oznaci na listu DataIN vsechny radky KROME TOHO POSLEDNIHO, ktery by tak nebyl zahrnut do vytvarene KT s vyuzitim TABUL Dotaz: jak upravit vzorec pro dynamicky vyber oblasti, aby byly vybrany skutecne vsechny radky

Karel

Přidáno: 24.07.14 10:53

doplneni k predchozimu dotazu: v uvedenm vzorci má být POČET nikoliv POČET2. POČET2 označí i prázdné řádky, které se pak dostanou do KT a je nutno je pak v KT odfiltrovat.

Peter

Přidáno: 06.12.14 18:53

Ahoj, je možné toto aplikovať aj v excel 2003?

:)

Přidáno: 07.01.15 15:08

Jen maly poznatek. nepadlo to tady ale mozna to mam spatne :) ale tohle pomohlo. generuji si seznam pres funkci svyhledat. tim padem kdyz pouziju pocet2 tak mam v drop down seznamu i prazdne bunky protoze vlastne prazdne nejsou ale je v nich funkce ktera nevraci zadnou hodnotu. nakonec jsem vyresil tak ze jsem od pocet2 jeste odecetl countifblank

Petr

Přidáno: 15.10.15 15:26

Dobrý den, moc pěkný a poučný článek. Chtěl bych se ovšem zeptat, zda-li není možné vytvořit graf bez použití maker tak, aby měl dynamicky definovaný počet řad, - podobně, jako má graf kontingenční tabulky. Děkuji za odpověď

Pavel Lasák

Přidáno: 17.10.15 17:22

To :) Děkuji za doplnění.

Pavel Lasák

Přidáno: 17.10.15 17:33

To Petr: Formatovat tabulku jako tabulku, nebo vytvořit dynamickou oblast (ne jen sloupec).

Pavla

Přidáno: 07.01.16 13:48

Dobrý den, dynamická oblast dle článku nahoře mi všude funguje (svyhledat, sumif, suma atd), s vyjímkou případu, kdy ji chci použít jako zdroj dat pro kontingenční tabulku. Pravidelně v ní zpracovávám přehled výroby podle produktů a výrobních zakázek, které se stále mění. Nechce se mi stále ručně přenastavovat oblast dat. Uměl byste mi poradit, jak to obejít? Děkuji za odpověď.

Jakub

Přidáno: 09.01.16 22:01

Dobrý den. Mám dva problémy. Dynamická oblast už mne v podstatě funguje. První problém ale nastává, pokud se přejmenuje soubor, uloží někam jinam apod. Pak je potřeba ručně přepsat v grafu název souboru. Přemýšlím, jestli nějak půjde název souboru vyřešit v buňce (jsem schopen zjistit název souboru a vložit ho do buňky, k tomu přidat název definované oblasti)? Druhá otázka. Mám dvě řady hodnot. Jedna začíná v roce 1990, druhá 1995. Obě řady trvají stejně dlouho. Graf chci spojnicový. Je možné graf donutit, aby opravdu obě křivky vykreslil s počátkem v daných letech? Zatím mne obě křivky vykresluje od roku 1990. Pokud u toho pozdějšího grafu vložím do prvních let nuly, pak je graf vykreslen správně. Ale pak mne zas zobrazuje zbytečné čáry (nárůst hodnoty v roce 1994 z hodnoty 0 na hodnotu v roce 1995). To ale nechci.

Tom

Přidáno: 22.06.16 13:49

Dobrý den, dá se nějakým způsobem vytvořit dynamická oblast nesousedících buněk v jednom sloupci tak, aby v té oblasti byly jen buňky začínající na písmena XY?






Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2021 |