Anda disini: M Jurnal » Analisis Data Excel » Advanced Filter Excel: 7+ Contoh Kriteria AND, OR, Rumus, dll.)

Advanced Filter Excel: 7+ Contoh Kriteria AND, OR, Rumus, dll.)

Advanced Filter pada Excel adalah fitur filter tingkat lanjut yang berfungsi untuk menyaring data berdasarkan kriteria dari sebuah range / cell tertentu.

Advanced Filter memungkinkan Anda untuk menggunakan kriteria yang kompleks dan sangat bisa disesuaikan.

Ada 2 keuntungan menggunakan Advanced Filter ini.

Pertama, Hasil filter bisa langsung disalin. Jadi Anda tidak perlu menggunakan fitur Copy Visible Cells Only untuk menyalin hasil filter ini.

Kedua, Anda bisa memfilter 2 atau lebih kolom secara bersamaan. Kriteria setiap Filter terdapat pada sebuah cell / range.

Cara kerja Advanced Filter Excel mirip dengan Multi Filter pada Pivot Table (Walaupun lebih powerfull Pivot Table).

Cara membuat Pivot Table Multi - Level (Multi Rows, Columns, Filters, dan Values)

Meskipun tidak se-powerfull Pivot Table Excel, Anda tetap bisa memaksimalkan penggunakan Advanced Filter sehingga bisa membuat pemfilteran dengan tujuan yang lebih kompleks.

Ads

Saran Saya, ketahui dahulu Syarat Penggunaan Fitur Advanced Filter Excel ini:

Syarat Menggunakan Fitur Advanced Filter Excel

Dalam menggunakan fitur Advanced Filter, Anda membutuhkan 2 bagian utama yaitu Tabel Data Sumber dan Range Kriteria.

Tabel Data Sumber adalah tabel yang akan menerapkan filter. Sedangkan Range Kriteria adalah kriteria-kriteria pemfilteran yang akan diterapkan ke tabel data sumber.

Sama seperti menggunakan rumus / fungsi. Advanced Filter Excel memberlakukan syarat untuk ke dua bagian ini seperti berikut:

  1. Syarat Untuk Tabel Data Sumber:
    • Tabel Data Sumber harus memiliki maksimal 1 baris Header (judul kolom) dengan nama yang unik (tidak boleh sama dengan nama kolom lainnya).
    • Judul Kolom dan semua isi tabel masing-masing harus berupa 1 cell tunggal. Jadi tidak boleh menggunakan fitur Merge and Center.
    • Tidak boleh ada baris kosong dalam tabel.
  2. Syarat Untuk Range Kriteria
    • Judul Kriteria harus sama dengan judul kolom pada tabel (tidak peka huruf besar / kecil).
    • Masukkan setiap kriteria pada baris berikutnya (dibawah judul) yang sesuai dengan judul kolom dalam range kriteria.
    • Kriteria yang berada dalam 1 baris akan memberlakukan AND Kriteria.
    • Sedangkan jika kriteria berada dalam baris berbeda, maka Excel memberlakukan OR Kriteria (Selengkapnya akan Saya bahas pada bagian berikutnya).

Penting! Untuk menghindari kesalahan, ada baiknya pertimbangkan untuk menggunakan fitur Tabel Bernama Excel. Meskipun tanpa Tabel Bernama pun juga bisa.

Cara Menggunakan Advanced Filter Pada Excel

Sebagai contoh, Saya menggunakan Tabel Pembelian Produk dari beberapa suplier berikut:

Contoh Cara menggunakan fitur Advanced Filter Excel

Waktu yang dibutuhkan: 1 menit.

Kemudian, Saya hanya ingin menampilkan data Suplier Sumber Rezeki dengan Total Lebih dari 1.500.000. Untuk tujuan tersebut, silahkan ikuti cara menggunakan fitur Advanced Filter Excel berikut:

  1. Persiapkan Tabel Data Sumber yang akan difilter.

    Dalam contoh ini, Saya memindahkan tabel dan menyisakan beberapa baris sebelum tabel sebagai tempat meletakkan Range Kriteria (Tahap 2).
    Sesuaikan Tabel Data Sumber yang akan difilter

  2. Buat Range Kriteria yang menjadi acuan filter.

    Ingat! Judul masing-masing kolom kriteria harus sama dengan judul pada kolom tabel yang akan difilter. Dalam contoh ini, Saya hanya ingin memfilter kolom Suplier. Jadi buat Range kriteria dengan judul yang sama dengan tabel seperti gambar berikut:
    Buat Range Kriteria Advanced Filter
    Jangan lupa masukkan kriteria yang sudah ditentukan tepat dibawah judul range Kriteria.

  3. Tentukan Jenis (Action) Advanced Filter.

    Buka Fitur Advanced Filter dengan cara klik Tab Data, kemudian klik Advanced. Pada Window Advanced Filter (bagian Action), pilih Filter the list, in-place.Tentukan jenis output Advanced Filter

  4. Masukkan List Range.

    List Range ini merupakan Range Tabel Sumber yang akan difilter..
    Masukkan List Range
    Silahkan klik kolom list range, kemudian pilih (blok) Tabel yang akan difilter. Semuanya yaa.. termasuk header.

  5. Masukkan Criteria Range.

    Pada tahap 2, kita sudah membuat Range Kriteria. Nah, pada window Advanced Filter, klik kolom Criteria Range. Kemudian pilih (blok) Range Kriteria yang sudah Anda buat.
    Masukkan Criteria Range Advanced Filter Excel
    Note: Cukup pilih Judul Range Kriteria serta filter yang ingin disertakan. Halam contoh ini Cell A2 sampai B3.

  6. Terapkan Filter.

    Klik Ok untuk menerapkan filter. Dan lihat hasilnya:
    Hasil Advanced Filter Excel

Note: Untuk sementara kita mengabaikan beberapa parameter pada Window Advanced Filter seperti Copy to another location, Copy to, dan Unique records only. Ini akan Saya jelaskan pada contoh berikutnya.

Oh ya, dari awal panduan ini sudah Saya jelaskan bahwa hasil Advanced Filter bisa langsung dicopy tanpa menggunakan fitur Copy Visible Cells Only.

Ads

Sekarang coba buktikan…

Copy Paste hasil filter tersebut ke bawah tabel dan lihat hasilnya:

Copy Paste Advanced FIlter Excel

Benar bukan ?

Penting! Ada 15 jenis Copy Paste Excel. Silahkan tambah wawasan Anda pada Panduan Copy Paste Special Excel.

Pahami Parameter Advanced Filter

Pada Window Advanced Filter Excel, ada 5 parameter yang perlu Anda sesuaikan.

Pertama Action, merupakan jenis output Advanced Filter yang akan Anda gunakan.

Ada 2 pilihan yaitu Filter the list, in-place, ini akan memfilter sumber data asli dan menyembunyikan baris yang tidak sesuai dengan kriteria. (ini sudah kita bahas pada contoh pertama).

Sedangkan Output Copy the results to another location akan menyalin hasil filter ke cell lain tanpa mengubah data sumber.

Kedua List Range, ini sudah jelas merupakan range tabel data sumber.

Ketiga Criteria Range, ini juga sudah jelas range dari kriteria yang digunakan.

Penting! Untuk List dan Criteria Range, Anda bisa menggunakan Reference Cell (seperti contoh diatas) atau Named Range ataupun Table Name dari Format Tabel Bernama.

Keempat Copy to, merupakan lokasi tujuan untuk hasil filter. Parameter ini hanya akan aktif jika menggunakan output Copy the results to another location.

Hapus Data Duplikat Excel

Kelima Unique Records Only, berguna untuk memfilter data unik saja. Artinya tidak akan ada hasil data duplikat dari filter ini.

Parameter ini sangat berguna untuk menghapus data duplikat tanpa mengubah data sumber seperti gambar berikut:

Advanced FIlter Unique Value Excel

Selengkapnya bisa Anda pelajari pada Panduan Menghapus Data Duplikat.

7 Contoh Memfilter Data Menggunakan Advanced Filter Excel

Dalam contoh sebelumnya, Saya memasukkan kriteria secara langsung (berupa teks) ke dalam Range Kriteria. Kemudian Excel memfilter kolom Suplier yang berisi kriteria tersebut.

Sedangkan Advanced Filter memungkinkan Anda memfilter tabel dengan kriteria yang jauh lebih kompleks.

  • Memfilter Angka / Tanggal / Teks dengan kriteria menggunakan operator perbandingan (seperti =, >, < dll).
  • Memfilter Teks menggunakan Karakter Wildcard.
  • Menggunakan banyak kriteria dengan AND maupun OR Kriteria.
  • Menggunakan Rumus / Fungsi sebagai kriteria Filter.
  • Mengombinasikan Advanced Filter dengan Dropdown List.

#1 Advanced Filter untuk Data Berupa Angka atau Tanggal Valid

Untuk memfilter data berupa angka / tanggal valid, Anda bisa menggunakan operator perbandingan seperti =, >, <, >=, <=, dan <>.

Seperti contoh pertama dalam panduan ini menggunakan operator perbandingan Lebih Besar Dari “>1000000″ sebagai kriteria.

Penting! Sebelum melanjutkan, pastikan Anda sudah memahami penggunaan dasar operator perbandingan. Selengkapnya sudah Saya bahas pada SUB-BAB Operator Perbandingan Rumus.

Anda juga bisa menggunakan operator perbandingan ini untuk data berupa tanggal valid. Misalnya, Saya ingin menampilkan data pembelian sampai tanggal 22 Februari 2021 saja. (Dalam bahasa lain, menyembunyikan data setelah tanggal 22 Februari 2021).

Silahkan buat kolom baru untuk range kriteria tanggal dan gunakan kriteria ini: <=22/02/2021

Kemudian, buka Window Advanced Filter. Cara cepat: Tekan tombol ALT A Q secara berurutan. Jangan lupa sesuaikan Criteria Range pada Window Advanced Filter dan tekan Enter. Lihat hasilnya:

Advanced Filter untuk data Angka dan Tanggal menggunakan kriteria operator perbandingan

Benar bukan ? Excel hanya menampilkan data sampai tanggal 22/02/2021 dan menyembunyikan data lain yang tidak sesuai dengan kriteria.

Penting! Pastikan data tanggal menggunakan Format Tanggal & Waktu yang Valid. Jika ragu, silahkan pelajari cara memasukkan tanggal yang benar pada Panduan Format Date & Time Excel.

Contoh lainnya, jika Saya menggunakan kriteria ini: <>22/02/2021

Advanced Filter untuk data Angka dan Tanggal menggunakan kriteria operator perbandingan

Excel akan menyembunyikan data pada tanggal 22/02/2021. Karena operator perbandingan <> berarti tidak sama dengan.

Silahkan latihan menggunakan operator perbandingan lainnya…

#2 Advanced Filter Untuk Data Berupa Teks

Pada dasarnya, Anda juga bisa menggunakan Operator Perbandingan untuk memfilter teks. Hanya saja arti Operator Perbandingan pada data teks tidak sama dengan data Angka / Tanggal Valid.

Berikut arti dari operator perbandingan untuk memfilter teks.

KriteriaArtinya
Toko BungaFilter Kolom yang diawali dengan Toko Bunga
=”=Toko Bunga”Filter Kolom yang hanya berisi Toko Bunga
>Toko BungaFilter Kolom berdasarkan urutan data (sortir) setelah Toko Bunga
<Toko BungaFilter Kolom berdasarkan urutan data (sortir) sebelum Toko Bunga
<>Toko BungaFilter Kolom yang tidak berisi Toko Bunga
>=Toko BungaFilter Kolom berdasarkan urutan data (sortir) setelah dan termasuk Toko Bunga
<=Toko BungaFilter Kolom berdasarkan urutan data (sortir) sebelum dan termasuk Toko Bunga
Tabel: Kriteria Teks Pada Advanced Filter

Untuk kriteria yang pertama (Toko Bunga) Saya rasa sudah sangat jelas. Hasil filter ini haya akan menampilkan baris yang berisi teks yang diawali dengan Toko Bunga. Jadi Toko Bunga Si A, Toko Bunga Si B dan seterusnya juga termasuk dalam kriteria.

Begitu juga untuk kriteria yang ke 2 dan ke 5. Saya rasa artinya sudah cukup jelas.

Sementara khusus untuk operator perbandingan >, <, >=, dan <=, Lebih baik gunakan hanya jika list data pada kolom yang menggunakan filter ini diurutkan dari abjad A-Z atau Z-A.

Misalnya seperti ini:

Contoh kriteria filter jika data berupa teks

Lihat, ada 2 baris data untuk Suplier Toko Bunga. Jika Saya menggunakan kriteria >Toko Bunga, maka Excel hanya akan menampilkan semua baris data setelah data Toko Bunga seperti berikut:

Contoh filter data teks menggunakan operator perbandingan

Note: Excel juga ikut menyembunyikan baris data Suplier Sumber Rezeki.

Sebaliknya, jika Saya menggunakan kriteria <=Toko Bunga, maka Excel hanya akan menampilkan data Toko Bunga itu sendiri serta data sebelumnya seperti gambar berikut:

Kriteria menggunakan karakter wildcard (* atau ?)

Sejauh ini Saya harap Anda sudah paham penggunaan operator perbandingan dalam memfilter data.

Oh ya, ada 1 yang spesial untuk memfilter teks, yaitu…

“Kriteria Teks Bisa menggunakan Karakter Wildcard”

Karakter Wildcard seperti Asterisk (simbol *), Quetion Mark (simbol ?), dan Tilde (simbol ~) dapat mengganti / mewakilkan karakter dalam sebuah teks.

Note: Tilde (~) hanya berguna jika didalam teks yang akan difilter terdapat simbol Asterisk (*) atau Quetion Mark (?).

Dalam praktiknya, Wildcard Asterik (*) dan Quetion Mark (?) lebih sering digunakan. Selain itu, Anda bisa menempatkan simbol ini pada bagian manapun (sebelum, diantara, atau setelah karakter).

Misalnya, Saya menggunakan contoh berbeda seperti berikut:

Advanced Filter Excel dengan kriteria menggunakan karakter wildcard (* dan ?)

Kemudian Saya memfilter Suplier dan Produk menggunakan kriteria berikut:

  • Suplier: * Kelontong
  • Produk: ???? A

Sekarang lihat hasilnya:

Advanced Filter Excel menggunakan Wildcard (* dan ?) sebagai kriteria

Pada kriteria * Kelontong, simbol Asteriks (*) mewakilkan Teks Toko. Lihat, simbol asteriks hanya ada 1 yang mewakilkan 4 huruf (Toko).

Sementara, untuk simbol Quetion Mark pada kriteria ???? A ada 4 yang mana mewakilkan 4 huruf yaitu Lauk dan Buah.

Note: Jumlah tanda tanya harus mewakilkan banyak karakter yang akan digantikan.

Dalam contoh ini, Saya tidak akan mendapatkan apa-apa jika menggunakan kriteria ini: ? A. Silahkan buktikan sendiri.

#3 Advanced Filter Dengan AND Kriteria

Seperti yang Saya sebutkan pada bagian Syarat Fitur Advanced Filter, kriteria yang berada dalam 1 baris akan memberlakukan AND Kriteria (semua harus terpenuhi).

AND KRITERIA ARTINYA HASIL FILTER HARUS MEMENUHI SEMUA KRITERIA

rolan mardani

Misalnya jika Saya menggunakan kedua kriteria ini:

  • Harga: >20000 dan
  • Jumlah: <100

Note: Sesuaikan Parameter Criteria Range pada Window Advanced Filter Excel.

Lihat hasilnya:

Advanced Filter Excel dengan AND Kriteria (Banyak kriteria)

Benar bukan ? Excel memfilter Kolom Harga yang lebih dari 20.000 dan jumlah kurang dari 100.

#4 Advanced Filter Dengan OR Kriteria

OR KRITERIA ARTINYA HASIL FILTER HARUS MEMENUHI SALAH SATU KRITERIA

rolan mardani

Misalnya Saya menggunakan kriteria berikut:

  • Harga: >20000 atau
  • Jumlah: <100

Artinya, Saya ingin memfilter kolom harga yang lebih besar dari 20000 atau kolom jumlah yang lebih kecil dari 100.

Note: Dalam Advanced Filter, Anda harus menempatkan OR Kriteria dalam baris yang berbeda. Karena ada penambahan baris baru dalam range kriteria, maka Anda harus menyesuaikan parameter Criteria Range dalam Window Advanced Filter seperti berikut:

Sesuaikan kriteria range advanced Filter Excel

Gunakan cara cepat: Tekan ALT A Q > Ubah Criteria Range > Tekan Enter. Dan lihat hasilnya:

Advanced Filter Excel dengan OR Kriteria (Banyak Kriteria)

Note: Perhatikan penempatan Kriteria dalam Range Kriteria.

#5 Advanced Filter Dengan AND + OR Kriteria

Intinya, gabungkan AND + OR kriteria. Misalnya tujuan Saya seperti ini:

  • Filter Suplier: Sumber Rezeki Dan Jumlah <100. Atau
  • Filter Suplier: Sumber Rezeki Dan Harga <20000
Advanced Filter Excel dengan AND + OR Kriteria (Banyak Kriteria)

Lihat hasil filter pada baris pertama, hanya memenuhi kriteria pertama (suplier: Sumber Rezeki dan Jumlah: <100). Meskipun tidak memenuhi kriteria ke dua (Harga: <20000).

Begitu juga dengan hasil filter baris kedua. Meskipun tidak memenuhi kriteria pertama, Excel tetap menampilkannya.

Ini karena kriteria pertama dan kedua tidak berhubungan satu sama lain (OR Kriteria).

#6 Advanced Filter Dengan Kriteria Berupa Rumus / Fungsi

Ini salah satu Favorit Saya saat ini.

Anda juga bisa menggunakan rumus / fungsi Excel sebagai ganti range kriteria yang sudah ada.

Misalnya, Saya ingin memfilter dengan 2 kondisi berikut:

  • Suplier: Sumber Rezeki dan Jumlah: >100 dan Total: <=AVERAGE atau
  • Suplier: Toko Laris dan Jumlah: >100 dan Total: <=AVERAGE

Lihat, setiap kriteria filter harus memiliki nilai Total yang lebih kecil atau sama dengan nilai rata-rata keseluruhan.

Penting! Sebagai pembanding, Saya menghitung nilai rata-rata kolom Total menggunakan Fungsi AVERAGE pada Cell G6. Ingat, ini hanya sebagai pembanding hasil filter.

Untuk tujuan tersebut, silahkan buat range kriteria baru (tanpa header / judul) dan gunakan Rumus ini: =E7<=AVERAGE(E7:E21) pada Cell F3 dan F4.

Note: Jangan beri judul baru filter yang menggunakan Rumus ini. Biarkan saja kosong atau gunakan judul lain yang tidak sama dengan judul dari tabel. Selain itu, pastikan rumus memberikan hasil TRUE atau FALSE.

Advanced Filter Excel menggunakan Rumus / Fungsi

Update Advanced Filter dengan cara tekan ALT A Q, kemudian update Criteria Range pada Window Advanced Filter. Dalam contoh ini menjadi $A$2:$F$4. Pastikan semua kriteria masuk ke dalam Criteria Range ini. Kemudian tekan Enter dan lihat hasilnya:

Advanced Filter Excel menggunakan Rumus / Fungsi

Benar bukan ? Excel memberikan 2 hasil filter. Untuk kolom Suplier dan Jumlah (masing-masing) sudah memenuhi kriteria.

Begitu juga, kolom Total memenuhi kriteria <=AVERAGE.

Penting! Selalu gunakan Reference Absolute dalam Rumus. Silahkan pelajari jenis-jenis reference cell pada SUB-BAB Cell Reference Excel.

Untuk contoh ke 2, coba ubah Rumus =E7<=AVERAGE(E7:E21) menjadi =E7>=AVERAGE(E7:E21)pada Cell F3 dan F4. Kemudian tekan ALT A Q dan Enter. Lihat hasilnya:

Advanced Filter Excel menggunakan Rumus / Fungsi

Hanya ada 1 hasil.

Sekarang silahkan buktikan dan latihan sendiri. Gunakan kombinasi Kriteria yang berbeda, misal Suplier yang berbeda atau Jumlah yang berbeda.

Saya yakin dengan banyak latihan, Anda akan mendapatkan pemahaman yang mendalam.

#7 Kombinasi Advanced Filter Dengan Dropdown List

Sebelum Anda menggabungkan Advanced Filter dengan Dropdown List dari Conditional Formatting, ada baiknya Anda memahami cara membuat Dropdown List terlebih dahulu.

Dalam Panduan ini, Saya membuat Dropdown List untuk Criteria Range Suplier seperti ini:

Kombinasi Advanced Filter Excel dengan Dropdown List

Note: Saya juga menggunakan kriteria =E7<=AVERAGE(E7:E21) pada Cell F3 dan F4.

Kemudian, silahkan pilih dropdown list pada Range Kriteria Suplier. Misalnya Saya pilih Toko Laris dan Toko Bunga.

Terakhir, update Advanced Filter dengan cara ALT A Q. Sesuaikan Criteria Range (jika belum) dan tekan Enter. Lihat hasilnya:

Kombinasi Advanced Filter Excel dengan Dropdown List

Oh ya, selain mengombinasikan Dropdown List dengan Advanced Filter, Anda juga bisa menggunakan Dependent Dropdown List. Silahkan berkreasi…

Pintasan Panduan Sort & Filter Excel

  1. Sort: Cara mengurutkan data di Excel (1 kolom, 2 atau lebih kolom, dan custom)
  2. Sort by Color: Cara mengurutkan data berdasarkan warna Cell, Font, dan Icon.
  3. Reverse & Randomize List: Cara mengacak nama menggunakan rumus RAND, membalikkan urutan data menggunakan Sort serta (BONUS) contoh mengambil nama dari list data secara acak (gabungan fungsi INDEX, RANDBETWEEN dan COUNTA).
  4. Filter & AutoFilter: Cara Memfilter Data (Angka, Teks, Tanggal, Warna) secara manual dan otomatis menggunakan fitur AutoFilter
  5. Quick Filter: Cara Memfilter Cepat Menggunakan Pintasan Keyboard (tanpa menyentuh Mouse / Touchpad) seperti menggunakan CTRL + SHIFT + L, ALT + Panah bawah – E dll.
  6. Advanced Filter: (Anda Disini).
  7. Data Form: Cara Membuat Form Input Data (seperti data stok barang, absensi karyawan, data karyawan, data penjualan dll.)
  8. Remove Duplicates: 3 Cara Terbaik Menghapus Data Duplikat
  9. Outlining Data: Mengelompokkan Data (Grouping) untuk Membuat Subtotal Khusus
  10. Subtotal: Menampilkan Subtotal berdasarkan Filter Data.

Apakah Panduan ini Membantu Menyelesaikan Permasalahan Anda ?

Rolan Mardani

Traktir Saya secangkir kopi supaya kuat begadang untuk membuat konten-konten Panduan yang berkualitas dan membantu menyelesaikan permasalahan Anda.

Bantu Orang Lain... Share Panduan ini:

Komentar Paling Lambat di Balas Pukul 23:59 :)