Penapis Lanjutan dalam Excel

Apakah Penapis Lanjutan di Excel?

Penapis lanjutan berbeza dengan penapis automatik di excel, ciri ini tidak seperti butang yang dapat digunakan pada satu klik tetikus, untuk menggunakan penapis lanjutan terlebih dahulu kita harus menentukan kriteria untuk penapis automatik dan kemudian klik tab Data dan kemudian di bahagian lanjutan untuk penapis lanjutan di mana kami akan memenuhi kriteria data kami.

Bagaimana Menggunakan Penapis Lanjutan di Excel? (Dengan Contoh)

Mari kita pelajari penggunaannya dengan beberapa contoh.

Anda boleh memuat turun Templat Excel Penapis Lanjutan ini di sini - Templat Excel Penapis Lanjutan

Contoh # 1

Andaikan kita memiliki, mengikuti data untuk menyaring berdasarkan kriteria yang berbeza.

Kita perlu memeriksa transaksi penjualan yang dilakukan oleh 'Taran' dan 'Suresh', maka kita dapat menggunakan operator ATAU yang memaparkan catatan yang memenuhi salah satu syarat. Untuk mendapatkan hasilnya kita dapat mengikuti langkah-langkah untuk menerapkan penapis ini di Excel.

  • Langkah 1: Untuk menggunakan penapis lanjutan, pertama-tama kita perlu memilih mana-mana sel dalam julat data

  • Langkah 2: kemudian klik pada Data tab-> Isih & Tapis kumpulan -> Advanced arahan

  • Langkah 3: Ketika kita mengklik 'Advanced' , kotak dialog 'Advanced Filter' akan terbuka untuk meminta List Range untuk menyaring, Criteria Range untuk menentukan kriteria dan Extract Range untuk menyalin data yang disaring (jika dikehendaki).

  • Langkah 4: Untuk Kriteria, kita perlu menyalin tajuk lajur di baris atas dan menentukan kriteria di bawah tajuk bidang. Untuk menentukan kriteria, kami dapat menggunakan operator perbandingan, seperti berikut:

  • Langkah 5: Kerana kami ingin mendapatkan semua rekod yang mempunyai nama 'Suresh' atau 'Taran'. Julat Kriteria adalah seperti di bawah:

Untuk keadaan 'ATAU' di mana kita mahu memaparkan rekod yang memenuhi mana-mana syarat, maka kita perlu menentukan kriteria dalam baris yang berbeza.

Terdapat dua tindakan dalam penapis lanjutan.

  • Tapis senarai, di tempat : Pilihan ini menyaring senarai di tempat asal iaitu pada julat senarai itu sendiri dan setelah menganalisis, kita dapat membuang penapis menggunakan perintah 'Hapus' dalam kumpulan 'Urutkan & Tapis' di bawah 'Data'

  • Salin ke lokasi lain : Pilihan ini menyalin data yang diinginkan mengikut kriteria ke julat yang ditentukan.

Kita boleh menggunakan mana-mana pilihan mengikut keperluan kita tetapi kita akan menggunakan pilihan ke-2 lebih kerap.

Sekarang kita hanya perlu

  • Buka kotak dialog 'Penapis Lanjutan'

  • Menentukan Julat Senarai sebagai $ A $ 5: $ D $ 26, Julat Kriteria sebagai $ A $ 1: $ D $ 3 dan Julat ' Salin ke' sebagai $ F $ 5: $ I $ 26. Klik pada 'OK' .

Kita dapat melihat semua catatan yang mempunyai Nama sebagai 'Suresh' atau 'Taran' disaring dan dipaparkan secara berasingan dalam julat sel yang berbeza.

Contoh # 2

Sekarang kami ingin mendapatkan semua transaksi penjualan Qtr 1 dan India Selatan. Julat Kriteria adalah seperti di bawah:

Seperti yang kita ada di sini 'DAN' iaitu, kita ingin memaparkan rekod di mana kedua-dua syarat itu dipenuhi oleh sebab itu kita telah menyebutkan kriteria di bawah kedua tajuk lajur pada baris yang sama.

Sekarang kita akan klik pada arahan 'Advanced' dalam kumpulan 'Urutkan & Tapis' di bawah tab 'Data' .

Dari kotak dialog ' Penapis Lanjutan ', kita akan memilih 'Salin ke lokasi lain' dan kemudian akan menentukan A5: D26 sebagai Julat Senarai , A1: D2 sebagai Julat Kriteria dan F5: I26 sebagai julat ' Salin Ke' .

Sekarang hasilnya adalah seperti berikut:

Contoh # 3

Sekarang kami ingin mencari penjualan di Qtr 1 atau dibuat di India Utara.

Kita perlu menentukan kedua kriteria dalam baris yang berbeza dan di lajur yang berbeza kerana kita harus memaparkan data jika ada syarat yang dipenuhi dan kedua-dua syarat tersebut berkaitan dengan lajur yang berlainan.

Langkah-langkah:

  • Perlu membuka kotak dialog 'Penapis Lanjutan' .

  • Tentukan Julat Daftar sebagai $ A $ 5: $ D $ 26

  • Tentukan Julat Kriteria sebagai $ A $ 1: $ D $ 3

  • Tentukan julat 'Salin Ke' sebagai $ F $ 5: $ I $ 26

Hasilnya adalah seperti berikut:

Contoh # 4

Sekarang kami ingin mencari semua penjualan Rs. 2000-4000 dan Rs. 10000-13000.

Kerana kita mempunyai empat syarat seperti (Syarat 1 DAN Keadaan 2) ATAU (Keadaan 3 DAN Keadaan 4).

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

Itulah sebabnya kami telah menyebut syarat dengan " DAN" pada baris yang sama dan Syarat dengan "ATAU" dalam baris yang berbeza.

Langkah-langkah:

  • Untuk membuka kotak dialog 'Penapis Lanjutan' , kita akan mengklik 'Lanjutan' dalam kumpulan 'Urutkan & Tapis' di bawah 'Data'

  • Dalam kotak dialog 'Penapis Lanjutan' , kami akan menentukan
  • Julat Senarai sebagai $ A $ 5: $ D $ 26

  • Julat Kriteria ialah $ A $ 1: $ D $ 3

  • 'Salin ke' Julat sebagai $ F $ 5: $ I $ 26

  • Selepas mengklik 'OK'. Hasilnya akan:

Contoh # 5

Sekarang kita ingin mencari penjualan Qtr 1 oleh Sunny atau penjualan Qtr 3 oleh Mukesh.

Oleh kerana kita mempunyai AND dan OR , kedua-dua jenis hubungan dalam keadaan, itulah sebabnya kita akan menentukan syarat-syarat dalam kriteria dalam baris yang berbeza (ATAU) dan lajur yang berbeza (DAN).

Langkah-langkah:

  • Untuk membuka kotak dialog 'Penapis Lanjutan' , kita akan mengklik 'Lanjutan' dalam kumpulan 'Urutkan & Tapis' di bawah 'Data'

  • Dalam kotak dialog 'Penapis Lanjutan' , kami akan menentukan
  • Julat Senarai sebagai $ A $ 5: $ D $ 26

  • Julat Kriteria ialah $ A $ 1: $ D $ 3

  • 'Salin ke' Julat sebagai $ F $ 5: $ I $ 26

  • Setelah mengklik OK, hasilnya akan menjadi

Contoh # 6 - Menggunakan Watak WILDCARD

Sekarang kita ingin mencari semua transaksi penjualan yang mempunyai nama yang diakhiri dengan 'esh' atau kata pertama wilayah yang berakhir dengan 'st' dan hanya ingin mengambil Nama, Jualan dan Wilayah sahaja.

Di sini * menunjukkan lebih daripada satu watak dan

'?' hanya menunjukkan satu watak.

Oleh kerana kami hanya mahukan beberapa lajur tidak semuanya, maka kami perlu menentukan label lajur pada Salin ke Julat sebelum menerapkan penapis lanjutan.

Sekarang kita akan memanggil perintah.

Langkah-langkah:

  • Untuk membuka kotak dialog 'Penapis Lanjutan' , kita akan mengklik 'Lanjutan' dalam kumpulan 'Urutkan & Tapis' di bawah 'Data'

  • Dalam kotak dialog 'Penapis Lanjutan' , kami akan menentukan
  • Julat Senarai sebagai $ A $ 5: $ D $ 26

  • Julat Kriteria ialah $ A $ 1: $ D $ 3

  • 'Salin ke' Julat sebagai $ F $ 5: $ H $ 26

  • Selepas mengklik ' OK'. Hasilnya adalah:

Contoh # 7

Sekarang kami ingin menapis lima jualan teratas (dalam jumlah besar).

Sel formula mesti dinilai sama ada BENAR atau SALAH . Oleh kerana kami ingin mendapatkan 5 rekod terbesar itulah sebabnya kami menggunakan Fungsi Excel BESAR dan membandingkan nilainya dengan Jumlah Jualan .

Seperti yang kita lihat, lajur menuju sel formula kosong. Kita boleh membiarkannya kosong atau memberikan nama, yang tidak sepadan, dengan tajuk lajur dalam julat data.

Sekarang kita akan menentukan julat dalam kotak dialog 'Penapis Lanjutan' . Langkahnya adalah:

  • Untuk membuka kotak dialog 'Penapis Lanjutan' , kita akan mengklik 'Lanjutan' dalam kumpulan 'Urutkan & Tapis' di bawah 'Data'

  • Dalam kotak dialog 'Penapis Lanjutan Excel' , kami akan menentukan
  • Julat Senarai sebagai $ A $ 5: $ D $ 26

  • Julat Kriteria adalah $ A $ 1: $ E $ 2

  • 'Salin ke' Julat sebagai $ F $ 5: $ I $ 26

  • Selepas mengklik OK. Hasilnya akan seperti ini:

Perkara yang Perlu Diingat

  • Julat yang harus diterapkan harus memiliki tajuk unik kerana tajuk pendua menyebabkan masalah ketika menjalankan filter lanjutan.
  • Terdapat sekurang-kurangnya satu baris kosong antara Julat Daftar dan Julat Kriteria.