Filtër i avancuar dhe disa magji

Për shumicën dërrmuese të përdoruesve të Excel, kur fjala "filtri i të dhënave" shfaqet në kokën e tyre, vetëm filtri i zakonshëm klasik nga skeda Të dhënat - Filtri (Të dhënat - Filtër):

Një filtër i tillë është një gjë e njohur, pa dyshim, dhe në shumicën e rasteve do të ndodhë. Sidoqoftë, ka situata kur duhet të filtroni nga një numër i madh kushtesh komplekse në disa kolona menjëherë. Filtri i zakonshëm këtu nuk është shumë i përshtatshëm dhe dua diçka më të fuqishme. Një mjet i tillë mund të jetë filtër i avancuar, sidomos me pak “përfundim me dosje” (sipas traditës).

Bazë

Për të filluar, futni disa rreshta bosh mbi tabelën tuaj të të dhënave dhe kopjoni kokën e tabelës atje - kjo do të jetë një varg me kushte (të theksuara me të verdhë për qartësi):

Duhet të ketë të paktën një rresht bosh midis qelizave të verdha dhe tabelës origjinale.

Pikërisht në qelizat e verdha duhet të futni kriteret (kushtet), sipas të cilave më pas do të kryhet filtrimi. Për shembull, nëse duhet të zgjidhni banane në "Auchan" të Moskës në tremujorin e III, atëherë kushtet do të duken kështu:

Për të filtruar, zgjidhni çdo qelizë në intervalin me të dhënat e burimit, hapni skedën Data Dhe klikoni Përveç kësaj (Të dhënat - të avancuara). Në dritaren që hapet, një interval me të dhëna tashmë duhet të futet automatikisht dhe do të na duhet vetëm të specifikojmë gamën e kushteve, p.sh. A1:I2:

Ju lutemi vini re se diapazoni i kushteve nuk mund të ndahet "me një diferencë", d.m.th. nuk mund të zgjidhni vija shtesë të verdha boshe, sepse një qelizë boshe në intervalin e kushteve perceptohet nga Excel si mungesë e një kriteri dhe një tërësi bosh linjë si një kërkesë për të shfaqur të gjitha të dhënat pa dallim.

ndryshim Kopjojeni rezultatin në një vend tjetër do t'ju lejojë të filtroni listën jo aty në këtë fletë (si me një filtër të rregullt), por të shkarkoni rreshtat e zgjedhur në një gamë tjetër, e cila më pas do të duhet të specifikohet në fushë Vendosni rezultatin në interval. Në këtë rast, ne nuk e përdorim këtë funksion, ne largohemi Lista e filtrave në vend dhe klikoni OK. Rreshtat e zgjedhur do të shfaqen në fletë:

Shtimi i një makro

"Epo, ku është komoditeti këtu?" ju pyesni dhe do të keni të drejtë. Jo vetëm që duhet të futni kushtet në qelizat e verdha me duart tuaja, por gjithashtu të hapni një kuti dialogu, të futni vargjet atje, të shtypni OK. E trishtuar, jam dakord! Por "gjithçka ndryshon kur ato vijnë ©" - makro!

Puna me një filtër të avancuar mund të përshpejtohet dhe thjeshtohet shumë duke përdorur një makro të thjeshtë që do të ekzekutojë automatikisht filtrin e avancuar kur të futen kushtet, dmth. duke ndryshuar çdo qelizë të verdhë. Klikoni me të djathtën në skedën e fletës aktuale dhe zgjidhni komandën Teksti burimor (Kodi i burimit). Në dritaren që hapet, kopjoni dhe ngjisni kodin e mëposhtëm:

Nën-Fleta e Punës Private_Change(ByVal Target As Range) Nëse nuk ndërpritet(Target, Range("A2:I5")) Nuk është asgjë, atëherë në gabim. :=Range("A7").Rajoni aktual Fundi If End Sub  

Kjo procedurë do të ekzekutohet automatikisht kur çdo qelizë në fletën aktuale të punës ndryshohet. Nëse adresa e qelizës së ndryshuar bie në intervalin e verdhë (A2:I5), atëherë kjo makro heq të gjithë filtrat (nëse ka) dhe riaplikon filtrin e zgjeruar në tabelën e të dhënave burimore duke filluar me A7, dmth gjithçka do të filtrohet në çast, menjëherë. pasi të keni futur kushtin vijues:

Pra, gjithçka është shumë më mirë, apo jo? 🙂

Zbatimi i pyetjeve komplekse

Tani që gjithçka po filtrohet në fluturim, ne mund të futemi pak më thellë në nuancat dhe të çmontojmë mekanizmat e pyetjeve më komplekse në filtrin e avancuar. Përveç futjes së përputhjeve të sakta, mund të përdorni karaktere të ndryshme të karaktereve të egër (* dhe ?) dhe shenja matematikore të pabarazisë në një sërë kushtesh për të zbatuar një kërkim të përafërt. Rasti i personazhit nuk ka rëndësi. Për qartësi, unë kam përmbledhur të gjitha opsionet e mundshme në një tabelë:

Pika delikate:

• Shenja * nënkupton çdo numër të ndonjë karakteri, dhe ? - çdo personazh.
• Logjika në përpunimin e tekstit dhe pyetjeve numerike është paksa e ndryshme. Kështu, për shembull, një qelizë kushti me numrin 5 nuk do të thotë të kërkosh të gjithë numrat që fillojnë me pesë, por një qelizë kushti me shkronjën B është e barabartë me B*, dmth do të kërkojë çdo tekst që fillon me shkronjën B.
• Nëse pyetja e tekstit nuk fillon me shenjën =, atëherë mendërisht mund të vendosni * në fund.
• Datat duhet të futen në formatin amerikan muaj-ditë-vit dhe përmes një fraksioni (edhe nëse keni Excel dhe cilësime rajonale).

Lidhjet logjike DHE-OR

Kushtet e shkruara në qeliza të ndryshme, por në të njëjtën linjë, konsiderohen të ndërlidhura nga një operator logjik И (DHE):

Ato. filtro bananet për mua në tremujorin e tretë, pikërisht në Moskë dhe në të njëjtën kohë nga Auchan.

Nëse keni nevojë të lidhni kushtet me një operator logjik OR (OR), atëherë ato thjesht duhet të futen në rreshta të ndryshëm. Për shembull, nëse duhet të gjejmë të gjitha porositë e menaxherit Volina për pjeshkët e Moskës dhe të gjitha porositë për qepë në tremujorin e tretë në Samara, atëherë kjo mund të specifikohet në një sërë kushtesh si më poshtë:

Nëse keni nevojë të vendosni dy ose më shumë kushte në një kolonë, atëherë thjesht mund të kopjoni kokën e kolonës në intervalin e kritereve dhe të futni të dytën, të tretën, etj. nën të. kushtet. Kështu, për shembull, ju mund të zgjidhni të gjitha transaksionet nga marsi deri në maj:

Në përgjithësi, pas "përfundimit me një skedar", një filtër i avancuar del të jetë një mjet mjaft i mirë, në disa vende jo më keq se një autofiltër klasik.

• Superfiltri në makro
• Çfarë janë makro, ku dhe si të futni kodin makro në Visual Basic
• Tabelat inteligjente në Microsoft Excel