Apa itu Filter Tingkat Lanjut di Excel?
Filter lanjutan berbeda dengan filter otomatis di excel, fitur ini tidak seperti tombol yang dapat digunakan dengan sekali klik mouse, untuk menggunakan filter lanjutan terlebih dahulu kita harus menentukan kriteria untuk filter otomatis dan kemudian klik tab Data dan kemudian di bagian lanjutan untuk filter lanjutan di mana kami akan mengisi kriteria kami untuk data.
Bagaimana Cara Menggunakan Filter Tingkat Lanjut di Excel? (Dengan Contoh)
Mari kita pelajari penggunaan ini dengan beberapa contoh.
Contoh 1
Misalkan kita punya, data berikut untuk disaring berdasarkan kriteria yang berbeda.
Kami perlu memeriksa transaksi penjualan yang dilakukan oleh 'Taran' dan 'Suresh.' Kemudian, kita dapat menggunakan operator OR, menampilkan catatan yang memenuhi kondisi apa pun. Untuk mendapatkan hasilnya, kita bisa mengikuti langkah-langkah untuk menerapkan filter tersebut di Excel.
- Langkah 1: Untuk menerapkan filter lanjutan, pertama, kita perlu memilih salah satu sel dalam rentang data.
- Langkah 2: kemudian klik pada data Tab-> Urutkan & Filter grup -> Lanjutan perintah
- Langkah 3: Saat kita mengklik 'Advanced', kotak dialog 'Advanced Filter' akan terbuka untuk meminta List Range untuk disaring, Kriteria Range untuk menentukan kriteria, dan Extract Range untuk menyalin data yang difilter (jika diinginkan).
- Langkah 4: Untuk Kriteria, kita perlu menyalin judul kolom di baris atas dan menentukan kriteria di bawah judul bidang. Untuk menentukan kriterianya, kita dapat menggunakan operator pembanding, yaitu sebagai berikut:
- Langkah 5: Kami ingin mendapatkan semua rekaman dengan nama 'Suresh' atau 'Taran.' Rentang Kriteria akan seperti di bawah ini:
Untuk kondisi 'OR' di mana kita ingin menampilkan record yang memenuhi salah satu kondisi, maka kita perlu menentukan kriteria di baris yang berbeda.
Ada dua tindakan dalam filter lanjutan.
- Filter daftar di tempat : Opsi ini memfilter daftar di tempat asli, yaitu di rentang daftar itu sendiri. Setelah menganalisis, kami dapat menghapus filter menggunakan perintah 'Hapus' di grup 'Urutkan & Filter' di bawah 'Data.'
- Salin ke lokasi lain : Opsi ini menyalin data yang diinginkan sesuai dengan kriteria ke kisaran yang ditentukan.
Kita bisa menggunakan salah satu pilihan sesuai dengan kebutuhan kita, tetapi kita akan menggunakan 2 nd pilihan yang lebih sering.
Sekarang kita perlu
- Buka kotak dialog 'Filter Lanjutan'
- Menentukan Kisaran Daftar sebagai $ A $ 5: $ D $ 26, Kisaran Kriteria sebagai $ A $ 1: $ D $ 3, dan Kisaran ' Salin ke' sebagai $ F $ 5: $ I $ 26. Klik 'OK.'
Kita dapat melihat semua rekaman yang memiliki Nama sebagai 'Suresh' atau 'Taran' disaring dan ditampilkan secara terpisah dalam rentang sel yang berbeda.
Contoh # 2
Sekarang kami ingin mendapatkan semua transaksi penjualan Qtr 1 dan India Selatan. Rentang Kriteria adalah sebagai berikut:
Seperti yang kita miliki di sini kondisi 'DAN', yaitu, kita ingin menampilkan catatan di mana kedua kondisi tersebut terpenuhi. Itulah mengapa 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
Sekarang kami ingin mencari penjualan di Qtr 1 atau dibuat di India Utara.
Kita perlu menentukan kriteria di baris berbeda dan kolom berbeda. Kami harus menampilkan data jika salah satu ketentuan terpenuhi, dan kedua ketentuan tersebut terkait dengan kolom yang berbeda.
Langkah:
- Perlu membuka kotak dialog 'Filter Lanjutan' .
- Tentukan Kisaran Daftar sebagai $ A $ 5: $ D $ 26
- Tentukan Rentang Kriteria sebagai $ A $ 1: $ D $ 3
- Tentukan kisaran 'Salin Ke' sebagai $ F $ 5: $ I $ 26
Hasilnya adalah sebagai berikut:
Contoh # 4
Sekarang kami ingin menemukan semua penjualan Rs. 2000-4000 dan Rs. 10.000-13.000.
Karena kami memiliki empat kondisi sebagai (Kondisi 1 DAN Kondisi 2) ATAU (Kondisi 3 DAN Kondisi 4).
(> = 2000 AND = 10000 AND <= 13000)
Itulah sebabnya kami menyebutkan ketentuan dengan " DAN" di baris yang sama dan Ketentuan dengan "ATAU" di baris yang berbeda.
Langkah:
- Untuk membuka kotak dialog 'Filter Lanjutan' , kita akan mengklik 'Lanjutan' dalam grup 'Urutkan & Filter' di bawah 'Data.'
- Di kotak dialog 'Filter Lanjutan' , kami akan menentukan
- Daftar Range sebagai $ A $ 5: $ D $ 26
- Kisaran Kriteria sebagai $ A $ 1: $ D $ 3
- 'Salin ke' Rentang sebagai $ F $ 5: $ I $ 26
- Setelah mengklik 'OK.' Hasilnya adalah:
Contoh # 5
Sekarang kami ingin mencari penjualan Qtr 1 oleh Sunny atau Qtr 3 oleh Mukesh.
Karena kami memiliki AND dan OR , kedua jenis relasi dalam kondisi, itulah sebabnya kami akan menentukan kondisi dalam rentang kriteria di baris berbeda (OR) dan kolom berbeda (AND).
Langkah:
- Untuk membuka kotak dialog 'Filter Lanjutan' , kita akan mengklik 'Lanjutan' dalam grup 'Urutkan & Filter' di bawah 'Data.'
- Di kotak dialog 'Filter Lanjutan' , kami akan menentukan
- Daftar Range sebagai $ A $ 5: $ D $ 26
- Kisaran Kriteria sebagai $ A $ 1: $ D $ 3
- 'Salin ke' Rentang sebagai $ F $ 5: $ I $ 26
- Setelah mengklik OK, hasilnya adalah
Contoh # 6 - Menggunakan Karakter WILDCARD
Kami ingin menemukan semua transaksi penjualan yang memiliki nama yang diakhiri dengan 'esh' atau kata pertama wilayah yang diakhiri dengan 'st' dan hanya ingin mengambil Nama, Penjualan, dan Wilayah.
Di sini * menunjukkan lebih dari satu karakter dan
'?' hanya menunjukkan satu karakter.
Karena kita hanya menginginkan beberapa kolom, tidak semua, maka kita perlu menentukan label kolom pada Salin ke Rentang sebelum menerapkan filter lanjutan.
Sekarang kita akan memanggil perintahnya.
Langkah:
- Untuk membuka kotak dialog 'Filter Lanjutan' , kita akan mengklik 'Lanjutan' dalam grup 'Urutkan & Filter' di bawah 'Data.'
- Di kotak dialog 'Filter Lanjutan' , kami akan menentukan
- Daftar Range sebagai $ A $ 5: $ D $ 26
- Kisaran Kriteria sebagai $ A $ 1: $ D $ 3
- 'Salin ke' Rentang sebagai $ F $ 5: $ H $ 26
- Setelah mengklik ' OK.' Hasilnya adalah:
Contoh # 7
Sekarang kami ingin memfilter lima penjualan teratas (dengan jumlah besar).
Sel rumus harus mengevaluasi TRUE atau FALSE karena kita ingin mendapatkan lima record terbesar. Itulah sebabnya kami menggunakan Fungsi Excel BESAR dan membandingkan nilainya dengan Jumlah Penjualan .
Seperti yang bisa kita lihat, judul kolom untuk sel formula kosong. Kita bisa tetap mengosongkannya atau memberikan nama yang tidak cocok dengan judul kolom manapun dalam rentang data.
Sekarang kami akan menentukan rentang di kotak dialog 'Filter Lanjutan' . Langkah-langkahnya adalah:
- Untuk membuka kotak dialog 'Filter Lanjutan' , kita akan mengklik 'Lanjutan' dalam grup 'Urutkan & Filter' di bawah 'Data.'
- Di kotak dialog 'Filter Lanjutan Excel' , kami akan menentukan
- Daftar Range sebagai $ A $ 5: $ D $ 26
- Kisaran Kriteria sebagai $ A $ 1: $ E $ 2
- 'Salin ke' Rentang sebagai $ F $ 5: $ I $ 26
- Setelah mengklik OK. Hasilnya akan seperti ini:
Hal-hal untuk diingat
- Rentang yang perlu diterapkan harus memiliki tajuk unik karena tajuk duplikat menyebabkan masalah saat menjalankan filter lanjutan.
- Harus ada setidaknya satu baris kosong antara Rentang Daftar dan Rentang Kriteria.
