Featured Post

Identifikasi Formula Dengan Conditional Formating

Bagaimanakah cara mengenali dan membedakan jenis konten sel, apakah berupa nilai statis atau sebuah formula? Cara pertama yang umumnya dig...

Tuesday, January 10, 2017

Menghitung Jumlah Sel Dengan Kriteria Warna

Menghitung sel sesuai warna
Bagaimana cara menghitung jumlah sel dengan kriteria warna tertentu? Pertanyaan ini kerap muncul di forum-forum diskusi mengenai excel.

Nampaknya sampai dengan saat ini, solusi terbaik untuk menghitung banyaknya sel yang berwarna tertentu adalah dengan menggunakan VBA.


Nah, dalam kesempatan ini je-xcel akan memberikan tips singkat bagaimana membuat UDF (User Defined Function) untuk menghitung banyaknya sel dalam sebuah range yang memiliki warna tertentu.

Code VBA untuk menghitung banyak sel dengan kriteria warna tergolong sangat singkat. Perhatikan contoh berikut:






Function hitungWarna(selAcuan As Range, rangeWarna As Range)
Dim sel As Range
For Each sel In rangeWarna
    If sel.Interior.Color = selAcuan.Interior.Color Then
        hitungWarna = hitungWarna + 1
    End If
Next
End Function


UDF hitungWarna dapat digunakan dalam lembar kerja excel, setelah code diatas diketik atau dicopy pada module standar dalam jendela VBA, caranya sebagai berikut
  • Masuk dulu ke dalam jendela VBA dengan cara tekan shorcut ALT+F11
window vba excel













  • Dari jendela VBA, klik menu Insert , dan klik module
cara membuat module baru di vba excel

  • Maka kemudian akan muncul module standar yang baru. Copy atau ketik code di atas ke dalam module:

cara menulis code ke module vba

  • Save as file excel dengan type  file Excel macro enable workbook atau Excel binary workbook
  • Sampai pada tahap ini, fungsi hitungWarna sudah dapat digunakan dalam lembar kerja excel.


Penjelasan fungsi:

Fungsi hitungWarna dapat digunakan untuk menghitung banyaknya sel didalam rangeWarna yang memiliki warna sama dengan selAcuan.


Contohnya:

selAcuan   = D1

rangeWarna = A1:B5

Maka untuk menghitung banyaknya sel di range A1:B5 yang memiliki warna yang sama dengan sel D1 dapat dituliskan rumusnya sebagai berikut:

=hitungwarna(D1,A1:B5)

Sesuai contoh gambar berikut:

contoh rumus hitung sel sesuai warna

Dari gambar tersebut, bisa disimpulkan bahwa fungsi hitungWarna berhasil menghitung banyaknya warna yang sama dengan sel D1 (kuning) pada range A1:B5.  (dalam contoh ada 3 warna)


Demikian tips singkat bagaimana menggunakan code VBA untuk menghitung banyaknya sel dengan kriteria warna.

Semoga bermanfaat.


Baca juga artikel belajar excel lainnya:






24 comments:

  1. Thanks a lot, Gan
    fungsi berjalan mulus.

    ReplyDelete
  2. terima kasih gan, fungsi nya oke punyaa

    ReplyDelete
  3. gan, mau minta pencerahan nih. gimana ya caranya klo mau menghitung jumlah huruf yang warnanya berbeda-beda ? klo contoh yang diatas kan buat cell yang berwarna, klo hurufnya yang berwarna gimana ya gan ? mohon pencerahannya ya. terima kasih

    ReplyDelete
    Replies
    1. Code cell.interior.color
      Ganti dengan cell.font.color

      Delete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Gimana caranya menjumlahkan total angka dalam cell yg warna sama?
    td kan hanya menghitung. misal dala kolom merah ada angka 2 dan 3. biar bisa kejumlah 5. Terima kasih.

    ReplyDelete
    Replies
    1. Silahkan dimodifikasi:

      Function hitungWarna(selAcuan As Range, rangeWarna As Range)
      Dim sel As Range
      For Each sel In rangeWarna
      If sel.Interior.Color = selAcuan.Interior.Color Then
      hitungWarna = hitungWarna + sel.value
      End If
      Next
      End Function

      Delete
  6. Jika warna diganti, nilai tidak otomatis berubah. Bagaimana caranya? Thanks

    ReplyDelete
    Replies
    1. Bisa dikalkulasi secara manual dengan cara menekan tombol Ctrl + Alt + F9

      Delete
  7. Hasil yang dimunculkan oleh Modul VBA apa gak bisa cepet seperti pakai Sum atau count, saya test hasil tidak langsung berubah kalau rumus gak di cek lagi begitu berulang-ulang kan gak maksimal jg kerjanya bos,trus sy cobak test pakai warna yang dihasilkan dari hasil cek Duplicat Value Modul vGA gak bisa baca, mohon pencerahanya admin.....Terimakasih

    ReplyDelete
    Replies
    1. Memang benar om, excel tidak melakukan rekalkulasi ketika ada perubahan property cell (misalnya: warna).

      Solusinya harus kalkulasi manual, misalnya dengan tekan shortcut Ctrl + Shift + F9

      Bisa juga dgn menambahkan sedikit script pada modul worksheet untuk memaksa kalkusasi.


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


      Btw, script fungsi hitungWarna yg dicontohkan memang diperuntukan untuk menghitung warna yg dibuat secara langsung, bukan warna hasil conditional formating

      Delete
  8. This comment has been removed by the author.

    ReplyDelete
  9. Kalau warna yg dibuat dengan conditional gimana ya? Mohon pencerahannya 🙏🙏

    ReplyDelete
  10. saya coba kok ga berhasil ya Pak. hasil nya #NAME?

    ReplyDelete
    Replies
    1. Code vba nya sudah dipasang kak?
      Jika sudah dipasang tapi masih error, cek setting macro security nya, harus enable

      Delete
  11. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. Terima Kasih atas pengetahuannya....sangat bermanfaat

      Delete
  12. Kalau warna yg dibuat dengan conditional formatting gimana ya? Mohon pencerahannya

    ReplyDelete
  13. Bagamana jika misalkan pada masing cell dari A2 sd A100 kita beri warna 3 macam :
    jika warna antara cell A2 sd A100 ada warna merah keterangannya : belum di stamp
    jika warna antara cell A2 sd A100 ada warna Hijau keterangannya : belum di sign
    jika warna antara cell A2 sd A100 ada warna kuning keterangannya : sudah lengkap
    apakah bisa dengan macro, mohon solusinya, Terimakasih Banyak

    ReplyDelete
  14. saya sudah pakai cara d atas, tapi kenapa setiap saya close dan saya buka lg file nya, rumus nya tidak terbaca?

    ReplyDelete
  15. Pertanyaan sama, bgmn menghitung warna Cell hasil conditional formatting?
    Terimakasih buanyak penjelasannya...

    ReplyDelete
    Replies
    1. SAYA PUNYA PERMASALAHAN YANG SAMA
      APA MAS SUDAH PUNYA SOLUSI/JAWABANNYA ?

      Delete

Terimakasih sudah berkunjung dan membaca blog ini. Silahkan berkomentar.