Rumus FILTER Excel: Banyak Kriteria, AND dan OR

=FILTER(A2:C7,B2:B7="North") menghasilkan setiap baris A2:C7 yang wilayahnya di kolom B adalah North. Kamu mengetiknya di satu sel, lalu baris-baris yang cocok tampil sebagai spill ke sel di bawah dan di sebelah kanannya. Ubah sebuah wilayah di kolom B menjadi North, atau North menjadi South, dan daftarnya langsung diperbarui.

RumusReset

E2fx

=FILTER(A2:C7,B2:B7="North")

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Finn	North	60
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

Hanya E2 yang berisi rumus. Sel-sel terisi lainnya di E:G adalah hasil spill-nya: klik F3 dan kamu akan melihat sel itu milik rumus di E2. Kalau ada sesuatu yang diketik di area itu, FILTER menampilkan #SPILL!, bukan baris-barisnya (lihat error #SPILL!).

Sintaks FILTER

=FILTER(array, include, [if_empty])

• array adalah yang ingin kamu dapatkan kembali: satu kolom, beberapa kolom, atau seluruh tabel.
• include adalah kondisi dengan satu TRUE atau FALSE untuk setiap baris di array, seperti B2:B7="North". Jumlah barisnya harus persis sama dengan array. (Untuk menyaring kolom, berikan satu nilai per kolom.)
• if_empty adalah apa yang ditampilkan kalau tidak ada baris yang cocok. Tanpa argumen ini, hasil yang kosong menjadi error #CALC!.

FILTER butuh Excel 2021, Excel 2024, atau Microsoft 365. Di Excel 2019 dan versi sebelumnya, fungsi ini menampilkan #NAME?, dan tombol Filter di tab Data adalah cara menyaring di sana. Google Sheets juga punya FILTER, dan di sana setiap kondisi juga bisa diberikan sebagai argumen terpisah.

Perbandingan teks tidak membedakan huruf besar kecil: B2:B7="north" cocok dengan North. FILTER mempertahankan urutan asli baris-barisnya; mengurutkan hasilnya adalah langkah terpisah, seperti ditunjukkan lebih jauh di bawah.

Filter berdasarkan nilai sel

Menulis "North" langsung di rumus berarti harus mengedit rumusnya setiap kali. Taruh nilainya di sebuah sel lalu bandingkan dengan sel itu. Pilih wilayah lain di F1 dan hasilnya ikut berubah:

RumusReset

E3fx

=FILTER(A2:C7,B2:B7=F1,"No sales")

	A	B	C	D	E	F	G
1	Name	Region	Sales		Region	North▾
2	Ann	North	120
3	Ben	South	80		Ann	North	120
4	Cara	North	200		Cara	North	200
5	Dan	East	150		Finn	North	60
6	Eve	South	95
7	Finn	North	60

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

West tidak punya baris, jadi memilihnya akan menampilkan teks if_empty, yaitu No sales.

Angka bekerja dengan cara yang sama. C2:C7>=F1 dengan 100 di F1 mempertahankan setiap baris dengan penjualan minimal 100, dan C2:C7>F1 membuatnya menjadi lebih besar dari, tanpa sama dengan.

FILTER dengan banyak kriteria (AND)

Untuk mempertahankan sebuah baris hanya kalau dua kondisi sama-sama benar, kalikan keduanya. Rumus ini menghasilkan baris North dengan penjualan di atas 100:

RumusReset

E2fx

=FILTER(A2:C7,(B2:B7="North")*(C2:C7>100))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

Ann (120) dan Cara (200) lolos. Finn berasal dari North tetapi 60 miliknya tidak di atas 100, jadi ia tidak disertakan.

Kenapa dikalikan: setiap kondisi adalah sebuah kolom berisi TRUE dan FALSE, dan dalam aritmetika TRUE dihitung sebagai 1 dan FALSE sebagai 0. Sebuah baris hanya mendapat 1 kalau setiap faktornya bernilai 1, jadi * bekerja sebagai AND. Setiap kondisi butuh tanda kurungnya sendiri, dan kamu bisa merangkai sebanyak yang kamu mau: (B2:B7="North")*(C2:C7>100)*(C2:C7<500).

AND() tidak berfungsi di sini. AND(B2:B7="North",C2:C7>100) meringkas seluruh rentang menjadi satu TRUE atau FALSE saja, bukan satu per baris, jadi FILTER mendapat bentuk yang salah.

FILTER dengan OR

Jumlahkan kondisinya untuk mempertahankan baris yang minimal satu kondisinya benar. Rumus ini menghasilkan baris North dan East:

RumusReset

E2fx

=FILTER(A2:C7,(B2:B7="North")+(B2:B7="East"))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Dan	East	150
5	Dan	East	150		Finn	North	60
6	Eve	South	95
7	Finn	North	60

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

Baris yang memenuhi kedua kondisi akan berjumlah 2, dan FILTER mempertahankan setiap baris yang hasilnya bukan 0, jadi penjumlahan bekerja sebagai OR. Kamu bisa menggabungkan keduanya: ((B2:B7="North")+(B2:B7="East"))*(C2:C7>100) berarti (North atau East) dan di atas 100. Di sini hasilnya Ann, Cara, dan Dan.

FILTER menghasilkan #CALC! saat tidak ada yang cocok

Kalau tidak ada baris yang lolos, FILTER tidak punya apa pun untuk dikembalikan. Tanpa argumen ketiga, hasilnya error #CALC!; dengan argumen itu, kamu mendapat teksmu sendiri:

RumusReset

E2fx

=FILTER(A2:A7,B2:B7="West")

	A	B	C	D	E	F
1	Name	Region	Sales		No if_empty	With if_empty
2	Ann	North	120		#CALC!	No match
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

#CALC! Perhitungan tidak punya hasil, misalnya FILTER yang tidak menemukan kecocokan.

E2 menampilkan #CALC! dan F2 menampilkan No match. Ubah B3 dari South menjadi West dan kedua rumus menghasilkan Ben. Untuk tidak menampilkan apa pun, pakai string kosong: =FILTER(A2:A7,B2:B7="West","").

Lembar kerja ini juga menunjukkan cara menyaring satu kolom saja: array-nya A2:A7, jadi hanya nama yang dikembalikan. Untuk mendapatkan sebagian kolom dari sebuah tabel, bungkus hasilnya dengan CHOOSECOLS: =CHOOSECOLS(FILTER(A2:C7,B2:B7="North"),1,3) menghasilkan nama dan penjualan tanpa wilayah. CHOOSECOLS butuh Microsoft 365 atau Excel 2024.

Mengurutkan hasil FILTER

FILTER mengembalikan baris sesuai urutan kemunculannya di tabel. Bungkus dengan SORT untuk mengurutkan hasilnya: di sini baris North diurutkan berdasarkan penjualan, dari yang terbesar. Angka 3 adalah kolom hasil yang dijadikan dasar pengurutan, dan -1 berarti menurun.

RumusReset

E2fx

=SORT(FILTER(A2:C7,B2:B7="North"),3,-1)

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Cara	North	200
3	Ben	South	80		Ann	North	120
4	Cara	North	200		Finn	North	60
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

Cara (200) muncul pertama, lalu Ann (120) dan Finn (60). Untuk hanya mengembalikan baris teratas, bungkus sekali lagi dengan TAKE: =TAKE(SORT(FILTER(A2:C7,B2:B7="North"),3,-1),2) mempertahankan dua baris pertama (TAKE butuh Microsoft 365 atau Excel 2024). SORT dan SORTBY membahas pilihan pengurutan lainnya.

FILTER baris yang mengandung teks

FILTER tidak punya wildcard, jadi B2:B7="*th*" mencari teks *th* apa adanya. Untuk mempertahankan baris yang namanya mengandung suatu teks, uji setiap sel dengan SEARCH, yang menghasilkan posisi kalau teksnya ditemukan dan error kalau tidak, lalu bungkus dengan ISNUMBER:

RumusReset

E2fx

=FILTER(A2:C7,ISNUMBER(SEARCH("an",A2:A7)))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Dan	East	150
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

Hasilnya Ann dan Dan: SEARCH mengabaikan huruf besar kecil, jadi "an" juga cocok dengan An di Ann. Pakai FIND sebagai ganti SEARCH untuk pencocokan yang membedakan huruf besar kecil.

Latihan: FILTER dengan dua kondisi

RumusReset

E2fx

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

Giliranmu: Di E2, hasilkan baris (ketiga kolomnya) dari sales South dengan penjualan di atas 85.

PeriksaTampilkan jawaban

Latihan: FILTER berdasarkan sel, dengan nilai cadangan

RumusReset

F3fx

	A	B	C	D	E	F
1	Name	Region	Sales		Region	North
2	Ann	North	120
3	Ben	South	80		Names
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

Giliranmu: Di F3, tampilkan daftar nama (kolom A saja) dari sales di wilayah yang diketik di F1. Kalau tidak ada, tampilkan None.

PeriksaTampilkan jawaban

Kesalahan umum saat memakai FILTER

• Rentang dengan tinggi yang berbeda. =FILTER(A2:C7,B2:B6="North") mengecek 5 baris untuk tabel 6 baris, dan Excel menghasilkan #VALUE!. Pastikan include dimulai dan diakhiri di baris yang sama dengan array.
• Muncul angka nol di tempat yang sumbernya kosong. FILTER menghasilkan 0 untuk sel kosong di array. Ganti sel kosong dengan teks kosong sebelum menyaring: =FILTER(IF(A2:C7="","",A2:C7),B2:B7="North").
• Kolom penuh. =FILTER(A:C,B:B="North") berfungsi, tetapi kalau rumusnya sendiri berada di kolom A sampai C, rumus itu akan merujuk ke dirinya sendiri. Taruh hasilnya di samping tabel, atau pakai rentang tetap seperti A2:C1000.
• Tanda kutip di sekitar angka. C2:C7>"100" membandingkan angka dengan teks dan tidak mempertahankan apa pun. Tulis C2:C7>100.
• Mengira sama dengan tombol Filter. FILTER menyalin baris yang cocok ke tempat baru dan tidak mengubah tabelnya. Untuk menyembunyikan baris di tabel itu sendiri, pakai Data > Filter.