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