Penapis lanjutan dan beberapa keajaiban

Bagi sebahagian besar pengguna Excel, apabila perkataan "penapisan data" muncul di kepala mereka, hanya penapis klasik biasa daripada tab Data – Penapis (Data — Penapis):

Penapis sedemikian adalah perkara biasa, tidak syak lagi, dan untuk kebanyakan kes ia akan berfungsi. Walau bagaimanapun, terdapat situasi apabila anda perlu menapis mengikut sejumlah besar keadaan kompleks dalam beberapa lajur sekaligus. Penapis biasa di sini tidak begitu mudah dan saya mahukan sesuatu yang lebih berkuasa. Alat sedemikian boleh jadi penapis maju, terutamanya dengan sedikit "penamat dengan fail" (mengikut tradisi).

Base

Untuk bermula, masukkan beberapa baris kosong di atas jadual data anda dan salin pengepala jadual di sana - ini akan menjadi julat dengan syarat (diserlahkan dengan warna kuning untuk kejelasan):

Mesti ada sekurang-kurangnya satu baris kosong antara sel kuning dan jadual asal.

Dalam sel kuning anda perlu memasukkan kriteria (syarat), mengikut mana penapisan kemudiannya akan dilakukan. Sebagai contoh, jika anda perlu memilih pisang di Moscow "Auchan" pada suku III, maka keadaannya akan kelihatan seperti ini:

Untuk menapis, pilih mana-mana sel dalam julat dengan data sumber, buka tab Tarikh Dan klik Selain itu (Data — Lanjutan). Dalam tetingkap yang terbuka, julat dengan data sepatutnya telah dimasukkan secara automatik dan kami hanya perlu menentukan julat syarat, iaitu A1:I2:

Sila ambil perhatian bahawa julat syarat tidak boleh diperuntukkan "dengan jidar", iaitu anda tidak boleh memilih garis kuning kosong tambahan, kerana sel kosong dalam julat keadaan dianggap oleh Excel sebagai ketiadaan kriteria, dan keseluruhan kosong. talian sebagai permintaan untuk memaparkan semua data secara sembarangan.

Beralih Salin hasilnya ke lokasi lain akan membolehkan anda menapis senarai yang tidak terdapat di helaian ini (seperti penapis biasa), tetapi untuk memunggah baris yang dipilih ke dalam julat lain, yang kemudiannya perlu dinyatakan dalam medan Letakkan hasil dalam julat. Dalam kes ini, kami tidak menggunakan fungsi ini, kami tinggalkan Senarai penapis di tempat dan klik OK. Baris yang dipilih akan dipaparkan pada helaian:

Menambah Makro

"Nah, di manakah kemudahan di sini?" anda bertanya dan anda akan betul. Anda bukan sahaja perlu memasukkan syarat ke dalam sel kuning dengan tangan anda, tetapi juga membuka kotak dialog, masukkan julat di sana, tekan OK. Sedih, saya setuju! Tetapi "semuanya berubah apabila ia datang ©" - makro!

Bekerja dengan penapis lanjutan boleh dipercepatkan dan dipermudahkan menggunakan makro ringkas yang akan menjalankan penapis lanjutan secara automatik apabila syarat dimasukkan, iaitu menukar sebarang sel kuning. Klik kanan pada tab helaian semasa dan pilih arahan Teks sumber (Kod sumber). Dalam tetingkap yang terbuka, salin dan tampal kod berikut:

Sub Worksheet_Change(ByVal Sasaran Sebagai Julat) Jika Tidak Bersilang(Sasaran, Julat("A2:I5")) Tiada Maka Pada Ralat Sambung semula ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Julat("A1").Wilayah Semasa Tamat Jika End Sub  

Prosedur ini akan dijalankan secara automatik apabila mana-mana sel pada lembaran kerja semasa ditukar. Jika alamat sel yang diubah jatuh ke dalam julat kuning (A2:I5), maka makro ini mengalih keluar semua penapis (jika ada) dan menggunakan semula penapis lanjutan ke jadual data sumber bermula dengan A7, iaitu semuanya akan ditapis serta-merta, serta-merta selepas memasuki syarat berikut:

Jadi semuanya lebih baik, bukan? 🙂

Melaksanakan pertanyaan kompleks

Sekarang segala-galanya sedang ditapis dengan cepat, kita boleh pergi sedikit lebih mendalam ke dalam nuansa dan membuka mekanisme pertanyaan yang lebih kompleks dalam penapis lanjutan. Selain memasukkan padanan tepat, anda boleh menggunakan pelbagai aksara kad bebas (* dan ?) dan tanda ketaksamaan matematik dalam julat keadaan untuk melaksanakan carian anggaran. Kes watak tidak penting. Untuk kejelasan, saya telah meringkaskan semua pilihan yang mungkin dalam jadual:

Mata halus:

• Tanda * bermaksud sebarang nombor mana-mana aksara, dan ? – mana-mana satu watak.
• Logik dalam memproses teks dan pertanyaan berangka sedikit berbeza. Jadi, sebagai contoh, sel keadaan dengan nombor 5 tidak bermaksud untuk mencari semua nombor bermula dengan lima, tetapi sel syarat dengan huruf B adalah sama dengan B*, iaitu akan mencari mana-mana teks bermula dengan huruf B.
• Jika pertanyaan teks tidak bermula dengan tanda =, maka anda boleh meletakkan * secara mental pada penghujungnya.
• Tarikh mesti dimasukkan dalam format AS bulan-hari-tahun dan melalui pecahan (walaupun anda mempunyai tetapan Excel dan serantau).

Penghubung logik DAN-ATAU

Keadaan yang ditulis dalam sel yang berbeza, tetapi dalam baris yang sama, dianggap saling berkaitan oleh pengendali logik И (DAN):

Itu. tapis pisang untuk saya pada suku ketiga, tepatnya di Moscow dan pada masa yang sama dari Auchan.

Jika anda perlu memautkan keadaan dengan pengendali logik OR (OR), maka mereka hanya perlu dimasukkan dalam baris yang berbeza. Sebagai contoh, jika kita perlu mencari semua pesanan pengurus Volina untuk pic Moscow dan semua pesanan untuk bawang pada suku ketiga di Samara, maka ini boleh ditentukan dalam pelbagai syarat seperti berikut:

Jika anda perlu mengenakan dua atau lebih syarat pada satu lajur, maka anda hanya boleh menduplikasi pengepala lajur dalam julat kriteria dan masukkan yang kedua, ketiga, dsb. di bawahnya. syarat. Jadi, sebagai contoh, anda boleh memilih semua transaksi dari Mac hingga Mei:

Secara umum, selepas "menamatkan dengan fail", penapis lanjutan menjadi alat yang cukup baik, di beberapa tempat tidak lebih buruk daripada penapis automatik klasik.

• Penapis super pada makro
• Apakah itu makro, di mana dan bagaimana untuk memasukkan kod makro dalam Visual Basic
• Jadual pintar dalam Microsoft Excel