Memahami Sel Excel vs Fungsi Julat dalam VBA

Memahami Sel Excel vs Fungsi Julat dalam VBA

Excel sangat hebat. Sekiranya anda banyak menggunakannya, anda mungkin sudah mengetahui banyak helah menggunakan formula atau pemformatan automatik, tetapi menggunakan Sel dan Julat berfungsi dalam VBA, anda dapat meningkatkan analisis Excel anda ke tahap yang baru.





Masalah dengan menggunakan fungsi Sel dan Rentang dalam VBA adalah bahawa pada tahap lanjutan, kebanyakan orang sukar memahami bagaimana fungsi ini sebenarnya berfungsi. Menggunakannya boleh membingungkan. Inilah cara anda dapat memanfaatkannya dengan cara yang mungkin tidak pernah anda bayangkan.



Fungsi Sel

Fungsi Sel dan Julat memberitahu anda skrip VBA anda tepat di mana di lembaran kerja anda ingin memperoleh, atau meletakkan data. Perbezaan utama antara dua sel adalah apa yang mereka rujuk.





Sel biasanya merujuk satu sel pada satu masa, sementara Julat merujuk sekumpulan sel sekaligus. Format untuk fungsi ini adalah Sel (baris, lajur) .

Ini merujuk setiap sel dalam keseluruhan helaian. Ini adalah satu contoh di mana fungsi Sel tidak merujuk kepada satu sel:



Worksheets('Sheet1').Cells

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

Worksheets('Sheet1').Cells(3)

Kod berikut merujuk sel D15:



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

Sekiranya anda mahu, anda juga boleh merujuk sel D15 dengan 'Sel (15,' D ')' --- anda dibenarkan menggunakan huruf lajur.

Ada banyak fleksibiliti untuk dapat merujuk sel menggunakan angka untuk lajur dan sel, terutama dengan skrip yang dapat gelung melalui sebilangan besar sel (dan melakukan pengiraan padanya) dengan cepat. Kami akan menerangkannya dengan lebih terperinci di bawah.



Fungsi Julat

Dalam banyak cara, fungsi Range jauh lebih hebat daripada menggunakan Sel, kerana ia memungkinkan anda merujuk kepada satu sel, atau sel tertentu, sekaligus. Anda tidak mahu melengkapkan fungsi Range, kerana rujukan untuk sel bukan nombor (kecuali jika anda menyematkan fungsi Sel di dalamnya).

Format untuk fungsi ini adalah Julat (Sel # 1, Sel # 2) . Setiap sel boleh ditentukan dengan nombor huruf.

Mari lihat beberapa contoh.

kesakitan adalah sebab utama untuk mencintai kenderaan itu sendiri. dalam Bahasa Inggeris

Di sini, fungsi julat merujuk sel A5:

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

Di sini, fungsi julat merujuk semua sel antara A1 hingga E20:

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

Seperti yang disebutkan di atas, anda tidak perlu menggunakan penugasan sel huruf-huruf. Anda sebenarnya boleh menggunakan dua fungsi Sel di dalam fungsi Range untuk mengenal pasti julat pada helaian, seperti ini:


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

Kod di atas merujuk kepada julat yang sama seperti fungsi Range ('A1: E20'). Nilai dalam menggunakannya, adalah membolehkan anda menulis kod yang berfungsi secara dinamik dengan julat menggunakan gelung.

Sekarang setelah anda memahami cara memformat fungsi Sel dan Rentang, mari kita selidiki bagaimana anda dapat menggunakan fungsi ini secara kreatif dalam kod VBA anda.

Memproses Data Dengan Fungsi Sel

Fungsi Sel sangat berguna apabila anda mempunyai formula kompleks yang ingin anda lakukan di pelbagai sel. Julat ini juga boleh wujud di beberapa helaian.

Mari kita ambil contoh mudah. Katakan anda menguruskan pasukan jualan dengan 11 orang, dan setiap bulan anda ingin melihat prestasi mereka.

Anda mungkin mempunyai Helaian1 yang mengesan jumlah penjualan mereka, dan jumlah penjualan mereka.

Dihidupkan Helaian2 adalah tempat anda mengesan penilaian maklum balas mereka selama 30 hari terakhir dari pelanggan syarikat anda.

Sekiranya anda ingin mengira bonus pada helaian pertama menggunakan nilai dari dua helaian, terdapat beberapa cara untuk melakukannya. Anda boleh menulis formula di sel pertama yang melakukan pengiraan menggunakan data merentasi dua helaian dan menyeretnya ke bawah. Itu akan berjaya.

Alternatif untuk ini adalah membuat skrip VBA yang boleh anda jalankan setiap kali anda membuka lembaran, atau dipicu oleh butang arahan pada helaian sehingga anda dapat mengawal ketika menghitung. Anda mungkin menggunakan skrip VBA untuk menarik semua data penjualan dari fail luaran pula.

Jadi mengapa tidak hanya mencetuskan pengiraan untuk lajur bonus dalam skrip yang sama pada masa itu?

Fungsi Sel Beraksi

Sekiranya anda belum pernah menulis VBA di Excel sebelum ini, anda perlu mengaktifkan item menu Pembangun. Untuk melakukan ini, pergi ke Fail > Pilihan . Klik pada Sesuaikan Pita . Akhirnya, pilih Pembangun dari panel kiri, Tambah ke panel kanan, dan pastikan kotak pilihan dipilih.

Sekarang, apabila anda mengklik okey dan kembali ke helaian utama, anda akan melihat pilihan menu Pembangun.

Anda boleh menggunakan Masukkan menu untuk memasukkan butang arahan, atau cukup klik Lihat Kod untuk memulakan pengekodan.

Dalam contoh ini kita akan membuat skrip berjalan setiap kali buku kerja dibuka. Untuk melakukan ini, klik sahaja Lihat Kod dari menu pembangun, dan tampal fungsi baru berikut ke dalam tetingkap kod.

Private Sub Workbook_Open()
End Sub

Tetingkap kod anda akan kelihatan seperti ini.

Sekarang anda sudah bersedia untuk menulis kod untuk menangani pengiraan. Dengan menggunakan satu gelung, anda dapat melangkah ke seluruh 11 pekerja, dan dengan fungsi Sel menarik tiga pemboleh ubah yang diperlukan untuk pengiraan.

Ingat fungsi Sel mempunyai baris dan lajur sebagai parameter untuk mengenal pasti setiap sel. Kami akan membuat baris 'x', menggunakan angka untuk meminta data setiap lajur. Bilangan baris adalah bilangan pekerja, jadi ini adalah dari 1 hingga 11. Pengecam lajur adalah 2 untuk Kiraan Jualan, 3 untuk Jumlah Jualan, dan 2 dari Lembaran 2 untuk Skor Maklum Balas.

Pengiraan terakhir menggunakan peratusan berikut untuk menambahkan hingga 100 peratus daripada jumlah skor bonus. Ini berdasarkan jumlah penjualan yang ideal adalah 50, jumlah penjualan sebanyak $ 50,000, dan skor maklum balas 10.

  • (Jumlah Jualan / 50) x 0.4
  • (Jumlah Jualan / 50,000) x 0,5
  • (Skor Maklum Balas / 10) x 0.1

Pendekatan sederhana ini memberi bonus kepada pekerja jualan. Untuk jumlah 50, jumlah $ 50,000, dan skor 10 --- mereka mendapat keseluruhan bonus maksimum untuk bulan tersebut. Walau apa pun yang kurang sempurna atas faktor apa pun akan mengurangkan bonus. Apa-apa yang lebih baik daripada yang ideal meningkatkan bonus.

Sekarang mari kita lihat bagaimana semua logik itu dapat dikeluarkan dalam skrip VBA pendek yang sangat mudah:

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 skrip ini.

cara melihat instagram tanpa akaun

Sekiranya anda ingin lajur Bonus menunjukkan bonus dolar sebenar dan bukan peratusannya, anda boleh menggandakannya dengan jumlah bonus maksimum. Lebih baik lagi, letakkan jumlah itu dalam sel pada helaian lain, dan rujuknya dalam kod anda. Ini akan memudahkan anda menukar nilai kemudian tanpa perlu mengedit kod anda.

Keindahan fungsi Sel adalah anda boleh membina logik yang cukup kreatif untuk menarik data banyak sel merentasi banyak helaian yang berbeza, dan melaksanakan beberapa helaian pengiraan yang cukup kompleks dengan mereka.

Anda boleh melakukan segala macam tindakan pada sel menggunakan fungsi Sel --- perkara seperti membersihkan sel, mengubah format fon, dan banyak lagi.

Untuk meneroka semua yang boleh anda lakukan lebih jauh, lihat Halaman Microsoft MSDN untuk objek Sel.

Memformat Sel Dengan Fungsi Julat

Untuk melancarkan banyak sel satu demi satu, fungsi Sel adalah sempurna. Tetapi jika anda ingin menerapkan sesuatu ke seluruh sel sekaligus, fungsi Range jauh lebih efisien.

Satu kasus penggunaan untuk ini mungkin adalah memformat berbagai sel menggunakan skrip, jika syarat tertentu dipenuhi.

Sebagai contoh, katakan jika jumlah keseluruhan penjualan di semua pekerja jualan melebihi $ 400,000, anda ingin menonjolkan semua sel di lajur bonus dengan warna hijau untuk menandakan bahawa pasukan telah memperoleh bonus pasukan tambahan.

Mari lihat bagaimana anda boleh melakukannya penyataan IF .

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

Apabila ini berjalan, jika sel melebihi matlamat pasukan, semua sel dalam julat akan diisi dengan warna hijau.

Ini hanyalah satu contoh mudah dari sekian banyak tindakan yang dapat anda lakukan pada kumpulan sel menggunakan fungsi Range. Perkara lain yang boleh anda lakukan termasuk:

  • Terapkan garis besar di seluruh kumpulan
  • Periksa ejaan teks dalam pelbagai sel
  • Kosongkan, salin, atau potong sel
  • Cari melalui julat dengan kaedah 'Cari'
  • Banyak lagi

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

Naik Excel ke Tahap Seterusnya

Sekarang setelah anda memahami perbezaan antara fungsi Sel dan Rentang, inilah masanya untuk meningkatkan skrip VBA anda ke tahap seterusnya. Artikel Dann mengenai penggunaan fungsi Menghitung dan Menambah di Excel akan membolehkan anda membina skrip yang lebih maju lagi yang dapat mengumpulkan nilai di semua set data anda dengan cepat.

Dan jika anda baru memulakan dengan VBA di Excel, jangan lupa kami mempunyai yang hebat panduan pengenalan kepada Excel VBA untuk anda juga.

Berkongsi Berkongsi Tweet E-mel Canon vs Nikon: Jenama Kamera mana yang lebih baik?

Canon dan Nikon adalah dua nama terbesar dalam industri kamera. Tetapi jenama mana yang menawarkan barisan kamera dan lensa yang lebih baik?

Baca Seterusnya Topik-topik yang berkaitan
  • Produktiviti
  • Pengaturcaraan
  • Pengaturcaraan Asas Visual
  • Microsoft Excel
Mengenai Pengarang Ryan Dube(942 Artikel Diterbitkan)

Ryan mempunyai ijazah BSc dalam Kejuruteraan Elektrik. Dia bekerja 13 tahun dalam kejuruteraan automasi, 5 tahun dalam bidang IT, dan sekarang adalah Jurutera Aplikasi. Bekas Editor Urusan MakeUseOf, dia bercakap di persidangan nasional mengenai Visualisasi Data dan telah dipaparkan di TV nasional dan radio.

Lagi Dari Ryan Dube

Langgan buletin kami

Sertailah buletin kami untuk mendapatkan petua, ulasan, ebook percuma, dan tawaran eksklusif!

Klik di sini untuk melanggan