Ընդլայնված զտիչ և որոշ կախարդանք

Excel-ի օգտատերերի ճնշող մեծամասնության համար, երբ իրենց գլխում հայտնվում է «տվյալների զտում» բառը, ներդիրից միայն սովորական դասական զտիչը Տվյալներ - զտիչ (Տվյալներ — Զտիչ):

Նման ֆիլտրը ծանոթ բան է, անկասկած, և շատ դեպքերում դա տեղի կունենա: Այնուամենայնիվ, կան իրավիճակներ, երբ դուք պետք է զտեք մեծ թվով բարդ պայմաններով միանգամից մի քանի սյունակներում: Այստեղ սովորական ֆիլտրը այնքան էլ հարմար չէ, և ես ավելի հզոր բան եմ ուզում: Նման գործիք կարող է լինել առաջադեմ զտիչ, հատկապես մի փոքր «ֆայլով ավարտելով» (ըստ ավանդույթի):

Հիմք

Սկսելու համար տեղադրեք մի քանի դատարկ տող ձեր տվյալների աղյուսակի վերևում և պատճենեք աղյուսակի վերնագիրն այնտեղ. սա կլինի պայմաններով ընդգրկույթ (հստակության համար ընդգծված դեղինով).

Դեղին բջիջների և սկզբնական աղյուսակի միջև պետք է լինի առնվազն մեկ դատարկ տող:

Հենց դեղին բջիջներում անհրաժեշտ է մուտքագրել այն չափանիշները (պայմանները), որոնց համաձայն այնուհետև կկատարվի զտումը: Օրինակ, եթե III եռամսյակում ձեզ հարկավոր է բանան ընտրել Մոսկվայի «Աուչանում», ապա պայմանները նման կլինեն.

Զտելու համար ընտրեք տիրույթի ցանկացած բջիջ աղբյուրի տվյալներով, բացեք ներդիրը Ամսաթիվ Եւ սեղմեք այն Լրացուցիչ (Տվյալներ — Ընդլայնված). Բացվող պատուհանում արդեն ավտոմատ կերպով պետք է մուտքագրվի տվյալների տիրույթ, և մենք պետք է միայն նշենք պայմանների շրջանակը, այսինքն՝ A1:I2:

Խնդրում ենք նկատի ունենալ, որ պայմանների տիրույթը չի կարող հատկացվել «մարգինալով», այսինքն՝ դուք չեք կարող ընտրել լրացուցիչ դատարկ դեղին գծեր, քանի որ պայմանների միջակայքում դատարկ բջիջը Excel-ի կողմից ընկալվում է որպես չափանիշի բացակայություն, իսկ մի ամբողջ դատարկ: տող՝ որպես բոլոր տվյալները անխտիր ցուցադրելու խնդրանք:

անջատիչ Արդյունքը պատճենեք մեկ այլ վայրում թույլ կտա ձեզ զտել ցուցակը ոչ թե հենց այս թերթի վրա (ինչպես սովորական զտիչով), այլ բեռնաթափել ընտրված տողերը մեկ այլ տիրույթում, որն այնուհետև պետք է նշվի դաշտում։ Արդյունքը դրեք միջակայքում. Այս դեպքում մենք չենք օգտագործում այս գործառույթը, մենք հեռանում ենք Զտել ցուցակը տեղում եւ սեղմեք OK. Ընտրված տողերը կցուցադրվեն թերթի վրա.

Մակրո ավելացում

«Դե, որտե՞ղ է այստեղ հարմարությունը»: կխնդրես ու ճիշտ կլինես։ Ձեզ անհրաժեշտ է ոչ միայն ձեռքերով մուտքագրել պայմաններ դեղին բջիջների մեջ, այլև բացել երկխոսության տուփը, մուտքագրել միջակայքերը այնտեղ, սեղմել OK. Տխուր, համաձայն եմ։ Բայց «ամեն ինչ փոխվում է, երբ նրանք գալիս են ©» – մակրոներ:

Ընդլայնված ֆիլտրի հետ աշխատանքը կարող է զգալիորեն արագացվել և պարզեցնել՝ օգտագործելով պարզ մակրո, որն ավտոմատ կերպով կաշխատի առաջադեմ զտիչը, երբ պայմանները մուտքագրվեն, այսինքն՝ փոխելով ցանկացած դեղին բջիջ: Աջ սեղմեք ընթացիկ թերթի ներդիրի վրա և ընտրեք հրամանը Աղբյուրի տեքստը (Աղբյուրի կոդը). Բացվող պատուհանում պատճենեք և տեղադրեք հետևյալ կոդը.

Մասնավոր ենթաթերթ_Փոփոխություն (ByVal թիրախը որպես միջակայք) Եթե չի հատվում (Թիրախ, տիրույթ ("A2:I5")) Ոչինչ չէ, ապա սխալ է: Վերսկսել հաջորդ ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=PaceF. :=Range("A1").CurrentRegion End If End Sub  

Այս ընթացակարգը ավտոմատ կերպով կաշխատի, երբ ընթացիկ աշխատաթերթի որևէ բջիջ փոխվի: Եթե ​​փոխված բջիջի հասցեն ընկնում է դեղին տիրույթում (A2:I5), ապա այս մակրոն հեռացնում է բոլոր զտիչները (եթե այդպիսիք կան) և նորից կիրառում է ընդլայնված ֆիլտրը աղբյուրի տվյալների աղյուսակում՝ սկսած A7-ով, այսինքն՝ ամեն ինչ կզտվի անմիջապես, անմիջապես։ հաջորդ պայմանը մուտքագրելուց հետո.

Այսպիսով, ամեն ինչ շատ ավելի լավ է, չէ՞: 🙂

Բարդ հարցումների իրականացում

Այժմ, երբ ամեն ինչ զտվում է, մենք կարող ենք մի փոքր խորանալ նրբությունների մեջ և ապամոնտաժել ավելի բարդ հարցումների մեխանիզմները առաջադեմ ֆիլտրում: Բացի ճշգրիտ համընկնումներ մուտքագրելուց, մոտավոր որոնում իրականացնելու համար դուք կարող եք օգտագործել տարբեր նիշերի (* և ?) և մաթեմատիկական անհավասարության նշաններ մի շարք պայմաններում: Բնավորության գործը նշանակություն չունի։ Պարզության համար ես ամփոփել եմ բոլոր հնարավոր տարբերակները աղյուսակում.

Նուրբ կետեր.

• * նշանը նշանակում է ցանկացած նիշերի ցանկացած թիվ, և ? - ցանկացած կերպար:
• Տեքստի և թվային հարցումների մշակման տրամաբանությունը մի փոքր այլ է: Այսպիսով, օրինակ, պայմանական բջիջը 5 թվով չի նշանակում որոնել հինգով սկսվող բոլոր թվերը, բայց B տառով պայմանական բջիջը հավասար է B*-ի, այսինքն կփնտրի B տառով սկսվող ցանկացած տեքստ:
• Եթե ​​տեքստային հարցումը չի սկսվում = նշանով, ապա մտովի կարող եք վերջում տեղադրել *:
• Ամսաթվերը պետք է մուտքագրվեն ԱՄՆ ձևաչափով ամիս-օր-տարի և կոտորակի միջոցով (նույնիսկ եթե ունեք Excel և տարածաշրջանային կարգավորումներ):

Տրամաբանական միացումներ AND-OR

Տարբեր բջիջներում, բայց նույն տողում գրված պայմանները համարվում են փոխկապակցված տրամաբանական օպերատորի կողմից И (ԵՎ):

Նրանք. զտեք բանան ինձ համար երրորդ եռամսյակում, հենց Մոսկվայում և միևնույն ժամանակ Աուչանից:

Եթե ​​Ձեզ անհրաժեշտ է պայմանները կապել տրամաբանական օպերատորի հետ OR (ԿԱՄ), ապա դրանք պարզապես պետք է մուտքագրել տարբեր տողերով։ Օրինակ, եթե մենք պետք է գտնենք մենեջեր Վոլինայի բոլոր պատվերները մոսկովյան դեղձի համար և սոխի բոլոր պատվերները երրորդ եռամսյակում Սամարայում, ապա դա կարելի է նշել մի շարք պայմաններով հետևյալ կերպ.

Եթե ​​Ձեզ անհրաժեշտ է մեկ սյունակի վրա դնել երկու կամ ավելի պայմաններ, ապա կարող եք պարզապես կրկնօրինակել սյունակի վերնագիրը չափանիշների տիրույթում և դրա տակ մուտքագրել երկրորդ, երրորդ և այլն: պայմանները. Այսպիսով, օրինակ, դուք կարող եք ընտրել բոլոր գործարքները մարտից մինչև մայիս.

Ընդհանուր առմամբ, «ֆայլով ավարտելուց» հետո առաջադեմ զտիչը բավականին պարկեշտ գործիք է դառնում, որոշ տեղերում ոչ ավելի վատ, քան դասական ավտոմատ զտիչը:

• Սուպերֆիլտր մակրոների վրա
• Ինչ են մակրոները, որտեղ և ինչպես տեղադրել մակրո կոդը Visual Basic-ում
• Խելացի աղյուսակներ Microsoft Excel-ում