Dalam tugas sehari-hari, penandaan warna adalah metode populer untuk membedakan dan menyorot data penting dengan cepat. Namun, bagaimana cara kita menghitung atau menjumlahkan data sel berdasarkan warna tertentu (warna isian, warna font, format kondisional)? Secara default, Excel tidak menawarkan fitur langsung untuk menghitung atau menjumlahkan berdasarkan warna. Meski demikian, dengan beberapa trik dan cara tidak langsung, kita tetap bisa mencapainya. Artikel ini akan membahas cara menghitung atau menjumlahkan data berdasarkan warna.

Hitung dan jumlahkan sel berdasarkan warna latar belakang

• Dengan menggunakan Fungsi Buatan Pengguna
• Dengan menggunakan fitur canggih – Kutools for Excel
• Dengan menggunakan Filter dan SUBTOTAL

Menghitung dan menjumlahkan sel berdasarkan warna font

• Dengan menggunakan Fungsi Buatan Pengguna
• Dengan menggunakan fitur yang mudah – Kutools for Excel

Menghitung dan menjumlahkan sel berdasarkan warna pemformatan bersyarat

• Dengan menggunakan kode VBA
• Dengan menggunakan fitur pintar – Kutools for Excel

Video: Menghitung dan menjumlahkan sel berdasarkan warna

Hitung dan jumlahkan sel berdasarkan warna latar belakang

Misalnya, jika Anda memiliki rentang data yang nilainya diisi dengan warna latar belakang berbeda seperti yang ditunjukkan pada gambar di bawah. Untuk menghitung atau menjumlahkan sel berdasarkan warna tertentu, Excel tidak menawarkan fitur langsung untuk menghitung atau menjumlahkan sel berdasarkan warna latar belakangnya. Namun, dengan sedikit kecerdikan dan beberapa teknik praktis, Anda dapat menyelesaikan tugas ini. Mari jelajahi beberapa metode berguna di bagian ini.

Hitung dan jumlahkan sel berdasarkan warna latar belakang dengan Fungsi Buatan Pengguna

Di sini, kami akan menunjukkan kepada Anda cara membuat dan menggunakan Fungsi Buatan Pengguna untuk menyelesaikan tugas ini di Excel. Silakan lakukan dengan langkah-langkah berikut:

Langkah 1: Buka editor modul VBA dan salin kodenya

1. Tekan Alt + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.
2. Di jendela yang terbuka, klik Menyisipkan > Modul untuk membuat modul kosong baru.
3. Kemudian, salin dan tempel kode di bawah ini ke modul kosong.
   Kode VBA: Menghitung dan menjumlahkan sel berdasarkan warna latar belakang

   Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
   'Updateby Extendoffice
       Dim rCell As Range
       Dim lCol As Long
       Dim vResult As Double
       lCol = rColor.Interior.ColorIndex
       vResult = 0
       If SUM Then
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + rCell.Value
               End If
           Next rCell
       Else
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + 1
               End If
           Next rCell
       End If
       ColorFunction = vResult
   End Function
   

Langkah 2: Buat rumus untuk menghitung dan menjumlahkan sel berdasarkan warna latar belakang

Setelah menempelkan kode di atas, tutup jendela modul, lalu terapkan rumus berikut:

• Hitung sel berdasarkan warna latar belakang tertentu:
  Salin atau ketik rumus yang disediakan di bawah ini ke dalam sel yang Anda inginkan untuk hasilnya. Lalu, seret gagang isian ke bawah untuk mendapatkan hasil lainnya. Lihat tangkapan layar:
  

  =colorfunction(G2,$B$2:$E$12,FALSE) 

  Note: Dalam rumus ini, G2 adalah sel referensi dengan warna latar belakang spesifik yang ingin Anda cocokkan; $B$2:$E$12 adalah rentang di mana Anda ingin menghitung jumlah sel warna G2; SALAH digunakan untuk menghitung sel dengan warna yang serasi.
  
• Jumlahkan sel berdasarkan warna latar belakang tertentu:
  Salin atau ketik rumus yang disediakan di bawah ini ke dalam sel yang Anda inginkan untuk hasilnya. Lalu, seret gagang isian ke bawah untuk mendapatkan hasil lainnya. Lihat tangkapan layar:
  

  =colorfunction(G2,$B$2:$E$12,TRUE)  

  Note: Dalam rumus ini, G2 adalah sel referensi dengan warna latar belakang spesifik yang ingin Anda cocokkan; $B$2:$E$12 adalah rentang di mana Anda ingin menghitung jumlah sel warna G2; BENAR digunakan untuk menjumlahkan sel dengan warna senada.
  

Hitung dan jumlahkan sel berdasarkan warna latar belakang dengan fitur canggih

Bagi mereka yang belum terbiasa dengan pemrograman, VBA bisa terlihat cukup rumit. Di sini, kami akan memperkenalkan alat yang ampuh - Kutool untuk Excel, nya Hitung dengan Warna Fitur ini memungkinkan Anda menghitung dengan mudah (menghitung, menjumlahkan, rata-rata, dll.) berdasarkan warna latar belakang hanya dalam beberapa klik. Mengesankan, Hitung dengan Warna Fitur ini lebih dari sekadar warna latar belakang – fitur ini juga dapat membedakan dan menghitung berdasarkan warna font dan pemformatan bersyarat.

Setelah mengunduh dan menginstal Kutools untuk Excel, pertama, pilih rentang data yang ingin Anda hitung atau jumlahkan sel berdasarkan warna latar belakang tertentu. Selanjutnya, navigasikan ke Kutools Ditambah Dan pilihlah Hitung dengan Warna.

Dalam majalah Hitung dengan Warna kotak dialog, harap tentukan operasinya:

1. Pilih Pemformatan standar dari Metode warna daftar drop-down;
2. Menentukan Latar Belakang dari Jenis hitung daftar drop-down, dan Anda dapat melihat pratinjau hasil statistik untuk setiap warna latar belakang di kotak dialog;
3. Terakhir, klik Buat laporan untuk mengekspor hasil terhitung ke buku kerja baru.

Hasil:

Sekarang Anda akan mendapatkan buku kerja baru dengan statistik. Lihat tangkapan layar:

Hitung dan jumlahkan sel berdasarkan warna latar belakang dengan fungsi Filter dan SUBTOTAL

Misalkan kita memiliki tabel penjualan buah seperti gambar di bawah ini, dan kita akan menghitung atau menjumlahkan sel berwarna di dalamnya Jumlah kolom.

Langkah 1: Terapkan fungsi SUBTOTAL

Pilih sel kosong untuk memasukkan fungsi SUBTOTAL.

• Untuk menghitung semua sel dengan warna latar belakang yang sama, silakan masukkan rumus:

  =SUBTOTAL(102, F2:F16)

• Untuk menjumlahkan semua sel dengan warna latar belakang yang sama, silakan masukkan rumusnya;

  =SUBTOTAL(109, F2:F16)

• Note: pada rumus di atas, 102 mewakili untuk menghitung nilai numerik dalam daftar yang difilter sambil mengecualikan sel tersembunyi; 109 mewakili untuk menjumlahkan nilai dalam daftar yang difilter tidak termasuk sel tersembunyi; F2: F16 adalah kisaran di mana hitungan atau jumlah akan dihitung.
• 

Langkah 2: Filter sel berdasarkan warna tertentu

1. Pilih header tabel, dan klik Data > Filter. Lihat tangkapan layar:
   
2. klik Filter icon  di sel header Jumlah kolom, dan klik Filter dengan Warna dan warna yang ditentukan akan dihitung secara berurutan. Lihat tangkapan layar:
   

Hasil:

Setelah difilter, rumus SUBTOTAL secara otomatis menghitung dan menjumlahkan sel berwarna di dalamnya Jumlah kolom. Lihat tangkapan layar:

Menghitung dan menjumlahkan sel berdasarkan warna font

Ingin menghitung atau menjumlahkan sel berdasarkan warna fontnya di Excel? Katakanlah Anda memiliki data, seperti pada tangkapan layar yang diberikan, dengan sel yang berisi teks berwarna merah, biru, oranye, dan hitam. Excel tidak membuat ini mudah secara default. Tapi jangan khawatir! Di bagian ini, kami akan menunjukkan beberapa trik sederhana untuk melakukan hal itu.

Hitung dan jumlahkan sel berdasarkan warna font dengan User Defined Function

Untuk menghitung dan menjumlahkan sel dengan warna font tertentu, Fungsi Buatan Pengguna berikut dapat membantu Anda menyelesaikan tugas ini. Silakan lakukan dengan langkah-langkah berikut:

Langkah 1: Buka editor modul VBA dan salin kodenya

1. Tekan Alt + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.
2. Di jendela yang terbuka, klik Menyisipkan > Modul untuk membuat modul kosong baru.
3. Kemudian, salin dan tempel kode di bawah ini ke modul kosong.
   Kode VBA: Menghitung dan menjumlahkan sel berdasarkan warna font

   Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
   'Updateby Extendoffice
       Application.Volatile
       Dim rng As Range
       Dim xTotal As Double
       Dim xCount As Double
       xTotal = 0
       xCount = 0
       For Each rng In pRange1
           If rng.Font.Color = pRange2.Font.Color Then
               If UCase(FunctionType) = "SUM" Then
                   xTotal = xTotal + rng.Value
               ElseIf UCase(FunctionType) = "COUNT" Then
                   xCount = xCount + 1
               End If
           End If
       Next
       If UCase(FunctionType) = "SUM" Then
           ProcessByFontColor = xTotal
       ElseIf UCase(FunctionType) = "COUNT" Then
           ProcessByFontColor = xCount
       Else
           ProcessByFontColor = CVErr(xlErrValue)
       End If
   End Function
   

Langkah 2: Buat rumus untuk menghitung dan menjumlahkan sel berdasarkan warna font

Setelah menempelkan kode di atas, tutup jendela modul, lalu terapkan rumus berikut:

• Hitung sel berdasarkan warna font tertentu:
  Salin atau ketik rumus yang disediakan di bawah ini ke dalam sel yang Anda inginkan untuk hasilnya. Lalu, seret gagang isian ke bawah untuk mendapatkan hasil lainnya. Lihat tangkapan layar:
  

  =ProcessByFontColor($B$2:$E$12,G2, "COUNT")

  Note: Dalam rumus ini, G2 adalah sel referensi dengan warna font tertentu yang ingin Anda cocokkan; $B$2:$E$12 adalah rentang di mana Anda ingin menghitung jumlah sel warna G2.
  
• Jumlahkan sel berdasarkan warna font tertentu:
  Salin atau ketik rumus yang disediakan di bawah ini ke dalam sel yang Anda inginkan untuk hasilnya. Lalu, seret gagang isian ke bawah untuk mendapatkan hasil lainnya. Lihat tangkapan layar:
  

  =ProcessByFontColor($B$2:$E$12,G2, "SUM")  

  Note: Dalam rumus ini, G2 adalah sel referensi dengan warna font tertentu yang ingin Anda cocokkan; $B$2:$E$12 adalah rentang di mana Anda ingin menghitung jumlah sel warna G2.
  

Menghitung dan menjumlahkan sel berdasarkan warna font dengan fitur yang mudah

Ingin menghitung atau menjumlahkan nilai sel dengan mudah di Excel berdasarkan warna font? Menyelami Kutools untuk Excel's Hitung dengan Warna fitur! Dengan alat pintar ini, menghitung dan menjumlahkan sel berdasarkan warna font tertentu menjadi mudah. Temukan caranya Kutools dapat mengubah pengalaman Excel Anda.

Setelah mengunduh dan menginstal Kutools untuk Excel, pertama, pilih rentang data yang ingin Anda hitung atau jumlahkan sel berdasarkan warna font tertentu. Lalu klik Kutools Ditambah > Hitung dengan Warna untuk membuka Hitung dengan Warna kotak dialog.

Dalam majalah Hitung dengan Warna kotak dialog, harap tentukan operasinya:

1. Pilih Pemformatan standar dari Metode warna daftar drop-down;
2. Menentukan Font dari Jenis hitung daftar drop-down, dan Anda dapat melihat pratinjau hasil statistik untuk setiap warna font di kotak dialog;
3. Terakhir, klik Buat laporan untuk mengekspor hasil terhitung ke buku kerja baru.

Hasil:

Sekarang, Anda memiliki buku kerja baru yang menampilkan statistik terperinci berdasarkan warna font. Lihat tangkapan layar:

Menghitung dan menjumlahkan sel berdasarkan warna pemformatan bersyarat

Di Excel, Anda biasanya menggunakan Pemformatan Bersyarat untuk menerapkan warna tertentu ke sel yang memenuhi kriteria tertentu, sehingga membuat visualisasi data menjadi intuitif. Namun bagaimana jika Anda perlu menghitung atau menjumlahkan sel yang diformat khusus tersebut? Meskipun Excel tidak menawarkan cara langsung untuk melakukan hal ini, berikut adalah cara untuk mengatasi batasan ini.

Hitung dan jumlahkan sel yang diformat secara kondisional dengan kode VBA

Menghitung dan menjumlahkan sel yang diformat secara kondisional di Excel tidak mudah menggunakan fungsi bawaan. Namun, Anda dapat menyelesaikan tugas ini menggunakan kode VBA. Mari kita bahas bagaimana Anda dapat menggunakan VBA untuk ini:

Langkah 1: Buka editor modul VBA dan salin kodenya

1. Tekan Alt + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.
2. Di jendela yang terbuka, klik Menyisipkan > Modul untuk membuat modul kosong baru.
3. Kemudian, salin dan tempel kode di bawah ini ke modul kosong.
   Kode VBA: Menghitung dan menjumlahkan sel berdasarkan warna pemformatan bersyarat

   Sub SumCountByConditionalFormat()
   'Updateby Extendoffice
       Dim sampleColor As Range
       Dim selectedRange As Range
       Dim cell As Range
       Dim countByColor As Long
       Dim sumByColor As Double
       Dim refColor As Long
       Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                "Kutools for Excel", _
                                                Type:=8)
       If selectedRange Is Nothing Then Exit Sub
       Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                              "Kutools for Excel", _
                                              Type:=8)
       If Not sampleColor Is Nothing Then
           refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
           For Each cell In selectedRange
               If cell.DisplayFormat.Interior.color = refColor Then
                   countByColor = countByColor + 1
                   sumByColor = sumByColor + cell.Value
               End If
           Next cell
           MsgBox "Count: " & countByColor & vbCrLf & _
                  "Sum: " & sumByColor, _
                  vbInformation, "Results based on Conditional Format Color"
       End If
   End Sub
   

Langkah 2: Jalankan kode VBA ini

1. Setelah menempelkan kode, tekan F5 kunci untuk menjalankan kode ini, kotak prompt akan muncul, silakan pilih rentang data di mana Anda ingin menghitung dan menjumlahkan sel berdasarkan pemformatan bersyarat. Lalu klik OK, Lihat tangkapan layar:
   
2. Di kotak prompt lainnya, pilih warna pemformatan bersyarat tertentu yang ingin Anda hitung dan jumlahkan, lalu klik OK tombol, lihat tangkapan layar:
   

Hasil:

Sekarang, hasilnya, yang mencakup jumlah dan jumlah sel dengan warna pemformatan bersyarat yang ditentukan, akan ditampilkan di kotak yang muncul. Lihat tangkapan layar:

Hitung dan jumlahkan sel yang diformat secara kondisional dengan fitur cerdas

Jika Anda mencari metode cepat dan mudah lainnya untuk menghitung dan menjumlahkan sel yang diformat secara kondisional, Kutools untuk Excel adalah solusi tepat Anda. Dia Hitung dengan Warna fitur dapat menyelesaikan tugas ini hanya dalam beberapa klik. Selami untuk mengetahui efisiensi dan presisi yang dapat dibawa Kutools ke alur kerja Anda.

Setelah mengunduh dan menginstal Kutools untuk Excel, pertama, pilih rentang data yang ingin Anda hitung atau jumlahkan selnya berdasarkan warna pemformatan bersyarat tertentu. Lalu klik Kutools Ditambah > Hitung dengan Warna untuk membuka Hitung dengan Warna kotak dialog.

Dalam majalah Hitung dengan Warna kotak dialog, harap tentukan operasinya:

1. Pilih Pemformatan bersyarat dari Metode warna daftar drop-down;
2. Menentukan Latar Belakang dari Jenis hitung daftar drop-down, dan Anda dapat melihat pratinjau hasil statistik untuk setiap kondisi warna pemformatan di kotak dialog;
3. Terakhir, klik Buat laporan untuk mengekspor hasil terhitung ke buku kerja baru.

Hasil:

Sekarang, Anda memiliki buku kerja baru yang menampilkan statistik terperinci berdasarkan warna pemformatan bersyarat. Lihat tangkapan layar:

Artikel Terkait: