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.
