Applies ToExcel pro Microsoft 365 Excel 2024 Access 2024 Excel 2021 Access 2021 Excel 2019 Access 2019 Excel 2016 Access 2016

Poznámka: Microsoft Access nepodporuje import excelových dat s použitým popiskem citlivosti. Jako alternativní řešení můžete popisek před importem odebrat a po importu ho znovu použít. Další informace najdete v článku Použití popisků citlivosti u souborů a e-mailů v Office.

V tomto článku se dozvíte, jak přesunout data z Excelu do Accessu a převést je na relační tabulky, abyste mohli používat Microsoft Excel a Access společně. Když to shrneme, Access je nejvhodnější pro zachytává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: Použití Accessu nebo Excelu ke správě dat a 10 hlavních důvodů, proč používat Access s Excelem, popisují, který program je nejvhodnější pro konkrétní úkol a jak společně používat Excel a Access k vytvoření praktického řešení.

Když přesunete data z Excelu do Accessu, proces má tři základní kroky.

three basic steps

Poznámka: Informace o modelování dat a relacích v Accessu najdete v tématu Základy návrhu databáze.

Krok 1: Import dat z Excelu do Accessu

Import dat je operace, která může proběhnout mnohem plynuleji, pokud vám příprava a vyčištění dat zabere nějaký čas. Import dat je jako přesun do nového domova. Pokud si před přestěhováním uklidíte a uspořádáte svůj majetek, usadit se v novém domově je mnohem jednodušší.

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

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

  • Převeďte buňky, které obsahují neatomická data (tj. více hodnot v jedné buňce), na více sloupců. Například buňka ve sloupci "Dovednosti", která obsahuje více hodnot dovedností, například "programování v jazyce C#", "programování VBA" a "Návrh webu", by měla být rozdělena na samostatné sloupce, které obsahují pouze jednu hodnotu dovednosti.

  • Pomocí příkazu PROČISTIT odeberte úvodní, koncové a více vložených mezer.

  • Odeberte netisknoucí znaky.

  • Vyhledání a oprava pravopisu a interpunkčních znaménách

  • Odeberte duplicitní řádky nebo duplicitní pole.

  • Ujistěte se, že sloupce dat neobsahují smíšené 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 požadavky na čištění dat složité nebo nemáte čas nebo prostředky na automatizaci procesu sami, můžete zvážit použití jiného dodavatele. Další informace získáte tak, že ve webovém prohlížeči vyhledáte "software pro čištění dat" nebo "kvalitu dat".

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

Během operace importu v Accessu chcete provést správná rozhodnutí, aby se zobrazilo několik (pokud vůbec nějaké) chyb 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řevádí formáty čísel excelových čísel a datové typy Accessu, a nabízí několik tipů pro nejlepší datové typy, které můžete zvolit v Průvodci importem tabulky.

Formát čísel v Excelu

Datový typ Accessu

Komentáře

Doporučené postupy

Text

Text, Memo

Datový typ Access Text ukládá alfanumerická data o velikosti až 255 znaků. Datový typ Memo aplikace Access ukládá alfanumerická data o maximálním počtu 65 535 znaků.

Zvolte Memo, abyste se vyhnuli zkrácení dat.

Číslo, Procento, Zlomek, Vědecké

Číslo

Access má jeden datový typ Číslo, který se liší v závislosti na vlastnosti Velikost pole (Byte, Integer, Long Integer, Single, Double, Decimal).

Pokud chcete zabránit chybám při převodu dat, zvolte Double ( Dvojitá ).

Funkce Date

Date

Access i Excel používají k ukládání kalendářních dat stejné pořadové číslo. V Accessu je rozsah dat větší: od -657 434 (1. ledna 100) do 2 958 465 (31. prosince 9999).

Vzhledem k tomu, že Aplikace Access nerozpozná kalendářní systém 1904 (používá se v Excelu pro Macintosh), je třeba data převést buď v Excelu, nebo v Accessu, abyste se vyhnuli nejasnostem.

Další informace najdete v tématech Změna kalendářního systému, formátu nebo dvoumístné interpretace roku a Import nebo propojení dat v excelovém sešitu.

Zvolte Datum.

Funkce Time

Čas

Access i Excel ukládají časové hodnoty pomocí stejného datového typu.

Zvolte Čas, který je obvykle výchozí.

Měna, účetnický

Měna

Datový typ Měna v Accessu ukládá data jako 8 bajtová čí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á -1 pro všechny hodnoty Ano a 0 pro všechny hodnoty Ne, zatímco Excel používá hodnotu 1 pro všechny hodnoty PRAVDA a 0 pro všechny hodnoty NEPRAVDA.

Zvolte Ano/Ne, čímž se automaticky převedou 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.

Zvolte Hypertextový odkaz, jinak access může ve výchozím nastavení používat datový typ Text.

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

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

Snadné automatické připojení dat

Běžným problémem, který uživatelé Excelu mají, je připojení dat se stejnými sloupci do jednoho velkého listu. Můžete mít například řešení pro sledování prostředků, které začalo v Excelu, ale teď se rozšířilo o soubory z mnoha pracovních skupin a oddělení. Tato data mohou být v různých listech a sešitech nebo v textových souborech, které jsou datovými kanály z jiných systémů. Neexistuje žádný příkaz uživatelského rozhraní ani snadný způsob, jak v Excelu připojit podobná data.

Nejlepším řešením je použít Access, kde můžete data snadno importovat a přidávat do jedné tabulky pomocí Průvodce importem tabulky. Kromě toho můžete do jedné tabulky připojit velké množství dat. Můžete uložit operace importu, přidat je jako naplánované úkoly aplikace Microsoft Outlook a dokonce použít makra k automatizaci procesu.

Krok 2: Normalizace dat pomocí Průvodce analyzátorem tabulky

Na první pohled může krokování procesem normalizace dat vypadat jako náročný úkol. Normalizace tabulek v Accessu je naštěstí mnohem jednodušší proces díky Průvodci analyzátorem tabulky.

the table analyzer wizard

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

2. Pomocí tlačítek přejmenujte tabulku, přidejte primární klíč, nastavte existující sloupec jako primární klíč a vraťte zpět poslední akci.

Pomocí tohoto průvodce můžete provést následující akce:

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

  • 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.

  • Automatické vytváření relací pro vynucení referenční integrity pomocí kaskádových aktualizací Kaskádové odstranění se nepřidávají automaticky, aby se zabránilo náhodnému odstranění dat, ale kaskádové odstranění můžete snadno přidat později.

  • Vyhledejte v nových tabulkách redundantní nebo duplicitní data (například stejný zákazník se dvěma různými telefonními čísly) a podle potřeby je aktualizujte.

  • Zálohujte původní tabulku a přejmenujte ji tak, že k názvu připojíte "_OLD". Potom vytvoříte dotaz, který rekonstruuje původní tabulku s původním názvem tabulky, aby všechny existující formuláře nebo sestavy založené na původní tabulce fungovaly s novou strukturou tabulky.

Další informace najdete v tématu Normalizace dat pomocí Analyzátoru tabulek.

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

Po normalizaci dat v Accessu a vytvoření dotazu nebo tabulky, která rekonstruuje původní data, je jednoduché připojit se k datům Accessu z Excelu. Vaše data jsou teď v Accessu jako externí zdroj dat, takže se můžou připojit k sešitu prostřednictvím datového připojení, což je kontejner informací, který slouží k vyhledání, přihlášení a přístupu k externímu zdroji dat. Informace o připojení jsou uloženy v sešitu a můžou být také uloženy v souboru připojení, například v souboru odc (odc) nebo v souboru s názvem zdroje dat (přípona DSN). Po připojení k externím datům můžete také automaticky aktualizovat (nebo aktualizovat) excelový sešit z Accessu pokaždé, když se data aktualizují v Accessu.

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

Získání dat do Accessu

Tato část vás provede následujícími fázemi normalizace dat: Rozdělení hodnot ve sloupcích Salesperson (Prodejce) a Address (Adresa) na jejich nejatomnější části, oddělení souvisejících předmětů do jejich vlastních tabulek, kopírování a vkládání těchto tabulek z Excelu do Accessu, vytváření klíčových relací mezi nově vytvořenými tabulkami Accessu a vytvoření a spuštění jednoduchého dotazu v Accessu pro vrácení informací.

Ukázková data v nenormalizované podobě

Následující list obsahuje neatomické hodnoty ve sloupcích Salesperson (Prodejce) a Address (Adresa). Oba sloupce by měly být rozdělené na dva nebo více samostatných sloupců. Tento list obsahuje také informace o prodejcích, produktech, zákaznících a objednávkách. Tyto informace by také 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žství

Cena

Jméno zákazníka

Address (Adresa)

Telefon

Li, Yale

2349

3/4/09

C-789

3

7,00 Kč

Fourth Coffee

7007 Cornell St Redmond, WA 98199

425-555-0201

Li, Yale

2349

3/4/09

C-795

6

9,75 Kč

Fourth Coffee

7007 Cornell St Redmond, WA 98199

425-555-0201

Adams, Ellen

2350

3/4/09

A-2275

2

16,75 Kč

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Adams, Ellen

2350

3/4/09

F-198

6

5,25 Kč

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Adams, Ellen

2350

3/4/09

B-205

1

4,50 Kč

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Hance, Jim

2351

3/4/09

C-795

6

9,75 Kč

Contoso, Ltd.

2302 Harvard Ave Bellevue, WA 98227

425-555-0222

Hance, Jim

2352

3/5/09

A-2275

2

16,75 Kč

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Hance, Jim

2352

3/5/09

D-4420

3

7,25 Kč

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Koch, Reed

2353

3/7/09

A-2275

6

16,75 Kč

Fourth Coffee

7007 Cornell St Redmond, WA 98199

425-555-0201

Koch, Reed

2353

3/7/09

C-789

5

7,00 Kč

Fourth Coffee

7007 Cornell St Redmond, WA 98199

425-555-0201

Informace v nejmenších částech: atomická data

Při práci s daty v tomto příkladu můžete pomocí příkazu Text do sloupce v Excelu oddělit "atomické" části buňky (například adresu, město, stát a PSČ) do samostatných sloupců.

Následující tabulka obsahuje nové sloupce ve stejném listu po jejich rozdělení, aby se všechny hodnoty staly atomické. Všimněte si, že informace ve sloupci Prodejce byly rozděleny do sloupců Příjmení a Jméno a že informace ve sloupci Adresa byly rozděleny do sloupců Adresa, Město, Stát a PSČ. Tato data jsou v "první normální podobě".

Last Name

First Name

 

Ulice

Město

Stát

PSČ

Li

Yale

2302 Harvard Ave

Pardubice

WA

98227

Florian

Ellen

1025 Columbia Circle

Kirkland

WA

98234

Holý

Jim

2302 Harvard Ave

Pardubice

WA

98227

Koch

Rákos

7007 Cornell St Redmond

Redmond

WA

98199

Rozdělení dat do uspořádaných předmětů v Excelu

Několik následujících tabulek ukázkových dat zobrazuje stejné informace z excelového listu po jeho rozdělení do tabulek pro prodejce, produkty, zákazníky a objednávky. Návrh tabulky není konečný, ale je na správné cestě.

Tabulka Salespersons (Prodejci) obsahuje pouze informace o prodejních zaměstnancích. Všimněte si, že každý záznam má jedinečné ID (ID prodejce). Hodnota ID prodejce se použije v tabulce Orders pro připojení objednávek k prodejcům.

Prodejců

ID prodejce

Last Name

First Name

101

Li

Yale

103

Florian

Ellen

105

Holý

Jim

107

Koch

Rákos

Tabulka Products (Produkty) 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 Podrobnosti objednávky.

Produkty

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 Customers (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 se použije k připojení informací o zákazníkovi k tabulce 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 Columbia Circle

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, prodejcích, 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 potřeba rozdělit do další tabulky, 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 ID zákazníka. Zde zobrazená tabulka ještě nebyla rozdělena do tabulky Podrobnosti objednávky.

Objednávky

ID objednávky

Datum objednávky

ID prodejce

ID zákazníka

Product ID

Množství

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 o objednávce, jako je 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 existuje 9 objednávek, takže dává smysl, že v této tabulce je 9 záznamů. Všimněte si, že tabulka Orders má jedinečné ID (ID objednávky), na které se bude odkazovat z tabulky Podrobnosti objednávky.

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

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 Order Details (Podrobnosti objednávky) neobsahuje žádné sloupce, které vyžadují jedinečné hodnoty (to znamená, že neexistuje žádný primární klíč), takže je v pořádku, když některý nebo všechny sloupce obsahují "redundantní" data. Žádné dva záznamy v této tabulce by však neměly být zcela identické (toto pravidlo platí pro všechny tabulky v databázi). V této tabulce by mělo být 17 záznamů , z nichž každý odpovídá produktu v individuální objednávce. Například v objednávce 2349 tvoří tři produkty C-789 jednu ze dvou částí celé objednávky.

Tabulka Podrobnosti objednávky by proto měla vypadat takto:

Podrobnosti objednávky

ID objednávky

Product ID

Množství

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ž jsou informace o prodejcích, zákaznících, produktech, objednávkách a podrobnostech objednávek v Excelu rozdělené do samostatných témat, můžete tato data zkopírovat přímo do Accessu, kde se stanou tabulkami.

Vytváření relací mezi tabulkami Accessu a spuštění 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ý vrátí 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, které usnadňují zadávání dat a analýzu prodeje.

Potřebujete další pomoc?

Kdykoli se můžete zeptat odborníka z komunity Excel Tech nebo získat podporu v komunitách.

Potřebujete další pomoc?

Chcete další možnosti?

Prozkoumejte výhody předplatného, projděte si školicí kurzy, zjistěte, jak zabezpečit své zařízení a mnohem více.

Komunity vám pomohou klást otázky a odpovídat na ně, poskytovat zpětnou vazbu a vyslechnout odborníky s bohatými znalostmi.