Ամփոփիչ աղյուսակը տվյալների մի քանի տիրույթներում

Խնդրի ձևակերպում

Pivot աղյուսակները Excel-ի ամենահիասքանչ գործիքներից են: Բայց մինչ այժմ, ցավոք, Excel-ի տարբերակներից և ոչ մեկը չի կարող արագորեն անել այնպիսի պարզ և անհրաժեշտ բան, ինչպիսին է մի քանի նախնական տվյալների տիրույթների ամփոփագիր ստեղծելը, որոնք տեղակայված են, օրինակ, տարբեր թերթիկների կամ տարբեր աղյուսակների վրա.

Նախքան սկսելը, եկեք հստակեցնենք մի քանի կետ. Ապրիորի, կարծում եմ, որ մեր տվյալների մեջ բավարարված են հետևյալ պայմանները.

• Աղյուսակները կարող են ունենալ ցանկացած թվով տող՝ ցանկացած տվյալով, բայց դրանք պետք է ունենան նույն վերնագիրը:
• Աղբյուրի աղյուսակներով թերթերի վրա չպետք է լրացուցիչ տվյալներ լինեն: Մեկ թերթ - մեկ սեղան: Վերահսկելու համար խորհուրդ եմ տալիս օգտագործել ստեղնաշարի դյուրանցում Ctrl+վերջ, որը ձեզ տեղափոխում է աշխատաթերթի վերջին օգտագործված բջիջը: Իդեալում, սա պետք է լինի տվյալների աղյուսակի վերջին բջիջը: Եթե ​​երբ սեղմում եք Ctrl+վերջ Աղյուսակի աջ կողմում կամ ներքևում գտնվող ցանկացած դատարկ բջիջ ընդգծված է. ջնջեք այս դատարկ սյունակները աջ կողմում կամ աղյուսակից հետո աղյուսակի տակ գտնվող տողերը և պահեք ֆայլը:

Մեթոդ 1. Կառուցեք աղյուսակներ առանցքի համար՝ օգտագործելով Power Query

Excel-ի 2010 թվականի տարբերակից սկսած՝ կա Power Query-ի անվճար հավելում, որը կարող է հավաքել և փոխակերպել ցանկացած տվյալ, այնուհետև դրանք տալ որպես առանցքային աղյուսակ կառուցելու աղբյուր: Այս հավելյալի օգնությամբ մեր խնդրի լուծումն ամենևին էլ դժվար չէ։

Նախ, եկեք ստեղծենք նոր դատարկ ֆայլ Excel-ում, որի մեջ տեղի կունենա հավաքում, այնուհետև կստեղծվի առանցքային աղյուսակ:

Այնուհետև ներդիրում Ամսաթիվ (եթե ունեք Excel 2016 կամ ավելի նոր տարբերակ) կամ ներդիրում Power հարցում (եթե ունեք Excel 2010-2013) ընտրեք հրամանը Ստեղծեք հարցում – Ֆայլից – Excel (Ստացեք տվյալներ — ֆայլից — Excel) և նշեք սկզբնաղբյուր ֆայլը հավաքվող աղյուսակներով.

Բացվող պատուհանում ընտրեք ցանկացած թերթիկ (կարևոր չէ, թե որն է) և սեղմեք ներքևի կոճակը Փոփոխություն (Խմբագրել):

Power Query Query Editor պատուհանը պետք է բացվի Excel-ի վերևում: Վահանակի վրա գտնվող պատուհանի աջ կողմում Հարցման պարամետրեր ջնջել բոլոր ինքնաբերաբար ստեղծված քայլերը, բացառությամբ առաջինի. Աղբյուր (Աղբյուր):

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

Ջնջել բոլոր սյունակները, բացի սյունակից Ամսաթիվաջ սեղմելով սյունակի վերնագրի վրա և ընտրելով Ջնջել այլ սյունակներ (Հեռացնել այլ սյունակներ):

Այնուհետև կարող եք ընդլայնել հավաքված աղյուսակների բովանդակությունը՝ կտտացնելով սյունակի վերևում գտնվող կրկնակի սլաքի վրա (վանդակը Որպես նախածանց օգտագործիր սյունակի բնօրինակը կարող եք անջատել):

Եթե ​​ամեն ինչ ճիշտ եք արել, ապա այս պահին դուք պետք է տեսնեք բոլոր աղյուսակների բովանդակությունը, որոնք հավաքված են մեկը մյուսի տակ.

Մնում է կոճակով առաջին շարքը բարձրացնել աղյուսակի վերնագրի վրա Օգտագործեք առաջին տողը որպես վերնագիր (Օգտագործեք առաջին տողը որպես վերնագիր) ականջակալ Գլխավոր (Տուն) և հեռացնել աղյուսակի կրկնօրինակ վերնագրերը տվյալներից՝ օգտագործելով զտիչ.

Պահպանեք հրամանով կատարված ամեն ինչ Փակել և բեռնել – Փակել և բեռնել… (Փակել և բեռնել — Փակել և բեռնել…) ականջակալ Գլխավոր (Տուն), և բացվող պատուհանում ընտրեք տարբերակը Միայն միացում (Միայն միացում):

Ամեն ինչ. Մնում է միայն ամփոփում կառուցել։ Դա անելու համար անցեք ներդիր Տեղադրել – PivotTable (Տեղադրեք - առանցքային աղյուսակ), ընտրեք տարբերակը Օգտագործեք արտաքին տվյալների աղբյուր (Օգտագործեք արտաքին տվյալների աղբյուր)իսկ հետո սեղմելով կոճակը Ընտրեք կապը, մեր խնդրանքը։ Առանցքի հետագա ստեղծումը և կազմաձևումը տեղի է ունենում ամբողջովին ստանդարտ ձևով, մեզ անհրաժեշտ դաշտերը քաշելով տողերի, սյուների և արժեքների տարածքի մեջ.

Եթե ​​ապագայում աղբյուրի տվյալները փոխվեն կամ ավելացվեն խանութի ևս մի քանի թերթեր, ապա բավական կլինի թարմացնել հարցումը և մեր ամփոփագիրը՝ օգտագործելով հրամանը. Թարմացնել բոլորը ականջակալ Ամսաթիվ (Տվյալներ — Թարմացնել բոլորը).

Մեթոդ 2. UNION SQL հրամանով աղյուսակները միավորում ենք մակրոյում

Մեր խնդրի մեկ այլ լուծում ներկայացված է այս մակրոյով, որը հրամանի միջոցով ստեղծում է տվյալների հավաքածու (քեշ) առանցքային աղյուսակի համար. UNITY- ը SQL հարցման լեզու. Այս հրամանը միավորում է աղյուսակները զանգվածում նշված բոլորից Թերթի Անուններ գրքի թերթիկները մեկ տվյալների աղյուսակի մեջ: Այսինքն, փոխանակ ֆիզիկապես պատճենելու և տեղադրելու միջակայքերը տարբեր թերթերից մեկին, մենք նույնն ենք անում համակարգչի RAM-ում: Այնուհետև մակրոն ավելացնում է նոր թերթ՝ տրված անունով (փոփոխական ResultSheetName) և հավաքագրված քեշի հիման վրա ստեղծում է դրա վրա ամբողջական (!) ամփոփում։

Մակրո օգտագործելու համար օգտագործեք ներդիրի Visual Basic կոճակը երեվակիչ (մշակող) կամ ստեղնաշարի դյուրանցում ալտ+F11. Այնուհետև ցանկի միջոցով տեղադրում ենք նոր դատարկ մոդուլ Ներդիր – մոդուլ և այնտեղ պատճենեք հետևյալ կոդը.

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache Որպես PivotCache Dim objRS Որպես օբյեկտ Dim ResultSheetName Որպես String Dim SheetsName Որպես տարբերակ 'sheet name, որտեղ արդյունքում ստացված առանցքը կցուցադրվի «rezultSheaet": անուններ սկզբնաղբյուր աղյուսակներով SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'մենք ձևավորում ենք աղյուսակների քեշ SheetsNames-ի թերթերից, ActiveWorkbook-ով ReDim arSQL(1 Դեպի (UBound(SheetsNames) + 1) ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Հաջորդ i Set objRS = CreateObject("ADODB.Recordset") objRS .Բացել Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Ավարտել 'վերստեղծել թերթիկը, որպեսզի ցուցադրվի ստացված առանցքային աղյուսակը Սխալի վրա Վերսկսել Հաջորդ Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo տ. Name = ResultSheetName 'ցուցադրել ստեղծված քեշի ամփոփումը այս թերթում Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Սահմանել objPivotCache.Recordset = objRS Սահմանել objRS = Nothing With wsPivotaPcheet. objPivotCache = Nothing Range("A3"): Ընտրեք End With End Sub    

Ավարտված մակրոն կարող է գործարկվել ստեղնաշարի դյուրանցմամբ ալտ+F8 կամ «Մակրո» կոճակը ներդիրում երեվակիչ (Մշակող — մակրո).

Այս մոտեցման թերությունները.

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

Բայց ի վերջո մենք ստանում ենք իրական լիարժեք առանցքային աղյուսակ, որը կառուցված է տարբեր թերթերից մի քանի միջակայքերի վրա.

Voilà!

Տեխնիկական նշում: եթե մակրո գործարկելիս սխալ եք ստանում «Մատակարարը գրանցված չէ», ապա, ամենայն հավանականությամբ, ունեք Excel-ի 64-բիթանոց տարբերակ կամ տեղադրված է Office-ի թերի տարբերակը (Առանց Access): Իրավիճակը շտկելու համար փոխարինեք հատվածը մակրո կոդի մեջ.

	 Մատակարար=Microsoft.Jet.OLEDB.4.0;  

- ից:

	Մատակարար=Microsoft.ACE.OLEDB.12.0;  

Եվ ներբեռնեք և տեղադրեք տվյալների մշակման անվճար շարժիչը Access-ից Microsoft-ի կայքից – Microsoft Access Database Engine 2010 Redistributable

Մեթոդ 3. Համախմբել PivotTable Wizard-ը Excel-ի հին տարբերակներից

Այս մեթոդը մի փոքր հնացած է, բայց դեռ պետք է նշել: Պաշտոնապես ասած, մինչև 2003 թվականը ներառյալ բոլոր տարբերակներում, PivotTable Wizard-ում կար «առանցք կառուցելու մի քանի համախմբման միջակայքերի համար» տարբերակ: Այնուամենայնիվ, այս ձևով կառուցված զեկույցը, ցավոք, կլինի միայն իրական ամբողջական ամփոփման ողորմելի տեսք և չի աջակցում սովորական առանցքային աղյուսակների «չիպերից» շատերին.

Նման առանցքում դաշտերի ցանկում չկան սյունակների վերնագրեր, չկա ճկուն կառուցվածքի կարգավորում, օգտագործվող գործառույթների շարքը սահմանափակ է, և, ընդհանուր առմամբ, այս ամենն այնքան էլ նման չէ առանցքային աղյուսակին: Թերևս դա է պատճառը, որ 2007 թվականից սկսած Microsoft-ը հանեց այս գործառույթը ստանդարտ երկխոսությունից առանցքային աղյուսակների հաշվետվություններ ստեղծելիս: Այժմ այս գործառույթը հասանելի է միայն հատուկ կոճակի միջոցով PivotTable Wizard(Առանցքային աղյուսակի մոգ), որը ցանկության դեպքում կարող է ավելացվել Արագ մուտքի Գործիքադարակի միջոցով Ֆայլ – Ընտրանքներ – Անհատականացրեք Արագ մուտքի Գործիքադարակը – Բոլոր հրամանները (Ֆայլ — Ընտրանքներ — Անհատականացնել Արագ մուտքի Գործիքադարակը — Բոլոր հրամանները):

Ավելացված կոճակը սեղմելուց հետո դուք պետք է ընտրեք համապատասխան տարբերակը հրաշագործի առաջին քայլում.

Եվ այնուհետև հաջորդ պատուհանում հերթով ընտրեք յուրաքանչյուր տիրույթ և ավելացրեք այն ընդհանուր ցանկում.

Բայց, նորից եմ ասում, սա լիարժեք ամփոփում չէ, ուստի դրանից շատ բան մի սպասեք։ Այս տարբերակը կարող եմ խորհուրդ տալ միայն շատ պարզ դեպքերում։

• Զեկույցների ստեղծում PivotTables-ով
• Ստեղծեք հաշվարկներ PivotTables-ում
• Ինչ են մակրոները, ինչպես օգտագործել դրանք, որտեղ պատճենել VBA կոդը և այլն:
• Տվյալների հավաքագրում մի քանի թերթից մեկ (PLEX հավելում)