Fejlett szűrő és némi varázslat

Az Excel-felhasználók túlnyomó többsége számára, ha az „adatszűrés” szó jut a fejébe, csak a szokásos klasszikus szűrő jelenik meg a lapról. Adatok – Szűrő (Adatok – Szűrő):

Egy ilyen szűrő kétségtelenül ismerős dolog, és a legtöbb esetben megteszi. Vannak azonban olyan helyzetek, amikor nagyszámú összetett feltétel alapján kell szűrni egyszerre több oszlopban. A szokásos szűrő itt nem túl kényelmes, és valami erősebbet szeretnék. Ilyen eszköz lehet speciális szűrő, főleg egy kis „reszelővel befejezve” (a hagyományoknak megfelelően).

Bázis

A kezdéshez illesszen be néhány üres sort az adattáblázat fölé, és másolja oda a táblázat fejlécét – ez egy feltételekkel rendelkező tartomány lesz (az egyértelműség kedvéért sárgával kiemelve):

A sárga cellák és az eredeti táblázat között legalább egy üres sornak kell lennie.

A sárga cellákba kell beírni azokat a kritériumokat (feltételeket), amelyek szerint a szűrés ezután megtörténik. Például, ha banánt kell kiválasztania a moszkvai „Auchan”-ban a III. negyedévben, akkor a feltételek így fognak kinézni:

A szűréshez válassza ki a forrásadatokkal rendelkező tartomány bármely celláját, nyissa meg a lapot dátum És kattintson a gombra Ezen kívül (Adatok – Speciális). A megnyíló ablakban már automatikusan be kell írni egy adattartományt, és már csak a feltételtartományt kell megadnunk, azaz A1:I2:

Kérjük, vegye figyelembe, hogy a feltételtartomány nem osztható ki „margóval”, azaz nem választhat extra üres sárga sorokat, mert a feltételtartományban lévő üres cellát az Excel kritérium hiányának, egy egész üresnek érzékeli. sort az összes adat válogatás nélküli megjelenítésére.

kapcsoló Másolja az eredményt egy másik helyre lehetővé teszi a lista szűrését nem közvetlenül ezen a lapon (mint egy normál szűrőnél), hanem a kiválasztott sorok kitöltését egy másik tartományba, amelyet ezután meg kell adni a mezőben Helyezze az eredményt tartományba. Ebben az esetben nem használjuk ezt a funkciót, kilépünk A szűrőlista a helyén van és kattintson OK. A kiválasztott sorok megjelennek a lapon:

Makró hozzáadása

– Nos, hol itt a kényelem? kérdezel és igazad lesz. Nem csak a feltételeket kell kézzel beírni a sárga cellákba, hanem meg kell nyitni egy párbeszédpanelt, megadni a tartományokat, majd megnyomni OK. Szomorú, egyetértek! De „minden megváltozik, ha jön ©” – makrók!

A speciális szűrővel végzett munka nagymértékben felgyorsítható és leegyszerűsíthető egy egyszerű makró használatával, amely automatikusan futtatja a speciális szűrőt a feltételek megadásakor, azaz bármely sárga cella megváltoztatásakor. Kattintson a jobb gombbal az aktuális lap fülére, és válassza ki a parancsot Forrás szöveg (Forráskód). A megnyíló ablakban másolja ki és illessze be a következő kódot:

Private Sub Worksheet_Change(ByVal Target As Range) Ha nem metszik(Cél, Tartomány("A2:I5")) Semmi, akkor hiba esetén Folytatás a következő ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=RlaceFilterInria :=Tartomány("A1").CurrentRegion End If End Sub  

Ez az eljárás automatikusan lefut, ha az aktuális munkalap bármely cellája megváltozik. Ha a megváltozott cella címe a sárga tartományba esik (A2:I5), akkor ez a makró eltávolítja az összes szűrőt (ha van ilyen), és újra alkalmazza a kiterjesztett szűrőt az A7-tel kezdődő forrásadattáblázatra, azaz minden azonnal, azonnal szűrésre kerül. a következő feltétel megadása után:

Szóval minden sokkal jobb, igaz? 🙂

Összetett lekérdezések megvalósítása

Most, hogy mindent menet közben szűrünk, kicsit mélyebben belemegyünk az árnyalatokba, és szétszedhetjük a bonyolultabb lekérdezések mechanizmusait a speciális szűrőben. A pontos egyezések megadása mellett különféle helyettesítő karaktereket (* és ?) és matematikai egyenlőtlenségjeleket is használhat a hozzávetőleges keresés végrehajtásához. A karakter kis- és nagybetűje nem számít. Az egyértelműség kedvéért táblázatban foglaltam össze az összes lehetséges lehetőséget:

Finom pontok:

• A * jel tetszőleges számú karaktert jelent, és a ? – bármelyik karakter.
• A szöveges és numerikus lekérdezések feldolgozásának logikája kissé eltér. Tehát például egy 5-ös számú feltételcella nem azt jelenti, hogy minden öttel kezdődő számot meg kell keresni, hanem a B betűvel rendelkező feltételcella egyenlő B*-val, azaz minden B betűvel kezdődő szöveget megkeres.
• Ha a szöveges lekérdezés nem = jellel kezdődik, akkor gondolatban a * végére tehető.
• A dátumokat amerikai formátumban kell megadni hónap-nap-év törtrészen keresztül (még akkor is, ha rendelkezik Excel és regionális beállításokkal).

Logikai összeköttetések ÉS-VAGY

A különböző cellákban, de ugyanabban a sorban írt feltételeket egy logikai operátor összekapcsoltnak tekinti И (ÉS):

Azok. banánszűrő nekem a harmadik negyedévben, pontosan Moszkvában és egyben az Auchanból.

Ha a feltételeket logikai operátorral kell összekapcsolnia OR (OR), akkor csak különböző sorokba kell beírni őket. Például, ha meg kell találnunk Volina menedzser összes moszkvai őszibarack- és összes hagymarendelését a harmadik negyedévben Szamarában, akkor ez a következő feltételekkel megadható:

Ha két vagy több feltételt kell szabnia egy oszlophoz, akkor egyszerűen megismételheti az oszlop fejlécét a feltételtartományban, és beírhatja alá a második, harmadik stb. feltételeket. Így például kiválaszthatja az összes tranzakciót márciustól májusig:

Általánosságban elmondható, hogy a „fájllal végzett befejezés” után egy fejlett szűrő egészen tisztességes eszköznek bizonyul, néhány helyen nem rosszabb, mint egy klasszikus automatikus szűrő.

• Szuperszűrő makróknál
• Mik azok a makrók, hol és hogyan kell beszúrni a makrókódot a Visual Basicben
• Intelligens táblázatok a Microsoft Excelben