Jumat, 10 Agustus 2018

Rumus COUNTIF untuk Baris yang terlihat saja (Visible Rows only)

Rumus Excel [.] com - Fungsi COUNTIF biasanya digunakan untuk menghitung jumlah sel dalam suatu range atau rentang yang memenuhi kriteria tunggal yang telah kita tentukan sebelumnya, termasuk jika pada range selnya ada baris/sel yang tersembunyi tetap akan dihitung.

Agar baris-baris yang tersembunyi tidak ikut terhitung perlu cara khusus untuk menghitungnya, jika menyembunyikan barisnya dengan cara di filter kita bisa menggunakan gabungan beberapa fungsi Excel contohnya seperti ini:
=SUMPRODUCT((A2:B6="APEL")*(SUBTOTAL(3;OFFSET(A2;ROW(A2:B6)-MIN(ROW(A2:B6));0))))
Hasilnya kurang lebih seperti ini:

Rumus COUNTIF untuk Baris yang terlihat saja

Terlihat pada bagian kiri jumlah buah Apel ada 5 dan ketika di filter dengan menyembunyikan baris ke tiga hasilnya buah Apelnya berjumlah 4.

Namun cara ini ada kekurangannya yakni ketika menyembunyikan baris melalui menu Hide Rows atau bisa juga dengan keyboard Ctrl + 9 baris-baris yang tersembunyi akan tetap di hitung. Jadi cara ini hanya cocok untuk data pada baris excel yang disembunyikan dengan cara difilter saja.

Agar baris-baris yang tersembunyi tidak ikut di hitung, kita bisa mengakalinya dengan membuat fungsi sendiri atau istilahnya UDF (User Defined Function) yang bernama COUNTIFVISIBLE hasil akhirnya nanti akan terlihat seperti gambar animasi berikut ini:

Rumus COUNTIF untuk Baris yang terlihat saja
Ketika row atau baris disembunyikan otomatis juga akan diabaikan alias tidak ikut di hitung, untuk membuat fungsi UDF harus mengguanakan VBA atau Macro dan caranya adalah sebagai berikut:

  1. Buka aplikasi Microsoft Excel, kemudian tekan tombol Alt + F11 untuk membuka aplikasi Microsoft Visual Basic for Application.
  2. Tambahkan Module baru dengan cara Insert - Module.

    Insert Module pad Excel VBA
  3. Pada jendela Module (Code) masukkan script VBA berikut ini:
    Function COUNTIFVISIBLE(rentang As Range, Kriteria As Variant) As Long
        Dim rngSel As Range, hasil As Long
        For Each rngSel In rentang
            If Not (rngSel.EntireRow.Hidden Or rngSel.EntireColumn.Hidden) Then
                If rngSel.Value = Kriteria Then hasil = hasil + 1
            End If
        Next rngSel
        COUNTIFVISIBLE = hasil
    End Function
    
    Hasilnya kurang lebih seperti ini:

    Rumus COUNTIF untuk Baris yang terlihat saja

Pembuatan fungsi UDF sudah selesai, untuk penggunaannya adalah:
=COUNTIFVISIBLE (rentang; kriteria)
Contohnya seperti gambar di bawah ini:

Rumus COUNTIF untuk Baris yang terlihat saja
Agar kode Macro tidak hilang, maka ketika menyimpan workbook Anda harus menggunakan format yang bisa menyimpan macro yaitu .XLSM atau .XLSB atau bisa juga format .XLS, atau jika ingin dijadikan sebagai add ins maka bisa menyimpannya dalam format file .XLAM atau XLA.

Kita juga menambahkan teks bantuan pada fungsi UDF yang kita buat, supaya memudahkan agar tidak salah ketika memasukkan fungsi.

Rumus COUNTIF untuk Baris yang terlihat saja
Caranya adalah dengan memasukan kode vab berikut ini pada ThisWorkbook (code):
Private Sub Workbook_Open()
    Application.MacroOptions _
    Macro:="COUNTIFVISIBLE", _
    Category:=4, _
    ArgumentDescriptions:=Array( _
        "Rentang atau Range yang hitung", _
        "Kriteria yang diinginkan")
End Sub
Rumus COUNTIF untuk Baris yang terlihat saja
Keterangan:
Macro adalah nama fungsi UDF yang kita buat.
Category:= 4 artinya fungsi UDF kita akan di masukkan ke grup Statistical Functions.
ArgumentDescriptions untuk memasukkan teks bantuannya.

Rumus COUNTIF untuk Baris yang terlihat saja

Lampiran

COUNTIF VISIBLE ROW.xlsb   [ 17 KB ]

Sekian tutorial pembuatan fungsi UDF COUNTIF untuk menghitung sel excel yang terlihat saja, Semoga bermanfaat...
Comments


EmoticonEmoticon