V tomto článku sa dozviete, ako presunúť údaje z Excelu na prístup a skonvertovať údaje na relačné tabuľky, aby ste mohli používať Microsoft Excel a Access spoločne. Ak chcete zhrnúť, Access je najvhodnejší na zachytenie, ukladanie, vytváranie dotazov a zdieľanie údajov a Excel je najvhodnejší na výpočet, analyzovanie a vizualizáciu údajov.
Dva články s použitím Accessu alebo Excelu na správu údajov a Desať najdôležitejších dôvodov na používanie Accessu s Excelom, diskutovať o tom, ktorý program je najvhodnejší pre konkrétnu úlohu a ako používať Excel a Access spoločne na vytvorenie praktického riešenia.
Keď presuniete údaje z Excelu na Access, tento proces obsahuje tri základné kroky.
Poznámka: Informácie o modelovanie údajov a vzťahoch v Accesse nájdete v téme základy návrhu databázy.
Krok 1: Importovanie údajov z Excelu do programu Access
Importovanie údajov je operácia, ktorá môže oveľa ľahšie fungovať, ak budete mať nejaký časový význam na prípravu a vyčistenie údajov. Importovanie údajov je ako prechod na nový domov. Ak ste pred premiestnením vyčistia a usporiadali svoje majetky, vyrovnanie sa s novým domovom je oveľa jednoduchšie.
Vyčistenie údajov pred importovaním
Pred importovaním údajov do Accessu je v Exceli vhodné:
-
Skonvertovať bunky, ktoré obsahujú iné ako atómové údaje (čiže viaceré hodnoty v jednej bunke) do viacerých stĺpcov. Napríklad bunka v stĺpci "skills", ktorá obsahuje viacero hodnôt kvalifikácie, ako je napríklad programovanie v jazyku C#, "" Programovanie VBA "a" Web Design ", by sa mala rozdeliť na samostatné stĺpce, ktoré obsahujú len jednu hodnotu kvalifikácie.
-
Použite príkaz TRIM na odstránenie úvodných, koncových a viacerých vložených medzier.
-
Odstránenie znakov, ktoré nie sú tlačené.
-
Vyhľadajte a opravte chyby pravopisu a interpunkcie.
-
Odstránenie duplicitných riadkov alebo duplicitných polí.
-
Uistite sa, že 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 programu Excel:
Poznámka: Ak sú vaše potreby na čistenie údajov zložité, alebo nemáte časovú alebo zdrojovú automatizáciu samotného procesu, zvážte použitie dodávateľa tretej strany. Ďalšie informácie nájdete v časti "softvér na čistenie údajov" alebo "kvalita údajov" prostredníctvom vášho obľúbeného vyhľadávacieho nástroja vo webovom prehliadači.
Výber najlepšieho typu údajov pri importovaní
Počas operácie importovania v Accesse si chcete vybrať z možností, aby sa zobrazili niektoré chyby konverzie, ktoré budú vyžadovať manuálny zásah. Nasledujúca tabuľka obsahuje súhrn spôsobu konverzie formátov čísel a typov údajov Accessu pri importovaní údajov z Excelu do programu Access a ponúka niekoľko tipov na dosiahnutie najvhodnejších typov údajov, ktoré môžete vybrať v Sprievodcovi importom tabuľkových hárkov.
Formát čísla Excelu | Typ údajov Accessu | Komentáre | Najvhodnejší postup |
---|---|---|---|
Text |
Text, Memo |
Typ údajov Accessu ukladá alfanumerické údaje až do 255 znakov. Typ údajov Accessového Memo ukladá alfanumerické údaje až do 65 535 znakov. |
Vyberte položku Memo , aby ste zabránili skráteniu všetkých údajov. |
Číslo, percento, zlomok, vedecký |
Číslo |
Access má jeden typ údajov Number, ktorý sa líši v závislosti od vlastnosti veľkosť poľa (bajt, celé číslo, dlhé celé číslo, jedno, dvojité, desatinné číslo). |
Vyberte položku zdvojnásobiť , aby ste sa vyhli chybám konverzie údajov. |
Date |
Dátum |
Access a Excel používajú rovnaké poradové číslo dátumu na ukladanie dátumov. V Accesse je rozsah dátumov väčší: od – 657 434 (január 1; 100 NL) po 2 958 465 (31. december, 9999 NL). Keďže Access nerozpozná kalendárny systém 1904 (používaný v Exceli pre Macintosh), je potrebné skonvertovať dátumy v Exceli alebo Accesse, aby sa predišlo zámene. Ďalšie informácie nájdete v téme Zmena dátumu systému, formátu alebo dvoch číselných interpretácií roka a Importovanie údajov alebo prepojenie s údajmi v zošite programu Excel. |
Vyberte položku Dátum. |
Time |
Čas |
V Accesse a Exceli môžete hodnoty času uložiť pomocou rovnakého typu údajov. |
Vyberte položku Doba, ktorá je zvyčajne predvolená. |
Mena, účtovanie |
Mena |
V Accesse typ údajov meny 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 položku mena, ktorá je zvyčajne predvolená. |
boolovský výraz |
Áno/Nie |
Access použije hodnotu-1 pre všetky hodnoty Áno a hodnotu 0 pre všetky žiadne hodnoty, zatiaľ čo Excel používa 1 pre všetky pravdivé hodnoty a hodnotu 0 pre všetky falošné hodnoty. |
Vyberte položku Áno/nie, ktorá automaticky skonvertuje základné hodnoty. |
Hypertextové prepojenie |
Hypertextové prepojenie |
Hypertextové prepojenie v Exceli a Accesse obsahuje URL adresu alebo webovú adresu, ktorú môžete kliknúť a sledovať. |
Vyberte položku hypertextové prepojenie, v opačnom prípade môže Access použiť typ údajov text na základe predvoleného nastavenia. |
Keď sú údaje v Accesse, môžete odstrániť excelové údaje. Skôr než ho odstránite, nezabudnite najskôr zálohovať pôvodný excelový zošit.
Ďalšie informácie nájdete v téme Pomocník pre Access Import údajov alebo prepojenie s údajmi v zošite programu Excel.
Automatické pripojenie údajov jednoduchým spôsobom
Bežný problém, ktorý používatelia programu Excel majú, pripojí k jednému veľkému hárku údaje s tými istými stĺpcami. Môžete napríklad použiť riešenie na sledovanie majetku, ktoré sa začalo v Exceli, ale teraz sa rozrástla tak, aby obsahovalo 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 alebo jednoduchý spôsob, ako pripojiť podobné údaje v Exceli.
Najlepším riešením je použitie Accessu, kde môžete jednoducho importovať a pripojiť údaje do jednej tabuľky pomocou Sprievodcu importom tabuľkových hárkov. Okrem toho môžete do jednej tabuľky pridať veľa údajov. Môžete uložiť operácie importu, pridať ich do plánovaných úloh programu Microsoft Outlook a dokonca používať makrá na automatizáciu procesu.
Krok 2: normalizácia údajov pomocou Sprievodcu analýzou tabuľky
Na prvý pohľad sa môže zintenzívniť proces normalizácie údajov, ktoré sa môžu zdať skľučujúcou úlohou. Vďaka Sprievodcovi analýzou tabuľky Našťastie normalizácia tabuliek v Accesse je proces, ktorý je oveľa jednoduchší.
1. presuňte vybraté stĺpce do novej tabuľky a automaticky vytvorte vzťahy
2. Použite tlačidlo príkazy na Premenovanie tabuľky, Pridanie hlavného kľúča, vytvorenie existujúceho stĺpca primárnym kľúčom a zrušenie poslednej akcie
Pomocou tohto sprievodcu môžete vykonať tieto kroky:
-
Skonvertuje tabuľku na množinu menších tabuliek a automaticky vytvorte vzťah primárneho a cudzieho kľúča medzi tabuľkami.
-
Pridajte primárny kľúč k existujúcemu poľu, ktoré obsahuje jedinečné hodnoty, alebo vytvorte nové pole ID, ktoré používa typ údajov Automatické číslovanie.
-
Automatické vytvorenie vzťahov na vynútenie referenčnej integrity pomocou kaskádových aktualizácií. Kaskádové odstránenia sa nepridajú automaticky, aby sa predišlo neúmyselnému odstraňovaniu údajov, môžete však neskôr jednoducho pridať kaskádové odstránenie.
-
Vyhľadávajte nové tabuľky nadbytočných alebo duplicitných údajov (napríklad rovnakého zákazníka s dvomi rôznymi telefónnymi číslami) a aktualizujte ho podľa potreby.
-
Zálohujte pôvodnú tabuľku a premenujte ju tak, že na jej názov pripojíte "_OLD". Potom vytvoríte dotaz, ktorý rekonštruuje pôvodnú tabuľku, s názvom pôvodnej tabuľky, 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 analýzy tabuľky.
Krok 3: pripojenie k údajom Accessu z Excelu
Po tom, ako boli údaje v Accesse normalizované a bol vytvorený dotaz alebo tabuľka, ktorá rekonštruuje pôvodné údaje, je to jednoduchá otázka pripojenia k údajom Accessu z Excelu. Údaje sú teraz v Accesse ako externý zdroj údajov, a preto sa môžu pripojiť k zošitu prostredníctvom pripojenia údajov, čo je kontajner informácií, ktorý sa používa na vyhľadanie, prihlásenie a prístup k externému zdroju údajov. Informácie o pripojení sú uložené v zošite a môžu byť uložené aj 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 v programe Access automaticky obnoviť (alebo aktualizovať) zošit programu Excel vždy, keď sa údaje aktualizujú v Accesse.
Ďalšie informácie nájdete v téme Importovanie údajov z externých zdrojov údajov (Power Query).
Získanie údajov do Accessu
Táto časť vás prevedie týmito fázami normalizácie údajov: rozbitie hodnôt v stĺpcoch predajca a adresa do svojich najviac atómových častí, oddelenie príbuzných predmetov do vlastných tabuliek, kopírovanie a prilepenie týchto tabuliek z Excelu do Access, vytváranie kľúčových vzťahov medzi novovytvorenými Accessmi 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 iné ako atómové hodnoty v stĺpci Predajca a v stĺpci adresa. Oba stĺpce by sa mali rozdeliť na dva alebo viac samostatných stĺpcov. Tento hárok obsahuje aj informácie o predavačoch, produktoch, zákazníkoch a objednávkach. Tieto informácie by sa mali ďalej rozdeliť podľa predmetu na samostatné tabuľky.
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: údaje o atómových údajoch
Práca s údajmi v tomto príklade môžete použiť príkaz text na stĺpec v Exceli na oddelenie "atómových" častí bunky (ako je napríklad poštová adresa, mesto, štát a PSČ) do samostatných stĺpcov.
V nasledujúcej tabuľke sú uvedené nové stĺpce v tom istom hárku, ktoré boli rozdelené tak, aby boli všetky hodnoty atómové. Všimnite si, že informácie v stĺpci Predajca boli rozdelené do stĺpcov priezvisko a krstné meno a že informácie v stĺpci adresa boli rozdelené do stĺpcov adresy ulíc, miest, štátov a PSČ. Tieto údaje sa nachádzajú v časti prvý normálny formulár.
Last Name | First Name |
| Ulica | Mesto | Štát | smerovacie číslo |
---|---|---|---|---|---|---|
Li |
Yale |
2302 Harvard Ave |
Malacky |
WA |
98227 |
|
Kobetič |
Premenná |
1025 Columbia Circle |
Kirkland |
WA |
98234 |
|
Konečný |
Jim |
2302 Harvard Ave |
Malacky |
WA |
98227 |
|
Koch |
Reed |
7007 Cornell St Redmond |
Redmond |
WA |
98199 |
Prelomenie údajov do usporiadaných predmetov v Exceli
Niekoľko tabuliek s príkladmi údajov, ktoré nasledujú, zobrazujú tie isté informácie z excelového hárka po rozdelení na tabuľky pre predajcov, produkty, zákazníkov a objednávky. Návrh tabuľky nie je konečný, ale nachádza sa na správnej ceste.
Tabuľka predajcovia obsahuje len informácie o pracovníkoch oddelenia predaja. Všimnite si, že každý záznam má jedinečnú identifikáciu (ID predajcu). Hodnota ID predajcu sa použije v tabuľke objednávky na pripojenie objednávok k predajcom.
Predajcovia | ||
---|---|---|
Identifikácia predajcu |
Last Name |
First Name |
101 |
Li |
Yale |
103 |
Kobetič |
Premenná |
105 |
Konečný |
Jim |
107 |
Koch |
Reed |
Tabuľka produkty obsahuje len informácie o produktoch. Všimnite si, že každý záznam má jedinečnú identifikáciu (ID produktu). Hodnota ID produktu sa použije na pripojenie informácií o produktoch 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ú identifikáciu (identifikáciu zákazníka). Hodnota ID zákazníka sa použije na spojenie informácií o zákazníkoch s tabuľkou 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, predavačoch, zákazníkoch a produktoch. Všimnite si, že každý záznam má jedinečnú identifikáciu (ID objednávky). Niektoré z informácií v tejto tabuľke je potrebné rozdeliť do ďalšej tabuľky, ktorá obsahuje Podrobnosti objednávky, aby tabuľka objednávky obsahovala len štyri stĺpce – jedinečnú identifikáciu objednávky, dátum objednávky, ID predajcu a identifikáciu zákazníka. Tabuľka, ktorá sa tu zobrazuje, zatiaľ nebola rozdelená do tabuľky Podrobnosti objednávky.
Orders | |||||
---|---|---|---|---|---|
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 je napríklad ID produktu a množstvo, sa odstránia z tabuľky objednávky a uložia sa do tabuľky s názvom Podrobnosti objednávky. Majte na pamäti, že k dispozícii je 9 objednávok, preto je potrebné, aby sa v tejto tabuľke nachádzalo 9 záznamov. Všimnite si, že tabuľka objednávky obsahuje jedinečnú identifikáciu (ID objednávky), ktorá sa bude uvádzať v tabuľke Podrobnosti objednávky.
Konečný návrh tabuľky objednávky by mal vyzerať takto:
Orders | |||
---|---|---|---|
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é vyžadujú jedinečné hodnoty (čiže nie je k dispozícii žiadny hlavný kľúč), takže je v poriadku pre všetky alebo všetky stĺpce, ktoré obsahujú nadbytočné údaje. Žiadne dva záznamy v tejto tabuľke by však nemali byť úplne identické (Toto pravidlo platí pre ľubovoľnú tabuľku v databáze). V tejto tabuľke by malo byť 17 záznamov, z ktorých každá zodpovedá produktu v individuálnej objednávke. Napríklad v objednávke 2349 tri produkty C-789 obsahujú jednu z dvoch častí celej objednávky.
Tabuľka s podrobnosťami 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ď sa informácie o predavačoch, zákazníkoch, produktoch, objednávkach a podrobnostiach objednávok rozdelili na samostatné predmety v Exceli, môžete tieto údaje skopírovať priamo do Accessu, kde sa stanú tabuľkami.
Vytvorenie vzťahov medzi tabuľkami Accessu a spustením dotazu
Po premiestnení ú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 identifikáciu objednávky a mená predajcov objednávok zadaných medzi 3/05/09 a 3/08/09.
Okrem toho môžete vytvárať formuláre a zostavy na zjednodušenie zadávania údajov a analýzy predaja.
Potrebujete ďalšiu pomoc?
Vždy sa môžete opýtať odborníka v komunite Excel Tech Community, získať podporu v rámci komunity lokality Answers alebo navrhnúť novú funkciu či vylepšenie na lokalite Excel User Voice.