Skrytý obsah na listu (2)

V článku Skrytý obsah na listu (1) jsme se zabývali obecnými možnosti skrývání obsahu. Dnes se na dané téma podíváme prakticky.

Testování buněk

Představme si situaci, kdy je výpočet podmíněn třemi vyplněnými buňkami.

Běžné řešení podmíněného výpočtu

Běžné řešení podmíněného výpočtu

Komplexní test provádíme v buňce E12 s pomocí funkce KDYŽ a logické funkce (spojky) A (AND). S postupem času se jistě dostanete do situace, kdy testovaný počet buněk bude větší, test složitější (vnořené funkce KDYŽ, IFERROR atd.), a vlastně budete mít na očích více z „blbovzdornosti“ než z výpočtů samotných. Ano, částečně lze řešení podpořit s pomocí Data / Ověření, nicméně si nyní zkusme testování přesunout doprava, do skryté části listu.

Zrcadlo a rozfázované podmínky výpočtu A

Zrcadlo a rozfázované podmínky výpočtu A

Princip je z obrázku myslím si zřejmý. Vytvořili jsme si jakési zrcadlo viditelné oblasti, které docela přehledně ukazuje stav věcí. Buňka K10 je ta, na níž leží verdikt stran proveditelnosti výpočtu. Nepřipouští žádnou hodnotu NEPRAVDA v testované (orámované) oblasti. Odvoláváme se na ni ve viditelné části, v buňce E12. Úloha předpokládá, že uživatele do hloubky „nezajímá“, co je špatně. Nějakou základní informaci mu poskytneme v nápovědě či komentáři na místě (osobně mám raději „falešné“ komentáře z Data / Ověření).

Pokud nechcete vyhradit tolik prostoru pro skrytou část (ne vždy máme pro práci velký monitor), je možné nějaké místo ušetřit.

Zrcadlo a rozfázované podmínky výpočtu B

Zrcadlo a rozfázované podmínky výpočtu B

Variabilní parametry výpočtu

Na přelomu roku se mi v poště objevil sešit věnující se finančnímu plánování, do kterého kolega na novém listu zapracoval jistou kalkulačku. Stálo v ní: „Zadej čtyři libovolné parametry z pěti a poslední se dopočítá.“ Zajímalo mě, jak to vyřešil technicky. Buňka – jak známo – obsahuje buď hodnotu, nebo vzorec (vracející hodnotu). Má tedy buď „vstupní“, nebo „výstupní“ charakter. Na obojí naráz není stavěná (někdy si ukážeme, že i toto je řešitelné). Když jsem vyloučil nějakou formu Řešitele a makra, bylo jasné, že se výpočtu účastní pomocné buňky. Nechtěl jsem ale „oprásknout“ jeho řešení a tak jsem jej nechal být a podíval jsem se na ně až později (výrazně se neliší, princip je stejný). Zde prostá ukázka na obvod trojúhelníku (strany 3, 4 a 5 jsou dobré na testování, jedná se o základní pythagorejskou trojici pro pravoúhlý trojúhelník).

Zrcadlo pro variabilní parametry výpočtu

Zrcadlo pro variabilní parametry výpočtu

Ve sloupci G testuji, zda-li bylo v odpovídající vstupní buňce vlevo zadáno číslo. V G10 je ověřena proveditelnost výpočtu (právě když počet hodnot PRAVDA je tři a NEPRAVDA se vyskytuje jen jednou). Na G10 se odkazuje buňka B10. Ve sloupci H jsou jednotlivé výpočty. Ve sloupci D se objeví převzatá hodnota ze sloupce H tehdy, pokud jsou splněny podmínky výpočtu a v buňce vlevo není číslo (viz sloupec G).

Úschovna

O co jde? Vrátíme se k myšlence zrcadla viditelné oblasti. Budeme jej brát jako odkládací prostor, který může fungovat coby záloha uživatelských nastavení nebo jako zdroj dat, např. demo výpočet.

Úschovna pro zálohu a demo data

Úschovna pro zálohu a demo data

Jak jistě správně chápete, bude zapotřebí sepsat makra pro přenos dat zleva doprava a naopak. Já se chtěl přitom vyvarovat toho, že bych přenášel oblast komplet včetně popisků. Při kopírování zprava doleva (demo data se přenáší do viditelné oblasti) je postup snadný, stačí použít SpecialCells pro vyplněné buňky, ale opačným směrem narazí kosa na kámen. Jak definovat buňky viditelné oblasti pro zálohu? Nějakým výpisem vedle, který budeme programově procházet? Ne ne ne. Stačí naplnit výchozími hodnotami buňky zálohy a ty brát jako definiční. Mezi zdrojem a zrcadlem je přeci konstantní horizontální posun (ve VBA metoda Offset) a směr vyjadřuje znaménko u jeho hodnoty.

V kódu níže pracujeme pouze s jednou skrytou oblastí, doplnění pro druhou je ovšem po samonastudování již snadné.

'oblast pro úschovu dat
Private Const cstrUschovnaList As String = "Úschovna"
Private Const cstrUschovnaOblast As String = "H4:K9"

'posun skryté uživatelské oblasti vůči viditelné
Private Const cintUschovnaPosun As Integer = 6

Sub NacistData()

    Dim rngOblast As Range

    Set rngOblast = Worksheets(cstrUschovnaList).Range(cstrUschovnaOblast)

    'kopie dat zprava doleva
    Call KopieDat(rngOblast, cintUschovnaPosun, "doleva")

End Sub


Sub UlozitData()

    'před prvním spuštěním musí kopírované buňky
    'v oblasti úschovy obsahovat výchozí hodnoty
   
    Dim rngOblast As Range

    Set rngOblast = Worksheets(cstrUschovnaList).Range(cstrUschovnaOblast)

    'kopie dat zleva doprava
    Call KopieDat(rngOblast, cintUschovnaPosun, "doprava")

End Sub


Sub KopieDat(rngUschova As Range, intPosun As Integer, strSmer As String)

    Dim rngBunka As Range

    'zamezení překreslování a přepočtu listu
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Select Case strSmer

        Case "doprava"

            'pro všechny buňky zdroje s hodnotou...
            For Each rngBunka In rngUschova.SpecialCells(xlCellTypeConstants)

                'zápis hodnoty ze zdroje
                rngBunka.Value = rngBunka.Offset(0, -intPosun).Value

            Next rngBunka

        Case "doleva"

            'pro všechny buňky zdroje s hodnotou...
            For Each rngBunka In rngUschova.SpecialCells(xlCellTypeConstants)

                'zápis hodnoty ze zdroje
                rngBunka.Offset(0, -intPosun).Value = rngBunka.Value

            Next rngBunka

    End Select

    'povolení přepočtu listu a překreslování
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Sub VymazaniDat(rng)

    'zamezení překreslování a přepočtu listu
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    'pro všechny buňky zdroje s hodnotou...
    For Each rngBunka In rngUschova.SpecialCells(xlCellTypeConstants)

        'vymazání hodnoty ze zdroje
        rngBunka.Offset(0, -intPosun).ClearContents

    Next rngBunka

    'povolení přepočtu listu a překreslování
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Příloha:
excel_skryta_kontrola_uschovna.zip

Klikni a stahuj!