Предимства на Pivot by Data Model

Когато създаваме обобщена таблица в Excel, в първия диалогов прозорец, където сме помолени да зададем началния диапазон и да изберем място за вмъкване на обобщената таблица, има незабележимо, но много важно квадратче за отметка по-долу – Добавете тези данни към модела на данни (Добавете тези данни към модел на данни) и малко по-нагоре превключвателят Използвайте модела на данните на тази книга (Използвайте модела на данни от тази работна книга):

За съжаление много потребители, които са запознати с обобщените таблици от дълго време и успешно ги използват в работата си, понякога не разбират наистина значението на тези опции и никога не ги използват. И напразно. В крайна сметка създаването на обобщена таблица за модела на данни ни дава няколко много важни предимства в сравнение с класическата обобщена таблица на Excel.

Въпреки това, преди да разгледаме тези „кифлички“ отблизо, нека първо разберем какво всъщност представлява този модел на данни?

Какво е модел на данни

Модел на данни (съкратено като MD или DM = Data Model) е специална област във файл на 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), ИНДЕКС (ИНДЕКС), ПО-ИЗЛОЖЕН (СЪВПАДА), SUMMESLIMN (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: Персонализирани шаблони

Продължавайки идеята от предишния параграф, в резюмето на модела на данни можете също да създадете свои собствени набори от елементи за всяко поле. Например, от целия списък с градове можете лесно да направите набор само от тези, които са във вашата зона на отговорност. Или съберете само вашите клиенти, вашите стоки и т.н. в специален комплект.

За да направите това, в раздела Анализ на обобщена таблица в падащия списък Полета, елементи и набори има съответните команди (Анализиране - Фийлдс, Items & Sets — Създаване на набор въз основа на елементи от ред/колона):

В прозореца, който се отваря, можете избирателно да премахвате, добавяте или променяте позицията на елементи и да запазвате получения набор под ново име:

Всички създадени набори ще бъдат показани в панела с полета на обобщена таблица в отделна папка, откъдето могат свободно да се плъзгат към областите на редовете и колоните на всяка нова обобщена таблица:

Предимство 6: Избирателно скриване на таблици и колони

Въпреки че това е малко, но много приятно предимство в някои случаи. Като щракнете с десния бутон върху името на полето или върху раздела таблица в прозореца на Power Pivot, можете да изберете командата Скриване от Client Toolkit (Скриване от клиентските инструменти):

Скритата колона или таблица ще изчезне от прозореца на списъка с полета на обобщена таблица. Много е удобно, ако трябва да скриете от потребителя някои помощни колони (например изчислени или колони с ключови стойности за създаване на връзки) или дори цели таблици.

Полза 7. Усъвършенствана разбивка

Ако щракнете двукратно върху която и да е клетка в областта на стойността в обикновена обобщена таблица, тогава Excel показва на отделен лист копие на фрагмента на изходните данни, който е участвал в изчислението на тази клетка. Това е много удобно нещо, официално наречено Drill-down (обикновено казват „fail“).

В обобщението на модела на данни този удобен инструмент работи по-фино. Като застанете на която и да е клетка с резултата, който ни интересува, можете да щракнете върху иконата с лупа, която изскача до нея (нарича се Експресни тенденции) и след това изберете всяко поле, което ви интересува, във всяка свързана таблица:

След това текущата стойност (Модел = Explorer) ще отиде в областта на филтъра и обобщението ще бъде изградено по офиси:

Разбира се, такава процедура може да се повтори многократно, като последователно се задълбочавате във вашите данни в посоката, която ви интересува.

Полза 8: Преобразувайте Pivot в Cube функции

Ако изберете която и да е клетка в резюмето за модела на данни и след това изберете в раздела Анализ на обобщена таблица Команда OLAP инструменти – Преобразуване във формули (Анализиране — OLAP инструменти — Преобразуване във формули), тогава цялото резюме ще бъде автоматично преобразувано във формули. Сега стойностите на полето в областта ред-колона и резултатите в областта на стойността ще бъдат извлечени от модела на данни с помощта на специалните функции на куба: CUBEVALUE и CUBEMEMBER:

Технически това означава, че сега не се занимаваме с резюме, а с няколко клетки с формули, т.е. можем лесно да направим всякакви трансформации с нашия отчет, които не са налични в резюмето, например да вмъкнем нови редове или колони в средата на отчета, правете допълнителни изчисления вътре в резюмето, подреждайте ги по желания начин и т.н.

В същото време връзката с изходните данни, разбира се, остава и в бъдеще тези формули ще бъдат актуализирани, когато източниците се променят. Красотата!

• Анализ на план-факт в обобщена таблица с Power Pivot и Power Query
• Пивотна таблица с многоредова заглавка
• Създайте база данни в Excel с помощта на Power Pivot