I pokud s Excelem pracujete delší dobu, možná vás, stejně jako mě, zatím nenapadlo dynamicky definovat oblasti buněk. Přitom využití je zajímavé - např. pro ověřování dat nebo pro kontingenční tabulky.
Příklad
Chcete použít ověření dat založené na výběru ze seznamu.
Např. takto:
Zápis oblasti, ze které se čerpají hodnoty pro rozbalovací seznam, vypadá takto:
Všechno funguje. Jenomže co když přidáme do seznamu nového zaměstnance?
Tento zaměstnanec, připsaný do seznamu pod předchozí, ve vybíracím seznamu nebude - protože je mimo oblast, ze které vybírací seznam čerpá.
Návod
Řešením je vytvořit odkaz na oblast ne výčtem konkrétních buněk, ale proměnlivou oblastí - tak, aby oblast byla vždy přesně tak dlouhá, kolik je v ní buněk.
K tomu využijeme kombinaci funkcí POČET2 (anglicky COUNTA) a POSUN (anglicky OFFSET).
Zápis oblasti pro vybírací menu pak vypadá takto:
=POSUN(A2;0;0;POČET2($A:$A)-1;1)
Protože:
- POSUN - název funkce, která definuje oblast na základě parametrů. Pro více informací rozklikněte.
- A2 - oblast, kde začíná oblast buněk pro výběrové pole - bez ohledu na to, kam až sahá
- 0 - buňky se nikam neposouvají, teď nás nezajímá
- 0 - to samé jako předchozí bod
- POČET2($A:$A) - vyjadřuje rozměr oblasti směrem dolů. Je mazaně definovaná počtem neprázdných buněk ve sloupci A
- -1 - číslo získané v předchozím bodě je třeba zmenšit o jedničku, protože je v něm započítané i záhlaví - a to v rozevíracím seznamu nemá být
- 1 - vyjadřuje rozměr oblasti směrem doprava. Jinými slovy - vybírací menu se bere jen z jednoho sloupce
Dynamická definice pojmenované oblasti
Pokud chci, mohu dynamicky i definovat pojmenovanou oblast. Pak bude oblast s určitým názvem (např. "zdrojovadata" různě velká podle toho, jaká data obsahuje.
A nad takovou oblastí je pak samozřejmě možné vytvořit kontingenční tabulku.
Žádné komentáře:
Okomentovat