Model podataka Excel (Sadržaj)

  • Uvod u model podataka u Excelu
  • Kako stvoriti model podataka u Excelu?

Uvod u model podataka u Excelu

Značajka modela podataka Excel omogućava jednostavnu izgradnju odnosa između jednostavnog izvještavanja i njihovih skupova podataka. To olakšava analizu podataka. Omogućuje integraciju podataka iz mnoštva tablica raspoređenih na više radnih listova jednostavnom izgradnjom odnosa između odgovarajućih stupaca. Djeluje potpuno iza scene i znatno pojednostavljuje značajke izvještavanja poput PivotTable-a itd.

U našem ćemo članku pokušati pokazati kako stvoriti okretnu tablicu iz dviju tablica primjenom značajke Data Model, na taj način uspostaviti odnos između dva objekta tablice i na taj način stvoriti okretnu tablicu.

Kako stvoriti model podataka u Excelu?

Razjasnimo kako stvoriti model podataka u Excelu s nekoliko primjera.

Ovdje možete preuzeti ovaj model modela Excel predložak - Data Model Excel predložak

Primjer 1

  • Imamo popis proizvoda i za svaki proizvod imamo šifru polica. Potrebna nam je tablica s opisom police, zajedno s šiframa polica. Pa kako u svaki opis ugraditi police za pohranu? Možda bi se mnogi od nas priklonili upotrebi VLOOKUP-a, ali u potpunosti ćemo ukloniti potrebu za korištenjem VLOOKUP-a koristeći Excel Data Model.

  • Tablica s lijeve strane je tablica podataka, a tabla s desne strane je tablica za pretraživanje. Kao što vidimo iz podataka, moguće je stvoriti odnos na temelju zajedničkih stupaca.

  • Sada je Model podataka kompatibilan samo s objektima tablice. Dakle, možda će biti potrebno ponekad pretvoriti skupove podataka u objekte tablice. Da biste to učinili slijedite dolje navedene korake.
  1. Kliknite lijevim klikom bilo gdje u skupu podataka.
  2. Kliknite karticu Umetanje i pomaknite se do tablice u grupi Tablice ili jednostavno pritisnite Ctrl + T.
  3. Poništite ili provjerite Moja tablica ima opciju Zaglavlje. U našem primjeru doista ima zaglavlje. Kliknite U redu.
  4. Iako smo još usredotočeni na novu tablicu, moramo uvesti naziv koji ima značenje u polju Name (lijevo od trake formule).

U našem primjeru imenovali smo stol Osoblje.

  • Sada moramo obaviti isti postupak za tablicu pretraživanja i imenovati je Shelf Code.

Stvaranje odnosa

Dakle, prvo idemo na karticu Podaci, a zatim odaberemo Odnosi u podskupini Alati za podatke. Nakon što kliknemo na opciju Relationships, u početku budući da ne postoji veza, stoga nećemo imati ništa.

Prvo ćemo kliknuti Novo kako bismo stvorili odnos. Sada ćemo morati navesti primarna i nazive tablica za pretraživanje s padajućeg popisa, a zatim spomenuti stupac koji je zajednički između dvije tablice, tako da možemo uspostaviti odnos između dvije tablice, s padajućeg popisa stupaca.

  • Sada je primarna tablica tablica koja sadrži podatke. To je primarna tablica podataka - Tabela5. S druge strane, srodna tablica je tablica s podacima o pretraživanju - to je naša tablica pretraživanja ShelfCodesTable. Primarna tablica je ona koja se analizira na temelju pretraživačke tablice koja sadrži podatke pretraživanja, a koji će na kraju sadržane podatke učiniti značajnijima.

  • Dakle, zajednički stupac između dviju tablica je stupac Šifra polica. To smo koristili za uspostavljanje odnosa između dvije tablice. Dolazeći do stupaca, stupac (strani) je onaj koji se odnosi na tablicu podataka u kojoj mogu biti duplicirane vrijednosti. S druge strane, povezani stupac (primarni) odnosi se na stupac u pretraživačkoj tablici u kojem imamo jedinstvene vrijednosti. Jednostavno postavljamo polje za pronalaženje vrijednosti iz tablice pretraživanja u tablici podataka.
  • Jednom kada bismo ovo postavili, Excel bi stvorio odnos između njih dvoje iza scene. Integrira podatke i stvara model podataka na temelju zajedničkog stupca. Ovo nije samo svjetlo za memorijske potrebe, nego je i brže od korištenja VLOOKUP-a u velikim radnim knjigama. Nakon što je definirao model podataka, Excel će te objekte tretirati kao tablice modela podataka umjesto tablice radnog lista.
  • Da bismo vidjeli što je Excel radio, možemo kliknuti na Upravljanje podatkovnim modelima u Podatci -> Alati podataka.

  • Također možemo dobiti shematski prikaz modela podataka promjenom pogleda. Kliknite na opciju Pogled. Ovo će otvoriti mogućnosti pregleda. Tada ćemo odabrati prikaz dijagrama. Tada ćemo vidjeti shematski prikaz, prikazujući dvije tablice i odnos među njima tj. Zajednički stupac - Šifra polica.

  • Dijagram gore prikazuje odnos između jedinstvenih vrijednosti tablice pretraživanja i podatkovne tablice s dupliranim vrijednostima.
  • Sada ćemo morati izraditi okretnu tablicu. Da bismo to učinili, idemo na karticu Umetanje, a zatim kliknite na opciju Stožerna tablica.

U dijaloškom okviru Create Pivot Table dijalog tablice, mi ćemo odabrati izvor kao "Koristi model podataka ove radne knjige".

  • Ovo će stvoriti stožernu tablicu i možemo vidjeti da su obje izvorne tablice dostupne u izvornom odjeljku.

  • Sada ćemo stvoriti okretnu tablicu koja prikazuje broj svake osobe koja ima odlagane predmete.

  • Osoblje ćemo odabrati u odjeljku Redovi iz tablice 5 (tablica podataka), a zatim Opis (tablica pretraživanja).

  • Sada ćemo povući kod polica iz tablice 5 u odjeljak Vrijednosti.

  • Sada ćemo dodati mjesece iz tablice 5 u odjeljak Redse.

  • Ili bismo mogli dodati mjesece kao filtar i dodati ih u odjeljak Filteri.

Primjer 2

  • Sada je gospodin Basu vodio tvornicu zvanu Basu Corporation. Gospodin Basu pokušava procijeniti prihod za 2019. na temelju podataka iz 2018. godine.
  • Imamo tablicu u kojoj imamo prihode za 2018. i slijedeće prihode na različitim inkrementalnim razinama.

  • Dakle, imamo prihod za 2018. godinu - 1, 5 milijuna dolara, a minimalni rast koji se očekuje sljedeće godine je 12%. Gospodin Basu želi tablicu koja će prikazivati ​​prihod na različitim inkrementalnim razinama.
  • Napravit ćemo sljedeću tablicu za projekcije na različitim inkrementalnim razinama za 2019. godinu.

  • Sada ćemo u prvom redu prihoda dati referencu na procijenjeni minimalni prihod za 2019. godinu, tj. 1, 68 milijuna dolara.

  • Nakon korištenja formule odgovor je prikazan dolje.

  • Sada ćemo odabrati cijelu tablicu, tj. D2: E12, a zatim idemo na Podaci -> Prognoza -> Što ako analiza -> Tablica podataka.

  • Ovo će otvoriti dijaloški okvir Data Table. Ovdje ćemo unijeti minimalni priraštaj od ćelije B4 u ćeliji unosa stupca. Razlog za to je taj što su naši projicirani procijenjeni postoci rasta u tablici raspoređeni na stupac.

  • Nakon što kliknemo na U redu, What-If analiza automatski će popuniti tablicu s projiciranim prihodom u različitim inkrementalnim postotcima.

Primjer 3

  • Pretpostavimo da imamo isti scenarij kao gore, osim što sada moramo uzeti u obzir i drugu osovinu. Pretpostavimo da pored prikaza projiciranog prihoda u 2019. godini na temelju podataka 2018. i minimalne očekivane stope rasta, sada imamo i procijenjenu diskontnu stopu.

  • Prvo ćemo imati tablicu prikazanu u nastavku.

  • Sada ćemo se uputiti na minimalni projicirani prihod za 2019. godinu, tj. Ćeliju B5 do ćelije D8.

  • Sada ćemo odabrati cijelu tablicu, tj. D8: J18, a zatim idemo na Podaci -> Prognoza -> Što ako analiza -> Tablica podataka.

  • Ovo će otvoriti dijaloški okvir Data Table. Ovdje ćemo unijeti najniži priraštaj od ćelije B3 u ćeliji unosa stupca. Razlog za to je taj što su naši projicirani procijenjeni postoci rasta u tablici raspoređeni na stupac. Sada ćemo također unijeti minimalni postotak popusta od ćelije B4 u ćeliju retka za unos. Razlog za to je taj što su naši projicirani postoci popusta u tablici raspoređeni redom.

  • Kliknite U redu. Tako će se What-If analizom automatski popuniti tablica s projiciranim prihodima u različitim inkrementalnim postocima po postotcima popusta.

Stvari koje treba zapamtiti o modelu podataka u Excelu

  • Nakon uspješnog izračuna vrijednosti iz tablice podataka, jednostavno poništavanje, tj. Ctrl + Z, neće raditi. Međutim, moguće je ručno izbrisati vrijednosti iz tablice.
  • Iz tablice nije moguće izbrisati niti jednu ćeliju. On je u Excelu opisan kao niz armatura, stoga ćemo morati izbrisati sve vrijednosti.
  • Moramo pravilno odabrati liniju unosa retka i ulaznu ćeliju stupca.
  • Tablica podataka, za razliku od stožerne tablice, nije potrebno svaki put osvježavati.
  • Koristeći model podataka u Excelu, ne samo da možemo poboljšati performanse, već i olakšati memorijske potrebe na velikim radnim listovima.
  • Podaci modeli također čine našu analizu mnogo jednostavnijom u usporedbi s upotrebom brojnih kompliciranih formula širom radne knjige.

Preporučeni članci

Ovo je vodič za Data Model u Excelu. Ovdje smo razgovarali o tome kako stvoriti Data Model u Excelu zajedno s praktičnim primjerima i download-ovim Excel predloška. Možete i pregledati naše druge predložene članke -

  1. Formula bar u Excelu
  2. Ispis mrežnih linija u Excelu
  3. Pogledajte Prozor u Excelu
  4. Excel SUMIFS s datumima

Kategorija:

Excel savjeti