Директно захранване на вътрешния модел на данни на Excel

Можете да използвате комбинация от централни таблици и връзки за данни на Excel, за да взаимодействате директно с вътрешния модел на данни, без добавката Power Pivot. Това е полезно, ако използвате версии на Excel, които не се доставят с добавката Power Pivot, например когато използвате Microsoft Office, или домашно, или издание за малък бизнес. Всяка работна книга на Excel 2013 и 2016 идва с вътрешен модел на данни.

Представете си, че имате таблицата Транзакции, която виждате тук, а на друг работен лист имате таблица Служители, която съдържа информация за служителите.

Тази таблица показва транзакции по номер на служител.

Тази таблица предоставя информация за служителите: име, фамилия и длъжност.

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

Щракнете вътре в таблицата с данни за транзакции и стартирайте нова въртяща таблица, като изберете Вмъкване ➪ Пивотна таблица от лентата.

В диалоговия прозорец Създаване на обобщена таблица изберете опцията Добавяне на тези данни към модела на данни.

Когато създавате нова централна таблица от таблицата за транзакции, не забравяйте да изберете Добавяне на тези данни към модела на данни.

Щракнете вътре в таблицата с данни за служителите и започнете нова централна таблица.
Отново, не забравяйте да изберете опцията Добавяне на тези данни към модела на данни, както е показано.
Забележете, че диалоговите прозорци Създаване на обобщена таблица се позовават на именувани диапазони. Тоест на всяка таблица е дадено конкретно име. Когато добавяте данни към вътрешния модел на данни, най-добрата практика е да наименувате таблиците с данни. По този начин можете лесно да разпознаете вашите таблици във вътрешния модел на данни. Ако не наименувате таблиците си, вътрешният модел на данни ги показва като Range1, Range2 и т.н.

Създайте нова централна таблица от таблицата Служители и изберете Добавяне на тези данни към модела на данни.

За да дадете име на таблицата с данни, просто маркирайте всички данни в таблицата и след това изберете командата Формули→ Дефиниране на име от лентата. В диалоговия прозорец въведете име за таблицата. Повторете за всички останали таблици.

След като и двете таблици бъдат добавени към вътрешния модел на данни, отворете списъка с полета на обобщената таблица и изберете селектора ВСИЧКИ. Тази стъпка показва и двата диапазона в списъка с полета.

Изберете ВСИЧКИ в списъка с полета на обобщената таблица, за да видите и двете таблици във вътрешния модел на данни.

Изградете въртящата се таблица както обикновено. В този случай Job_Title се поставя в областта на ред, а Sales_Amount отива в областта на стойностите.
Както можете да видите тук, Excel веднага разпознава, че използвате две таблици от вътрешния модел на данни и ви подканва да създадете връзка между тях. Имате възможност да позволите на Excel да открие автоматично връзките между вашите таблици или да щракнете върху бутона Създаване. Винаги създавайте връзките сами, за да избегнете всяка възможност Excel да се обърка.

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

Щракнете върху бутона Създаване.
Excel отваря диалоговия прозорец Създаване на връзка, показан тук. Там избирате таблиците и полетата, които дефинират връзката. Можете да видите, че таблицата Transactions има поле Sales_Rep. Той е свързан с таблицата Employees чрез полето Employee_Number.

Изградете подходящата връзка с помощта на падащите списъци Таблица и Колона.

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

Постигнахте целта си да показвате продажбите по длъжност.

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

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

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

След като присвоите таблици към вътрешния модел на данни, може да се наложи да коригирате връзките между таблиците. За да направите промени във връзките във вътрешен модел на данни, щракнете върху раздела Данни на лентата и изберете командата Връзки. Отваря се диалоговият прозорец Управление на връзките, показан тук.

Диалоговият прозорец Управление на връзките ви позволява да правите промени във връзките във вътрешния модел на данни.

Тук ще намерите следните команди:

• Ново: Създайте нова връзка между две таблици във вътрешния модел на данни.
• Редактиране: Променете избраната връзка.
• Активиране: Приложете избраната връзка, като кажете на Excel да вземе предвид връзката, когато агрегира и анализира данните във вътрешния модел на данни.
• Деактивиране: Изключете избраната връзка, като кажете на Excel да игнорира връзката, когато агрегира и анализира данните във вътрешния модел на данни.
• Изтриване: Премахване на избраната връзка.