Přesunutí dat z Excelu do Accessu

Tento článek vysvětluje, 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 společně. Aplikace Access je nejvhodnější pro sběr, ukládání, zadávání dotazů a sdílení dat a aplikace Excel je nejlepší pro výpočet, analýzu a vizualizaci dat.

Dva články, Správa dat pomocí Accessu nebo Excelu a 10 hlavních důvodů, proč používat Access s Excelem, probírají, která aplikace je pro konkrétní úkol nejvhodnější a jak pomocí Excelu a Accessu společně vytvořit praktické řešení.

Když přesunete data z Excelu do Accessu, tento proces se skládá ze tří základních kroků.

Krok 1: Import dat z Excelu do Accessu

Import dat je operace, která může proběhnout mnohem hladčeji, pokud si na přípravu a vyčištění dat vyhradíte trochu času. Import dat je jako stěhování se do nového domova. Pokud si před stěhováním uklidíte a uspořádáte svůj majetek, je zabydlování v novém domově mnohem snazší.

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

Před importem dat do aplikace Access je vhodné v aplikaci Excel provést následující kroky:

• Převeďte buňky, které obsahují jiná než atomická 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í, jako je třeba "Programování v C#", "Programování v jazyce VBA" a "Návrh webu", by měla být rozdělená do samostatných sloupců, které obsahují pouze jednu hodnotu dovednosti.
• Pomocí příkazu PROČISTIT můžete odebrat úvodní a koncové mezery a vícenásobné vložené mezery.
• Odeberte netisknutelné znaky.
• Najít a opravit pravopisné a interpunkční chyby.
• Odebrání duplicitních řádků nebo duplicitních polí
• Zajistěte, aby sloupce dat neobsahovaly smíšené formáty, zejména čísla formátovaná jako text nebo data formátovaná jako čísla.

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

• Deset nejlepších způsobů čištění dat
• Filtrování jedinečných hodnot nebo odebrání duplicitních hodnot
• Převod čísel uložených jako text na čísla
• Převedení dat uložených jako text na kalendářní data

Výběr nejlepšího datového typu při importu

Během importu v Accessu je vhodné zvolit několik chyb převodu, které budou vyžadovat ruční zásah (pokud vůbec nějaké). Následující tabulka shrnuje převody číselných formátů Excelu a datových typů Accessu při importu dat z Excelu do Accessu a nabízí několik tipů na nejlepší datové typy, které si můžete vybrat v Průvodci importem z tabulkového kalkulátoru.

Formát čísel v Excelu	Datový typ Accessu	Komentáře	Doporučené postupy
Text	Text, poznámka	Datový typ Access Text ukládá alfanumerická data o délce maximálně 255 znaků. Datový typ Access Memo ukládá alfanumerická data o délce maximálně 65 535 znaků.	Pokud chcete předejít zkrácení dat, zvolte Memo .
Čí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 se chcete vyhnout chybám při převodu dat, zvolte Double .
Funkce Date	Date	Access i Excel používají k ukládání kalendářních dat stejná pořadová data. V Accessu je rozsah dat větší: od -657 434 (1. ledna 100 n. l.) do 2 958 465 (31. prosince 9999 n. l.). Protože Access nerozpozná kalendářní systém 1904 (používaný v Excelu pro Macintosh), je potřeba kalendářní data převést v Excelu nebo Accessu, aby nedošlo ke zmatkům. Další informace najdete v tématech Změna kalendářního systému, formátu data nebo interpretace roků pomocí dvou číslic a Import nebo propojení dat z excelového 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, účetnictví	Měna	Datový typ Měna v Accessu ukládá data jako osmibajtová čísla s přesností na čtyři desetinná místa, používá se k ukládání finančních dat a k zabránění zaokrouhlování hodnot.	Zvolte možnost Měna, která je obvykle výchozí.
Logická hodnota	Ano/Ne	Access používá číslo -1 pro všechny hodnoty Ano a 0 pro všechny hodnoty Ne, zatímco Excel používá 1 pro všechny hodnoty PRAVDA a 0 pro všechny hodnoty NEPRAVDA.	Zvolte Ano/Ne, čímž se podkladové hodnoty automaticky převedou.
Hypertextový odkaz	Hypertextový odkaz	Hypertextový odkaz v Excelu a Accessu obsahuje adresu URL nebo webovou adresu, na kterou můžete kliknout a přejít.	Zvolte Hypertextový odkaz, jinak může Access ve výchozím nastavení používat datový typ Text.

Jakmile jsou data v Accessu, můžete je odstranit. Před odstraněním původního excelového sešitu si nezapomeňte nejdřív udělat jeho zálohu.

Další informace najdete v tématu nápovědy Accessu Import a propojení dat z excelového sešitu.

Automatické připojení dat – jednoduchý způsob

Častým problémem, se kterým se uživatelé Excelu setkávají, je připojení dat se stejnými sloupci do jednoho velkého listu. Můžete mít třeba řešení pro sledování majetku, které začalo v Excelu, ale teď se rozrostlo a 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, které slouží jako datové kanály z jiných systémů. V Excelu neexistuje žádné uživatelské rozhraní, příkaz ani snadný způsob, jak podobná data připojit.

Nejvhodnějším řešením je použít Access, ve kterém můžete pomocí Průvodce importem z tabulkového kalkulátoru snadno importovat data a přidat je do jedné tabulky. Navíc můžete do jedné tabulky připojit spoustu dat. Operace importu můžete uložit, přidat jako naplánované úkoly Microsoft Outlooku a dokonce použít makra k automatizaci procesu.

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

Na první pohled se může zdát, že projít procesem normalizace dat může být náročný úkol. Normalizace tabulek v Accessu je naštěstí proces, který je díky průvodci analýzou tabulky mnohem jednodušší.

1. Přetažení vybraných sloupců do nové tabulky a automatické vytvoření relací

2. Použití příkazů tlačítek k přejmenování tabulky, přidání primárního klíče, nastavení existujícího sloupce jako primárního klíče nebo vrácení poslední akce zpět

Pomocí tohoto průvodce můžete provádět následující akce:

• Převeďte tabulku na sadu menších tabulek a automaticky mezi nimi 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 datového typu Automatické číslo.
• Automaticky vytvářet relace k vynucení referenční integrity s kaskádovými aktualizacemi. Kaskádové odstranění se nepřidává automaticky, aby se předešlo nechtěnému odstranění dat, ale kaskádové odstranění můžete později snadno přidat.
• Vyhledejte v nových tabulkách nadbytečná nebo duplicitní data (například stejného zákazníka se dvěma různými telefonními čísly) a podle potřeby je aktualizujte.
• Zazálohujte původní tabulku a přejmenujte ji tak, že do názvu přidáte "_OLD". Potom vytvoříte dotaz, který rekonstruuje původní tabulku s původním názvem tak, 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í funkce Analýza tabulky.

Krok 3: Připojení k datům aplikace Access z aplikace Excel

Po normalizaci dat v Accessu a vytvoření dotazu nebo tabulky, která rekonstruuje původní data, zbývá jednoduché připojení k datům Accessu z Excelu. Data jsou teď v Accessu jako externí zdroj dat, takže je možné je připojit k sešitu pomocí datového připojení, což je kontejner informací sloužící k vyhledání, přihlášení a získání přístupu k externímu zdroji dat. Informace o připojení jsou uložené v sešitu a můžou být uložené taky v souboru připojení, například v souboru ODC (Office Data Connection) (s příponou názvu souboru) nebo v souboru názvu zdroje dat (s příponou DSN). Po připojení k externím datům můžete také automaticky aktualizovat (neboli aktualizovat) excelový sešit z Accessu při každé aktualizaci dat v Accessu.

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

Přenesení dat do Accessu

Tato část vás provede následujícími fázemi normalizace dat: rozdělení hodnot ve sloupci Prodejce a Adresa na dvě části, rozdělení souvisejících předmětů do samostatných tabulek, zkopírování a vložení těchto tabulek z Excelu do Accessu, vytvoření klíčových relací mezi nově vytvořenými tabulkami v Accessu a vytvoření a spuštění jednoduchého dotazu v Accessu pro vrácení informací.

Ukázková data v nenormalizovaném formátu

Následující list obsahuje ve sloupcích Prodejce a Adresa jiné než atomické hodnoty. Oba sloupce by měly být rozděleny do dvou 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ětů 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 dolarů	Fourth Coffee	7007 Cornell St Redmond, WA 98199	425-555-0201
Adams, Ellen	2350	3/4/09	A-2275	2	16,75 dolarů	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 dolarů	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Hance, Jim	2351	3/4/09	C-795	6	9,75 dolarů	Contoso, Ltd.	2302 Harvard Ave Bellevue, WA 98227	425-555-0222
Hance, Jim	2352	3/5/09	A-2275	2	16,75 dolarů	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Hance, Jim	2352	3/5/09	D-4420	3	7,25 dolarů	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Koch, Reed	2353	3/7/09	A-2275	6	16,75 dolarů	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: atomová data

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

Následující tabulka ukazuje nové sloupce ve stejném listu po jejich rozdělení, aby byly všechny hodnoty atomické. Všimněte si, že informace ve sloupci Prodejce jsou rozdělené na sloupce Příjmení a Křestní jméno a informace ve sloupci Adresa jsou rozdělené na sloupce Ulice, Město, Stát a PSČ. Tato data jsou v "prvním normálním formátu".

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 předmětů uspořádaných v Excelu

V následujících několika tabulkách s ukázkovými daty se zobrazí stejné informace jako v excelovém listu po jeho rozdělení na tabulky prodejců, produktů, zákazníků a objednávek. Návrh stolu není konečný, ale je na správné cestě.

Tabulka Prodejci obsahuje pouze informace o prodejcích. Každý záznam má jedinečné ID (ID prodejce). Hodnota ID prodejce se použije v tabulce Objednávky k připojení objednávek k prodejcům.

Prodejci
ID prodejce	Last Name	First Name
101	Li	Yale
103	Florian	Ellen
105	Holý	Jim
107	Koch	Rákos

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

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 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	Název	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 nutné rozdělit do další tabulky, která bude obsahovat podrobnosti o objednávkách, aby tabulka Objednávky obsahovala pouze čtyři sloupce – jedinečné ID objednávky, datum objednávky, ID prodejce a ID zákazníka. Tabulka zde zobrazená ještě není rozdělena na tabulku Rozpis objednávek.

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 objednávky, jako je ID produktu a množství, se přesunou z tabulky Objednávky a uloží se do tabulky s názvem Rozpis objednávek. Nezapomeňte, že je tam 9 objednávek, takže by bylo logické, že v této tabulce je 9 záznamů. Všimněte si, že 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:

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 Podrobnosti objednávek neobsahuje žádné sloupce, které by vyžadovaly jedinečné hodnoty (neexistuje tedy primární klíč), takže nevadí, když kterýkoli nebo všechny sloupce obsahují "redundantní" data. Žádné dva záznamy v této tabulce by však neměly být zcela shodné (toto pravidlo platí pro libovolnou tabulku v databázi). V této tabulce by mělo být 17 záznamů, z nichž každý odpovídá jednomu produktu v samostatné objednávce. Například v objednávce 2349 tvoří tři výrobky C-789 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ž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

Informace o prodejcích, zákaznících, produktech, objednávkách a podrobnostech objednávek jsou teď v Excelu rozdělené do samostatných předmětů, a proto můžete data zkopírovat přímo do Accessu, kde se z nich stanou tabulky.

Vytvoření relací mezi tabulkami aplikace Access a spuštění dotazu

Po přesunutí dat do Accessu můžete vytvářet relace mezi tabulkami a potom vytvářet dotazy, které budou vracet 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ů u objednávek zadaných mezi 3. 5. 2009 a 3. 8. 2009.

Kromě toho můžete vytvářet formuláře a sestavy, které usnadňují zadávání dat a analýzy prodejů.

Potřebujete další pomoc?

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