Tetangga saya buka toko bahan makanan segar di pasar. Beliau tahu saya cukup sering berurusan dengan komputer, jadi suatu hari beliau bertanya ke saya sambil membuka laptopnya. Catatannya sudah rapi: ada nomor, nama barang, kode pembeli, harga beli, dan harga jual.
Pertanyaannya sederhana. Kalau datanya difilter, misalnya cuma mau lihat Ikan saja, bagaimana caranya tahu jumlah harganya untuk Ikan saja. Beliau sudah mencoba memblok kolomnya lalu menjumlahkan, tapi hasilnya tetap jumlah seluruh baris, termasuk baris yang sedang disembunyikan oleh filter.
Masalah itu ternyata umum, dan jawabannya ada dua. Maka post ini saya isi dengan cara SUM data yang difilter di Excel, lengkap dengan gambar tiap langkahnya, persis seperti yang saya tunjukkan ke beliau waktu itu.
Tonton juga tutorial detailnya di YouTube Channel saya
Kenapa SUM biasa tetap menjumlah baris yang disembunyikan
SUM tidak peduli baris mana yang sedang terlihat di layar. Yang dia lihat cuma rentang selnya. Begitu Anda menulis rumus penjumlahan untuk rentang F6 sampai F22, semua sel di rentang itu ikut dihitung, mau barisnya tampil atau sedang disembunyikan filter.
Filter cuma menyembunyikan baris dari pandangan mata. Isi selnya tetap ada di sana. Jadi wajar kalau hasilnya tidak berubah sedikit pun waktu filternya diganti, dan itu persis yang dikeluhkan tetangga saya.
Yang dibutuhkan adalah rumus yang memang dirancang untuk melewati baris tersembunyi. Di Excel namanya SUBTOTAL.
Data yang saya pakai di contoh ini
Datanya tujuh belas baris, dari baris 6 sampai baris 22, dengan baris judul di baris 5. Kolomnya nomor, item, pembeli, harga beli, dan harga jual. Tombol filter sudah dinyalakan di baris judulnya.
Kode pembeli saya tulis sebagai huruf saja, karena nama pembeli tidak ada hubungannya dengan cara menjumlah. Angka-angkanya pun angka contoh yang dibulatkan, bukan harga asli dagangan beliau.
Cara pertama SUM data yang difilter: rumus SUBTOTAL
Cara ini paling cepat kalau Anda cuma butuh satu angka total dan tidak mau mengubah bentuk datanya sama sekali.
- Klik satu sel kosong di bawah kolom yang mau dijumlah. Di contoh ini sel F23, tepat di bawah kolom harga beli.
- Ketik =SUB. Excel langsung menawarkan SUBTOTAL di daftar saran.
- Pilih SUBTOTAL, lalu ketik kurung buka. Begitu kurungnya dibuka, daftar nomor fungsinya muncul sendiri.
- Pilih nomor 9 – SUM, lalu ketik pemisah argumennya. Di komputer saya pemisahnya titik koma, di komputer lain bisa koma.
- Blok rentang angkanya, di contoh ini F6 sampai F22. Baris judul dan sel totalnya sendiri jangan ikut diblok.
- Tutup kurungnya lalu tekan Enter. Rumus utuhnya jadi =SUBTOTAL(9;F6:F22).
Angka 962.000 itu total semua baris, jadi untuk sementara hasilnya memang sama saja dengan penjumlahan biasa. Bedanya baru kelihatan begitu filternya dipakai.
Sekarang klik tombol filter di kolom Item, hilangkan semua centang, lalu centang Ikan saja, dan tekan OK.
Rumusnya tidak diubah sedikit pun, tapi angkanya ikut. Waktu filternya saya ganti ke Ayam, hasilnya jadi 170.000. Waktu saya ganti lagi ke Tomat, hasilnya jadi 10.000. Itulah yang dicari tetangga saya.
Arti angka 9 dan angka 109 di dalam SUBTOTAL
Angka pertama di dalam SUBTOTAL itu nomor fungsi, bukan sekadar pelengkap. Dia menentukan SUBTOTAL mau menghitung apa, dan sekaligus menentukan baris mana yang dilewati.
- Nomor 1 sampai 11 adalah jenis hitungannya. Angka 9 untuk menjumlah, 1 untuk rata-rata, 2 untuk menghitung banyaknya angka, 4 untuk nilai terbesar, 5 untuk nilai terkecil.
- Nomor 101 sampai 111 isinya sama persis, cuma beda perlakuan. Angka 109 juga menjumlah, sama seperti 9.
- Bedanya ada di baris yang Anda sembunyikan sendiri. Angka 9 melewati baris yang disembunyikan filter, tapi tetap menghitung baris yang Anda sembunyikan manual lewat klik kanan lalu Hide. Angka 109 melewati dua-duanya.
Jadi kalau Anda cuma memakai filter, 9 dan 109 hasilnya akan sama. Kalau Anda punya kebiasaan menyembunyikan baris secara manual, pakai 109 supaya hasilnya ikut bersih. Daftar lengkap nomor fungsinya ada di halaman resmi Microsoft untuk fungsi SUBTOTAL.
Cara kedua SUM data yang difilter: ubah datanya jadi Tabel
Cara kedua ini tidak menulis rumus sama sekali. Excel yang menuliskannya untuk Anda, dan hasilnya tetap SUBTOTAL juga.
- Blok seluruh datanya berikut baris judulnya. Di contoh ini dari C5 sampai G22.
- Tekan Ctrl + T. Kotak konfirmasi rentang data langsung muncul.
- Tekan OK. Datanya langsung berganti rupa jadi Tabel, dan tab Table Design muncul di ribbon.
- Buka tab Table Design, lalu buka Table Style Options dan centang Total Row.
- Baris Total muncul sendiri di bawah tabel. Klik selnya, dan di baris rumus terlihat Excel menulis =SUBTOTAL(109;[Harga jual]).
- Untuk kolom lain, klik sel totalnya, lalu klik panah kecil di sebelah kanannya dan pilih Sum.
Perhatikan nomor fungsinya. Excel memakai 109, bukan 9. Jadi baris Total bawaan Tabel sudah otomatis melewati baris tersembunyi, baik yang disembunyikan filter maupun yang Anda sembunyikan sendiri.
Sekarang filternya dipakai seperti biasa, dan dua angkanya ikut berubah bersamaan.
Mana yang sebaiknya dipakai
Dua cara SUM data yang difilter di atas hasilnya sama persis. Yang membedakan cuma kapan sebaiknya dipakai.
- Pakai SUBTOTAL kalau datanya tidak mau diubah bentuknya, atau kalau totalnya mau ditaruh di tempat lain, misalnya di sudut atas lembar kerja.
- Pakai Tabel kalau datanya masih akan bertambah terus. Baris baru yang Anda ketik di bawah tabel otomatis ikut masuk hitungan, sedangkan rumus SUBTOTAL manual harus Anda lebarkan rentangnya sendiri.
- Untuk catatan dagang yang tiap hari nambah baris seperti punya tetangga saya, Tabel jauh lebih enak dipakai.
Yang sering bikin hasilnya tetap salah
Empat hal ini yang paling sering bikin SUM data yang difilter tetap meleset, dan semuanya pernah saya temui sendiri.
- Sel totalnya ikut terblok. Kalau rentangnya ditulis sampai F23 padahal rumusnya ada di F23, Excel akan protes karena rumusnya menghitung dirinya sendiri.
- Baris judul ikut terblok. Tidak bikin error, tapi bikin rentangnya tidak rapi dan menyulitkan waktu datanya ditambah.
- Pemisah argumennya salah. Kalau titik koma ditolak, ganti dengan koma. Itu cuma soal pengaturan wilayah di komputer masing-masing, bukan soal rumusnya salah.
- Masih memakai SUM di sebelahnya. Kalau di lembar yang sama masih ada penjumlahan biasa, angkanya memang tidak akan ikut filter, dan itu sering bikin bingung sendiri waktu dua angka itu dibandingkan.
Tetangga saya akhirnya memakai cara kedua, karena catatannya memang bertambah tiap hari dan beliau tidak mau repot mengurus rentang rumus. Buat saya sendiri, yang menarik dari kasus ini bukan rumusnya, tapi kenyataan bahwa satu pertanyaan kecil dari meja dagang di pasar ternyata jawabannya sudah lama disiapkan Excel, cuma jarang ada yang memberitahu.