Power Query-ի միջոցով Excel-ի տարբեր ֆայլերից աղյուսակների հավաքում

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

Դիտարկենք գեղեցիկ լուծում այն ​​ստանդարտ իրավիճակներից մեկի համար, որին վաղ թե ուշ բախվում են Excel-ի օգտատերերի մեծ մասը. դուք պետք է արագ և ավտոմատ կերպով հավաքեք տվյալներ մեծ թվով ֆայլերից մեկ վերջնական աղյուսակում: 

Ենթադրենք, որ մենք ունենք հետևյալ թղթապանակը, որը պարունակում է մի քանի ֆայլեր՝ մասնաճյուղային քաղաքների տվյալներով.

Ֆայլերի քանակը նշանակություն չունի և ապագայում կարող է փոխվել: Յուրաքանչյուր ֆայլ ունի իր անունով թերթիկ Salesորտեղ գտնվում է տվյալների աղյուսակը.

Աղյուսակներում տողերի (պատվերների) քանակը, իհարկե, տարբեր է, բայց սյունակների հավաքածուն ամենուր ստանդարտ է։

Առաջադրանք. հավաքել տվյալները բոլոր ֆայլերից մեկ գրքում՝ հետագա ավտոմատ թարմացմամբ աղյուսակներում քաղաքային ֆայլեր կամ տողեր ավելացնելիս կամ ջնջելիս: Համաձայն վերջնական համախմբված աղյուսակի, այնուհետև հնարավոր կլինի կառուցել ցանկացած հաշվետվություն, առանցքային աղյուսակներ, զտիչ-տեսակավորող տվյալներ և այլն: Հիմնական բանը հավաքել կարողանալն է:

Մենք ընտրում ենք զենքեր

Լուծման համար մեզ անհրաժեշտ է Excel 2016-ի վերջին տարբերակը (անհրաժեշտ ֆունկցիոնալությունն արդեն իսկ ներկառուցված է դրա մեջ լռելյայն) կամ Excel 2010-2013-ի նախորդ տարբերակները՝ տեղադրված անվճար հավելումով։ Power հարցում Microsoft-ից (ներբեռնեք այստեղից): Power Query-ը գերճկուն և գերհզոր գործիք է՝ արտաքին աշխարհից տվյալները Excel-ում բեռնելու, այնուհետև դրանք հանելու և մշակելու համար: Power Query-ն աջակցում է գրեթե բոլոր առկա տվյալների աղբյուրները՝ տեքստային ֆայլերից մինչև SQL և նույնիսկ Facebook 🙂

Եթե ​​դուք չունեք Excel 2013 կամ 2016, ապա չեք կարող կարդալ հետագա (ուղղակի կատակում եմ): Excel-ի հին տարբերակներում նման խնդիր կարելի է իրականացնել միայն Visual Basic-ում մակրո ծրագրավորելու միջոցով (որը շատ դժվար է սկսնակների համար) կամ միապաղաղ ձեռքով պատճենահանման միջոցով (որը երկար ժամանակ է պահանջում և առաջացնում է սխալներ):

Քայլ 1. Ներմուծեք մեկ ֆայլ որպես նմուշ

Նախ, եկեք ներմուծենք տվյալներ մեկ աշխատանքային գրքույկից որպես օրինակ, որպեսզի Excel-ը «վերցնի գաղափարը»: Դա անելու համար ստեղծեք նոր դատարկ աշխատանքային գրքույկ և…

• եթե ունեք Excel 2016, ապա բացեք ներդիրը Ամսաթիվ եւ ապա Ստեղծեք հարցում – Ֆայլից – Գրքից (Տվյալներ — Նոր հարցում- Ֆայլից — Excel-ից)
• եթե ունեք Excel 2010-2013, որտեղ տեղադրված է Power Query հավելումը, ապա բացեք ներդիրը Power հարցում և ընտրեք դրա վրա Ֆայլից – Գրքից (Ֆայլից — Excel-ից)

Այնուհետև բացվող պատուհանում մտեք հաշվետվություններով մեր թղթապանակը և ընտրեք քաղաքային ֆայլերից որևէ մեկը (կարևոր չէ, թե որ մեկը, քանի որ դրանք բոլորը բնորոշ են): Մի քանի վայրկյան հետո պետք է հայտնվի Navigator պատուհանը, որտեղ ձախ կողմում պետք է ընտրենք մեզ անհրաժեշտ թերթիկը (Sales), իսկ աջ կողմում կցուցադրվի դրա բովանդակությունը.

Եթե ​​սեղմեք այս պատուհանի ստորին աջ անկյունում գտնվող կոճակը Բեռնել (Բեռնել), ապա աղյուսակը անմիջապես կներմուծվի թերթիկ իր սկզբնական տեսքով: Մեկ ֆայլի համար սա լավ է, բայց մենք պետք է բեռնենք շատ նման ֆայլեր, այնպես որ մենք մի փոքր այլ կերպ կգնանք և սեղմում ենք կոճակը Ուղղում (Խմբագրել). Դրանից հետո Power Query հարցման խմբագրիչը պետք է ցուցադրվի առանձին պատուհանում՝ գրքի մեր տվյալներով.

Սա շատ հզոր գործիք է, որը թույլ է տալիս «ավարտել» աղյուսակը մեզ անհրաժեշտ տեսքով: Նրա բոլոր գործառույթների նույնիսկ մակերեսային նկարագրությունը կտևի մոտ հարյուր էջ, բայց, եթե շատ հակիրճ, օգտագործելով այս պատուհանը, կարող եք.

• զտել ավելորդ տվյալները, դատարկ տողերը, սխալներով տողերը
• տեսակավորել տվյալները մեկ կամ մի քանի սյունակներով
• ազատվել կրկնությունից
• կպչուն տեքստը բաժանել սյունակների (ըստ սահմանազատողների, նիշերի քանակի և այլն)
• դասավորել տեքստը (հեռացնել ավելորդ բացատները, ուղղել մեծատառերը և այլն)
• փոխարկել տվյալների տեսակները ամեն կերպ (տեքստի նման թվերը վերածել նորմալ թվերի և հակառակը)
• փոխադրել (պտտել) աղյուսակները և ընդլայնել երկչափ խաչաձև աղյուսակները հարթերի
• Աղյուսակում ավելացրեք լրացուցիչ սյունակներ և դրանցում օգտագործեք բանաձևեր և գործառույթներ՝ օգտագործելով Power Query-ում ներկառուցված M լեզուն:
• ...

Օրինակ, եկեք մեր աղյուսակում ավելացնենք ամսվա տեքստային անվանումով սյունակ, որպեսզի հետագայում ավելի հեշտ լինի կառուցել առանցքային աղյուսակի հաշվետվություններ։ Դա անելու համար աջ սեղմեք սյունակի վերնագրի վրա ամսաթիվև ընտրեք հրամանը Կրկնօրինակ սյունակ (Կրկնօրինակ սյունակ), ապա աջ սեղմեք կրկնօրինակ սյունակի վերնագրի վրա, որը հայտնվում է և ընտրեք Հրամաններ Փոխակերպում – Ամիս – Ամիս Անուն:

Յուրաքանչյուր տողի համար պետք է ձևավորվի նոր սյունակ՝ ամսվա տեքստային անուններով: Կրկնակի սեղմելով սյունակի վերնագրի վրա՝ կարող եք վերանվանել այն Պատճենել ամսաթիվը դեպի ավելի հարմարավետ Ամիս, օրինակ.

Եթե ​​որոշ սյունակներում ծրագիրը այնքան էլ ճիշտ չի ճանաչել տվյալների տեսակը, ապա կարող եք օգնել դրան՝ սեղմելով յուրաքանչյուր սյունակի ձախ կողմում գտնվող ձևաչափի պատկերակը.

Դուք կարող եք բացառել սխալներով կամ դատարկ տողերով տողերը, ինչպես նաև անհարկի մենեջերները կամ հաճախորդները՝ օգտագործելով պարզ զտիչ.

Ավելին, կատարված բոլոր փոխակերպումները ամրագրված են աջ վահանակում, որտեղ դրանք միշտ կարող են հետ գլորվել (խաչել) կամ փոխել դրանց պարամետրերը (փոխանցում).

Թեթև և էլեգանտ, այնպես չէ՞։

Քայլ 2. Եկեք մեր հարցումը վերածենք ֆունկցիայի

Յուրաքանչյուր ներմուծված գրքի համար կատարված բոլոր տվյալների փոխակերպումները հետագայում կրկնելու համար մենք պետք է մեր ստեղծված հարցումը փոխարկենք ֆունկցիայի, որն այնուհետև կկիրառվի մեր բոլոր ֆայլերի վրա: Դա անելն իրականում շատ պարզ է:

Հարցման խմբագրիչում անցեք «Դիտել» ներդիր և սեղմեք կոճակը Ընդլայնված խմբագիր (Դիտել — Ընդլայնված խմբագիր). Պետք է բացվի մի պատուհան, որտեղ մեր բոլոր նախորդ գործողությունները կգրվեն կոդի տեսքով M լեզվով: Խնդրում ենք նկատի ունենալ, որ ֆայլի ուղին, որը մենք ներմուծել ենք օրինակի համար, կոշտ կոդավորված է կոդի մեջ.

Այժմ եկեք մի քանի ճշգրտում կատարենք.

Նրանց իմաստը պարզ է՝ առաջին տող (ֆայլի ուղի)=> մեր պրոցեդուրան վերածում է արգումենտով ֆունկցիայի ֆայլի ուղին, իսկ ներքևում մենք փոխում ենք ֆիքսված ուղին դեպի այս փոփոխականի արժեքը։ 

Բոլորը. Սեղմեք Ավարտել և պետք է տեսնել սա.

Մի վախեցեք, որ տվյալները անհետացել են. իրականում ամեն ինչ կարգին է, ամեն ինչ պետք է նման լինի 🙂 Մենք հաջողությամբ ստեղծել ենք մեր հատուկ գործառույթը, որտեղ տվյալների ներմուծման և մշակման ողջ ալգորիթմը հիշվում է՝ առանց որևէ ֆայլի հետ կապվելու։ . Մնում է դրան ավելի հասկանալի անուն տալ (օրինակ ստանալ Տվյալներ) դաշտի աջ կողմում գտնվող վահանակում Անուն և դու կարող ես քաղել Գլխավոր — Փակել և ներբեռնել (Տուն — Փակել և բեռնել). Խնդրում ենք նկատի ունենալ, որ ֆայլի ուղին, որը մենք ներմուծել ենք օրինակի համար, ծածկագրված է կոդի մեջ: Դուք կվերադառնաք Microsoft Excel-ի հիմնական պատուհանին, սակայն աջ կողմում պետք է հայտնվի մեր ֆունկցիայի հետ ստեղծված կապով վահանակ.

Քայլ 3. Բոլոր ֆայլերի հավաքում

Ամենադժվարը հետևում է, հաճելին ու հեշտը մնում է։ Գնացեք ներդիր Տվյալներ – Ստեղծել հարցում – Ֆայլից – Թղթապանակից (Տվյալներ — Նոր հարցում — Ֆայլից — թղթապանակից) կամ, եթե ունեք Excel 2010-2013, ներդիրի նման Power հարցում. Բացվող պատուհանում նշեք այն թղթապանակը, որտեղ գտնվում են մեր սկզբնաղբյուր քաղաքի բոլոր ֆայլերը և սեղմեք OK. Հաջորդ քայլը պետք է բացել պատուհան, որտեղ նշված կլինեն այս թղթապանակում հայտնաբերված բոլոր Excel ֆայլերը (և դրա ենթապանակները) և դրանցից յուրաքանչյուրի մանրամասները.

Սեղմել Փոփոխություն (Խմբագրել) և նորից մենք մտնում ենք ծանոթ հարցման խմբագրիչի պատուհան:

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

Սեղմելուց հետո OK ստեղծված սյունակը պետք է ավելացվի մեր աջ կողմում գտնվող աղյուսակին:

Հիմա եկեք ջնջենք բոլոր ավելորդ սյունակները (ինչպես Excel-ում, օգտագործելով մկնիկի աջ կոճակը – Հեռացնել), թողնելով միայն ավելացված սյունակը և ֆայլի անունով սյունակը, քանի որ այս անունը (ավելի ճիշտ՝ քաղաքը) օգտակար կլինի ունենալ յուրաքանչյուր տողի ընդհանուր տվյալների մեջ։

Եվ հիմա «wow moment»-ը կտտացրեք պատկերակի վրա՝ իր սեփական սլաքներով, ավելացված սյունակի վերին աջ անկյունում մեր գործառույթով.

… հանել նշումը Որպես նախածանց օգտագործիր սյունակի բնօրինակը (Օգտագործեք բնօրինակ սյունակի անունը որպես նախածանց)եւ սեղմեք OK. Եվ մեր գործառույթը կբեռնի և կմշակի յուրաքանչյուր ֆայլի տվյալները՝ հետևելով գրանցված ալգորիթմին և հավաքելով ամեն ինչ ընդհանուր աղյուսակում.

Լրիվ գեղեցկության համար դուք կարող եք նաև հեռացնել .xlsx ընդլայնումները առաջին սյունակից՝ ֆայլերի անուններով՝ ստանդարտ փոխարինելով «ոչինչ»-ով (աջ սեղմեք սյունակի վերնագրի վրա. Փոխարինեք) և վերանվանեք այս սյունակը Քաղաք. Եվ նաև շտկեք տվյալների ձևաչափը սյունակում ամսաթվի հետ:

Բոլորը! Սեղմեք Գլխավոր – Փակել և բեռնել (Տուն — Փակել և բեռնել). Բոլոր քաղաքների համար հարցման միջոցով հավաքված բոլոր տվյալները կվերբեռնվեն ընթացիկ Excel թերթում՝ «խելացի աղյուսակ» ձևաչափով.

Ստեղծված կապը և մեր հավաքման գործառույթը որևէ կերպ առանձին պահելու կարիք չունեն. դրանք պահպանվում են ընթացիկ ֆայլի հետ միասին սովորական եղանակով:

Հետագայում, թղթապանակում (ավելացնելով կամ հեռացնելով քաղաքներ) կամ ֆայլերում (տողերի քանակը փոխելով) ցանկացած փոփոխության դեպքում բավական կլինի աջ սեղմել անմիջապես սեղանի վրա կամ աջ վահանակի հարցման վրա և ընտրել հրաման Թարմացնել և պահպանել (Թարմացնել) – Power Query-ը մի քանի վայրկյանից կրկին «կվերակառուցի» բոլոր տվյալները:

PS

Փոփոխություն. 2017 թվականի հունվարի թարմացումներից հետո Power Query-ն սովորեց, թե ինչպես հավաքել Excel-ի աշխատանքային գրքույկներն ինքնուրույն, այսինքն՝ այլևս կարիք չկա առանձին ֆունկցիա կատարել. դա տեղի է ունենում ավտոմատ կերպով: Այսպիսով, այս հոդվածից երկրորդ քայլն այլևս անհրաժեշտ չէ, և ամբողջ գործընթացը նկատելիորեն ավելի պարզ է դառնում.

1. Ընտրել Ստեղծեք հարցում – Ֆայլից – Թղթապանակից – Ընտրեք Թղթապանակ – Լավ
2. Ֆայլերի ցանկը հայտնվելուց հետո սեղմեք Փոփոխություն
3. Հարցման խմբագրիչի պատուհանում ընդլայնեք Երկուական սյունակը կրկնակի սլաքով և ընտրեք թերթի անունը, որը պետք է վերցվի յուրաքանչյուր ֆայլից:

Եվ այսքանը: Երգ!

• Խաչաձև էջանիշի վերաձևավորումը հարթի վերածելով, որը հարմար է առանցքային աղյուսակներ կառուցելու համար
• Անիմացիոն պղպջակների գծապատկերի կառուցում Power View-ում
• Մակրո՝ տարբեր Excel ֆայլերից թերթերը մեկում հավաքելու համար