Bagaimana Menjumlahkan dengan Warna di Excel? (2 Kaedah Berguna)
2 Kaedah teratas untuk Menjumlahkan dengan Warna di Excel adalah seperti berikut -
- Penggunaan formula SUBTOTAL di excel dan tapis mengikut fungsi warna.
- Menerapkan formula GET.CELL dengan menentukan nama di tab formula dan menerapkan formula SUMIF di excel untuk meringkaskan nilai dengan kod warna.
Mari kita bincangkan masing-masing secara terperinci -
# 1 - Jumlahkan dengan Warna menggunakan Fungsi Subtotal
Untuk memahami pendekatan untuk menghitung jumlah nilai berdasarkan 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 dihitung. Di bawah ini 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 penapis 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 belakang berwarna merah terang, dan rumus subtotal yang diterapkan di bagian bawah tabel data akan menampilkan penjumlahan sel berwarna, yang disaring seperti yang ditunjukkan 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, seperti yang 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 ingin 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 secara langsung menggunakannya di excel.
- Langkah 3: Sekarang, setelah kotak dialog untuk 'Define Name' muncul, masukkan Nama dan formula lupa. CELL di 'Refer to' seperti yang ditunjukkan dalam tangkapan skrin di bawah.
Seperti yang dilihat dalam tangkapan skrin di atas, Nama yang dimasukkan untuk fungsi tersebut adalah 'CellColor' dan rumus = GET.CELL (38, 'Contoh 2! $ D2) harus dimasukkan dalam' Merujuk ke. ' Dalam rumus, angka 38 merujuk kepada maklumat kod sel, dan argumen kedua adalah nombor sel D2 merujuk pada sel rujukan. Sekarang klik, OK.
- Langkah 4: Sekarang masukkan fungsi Nama 'CellColor' di sel di sebelah sel berkod warna, yang ditentukan 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 belakang.
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, yang nilai ringkasannya diperlukan untuk 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 hanya 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 mengikut 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.
