excel.my.id
Array DinamisExcelGoogle Sheets

FILTER

Diperbarui

FILTER mengambil baris (atau kolom) dari sebuah rentang yang memenuhi syarat Anda. Hasilnya tumpah ke sel di bawahnya dan ikut berubah saat data sumber berubah. Pakai untuk daftar siswa yang lulus, transaksi di atas batas tertentu, atau produk dengan stok menipis.

Formula dasar

=FILTER(array, include, [if_empty])
ArgumenArtiWajib?
arrayRentang yang diambil barisnya (boleh banyak kolom)Ya
includeDeretan TRUE/FALSE sepanjang jumlah baris array. Baris yang TRUE diambilYa
if_emptyNilai bila tidak ada baris yang memenuhi syaratTidak

Contoh: siswa yang lulus

Kolom 1ABC
1NamaNilaiLulus (>60)
2Budi80TRUE
3Ani50FALSE
4Eka90TRUE

Kolom C berisi =B2>60.

RumusHasilKeterangan
=FILTER(A2:A4, C2:C4)Budi, EkaMemakai kolom bantu TRUE/FALSE
=FILTER(A2:A4, B2:B4>60)Budi, EkaSyarat langsung, tanpa kolom bantu
=FILTER(A2:B4, B2:B4>60)Budi 80; Eka 90Beberapa kolom ikut diambil
=FILTER(A2:A4, (B2:B4<60)+(B2:B4>85))Ani, EkaSyarat ATAU: nilai di bawah 60 atau di atas 85
=FILTER(A2:A4, B2:B4>95, "Tidak ada")Tidak adaTidak ada yang memenuhi syarat
=SORT(FILTER(A2:B4, B2:B4>60), 2, -1)Eka 90; Budi 80Disaring lalu diurutkan nilai terbesar dulu (Excel)

Simulasi: Fungsi FILTER

Ambil hanya baris yang memenuhi syarat. Pelajari FILTER langkah demi langkah.

C2 =B2>60
Simulasi FILTER. Sel biru bisa diubah, sel tebal berisi rumus.
ABC
1NamaNilaiLulus (>60)
2Budi80TRUE
3Ani50FALSE
4Eka90TRUE
6Yang LulusBudi, Eka=FILTER(range, syarat_truefalse)

Kolom biru = input yang bisa diedit. Ubah nilai atau kolom bantuan (TRUE/FALSE).

Langkah demi langkah
  1. 1. Apa itu FILTER?

    FILTER mengambil baris berdasar syarat: =FILTER(range, include_array). Di Excel 365 hasilnya mengalir (spill) ke bawah.

    Lihat juga: SORT, UNIQUE, IF

  2. 2. Syarat TRUE/FALSE

    B6 = FILTER(A2:A4, C2:C4). C2:C4 berisi TRUE/FALSE (hasil B>60). Hanya nama dengan TRUE yang diambil: Budi, Eka.

  3. 3. Bikin Syarat

    Kolom C adalah bantuan: =B2>60. FILTER butuh array TRUE/FALSE, bukan perbandingan langsung di dalamnya (di engine ini).

    Lihat juga: IF, AND

  4. 4. Filter Banyak Kolom

    FILTER(A2:B4, (B2:B4>60)*(C2:C4="A")) untuk syarat AND. Kali (*) = AND, plus (+) = OR antar array.

    Lihat juga: AND, OR

  5. Pelajaran Kunci

    FILTER/UNIQUE/SORT adalah fungsi array dinamis. Gabungkan: =SORT(FILTER(A2:A4, C2:C4)) untuk hasil urut & tersaring.

    Lihat juga: SORT, UNIQUE

Kesalahan umum

GejalaPenyebabPerbaikan
#SPILL!Ada sel berisi data di jalur tumpahanKosongkan sel yang menghalangi
#CALC! (Excel) atau #N/A (Sheets)Tidak ada baris yang memenuhi syaratIsi if_empty di Excel, atau bungkus dengan IFNA di Sheets
#VALUE!include tidak sama panjang dengan jumlah baris arraySamakan jumlah baris
#NAME?Excel 2019 atau lebih lamaPakai Advanced Filter, atau PivotTable
Teks hilang pada hasilSel kosong ikut sebagai 0Tambahkan syarat (A2:A4<>"")
Rumus ditolakPemisah regional Indonesia umumnya titik koma=FILTER(A2:A4;B2:B4>95;"Tidak ada")

Excel vs Google Sheets, dan alternatifnya

  • Excel 2021 dan Microsoft 365: FILTER(array, include, [if_empty]). Google Sheets: FILTER(range, condition1, [condition2], ...) dengan beberapa syarat sebagai argumen terpisah, tanpa if_empty.
  • Di Excel lama, gunakan Data > Filter atau Advanced Filter, yang menyaring di tempat tanpa rumus.
  • Hasil FILTER bisa dibungkus fungsi array lain: SORT, UNIQUE, TEXTJOIN.
  • Untuk satu nilai dengan syarat, VLOOKUP atau XLOOKUP lebih sederhana. Jumlah dengan syarat: SUMIFS.

Sumber: FILTER di Microsoft Support dan FILTER di Google Docs Editors Help.

Tips

  • Jangan menaruh apa pun di bawah atau di samping rumus FILTER, karena tumpahannya membutuhkan ruang itu.
  • Rujuk seluruh hasil dengan operator tumpahan: =ROWS(E2#) di Excel menghitung baris hasil.
  • Pakai syarat langsung (B2:B4>60) agar tidak perlu kolom bantu.

Lihat juga

SORT, UNIQUE, XLOOKUP, SUMIFS, TEXTJOIN, IFNA.

Pertanyaan yang sering muncul

Versi Excel mana yang punya FILTER?

Excel 2021 dan Microsoft 365. Excel 2019 ke bawah tidak punya FILTER dan menampilkan #NAME?. Google Sheets mendukung FILTER, tetapi tidak punya argumen if_empty.

Kenapa FILTER menghasilkan

Hasil FILTER tumpah ke sel di bawah atau di samping, dan ada sel yang tidak kosong menghalanginya. Kosongkan sel itu, atau pindahkan rumus ke tempat yang lapang.

Bagaimana memberi syarat lebih dari satu pada FILTER?

Untuk DAN, kalikan syarat: =FILTER(A2:B10, (B2:B10>60)*(C2:C10="A")). Untuk ATAU, jumlahkan: =FILTER(A2:A4, (B2:B4<60)+(B2:B4>85)).

Apa yang terjadi bila tidak ada baris yang memenuhi syarat?

Excel menghasilkan #CALC! kecuali Anda mengisi argumen ketiga: =FILTER(A2:A4, B2:B4>95, "Tidak ada"). Google Sheets menghasilkan #N/A.

Cari di excel.my.id

Coba cari