Dalam pengolahan data di Microsoft Excel, salah satu kebutuhan yang sangat sering muncul adalah cara menjumlah data terfilter. Masalah ini biasanya muncul ketika kita bekerja dengan laporan gaji, laporan penjualan, rekap proyek, atau data administrasi lain yang menggunakan fitur Filter.
Banyak pengguna Excel tidak menyadari bahwa rumus SUM biasa tetap menjumlahkan data yang tersembunyi, sehingga hasilnya menjadi tidak akurat. Oleh karena itu, memahami cara menjumlah data terfilter dengan benar sangat penting, terutama untuk laporan yang bersifat resmi dan sensitif seperti penggajian atau keuangan.
Pada artikel ini, kita akan membahas cara menjumlah data terfilter secara otomatis di Microsoft Excel menggunakan 3 rumus berbeda, mulai dari cara klasik hingga cara modern. Ketiga rumus ini menghasilkan nilai yang sama, tetapi memiliki pendekatan dan tingkat kemudahan yang berbeda. Contoh yang digunakan adalah rekap gaji pekerja proyek konstruksi, di mana hanya pekerja dengan status Aktif yang ditampilkan melalui filter.
Contoh Tabel: Rekap Gaji Pekerja Proyek
Misalkan Anda memiliki tabel seperti berikut di Excel:
Dan di sini, baris dengan status Resign dan Tidak Aktif akan disembunyikan menggunakan filter. Tujuan kita adalah menghitung total gaji yang harus dibayar, yaitu:
Gaji Harian × Hari Kerja,
hanya untuk baris yang terlihat (terfilter).
Di sinilah pentingnya memahami cara menjumlah data terfilter dengan rumus yang tepat.
Cara Menjumlah Data Terfilter dengan SUMPRODUCT dan SUBTOTAL (Metode Klasik)
Metode pertama dalam cara menjumlah data terfilter menggunakan kombinasi SUMPRODUCT, SUBTOTAL, dan OFFSET. Rumus yang digunakan di sel G14 adalah:
=SUMPRODUCT(SUBTOTAL(109;OFFSET(F3;ROW(F3:F12)-ROW(F3);));$E$3:$E$12)Dan jika benar, berikut adalah hasil akhirnya:
Penjelasan rumus:
OFFSET(F3;ROW(F3:F12)-ROW(F3);)
Digunakan untuk mengambil setiap sel di kolom Hari Kerja satu per satu.SUBTOTAL(109; … )
Angka 109 berarti menjumlahkan hanya data yang terlihat dan mengabaikan baris tersembunyi akibat filter.SUMPRODUCT()
Mengalikan nilai Hari Kerja dengan Gaji Harian lalu menjumlahkannya.
Metode ini adalah teknik lama tetapi sangat populer. Banyak tutorial Excel klasik membahas cara menjumlah data terfilter menggunakan pendekatan ini karena kompatibel dengan Excel versi lama.
Kelebihan:
- Cocok untuk Excel versi lama
- Hasil sangat akurat
Kekurangan:
- Rumus panjang
- Sulit dipahami oleh pemula
- Kurang efisien untuk file besar
Cara Menjumlah Data Terfilter dengan FILTER (Metode Modern)
Seiring berkembangnya Excel, Microsoft memperkenalkan fungsi FILTER yang membuat cara menjumlah data terfilter menjadi jauh lebih sederhana dan mudah dibaca. Rumus yang digunakan di sel G14 adalah:
=SUM(FILTER($E$3:$E$12*$F$3:$F$12;SUBTOTAL(103;OFFSET($F$3;ROW($F$3:$F$12)-ROW($F$3);0;1))))Dan jika benar, berikut adalah hasil akhirnya:
Penjelasan rumus:
$E$3:$E$12 * $F$3:$F$12
Menghitung gaji per pekerja (gaji harian × hari kerja).SUBTOTAL(103; … )
Angka 103 berfungsi sebagai pengecek baris yang masih terlihat.FILTER()
Menyaring hanya baris yang terlihat saja.SUM()
Menjumlahkan hasil akhir.
Pendekatan ini sangat cocok untuk pengguna Excel 365 atau Excel 2021 ke atas. Cara menjumlah data terfilter dengan metode ini jauh lebih intuitif dibandingkan metode klasik.
Kelebihan:
- Lebih ringkas
- Mudah dipahami
- Lebih cepat dihitung
Cara Menjumlah Data Terfilter dengan LET dan FILTER (Metode Profesional)
Metode ketiga adalah versi paling rapi dan profesional dari cara menjumlah data terfilter, yaitu menggunakan fungsi LET. Rumusnya yang digunakan di sel G14 adalah sebagai berikut:
=LET(
c;$F$3:$F$12;
b;$E$3:$E$12;
v;SUBTOTAL(103;OFFSET($F$3;ROW(c)-ROW($F$3);0;1));
SUM(FILTER(b*c;v))
)Dan jika benar, berikut adalah hasil akhirnya:
Penjelasan rumus:
LET()memungkinkan kita memberi nama variabel pada range.cmewakili Hari Kerjabmewakili Gaji Harianvadalah penanda baris yang terlihatFILTER(b*c;v)menyaring hasil perkalianSUM()menjumlahkan total gaji
Cara menjumlah data terfilter dengan LET sangat direkomendasikan untuk laporan besar, template gaji, dan file Excel yang sering diperbarui.
Kelebihan:
- Rumus paling bersih
- Mudah dirawat dan dikembangkan
- Performa lebih baik
Hasil Akhir Perhitungan
Dengan menggunakan ketiga cara menjumlah data terfilter di atas, hasil yang diperoleh adalah:
Total Harus Dibayar = 24.265.000
Nilai ini hanya menghitung pekerja yang Aktif dan tidak memasukkan baris yang tersembunyi, sehingga laporan gaji menjadi valid dan dapat dipertanggungjawabkan.
Kesimpulan
Memahami cara menjumlah data terfilter di Excel adalah keterampilan penting, terutama untuk laporan keuangan, gaji, dan proyek. Dalam artikel ini, kita telah membahas tiga pendekatan utama, mulai dari metode klasik menggunakan SUMPRODUCT, metode modern dengan FILTER, hingga metode profesional menggunakan LET. Ketiganya memiliki fungsi yang sama, tetapi tingkat kemudahan dan efisiensinya berbeda.
Jika Anda masih menggunakan Excel versi lama, metode pertama masih relevan. Namun, untuk pengguna Excel modern, cara menjumlah data terfilter dengan FILTER dan LET jauh lebih disarankan karena lebih ringkas, mudah dipahami, dan minim kesalahan. Dengan memilih metode yang tepat, Anda dapat memastikan bahwa hasil perhitungan selalu akurat meskipun data sering difilter atau diubah.
Download Template Gratis
Bagi Anda yang ingin mencoba teknik ini secara langsung, kami telah menyiapkan template gratis yang dirancang khusus untuk membantu Anda mempraktikkan 3 Cara Menjumlah Data Terfilter di Excel ini. File Excel bisa didownload melalui tautan berikut:
Atau juga jika ingin mempelajari langkah demi langkah secara visual, bisa kunjungi Youtube Channel Depot Excel. Di sana teman-teman akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!
—
0 Komentar