Power Pivot – преглед и обучение. Power Pivot: Функции на прозорец с DAX сос Какво е powerpivot в excel

Power Pivot е добавка, която може да се използва за извършване на задълбочен анализ в Excel. Добавката е вградена в някои версии на Office, но не е активирана по подразбиране.

За списък с версии на Office, включително Power Pivot, както и списък с версии, които не се препоръчват, вижте статията: Къде е PowerPivot?

По-долу е описано как да активирате Power Pivot, преди да го използвате за първи път.

    Отидете в раздела Файл > Настроики > Добавки.

    В полето контролизберете COM добавкии натиснете Отивам.

    Поставете отметка в квадратчето Microsoft Office Power Pivotи натиснете бутона Добре. Ако са инсталирани други версии на Power Pivot, те също ще бъдат посочени в списъка с COM добавки. Изберете добавката Power Pivot за Excel.

Разделът Power Pivot се появява на лентата.

Отворете прозореца на Power Pivot.

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

Отстраняване на неизправности: лентата на Power Pivot изчезва

В някои случаи лентата на Power Pivot ще се появи в менюто, ако Excel открие, че добавката за стабилност е в Microsoft Excel. Това може да се случи, ако Excel се срине, докато прозорецът на Power Pivot е отворен. За да възстановите менюто на Power Pivot, направете следното:

    Изберете Файл > Настроики > Добавки.

    В полето контролизберете Обекти с увреждания > Отивам.

    Изберете Microsoft OfficePower Pivotи натиснете бутона Включи.

Ако не можете да възстановите лентата на Power Pivot, като използвате стъпките по-горе, или лентата изчезва, когато затворите и отворите отново Excel, направете следното:

    затворете Excel;

    отворете менюто Започнете > Изпълнии въведете командата regedit;

    В редактора на системния регистър разгънете следния раздел:

    За Excel 2013: HKEY_CURRENT_USER > Софтуер > Microsoft > офис > 15.0 > Потребителски настройки.

    За Excel 2016: HKEY_CURRENT_USER > Софтуер > Microsoft > офис > 16.0 > Потребителски настройки

    Кликнете с десния бутон PowerPivotExcelAddinи след това щракнете Изтрий;

    върнете се в горната част на редактора на системния регистър;

    разширете секцията HKEY_CURRENT_USER > Софтуер > Microsoft > офис > Excel > Добавки;

    Кликнете с десния бутон PowerPivotExcelClientAddIn.NativeEntry.1и след това щракнете Изтрий;

    затворете редактора на системния регистър;

    отворете Excel;

    Активирайте добавката, като следвате стъпките в началото на тази статия.

Следващите няколко раздела разглеждат примери за това как да използвате файла ChlOWatherMashup.xlsx, който съдържа информация за ежедневните продажби и набор от данни за точката на продажба. Компанията продава продукти в супермаркета и на летището. Двете търговски обекта са на по-малко от 10 мили едно от друго, но показват различни тенденции в продажбите.

С помощта на уеб заявка и макрос информацията за времето се зарежда за всеки ден в продължение на три години. Обсъждат се примери за DAX мерки, които са комбинация от прогнози за времето и данни за продажбите. Тази комбинация ни позволява да определим връзката между времето и обема на продажбите.

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

  • WeekdayName, който използва формулата =FORMAT(Sales,"dddd") за преобразуване на дата в ден от седмицата.

    • Много потребители изразяват разочарование, когато открият, че обобщените таблици на PowerPivot не поддържат персонализирани списъци, които показват дни от седмицата, и трябва да създадат изчислена колона WeekdayID.

  • Изчисляваната колона WeekdaylD използва функцията =WEEKDAY(Sales,2). Аргумент 2 има същото значение като съответния аргумент във функцията на Excel. По-специално, той номерира дните от седмицата по такъв начин, че на понеделник се присвоява стойност 1, на вторник - 2 и т.н. Неделя получава стойност 7. Таблицата Weekday, състояща се от 7 реда, картографира дните от седмицата (от понеделник до неделя включително) и числата (от 1 до 7 включително). В резултат на това имената на дните от седмицата се показват правилно.
  • Изчислената колона LocationDays съдържа формулата CONCATENATE(Sales,Sales). Тази формула се използва по-късно при изчисляване на различните числа за дните, през които се извършват продажби.

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

Изчисляваната колона LocationWeek използва следната формула: =Свързване(Продажби,Продажби). Обърнете внимание, че всички изчислени колони, изброени по-горе, могат да препращат към други изчисляеми колони. Тази изчислена колона се свързва с таблица, която съдържа информация за персонала.

Много потребители смятат, че тъй като PowerPivot разбира връзките между таблиците, които съдържат информация за продажбите и персонала, те могат да създадат формула като =Продажби/Набиране на персонал (Фигура 10.25). Но, за съжаление, при изпълнение на тази формула се появява съобщение за грешка.

Причината за тази грешка е, че изчислението се опитва да раздели стойността 2202 в колоната Нетни продажби на стойностите в колоната Ниво на персонала в таблицата с персонала. За да разрешите този проблем, използвайте функцията Related). Пренапишете формулата като =Sales/Related(Staffing). Функцията Related() казва на DAX да раздели 2202 не на всичките 14 стойности в колоната Staff Level от таблицата Staffing, а само на една стойност, която е свързана с AirportSunday.

На фиг. Фигура 10.26 показва резултата от прилагането на тази формула. При разглеждането на първата линия стигаме до извода, че двама души работят в търговския обект, който се намира на летището. Следователно обемът на продажбите на човек е половината от стойността на 2202, т.е. 1101. Във вторник в магазина работи един човек, така че стойността в колоната SalesPerPerson е същата като стойността в колоната Нетни продажби.

Можете да създадете интересни примери за обобщени таблици, като използвате изчислени колони и релации.

На фиг. Фигура 10.27 показва резултатите от анализ на продажбите по дни от седмицата в два търговски обекта. Моля, имайте предвид, че пиковите разпродажби в търговския център са в събота, а на летището в неделя, когато много хора отиват в командировки и купуват всичко необходимо за предстоящата работна седмица. Обърнете внимание на процентите в редовете (Фигура 10.27). Анализирайки тези съотношения, можем да заключим, че стойностите на продажбите в понеделник и петък са почти идентични.

Продължавайки да извършваме анализ на данни, получаваме резултати, близки до очакваните. На фиг. Фигура 10.28 демонстрира зависимостта на обема на продажбите от това дали е валял дъжд и колко силен е бил. Ако вашите търговски обекти се намират във Флорида, където практически няма дъжд, подобен анализ едва ли ще бъде полезен на практика.

Ако трябва да изчислявате нивото на продажби за всеки търговски обект ежедневно, създайте нови мерки с помощта на DAX.

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

Какво е Power Pivot

Excel Power Pivot е инструмент, който позволява на потребителите да създават свои собствени системи за бизнес разузнаване с помощта на Excel - базирани на таблични бази данни.

Като правило, обобщените таблици, първоначално вградени в Excel, не изискват специални познания и са незаменими, ако имате нужда от визуален анализ в Excel. Книгите ви позволяват да съхранявате данни в отделна таблица, ако желаете. Освен това Excel поддържа оператори за работа с масиви от данни - много полезен инструментариум за анализи.

Добавката Excel Power Pivot работи само в Office 2010 и по-нова версия и има редица функции:

  • извършва всички изчисления директно в RAM на компютъра, което позволява висока производителност на системата, както и възможност за обработка на големи количества данни (размерът на изходните таблици може да достигне милиони редове);
  • ви позволява да създавате обобщени таблици на вашата база. Стойността на приложението на Excel PowerPivot за финансисти и анализатори се състои в това, че с помощта на добавката вече можете самостоятелно да обработвате такива обеми информация, които преди това изискваха използването на специализиран софтуер (СУБД) и участието на квалифицирани ИТ специалисти ;
  • съдържа език за изразяване на анализ на данни (Data Analysis eXpression, съкратено DAX), предназначен за разработване на нови правила за бизнес логика. Просто казано, може да се счита за разширена версия на формулите за масиви. Формулите на езика DAX са специално проектирани да бъдат възможно най-близо до синтаксиса на Excel. Въпреки че са подобни на стандартните функции на работен лист на Excel, DAX формулите се записват само в обвивката на Power Pivot. Езикът DAX има широк набор от възможности и информация за различни начини за използването му е достъпна в Интернет.

Както показва практиката, популярността на дадена технология често зависи не от нивото и разнообразието от проблеми, решени с нейна помощ, а от това колко ефективно и просто можете да преминете към нея, като използвате вашите знания и опит. Тази статия разглежда схеми за създаване на същия индикатор от областта на управленските отчети - първо на базата на формули на Excel, а след това с помощта на DAX команди. Това ще гарантира, че работата с Excel Power Pivot е почти същата като работата с обикновени електронни таблици на Excel.

Като пример, нека изберем индикатора „Ниво на месечен клиентски доход“, който ни позволява да класираме контрагентите на компанията по степен на рентабилност. С негова помощ става ясно какъв дял от общите приходи идва от клиенти от различни видове, тяхното количествено разпределение в групи, динамиката на промените в клиентската база и т.н. Въпреки това, изчисляването на такъв показател е изпълнено с определени трудности:

  1. В рамките на един календарен период клиентът може да използва няколко услуги наведнъж (да закупи няколко стоки) - следователно, за да се изчисли общото ниво на рентабилност, е необходимо да се сумират приходите, получени от всички услуги, предоставени на клиента през анализирания период;
  2. В зависимост от размера на изчисления доход, клиентът трябва да бъде причислен към една от предварително определените групи.

Като се вземат предвид особеностите на условията за изчисление, индикаторът „Ниво на рентабилност“ ще бъде добър пример за изучаване на логиката на изпълнение на команди, която е в основата на езика DAX.

справка

Използване на формули за масиви

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

Нека да разгледаме израза (виж фиг. 1). Неговите входни параметри са два диапазона от клетки: „Ред“, маркиран в жълто, и „Колона“ със синьо запълване. В областта, ограничена от тези диапазони, се въвежда формула за масив със следната форма: (=Ред Колона).

Тази формула е компактно представяне на операцията по добавяне на двойки елементи от оригиналните масиви. Както може да се види от фигурата, той има еднакъв правопис за всички елементи на диапазона от обхвата. В този случай стойностите в клетките на получената таблица се различават една от друга, което се обяснява с контекста на изпълнение. Например клетка E4, маркирана на фигурата, се намира в третата колона и втория ред на масива, което съответства на втория елемент от масива Row и третия елемент от масива Column.

Описаният начин на организиране на изчисленията не изглежда съвсем познат, тъй като във формулата няма изрично указание кои клетки трябва да бъдат избрани за добавяне. Следователно овладяването на тази техника отнема известно време. Но след като научи тази техника, потребителят ще може да създава изрази за многоизмерна OLAP среда. По този начин основните възможности на програмата Excel се разширяват чрез добавяне на аналитични изчисления, базирани на синтаксиса на формулите в работния лист.

Работа със стандартни формули на Excel

Да приемем, че имаме на разположение отчет за приходите на фирма, детайлизиран от трима анализатори (контрагенти, видове услуги и календарни периоди).

На фигура 2 изходните данни са представени в колоните „Компания“, „Услуга“, „Период“, „Приходи“. Искаме да създадем две нови изчисляеми колони “Revenue_Amount”, където се изчислява общият доход за клиент за определен период, както и “Revenue_Level”, която в зависимост от нивото на “Revenue_Amount” определя групата доходи (доход нивата са посочени от дясната страна на фиг. 2).

За удобство и яснота на последващите изчисления незабавно преобразуваме изходните данни в режим „Таблици“. За да направим това, ще използваме съответната команда в раздела „Вмъкване“ и ще й дадем нашето собствено име „Fact_Table“.

Нашата цел. За всеки ред от таблицата с факти изчислете общия доход, който съответства на клиента и месеца. Например на „Клиент А” през февруари 2013 г. са предоставени три услуги в размер на 15, 15 и 25 единици. Следователно във всеки от редовете, които се отнасят за даден период, трябва да се въведе една и съща стойност - 55 единици (15 + 15 + 25). Тази операция се извършва на два етапа.

Етап 1.Сред всички редове на таблицата с факти се филтрират онези, чиито стойности на атрибути „Компания“ и „Период“ съответстват на текущия ред.

Етап 2.Стойностите на атрибута „Доход“ в получения филтриран набор се сумират.

Функцията SUMIFS е много подходяща за тази задача, като ви позволява да сумирате диапазони в няколко условия едновременно. Основният въпрос е как да въведете тази формула в изчисляема колона. В крайна сметка в таблиците всяка формула трябва да бъде посочена за всички редове наведнъж. Трябва да дефинираме само един, но такъв, че неговият контекст на изпълнение да се променя в зависимост от параметрите на активния ред.

Нека въведем израз 1 в някой от редовете на колоната "Income_Amount" Просто натиснете Enter, след което той ще се побере във всички редове на таблицата.

Израз 1:
SUMIFS([Приходи]; [Компания]; Факт_Таблица[[#Този ред];[Компания]]; [Период]; Факт_Таблица[[#Този ред]; [Период]])

Нека да разгледаме как работи формулата. Всеки път, когато извиквате (във всеки ред) функцията SUMIFS, цялата таблица с факти (нейната колона „Приходи“) се използва наведнъж. Но при всяка итерация се сумират само онези редове, които съответстват на стойностите на атрибута на текущия ред. За да направите това, към формулата е добавен специален аргумент - [# Този ред], който действа като връзка към текущия ред. По-специално, за редове, свързани с „Клиент, A“ и февруари 2013 г., стойността на параметъра [[#This row];[Company]] ще бъде равна на „Client, A“ и [[#This row]; [Период]] - „01.02.2013 г.“.

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

Имайте предвид, че представеният формат на запис се различава значително от стандартния режим на Excel, където входните параметри са изрично посочени във формули. Например, можем да конвертираме таблица с факти обратно в нормален диапазон (Преобразуване в диапазон в раздела Инструменти за таблици). Тогава, например, израз 1 за ред 5 ще приеме формата на израз 2:

Израз 2:
=SUMIFS(Регулярен_диапазон!$D$2:$D$37; Редовен_диапазон!$A$2:$A$37; Редовен_диапазон!$A5; Редовен_диапазон!$C$2:$C$37; Редовен_диапазон!$C5)

За ред 6 изразът ще изглежда идентичен, с единствената разлика, че вместо $A5 ще има $A6 (а вместо $C5 - $C6). В този случай резултатът от изчисляването на израз 2 съвпада със стойността на израз 1 в съответния ред, което ни позволява да ги считаме за идентични.

Сега трябва да сортираме месечните доходи на клиентите по групи. Въз основа на интегралния показател за рентабилност, изчислен по-рано, трябва да изберем съответния ред във външна (спрямо таблицата с факти) таблица. Тази операция е лесна за изпълнение в Excel, защото има много оператори за работа с масиви и диапазони.

По-специално, функцията INDEX ни позволява лесно да получим стойност от двуизмерен масив във всяка клетка на работната книга; трябва само да знаем идентификатора на съответния обект. Нека напишем израз 3 в колоната „Income_Level“:

Израз 3:
=ИНДЕКС(Нива[Ниво]; MATCH(Таблица_факти[[#Този ред]; [Сума_приходи]]; Нива[Начало]))

Формулата, представена в израз 3, подобно на израз 1, се изпълнява в текущия контекст на таблицата с факти - тя използва препратка към активния ред. Освен това с помощта на функцията INDEX получаваме достъп до външен обект - таблицата „Нива“. В него чрез функцията SEARCH се търси подходящ низ.

Нека отбележим още веднъж как успяхме да получим данни от външна таблица: избрахме подходяща функция и посочихме идентификатора на желания обект в нея като аргумент.

Работа с Excel Power Pivot

Нека да разгледаме как Power Pivot изпълнява операциите, описани в първата част на статията.

Инсталиране на Excel Power Pivot.Първо, добавката трябва да бъде инсталирана (можете да я изтеглите безплатно от официалния уебсайт на Microsoft). Нов раздел „Power Pivot“ трябва да се появи на лентата на Excel. Ако добавката не е активирана веднага, трябва да я активирате ръчно: „Опции на Excel“ - „Добавки“, в списъка „Управление“ изберете „COM добавки“ - „Отиди“. Всичко, което остава, е да поставите отметка в квадратчето до „Power Pivot за Excel“. Прозорецът е активен само в документи във формат xlsx.

Подготовка на изходни данни. Базата данни PowerPivot поддържа голямо разнообразие от източници. По-специално, можете да заредите данни в него от MS Access или SQL Server DBMS. В този случай обемът на обработената информация може да достигне милиони редове. Но за обикновените потребители възможността за съхраняване на изходна информация на листове в обикновена работна книга на Excel е по-ценна.

В примера, който разглеждаме в статията, се използват само две таблици - таблица с факти, както и малка спомагателна справочна таблица с нива на рентабилност. За да ги добавите към модела на Power Pivot, трябва да отговаряте само на две условия:

1. Поставете таблиците на отделни листове на книгата.Имайте предвид, че това изискване се различава от стандартния режим на работа на Excel, който позволява поставянето на неограничен брой таблици, описващи различни обекти на един лист.

2. Съхранявайте таблиците отделно от файла с обобщен отчет.Ако е необходимо, таблици с данни могат дори да бъдат поставени в различни работни книги на Excel - Power Pivot може да поддържа множество паралелни отворени връзки към източници на данни от определен тип.

Когато копирате изходни данни в нови листове, се препоръчва незабавно да присвоите информативни имена на последните. Когато впоследствие импортирате данни в Power Pivot, оригиналните имена на обекти стават имена на съответните таблици в модела.

След това създайте нова работна книга на Excel, която ще наречем „Оригинални данни“. Нека поставим таблица с първоначални данни на един лист, наречен „Table_Facts“, а на друг лист „Нива“ - референтна таблица с нива на доходност.

Сега нека създадем друга работна книга на Excel, да извикаме прозореца на PowerPivot в нея и да импортираме данни от файла „Оригинални данни“ в нея. За да импортирате данни, трябва да извикате командата „Получаване на външни данни от други източници“ в менюто „Файл“ и след това тип „Файл на Excel“, стартирайки съветника за импортиране на данни.

На първия етап от работата му ние посочваме физическото местоположение на файла с данни.Препоръчваме незабавно да активирате опцията „Използване на първия ред за заглавия на колони“ - тогава колоните на PowerPivot автоматично ще получат имената на колоните от работната книга на Excel.

На втория етап избираме кои таблици ще се използват в модела.В нашия случай трябва да изберем и двете.

В резултат на извършените операции изходните таблици трябва да се появят в прозореца на Power Pivot (вижте фиг. 3). Можете да превключвате между тях, като изберете съответния раздел в долния ляв ъгъл на прозореца.

Във всяка таблица можете да създадете два типа обекти: изчисляеми колони и изчисляеми полета.

Изчисляваните колони действат като измерения в обобщена таблица – те съдържат стойности, които след това се поставят в областите на реда или колоната на оформлението на отчета.

Изчисляваните полета - мерки, тоест обобщени индикатори, които се поставят в работното пространство на отчета - се дефинират в областта за изчисление; в прозореца за изглед на данни на Power Pivot тази област се поставя под нивото на данните в таблицата.

В тази статия няма да засягаме проблемите със създаването на потребителски полета, а само ще разгледаме добавянето на нови изчисляеми колони към модела. В Power Pivot те действат като пълен аналог на изчисляемите колони в таблиците на Excel. По-специално, за всички елементи на една колона винаги се дефинира една формула на езика DAX, която се въвежда в прозореца с формули на Model Builder.

Както при обикновените таблици, DAX формулите се изпълняват в контекст на ред. Но за разлика от таблиците, няма нужда да се указва изрично параметърът [# Този ред]. Просто казано, дори без да записвате параметъра [# Този ред] във формула на DAX, можете да приемете, че той присъства в нея и да го използвате в изчисленията. Въпреки че тази функция прави DAX формулите по-малко визуални, тя позволява изразите да бъдат написани по-компактно.

Като вземем предвид изброените характеристики на работа с DAX формули, ще създадем нова колона „Доходност“ със следната формула (израз 4):

Израз 4:
=CALCULATE(sum([Sum]); ALLEXCEPT("Таблица_факти"; "Таблица_факти"[Компания]; "Таблица_факти"[Период]))

Нека да разгледаме принципа на действие на израз 4.

Първо, за всеки ред от таблицата с факти този оператор получава като вход временна таблица, която съвпада със самата таблица с факти. Филтрите, дефинирани от текущия контекст, след това се прилагат към тази междинна таблица. Съдържа редове, чиито атрибути съвпадат със стойностите на текущия ред: [[#Този ред]; [Компания]], [[#Този ред]; [Услуга]], [[#Този ред]; [Точка]], [[#Този ред]; [Сума]].

Обаче изразът ALLEXCEPT ("Таблица_факти"; "Таблица_факти" [Компания]; "Таблица_факти" [Период]) изисква текущите контекстни филтри да бъдат изчистени за всички колони с изключение на [Компания] и [Период]. Оказва се, че в междинната таблица оставяме само онези редове, чиито стойности на атрибути [Компания] и [Период] съвпадат с текущия ред. В така филтрираната таблица се избира атрибут [Sum], чиито стойности след това се сумират.

На пръв поглед формулата може да изглежда твърде объркваща, но в действителност тя само възпроизвежда изчисленията на израз 1, които цитирахме в самото начало на статията. Стойностите в колоната „Доходност“ ще бъдат ясно потвърждение, тъй като те съвпадат с колоната „Revenue_Amount“ на фиг. 2.

Сега нека добавим друга изчислена колона към таблицата - „Technical_Level“. В него срещу всяка сума, изчислена на първия етап, ще посочим числовата стойност на скалата от таблицата „Нива“. Всъщност трябва да получим стойности от друга таблица в базата „Fact_Table“. Както при обикновените изчисления в Excel, за да направите това, просто изберете подходящата функция и й предайте идентификатора на желания обект като аргумент.

Нека въведем следната формула в колоната "Technical_Level" (израз 5):

Израз 5:
=ИЗЧИСЛЯВАНЕ(макс.("Нива"[Начало]);филтриране(всички("Нива"[Начало]);"Нива"[Начало]

В представения израз функцията за агрегиране MAX() се прилага към външната таблица „Нива“. Първо, той се филтрира според ограниченията на текущия контекст - в таблицата остават само тези редове, които са по-малки от числото в колоната Добив на активния ред.

И накрая, нека добавим последната изчислена колона към нашата таблица - "Dokh_Level". В него ще покажем ред с името на нивото от таблицата „Нива“. Такива операции се извършват с помощта на VLOOKUP функции в Excel и GLOOKUP (VLOOKUP и HLOOKUP). Езикът DAX има функция, наречена LOOKUPVALUE, която е подобна на оператора VLOOKUP.

Следователно в колоната „Ниво_доход“ е достатъчно да напишете прост израз от следната форма (израз 6):

Израз 6:
=LOOKUPVALUE("Нива"[Ниво]; "Нива"[Начало]; [Техническо_ниво])

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

За да ограничим набора от анализи в Excel, ще използваме командата „Скриване от набора от клиентски инструменти“ (за да направите това, просто щракнете с десния бутон върху колоната, от която се нуждаем, и изберете съответния елемент в контекстното меню). Скритите колони в Model Builder са сиви. За да превключите към средата на Excel и да работите с получения обобщен отчет, просто изберете командата „Превключване към работна книга на Excel“.

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

Помощ за използване на добавката PowerPivot и езика DAX, включително урока „Научете основите на DAX за 30 минути“ на официалния уебсайт на MS Office, можете да намерите онлайн.

Милиони потребители използват формули на Excel за извършване на изчисления. Тези изчисления могат да бъдат толкова прости, колкото добавяне на колони с числа, или много по-сложни, като например моделиране на бизнес процеси. Но във всеки случай всяка формула е изградена с помощта на основни оператори и функции, които са градивните елементи на такива формули.

PowerPivot е градивният елемент, от който Excel се нуждае, за да създава решения за бизнес разузнаване. Независимо дали решението използва прости изчисления или нещо много по-сложно. PowerPivot включва възможност за импортиране на таблици от широк набор от източници на данни, извършване на бързи изчисления върху големи количества данни, използване на DAX (изрази за анализ на данни) формули и създаване на отчети с обобщена таблица въз основа на получената информация.

DAX изразите са много подобни на формулите на Excel. Но въпреки че списъкът с DAX функции е до голяма степен същият като списъка в Excel, има нови функции в изразите за анализ, които Excel няма. Тези функции са предназначени да позволят анализ на данни, по-специално свързване на таблици и динамичен анализ. Възможността за създаване на изчисления, които ще бъдат динамично оценени в различни контексти, е мощен инструмент. Преди PowerPivot и DAX, този вид изчисление често изискваше познаване на концепции за многомерно програмиране.

Въведение в добавката PowerPivot

След като бъде инсталиран, на лентата ще се появи нов раздел PowerPivot.

Кликнете върху прозорецPower Piwotще стартира нов раздел PowerPivotЗаExcel.

Нека отделим малко време тук, за да разгледаме как PowerPivot и Excel се допълват взаимно.

PowerPivot има вграден алгоритъм за обработка на данни, който ви позволява да 1) съхранявате и обработвате данни Опо-големи количества данни от наличните в Excel, 2) импортиране на данни от различни източници на данни, 3) моделиране на операции, като дефиниране на релации между таблици и създаване на DAX формули.

Надстройка PowerPivot заExcel(вижте по-горе) има ориентиран към Excel потребителски интерфейс.

Excel разполага с широк набор от инструменти за визуализация като , PivotCharts и . Те могат да се използват и за работа с PowerPivot данни.

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

Импортиране на данни

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

В зависимост от типа на източника можете да изберете необходимите таблици, да прегледате и филтрирате полетата на таблицата и/или да използвате заявка за импортиране.

Копирай постави

Често потребителите трябва да комбинират данни, съхранени в PowerPivot, с малко количество данни, разположени в работен лист. Това може да стане чрез създаване на релации между таблици.

Работа с таблици

След като данните бъдат импортирани, те се показват в прозореца PowerPivot заExcel.

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

Защо да създавате релации между таблици? Такива връзки ви позволяват правилно да навигирате и групирате данни, елиминирайки необходимостта от комбиниране на свързани колони в една голяма таблица.

Изчисления

Крайна цел PowerPivot заExcelнаправи анализа на данните прост. За разлика от продуктите за анализ на данни, които са насочени към ИТ специалисти (като SQL Server Analysis Services), PowerPivot е предназначен за хора, които използват обобщени таблици в ежедневната си работа. Идеята е потребителите да могат да използват съществуващите умения за Excel, без да се налага да учат специализирани езици.

If(>100000000, „Отличен“, if(>10000000, „Добър“, „Лош“))

Следователно, докато работите, можете да срещнете познати формули като ABS, AVERAGE, AVERAGEA (мисля, че с течение на времето те също ще бъдат преведени на руски). Има и напълно нови: ALL, ALLEXEPT, AVERAGEX.

Визуализация

За да създаде богат на функции, PowerPivot използва инструменти за визуализация на Excel. За щастие Excel има много от тези неща: осеви таблици, осеви диаграми и слайсери.

След няколко минути форматиране и поставяне на контроли имах функционална самостоятелна работна книга, която можеше да работи като приложение.

Долен ред

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

Ако вашата версия на Excel 2013 поддържа добавката PowerPivot, можете:

· Можете да заредите данни в PowerPivot по различни начини. Имате множество източници на данни, свързани таблици, възможност за копиране и поставяне на данни и възможност за създаване на абонаменти.

· Можете да преглеждате, сортирате и филтрирате данни в таблици на PowerPivot.

· Можете да импортирате милиони редове в един работен лист в таблица на PowerPivot.

· Можете да създавате DAX формули или в таблица, или като ново изчислено поле, наречено мярка. Съкращението DAX означава Изрази за анализ на данни(Изрази за анализ на данни). DAX включва 117 функции, които ви позволяват да извършвате два вида изчисления. Можете да използвате 81 стандартни функции на Excel, за да добавите изчислени колони към таблица в прозорец на PowerPoint. А с 54 функции можете да създавате нови мерки в обобщени таблици. С тези функции PivotTables придобиват възможности както никога досега.

· Имате допълнителни начини за създаване на релации, включително изглед на диаграма, който можете да използвате за показване на релации.

· Можете да скриете или преименувате колони.

· Преди да създадете обобщена таблица, можете да зададете числово форматиране за колона.

· Вече е възможно да се присвояват категории на полета, напр. география, URL връзка към изображениеили уеб линк.

· Можете да дефинирате ключови показатели за ефективност или йерархии.

· Ако вашата компания има инсталиран PowerPivot сървър, вие ще можете да публикувате интерактивни PowerPivot отчети на сайт на SharePoint.

Ако планирате да обработвате милиони записи, инсталирайте 64-битовите версии на Office и PowerPivot. В този случай ограниченията, свързани с липсата на RAM, ще останат в сила, но те са частично компенсирани от факта, че PowerPivot може да компресира данните, съхранени във файла PowerPivot, почти 10 пъти. 64-битовата версия на Office 2013 позволява достъп до памет, която надхвърля лимита от 4 GB, зададен за 32-битовата версия на Windows.

Активиране на добавката PowerPivot.Ако използвате Office 365, Office 2013 Pro Plus, Office 2013 Enterprise или версията в кутия на Excel 2013, ще имате достъп до добавката PowerPivot. За да активирате тази добавка, изпълнете следните стъпки:

1. Отворете Excel 2013. Вижте раздела на лентата на PowerPivot (Фигура 8)? Ако да, не е нужно да правите следното.

2. Изпълнете командата Файлж Настроики, изберете Добавки. В падащия списък контролразположен в долната част на прозореца, изберете COM добавкии щракнете върху бутона Отивам(фиг. 9а).

3. В списъка с налични COM добавки намерете добавката Microsoft Office PowerPivot за Excel 2013. Поставете отметка в съответното квадратче и щракнете върху OK (фиг. 9b). (Моля, обърнете внимание, че имате нужда от добавката PowerPivot за Excel 2013. Наследената добавка PowerPivot за Excel, използвана в Excel 2010, не се поддържа в Excel 2013.)

4. Ако не виждате раздела PowerPivot на лентата, излезте от Excel 2013 и го стартирайте отново.

След като инсталирате добавката, разделът PowerPivot ще се появи на лентата на Excel 2013 (Фигура 8).

Ориз. 8. Раздел PowerPivot

Ориз. 9. Инсталирайте добавката PowerPivot

Импортирайте текстов файл.Таблицата източник включва 1,8 милиона записа, разположени във файла BigDatal.txt (част от файла в прозореца на програмата Notepad е показана на фиг. 10). Заглавията на колоните са на първия ред на файла. Може да искате да премахнете нестандартните редове в горната част на файла, за да избегнете проблеми, когато PowerPivot ги обработва. За съжаление, датите във файла са в американски формат, така че не се обработват правилно в бъдеще.

Ориз. 10. Този файл с 1,8 милиона реда е твърде голям за Excel.

За да импортирате файл, съдържащ 1,8 милиона реда в PowerPivot, изпълнете следните стъпки:

1. Отидете в раздела PowerPivot. Кликнете върху иконата контрол. Прозорецът на приложението PowerPivot ще се появи на екрана, показвайки собствена лента (Фигура 11). Централната част на този прозорец е таблица, която ви позволява да преглеждате данни във вашия модел PowerPivot. Прозорецът на PowerPivot съдържа следните три раздела: Към началото, КонструкторИ Допълнително.

Ориз. 11. Щракнете върху иконата Управление в раздела PowerPivot на лентата на Excel, за да отворите прозореца на добавката PowerPivot, който показва собствена лента

2. Импортирайте таблицата от файла BigDatal.txt. За да направите това в група Получаване на външни даннищракнете върху бутона От други източници Съветник за импортиране на таблици.

3. В прозореца Съветник за импортиране на таблициизберете най-ниския елемент Текстов файл. Щракнете върху бутона По-нататък.

4. Въведете в полето Приятелско име на връзкатаиме за вашата връзка.

5. Щракнете върху бутона Прегледи намерете текстовия файл. Ако данните включват заглавки, PowerPivot ще ги открие.

6. Уверете се, че сте избрали запетая като разделител. В падащия списък Разделител на колонипоказват се редица стандартни разделители, като запетая, точка и запетая, вертикална лента и др.

7. Ако искате да откажете импортиране на която и да е колона, премахнете отметките от съответните квадратчета. Текстовият файл е готов за зареждане в RAM. Имайте предвид, че можете значително да намалите количеството RAM, което използвате, като премахнете ненужните колони, особено когато включват дълги текстови стойности. За да направите това, изчистете квадратчетата за отметка, съответстващи на скритите колони (фиг. 12).

Ориз. 12. Настройка на съветника за импортиране на таблици

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

9. След като щракнете върху бутона Готов PowerPivot започва да зарежда файла в паметта. Показва се броят на заредените в момента редове (фиг. 13).

Ориз. 13. PowerPivot зареди 1 550 000 реда само за няколко секунди

10. След като импортирането на файла приключи, се показва броят на заредените редове. Щракнете върху бутона Близоза да се върнете към прозореца на PowerPivot.

11. Прозорецът PowerPivot показва 1,8 милиона записа. Можете да използвате вертикалната лента за превъртане, за да ги видите. Можете също така да сортирате, промените числовия формат или да приложите филтър (Фигура 14).

Ориз. 14. Записите се показват в мрежа, която прилича на обикновена електронна таблица на Excel

Задайте числов формат на колоните. Полето за дата е предназначено да съхранява дата и час. Ако изходните данни не включват времеви компонент, изберете заглавката на датата и използвайте падащия списък форматв групата Форматиранераздели Към началото PowerPivot ленти. Изберете формата 03/14/2001. Към колона доходиприложете валутния формат. Ако не искате десетичните знаци да се показват в обобщената таблица, намалете броя на десетичните знаци до нула.

Щракването с десния бутон върху колона показва контекстно меню, което ви позволява да преименувате, замразявате и копирате колони. Използвайте това меню, за да скриете колона от списъка с полета на обобщена таблица в Excel.

Въпреки че PowerPivot изглежда като Excel, той всъщност не е Excel. Няма да можете да променяте отделни клетки. Ако добавите формула, която изчислява сумата към клетка E1, тази формула ще бъде автоматично копирана във всички редове. Ако форматирате стойност в една клетка, всички клетки в тази колона също се форматират.

За да промените ширината на колоните, плъзнете границата между заглавията на колоните (както в Excel).

Така че имате 1,8 милиона записа, които можете да сортирате, филтрирате и обединявате в обобщени таблици. Имайте предвид, че 1,8 милиона реда, импортирани от текстовия файл, се съхраняват в работната книга на Excel. Можете да копирате файла .xlsx на нов компютър, след което всички редове ще се окажат на новия компютър. Оригиналният текстов файл е с размер 58 MB, но компресираният Excel файл е с размер само 4 MB.

Добавяне на данни от Excel чрез копиране и поставяне.В нашата примерна таблица PowerPivot информацията за магазина беше ограничена само до полето Код[магазин]. Нямаше информация за името или местоположението на магазина. Проблемът може да бъде решен чрез използване на малък Excel файл, който съпоставя кодовете на магазини с имена и друга идентифицираща информация. Можете да добавите тези данни към нов раздел в прозореца на PowerPivot. Можете да използвате копиране и поставяне, както е описано по-долу, или можете да създадете свързана таблица (вижте следващия раздел), която е по-лесна за използване.

За да използвате операции за копиране и поставяне, изпълнете следните стъпки:

1. Отворете работна книга, съдържаща диапазон, който свързва идентификатори на магазини с имена (Фигура 15).

2. Изберете данните, като използвате клавишната комбинация Ctrl+Shift*.

3. Копирайте данните, като натиснете клавишната комбинация Ctrl+C.

4. Отидете в раздела PowerPivot в Excel.

5. Щракнете върху бутона контролза да отворите прозореца на PowerPivot. Вече ще можете да видите предварително импортиран набор от данни, съдържащ 1,8 милиона реда.

6. От лявата страна на раздела Към началотоПрозорец на PowerPivot щракнете върху иконата Поставете. На екрана ще се появи диалогов прозорец Преглед на вграждане.

7. Дайте на новата таблица по-смислено име от таблицата по подразбиране, напр. Магазините(фиг. 16). Натиснете OK.

Ориз. 15. Таблица с кодове и имена на магазини

Ориз. 16. Прозорец Преглед на вграждане

В прозореца на PowerPivot се появи нов раздел Магазините, който съдържа допълнителна информация за магазините. Моля, обърнете внимание, че в долната част на прозореца на PowerPivot има преки пътища към работния лист (Фигура 17). Данните, поставени от клипборда, са статично копие на данните на Excel. Ако данните в Excel се променят, копирайте ги и изпълнете командата PowerPivot Вмъкване със замяна.

Ориз. 17. Преки пътища към работния лист на прозореца на PowerPivot

Добавете данни от Excel чрез свързване.В предишния раздел беше добавена таблица Магазинитечрез извършване на операции за копиране и поставяне. Това всъщност създава две копия на данните. Единият от тях се съхранява в работния лист на Excel, а вторият се съхранява в прозореца на PowerPivot. Ако изходният лист се промени, съдържанието на прозореца на PowerPivot остава непроменено. Ако тази ситуация не ви подхожда, свържете данните от таблицата на Excel с данните в прозореца на PowerPivot:

1. За да започнете, трансформирайте данните (както на фиг. 15) в таблица, като щракнете върху която и да е клетка в диапазона и натиснете Ctrl+T.

2. Изберете контекстуален раздел Конструктор. Лявата страна на лентата показва името на новосъздадената таблица - Table1. В това поле въведете ново име, например Store_Code.

3. Отидете в раздела PowerPivotи в групата Масищракнете Добавяне към модела на данни. Копие на таблицата ще бъде създадено и показано в прозореца PowerPivot(фиг. 18).

Ориз. 18. Нова маса в PowerPivot – Store_code

Дефиниция на връзките.Обикновено Excel използва функцията VLOOKUP, за да свърже две таблици. В PowerPivot тази задача се решава много по-лесно:

1. В прозореца на PowerPivot отидете на Към началотои щракнете върху бутона Изглед на диаграма. Ще се покажат две таблици една до друга (фиг. 19).

Ориз. 19. Плъзнете едно поле върху друго, за да създадете връзка

2. Щракнете в полето Кодв главната таблица (BigData) плъзнете и го пуснете, докато е над полето Код на магазинарегион Магазините. Ще се появят стрелки, съответстващи на установената връзка.

3. За да се върнете към таблицата на PowerPivot, щракнете Представяне на данниразположен на раздела Към началотопрозорец PowerPivot.

Добавяне на изчислени колони с помощта на DAX.Един от недостатъците на обобщените таблици, създадени от данни на PowerPivot, е, че те не могат автоматично да групират ежедневните данни по години. Следователно, преди да създадете обобщена таблица, използвайте езика за формули DAX, за да добавите нова изчисляема колона към таблицата PowerPivot.

1. Отидете до отметката PowerPivot. Щракнете върху бутона контроли в прозореца, който се отваря, щракнете върху раздела на първия работен лист - Продажбиразположен в долната част на прозореца PowerPivot.

2. Извиква се най-дясната колона Добавяне на колона. Щракнете върху първата клетка на тази празна колона.

3. Щракнете върху иконата fxразположен от лявата страна на лентата с формули. На екрана ще се появи диалогов прозорец Вмъкване на функция, който включва редица категории. Избери категория дата и час. Моля, обърнете внимание, че функциите, показани в този списък, са различни от функциите в категорията дата и часв Excel.

4. Превъртете и изберете функция ГОДИНАи натиснете Добре(фиг. 20). Кликнете върху заглавката на колоната дата на поръчка. Надстройка PowerPivotпредлага формулата =YEAR([Дата на поръчка]. Завършете формулата, като въведете затварящата скоба и натиснете Въведете. Excel попълва колоната със стойностите на годината, свързани с датата.

5. Щракнете с десния бутон върху колоната и изберете опцията от контекстното меню Преименуване на колона. Въведете ново име на колона, например година(фиг. 21).

Ориз. 20. Вмъкване на функцията YEAR

Ориз. 21. ГОДИНА формула

Създаване на обобщена таблица:

1. Отидете в раздела Към началотоленти PowerPivot.Разгънете списъка под бутона Пивотна таблицаи изберете Пивотна таблица(фиг. 22).

2. В прозореца, който се отваря, изберете радио бутона, който определя как да вмъкнете обобщената таблица в нов лист. Ще се върнете отново в прозореца на Excel. Списъкът с полета на обобщена таблица ще покаже всички таблици, съдържащи се в PowerPivot.За да видите полетата, включени във всяка таблица, разгънете списъка, съответстващ на таблицата (като щракнете върху „триъгълника“).

3. В списъка с полета PowerPivotотвори масата Голяма информацияи изберете полето доходи. Разширете таблицата Магазините o и изберете полето в него Регион. Excel ще генерира обобщена таблица, показваща продажбите по региони (фиг. 23). И така, имате на ваше разположение обобщена таблица, която е изградена на базата на 1,8 милиона реда данни и съдържа виртуална връзка към съответната таблица.

Ориз. 22. Създаване на обобщена таблица в прозорец PowerPivot

Ориз. 23. Тази обобщена таблица обобщава 1,8 милиона реда и също така използва данни от две таблици

След това можете да използвате инструментите от набора от контекстни раздели Работа с обобщени таблициза форматиране на обобщена таблица. Например, можете да приложите валутен формат и да преименувате полето Сума по колона Приходи, изберете формат с редуващи се редове и т.н.

Разлики между обобщените таблици на PowerPivot и Excel.Ако досега сте създавали само обикновени обобщени таблици на Excel, обобщени таблици PowerPivotможе да ви изглежда неудобно. Причината за много проблеми не е свързана със самата добавка. PowerPivot, но с факта, че обобщената таблица PowerPivotе OLAP обобщена таблица и се държи съответно. При работа с осеви таблици PowerPivotТрябва да се има предвид, че:

· Няма автоматично сортиране по дни от седмицата (понеделник, вторник, сряда и т.н.). За да извършите правилно сортиране, изберете командата Допълнителни опции за сортиранеж Възходящж Допълнително. Махнете отметката от квадратчето Автоматично сортиране, разширете списъка Сортиране по първи ключи изберете последователността понеделник, вторник, сряда, четвъртък, петък, събота, неделя.

· Можете да използвате следната техника (вместо плъзгане и пускане), за да сортирате полета в обикновени обобщени таблици. Изберете клетка, съдържаща например думата петъки въведете думата в тази клетка понеделник. След натискане на клавиша Въведетеданни от колона понеделникще се премести в нова колона. Моля, обърнете внимание, че тази техника не се прилага за осеви таблици. PowerPivot.

· Докато въвеждате формули с помощта на интерфейса на Excel, можете да изберете клетките, които да включите, като щракнете с мишката или използвате клавишите със стрелки. Вероятно добавка PowerPivotе създаден от феновете на мишката, така че при създаването на формули PowerPivotМожете да използвате само мишката.

· След натискане на бутона Актуализациянамиращ се в контекстния раздел Анализ, Excel актуализира данните в обобщената таблица. Помислете, преди да направите това в Excel 2013. Този пример повторно импортира 1,8 милиона реда.

Два вида DAX изчисления

Току-що разгледахме пример за използване на функцията DAX (YEAR) за деклариране на изчисляема колона в таблица, която се показва в прозорец PowerPivot. Има 81 функции, използвани за създаване на тези колони, повечето от които са копирани директно от Excel. Много от функциите на DAX са подобни на съответните функции на Excel, с няколко изключения, разгледани по-долу. Можете също да използвате DAX за създаване на нови изчисляеми полета в обобщена таблица. Тези функции не са предназначени за изчисляване на стойност на една клетка, а по-скоро за определяне на стойностите на филтрирани редове, свързани с клетки на обобщена таблица (функции за агрегиране). В DAX има 54 такива функции. Истинската сила на PowerPivot се крие в тези функции.

Използване на DAX функции върху изчисляеми колони.Тези функции са много подобни на обикновените функции на Excel, така че повечето от тях не изискват допълнително обяснение. Но някои DAX функции се различават от функциите на Excel:

· Рядко споменаваната функция на Excel RAZNDAT е преименувана на YEARFRAC и нейният код е пренаписан. В интерес на истината, самата функция RAZNDAT не е в помощта на Excel. Освен това го няма и в съветника за функции. И когато ръчно въвеждате първите букви от името на функцията, Excel също няма да покаже намек в падащия списък. Това се обяснява съвсем просто. Тази функция не е естествено функция на Excel. Просто се поддържа от Excel за съвместимост с други системи за електронни таблици. Тази функция дойде в Excel от електронните таблици Lotus 1-2-3. Прочетете повече тук.

· Функцията TEXT на Excel е преименувана на FORMAT.

· Функцията SUMIFS е заменена от подобрената функция CALCULATE.

· Вместо функцията VLOOKUP се използва по-простата функция RELATED.

· DAX вече има функцията BLANK(). Тъй като някои от функциите за агрегиране могат да базират изчисления на параметрите ALLN0NBLANKR0W или FIRSTNONBLANK, функцията BLANK() може да се използва като аргумент на функцията IF(), за да изключи определени редове от изчисленията на мярката.

· Функцията SELECT е преименувана на SWITCH. Докато функцията SELECT приема числови стойности от 1 до 255 като аргументи, функцията SWITCH може да бъде програмирана да работи с други стойности.

Използвайте функцията RELATED, за да правите изчисления в колони въз основа на стойности в друга таблица.По време на изчисленията в табл PowerPivotможе да се наложи да посочите стойности в други клетки PowerPivot. В стандартната версия на Excel в такива случаи се използва функцията VLOOKUP. PowerPivot използва функцията RELATED.

Този пример използва таблица BigData, която включва поле Код[магазин] и доходи(количество продажби на ден). Отчетите за продажби често използват индикатор като продажби на квадратен метър площ. В свързана таблица Магазинитеима ниви Код на магазинаИ Търговска площ. Наборът от тези полета съдържа всички данни, необходими за извършване на изчисленията.

За да започнете да създавате нова формула, отидете на таблицата PowerPivotи щракнете в празна клетка на колоната Добавяне на колона. Въведете знак за равенство и щракнете върху клетка в колоната доходи. Започнете да въвеждате формулата PowerPivot: = [Приходи]. Въведете наклонената черта, за да представите разделението. Сега трябва да получите достъп до полето Търговска площ, разположени в табл Магазините. Започнете да въвеждате функция СВЪРЗАНИ(. Въведете първите няколко букви от името на таблицата - мамо. Ще се покаже списък с полета в таблицата Магазините(фиг. 24). Кликнете два пъти в полето Търговска площ. Завършете формулата, като въведете затваряща скоба и натиснете Enter. Щракнете с десния бутон върху колоната и изберете опцията от контекстното меню Преименуване на колона. Дайте ново име, напр. ПродажбиNaKvM.

Ориз. 24. Създайте изчислена колона на обобщена таблица на PowerPivot с помощта на DAX функции

Използвайки изчислени колони и релации, можете да създадете редица интересни обобщени таблици. Изчисляваните колони се изчисляват за всеки ред от основните данни. В резултат на това формулата Продажби на квадратен метър търговска площработи 1,8 милиона пъти на маса PowerPivot. С помощта на DAX формули можете да създадете ново изчисляемо поле, което се изчислява веднъж за всяка клетка в крайната обобщена таблица.

Създайте изчисляемо поле в обобщена таблица с помощта на DAX формули. DAX изчисляемите полета имат няколко предимства пред обикновените изчисляеми полета. Едно предимство е, че когато използвате поле като това, изчисленията се извършват веднъж във всяка клетка на получената обобщена таблица. На фиг. Фигура 25 показва обобщена таблица, в която клетки B5:C12 съдържат числени стойности. Създаденото изчисляемо поле на DAX ще бъде изчислено само за 16-те числови клетки в обобщената таблица. Това е много по-бързо от изчисляването на стойностите в 1,8 милиона клетки и след това сумирането им. Поради американския формат на датата в изходния файл BigData.txt, редица дати в модела PowerPivotне се показва правилно, което води до поле (празен)по отношение на датите. Преди да започнете да създавате първото си изчисляемо поле, трябва да разберете филтрите.

Ориз. 25. Моделна обобщена таблица PowerPivot

Прилагане на предварително изградени филтри върху DAX изчисляеми полета.Изчисляваните полета на DAX автоматично прилагат филтри, които са предварително дефинирани в съответните клетки. Първо се прилагат всички филтри и след това се извършват изчисления върху DAX полетата. Нека разгледаме клетка B6, показана на фиг. 25. Нека се опитаме да отговорим на въпроса колко филтъра са посочени в клетка B6. Ще кажете, че са дефинирани два филтъра. Мисля, че има четири филтъра, дефинирани в тази клетка.

Заедно с обикновените автофилтри, данните в клетките могат да бъдат филтрирани с помощта на срезове. След създаването на първия срез се показват записите, разположени в клетка B6, които съответстват на 2006 година. Можете също така да видите данните за продажбите на магазини на Bellevue Square Managers, като добавите втори отрязък. Тези две части образуват първите два филтъра. Заглавката на реда за очила също действа като филтър. Това ще бъде третият филтър. И накрая, четвъртият филтър е щата Вашингтон. DAX изчислените полета първо прилагат предварително създадени филтри и след това изчисляват резултата от прилагането на DAX формулата.

Създаване на изчисляемо поле OAX.За да създадете изчисляемо поле, отидете на лентата на Excel, изберете раздела PowerPivotи изпълнете командата Изчисляеми полетаж Създайте изчисляемо поле. На екрана ще се появи диалогов прозорец Изчисляемо поле. В полето Име на таблицапосочете името на таблицата. Дайте име на изчисленото поле, например Брой магазини. В панела за въвеждане на формула въведете формула. За да вмъкнете имена на функции, щракнете върху иконата fx. Докато въвеждате имена на полета, започнете да въвеждате няколко знака от името на таблицата и след това използвайте AutoComplete, за да изберете полето, което искате.

След като приключите с въвеждането на формулата, щракнете върху бутона Проверете формулатаза тестване на синтаксиса на формулата. Обърнете внимание на подсказката за формулата, която се появява над панела, показващ резултатите от теста на формулата. Кликнете в полето Описаниеза да скриете подсказката. Резултатите от проверката на формулата ще се появят на екрана. Ако проверката по формулата е успешна, се появява съобщение Формулата не съдържа грешки(фиг. 26). Кликнете Добреза добавяне на изчисляемо поле към списъка с полета на обобщена таблица. На масата Магазинитемодели PowerPivotновото поле не се появи, но е в осевата таблица (фиг. 27). Точно като в обикновена обобщена таблица!

Ориз. 26. Създайте ново изчисляемо поле

Ориз. 27. Ново изчисляемо поле в обобщена таблица

Веднъж създадено, изчисляемото поле може да се използва за извършване на изчисления в бъдеще. Изчисляемо поле Продажби в магазини(фиг. 28) използва следната формула:

SUMX(BigData;BigData[Приходи])/[Брой магазини]

Ориз. 28. Поле Продажби в магазиниизчислено въз основа на предварително създадено поле Брой магазини

За да опростите структурата на обобщената таблица, можете да скриете полетата Сума по колона ПриходиИ Брой магазини, напускайки терена Продажби в магазини.

Как да отмените стандартното филтриране.Сега нека разгледаме следващия проблем. На фиг. 29, клетка C5 е осветена. Прилагането на филтри към тази клетка показва записи, които съответстват на името на търговеца Amber и датата на продажба 01.06.2014 г. Този и следващите раздели (до Workshop) използват Excel файл Пример filter.xlsx

Ориз. 29. Клетка C5 има имплицитно приложени филтри по дата и търговец

Както бе споменато по-рано, всички филтри, които задавате в клетка с обобщена таблица, се прилагат автоматично към изчисляемото поле на DAX. В много случаи това е неприемливо. Клетка G6 показва $165 продадени артикули, които не са свързани с нито един продавач. Политиката в този случай е сумата от $165 да бъде разпределена на други хора въз основа на техния процент от продадените артикули през този ден, като продавачът Хюз не участва в продажбите. Възможно ли е да се приложи тази политика в тази обобщена таблица?

Имайте предвид, че изчисленията, извършени в клетка C5, са имплицитно филтрирани, за да покажат само продажбите на Amber. Трябва да създадем DAX формула, която ще дефилтрира данните. Тази формула ще замени филтъра, който показва публикации, които съответстват на Амбър, и показва публикации, които не са свързани с Амбър. Функцията DAX CALCULATE може да премахне инсталираните филтри и да приложи други филтри. Тази функция е подобна на функцията SUMIFS, но дава на потребителите повече опции.

Функция CALCULATEизползва се за изчисляване на обща колона, като се вземат предвид един или повече филтъра. Ако за поле Продавачзададете филтър, той ще замени филтъра, зададен преди това за това поле. Ако създадете филтър Продавач=Хюз, DAX автоматично ще игнорира неявния филтър Продавач=Амбър, което ще покаже всички записи, съответстващи на Hughes. Ако имате проблеми с научаването как да създавате DAX формули веднага, опитайте подхода стъпка по стъпка. Вместо многоетажни формули, оформете набор, състоящ се от малки формули. Формулата, която изчислява обема на продажбите на Хюз (фиг. 30):

CALCULATE([Сума по колона Продажби];Sls[Продавач]="Хюз")

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

Ориз. 30. DAX формулата премахва филтъра на полето Продавач и прилага друг филтър

Задайте следните изчисляеми полета:

Сега е време да приложите филтъра към колоната Продавачза да скриете дела на Хюз от обобщената таблица. В резултат на това получаваме сума на продажбите от $911 за 2 юни (стойността в клетка H6, фиг. 31), въпреки факта, че делът на Хюз е разпределен между други продавачи. Сравнете стойностите за всеки отделен продавач и сумите по дни в таблиците на фиг. 30 и 31. Този резултат не може да бъде постигнат с помощта на изчислени полета, създадени в обикновена обобщена таблица.

Ориз. 31. Продажбите на Хюз се разпределят между други продавачи.


Свързана информация.