xcel sangat kuat. Jika Anda menggunakannya banyak, Anda mungkin sudah tahu banyak trik menggunakan rumus atau autoformatting, tetapi memanfaatkan fungsi Sel dan Rentang di VBA, Anda dapat meningkatkan analytics Excel Anda ke tingkat yang baru.
Masalah dengan menggunakan fungsi Sel dan Rentang di VBA adalah bahwa pada tingkat lanjutan, kebanyakan orang memiliki kesulitan memahami bagaimana fungsi-fungsi ini benar-benar berfungsi. Menggunakannya bisa sangat membingungkan. Inilah cara Anda memanfaatkannya dengan cara yang mungkin tidak pernah Anda bayangkan.
4 Kesalahan yang Dapat Anda Hindari saat Memprogram Excel Macro dengan VBA
Fungsi Sel
Fungsi Sel dan Rentang memungkinkan Anda memberi tahu skrip VBA Anda tepat di mana pada lembar kerja Anda yang ingin Anda dapatkan, atau menempatkan data. Perbedaan utama antara dua sel adalah apa yang mereka rujuk.
Sel biasanya mereferensikan satu sel pada satu waktu, sementara Range merujuk sekelompok sel sekaligus. Format untuk fungsi ini adalah Sel (baris, kolom) .
Referensi ini setiap sel dalam seluruh lembaran. Ini adalah satu contoh di mana fungsi Sel tidak mereferensikan satu sel:
Worksheets("Sheet1").CellsReferensi ini sel ketiga dari kiri, dari baris atas. Sel C1:
Worksheets("Sheet1").Cells(3)Referensi kode berikut sel D15:
Worksheets("Sheet1").Cells(15,4)Jika Anda ingin, Anda juga bisa mereferensikan sel D15 dengan “Sel (15,” D “)” – Anda diizinkan untuk menggunakan huruf kolom.
Ada banyak fleksibilitas untuk dapat mereferensikan sel menggunakan nomor untuk kolom dan sel, terutama dengan skrip yang dapat mengulang melalui sejumlah besar sel (dan melakukan perhitungan pada mereka) dengan sangat cepat. Kita akan membahasnya lebih detail di bawah ini.
Fungsi Rentang
Dalam banyak hal, fungsi Range jauh lebih kuat daripada menggunakan Cells, karena memungkinkan Anda mereferensikan baik satu sel, atau rentang sel tertentu, sekaligus. Anda tidak akan ingin mengulang melalui fungsi Range, karena referensi untuk sel bukanlah angka (kecuali Anda menanamkan fungsi Cell di dalamnya).
Format untuk fungsi ini adalah Range (Cell # 1, Cell # 2) . Setiap sel dapat ditunjuk oleh nomor-huruf.
Mari kita lihat beberapa contoh.
Di sini, fungsi rentang mereferensikan sel A5:
Worksheets("Sheet1").Range("A5")Di sini, fungsi rentang merujuk semua sel antara A1 hingga E20:
Worksheets("Sheet1").Range("A1:E20")Seperti yang disebutkan di atas, Anda tidak perlu menggunakan penetapan sel nomor-huruf. Anda sebenarnya bisa menggunakan dua fungsi Sel di dalam fungsi Range untuk mengidentifikasi rentang pada sheet, seperti ini:
With Worksheets("Sheet1")
.Range(.Cells(1, 1), _
.Cells(20, 5))
End With
Kode di atas merujuk ke rentang yang sama dengan fungsi Range (“A1: E20”). Nilai dalam menggunakannya, adalah bahwa hal itu akan memungkinkan Anda untuk menulis kode yang secara dinamis bekerja dengan rentang menggunakan loop .
Sekarang setelah Anda memahami cara memformat fungsi Sel dan Rentang, mari selami bagaimana Anda dapat menggunakan fungsi-fungsi ini secara kreatif dalam kode VBA Anda.
Pengolahan Data Dengan Fungsi Sel
Fungsi sel paling berguna ketika Anda memiliki rumus kompleks yang ingin Anda lakukan di berbagai rentang sel. Rentang ini juga bisa ada di beberapa lembar.
Mari kita ambil contoh sederhana. Katakanlah Anda mengelola tim penjualan sebanyak 11 orang, dan setiap bulan Anda ingin melihat kinerja mereka.
Anda mungkin memiliki Sheet1 yang melacak jumlah penjualan mereka, dan volume penjualan mereka.
Pada Sheet2 adalah tempat Anda melacak rating 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 melakukan ini. Anda bisa menulis rumus di sel pertama yang melakukan perhitungan menggunakan data di dua lembar dan seret ke bawah. Itu akan berhasil.
Alternatif untuk ini adalah membuat skrip VBA yang Anda picu untuk dijalankan setiap kali Anda membuka sheet, atau dipicu oleh tombol perintah pada sheet sehingga Anda dapat mengontrol saat 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 pada waktu itu?
Fungsi Sel Beraksi
Jika Anda belum pernah menulis VBA di Excel sebelumnya, Anda harus mengaktifkan item menu Pengembang. Untuk melakukan ini, buka File > Opsi . Klik pada Customize Ribbon . Akhirnya, pilih Pengembang dari panel kiri, Tambahkan ke panel kanan, dan pastikan kotak centang dipilih.
Sekarang, ketika Anda mengklik OK dan kembali ke lembar utama, Anda akan melihat opsi menu Pengembang.
Anda dapat menggunakan menu Sisipkan untuk memasukkan tombol perintah, atau cukup klik Lihat Kode untuk memulai pengkodean.
Dalam contoh ini, kami akan membuat skrip berjalan setiap kali buku kerja dibuka. Untuk melakukan ini, cukup klik Lihat Kode dari menu pengembang, dan tempelkan fungsi baru berikut ke dalam jendela kode.
Private Sub Workbook_Open()
End SubJendela kode Anda akan terlihat seperti ini.
Sekarang Anda siap menulis kode untuk menangani perhitungan. Dengan menggunakan satu putaran, Anda dapat melangkah ke seluruh 11 karyawan, dan dengan fungsi Sel menarik tiga variabel yang diperlukan untuk perhitungan.
Ingat fungsi Sel memiliki baris dan kolom sebagai parameter untuk mengidentifikasi setiap sel individu. Kami akan membuat “x” baris, gunakan nomor untuk meminta data masing-masing kolom. Jumlah baris adalah jumlah karyawan, jadi ini akan dari 1 hingga 11. Pengidentifikasi kolom akan menjadi 2 untuk Sales Count, 3 untuk Volume Penjualan, dan 2 dari Sheet 2 untuk Skor Umpan Balik.
Perhitungan terakhir menggunakan persentase berikut untuk menambahkan hingga 100 persen dari total skor bonus. Ini didasarkan pada jumlah penjualan yang ideal adalah 50, volume penjualan sebagai $ 50.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 bonus pada karyawan penjualan. Untuk hitungan 50, volume $ 50.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 yang ideal meningkatkan bonus.
Sekarang mari kita lihat bagaimana semua logika itu dapat ditarik 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 output dari skrip ini.
Jika Anda ingin memiliki kolom Bonus menunjukkan bonus dolar yang sebenarnya daripada persentase, Anda dapat mengalikannya dengan jumlah bonus maksimum. Lebih baik lagi, letakkan jumlah itu di sel pada lembar lain, dan rujuk di dalam kode Anda. Ini akan mempermudah untuk mengubah nilainya nanti tanpa harus mengedit kode Anda.
Keindahan fungsi Cells adalah Anda dapat membangun beberapa logika yang cukup kreatif untuk menarik data dari banyak sel di berbagai lembaran berbeda, dan melakukan beberapa perhitungan yang cukup rumit dengan mereka.
Anda dapat melakukan berbagai tindakan pada sel menggunakan fungsi Sel — hal-hal seperti membersihkan sel, mengubah format font, dan banyak lagi.
Untuk menjelajahi semua yang dapat Anda lakukan lebih lanjut, periksa halaman Microsoft MSDN untuk objek Sel.
Memformat Sel Dengan Fungsi Jarak
Untuk perulangan 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 berbagai sel menggunakan skrip, jika kondisi tertentu terpenuhi.
Sebagai contoh, katakanlah jika penghitungan semua volume penjualan di seluruh karyawan penjualan melampaui total $ 400.000, Anda ingin menyoroti semua sel di kolom bonus berwarna hijau untuk menandakan bahwa tim telah mendapatkan bonus tim tambahan.
Mari kita lihat bagaimana Anda dapat melakukannya dengan pernyataan IF .
Sumber https://indoint.com/
0 comments:
Post a Comment