Koordináta kiválasztása

Nagy monitorod van, de az asztalok, amelyekkel dolgozol, még nagyobbak. És a képernyőn átnézve a szükséges információkat keresve, mindig van esély arra, hogy a szemét a következő sorra „csúsztassa”, és rossz irányba nézzen. Még olyanokat is ismerek, akik ilyen alkalmakkor mindig a közelükben tartanak egy fa vonalzót, hogy a monitoron lévő vonalra rögzítsék. A jövő technológiái! 

És ha az aktuális sor és oszlop kiemelve van, amikor az aktív cella áthalad a lapon? Egyfajta koordináta-kiválasztás:

Jobb, mint egy vonalzó, igaz?

Ennek megvalósítására többféle bonyolultságú módszer létezik. Mindegyik módszernek megvannak a maga előnyei és hátrányai. Nézzük meg őket részletesen.

1. módszer. Nyilvánvaló. Makró, amely kiemeli az aktuális sort és oszlopot

A legkézenfekvőbb módja a „homlokon” lévő probléma megoldásának – szükségünk van egy makróra, amely nyomon követi a kijelölés változását a lapon, és kijelöli a teljes sort és oszlopot az aktuális cellához. Kívánatos az is, hogy ezt a funkciót szükség esetén be- és kikapcsolhassuk, hogy egy ilyen kereszt alakú kijelölés ne akadályozza meg például képletek bevitelét, hanem csak akkor működjön, ha a listát átnézve keressük a szükséges információ. Ezzel eljutunk ahhoz a három makróhoz (kiválasztás, engedélyezés és letiltás), amelyeket hozzá kell adni a lapmodulhoz.

Nyisson meg egy lapot egy táblázattal, amelyben ilyen koordinátakijelölést szeretne kapni. Kattintson a jobb gombbal a lapfülre, és válassza ki a parancsot a helyi menüből Forrás szöveg (Forráskód).A Visual Basic Editor ablakának meg kell nyílnia. Másolja be a három makró szövegét:

Dim Coord_Selection As Boolean 'Globális változó a kijelöléshez Sub Selection_On() 'Makró a kijelölésnél Coord_Selection = True End Sub Selection_Off() 'Makró kikapcsolva a kijelölés Coord_Selection = False End Sub 'A kijelölést végrehajtó fő eljárás Private Sub Worksheet A Target_ByValChangeet A Tartomány) Dim WorkRange Tartományként Ha Target.Cells.Count > 1, majd Exit Sub 'ha több mint 1 cella van kiválasztva, lépjen ki Ha Coord_Selection = False, majd Exit Sub 'ha a kijelölés ki van kapcsolva, lépjen ki az Application.ScreenUpdating = False Set WorkRange = Range ("A6:N300") 'a munkatartomány címe, amelyen belül a kijelölés látható

Módosítsa a munkatartomány címét a sajátjára – ezen a tartományon belül fog működni a kiválasztás. Ezután zárja be a Visual Basic Editort, és térjen vissza az Excelbe.

Nyomja meg a billentyűparancsot ALT + F8az elérhető makrók listáját tartalmazó ablak megnyitásához. Makró Selection_On, ahogy sejtheti, magában foglalja a koordináták kiválasztását az aktuális lapon és a makrót Selection_Off – kikapcsolja. Ugyanebben az ablakban a gombra kattintva paraméterek (Lehetőségek) Ezekhez a makrókhoz billentyűparancsokat rendelhet az egyszerű indítás érdekében.

Ennek a módszernek az előnyei:

  • viszonylag egyszerű a megvalósítás
  • kijelölés – a művelet ártalmatlan és semmilyen módon nem változtatja meg a lapcellák tartalmát vagy formázását, minden marad a régiben

Ennek a módszernek a hátrányai:

  • az ilyen kijelölés nem működik megfelelően, ha egyesített cellák vannak a munkalapon – az egyesítésben szereplő összes sor és oszlop egyszerre kijelölésre kerül
  • ha véletlenül megnyomja a Delete billentyűt, akkor nem csak az aktív cella törlődik, hanem a teljes kijelölt terület, azaz törli az adatokat a teljes sorból és oszlopból

2. módszer. Eredeti. CELL + feltételes formázás funkció

Ez a módszer, bár van néhány hátránya, számomra nagyon elegánsnak tűnik. Ahhoz, hogy valamit csak a beépített Excel eszközökkel megvalósítsunk, a VBA-ban való programozásba minimálisan bele kell menni a műrepülésbe 😉

A módszer a CELL függvény használatán alapul, amely sokféle információt adhat egy adott celláról – magasság, szélesség, sor-oszlop szám, számformátum stb. Ennek a függvénynek két argumentuma van:

  • a paraméter kódszava, például „oszlop” vagy „sor”
  • annak a cellának a címe, amelyre ennek a paraméternek az értékét szeretnénk meghatározni

A trükk az, hogy a második argumentum nem kötelező. Ha nincs megadva, akkor az aktuális aktív cellát veszi fel.

A módszer második összetevője a feltételes formázás. Ez a rendkívül hasznos Excel szolgáltatás lehetővé teszi a cellák automatikus formázását, ha megfelelnek meghatározott feltételeknek. Ha ezt a két ötletet egyesítjük, akkor a következő algoritmust kapjuk a koordináta-kiválasztásunk feltételes formázással történő megvalósításához:

  1. Kijelöljük a táblázatunkat, vagyis azokat a cellákat, amelyekben a koordinátakijelölést a jövőben meg kell jeleníteni.
  2. Excel 2003 és régebbi verziókban nyissa meg a menüt Formátum – Feltételes formázás – Képlet (Formátum – Feltételes formázás – Képlet). Excel 2007 és újabb verziókban – kattintson a fülre Kezdőlap (Itthon)gomb Feltételes formázás – Szabály létrehozása (Feltételes formázás – Szabály létrehozása) és válassza ki a szabály típusát Képlet segítségével határozza meg, hogy mely cellákat formázza (Használj képletet)
  3. Adja meg a koordinátaválasztás képletét:

    =VAGY(CELL(“sor”)=ROW(A2),CELL(“oszlop”)=COLUMN(A2))

    =VAGY(CELL(«sor»)=ROW(A1),CELL(«oszlop»)=COLUMN(A1))

    Ez a képlet ellenőrzi, hogy a táblázat minden cellájának oszlopszáma megegyezik-e az aktuális cella oszlopszámával. Hasonlóképpen az oszlopokkal. Így csak azok a cellák kerülnek kitöltésre, amelyeknek az aktuális cellával megegyező oszlopszáma vagy sorszáma van. És ez a kereszt alakú koordináta-kiválasztás, amit szeretnénk elérni.

  4. sajtó Keretrendszer (Formátum) és állítsa be a kitöltési színt.

Minden majdnem kész, de van egy árnyalat. Az a helyzet, hogy az Excel a kijelölés változását nem tekinti a lapon szereplő adatok változásának. Ennek eredményeként nem váltja ki a képletek újraszámítását és a feltételes formázás újraszínezését, ha az aktív cella helyzete megváltozik. Ezért adjunk hozzá egy egyszerű makrót a lapmodulhoz, amely ezt megteszi. Kattintson a jobb gombbal a lapfülre, és válassza ki a parancsot a helyi menüből Forrás szöveg (Forráskód).A Visual Basic Editor ablakának meg kell nyílnia. Másolja be az egyszerű makró szövegét:

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

Most, amikor a kijelölés megváltozik, elindul a képlet függvényrel történő újraszámítása SEJT feltételes formázásban, és elárasztja az aktuális sort és oszlopot.

Ennek a módszernek az előnyei:

  • A feltételes formázás nem töri meg az egyéni táblázatformázást
  • Ez a kiválasztási lehetőség megfelelően működik egyesített cellák esetén.
  • Nem áll fenn annak veszélye, hogy véletlen kattintás esetén egy teljes adatsor és -oszlop törlődik töröl.
  • A makrókat minimálisan használják

Ennek a módszernek a hátrányai:

  • A feltételes formázás képletét manuálisan kell megadni.
  • Nincs gyors mód az ilyen formázás engedélyezésére/letiltására – a szabály törléséig mindig engedélyezve van.

3. módszer. Optimális. Feltételes formázás + Makrók

Arany középút. A kijelölés nyomon követésére szolgáló mechanizmust használjuk a lapon az 1. módszer makróival, és biztonságos kiemelést adunk hozzá a 2. módszer feltételes formázásával.

Nyisson meg egy lapot egy táblázattal, amelyben ilyen koordinátakijelölést szeretne kapni. Kattintson a jobb gombbal a lapfülre, és válassza ki a parancsot a helyi menüből Forrás szöveg (Forráskód).A Visual Basic Editor ablakának meg kell nyílnia. Másolja be a három makró szövegét:

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 WorkRange As Range =N Range7(R"300 Range)" 'адрес рабочего диапазона с таблицей If Target.Count > 1 then Exit Sub If Coord_Selection = False then WorkRange.FormatConditions.Delete Exit Sub End If Application.ScreenUpdating = False Ha nem metszik egymást WorkRange, Union(Target.EntireRow, Target.EntireColumn)) WorkRange.FormatConditions.Delete CrossRange.FormatConditions.Add Type:=xlExpression, Formula1:="=1" CrossRange.FormatConditions(1).Interior.ColorIndex =33ditions .Delete End If End Sub

Ne felejtse el megváltoztatni a munkatartomány címét a táblázat címére. Zárja be a Visual Basic szerkesztőt, és térjen vissza az Excelbe. A hozzáadott makrók használatához nyomja meg a billentyűkódot ALT + F8  és az 1. módszerrel megegyező módon járjunk el. 

4. módszer. Gyönyörű. Kövesse a CellPointer kiegészítőt

Az Excel MVP-je, a holland Jan Karel Pieterse ingyenes kiegészítőt ad a weboldalán Kövesse a CellPointert(36 Kb), amely ugyanazt a problémát oldja meg azáltal, hogy grafikus nyílvonalakat rajzol makrók segítségével az aktuális sor és oszlop kiemelésére:

 

Szép megoldás. Nem mentes helyenként hibáktól, de egy próbát mindenképpen megér. Töltse le az archívumot, csomagolja ki a lemezre, és telepítse a kiegészítőt:

  • Excel 2003 és régebbi verziókban – a menün keresztül Szolgáltatás – Kiegészítők – Áttekintés (Eszközök — Bővítmények — Böngészés)
  • Excel 2007 és újabb verziókban, keresztül Fájl – Beállítások – Bővítmények – Ugrás – Böngészés (Fájl — Excel-beállítások — Bővítmények — Ugrás — Tallózás)

  • Mik azok a makrók, hova kell beszúrni a makrókódot a Visual Basicben

 

Hagy egy Válaszol