Coördinaten selectie

Je hebt een grote monitor, maar de tafels waarmee je werkt zijn nog groter. En als je over het scherm kijkt op zoek naar de nodige informatie, is er altijd een kans om je ogen naar de volgende regel te "glijden" en in de verkeerde richting te kijken. Ik ken zelfs mensen die voor zulke gelegenheden altijd een houten liniaal bij zich houden om deze aan de lijn op de monitor te bevestigen. Technologieën van de toekomst! 

En als de huidige rij en kolom worden gemarkeerd wanneer de actieve cel over het blad beweegt? Een soort coördinaatselectie zoals deze:

Beter dan een heerser, toch?

Er zijn verschillende manieren van variërende complexiteit om dit te implementeren. Elke methode heeft zijn voor- en nadelen. Laten we ze eens in detail bekijken.

Methode 1. Duidelijk. Macro die de huidige rij en kolom markeert

De meest voor de hand liggende manier om ons probleem "op het voorhoofd" op te lossen - we hebben een macro nodig die de wijziging in de selectie op het blad volgt en de hele rij en kolom voor de huidige cel selecteert. Ook is het wenselijk deze functie zo nodig aan en uit te kunnen zetten, zodat zo'n kruisvormige selectie ons niet belet om bijvoorbeeld formules in te voeren, maar pas werkt als we de lijst doorbladeren op zoek naar de benodigde informatie. Dit brengt ons bij de drie macro's (selecteren, inschakelen en uitschakelen) die aan de werkbladmodule moeten worden toegevoegd.

Open een blad met een tabel waarin u zo'n coördinaatselectie wilt krijgen. Klik met de rechtermuisknop op de bladtab en selecteer de opdracht in het contextmenu Brontekst (Broncode).Het venster Visual Basic Editor zou moeten openen. Kopieer deze tekst van deze drie macro's erin:

Dim Coord_Selection As Boolean 'Globale variabele voor selectie aan/uit Sub Selection_On() 'Macro aan selectie Coord_Selection = True End Sub Selection_Off() 'Macro off selection Coord_Selection = False End Sub 'Hoofdprocedure die selectie uitvoert Private Sub Worksheet_SelectionChange(ByVal Target As Bereik) Dim WorkRange As Range Als Target.Cells.Count > 1 Dan Exit Sub 'als er meer dan 1 cel is geselecteerd, exit If Coord_Selection = False Then Exit Sub 'als selectie is uitgeschakeld, sluit Application.ScreenUpdating = False Set WorkRange = Range ("A6:N300") 'adres van het werkbereik waarbinnen de selectie zichtbaar is

Wijzig het adres van het werkbereik in uw eigen adres - het is binnen dit bereik dat onze selectie zal werken. Sluit vervolgens de Visual Basic Editor en keer terug naar Excel.

Druk op de sneltoets ALT + F8om een ​​venster te openen met een lijst met beschikbare macro's. macro Selectie_Aan, zoals je zou kunnen raden, omvat coördinaatselectie op het huidige blad en de macro Selectie_Uit – schakelt het uit. Klik in hetzelfde venster op de knop parameters (Opties) U kunt sneltoetsen aan deze macro's toewijzen om ze gemakkelijk te kunnen starten.

Voordelen van deze methode:

  • relatief gemak van implementatie
  • selectie - de bewerking is onschadelijk en verandert op geen enkele manier de inhoud of opmaak van de bladcellen, alles blijft zoals het is

Nadelen van deze methode:

  • een dergelijke selectie werkt niet correct als er samengevoegde cellen op het blad staan ​​- alle rijen en kolommen die in de unie zijn opgenomen, worden in één keer geselecteerd
  • als u per ongeluk op de Delete-toets drukt, wordt niet alleen de actieve cel gewist, maar het hele geselecteerde gebied, dwz verwijder gegevens uit de hele rij en kolom

Methode 2. Origineel. CELL + voorwaardelijke opmaakfunctie

Deze methode, hoewel er een paar nadelen aan kleven, lijkt me erg elegant. Om iets te implementeren met alleen de ingebouwde Excel-tools, is het minimaal om te beginnen met programmeren in VBA aerobatics

De methode is gebaseerd op het gebruik van de CEL-functie, die veel verschillende informatie over een bepaalde cel kan geven - hoogte, breedte, rij-kolomnummer, getalnotatie, enz. Deze functie heeft twee argumenten:

  • een codewoord voor de parameter, zoals "kolom" of "rij"
  • het adres van de cel waarvoor we de waarde van deze parameter willen bepalen

De truc is dat het tweede argument optioneel is. Als het niet is opgegeven, wordt de huidige actieve cel genomen.

Het tweede onderdeel van deze methode is voorwaardelijke opmaak. Met deze uiterst handige Excel-functie kunt u cellen automatisch opmaken als ze aan bepaalde voorwaarden voldoen. Als we deze twee ideeën in één combineren, krijgen we het volgende algoritme voor het implementeren van onze coördinaatselectie via voorwaardelijke opmaak:

  1. We selecteren onze tabel, dat wil zeggen die cellen waarin de coördinaatselectie in de toekomst moet worden weergegeven.
  2. Open in Excel 2003 en ouder het menu Opmaak – Voorwaardelijke opmaak – Formule (Formaat — Voorwaardelijke opmaak — Formule). In Excel 2007 en nieuwer – klik op het tabblad Home (Home) Voorwaardelijke opmaak – Regel maken (Voorwaardelijke opmaak - Regel maken) en kies het regeltype Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt (Gebruik formule)
  3. Voer de formule in voor onze coördinatenselectie:

    =OF(CEL(“rij”)=RIJ(A2),CEL(“kolom”)=KOLOM(A2))

    =OF(CEL(«rij»)=RIJ(A1),CEL(«kolom»)=KOLOM(A1))

    Deze formule controleert of het kolomnummer van elke cel in de tabel hetzelfde is als het kolomnummer van de huidige cel. Zo ook met kolommen. Dus alleen die cellen met een kolomnummer of een rijnummer dat overeenkomt met de huidige cel worden ingevuld. En dit is de kruisvormige coördinaatselectie die we willen bereiken.

  4. Klik op de knop Achtergrond (Formaat) en stel de vulkleur in.

Alles is bijna klaar, maar er is één nuance. Feit is dat Excel een wijziging in de selectie niet beschouwt als een wijziging in de gegevens op het blad. En als gevolg hiervan activeert het geen herberekening van formules en herkleuren van voorwaardelijke opmaak alleen wanneer de positie van de actieve cel verandert. Laten we daarom een ​​eenvoudige macro toevoegen aan de werkbladmodule die dit zal doen. Klik met de rechtermuisknop op de bladtab en selecteer de opdracht in het contextmenu Brontekst (Broncode).Het venster Visual Basic Editor zou moeten openen. Kopieer deze tekst van deze eenvoudige macro erin:

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

Wanneer de selectie nu verandert, wordt het proces van het herberekenen van de formule met de functie gestart CELL in voorwaardelijke opmaak en de huidige rij en kolom overspoelen.

Voordelen van deze methode:

  • Voorwaardelijke opmaak verbreekt geen aangepaste tabelopmaak
  • Deze selectieoptie werkt correct met samengevoegde cellen.
  • Geen risico op het verwijderen van een hele rij en kolom met gegevens bij een onbedoelde klik Verwijder.
  • Macro's worden minimaal gebruikt

Nadelen van deze methode:

  • De formule voor voorwaardelijke opmaak moet handmatig worden ingevoerd.
  • Er is geen snelle manier om dergelijke opmaak in of uit te schakelen - het is altijd ingeschakeld totdat de regel wordt verwijderd.

Methode 3. Optimaal. Voorwaardelijke opmaak + macro's

Gulden snede. We gebruiken het mechanisme voor het volgen van de selectie op het blad met behulp van macro's van methode-1 en voegen er veilige markering aan toe met behulp van voorwaardelijke opmaak van methode-2.

Open een blad met een tabel waarin u zo'n coördinaatselectie wilt krijgen. Klik met de rechtermuisknop op de bladtab en selecteer de opdracht in het contextmenu Brontekst (Broncode).Het venster Visual Basic Editor zou moeten openen. Kopieer deze tekst van deze drie macro's erin:

Dim Coord_Selection As Boolean Sub Selection_On() Coord_Selection = True End Sub Sub Selection_Off() Coord_Selection = False 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 = False If Not Intersect(Target) Is Nothing = Intersect(Target) WorkRange, Union(Target.EntireRow, Target.EntireColumn)) WorkRange.FormatConditions.Delete CrossRange.FormatConditions.Add Type:=xlExpression, Formula1:="=1" CrossRange.FormatConditions(1).Interior.ColorIndex = 33 Target.FormatConditions .Verwijder einde als einde sub

Vergeet niet het werkbereikadres te wijzigen in uw tafeladres. Sluit de Visual Basic Editor en keer terug naar Excel. Om de toegevoegde macro's te gebruiken, drukt u op de sneltoets ALT + F8  en ga op dezelfde manier te werk als bij methode 1. 

Methode 4. Mooi. FollowCellPointer-add-on

Excel MVP Jan Karel Pieterse uit Nederland geeft een gratis add-on weg op zijn website Volg CellPointer(36Kb), die hetzelfde probleem oplost door grafische pijllijnen te tekenen met behulp van macro's om de huidige rij en kolom te markeren:

 

Mooie oplossing. Niet zonder haperingen op plaatsen, maar zeker het proberen waard. Download het archief, pak het uit op schijf en installeer de add-on:

  • in Excel 2003 en ouder – via het menu Service – Add-ons – Overzicht (Extra — Invoegtoepassingen — Bladeren)
  • in Excel 2007 en later, via Bestand – Opties – Add-ons – Ga – Bladeren (Bestand — Excel-opties — Invoegtoepassingen — Ga naar — Bladeren)

  • Wat zijn macro's, waar macrocode in te voegen in Visual Basic

 

Laat een reactie achter