Jumlahkan mengikut Warna dalam Excel

Bagaimana Menjumlahkan dengan Warna di Excel? (2 Kaedah Berguna)

2 Kaedah Teratas untuk Menjumlahkan dengan Warna di Excel adalah seperti berikut -

  1. Penggunaan formula SUBTOTAL di excel dan tapis mengikut fungsi warna.
  2. Menerapkan formula GET.CELL dengan menentukan nama di tab formula dan menerapkan formula SUMIF di excel untuk meringkaskan nilai mengikut kod warna.

Mari kita bincangkan masing-masing secara terperinci -

Anda boleh memuat turun Templat Jumlah mengikut Warna Excel ini di sini - Templat Jumlah mengikut Warna Excel

# 1 - Jumlahkan dengan Warna menggunakan Fungsi Subtotal

Untuk memahami pendekatan untuk menghitung jumlah nilai dengan warna latar belakang mari kita perhatikan jadual data di bawah ini yang memberikan perincian jumlah dalam US $ mengikut wilayah dan bulan.

  • Langkah 1:  Andaikan kita ingin menonjolkan sel-sel yang bernilai negatif untuk tujuan petunjuk, ini dapat dicapai dengan menerapkan pemformatan bersyarat atau dengan menonjolkan sel secara manual seperti yang ditunjukkan di bawah.

  • Langkah 2: Sekarang untuk mencapai jumlah sel yang berwarna excel, masukkan formula SUBTOTAL di bawah jadual data. Sintaks untuk formula SUBTOTAL ditunjukkan di bawah.

Formula yang dimasukkan untuk mengira penjumlahan adalah

= SUBTOTAL (9, D2: D13)

Di sini nombor '9' dalam argumen function_num merujuk kepada fungsi jumlah dan argumen rujukan diberikan sebagai julat sel yang akan dikira. Di bawah adalah tangkapan skrin untuk perkara yang sama.

  • Langkah 3: Seperti yang dilihat dalam tangkapan skrin di atas, penjumlahan jumlah USD telah dikira. Untuk menghitung jumlah yang diserlahkan dengan warna latar merah terang. Terapkan penapis ke jadual data dengan pergi ke Data kemudian pilih penapis.

  • Langkah 4: Kemudian pilih tapisan mengikut warna dan pilih warna sel merah terang di bawah 'Tapis mengikut warna sel'. Berikut adalah tangkapan skrin untuk menerangkan penapis dengan lebih baik.

  • Langkah 5: Setelah penapis excel diterapkan, tabel data akan disaring hanya untuk sel latar merah terang dan formula subtotal yang diterapkan di bawah tabel data akan menampilkan penjumlahan sel berwarna yang disaring seperti gambar di bawah.

Seperti yang ditunjukkan dalam tangkapan skrin di atas, pengiraan sel berwarna dicapai dalam sel E17, formula subtotal.

# 2 - Jumlahkan dengan Warna menggunakan Fungsi Get.Cell

Pendekatan kedua dijelaskan untuk mencapai jumlah sel warna dalam excel dibincangkan dalam contoh di bawah. Pertimbangkan jadual data untuk lebih memahami metodologi.

  • Langkah 1: Sekarang mari kita sorot senarai sel di lajur Jumlah USD yang kita bersedia sampai pada jumlah sel berwarna yang diinginkan seperti yang ditunjukkan di bawah.

  • Langkah 2: Seperti yang dapat kita lihat dalam tangkapan skrin di atas, tidak seperti contoh pertama di sini kita mempunyai pelbagai warna. Dengan itu kita akan menggunakan formula = GET.CELL dengan mendefinisikannya dalam kotak nama dan tidak langsung menggunakannya di excel.
  • Langkah 3: Sekarang setelah kotak dialog untuk 'Tentukan Nama' muncul, masukkan Nama dan formula untuk GET.CELL di 'Rujuk' seperti yang ditunjukkan dalam tangkapan skrin di bawah.

Seperti yang dilihat pada tangkapan skrin di atas, Nama yang dimasukkan untuk fungsi tersebut adalah 'CellColor' dan formula = GET.CELL (38, 'Contoh 2! $ D2) harus dimasukkan dalam' Merujuk ke '. Dalam formula, angka 38 merujuk kepada maklumat kod sel dan argumen kedua adalah nombor sel D2 merujuk kepada sel rujukan. Sekarang klik OK.

  • Langkah 4: Sekarang masukkan fungsi Nama 'CellColor' di sel di sebelah sel berkod warna yang didefinisikan dalam kotak dialog seperti yang dijelaskan pada langkah 3.

Seperti yang dapat dilihat pada tangkapan layar di atas, fungsi 'CellColor' dimasukkan yang mengembalikan kod warna untuk warna sel latar.

Begitu juga, formula diseret untuk keseluruhan lajur.

  • Langkah 5:  Sekarang untuk mencapai jumlah nilai berdasarkan warna dalam excel, kita akan memasukkan formula SUMIF. Sintaks untuk formula SUMIF adalah seperti berikut: -

Seperti yang dapat dilihat dari tangkapan skrin di atas, argumen berikut dimasukkan ke dalam formula SUMIF: -

  • Argumen julat dimasukkan untuk julat sel E2: E13
  • Kriteria dimasukkan sebagai G2 siapa nilai yang diringkaskan perlu diambil.
  • Julat sel dimasukkan menjadi D2: D13

Rumus SUMIF diseret ke bawah untuk semua nombor kod warna yang nilainya akan ditambah bersama.

Perkara yang Perlu Diingat

  • Semasa menggunakan pendekatan dengan formula SUBTOTAL, fungsi ini membolehkan pengguna menyaring hanya dengan satu warna penapis pada satu masa. Lebih-lebih lagi, fungsi ini dapat digunakan untuk menambahkan satu lajur nilai dengan warna penapis. Sekiranya terdapat lebih daripada satu lajur dengan warna yang berbeza mengikut baris di lajur yang berlainan, SUBTOTAL hanya dapat menunjukkan hasil yang betul untuk satu penapis berdasarkan warna ke lajur tertentu sahaja.
  • Rumus GET.CELL dalam kombinasi dengan pendekatan SUMIF menghilangkan batasan ini, kerana fungsi ini dapat digunakan untuk meringkaskan dengan warna untuk beberapa kod warna di latar belakang sel.