Направете анализа на данни по-задълбочен, като създадете релации между различните таблици. Релация означава връзка между две таблици, които съдържат данни: една колона във всяка таблица е основата на релацията. За да видите защо релациите са полезни, представете си, че проследявате данни за поръчки на клиенти на вашия бизнес. Може да проследите всички данни в една единствена таблица, която има структура по следния начин:
| ИД на клиент | Име | Начало | Процент_отстъпка | ИД_поръчка | ДатаПоръчка | Продукт | Количество |
|---|---|---|---|---|---|---|---|
| 1 | Христов | chris.ashton@contoso.com | 0,05 | 256 | 07.01.2010 | Компактни цифрови | 11 |
| 1 | Христов | chris.ashton@contoso.com | 0,05 | 255 | 03.01.2010 | Огледално-рефлексни фотоапарати | 15 |
| 2 | Димитров | michal.jaworski@contoso.com | 0,10 | 254 | 03.01.2010 | Бюджетни видеокамери | 27 |
Този метод работи, но включва съхранението на много излишни данни, напр. имейл адресите на клиентите за всяка поръчка. Съхранението е евтино, но ако имейл адресът се промени, трябва да актуализирате всеки ред за този клиент. Едно решение на този проблем е да разделите данните в няколко таблици и да дефинирате релации между тях. Това е подходът, който се използва в релационни бази данни като SQL Server. Например една база данни, която вие импортирате, може да представя данни за поръчките, като се използват три свързани таблици:
Клиенти
| [ИД_клиент] | Име | Имейл |
|---|---|---|
| 1 | Христов | chris.ashton@contoso.com |
| 2 | Димитров | michal.jaworski@contoso.com |
Отстъпки_клиент
| [ИД_клиент] | Процент_отстъпка |
|---|---|
| 1 | 0,05 |
| 2 | 0,10 |
Поръчки
| [ИД_клиент] | ИД_поръчка | ДатаПоръчка | Продукт | Количество |
|---|---|---|---|---|
| 1 | 256 | 07.01.2010 | Компактни цифрови | 11 |
| 1 | 255 | 03.01.2010 | Огледално-рефлексни фотоапарати | 15 |
| 2 | 254 | 03.01.2010 | Бюджетни видеокамери | 27 |
Релации съществуват в рамките на модела на данни – такъв, който изрично създавате вие или който Excel създава автоматично от ваше име, когато импортирате едновременно множество таблици. Можете също да използвате добавката Power Pivot, за да създадете или управлявате модела. За подробности вж . "Създаване на модел на данни в Excel ".
Ако използвате добавката Power Pivot, за да импортирате таблици от същата база данни, Power Pivot може да открие релациите между таблиците, базирани на колоните, които са в [скоби], и да възпроизведе тези релации в модела на данни, който бива създаден на заден план. За повече информация вж. "Автоматично откриване и подразбиране на релации " в тази статия. Ако импортирате таблици от няколко източника, може ръчно да създадете релации, както е описано в "Създаване на релация между две таблици".
Колони и ключове
Релациите са базирани на колони във всяка таблица, която съдържа еднакви данни. Можете например да свържете таблица "Клиенти " с таблица "Поръчки ", ако всяка от тях съдържа колона с клиентския ИД. В примера имената на колоните са същите, но това не е задължително. Едната може да се казва CustomerID, а другата CustomerNumber, стига всички редове в таблица "Поръчки" да съдържат ИД като този в таблица "Клиенти".
В една релационна база данни има няколко типа ключове. Ключът обикновено е колона със специални свойства. Познанията относно предназначението на всеки ключ може да ви помогнат да управлявате модел на данни, съдържащ няколко таблици, който предоставя данни на обобщена таблица, обобщена диаграма или отчет на Power View.
Въпреки че има много видове ключове, ето най-важните за нашата цел тук:
- Основен ключ: уникално идентифицира ред в таблица, като например CustomerID в таблица "Клиенти ".
- Алтернативен ключ (или ключ кандидат): колона, различна от основния ключ, който е уникален. Например таблица "Служители" може да съхранява ИД на служителите и техния социално-осигурителен номер, като и двата са уникални.
- Чужд ключ: колона, която се обръща към уникална колона в друга таблица, като например CustomerID в таблица "Поръчки ", която се обръща към CustomerID в таблица "Клиенти".
В модела на данни основният ключ или алтернативният ключ се наричат свързана колона. Ако една таблица съдържа основен и алтернативен ключ, може да използвате който и да е от двата като основа за релация на таблица. Чуждият ключ се нарича колона източник или просто колона. В нашия пример ще бъде дефинирана релация между CustomerID в таблицата "Поръчки " (колоната) и CustomerID в таблицата "Клиенти " (колоната за справка). Ако импортирате данни от релационни база данни, Excel по подразбиране избира чуждия ключ от едната таблица и съответстващия основен ключ от другата таблица. Вие обаче можете да използвате всяка колона, която съдържа уникални стойности за справочната колона.
Избира диапазона на показваните дати
Релацията между клиент и поръчка е релация от тип "един към много". Всеки клиент има няколко поръчки, но една поръчка не може да има няколко клиента. Друга важна релация между таблици е "един към един". В нашия пример тук таблицата CustomerDiscounts , която дефинира една отстъпка за всеки клиент, има релация "един към един" с таблица "Клиенти".
Тази таблица показва релациите между трите таблици ("Клиенти", "Отстъпки за клиенти" и "Поръчки"):
| Зависимост | Тип | Справочна колона | Колона |
|---|---|---|---|
| Customers-CustomerDiscounts | "един към един" | Customers.CustomerID | CustomerDiscounts.CustomerID |
| Customers-Orders | "един към много" | Customers.CustomerID | Orders.CustomerID |
Забележка
Релациите тип "много към много" не се поддържат в модел на данни. Пример за релация тип "много към много" е пряката релация между "Продукти" и "Клиенти", в която един клиент може да купи много продукти и един и същ продукт да бъде продаден на много клиенти.
Релации и представяне
След създаването на релация обикновено Excel трябва да пресметне отново всички формули, които използват колони от таблиците в новосъздадената релация. Обработването може да отнеме известно време в зависимост от обема на данните и сложността на релациите. За повече подробности вж. "Преизчисляване на формули".
Множество релации между таблици
Даден модел на данни може да съдържа няколко релации между две таблици. За да създаде верни изчисления, Excel се нуждае от единствен път от една таблица до друга. Затова само една релация между всяка двойка от таблици е активна по едно и също време. Въпреки че другите не са активни, можете да указвате неактивна релация във формули и заявки.
В изглед на диаграма активната релация се отбелязва с непрекъсната линия, а неактивните – с прекъсната. Например в AdventureWorksDW2012 таблица DimDate съдържа колона DateKey, която се обръща към три различни колони в таблица FactInternetSales: OrderDate, DueDate и ShipDate. Ако активната релация е между DateKey и OrderDate, тя ще бъде релацията, използвана по подразбиране във формули, освен ако не укажете друго.
Изисквания към релация на таблица
Дадена релация може да бъде създадена, ако бъдат изпълнени следните изисквания:
| "критерий" | Описание |
|---|---|
| Уникален идентификатор за всяка таблица | Всяка таблица трябва да има една колона, която уникално да идентифицира всеки ред в тази таблица. Колоната често се нарича основен ключ. |
| Уникални справочни колони | Стойностите на данните в справочната колона трябва да бъдат уникални. С други думи, колоната не може да съдържа дублиращи се стойности. В модела на данни нулите и празните низове са равностойни на празни стойности, които представляват различни стойности на данни. Това означава, че не може да има множество нули в справочната колона. |
| Съвместими типове данни | Типовете данни в колоната източник и справочната колона трябва да са съвместими. За повече информация относно типовете данни вж. "Типове данни, поддържани в моделите на данни". |
Неподдържани функции за бази данни в модела на данни на Excel
В модела на данни не може да се създава релация на таблица, ако ключът е комбиниран. Освен това сте ограничени при създаването на релации от типа "един към един" и "един към много". Не се поддържат други типове релации.
Комбинирани ключове и справочни колони
Комбинираният ключ е съставен от повече от една колона. Моделите на данни не могат да използват комбинирани ключове: една таблица винаги трябва да съдържа точно една колона, която уникално да идентифицира всеки ред в таблицата. Ако импортирате таблици, които съдържат съществуваща релация, базирани на комбиниран ключ, съветникът за импортиране на таблици в Power Pivot ще игнорира релацията, тъй като тя не може да бъде създадена в модела.
За да създадете релация между две таблици, които съдържат множество колони, дефиниращи основния и чуждия ключ, първо комбинирайте стойностите, за да създадете една колона с ключ, преди да създадете релацията. Може да направите това, преди да импортирате данните или като създадете изчисляема колона в модела на данни, използвайки добавката Power Pivot.
Релации тип "много към много"
Модел на данни не може да съдържа релации тип "много към много". Не може просто да добавите съединителни таблици в модела. Въпреки това може да използвате функции DAX, за да моделирате релации тип "много към много".
Рефлексивни съединения и цикли
Рефлексивните съединения не са позволени в модела на данни. Рефлексивно съединение представлява рекурсивна релация между една таблица и нея самата. Рефлексивните съединения често се използват за дефиниране на йерархии от тип родител-наследник. Например може да съедините таблица "Служители" с нея самата, за да получите йерархия, която да показва управленската верига в даден бизнес.
Excel не позволява създаването на цикли между релации в една работна книга. С други думи, следният набор от релации е забранен.
Таблица 1, колона а към Таблица 2, колона f
Таблица 2, колона f към Таблица 3, колона n
Таблица 3, колона n към Таблица 1, колона а
Ако опитате да създадете зависимост, която ще създаде цикъл, се генерира грешка.
Автоматично откриване и подразбиране на зависимости в Power Pivot
Едно от предимствата на импортирането на данни посредством добавката Power Pivot е, че понякога Power Pivot може да открива релации и да създава нови такива в модела на данни, който създава в Excel.
Когато импортирате множество таблици, Power Pivot автоматично открива всички съществуващи релации между таблиците. Също така, когато създавате обобщена таблица, Power Pivot анализира данните в таблиците. Той открива възможни релации, които не са били дефинирани, и предлага подходящи колони, които да се включат в тези релации.
Алгоритъмът за откриване използва статистически данни за стойностите и метаданните на колоните, за да направи изводи за вероятността на релациите.
- Типовете данни във всички свързани колони трябва да са съвместими. За автоматично откриване се поддържат само цели числа и текстови типове данни. За повече информация относно типовете данни вж. " Типове данни, поддържани в моделите на данни ".
- За да бъде открита една релация успешно, броят на уникалните ключове в справочната колона трябва да бъде по-голям от стойностите в таблицата в страна "много". С други думи, ключовата колона в страна "много" на зависимостта не трябва да съдържа стойности, които не са в ключовата колона на справочната таблица. Например представете си, че имате таблица, която изброява продукти с техните ИД (справочната таблица) и таблица с продажби, която изброява продажбите за всеки продукт (страна "много" на релацията). Ако списъкът с продажби съдържа ИД на продукта, който няма съответстващ ИД в таблица "Продукти", релацията не може да бъде създадена автоматично, а вие вероятно ще трябва да я създадете ръчно. За да може Excel да открие релацията, трябва първо да актуализирате справочна таблица "Продукти" с ИД на липсващите продукти.
- Уверете се, че името на колоната с ключа от страна "много" е сходно с името на колоната с ключа в справочната таблица. Не е необходимо имената да са едни и същи. Например в бизнес среда често имате вариации на имената на колони, съдържащи по същество едни и същи данни: EMP ID, EmployeeID, Employee ID EMP_ID и т.н. Алгоритъмът открива подобни имена и задава по-голяма вероятност за тези колони, които имат подобни или точно съвпадащи имена. Следователно, за да увеличите вероятността за създаване на релация, можете да опитате да преименувате колоните в данните, които импортирате, подобно на колоните във вашите съществуващи таблици. Ако Excel намери няколко възможни релации, той няма да създаде релация.
Тази информация може да ви помогне да разберете защо не всички релации се откриват или как промените в метаданните – като имена на полета и типове данни – могат да подобрят резултатите от автоматичното откриване на релации. За повече информация вж. "Отстраняване на неизправности при релации".
Автоматично откриване на наименувани набори
Релациите не се откриват автоматично между наименувани набори и свързани полета в обобщена таблица. Може да създадете тези релации ръчно. Ако искате да използвате автоматичното откриване на релации, премахнете всеки наименуван набор и добавете индивидуални полета от наименувания набор директно към обобщената таблица.
Подразбиране на релации
В някои случаи релациите между таблиците се свързват автоматично. Например ако създадете релация между първите два набора от таблиците по-долу, подразбира се, че съществува релация между другите две таблици и релацията се създава автоматично.
Продукти и категория – създадена ръчно
Категория и подкатегория – създадена ръчно
Продукти и подкатегория – зависимостта се подразбира
За да се свържат автоматично релациите, те трябва да вървят последователно, както е указано по-горе. Ако първоначалните релации бяха, например между "Продажби" и "Продукти" и "Продажби" и "Клиенти", релация няма да бъде подразбрана. Това е така, защото релацията между "Продукти" и "Клиенти" е релация от тип "много към много".