Բովանդակություն
Խնդրի ձևակերպում
Որպես մուտքային տվյալ ունենք Excel ֆայլ, որտեղ թերթերից մեկը պարունակում է մի քանի աղյուսակ հետևյալ ձևի վաճառքի տվյալներով.
Նշենք, որ.
- Տարբեր չափերի և ապրանքների և տարածաշրջանների տարբեր խմբերով աղյուսակներ՝ տողերով և սյունակներով՝ առանց որևէ տեսակավորման:
- Աղյուսակների միջև կարող են տեղադրվել դատարկ տողեր:
- Սեղանների քանակը կարող է լինել ցանկացած:
Երկու կարևոր ենթադրություն. Ենթադրվում է, որ.
- Յուրաքանչյուր աղյուսակի վերևում, առաջին սյունակում, կա այն մենեջերի անունը, որի վաճառքը ցույց է տալիս աղյուսակը (Իվանով, Պետրով, Սիդորով և այլն):
- Բոլոր աղյուսակներում ապրանքների և տարածաշրջանների անվանումները գրված են նույն կերպ՝ մեծատառերի ճշգրտությամբ:
Վերջնական նպատակն է բոլոր աղյուսակներից տվյալները հավաքել մեկ հարթ նորմալացված աղյուսակի մեջ, որը հարմար է հետագա վերլուծության և ամփոփում ստեղծելու համար, այսինքն՝ այս մեկում.
Քայլ 1. Միացեք ֆայլին
Եկեք ստեղծենք նոր դատարկ Excel ֆայլ և ընտրենք այն ներդիրում Ամսաթիվ Հրաման Ստացեք տվյալներ – Ֆայլից – Գրքից (Տվյալներ — Ֆայլից — Աշխատանքային գրքույկից). Նշեք աղբյուրի ֆայլի գտնվելու վայրը վաճառքի տվյալներով, այնուհետև նավիգատորի պատուհանում ընտրեք մեզ անհրաժեշտ թերթիկը և սեղմեք կոճակը Փոխարկել տվյալները (Տվյալների փոխակերպում):
Արդյունքում, դրանից ստացված բոլոր տվյալները պետք է բեռնվեն Power Query խմբագրիչում.
Քայլ 2. Մաքրել աղբը
Ջնջել ինքնաբերաբար ստեղծված քայլերը փոփոխված տեսակը (Փոխված տեսակը) и Բարձրացված վերնագրեր (Խթանված վերնագրեր) և ֆիլտրի միջոցով ազատվել դատարկ գծերից և տոտալներով տողերից զրո и Ընդհանուր առաջին սյունակի կողմից։ Արդյունքում մենք ստանում ենք հետևյալ պատկերը.
Քայլ 3. Կառավարիչների ավելացում
Որպեսզի հետո հասկանանք, թե որտեղ են վաճառքները, անհրաժեշտ է մեր աղյուսակում ավելացնել սյունակ, որտեղ յուրաքանչյուր տողում կլինի համապատասխան ազգանունը։ Սրա համար:
1. Եկեք ավելացնենք օժանդակ սյունակ տողերի համարներով՝ օգտագործելով հրամանը Ավելացնել սյունակ – ինդեքս սյունակ – 0-ից (Ավելացնել սյունակ — Ինդեքս սյունակ — 0-ից).
2. Հրամանով բանաձևով սյունակ ավելացրեք Սյունակի ավելացում – Պատվերով սյունակ (Ավելացնել սյունակ — Հատուկ սյունակ) և այնտեղ ներկայացնել հետևյալ շինարարությունը.
Այս բանաձևի տրամաբանությունը պարզ է. եթե առաջին սյունակի հաջորդ բջիջի արժեքը «Արտադրանք» է, ապա դա նշանակում է, որ մենք պատահաբար հայտնվել ենք նոր աղյուսակի սկզբում, ուստի մենք ցուցադրում ենք նախորդ բջիջի արժեքը կառավարչի անունը. Հակառակ դեպքում մենք ոչինչ չենք ցուցադրում, այսինքն՝ զրոյական։
Ազգանունով մայր բջիջը ստանալու համար նախ վերաբերում ենք նախորդ քայլի աղյուսակին #«Ինդեքսն ավելացված է», և այնուհետև նշեք մեզ անհրաժեշտ սյունակի անունը [Սյունակ 1] քառակուսի փակագծերում և այդ սյունակի բջիջի համարը՝ գանգուր փակագծերում: Բջջի համարը մեկով պակաս կլինի ընթացիկից, որը մենք վերցնում ենք սյունակից ինդեքս, Համապատասխանաբար.
3. Մնում է լրացնել դատարկ բջիջները զրո անունները բարձրագույն բջիջներից հրամանով Փոխակերպում – Լրացրեք – Ներքև (Փոխակերպել — Լրացնել — Ներքև) և ջնջել այլևս անհրաժեշտություն չունեցող սյունակը` առաջին սյունակում ցուցիչներով և ազգանուններով տողերով: Արդյունքում մենք ստանում ենք.
Քայլ 4. Ղեկավարների կողմից առանձին աղյուսակների խմբավորում
Հաջորդ քայլը յուրաքանչյուր մենեջերի համար տողերը առանձին աղյուսակների մեջ խմբավորելն է: Դա անելու համար փոխակերպման ներդիրում օգտագործեք Group by command (Transform – Group By) և բացվող պատուհանում ընտրեք Կառավարիչ սյունակը և գործողությունը Բոլոր տողերը (Բոլոր տողերը)՝ պարզապես տվյալներ հավաքելու համար՝ առանց որևէ ագրեգացիոն ֆունկցիա կիրառելու: դրանք (գումար, միջին և այլն): Պ.):
Արդյունքում յուրաքանչյուր մենեջերի համար մենք ստանում ենք առանձին աղյուսակներ.
Քայլ 5. Փոխակերպել Ներդրված աղյուսակները
Այժմ մենք տալիս ենք աղյուսակները, որոնք գտնվում են ստացված սյունակի յուրաքանչյուր բջիջում Բոլոր տվյալները պատշաճ վիճակում:
Նախ, ջնջեք մի սյունակ, որն այլևս անհրաժեշտ չէ յուրաքանչյուր աղյուսակում Մենեջեր. Մենք նորից օգտագործում ենք Պատվերով սյունակ ականջակալ Փոխակերպում (Փոխակերպում — Պատվերով սյունակ) եւ հետեւյալ բանաձեւը.
Այնուհետև, մեկ այլ հաշվարկված սյունակով, մենք յուրաքանչյուր աղյուսակի առաջին տողը բարձրացնում ենք վերնագրերին.
Եվ վերջապես, մենք կատարում ենք հիմնական փոխակերպումը. յուրաքանչյուր աղյուսակը բացում ենք M-ֆունկցիայի միջոցով Table.UnpivotOtherColumns:
Վերնագրից շրջանների անունները կմտնեն նոր սյունակ, և մենք կստանանք ավելի նեղ, բայց միևնույն ժամանակ, ավելի երկար նորմալացված աղյուսակ: Դատարկ բջիջների հետ զրո անտեսվում են:
Ազատվելով ավելորդ միջանկյալ սյուներից՝ մենք ունենք.
Քայլ 6 Ընդարձակեք Nested Tables-ը
Մնում է ընդլայնել բոլոր նորմալացված ներդիր աղյուսակները մեկ ցուցակում՝ օգտագործելով սյունակի վերնագրի կրկնակի սլաքներով կոճակը.
… և վերջապես մենք ստանում ենք այն, ինչ ուզում էինք.
Դուք կարող եք ստացված աղյուսակը ետ արտահանել Excel՝ օգտագործելով հրամանը Գլխավոր — Փակել և բեռնել — Փակել և բեռնել… (Տուն — Փակել և բեռնել — Փակել և բեռնել…).
- Կառուցեք աղյուսակներ տարբեր վերնագրերով մի քանի գրքերից
- Տվյալ թղթապանակի բոլոր ֆայլերից տվյալների հավաքում
- Գրքի բոլոր թերթերից տվյալների հավաքում մեկ աղյուսակի մեջ