Výběr souřadnic

Máte velký monitor, ale stoly, se kterými pracujete, jsou ještě větší. A když se díváte přes obrazovku při hledání potřebných informací, vždy máte možnost „překlouznout“ očima na další řádek a podívat se špatným směrem. Znám dokonce lidi, kteří si pro takové příležitosti vždy drží dřevěné pravítko blízko sebe, aby ho připevnili na linku na monitoru. Technologie budoucnosti! 

A pokud se aktuální řádek a sloupec zvýrazní, když se aktivní buňka pohybuje po listu? Druh výběru souřadnic, jako je tento:

Lepší než pravítko, ne?

Existuje několik způsobů s různou složitostí, jak to implementovat. Každá metoda má své pro a proti. Pojďme se na ně podívat podrobně.

Metoda 1. Je zřejmé. Makro, které zvýrazní aktuální řádek a sloupec

Nejviditelnější způsob, jak vyřešit náš problém „na čele“ – potřebujeme makro, které bude sledovat změnu výběru na listu a vybrat celý řádek a sloupec pro aktuální buňku. Je také žádoucí mít možnost tuto funkci v případě potřeby povolit a zakázat, aby nám takový výběr ve tvaru kříže nebránil v zadávání např. vzorců, ale fungoval pouze tehdy, když prohlížíme seznam a hledáme potřebné informace. Tím se dostáváme ke třem makrům (výběr, povolení a zakázání), která bude nutné přidat do modulu listu.

Otevřete list s tabulkou, ve které chcete takový výběr souřadnic získat. Klepněte pravým tlačítkem myši na kartu listu a vyberte příkaz z místní nabídky Zdrojový text (Zdrojový kód).Mělo by se otevřít okno editoru jazyka Visual Basic. Zkopírujte do něj tento text těchto tří maker:

Dim Coord_Selection As Boolean 'Globální proměnná pro výběr zapnuto/vypnuto Sub Selection_On() 'Makro při výběru Coord_Selection = True End Sub Selection_Off() 'Makro vypnuto výběr Coord_Selection = False End Sub 'Hlavní procedura, která provádí výběr Private Sub Worksheet_SelectionChange(ByVal Target Rozsah) Dim WorkRange As Range If Target.Cells.Count > 1 Then Exit Sub 'pokud je vybrána více než 1 buňka, ukončete If Coord_Selection = False Then Exit Sub 'pokud je výběr vypnutý, ukončete Application.ScreenUpdating = False Set WorkRange = Range (" A6:N300") 'adresa pracovního rozsahu, ve kterém je výběr viditelný

Změňte adresu pracovního rozsahu na svou vlastní – v tomto rozsahu bude náš výběr fungovat. Potom zavřete editor jazyka Visual Basic a vraťte se do Excelu.

Stiskněte klávesovou zkratku ALT + F8otevřete okno se seznamem dostupných maker. Makro Selection_On, jak asi tušíte, zahrnuje výběr souřadnic na aktuálním listu a makro Selection_Off – vypne se. Ve stejném okně kliknutím na tlačítko parametry (Možnosti) K těmto makrům můžete přiřadit klávesové zkratky pro snadné spouštění.

Výhody této metody:

  • relativní snadnost implementace
  • výběr – operace je nezávadná a nijak nemění obsah ani formátování buněk listu, vše zůstává tak, jak je

Nevýhody této metody:

  • takový výběr nefunguje správně, pokud jsou na listu sloučené buňky – všechny řádky a sloupce zahrnuté ve sjednocení jsou vybrány najednou
  • pokud omylem stisknete klávesu Delete, pak se vymaže nejen aktivní buňka, ale celá vybraná oblast, tj. smaže se data z celého řádku a sloupce

Metoda 2. Originál. CELL + funkce podmíněného formátování

Tato metoda, i když má několik nevýhod, se mi zdá velmi elegantní. Implementovat něco pouze pomocí vestavěných nástrojů Excelu, dostat se do programování ve VBA minimálně je akrobacie 😉

Metoda je založena na použití funkce CELL, která může poskytnout mnoho různých informací o dané buňce – výšku, šířku, číslo řádku-sloupce, formát čísla atd. Tato funkce má dva argumenty:

  • kódové slovo pro parametr, například „sloupec“ nebo „řádek“
  • adresu buňky, pro kterou chceme určit hodnotu tohoto parametru

Trik je v tom, že druhý argument je volitelný. Není-li zadáno, pak se vezme aktuální aktivní buňka.

Druhou součástí této metody je podmíněné formátování. Tato mimořádně užitečná funkce Excelu umožňuje automaticky formátovat buňky, pokud splňují zadané podmínky. Pokud tyto dva nápady spojíme do jednoho, získáme následující algoritmus pro implementaci našeho výběru souřadnic prostřednictvím podmíněného formátování:

  1. Vybereme naši tabulku, tedy ty buňky, ve kterých by se měl v budoucnu zobrazovat výběr souřadnic.
  2. V aplikaci Excel 2003 a starších otevřete nabídku Formát – Podmíněné formátování – Vzorec (Formát — Podmíněné formátování — Vzorec). V Excelu 2007 a novějších – klikněte na záložku Domů (Domov)tlačítko Podmíněné formátování – vytvořit pravidlo (Podmíněné formátování — Vytvořit pravidlo) a vyberte typ pravidla Pomocí vzorce určete, které buňky chcete formátovat (Použijte vzorec)
  3. Zadejte vzorec pro náš výběr souřadnic:

    =NEBO(BUŇKA(“řádek”)=ŘÁDEK(A2),BUŇKA(“sloupec”)=SLOUPEK(A2))

    =NEBO(BUŇKA(«řádek»)=ŘÁDEK(A1),CELL(«sloupec»)=SLOUPEK(A1))

    Tento vzorec kontroluje, zda je číslo sloupce každé buňky v tabulce stejné jako číslo sloupce aktuální buňky. Stejně tak se sloupky. Vyplní se tedy pouze ty buňky, které mají číslo sloupce nebo číslo řádku, které odpovídá aktuální buňce. A to je výběr souřadnic ve tvaru kříže, kterého chceme dosáhnout.

  4. lis Rámec (Formát) a nastavte barvu výplně.

Vše je téměř připraveno, ale je tu jedna nuance. Excel totiž změnu výběru nepovažuje za změnu údajů na listu. A díky tomu nespouští přepočítávání vzorců a přebarvování podmíněného formátování pouze při změně pozice aktivní buňky. Přidejme proto do modulu list jednoduché makro, které to udělá. Klepněte pravým tlačítkem myši na kartu listu a vyberte příkaz z místní nabídky Zdrojový text (Zdrojový kód).Mělo by se otevřít okno editoru jazyka Visual Basic. Zkopírujte do něj tento text tohoto jednoduchého makra:

Private Sub Worksheet_SelectionChange (ByVal Target As Range) ActiveCell.Calculate End Sub

Nyní, když se výběr změní, spustí se proces přepočtu vzorce s funkcí BUŇKA v podmíněném formátování a zaplaví aktuální řádek a sloupec.

Výhody této metody:

  • Podmíněné formátování nenarušuje vlastní formátování tabulky
  • Tato možnost výběru funguje správně se sloučenými buňkami.
  • Žádné riziko smazání celého řádku a sloupce dat při náhodném kliknutí Vymazat.
  • Makra se používají minimálně

Nevýhody této metody:

  • Vzorec pro podmíněné formátování je nutné zadat ručně.
  • Neexistuje žádný rychlý způsob, jak takové formátování povolit/zakázat – je vždy povoleno, dokud není pravidlo smazáno.

Metoda 3. Optimální. Podmíněné formátování + makra

Zlatá střední cesta. Použijeme mechanismus pro sledování výběru na listu pomocí maker z metody-1 a přidáme k němu bezpečné zvýraznění pomocí podmíněného formátování z metody-2.

Otevřete list s tabulkou, ve které chcete takový výběr souřadnic získat. Klepněte pravým tlačítkem myši na kartu listu a vyberte příkaz z místní nabídky Zdrojový text (Zdrojový kód).Mělo by se otevřít okno editoru jazyka Visual Basic. Zkopírujte do něj tento text těchto tří maker:

Ztlumit Coord_Selection jako logickou část Selection_On() Coord_Selection = True End Sub Selection_Off() Coord_Selection = Nepravdivý End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim WorkRange as Range, CrossRange as Range Set WorkRange = Range("A7:N300) 'адрес рабочего диапазона с таблицей If Target.Count > 1 Then Exit Sub If Coord_Selection = False Then WorkRange.FormatConditions.Delete Exit Sub End If Application.ScreenUpdating =Rangect Interface =False. WorkRange, Union(Target.EntireRow, Target.EntireColumn)) WorkRange.FormatConditions.Delete CrossRange.FormatConditions.Add Type:=xlExpression, Formula1:="=1" CrossRange.FormatConditions(1).Interior.ColorIndex = 33 Target.Format .Delete End If End Sub

Nezapomeňte změnit adresu pracovního rozsahu na adresu vašeho stolu. Zavřete editor jazyka Visual Basic a vraťte se do Excelu. Chcete-li použít přidaná makra, stiskněte klávesovou zkratku ALT + F8  a postupujte stejně jako u metody 1. 

Metoda 4. Krásná. Doplněk FollowCellPointer

Excel MVP Jan Karel Pieterse z Nizozemska rozdává na svém webu bezplatný doplněk Sledujte CellPointer(36Kb), který řeší stejný problém kreslením grafických šipek pomocí maker pro zvýraznění aktuálního řádku a sloupce:

 

Pěkné řešení. Místy ne bez závad, ale rozhodně stojí za vyzkoušení. Stáhněte si archiv, rozbalte jej na disk a nainstalujte doplněk:

  • v Excelu 2003 a starších – prostřednictvím nabídky Služba – Doplňky – Přehled (Nástroje — Doplňky — Procházet)
  • v aplikaci Excel 2007 a novější, prostřednictvím Soubor – Možnosti – Doplňky – Přejít – Procházet (Soubor — Možnosti aplikace Excel — Doplňky — Přejít na — Procházet)

  • Co jsou makra, kam vložit kód makra ve Visual Basicu

 

Napsat komentář