Създаване на модел на данни в Excel

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

Преди да можете да започнете да работите с модела на данни, трябва да получите някои данни. За целта ще използваме средата Get & Transform (Power Query), така че може да искате да направите стъпка назад и да гледате видео или да следвате нашето ръководство за обучение за Получаване на & трансформация и Power Pivot.

Какво е Power Pivot?

Excel 2016 & Excel за Microsoft 365 – Power Pivot е включен в лентата.

Къде се получава & трансформация (Power Query)?

Excel 2016 & Excel за Microsoft 365 – "Получаване на & трансформация" (Power Query) е интегрирано с Excel в раздела " Данни ".

Първи стъпки

Първо, трябва да получите някои данни.

В Excel 2016 и Excel за Microsoft 365 използвайте >получаване на & трансформиране на данни > Получаване на данни за импортиране на данни от произволен брой външни източници на данни, като например текстов файл, работна книга на Excel, уеб сайт, Microsoft Access, SQL Server или друга релационна база данни, която съдържа множество свързани таблици.
Excel ви подканя да изберете таблица. Ако искате да получите множество таблици от един и същ източник на данни, отметнете опцията Разреши избирането на множество таблици . Когато изберете няколко таблици, Excel автоматично създава модел на данни вместо вас.

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

Ако трябва да редактирате първичните данни, можете да изберете опцията Редактиране . За повече подробности вижте: Въведение в редактора на заявки (Power Query).

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

Забележки:

Моделите се създават неявно, когато импортирате в Excel две или повече таблици едновременно.
Моделите се създават изрично, когато използвате Power Pivot добавка за импортиране на данни. В добавката моделът е представен в оформление с раздели, подобно на Excel, където всеки раздел съдържа таблични данни. Вижте Получаване на данни с помощта на добавката Power Pivot, за да научите основите на импортирането на данни с помощта на база данни на SQL Server.
Моделът може да съдържа само една таблица. За да създадете модел, базиран само на една таблица, изберете таблицата и щракнете върху Добавяне към модела за данни в Power Pivot. Можете да направите това, ако искате да използвате функции на Power Pivot, като например филтрирани набори от данни, изчисляеми колони, изчисляеми полета, KPI и йерархии.
Релациите между таблиците могат да бъдат създавани автоматично, ако сте импортирали свързани таблици, които имат релации с основен и чужд ключ. Excel обикновено може да използва информацията за импортираната релация като основа за релациите между таблиците в модела на данни.
За съвети как да намалите размера на модел на данни, вижте Създаване на ефективен по отношение на паметта модел на данни чрез Excel и Power Pivot.
За по-нататъшно проучване вижте Урок: Импортиране на данни в Excel и Създаване на модел на данни.

Съвет: Как да разберете дали вашата работна книга има модел на данни? Отидете вPower Pivot > Управление. Ако виждате данни, подобни на работен лист, значи съществува модел. Вижте: Разберете кои източници на данни се използват в модела на данни на работна книга , за да научите повече.

Създаване на релации между таблиците

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

Отидете на Power Pivot > Управление.
В раздела Начало изберете Изглед на диаграма.
Ще бъдат показани всички импортирани таблици и може да ви отнеме известно време, за да ги преоразмерите в зависимост от това колко полета има всяко от тях.
След това плъзнете полето за първичен ключ от една таблица в следващата. Следващият пример е изгледът на диаграма на нашите таблици на учениците:

Създадохме следните връзки:
- tbl_Students | ИД на студент > tbl_Grades | ИД на студент
  
  С други думи плъзнете полето ИД на студент от таблицата Ученици в полето ИД на студент в таблицата Оценки.
- tbl_Semesters | ИД на семестъра > tbl_Grades | Семестър
- tbl_Classes | Номер на клас > tbl_Grades | Номер на клас
Забележки:
- Не е необходимо имената на полетата да са еднакви, за да се създаде релация, но трябва да са от един и същ тип данни.
- Конекторите в изгледа на диаграма имат "1" от едната страна и "*" от другата. Това означава, че има релация "един към много" между таблиците и това определя как данните се използват във вашите обобщени таблици. Вижте: Релации между таблици в модел на данни , за да научите повече.
- Конекторите показват само, че има релация между таблиците. Те всъщност няма да ви покажат кои полета са свързани помежду си. За да видите връзките, отидете на Power Pivot > Управление на > проектиране > релации > Управление на релации. В Excel можете да отидете на Данни > Релации.

Използване на модел на данни за създаване на обобщена таблица или обобщена диаграма

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

В Power Pivotотидете на Управление.
В раздела Начало изберете Обобщена таблица.
Изберете къде искате да се постави обобщената таблица: нов работен лист или текущото местоположение.
Щракнете върху OK и Excel ще добави празна обобщена таблица с екрана Списък на полетата, показан отдясно.

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

Добавяне на съществуващи, несвързани данни към модел на данни

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

Започнете, като изберете произволна клетка в данните, които искате да добавите към модела. Това може да е всякакъв диапазон от данни, но данните са форматирани като таблица на Excel .
Използвайте някой от следните подходи, за да добавите данните:
Щракнете върху Power Pivot > Добавяне към модела за данни.
Щракнете върху Вмъкване > Обобщена таблица, а после отметнете Добавяне на тези данни към модела на данни в диалоговия прозорец "Създаване на обобщена таблица".

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

Добавяне на данни към таблица на Power Pivot

В Power Pivot не можете да добавите ред в таблицата, като директно започнете да пишете в нов ред, както можете да направите в работен лист на Excel. Можете обаче да добавите редове , като копирате и поставите или актуализирате изходните данни и обновите модела на Power Pivot.