Penapisan lajur mendatar dalam Excel

Jika anda bukan pengguna baru, maka anda pasti sudah perasan bahawa 99% daripada segala-galanya dalam Excel direka bentuk untuk berfungsi dengan jadual menegak, di mana parameter atau atribut (medan) melalui lajur, dan maklumat tentang objek atau peristiwa terletak. dalam barisan. Jadual pangsi, subjumlah, menyalin formula dengan klik dua kali – semuanya disesuaikan khusus untuk format data ini.

Walau bagaimanapun, tiada peraturan tanpa pengecualian dan dengan kekerapan yang agak biasa saya ditanya apa yang perlu dilakukan jika jadual dengan orientasi semantik mendatar, atau jadual di mana baris dan lajur mempunyai berat yang sama dalam makna, terjumpa dalam kerja:

Dan jika Excel masih tahu cara mengisih secara mendatar (dengan perintah Data – Isih – Pilihan – Isih lajur), maka keadaan dengan penapisan adalah lebih teruk - tiada alat terbina dalam untuk menapis lajur, bukan baris dalam Excel. Jadi, jika anda berhadapan dengan tugas sedemikian, anda perlu membuat penyelesaian dengan pelbagai tahap kerumitan.

Kaedah 1. Fungsi FILTER baharu

Jika anda menggunakan versi baharu Excel 2021 atau langganan Excel 365, anda boleh memanfaatkan ciri yang baru diperkenalkan FILTER (PENAPIS), yang boleh menapis data sumber bukan sahaja mengikut baris, tetapi juga mengikut lajur. Untuk berfungsi, fungsi ini memerlukan baris tatasusunan satu dimensi mendatar tambahan, di mana setiap nilai (BENAR atau SALAH) menentukan sama ada kita menunjukkan atau, sebaliknya, menyembunyikan lajur seterusnya dalam jadual.

Mari tambahkan baris berikut di atas jadual kami dan tulis status setiap lajur di dalamnya:

• Katakan kita sentiasa mahu memaparkan lajur pertama dan terakhir (pengepala dan jumlah), jadi bagi mereka dalam sel pertama dan terakhir tatasusunan kita tetapkan nilai = TRUE.
• Untuk lajur yang tinggal, kandungan sel yang sepadan akan menjadi formula yang menyemak keadaan yang kita perlukan menggunakan fungsi И (DAN) or OR (OR). Sebagai contoh, jumlah itu berada dalam julat dari 300 hingga 500.

Selepas itu, ia kekal hanya untuk menggunakan fungsi itu FILTER untuk memilih lajur di atas yang tatasusunan tambahan kami mempunyai nilai TRUE:

Begitu juga, anda boleh menapis lajur mengikut senarai yang diberikan. Dalam kes ini, fungsi akan membantu COUNTIF (COUNTIF), yang menyemak bilangan kejadian nama lajur seterusnya daripada pengepala jadual dalam senarai yang dibenarkan:

Kaedah 2. Jadual pangsi bukannya jadual biasa

Pada masa ini, Excel mempunyai penapisan mendatar terbina dalam mengikut lajur sahaja dalam jadual pangsi, jadi jika kami berjaya menukar jadual asal kami kepada jadual pangsi, kami boleh menggunakan fungsi terbina dalam ini. Untuk melakukan ini, jadual sumber kami mesti memenuhi syarat berikut:

• mempunyai baris pengepala satu baris yang "betul" tanpa sel kosong dan bercantum - jika tidak, ia tidak akan berfungsi untuk membina jadual pangsi;
• tidak mengandungi pendua dalam label baris dan lajur – ia akan "runtuh" ​​dalam ringkasan ke dalam senarai nilai unik sahaja;
• mengandungi hanya nombor dalam julat nilai (di persimpangan baris dan lajur), kerana jadual pangsi pasti akan menggunakan beberapa jenis fungsi pengagregatan kepada mereka (jumlah, purata, dll.) dan ini tidak akan berfungsi dengan teks

Jika semua syarat ini dipenuhi, maka untuk membina jadual pangsi yang kelihatan seperti jadual asal kami, ia (yang asal) perlu dikembangkan daripada tab silang kepada jadual rata (dinormalkan). Dan cara paling mudah untuk melakukan ini ialah dengan tambahan Power Query, alat transformasi data yang berkuasa terbina dalam Excel sejak 2016. 

Ini adalah:

1. Mari tukar jadual menjadi arahan dinamik "pintar". Laman Utama – Format sebagai jadual (Laman Utama — Format sebagai Jadual).
2. Memuatkan ke Power Query dengan arahan Data – Daripada Jadual / Julat (Data – Daripada Jadual / Julat).
3. Kami menapis baris dengan jumlah (ringkasan akan mempunyai jumlahnya sendiri).
4. Klik kanan pada tajuk lajur pertama dan pilih Nyahruntuhkan lajur lain (Nyahpaut Lajur Lain). Semua lajur yang tidak dipilih ditukar kepada dua - nama pekerja dan nilai penunjuknya.
5. Menapis lajur dengan jumlah yang masuk ke dalam lajur atribut.
6. Kami membina jadual pangsi mengikut jadual rata (normal) yang dihasilkan dengan arahan Laman Utama — Tutup dan Muatkan — Tutup dan Muatkan dalam… (Laman Utama — Tutup & Muatkan — Tutup & Muatkan ke…).

Kini anda boleh menggunakan keupayaan untuk menapis lajur yang tersedia dalam jadual pangsi – tanda semak biasa di hadapan nama dan item Penapis Tandatangan (Penapis Label) or Tapis mengikut nilai (Penapis Nilai):

Dan sudah tentu, apabila menukar data, anda perlu mengemas kini pertanyaan kami dan ringkasan dengan pintasan papan kekunci Ctrl+Alt+F5 atau pasukan Data – Muat semula Semua (Data — Muat semula Semua).

Kaedah 3. Makro dalam VBA

Semua kaedah sebelumnya, seperti yang anda boleh lihat dengan mudah, tidak menapis secara tepat – kami tidak menyembunyikan lajur dalam senarai asal, tetapi membentuk jadual baharu dengan set lajur yang diberikan daripada yang asal. Jika diperlukan untuk menapis (menyembunyikan) lajur dalam data sumber, maka pendekatan asas yang berbeza diperlukan, iaitu makro.

Katakan kita ingin menapis lajur dengan cepat di mana nama pengurus dalam pengepala jadual memenuhi topeng yang dinyatakan dalam sel kuning A4, sebagai contoh, bermula dengan huruf "A" (iaitu, dapatkan "Anna" dan "Arthur " Akibatnya). 

Seperti dalam kaedah pertama, kami mula-mula melaksanakan baris julat tambahan, di mana dalam setiap sel kriteria kami akan disemak oleh formula dan nilai logik TRUE atau FALSE akan dipaparkan untuk lajur yang kelihatan dan tersembunyi, masing-masing:

Kemudian mari tambah makro yang mudah. Klik kanan pada tab helaian dan pilih arahan Source (Kod sumber). Salin dan tampal kod VBA berikut ke dalam tetingkap yang terbuka:

Sub Helaian Kerja Peribadi_Tukar(ByVal Sasaran Sebagai Julat) Jika Sasaran.Alamat = "$A$4" Kemudian Untuk Setiap sel Dalam Julat("D2:O2") Jika sel = Benar Kemudian sel.EntireColumn.Hidden = Palsu Sel lain.EntireColumn.Hidden = Tamat Benar Jika Sel Seterusnya Tamat Jika Tamat Sub  

Logiknya adalah seperti berikut:

• Secara umum, ini adalah pengendali acara Lembaran Kerja_Perubahan, iaitu makro ini akan berjalan secara automatik pada sebarang perubahan pada mana-mana sel pada helaian semasa.
• Rujukan kepada sel yang diubah akan sentiasa berada dalam pembolehubah Sasaran.
• Mula-mula, kami menyemak sama ada pengguna telah menukar betul-betul sel dengan kriteria (A4) - ini dilakukan oleh pengendali if.
• Kemudian kitaran bermula Untuk setiap… untuk lelaran ke atas sel kelabu (D2:O2) dengan nilai penunjuk BENAR / SALAH untuk setiap lajur.
• Jika nilai sel kelabu seterusnya adalah BENAR (benar), maka lajur tidak tersembunyi, jika tidak, kami menyembunyikannya (harta Tersembunyi).

•  Fungsi tatasusunan dinamik daripada Office 365: FILTER, SORT dan UNIC
• Jadual pangsi dengan pengepala berbilang baris menggunakan Power Query
• Apakah makro, cara mencipta dan menggunakannya