Přesunutí dat z Excelu do Accessu

V tomto článku se dozvíte, jak přesunout data z Excelu do Accessu a jak je převést na relační tabulky, abyste mohli používat Microsoft Excel a Access pokaždé. Abyste mohli vytvořit souhrn, Access je nejvhodnější pro zachycování, ukládání, dotazování a sdílení dat a Excel je nejvhodnější pro výpočet, analýzu a vizualizaci dat.

Dva články: pomocí Accessu nebo Excelu můžete spravovat data a 10 hlavních důvodů pro použití aplikace Access s Excelema diskutovat o tom, který program je nejvhodnější pro konkrétní úkol a jak používat Excel a Access společně k vytvoření praktického řešení.

Když přesunete data z Excelu do Accessu, máte tento postup třemi základními kroky.

three basic steps

Poznámka: Informace o modelování a relacích dat v Accessu najdete v článku Základy návrhu databáze.

Krok 1: import dat z Excelu do Accessu

Import dat je operace, která se bude mnohem hladově rychle dostat, pokud si nějakou dobu vyřešíte a vyčistíte. Import dat se podobá přesunutí do nové domů. Pokud před přemístěním vyčistíte a uspořádáte vlastníky, bude to mnohem jednodušší.

Vyčištění dat před importem

Před importem dat do Accessu je v Excelu dobré:

  • Převést buňky obsahující neatomová data (to znamená více hodnot v jedné buňce) do více sloupců Například buňka ve sloupci "dovednosti", která obsahuje více hodnot dovedností, třeba "programování v jazyce C#", "programování v jazyce VBA" a "návrh webu" by měla být rozdělená na jednotlivé sloupce, které obsahují jenom jednu dovednostní hodnotu.

  • Pomocí příkazu pročistit můžete odebrat úvodní, koncové a více vložené mezery.

  • Odeberte netisknutelné znaky.

  • Nalezení a oprava pravopisných a interpunkčních chyb

  • Odebrání duplicitních řádků nebo duplicitních polí

  • Ujistěte se, že sloupce dat neobsahují různé formáty, zejména čísla formátovaná jako text nebo kalendářní data formátovaná jako čísla.

Další informace najdete v následujících tématech nápovědy k Excelu:

Poznámka: Pokud jsou vaše potřeby čištění dat složité nebo nemáte čas ani prostředky k automatizaci vlastního procesu, můžete zvážit použití jiného dodavatele. Další informace najdete v tématu o tom, že váš oblíbený vyhledávací web ve webovém prohlížeči hledá "software pro čištění dat" nebo "kvalita dat".

Volba nejlepšího datového typu při importu

V průběhu operace importu v Accessu budete chtít mít dobré možnosti, abyste měli k dispozici pár chyb při převodu, které budou vyžadovat ruční zásah. Následující tabulka shrnuje, jak se při importu dat z Excelu do Accessu převedou formáty čísel v Excelu a datové typy Accessu a nabízí několik tipů k dosažení nejlepších dostupných datových typů v Průvodci importem.

Formát čísla v Excelu

Datový typ Accessu

Komentáře

Doporučené postupy

Text

Text, Memo

Datový typ Accessu obsahuje Alfanumerická data až 255 znaků. Datový typ Memo Access ukládá Alfanumerická data až na 65 535 znaků.

Zvolte Poznámka , abyste předešli zkracování dat.

Číslo, procenta, zlomek, matematické

Číslo:

Access má jeden datový typ číslo, který se liší podle vlastnosti velikost pole (Byte, celé číslo, dlouhé celé číslo, jednoduché, dvojité, desetinné).

Pokud chcete předejít chybám při převodu dat, zvolte Double .

Funkce Date

Date

Access a Excel používají ke skladování kalendářních dat stejné sériové číslo. V Accessu je rozsah dat větší: od-657 434 (1. ledna 100) do 2 958 465 (31. prosince, 9999 N.L.).

Protože Access nerozpoznává kalendářní systém 1904 (používaný v Excelu pro Macintosh), je třeba data převést v Excelu nebo Accessu, abyste předešli nejasnostem.

Další informace najdete v tématech Změna kalendářního systému, formátování nebo vyjádření ročního výkladu roku a Import nebo propojení dat v excelovém sešitu.

Zvolte Datum.

Funkce Time

Čas

Access a Excel ukládají hodnoty času pomocí stejného datového typu.

Zvolte čas, což je obvykle výchozí hodnota.

Měna, účetnictví

Měna

V Accessu datový typ Měna ukládá data jako 8bitové čísla s přesností na čtyři desetinná místa a slouží k ukládání finančních dat a zabránění zaokrouhlování hodnot.

Zvolte Měna, která je obvykle výchozí.

Logická hodnota

Ano/Ne

Access používá hodnotu-1 pro všechny hodnoty Ano a 0 pro všechny hodnoty ne, zatímco Excel používá 1 pro všechny skutečné hodnoty a 0 pro všechny hodnoty FALSE.

Zvolte Ano/ne, která automaticky převede podkladové hodnoty.

Hypertextový odkaz

Hypertextový odkaz

Hypertextový odkaz v Excelu a Accessu obsahuje adresu URL nebo webovou adresu, na kterou můžete kliknout a sledovat ji.

Zvolte hypertextový odkaz, jinak Access smí ve výchozím nastavení používat datový typ text.

Jakmile budou data v Accessu, můžete data z Excelu odstranit. Nezapomeňte před odstraněním původní excelový sešit zálohovat.

Další informace najdete v tématu nápovědy k Accessu Import nebo propojení dat v excelovém sešitu.

Automatické připojení dat

Běžné problémy: uživatelé Excelu přidávají data se stejnými sloupci do jednoho velkého listu. Můžete mít například řešení sledování majetku, které se v Excelu spustilo, ale teď se zvětšilo, že zahrnuje soubory z mnoha pracovních skupin a oddělení. Tato data můžou být v různých listech a sešitech nebo v textových souborech s datovými kanály z jiných systémů. V Excelu neexistuje příkaz uživatelského rozhraní ani snadný způsob, jak připojit podobná data.

Nejlepším řešením je použít Access, kde můžete snadno importovat a připojit data do jedné tabulky pomocí Průvodce importem tabulky. Navíc můžete do jedné tabulky připojit velké množství dat. Operace importu můžete uložit, přidat jako plánované úkoly Microsoft Outlooku a dokonce můžete proces automatizovat pomocí maker.

Krok 2: normalizace dat pomocí Průvodce analýzou tabulky

Na první pohled se může postupovat normalizací vašich dat na daunting úkol. Naštěstí: normalizace tabulek v Accessu je jednodušší a díky Průvodci analýzou tabulky.

the table analyzer wizard

1. Přetáhněte vybrané sloupce do nové tabulky a automaticky vytvořte relace.

2. k přejmenování tabulky pomocí příkazů tlačítka, přidání primárního klíče, vytvoření existujícího sloupce jako primárního klíče a vrácení poslední akce zpět

Pomocí tohoto průvodce můžete:

  • Převeďte tabulku na sadu menších tabulek a automaticky vytvořte vztah primárního a cizího klíče mezi tabulkami.

  • Přidejte primární klíč do existujícího pole, které obsahuje jedinečné hodnoty, nebo vytvořte nové pole ID, které používá datový typ Automatické číslo.

  • Automaticky vytvářet relace pro vynucení referenční integrity v kaskádových aktualizacích. Kaskádové odstranění se automaticky nepřidá, aby se zabránilo nechtěnému odstranění dat

  • Prohledejte nové tabulky pro redundantní nebo duplicitní data (například stejného zákazníka se dvěma různými telefonními čísly) a aktualizujte to podle potřeby.

  • Vytvořte zálohu původní tabulky a přejmenujte ji přidáním slova "_OLD" do jeho názvu. Potom vytvoříte dotaz, který rekonstruuje původní tabulku s původními názvy, aby všechny existující formuláře nebo sestavy založené na původní tabulce fungovaly s novou strukturou tabulky.

Další informace najdete v článku normalizace dat pomocí funkce Analýza tabulky.

Krok 3: připojení k datům Accessu z Excelu

Po normalizování dat v Accessu a vytvoření dotazu nebo tabulky, která rekonstruuje původní data, je jednoduchá možnost připojení k datům Accessu z Excelu. Data jsou teď ve Accessu jako externí zdroj dat, a proto je můžete připojit k sešitu pomocí datového připojení, což je kontejner informací, které se používají k vyhledání, přihlášení k a získání přístupu k externímu zdroji dat. Informace o připojení se ukládají v sešitu a můžou být uložené taky v souboru připojení, třeba v souboru ODC (Office Data Connection) (Přípona souboru. odc) nebo v souboru názvu zdroje dat (. přípona. DSN). Po připojení k externím datům můžete automaticky aktualizovat (nebo aktualizovat) excelový sešit z Accessu, kdykoli aktualizujete data v Accessu.

Další informace najdete v tématu Import dat z externích zdrojů dat (Power Query).

Získání dat do Accessu

V této části se seznámíte s následujícími fázemi normalizace dat: rozdělení hodnot ve sloupcích prodejce a adresa do většiny atomových částí, oddělení souvisejících předmětů do jejich vlastních tabulek, zkopírování a vložení těchto tabulek z aplikace Excel do Access, vytvoření vztahů mezi nově vytvořenými tabulkami Accessu a vytvořením a prováděním jednoduchého dotazu v Accessu a vrácení informací.

Příklad dat v nenormalizované podobě

Následující list obsahuje neatomové hodnoty ve sloupci prodejce a sloupci adresa. Oba sloupce by měly být rozděleny do dvou nebo více samostatných sloupců. Tento list obsahuje také informace o prodejcech, produktech, zákaznících a objednávkách. Tyto informace by měly být dále rozděleny podle předmětu do samostatných tabulek.

Prodejce

ID objednávky

Datum objednávky

Product ID

Množ

Cena

Jméno zákazníka

Adresa

Telefon

Li, Yale

2349

3/4/09

C-789

3

$7,00

Fourth Coffee

7007 Cornell St Redmond, WA 98199

425-555-0201

Li, Yale

2349

3/4/09

C-795

6

$9,75

Fourth Coffee

7007 Cornell St Redmond, WA 98199

425-555-0201

Kupková, že Helena

2350

3/4/09

A-2275

2

$16,75

Adventure Works

1025 Kolumbie Kirkland, WA 98234

425-555-0185

Kupková, že Helena

2350

3/4/09

F-198

6

$5,25

Adventure Works

1025 Kolumbie Kirkland, WA 98234

425-555-0185

Kupková, že Helena

2350

3/4/09

B-205

1

$4,50

Adventure Works

1025 Kolumbie Kirkland, WA 98234

425-555-0185

Hance, následujícím

2351

3/4/09

C-795

6

$9,75

Contoso, Ltd.

2302 Harvard Ave Pardubice, WA 98227

425-555-0222

Hance, následujícím

2352

3/5/09

A-2275

2

$16,75

Adventure Works

1025 Kolumbie Kirkland, WA 98234

425-555-0185

Hance, následujícím

2352

3/5/09

D-4420

3

$7,25

Adventure Works

1025 Kolumbie Kirkland, WA 98234

425-555-0185

Koch, Reed

2353

3/7/09

A-2275

6

$16,75

Fourth Coffee

7007 Cornell St Redmond, WA 98199

425-555-0201

Koch, Reed

2353

3/7/09

C-789

5

$7,00

Fourth Coffee

7007 Cornell St Redmond, WA 98199

425-555-0201

Informace ve svých nejmenších částech: atomická data

Práce s daty v tomto příkladu: pomocí příkazu text to Column v Excelu můžete oddělit "atomické" části buňky (například ulice, město, stát a PSČ) do samostatných sloupců.

Následující tabulka zobrazuje nové sloupce ve stejném listu po jejich rozdělení tak, aby byly všechny hodnoty atomické. Všimněte si, že informace ve sloupci prodejce jsou rozděleny do sloupců příjmení a jméno a že informace ve sloupci adresa byly rozděleny do sloupců ulice, město, stát a PSČ. Tato data jsou v části první normalizační formulář.

Last Name

First Name

 

Ulice

Město

Stát

PSČ

Li

Yale

2302 Harvard Ave

Pardubice

WA

98227

Florian

Že Helena

1025 Kolumbie

Kirkland

WA

98234

Holý

Jim

2302 Harvard Ave

Pardubice

WA

98227

Koch

Reed

7007 Cornell St Redmond

Redmond

WA

98199

Rozdělení dat do uspořádaných témat v Excelu

V několika tabulkách příkladů dat, která následují po rozdělení do tabulek pro prodejce, produkty, zákazníky a objednávky, se zobrazí stejné informace z excelového listu. Návrh tabulky není finální, ale je na správné dráze.

Tabulka prodejci obsahuje pouze informace o prodejních zaměstnancích. Každý záznam má jedinečné ID (ID prodejce). Hodnota ID prodejce bude použita v tabulce objednávky pro spojení objednávek s prodejci.

Prodejci

ID prodejce

Last Name

First Name

101

Li

Yale

103

Florian

Že Helena

105

Holý

Jim

107

Koch

Reed

Tabulka Products obsahuje pouze informace o produktech. Všimněte si, že každý záznam má jedinečné ID (ID produktu). Hodnota ID produktu se použije k připojení informací o produktu k tabulce Rozpis objednávek.

Produktech

Product ID

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

Tabulka zákazníci obsahuje pouze informace o zákaznících. Všimněte si, že každý záznam má jedinečné ID (ID zákazníka). Hodnota ID zákazníka bude použita k propojení informací o zákaznících s tabulkou objednávky.

Customers (Zákazníci)

Číslo zákazníka

Jméno

Ulice

Město

Stát

PSČ

Telefon

1001

Contoso, Ltd.

2302 Harvard Ave

Pardubice

WA

98227

425-555-0222

1003

Adventure Works

1025 Kolumbie

Kirkland

WA

98234

425-555-0185

1005

Fourth Coffee

7007 Cornell St

Redmond

WA

98199

425-555-0201

Tabulka objednávky obsahuje informace o objednávkách, prodejci, zákaznících a produktech. Všimněte si, že každý záznam má jedinečné ID (ID objednávky). Některé informace v této tabulce je třeba rozdělit na další tabulku, která obsahuje podrobnosti objednávky, aby tabulka objednávky obsahovala pouze čtyři sloupce – jedinečné ID objednávky, datum objednávky, ID prodejce a číslo zákazníka. Tabulka, která je zde uvedena, ještě nebyla rozdělena do tabulky Rozpis objednávek.

Orders (Objednávky)

ID objednávky

Datum objednávky

ID prodejce

ID zákazníka

Product ID

Množ

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, například ID produktu a množství, se přesunou z tabulky objednávky a uloží se do tabulky s názvem podrobnosti objednávky. Mějte na paměti, že je k dispozici 9 objednávek, takže je to smysl, že v této tabulce je 9 záznamů. Tabulka objednávky má jedinečné ID (ID objednávky), na které se bude odkazovat z tabulky Rozpis objednávek.

Konečný návrh tabulky objednávky by měl vypadat takto:

Orders (Objednávky)

ID objednávky

Datum objednávky

ID prodejce

ID 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

Tabulka Rozpis objednávek neobsahuje žádné sloupce, které vyžadují jedinečné hodnoty (to znamená, že neexistuje primární klíč), a proto je v pořádku, aby obsahovala "redundantní" data. Žádné dva záznamy v této tabulce by však neměly být zcela totožné (Toto pravidlo platí pro všechny tabulky v databázi). V této tabulce by měly existovat 17 záznamů – každý odpovídající produktu v jednotlivých objednávkách. Například v pořadí 2349 tři C-789 výrobky obsahují jednu ze dvou částí celé objednávky.

Tabulka Rozpis objednávek by proto měla vypadat takto:

Podrobnosti objednávky

ID objednávky

Product ID

Množ

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írování a vkládání dat z Excelu do Accessu

Teď, když se informace o prodejcůch, zákaznících, produktech, objednávkách a objednávkách v Excelu rozdělily do samostatných předmětů, můžete tato data zkopírovat přímo do Accessu, kde se stanou tabulkami.

Vytvoření relací mezi tabulkami Accessu a zobrazením dotazu

Po přesunutí dat do Accessu můžete vytvořit relace mezi tabulkami a pak vytvořit dotazy, které vrátí informace o různých předmětech. Můžete například vytvořit dotaz, který vrací ID objednávky a jména prodejců pro objednávky zadané mezi 3/05/09 a 3/08/09.

Kromě toho můžete vytvářet formuláře a sestavy a usnadnit tak zadávání dat.

Potřebujete další pomoc?

Kdykoli se můžete zeptat některého odborníka v technické komunitě Excelu, získat podporu v komunitě pro odpovědi, případně navrhnout novou funkci nebo vylepšení ve fóru Excel User Voice.

Rozšiřte své dovednosti s Office
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

Děkujeme vám za zpětnou vazbu.

Děkujeme vám za váš názor! Pravděpodobně bude užitečné, když vás spojíme s některým z našich agentů podpory Office.

×