Cara menjumlahkan cell berdasarkan warna di microsoft excel

Misalkan Anda memiliki rentang sel dengan warna latar berbeda, seperti merah, hijau, biru, dan sebagainya, tetapi sekarang Anda perlu menghitung berapa banyak sel dalam rentang itu yang memiliki warna latar tertentu dan menjumlahkan 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 beberapa cara untuk mengatasi masalah ini.

• Hitung dan Jumlahkan sel berdasarkan warna isian tertentu dengan Filter dan SUBTOTAL
• Hitung dan Jumlahkan sel berdasarkan warna isian tertentu dengan fungsi GET.CELL
• Hitung dan jumlahkan sel berdasarkan warna isian tertentu dengan User Defined Function
• Hitung dan Jumlahkan sel berdasarkan warna isian spesifik dengan Fungsi Kutools
• Hitung dan Jumlahkan sel berdasarkan warna isian tertentu (atau warna format bersyarat) dengan Kutools for Excel

Hitung dan Jumlahkan sel berwarna menurut Filter dan SUBTOTAL

Misalkan kita memiliki tabel penjualan buah seperti gambar di bawah ini yang ditampilkan, dan kita akan menghitung atau menjumlahkan sel berwarna di kolom Jumlah. Dalam situasi ini, kita bisa memfilter kolom Jumlah menurut warna, lalu 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, harap masukkan rumus = SUBTOTAL (102, E2: E20);
2. Untuk menjumlahkan semua sel dengan warna latar yang sama, harap masukkan rumus = SUBTOTAL (109, E2: E20);

2. Pilih header tabel, dan klik Data > SARING. Lihat tangkapan layar:

3. Klik ikon Filter

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

Note: Metode ini membutuhkan sel berwarna yang akan Anda hitung atau jumlahkan berada di kolom yang sama.

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

Dengan sangat baik Hitung dengan Warna fitur dari Kutools untuk Excel, Anda bisa dengan cepat menghitung, menjumlahkan, dan rata-rata sel dengan warna isian atau warna font tertentu hanya dengan satu klik di Excel. Selain itu fitur ini juga akan mengetahui nilai max dan min dari cell berdasarkan fill color atau warna font. Uji Coba Gratis Fitur Lengkap 30 hari!

Kutools untuk Excel- Termasuk lebih dari 300 alat praktis untuk Excel. Uji coba gratis fitur lengkap 30 hari, tidak perlu kartu kredit! Get It Now

Hitung atau Jumlahkan 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 kode warna dengan mudah di Excel. Harap lakukan sebagai berikut:

1. Klik Rumus > Tentukan Nama. Lihat tangkapan layar:

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

3. Sekarang tambahkan kolom Warna baru tepat ke tabel asli. Selanjutnya ketikkan rumusnya = NumColor , dan seret gagang IsiOtomatis untuk menerapkan rumus ke sel lain di kolom Warna. Lihat tangkapan layar:
Note: Dalam rumusnya, AngkaWarna adalah rentang bernama yang kami tentukan dalam 2 langkah pertama. Anda perlu mengubahnya ke nama tertentu yang Anda tetapkan.

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

4. Salin dan daftar warna isian dalam rentang kosong di lembar kerja aktif, dan ketik rumus di sebelahnya seperti gambar di bawah ini yang ditampilkan:
A. Untuk menghitung sel berdasarkan warna, masukkan rumus = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. Untuk menjumlahkan sel berdasarkan warna, masukkan rumus = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

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

Hitung dan jumlahkan sel berdasarkan warna isian tertentu dengan User Defined Function

Misalkan sel berwarna tersebar dalam kisaran seperti gambar di bawah ini yang ditunjukkan, kedua metode di atas tidak dapat menghitung atau menjumlahkan sel berwarna. Di sini, metode ini akan memperkenalkan VBA untuk mengatasi masalah tersebut.

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

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

VBA: Hitung dan jumlahkan 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 kodenya, dan terapkan rumus berikut:
A.Hitung sel berwarna: = fungsi warna (A, B: C, FALSE)
B.Jumlahkan sel berwarna: = fungsi warna (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= fungsi warna (A1, A1: D11, FALSE) untuk menghitung sel kuning. Dan gunakan rumusnya = fungsi warna (A1, A1: D11, TRUE) untuk menjumlahkan sel kuning. Lihat tangkapan layar:

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

Hitung dan Jumlahkan sel berdasarkan warna isian spesifik dengan Fungsi Kutools

Kutools for Excel juga mendukung beberapa fungsi yang berguna untuk membantu pengguna Excel membuat perhitungan khusus, mengatakan menghitung dengan warna latar belakang sel, jumlah menurut warna font, dll.

Kutools untuk Excel- Termasuk lebih dari 300 alat praktis untuk Excel. Uji coba gratis fitur lengkap 30 hari, tidak perlu kartu kredit! Uji Coba Gratis Sekarang!

1. Pilih sel kosong tempat Anda menempatkan hasil penghitungan, dan klik Kutools > Fungsi Kutools > Statistik & Matematika > COUNTBYCELLCOLOR. Lihat tangkapan layar:

2. Dalam dialog Function Arguments, harap tentukan rentang yang akan Anda hitung sel berwarna di dalamnya Referensi kotak, pilih sel yang diisi oleh warna latar yang ditentukan di indeks_warna_nr kotak, dan klik OK tombol. Lihat tangkapan layar:

Catatan:
(1) Anda juga dapat mengetikkan Fungsi Kutools yang ditentukan = COUNTBYCELLCOLOR ($ A $ 1: $ E $ 20, G2)  di sel kosong atau bilah rumus secara langsung untuk mendapatkan hasil penghitungan;
(2) Klik Kutools > Fungsi Kutools > Statistik & Matematika > WARNA SUMBYCELL atau ketik = SUMBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) di sel kosong secara langsung untuk menjumlahkan sel berdasarkan warna latar yang ditentukan.
Terapkan COUNTBYCELLCOLOR serta WARNA SUMBYCELL fungsi untuk setiap warna latar belakang secara terpisah, dan Anda akan mendapatkan hasil seperti gambar di bawah ini yang ditampilkan:

Fungsi Kutools berisi sejumlah fungsi bawaan untuk membantu pengguna Excel menghitung dengan mudah, termasuk Count / Sum / Average Sel Terlihat, Hitung / Jumlahkan dengan warna sel, Hitung / Jumlah berdasarkan warna font, Hitung karakter, Hitung dengan huruf tebal, Dll Dapatkan Uji Coba Gratis!

Hitung dan Jumlahkan sel berdasarkan warna isian spesifik dengan Kutools for Excel

Dengan User Defined Function di atas, Anda perlu memasukkan rumus satu per satu, jika ada banyak warna berbeda, cara ini akan membosankan dan menyita waktu. Tetapi jika Anda punya Kutools untuk Excel'S Hitung dengan Warna utilitas, Anda dapat dengan cepat membuat laporan sel berwarna. Anda tidak hanya dapat menghitung dan menjumlahkan sel berwarna, tetapi juga bisa mendapatkan nilai rata-rata, maks, dan min dari rentang berwarna.

Kutools untuk Excel- Termasuk lebih dari 300 alat praktis untuk Excel. Uji coba gratis fitur lengkap 30 hari, tidak perlu kartu kredit! Uji Coba Gratis Sekarang!

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, lakukan seperti gambar di bawah ini yang ditunjukkan:
(1) Pilih Pemformatan standar dari Metode warna daftar drop-down;
(2) Pilih Latar Belakang dari Jenis hitung daftar drop-down.
(3) Klik tombol Buat laporan.

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

The Hitung dengan Warna fitur menghitung (Hitung, Jumlah, Rata-rata, Maks, dll.) sel dengan warna latar belakang atau warna font. Dapatkan Uji Coba Gratis!

Artikel terkait:

Demo: Hitung dan jumlahkan sel berdasarkan latar belakang, warna pemformatan bersyarat:

Alat Produktivitas Kantor Terbaik

Kutools for Excel Memecahkan Sebagian Besar Masalah Anda, dan Meningkatkan Produktivitas Anda hingga 80%

• Reuse: Masukkan dengan cepat rumus, bagan yang kompleks dan apa pun yang pernah Anda gunakan sebelumnya; Enkripsi Sel dengan kata sandi; Buat Milis dan mengirim email ...
• Super Formula Bar (dengan mudah mengedit beberapa baris teks dan rumus); Membaca Tata Letak (membaca dan mengedit sel dalam jumlah besar dengan mudah); Tempel ke Rentang yang Difilter...
• Gabungkan Sel / Baris / Kolom tanpa kehilangan Data; Pisahkan Konten Sel; Gabungkan Baris / Kolom Duplikat... Mencegah Sel Duplikat; Bandingkan Rentang...
• Pilih Duplikat atau Unik Baris; Pilih Baris Kosong (semua sel kosong); Temukan Super dan Temukan Fuzzy di Banyak Buku Kerja; Pilih Acak ...
• Salinan Tepat Beberapa Sel tanpa mengubah referensi rumus; Buat Referensi Otomatis ke Beberapa Lembar; Sisipkan Poin, Kotak Centang, dan lainnya ...
• Ekstrak Teks, Tambahkan Teks, Hapus berdasarkan Posisi, Hapus Space; Membuat dan Mencetak Subtotal Paging; Konversi Konten Antar Sel dan Komentar...
• Filter Super (simpan dan terapkan skema filter ke sheet lain); Penyortiran Lanjutan menurut bulan / minggu / hari, frekuensi dan lainnya; Filter Khusus dengan huruf tebal, miring ...
• Gabungkan Workbooks dan WorkSheets; Gabungkan Tabel berdasarkan kolom kunci; Pisahkan Data menjadi Beberapa Lembar; Konversi Batch xls, xlsx dan PDF...
• Lebih dari 300 fitur canggih. Mendukung Office / Excel 2007-2019 dan 365. Mendukung semua bahasa. Penerapan yang mudah di perusahaan atau organisasi Anda. Fitur lengkap uji coba gratis 30 hari. Jaminan uang kembali 60 hari.

Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

• Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
• Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
• Tingkatkan produktivitas Anda hingga 50%, dan kurangi ratusan klik mouse untuk Anda setiap hari!

komentar (235)

Belum ada peringkat. Jadilah yang pertama memberi peringkat!