Mahir Fungsi Excel: FILTER

Fitur-fitur Microsoft Excel saat ini berkembang cepat serta menyediakan berbagai fungsi yang memudahkan pengguna dalam menganalisis dan mengolah data. Salah satu fitur penting yang diperkenalkan sejak Excel 2019 ke atas dan Office 365 adalah Dynamic Array Functions. Fitur ini mengubah cara kita bekerja dengan data di Excel menjadi lebih fleksibel dalam proses pengolahan data, salah satunya adalah fungsi FILTER.

Fungsi FILTER memungkinkan kita untuk menyaring data dalam sebuah rentang berdasarkan kondisi yang ditentukan. Keunggulan dari fungsi ini adalah kemampuannya untuk mengembalikan hasil dinamis—artinya, jika nilai-nilai dari sumber datanya diubah, hasil filter juga akan otomatis diperbarui tanpa perlu mengubah rumus atau melakukan refresh manual.

Beberapa keunggulan fungsi FILTER adalah sebagai berikut:

• Hasil Dinamis: Filter secara otomatis ter-update jika data sumber berubah
• Lebih Fleksibel: Bisa digunakan dengan satu atau banyak kriteria sekaligus
• Cocok untuk Data Besar: Membuat penyaringan data tanpa perlu manual filtering

Fungsi FILTER termasuk dalam kategori Dynamic Array Functions, yang berarti hasilnya akan mengisi sel secara otomatis sesuai dengan jumlah data yang memenuhi kriteria. Kita hanya perlu menuliskan formula pada satu sel saja, dan Excel akan menampilkan semua hasil tanpa perlu melakukan copy-paste atau drag formula pada sel lainnya. Kemudian, jika jumlah hasil berubah karena sumber datanya diperbarui, rentang hasil juga akan berubah secara otomatis.

Formula umum fungsi FILTER adalah sebagai berikut:

=FILTER(array, include, [if_empty])

Keterangan

• array – Rentang data yang ingin difilter
• include – Kriteria yang digunakan untuk menyaring data
• [if_empty] (opsional) – Nilai yang dikembalikan jika tidak ada data yang memenuhi kriteria. Jika nilainya tidak diisi, Excel akan menampilkan error #CALC

Parameter include menentukan baris mana yang akan ditampilkan dalam hasil filter. Ekspresi ini harus menghasilkan array nilai TRUE dan FALSE untuk setiap baris dalam rentang yang disaring.

Beberapa contoh isian yang valid untuk include adalah:

• D3:D22="Indonesia": menyaring baris-baris di mana isian pada D3:D22 adalah Indonesia
• E3:E22>80: menyaring baris-baris dengan nilai pada E3:E22 lebih dari 80
• (D3:D22<>"Indonesia")*(D3:D22<>"Malaysia"): menyaring data di mana isian pada D3:D22 tidak sama dengan Indonesia DAN juga bukan Malaysia (semua kondisi harus terpenuhi)
• (E3:E22>=95)+(F3:F22>=95)+(G3:G22>=95): menyaring data di mana isian pada E3:E22 lebih dari sama dengan 95 ATAU nilai F3:F22 lebih dari sama dengan 95, ATAU G3:G22 lebih dari sama dengan 95 (setidaknya salah satu kondisi harus terpenuhi)
• E3:E22<AVERAGE(E3:E22): menyaring data dengan nilai isian pada E3:E22 lebih kecil dari nilai rata-ratanya
• MOD(E3:E22,2)=0: menyaring data dengan nilai isian pada E3:E22 habis dibagi 2 (bilangan genap)
• BYROW(E3:G22;AVERAGE)>80: menyaring data dengan nilai rata-rata dari kolom E, F dan G lebih dari 80
• ISNUMBER(SEARCH("sia", D3:D22)): pilih jika isian pada D3:D22 mengandung teks "sia"

Misalkan terdapat data peserta suatu kompetisi dari berbagai negara di ASEAN dengan hasil sebagai berikut:

Filter untuk menampilkan data peserta dari "Indonesia" saja:

Formula

=FILTER(B3:G22, D3:D22="Indonesia", "Tidak Ada")

Filter untuk menampilkan data peserta dengan nilai Tes 1 lebih dari 80:

Formula

=FILTER(B3:G22, E3:E22>80, "Tidak Ada")

Filter untuk menampilkan data peserta yang BUKAN dari "Indonesia" dan "Malaysia" (menggunakan operator * untuk memilih data yang memenuhi semua kondisi):

Formula

=FILTER(B3:G22, (D3:D22<>"Indonesia")*(D3:D22<>"Malaysia"), "Tidak Ada")

Filter untuk menampilkan data peserta dengan nilai Tes 1, Tes 2 dan Tes 3 seluruhnya lebih dari 70:

Formula

=FILTER(B3:G22, (E3:E22>70)*(F3:F22>70)*(G3:G22>70), "Tidak Ada")

Filter untuk menampilkan data peserta yang mendapat nilai lebih dari sama dengan 95 setidaknya pada salah satu Tes (menggunakan operator + untuk memilih data yang memenuhi setidaknya salah satu kondisi):

Formula

=FILTER(B3:G22, (E3:E22>=95)+(F3:F22>=95)+(G3:G22>=95), "Tidak Ada")

Filter untuk menampilkan data peserta dengan nilai Tes 1 lebih kecil dari rata-rata nilai Tes 1 dari seluruh peserta (jika dihitung, rata-rata nilai Tes 1 adalah 76,55; jadi akan difilter data Tes 1 yang kurang dari 76,55):

Formula

=FILTER(B3:G22, E3:E22<AVERAGE(E3:E22), "Tidak Ada")

Selain melalui seleksi nilai range secara langsung, dapat juga menggunakan kombinasi fungsi lainnya. Misalnya SEARCH dan ISNUMBER. Fungsi SEARCH digunakan untuk mendapatkan posisi/indeks dari kata yang dicari. Jika ditemukan maka fungsi akan mengembalikan nilai indeksnya dan jika tidak maka mengembalikan #VALUE!. Berikutnya, fungsi ISNUMBER akan mengembalikan nilai TRUE jika hasilnya dari SEARCH berupa angka (artinya, kata yang dicari ditemukan).

Contoh, filter untuk menampilkan data dari negara yang mengandung kata "sia":

Formula

=FILTER(B3:G22, ISNUMBER(SEARCH("sia", D3:D22)), "Tidak Ada")

Menggunakan fungsi BYROW, kita dapat melakukan kalkulasi setiap baris untuk beberapa kolom sekaligus. Misalkan menghitung nilai rata-rata, median, minimum, maksimum dan sebagainya untuk setiap baris dari beberapa kolom yang diberikan.

Contoh, filter untuk menampilkan data di mana nilai rata-rata dari ketiga Tes (Tes 1, Tes 2 dan Tes 3) untuk setiap barisnya lebih dari 80 (tanpa perlu membuat kolom baru untuk menyimpan nilai rata-ratanya):

Formula

=FILTER(B3:G22, BYROW(E3:G22, AVERAGE)>80, "Tidak Ada")

Download: Workbook contoh fungsi FILTER

• Mahir Fungsi Excel: SORT dan SORTBY
• Mahir Fungsi Excel: XLOOKUP
• Mahir Fungsi Excel: BYROW dan BYCOL