Výber súradníc

Máte veľký monitor, no stoly, s ktorými pracujete, sú ešte väčšie. A pri pohľade na obrazovku pri hľadaní potrebných informácií vždy existuje možnosť „skĺznuť“ očami na ďalší riadok a pozrieť sa nesprávnym smerom. Poznám dokonca ľudí, ktorí si na takéto príležitosti držia vždy blízko seba drevené pravítko, aby ho pripevnili na linku na monitore. Technológie budúcnosti! 

A ak sú aktuálny riadok a stĺpec zvýraznené, keď sa aktívna bunka pohybuje po hárku? Druh výberu súradníc, ako je tento:

Lepšie ako pravítko, však?

Existuje niekoľko spôsobov s rôznou zložitosťou, ako to implementovať. Každá metóda má svoje pre a proti. Poďme sa na ne pozrieť podrobne.

Metóda 1. Je zrejmé. Makro, ktoré zvýrazní aktuálny riadok a stĺpec

Najzrejmejší spôsob, ako vyriešiť náš problém „na čele“ – potrebujeme makro, ktoré bude sledovať zmenu výberu na hárku a vyberie celý riadok a stĺpec pre aktuálnu bunku. Je tiež žiaduce, aby bolo možné túto funkciu v prípade potreby povoliť a zakázať, aby nám takýto výber v tvare kríža nebránil v zadávaní napríklad vzorcov, ale fungoval iba vtedy, keď si zoznam prezeráme a hľadáme potrebné informácie. To nás privádza k trom makrám (výber, povolenie a zakázanie), ktoré bude potrebné pridať do modulu listu.

Otvorte hárok s tabuľkou, v ktorej chcete získať takýto výber súradníc. Kliknite pravým tlačidlom myši na kartu listu a vyberte príkaz z kontextovej ponuky Zdrojový text (Zdrojový kód).Malo by sa otvoriť okno editora jazyka Visual Basic. Skopírujte do nej tento text týchto troch makier:

Dim Coord_Selection As Boolean 'Globálna premenná pre výber zapnutá/vypnutá Sub Selection_On() 'Makro pri výbere Coord_Selection = True End Sub Selection_Off() 'Vypnutie makra Coord_Selection = False End Sub 'Hlavný postup, ktorý vykoná výber Private Sub Worksheet_SelectionChange(ByVal Target Rozsah) Dim WorkRange As Range If Target.Cells.Count > 1 Then Exit Sub 'ak je vybratá viac ako 1 bunka, ukončite If Coord_Selection = False Then Exit Sub 'ak je výber vypnutý, ukončite Application.ScreenUpdating = False Set WorkRange = Range ("A6:N300") 'adresa pracovného rozsahu, v rámci ktorého je výber viditeľný

Zmeňte adresu pracovného rozsahu na svoju vlastnú – v rámci tohto rozsahu bude fungovať náš výber. Potom zatvorte editor jazyka Visual Basic a vráťte sa do Excelu.

Stlačte klávesovú skratku ALT + F8otvoríte okno so zoznamom dostupných makier. Makro Selection_On, ako by ste mohli hádať, zahŕňa výber súradníc na aktuálnom hárku a makro Selection_Off – vypne sa. V tom istom okne kliknutím na tlačidlo parametre (Možnosti) K týmto makrám môžete priradiť klávesové skratky pre jednoduché spustenie.

Výhody tejto metódy:

  • relatívna jednoduchosť implementácie
  • výber – operácia je nezávadná a nijako nemení obsah ani formátovanie buniek hárku, všetko zostáva tak, ako je

Nevýhody tejto metódy:

  • takýto výber nefunguje správne, ak sú na hárku zlúčené bunky – všetky riadky a stĺpce zahrnuté do spojenia sa vyberú naraz
  • ak omylom stlačíte kláves Delete, tak sa vymaže nielen aktívna bunka, ale celá vybraná oblasť, teda vymazanie údajov z celého riadka a stĺpca

Metóda 2. Originál. CELL + funkcia podmieneného formátovania

Táto metóda, aj keď má niekoľko nevýhod, sa mi zdá veľmi elegantná. Implementovať niečo iba pomocou vstavaných nástrojov Excelu, minimálne dostať sa do programovania vo VBA je akrobacia 😉

Metóda je založená na použití funkcie CELL, ktorá môže poskytnúť množstvo rôznych informácií o danej bunke – výšku, šírku, číslo riadka-stĺpec, formát čísla atď. Táto funkcia má dva argumenty:

  • kódové slovo pre parameter, napríklad „stĺpec“ alebo „riadok“
  • adresu bunky, pre ktorú chceme určiť hodnotu tohto parametra

Trik je v tom, že druhý argument je voliteľný. Ak nie je zadaný, použije sa aktuálna aktívna bunka.

Druhou súčasťou tejto metódy je podmienené formátovanie. Táto mimoriadne užitočná funkcia Excelu vám umožňuje automaticky formátovať bunky, ak spĺňajú zadané podmienky. Ak skombinujeme tieto dva nápady do jedného, ​​dostaneme nasledujúci algoritmus na implementáciu nášho výberu súradníc prostredníctvom podmieneného formátovania:

  1. Vyberieme našu tabuľku, teda tie bunky, v ktorých by sa mal v budúcnosti zobrazovať súradnicový výber.
  2. V Exceli 2003 a staršom otvorte ponuku Formát – Podmienené formátovanie – Vzorec (Formát – Podmienené formátovanie – Vzorec). V Exceli 2007 a novšom – kliknite na kartu Domov (Domov)gombík Podmienené formátovanie – vytvoriť pravidlo (Podmienené formátovanie – vytvoriť pravidlo) a vyberte typ pravidla Použite vzorec na určenie, ktoré bunky sa majú formátovať (Použite vzorec)
  3. Zadajte vzorec pre náš výber súradníc:

    =ALEBO(BUŇKA(“riadok”)=RIADOK(A2),BUNKA(”stĺpec”)=STĺpec(A2))

    =ALEBO(BUŇKA(«riadok»)=RIADOK(A1),BUNKA(«stĺpec»)=STĺpec(A1))

    Tento vzorec kontroluje, či je číslo stĺpca každej bunky v tabuľke rovnaké ako číslo stĺpca aktuálnej bunky. Podobne aj so stĺpikmi. Vyplnia sa teda len tie bunky, ktoré majú číslo stĺpca alebo číslo riadku, ktoré sa zhoduje s aktuálnou bunkou. A toto je výber súradníc v tvare kríža, ktorý chceme dosiahnuť.

  4. lis Rámec (Formát) a nastavte farbu výplne.

Všetko je takmer pripravené, ale je tu jedna nuance. Faktom je, že Excel nepovažuje zmenu výberu za zmenu údajov na hárku. A v dôsledku toho nespustí prepočítavanie vzorcov a prefarbenie podmieneného formátovania iba vtedy, keď sa zmení pozícia aktívnej bunky. Pridajme preto do hárkového modulu jednoduché makro, ktoré to urobí. Kliknite pravým tlačidlom myši na kartu listu a vyberte príkaz z kontextovej ponuky Zdrojový text (Zdrojový kód).Malo by sa otvoriť okno editora jazyka Visual Basic. Skopírujte doň tento text tohto jednoduchého makra:

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

Teraz, keď sa výber zmení, spustí sa proces prepočtu vzorca s funkciou BUNKA v podmienenom formátovaní a zaplaví aktuálny riadok a stĺpec.

Výhody tejto metódy:

  • Podmienené formátovanie nenaruší vlastné formátovanie tabuľky
  • Táto možnosť výberu funguje správne so zlúčenými bunkami.
  • Žiadne riziko vymazania celého riadka a stĺpca údajov pri náhodnom kliknutí vymazať.
  • Makrá sa používajú minimálne

Nevýhody tejto metódy:

  • Vzorec pre podmienené formátovanie je potrebné zadať ručne.
  • Neexistuje rýchly spôsob, ako povoliť/zakázať takéto formátovanie – je vždy povolené, kým sa pravidlo neodstráni.

Metóda 3. Optimálna. Podmienené formátovanie + makrá

Zlatá stredná cesta. Využívame mechanizmus sledovania výberu na hárku pomocou makier z metódy-1 a pridávame k nemu bezpečné zvýraznenie pomocou podmieneného formátovania z metódy-2.

Otvorte hárok s tabuľkou, v ktorej chcete získať takýto výber súradníc. Kliknite pravým tlačidlom myši na kartu listu a vyberte príkaz z kontextovej ponuky Zdrojový text (Zdrojový kód).Malo by sa otvoriť okno editora jazyka Visual Basic. Skopírujte do nej tento text týchto troch makier:

Stlmiť Coord_Selection ako boolovský Sub Selection_On() Coord_Selection = Pravda End Sub Selection_Off() Coord_Selection = Falošný 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 SubTarget If Application.Screange Not Intered, If Application.Screange Notdating 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

Nezabudnite zmeniť adresu pracovného rozsahu na adresu vášho stola. Zatvorte editor jazyka Visual Basic a vráťte sa do Excelu. Ak chcete použiť pridané makrá, stlačte klávesovú skratku ALT + F8  a postupujte rovnakým spôsobom ako pri metóde 1. 

Metóda 4. Krásna. Doplnok FollowCellPointer

Excel MVP Jan Karel Pieterse z Holandska rozdáva na svojej webovej stránke bezplatný doplnok Sledujte CellPointer(36 kB), ktorý rieši rovnaký problém kreslením grafických šípok pomocou makier na zvýraznenie aktuálneho riadka a stĺpca:

 

Pekné riešenie. Nie bez chýb na miestach, ale rozhodne stojí za vyskúšanie. Stiahnite si archív, rozbaľte ho na disk a nainštalujte doplnok:

  • v Exceli 2003 a staršom – cez menu Služba – Doplnky – Prehľad (Nástroje — Doplnky — Prehľadávať)
  • v Exceli 2007 a novších verziách Súbor – Možnosti – Doplnky – Prejsť – Prehľadávať (Súbor — Možnosti programu Excel — Doplnky — Prejsť na — Prehľadávať)

  • Čo sú makrá, kam vložiť kód makra vo Visual Basicu

 

Nechaj odpoveď