Obs!: Microsoft Access stöder inte import av Excel-data med en använd känslighetsetikett. Som en lösning kan du ta bort etiketten innan du importerar och sedan tillämpa etiketten igen efter importen. Mer information finns i Använda känslighetsetiketter för filer och e-post i Office.
Den här artikeln visar hur du flyttar data från Excel till Access och konverterar data till relationstabeller så att du kan använda Microsoft Excel och Access tillsammans. Sammanfattningsvis är Access bäst för att samla in, lagra, fråga och dela data, och Excel är bäst för att beräkna, analysera och visualisera data.
I två artiklar, Använda Access eller Excel för att hantera dina data och de tio främsta orsakerna till att använda Access med Excel, diskuteras vilket program som passar bäst för en viss uppgift och hur du använder Excel och Access tillsammans för att skapa en praktisk lösning.
När du flyttar data från Excel till Access finns det tre grundläggande steg i processen.
Obs!: Mer information om datamodellering och relationer i Access finns i Grundläggande databasdesign.
Steg 1: Importera data från Excel till Access
Att importera data är en åtgärd som kan gå mycket smidigare om du tar dig tid att förbereda och rensa dina data. Att importera data är som att flytta till ett nytt hem. Om du rensar ut och organiserar dina ägodelar innan du flyttar, är det mycket lättare att bosätta sig i ditt nya hem.
Rensa dina data innan du importerar
Innan du importerar data till Access är det en bra idé att göra följande i Excel:
-
Konvertera celler som innehåller icke-atomära data (d.v.s. flera värden i en cell) till flera kolumner. En cell i kolumnen "Kompetens" som innehåller flera färdighetsvärden, t.ex. programmering med "C# ", "VBA-programmering" och "Webbdesign" ska till exempel brytas ut för att avgränsa kolumner som var och en bara innehåller ett färdighetsvärde.
-
Använd kommandot RENSA för att ta bort inledande, avslutande och flera inbäddade blanksteg.
-
Ta bort icke utsskrivande tecken.
-
Hitta och åtgärda stavfel och skiljetecken.
-
Ta bort dubblettrader eller dubblettfält.
-
Kontrollera att kolumner med data inte innehåller blandade format, särskilt tal som formaterats som text eller datum som formaterats som tal.
Mer information finns i följande hjälpavsnitt för Excel:
Obs!: Om dina datarensningsbehov är komplexa, eller om du inte har tid eller resurser för att automatisera processen på egen hand, kan du överväga att använda en tredjepartsleverantör. Om du vill ha mer information kan du söka efter "datarensningsprogram" eller "datakvalitet" efter din favoritsökmotor i webbläsaren.
Välj den bästa datatypen när du importerar
Under importen i Access vill du göra bra val så att du får få (om några) konverteringsfel som kräver manuell åtgärd. I följande tabell sammanfattas hur Excel-talformat och Access-datatyper konverteras när du importerar data från Excel till Access, och innehåller några tips på de bästa datatyperna att välja i guiden Importera kalkylblad.
Excel-talformat |
Datatyper i Access |
Kommentarer |
Bästa praxis |
---|---|---|---|
Text |
Text, PM |
Datatypen Access Text lagrar alfanumeriska data på upp till 255 tecken. Datatypen Pm i Access lagrar alfanumeriska data på upp till 65 535 tecken. |
Välj PM för att undvika att trunkera data. |
Tal, Procent, Bråk, Avancerat |
Tal |
Access har datatypen Tal som varierar beroende på en fältstorleksegenskap (Byte, Heltal, Långt heltal, Enkelt, Dubbelt, Decimal). |
Välj Double för att undvika datakonverteringsfel. |
Datum |
Datum |
Både Access och Excel använder samma serienummer för att lagra datum. I Access är datumintervallet större: från -657 434 (1 januari 100 e.D.) till 2 958 465 (31 december 9999). Eftersom Access inte känner igen 1904-datumsystemet (används i Excel för Macintosh) måste du konvertera datumen antingen i Excel eller Access för att undvika förvirring. Mer information finns i Ändra datumsystemet, formatet eller den tvåsiffriga tolkningen av år och Importera eller länka till data i en Excel-arbetsbok. |
Välj Datum. |
Tid |
Tid |
Både Access och Excel lagrar tidsvärden med samma datatyp. |
Välj Tid, som vanligtvis är standard. |
Valuta, redovisning |
Valuta |
I Access lagrar datatypen Valuta data som 8-byte-tal med precision till fyra decimaler och används för att lagra ekonomiska data och förhindra avrundning av värden. |
Välj Valuta, som vanligtvis är standard. |
boolesk |
Ja/Nej |
I Access används -1 för alla Ja-värden och 0 för alla Nej-värden, medan 1 används för alla SANT-värden och 0 för alla FALSKT-värden. |
Välj Ja/Nej, som automatiskt konverterar underliggande värden. |
Hyperlänk |
Hyperlänk |
En hyperlänk i Excel och Access innehåller en URL eller webbadress som du kan klicka och följa. |
Välj Hyperlänk, annars kan datatypen Text användas som standard i Access. |
När data finns i Access kan du ta bort Excel-data. Glöm inte att säkerhetskopiera den ursprungliga Excel-arbetsboken innan du tar bort den.
Mer information finns i access-hjälpavsnittet Importera eller länka till data i en Excel-arbetsbok.
Lägg automatiskt till data på ett enkelt sätt
Ett vanligt problem som Excel-användare har är att lägga till data med samma kolumner i ett stort kalkylblad. Du kan till exempel ha en lösning för resursspårning som började i Excel men som nu har vuxit till att omfatta filer från många arbetsgrupper och avdelningar. Dessa data kan finnas i olika kalkylblad och arbetsböcker eller i textfiler som är datafeeds från andra system. Det finns inget användargränssnittskommando eller ett enkelt sätt att lägga till liknande data i Excel.
Den bästa lösningen är att använda Access, där du enkelt kan importera och lägga till data i en tabell med hjälp av guiden Importera kalkylblad. Dessutom kan du lägga till mycket data i en tabell. Du kan spara importåtgärderna, lägga till dem som schemalagda Microsoft Outlook-uppgifter och även använda makron för att automatisera processen.
Steg 2: Normalisera data med hjälp av tabellanalysguiden
Vid första anblicken kan det verka som en skrämmande uppgift att gå igenom processen att normalisera dina data. Som tur är är det mycket enklare att normalisera tabeller i Access, tack vare tabellanalysguiden.
1. Dra markerade kolumner till en ny tabell och skapa relationer automatiskt
2. Använd knappkommandon för att byta namn på en tabell, lägga till en primärnyckel, göra en befintlig kolumn till primärnyckel och ångra den senaste åtgärden
Du kan använda den här guiden för att göra följande:
-
Konvertera en tabell till en uppsättning mindre tabeller och skapa automatiskt en primär- och sekundärnyckelrelation mellan tabellerna.
-
Lägg till en primärnyckel i ett befintligt fält som innehåller unika värden eller skapa ett nytt ID-fält som använder datatypen Räknare.
-
Skapa relationer automatiskt för att tillämpa referensintegritet med sammanhängande uppdateringar. Sammanhängande borttagningar läggs inte till automatiskt för att förhindra att data tas bort av misstag, men du kan enkelt lägga till sammanhängande borttagningar senare.
-
Sök i nya tabeller efter redundanta eller duplicerade data (till exempel samma kund med två olika telefonnummer) och uppdatera detta efter behov.
-
Säkerhetskopiera den ursprungliga tabellen och byt namn på den genom att lägga till "_OLD" i dess namn. Sedan skapar du en fråga som återskapar den ursprungliga tabellen, med det ursprungliga tabellnamnet så att alla befintliga formulär eller rapporter baserade på den ursprungliga tabellen fungerar med den nya tabellstrukturen.
Mer information finns i Normalisera data med hjälp av Tabellanalys.
Steg 3: Ansluta till Access-data från Excel
När data har normaliserats i Access och en fråga eller tabell har skapats som återskapar de ursprungliga data, är det enkelt att ansluta till Access-data från Excel. Dina data finns nu i Access som en extern datakälla och kan därför anslutas till arbetsboken via en dataanslutning, som är en behållare med information som används för att hitta, logga in på och komma åt den externa datakällan. Anslutningsinformationen lagras i arbetsboken och kan också lagras i en anslutningsfil, till exempel en ODC-fil (Office Data Connection) (ODC-filnamnstillägget.odc) eller en fil med filnamnstillägget Datakälla (.dsn). När du har anslutit till externa data kan du också automatiskt uppdatera (eller uppdatera) Excel-arbetsboken från Access när data uppdateras i Access.
Mer information finns i Importera data från externa datakällor (Power Query).
Hämta dina data till Access
I det här avsnittet går du igenom följande faser av normalisering av data: Dela upp värden i kolumnerna Försäljare och Adress i de mest atomära delarna, dela upp relaterade ämnen i sina egna tabeller, kopiera och klistra in tabellerna från Excel i Access, skapa viktiga relationer mellan de nya Access-tabellerna och skapa och köra en enkel fråga i Access för att returnera information.
Exempeldata i icke-normaliserad form
Följande kalkylblad innehåller icke-atomvärden i kolumnen Försäljare och kolumnen Adress. Båda kolumnerna ska delas upp i två eller flera separata kolumner. Det här kalkylbladet innehåller också information om säljare, produkter, kunder och order. Informationen bör också delas upp ytterligare, efter ämne, i separata tabeller.
Säljare |
Order ID |
Orderdatum |
Produkt-ID |
Antal |
Pris |
Kundnamn |
Adress |
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 |
Adams, Ellen |
2350 |
3/4/09 |
A-2275 |
2 |
$16.75 |
Adventure Works |
1025 Columbia cirklar Kirkland, WA 98234 |
425-555-0185 |
Adams, Ellen |
2350 |
3/4/09 |
F-198 |
6 |
$5.25 |
Adventure Works |
1025 Columbia cirklar Kirkland, WA 98234 |
425-555-0185 |
Adams, Ellen |
2350 |
3/4/09 |
B-205 |
1 |
$4.50 |
Adventure Works |
1025 Columbia cirklar 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 cirklar Kirkland, WA 98234 |
425-555-0185 |
Hance, Jim |
2352 |
3/5/09 |
D-4420 |
3 |
$7.25 |
Adventure Works |
1025 Columbia cirklar 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 |
Information i dess minsta delar: atomdata
När du arbetar med data i det här exemplet kan du använda kommandot Text till kolumn i Excel för att separera "atomiska" delar av en cell (till exempel gatuadress, ort, region och postnummer) i separata kolumner.
I följande tabell visas de nya kolumnerna i samma kalkylblad när de har delats för att göra alla värden atomära. Observera att informationen i kolumnen Försäljare har delats upp i kolumnerna Efternamn och Förnamn och att informationen i kolumnen Adress har delats upp i kolumnerna Gatuadress, Ort, Delstat och Postnummer. Dessa data är i "första normalform".
Efternamn |
Förnamn |
|
Gatuadress |
Ort |
Region |
Postnummer |
---|---|---|---|---|---|---|
Li |
Yale |
2302 Harvard Ave |
Bellevue |
WA |
98227 |
|
Adams |
Ellen |
1025 Columbia cirkel |
Kirkland |
WA |
98234 |
|
Hance |
Jim |
2302 Harvard Ave |
Bellevue |
WA |
98227 |
|
Koch |
Vass |
7007 Cornell St Redmond |
Redmond |
WA |
98199 |
Dela upp data i organiserade ämnen i Excel
De flera tabellerna med exempeldata som följer visar samma information från Excel-kalkylbladet när det har delats upp i tabeller för säljare, produkter, kunder och order. Tabelldesignen är inte slutgiltig, men den är på rätt spår.
Tabellen Säljare innehåller endast information om säljpersonal. Observera att varje post har ett unikt ID (Försäljare-ID). Värdet Försäljare-ID används i tabellen Order för att koppla order till säljare.
Säljare |
||
---|---|---|
Försäljare-ID |
Efternamn |
Förnamn |
101 |
Li |
Yale |
103 |
Adams |
Ellen |
105 |
Hance |
Jim |
107 |
Koch |
Vass |
Tabellen Produkter innehåller endast information om produkter. Observera att varje post har ett unikt ID (Produkt-ID). Produkt-ID-värdet används för att koppla produktinformation till tabellen Orderdetaljer.
Produkter |
|
---|---|
Produkt-ID |
Pris |
A-2275 |
16.75 |
B-205 |
4.50 |
C-789 |
7.00 |
C-795 |
9.75 |
D-4420 |
7.25 |
F-198 |
5,25 |
Tabellen Kunder innehåller endast information om kunder. Observera att varje post har ett unikt ID (kund-ID). Värdet kund-ID används för att koppla kundinformation till tabellen Order.
Kunder |
||||||
---|---|---|---|---|---|---|
Kundnummer |
Namn |
Gatuadress |
Ort |
Region |
Postnummer |
Telefon |
1001 |
Contoso, Ltd. |
2302 Harvard Ave |
Bellevue |
WA |
98227 |
425-555-0222 |
1003 |
Adventure Works |
1025 Columbia cirkel |
Kirkland |
WA |
98234 |
425-555-0185 |
1 005 |
Fourth Coffee |
7007 Cornell St |
Redmond |
WA |
98199 |
425-555-0201 |
Tabellen Order innehåller information om order, säljare, kunder och produkter. Observera att varje post har ett unikt ID (Order-ID). En del av informationen i den här tabellen måste delas upp i en ytterligare tabell som innehåller orderinformation så att tabellen Order bara innehåller fyra kolumner – det unika order-ID:t, orderdatumet, försäljarens ID och kund-ID. Tabellen som visas här har ännu inte delats upp i tabellen Orderdetaljer.
Beställningar |
|||||
---|---|---|---|---|---|
Order ID |
Orderdatum |
Försäljare-ID |
Kundnummer |
Produkt-ID |
Antal |
2349 |
3/4/09 |
101 |
1 005 |
C-789 |
3 |
2349 |
3/4/09 |
101 |
1 005 |
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 |
1 005 |
A-2275 |
6 |
2353 |
3/7/09 |
107 |
1 005 |
C-789 |
5 |
Orderinformation, till exempel produkt-ID och antal, flyttas från tabellen Order och lagras i en tabell med namnet Orderinformation. Tänk på att det finns 9 order, så det är vettigt att det finns 9 poster i den här tabellen. Observera att tabellen Order har ett unikt ID (Order-ID), som refereras till från tabellen Orderdetaljer.
Den slutliga utformningen av tabellen Order bör se ut så här:
Beställningar |
|||
---|---|---|---|
Order ID |
Orderdatum |
Försäljare-ID |
Kundnummer |
2349 |
3/4/09 |
101 |
1 005 |
2350 |
3/4/09 |
103 |
1003 |
2351 |
3/4/09 |
105 |
1001 |
2352 |
3/5/09 |
105 |
1003 |
2353 |
3/7/09 |
107 |
1 005 |
Tabellen Orderdetaljer innehåller inga kolumner som kräver unika värden (det vill säga det finns ingen primärnyckel), så det är okej att alla kolumner innehåller "redundanta" data. Men inga två poster i den här tabellen ska vara helt identiska (den här regeln gäller för alla tabeller i en databas). I den här tabellen bör det finnas 17 poster – var och en motsvarar en produkt i en enskild order. I order 2349 består exempelvis tre C-789-produkter av en av de två delarna av hela beställningen.
Tabellen Orderdetaljer bör därför se ut så här:
Orderinformation |
||
---|---|---|
Order ID |
Produkt-ID |
Antal |
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 |
Kopiera och klistra in data från Excel i Access
Nu när informationen om säljare, kunder, produkter, order och orderinformation har delats upp i separata ämnen i Excel kan du kopiera dessa data direkt till Access, där de blir tabeller.
Skapa relationer mellan Access-tabellerna och köra en fråga
När du har flyttat data till Access kan du skapa relationer mellan tabeller och sedan skapa frågor för att returnera information om olika ämnen. Du kan till exempel skapa en fråga som returnerar Order-ID och namnen på säljare för order mellan 09-03-05 och 08-03.08.
Dessutom kan du skapa formulär och rapporter för att underlätta datainmatning och försäljningsanalys.
Behöver du mer hjälp?
Du kan alltid fråga en expert i Excel Tech Community eller få support i Communities.