Note: The other languages of the website are Google-translated. Back to English

Bagaimana cara menghitung atau menjumlahkan sel berdasarkan warna sel di lembar Google?

Menghitung atau menjumlahkan nilai sel berdasarkan warna latar belakang sel tertentu untuk mendapatkan hasil seperti gambar berikut yang ditampilkan. Artikel ini, saya akan berbicara tentang bagaimana menyelesaikan tugas ini di Google sheet dan Microsoft Excel.

Hitung nilai sel berdasarkan warna sel dengan skrip di lembar Google

Jumlahkan nilai sel berdasarkan warna sel dengan skrip di lembar Google

Hitung atau jumlahkan nilai sel pada warna sel dengan Kutools for Excel di Microsoft Excel


Hitung nilai sel berdasarkan warna sel dengan skrip di lembar Google

Skrip berikut dapat membantu Anda menghitung nilai sel berdasarkan warna sel tertentu, lakukan seperti ini:

1. Klik Tools > Editor skrip, lihat tangkapan layar:

2. Di jendela proyek yang terbuka, klik File > New > File skrip untuk membuka jendela kode, lihat tangkapan layar:

3. Dan di kotak prompt, masukkan nama untuk kode skrip ini, lihat tangkapan layar:

4. Klik OK lalu salin dan tempel kode berikut untuk mengganti kode asli ke modul kode, lihat tangkapan layar:

function countColoredCells(countRange,colorRef) {
  var activeRg = SpreadsheetApp.getActiveRange();
  var activeSht = SpreadsheetApp.getActiveSheet();
  var activeformula = activeRg.getFormula();
  var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
  var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
  var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
  var BackGround = activeSht.getRange(colorRefAddress).getBackground();
  var countCells = 0;
  for (var i = 0; i < backGrounds.length; i++)
    for (var k = 0; k < backGrounds[i].length; k++)
      if ( backGrounds[i][k] == BackGround )
        countCells = countCells + 1;
  return countCells;
};

5. Kemudian simpan kode skrip ini, dan kembali lembar, masukkan rumus ini: = countcoloredcells (A1: E11, A1) ke dalam sel kosong, lalu tekan Enter kunci untuk mendapatkan hasil yang dihitung. Lihat tangkapan layar:

Note: Dalam rumus ini: A1: E11 adalah rentang data yang ingin Anda gunakan, A1 adalah sel yang diisi dengan warna tertentu yang ingin Anda hitung.

6. Ulangi rumus di atas untuk menghitung sel berwarna tertentu lainnya.


Jumlahkan nilai sel berdasarkan warna sel dengan skrip di lembar Google

Untuk menjumlahkan nilai sel dengan warna sel tertentu, harap terapkan kode skrip di bawah ini.

1. Klik Tools > Editor skrip untuk membuka jendela proyek, dan klik File > New > File skrip untuk memasukkan modul kode baru lainnya, kemudian, di kotak prompt, ketikkan nama untuk skrip ini, lihat tangkapan layar:

2. Klik OK dan dalam modul kode terbuka, salin dan tempel kode skrip di bawah ini untuk mengganti kode asli, lihat tangkapan layar:

function sumColoredCells(sumRange,colorRef) {
  var activeRg = SpreadsheetApp.getActiveRange();
  var activeSht = SpreadsheetApp.getActiveSheet();
  var activeformula = activeRg.getFormula();
  var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
  var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
  var sumValues = activeSht.getRange(countRangeAddress).getValues();  
  var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
  var BackGround = activeSht.getRange(colorRefAddress).getBackground();
  var totalValue = 0;
  for (var i = 0; i < backGrounds.length; i++)
    for (var k = 0; k < backGrounds[i].length; k++)
      if ( backGrounds[i][k] == BackGround )
        if ((typeof sumValues[i][k]) == 'number')
          totalValue = totalValue + (sumValues[i][k]);
  return totalValue;
};

3. Dan kemudian simpan kode ini, kembali ke sheet, dan masukkan rumus ini: = sumcoloredcells (A1: E11, A1) ke dalam sel kosong, dan tekan Enter kunci untuk mendapatkan hasil yang dihitung, lihat tangkapan layar:

Note: Dalam rumus ini: A1: E11 adalah rentang data yang ingin Anda gunakan, A1 adalah sel dengan warna latar tertentu yang ingin Anda jumlahkan.

4. Dan kemudian Anda dapat mengulangi rumus di atas untuk menjumlahkan sel berwarna tertentu lainnya.


Hitung atau jumlahkan nilai sel pada warna sel dengan Kutools for Excel di Microsoft Excel

Di Microsoft Excel, untuk menghitung atau menjumlahkan nilai sel berdasarkan warna sel tertentu, Kutools untuk Excel'S Hitung dengan Warna utilitas dapat membantu Anda menyelesaikan tugas ini secepat mungkin.

Kutools untuk Excel : dengan lebih dari 300 add-in Excel yang praktis, gratis untuk dicoba tanpa batasan dalam 30 hari. 

Setelah menginstal Kutools untuk Excel, lakukan seperti ini:

1. Pilih sel untuk rentang yang ingin Anda hitung atau jumlahkan berdasarkan warna sel, lalu klik Kutools Plus > Hitung dengan Warna, lihat tangkapan layar:

2. di Hitung dengan Warna kotak dialog, pilih Pemformatan standar dari Metode warna daftar drop-down, lalu pilih Latar Belakang dari Jenis hitung drop down, lihat tangkapan layar:

3. Lalu klik Buat laporan tombol, dan lembar kerja baru dengan hasil yang dihitung dihasilkan sekaligus, lihat tangkapan layar:

Note: Dengan fitur canggih ini, Anda juga bisa menghitung nilai sel berdasarkan pemformatan bersyarat atau warna font.

Klik Unduh dan uji coba gratis Kutools untuk Excel Sekarang!


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 kte 201905

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!
officetab bawah
Urutkan komentar berdasarkan
komentar (67)
Belum ada peringkat. Jadilah yang pertama memberi peringkat!
Komentar ini diminimalkan oleh moderator di situs
Hai! Skrip bagus tapi saya mengalami masalah saat mencoba mengedit nilai di dalam sel berwarna. Script tidak me-refresh nilai jika saya mengubah warna sel. Saya hanya bisa memaksanya untuk me-refresh jika saya menghapus isi sel, mengubah warna dan kemudian memasukkan nilainya lagi.
Komentar ini diminimalkan oleh moderator di situs
Hei, terima kasih untuk skrip ini. Saya mengalami masalah dalam skrip untuk google sheets: TypeError: Cannot call method "pop" of null. (baris 5, file "SumByColor"). Bisa tolong bantu?, terima kasih
Komentar ini diminimalkan oleh moderator di situs
Terima kasih atas bantuannya dengan "Hitung sel berdasarkan warna untuk lembar google". Keterbatasan tampaknya adalah ketika warna sel diubah, lembar tidak disegarkan dan totalnya salah. Saya harus menghapus kode di sel total dan menyalin lagi dari sel tetangga. Tidak ideal, tetapi berhasil. Terima kasih lagi.
Komentar ini diminimalkan oleh moderator di situs
Halo, Todd,
Ya, seperti yang Anda katakan, Anda harus mengetik ulang rumus saat warna sel berubah.
Mungkin tidak ada cara lain yang baik untuk menyelesaikan masalah ini.
Jika ada metode lain yang bagus, silakan beri komentar di sini.
Terima kasih!
Komentar ini diminimalkan oleh moderator di situs
Fungsi sumColoredCells() Anda salah karena tidak menggunakan sumRange
Komentar ini diminimalkan oleh moderator di situs
Halo, Dave,
Skrip di atas berfungsi dengan baik di lembar Google saya.
Saat menerapkan rumus, Anda harus menyimpan jendela skrip terlebih dahulu.
Silakan dicoba, Terima kasih!
Komentar ini diminimalkan oleh moderator di situs
Hei, terima kasih untuk skrip ini. Saya mengalami masalah dalam skrip untuk google sheets: TypeError: Cannot call method "pop" of null. (baris 5, file "SumByColor"). Bisa tolong bantu?, terima kasih
Komentar ini diminimalkan oleh moderator di situs
Hai, Laura,
Script berfungsi dengan baik di lembar google saya.
Jika ada masalah di google sheet Anda, saya dapat membagikan google sheet saya kepada Anda.
Tolong beri alamat email Anda.
Terima kasih!
Komentar ini diminimalkan oleh moderator di situs
Halo, saya memiliki masalah yang sama, apakah Anda sudah memperbaikinya?
Komentar ini diminimalkan oleh moderator di situs
Masalahnya mungkin adalah konfigurasi regional Anda, skrip ini mengira Anda memisahkan parameter dengan ',', tetapi itu mungkin bukan kasus Anda. Dalam kasus saya, saya menggunakan ';', jadi saya memodifikasi skrip, mengubah karakter di baris

var countRangeAddress = activeformula.match(/\((.*)\;/).pop().trim();

var colorRefAddress = activeformula.match(/\;(.*)\)/).pop().trim();
Komentar ini diminimalkan oleh moderator di situs
cambio la coma (,) por punto y coma (;) pero aun así no me cuenta el color. saya jual como resultado (0 cero).
Komentar ini diminimalkan oleh moderator di situs
Apakah ada cara untuk memasukkan angka negatif ke dalam campuran dan mendapatkan jumlah yang benar? Milik saya terus menambahkan semua angka dalam rentang, bahkan jika angka tersebut memiliki tanda negatif.
Komentar ini diminimalkan oleh moderator di situs
Saya mendapatkan "Action Not Allowed Line 0" Apa yang saya lakukan salah?
Komentar ini diminimalkan oleh moderator di situs
Saya terus mendapatkan kesalahan "Tindakan tidak diizinkan (Baris 0)". Apa artinya ini?
Komentar ini diminimalkan oleh moderator di situs
apakah itu otomatis memperbarui ketika data baru ditambahkan?
Komentar ini diminimalkan oleh moderator di situs
Sepertinya tidak
Komentar ini diminimalkan oleh moderator di situs
Apakah Anda pernah menemukan cara untuk mendapatkan ini untuk update otomatis?
Komentar ini diminimalkan oleh moderator di situs
Adakah yang menemukan cara untuk memperbarui otomatis?
Komentar ini diminimalkan oleh moderator di situs
Saya perlu mencari cara agar pembaruan otomatis juga!... Adakah yang menemukan jawabannya?
Komentar ini diminimalkan oleh moderator di situs
Adakah yang menemukan cara untuk memperbarui otomatis?
Komentar ini diminimalkan oleh moderator di situs
Tidak, dan saya mencoba mencari jawabannya
Komentar ini diminimalkan oleh moderator di situs
Ganti baris ini:
var countRangeAddress = activeformula.match(/\((.*)\;/).pop().trim();


var colorRefAddress = activeformula.match(/\;(.*)\)/).pop().trim();

Pemberitahuan dari , diubah untuk ;
Komentar ini diminimalkan oleh moderator di situs
Halo,


Dapatkah saya memerlukan bantuan dengan rumus yang mirip dengan SUM total jumlah kata kunci yang cocok dan mencerminkan jumlah warna.


Apa yang saya apa untuk mencapainya menentukan jumlah laptop yang merupakan kata kunci tetapi mengenali Tersedia berdasarkan warna sel?
Komentar ini diminimalkan oleh moderator di situs
saya mencoba melakukan sesuatu seperti ini
=countSel Berwarna (H62:H,B2)countif("MBA13R15")
Komentar ini diminimalkan oleh moderator di situs
Halo,
Formulanya tidak bekerja untuk saya.
Jika saya melakukan ini di spreadsheet, hanya kesalahan yang akan terjadi.
Komentar ini diminimalkan oleh moderator di situs
Terima kasih, ini bekerja dengan baik!
Komentar ini diminimalkan oleh moderator di situs
Skrip SumColoredCells berfungsi dengan baik. Tahukah Anda jika ada cara untuk menampilkan (dalam satu sel) jumlah 2 warna? Misalnya, jika saya ingin jumlah Sel Kuning dan Hijau ditampilkan dalam satu sel, apakah itu mungkin? Saya akan berpikir rumusnya adalah:
=SUM(sumcoloredcells(A1:E11,A1)),(sumcoloredcells(A1:E11,A5)) ....Tapi itu menghasilkan ERROR. Ada ide?
Komentar ini diminimalkan oleh moderator di situs
Halo, Geoff,
Mungkin tidak ada rumus langsung untuk menyelesaikan masalah Anda, tetapi, Anda dapat menjumlahkan sel berwarna secara terpisah terlebih dahulu, lalu menjumlahkan kedua hasil yang dihitung sesuai kebutuhan.
Komentar ini diminimalkan oleh moderator di situs
Terima kasih balasannya. Saya telah melakukan itu, tetapi ingin menggabungkan formula ke dalam satu sel, karena saya harus memperbarui rentang setiap minggu. Bagaimanapun, terima kasih telah memeriksa!
Komentar ini diminimalkan oleh moderator di situs
Halo,


Fungsi ini berfungsi, namun, ketika saya mencoba menjumlahkan 2 instance di sel yang sama seperti di (dalam kasus saya):


=sumColoredCells(A103:AW103, C171) + sumColoredCells(A138:AW138, C171)


Ini memberi saya #Error - Range not found (baris 6).


Itu tidak berfungsi bahkan jika saya menggunakan SUM() atau ADD(). Ada ide?


Terima kasih sebelumnya.
Komentar ini diminimalkan oleh moderator di situs
terima kasih :*
Komentar ini diminimalkan oleh moderator di situs
Halo,
di lembar Google, sepertinya tidak berfungsi, saya melanjutkan debug dan untuk kedua proses, di baris 5 tampaknya ada kesalahan ketik, pesannya adalah: tidak mungkin untuk mengingat "metode pop2 nol. Apakah masuk akal? Terima kasih a banyak!
Komentar ini diminimalkan oleh moderator di situs
Internet penuh dengan contoh solusi ini dan tidak berfungsi.
Komentar ini diminimalkan oleh moderator di situs
Skrip ini dulu berfungsi dengan baik, tetapi tidak berfungsi lagi. Apakah ada perubahan yang membuatnya turun?
Belum ada komentar yang diposting di sini
Muat Lebih
Tinggalkan komentar anda
Posting sebagai Tamu
×
Beri peringkat pos ini:
0   Karakter
Lokasi yang Disarankan