Урок: Анализ на данни в обобщена таблица посредством модел на данни в Excel 2013

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

Да започнем с импортиране на малко данни.

  1. Изтеглете примерните данни (ContosoV2) за този урок. Вж. Получаване на примерни данни за уроците за DAX и модели на данни. Извлечете и запишете файловете с данни на лесно достъпно място, като например папката "Изтегляния" или "Моите документи".

  2. Отворете празна работна книга в Excel.

  3. Щракнете върху Данни > Получаване на външни данни > От Access.

  4. Отидете в папката, която съдържа файловете с примерни данни и изберете ContosoSales.

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

    Диалогов прозорец ''Избор на таблица''

  6. В "Избор на таблица" отметнете Разреши избирането на множество таблици.

  7. Изберете всичките таблици и щракнете върху OK.

  8. В "Импортиране на данни" щракнете върху Отчет с обобщена таблица, а после върху OK.

    Забележки: 

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

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

Преглед на данните чрез използване на обобщена таблица

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

  1. В списъка с полета превъртете надолу, докато намерите таблицата FactSales.

  2. Щракнете върху SalesAmount. Тъй като тези данни са числови, Excel автоматично поставя SalesAmount в областта "Стойности".

  3. В DimDate плъзнете CalendarYear към "Колони".

  4. В DimProductSubcategory плъзнете ProductSubcategoryName към "Редове".

  5. В DimProduct плъзнете BrandName към "Редове", поставяйки го под подкатегория.

Вашата обобщена таблица трябва да изглежда подобна на екрана по-долу.

Обобщена таблица, показваща примерни данни

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

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

Добавяне на още таблици

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

Добавете продуктови категории

  1. Отворете нов лист в работната книга. Ще го използвате за съхраняване на допълнителни данни.

  2. Щракнете върху Данни > Получаване на външни данни > От Access.

  3. Отидете в папката, която съдържа файловете с примерни данни, и изберете ProductCategories. Щракнете върху Отваряне.

  4. В "Импортиране на данни" изберете Таблица, а после щракнете върху OK.

Добавете географски данни

  1. Вмъкнете друг лист.

  2. От файловете с примерни данни отворете Geography.xlsx, поставете курсора в A1 и натиснете Ctrl-Shift-End, за да изберете всичките данни.

  3. Копирайте данните в клипборда.

  4. Поставете данните в празния лист, който току-що добавихте.

  5. Щракнете върху Форматирай като таблица, избирайки който и да е стил. Форматирането на данни като таблица ви позволява да ги наименувате, което е полезно, когато определяте релации на по-късен етап.

  6. Във "Форматирай като таблица" проверете дали Моята таблица има заглавки е избрано. Щракнете върху OK.

  7. Наименувайте таблицата География. В Инструменти за таблица > Проектиране въведете География в "Име на таблица".

  8. Затворете Geography.xlsx, за да я изчистите от работното пространство.

Добавете данни за магазини

  • Повторете предходните стъпки за файла Stores.xlsx, като поставите съдържанието в празен лист. Наименувайте таблицата Stores.

Трябва вече да разполагате с четири листа. Лист 1 съдържа обобщена таблица, лист 2 съдържа ProductCategories, лист 3 – Geography, а лист 4 – Stores. Тъй като отделихте време, за да наименувате всяка от таблиците, следващата стъпка, създаване на релации, ще бъде много по-лесна.

Използване на полета от новите импортирани таблици

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

  1. В горната част на "Полета на обобщената таблица" щракнете върху Всички, за да видите пълен списък с наличните таблици.

  2. Превъртете до дъното на списъка. Там ще намерите новите таблици, които добавихте току-що.

  3. Разгънете Stores.

  4. Плъзнете StoreName до областта "Филтри".

  5. Обърнете внимание, че Excel ви подканва да създадете релация. Това уведомление се появява, защото сте използвали полета от таблица, която не е свързана с модела.

  6. Щракнете върху Създаване, за да отворите диалоговия прозорец "Създаване на зависимост".

  7. В "Таблица" изберете FactSales. В примерните данни, който използвате, FactSales съдържа подробна информация за продажби и разходи за бизнеса на Contoso, както и ключове към други таблици, включително кодове на магазини, които също са представени във файла Stores.xlsx, който импортирахте в предишната стъпка.

  8. В "Колона (външна)" изберете StoreKey.

  9. В "Свързана таблица" изберете Stores.

  10. В "Свързана колона (основна)" изберете StoreKey.

  11. Щракнете върху OK.

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

Добавяне на релации

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

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

Нова задача

Свържете ProductSubcategory с ProductCategory

  1. В Excel щракнете върху Данни > Зависимости > Създай.

  2. В "Таблица" изберете DimProductSubcategory.

  3. В "Колона (външна)" изберете ProductCategoryKey.

  4. В "Свързана таблица" изберете Table_ProductCategory.accdb.

  5. В "Свързана колона (основна)" изберете ProductCategoryKey.

  6. Щракнете върху OK.

  7. Затворете диалоговия прозорец Управление на зависимости.

Добавете категории към обобщената таблица

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

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

  2. Превъртете до дъното на списъка.

  3. В областта "Редове" премахнете BrandName.

  4. Разгънете Table_DimProductCategories.accdb.

  5. Плъзнете ProductCategoryName в областта "Редове" и я поставете над ProductSubcategory.

  6. В "Полета на обобщената таблица" щракнете върху Активно, за да проверите дали таблиците, които току-що използвахте, вече се използват активно в обобщената таблица.

Контролна точка: Преглед на наученото

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

Въпреки че обобщената таблица е функционална, вероятно забелязахте, че някои неща могат да бъдат усъвършенствани. Изглежда, че списъкът с полета на обобщената таблица съдържа допълнителни таблици (DimEntity) и колони (ETLLoadID), които не са свързани с бизнеса на Contoso. А и все още не сме интегрирали географските данни.

Следва: Преглед и разширяване на модела с Power Pivot

В следващата серия от задачи ще използвате добавката за Microsoft Office Power Pivot в Microsoft Excel 2013, за да разширите модела. Ще видите, че е по-лесно да създавате релации, като използвате "Изглед за диаграми", който добавката ви предлага. Също така ще използвате добавката, за да създавате изчисления и йерархии, да скривате елементи, които не бива да се появяват в списъка на полетата, и да оптимизирате данните за допълнително отчитане.

Забележка:  Добавката Power Pivot в Microsoft Excel 2013 е налична в Office Professional Plus. Вж. Добавката Power Pivot в Microsoft Excel 2013 за повече информация.

Добавете Power Pivot към лентата на Excel, като разрешите добавката Power Pivot.

  1. Отидете на Файл > Опции > Добавки.

  2. В полето Управление щракнете върху COM добавки> Започване.

  1. Поставете отметка в квадратчето Microsoft Office Power Pivot в Microsoft Excel 2013, след което щракнете върху OK.

Сега в лентата има раздел Power Pivot.

Добавяне на релация чрез използване на "Изглед на диаграма" в Power Pivot

  1. В Excel щракнете върху "Лист3", за да го направите активен. "Лист3" съдържа таблицата "Geography", която импортирахте по-рано.

  2. В лентата щракнете върху Power Pivot > Добавяне към модела за данни. С тази стъпка добавяте таблицата "Geography" към модела. Освен това се отваря добавката Power Pivot, която ще използвате, за да изпълните оставащите стъпки в тази задача.

  3. Обърнете внимание, че в прозореца на Power Pivot са показани всички таблици в модела, включително "Geography". Щракнете в няколко от таблиците поред. В добавката можете да видите всички данни, които се съдържат във вашия модел.

  4. В прозореца на Power Pivot, в раздела "Изглед" щракнете върху Изглед на диаграма.

  5. Използвайте лентата на плъзгача, за да промените размерите на диаграмата, така че да виждате всички обекти в нея. Обърнете внимание, че две таблици не са свързани с останалата част от диаграмата: DimEntity и Geography.

  6. Щракнете с десния бутон на мишката върху DimEntity и щракнете върху Изтрий. Тази таблица е артефакт от оригиналната база данни и не е необходима в модела.

  7. Увеличете "Geography", така че да виждате всичките полета. Може да използвате плъзгача, за да увеличите диаграмата на таблицата.

  8. Обърнете внимание, че "Geography" има GeographyKey. Тази колона съдържа стойности, които идентифицират еднозначно всеки ред в таблицата "Geography". Нека разберем дали други таблици в модела също използват този ключ. Ако това е така, можем да създадем релация, която свързва Geography с останалата част на модела.

  9. Щракнете върху Търсене.

  10. В "Търсене на метаданни" въведете GeographyKey.

  11. Щракнете върху Намери следващия няколко пъти. Ще забележите, че GeographyKey се появява в таблицата "Geography" и в таблицата "Stores".

  12. Позиционирайте таблицата "Geography" така, че да се намира до "Stores".

  13. Плъзнете колоната GeographyKey в "Stores" към колоната GeographyKey в "Geography". Power Pivot начертава линия между двете колони, указвайки релация.

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

Съвет:  В изгледа за диаграми няколко диаграми на таблици са разгънати изцяло, показвайки колони като ETLLoadID, LoadDate и UpdateDate. Тези конкретни полета са артефакти от оригиналното хранилище за данни на Contoso, добавени да поддържат извличането на данните и зареждането на операциите. Те не са ви необходими във вашия модел. За да се отървете от тях, осветете и щракнете с десния бутон на мишката върху полето, след което щракнете върху Изтрий .

Създаване на изчисляема колона

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

  1. В прозореца на Power Pivot, превключете обратно към изгледа на данните.

  2. Преименувайте таблицата Table_ProductCategories accdb с по-удобно за използване име. Ще използвате тази таблица в следващите стъпки и по-кратко име би улеснило прочитането на изчисленията. Щракнете с десния бутон на мишката върху името на таблицата, щракнете върху Преименувай, въведете ProductCategories и натиснете клавиша Enter.

  3. Изберете таблицата FactSales.

  4. Щракнете върху Проектиране > Колони > Добавяне.

  5. В лентата за формули над таблицата въведете формулата по-долу. Автодовършването ви помага да въведете пълните имена на колоните и таблиците и изброява достъпните функции. Можете също просто да щракнете върху колоната и Power Pivot добавя нейното име към формулата.

    = [SalesAmount] - [TotalCost] - [ReturnAmount]

  6. Когато приключите със съставянето на формулата, натиснете клавиша Enter, за да приемете формулата.

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

  7. Преименувайте колоната, като щракнете с десния бутон на мишката върху CalculatedColumn1 и изберете Преименуване на колона. Въведете Печалба и натиснете клавиша Enter.

  8. Сега изберете таблицата DimProduct.

  9. Щракнете върху Проектиране > Колони > Добавяне.

  10. В лентата за формули над таблицата въведете следната формула.

    = RELATED(ProductCategories[ProductCategoryName])

    Функцията RELATED връща стойност от свързана таблица. В този случай таблицата ProductCategories включва имената на категориите продукти, които ще е полезно да имате в таблицата DimProduct, когато изграждате йерархия, която включва информация за категории. За повече информация относно тази функция вж. Функцията RELATED (DAX).

  11. Когато приключите със съставянето на формулата, натиснете клавиша Enter, за да приемете формулата.

    Стойностите се попълват във всички редове на изчисляемата колона. Ако превъртите надолу по таблицата, ще видите, че вече всеки ред има Product Category Name.

  12. Преименувайте колоната, като щракнете с десния бутон на мишката върху CalculatedColumn1 и изберете Преименуване на колоната. Въведете ProductCategory и натиснете клавиша Enter.

  13. Щракнете върху Проектиране > Колони > Добавяне.

  14. В лентата за формули над таблицата въведете формулата по-долу и натиснете клавиша Enter, за да приемете формулата.

    = RELATED(DimProductSubcategory[ProductSubcategoryName])

  15. Преименувайте колоната, като щракнете с десния бутон на мишката върху CalculatedColumn1 и изберете Преименуване на колоната. Въведете ProductSubcategory и натиснете клавиша Enter.

Създаване на йерархия

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

  1. В Power Pivot, превключете към изгледа на диаграми. Разгънете таблицата DimDate, така че по-лесно да виждате всичките й полета.

  2. Натиснете и задръжте върху Ctrl, а после щракнете върху колоните CalendarYear, CalendarQuarter и CalendarMonth (трябва да превъртите надолу по таблицата).

  3. Докато са избрани трите колони, щракнете с десен бутон върху една от тях и щракнете върху Създаване на йерархия. В дъното на таблицата се създава йерархичен родителски възел, "Йерархия 1", а избраните колони се копират под йерархията като дъщерни възли.

  4. Въведете Дати за име на вашата нова йерархия.

  5. Добавете колоната FullDateLabel към йерархията. Щракнете с десния бутон на мишката върху FullDateLabel и изберете Добавяне към йерархията. Изберете Дата. FullDateLabel съдържа пълна дата, включително година, месец и ден. Проверете дали FullDateLabel се появява в края на йерархията. Сега вече имате йерархия с няколко нива, която включва година, тримесечие, месец и отделни календарни дни.

  6. Докато сте в "Изглед на диаграма", посочете към таблицата DimProduct, а след това щракнете върху бутона Създаване на йерархия в заглавката на таблицата. В дъното на таблицата се появява празен йерархичен родителски възел.

  7. Въведете Product Categories за име на вашата нова йерархия.

  8. За да създадете йерархичен дъщерен възел, плъзнете ProductCategory и ProductSubcategory в йерархията.

  9. Щракнете с десен бутон върху ProductName и изберете Добавяне към йерархията. Изберете Product Categories.

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

  1. Върнете се в Excel.

  2. В "Лист1" (листът, който съдържа обобщената таблица) премахнете полетата от областта "Редове".

  3. Заместете ги с новата йерархия Product Categories в DimProduct.

  4. По същия начин заместете CalendarYear в областта "Колони" с йерархията Дати в DimDate.

Сега, когато разглеждате данните, лесно ще видите ползите от използване на йерархии. Може самостоятелно да разгъвате и затваряте различни области на обобщената таблица, което ви дава по-голям контрол върху това как се използва наличното пространство. Нещо повече, като добавите само една йерархия към "Редове" и "Колони", незабавно получавате подробно детайлизиране, без да се налага да наслагвате множество полета, за да постигнете същия ефект.

Скриване на колони

След като създадохте йерархия "Продуктови категории" и я поставихте в DimProduct, повече не се нуждаете от DimProductCategory или DimProductSubcategory в списъка с полета на обобщената таблица. В тази задача ще се научите да скривате нерелеватни таблици и колони, които заемат място в списъка с полета на обобщена таблица. Като скриете таблиците и колоните, подобрявате работата с отчетите, без да това да се отрази върху модела, който предоставя релации и изчисления на данни.

Нова задача

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

  1. В Power Pivot се уверете, че сте избрали изглед на данните.

  2. В разделите най-отдолу щракнете с десен бутон върху DimProductSubcategory и изберете Скриване от клиентски инструменти.

  3. Повторете за ProductCategories.

  4. Отворете DimProduct.

  5. Щракнете с десния бутон върху следващите колони, а после щракнете и върху Скриване от клиентски инструменти.

    • ProductKey

    • ProductLabel

    • ProductSubcategory

  6. Изберете групово съседни колони. Започнете с ClassID и продължете към ProductSubcategory в края. Щракнете с десния бутон, за да ги скриете.

  7. Повторете за другите таблици, като премахвате ИД, ключове или други подробности, които няма да използвате в този отчет.

Превключете обратно към лист 1 в Excel със списъка на полета на обобщената таблица, за да видите разликата. Броят на таблиците е намален, а DimProduct включва само онези елементи, които е по-вероятно да използвате, когато анализирате продажбите.

Създаване на отчет на Power View

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

  1. В Excel щракнете върху Вмъкване > Power View.

    Забележка:  Ако използвате Power View за първи път на тази машина, ще получите подкана първо да активирате добавката и да инсталирате Silverlight.

  2. В "Полета на Power View" щракнете върху стрелката до таблицата FactSales и изберете SalesAmount.

  3. Разгънете таблицата "Geography" и щракнете върху RegionCountryName.

  4. В лентата щракнете върху Карта.

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

Оптимизиране за отчитане в Power View

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

Като първа стъпка добавете URL адреси към своята работна книга.

  1. В Excel отворете нов лист и копирайте тези стойности:

ManufacturerURL

ManufacturerID

http://www.contoso.com

Contoso LTD

http://www.adventure-works.com

Adventure Works

http://www.fabrikam.com

Fabrikam, Inc.

  1. Форматирайте клетките като таблица и наречете таблицата URL.

  2. Създайте релация между URL и таблицата, която съдържа имената на производителите – DimProduct:

    1. Щракнете върху Данни > Зависимости. Ще се отвори диалоговият прозорец "Създаване на зависимост".

    2. Щракнете върху Създай.

    3. В "Таблица" изберете DimProduct.

    4. В "Колона" изберете Manufacturer.

    5. В "Свързана таблица" изберете URL.

    6. В "Свързана колона (основна)" изберете ManufacturerID.

За да сравните резултатите преди и след операцията, стартирайте нов отчет на Power View и добавете FactSales | SalesAmount, dimProduct | Manufacturer и URL | ManufacturerURL към отчет. Обърнете внимание, че URL адресите се показват като статичен текст.

Превръщането на URL адрес в активна хипервръзка изисква категоризиране. За да категоризирате колона, трябва да използвате Power Pivot.

  1. В Power Pivot, отворете URL адрес.

  2. Изберете ManufacturerURL.

  3. Щракнете върху Разширено > Свойства на отчитането > Категория на данните: некатегоризирана.

  4. Щракнете върху стрелката надолу.

  5. Изберете Уеб URL.

  6. В Excel щракнете върху Вмъкване > Power View.

  7. В "Полета на Power View" изберете FactSales | SalesAmount, dimProduct | Manufacturer и URL | ManufacturerURL. Този път URL адресите се показват като действителни хипервръзки.

Другите оптимизации в Power View включват дефиниране на набор от полета по подразбиране за всяка таблица и задаване на свойства, които определят дали редове с повтарящи си данни са агрегирани или са изредени самостоятелно. За повече информация вж. Конфигуриране на набор от полета по подразбиране за отчетите в Power View и Конфигуриране на свойства за поведение на таблици за отчетите в Power View.

Създаване на изчисляеми полета

Във втората задача, "Преглед на данните чрез използване на обобщена таблица", щракнахте върху полето SalesAmount в списъка с полетата на обобщената таблица. Тъй като SalesAmount е числова колона, тя автоматично беше поставена в областта "Стойности" на обобщената таблица. Тогава функцията за сумиране на SalesAmount стана готова да изчисли сумите от продажбите при прилагане на какъвто и да е филтър. В конкретния случай нямаше първоначални филтри, но след това бяха приложени CalendarYear, ProductSubcategoryName и BrandName.

Това, което направихте в действителност, бе да създадете неявно изчисляемо поле, улеснявайки по този начин анализа на сумите на продажбите от таблицата FactSales в сравнение с други полета, като например категория на продукт, регион и дати. Неявните изчисляеми полета се създават от Excel, когато плъзнете поле в областта "Стойности" или когато щракнете върху числово поле, както направихте със SalesAmount. Неявните изчисляеми полета представляват формули, използващи стандартни агрегатни функции, като SUM, COUNT и AVERAGE, които се създават автоматично за вас.

Има и други типове изчисляеми полета. Можете да създавате явни изчисляеми полета в Power Pivot. За разлика от неявните изчисляеми полета, които могат да се използват само в обобщените таблици, в които са създадени, явните изчисляеми полета могат да се използват във всяка обобщена таблица в работната книга или във всеки отчет, който използва модела на данните като източник на данни. С явните изчисляеми полета, създадени в Power Pivot, можете да използвате автосумиране, за да се създадат автоматично изчисляеми полета чрез стандартни агрегирания, или да създадете свои собствени, като използвате формула, създадена с помощта на изрази за анализ на данни (Data Analysis Expression, DAX).

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

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

  1. В таблицата FactSales щракнете върху колоната Печалба.

  2. Щракнете върху Изчисления > Автосумиране. Обърнете внимание, че едно ново изчисляемо поле, наречено Сума на печалбата, беше създадено автоматично в клетката в областта за изчисляване директно под колоната Печалба.

  3. Във FactSales от списъка с полета в "Лист1" на Excel щракнете върху Сума на печалбата.

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

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

  1. В таблицата FactSales щракнете върху колоната SalesKey.

  2. В Изчисления щракнете върху стрелката надолу на Автосумиране > Брой.

  3. Преименувайте новото изчисляемо поле, като щракнете с десния бутон на мишката върху Брой на SalesKey в областта за изчисления и след това изберете Преименувай. Въведете Брой и натиснете клавиша Enter.

  4. Във FactSales от списъка с полета в "Лист1" на Excel щракнете върху Брой.

Обърнете внимание, че в обобщената таблица се добавя нова колона – Брой, която показва броя на продажбите в зависимост от приложените филтри. Точно както с изчисляемото поле "Сума на продажбата", виждате колоната "Брой" с филтриране по йерархиите "Категория продукт" и "Дати".

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

  1. В таблицата FactSales щракнете върху празна клетка в областта за изчисляване. Съвет: най-горната лява клетка е чудесно място да започнете да поставяте своите изчисляеми полета. Това улеснява намирането им. Можете да премествате всяко изчисляемо поле в областта за изчисляване.

  2. В лентата за формули въведете и използвайте IntelliSense, за да създадете следната формула: Percentage of All Products:=[Count]/CALCULATE([Count], ALL(DimProduct))

  3. Натиснете ENTER, за да приемете формулата.

  4. В Excel, в "Лист1", в списъка с полета, във FactSales щракнете върху Процент на всички продукти.

  5. В обобщената таблица изберете групово колоните Процент на всички продукти.

  6. В раздела Начало щракнете върху Число > Процент. Използвайте две цифри след десетичния знак за форматиране на всяка нова колона.

Това, което новото изчисляемо поле прави, е да изчисли процента на общите продажби за даден контекст за филтриране. В този случай нашият контекст за филтриране все още са йерархиите "Категория продукт" и "Дати". Можете да видите например, че продажбите на компютри като процент от общите продажби на продукти се увеличават с течение на годините.

Създаването на формули както за изчисляеми колони, така и за изчисляеми полета ще бъде доста лесно за вас, ако сте запознати със съставянето на формули в Excel. Независимо дали сте запознати с формулите на Excel, или не, добър начин да научите основите на DAX формулите е да преминете през уроците в Бърз старт: Научете основите на DAX за 30 минути.

Записване на работата ви

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

Следващи стъпки

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

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

Опитайте да подобрите своя модел на данни с цел създаване на по-добри отчети в Power View, като изпълните следното: Урок: Оптимизиране на модела на данните за създаване на отчети в Power View

Разширете уменията си в Office
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

Беше ли полезна тази информация?

Благодарим ви за обратната връзка!

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×