Հորիզոնական սյունակի զտում Excel-ում

Եթե ​​դուք այնքան էլ սկսնակ օգտատեր չեք, ապա պետք է արդեն նկատել, որ Excel-ում ամեն ինչի 99%-ը նախատեսված է ուղղահայաց աղյուսակների հետ աշխատելու համար, որտեղ պարամետրերը կամ ատրիբուտները (դաշտերը) անցնում են սյունակների միջով, և գտնվում են օբյեկտների կամ իրադարձությունների մասին տեղեկությունները: տողերում. Առանցքային աղյուսակներ, ենթագումարներ, կրկնակի սեղմումով բանաձևերի պատճենում – ամեն ինչ հատուկ ստեղծված է տվյալների այս ձևաչափի համար:

Այնուամենայնիվ, չկան կանոններ առանց բացառությունների և բավականին կանոնավոր հաճախականությամբ ինձ հարցնում են, թե ինչ անել, եթե աշխատության մեջ հանդիպի հորիզոնական իմաստային ուղղվածությամբ աղյուսակ կամ աղյուսակ, որտեղ տողերն ու սյունակները ունեն նույն նշանակությունը.

Եվ եթե Excel-ը դեռ գիտի, թե ինչպես կարելի է դասավորել հորիզոնական (հրամանով Տվյալներ – Տեսակավորել – Ընտրանքներ – Տեսակավորել սյունակները), ապա զտման հետ կապված իրավիճակը ավելի վատ է. Excel-ում պարզապես չկան ներկառուցված գործիքներ սյունակները զտելու համար, ոչ տողեր: Այսպիսով, եթե ձեզ նման խնդիր է դրված, դուք ստիպված կլինեք տարբեր աստիճանի բարդության լուծումներ գտնել:

Մեթոդ 1. Նոր FILTER ֆունկցիա

Եթե ​​դուք օգտագործում եք Excel 2021-ի նոր տարբերակը կամ Excel 365-ի բաժանորդագրությունը, կարող եք օգտվել նոր ներդրված հնարավորությունից: Ֆիլտր (ԶԻՏՐ), որը կարող է զտել աղբյուրի տվյալները ոչ միայն ըստ տողերի, այլև սյունակների։ Այս ֆունկցիան աշխատելու համար պահանջում է օժանդակ հորիզոնական միաչափ զանգված-տող, որտեղ յուրաքանչյուր արժեքը (TRUE կամ FALSE) որոշում է, թե արդյոք մենք ցույց կտանք, թե հակառակը, թաքցնենք հաջորդ սյունակը աղյուսակում:

Եկեք մեր աղյուսակի վերևում ավելացնենք հետևյալ տողը և դրանում գրենք յուրաքանչյուր սյունակի կարգավիճակը.

• Ենթադրենք, որ մենք միշտ ցանկանում ենք ցուցադրել առաջին և վերջին սյունակները (վերնագրերը և գումարները), ուստի դրանց համար զանգվածի առաջին և վերջին բջիջներում մենք սահմանել ենք արժեքը = TRUE:
• Մնացած սյունակների համար համապատասխան բջիջների բովանդակությունը կլինի բանաձև, որը ստուգում է մեզ անհրաժեշտ վիճակը՝ օգտագործելով ֆունկցիաները: И (ԵՎ) or OR (ԿԱՄ). Օրինակ, որ ընդհանուրը գտնվում է 300-ից 500-ի սահմաններում:

Դրանից հետո մնում է միայն օգտագործել գործառույթը Ֆիլտր ընտրել սյունակներ, որոնց վերևում մեր օժանդակ զանգվածն ունի TRUE արժեք.

Նմանապես, դուք կարող եք զտել սյունակները ըստ տվյալ ցանկի: Այս դեպքում գործառույթը կօգնի COUNTIF (COUNTIF), որը ստուգում է թույլատրված ցանկի աղյուսակի վերնագրից հաջորդ սյունակի անվան առաջացման քանակը.

Մեթոդ 2. Առանցքային աղյուսակը սովորականի փոխարեն

Ներկայումս Excel-ն ունի ներկառուցված հորիզոնական զտում ըստ սյունակների միայն առանցքային աղյուսակներում, այնպես որ, եթե մեզ հաջողվի մեր սկզբնական աղյուսակը վերածել առանցքային աղյուսակի, մենք կարող ենք օգտագործել այս ներկառուցված գործառույթը: Դա անելու համար մեր աղբյուրի աղյուսակը պետք է բավարարի հետևյալ պայմանները.

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

Եթե ​​այս բոլոր պայմանները բավարարվեն, ապա մեր սկզբնական աղյուսակին նմանվող առանցքային աղյուսակ կառուցելու համար այն (բնօրինակը) պետք է ընդլայնել խաչմերուկից հարթ (նորմալացված): Եվ դա անելու ամենահեշտ ձևը Power Query հավելումն է, որը տվյալների փոխակերպման հզոր գործիք է, որը ներկառուցված է Excel-ում 2016 թվականից: 

Դրանք են `

1. Եկեք աղյուսակը վերածենք «խելացի» դինամիկ հրամանի Գլխավոր – Ձևաչափել որպես աղյուսակ (Տուն — Ձևաչափել որպես աղյուսակ).
2. Բեռնվում է Power Query-ում հրամանով Տվյալներ – աղյուսակից / միջակայք (Տվյալներ – աղյուսակից / միջակայք).
3. Մենք զտում ենք գիծը հանրագումարներով (ամփոփագիրը կունենա իր տոտալները):
4. Աջ սեղմեք առաջին սյունակի վերնագրի վրա և ընտրեք Անջատեք մյուս սյունակները (Անջատել այլ սյունակներ). Բոլոր չընտրված սյունակները վերածվում են երկուսի՝ աշխատողի անունը և նրա ցուցիչի արժեքը:
5. Սյունակի զտում սյունակում ներառված գումարներով Վերագրել.
6. Հրամանով կառուցում ենք առանցքային աղյուսակ՝ ըստ ստացված հարթ (նորմալացված) աղյուսակի Գլխավոր — Փակել և բեռնել — Փակել և բեռնել… (Տուն — Փակել և բեռնել — Փակել և բեռնել…).

Այժմ դուք կարող եք օգտագործել առանցքային աղյուսակներում առկա սյունակները զտելու հնարավորությունը՝ սովորական նշագրերը անունների և տարրերի դիմաց: Ստորագրության զտիչներ (Պիտակի զտիչներ) or Զտիչներ ըստ արժեքի (Արժեքի զտիչներ):

Եվ, իհարկե, տվյալները փոխելիս անհրաժեշտ կլինի թարմացնել մեր հարցումը և ամփոփագիրը ստեղնաշարի դյուրանցմամբ Ctrl+ալտ+F5 կամ թիմ Տվյալներ – Թարմացրեք բոլորը (Տվյալներ — Թարմացնել բոլորը).

Մեթոդ 3. Մակրո VBA-ում

Բոլոր նախորդ մեթոդները, ինչպես հեշտությամբ կարող եք տեսնել, հենց այնպես չեն զտվում. մենք չենք թաքցնում սյունակները սկզբնական ցուցակում, այլ կազմում ենք նոր աղյուսակ՝ սկզբնականից սյունակների տրված հավաքածուով: Եթե ​​անհրաժեշտ է զտել (թաքցնել) սյունակները աղբյուրի տվյալների մեջ, ապա անհրաժեշտ է սկզբունքորեն այլ մոտեցում, այն է՝ մակրո:

Ենթադրենք, մենք ուզում ենք զտել սյունակներ, որտեղ աղյուսակի վերնագրի մենեջերի անունը բավարարում է A4 դեղին բջիջում նշված դիմակը, օրինակ՝ սկսվում է «Ա» տառով (այսինքն՝ ստանալ «Աննա» և «Արթուր» " որպես արդյունք). 

Ինչպես առաջին մեթոդում, մենք նախ իրականացնում ենք օժանդակ տիրույթ-տող, որտեղ յուրաքանչյուր բջիջում մեր չափանիշը կստուգվի բանաձևով և TRUE կամ FALSE տրամաբանական արժեքները կցուցադրվեն համապատասխանաբար տեսանելի և թաքնված սյունակների համար.

Հետո եկեք ավելացնենք պարզ մակրո: Աջ սեղմեք թերթի ներդիրի վրա և ընտրեք հրաման Աղբյուր (աղբյուրի կոդը). Պատճենեք և տեղադրեք հետևյալ VBA կոդը բացվող պատուհանում.

Մասնավոր ենթ աշխատանքային թերթիկ_Change (ByVal թիրախը որպես միջակայք) If Target.Address = "$A$4" Այնուհետեւ տիրույթի յուրաքանչյուր բջիջի համար ("D2:O2") Եթե բջիջը = True, ապա cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next cell End If End Sub  

Դրա տրամաբանությունը հետևյալն է.

• Ընդհանուր առմամբ, սա իրադարձությունների մշակող է Աշխատանքային թերթիկ_Փոփոխություն, այսինքն՝ այս մակրոն ավտոմատ կերպով կաշխատի ընթացիկ թերթի ցանկացած բջիջի ցանկացած փոփոխության դեպքում:
• Փոխված բջիջի հղումը միշտ կլինի փոփոխականում թիրախ.
• Նախ, մենք ստուգում ենք, որ օգտագործողը փոխել է հենց բջիջը չափանիշով (A4), դա արվում է օպերատորի կողմից: if.
• Այնուհետեւ ցիկլը սկսվում է Յուրաքանչյուրի համար… կրկնել մոխրագույն բջիջների վրա (D2:O2) յուրաքանչյուր սյունակի համար TRUE / FALSE ցուցիչի արժեքներով:
• Եթե ​​հաջորդ մոխրագույն բջիջի արժեքը TRUE է (true), ապա սյունակը թաքցված չէ, հակառակ դեպքում մենք թաքցնում ենք այն (հատկություն թաքուն).

•  Դինամիկ զանգվածի գործառույթները Office 365-ից՝ FILTER, SORT և UNIC
• Առանցքային աղյուսակ՝ բազմագիծ վերնագրով, օգտագործելով Power Query
• Ինչ են մակրոները, ինչպես ստեղծել և օգտագործել դրանք