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.

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.
One comment on “Dynamická definice oblasti buněk s funkcemi POSUN/OFFSET a POČET2/COUNTA – pro ověření dat nebo pro kontingenční tabulku
  1. Jiří Beran napsal:

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

Napsat komentář

Vaše emailová adresa nebude zveřejněna.

Vyhledávání

Exact matches only
Search in title
Search in content
Search in posts
Search in pages
Filter by Categories
Nezařazené

Nyní otevřený kurz

Jednodenní Excel v praxi

  • 14.3.2017
  • V Praze
Další info / přihláška

Firemní kurzy

  • Přemýšlíte o firemním školení Excelu?
  • Školíme Excel od začátečníků po experty, ale také PowerPoint, PowerPivot nebo Access.
Mám zájem o kurz

Individuální výuka

Mám zájem o výuku

Vývoj aplikací v Excelu (makra - VBA)

  • Vyvíjíme aplikace pro potřeby firemních zákazníků
  • Reporty, vizualizace dat, harmonogramy, plánování
  • Slučování dat ze souborů, agregace, exporty
Zajímám se o vytvoření aplikace

Kontakt

  • Tel: +420 602 274 999
  • Mail: info@vyuka-excelu.cz
Kontaktní formulář

FB CZ

TOPlist