Excel-tietomalli (sisällysluettelo)

  • Johdanto Excel-tietomalliin
  • Kuinka luoda tietomalli Excelissä?

Johdanto Excel-tietomalliin

Excelin datamalliominaisuus mahdollistaa helpon yhteyksien luomisen helpon raportoinnin ja niiden taustatietojen välillä. Se helpottaa tietojen analysointia. Se mahdollistaa useiden taulukoiden hajautettujen taulukoiden tietojen integroinnin yksinkertaisesti luomalla suhteita vastaavien sarakkeiden välillä. Se toimii täysin kohtauksen takana ja yksinkertaistaa huomattavasti raportointiominaisuuksia, kuten PivotTable jne.

Artikkelissamme yritämme näyttää, kuinka luoda kääntötaulukko kahdesta taulukosta käyttämällä Data Model -ominaisuutta, jolloin luodaan suhde kahden taulukkoobjektin välillä ja luodaan siten PivotTable.

Kuinka luoda tietomalli Excelissä?

Ymmärretään kuinka luoda datamalli Excelissä muutamalla esimerkillä.

Voit ladata tämän Data Model Excel -mallin täältä - Data Model Excel Template

Esimerkki # 1

  • Meillä on luettelo tuotteista ja meillä on hyllykoodi jokaiselle tuotteelle. Tarvitsemme taulukon, jossa meillä on hyllykuvaus yhdessä hyllykoodien kanssa. Joten miten sisällytämme hyllykuvaukset jokaiseen hyllykoodiin? Ehkä monet meistä haluaisivat käyttää VLOOKUPia täällä, mutta poistamme kokonaan tarpeen käyttää VLOOKUPia täällä käyttämällä Excel Data Model -sovellusta.

  • Vasemmalla oleva taulukko on tietotaulukko ja oikealla oleva taulukko hakutaulukko. Kuten tiedoista voidaan nähdä, on mahdollista luoda suhde yhteisiin sarakkeisiin perustuen.

  • Nyt tietomalli on yhteensopiva vain taulukko-olosuhteiden kanssa. Joten voi joskus olla tarpeen muuntaa tietojoukot taulukko-objekteiksi. Voit tehdä tämän noudattamalla alla olevia vaiheita.
  1. Napsauta hiiren vasemmalla painikkeella mitä tahansa tietojoukon kohtaa.
  2. Napsauta Lisää-välilehteä ja siirry Taulukot-ryhmän taulukkoon tai paina vain Ctrl + T.
  3. Poista valinta tai valitse Oma taulukko -otsikon vaihtoehto. Esimerkissämme sillä on todellakin otsikko. Napsauta OK.
  4. Vaikka keskitymme edelleen uuteen taulukkoon, meidän on annettava nimi, jolla on merkitys Nimi-ruudussa (kaavapalkin vasemmalla puolella).

Esimerkissämme olemme nimittäneet taulukon Henkilöstö.

  • Nyt meidän on tehtävä sama prosessi myös hakutaulukolle ja annettava sille hyllykoodi.

Suhteen luominen

Joten ensin siirrymme Data-välilehteen ja valitse sitten Data Tools-alaryhmästä Relationships. Kun olemme napsauttaneet Suhteet-vaihtoehtoa, alussa koska suhdetta ei ole, meillä ei siis ole mitään.

Napsauta ensin Uusi luodaksesi suhteen. Meidän on nyt toimitettava ensisijaiset ja hakutaulukon nimet avattavasta luettelosta ja mainittava sitten sarake, joka on yhteinen kahden taulukon välillä, jotta voimme määrittää kahden taulukon välisen suhteen avattavasta luettelosta. sarakkeista.

  • Nyt ensisijainen taulukko on taulukko, jolla on tiedot. Se on ensisijainen tietotaulukko - taulukko5. Toisaalta Liittyvä-taulukko on taulukko, jolla on hakutiedot - se on hakutaulukkomme ShelfCodesTable. Ensisijainen taulukko analysoidaan hakutaulukon perusteella, joka sisältää hakutiedot, mikä tekee lopulta ilmoitetusta tiedosta merkityksellisemmän.

  • Joten, kahden taulukon välinen yhteinen sarake on Hyllykoodi-sarake. Tätä me olemme käyttäneet vahvistaaksesi kahden taulukon välisen suhteen. Sarakkeisiin tultaessa Sarake (vieraana) viittaa datataulukkoon, jossa voi olla päällekkäisiä arvoja. Toisaalta liittyvä sarake (ensisijainen) viittaa hakutaulukon sarakkeeseen, jossa meillä on yksilölliset arvot. Olemme vain määrittämässä kentän hakuarvoille datataulukon hakutaulukosta.
  • Kun olemme asettaneet tämän, Excel loisi suhteen kohtauksen takana olevien kahden välille. Se integroi tiedot ja luo tietomallin, joka perustuu yhteiseen sarakkeeseen. Tämä ei pelkästään riitä muistin tarpeisiin, vaan myös paljon nopeammin kuin VLOOKUP: n käyttö suurissa työkirjoissa. Kun tietomalli on määritelty, Excel käsittelee näitä objekteja tietomallitaulukoina laskentataulukkojen sijasta.
  • Nyt nähdäksesi mitä Excel on toiminut, voimme napsauttaa Hallitse datamalleja kohdassa Data -> Data Tools.

  • Voimme saada myös datamallin kaavamaisen esityksen muuttamalla näkymää. Napsauta Näytä-vaihtoehtoa. Tämä avaa näkymävaihtoehdot. Valitsemme sitten kaavionäkymän. Sitten nähdään kaavamainen esitys, jossa esitetään kaksi taulukkoa ja niiden välinen suhde eli yhteinen sarake - Hyllykoodi.

  • Yllä oleva kaavio näyttää yksilöllisen hakutaulukon arvojen ja kopioitujen arvojen tietotaulukon välisen suhteen yksin suhteen.
  • Nyt meidän on luotava kääntötaulukko. Sitä varten siirrymme Lisää-välilehteen ja napsauta sitten Pivot Table -vaihtoehtoa.

Pivot-taulukon Luo Pivot-taulukko -valintaikkunassa valitsemme lähteeksi ”Käytä tämän työkirjan tietomallia”.

  • Tämä luo Pivot-taulukon ja voimme nähdä, että molemmat lähdetaulut ovat saatavissa lähdeosiossa.

  • Nyt luomme kääntötaulukon, joka näyttää kaikkien hyllyllä olevien henkilöiden lukumäärän.

  • Valitaan Henkilöstö Rivit-osiosta taulukosta 5 (tietotaulukko), jota seuraa Kuvaus (hakutaulukko).

  • Nyt vedämme hyllykoodin taulukosta 5 arvot-osioon.

  • Nyt lisäämme kuukaudet taulukosta 5 Rivit-osioon.

  • Tai voimme lisätä kuukaudet suodattimena ja lisätä sen Suodattimet-osioon.

Esimerkki 2

  • Meillä on nyt herra Basu, joka johtaa Basu Corporation -nimistä tehtaata. Basu yrittää arvioida vuoden 2019 tulot vuoden 2018 tietojen perusteella.
  • Meillä on taulukko, jossa meillä on tulot vuodelle 2018 ja sitä seuraavat tulot eri asteittain.

  • Joten meillä on vuoden 2018 liikevaihto - 1, 5 miljoonaa dollaria ja seuraavan vuoden vähimmäiskasvun odotetaan olevan 12%. Herra Basu haluaa taulukon, joka näyttää tulot eri asteittain.
  • Luomme seuraavan taulukon ennusteille eri asteikkoilla vuodelle 2019.

  • Nyt annamme ensimmäiselle tuloriville viitteen arvioituun vähimmäistuottoon vuodelle 2019 eli 1, 68 miljoonaa dollaria.

  • Kaavan käytön jälkeen vastaus näkyy alla.

  • Nyt valitsemme koko taulukon eli D2: E12 ja siirrymme sitten kohtaan Data -> Sääennuste -> Mitä jos tehdään, jos analyysi -> Datataulu.

  • Tämä avaa Data Table -valintaikkunan. Tähän syötetään pienin lisäysprosentti solusta B4 Sarakkeen syöttö -soluun. Syynä tähän on, että ennustetut arvioidut kasvuprosentit taulukossa on järjestetty sarakkeellisesti.

  • Kun napsautamme OK, mitä-jos-analyysi täyttää taulukon automaattisesti ennustetuilla tuloilla erilaisilla prosenttiosuuksilla.

Esimerkki 3

  • Oletetaan nyt, että meillä on sama skenaario kuin yllä, paitsi että meillä on nyt myös toinen akseli harkittavana. Oletetaan, että sen lisäksi, että esitetään ennustetut tulot vuonna 2019 vuoden 2018 tietojen ja vähimmäisodotettavan kasvun perusteella, meillä on nyt myös arvio diskonttokorosta.

  • Ensinnäkin meillä on alla oleva taulukko.

  • Nyt annamme viitteen ennakoituihin vähimmäistuottoihin 2019 eli solu B5 soluun D8.

  • Nyt valitsemme koko taulukon eli D8: J18 ja siirrymme sitten kohtaan Data -> Sääennuste -> Mitä jos tehdään, jos analyysi -> Datataulu.

  • Tämä avaa Data Table -valintaikkunan. Tähän tulee syöttää pienin lisäysprosentti solusta B3 Sarakkeen syöttö -soluun. Syynä tähän on, että ennustetut arvioidut kasvuprosentit taulukossa on järjestetty sarakkeellisesti. Annamme nyt myös lisäksi pienimmän alennusprosentin solusta B4 Rivitulo-soluun. Syynä tähän on, että taulukossa ennustetut alennusprosentit on järjestetty rivittäin.

  • Napsauta OK. Tämä tekee What-If -analyysin täyttämään taulukon automaattisesti ennustetulla tulolla eri inkrementaaliprosenteilla alennusprosentteina.

Muistettavat tiedot Excel-mallin datamallista

  • Kun arvot on laskettu datataulusta onnistuneesti, yksinkertainen Kumoa, ts. Ctrl + Z, ei toimi. Arvot on kuitenkin mahdollista poistaa manuaalisesti taulukosta.
  • Yksittäistä solua ei ole mahdollista poistaa taulukosta. Sitä kuvataan Excel-järjestelmän sisäisenä taulukkona, joten meidän on poistettava kaikki arvot.
  • Meidän on valittava rivin syöttösolu ja sarakkeen syöttösolu oikein.
  • Datataulua, toisin kuin Pivot-taulukko, ei tarvitse päivittää joka kerta.
  • Käyttämällä Excel-tietomallia, emme voi vain parantaa suorituskykyä, mutta myös helpottaa muistien tarvetta suurissa taulukkoissa.
  • Datamallit tekevät myös analyysistämme paljon yksinkertaisempia verrattuna useiden monimutkaisten kaavojen käyttämiseen koko työkirjassa.

Suositellut artikkelit

Tämä on opas tietomalliin Excelissä. Täällä keskustellaan siitä, miten luodaan tietomalli Exceliin yhdessä käytännön esimerkkien ja ladattavan excel-mallin kanssa. Voit myös käydä läpi muiden ehdottamiemme artikkeleidemme -

  1. Kaavapalkki Excelissä
  2. Tulosta ruudukkoviivat Excelissä
  3. Tarkkailuikkuna Excelissä
  4. Excel SUMIFS päivämäärillä

Luokka:

Excel-vinkkejä