Poznámka: Microsoft Access nepodporuje import excelových údajov s použitým označením citlivosti. Ako alternatívne riešenie môžete pred importovaním označenie odstrániť a potom ho znova použiť po importovaní. Ďalšie informácie nájdete v téme Použitie označení citlivosti na súbory a e-maily v balíku Office.
V tomto článku sa dozviete, ako premiestniť údaje z Excelu do Accessu a skonvertovať údaje na relačné tabuľky, aby ste mohli používať Microsoft Excel a Access spoločne. Aby sme to zhrnuli, Access je najvhodnejší na zaznamenávanie, ukladanie, dotazovanie a zdieľanie údajov a Excel je najvhodnejší na výpočet, analýzu a vizualizáciu údajov.
Dva články: Používanie Accessu alebo Excelu na spravovanie údajov a prvých 10 dôvodov na používanie Accessu s Excelom, prediskutovanie toho, ktorý program je najvhodnejší pre konkrétnu úlohu, a o tom, ako používať Excel a Access spoločne na vytvorenie praktického riešenia.
Pri presune údajov z Excelu do Accessu sú v procese k dispozícii tri základné kroky.
Poznámka: Informácie o modelovaní údajov a vzťahoch v Accesse nájdete v téme Základy navrhovania databázy.
Krok 1: Import údajov z Excelu do Accessu
Importovanie údajov je operácia, ktorá môže byť plynulejšia, ak potrebujete nejaký čas na prípravu a vyčistenie údajov. Importovanie údajov je ako premiestnenie do nového domova. Ak si vyčistiť a organizovať svoje majetky, ako sa presťahovať, usadiť sa do nového domova, je oveľa jednoduchšie.
Vyčistenie údajov pred importovaním
Pred importovaním údajov do Accessu je vhodné v Exceli:
-
Skonvertujte bunky, ktoré obsahujú neomymické údaje (čiže viaceré hodnoty v jednej bunke) na viacero stĺpcov. Napríklad bunka v stĺpci Zručnosti, ktorá obsahuje viacero hodnôt zručností, ako je napríklad programovanie v jazyku C#, programovanie v jazyku VBA a webový návrh, by sa mala rozdeliť tak, aby oddelila stĺpce, z ktorých každý obsahuje len jednu hodnotu odbornosti.
-
Pomocou príkazu TRIM odstráňte úvodné, koncové a viaceré vložené medzery.
-
Odstráňte netlačiteľných znakov.
-
Vyhľadajte a opravte pravopisné a interpunkčné znamienka.
-
Odstráňte duplicitné riadky alebo duplicitné polia.
-
Skontrolujte, či stĺpce údajov neobsahujú zmiešané formáty, najmä čísla formátované ako text alebo dátumy formátované ako čísla.
Ďalšie informácie nájdete v nasledujúcich témach Pomocníka pre Excel:
Poznámka: Ak sú vaše potreby čistenia údajov zložité alebo nemáte čas ani zdroje na automatizáciu procesu sami, môžete zvážiť použitie dodávateľa tretej strany. Ďalšie informácie získate vyhľadaním výrazu "softvér na čistenie údajov" alebo "kvalita údajov" podľa vášho obľúbeného vyhľadávacieho nástroja vo webovom prehliadači.
Výber najlepšieho typu údajov pri importe
Počas operácie importovania v Accesse sa chcete rozhodnúť správne, aby sa zobrazili chyby konverzie (ak sa vyskytli), ktoré budú vyžadovať manuálny zásah. Nasledujúca tabuľka obsahuje súhrn toho, ako sa formáty excelových čísel a accessové typy údajov skonvertujú pri importe údajov z Excelu do Accessu, a ponúka niekoľko tipov k najlepším typom údajov, ktoré si môžete vybrať v Sprievodcovi importovaním tabuľkového hárka.
Formát čísel v Exceli |
Typ údajov Accessu |
Komentáre |
Najvhodnejší postup |
---|---|---|---|
Text |
Text, Memo |
Typ údajov Access Text uchováva alfanumerické údaje do 255 znakov. Typ údajov Access Memo uchováva alfanumerické údaje do 65 535 znakov. |
Ak sa chcete vyhnúť skráteniu akýchkoľvek údajov, vyberte položku Memo . |
Číslo, Percento, Zlomok, Vedecké |
Číslo |
Access má jeden typ údajov Číslo, ktorý sa líši v závislosti od vlastnosti Veľkosť poľa (Byte, Integer, Long Integer, Single, Double, Decimal). |
Ak sa chcete vyhnúť chybám konverzie údajov, vyberte možnosť Double . |
Date |
Dátum |
Access aj Excel používajú na ukladanie dátumov rovnaké poradové číslo dátumu. V Accesse je rozsah dátumov väčší: od -657 434 (1. januára 100 ročne) do 2 958 465 (31. decembra 9999). Keďže Access nerozpoznáva dátumový systém 1904 (používaný v Exceli pre Macintosh), dátumy je potrebné skonvertovať v Exceli alebo Accesse, aby ste sa vyhli nejasnostiam. Ďalšie informácie nájdete v téme Zmena dátumového systému, formátu alebo dvojciferného výkladu roka a import údajov alebo prepojenie na údaje v excelovom zošite. |
Vyberte položku Dátum. |
Time |
Čas |
Access aj Excel ukladajú hodnoty času pomocou rovnakého typu údajov. |
Vyberte možnosť Čas, ktorá je zvyčajne predvolená. |
Mena, účtovnícke |
Mena |
V Accesse typ údajov Mena ukladá údaje ako 8-bajtové čísla s presnosťou na štyri desatinné miesta a používa sa na ukladanie finančných údajov a zabránenie zaokrúhľovania hodnôt. |
Vyberte možnosť Mena, ktorá je zvyčajne predvolená. |
boolovský výraz |
Áno/Nie |
Access používa hodnotu -1 pre všetky hodnoty Yes a 0 pre všetky hodnoty No, zatiaľ čo Excel používa 1 pre všetky hodnoty TRUE a 0 pre všetky hodnoty FALSE. |
Vyberte možnosť Áno/Nie, ktorá automaticky konvertuje základné hodnoty. |
Hypertextové prepojenie |
Hypertextové prepojenie |
Hypertextové prepojenie v Exceli a Accesse obsahuje URL adresu alebo webovú adresu, na ktorú môžete kliknúť a sledovať ju. |
Vyberte položku Hypertextové prepojenie, v opačnom prípade môže Access predvolene použiť typ údajov Text. |
Keď sú údaje v Accesse, môžete odstrániť excelové údaje. Nezabudnite si pred odstránením pôvodný excelového zošita zálohovať.
Ďalšie informácie nájdete v téme Pomocníka Accessu Import údajov alebo prepojenie na údaje v excelovom zošite.
Automatické pripojenie údajov jednoduchým spôsobom
Bežným problémom používateľov Excelu je pripojenie údajov s rovnakými stĺpcami do jedného veľkého hárka. Môžete mať napríklad riešenie na sledovanie aktív, ktoré sa začalo v Exceli, ale teraz sa rozrástlo o súbory z mnohých pracovných skupín a oddelení. Tieto údaje sa môžu nachádzať v rôznych hárkoch a zošitoch alebo v textových súboroch, ktoré sú údajovými informačnými kanálmi z iných systémov. Neexistuje žiadny príkaz používateľského rozhrania ani jednoduchý spôsob pripojenia podobných údajov v Exceli.
Najlepším riešením je použiť Access, kde môžete jednoducho importovať a pripájať údaje do jednej tabuľky pomocou Sprievodcu importovaním tabuľkového hárka. Okrem toho môžete pripojiť veľa údajov do jednej tabuľky. Môžete uložiť operácie importu, pridať ich ako plánované úlohy Programu Microsoft Outlook a dokonca použiť makrá na automatizáciu procesu.
Krok 2: Normalizácia údajov pomocou Sprievodcu analýzou tabuľky
Na prvý pohľad sa kroky v procese normalizácie údajov môžu zdať skľučujúcou úlohou. Normalizácia tabuliek v Accesse je našťastie oveľa jednoduchší proces vďaka Sprievodcovi analýzou tabuliek.
1. Presuňte vybraté stĺpce do novej tabuľky a automaticky vytvorte vzťahy
2. Použite príkazy tlačidla na premenovanie tabuľky, pridanie hlavného kľúča, vytvorenie existujúceho stĺpca ako hlavného kľúča a vrátenie poslednej akcie späť
Pomocou tohto sprievodcu môžete vykonať nasledujúce kroky:
-
Konvertujte tabuľku na množinu menších tabuliek a automaticky vytvorte vzťah primárneho a cudzieho kľúča medzi tabuľkami.
-
Pridajte hlavný kľúč do existujúceho poľa, ktoré obsahuje jedinečné hodnoty, alebo vytvorte nové pole identifikácie, ktoré používa typ údajov Automatické číslovanie.
-
Automaticky vytvárať vzťahy na vynútenie referenčnej integrity s kaskádovými aktualizáciami. Kaskádové odstránenia sa nepridajú automaticky, aby sa zabránilo náhodnému odstráneniu údajov, ale kaskádové odstránenia môžete neskôr jednoducho pridať.
-
Vyhľadajte v nových tabuľkách nadbytočné alebo duplicitné údaje (napríklad toho istého zákazníka s dvoma rôznymi telefónnymi číslami) a podľa potreby ho aktualizujte.
-
Zálohujte pôvodnú tabuľku a premenujte ju tak, že k jej názvu pripojíte "_OLD". Potom vytvorte dotaz, ktorý rekonštruuje pôvodnú tabuľku s pôvodným názvom tabuľky tak, aby všetky existujúce formuláre alebo zostavy založené na pôvodnej tabuľke fungovali s novou štruktúrou tabuľky.
Ďalšie informácie nájdete v téme Normalizácia údajov pomocou analyzátora tabuliek.
Krok 3: Pripojenie k údajom programu Excel
Po normalizácii údajov v Accesse a vytvorení dotazu alebo tabuľky, ktorá rekonštruuje pôvodné údaje, je to jednoduchá záležitosť pripojenia k accessovým údajom z Excelu. Vaše údaje sú teraz v Accesse ako externý zdroj údajov, takže ich možno pripojiť k zošitu prostredníctvom pripojenia údajov, čo je kontajner informácií, ktorý sa používa na vyhľadávanie, prihlásenie a prístup k externému zdroju údajov. Informácie o pripojení sú uložené v zošite a môžu byť tiež uložené v súbore pripojenia, ako je napríklad súbor pripojenia údajov balíka Office (odc) (prípona súboru .odc) alebo súbor s názvom zdroja údajov (prípona .dsn). Po pripojení k externým údajom môžete excelový zošit automaticky obnoviť (alebo aktualizovať) z Accessu pri každej aktualizácii údajov v Accesse.
Ďalšie informácie nájdete v téme Import údajov z externých zdrojov údajov (Power Query).
Získanie údajov do Accessu
Táto časť vás prevedie nasledujúcimi fázami normalizácie údajov: Rozdelenie hodnôt v stĺpcoch Predajca a Adresa do naj atómových častí, oddelenie súvisiacich predmetov do vlastných tabuliek, kopírovanie a prilepenie týchto tabuliek z Excelu do Accessu, vytvorenie kľúčových vzťahov medzi novovytvorenými accessovými tabuľkami a vytvorenie a spustenie jednoduchého dotazu v Accesse na vrátenie informácií.
Príklad údajov v nenormalizovanej forme
Nasledujúci hárok obsahuje neomymické hodnoty v stĺpci Predajca a v stĺpci Adresa. Oba stĺpce by mali byť rozdelené do dvoch alebo viacerých samostatných stĺpcov. Tento hárok obsahuje aj informácie o predajcoch, produktoch, zákazníkoch a objednávkach. Tieto informácie by sa mali ďalej rozdeliť podľa predmetu do samostatných tabuliek.
Predajca |
Identifikácia objednávky |
Dátum objednávky |
ID produktu |
Množstvo |
Cena |
Meno zákazníka |
Adresa |
Telefón |
---|---|---|---|---|---|---|---|---|
Li, Yale |
2349 |
3/4/09 |
C-789 |
3 |
7,00 $ |
Kaviareň Slávia |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Li, Yale |
2349 |
3/4/09 |
C-795 |
6 |
9,75 $ |
Kaviareň Slávia |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Adams, Ellen |
2350 |
3/4/09 |
A-2275 |
2 |
16,75 $ |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Adams, Ellen |
2350 |
3/4/09 |
F-198 |
6 |
5,25 $ |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Adams, Ellen |
2350 |
3/4/09 |
B-205 |
1 |
4,50 $ |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Hance, Jim |
2351 |
3/4/09 |
C-795 |
6 |
9,75 $ |
Contoso, Ltd. |
2302 Harvard Ave Bellevue, WA 98227 |
425-555-0222 |
Hance, Jim |
2352 |
3/5/09 |
A-2275 |
2 |
16,75 $ |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Hance, Jim |
2352 |
3/5/09 |
D-4420 |
3 |
7,25 $ |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Koch, Reed |
2353 |
3/7/09 |
A-2275 |
6 |
16,75 $ |
Kaviareň Slávia |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Koch, Reed |
2353 |
3/7/09 |
C-789 |
5 |
7,00 $ |
Kaviareň Slávia |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Informácie v najmenších častiach: atómové údaje
Pri práci s údajmi v tomto príklade môžete použiť príkaz Text na stĺpec v Exceli a oddeliť "atómové" časti bunky (napríklad ulicu, mesto, štát a PSČ) do samostatných stĺpcov.
Nasledujúca tabuľka zobrazuje nové stĺpce v tom istom hárku po ich rozdelení, aby sa všetky hodnoty stali atómovými. Všimnite si, že informácie v stĺpci Predajca boli rozdelené do stĺpcov Priezvisko a Meno a že informácie v stĺpci Adresa boli rozdelené do stĺpcov Ulica, Mesto, Štát a PSČ. Tieto údaje sú v "prvej normálnej forme".
Last Name |
First Name |
|
Ulica |
Mesto |
Štát |
Smerovacie číslo |
---|---|---|---|---|---|---|
Li |
Yale |
2302 Harvard Ave |
Malacky |
WA |
98227 |
|
Kobetič |
Ellen |
1025 Columbia Circle |
Kirkland |
WA |
98234 |
|
Konečný |
Jim |
2302 Harvard Ave |
Malacky |
WA |
98227 |
|
Koch |
Trstina |
7007 Cornell St Redmond |
Redmond |
WA |
98199 |
Rozdelenie údajov do usporiadaných predmetov v Exceli
Niekoľko tabuliek vzorových údajov, ktoré nasledujú, zobrazuje rovnaké informácie z excelového hárka po ich rozdelení do tabuliek pre predajcov, produkty, zákazníkov a objednávky. Návrh tabuľky nie je konečný, ale je na správnej ceste.
Tabuľka Salespersons (Predajcovia) obsahuje len informácie o pracovníkoch predaja. Všimnite si, že každý záznam má jedinečné ID (ID predajcu). Hodnota ID predajcu sa použije v tabuľke Objednávky na prepojenie objednávok s predajcami.
Predajcovia |
||
---|---|---|
Identifikácia predajcu |
Last Name |
First Name |
101 |
Li |
Yale |
103 |
Kobetič |
Ellen |
105 |
Konečný |
Jim |
107 |
Koch |
Trstina |
Tabuľka Produkty obsahuje len informácie o produktoch. Všimnite si, že každý záznam má jedinečný identifikátor (ID produktu). Hodnota ID produktu sa použije na pripojenie informácií o produkte k tabuľke Podrobnosti objednávky.
Produkty |
|
---|---|
ID produktu |
Cena |
A-2275 |
16.75 |
B-205 |
4.50 |
C-789 |
7,00 |
C-795 |
9.75 |
D-4420 |
7.25 |
F-198 |
5,25 |
Tabuľka Zákazníci obsahuje len informácie o zákazníkoch. Všimnite si, že každý záznam má jedinečné ID (ID zákazníka). Hodnota ID zákazníka sa použije na pripojenie informácií o zákazníkoch k tabuľke Objednávky.
Customers |
||||||
---|---|---|---|---|---|---|
Identifikácia zákazníka |
Názov |
Ulica |
Mesto |
Štát |
Smerovacie číslo |
Telefón |
1001 |
Contoso, Ltd. |
2302 Harvard Ave |
Malacky |
WA |
98227 |
425-555-0222 |
1003 |
Adventure Works |
1025 Columbia Circle |
Kirkland |
WA |
98234 |
425-555-0185 |
1005 |
Kaviareň Slávia |
7007 Cornell St |
Redmond |
WA |
98199 |
425-555-0201 |
Tabuľka Objednávky obsahuje informácie o objednávkach, predajcoch, zákazníkoch a produktoch. Všimnite si, že každý záznam má jedinečné ID (ID objednávky). Niektoré informácie v tejto tabuľke je potrebné rozdeliť do ďalšej tabuľky, ktorá obsahuje podrobnosti objednávky, aby tabuľka Objednávky obsahovala iba štyri stĺpce – jedinečné ID objednávky, dátum objednávky, ID predajcu a identifikáciu zákazníka. Tabuľka zobrazená na tomto mieste ešte nebola rozdelená do tabuľky Podrobnosti objednávky.
Objednávky |
|||||
---|---|---|---|---|---|
Identifikácia objednávky |
Dátum objednávky |
Identifikácia predajcu |
Identifikácia zákazníka |
ID produktu |
Množstvo |
2349 |
3/4/09 |
101 |
1005 |
C-789 |
3 |
2349 |
3/4/09 |
101 |
1005 |
C-795 |
6 |
2350 |
3/4/09 |
103 |
1003 |
A-2275 |
2 |
2350 |
3/4/09 |
103 |
1003 |
F-198 |
6 |
2350 |
3/4/09 |
103 |
1003 |
B-205 |
1 |
2351 |
3/4/09 |
105 |
1001 |
C-795 |
6 |
2352 |
3/5/09 |
105 |
1003 |
A-2275 |
2 |
2352 |
3/5/09 |
105 |
1003 |
D-4420 |
3 |
2353 |
3/7/09 |
107 |
1005 |
A-2275 |
6 |
2353 |
3/7/09 |
107 |
1005 |
C-789 |
5 |
Podrobnosti objednávky, ako napríklad ID produktu a množstvo, sa premiestnia z tabuľky Objednávky a uložia do tabuľky s názvom Podrobnosti objednávky. Nezabúdajte, že existuje 9 objednávok, takže je logické, že v tejto tabuľke je 9 záznamov. Všimnite si, že tabuľka Objednávky má jedinečné ID (ID objednávky), na ktoré bude odkazovať z tabuľky Podrobnosti objednávky.
Konečný návrh tabuľky Objednávky by mal vyzerať takto:
Objednávky |
|||
---|---|---|---|
Identifikácia objednávky |
Dátum objednávky |
Identifikácia predajcu |
Identifikácia zákazníka |
2349 |
3/4/09 |
101 |
1005 |
2350 |
3/4/09 |
103 |
1003 |
2351 |
3/4/09 |
105 |
1001 |
2352 |
3/5/09 |
105 |
1003 |
2353 |
3/7/09 |
107 |
1005 |
Tabuľka Podrobnosti objednávky neobsahuje žiadne stĺpce, ktoré by vyžadovali jedinečné hodnoty (čiže nie je k dispozícii žiadny primárny kľúč), takže je v poriadku, aby všetky stĺpce obsahovali nadbytočné údaje. Žiadne dva záznamy v tejto tabuľke by však nemali byť úplne identické (toto pravidlo sa vzťahuje na ľubovoľnú tabuľku v databáze). V tejto tabuľke by malo existovať 17 záznamov – každý z nich zodpovedá produktu v samostatnej objednávke. Napríklad v poradí 2349 tri produkty C-789 obsahujú jednu z dvoch častí celej objednávky.
Tabuľka Podrobnosti objednávky by preto mala vyzerať takto:
Podrobnosti objednávky |
||
---|---|---|
Identifikácia objednávky |
ID produktu |
Množstvo |
2349 |
C-789 |
3 |
2349 |
C-795 |
6 |
2350 |
A-2275 |
2 |
2350 |
F-198 |
6 |
2350 |
B-205 |
1 |
2351 |
C-795 |
6 |
2352 |
A-2275 |
2 |
2352 |
D-4420 |
3 |
2353 |
A-2275 |
6 |
2353 |
C-789 |
5 |
Kopírovanie a prilepenie údajov z Excelu do Accessu
Teraz, keď sú informácie o predajcoch, zákazníkoch, produktoch, objednávkach a podrobnostiach objednávok rozdelené do samostatných predmetov v Exceli, môžete tieto údaje skopírovať priamo do Accessu, kde sa stanú tabuľkami.
Vytvorenie vzťahov medzi accessovými tabuľkami a spustenie dotazu
Po presunutí údajov do Accessu môžete vytvoriť vzťahy medzi tabuľkami a potom vytvoriť dotazy na vrátenie informácií o rôznych predmetoch. Môžete napríklad vytvoriť dotaz, ktorý vráti ID objednávky a názvy predajcov pre objednávky zadané medzi 05.3.09 a 08.08.09.
Okrem toho môžete vytvárať formuláre a zostavy, ktoré zjednodušia zadávanie údajov a analýzu predaja.
Potrebujete ďalšiu pomoc?
Vždy sa môžete opýtať odborníka v komunite Excel Tech Community alebo získať podporu v komunitách.