Thursday, November 1, 2018

Menghitung Baris Terfilter Dengan Kriteria



Kali ini kita akan mengupas contoh rumus excel untuk menghitung baris terfilter dengan kriteria atau besyarat. Berbeda dengan contoh-contoh sebelumnya mengenai penjumlahan/perhitungan besyarat (misalnya menggunakan fungsi COUNTIF) hasilnya tidak mempedulikan apakah baris di-hide (tersembunyi) atau tidak, maka contoh rumus excel kali ini akan mencoba memberikan solusi supaya rumus hanya menghitung banyaknya data tertentu pada baris yang tidak tersembunyi atau yang tampak saja (visible  only) melalui proses autofilter.

Adapun rumus yang dapat digunakan untuk keperluan ini berupa kombinasi beberapa fungsi yang terdiri atas SUMPRODUCT, SUBTOTAL, OFFSET, dan ROW.

Contoh Kasus


Anggaplah kita memiliki sebuah tabel data peserta sebuah kegiatan. Data terdiri atas 4 Kolom yaitu kolom "No", "Nama", "Asal Kota", dan "Jenis Kelamin". Untuk contoh, supaya mudah ditampilkan dalam screenshot, saya gunakan tabel dengan jumlah baris yang sedikit saja. Tabel terletak pada range A4:D10 dan menggunakan autofilter pada kolom "Asal Kota" sehingga hanya menampilkan peserta yang berasa dari kota tertentu saja.

Pertanyaannya: Bagaimana cara menghitung jumlah peserta dengan jenis kelamin laki-laki ataupun perempuan pada tabel yang difilter?

Solusi atas contoh kasus ini dapat dilihat dalam screenshot di bawah ini.

menghitung baris terfilter dengan kriteria


Rumus Umum:


=SUMPRODUCT((kolomKriteria=kriteria)*SUBTOTAL(103,OFFSET(headerKriteria,ROW(kolomKriteria)-ROW(headerKriteria),0)))

Keterangan:

Secara umum rumus digunakan untuk menghitung banyaknya baris di kolomKriteria yang berisi text kriteria dan dalam kondisi visible (baris tidak tersembunyi)

  • kolomKriteria = merupakan range berupa kolom dalam tabel, range ini berisi text yang menjadi syarat atau kriteria dalam penjumlahan. Contoh: range D5:D11 yang berisi data jenis kelamin peserta. Meskipun baris terakhir dalam tabel contoh adalah baris 10, tapi kita gunakan range D5:D11 (baris terakhir 11) sebagai referensi kolomKriteria untuk memastikan referensi mencakup semua baris dalam tabel. Hal ini terutama jika kita menyeleksi referensi kolom dalam keadaan tabel terfilter karena mungkin saja baris terakhir tidak dapat terseleksi jika dalam kondisi tersembunyi.
  • kriteria = text yang harus ada supaya penjumlahan bisa dieksekusi. Misalnya: "Laki-Laki" ataupun sebuah referensi sel yang berisi text "Laki-Laki", contoh: sel B13.
  • headerKriteria = referensi sel yang merupakan header dari kolomKriteria, contohnya sel D4. Harap dipastikan bahwa headerKriteria ini tepat satu sel diatas referensi kolomKriteria, bukan merupakan bagian dari kolomKriteria. Perhatikan contoh sel D4, referensi sel tersebut tepat satu sel di atas referensi kolomKriteria D5:D11



Contoh Rumus:


=SUMPRODUCT(($D$5:$D$11=B13)*SUBTOTAL(103,OFFSET($D$4,ROW($D$5:$D$11)-ROW($D$4),0)))

Menjumlahkan jumlah baris pada range D5:D1 yang berisi text sesuai referensi B13 dan konsisi tampak visible atau tidak tersembunyi.

Cara Kerja Rumus


Dalam contoh rumus di atas, SUMPRODUCT berperan sebagai fungsi utama, dan kondisi dalam rumus SUMPRODUCT dapat dipecah menjadi 2 bagian seperti berikut ini:

=SUMPRODUCT(kolomKriteria_berisi_kriteria * Nampak_atau_tersembunyi)

kolomKriteria_berisi_kriteria diexpresikan dengan statement ($D$5:$D$11=B13) yaitu untuk mengecek apakah sel dalam range D5:D11 berisi text yang sama dengan text di sel B13 atau tidak. Jika iya maka akan menghasilkan nilai TRUE, jika tidak maka akan menghasikan nilai FALSE.

Untuk membuktikannya, jika anda sudah mencoba mengetikan rumus secara lengkap sesuai contoh. Silahkan diseleksi bagian rumus ($D$5:$D$11=B13) kemudian tekan F9 sehingga statement akan berubah menjadi array  {TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE} yang merupakan value atas pengecekan masing-masing baris pada kolomKriteria (D5:D11)

kolomKriteria baris ke-1 = "Laki-Laki"  (TRUE)
kolomKriteria baris ke-2 = "Perempuan"  (FALSE)
kolomKriteria baris ke-3 = "Laki-Laki"  (TRUE)
kolomKriteria baris ke-4 = "Laki-Laki"  (TRUE)    - tersembunyi
kolomKriteria baris ke-5 = "Laki-Laki"  (TRUE)
kolomKriteria baris ke-6 = "Perempuan"  (FALSE)  -tersembunyi
kolomKriteria baris ke-7 = ""  (FALSE)

Dapat kita perhatikan dari elemen array diatas, ternyata pengecekan value pada range D5:D11 tidak membedakan apakah baris dakam kondisi tersembunyi (hidden) atau tidak. Akibatnya: hasil TRUE akan diperoleh juga untuk baris tersembunyi berisi text "Laki-Laki" (baris ke-4).

excel autofilter visible only


Nampak_atau_tersembunyi : Supaya hanya bisa menghitung jumlah data laki-laki dalam baris tersembunyi, maka kita harus membuat statement ke-2 yang bisa mengecek apakah baris tersembunyi atau tidak.

Tugas ini diserahkan pada bagian rumus sebagai berikut:

SUBTOTAL(103,OFFSET($D$4,ROW($D$5:$D$11)-ROW($D$4),0))

Fungsi SUBTOTAL dengan kode 103 digunakan untuk menghitung banyaknya data yang tidak tersembunyi. Dengan kata lain, jika visible atau tidak tersembunyi maka masing-masing baris yang berisi data akan bernilai 1, sebaliknya akan bernilai 0.

Fungsi OFFSET digunakan sebagai patokan langkah pengecekan sel.

OFFSET(D4, ROW(D5)-ROW(D4),0) = OFFSET(D4,1,0) = baris ke-1 di bawah sel D4
OFFSET(D4, ROW(D6)-ROW(D4),0) = OFFSET(D4,2,0) = baris ke-2 di bawah sel D4
OFFSET(D4, ROW(D7)-ROW(D4),0) = OFFSET(D4,3,0) = baris ke-3 di bawah sel D4
OFFSET(D4, ROW(D8)-ROW(D4),0) = OFFSET(D4,4,0) = baris ke-4 di bawah sel D4
OFFSET(D4, ROW(D9)-ROW(D4),0) = OFFSET(D4,5,0) = baris ke-5 di bawah sel D4
OFFSET(D4, ROW(D10)-ROW(D4),0) = OFFSET(D4,6,0) = baris ke-6 di bawah sel D4
OFFSET(D4, ROW(D11)-ROW(D4),0) = OFFSET(D4,7,0) = baris ke-7 di bawah sel D4

Kembali ke contoh rumus lengkapnya:

=SUMPRODUCT(($D$5:$D$11=B13)*SUBTOTAL(103,OFFSET($D$4,ROW($D$5:$D$11)-ROW($D$4),0)))

Dalam kondisi tabel terfilter Asal Kota Bandung, silahkan diseleksi bagian rumus yang berwarna merah, dan kemudian tekan F9. Maka kita akan melihat array {1;1;1;0;1;0;0} yang menggambarkan kondisi baris apakah berisi data dan tersembunyai atau tidak.

Baris ke-1 = berisi data dan tampak >> Nilai 1
Baris ke-2 = berisi data dan tampak >> Nilai 1
Baris ke-3 = berisi data dan tampak >> Nilai 1
Baris ke-4 = berisi data dan tersembunyi >> Nilai 0
Baris ke-5 = berisi data dan tampak >> Nilai 1
Baris ke-6 = berisi data dan tersembunyi >> Nilai 0
Baris ke-7 = data kosong dan tampak >> Nilai 0

Langkah berikutnya: 2 buah array yang merupakan hasil dari 2 proses sebelumnya diproses oleh fungsi SUMPRODUCT.  Fungsi ini bertugas untuk mengalikan masing-masing element array yang seposisi kemudian menjumlahkan hasil masing-masing perkalian tersebut. Secara ringkas dapat dilihat dalam langkah rumus berikut:

=SUMPRODUCT({TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE}*{1;1;1;0;1;0;0})
=(TRUE*1)+(FALSE*1)+(TRUE*1)+(TRUE*0)+(TRUE*1)+(FALSE*0)+(FALSE*0)
= 1 + 0 + 1 + 0 + 1 + 0 + 0
= 3

Sampai pada tahap ini selesai sudah pembahasan mengenai cara menghitung baris terfilter dengan kriteria. Mudah-mudahan penjelasan yang singkat ini dapat mudah difahami dan tentunya semoga bermanfaat.


Contoh Rumus Excel Lainnya