Предности Пивот према моделу података

Када правимо изведену табелу у Екцел-у, у првом дијалогу, где се од нас тражи да поставимо почетни опсег и изаберемо место за уметање пивот табеле, испод је неупадљиво, али веома важно поље за потврду – Додајте ове податке у модел података (Додајте ове податке на модел података) и, мало више, прекидач Користите модел података ове књиге (Користите модел података ове радне свеске):

Нажалост, многи корисници који су већ дуже време упознати са пивот табелама и успешно их користе у свом раду понекад заиста не разумеју значење ових опција и никада их не користе. И узалуд. На крају крајева, креирање пивот табеле за модел података даје нам неколико веома важних предности у поређењу са класичном Екцел пивот табелом.

Међутим, пре него што изблиза размотримо ове „земље“, хајде да прво разумемо шта је, у ствари, овај модел података?

Шта је модел података

Модел података (скраћено као МД или ДМ = Модел података) је посебна област унутар Екцел датотеке где можете да складиштите табеларне податке – једну или више табела повезаних, ако желите, једна са другом. У ствари, ово је мала база података (ОЛАП коцка) уграђена у Екцел радну свеску. У поређењу са класичним складиштењем података у облику регуларних (или паметних) табела на листовима самог Екцел-а, модел података има неколико значајних предности:

• Столови могу бити до 2 милијарде линија, а Екцел лист може да стане нешто више од милион.
• Упркос гигантској величини, обрада оваквих табела (филтрирање, сортирање, прорачуни на њима, резиме зграде итд.) се обавља веома брзо Много брже од самог Екцел-а.
• Са подацима у Моделу можете извршити додатне (по жељи, веома сложене) прорачуне користећи уграђени ДАКС језик.
  
• Све информације учитане у модел података су веома јако стиснут користећи посебан уграђени архиватор и прилично умерено повећава величину оригиналне Екцел датотеке.
  

Моделом управља и израчунава посебан додатак уграђен у Мицрософт Екцел – ПоверПивото чему сам већ писао. Да бисте то омогућили, на картици развијач клик ЦОМ додаци (Програмер — ЦОМ додаци) и означите одговарајуће поље:

Ако табс развијач (Програмер)не можете га видети на траци, можете га укључити кроз Датотека – Опције – Подешавање траке (Датотека — Опције — Прилагоди траку). Ако у прозору приказаном изнад на листи ЦОМ додатака немате Повер Пивот, онда он није укључен у вашу верзију Мицрософт Оффице-а 🙁

На картици Повер Пивот која се појави биће велико светло зелено дугме управљање (Управљати), кликом на који ће се отворити прозор Повер Пивот на врху Екцел-а, где ћемо видети садржај модела података тренутне књиге:

Важна напомена на путу: Екцел радна свеска може да садржи само један модел података.

Учитајте табеле у модел података

Да бисмо учитали податке у модел, прво претварамо табелу у динамичку „паметну“ пречицу на тастатури Цтрл+T и дајте му пријатељско име на картици Конструктор (Дизајн). Ово је обавезан корак.

Затим можете да користите било коју од три методе за избор:

• притисните дугме Додај у модел (Додај у модел података) табулатор ПоверПивот табулатор Početna (Кућа).
  
• Бирање тимова Уметање – заокретна табела (Инсерт — Пивот Табле) и укључите поље за потврду Додајте ове податке у модел података (Додајте ове податке у модел података). У овом случају, према подацима учитаним у Модел, одмах се прави и пивот табела.
  
• На картици Напредно Датум (Датум) кликните на дугме Из табеле/опсега (Из табеле/опсега)да учитамо нашу табелу у Повер Куери едитор. Овај пут је најдужи, али, по жељи, овде можете извршити додатно чишћење, уређивање и све врсте трансформација, у чему је Повер Куери веома јак.

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

Правимо резиме модела података

Да бисте направили сажети модел података, можете користити било који од три приступа:

• притиснути дугме збирна табела (Ротациона табела) у прозору Повер Пивот.
• Изаберите команде у програму Екцел Уметање – заокретна табела и пребаците у режим Користите модел података ове књиге (Инсерт — Пивот Табле — Користите модел података ове радне свеске).
• Бирање тимова Уметање – заокретна табела (Инсерт — Пивот Табле) и укључите поље за потврду Додајте ове податке у модел података (Додајте ове податке у модел података). Тренутна „паметна“ табела ће бити учитана у модел и биће направљена сумарна табела за цео модел.

Сада када смо схватили како да учитамо податке у модел података и направимо резиме на основу тога, хајде да истражимо предности и предности које нам ово даје.

Предност 1: Релације између табела без употребе формула

Редовни резиме може да се направи само коришћењем података из једне изворне табеле. Ако имате неколико њих, на пример, продају, ценовник, именик купаца, регистар уговора итд., онда ћете прво морати да прикупите податке из свих табела у једну користећи функције као што је ВЛООКУП (ВЛООКУП), ИНДЕКС (ИНДЕКС), ИЗЛОЖЕНИЈИ (УТАКМИЦА), СУММЕСЛИМН (СУМИФС) и слично. Ово је дуго, заморно и доводи ваш Екцел у „мисао“ са великом количином података.

У случају резимеа модела података, све је много једноставније. Довољно је једном подесити односе између табела у прозору Повер Пивот – и готово. Да бисте то урадили, на картици ПоверПивот притисните дугме управљање (Управљати) а затим у прозору који се појави – дугме Приказ графикона (Приказ дијаграма). Остаје да превучете уобичајена (кључна) имена колона (поља) између табела да бисте креирали везе:

Након тога, у резимеу за модел података, можете убацити у област сажетка (редови, колоне, филтери, вредности) било која поља из било које повезане табеле – све ће бити повезано и аутоматски израчунато:

Предност 2: Бројите јединствене вредности

Уобичајена стожерна табела нам даје могућност да изаберемо једну од неколико уграђених функција израчунавања: збир, просек, број, минимум, максимум, итд. У резимеу модела података, овој стандардној листи је додата веома корисна функција за бројање број јединствених (непоновљивих вредности). Уз његову помоћ, на пример, можете лако да пребројите број јединствених артикала робе (програм) које продајемо у сваком граду.

Кликните десним тастером миша на поље – команду Опције поља вредности и на картици операција Изаберите Број различитих елемената (различити број):

Предност 3: Прилагођене ДАКС формуле

Понекад морате да извршите разне додатне прорачуне у изведеним табелама. У редовним резимеима, ово се ради помоћу израчунатих поља и објеката, док резиме модела података користи мере у посебном ДАКС језику (ДАКС = Изрази анализе података).

Да бисте креирали меру, изаберите на картици ПоверПивот команда Мере – Креирајте меру (Мере — Нова мера) или само кликните десним тастером миша на табелу на листи Пивот Фиелдс и изаберите Додајте меру (Додај меру) у контекстуалном менију:

У прозору који се отвори поставите:

• Назив табелегде ће створена мера бити смештена.
• Назив мере – било које име које разумете за ново поље.
• Opis - опционо.
• Формула – најважније, јер овде или ручно уносимо, или кликнемо на дугме fx и изаберите ДАКС функцију са листе, која би требало да израчуна резултат када затим бацимо нашу меру у област вредности.
• У доњем делу прозора можете одмах подесити формат броја за меру у листи Категорија.
  

ДАКС језик није увек лак за разумевање јер не оперише са појединачним вредностима, већ са целим колонама и табелама, односно захтева извесно реструктурирање размишљања према класичним Екцел формулама. Међутим, вреди, јер је моћ његових могућности у обради великих количина података тешко преценити.

Корист 4: Прилагођене хијерархије поља

Често, када креирате стандардне извештаје, морате да убаците исте комбинације поља у изведене табеле у датом низу, нпр. Година-Кварт-Месец-Дан, Или Категорија-Производ, Или Држава-Град-Клијент итд. У резимеу модела података, овај проблем се лако решава креирањем сопственог хијерархије — прилагођени сетови поља.

У прозору Повер Пивот пређите на режим графикона помоћу дугмета Приказ графикона табулатор Početna (Почетна — Приказ дијаграма), изаберите са Цтрл жељена поља и кликните десним тастером миша на њих. Контекстни мени ће садржати команду Креирајте хијерархију (Креирајте хијерархију):

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

Предност 5: Прилагођене шаблоне

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

Да бисте то урадили, на картици Анализа заокретне табеле у падајућој листи Поља, ставке и скупови постоје одговарајуће команде (Анализа — Филдс, Итемс & Сетс — Креирајте скуп на основу ставки реда/колоне):

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

Сви креирани скупови ће бити приказани на панелу Поља изведене табеле у посебној фасцикли, одакле се могу слободно превлачити у области редова и колона било које нове изведене табеле:

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

Иако је ово мала, али у неким случајевима веома пријатна предност. Десним кликом на назив поља или на картицу табеле у прозору Повер Пивот можете изабрати команду Сакриј из Цлиент Тоолкита (Сакриј од клијентских алатки):

Скривена колона или табела ће нестати из окна листе поља заокретне табеле. Веома је згодно ако морате да сакријете од корисника неке помоћне колоне (на пример, израчунате или колоне са кључним вредностима за креирање односа) или чак целе табеле.

Предност 7. Напредна анализа

If you double-click on any cell in the value area in a regular pivot table, then Excel displays on a separate sheet a copy of the source data fragment that was involved in the calculation of this cell. This is a very handy thing, officially called Drill-down (in they usually say “fail”).

У резимеу модела података, овај згодан алат ради суптилније. Ако станете на било коју ћелију са резултатом који нас занима, можете кликнути на икону са лупом која искаче поред ње (зове се Екпресс Трендс), а затим изаберите било које поље које вас занима у било којој повезаној табели:

Након тога, тренутна вредност (Модел = Екплорер) ће отићи у област филтера, а резиме ће направити канцеларије:

Наравно, такав поступак се може поновити много пута, доследно удубљујући се у своје податке у правцу који вас занима.

Корист 8: Претворите Пивот у коцке функције

Ако изаберете било коју ћелију у резимеу за модел података, а затим изаберете картицу Анализа заокретне табеле команда ОЛАП алати – Претвори у формуле (Анализа — ОЛАП алати — Претвори у формуле), онда ће цео резиме бити аутоматски конвертован у формуле. Сада ће вредности поља у области ред-колона и резултати у области вредности бити преузете из модела података помоћу посебних функција коцке: ЦУБЕВАЛУЕ и ЦУБЕМЕМБЕР:

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

Истовремено, веза са изворним подацима, наравно, остаје иу будућности ће се ове формуле ажурирати када се извори промене. Лепота!

• План-факт анализа у пивот табели са Повер Пивот-ом и Повер Куери-јем
• Заокретна табела са вишелинијским заглављем
• Креирајте базу података у Екцел-у користећи Повер Пивот