Cara menggunakan fungsi COUNT atau COUNTA pada daftar data yang difilter agar baris tersembunyi tidak disertakan dalam hitungan
Untuk melakukannya, gunakan fungsi SUBTOTAL()
Bagian
Sintaksis
=SUBTOTAL(2, range_to_count)2 memberi tahu fungsi untuk melakukan penghitungan
range_to_count adalah rentang yang ingin Anda hitung
COUNTA
Gunakan 3 daripada 2 untuk melakukan COUNTA, bukan hanya COUNT biasa
Hitung - Data yang Difilter
=SUBTOTAL(2,B4:B8) _Ini menghitung sel yang masih terlihat, setelah filter diterapkan, dalam rentang B4. B8
Hasil
Ini memperbarui setiap kali filter ditambahkan atau dihapus dan juga berfungsi saat tidak ada filter sama sekali yang digunakan
Lihat contoh berikutnya untuk mengetahui cara menghindari penghitungan baris yang disembunyikan secara manual
Hitung - Kecualikan Baris Tersembunyi Secara Manual
Contoh di atas tidak berfungsi saat baris disembunyikan secara manual - klik kanan baris dan klik Sembunyikan untuk menyembunyikan baris secara manual
Untuk memastikan fungsi tidak menghitung baris yang disembunyikan secara manual, kita harus mengubah argumen pertama fungsi
=SUBTOTAL(102,B4:B8)102 adalah 2 pada contoh pertama. Mengubah ini menjadi 102 membuat fungsi mengabaikan data yang difilter dan juga data yang disembunyikan secara manual
Tidak ada lagi yang diubah
COUNTA
Jika menggunakan fitur COUTNA, ubah 3 menjadi 103
Fungsi Agregat - Cara Baru untuk Menghitung
=AGGREGATE(2,3,B5:B9)Ini adalah versi fungsi SUBTOTAL yang lebih baru dan lebih canggih yang dapat melakukan lebih banyak penghitungan dan mencakup lebih banyak opsi penghitungan
2 adalah angka yang menyatakan perhitungan mana yang harus dilakukan
3 adalah opsi untuk dipilih, termasuk mengabaikan kesalahan dan fungsi SUBTOTAL dan AGREGAT lainnya
B5. B9 adalah rentang untuk melakukan perhitungan
Saat Anda memasukkan fungsi ini, Anda akan melihat daftar opsi untuk dua argumen pertama, dan keduanya sangat jelas dan mudah dipahami
Catatan
Fungsionalitas COUNT hanya menghitung sel yang berisi angka
Fungsionalitas COUNTA menghitung semua sel yang tidak kosong
Karena itu, mungkin lebih baik Anda menggunakan fungsionalitas COUNTA (baik 3 atau 103) untuk fungsi SUBTOTAL
Fungsi SUBTOTAL memungkinkan Anda menerapkan sejumlah fungsi reguler ke data yang difilter, untuk daftar lengkap dan penjelasannya, lihat tutorial kami tentang fungsi SUBTOTAL di Excel
Di bilah rumus, Anda dapat memilih setiap rumus (A, B, dan C), dan tekan F9 untuk menghitung rumus tersebut. Tangkapan layar ini menunjukkan hasilnya
Kemudian, hitung bagian A/B dari rumus tersebut, untuk melihat hasil ini. BENAR sama dengan 1, jadi 1/1 sama dengan 1, dan 1/2 sama dengan 0. 5
Selanjutnya, hitung A/B - C, untuk melihat hasil tersebut. BENAR adalah 1 dan SALAH adalah nol, jadi 1-1 sama dengan nol, dan 0. 5 - 0 sama dengan 0. 5
Terakhir, fungsi SUMPRODUCT memberikan jumlah dari angka-angka tersebut, dengan hasil 2
Formula Lembar KerjaAnda dapat melihat hasil yang sama jika Anda meletakkan setiap bagian rumus di lembar kerja, dan menghitung setiap baris secara terpisah
Di tangkapan layar ini, Anda dapat melihat hasil A dan B, lalu A/B. Selanjutnya, lihat perhitungan C, dan A/B-C. Di bagian bawah kolom terakhir, jumlahnya ditampilkan
Gunakan Hasil Rumus
Di bagian lain buku kerja Anda, Anda bisa merujuk ke sel A4 (DupIds), untuk membuat pesan peringatan, atau memperlihatkan nol, bukan hasil yang diharapkan. Misalnya, tampilkan pesan dengan rumus seperti ini
Dalam contoh ini, tujuannya adalah menghitung baris yang terlihat dan mengabaikan baris yang tersembunyi. Ini adalah tugas untuk fungsi SUBTOTAL. SUBTOTAL dapat melakukan berbagai perhitungan seperti COUNT, SUM, MAX, MIN, dan lainnya. Apa yang membuat SUBTOTAL menarik dan bermanfaat adalah ia secara otomatis mengabaikan item yang tidak terlihat dalam daftar atau tabel yang difilter. Hal ini membuatnya ideal untuk menjalankan penghitungan pada baris yang terlihat di data yang difilter
Hitung dengan SUBTOTAL
Mengikuti contoh di lembar kerja di atas, untuk menghitung jumlah baris tidak kosong yang terlihat saat filter aktif, gunakan rumus seperti ini
=SUBTOTAL(3,B7:B16) _Argumen pertama, function_num, menentukan hitungan sebagai operasi yang akan dilakukan. SUBTOTAL mengabaikan 3 baris yang disembunyikan oleh filter dan mengembalikan 7 sebagai hasilnya, karena ada 7 baris yang terlihat
Perhatikan bahwa SUBTOTAL selalu mengabaikan nilai dalam sel yang disembunyikan dengan filter. Nilai dalam baris yang telah "difilter" tidak pernah disertakan, terlepas dari function_num. Jika Anda menyembunyikan baris secara manual (mis. e. klik kanan > Sembunyikan), dan tidak menggunakan kontrol filter , gunakan versi rumus ini sebagai gantinya
=SUBTOTAL(103,B7:B16)Dengan function_num disetel ke 103, SUBTOTAL masih melakukan penghitungan, tetapi kali ini akan mengabaikan baris yang disembunyikan secara manual, serta yang disembunyikan dengan filter. Agar jelas, nilai dalam baris yang telah disembunyikan dengan filter tidak pernah disertakan, terlepas dari function_num
Fungsi SUBTOTAL dapat melakukan banyak perhitungan lainnya. Untuk melihat daftar semua perhitungan yang dapat dilakukan SUBTOTAL, lihat halaman ini