Koordinat Seleksi

Anda memiliki monitor besar, tetapi meja yang Anda gunakan bahkan lebih besar. Dan, melihat ke atas layar untuk mencari informasi yang diperlukan, selalu ada kesempatan untuk "menyelipkan" mata Anda ke baris berikutnya dan melihat ke arah yang salah. Saya bahkan mengenal orang-orang yang, untuk kesempatan seperti itu, selalu meletakkan penggaris kayu di dekat mereka untuk menempelkannya ke garis di monitor. Teknologi masa depan! 

Dan jika baris dan kolom saat ini disorot saat sel aktif bergerak melintasi lembar? Semacam pemilihan koordinat seperti ini:

Lebih baik dari penguasa, kan?

Ada beberapa cara dari berbagai kompleksitas untuk mengimplementasikan ini. Setiap metode memiliki pro dan kontra. Mari kita lihat mereka secara detail.

Metode 1. Jelas. Makro yang menyoroti baris dan kolom saat ini

Cara paling jelas untuk memecahkan masalah kita "di dahi" - kita membutuhkan makro yang akan melacak perubahan dalam pemilihan pada lembar dan memilih seluruh baris dan kolom untuk sel saat ini. Juga diinginkan untuk dapat mengaktifkan dan menonaktifkan fungsi ini jika perlu, sehingga pemilihan berbentuk silang seperti itu tidak mencegah kita memasukkan, misalnya, formula, tetapi hanya berfungsi ketika kita melihat daftar untuk mencari yang diperlukan informasi. Ini membawa kita ke tiga makro (pilih, aktifkan, dan nonaktifkan) yang perlu ditambahkan ke modul sheet.

Buka lembar dengan tabel di mana Anda ingin mendapatkan pilihan koordinat seperti itu. Klik kanan pada tab lembar dan pilih perintah dari menu konteks Sumber teks (Kode sumber).Jendela Editor Visual Basic akan terbuka. Salin teks dari ketiga makro ini ke dalamnya:

Dim Coord_Selection As Boolean 'Variabel global untuk seleksi aktif/nonaktif Sub Selection_On() 'Macro on selection Coord_Selection = True End Sub Selection_Off() 'Macro off selection Coord_Selection = False End Sub 'Prosedur utama yang melakukan seleksi Private Sub Worksheet_SelectionChange(ByVal Target As Range) Redupkan WorkRange As Range Jika Target.Cells.Count > 1 Kemudian Exit Sub 'jika lebih dari 1 sel dipilih, keluar If Coord_Selection = False Kemudian Exit Sub 'jika pilihan tidak aktif, keluar Application.ScreenUpdating = False Set WorkRange = Range (" A6:N300") 'alamat rentang kerja di mana pilihan terlihat

Ubah alamat rentang kerja menjadi milik Anda – dalam rentang inilah pilihan kami akan berfungsi. Kemudian tutup Editor Visual Basic dan kembali ke Excel.

Tekan pintasan keyboard ALT + F8untuk membuka jendela dengan daftar makro yang tersedia. Makro Pilihan_Aktif, seperti yang Anda duga, termasuk pemilihan koordinat pada lembar saat ini, dan makro Pilihan_Mati - mematikannya. Di jendela yang sama, dengan mengklik tombol parameter (Pilihan) Anda dapat menetapkan pintasan keyboard ke makro ini untuk peluncuran yang mudah.

Keuntungan dari metode ini:

  • relatif mudah implementasinya
  • seleksi – operasi tidak berbahaya dan tidak mengubah konten atau pemformatan sel lembar dengan cara apa pun, semuanya tetap apa adanya

Kekurangan dari metode ini:

  • pemilihan seperti itu tidak berfungsi dengan benar jika ada sel yang digabungkan pada lembar – semua baris dan kolom yang termasuk dalam gabungan dipilih sekaligus
  • jika Anda secara tidak sengaja menekan tombol Hapus, maka tidak hanya sel yang aktif akan dihapus, tetapi seluruh area yang dipilih, yaitu menghapus data dari seluruh baris dan kolom

Metode 2. Asli. SEL + Fungsi Pemformatan Bersyarat

Metode ini, meskipun memiliki beberapa kelemahan, menurut saya sangat elegan. Untuk mengimplementasikan sesuatu hanya dengan menggunakan alat Excel bawaan, minimal masuk ke pemrograman di VBA adalah aerobatik

Metode ini didasarkan pada penggunaan fungsi CELL, yang dapat memberikan banyak informasi berbeda pada sel tertentu – tinggi, lebar, nomor baris-kolom, format angka, dll. Fungsi ini memiliki dua argumen:

  • kata kode untuk parameter, seperti "kolom" atau "baris"
  • alamat sel yang ingin kita tentukan nilai parameter ini

Triknya adalah argumen kedua adalah opsional. Jika tidak ditentukan, maka sel aktif saat ini diambil.

Komponen kedua dari metode ini adalah pemformatan bersyarat. Fitur Excel yang sangat berguna ini memungkinkan Anda untuk memformat sel secara otomatis jika memenuhi kondisi yang ditentukan. Jika kita menggabungkan dua ide ini menjadi satu, kita mendapatkan algoritme berikut untuk mengimplementasikan pemilihan koordinat kita melalui pemformatan bersyarat:

  1. Kami memilih tabel kami, yaitu sel-sel di mana pemilihan koordinat harus ditampilkan di masa depan.
  2. Di Excel 2003 dan yang lebih lama, buka menu Format – Pemformatan Bersyarat – Rumus (Format — Pemformatan Bersyarat — Rumus). Di Excel 2007 dan yang lebih baru – klik pada tab Beranda (Rumah)tombol Pemformatan Bersyarat – Buat Aturan (Pemformatan Bersyarat — Buat Aturan) dan pilih jenis aturan Gunakan rumus untuk menentukan sel mana yang akan diformat (Gunakan rumus)
  3. Masukkan rumus untuk pemilihan koordinat kami:

    =OR(SEL(“baris”)=ROW(A2),SEL(“kolom”)=KOLOM(A2))

    =OR(SEL(«baris»)=ROW(A1),SEL(«kolom»)=COLUMN(A1))

    Rumus ini memeriksa untuk melihat apakah nomor kolom setiap sel dalam tabel sama dengan nomor kolom sel saat ini. Begitu juga dengan kolom. Jadi, hanya sel-sel yang memiliki nomor kolom atau nomor baris yang cocok dengan sel saat ini yang akan diisi. Dan ini adalah pemilihan koordinat berbentuk silang yang ingin kita capai.

  4. Klik tombol Kerangka (format) dan atur warna isian.

Semuanya hampir siap, tetapi ada satu nuansa. Faktanya adalah bahwa Excel tidak menganggap perubahan dalam pemilihan sebagai perubahan data pada lembar. Dan, sebagai hasilnya, ini tidak memicu penghitungan ulang rumus dan pewarnaan ulang pemformatan bersyarat hanya ketika posisi sel aktif berubah. Oleh karena itu, mari tambahkan makro sederhana ke modul sheet yang akan melakukan ini. Klik kanan pada tab lembar dan pilih perintah dari menu konteks Sumber teks (Kode sumber).Jendela Editor Visual Basic akan terbuka. Salin teks makro sederhana ini ke dalamnya:

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

Sekarang, ketika pilihan berubah, proses penghitungan ulang rumus dengan fungsi akan diluncurkan SEL dalam pemformatan bersyarat dan membanjiri baris dan kolom saat ini.

Keuntungan dari metode ini:

  • Pemformatan bersyarat tidak merusak pemformatan tabel khusus
  • Opsi pemilihan ini berfungsi dengan benar dengan sel yang digabungkan.
  • Tidak ada risiko menghapus seluruh baris dan kolom data pada klik yang tidak disengaja Delete.
  • Makro digunakan secara minimal

Kekurangan dari metode ini:

  • Rumus untuk pemformatan bersyarat harus dimasukkan secara manual.
  • Tidak ada cara cepat untuk mengaktifkan/menonaktifkan pemformatan seperti itu – selalu diaktifkan hingga aturan dihapus.

Metode 3. Optimal. Pemformatan Bersyarat + Makro

Emas berarti. Kami menggunakan mekanisme untuk melacak pemilihan pada lembar menggunakan makro dari metode-1 dan menambahkan penyorotan aman ke dalamnya menggunakan pemformatan bersyarat dari metode-2.

Buka lembar dengan tabel di mana Anda ingin mendapatkan pilihan koordinat seperti itu. Klik kanan pada tab lembar dan pilih perintah dari menu konteks Sumber teks (Kode sumber).Jendela Editor Visual Basic akan terbuka. Salin teks dari ketiga makro ini ke dalamnya:

Redupkan 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) Redupkan WorkRange As Range, CrossRange As Range Set WorkRange = Range("A7:N300") 'адрес абочего диапазона аблицей If Target.Count > 1 Kemudian Exit Sub If Coord_Selection = False Then WorkRange.FormatConditions.Delete Exit Sub End If Application.ScreenUpdating = False If Not Intersect(Target, WorkRange) Is Nothing Kemudian Set CrossRange(Target, WorkRange) WorkRange, Union(Target.EntireRow, Target.EntireColumn)) WorkRange.FormatConditions.Delete CrossRange.FormatConditions.Add Type:=xlExpression, Formula1:="=1" CrossRange.FormatConditions(1).Interior.ColorIndex = 33 Target.FormatConditions .Hapus End If End Sub

Jangan lupa untuk mengubah alamat rentang kerja ke alamat tabel Anda. Tutup Editor Visual Basic dan kembali ke Excel. Untuk menggunakan makro yang ditambahkan, tekan pintasan keyboard ALT + F8  dan lanjutkan dengan cara yang sama seperti metode 1. 

Metode 4. Cantik. IkutiCellPointer add-on

Excel MVP Jan Karel Pieterse dari Belanda memberikan add-on gratis di situs webnya IkutiCellPointer(36Kb), yang memecahkan masalah yang sama dengan menggambar garis panah grafik menggunakan makro untuk menyorot baris dan kolom saat ini:

 

Solusi yang bagus. Bukan tanpa gangguan di beberapa tempat, tapi pasti patut dicoba. Unduh arsip, buka kemasannya ke disk dan instal add-on:

  • di Excel 2003 dan yang lebih lama – melalui menu Layanan – Pengaya – Ikhtisar (Alat — Add-In — Jelajahi)
  • di Excel 2007 dan yang lebih baru, melalui File – Opsi – Pengaya – Buka – Jelajahi (File — Opsi Excel — Add-In — Buka — Telusuri)

  • Apa itu makro, di mana menyisipkan kode makro di Visual Basic

 

Tinggalkan Balasan