Pahalang na pag-filter ng column sa Excel

Kung hindi ka pa isang baguhan na gumagamit, malamang na napansin mo na 99% ng lahat ng bagay sa Excel ay idinisenyo upang gumana sa mga patayong talahanayan, kung saan ang mga parameter o katangian (mga patlang) ay dumaan sa mga haligi, at ang impormasyon tungkol sa mga bagay o kaganapan ay matatagpuan. sa mga linya. Mga pivot table, subtotal, pagkopya ng mga formula na may double click – lahat ay partikular na iniakma para sa format ng data na ito.

Gayunpaman, walang mga patakaran nang walang mga pagbubukod at sa medyo regular na dalas ay tatanungin ako kung ano ang gagawin kung ang isang talahanayan na may pahalang na oryentasyong semantiko, o isang talahanayan kung saan ang mga hilera at mga haligi ay may parehong timbang sa kahulugan, ay dumating sa trabaho:

At kung alam pa rin ng Excel kung paano mag-uri-uriin nang pahalang (na may command Data - Pagbukud-bukurin - Mga Pagpipilian - Pagbukud-bukurin ang mga hanay), kung gayon ang sitwasyon sa pag-filter ay mas malala - walang mga built-in na tool para sa pag-filter ng mga haligi, hindi mga hilera sa Excel. Kaya, kung nahaharap ka sa ganoong gawain, kakailanganin mong makabuo ng mga workaround na may iba't ibang antas ng pagiging kumplikado.

Paraan 1. Bagong FILTER function

Kung ikaw ay nasa bagong bersyon ng Excel 2021 o isang Excel 365 na subscription, maaari mong samantalahin ang bagong ipinakilalang feature. FILTER (FILTER), na maaaring mag-filter ng source data hindi lamang ayon sa mga row, kundi pati na rin ng mga column. Upang gumana, nangangailangan ang function na ito ng auxiliary horizontal one-dimensional array-row, kung saan tinutukoy ng bawat value (TRUE o FALSE) kung ipapakita namin o, sa kabilang banda, itatago ang susunod na column sa talahanayan.

Idagdag natin ang sumusunod na linya sa itaas ng ating talahanayan at isulat ang katayuan ng bawat column dito:

• Sabihin nating gusto nating palaging ipakita ang una at huling mga column (mga header at kabuuan), kaya para sa kanila sa una at huling mga cell ng array itinakda namin ang value = TRUE.
• Para sa natitirang mga column, ang mga nilalaman ng kaukulang mga cell ay magiging isang formula na sumusuri sa kundisyong kailangan namin gamit ang mga function. И (AT) or OR (OR). Halimbawa, na ang kabuuan ay nasa hanay mula 300 hanggang 500.

Pagkatapos nito, nananatili lamang itong gamitin ang function FILTER upang pumili ng mga column sa itaas kung saan ang aming auxiliary array ay may TRUE value:

Katulad nito, maaari mong i-filter ang mga column ayon sa isang ibinigay na listahan. Sa kasong ito, makakatulong ang function COUNTIF (COUNTIF), na sumusuri sa bilang ng mga paglitaw ng susunod na pangalan ng column mula sa header ng talahanayan sa pinapayagang listahan:

Paraan 2. Pivot table sa halip na ang karaniwan

Sa kasalukuyan, ang Excel ay may built-in na pahalang na pag-filter sa pamamagitan ng mga column sa mga pivot table lamang, kaya kung namamahala kaming i-convert ang aming orihinal na talahanayan sa isang pivot table, magagamit namin ang built-in na functionality na ito. Para magawa ito, dapat matugunan ng aming source table ang mga sumusunod na kundisyon:

• magkaroon ng "tamang" one-line na linya ng header na walang laman at pinagsanib na mga cell - kung hindi, hindi ito gagana upang bumuo ng isang pivot table;
• huwag maglaman ng mga duplicate sa mga label ng mga row at column – sila ay "mag-collapse" sa buod sa isang listahan ng mga natatanging value lamang;
• naglalaman lamang ng mga numero sa hanay ng mga halaga (sa intersection ng mga row at column), dahil ang pivot table ay tiyak na maglalapat ng ilang uri ng aggregating function sa kanila (sum, average, atbp.) at hindi ito gagana sa text.

Kung natutugunan ang lahat ng kundisyong ito, para makabuo ng pivot table na kamukha ng aming orihinal na table, kakailanganin itong (ang orihinal) na palawakin mula sa crosstab patungo sa isang flat (na-normalize). At ang pinakamadaling paraan para gawin ito ay gamit ang Power Query add-in, isang mahusay na tool sa pagbabago ng data na binuo sa Excel mula noong 2016. 

Ang mga ito ay:

1. I-convert natin ang talahanayan sa isang "matalinong" dynamic na command Tahanan – I-format bilang isang talahanayan (Home — Format bilang Talahanayan).
2. Naglo-load sa Power Query gamit ang command Data – Mula sa Talahanayan / Saklaw (Data – Mula sa Talahanayan / Saklaw).
3. Sinasala namin ang linya gamit ang mga kabuuan (ang buod ay magkakaroon ng sarili nitong mga kabuuan).
4. Mag-right click sa unang column heading at piliin I-uncollapse ang iba pang column (I-unpivot ang Iba Pang Mga Column). Ang lahat ng hindi napiling mga column ay na-convert sa dalawa - ang pangalan ng empleyado at ang halaga ng kanyang indicator.
5. Pag-filter sa column gamit ang mga kabuuan na napunta sa column katangian.
6. Bumubuo kami ng pivot table ayon sa resultang flat (normalized) table na may command Tahanan — Isara at I-load — Isara at I-load sa… (Home — Isara at I-load — Isara at I-load sa…).

Magagamit mo na ngayon ang kakayahang mag-filter ng mga column na available sa mga pivot table – ang karaniwang mga checkmark sa harap ng mga pangalan at item Mga Filter ng Lagda (Mga Filter ng Label) or Mga filter ayon sa halaga (Mga Filter ng Halaga):

At siyempre, kapag binabago ang data, kakailanganin mong i-update ang aming query at ang buod gamit ang isang keyboard shortcut Ctrl+Alt+F5 o pangkat Data – I-refresh Lahat (Data — I-refresh Lahat).

Paraan 3. Macro sa VBA

Ang lahat ng mga naunang pamamaraan, tulad ng madali mong makita, ay hindi eksakto sa pag-filter - hindi namin itinago ang mga column sa orihinal na listahan, ngunit bumubuo ng isang bagong talahanayan na may ibinigay na hanay ng mga column mula sa orihinal. Kung kinakailangan na i-filter (itago) ang mga column sa pinagmulang data, kailangan ang isang pangunahing naiibang diskarte, ibig sabihin, isang macro.

Ipagpalagay na gusto naming i-filter ang mga column sa mabilisang kung saan ang pangalan ng manager sa header ng talahanayan ay nakakatugon sa mask na tinukoy sa dilaw na cell A4, halimbawa, ay nagsisimula sa titik na "A" (iyon ay, kunin ang "Anna" at "Arthur " ang resulta). 

Tulad ng sa unang paraan, una naming ipinatupad ang isang auxiliary range-row, kung saan sa bawat cell ang aming criterion ay susuriin ng isang formula at ang mga lohikal na halaga na TRUE o FALSE ay ipapakita para sa nakikita at nakatagong mga column, ayon sa pagkakabanggit:

Pagkatapos ay magdagdag tayo ng isang simpleng macro. Mag-right-click sa tab na sheet at piliin ang command pinagmulan (source code). Kopyahin at i-paste ang sumusunod na VBA code sa window na bubukas:

Pribadong Sub Worksheet_Change(ByVal Target Bilang Saklaw) Kung Target.Address = "$A$4" Pagkatapos Para sa Bawat cell Sa Saklaw("D2:O2") Kung cell = True Pagkatapos cell.EntireColumn.Hidden = False Iba pang cell.EntireColumn.Hidden = True End Kung Susunod na cell End Kung End Sub  

Ang lohika nito ay ang mga sumusunod:

• Sa pangkalahatan, ito ay isang event handler Worksheet_Change, ibig sabihin, awtomatikong tatakbo ang macro na ito sa anumang pagbabago sa anumang cell sa kasalukuyang sheet.
• Ang reference sa binagong cell ay palaging nasa variable Target.
• Una, sinusuri namin na ang user ay eksaktong binago ang cell na may criterion (A4) - ito ay ginagawa ng operator if.
• Pagkatapos ay magsisimula ang cycle Para sa bawat… upang umulit sa mga gray na cell (D2:O2) na may TRUE / FALSE indicator values ​​para sa bawat column.
• Kung ang halaga ng susunod na gray na cell ay TRUE (true), kung gayon ang column ay hindi nakatago, kung hindi, itatago namin ito (property Nakatago).

•  Dynamic na array function mula sa Office 365: FILTER, SORT, at UNIC
• Pivot table na may multiline na header gamit ang Power Query
• Ano ang mga macro, kung paano lumikha at gamitin ang mga ito