Cara menggunakan fungsi Excel FILTER

Office 365 membawakan beberapa ciri hebat seperti XLOOKUP, SORT dan FILTER.

Apabila menapis data dalam Excel, dalam dunia pra-Office 365, kami kebanyakannya bergantung pada penapis terbina dalam Excel atau pada kebanyakan penapis lanjutan atau formula SUMPRODUCT yang kompleks.Ini selalunya merupakan penyelesaian yang rumit jika anda perlu menapis sebahagian daripada set data.

Tetapi dengan ciri FILTER baharu, kini sangat mudah untuk menapis bahagian set data berdasarkan syarat dengan cepat.

Dalam tutorial ini, saya akan menunjukkan kepada anda betapa hebatnya ciri FILTER baharu dan beberapa perkara berguna yang boleh anda lakukan dengannya.

Tetapi sebelum saya masuk ke dalam contoh, mari kita lihat dengan pantas sintaks fungsi FILTER.

Jika anda ingin mendapatkan ciri baharu ini dalam Excel, anda bolehNaik taraf kepada Office 365(Sertai program dalaman untuk mengakses semua ciri/formula)

Fungsi Penapis Excel – Sintaks

Berikut ialah sintaks fungsi FILTER:

=FILTER(array,include,[jika_kosong])

• pelbagai - ini ialah julat sel yang anda mempunyai data dan ingin menapis beberapa data daripadanya
• termasuk - Ini ialah keadaan yang memberitahu fungsi yang merekodkan untuk ditapis
• [jika_kosong] – Ini ialah parameter pilihan di mana anda boleh menentukan perkara yang akan dikembalikan jika fungsi FILTER tidak menemui hasil.Secara lalai (apabila tidak dinyatakan) ia mengembalikan #CALC!Kesalahan

Sekarang mari kita lihat beberapa contoh menakjubkan fungsi penapis dan apa yang boleh dilakukannya yang dahulunya sangat rumit tanpanya.

Contoh 1: Tapis data berdasarkan satu syarat (rantau)

Katakan anda mempunyai set data seperti di bawah dan anda hanya mahu menapis semua rekod di Amerika Syarikat.

Berikut ialah formula FILTER yang melakukan ini:

=PENAPIS($A$2:$C$11,$B$2:$B$11="AS")

Formula di atas mengambil set data sebagai tatasusunan, dan syaratnya ialah $B$2:$B$11=”AS”

Keadaan ini akan menyebabkan fungsi FILTER menyemak setiap sel dalam lajur B (sel dengan julat) dan menapis hanya rekod yang sepadan dengan keadaan ini.

Selain itu, dalam contoh ini, saya meletakkan data asal dan ditapis pada helaian yang sama, tetapi anda juga boleh meletakkannya dalam helaian berasingan atau bahkan buku kerja.

Fungsi penapis mengembalikan tatasusunan hasil yang dinamik (bermaksud bukannya mengembalikan nilai, ia mengembalikan tatasusunan yang melimpah ke dalam sel lain).

Untuk ini, anda perlu mempunyai kawasan di mana hasilnya kosong.Sudah ada sesuatu dalam mana-mana sel dalam julat (E2:G5 dalam contoh ini) dan fungsi itu akan memberi anda ralat #SPILL.

Selain itu, kerana ini adalah tatasusunan dinamik, anda tidak boleh menukar sebahagian daripada hasilnya.Anda boleh memadamkan keseluruhan julat dengan hasil atau sel E2 (di mana formula dimasukkan).Kedua-dua ini akan memadamkan keseluruhan tatasusunan hasil.Tetapi anda tidak boleh menukar mana-mana sel tunggal (atau memadamkannya).

Dalam formula di atas, saya mengekodkan nilai julat, tetapi anda juga boleh meletakkannya dalam sel dan merujuk sel itu dengan nilai julat.

Sebagai contoh, dalam contoh di bawah, saya mempunyai nilai julat dalam sel I2 dan kemudian merujuknya dalam formula:

=FILTER($A$2:$C$11,$B$2:$B$11=I1)

Ini menjadikan formula lebih berguna, kini anda hanya perlu menukar nilai julat dalam sel I2 dan penapis akan berubah secara automatik.

Anda juga boleh mempunyai menu lungsur dalam sel I2 di mana anda boleh membuat pilihan dan ia akan mengemas kini data yang ditapis serta-merta.

Contoh 2: Tapis data berdasarkan satu kriteria (lebih besar atau kurang daripada)

Anda juga boleh menggunakan operator perbandingan dalam fungsi penapis dan mengekstrak semua rekod yang lebih besar atau kurang daripada nilai tertentu.

Sebagai contoh, katakan anda mempunyai set data yang ditunjukkan di bawah dan anda mahu menapis semua rekod dengan jualan melebihi 10000.

Formula berikut boleh melakukan ini:

=FILTER($A$2:$C$11,($C$2:$C$11>10000))

Argumen tatasusunan merujuk kepada keseluruhan set data, dalam kes ini keadaan ($C$2:$C$11>10000).

Formula menyemak setiap rekod untuk nilai dalam lajur C.Jika nilai lebih besar daripada 10000, ia ditapis, jika tidak, ia diabaikan.

Jika anda ingin mendapatkan semua rekod kurang daripada 10000, anda boleh menggunakan formula berikut:

=FILTER($A$2:$C$11,($C$2:$C$11<10000))

Anda juga boleh menjadi lebih kreatif dengan formula FILTER.Contohnya, jika anda ingin menapis tiga rekod teratas berdasarkan jualan, anda boleh menggunakan formula berikut:

=FILTER($A$2:$C$11,($C$2:$C$11>=LARGE(C2:C11,3)))

Formula di atas menggunakan fungsi LARGE untuk mendapatkan nilai ketiga terbesar dalam set data.Kemudian gunakan nilai tersebut dalam keadaan fungsi FILTER untuk mendapatkan semua rekod dengan jualan lebih besar daripada atau sama dengan nilai ketiga terbesar.

Contoh 3: Menapis data menggunakan berbilang syarat (DAN)

Katakan anda mempunyai set data berikut dan anda ingin menapis semua rekod di Amerika Syarikat dengan nilai jualan lebih daripada 10000.

Ini adalah syarat DAN, anda perlu menyemak dua perkara - rantau ini perlu berada di AS dan jualan perlu melebihi 10000.Jika hanya satu syarat dipenuhi, hasilnya tidak boleh ditapis.

Berikut ialah formula penapis yang akan menapis rekod dengan AS sebagai rantau dan dengan lebih daripada 10000 jualan:

=FILTER($A$2:$C$11,($B$2:$B$11="US")*($C$2:$C$11>10000))

請注意，標準（稱為包含參數）是 ($B$2:$B$11=”US”)*($C$2:$C$11>10000)

Oleh kerana saya menggunakan dua syarat dan saya memerlukan kedua-duanya adalah benar, saya telah menggunakan pengendali pendaraban untuk menggabungkan dua syarat.Ini mengembalikan tatasusunan 0s dan 1s, di mana 1 hanya dikembalikan jika kedua-dua syarat dipenuhi.

Jika tiada rekod yang sepadan, fungsi akan mengembalikan #CALC!Kesalahan.

Jika anda ingin mengembalikan sesuatu yang bermakna (bukan ralat), anda boleh menggunakan formula seperti ini:

=FILTER($A$2:$C$11,($B$2:$B$11="USA")*($C$2:$C$11>10000),"Nothing Found")

Di sini, saya telah menggunakan "tidak dijumpai" sebagai parameter ketiga, yang digunakan apabila tiada rekod sepadan ditemui.

Contoh 4: Menapis data menggunakan berbilang kriteria (ATAU)

Anda juga boleh mengubah suai parameter "mengandungi" dalam fungsi FILTER untuk menyemak keadaan ATAU (yang mana-mana syarat tertentu boleh benar).

Sebagai contoh, katakan anda mempunyai set data yang ditunjukkan di bawah dan anda ingin menapis rekod di mana negara itu ialah Amerika Syarikat atau Kanada.

Berikut ialah formula untuk melakukan ini:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+($B$2:$B$11="Canada"))

Ambil perhatian bahawa dalam formula di atas, saya hanya menambah dua syarat menggunakan pengendali penambahan.Memandangkan setiap syarat ini mengembalikan tatasusunan TRUE dan FALSE, saya boleh menambah tatasusunan gabungan yang akan menjadi TRUE jika mana-mana syarat dipenuhi.

Contoh lain mungkin apabila anda ingin menapis semua rekod di mana negara itu ialah Amerika Syarikat atau nilai jualan melebihi 10000.

Formula berikut akan melakukan ini:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+(C2:C11>10000))

NOTA: Apabila menggunakan syarat AND dalam fungsi FILTER, gunakan operator darab (*), dan apabila menggunakan syarat OR, gunakan operator penambahan (+).

Contoh 5: Tapis data untuk rekod di atas/bawah purata

Anda boleh menggunakan formula dalam fungsi FILTER untuk menapis dan mengekstrak rekod dengan nilai di atas atau di bawah purata.

Sebagai contoh, katakan anda mempunyai set data yang ditunjukkan di bawah dan anda mahu menapis semua rekod dengan nilai jualan melebihi purata.

Anda boleh melakukan ini dengan formula berikut:

=FILTER($A$2:$C$11,C2:C11>AVERAGE(C2:C11))

Sekali lagi, untuk di bawah purata anda boleh menggunakan formula berikut:

=FILTER($A$2:$C$11,C2:C11<AVERAGE(C2:C11))

Contoh 6: Tapis hanya rekod genap (atau rekod ganjil)

Jika anda perlu menapis dan mengekstrak semua rekod dengan cepat dalam baris genap atau ganjil, anda boleh menggunakan fungsi FILTER untuk melakukannya.

Untuk melakukan ini, anda perlu menyemak nombor baris dalam fungsi FILTER dan tapis hanya nombor baris yang sepadan dengan syarat nombor baris.

Katakan anda mempunyai set data seperti di bawah dan saya hanya mahu mengekstrak walaupun rekod daripada set data ini.

Berikut ialah formula untuk melakukan ini:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=0)

Formula di atas menggunakan fungsi MOD untuk menyemak nombor baris (diberikan oleh fungsi ROW) setiap rekod.

Formula MOD(ROW(A2:A11)-1,2)=0 mengembalikan BENAR apabila nombor baris genap dan FALSE apabila ganjil.Ambil perhatian bahawa saya telah menolak 2 daripada bahagian ROW(A11:A1) kerana rekod pertama berada di baris kedua, yang melaraskan nombor baris untuk menganggap baris kedua sebagai rekod pertama.

Begitu juga, anda boleh menapis semua rekod ganjil dengan formula berikut:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=1)

Contoh 7: Mengisih data yang ditapis menggunakan formula

Menggunakan fungsi FILTER dengan fungsi lain membolehkan kami melakukan lebih banyak lagi.

Contohnya, jika anda menggunakan fungsi FILTER untuk menapis set data, anda boleh menggunakan fungsi SORT untuk mendapatkan hasil yang diisih.

Katakan anda mempunyai set data seperti yang ditunjukkan di bawah dan anda ingin menapis semua rekod dengan jualan melebihi 10000.Anda boleh menggunakan fungsi SORT dengan fungsi ini untuk memastikan data yang terhasil diisih mengikut jualan.

Formula berikut akan melakukan ini:

=SORT(FILTER($A$2:$C$11,($C$2:$C$11>10000)),3,-1)

Fungsi di atas menggunakan fungsi FILTER untuk mendapatkan data dalam lajur C dengan jualan melebihi 10000.Kemudian gunakan tatasusunan yang dikembalikan oleh fungsi FILTER dalam fungsi SORT untuk mengisih data mengikut jualan.

Parameter kedua dalam fungsi SORT ialah 3, iaitu mengisih mengikut lajur ketiga.Parameter keempat ialah -1, yang mengisih data dalam susunan menurun.

Jadi ini adalah 7 contoh penggunaan fungsi FILTER dalam Excel.

Harap anda mendapati tutorial ini berguna.