Dynamická definice oblasti buněk s funkcemi POSUN/OFFSET a POČET2/COUNTA – pro ověření dat nebo pro kontingenční tabulku
S tímto tématem se setkáte na našich kurzech ExcelTown:
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.
Tento článek popisuje, jak to dělat pomocí funkce POSUN a POČET2. Alternativně (a možná o trochu jednodušeji) to jde také pomocí vložené 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ů.
- 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
Vzniklé vybírací menu bude vždy zobrazovat všechny položky - bez ohledu na to, kolik jich je (pokud budou za sebou a nebude mezi nimi mezera).
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.
Dobrý den, jak to přesně myslíte, že máte další buńky pod vzorcem?