Exceli andmemudel (sisukord)

  • Sissejuhatus andmemudelisse Excelis
  • Kuidas luua andmemudelit Excelis?

Sissejuhatus andmemudelisse Excelis

Exceli andmemudelifunktsioon võimaldab hõlpsalt luua seoseid hõlpsa aruandluse ja nende taustandmekogumite vahel. See muudab andmete analüüsi palju lihtsamaks. See võimaldab integreerida paljude töölehtede hulgast tabelite hulgast pärinevaid andmeid, luues lihtsalt seoseid vastavate veergude vahel. See töötab täielikult stseeni taga ja lihtsustab oluliselt aruandlusfunktsioone, näiteks PivotTable jne.

Meie artiklis proovime näidata, kuidas luua andmemudeli funktsiooni abil kahest tabelist pöördetabel, luues seose kahe tabeliobjekti vahel ja luues seeläbi PivotTable'i.

Kuidas luua andmemudelit Excelis?

Mõistame, kuidas luua andmemudelit Excelis mõne näitega.

Selle andmemudeli Exceli malli saate alla laadida siit - andmemudeli Exceli mall

Näide nr 1

  • Meil on toodete loetelu ja iga toote jaoks on riiulikood. Vajame tabelit, kus oleks riiulikirjeldus koos riiulikoodidega. Kuidas lisada riiulikirjeldused igasse riiulikoodi? Võib-olla kasutavad paljud meist siin VLOOKUP-i, kuid me eemaldame vajaduse kasutada VLOOKUP-i siin, kasutades Exceli andmemudelit.

  • Vasakul olev tabel on andmetabel ja paremal olev tabel on otsingutabel. Nagu andmetest näeme, on võimalik luua seos ühiste veergude alusel.

  • Nüüd ühildub andmemudel ainult tabeliobjektidega. Niisiis, mõnikord võib osutuda vajalikuks teisendada andmekogumid tabeliobjektideks. Selleks toimige järgmiselt.
  1. Vasakklõpsake andmekogu mis tahes asukohas.
  2. Klõpsake vahekaarti Sisesta ja liikuge tabelite rühmas tabelisse või vajutage lihtsalt Ctrl + T.
  3. Tühjendage märkeruut või tehke valik Minu tabelis suvand Päis. Meie näites on sellel tõepoolest päis. Klõpsake nuppu OK.
  4. Kuigi oleme endiselt keskendunud uuele tabelile, peame väljale Nimi (valemiriba vasakule poole) lisama tähendusliku nime.

Meie näites oleme nimetanud tabeli Personnel.

  • Nüüd peame sama protsessi tegema ka otsingulaua jaoks ja panema sellele nimeks Riivakood.

Suhte loomine

Esiteks läheme vahekaardile Andmed ja valime siis Andmetööriistade alarühmas suhted. Pärast seda, kui oleme klõpsanud suvandil Suhted, siis kuna suhet pole, järelikult pole meil midagi.

Suhte loomiseks klõpsame kõigepealt nupul Uus. Peame nüüd esitama ripploendist esmased ja otsingulaudade nimed ning seejärel mainima ka veeru, mis on kahe tabeli vahel tavaline, et saaksime ripploendist tuvastada kahe tabeli vahelise seose. veergude arv.

  • Nüüd on peamine tabel tabel, millel on andmed. See on esmaste andmete tabel - tabel5. Teisest küljest on seotud tabel tabel, millel on otsimisandmed - see on meie otsingustabel ShelfCodesTable. Peamine tabel on see, mida analüüsitakse otsingutulemite tabeli põhjal, mis sisaldab otsinguandmeid, mis muudavad esitatud andmed lõpuks sisukamaks.

  • Niisiis, kahe tabeli vaheline ühine veerg on riiulikood. Seda me oleme kasutanud kahe tabeli vahelise seose loomiseks. Veergude juurde tulp (võõras) viitab andmetabelile, kus võib olla dubleerivaid väärtusi. Teisest küljest viitab seotud veerg (esmane) veerule otsingutulemites, kus meil on kordumatud väärtused. Seadistame andmevälja tabelite otsimistabelite väärtuste otsimisvälja lihtsalt.
  • Kui oleme selle üles seadnud, loob Excel seose nende kahe sündmuskoha vahel. See integreerib andmed ja loob ühise veeru alusel andmemudeli. See ei kajasta mitte ainult mälunõudeid, vaid ka palju kiiremini kui VLOOKUPi kasutamine suurtes töövihikutes. Pärast andmemudeli määratlemist töötleb Excel neid objekte töölehetabeli asemel andmemudeli tabelitena.
  • Nüüd, et näha, mida Excel on teinud, võime klõpsata käsku Halda andmemudeleid jaotises Andmed -> Andmetööriistad.

  • Andmemudeli skemaatilise esituse saame ka vaate muutmisega. Klõpsame nuppu Kuva. See avab vaatevalikud. Seejärel valime diagrammivaate. Siis näeme skemaatilist esitust, mis näitab kahte tabelit ja nendevahelisi suhteid, st ühist veergu - riiulikoodi.

  • Ülaltoodud diagramm näitab üks-ühele suhet unikaalsete otsingustabeli väärtuste ja dubleeritud väärtustega andmetabeli vahel.
  • Nüüd peame looma pöördetabeli. Selleks läheme vahekaardile Insert ja klõpsake siis suvandil Pivot Table.

Pivot-tabeli dialoogiboksis Loo Pivot-tabel valime allikaks „Kasuta selle töövihiku andmemudelit“.

  • Sellega luuakse Pivoti tabel ja näeme, et mõlemad allikatabelid on allikaosas saadaval.

  • Nüüd loome pöördetabeli, kus on näidatud iga eseme riiulil olnud inimeste arv.

  • Valime tabelist 5 (andmetabel) jaotises Ridad personali, millele järgneb kirjeldus (otsingutabel).

  • Nüüd lohistame riiulikoodi tabelist 5 väärtuste jaotisesse.

  • Nüüd lisame tabelite 5 jaotisesse Kuud.

  • Või võiksime lisada kuud filtrina ja lisada selle jaotisse Filtrid.

Näide 2

  • Nüüd on hr Basu juhtimas tehast nimega Basu Corporation. Hr Basu proovib 2019. aasta tulusid hinnata 2018. aasta andmete põhjal.
  • Meil on tabel, kus on 2018. aasta tulud ja sellele järgnenud tulud erinevatel astmelistel astmetel.

  • Niisiis, meil on 2018. aasta müügitulu - 1, 5 miljonit dollarit ja järgmisel aastal eeldatav minimaalne kasv on 12%. Hr Basu soovib tabelit, mis kajastaks tulusid erinevatel astmetel.
  • Koostame järgmise tabeli prognooside jaoks erinevatel astmetel 2019. aastaks.

  • Nüüd anname esimesele tulude reale viite 2019. aasta eeldatavale minimaalsele tulule, st 1, 68 miljonit dollarit.

  • Pärast valemi kasutamist kuvatakse vastus allpool.

  • Nüüd valime kogu tabeli, st D2: E12, ja siis minge jaotisse Data -> Forecast -> What-If Analysis -> Data Table.

  • See avab dialoogiboksi Andmetabel. Siin sisestame lahtri B4 lahtri B4 minimaalse juurdekasvu protsendi lahtrist B4. Põhjus on see, et meie prognoositavad kasvuprotsendid tabelis on paigutatud veergude kaupa.

  • Kui oleme klõpsanud nuppu OK, täidab Mis-juhul analüüs tabeli automaatselt prognoositava tuluga erineva protsendimääraga.

Näide 3

  • Oletame nüüd, et meil on sama stsenaarium kui ülalpool, välja arvatud see, et nüüd on meil ka teine ​​telg, mida kaaluda. Oletame, et lisaks 2019. aasta prognoositava tulu kuvamisele 2018. aasta andmete ja minimaalse oodatava kasvumäära alusel on meil nüüd ka hinnanguline diskontomäär.

  • Esiteks on meil allpool esitatud tabel.

  • Nüüd viidatakse 2019. aasta eeldatavale minimaalsele tulule, st lahtrile B5 lahtrile D8.

  • Nüüd valime kogu tabeli, st D8: J18, ja siis minge jaotisse Data -> Forecast -> What-If Analysis -> Data Table.

  • See avab dialoogiboksi Andmetabel. Siin sisestame lahtri B3 lahtri B3 minimaalse juurdekasvu protsendi lahtrist B3. Põhjus on see, et meie prognoositavad kasvuprotsendid tabelis on paigutatud veergude kaupa. Nüüd sisestame lahtri R4 sisendisse ka minimaalse allahindluse protsendi lahtrist B4. Põhjus on see, et meie prognoositavad allahindlusprotsendid tabelis on paigutatud ridade kaupa.

  • Klõpsake nuppu OK. See muudab nn if-analüüsi, et tabel täidetakse automaatselt prognoositava tuluga erinevatel protsendimääradel vastavalt diskontomääradele.

Excelsi andmemudeli kohta meelde tuletavad asjad

  • Andmetabelis väärtuste edukal arvutamisel lihtne Undo st Ctrl + Z ei tööta. Väärtusi on aga võimalik tabelist käsitsi kustutada.
  • Ühte lahtrit pole võimalik tabelist kustutada. Seda kirjeldatakse Excelis massiivina, seetõttu peame kustutama kõik väärtused.
  • Peame õigesti valima Rida sisestus lahtri ja Veeru sisendi lahtri.
  • Andmetabelit, erinevalt Pivoti tabelist, ei pea iga kord värskendama.
  • Kasutades Excelis andmemudelit, ei saa me mitte ainult jõudlust parandada, vaid ka suurte töölehtede mälunõuete täitmiseks.
  • Andmemudelid muudavad meie analüüsi ka palju lihtsamaks, kui kogu töövihikus kasutada mitmeid keerulisi valemeid.

Soovitatavad artiklid

See on Exceli andmemudeli juhend. Siin arutatakse, kuidas luua andmemudelit Excelis koos praktiliste näidete ja allalaaditava Exceli malliga. Võite vaadata ka meie teisi soovitatud artikleid -

  1. Vormeliriba Excelis
  2. Printige ruutvõrgud Excelis
  3. Vaateaken Excelis
  4. Excel SUMIFS koos kuupäevadega

Kategooria:

Exceli näpunäited