Ako vytvoriť vzťahy medzi viacerými tabuľkami pomocou dátového modelu v programe Excel

Ako vytvoriť vzťahy medzi viacerými tabuľkami pomocou dátového modelu v programe Excel

Excel je účinný nástroj na analýzu údajov a následnú automatizáciu pri manipulácii s veľkými množinami údajov. Mohli by ste stráviť značný čas analyzovaním ton údajov pomocou nástrojov VLOOKUP, INDEX-MATCH, SUMIF atď.





Vďaka dátovému modelu Excel môžete ušetriť drahocenný čas prostredníctvom automatických prehľadov údajov. Zistite, ako ľahko môžete priradiť vzťah medzi dvoma tabuľkami pomocou dátového modelu a ilustrácie takéhoto vzťahu v kontingenčnej tabuľke v nasledujúcej časti.



Základné požiadavky

Na splnenie niekoľkých úloh pri vytváraní budete potrebovať Power Pivot a Power Query (Get & Transform) Excel Dátový model. Tu je postup, ako môžete získať tieto funkcie do zošita programu Excel:





Ako získať Power Pivot

1. Excel 2010: Budete si musieť stiahnuť doplnok Power Pivot Microsoft a potom ho nainštalujte do programu Excel do počítača.

2. Excel 2013: Edícia Office Professional Plus Excelu 2013 obsahuje Power Pivot. Pred prvým použitím ho však musíte aktivovať. Tu je postup:



  1. Kliknite na Súbor na Stuha zošita programu Excel.
  2. Potom kliknite na možnosti na otvorenie Možnosti programu Excel .
  3. Teraz kliknite na Doplnky .
  4. Vyberte Doplnky COM kliknutím na rozbaľovaciu ponuku Spravovať box.
  5. Kliknite na Choď a potom začiarknite políčko pre Microsoft Power Pivot pre Excel .

3. Excel 2016 a novší: Ponuku Power Pivot nájdete na Stuha .

Súvisiace: Ako pridať kartu vývojára na pás s nástrojmi v programe Microsoft Word a Excel



Ako získať Power Query (Získať a transformovať)

1. Excel 2010: Doplnok Power Query si môžete stiahnuť z Microsoft . Po inštalácii, Power Query sa zobrazí na Stuha .

2. Excel 2013: Power Query musíte aktivovať podľa rovnakých krokov, ktoré ste urobili, aby bol Power Pivot funkčný v programe Excel 2013.



3. Excel 2016 a novší: Power Query (Get & Transform) nájdete v Údaje kartu v programe Excel Stuha .

Vytvorte dátový model importovaním údajov do zošita programu Excel

Pre tento tutoriál môžete získať vopred naformátované vzorové údaje od spoločnosti Microsoft:

Stiahnuť ▼ : Ukážkový študent údaje (iba údaje) | Ukážkový študent údaje (kompletný model)

Môžete importovať databázu s viacerými súvisiacimi tabuľkami z mnohých zdrojov, ako sú zošity programu Excel, Microsoft Access, webové stránky, SQL Server atď. Potom musíte súbor údajov naformátovať, aby ho Excel mohol využiť. Tu sú kroky, ktoré môžete vyskúšať:

1. V Exceli 2016 a novších vydaniach kliknite na ikonu Údaje kartu a vyberte Nový dotaz .

2. Nájdete niekoľko spôsobov, ako importovať údaje z externých alebo interných zdrojov. Vyber si ten, ktorý vám vyhovuje.

3. Ak používate vydanie Excel 2013, kliknite na Power Query na Stuha a potom vyberte Získajte externé údaje vybrať údaje na import.

4. Uvidíte Navigátor pole, kde musíte vybrať, ktoré tabuľky chcete importovať. Kliknite na začiarkavacie políčko pre Vyberte viac položiek vybrať niekoľko tabuliek na import.

5. Kliknite na Naložiť dokončite proces importu.

6. Excel vám pomocou týchto tabuliek vytvorí dátový model. Hlavičky stĺpcov tabuľky môžete vidieť v Polia kontingenčnej tabuľky zoznamy.

Môžete tiež využiť funkcie doplnku Power Pivot, ako sú vypočítané stĺpce, KPI, hierarchie, vypočítané polia a filtrované množiny údajov z dátového modelu Excelu. Na tento účel budete musieť vygenerovať dátový model z jednej tabuľky. Môžete vyskúšať tieto kroky:

1. Formátujte údaje v tabuľkovom modeli tak, že vyberiete všetky bunky obsahujúce údaje a potom kliknete Ctrl+T .

2. Teraz vyberte celú tabuľku a potom kliknite na Power Pivot záložka na Stuha .

3. Od Tabuľky sekciu, kliknite na Pridať do dátového modelu .

Excel vytvorí vzťahy tabuliek medzi súvisiacimi údajmi z dátového modelu. V importovaných tabuľkách by preto mali existovať vzťahy primárnych a cudzích kľúčov.

Excel používa informácie o vzťahoch z importovanej tabuľky ako základ na generovanie prepojení medzi tabuľkami v dátovom modeli.

Súvisiace: Ako vytvoriť analýzu typu „čo keď“ v programe Microsoft Excel

Budujte vzťahy medzi tabuľkami v dátovom modeli

Teraz, keď máte v zošite programu Excel dátový model, budete musieť definovať vzťahy medzi tabuľkami a vytvárať zmysluplné zostavy. Každej tabuľke musíte priradiť jedinečný identifikátor poľa alebo primárny kľúč, napríklad ID semestra, číslo triedy, ID študenta atď.

Funkcia Zobrazenie diagramu v programe Power Pivot vám umožní tieto polia presúvať a budovať vzťah. Pri vytváraní odkazov na tabuľky v dátovom modeli Excelu postupujte nasledovne:

1. Na stránke Stuha zošita Excelu, kliknite na Power Pivot Ponuka.

2. Teraz kliknite na Spravovať v Dátový model sekcii. Uvidíte Power Pivot editor, ako je uvedené nižšie:

3. Kliknite na ikonu Zobrazenie diagramu tlačidlo umiestnené v vyhliadka sekcii Power Pivot Domov tab. Uvidíte hlavičky stĺpcov tabuľky zoskupené podľa názvu tabuľky.

4. Teraz budete môcť presúvať jedinečný identifikátor poľa z jednej tabuľky do druhej. Nasleduje schematická schéma vzťahu medzi štyrmi tabuľkami dátového modelu programu Excel:

Nasledujúci text popisuje prepojenie medzi tabuľkami:

  • Stolní študenti | ID študenta do tabuľky známok | Študentská karta
  • Tabuľkové semestre | ID semestra do tabuľky Známky | Semester
  • Tabuľkové triedy | Trieda Číslo do tabuľky Známky | ID triedy

5. Vzťahy môžete vytvárať výberom dvojice stĺpcov s jedinečnou hodnotou. Ak existujú duplikáty, zobrazí sa nasledujúca chyba:

6. Všimnete si Hviezda (*) na jednej strane a Jeden (1) na druhej strane v diagramovom zobrazení vzťahov. Definuje, že medzi tabuľkami existuje vzťah jedna k mnohým.

7. V editore Power Pivot kliknite na ikonu Dizajn kartu a potom vyberte Spravujte vzťahy vedieť, ktoré polia spájajú.

Vygenerujte kontingenčnú tabuľku pomocou dátového modelu programu Excel

Teraz môžete vytvoriť kontingenčnú tabuľku alebo kontingenčný graf na vizualizáciu údajov z dátového modelu programu Excel. Zošit Excelu môže obsahovať iba jeden údajový model, tabuľky však môžete aktualizovať.

Súvisiace články: Čo je ťažba údajov a je nezákonné?

Pretože sa údaje v priebehu času menia, môžete naďalej používať ten istý model a šetriť čas pri práci s rovnakým súborom údajov. Pri práci s údajmi v tisícoch riadkov a stĺpcov si všimnete väčšiu úsporu času. Ak chcete vytvoriť zostavu založenú na kontingenčnej tabuľke, postupujte nasledovne:

1. V editore Power Pivot kliknite na ikonu Domov tab.

2. Na stránke Stuha , kliknite na Kontingenčná tabuľka .

3. Vyber si ktorýkoľvek medzi novým pracovným listom alebo existujúcim pracovným listom.

ako získate karmu na reddite

4. Vyberte OK . Excel pridá a Kontingenčná tabuľka to ukáže Zoznam polí tabla vpravo.

Nasleduje holistický pohľad na kontingenčnú tabuľku vytvorenú pomocou údajového modelu programu Excel pre vzorové údaje študentov použité v tomto tutoriáli. Profesionálne kontingenčné tabuľky alebo grafy môžete vytvárať aj z veľkých dát pomocou nástroja Excel Data Model.

Transformujte komplexné súbory údajov na jednoduché zostavy pomocou dátového modelu programu Excel

Dátový model Excel využíva výhody vytvárania vzťahov medzi tabuľkami na vytváranie zmysluplných kontingenčných tabuliek alebo grafov na účely vykazovania údajov.

Existujúci zošit môžete priebežne aktualizovať a publikovať správy o aktualizovaných údajoch. Pri každej aktualizácii zdrojových údajov nemusíte upravovať vzorce ani investovať čas do posúvania sa v tisícoch stĺpcov a riadkov.

zdieľam zdieľam Tweet E -mail Ako vytvoriť kontingenčnú tabuľku v programe Excel

Naučte sa vytvárať kontingenčné tabuľky v Exceli a používať ich na čerpanie informácií, ktoré chcete vidieť.

Čítajte ďalej Súvisiace témy
  • Produktivita
  • Tabuľkové tipy
  • Microsoft Excel
  • Tipy pre Microsoft Office
  • Analýza dát
O autorovi Tamal Das(100 publikovaných článkov)

Tamal je nezávislým spisovateľom na MakeUseOf. Po získaní značných skúseností v oblasti technológií, financií a obchodných procesov v predchádzajúcom zamestnaní v IT poradenskej spoločnosti si pred 3 rokmi osvojil písanie ako profesiu na plný úväzok. Aj keď nepíše o produktivite a najnovších technologických správach, rád hrá Splinter Cell a často sa pozerá na Netflix/ Prime Video.

Viac od Tamala Dasa

prihlásiť sa ku odberu noviniek

Pripojte sa k nášmu bulletinu a získajte technické tipy, recenzie, bezplatné elektronické knihy a exkluzívne ponuky!

Kliknutím sem sa prihlásite na odber