Cookies membantu kami memberikan layanan kami. Dengan menggunakan layanan kami, Anda setuju untuk kami menggunakan cookies.
Kiat: Bahasa lain adalah Terjemahan-Google. Anda dapat mengunjungi English versi link ini
Masuk
x
or
x
x
Daftar
x

or

Bagaimana menghitung dan jumlah sel berdasarkan warna latar belakang di Excel?

Misalkan Anda memiliki rentang sel dengan warna latar yang berbeda, seperti merah, hijau, biru dan sebagainya, tetapi sekarang Anda perlu menghitung berapa banyak sel dalam rentang itu memiliki warna latar belakang tertentu dan menjumlahkan sel-sel berwarna dengan warna tertentu yang sama . Di Excel, tidak ada rumus langsung untuk menghitung jumlah dan jumlah sel warna, di sini saya akan memperkenalkan Anda beberapa cara untuk menyelesaikan masalah ini.

Hitung dan jumlah sel berdasarkan warna isi spesifik dengan Filter dan SUBTOTAL

Hitung dan Jumlahkan sel berdasarkan pada warna isi spesifik dengan fungsi GET.CELL

Jumlah dan jumlah sel berdasarkan warna isi tertentu dengan User Defined Function

Hitung dan jumlah sel berdasarkan warna isi tertentu (atau warna pemformatan bersyarat) dengan Kutools untuk Excel

Satu klik untuk menghitung, menjumlahkan, dan rata-rata sel berwarna di Excel

Dengan sangat baik Hitung dengan Warna fitur dari Kutools for Excel, Anda dapat dengan cepat menghitung, menjumlahkan, dan rata-rata sel dengan warna isi atau warna font yang ditentukan hanya dengan satu klik di Excel. Selain itu, fitur ini juga akan mengetahui nilai sel maksimum dan minimum dengan warna isian atau warna font. Uji Coba Gratis Fitur Lengkap 60-hari!
jumlah iklan berdasarkan warna 2


Hitung dan Jumlahkan sel berwarna dengan Filter dan SUBTOTAL

Andaikata kita memiliki tabel penjualan buah seperti gambar layar di bawah ini, dan kami akan menghitung atau menjumlahkan sel-sel berwarna di kolom Jumlah. Dalam situasi ini, kita bisa memfilter kolom Jumlah berdasarkan warna, dan kemudian menghitung atau menjumlahkan sel berwarna yang difilter dengan fungsi SUBTOTAL dengan mudah di Excel.

1. Pilih sel kosong untuk masuk ke fungsi SUBTOTAL.

  1. Untuk menghitung semua sel dengan warna latar yang sama, silakan masukkan rumus = SUBTOTAL (102, E2: E20);
  2. Untuk menjumlahkan semua sel dengan warna latar yang sama, silakan masukkan rumus = SUBTOTAL (109, E2: E20);


Catatan: Dalam kedua rumus, E2: E20 adalah kolom Jumlah yang berisi sel-sel berwarna, dan Anda bisa mengubahnya sesuai kebutuhan.

2. Pilih tajuk tabel, dan klik Data > Menyaring. Lihat tangkapan layar

3. Klik ikon Filter di sel header pada kolom Jumlah, dan klik Filter dengan Warna dan warna yang ditentukan akan Anda hitung secara berturut-turut. Lihat tangkapan layar:

Setelah memfilter, kedua rumus SUBTOTAL menghitung dan menjumlahkan semua sel warna yang difilter dalam kolom Jumlah secara otomatis. Lihat tangkapan layar:

Catatan: Metode ini membutuhkan sel berwarna yang akan Anda hitung atau jumlahkan dalam kolom yang sama.


Hitung atau jumlah sel berwarna dengan fungsi GET.CELL

Dalam metode ini, kita akan membuat rentang bernama dengan fungsi GET.CELL, mendapatkan kode warna sel, dan kemudian menghitung atau menjumlahkan dengan kode warna dengan mudah di Excel. Silakan lakukan sebagai berikut:

1. Klik Rumus > Tentukan Nama. Lihat tangkapan layar

2. Dalam dialog Nama Baru, silakan lakukan seperti screenshot di bawah ini yang ditunjukkan:
(1) Ketikkan nama dalam kotak Nama;
(2) Masukkan formula = GET.CELL (38, Sheet4! $ E2) di kotak Rujuk ke (mencatat: dalam rumus, 38 berarti mengembalikan kode sel, dan Sheet4! $ E2 adalah sel pertama dalam kolom Jumlah kecuali tajuk kolom yang perlu Anda ubah berdasarkan data tabel Anda.)
(3) Klik OK tombol.

3. Sekarang tambahkan kolom Warna baru langsung ke tabel asli. Selanjutnya ketik rumus = NumColor , dan seret gagang IsiOtomatis untuk menerapkan rumus ke sel lain di kolom Warna. Lihat tangkapan layar:
Catatan: Dalam rumus, NumColor adalah rentang bernama yang kami tentukan di langkah 2 pertama. Anda perlu mengubahnya ke nama yang Anda tentukan.

Sekarang kode warna setiap sel di kolom Jumlah kembali di Kolom Warna. Lihat tangkapan layar:

4. Salin dan daftarkan warna isi dalam rentang kosong di lembar kerja aktif, dan ketikkan rumus di sebelahnya seperti tangkapan layar di bawah ini yang ditunjukkan:
A. Untuk menghitung sel berdasarkan warna, silakan masukkan rumus = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. Untuk menjumlahkan sel dengan warna, silakan masukkan formula = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

Catatan: Di kedua formula, $ F $ 2: $ F $ 20 adalah kolom Warna, NumColor adalah rentang bernama yang ditentukan, $ E $ 2: $ E $ 20 adalah Kolom Jumlah, dan Anda dapat mengubahnya sesuai kebutuhan.

Sekarang Anda akan melihat sel-sel di kolom Jumlah dihitung dan dijumlahkan dengan warna isinya.


Jumlah dan jumlah sel berdasarkan warna isi tertentu dengan User Defined Function


Andaikata sel-sel berwarna tersebar dalam kisaran seperti screenshot di bawah ini, kedua metode di atas tidak dapat menghitung atau menjumlahkan sel-sel berwarna. Di sini, metode ini akan memperkenalkan VBA untuk menyelesaikan masalah.

1. Tahan ALT + F11 kunci, dan itu membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Klik Menyisipkan > Modul, dan paste kode berikut di Module Window.

VBA: Hitung dan jumlah sel berdasarkan warna latar belakang:

Function ColorFunction(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
ColorFunction = vResult
End Function

3. Kemudian simpan kode, dan menerapkan rumus berikut:
A. Hitung sel berwarna: = colorfunction (A, B: C, FALSE)
B. Jumlahkan sel-sel berwarna: = colorfunction (A, B: C, TRUE)

Catatan: Dalam rumus di atas, A adalah sel dengan warna latar belakang tertentu yang ingin Anda hitung jumlah dan jumlahnya, dan B: C adalah rentang sel tempat Anda ingin menghitung jumlah dan jumlah.

4. Ambil screenshot berikut misalnya, masukkan rumusnya= colorfunction (A1, A1: D11, FALSE) untuk menghitung sel kuning. Dan gunakan rumusnya = colorfunction (A1, A1: D11, TRUE) untuk jumlah sel kuning. Lihat tangkapan layar

5. Jika Anda ingin menghitung dan jumlah sel berwarna lainnya, ulangi langkah 4. Maka Anda akan mendapatkan hasil sebagai berikut:


Hitung dan sel Sum berdasarkan warna fill spesifik dengan Kutools for Excel

Dengan Fungsi Ditetapkan Pengguna di atas, Anda perlu memasukkan rumus satu per satu, jika ada banyak warna yang berbeda, metode ini akan membosankan dan menyita waktu. Tapi jika sudah Kutools for Excel'S Hitung dengan Warna utilitas, Anda dapat dengan cepat menghasilkan laporan dari sel berwarna. Anda tidak hanya bisa menghitung dan menghitung sel berwarna, tapi juga bisa mendapatkan nilai rata-rata, maks dan min dari kisaran warna.

1. Pilih rentang yang ingin Anda gunakan, dan klik Kutools Plus > Hitung dengan Warna, lihat tangkapan layar:

2. Dan di Hitung dengan Warna kotak dialog, silakan lakukan seperti screenshot di bawah ini yang ditunjukkan:
(1) Pilih Format standar dari Metode warna daftar drop down;
(2) Pilih Latar Belakang dari Hitung jenis daftar drop down
(3) Klik tombol Hasilkan laporan.

Kutools for Excel - Termasuk lebih dari 300 alat Excel yang berguna. Uji coba gratis fitur lengkap 60 hari, tidak memerlukan kartu kredit! Dapatkan sekarang!

Catatan: Untuk menghitung dan menjumlahkan sel berwarna dengan warna pemformatan bersyarat khusus, silakan pilih Format kondisional dari Metode warna daftar drop-down di dialog di atas, atau pilih Format Standar dan Bersyarat dari daftar drop-down untuk menghitung semua sel yang diisi oleh warna yang ditentukan.

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


Artikel terkait:

Bagaimana menghitung / jumlah sel berdasarkan warna font di Excel?


Demo: Jumlah dan jumlah sel berdasarkan latar belakang, warna format kondisional:

Dalam Video ini, the Kutools tab dan Kutools Plus tab ditambahkan oleh Kutools for Excel. Jika perlu, silakan klik sini untuk memiliki percobaan gratis 60 hari tanpa batasan!



Alat Produktifitas yang Direkomendasikan

Office Tab

Bintang emas1 Bawa tab yang berguna ke Excel dan perangkat lunak Office lainnya, seperti Chrome, Firefox dan Internet Explorer baru.

Kutools for Excel

Bintang emas1 Menakjubkan! Tingkatkan produktivitas Anda dalam 5 menit. Tidak perlu keahlian khusus, hemat dua jam setiap hari!

Bintang emas1 300 Fitur Baru untuk Excel, Membuat Excel Lebih Mudah dan Hebat:

  • Gabungkan Sel / Baris / Kolom tanpa Kehilangan Data.
  • Menggabungkan dan mengkonsolidasikan beberapa lembar dan buku kerja.
  • Bandingkan Range, Copy Multiple Ranges, Konversi Teks ke Tanggal, Konversi Unit dan Mata Uang.
  • Hitung dengan Warna, Pager Subtotals, Filter Sort dan Super Tingkat Lanjut,
  • Lebih Pilih / Sisipkan / Hapus / Teks / Format / Link / Komentar / Buku Kerja / Lembar Kerja Alat ...

Tembakan layar dari Kutools untuk Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Mori · 6 days ago
    I copied and paste but calculation result is "0" why?? I am using Office 2016.
  • To post as a guest, your comment is unpublished.
    Troy Havard · 1 months ago
    I've copied and pasted as stated and have used this formula for over a year but recently saved the workbook to a new name and now the function doesn't work! I can't figure out what the deal is and I'm losing hair and sleep over it! Loading the original workbook, the formula works like a charm but going back to the new one, it doesn't! I've loaded VBA and tried re-creating the function but it doesn't work. Using Office 2019 - any help appreciated.
    • To post as a guest, your comment is unpublished.
      Jku · 17 days ago
      #NAME clearly indicates some keyword is not used in 2019, say, Interior.ColorIndex. Try to figure out changing the code from minimum lines to the full by adding one by one or search for the keywords in Excel/VBA in 2019
    • To post as a guest, your comment is unpublished.
      Troy Havard · 1 months ago
      I should probably have also noted that I get a #NAME? error in the cell in which I try to use the function.
  • To post as a guest, your comment is unpublished.
    Karolin · 2 months ago
    works fine, except it does not update the value in the totals when i change a cell to a diffrent colour. i have to click in the totals cell again to trigger a recount. Any Ideas how to fix this?
    • To post as a guest, your comment is unpublished.
      Arnier Hamz · 1 months ago
      Same here, i tried Ctrl+Alt+F9 or Ctrl+Shift+Alt+F9 to recalculate all formula and it works
  • To post as a guest, your comment is unpublished.
    Aaron · 3 months ago
    This doesn't work at all. It's total BS.
  • To post as a guest, your comment is unpublished.
    Schmingy · 5 months ago
    I was experiencing issues when attempting to use this function as-is to count/sum the lighter (pastel?) colors, at least in Excel 2016. Some debugging led me to realize that the range of "ColorIndex" values is quite limited, and the function above fails to distinguish between light yellow and light orange, reading both as ColorIndex = 19.

    Using the "Color" attribute, instead (as in "lCol = rColor.Interior.Color" and the two instances of "If rCell.Interior.Color = lCol Then", per the sample code above), resolved the issue.