Модел на данни на Excel (Съдържание)

  • Въведение в модела данни в Excel
  • Как да създадете модел на данни в Excel?

Въведение в модела данни в Excel

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

В нашата статия ще се опитаме да покажем как да създадем въртяща се таблица от две таблици, като използваме функцията Data Model, като по този начин установим връзка между два обекта на таблицата и по този начин създаваме PivotTable.

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

Нека разберем как да създадем модела на данни в Excel с няколко примера.

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

Пример №1

  • Имаме списък с продукти и имаме код на стелажи за всеки продукт. Нуждаем се от таблица, в която имаме описанието на стелажа, заедно с кодовете на стелажите. И така, как да включим описанията на стелажите срещу всеки код на стелажа? Може би много от нас биха прибягнали до използването на VLOOKUP тук, но напълно ще премахнем необходимостта от използване на VLOOKUP тук, използвайки Excel Data Model.

  • Таблицата вляво е таблицата с данни, а таблицата вдясно е таблицата за търсене. Както можем да видим от данните, е възможно да се създаде връзка въз основа на общи колони.

  • Сега Моделът на данните е съвместим само с обекти на таблица. Така че понякога може да се наложи да преобразувате набори от данни в обекти на таблица. За целта следвайте стъпките по-долу.
  1. Щракнете с левия бутон навсякъде в набора от данни.
  2. Щракнете върху раздела Вмъкване и отворете Таблица в групата таблици или просто натиснете Ctrl + T.
  3. Премахнете отметката или поставете отметка в My Table има опцията Header. В нашия пример той наистина има заглавка. Щракнете върху OK.
  4. Въпреки че все още сме фокусирани върху новата таблица, трябва да предоставим име, което има значение в полето Име (вляво от лентата с формули).

В нашия пример сме нарекли таблицата Персонал.

  • Сега трябва да направим същия процес и за таблицата за търсене и да го наречем Shelf Code.

Създаване на връзка

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

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

  • Сега основната таблица е таблицата, която съдържа данните. Това е основната таблица с данни - Таблица5. От друга страна, свързаната таблица е таблицата с данни за търсенето - тя е нашата таблица за търсене ShelfCodesTable. Основната таблица е тази, която се анализира въз основа на таблицата за търсене, която съдържа данни за търсене, които ще направят отчетените данни в крайна сметка по-смислени.

  • И така, общата колона между двете таблици е колоната с код на шелфа. Това е, което използвахме за установяване на връзката между двете таблици. Идвайки към колоните, колоната (чуждестранна) е тази, която се отнася до таблицата с данни, където може да има дублирани стойности. От друга страна, свързаната колона (първична) се отнася до колоната в таблицата за търсене, където имаме уникални стойности. Ние просто настройваме полето за търсене на стойности от таблицата за търсене в таблицата с данни.
  • След като настроим това, Excel ще създаде връзка между двамата зад сцената. Той интегрира данните и създава модел на данни въз основа на общата колона. Това е не само светлина за изискванията към паметта, но и много по-бързо, отколкото използването на VLOOKUP в големи работни книги. След като дефинира модела на данни, Excel ще третира тези обекти като таблици за модел на данни вместо таблица на работен лист.
  • Сега, за да видим за какво се е занимавал Excel, можем да кликнете върху Управление на модели на данни в Data -> Tools Data.

  • Можем да получим и схематичното представяне на модела на данни, като променим изгледа. Ще кликнем върху опцията Преглед. Това ще отвори опциите за изглед. След това ще изберем изглед на диаграма. Тогава ще видим схематичното представяне, показващо двете таблици и връзката между тях, т.е. общата колона - Код на шелфа.

  • Диаграмата по-горе показва взаимоотношение между много и много между уникалните стойности на таблицата за търсене и таблицата с данни с дублирани стойности.
  • Сега ще трябва да създадем въртяща се таблица. За целта ще отидем в раздела Вмъкване и след това щракнете върху опцията Pivot Table.

В диалоговия прозорец Създаване на оборотна таблица на Pivot таблицата ще изберем източника като „Използване на модела данни на тази работна книга“.

  • Това ще създаде Pivot таблицата и можем да видим, че и двете изходни таблици са налични в източника раздел.

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

  • Ще изберем персонал в секцията редове от таблица 5 (таблица с данни), последвана от описание (таблица за търсене).

  • Сега ще плъзнем кода на шелфа от таблица 5 в секцията Стойности.

  • Сега ще добавим месеци от таблица 5 към раздела Редове.

  • Или можем да добавим месеците като филтър и да го добавим в секцията Филтри.

Пример №2

  • Сега господин Басу управлява фабрика, наречена Basu Corporation. Г-н Басу се опитва да оцени приходите за 2019 г. въз основа на данните от 2018 г.
  • Имаме таблица, в която имаме приходите за 2018 г. и последващите приходи на различни инкрементални нива.

  • И така, имаме приходи за 2018 г. - 1, 5 милиона долара, а минималният ръст, очакван през следващата година, е 12%. Г-н Басу иска таблица, която ще показва приходите на различни инкрементални нива.
  • Ще създадем следната таблица за прогнозите на различни инкрементални нива за 2019 г.

  • Сега ще дадем на първия ред за приходите препратка към прогнозния минимален приход за 2019 г., т.е. 1, 68 милиона долара.

  • След използване на формулата отговорът е показан по-долу.

  • Сега ще изберем цялата таблица, т.е. D2: E12 и след това ще отидем в Data -> прогноза -> What-If Analysis -> Table Data.

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

  • След като щракнем върху OK, анализът What-If автоматично ще попълни таблицата с прогнозираните приходи в различните инкрементни проценти.

Пример №3

  • Нека предположим, че имаме същия сценарий като по-горе, с изключение на това, че сега имаме и друга ос, която трябва да разгледаме. Да предположим, че освен да показваме прогнозните приходи през 2019 г. въз основа на данните за 2018 г. и минималния очакван темп на растеж, сега имаме и прогнозната норма на дисконтиране.

  • Първо ще имаме таблица, показана по-долу.

  • Сега ще дадем позоваване на минималния прогнозен приход за 2019 г., т.е. клетка B5 до клетка D8.

  • Сега ще изберем цялата таблица, т.е. D8: J18, след което ще отидем в Data -> прогноза -> What-If Analysis -> Data Data.

  • Това ще отвори диалоговия прозорец Таблица с данни. Тук ще въведем минималния процент на нарастване от клетка B3 в клетката за въвеждане на колони. Причината за това е, че прогнозираните ни прогнозни проценти на растеж в таблицата са подредени по колонен начин. Сега ще добавим и допълнителния процент на намаление от клетка B4 в клетката Row Input. Причината за това е, че нашите прогнозни проценти на отстъпки в таблицата са подредени по ред.

  • Щракнете върху OK. Това ще направи анализът What-If автоматично да попълни таблицата с прогнозните приходи в различните инкрементални проценти според процентите на отстъпките.

Неща, които трябва да запомните за модела данни в Excel

  • При успешно изчисляване на стойностите от таблицата с данни, просто отмяна, т.е. Ctrl + Z, няма да работи. Възможно е обаче ръчно да се изтрият стойностите от таблицата.
  • Не е възможно да изтриете една клетка от таблицата. Той е описан като масив вътрешно в Excel, следователно ще трябва да изтрием всички стойности.
  • Трябва правилно да изберете клетката за въвеждане на редове и клетката за въвеждане на колони.
  • Таблицата с данни, за разлика от Pivot таблицата, не е необходимо да се обновява всеки път.
  • Използвайки Модела на данните в Excel, ние можем не само да подобрим производителността, но и да опростим изискванията за паметта в големи работни листове.
  • Моделите данни също правят нашия анализ много по-опростен в сравнение с използването на редица сложни формули в цялата работна книга.

Препоръчителни статии

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

  1. Формула бар в Excel
  2. Печат на мрежи в Excel
  3. Гледайте Прозорец в Excel
  4. Excel SUMIFS с дати

Категория:

Excel Съвети