Memahami Sel Excel vs. Fungsi Rentang di VBA

Excel sangat kuat. Jika Anda sering menggunakannya, Anda mungkin sudah tahu banyak trik menggunakan rumus atau pemformatan otomatis, tetapi manfaatkan Sel dan Jangkauan fungsi di VBA, Anda dapat meningkatkan analitik Excel Anda ke tingkat yang sama sekali baru.

Masalah dengan menggunakan fungsi Sel dan Rentang di VBA adalah bahwa pada tingkat lanjutan, kebanyakan orang mengalami kesulitan memahami bagaimana fungsi ini sebenarnya bekerja. Menggunakannya bisa sangat membingungkan. Inilah cara Anda dapat memanfaatkannya dengan cara yang mungkin tidak pernah Anda bayangkan.

Fungsi Sel

Fungsi Sel dan Jangkauan memungkinkan Anda memberi tahu skrip VBA Anda persis di mana pada lembar kerja Anda yang ingin Anda peroleh, atau tempatkan data. Perbedaan utama antara kedua sel adalah apa yang mereka rujuk.

Sel biasanya mereferensikan satu sel pada satu waktu, sementara Jangkauan referensi sekelompok sel sekaligus. Format untuk fungsi ini adalah Sel (baris, kolom) .

Ini mereferensikan setiap sel di seluruh lembar. Ini adalah satu contoh di mana fungsi Sel tidak mereferensikan satu sel:

Worksheets('Sheet1').Cells

Ini merujuk ke sel ketiga dari kiri, dari baris atas. Sel C1:

Worksheets('Sheet1').Cells(3)

Kode berikut mereferensikan sel D15:

Worksheets('Sheet1').Cells(15,4)

Jika Anda mau, Anda juga dapat mereferensikan sel D15 dengan 'Sel (15,'D')'---Anda diperbolehkan menggunakan huruf kolom.

Ada banyak fleksibilitas untuk dapat mereferensikan sel menggunakan angka untuk kolom dan sel, terutama dengan skrip yang dapat loop melalui sejumlah besar sel (dan melakukan perhitungan pada mereka) dengan sangat cepat. Kami akan membahasnya lebih detail di bawah.

Fungsi Rentang

Dalam banyak hal, fungsi Rentang jauh lebih kuat daripada menggunakan Sel, karena memungkinkan Anda mereferensikan satu sel, atau rentang sel tertentu, sekaligus. Anda tidak akan ingin mengulang fungsi Rentang, karena referensi untuk sel bukanlah angka (kecuali jika Anda menyematkan fungsi Sel di dalamnya).

Format untuk fungsi ini adalah Rentang (Sel #1, Sel #2) . Setiap sel dapat ditunjuk dengan nomor-huruf.

Mari kita lihat beberapa contoh.

mengapa pesan teks tidak terkirim?

Di sini, fungsi rentang mereferensikan sel A5:

Worksheets('Sheet1').Range('A5')

Di sini, fungsi rentang mereferensikan semua sel antara A1 hingga E20:

Worksheets('Sheet1').Range('A1:E20')

Seperti disebutkan di atas, Anda tidak harus menggunakan penetapan sel angka-huruf. Anda sebenarnya dapat menggunakan dua fungsi Sel di dalam fungsi Rentang untuk mengidentifikasi rentang pada lembar, seperti ini:

  
With Worksheets('Sheet1')  
 .Range(.Cells(1, 1), _  
 .Cells(20, 5))  
End With

Kode di atas mereferensikan rentang yang sama dengan fungsi Range('A1:E20'). Nilai dalam menggunakannya, adalah memungkinkan Anda untuk menulis kode yang bekerja secara dinamis dengan rentang menggunakan loops .

Sekarang setelah Anda memahami cara memformat fungsi Sel dan Rentang, mari selami bagaimana Anda dapat memanfaatkan fungsi-fungsi ini secara kreatif dalam kode VBA Anda.

Memproses Data Dengan Fungsi Sel

Fungsi Sel paling berguna saat Anda memiliki rumus kompleks yang ingin Anda lakukan di beberapa rentang sel. Rentang ini juga bisa ada di beberapa lembar.

Mari kita ambil contoh sederhana. Katakanlah Anda mengelola tim penjualan yang terdiri dari 11 orang, dan setiap bulan Anda ingin melihat kinerja mereka.

Anda mungkin memiliki Lembar1 yang melacak jumlah penjualan mereka, dan volume penjualan mereka.

Pada Lembar2 adalah tempat Anda melacak peringkat umpan balik mereka selama 30 hari terakhir dari klien perusahaan Anda.

Jika Anda ingin menghitung bonus pada lembar pertama menggunakan nilai dari dua lembar, ada beberapa cara untuk melakukannya. Anda bisa menulis rumus di sel pertama yang melakukan penghitungan menggunakan data di dua lembar dan menyeretnya ke bawah. Itu akan berhasil.

Alternatif untuk ini adalah membuat skrip VBA yang Anda picu untuk dijalankan setiap kali Anda membuka lembar, atau dipicu oleh tombol perintah pada lembar sehingga Anda dapat mengontrol kapan menghitung. Anda mungkin menggunakan skrip VBA untuk menarik semua data penjualan dari file eksternal.

Jadi mengapa tidak memicu perhitungan untuk kolom bonus di skrip yang sama saat itu?

Fungsi Sel dalam Aksi

Jika Anda belum pernah menulis VBA di Excel sebelumnya, Anda harus mengaktifkan item menu Pengembang. Untuk melakukannya, buka Mengajukan > Pilihan . Klik Sesuaikan Pita . Terakhir, pilih Pengembang dari panel kiri, Menambahkan ke panel kanan, dan pastikan kotak centang dipilih.

Sekarang, ketika Anda mengklik oke dan kembali ke lembar utama, Anda akan melihat opsi menu Pengembang.

Anda dapat menggunakan Memasukkan menu untuk memasukkan tombol perintah, atau cukup klik Lihat Kode untuk memulai pengkodean.

Dalam contoh ini kita akan membuat skrip berjalan setiap kali buku kerja dibuka. Untuk melakukan ini, cukup klik Lihat Kode dari menu pengembang, dan rekatkan fungsi baru berikut ke dalam jendela kode.

Private Sub Workbook_Open()  
End Sub

Jendela kode Anda akan terlihat seperti ini.

Sekarang Anda siap untuk menulis kode untuk menangani perhitungan. Dengan menggunakan satu putaran, Anda dapat melewati semua 11 karyawan, dan dengan fungsi Sel, tarik tiga variabel yang diperlukan untuk penghitungan.

Ingat fungsi Sel memiliki baris dan kolom sebagai parameter untuk mengidentifikasi setiap sel individu. Kami akan membuat 'x' baris, gunakan nomor untuk meminta data setiap kolom. Jumlah baris adalah jumlah karyawan, jadi ini akan dari 1 hingga 11. Pengidentifikasi kolom adalah 2 untuk Hitungan Penjualan, 3 untuk Volume Penjualan, dan 2 dari Lembar 2 untuk Skor Umpan Balik.

Perhitungan akhir menggunakan persentase berikut untuk menambahkan hingga 100 persen dari total skor bonus. Ini didasarkan pada jumlah penjualan yang ideal adalah 50, volume penjualan sebesar .000, dan skor umpan balik 10.

(Jumlah Penjualan/50) x 0,4
(Volume Penjualan/50.000) x 0,5
(Skor Umpan Balik/10) x 0,1

Pendekatan sederhana ini memberi karyawan penjualan bonus tertimbang. Untuk hitungan 50, volume .000, dan skor 10 --- mereka mendapatkan seluruh bonus maksimum untuk bulan itu. Namun apa pun di bawah sempurna pada faktor apa pun mengurangi bonus. Apa pun yang lebih baik dari ideal meningkatkan bonus.

Sekarang mari kita lihat bagaimana semua logika itu dapat dilakukan dalam skrip VBA pendek yang sangat sederhana:

Private Sub Workbook_Open()  
For x = 2 To 12  
Worksheets('Sheet1').Cells(x, 4) = (Worksheets('Sheet1').Cells(x, 2).Value / 50) * 0.4 _  
 + (Worksheets('Sheet1').Cells(x, 3).Value / 50000) * 0.5 _  
 + (Worksheets('Sheet2').Cells(x, 2).Value / 10) * 0.1 _  
Next x  
End Sub

Seperti inilah tampilan dari skrip ini.

cara melihat instagram tanpa akun

Jika Anda ingin kolom Bonus menunjukkan bonus dolar yang sebenarnya daripada persentase, Anda dapat mengalikannya dengan jumlah bonus maksimum. Lebih baik lagi, tempatkan jumlah itu di sel pada lembar lain, dan rujuk dalam kode Anda. Ini akan memudahkan untuk mengubah nilai nanti tanpa harus mengedit kode Anda.

Keindahan fungsi Sel adalah Anda dapat membangun logika yang cukup kreatif untuk menarik data dari banyak sel di banyak lembar yang berbeda, dan lakukan beberapa perhitungan yang cukup rumit dengan mereka.

Anda dapat melakukan segala macam tindakan pada sel menggunakan fungsi Sel --- hal-hal seperti membersihkan sel, mengubah pemformatan font, dan banyak lagi.

Untuk menjelajahi semua yang dapat Anda lakukan lebih lanjut, lihat Halaman Microsoft MSDN untuk objek Sel.

Memformat Sel Dengan Fungsi Rentang

Untuk mengulang melalui banyak sel satu per satu, fungsi Sel sempurna. Tetapi jika Anda ingin menerapkan sesuatu ke seluruh rentang sel sekaligus, fungsi Rentang jauh lebih efisien.

Satu kasus penggunaan untuk ini mungkin untuk memformat rentang sel menggunakan skrip, jika kondisi tertentu terpenuhi.

Misalnya, jika penghitungan semua volume penjualan di seluruh karyawan penjualan melebihi total 0.000, Anda ingin menyorot semua sel di kolom bonus dengan warna hijau untuk menandakan bahwa tim telah mendapatkan bonus tim tambahan.

Mari kita lihat bagaimana Anda bisa melakukannya dengan pernyataan JIKA .

Private Sub Workbook_Open()  
If Worksheets('Sheet1').Cells(13, 3).Value > 400000 Then  
 ActiveSheet.Range('D2:D12').Interior.ColorIndex = 4  
End If  
End Sub

Saat ini berjalan, jika sel melebihi tujuan tim, semua sel dalam rentang akan diisi dengan warna hijau.

Ini hanyalah salah satu contoh sederhana dari banyak tindakan yang dapat Anda lakukan pada grup sel menggunakan fungsi Range. Hal lain yang dapat Anda lakukan antara lain:

Terapkan garis besar di sekitar grup
Periksa ejaan teks di dalam rentang sel
Hapus, salin, atau potong sel
Cari melalui rentang dengan metode 'Temukan'
Lebih banyak

Pastikan untuk membaca Halaman Microsoft MSDN untuk objek Range untuk melihat semua kemungkinan.

Bawa Excel ke Tingkat Selanjutnya

Sekarang setelah Anda memahami perbedaan antara fungsi Sel dan Rentang, saatnya untuk membawa skrip VBA Anda ke tingkat berikutnya. Artikel Dann tentang menggunakan fungsi Menghitung dan Menambahkan di Excel akan memungkinkan Anda membuat skrip yang lebih canggih yang dapat mengakumulasi nilai di semua kumpulan data Anda dengan sangat cepat.

Dan jika Anda baru memulai dengan VBA di Excel, jangan lupa kami punya yang fantastis panduan pengantar untuk Excel VBA untukmu juga.

Membagikan Membagikan Menciak Surel Canon vs. Nikon: Merek Kamera Mana yang Lebih Baik?

Canon dan Nikon adalah dua nama besar di industri kamera. Tetapi merek mana yang menawarkan jajaran kamera dan lensa yang lebih baik?

Baca Selanjutnya Topik-topik yang berkaitan

Produktifitas
Pemrograman
Pemrograman Visual Basic
Microsoft Excel

Tentang Penulis Ryan Dub(942 Artikel Diterbitkan)

Ryan memiliki gelar BSc di bidang Teknik Elektro. Dia telah bekerja 13 tahun di bidang teknik otomasi, 5 tahun di bidang TI, dan sekarang menjadi Insinyur Aplikasi. Mantan Managing Editor MakeUseOf, dia berbicara di konferensi nasional tentang Visualisasi Data dan telah ditampilkan di TV dan radio nasional.

Berlangganan newsletter kami

Bergabunglah dengan buletin kami untuk kiat teknologi, ulasan, ebook gratis, dan penawaran eksklusif!

Klik di sini untuk berlangganan