Rumus FILTER Excel

Punya data dan mau saring data tersebut berdasarkan kriteria tertentu?

Pilihan awal tentu saja Auto Filter.

Tapi, bila teman-teman ingin agar perubahan pada data sumber juga tecermin pada data hasil saringan, maka gunakan rumus FILTER Excel.

File: fungsi-filter-excel.xlsx.

Apakah Fungsi FILTER pada Excel Itu?

Fungsi FILTER pada Excel adalah fungsi untuk mengambil sebagian data dari suatu range data berdasarkan kriteria tertentu.

Fungsi ini mengembalikan hasil berupa array.

Selain itu, fungsi FILTER hanya terdapat pada Excel 365.

Sintaks

FILTER(array; include; [if_empty])

• array (wajib) adalah array atau range yang akan disaring.
• include (wajib) adalah kriteria Boolean berupa evaluasi terhadap kategori data tertentu di dalam range.
  • Tinggi array include harus sama (untuk orientasi data vertikal) dengan array atau sama lebar (untuk horisontal).
  • Data yang dikembalikan adalah data dengan hasil evaluasi TRUE.
• if_empty (opsional), bila diisi, adalah nilai yang akan ditampilkan bila hasil penyaringan tidak mengembalikan apa pun.

Bila bingung dengan kriteria, apalagi kriteria yang panjang, saran kami adalah menjalankan kriteria sebagai rumus tersendiri. Bila perlu, bongkar setiap bagian kriteria dan eksekusi bagian-bagian tersebut secara terpisah.

Cara Menggunakan Rumus FILTER Excel

Dalam bentuk standar, rumus FILTER Excel cukup sederhana, seperti terlihat di bawah ini.

=FILTER(A2:D11; B2:B11="Juni")

Berikut penjelasan rumus:

1. A2:D11 adalah range data yang akan ditampilkan berdasarkan hasil kriteria.
2. B2:B11="Juni" adalah kriteria Boolean. Kriteria ini menyaring nilai-nilai di B2:B11 untuk penjualan di bulan Juni.

FILTER akan menampilkan baris-baris pada A2:D11 yang sesuai dengan baris-baris di mana Juni ditemukan di B2:B11.

Untuk membantu memahaminya, perhatikan gambar berikut.

Baris-baris (bila data berorientasi vertikal) yang diambil adalah baris-baris di mana hasil include adalah TRUE (bandingkan dengan hasil Gambar 02).

Untuk menghindari penggunaan hardcode, kita dapat mengganti nilai pembanding dengan referensi cell.

=FILTER(A2:D11; B2:B11=G1)

Bila ada kemungkinan data yang dicari tidak ketemu, pastikan parameter opsional if_empty terisi, untuk menghindari pesan error #CALC!.

=FILTER(A2:D11; B2:B11=G1; "Data kosong.")

Selain perbandingan sama dengan, kita dapat menggunakan tidak sama dengan (<>), lebih besar dari (>), lebih besar dari atau sama dengan (>=), lebih kecil dari (<), atau lebih kecil dari atau sama dengan (<=).

Pada prakteknya, kriteria tidak harus menggunakan operator perbandingan. Operasi Boolean apa pun dapat berfungsi sebagai kriteria.

Rumus FILTER berikut mencari data dengan nama penjual tidak terisi atau blank atau kosong.

=FILTER(A2:D11; ISBLANK(A2:A11); "Data tidak ada.")

Sementara itu, rumus berikut mencari apakah ada nilai penjualan yang bukan angka.

=FILTER(A2:D11; NOT(ISNUMBER(D2:D11)); "Data tidak ada.")

Seperti kriteria perbandingan, penyaringan dengan fungsi seperti ISBLANK, ISNUMBER, atau NOT akan dijalankan bila kriteria bernilai benar atau TRUE.

Cara FILTER Data di Excel dengan Rumus

Bila teman-teman sudah paham bagaimana menggunakan bentuk dasar rumus FILTER Excel, saatnya belajar menuliskannya dalam bentuk lebih kompleks.

Rumus FILTER Beberapa Kriteria

Sejauh ini kita sudah tau gimana pakai rumus FILTER dengan satu kriteria. Excel memungkinkan kita menggunakan FILTER dengan dua kriteria atau lebih.

Untuk menggabungkan setiap kriteria, gunakan ekspresi logika And atau Or.

Rumus FILTER Kriteria And

Ekspresi And bernilai TRUE bila seluruh kriteria bernilai benar (TRUE). Satu saja (atau semuanya) bernilai salah (FALSE), maka ekspresi secara keseluruhan menjadi salah.

Penggabungan And diwujudkan menggunakan operator perkalian (*).

=FILTER(A2:D11; (B2:B11=G1)*(C2:C11=G2); "Data Tidak Ada.")

Ingat agar selalu menempatkan setiap kriteria di dalam tanda kurung.

Rumus di atas terdiri dari dua kriteria, yaitu B2:B11=G1 dan C2:C11=G2. Artinya, data yang diambil adalah data untuk penjualan di bulan Juni  (B2:B11=G1) DAN berupa apartemen (C2:C11=G2).

Bagaimana ekpresi And di rumus FILTER Excel bekerja?

Di Excel, operasi aritmetika seperti perkalian mengubah TRUE menjadi 1 dan FALSE menjadi 0.

Untuk ekspresi dengan dua kriteria, bila semua kriteria bernilai TRUE, maka TRUE*TRUE → 1*1 = 1. Angka 1 sendiri bernilai Boolean TRUE yang berarti ada data yang sesuai kriteria.

Bila salah satu bernilai salah, maka FALSE*TRUE → 0*1 = 0. Karena 0 sama dengan FALSE, berarti tidak ada data yang sesuai kriteria.

Rumus FILTER Kriteria OR

Rumus FILTER Excel dengan ekspresi Or dilakukan menggunakan tanda tambah (+).

=FILTER(A2:D11; (B2:B11=G1)+(C2:C11=G2); "Data kosong")

Rumus di atas mencari penjualan di bulan Juni (B2:B11=G1) ATAU penjualan apartemen (C2:C11=G2).

Seperti FILTER dengan operasi And, konsep yang sama berlaku pada operasi Or. Operasi aritmetika akan mengubah TRUE menjadi 1 dan FALSE menjadi 0.

Operasi Or akan bernilai salah bila semua kriteria bernilai salah, yaitu FALSE+FALSE → 0+0 = 0. Dalam konteks FILTER, rumus tidak akan mengembalikan data apa pun.

Bila satu saja benar, maka seluruh pernyataan bernilai benar atau TRUE.

Rumus FILTER Kriteria Gabungan

Beberapa kriteria di dalam rumus FILTER tidak melulu hanya digabungkan dengan And atau Or, tapi bisa keduanya.

Rumus di bawah mencari data dengan nominal di atas 10,000,000 untuk penjualan apartemen atau di bulan Juni.

=FILTER(A2:D11; ((B2:B11=G1)+(C2:C11=G2))*(D2:D11>G3); "Data kosong.")

Saat menggunakan lebih dari satu kriteria, kriteria-kriteria di dalam kurung akan dijalankan terlebih dahulu, baru digabungkan dengan kriteria lain. Karenanya, pastikan penempatan kurung dengan tepat.

Bila bingung, jalankan kriteria sebagai rumus tersendiri atau pilah-pilah menjadi bagian-bagian rumus yang lebih kecil.

Rumus FILTER Tanggal di Excel

Untuk kriteria tanggal, gunakan tanggal dengan format yang berlaku di komputer bersangkutan. Format di satu komputer belum tentu berlaku di komputer lain. Agar aman, gunakan fungsi DATE untuk entri tanggal.

=FILTER(A2:D11; (B2:B11<=G1); "Data kosong.")

Rumus terlampir di atas mencari data dengan tanggal transaksi sampai 31 Maret 2024.

Rumus Excel SORT dan FILTER

Bagaimana jika kita mau hasil FILTER diurutkan tanpa harus menggunakan fitur pengurutan?

Gunakan fungsi SORT.

Dengan mengambil rumus yang sudah ada dan membungkusnya dengan SORT, kita mendapatkan rumus berikut.

=SORT(FILTER(A2:D11; (B2:B11<=G1); "Data kosong"); 2)

Rumus mengurutkan data berdasarkan tanggal penjualan, yang berada di kolom kedua.

Rumus SUM dengan FILTER

Jika hasil rumus FILTER Excel adalah array angka, kita dapat menghitung hasil ini dengan fungsi matematika dan agregat seperti SUM, MIN, MAX, atau AVERAGE.

Rumus di bawah mencari nilai-nilai penjualan (D2:D11) hanya untuk tanah dan menjumlahkannya dengan SUM.

=SUM(FILTER(D2:D11; (C2:C11=G1); 0))

Cara FILTER Duplikat di Excel

Data duplikat tidak bisa dihindari. Kemungkinannya selalu ada, terutama bila jumlah data sangat besar.

Untuk mencari duplikasi data dengan rumus, tentukan kolom-kolom data yang menjadi kriteria duplikasi lalu buat rumus dengan kombinasi FILTER dan COUNTIFS, seperti di bawah ini.

=FILTER(A2:D13; COUNTIFS(A2:A13; A2:A13; B2:B13; B2:B13; C2:C13; C2:C13)>1; "Tidak ada data.")

Teman-teman yang familiar dengan COUNTIFS mungkin bertanya kenapa ada dua range yang sama di dalam COUNTIFS atau mengapa tidak ada kriteria di dalam COUNTIFS.

Seperti sudah kami sampaikan sebelumnya, bila bingung dengan kriteria, jalankan kriteria sebagai rumus tersendiri.

Perhatikan potongan rumus berikut.

=COUNTIFS(A2:A13; A2:A13)

Range kedua adalah kriteria. Seperti FILTER, COUNTIFS juga menyaring data sumber sesuai dengan hasil kriteria. Berhubung range kedua menampilkan seluruh baris, berarti COUNTIFS akan menghitung berdasarkan seluruh baris dari range pertama.

=COUNTIFS(A2:A13; A2:A13; B2:B13; B2:B13; C2:C13; C2:C13)

Potongan rumus di atas membandingkan setiap kolom yang menjadi kriteria duplikasi. COUNTIFS kemudian menghitung seberapa banyak perulangan terjadi. Hasil FILTER yang ditampilkan adalah mereka yang memiliki hitungan COUNTIFS di atas satu.

Cara FILTER Teks di Excel

Pada data besar, kita mungkin saja mau filter data yang memiliki teks tertentu. Contohnya, kita mau saring data untuk klien dengan nama yang mengandung kus untuk mencari Kuswono, Kusnanto, atau Kusnadi.

=FILTER(A2:D11; ISNUMBER(SEARCH(G1; A2:A11)); "Tidak ada data.")

Berikut penjelasan rumus.

Potongan Rumus	Penjelasan	Hasil
SEARCH(G1; A2:A11)	Mencari in (G1) di range A2:A11. Bila teks pada baris tertentu memiliki in, SEARCH akan mengembalikan angka yang menandakan posisi in di dalam teks tersebut. Bila tidak, SEARCH akan mengembalikan pesan error #VALUE!.	Array posisi in dari setiap nilai pada A2:A11.
ISNUMBER(SEARCH(G1; A2:A11))	ISNUMBER mengubah hasil SEARCH menjadi nilai Boolean TRUE atau FALSE. Bila hasil SEARCH adalah angka, maka ISNUMBER mengembalikan TRUE untuk baris bersangkutan. Bila hasil SEARCH adalah #VALUE!, maka FALSE untuk baris bersangkutan.	Array Boolean sepanjang baris A2:A11.

Pada akhirnya, array Boolean hasil ISNUMBER digunakan untuk menyaring baris-baris yang memiliki penjual yang namanya mengandung in.

Kenapa Rumus FILTER Excel Tidak Berfungsi

Rumus Filter Tidak Ada di Excel

Saat ini, fungsi FILTER hanya ada di Excel 365.

Bagi teman-teman pengguna Excel selain versi tersebut, mengetikkan =FILTER tidak akan memberikan rekomendasi fungsi apa pun.

Catatan: Kami tidak tahu apakah Excel 2024 memiliki fungsi ini atau tidak. Mohon informasi bila teman-teman menggunakannya.

Pesan Error #NAME!

Menuliskan langsung rumus FILTER di Excel selain 365 akan memberikan pesan error #NAME! segera setelah Enter ditekan.

Pesan Error #CALC!

Pesan error ini terjadi saat if_empty tidak disediakan dan FILTER tidak mengembalikan hasil apa pun.

Bila ada kemungkinan FILTER tidak mengembalikan apa pun, pastikan if_empty terisi.

Pesan Error #SPILL!

Rumus FILTER Excel menghasilkan array.

Bila salah satu cell di mana hasil rumus FILTER tertumpah memiliki isi, maka pesan error ini akan hadir.

Pesan Error #VALUE!

FILTER mensyaratkan agar include memiliki jumlah baris atau kolom yang sama banyak dengan array, tergantung arah pencarian yang kita lakukan (horisontal atau vertikal).

Bila berbeda, maka pesan error #VALUE! tampil.

#VALUE juga dapat tampil bila kriteria di include menghasilkan error atau tidak bisa diterjemahkan sebagai nilai Boolean.

Pesan Error #N/A

Seperti penyebab error #VALUE!, #N/A tampil saat ada error di include atau hasil include gagal diinterpretasikan sebagai nilai Boolean.

Pesan Error #REF!

Saat menggunakan rumus FILTER beda file, pastikan agar semua file terbuka. Bila tidak, pesan error ini punya kemungkinan hadir.