Dynamické (podmienené) filtrovanie

Jednou z ďalších frekventovane žiadaných funkcionalít v Exceli je tzv. dynamické filtrovanie alebo použitie hierarchie. 

V princípe sa jedná o možnosť mať napríklad prvý drop - down, v ktorom si vyberiete kraj (Košický), v ďalšom drop - downe si vyberiete z miest, dostupných vrámci Košického kraja (Košice, Michalovce atď). Inými slovami, vrámci hierarchie vyberáte vždy relevantné informácie. Nasledujúca tabuľka reprezentuje hierachiu krajov, obcí a obchodníkov:

Spôsob, ktorý dnes ukážem nebude pozostávať z komplikovaných makier. 

Tvorba hierarchie:

Najprv si rozdelíme tabuľku na viacero podtabuliek:

Taktiež sme použili pomenované oblasti pre každú podtabuľku. Inými slovami: tabuľka KRAJ obsahuje všetky existujúce kraje vrámci filtra. ďalej tabuľka Košický, obsahuje všetky mesta vrámci Košického kraja, atď. Keď budete mať rozpitvanú celú tabuľku na menšie, gratulujem - definovali ste hierarchiu.

Skladanie hierarchie:

Vytvorte nový list a do bunky, v ktorej chcete mať prvý drop-down vložte validáciu dat ( DATA - Data Validation - List - KRAJ ):

O dva stĺpce vedľa vložte pomocou predchádzajúceho postupu nasledujúcu validáciu (prostredníctvom funkcie INDIRECT odkaz na pomenovanú oblasť, ktorej názov sa nachádza v prvom drop - downe, t.j. B4 bunke):

Danú techniku aplikujeme na obchodníka a samotný výsledok si môžete skúsit sami. Problém však nastane pri kliknutí na Bankú Štiavnicu alebo Bystricu. 

Pomenovaná oblasť nám totiž nesedí so samotným názvom ( Pomenované oblasti nedovolujú používať medzerník, preto som použil "_" miesto " " ). Pripravte si podpornú tabuľku SETTINGS s prekladmi a upravte posledný dropdown použitím kombinácii funkcií VLOOKUP, IFERROR a INDIRECT:

Ďalej odporúčam hrať sa s farbami resp. zarovnaním a výsledok môže vyzerať takto:

Bonusové riešenie:

Predstavte si situáciu, že zmeníte kraj a Obec spolu s Delaerom sa vynulujú. Táto dodatočná funkcionalita je dôležitá z rôznych - prevažne konsolidačných dôvodov. Nechcete predsa filtrovať alebo ukladat nezmyselné kombinácie. Spomenutý  reset prinúti užívateľa stále zadať zvalidovanú kombináciu.


Zvoľme si napríklad bunky B2, D2 a F2 - ktoré vrámci VBA označíme ako: cells(2,2), cells(2,4) a cells(2,6). Do nich sa budú zapisovat nové hodnoty, vybrané užívatelom (odporúčam daný riadok skryť alebo zamknúť a zmeniť farbu textu na bielu).


Ďalej vložíme do VBA prostredia pre ThisWorkbook nasledujúce makro:


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)


If Cells(4, 2).Value <> Cells(2, 2).Value Then
        Cells(2, 2).Value = Cells(4, 2).Value
        Cells(4, 4).Value = ""
        Cells(2, 4).Value = ""
        Cells(4, 6).Value = ""
        Cells(2, 6).Value = ""
End If
If Cells(4, 4).Value <> Cells(2, 4).Value Then
        Cells(2, 4).Value = Cells(4, 4).Value
        Cells(4, 6).Value = ""
        Cells(2, 6).Value = ""End If
If Cells(4, 6).Value <> Cells(2, 6).Value Then
        Cells(2, 6).Value = Cells(4, 6).ValueEnd If


End Sub

Vzorové riešenie si môžete stiahnuť kliknutím sem.