Придобивките од Pivot by Data Model

При градење на стожерна табела во 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 табот Почетна (Дома).
  
• Избор на тимови Вметнете – Стожерна табела (Вметни - Стожерна табела) и вклучете го полето за избор Додадете ги овие податоци во моделот на податоци (Додајте ги овие податоци во моделот на податоци). Во овој случај, според податоците вчитани во Моделот, веднаш се гради и стожерна табела.
  
• На јазичето Напредно податоци (Датум) кликнете на копчето Од Табела/Опсег (Од табела/опсег)да ја вчитаме нашата табела во уредувачот Power Query. Оваа патека е најдолга, но, по желба, тука можете да извршите дополнително чистење на податоци, уредување и секакви трансформации, во кои Power Query е многу силен.

  Потоа чешланите податоци се прикачуваат на Моделот со командата Дома — Затвори и вчитај — Затвори и вчитај во… (Дома - Затвори&Вчитај - Затвори&Вчитај до…). Во прозорецот што се отвора, изберете ја опцијата Само создадете врска (Само креирајте врска) и што е најважно, стави крлеж Додадете ги овие податоци во моделот на податоци (Додајте ги овие податоци во моделот на податоци).
  

Ние градиме резиме на моделот на податоци

За да изградите збирен модел на податоци, можете да користите кој било од трите пристапи:

• Притиснете го копчето сумарна табела (Свртена табела) во прозорецот Power Pivot.
• Изберете команди во Excel Вметнете – Стожерна табела и префрлете се на режим Користете го моделот на податоци на оваа книга (Вметни — Стожерна табела — Користете го моделот на податоци на оваа работна книга).
• Избор на тимови Вметнете – Стожерна табела (Вметни - Стожерна табела) и вклучете го полето за избор Додадете ги овие податоци во моделот на податоци (Додајте ги овие податоци во моделот на податоци). Тековната „паметна“ табела ќе биде вчитана во Моделот и ќе се изгради сумарна табела за целиот модел.

Сега кога сфативме како да вчитаме податоци во моделот на податоци и да изградиме резиме на него, ајде да ги истражиме придобивките и предностите што ни ги дава ова.

Придобивка 1: Врски помеѓу табелите без користење формули

Редовно резиме може да се изгради само со користење на податоци од една изворна табела. Ако имате неколку од нив, на пример, продажба, ценовник, именик на клиенти, регистар на договори итн., тогаш прво ќе треба да соберете податоци од сите табели во една користејќи функции како што е VLOOKUP (VLOOKUP), ИНДЕКС (ИНДЕКС), ПОИЗЛОЖЕНО (натпревар), САМЕСЛИМН (SUMIFS) и слично. Ова е долго, досадно и го води вашиот Excel во „мисла“ со голема количина на податоци.

Во случај на резиме на моделот на податоци, сè е многу поедноставно. Доволно е да поставите врски помеѓу табелите еднаш во прозорецот Power Pivot - и готово. За да го направите ова, на јазичето powerpivot притисни го копчето управување со (Управувај) а потоа во прозорецот што се појавува – копчето Приказ на графикон (Приказ на дијаграм). Останува да се влечат заеднички (клучни) имиња на колони (полиња) помеѓу табелите за да се создадат врски:

После тоа, во резимето за моделот на податоци, можете да ги фрлите во областа за резиме (редови, колони, филтри, вредности) сите полиња од кои било поврзани табели - сè ќе се поврзе и ќе се пресмета автоматски:

Придобивка 2: Наброј уникатни вредности

Редовната стожерна табела ни дава можност да избереме една од неколкуте вградени пресметковни функции: збир, просек, броење, минимум, максимум итн. број на единствени (вредности што не се повторуваат). Со негова помош, на пример, можете лесно да го броите бројот на уникатни артикли на стоки (опсег) што ги продаваме во секој град.

Десен-клик на полето – команда Опции на полето за вредност и на јазичето Операција Избери Број на различни елементи (различен број):

Придобивка 3: Прилагодени формули DAX

Понекогаш треба да извршите разни дополнителни пресметки во стожерните табели. Во редовните резимеа, ова се прави со помош на пресметани полиња и објекти, додека резимето на моделот на податоци користи мерки во посебен јазик DAX (DAX = Data Analysis Expressions).

За да креирате мерка, изберете на јазичето powerpivot Команда Мерки – Креирај мерка (Мерки — Нова мерка) или само кликнете со десното копче на табелата во списокот Pivot Fields и изберете Додадете мерка (Додадете мерка) во контекстното мени:

Во прозорецот што се отвора, поставете:

• Име на табелатакаде што ќе се чува креираната мерка.
• Име на мерење – секое име што го разбирате за новото поле.
• Опис - изборен.
• Формула – најважното, бидејќи овде или рачно внесуваме, или кликнуваме на копчето fx и изберете функција DAX од списокот, која треба да го пресмета резултатот кога ќе ја фрлиме нашата мерка во областа на вредности.
• Во долниот дел од прозорецот, можете веднаш да го поставите форматот на бројот за мерката во списокот категорија.
  

Јазикот DAX не е секогаш лесен за разбирање бидејќи работи не со поединечни вредности, туку со цели колони и табели, односно бара одредено преструктуирање на размислувањето по класичните формули на Excel. Сепак, вреди, бидејќи моќта на неговите способности во обработката на големи количини на податоци е тешко да се прецени.

Придобивка 4: Прилагодени хиерархии на полиња

Често, кога креирате стандардни извештаи, треба да ги фрлите истите комбинации на полиња во стожерните табели во дадена секвенца, на пример Година-четврт-месец-денили Категорија-производили Земја-Град-Клиент итн. Во резимето на моделот на податоци, овој проблем лесно се решава со креирање на свој хиерархии — прилагодени групи на полиња.

Во прозорецот Power Pivot, префрлете се на режим на графикон со копчето Приказ на графикон табот Почетна (Дома - Приказ на дијаграм), изберете со Ctrl саканите полиња и кликнете со десното копче на нив. Контексното мени ќе ја содржи командата Креирај хиерархија (Креирај хиерархија):

Создадената хиерархија може да се преименува и да се влече во неа со глувчето бараните полиња, така што подоцна во едно движење тие можат да бидат фрлени во резимето:

Придобивка 5: Прилагодени матрици

Продолжувајќи ја идејата од претходниот пасус, во резимето на моделот на податоци, можете исто така да креирате свои групи на елементи за секое поле. На пример, од целата листа на градови, можете лесно да направите збир само од оние што се во вашата област на одговорност. Или соберете ги само вашите клиенти, вашата стока итн. во посебен сет.

За да го направите ова, на јазичето Анализа на стожерна табела во паѓачката листа Полиња, ставки и множества има соодветни команди (Анализирај - Филдс, ИTems & Sets — Создадете множество врз основа на ставки од редови/колона):

Во прозорецот што се отвора, можете селективно да ја отстраните, додадете или менувате позицијата на кој било елемент и да го зачувате добиениот сет под ново име:

Сите креирани множества ќе бидат прикажани во панелот Полиња на PivotTable во посебна папка, од каде што може слободно да се влечат во областите на редови и колони на која било нова PivotTable:

Придобивка 6: Селективно сокријте табели и колони

Иако ова е мала, но многу пријатна предност во некои случаи. Со десен клик на името на полето или на јазичето на табелата во прозорецот Power Pivot, можете да ја изберете командата Скриј од Комплет со алатки за клиенти (Скриј од Алатки за клиенти):

Скриената колона или табела ќе исчезнат од окното Список на полиња на PivotTable. Многу е погодно ако треба да скриете од корисникот некои помошни колони (на пример, пресметани или колони со клучни вредности за создавање врски) или дури и цели табели.

Бенефит 7. Напредно вежбање

Ако кликнете двапати на која било ќелија во областа на вредности во редовна стожерна табела, тогаш Excel прикажува на посебен лист копија од изворниот фрагмент на податоци што бил вклучен во пресметката на оваа ќелија. Ова е многу корисна работа, официјално наречена Drill-down (во обично велат „не успеваат“).

Во резимето на моделот на податоци, оваа практична алатка работи посуптилно. Со стоење на која било ќелија со резултат што нè интересира, можете да кликнете на иконата со лупа што се појавува до неа (се вика Експрес трендови) и потоа изберете кое поле што ве интересира во која било поврзана табела:

После тоа, тековната вредност (Модел = Explorer) ќе влезе во областа на филтерот, а резимето ќе биде изградено од канцелариите:

Се разбира, таквата постапка може да се повтори многу пати, доследно навлегувајќи во вашите податоци во насоката за која сте заинтересирани.

Придобивка 8: Претворете ги функциите на Pivot во коцка

Ако изберете која било ќелија во резимето за моделот на податоци и потоа изберете на картичката Анализа на стожерна табела Команда Алатки OLAP - Претворете во формули (Анализа - Алатки OLAP - Претворете во формули), тогаш целото резиме автоматски ќе се претвори во формули. Сега вредностите на полето во областа на ред-колона и резултатите во областа на вредности ќе бидат преземени од моделот на податоци со помош на специјалните функции на коцка: CUBEVALUE и CUBEMEMBER:

Технички, тоа значи дека сега не се занимаваме со резиме, туку со неколку ќелии со формули, односно лесно можеме да направиме какви било трансформации со нашиот извештај што не се достапни во резимето, на пример, да вметнеме нови редови или колони во средината. на извештајот, направете дополнителни пресметки во резимето, распоредете ги на кој било посакуван начин итн.

Во исто време, врската со изворните податоци, секако, останува и во иднина овие формули ќе се ажурираат кога ќе се променат изворите. Убавината!

• Анализа на план-факти во стожерна табела со Power Pivot и Power Query
• Стожерна табела со повеќелиниско заглавие
• Направете база на податоци во Excel користејќи Power Pivot