Presun údajov z Excelu do programu Access

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.

three basic steps

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.

Rozšírte svoje zručnosti práce s balíkom Office
Preskúmať školenie
Buďte medzi prvými, ktorí získajú nové funkcie
Pridajte sa k insiderom pre Office

Boli tieto informácie užitočné?

Ďakujeme za vaše pripomienky!

Ďakujeme vám za pripomienky. Pravdepodobne vám pomôže, ak vás spojíme s pracovníkom podpory pre Office.

×