|
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.
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ž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.
Představte si, prodávate rozné věcí, které můžete rozdělit do sekcí:
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 |
Seznam pojmenujte například ProdavaneDruhy
V nabídce Vzorce v sekci Definované názvy ikona Definovat název.Obdržíte okno.
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).
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ě:
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.
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)
Stačí do tabulky doplnit:
Zubačka |
Pěškobus |
Dynamická oblast se zvětší a seznam bude větší:
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
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.
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ů.
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í
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.
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.
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.
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í.
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šší ... ?
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.
Přidáno: 25.09.13 16:25
Diky tento navod jsem potreboval.
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.
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
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 :)
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
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
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.
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
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ěď
Přidáno: 17.10.15 17:22
To :) Děkuji za doplnění.
Přidáno: 17.10.15 17:33
To Petr: Formatovat tabulku jako tabulku, nebo vytvořit dynamickou oblast (ne jen sloupec).
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ěď.
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.
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?
Pomohl Vám návod? Sdílejte na Facebooku, G+ |
||
LinkedIn... |
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 |