Menghitung sel dengan warna tertentu di Excel menggunakan VBA jauh lebih mudah dilakukan daripada harus menghitung manual atau menggunakan conditional formatting. Contoh sederhananya mungkin Anda ingin melacak jumlah sel yang memiliki warna latar belakang tertentu untuk keperluan analisis data atau presentasi.
Dalam artikel ini, kita akan mempelajari cara Menghitung sel dengan warna tertentu di Excel menggunakan fungsi kustom di Excel yakni menggunakan VBA. Tenang saja, code atau sintaks yang digunakan pun cukup sederhana kok. Kita mulai saja ya..
Membuat Tabel Sel Warna
Sebelum mulai menulis kode VBA-nya biar lebih mudah kita buat terlebih dahulu tabel yang berisi warna tertentu yang nantinya akan kita hitung dan juga angka-angka yang nanti akan kita jumlahkan. Contoh tabelnya seperti ini:
Menulis Kode VBA untuk Fungsi Kustom
Pertama, kita perlu menulis kode VBA yang akan digunakan sebagai fungsi kustom di Excel untuk Menghitung sel dengan warna tertentu di Excel. Fungsi ini akan menerima tiga argumen: sel referensi untuk warna acuan, rentang sel yang akan diperiksa, dan argumen opsional untuk menentukan apakah ingin menjumlahkan nilai sel-sel yang cocok. Berikut adalah kode VBA-nya:
[sourcecode language=”plain”]Option Explicit
Function FungsiWarna(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
FungsiWarna = vResult
End Function[/sourcecode]
Langkah berikutnya adalah menambahkan Kode VBA ke Editor VBA di Excel. Caranya adalah sebagai berikut:
- Buka Excel dan tekan tombol Alt + F11 untuk membuka Editor VBA.
- Di Editor VBA, pilih menu “Insert” dan pilih “Module” untuk membuat modul baru.
- Salin dan tempelkan kode VBA di atas ke modul baru yang telah dibuat.
Penjelasan Kode VBA
Option Explicit adalah pernyataan yang digunakan dalam kode VBA (Visual Basic for Applications) untuk menerapkan deklarasi variabel secara eksplisit. Ketika Option Explicit diaktifkan di awal modul atau kode, itu mengharuskan semua variabel dideklarasikan sebelum digunakan. Jika sebuah variabel tidak dideklarasikan secara eksplisit, VBA akan menampilkan kesalahan kompilasi.
Dalam kode yang diberikan, Option Explicit memastikan bahwa semua variabel yang digunakan dalam kode dideklarasikan secara eksplisit sebelum digunakan. Ini membantu mencegah kesalahan pengetikan atau penggunaan variabel yang tidak terdefinisi secara tidak sengaja, meningkatkan keandalan dan keberlakuan kode.
Berikut adalah penjelasan dari sisa kode:
Kode ini mendefinisikan sebuah fungsi bernama “FungsiWarna” yang mengambil tiga argumen: “rColor” (range yang mewakili satu sel), “rRange” (range yang mewakili kisaran sel), dan argumen opsional “SUM” (nilai boolean).
Kode ini mendeklarasikan dua variabel: “rCell” sebagai variabel Range, digunakan untuk mengulang setiap sel dalam range yang ditentukan. “lCol” sebagai variabel Long, digunakan untuk menyimpan indeks warna dari range “rColor”. Sementara baris “lCol = rColor.Interior.ColorIndex” mengassign indeks warna latar belakang (interior) dari range “rColor” ke variabel “lCol”.
Kode kemudian memeriksa nilai argumen “SUM”. Jika diatur ke True, kode memasuki blok “If SUM = True Then”. Blok ini mengulang setiap sel dalam range “rRange” dan memeriksa apakah indeks warna interior sel tersebut cocok dengan indeks warna yang disimpan di “lCol”. Jika cocok, menggunakan fungsi WorksheetFunction.SUM untuk menambahkan nilai sel tersebut ke variabel “vResult”.
Jika argumen “SUM” bukan True (yaitu False atau tidak diberikan), kode masuk ke blok “Else”. Blok ini melakukan iterasi serupa melalui setiap sel dalam range “rRange” dan menambahkan 1 ke variabel “vResult” jika indeks warna interior cocok dengan indeks warna yang disimpan di “lCol”.
Akhirnya, kode mengassign nilai “vResult” ke fungsi “FungsiWarna”, sehingga mengembalikannya sebagai hasil dari fungsi.
Secara keseluruhan, fungsi “FungsiWarna” menghitung jumlah atau jumlah sel dalam range yang diberikan yang memiliki warna interior yang sama dengan sel yang ditentukan (“rColor”). Jenis perhitungan (jumlah atau jumlah) ditentukan oleh argumen opsional “SUM”, dengan perilaku default adalah jumlah.
Menghitung Sel dengan Warna Tertentu di Excel
Setelah menambahkan kode VBA ke Editor VBA, Anda dapat langsung menggunakan fungsi “FungsiWarna” di dalam lembar kerja Excel untuk Menghitung sel dengan warna tertentu di Excel. Berikut adalah contoh penggunaan fungsi tersebut:
Jika Anda ingin menampilkan hasil perhitungan maka pilih sel C14 kemudian ketik rumus berikut ini:
=FungsiWarna($B14;$B$2:$G$11;FALSE)
Dan hasilnya adalah sebagai berikut:
Jika Anda ingin menjumlahkan nilai sel-sel yang cocok, ubahlah “FALSE” menjadi “TRUE”. Maka hasilnya adalah sebagai berikut:
Penjelasan Rumus
Rumus Excel “=FungsiWarna($B14;$B$2:$G$11;FALSE)” mengacu pada fungsi “FungsiWarna” yang ditulis dalam kode VBA yang telah Anda berikan sebelumnya. Fungsi ini digunakan untuk menghitung jumlah sel dalam rentang “$B$2:$G$11” yang memiliki warna latar belakang (interior) yang sama dengan sel “$B14”.
Pada rumus ini, argumen pertama “$B14” merupakan range yang mewakili satu sel, yaitu sel B14. Sel ini digunakan sebagai referensi untuk menentukan warna yang akan dicocokkan dalam rentang “$B$2:$G$11”.
Argumen kedua “$B$2:$G$11” adalah rentang sel di mana pencarian akan dilakukan. Fungsi akan memeriksa setiap sel dalam rentang ini untuk mencocokkan warna latar belakang dengan warna latar belakang sel “$B14”.
Argumen ketiga “FALSE” menandakan bahwa fungsi akan menghitung jumlah sel dengan warna latar belakang yang cocok, bukan menjumlahkan nilai-nilai dari sel-sel tersebut. Dalam kode VBA yang Anda berikan, jika argumen “SUM” diatur ke True, maka fungsi akan menjumlahkan nilai sel-sel yang cocok dengan warna latar belakang.
Jadi, rumus Excel “=FungsiWarna($B14;$B$2:$G$11;FALSE)” akan menghasilkan jumlah sel dalam rentang “$B$2:$G$11” yang memiliki warna latar belakang yang sama dengan sel “$B14”.
Penutup
Dengan menggunakan fungsi kustom ini, Anda dapat dengan mudah menghitung jumlah sel dengan warna tertentu di Excel. Ini dapat sangat berguna dalam menganalisis data atau membuat presentasi yang menarik secara visual. Dengan sedikit pengetahuan VBA, Anda dapat menyesuaikan kode ini untuk mencocokkan jenis dan kombinasi warna latar belakang yang berbeda.
Sekarang, Anda memiliki alat yang ampuh untuk menghitung jumlah sel dengan warna tertentu di Excel. Manfaatkanlah dan jelajahi berbagai kemungkinan yang ditawarkan oleh warna dalam lembar kerja Excel Anda.
Jika ada yang butuh file Excel ini silahkan download filenya dengan cara klik button download di bawah ini:
0 Komentar