Модел на данни в Excel | Как да създам модел на данни? (с примери)

Какво представлява моделът на данни в Excel?

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

Обяснение

Той позволява интегриране на данни от множество таблици чрез създаване на връзки, базирани на обща колона.
Моделите на данни се използват прозрачно, като предоставят таблични данни, които могат да се използват в обобщена таблица в Excel и обобщени диаграми в Excel. Той интегрира таблиците, позволявайки обширен анализ с помощта на обобщени таблици, Power Pivot и Power View в Excel.
Моделът за данни позволява зареждане на данни в паметта на Excel.
Запазва се в паметта там, където не можем директно да го видим. Тогава Excel може да бъде инструктиран да свързва данни помежду си, като използва обща колона. Частта „Модел“ на модела на данни се отнася до това как всички таблици са свързани помежду си.
Моделът за данни може да получи достъп до цялата информация, от която се нуждае, дори когато информацията е в множество таблици. След създаването на модела на данни, Excel разполага с данните в паметта си. С данните в паметта, данните могат да бъдат достъпни по много начини.

Примери

Можете да изтеглите този шаблон на модел на данни от Excel тук - шаблон на модел на данни на Excel

Пример # 1

Ако имаме три набора от данни, свързани с продавача: Първият, съдържащ информация за приходите, втори, съдържащ доходи на продавач, и трети, съдържащ разходи на продавача.

За да свържем тези три набора от данни и да установим връзка с тях, ние правим Модел на данни със следните стъпки:

Преобразувайте наборите от данни в обекти на таблица:

Не можем да създадем връзка с обикновени набори от данни. Моделът на данни работи само с обекти на Excel Tables. Да го направя:

Стъпка 1 - Щракнете навсякъде в набора от данни, след това кликнете върху раздела „Вмъкване“ и след това върху „Таблица“ в групата „Таблици“.

Стъпка 2 - Поставете или махнете отметката от опцията: „Моята таблица има заглавки“ и щракнете върху OK.

Стъпка 3 - С избраната нова таблица въведете името на таблицата в „Име на таблица“ в групата „Инструменти“.

Стъпка 4 - Сега можем да видим, че първият набор от данни се преобразува в обект „Таблица“. При повтаряне на тези стъпки за другите два набора от данни виждаме, че те също се преобразуват в обекти „Таблица“, както е показано по-долу:

Добавяне на обектите „Таблица“ към модела на данни: чрез връзки или връзки.

Чрез връзки

Изберете една таблица и кликнете върху раздела „Данни“ и след това върху „Връзки“.

В получения диалогов прозорец има икона на „Добавяне“. Разгънете падащото меню „Добавяне“ и кликнете върху „Добавяне към модела на данни“.

Щракнете върху „Таблици“ в получения диалогов прозорец и след това изберете една от таблиците и щракнете върху „Отваряне“.

При това ще се създаде модел с данни на работна книга с една таблица и се появява диалогов прозорец, както следва:

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

Вече можем да видим, че и трите таблици се появяват в Workbook Connections.

Чрез връзки

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

Кликнете върху раздела „Данни“ и след това върху „Връзки“.

Ще видим празен диалогов прозорец, тъй като няма текущи връзки.

Кликнете върху „Ново“ и ще се появи друг диалогов прозорец.

Разгънете падащите менюта „Таблица“ и „Свързана таблица“: Появява се диалоговият прозорец „Създаване на връзка“, за да изберете таблиците и колоните, които да използвате за връзка. В разгъването на „Таблици“ изберете набора от данни, който искаме да анализираме по някакъв начин, а в „Свързана таблица“ изберете набора от данни, който има справочни стойности.
Справочната таблица в Excel е по-малката таблица в случай на една към много връзки и не съдържа повтарящи се стойности в общата колона. В разгъването на „Column (Foreign)“ изберете общата колона в основната таблица, в „Related Column (Primary)“ изберете общата колона в свързаната таблица.

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

Ако повторим тези стъпки, за да свържем другите две таблици: Таблица на приходите с таблица на разходите, те също се свързват в модела на данни, както следва:

Excel сега създава връзката зад кулисите, като комбинира данни в модела на данни въз основа на обща колона: ИД на продавач (в този случай).

Пример # 2

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

Кликнете върху „Вмъкване“ -> „Осева таблица“.

В получения диалогов прозорец щракнете върху опцията, в която се посочва: „Използване на външен източник на данни“ и след това щракнете върху „Избор на връзка“.

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

Изберете опцията „Нов работен лист“ в местоположението и кликнете върху „OK“.

Екранът Полета на обобщената таблица ще покаже обекти на таблици.

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

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

Това е от огромна помощ в случай на модел / таблица, съдържаща голям брой наблюдения.

И така, можем да видим, че Pivot Table незабавно използва модела на данни (взимайки го чрез избор на връзка) в паметта на Excel, за да показва връзките между таблиците.

Неща за запомняне

Използвайки модела за данни, можем да анализираме данни от няколко таблици наведнъж.
Чрез създаването на взаимоотношения с модела на данни надминаваме необходимостта от използване на формули VLOOKUP, SUMIF, INDEX и MATCH, тъй като не е необходимо да получаваме всички колони в една таблица.
Когато наборите от данни се импортират в Excel от външни източници, тогава моделите се създават имплицитно.
Взаимоотношенията с таблици могат да бъдат създадени автоматично, ако импортираме свързани таблици, които имат връзки на първичен и външен ключ.
Докато създаваме връзки, колоните, които свързваме в таблици, трябва да имат същия тип данни.
С обобщените таблици, създадени с модела на данните, можем да добавим и срезачи и да нарязваме обобщените таблици на всяко поле, което искаме.
Предимството на модела за данни пред функциите LOOKUP () е, че той изисква значително по-малко памет.
Excel 2013 поддържа само една към една или една към много връзки, т.е. една от таблиците не трябва да има дублирани стойности в колоната, към която свързваме.