Կառուցեք աղյուսակներ տարբեր վերնագրերով մի քանի գրքերից

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

Մենք ունենք մի քանի ֆայլ (մեր օրինակում՝ 4 կտոր, ընդհանուր դեպքում՝ այնքան, որքան ցանկանում եք) մեկ թղթապանակում Զեկույցներ:

Ներսում այս ֆայլերը այսպիսի տեսք ունեն.

Որտեղ:

• Մեզ անհրաժեշտ տվյալների թերթիկը միշտ կանչվում է Pics, բայց կարող է լինել աշխատանքային գրքույկի ցանկացած կետում:
  
• Սավանից այն կողմ Pics Յուրաքանչյուր գիրք կարող է ունենալ այլ թերթիկներ:
• Տվյալներով աղյուսակներն ունեն տողերի տարբեր քանակ և կարող են սկսվել աշխատանքային թերթի մեկ այլ տողով:
• Տարբեր աղյուսակներում նույն սյունակների անունները կարող են տարբեր լինել (օրինակ. Քանակ = Քանակ = Քանակ).
• Աղյուսակների սյունակները կարող են դասավորվել այլ հերթականությամբ:

Առաջադրանք՝ հավաքեք վաճառքի տվյալները թերթի բոլոր ֆայլերից Pics մեկ ընդհանուր աղյուսակի մեջ՝ հետագայում դրա վրա ամփոփում կամ որևէ այլ վերլուծություն կառուցելու համար:

Քայլ 1. Սյունակների անունների գրացուցակի պատրաստում

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

Մենք այս ցուցակը վերածում ենք դինամիկ «խելացի» աղյուսակի՝ օգտագործելով ներդիրում գտնվող «Ձևաչափել որպես աղյուսակ» կոճակը Գլխավոր (Տուն — Ձևաչափել որպես աղյուսակ) կամ ստեղնաշարի դյուրանցում Ctrl+T և հրամանով բեռնեք այն Power Query-ում Տվյալներ – Աղյուսակից / միջակայքից (Տվյալներ՝ աղյուսակից/միջակայքից). Excel-ի վերջին տարբերակներում այն ​​վերանվանվել է Տերեւներով (թերթից).

Power Query հարցման խմբագրիչի պատուհանում մենք ավանդաբար ջնջում ենք քայլը Փոխված տեսակը և դրա փոխարեն ավելացրեք նոր քայլ՝ սեղմելով կոճակի վրա f_xբանաձևի տողում (եթե այն տեսանելի չէ, ապա կարող եք միացնել այն ներդիրում տեսություն) և այնտեղ մուտքագրեք բանաձևը ներկառուցված Power Query լեզվով M:

=Table.ToRows(Աղբյուր)

Այս հրամանը կվերափոխի նախորդ քայլում բեռնվածը Աղբյուր տեղեկատու աղյուսակը մի ցուցակի, որը բաղկացած է ներդիր ցուցակներից (Ցուցակ), որոնցից յուրաքանչյուրն իր հերթին արժեքների զույգ է Այն դարձավ-դարձավ մեկ տողից.

Այս տեսակի տվյալները մեզ պետք կգան մի փոքր ավելի ուշ՝ բոլոր բեռնված աղյուսակներից վերնագրերը զանգվածաբար վերանվանելիս։

Փոխակերպումն ավարտելուց հետո ընտրեք հրամանները Գլխավոր — Փակել և բեռնել — Փակել և բեռնել… և ներմուծման տեսակը Պարզապես կապ ստեղծեք (Գլխավոր — Փակել և բեռնել — Փակել և բեռնել… — Միայն ստեղծել կապ) և վերադառնալ Excel:

Քայլ 2. Մենք բեռնում ենք ամեն ինչ բոլոր ֆայլերից, ինչպես կա

Հիմա եկեք բեռնենք մեր բոլոր ֆայլերի բովանդակությունը թղթապանակից – առայժմ, ինչպես կա: Թիմերի ընտրություն Տվյալներ – Ստացեք տվյալներ – Ֆայլից – Թղթապանակից (Տվյալներ — Ստացեք տվյալներ — Ֆայլից — թղթապանակից) և այնուհետև այն թղթապանակը, որտեղ գտնվում են մեր սկզբնաղբյուր գրքերը:

Նախադիտման պատուհանում սեղմեք Փոխակերպել (Փոխակերպում) or Փոփոխություն (Խմբագրել):

Եվ հետո ընդլայնել բոլոր ներբեռնված ֆայլերի բովանդակությունը (Երկուական) կոճակը կրկնակի սլաքներով սյունակի վերնագրում Պարունակություն:

Power Query առաջին ֆայլի օրինակով (Vostok.xlsx) կհարցնի մեզ այն թերթիկի անունը, որը մենք ցանկանում ենք վերցնել յուրաքանչյուր աշխատանքային գրքույկից – ընտրեք Pics և սեղմեք OK:

Դրանից հետո (իրականում) տեղի կունենան օգտագործողի համար ոչ ակնհայտ մի քանի իրադարձություն, որոնց հետևանքները հստակ տեսանելի են ձախ վահանակում.

1. Power Query-ն առաջին ֆայլը կվերցնի թղթապանակից (մենք կունենանք այն Vostok.xlsx — տեսնել Ֆայլի օրինակ) որպես օրինակ և ներմուծում է դրա բովանդակությունը հարցում ստեղծելով Փոխակերպել նմուշի ֆայլը. Այս հարցումը կունենա մի քանի պարզ քայլեր, ինչպիսիք են Աղբյուր (ֆայլի մուտք) նավարկություն (թերթի ընտրություն) և, հնարավոր է, վերնագրերի բարձրացում: Այս հարցումը կարող է բեռնել տվյալներ միայն մեկ կոնկրետ ֆայլից Vostok.xlsx.
   
2. Այս հարցման հիման վրա կստեղծվի դրա հետ կապված գործառույթը Փոխարկել ֆայլը (նշված է բնորոշ պատկերակով f_x), որտեղ աղբյուրի ֆայլն այլևս հաստատուն չի լինի, այլ փոփոխական արժեք՝ պարամետր: Այսպիսով, այս ֆունկցիան կարող է տվյալներ հանել ցանկացած գրքից, որը մենք սայթաքում ենք դրա մեջ որպես փաստարկ:
   
3. Ֆունկցիան հերթով կկիրառվի սյունակից յուրաքանչյուր ֆայլի վրա (Բինար): Պարունակություն – Սրա համար պատասխանատու է քայլը Զանգել մաքսային գործառույթ մեր հարցման մեջ, որը սյունակ է ավելացնում ֆայլերի ցանկին Փոխարկել ֆայլը յուրաքանչյուր աշխատանքային գրքույկի ներմուծման արդյունքներով՝

   

4. Լրացուցիչ սյունակները հանվում են:
5. Ներդրված աղյուսակների բովանդակությունը ընդլայնվում է (քայլ Ընդլայնված աղյուսակի սյունակ) – և մենք տեսնում ենք տվյալների հավաքագրման վերջնական արդյունքները բոլոր գրքերից.

   
   

Քայլ 3. Ավազում

Նախորդ սքրինշոթը հստակ ցույց է տալիս, որ ուղղակի հավաքումը «ինչպես կա» պարզվեց, որ անորակ է.

• Սյունակները հակադարձված են:
• Շատ լրացուցիչ տողեր (դատարկ և ոչ միայն):
• Աղյուսակների վերնագրերը չեն ընկալվում որպես վերնագրեր և խառնվում են տվյալների հետ:
  

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

Հարցում բացելով Փոխակերպել նմուշի ֆայլը, ավելացրեք քայլեր՝ ավելորդ տողերը զտելու համար (օրինակ՝ ըստ սյունակի Column2) և վերնագրերը կոճակով բարձրացնելով Օգտագործեք առաջին տողը որպես վերնագիր (Օգտագործեք առաջին տողը որպես վերնագիր). Աղյուսակը շատ ավելի լավ տեսք կունենա։

Որպեսզի ավելի ուշ տարբեր ֆայլերի սյունակներն ավտոմատ կերպով տեղավորվեն միմյանց տակ, դրանք պետք է անվանվեն նույնը: Դուք կարող եք նման զանգվածային վերանվանում կատարել նախկինում ստեղծված գրացուցակի համաձայն M-code-ի մեկ տողով: Եկեք նորից սեղմենք կոճակը f_x բանաձևի տողում և ավելացրեք ֆունկցիա՝ փոխելու համար.

= Table.RenameColumns (#"Elevated Headers", Headers, MissingField.Ignore)

Այս ֆունկցիան վերցնում է նախորդ քայլի աղյուսակը Բարձրացված վերնագրեր և վերանվանում է դրա բոլոր սյունակները՝ ըստ տեղադրված փնտրման ցանկի Վերջին լուրերը,. Երրորդ փաստարկ MissingField.Անտեսել անհրաժեշտ է, որպեսզի այն վերնագրերի վրա, որոնք գտնվում են գրացուցակում, բայց չկան աղյուսակում, սխալ չառաջանա:

Իրականում այսքանը:

Վերադառնալով խնդրանքին Զեկույցներ մենք կտեսնենք բոլորովին այլ պատկեր՝ շատ ավելի գեղեցիկ, քան նախորդը.

• Ի՞նչ է Power Query-ը, Power Pivot-ը, Power BI-ն և ինչու են դրանք անհրաժեշտ Excel-ի օգտատերերին
  
• Տվյալ թղթապանակի բոլոր ֆայլերից տվյալների հավաքում
• Գրքի բոլոր թերթերից տվյալների հավաքում մեկ աղյուսակի մեջ