Filtrimi i kolonës horizontale në Excel

Nëse nuk jeni një përdorues mjaft fillestar, atëherë duhet ta keni vënë re tashmë se 99% e gjithçkaje në Excel është krijuar për të punuar me tabela vertikale, ku parametrat ose atributet (fushat) kalojnë nëpër kolona dhe gjenden informacione rreth objekteve ose ngjarjeve. në rreshta. Tabelat kryesore, nëntotalet, formulat e kopjimit me një klik të dyfishtë - gjithçka është përshtatur posaçërisht për këtë format të dhënash.

Sidoqoftë, nuk ka rregulla pa përjashtime dhe me një frekuencë mjaft të rregullt më pyesin se çfarë të bëj nëse në vepër haset një tabelë me orientim semantik horizontal, ose një tabelë ku rreshtat dhe kolonat kanë të njëjtën peshë në kuptim:

Dhe nëse Excel ende di të renditet horizontalisht (me komandën Të dhënat – Renditja – Opsionet – Renditni kolonat), atëherë situata me filtrimin është më e keqe - thjesht nuk ka mjete të integruara për filtrimin e kolonave, jo rreshtave në Excel. Pra, nëse përballeni me një detyrë të tillë, do t'ju duhet të gjeni zgjidhje me shkallë të ndryshme kompleksiteti.

Metoda 1. Funksioni i ri FILTER

Nëse jeni në versionin e ri të Excel 2021 ose një abonim në Excel 365, mund të përfitoni nga funksioni i sapo prezantuar Filtër (FILTER), i cili mund të filtrojë të dhënat burimore jo vetëm sipas rreshtave, por edhe sipas kolonave. Për të funksionuar, ky funksion kërkon një rresht ndihmës horizontal njëdimensional, ku çdo vlerë (TRUE ose FALSE) përcakton nëse do të shfaqim ose, anasjelltas, do ta fshehim kolonën tjetër në tabelë.

Le të shtojmë rreshtin e mëposhtëm mbi tabelën tonë dhe të shkruajmë statusin e secilës kolonë në të:

• Le të themi se gjithmonë duam të shfaqim kolonën e parë dhe të fundit (titujt dhe totalet), kështu që për to në qelizat e para dhe të fundit të grupit vendosim vlerën = TRUE.
• Për kolonat e mbetura, përmbajtja e qelizave përkatëse do të jetë një formulë që kontrollon gjendjen që na nevojitet duke përdorur funksionet И (DHE) or OR (OR). Për shembull, që totali është në rangun nga 300 në 500.

Pas kësaj, mbetet vetëm përdorimi i funksionit Filtër për të zgjedhur kolonat mbi të cilat grupi ynë ndihmës ka një vlerë të vërtetë:

Në mënyrë të ngjashme, ju mund të filtroni kolonat sipas një liste të caktuar. Në këtë rast, funksioni do të ndihmojë countif (COUNTIF), i cili kontrollon numrin e dukurive të emrit të kolonës tjetër nga kreu i tabelës në listën e lejuar:

Metoda 2. Pivot tabela në vend të asaj të zakonshmes

Aktualisht, Excel ka filtrim horizontal të integruar sipas kolonave vetëm në tabelat kryesore, kështu që nëse arrijmë ta shndërrojmë tabelën tonë origjinale në një tabelë kryesore, mund të përdorim këtë funksionalitet të integruar. Për ta bërë këtë, tabela jonë burimore duhet të plotësojë kushtet e mëposhtme:

• keni një linjë "korrekte" të kokës me një rresht pa qeliza boshe dhe të bashkuara - përndryshe nuk do të funksionojë për të ndërtuar një tabelë kryesore;
• mos përmbajnë dublikatë në etiketat e rreshtave dhe kolonave - ato do të "shemben" në përmbledhje në një listë vetëm me vlera unike;
• përmbajnë vetëm numra në rangun e vlerave (në kryqëzimin e rreshtave dhe kolonave), sepse tabela kryesore do të zbatojë patjetër një lloj funksioni grumbullues për to (shuma, mesatare, etj.) dhe kjo nuk do të funksionojë me tekstin

Nëse plotësohen të gjitha këto kushte, atëherë për të ndërtuar një tabelë strumbullare që duket si tabela jonë origjinale, ajo (ajo origjinale) do të duhet të zgjerohet nga kryqëzimi në një të sheshtë (normalizuar). Dhe mënyra më e lehtë për ta bërë këtë është me shtesën Power Query, një mjet i fuqishëm i transformimit të të dhënave të ndërtuar në Excel që nga viti 2016. 

Këto janë:

1. Le ta shndërrojmë tabelën në një komandë dinamike "të zgjuar". Faqja kryesore – Formatoni si tabelë (Shtëpia - Formatoni si tabelë).
2. Ngarkimi në Power Query me komandën Të dhënat - Nga tabela / diapazoni (Të dhënat - Nga tabela / diapazoni).
3. Ne e filtrojmë rreshtin me totalet (përmbledhja do të ketë totalet e veta).
4. Klikoni me të djathtën në titullin e kolonës së parë dhe zgjidhni Hiq palosjen e kolonave të tjera (Zhvendos kolonat e tjera). Të gjitha kolonat e pazgjedhura shndërrohen në dy - emrin e punonjësit dhe vlerën e treguesit të tij.
5. Filtrimi i kolonës me totalet që hynë në kolonë atribut.
6. Ne ndërtojmë një tabelë pivot sipas tabelës së sheshtë (normalizuar) që rezulton me komandën Faqja kryesore — Mbyll dhe ngarko — Mbylle dhe ngarko në… (Shtëpia - Mbyll & Ngarko - Mbylle & Ngarko për…).

Tani mund të përdorni aftësinë për të filtruar kolonat e disponueshme në tabelat kryesore - shenjat e zakonshme përpara emrave dhe artikujve Filtrat e nënshkrimit (Filtrat e etiketës) or Filtro sipas vlerës (Filtra të vlerave):

Dhe sigurisht, kur ndryshoni të dhënat, do t'ju duhet të përditësoni pyetjen tonë dhe përmbledhjen me një shkurtore të tastierës Ctrl+Ndalet+F5 ose ekipi Të dhënat - Rifresko të gjitha (Të dhënat - Rifresko të gjitha).

Metoda 3. Makro në VBA

Të gjitha metodat e mëparshme, siç mund ta shihni lehtësisht, nuk janë saktësisht filtruese - ne nuk i fshehim kolonat në listën origjinale, por formojmë një tabelë të re me një grup të caktuar kolonash nga ajo origjinale. Nëse kërkohet filtrimi (fshehja) e kolonave në të dhënat e burimit, atëherë nevojitet një qasje thelbësisht e ndryshme, domethënë, një makro.

Supozoni se duam të filtrojmë kolonat në fluturim ku emri i menaxherit në kokën e tabelës plotëson maskën e specifikuar në qelizën e verdhë A4, për shembull, fillon me shkronjën "A" (d.m.th., merrni "Anna" dhe "Arthur "si rezultat). 

Ashtu si në metodën e parë, ne fillimisht zbatojmë një varg-rresht ndihmës, ku në secilën qelizë kriteri ynë do të kontrollohet nga një formulë dhe vlerat logjike TRUE ose FALSE do të shfaqen për kolonat e dukshme dhe të fshehura, përkatësisht:

Pastaj le të shtojmë një makro të thjeshtë. Klikoni me të djathtën në skedën e fletës dhe zgjidhni komandën Burim (Kodi i burimit). Kopjoni dhe ngjisni kodin e mëposhtëm VBA në dritaren që hapet:

Nën-Private Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Pastaj për çdo qelizë në Range("D2:O2") Nëse qeliza = E vërtetë Pastaj cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = Fundi i vërtetë Nëse qeliza tjetër Fundi Nëse Fundi Nën  

Logjika e saj është si më poshtë:

• Në përgjithësi, ky është një mbajtës ngjarjesh Fletë pune_Ndrysho, dmth kjo makro do të funksionojë automatikisht në çdo ndryshim në çdo qelizë në fletën aktuale.
• Referenca për qelizën e ndryshuar do të jetë gjithmonë në ndryshore Objektiv.
• Së pari, ne kontrollojmë që përdoruesi ka ndryshuar saktësisht qelizën me kriterin (A4) - kjo bëhet nga operatori if.
• Pastaj fillon cikli Per secilin… për të përsëritur mbi qelizat gri (D2:O2) me vlerat e treguesit TRUE / FALSE për secilën kolonë.
• Nëse vlera e qelizës tjetër gri është TRUE (e vërtetë), atëherë kolona nuk fshihet, përndryshe ne e fshehim atë (vetia I fshehur).

•  Funksionet e grupit dinamik nga Office 365: FILTER, SORT dhe UNIC
• Pivot tabela me kokë me shumë rreshta duke përdorur Power Query
• Çfarë janë makro, si t'i krijoni dhe përdorni ato