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.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.

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

Např. takto:
menu
Zápis oblasti, ze které se čerpají hodnoty pro rozbalovací seznam, vypadá takto:
dynamicky_zapis_overovaciho_pole
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)
dynamicky_zapis_overovaciho_pole (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.

dynamicka_pojmenovana_oblast
A nad takovou oblastí je pak samozřejmě možné vytvořit kontingenční tabulku.

S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.

1 Komentář

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

Komentáře není možné přidávat