Cara menghitung dan menjumlahkan sel berdasarkan warna di Excel

Pada artikel ini Anda akan belajar cara menghitung sel dengan warna di Excel dan mendapatkan jumlah sel berwarna. Solusi ini berfungsi baik untuk sel yang diwarnai secara manual dan dengan pemformatan bersyarat. Anda juga akan belajar cara memfilter sel dengan beberapa warna di Excel 2010, Excel 2013, Excel 2016, dan Excel 2019.

Jika Anda secara aktif menggunakan beragam warna isi dan font di lembar kerja Excel Anda untuk membedakan antara berbagai jenis sel atau nilai, Anda mungkin ingin tahu berapa banyak sel yang disorot dalam warna tertentu. Nah, Jika nilai sel Anda berupa angka, Anda mungkin juga ingin secara otomatis menghitung jumlah sel yang diarsir dengan warna yang sama, mis. jumlah semua sel darah merah.
Seperti yang kita semua tahu, Microsoft Excel menyediakan berbagai formula untuk tujuan yang berbeda, dan akan logis untuk berasumsi bahwa ada beberapa yang menghitung sel berdasarkan warna. Namun sayangnya, tidak ada rumus yang memungkinkan kita untuk menjumlahkan berdasarkan warna atau menghitung berdasarkan warna dalam lembar kerja Excel yang biasa.
Cara menghitung dan menjumlahkan sel berdasarkan warna di Excel

Selain menggunakan add-in pihak ketiga, hanya ada satu solusi yang mungkin - memanfaatkan Fungsi Buatan Pengguna. Jika Anda tahu sedikit tentang teknologi ini atau belum pernah mendengar istilah ini sebelumnya, jangan takut, Anda tidak perlu menulis kode sendiri. Anda akan menemukan kode yang sempurna di sini dan yang harus Anda lakukan hanyalah menyalin / menempelkannya ke buku kerja Anda.
Cara menghitung dengan warna dan menjumlahkan dengan warna dalam lembar kerja Excel
Misalkan Anda memiliki tabel yang mencantumkan pesanan perusahaan Anda di mana sel-sel di kolom Pengiriman diwarnai berdasarkan nilainya - sel "Karena dalam X Hari" sel oranye, item "Terkirim/Delivery" berwarna hijau dan pesanan "Past Due" berwarna merah.
Cara menghitung dan menjumlahkan sel berdasarkan warna di Excel
contoh tabel
  • Buka lembar kerja Excel Anda dan tekan Alt + F11 untuk membuka Visual Basic Editor (VBE).
  • Klik kanan pada nama buku kerja Anda di bawah "Project-VBAProject" di bagian kanan layar, dan kemudian pilih Insert> Module dari menu konteks.
Cara menghitung dan menjumlahkan sel berdasarkan warna di Excel
Visual Basic Editor (VBE)
  • Tambahkan kode berikut ke lembar kerja Anda:
Function GetCellColor(xlRange As Range)
    Dim indRow, indColumn As Long
    Dim arResults()
 
    Application.Volatile
 
    If xlRange Is Nothing Then
        Set xlRange = Application.ThisCell
    End If
 
    If xlRange.Count > 1 Then
      ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
       For indRow = 1 To xlRange.Rows.Count
         For indColumn = 1 To xlRange.Columns.Count
           arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color
         Next
       Next
     GetCellColor = arResults
    Else
     GetCellColor = xlRange.Interior.Color
    End If
End Function
 
Function GetCellFontColor(xlRange As Range)
    Dim indRow, indColumn As Long
    Dim arResults()
 
    Application.Volatile
 
    If xlRange Is Nothing Then
        Set xlRange = Application.ThisCell
    End If
 
    If xlRange.Count > 1 Then
      ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
       For indRow = 1 To xlRange.Rows.Count
         For indColumn = 1 To xlRange.Columns.Count
           arResults(indRow, indColumn) = xlRange(indRow, indColumn).Font.Color
         Next
       Next
     GetCellFontColor = arResults
    Else
     GetCellFontColor = xlRange.Font.Color
    End If
 
End Function
 
Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long
 
    Application.Volatile
    cntRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Interior.Color Then
            cntRes = cntRes + 1
        End If
    Next cellCurrent
 
    CountCellsByColor = cntRes
End Function
 
Function SumCellsByColor(rData As Range, cellRefColor As Range)
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim sumRes
 
    Application.Volatile
    sumRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Interior.Color Then
            sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
        End If
    Next cellCurrent
 
    SumCellsByColor = sumRes
End Function
 
Function CountCellsByFontColor(rData As Range, cellRefColor As Range) As Long
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long
 
    Application.Volatile
    cntRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Font.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Font.Color Then
            cntRes = cntRes + 1
        End If
    Next cellCurrent
 
    CountCellsByFontColor = cntRes
End Function
 
Function SumCellsByFontColor(rData As Range, cellRefColor As Range)
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim sumRes
 
    Application.Volatile
    sumRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Font.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Font.Color Then
            sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
        End If
    Next cellCurrent
 
    SumCellsByFontColor = sumRes
End Function

  • Simpan buku kerja Anda sebagai "Buku Kerja yang Diaktifkan Makro Excel (.xlsm)".
  • Sekarang semua pekerjaan "di belakang layar" dilakukan untuk Anda oleh fungsi yang didefinisikan pengguna yang baru saja ditambahkan, pilih sel tempat Anda ingin menampilkan hasil dan masukkan fungsi CountCellsByColor ke dalamnya:
CountCellsByColor(range, color code)
Dalam contoh ini, saya menggunakan rumus = CountCellsByColor (F2: F14, A17) di mana F2: F14 adalah rentang yang berisi sel-sel kode warna yang ingin Anda hitung dan A17 adalah sel dengan warna latar belakang tertentu, yang merah dalam contoh ini.

Dengan cara yang sama, Anda menulis rumus untuk warna lain yang ingin Anda hitung, kuning dan hijau di tabel kita.
Cara menghitung dan menjumlahkan sel berdasarkan warna di Excel
Memasukkan rumus
Jika Anda memiliki data numerik dalam sel berwarna (mis. Kolom Qty. Dalam tabel kita), Anda bisa menjumlahkan nilai berdasarkan warna tertentu dengan menggunakan fungsi SumCellsByColor analog:
SumCellsByColor(range, color code)
Cara menghitung dan menjumlahkan sel berdasarkan warna di Excel
fungsi SumCellsByColor 
Seperti yang ditunjukkan dalam gambar di atas, kita menggunakan rumus = SumCellsByColor (D2: D14, A17) di mana D2: D14 adalah kisaran dan A17 adalah sel dengan pola warna.

Dengan cara yang sama Anda dapat menghitung sel dan menjumlahkan nilai sel dengan warna font menggunakan fungsi CountCellsByFontColor dan SumCellsByFontColor, masing-masing.
Cara menghitung dan menjumlahkan sel berdasarkan warna di Excel
Fungsi CountCellsByFontColor dan SumCellsByFontColor
Catatan: Jika setelah menerapkan kode VBA yang disebutkan di atas, Anda perlu mewarnai beberapa sel lagi secara manual, jumlah dan jumlah sel berwarna tidak akan dihitung ulang secara otomatis untuk mencerminkan perubahan. Tolong jangan marah dengan kita, ini bukan bug dari kode:). Bahkan, itu adalah perilaku normal semua makro Excel, skrip VBA dan Fungsi yang Ditentukan Pengguna. Intinya adalah bahwa semua fungsi tersebut dipanggil dengan perubahan data lembar kerja saja dan Excel tidak menganggap mengubah warna font atau warna sel sebagai perubahan data. Jadi, setelah mewarnai sel secara manual, cukup tempatkan kursor ke sel mana saja dan tekan F2 pada keyboard kemudian Enter, jumlah dan jumlah akan diperbarui. Hal yang sama berlaku untuk makro lain yang akan Anda temukan lebih lanjut dalam artikel ini.
Menjumlahkan berdasarkan warna dan hitung berdasarkan warna di  Semua lembar kerja
Skrip VB di bawah ini ditulis oleh guru Excel kami, yaitu menghitung dan menjumlahkan sel-sel dengan warna tertentu di semua lembar kerja / buku kerja. Jadi, ini dia kodenya:

Function WbkCountCellsByColor(cellRefColor As Range)
    Dim vWbkRes
    Dim wshCurrent As Worksheet
 
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    vWbkRes = 0
    For Each wshCurrent In Worksheets
       wshCurrent.Activate
       vWbkRes = vWbkRes + CountCellsByColor(wshCurrent.UsedRange, cellRefColor)
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
 
    WbkCountCellsByColor = vWbkRes
End Function
 
Function WbkSumCellsByColor(cellRefColor As Range)
    Dim vWbkRes
    Dim wshCurrent As Worksheet
 
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    vWbkRes = 0
    For Each wshCurrent In Worksheets
       wshCurrent.Activate
       vWbkRes = vWbkRes + SumCellsByColor(wshCurrent.UsedRange, cellRefColor)
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
 
    WbkSumCellsByColor = vWbkRes
End Function
Anda menggunakan makro ini dengan cara yang sama seperti kode sebelumnya dan output jumlah dan jumlah sel berwarna dengan bantuan rumus berikut, = WbkCountCellsByColor () dan = WbkSumCellsByColor (), masing-masing. Cukup masukkan salah satu rumus di sel kosong apa pun pada lembar mana pun tanpa menentukan rentangnya/kolom dan barisnya, tentukan alamat sel apa pun dari warna yang dibutuhkan dalam tanda kurung, misalnya. = WbkSumCellsByColor (A1), dan rumus akan menampilkan jumlah semua sel yang diarsir dengan warna yang sama di lembar kerja Anda.
Fungsi khusus untuk mendapatkan warna latar belakang sel, warna font, dan kode warna
Di sini Anda akan menemukan ringkasan dari semua fungsi yang telah kita gunakan dalam contoh ini serta beberapa yang baru yang mengambil kode warna.
Catatan: Harap diingat bahwa semua formula ini hanya akan berfungsi jika Anda telah menambahkan fungsi yang ditentukan pengguna ke buku kerja Excel Anda seperti yang ditunjukkan sebelumnya dalam artikel ini.
Fungsi untuk dihitung berdasarkan warna:
CountCellsByColor (range, kode warna) - menghitung sel dengan warna latar yang ditentukan.
Dalam contoh di atas, kami menggunakan rumus berikut untuk menghitung sel dengan warna = CountCellsByColor (F2: F14, A17) di mana F2: F14 adalah rentang yang dipilih dan A17 adalah sel dengan warna latar yang dibutuhkan. Anda dapat menggunakan semua formula lain yang tercantum di bawah ini dengan cara yang sama.
CountCellsByFontColor (rentang, kode warna) - menghitung sel dengan warna font yang ditentukan.

Rumus untuk dijumlahkan dengan warna:e
CountCellsByColor(range, color code) - menghitung jumlah sel dengan warna latar belakang tertentu.
CountCellsByFontColor(range, color code) - menghitung jumlah sel dengan warna font tertentu.

Rumus untuk mendapatkan kode warna:
GetCellFontColor (cell) - mengembalikan kode warna dari warna font sel yang ditentukan.
GetCellColor (cell) - mengembalikan kode warna dari warna latar belakang sel yang ditentukan.
Cara menghitung dan menjumlahkan sel berdasarkan warna di Excel
Menemukan warna cell
Ya, menghitung sel berdasarkan warna dan mendapatkan jumlah sel berwarna cukup mudah, bukan?

Silahkan masukan komentar Anda disini, dan Klik Emoticon untuk menambah emoji.. :D
EmoticonEmoticon