Filter Lanjutan di Excel

Apa itu Filter Tingkat Lanjut di Excel?

Filter lanjutan berbeda dari filter otomatis di Excel. Fitur ini tidak seperti tombol yang dapat digunakan dengan satu klik mouse. Untuk menggunakan filter lanjutan, kita harus menentukan kriteria untuk filter otomatis, lalu klik tab “Data”. Kemudian, di bagian lanjutan untuk filter lanjutan, kami akan mengisi kriteria data kami.

Misalnya, Anda memiliki kumpulan data. Dalam dataset ini, ada catatan duplikat. Itu bisa karena kesalahan dalam entri data atau kompilasi data. Kita dapat menggunakan Alat Penyaring Tingkat Lanjut Excel untuk menerima catatan unik di lokasi lain, mempertahankan data asli.

Bagaimana Cara Menggunakan Filter Tingkat Lanjut di Excel? (Dengan Contoh)

Mari kita pelajari penggunaan ini dengan beberapa contoh.

Contoh 1

Misalkan kita memiliki data berikut untuk difilter berdasarkan kriteria yang berbeda:

Kami perlu memeriksa transaksi penjualan yang dilakukan oleh ‘Taran’ dan ‘Suresh.’ Kemudian, kita dapat menggunakan operator OR, menampilkan record yang memenuhi kondisi apa pun. Kemudian, kita dapat mengikuti langkah-langkah untuk menerapkan filter ini di Excel untuk mendapatkan hasilnya.

Di bawah ini adalah langkah-langkah untuk menerapkan filter lanjutan di Excel: –

Untuk menggunakan filter lanjutan, pertama-tama, kita perlu memilih salah satu sel dalam rentang data.

Klik pada “tab Data” – grup “Sort & Filter” – perintah “Advanced”.

Saat kita mengklik “Lanjutan”, kotak dialog “Filter Lanjutan” akan terbuka untuk meminta “Rentang Daftar” untuk memfilter, “Rentang Kriteria” untuk menentukan kriteria, dan “Rentang Ekstrak” untuk menyalin data yang difilter (jika diinginkan).

Untuk “Rentang Kriteria”, kita perlu menyalin judul kolom di baris atas dan menentukan kriteria di bawah judul kolom. Untuk menentukan kriteria, kita dapat menggunakan operator pembanding, yaitu sebagai berikut:

Kami ingin mendapatkan semua catatan yang memiliki nama ‘Suresh’ atau ‘Taran.’ Rentang Kriteria akan seperti di bawah ini:

Untuk kondisi “ATAU” di mana kita ingin menampilkan rekaman yang memenuhi persyaratan apa pun, kita harus menentukan kriteria di baris yang berbeda.

Ada dua tindakan dalam filter lanjutan.

Saring daftar di tempat: Opsi ini memfilter daftar di posisi semula, yaitu, pada “Rentang Daftar”. Setelah menganalisis, kita dapat menghapus filter menggunakan perintah “Hapus” di grup “Sort & Filter” di bawah “Data .”

Salin ke lokasi lain : Opsi ini menyalin data yang diinginkan menurut kriteria ke rentang yang ditentukan.

Kami dapat menggunakan opsi apa pun sesuai dengan kebutuhan kami, tetapi kami akan lebih sering menggunakan opsi ke-2.

Sekarang, kita perlu:

Pertama, buka kotak dialog “Filter Lanjutan” .

Menentukan “Rentang Daftar” sebagai $A$5:$D$26, “Rentang Kriteria” sebagai $A$1:$D$3, dan Rentang ” Salin ke” sebagai $F$5:$I$26. Klik “OK.

Semua catatan dengan nama ‘Suresh’ atau ‘Taran’ disaring dan ditampilkan secara terpisah dalam rentang sel yang berbeda.

Contoh #2

Kami ingin semua transaksi penjualan Qtr 1 dan India Selatan. Oleh karena itu, “Kisaran Kriteria” adalah sebagai berikut:

Karena kami memiliki kondisi ‘DAN’ di sini, kami ingin menampilkan catatan di mana kedua kondisi terpenuhi. Oleh karena itu, kami telah menyebutkan kriteria di bawah kedua judul kolom pada baris yang sama.

Sekarang, kita akan mengklik perintah “Advanced” di grup “Sort & Filter” di bawah tab “Data” .

Dari kotak dialog ‘ Filter Lanjutan” , kita akan memilih “Salin ke lokasi lain” dan kemudian tentukan A5:D26 sebagai Rentang Daftar , A1:D2 sebagai “Rentang Kriteria”, dan F5:I26 sebagai rentang “Salin Ke” .

Sekarang hasilnya adalah sebagai berikut:

Contoh #3

Kami ingin mencari penjualan di Qtr 1 atau dibuat di India Utara.

Kita perlu menentukan kriteria dalam baris yang berbeda dan kolom yang berbeda. Kami harus menampilkan data jika ada kondisi yang terpenuhi, dan keduanya terkait dengan kolom yang berbeda.

Langkah:

Kita perlu membuka kotak dialog “Filter Lanjutan” .

Kami akan menentukan “Kisaran Daftar” sebagai $A$5:$D$26 .

Juga, tentukan “Rentang Kriteria” sebagai $A$1:$D$3 .

Dan, tentukan rentang “Salin Ke” sebagai $F$5:$I$26 .

Hasilnya adalah sebagai berikut:

Contoh #4

Sekarang, kami ingin menemukan semua penjualan ₹2.000 – ₹4.000 dan ₹10.000 – ₹13.000.

Karena kita memiliki empat syarat,

(Kondisi 1 DAN Kondisi 2) ATAU (Kondisi 3 DAN Kondisi 4).

(>=2000 DAN <=4000) ATAU (>=10000 DAN <=13000)

Kami telah menyebutkan kondisi dengan ” AND” di baris yang sama dan “ATAU” di baris yang berbeda.

Langkah:

Untuk membuka kotak dialog “Filter Lanjutan” , kami akan mengklik “Lanjutan” di grup “Urutkan & Filter” di bawah “Data.”

Di kotak dialog “Filter Lanjutan” , kami akan menentukan “Daftar Rentang” sebagai $A$5:$D$26

Dan, kami akan menentukan ‘Kisaran Kriteria” sebagai $A$1:$D$3

Juga, Rentang “Salin ke” sebagai $F$5:$I$26

Setelah mengklik “OK ” , hasilnya adalah:

Contoh #5

Kami ingin menemukan penjualan Qtr 1 oleh Sunny atau Qtr 3 oleh Mukesh.

Karena kita memiliki AND dan OR , keduanya berhubungan dalam kondisi, kita akan menentukan kondisi dalam rentang kriteria di baris yang berbeda (OR) dan kolom lainnya (AND).

Langkah:

Untuk membuka kotak dialog “Filter Lanjutan” , kami akan mengklik “Lanjutan” di grup “Urutkan & Filter” di bawah “Data . “

Di kotak dialog “Filter Lanjutan” , kami menetapkan “Kisaran Daftar” sebagai $A$5:$D$26 .

Dan kami akan menentukan “Rentang Kriteria” sebagai $A$1:$D$3.

Juga, Rentang ‘Salin ke’ sebagai $F$5:$I$26 .

Setelah mengklik “OK”, maka hasilnya adalah:

Contoh #6 – Menggunakan Karakter WILDCARD

Kami ingin menemukan semua transaksi penjualan dengan nama yang diakhiri dengan ‘esh’ atau kata pertama suatu wilayah yang diakhiri dengan ‘st’ dan hanya ingin mengambil nama, penjualan, dan wilayah.

Di sini, * menunjukkan lebih dari satu karakter, dan ‘?’ hanya mewakili satu karakter.

Sebelum menerapkan filter lanjutan, kita perlu menentukan label kolom pada “Copy to Range” karena kita hanya menginginkan beberapa kolom, tidak semua.

Sekarang, kita akan memanggil perintah.

Langkah:

Untuk membuka kotak dialog ‘Filter Lanjutan’ , kita akan mengklik ‘Lanjutan’ di grup ‘Urutkan & Filter’ di bawah ‘Data.’

Di kotak dialog “Filter Lanjutan” , kami menetapkan “Kisaran Daftar” sebagai $A$5:$D$26 .

Kami akan menentukan ‘Kisaran Kriteria’ sebagai $A$1:$D$3

Juga, Rentang ‘Salin ke’ sebagai $F$5:$H$26

Setelah mengklik ‘OK ‘ , maka hasilnya adalah:

Contoh #7

Kami ingin memfilter lima penjualan teratas (dalam jumlah besar).

Sel rumus harus mengevaluasi BENAR atau SALAH untuk mendapatkan lima rekaman paling signifikan. Oleh karena itu, kami menggunakan fungsi LARGE Excel dan membandingkan nilainya dengan jumlah penjualan .

Seperti yang bisa kita lihat, judul kolom untuk sel formula kosong. Oleh karena itu, kita dapat mengosongkannya atau memberinya nama yang tidak cocok dengan judul kolom dalam rentang data.

Kami akan menentukan rentang di kotak dialog “Filter Lanjutan” . Langkah-langkahnya adalah:

Untuk membuka kotak dialog “Filter Lanjutan” , kami akan mengklik “Lanjutan” di grup “Urutkan & Filter” di bawah “Data.”

Di kotak dialog “Filter Tingkat Lanjut Excel” , kami akan menentukan “Daftar Rentang” sebagai $A$5:$D$26 .

Dan “Rentang Kriteria” sebagai $A$1:$E$2

Juga, Rentang “Salin ke” sebagai $F$5:$I$26

Setelah klik “OK ” , maka hasilnya akan seperti ini:

Hal-hal untuk diingat

Rentang yang perlu diterapkan harus memiliki heading yang unik, karena header duplikat menyebabkan masalah saat menjalankan filter lanjutan.
Setidaknya harus ada satu baris kosong antara “Rentang Daftar” dan “Rentang Kriteria”.

Artikel yang Direkomendasikan

Artikel ini adalah panduan untuk Filter Tingkat Lanjut di Excel. Kami membahas penggunaan Filter Lanjutan di Excel, bersama dengan contoh Excel dan templat excel yang dapat diunduh. Anda juga dapat melihat alat Excel yang berguna ini: –