Cara Menggunakan Penapis Lanjutan dalam Excel - Kriteria Contoh dengan Formula

Tutorial menunjukkan cara menggunakan penapis lanjutan dalam Excel dan menyediakan beberapa contoh kriteria yang tidak remeh untuk membuat penapis sensitif kes, mencari perlawanan dan perbezaan antara dua lajur, rekod ekstrak yang sepadan dengan senarai yang lebih kecil, dan banyak lagi.

Dalam artikel sebelumnya, kami membincangkan pelbagai aspek penapis lanjutan Excel dan cara menggunakannya untuk menapis baris dengan dan juga atau logik. Sekarang bahawa anda tahu asas -asas, mari kita lihat contoh pelbagai kriteria yang lebih kompleks yang boleh membuktikan kerja anda.

• Cara Menyiapkan Julat Kriteria Berasaskan Formula
• Contoh Pelbagai Kriteria Penapis Lanjutan
  • Penapis sensitif kes untuk nilai teks
  • Nilai penapis di atas atau di bawah purata dalam lajur
  • Baris penapis dengan sel kosong / bukan kosong
  • Ekstrak rekod atas / bawah N
  • Perlawanan penapis dan perbezaan antara 2 lajur
  • Penapis baris yang sepadan atau tidak sepadan dengan item dalam senarai
  • Penapis hujung minggu dan hari kerja

Menyediakan pelbagai kriteria berasaskan formula

Memandangkan kebanyakan kriteria berkisar contoh yang dibincangkan dalam tutorial ini akan memasukkan pelbagai formula, mari kita mulakan dengan menentukan peraturan penting untuk menetapkannya dengan betul. Percayalah, teori kecil ini akan menjimatkan banyak masa dan melepaskan sakit kepala menyelesaikan masalah kriteria kompleks anda yang termasuk pelbagai syarat berdasarkan formula.

• Formula yang anda gunakan dalam julat kriteria mesti menilai kepada benar atau palsu .
• Julat kriteria harus mengandungi sekurang -kurangnya 2 sel: sel formula dan sel header.
• Sel header kriteria berasaskan formula hendaklah sama ada kosong atau berbeza dari mana-mana tajuk jadual (senarai senarai).
• Untuk formula yang akan dinilai untuk setiap baris dalam julat senarai, rujuk sel teratas dengan data menggunakan rujukan relatif seperti A1.
• Untuk formula yang akan dinilai hanya untuk sel tertentu atau julat sel , rujuk sel atau julat menggunakan rujukan mutlak seperti $ A $ 1.
• Apabila merujuk julat senarai dalam formula, selalu gunakan rujukan sel mutlak.
• Apabila membekalkan pelbagai syarat, masukkan semua kriteria pada baris yang sama untuk menyertai mereka dengan dan pengendali, dan letakkan setiap kriteria pada baris berasingan untuk menyertai mereka dengan pengendali atau pengendali.

Contoh Kriteria Penapis Lanjutan Excel

Contoh -contoh berikut akan mengajar anda bagaimana untuk membuat penapis anda sendiri dalam Excel untuk mengendalikan tugas yang lebih kompleks yang tidak dapat dilakukan menggunakan autofilter Excel biasa.

Penapis sensitif kes untuk nilai teks

Serta Autofilter Excel, alat penapis lanjutan adalah sifat yang tidak sensitif, yang bermaksud bahawa ia tidak membezakan antara huruf besar dan huruf kecil apabila menapis nilai teks. Walau bagaimanapun, anda boleh dengan mudah melakukan carian sensitif kes dengan menggunakan fungsi yang tepat dalam kriteria penapis lanjutan.

Sebagai contoh, untuk menapis baris yang mengandungi pisang , mengabaikan pisang dan pisang , masukkan formula berikut dalam pelbagai kriteria:

=EXACT(B5, "Banana")

Di mana B ialah lajur yang mengandungi nama item, dan baris 5 adalah baris data pertama.

Dan kemudian, gunakan Excel Advanced Filter dengan mengklik butang Lanjutan pada tab Data , dan konfigurasikan julat senarai dan kriteria seperti yang ditunjukkan dalam tangkapan skrin di bawah. Sila perhatikan bahawa pelbagai kriteria termasuk 2 sel - sel header dan sel formula .

Nota. Imej di atas juga semua tangkapan skrin selanjutnya dalam formula tutorial ini dalam sel -sel kriteria sel -sel semata -mata demi kejelasan. Dalam lembaran kerja sebenar anda, sel formula harus kembali sama ada benar atau palsu, bergantung kepada sama ada baris pertama data sepadan dengan kriteria atau tidak:

Nilai penapis di atas atau di bawah purata dalam lajur

Apabila penapisan nilai angka, anda mungkin sering mahu memaparkan hanya sel -sel yang berada di atas atau di bawah nilai purata dalam lajur. Contohnya:

Untuk menapis baris dengan sub-total di atas purata , gunakan formula berikut dalam julat kriteria:

=F5>AVERAGE($F$5:$F$50)

Untuk menapis baris dengan sub-total di bawah purata , gunakan formula berikut:

=F5<average></average>

Sila perhatikan bahawa kami menggunakan rujukan relatif untuk merujuk kepada sel atas dengan data (F5), dan rujukan mutlak untuk menentukan keseluruhan julat yang anda ingin mengira purata, tidak termasuk tajuk lajur ($ F $ 5: $ F $ 50).

Tangkapan skrin berikut menunjukkan formula purata di atas dalam tindakan:

Mereka yang biasa dengan penapis nombor Excel mungkin tertanya-tanya, mengapa seseorang mengganggu untuk menggunakan penapis lanjutan manakala penapis nombor terbina dalam sudah mempunyai purata di atas dan di bawah pilihan purata ? Betul, tetapi penapis Excel yang terbina tidak dapat digunakan dengan atau logik!

Oleh itu, untuk mengambil contoh ini lebih lanjut, mari penapis baris di mana sub-total (lajur F) atau September jualan (lajur E) melebihi purata. Untuk ini, sediakan julat kriteria dengan atau logik dengan memasukkan setiap keadaan pada baris yang berasingan. Hasilnya, anda akan mendapat senarai item dengan nilai purata di atas dalam lajur e atau f:

Baris penapis dengan tempat kosong atau bukan kosong

Seperti yang diketahui oleh semua orang, Excel Filter mempunyai pilihan terbina untuk menapis sel kosong. Dengan memilih atau menghilangkan kotak semak (kosong) dalam menu autofilter, anda boleh memaparkan hanya baris yang mempunyai sel-sel kosong atau tidak kosong dalam satu atau lebih lajur. Masalahnya ialah penapis Excel terbina dalam untuk kosong hanya boleh berfungsi dengan dan logik.

Jika anda ingin menapis sel kosong atau tidak kosong dengan atau logik, atau gunakan keadaan kosong / bukan kosong bersama-sama dengan beberapa kriteria lain, sediakan pelbagai kriteria penapis lanjutan dengan salah satu formula berikut:

Penapis kosong :

top_cell = ""

Penapis bukan kosong:

top_cell ""

Menapis sel kosong dengan atau logik

Untuk menapis baris yang mempunyai sel kosong sama ada dalam lajur A atau B, atau dalam kedua -dua lajur, konfigurasikan pelbagai kriteria penapis lanjutan dengan cara ini:

• =A6=""
• =B6=""

Di mana 6 adalah baris data paling teratas.

Penapisan sel bukan kosong dengan atau sebagai telaga dan logik

Untuk mendapatkan lebih banyak pemahaman tentang bagaimana penapis lanjutan Excel berfungsi dengan pelbagai kriteria, mari penapis baris dalam jadual sampel kami dengan syarat -syarat berikut:

• Sama ada rantau (lajur a) atau item (lajur b) hendaklah tidak kosong, dan
• Sub-total (lajur C) hendaklah lebih besar daripada 900.

Untuk meletakkannya secara berbeza, kami ingin memaparkan baris yang memenuhi syarat -syarat berikut:

( Subtotal > 900 dan rantau = tidak kosong) atau ( subtotal > 900 dan item = bukan kosong)

Seperti yang sudah anda ketahui, dalam pelbagai kriteria Penapis Lanjutan Excel, keadaan yang bergabung dengan dan logik harus dimasukkan dalam baris yang sama, dan syarat -syarat yang bergabung dengan atau logik - pada baris yang berbeza:

Kerana satu kriteria dalam contoh ini dinyatakan dengan formula (bukan kosong) dan yang lain termasuk pengendali perbandingan (sub-total> 900), izinkan saya mengingatkan anda bahawa:

• Kriteria yang dibentuk dengan pengendali perbandingan sepatutnya mempunyai tajuk yang sama dengan tajuk meja, seperti kriteria sub-total di tangkapan skrin di atas.
• Kriteria berasaskan formula sepatutnya mempunyai sel tajuk kosong atau tajuk yang tidak sepadan dengan mana-mana tajuk meja, seperti kriteria bukan kosong di tangkapan skrin di atas.

Cara mengekstrak rekod atas/bawah

Seperti yang anda ketahui, penapis nombor Excel membina mempunyai pilihan untuk memaparkan 10 item teratas atau bawah. Tetapi bagaimana jika anda perlu menapis nilai 3 atau bawah 5 atas? Dalam kes ini, Excel Advanced Filter dengan formula berikut berguna:

Ekstrak item N atas :

top_cell > = besar ( julat , n)

Ekstrak Bawah N Item:

top_cell julat , n)

Sebagai contoh, untuk menapis 3 subtotal atas, buat pelbagai kriteria dengan formula ini:

=F5>=LARGE($F$5:$F$50,3)

Untuk mengekstrak bawah 3 subtotal, gunakan formula ini:

=F5>=SMALL($F$5:$F$50,3)

Di mana F5 adalah sel teratas dengan data dalam lajur subtotal (tidak termasuk tajuk lajur).

Tangkapan skrin berikut menunjukkan formula 3 teratas dalam tindakan:

Nota. Jika julat senarai mengandungi beberapa baris dengan nilai yang sama yang jatuh ke dalam senarai atas/bawah, semua baris tersebut akan dipaparkan, seperti yang ditunjukkan dalam tangkapan skrin di bawah:

Penapis untuk perlawanan dan perbezaan antara dua lajur

Salah satu artikel terdahulu kami menjelaskan pelbagai cara untuk membandingkan dua lajur dalam Excel dan mencari perlawanan dan perbezaan di antara mereka. Sebagai tambahan kepada formula Excel, peraturan pemformatan bersyarat dan alat penghilang pendua yang diliputi dalam tutorial di atas, anda juga boleh menggunakan penapis lanjutan Excel untuk mengekstrak baris yang mempunyai nilai yang sama atau berbeza dalam dua atau lebih lajur. Untuk melakukan ini, masukkan salah satu formula mudah berikut dalam pelbagai kriteria:

• Penapis untuk perlawanan (pendua) dalam 2 lajur: =B5=C5
• Penapis untuk perbezaan (nilai unik) dalam 2 lajur: =B5C5

Di mana B5 dan C5 adalah sel-sel teratas dengan data dalam dua lajur yang anda mahu bandingkan.

Nota. Alat Penapis Lanjutan hanya boleh mencari perlawanan dan perbezaan dalam baris yang sama . Untuk mencari semua nilai yang ada dalam lajur A tetapi tidak berada di mana -mana dalam lajur B, gunakan formula ini.

Baris penapis berdasarkan item yang sepadan dalam senarai

Katakan anda mempunyai meja besar dengan beratus -ratus atau beribu -ribu baris, dan anda menerima senarai yang lebih pendek yang mengandungi hanya item yang relevan pada saat tertentu. Persoalannya - bagaimana anda menemui semua penyertaan dalam jadual anda yang berada atau tidak dalam senarai yang lebih kecil?

Penapis baris yang sepadan dengan item dalam senarai

Untuk mencari semua item dalam jadual sumber yang juga terdapat dalam senarai yang lebih kecil, menggunakan formula Countif berikut:

Countif ( list_to_match , top_data_cell)

Dengan mengandaikan bahawa senarai yang lebih kecil berada dalam julat D2: D7, dan item jadual untuk dibandingkan dengan senarai itu dalam lajur B bermula dengan baris 10, formula berjalan seperti berikut (sila perhatikan penggunaan rujukan mutlak dan relatif):

=COUNTIF($D$2:$D$7,B10)

Sudah tentu, anda tidak terhad untuk menapis jadual anda dengan hanya satu kriteria.

Sebagai contoh, untuk menapis baris yang sepadan dengan senarai, tetapi untuk wilayah Utara sahaja, masukkan dua kriteria dalam baris yang sama supaya mereka akan bekerja dengan dan logik:

• Wilayah: ="=North"
• Item Padanan: =COUNTIF($D$2:$D$7,B10)

Seperti yang anda lihat dalam tangkapan skrin di bawah, hanya terdapat dua rekod dalam jadual yang sepadan dengan kedua -dua kriteria:

Nota. Dalam contoh ini, kita menggunakan kriteria padanan tepat untuk nilai teks: <em>="=North</em> " untuk mencari hanya sel -sel yang sama persis dengan teks yang ditentukan. Jika anda memasuki kriteria rantau ini hanya sebagai utara (tanpa tanda yang sama dan petikan berganda), Microsoft Excel akan menemui semua item yang bermula dengan teks yang ditentukan, misalnya timur laut atau barat laut . Untuk maklumat lanjut, sila lihat Excel Advanced Filter untuk nilai teks.

Baris penapis yang tidak sepadan dengan item dalam senarai

Untuk mencari semua item dalam jadual yang tidak dalam senarai yang lebih kecil, periksa sama ada hasil formula countif kami adalah sama dengan sifar:

Countif ( list_to_match , top_data_cell) = 0

Sebagai contoh, untuk menapis item Wilayah Utara dalam jadual yang muncul dalam senarai, gunakan kriteria berikut:

• Wilayah: ="=North"
• Item Tidak Memadankan: =COUNTIF($D$2:$D$7,B10)=0

Nota:

• Jika senarai yang dipadankan berada dalam lembaran kerja yang berbeza, pastikan anda memasukkan nama lembaran dalam formula, misalnya =COUNTIF(Sheet2!$A$2:$A$7,B10) .
• Jika anda ingin mengekstrak hasilnya ke lembaran yang berbeza, mulakan penapis lanjutan dari lembaran destinasi, seperti yang dijelaskan dalam cara mengekstrak baris yang ditapis ke lembaran kerja lain.

Penapis untuk hujung minggu dan hari kerja

Setakat ini, Contoh Kriteria Penapis Lanjutan kami telah ditangani kebanyakannya dengan nilai angka dan teks. Sekarang, sudah tiba masanya untuk memberi petunjuk kepada anda yang beroperasi pada tarikh.

Penapis tarikh Excel terbina dalam menyediakan pelbagai pilihan yang meliputi banyak senario. Ramai, tetapi tidak semua! Sebagai contoh, jika anda diberi senarai tarikh dan diminta untuk menapis hari kerja dan hujung minggu, bagaimana anda akan melakukannya?

Seperti yang anda tahu, Microsoft Excel menyediakan fungsi hari kerja khas yang mengembalikan hari dalam seminggu yang sepadan dengan tarikh tertentu. Dan ini adalah fungsi yang akan kita gunakan dalam pelbagai kriteria penapis lanjutan Excel.

Cara Menapis Hujung Minggu di Excel

Perlu diingat bahawa, dalam istilah hari kerja, 1 bermaksud hari Ahad dan 6 bermaksud hari Sabtu, formula untuk menapis hujung minggu pergi seperti berikut:

Atau (hari kerja ( tarikh ) = 7, hari kerja ( tarikh ) = 1)

Dalam contoh ini, kami menapis tarikh dalam lajur B bermula dengan baris 5, jadi formula hujung minggu kami mengambil bentuk berikut:

=OR(WEEKDAY(B5)=7, WEEKDAY(B5)=1)

Cara menapis hari kerja di Excel

Untuk menapis hari kerja, ubah formula di atas supaya ia akan meninggalkan 1 (Ahad) dan 7 (Sabtu):

Dan (hari kerja ( tarikh ) 7, hari kerja ( tarikh ) 1)

Untuk jadual sampel kami, formula berikut akan berfungsi:

=AND(WEEKDAY(B5)7, WEEKDAY(B5)1)

Di samping itu, anda boleh menambah satu lagi syarat untuk menapis sel kosong: =B5""

Untuk menapis tarikh dalam lembaran kerja anda dengan cara lain, cari fungsi tarikh yang relevan dan jangan ragu untuk menggunakannya dalam pelbagai kriteria penapis lanjutan anda.

Nah, inilah cara anda menggunakan penapis lanjutan dalam Excel dengan kriteria kompleks. Sudah tentu, pilihan anda tidak terhad kepada contoh -contoh yang dibincangkan dalam tutorial ini, matlamat kami adalah untuk memberi anda beberapa idea inspirasi yang akan menjadikan anda di landasan yang betul. Mengingati bahawa jalan menuju penguasaan diturap dengan amalan, anda mungkin ingin memuat turun contoh kami menggunakan pautan di bawah dan melanjutkan atau membalikkan mereka untuk pemahaman yang lebih baik. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Amalan Buku Kerja

Contoh Penapis Lanjutan Excel (fail .xlsx)

Atas ialah kandungan terperinci Cara Menggunakan Penapis Lanjutan dalam Excel - Kriteria Contoh dengan Formula. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!