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

Отнася се за

Excel за Microsoft 365 Excel 2024 Excel 2021

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

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

Предварителни изисквания

Какво е Power Pivot?

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

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

Excel за Microsoft 365 – Get & Transform (Power Query) е интегрирана с Excel в раздела "Данни".

Първи стъпки

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

Създайте нова работна книга или отворете такава, която не съдържа данните.
На лентата в Excel за Microsoft 365 изберете раздела "Данни". В секцията "Получаване & трансформация на данни" изберете "Получаване на данни", за да импортирате данни от произволен брой външни източници на данни, като например текстов файл, работна книга на Excel, уеб сайт, Microsoft Access SQL Server или друга релационна база данни, съдържаща множество свързани таблици.
Excel ви подканя да изберете една или повече таблици. Ако искате да получите множество таблици от един и същ източник на данни, отметнете квадратчето " Избор на няколко елемента ".
1. Изберете трансформация. Когато изберете няколко таблици, Excel автоматично създава модел на данни вместо вас. За повече подробности вижте: Създаване, зареждане или редактиране на заявка в Excel (Power Query).
  
  Забележка
  
  За тези примери използваме работна книга на Excel с измислени подробности за учениците за класовете и оценките. Можете да изтеглите нашата примерна работна книга за модела на данни за ученици и да продължите. Можете също да изтеглите версия със завършен модел на данни.
Сега имате модел на данни, който съдържа всички таблици, които сте импортирали, и те ще се показват в списъка с полета на обобщената таблица.

Забележка

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

Съвет

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

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

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

Отидете на"Управлениена Power Pivot>".
On the Home tab, select Diagram View.
Всички импортирани таблици ще се покажат и може да ви отнеме известно време, за да ги преоразмерите в зависимост от това колко полета има всяка от тях.
След това плъзнете полето за първичен ключ от една таблица в друга. Следният пример е изгледът на диаграма на нашите таблици за ученици:

Създадохме следните връзки:
- 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.