Filter tingkat lanjut dan beberapa keajaiban

Untuk sebagian besar pengguna Excel, ketika kata "pemfilteran data" muncul di kepala mereka, hanya filter klasik biasa dari tab Data – Saring (Data — Filter):

Filter seperti itu adalah hal yang biasa, tidak diragukan lagi, dan untuk sebagian besar kasus itu akan berhasil. Namun, ada situasi ketika Anda perlu memfilter berdasarkan sejumlah besar kondisi kompleks di beberapa kolom sekaligus. Filter biasa di sini sangat tidak nyaman dan saya menginginkan sesuatu yang lebih kuat. Alat seperti itu bisa jadi filter lanjutan, terutama dengan sedikit "penyelesaian dengan file" (menurut tradisi).

Dasar

Untuk memulai, masukkan beberapa baris kosong di atas tabel data Anda dan salin tajuk tabel di sana – ini akan menjadi rentang dengan kondisi (disorot dengan warna kuning untuk kejelasan):

Harus ada setidaknya satu baris kosong antara sel kuning dan tabel asli.

Di sel kuning Anda harus memasukkan kriteria (kondisi), yang dengannya penyaringan akan dilakukan. Misalnya, jika Anda perlu memilih pisang di "Auchan" Moskow pada kuartal III, maka kondisinya akan terlihat seperti ini:

Untuk memfilter, pilih sel mana saja dalam rentang dengan data sumber, buka tab Data Dan klik Selain itu (Data — Lanjutan). Di jendela yang terbuka, rentang dengan data seharusnya sudah dimasukkan secara otomatis dan kita hanya perlu menentukan rentang kondisi, yaitu A1:I2:

Harap dicatat bahwa rentang kondisi tidak dapat dialokasikan "dengan margin", yaitu Anda tidak dapat memilih garis kuning ekstra kosong, karena sel kosong dalam rentang kondisi dianggap oleh Excel sebagai tidak adanya kriteria, dan keseluruhan kosong baris sebagai permintaan untuk menampilkan semua data tanpa pandang bulu.

Beralih Salin hasilnya ke lokasi lain akan memungkinkan Anda untuk memfilter daftar tidak di sana pada lembar ini (seperti dengan filter biasa), tetapi untuk membongkar baris yang dipilih ke rentang lain, yang kemudian perlu ditentukan di bidang Masukkan hasil dalam jangkauan. Dalam hal ini, kami tidak menggunakan fungsi ini, kami pergi Filter daftar di tempat dan klik OK. Baris yang dipilih akan ditampilkan pada lembar:

Menambahkan Makro

"Nah, di mana kenyamanan di sini?" Anda bertanya dan Anda akan benar. Anda tidak hanya perlu memasukkan kondisi ke dalam sel kuning dengan tangan Anda, tetapi juga membuka kotak dialog, memasukkan rentang di sana, tekan OK. Sedih, saya setuju! Tapi "semuanya berubah ketika mereka datang ©" – makro!

Bekerja dengan filter lanjutan dapat sangat dipercepat dan disederhanakan menggunakan makro sederhana yang akan secara otomatis menjalankan filter lanjutan saat kondisi dimasukkan, yaitu mengubah sel kuning apa pun. Klik kanan pada tab lembar saat ini dan pilih perintah Sumber teks (Kode sumber). Di jendela yang terbuka, salin dan tempel kode berikut:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Tidak Ada Apa-apa Kemudian Pada Error Lanjutkan Next ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Range("A1").CurrentRegion End If End Sub  

Prosedur ini akan berjalan secara otomatis ketika sel apa pun pada lembar kerja saat ini diubah. Jika alamat sel yang diubah jatuh ke dalam rentang kuning (A2:I5), maka makro ini menghapus semua filter (jika ada) dan menerapkan kembali filter yang diperluas ke tabel data sumber yang dimulai dengan A7, yaitu semuanya akan difilter secara instan, segera setelah memasuki kondisi berikutnya:

Jadi semuanya jauh lebih baik, bukan?

Menerapkan kueri kompleks

Sekarang setelah semuanya difilter dengan cepat, kita bisa masuk lebih dalam ke nuansa dan membongkar mekanisme kueri yang lebih kompleks di filter lanjutan. Selain memasukkan kecocokan persis, Anda dapat menggunakan berbagai karakter wildcard (* dan ?) dan tanda ketidaksetaraan matematis dalam berbagai kondisi untuk menerapkan pencarian perkiraan. Kasus karakter tidak masalah. Untuk kejelasan, saya telah merangkum semua opsi yang mungkin dalam sebuah tabel:

Poin halus:

• Tanda * berarti sejumlah karakter apa pun, dan ? - salah satu karakter.
• Logika dalam memproses kueri teks dan numerik sedikit berbeda. Jadi, misalnya, sel kondisi dengan angka 5 tidak berarti mencari semua angka yang dimulai dengan lima, tetapi sel kondisi dengan huruf B sama dengan B*, yaitu akan mencari teks apa pun yang dimulai dengan huruf B.
• Jika kueri teks tidak dimulai dengan tanda =, maka Anda dapat secara mental meletakkan * di akhir.
• Tanggal harus dimasukkan dalam format AS bulan-hari-tahun dan melalui pecahan (meskipun Anda memiliki Excel dan pengaturan regional).

Kata penghubung logika AND-OR

Kondisi yang ditulis dalam sel yang berbeda, tetapi pada baris yang sama, dianggap saling berhubungan oleh operator logika И (DAN):

Itu. saring pisang untuk saya di kuarter ketiga, tepatnya di Moskow dan sekaligus dari Auchan.

Jika Anda perlu menghubungkan kondisi dengan operator logika OR (OR), maka mereka hanya perlu dimasukkan dalam baris yang berbeda. Misalnya, jika kita perlu menemukan semua pesanan manajer Volina untuk buah persik Moskow dan semua pesanan bawang pada kuartal ketiga di Samara, maka ini dapat ditentukan dalam berbagai kondisi sebagai berikut:

Jika Anda perlu menerapkan dua atau lebih kondisi pada satu kolom, maka Anda cukup menduplikasi tajuk kolom dalam rentang kriteria dan memasukkan yang kedua, ketiga, dst. di bawahnya. ketentuan. Jadi, misalnya, Anda dapat memilih semua transaksi dari Maret hingga Mei:

Secara umum, setelah "menyelesaikan dengan file", filter lanjutan menjadi alat yang cukup baik, di beberapa tempat tidak lebih buruk daripada filter otomatis klasik.

• Superfilter pada makro
• Apa itu makro, di mana dan bagaimana menyisipkan kode makro di Visual Basic
• Tabel pintar di Microsoft Excel