Bagaimana Menjumlahkan dengan Warna di Excel? (2 Metode Berguna)
2 Metode Teratas untuk Menjumlahkan Berdasarkan Warna di Excel adalah sebagai berikut -
- Penggunaan rumus SUBTOTAL di excel dan filter berdasarkan fungsi warna.
- Menerapkan rumus GET.CELL dengan menentukan nama di tab rumus dan menerapkan rumus SUMIF di excel untuk meringkas nilai dengan kode warna.
Mari kita bahas masing-masing secara rinci -
Anda dapat mendownload Template Sum by Color Excel ini di sini - Template Sum by Color Excel# 1 - Jumlahkan dengan Warna menggunakan Fungsi Subtotal
Untuk memahami pendekatan untuk menghitung jumlah nilai menurut warna latar, mari kita pertimbangkan tabel data di bawah ini yang memberikan rincian jumlah dalam US $ menurut wilayah dan bulan.
- Langkah 1: Misalkan kita ingin menyorot sel-sel yang nilainya negatif untuk tujuan indikasi, ini dapat dicapai dengan menerapkan pemformatan bersyarat atau dengan menyorot sel secara manual seperti yang ditunjukkan di bawah ini.
- Langkah 2: Sekarang untuk mencapai jumlah sel yang diwarnai di excel, masukkan rumus untuk SUBTOTAL di bawah tabel data. Sintaks untuk rumus SUBTOTAL ditampilkan di bawah ini.
Rumus yang dimasukkan untuk menghitung penjumlahan adalah
= SUBTOTAL (9, D2: D13)
Di sini angka '9' dalam argumen function_num mengacu pada fungsionalitas penjumlahan dan argumen referensi diberikan sebagai rentang sel yang akan dihitung. Di bawah ini adalah tangkapan layar untuk hal yang sama.
- Langkah 3: Seperti yang terlihat pada gambar di atas, penjumlahan dari jumlah USD telah dihitung. Untuk menghitung jumlah yang disorot dengan warna latar belakang merah terang. Terapkan filter ke tabel data dengan membuka Data lalu memilih filter.
- Langkah 4: Kemudian pilih filter berdasarkan warna dan pilih warna sel merah terang di bawah 'Filter menurut warna sel'. Di bawah ini adalah tangkapan layar untuk mendeskripsikan filter dengan lebih baik.
- Langkah 5: Setelah filter excel diterapkan, tabel data akan difilter hanya untuk sel latar belakang merah terang dan rumus subtotal yang diterapkan di bagian bawah tabel data akan menampilkan penjumlahan sel berwarna yang difilter seperti yang ditunjukkan di bawah ini.
Seperti yang ditunjukkan pada tangkapan layar di atas, perhitungan sel berwarna dicapai di sel E17, rumus subtotal.
# 2 - Jumlahkan dengan Warna menggunakan Fungsi Get.Cell
Pendekatan kedua dijelaskan untuk sampai pada jumlah sel warna di excel dibahas dalam contoh di bawah ini. Pertimbangkan tabel data untuk lebih memahami metodologi.
- Langkah 1: Sekarang mari kita sorot daftar sel di kolom Jumlah USD yang ingin kita dapatkan pada jumlah sel berwarna yang diinginkan seperti yang ditunjukkan di bawah ini.
- Langkah 2: Seperti yang bisa kita lihat pada gambar di atas, tidak seperti contoh pertama di sini kita memiliki banyak warna. Dengan demikian kita akan menggunakan rumus = GET.CELL dengan mendefinisikannya di dalam kotak nama dan tidak langsung menggunakannya di excel.
- Langkah 3: Sekarang setelah kotak dialog untuk 'Define Name' muncul, masukkan Nama dan rumus untuk GET.CELL di 'Refer to' seperti yang ditunjukkan pada gambar di bawah ini.
Seperti yang terlihat pada gambar di atas, Nama yang dimasukkan untuk fungsinya adalah 'CellColor' dan rumusnya = GET.CELL (38, 'Contoh 2! $ D2) harus dimasukkan dalam' Mengacu ke '. Di dalam rumus, angka 38 mengacu pada informasi kode sel dan argumen kedua adalah nomor sel D2 mengacu pada sel referensi. Sekarang klik OK.
- Langkah 4: Sekarang masukkan nama fungsi 'CellColor' di sel di samping sel berkode warna yang ditentukan di kotak dialog seperti yang dijelaskan pada langkah 3.
Seperti yang dapat dilihat pada gambar di atas, fungsi 'CellColor' dimasukkan yang mengembalikan kode warna untuk warna sel latar belakang.
Demikian pula, rumus diseret untuk seluruh kolom.
- Langkah 5: Sekarang untuk sampai pada jumlah nilai dengan warna di excel, kita akan memasukkan rumus SUMIF. Sintaks rumus SUMIF adalah sebagai berikut: -
Seperti yang dapat dilihat dari gambar di atas, argumen berikut dimasukkan ke dalam rumus SUMIF: -
- Argumen rentang dimasukkan untuk rentang sel E2: E13
- Kriteria dimasukkan sebagai G2 yang nilai-nilai yang diringkas perlu diambil.
- Rentang sel dimasukkan menjadi D2: D13
Rumus SUMIF diseret ke bawah untuk semua nomor kode warna yang nilainya akan ditambahkan bersama-sama.
Hal-hal untuk diingat
- Saat menggunakan pendekatan dengan rumus SUBTOTAL, fungsi ini memungkinkan pengguna untuk memfilter hanya satu warna filter pada satu waktu. Selain itu, fungsionalitas ini dapat digunakan untuk menambahkan hanya satu kolom nilai menurut warna filter. Jika terdapat lebih dari satu kolom dengan warna yang berbeda menurut baris di kolom yang berbeda, SUBTOTAL mungkin hanya menampilkan hasil yang benar untuk satu filter dengan warna ke kolom tertentu saja.
- Rumus GET.CELL yang dikombinasikan dengan pendekatan SUMIF menghilangkan batasan ini, karena fungsionalitas ini dapat digunakan untuk meringkas dengan warna untuk beberapa kode warna di latar belakang sel.
