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.

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 Ditambah > 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!