Excel menghitung sel yang difilter dengan teks

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 Kerja

Anda 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

Bagaimana cara menghitung hanya sel yang difilter di Excel?

Bagaimana cara menggunakan Countif dengan data yang difilter?

Bagaimana Anda menghitung sel di Excel jika berisi teks?

Bagaimana cara membuat Excel menghitung sel dengan teks secara otomatis?