Pivot-ի առավելությունները տվյալների մոդելով

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

Ցավոք, շատ օգտատերեր, ովքեր վաղուց ծանոթ են առանցքային աղյուսակներին և հաջողությամբ օգտագործում են դրանք իրենց աշխատանքում, երբեմն իրականում չեն հասկանում այս տարբերակների իմաստը և երբեք չեն օգտագործում դրանք: Եվ ապարդյուն։ Ի վերջո, տվյալների մոդելի համար առանցքային աղյուսակ ստեղծելը մեզ տալիս է մի քանի շատ կարևոր առավելություններ՝ համեմատած դասական Excel առանցքային աղյուսակի հետ:

Այնուամենայնիվ, նախքան այս «փոքրիկները» մոտիկից դիտարկելը, նախ հասկանանք, թե իրականում ինչ է իրենից ներկայացնում տվյալների մոդելը:

Ինչ է տվյալների մոդելը

Տվյալների մոդել (կրճատվում է որպես MD կամ DM = Տվյալների մոդել) Excel ֆայլի ներսում հատուկ տարածք է, որտեղ դուք կարող եք պահել աղյուսակային տվյալներ՝ ցանկության դեպքում մեկ կամ մի քանի աղյուսակներ կապված միմյանց հետ: Փաստորեն, սա փոքր տվյալների բազա է (OLAP խորանարդ), որը ներկառուցված է Excel աշխատանքային գրքում: Համեմատած տվյալների դասական պահպանման հետ՝ սովորական (կամ խելացի) աղյուսակների տեսքով հենց Excel-ի թերթիկների վրա, Տվյալների մոդելն ունի մի քանի նշանակալի առավելություններ.

• Սեղանները կարող են լինել մինչև 2 միլիարդ տող, իսկ Excel թերթիկը կարող է տեղավորել 1 միլիոնից մի փոքր ավելի:
• Չնայած հսկա չափերին, նման աղյուսակների մշակումը (զտում, տեսակավորում, դրանց վրա հաշվարկներ, շենքի ամփոփում և այլն) կատարվում է. շատ արագ Շատ ավելի արագ, քան ինքը՝ Excel-ը:
• Մոդելի տվյալների օգնությամբ դուք կարող եք կատարել լրացուցիչ (ցանկության դեպքում՝ շատ բարդ) հաշվարկներ՝ օգտագործելով ներկառուցված DAX լեզու.
  
• Տվյալների մոդելում բեռնված ամբողջ տեղեկատվությունը շատ է ուժեղ սեղմված օգտագործելով հատուկ ներկառուցված արխիվատոր և բավականին չափավոր մեծացնում է բնօրինակ Excel ֆայլի չափը:
  

Մոդելը կառավարվում և հաշվարկվում է Microsoft Excel-ում ներկառուցված հատուկ հավելումով. Powerpivot- ըորի մասին արդեն գրել եմ։ Այն միացնելու համար ներդիրում երեվակիչ սեղմել COM հավելումներ (Մշակող — COM հավելումներ) և նշեք համապատասխան վանդակը.

Եթե ​​ներդիրները երեվակիչ (մշակող)դուք չեք կարող տեսնել այն ժապավենի վրա, կարող եք միացնել այն Ֆայլ – Ընտրանքներ – Ժապավենի տեղադրում (Ֆայլ — Ընտրանքներ — Անհատականացնել ժապավենը). Եթե ​​COM հավելումների ցանկում վերը նշված պատուհանում դուք չունեք Power Pivot, ապա այն ներառված չէ Microsoft Office-ի ձեր տարբերակում 🙁

«Power Pivot» ներդիրում, որը հայտնվում է, կլինի մեծ բաց կանաչ կոճակ կառավարում (Կառավարել), որի վրա սեղմելով կբացվի Power Pivot պատուհանը Excel-ի վերևում, որտեղ կտեսնենք ընթացիկ գրքի տվյալների մոդելի բովանդակությունը.

Կարևոր նշում ճանապարհին. Excel-ի աշխատանքային գիրքը կարող է պարունակել միայն մեկ Տվյալների մոդել:

Բեռնել աղյուսակները տվյալների մոդելում

Տվյալները Մոդելում բեռնելու համար նախ մենք աղյուսակը վերածում ենք դինամիկ «խելացի» ստեղնաշարի դյուրանցման Ctrl+T և ներդիրի վրա բարեկամական անուն տվեք շինարար (Դիզայն). Սա պարտադիր քայլ է։

Այնուհետև կարող եք ընտրել երեք մեթոդներից որևէ մեկը.

• սեղմել կոճակը Ավելացնել մոդելին (Ավելացնել տվյալների մոդելին) ականջակալ Powerpivot- ը ականջակալ Գլխավոր (Տուն).
  
• Թիմերի ընտրություն Տեղադրել – PivotTable (Տեղադրեք - առանցքային աղյուսակ) և միացրեք վանդակը Այս տվյալները ավելացրեք Տվյալների մոդելում (Ավելացրեք այս տվյալները Տվյալների մոդելում). Այս դեպքում, Մոդելի մեջ բեռնված տվյալների համաձայն, անմիջապես կառուցվում է նաև առանցքային աղյուսակ:
  
• Ընդլայնված ներդիրում Ամսաթիվ (Ամսաթիվ) սեղմեք կոճակի վրա Սեղանից / միջակայքից (Սեղանից / միջակայքից)մեր աղյուսակը Power Query խմբագրիչում բեռնելու համար: Այս ճանապարհն ամենաերկարն է, բայց ցանկության դեպքում այստեղ կարող եք կատարել տվյալների լրացուցիչ մաքրում, խմբագրում և բոլոր տեսակի փոխակերպումներ, որոնցում Power Query-ը շատ ուժեղ է:

  Այնուհետև սանրված տվյալները հրամանի միջոցով բեռնվում են Model Գլխավոր — Փակել և բեռնել — Փակել և բեռնել… (Տուն — Փակել և բեռնել — Փակել և բեռնել…). Բացվող պատուհանում ընտրեք տարբերակը Պարզապես կապ ստեղծեք (Միայն ստեղծել կապ) և, որ ամենակարևորը, տիզ դնել Այս տվյալները ավելացրեք Տվյալների մոդելում (Ավելացրեք այս տվյալները Տվյալների մոդելում).
  

Մենք կառուցում ենք տվյալների մոդելի ամփոփագիր

Տվյալների ամփոփ մոդել ստեղծելու համար կարող եք օգտագործել երեք մոտեցումներից որևէ մեկը.

• Սեղմեք կոճակը ամփոփ աղյուսակ (Առանցքային աղյուսակ) Power Pivot պատուհանում:
• Ընտրեք հրամաններ Excel-ում Տեղադրել – PivotTable և միացրեք ռեժիմին Օգտագործեք այս գրքի տվյալների մոդելը (Տեղադրեք — առանցքային աղյուսակ — Օգտագործեք այս աշխատանքային գրքի տվյալների մոդելը).
• Թիմերի ընտրություն Տեղադրել – PivotTable (Տեղադրեք - առանցքային աղյուսակ) և միացրեք վանդակը Այս տվյալները ավելացրեք Տվյալների մոդելում (Ավելացրեք այս տվյալները Տվյալների մոդելում). Ընթացիկ «խելացի» աղյուսակը կբեռնվի Մոդելի մեջ և ամփոփ աղյուսակ կկառուցվի ամբողջ Մոդելի համար:

Այժմ, երբ մենք հասկացանք, թե ինչպես կարելի է բեռնել տվյալները Տվյալների մոդելում և դրա վրա ամփոփել, եկեք ուսումնասիրենք այն առավելություններն ու առավելությունները, որոնք դա տալիս է մեզ:

Օգուտ 1. Աղյուսակների միջև փոխհարաբերություններ առանց բանաձևերի օգտագործման

Սովորական ամփոփագիր կարող է ստեղծվել միայն մեկ աղբյուրի աղյուսակի տվյալների միջոցով: Եթե ​​ունեք դրանցից մի քանիսը, օրինակ՝ վաճառք, գնացուցակ, հաճախորդների գրացուցակ, պայմանագրերի գրանցամատյան և այլն, ապա նախ պետք է հավաքեք տվյալները բոլոր աղյուսակներից մեկի մեջ՝ օգտագործելով այնպիսի գործառույթներ, ինչպիսիք են VLOOKUP-ը: (VLOOKUP), ԻՆԴԵՔՍ (INDEX), ԱՎԵԼԻ ԲԱՑՎԱԾ (ՄԱՏՉ), SUMMESLIMN (SUMIFS) և նմանները: Սա երկար է, հոգնեցուցիչ և մղում է ձեր Excel-ին «մտքի» մեջ՝ մեծ քանակությամբ տվյալների հետ:

Տվյալների մոդելի ամփոփման դեպքում ամեն ինչ շատ ավելի պարզ է: Բավական է Power Pivot պատուհանում մեկ անգամ կարգավորել աղյուսակների միջև հարաբերությունները, և դա արված է: Դա անելու համար ներդիրում Powerpivot- ը սեղմել կոճակը կառավարում (Կառավարել) և այնուհետև երևացող պատուհանում կոճակը Գծապատկերի տեսք (Դիագրամի տեսք). Մնում է քաշել ընդհանուր (բանալին) սյունակների անունները (դաշտերը) աղյուսակների միջև՝ հղումներ ստեղծելու համար.

Դրանից հետո տվյալների մոդելի ամփոփագրում դուք կարող եք ամփոփման տարածք (տողեր, սյունակներ, ֆիլտրեր, արժեքներ) գցել ցանկացած հարակից աղյուսակներից ցանկացած դաշտ. ամեն ինչ կկապվի և կհաշվարկվի ավտոմատ կերպով.

Օգուտ 2. Հաշվեք եզակի արժեքներ

Սովորական առանցքային աղյուսակը մեզ հնարավորություն է տալիս ընտրել մի քանի ներկառուցված հաշվարկային գործառույթներից մեկը՝ գումար, միջին, հաշվարկ, նվազագույն, առավելագույն և այլն: Տվյալների մոդելի ամփոփման մեջ այս ստանդարտ ցուցակին ավելացվել է շատ օգտակար ֆունկցիա՝ հաշվելու համար եզակի (չկրկնվող արժեքների) քանակը. Նրա օգնությամբ, օրինակ, դուք հեշտությամբ կարող եք հաշվել ապրանքների (տեսականու) եզակի ապրանքների քանակը, որոնք մենք վաճառում ենք յուրաքանչյուր քաղաքում:

Աջ սեղմեք դաշտի վրա՝ հրաման Արժեքի դաշտի ընտրանքներ և ներդիրի վրա Գործողություն Ընտրել Տարբեր տարրերի քանակը (Հստակ հաշվարկ):

Օգուտ 3. Պատվերով DAX բանաձևեր

Երբեմն դուք պետք է կատարեք տարբեր լրացուցիչ հաշվարկներ առանցքային աղյուսակներում: Սովորական ամփոփագրերում դա արվում է հաշվարկված դաշտերի և օբյեկտների միջոցով, մինչդեռ տվյալների մոդելի ամփոփումն օգտագործում է չափումներ հատուկ DAX լեզվով (DAX = Տվյալների վերլուծության արտահայտություններ):

Չափ ստեղծելու համար ընտրեք ներդիրում Powerpivot- ը Հրաման Միջոցառումներ – Ստեղծել չափ (Միջոցառումներ — Նոր միջոց) կամ պարզապես աջ սեղմեք աղյուսակի վրա Pivot Fields ցանկում և ընտրեք Ավելացնել չափ (Ավելացնել չափ) համատեքստի ընտրացանկում.

Բացվող պատուհանում սահմանեք.

• Աղյուսակի անվանումըորտեղ կպահվի ստեղծված միջոցը։
• Չափման անվանումը – ցանկացած անուն, որը դուք հասկանում եք նոր դաշտի համար:
• Նկարագրություն - ըստ ցանկության:
• Ֆորմուլա – Ամենակարևորը, քանի որ այստեղ մենք կամ ձեռքով մուտք ենք գործում, կամ սեղմում ենք կոճակը fx և ցուցակից ընտրեք DAX ֆունկցիա, որը պետք է հաշվարկի արդյունքը, երբ մենք այնուհետև մեր չափումը գցենք Արժեքների տարածք:
• Պատուհանի ներքևի մասում դուք կարող եք անմիջապես սահմանել ցուցակի չափման համարի ձևաչափը կատեգորիա.
  

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

Օգուտ 4. Հատուկ դաշտերի հիերարխիա

Հաճախ, ստանդարտ հաշվետվություններ ստեղծելիս, դուք պետք է դաշտերի նույն համակցությունները գցեք առանցքային աղյուսակների մեջ տվյալ հաջորդականությամբ, օրինակ. Տարի-եռամսյակ-ամիս-օրԿամ Կատեգորիա-ԱպրանքԿամ Երկիր-Քաղաք-Հաճախորդ և այլն: Տվյալների մոդելի ամփոփագրում այս խնդիրը հեշտությամբ լուծվում է՝ ստեղծելով ձեր սեփականը հիերարխիաներ - հարմարեցված դաշտերի հավաքածուներ:

Power Pivot պատուհանում կոճակով անցեք գծապատկերի ռեժիմին Գծապատկերի տեսք ականջակալ Գլխավոր (Տուն — Դիագրամի տեսք), ընտրել հետ Ctrl ցանկալի դաշտերը և աջ սեղմեք դրանց վրա: Համատեքստի ընտրացանկը կպարունակի հրամանը Ստեղծեք հիերարխիա (Ստեղծել հիերարխիա):

Ստեղծված հիերարխիան կարող է վերանվանվել և մկնիկի միջոցով քաշել դրա մեջ անհրաժեշտ դաշտերը, որպեսզի հետագայում մեկ շարժումով դրանք գցվեն ամփոփման մեջ.

Օգուտ 5. Պատվերով տրաֆարետներ

Շարունակելով նախորդ պարբերության գաղափարը, Տվյալների մոդելի ամփոփագրում կարող եք նաև ստեղծել տարրերի ձեր հավաքածուները յուրաքանչյուր դաշտի համար: Օրինակ, քաղաքների ամբողջ ցանկից հեշտությամբ կարող եք մի շարք կազմել միայն նրանցից, որոնք գտնվում են ձեր պատասխանատվության գոտում: Կամ հավաքեք միայն ձեր հաճախորդներին, ձեր ապրանքները և այլն հատուկ հավաքածուի մեջ:

Դա անելու համար ներդիրում Առանցքային աղյուսակի վերլուծություն բացվող ցանկում Դաշտեր, իրեր և հավաքածուներ կան համապատասխան հրամաններ (Վերլուծել - Դաշտեր, Իtemps & Sets — Ստեղծեք շարք՝ հիմնված տողերի/սյունակների տարրերի վրա):

Բացվող պատուհանում կարող եք ընտրովի հեռացնել, ավելացնել կամ փոխել ցանկացած տարրերի դիրքը և պահպանել ստացված հավաքածուն նոր անունով.

Ստեղծված բոլոր հավաքածուները կցուցադրվեն PivotTable Fields վահանակում առանձին թղթապանակում, որտեղից դրանք կարող են ազատորեն քաշվել ցանկացած նոր PivotTable-ի տողերի և սյունակների տարածքներ.

Օգուտ 6. Ընտրովի թաքցնել աղյուսակները և սյունակները

Չնայած սա փոքր, բայց որոշ դեպքերում շատ հաճելի առավելություն է։ Power Pivot պատուհանում դաշտի անվան կամ սեղանի ներդիրի վրա աջ սեղմելով՝ կարող եք ընտրել հրամանը. Թաքցնել Client Toolkit-ից (Թաքցնել Client Tools-ից):

Թաքնված սյունակը կամ աղյուսակը կվերանա PivotTable Field List վահանակից: Շատ հարմար է, եթե անհրաժեշտ է օգտագործողից թաքցնել որոշ օժանդակ սյունակներ (օրինակ՝ հաշվարկված կամ առանցքային արժեքներով սյունակներ՝ հարաբերություններ ստեղծելու համար) կամ նույնիսկ ամբողջ աղյուսակներ։

Օգուտ 7. Ընդլայնված զորավարժություն

Եթե ​​դուք կրկնակի սեղմում եք արժեքների տարածքում գտնվող որևէ բջիջի վրա սովորական առանցքային աղյուսակում, ապա Excel-ը առանձին թերթիկի վրա ցուցադրում է աղբյուրի տվյալների հատվածի պատճենը, որը ներգրավված է եղել այս բջիջի հաշվարկում: Սա շատ հարմար բան է, որը պաշտոնապես կոչվում է Drill-down (որովհետև նրանք սովորաբար ասում են «fail»):

Տվյալների մոդելի ամփոփագրում այս հարմար գործիքն ավելի նուրբ է աշխատում: Կանգնելով մեզ հետաքրքրող արդյունքով ցանկացած բջիջի վրա՝ կարող եք սեղմել պատկերակի վրա խոշորացույցով, որը հայտնվում է դրա կողքին (այն կոչվում է. Էքսպրես միտումներ) և այնուհետև ընտրեք ցանկացած դաշտ, որը ձեզ հետաքրքրում է որևէ առնչվող աղյուսակում.

Դրանից հետո ընթացիկ արժեքը (Model = Explorer) կմտնի ֆիլտրի տարածք, և ամփոփագիրը կկառուցվի գրասենյակների կողմից.

Իհարկե, նման ընթացակարգը կարելի է բազմիցս կրկնել՝ հետևողականորեն խորանալով ձեր տվյալների մեջ այն ուղղությամբ, որը ձեզ հետաքրքրում է։

Օգուտ 8. Վերափոխեք առանցքը խորանարդի ֆունկցիաների

Եթե ​​ամփոփագրում ընտրեք որևէ բջիջ Տվյալների մոդելի համար և ընտրեք ներդիրում Առանցքային աղյուսակի վերլուծություն Հրաման OLAP գործիքներ – Փոխարկել բանաձևերի (Վերլուծել — OLAP գործիքներ — Փոխարկել բանաձևերի), ապա ամբողջ ամփոփումն ավտոմատ կերպով կվերածվի բանաձևերի: Այժմ դաշտի արժեքները տող-սյունակ տարածքում և արդյունքները արժեքի տարածքում կվերցվեն Տվյալների մոդելից՝ օգտագործելով խորանարդի հատուկ գործառույթները՝ CUBEVALUE և CUBEMEMBER:

Տեխնիկապես սա նշանակում է, որ այժմ մենք գործ ունենք ոչ թե ամփոփագրի, այլ բանաձևերով մի քանի բջիջների հետ, այսինքն՝ մենք կարող ենք հեշտությամբ կատարել ցանկացած փոխակերպում մեր զեկույցով, որը հասանելի չէ ամփոփագրում, օրինակ՝ մեջտեղում տեղադրել նոր տողեր կամ սյունակներ։ հաշվետվության, ամփոփագրի ներսում կատարել լրացուցիչ հաշվարկներ, դասավորել դրանք ցանկացած ցանկալի ձևով և այլն։

Միաժամանակ աղբյուրի տվյալների հետ կապը, իհարկե, մնում է և ապագայում այդ բանաձևերը կթարմացվեն, երբ աղբյուրները փոխվեն։ Գեղեցկուհին։

• Պլան-փաստերի վերլուծություն առանցքային աղյուսակում Power Pivot-ով և Power Query-ով
• Առանցքային աղյուսակ՝ բազմագիծ վերնագրով
• Ստեղծեք տվյալների բազա Excel-ում Power Pivot-ի միջոցով