Dynamická definice oblasti buněk s funkcemi POSUN/OFFSET a POČET2/COUNTA – pro ověření dat nebo pro kontingenční tabulku

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

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.

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.

1 Komentář

Jiří Beran says:

29.7.2015 at 10:54

Dobrý den, jak to přesně myslíte, že máte další buńky pod vzorcem?

Návody