Excel adatmodell (Tartalomjegyzék)

  • Bevezetés az Excel adatmodelljébe
  • Hogyan hozhatunk létre adatmodellt az Excel programban?

Bevezetés az Excel adatmodelljébe

Az Excel adatmodellje lehetővé teszi a könnyebb kapcsolatok kiépítését az egyszerű jelentéstétel és a háttér-adatkészletek között. Ez megkönnyíti az adatok elemzését. Ez lehetővé teszi az adatok integrálását számos munkalapra elosztott táblák sokaságából azáltal, hogy egyszerűen létrehozza a kapcsolatokat a megfelelő oszlopok között. Teljesen működik a jelenet mögött, és jelentősen leegyszerűsíti a jelentési funkciókat, például a PivotTable stb.

Cikkünkben megkíséreljük megmutatni, hogyan lehet két táblából pivot táblát létrehozni az Adatmodell funkció alkalmazásával, ezáltal kapcsolatot létesítve két táblaobjektum között, és ezzel létrehozva PivotTable-t.

Hogyan hozhatunk létre adatmodellt az Excel programban?

Néhány példa alapján megértjük, hogyan lehet az adatmodellt Excelben létrehozni.

Az adatmodell Excel sablonját itt töltheti le - Adatmodell Excel sablonja

1. példa

  • Van egy terméklistánk, és minden termékhez van egy polc kód. Szüksége van egy táblára, ahol megtalálható a polcok leírása és a polcrendszerek. Szóval hogyan integrálhatjuk a polcok leírásait minden egyes polc kódhoz? Lehet, hogy sokan közülünk a VLOOKUP használatát veszik igénybe itt, de teljes mértékben el kell távolítanunk a VLOOKUP itt használatának szükségességét az Excel Data Model használatával.

  • A bal oldalon található táblázat az adattáblázat, a jobb oldalon található táblázat a keresési táblázat. Amint az adatokból kiderül, lehetséges kapcsolat létrehozása közös oszlopok alapján.

  • Az Adatmodell most csak a táblaobjektumokkal kompatibilis. Tehát néha szükség lehet az adatkészletek táblázati objektumokká konvertálására. Ehhez kövesse az alábbi lépéseket.
  1. Bal egérgombbal kattintson az adatkészlet bárhová.
  2. Kattintson a Beszúrás fülre, és keresse meg a Táblázatok csoportot a Táblák csoportban, vagy egyszerűen nyomja meg a Ctrl + T billentyűket.
  3. Törölje vagy jelölje be a Saját táblázat fejléc lehetőséget. Példánkban valóban van egy fejléce. Kattintson az OK gombra.
  4. Miközben továbbra is az új táblára összpontosítunk, meg kell adnunk egy nevet, amely értelmes a Név mezőben (a képletsáv bal oldalán).

Példánkban a Személyzet táblát neveztük el.

  • Most ugyanazt a folyamatot kell tennünk a keresési táblára is, és el kell neveznünk Polc kódnak.

Kapcsolat létrehozása

Tehát először a Data fülre lépünk, majd az Data Tools alcsoportban kiválasztjuk a Relationships-t. Miután rákattintunk a kapcsolatok lehetőségre, az elején, mivel nincs kapcsolat, így semmi sem lesz.

Először az Új elemre kattintunk, hogy kapcsolatot létesítsünk. Most meg kell adnunk az elsődleges és a keresési táblázatok nevét a legördülő listából, majd meg kell említenünk az oszlopot is, amely közös a két táblázat között, hogy a két táblázat közötti kapcsolatot meg tudjuk határozni a legördülő listából. oszlopok.

  • Az elsődleges táblázat az a tábla, amely rendelkezik az adatokkal. Ez az elsődleges adattábla - 5. táblázat. Másrészt a Kapcsolódó táblázat az a tábla, amely rendelkezik a keresési adatokkal - ez a ShelfCodesTable keresési táblázata. Az elsődleges táblázatot elemezzük a keresési táblázat alapján, amely olyan keresési adatokat tartalmaz, amelyek végül jelentősebbé teszik a bejelentett adatokat.

  • Tehát a két táblázat közti oszlop a Polckód oszlop. Ezt használtuk a két táblázat közötti kapcsolat megteremtésére. Az oszlopokhoz az oszlop (idegen) az, amely arra az adattáblázatra utal, ahol duplikált értékek lehetnek. Másrészt a Kapcsolódó oszlop (elsődleges) arra az oszlopra utal, amely a keresési táblázatban van, ahol egyedi értékek vannak. Egyszerűen azt a mezőt állítottuk be, amely az értékek lekérdezésére szolgál az adattáblázatban található keresési táblázatból.
  • Amint ezt beállítottuk, az Excel kapcsolatot teremt a jelenet mögött álló kettő között. Integrálja az adatokat, és létrehoz egy adatmodellt a közös oszlop alapján. Ez nemcsak a memóriaigényt tükrözi, hanem sokkal gyorsabb is, mint a VLOOKUP használata nagy munkafüzetekben. Az adatmodell meghatározása után az Excel ezeket az objektumokat adatmodell táblázatokként kezeli, és nem egy munkalap táblázataként.
  • Most, hogy megnézze, miben áll az Excel, rákattinthatunk az Adatmodellek kezelése az Adatok -> Adat eszközök menüpontra.

  • Az adatmodell vázlatos ábrázolását a nézet megváltoztatásával is megkaphatjuk. Kattintson a Nézet opcióra. Ezzel megnyílik a nézet lehetőségei. Ezután kiválasztjuk a Diagram nézetet. Ezután meglátjuk a vázlatos ábrázolást, amely bemutatja a két táblát és a közöttük fennálló kapcsolatot, azaz a közös oszlop - Polc kód.

  • A fenti ábra egy-egy-sok kapcsolatot mutat az egyedi keresési táblázat értékei és a duplikált értékek adattáblája között.
  • Most létre kell hoznunk egy pivot táblát. Ehhez megyünk a Beszúrás fülre, majd rákattintunk a Pivot Table opcióra.

A Pivot tábla Pivot tábla létrehozása párbeszédpanelen a forrást „A munkafüzet adatmodelljének használata” néven választjuk ki.

  • Ez létrehozza a Pivot táblát, és láthatjuk, hogy mindkét forrástábla elérhető a forrás szakaszban.

  • Most elkészítünk egy forgótáblát, amely megmutatja minden egyes személy számát, aki polcokkal rendelkezik.

  • Az 5. táblázatból (adattáblázat) a Személyzet elemet választjuk a Sorok részben, majd a Leírás (keresési táblázat) elemet.

  • Most áthúzzuk a polc kódot az 5. táblázatból az Értékek részbe.

  • Most hozzáadjuk az 5. táblázat havi adatait a Sorok szakaszhoz.

  • Vagy hozzáadhatjuk a hónapokat szűrőként, és hozzáadhatjuk a Szűrők szakaszhoz.

2. példa

  • Most Basu úr üzemelteti a Basu Corporation nevű gyárat. Basu úr megpróbálja becsülni a 2019-es bevételt a 2018. évi adatok alapján.
  • Van egy táblázat, ahol a 2018-as bevételek és az azt követő bevételek különböző növekményes szinteken vannak.

  • Tehát bevételünk van 2018-ra - 1, 5 millió dollár, és a következő évre várható minimális növekedés 12%. Basu úr egy táblázatot akar, amely a különféle növekményes szinteken mutatja a bevételt.
  • A következő táblázatot készítjük a 2019-re vonatkozó különféle növekményes szintekre vonatkozó előrejelzésekhez.

  • Most az első bevételi sorra hivatkozunk a 2019-re becsült minimális bevételre, azaz 1, 68 millió dollárra.

  • A képlet használata után az alábbi válasz látható.

  • Most kiválasztjuk a teljes táblát, azaz a D2: E12-t, majd megyünk az Adatok -> Előrejelzés -> Mi lenne, ha elemzése -> Adattábla elemre.

  • Ez megnyitja az Adat táblázat párbeszédpanelt. Itt be kell írnunk a B4 cellából származó minimális növekmény százalékot az Oszlopbeviteli cellába. Ennek oka az, hogy a táblázatban a becsült becsült növekedési százalékokat oszlop szerint rendezzük.

  • Miután rákattintunk az OK gombra, a Mit jelent, ha az elemzés automatikusan kitölti a táblát a különbözõ növekményes százalékos becsült bevételekkel.

3. példa

  • Tegyük fel most, hogy ugyanaz a forgatókönyv, mint a fentiekben, azzal a különbséggel, hogy most van egy másik tengelyünk is, amelyet mérlegelnünk kell. Tegyük fel, hogy a 2019-es előrejelzett bevételnek a 2018-as adatok és a várható minimális növekedési ráta alapján történő bemutatása mellett mostantól megvan a becsült diszkontrátája is.

  • Először az alább bemutatott táblázatot kell bemutatnunk.

  • Most hivatkozunk a 2019-re tervezett minimális bevételre, azaz a B5 cellára a D8 cellára.

  • Most kiválasztjuk a teljes táblát, azaz a D8: J18-t, majd lépjünk az Adatok -> Előrejelzés -> Mi lenne, ha elemzése -> Adattábla elemre.

  • Ez megnyitja az Adat táblázat párbeszédpanelt. Itt be kell írnunk a B3 cellából származó minimális növekedési százalékot az Oszlopbeviteli cellába. Ennek oka az, hogy a táblázatban a becsült becsült növekedési százalékokat oszlop szerint rendezzük. Most a R4 beviteli cellába ezenkívül a B4 cellából származó minimális engedmény százalékot is beírjuk. Ennek oka az, hogy a táblázatban szereplő becsült engedményszázalékok sorrendben vannak elrendezve.

  • Kattintson az OK gombra. Ezáltal a What-If elemzés automatikusan kitölti a táblát a becsült bevételekkel a különbözõ növekményes százalékokban, a diszkont százalékos arányában.

Emlékezzen az Excel adatmodelljére

  • Az adatok eredményes kiszámítása után az egyszerű visszavonás, azaz a Ctrl + Z nem fog működni. Az értékeket azonban manuálisan törölhetjük a táblázatból.
  • Nem lehet egyetlen cellát törölni a táblából. Az Excelben tömbként írják le, ezért minden értéket törölnünk kell.
  • Megfelelően ki kell választanunk a Sorbeviteli cellát és az Oszlopbeviteli cellát.
  • Az adattáblát, a Pivot táblával ellentétben, nem kell minden alkalommal frissíteni.
  • Az Adatmodell Excel alkalmazásával nemcsak a teljesítményt javíthatjuk, hanem a memóriaigényeket is megkönnyítjük a nagy munkalapokon.
  • Az adatmodellek sokkal egyszerűbbé teszik elemzésünket, összehasonlítva számos bonyolult képlettel az egész munkafüzetben.

Ajánlott cikkek

Ez egy útmutató az Excel adatmodelljéhez. Itt tárgyaljuk, hogyan lehet adatmodellt létrehozni Excelben, a gyakorlati példákkal és a letölthető Excel sablonnal együtt. Megnézheti más javasolt cikkeinket -

  1. Képletsáv Excelben
  2. Nyomtasson rácsvonalakat az Excel programban
  3. Figyelőablak Excelben
  4. Excel SUMIFS dátumokkal

Kategória:

Excel tippek