Používate často zoznamy (vytvorené cez “overenie údajov”) kde si môžete vybrať so zoznamu viacerých hodnôt? Vybrať v Exceli však môžete len fixný počet položiek a tak Vám v zozname niečo chýba, alebo tam máte kopec prázdnych položiek?
Skúste položky vyberať “dynamicky”.
Statický zoznam
Najjednoduchší, najpoužívanejší a najhorší spôsob ako používať zoznam v Exceli.
Označte bunky, ktoré chcete použiť v zozname a pomenujte ich.
Označte bunku v ktorej chcete zoznam použiť, v menu “Údaje > Overenie údajov>Povoliť ” vyberte položku zoznam a vložte váš zoznam (krajina_staticky) a potvrďte.
Potom už len stačí kliknúť na bunku a zoznam je k dispozícii.
Čo ale, ak sa váš zoznam rozšíri? Tu máte v zásade dve možnosti.
- celý vyššie popísaný postup zopakovať (ach jaj)
- ísť na to odlesa a vytvoriť si dynamický zoznam pomocou chytrej kombinácie dvoch funkcií
Dynamický zoznam
Tentokrát namiesto názvu zoznamu v Overení údajov použijeme nasledovný vzorec (“Parametre” – je názov listu na ktorom sa zoznam nachádza):
=OFFSET(Parametre!$D$2;0;0;COUNTA(Parametre!$D:$D)-1)
Ako to celé funguje (syntax a logika):
=OFFSET(odkaz;riadky;stĺpce;výška;šírka)
výška = COUNTA(Parametre!$D:$D)-1
Funkcia OFFSET vykonáva posun buniek (ich adries) podľa zvolených parametrov.
| odkaz | je ľavá horná bunka na ktorú odkazujeme |
| riadky; stĺpce | udávajú o koľko sa má “posunúť nová bunka” od adresy zadanej v “odkaz” |
| výška, šírka | je počet riadkov a stĺpcov, ktoré má odkaz zahŕňať (dole a napravo od pozície “novej bunky” |
V našom prípade odkazujeme na bunku D2 na liste Parametre – tam kde je prvá položka nášho zoznamu. Keďže ľavú hornú bunku nášho zoznamu nechceme meniť, položky riadky a stĺpce (ktoré definujú posun bunky) necháme na 0;0 = žiadny posun.
Celý trik potom spočíva v parametri výška, ktorý definuje počet riadkov posúvanej oblasti. (keď je napr. odkaz bunka D2 a výška 6, nová oblasť definovaná funkciou OFFSET bude D2:D8). My ale výšku (počet riadkov) nevieme, keďže chceme počet položiek zoznamu dynamický.
Tu pomôže jednoduchý trik: výšku (počet riadkov) zistíme tak že spočítame počet položiek v stĺpci, kde sa nachádza náš zoznam. Tie spočítame pomocou vzorca COUNTA(D:D) -1. (COUNTA spočíta počet neprázdnych buniek v rozsahu). Od COUNTA odpočítame 1, aby sme eliminovali použitý nadpis zoznamu.
Ak teda zoznam začína na D2 a má 5 položiek COUNTA spočíta všetky neprázdne bunky – vráti 5. Náš vzorec teda vyberie oblasť D2:D6. Ak pridáme do zoznamu 2 nové položky, náš dynamický zoznam bude zahŕňať oblasť D2:D8 …
