Opmerking: Microsoft Access biedt geen ondersteuning voor het importeren van Excel-gegevens met een toegepast vertrouwelijkheidslabel. Als tijdelijke oplossing kunt u het label verwijderen voordat u het importeert en het label vervolgens opnieuw toepassen na het importeren. Zie Vertrouwelijkheidslabels toepassen op uw bestanden en e-mail in Office voor meer informatie.
In dit artikel wordt beschreven hoe u uw gegevens verplaatst van Excel naar Access en uw gegevens converteert naar relationele tabellen, zodat u Microsoft Excel en Access samen kunt gebruiken. Samenvattend: Access is het beste voor het vastleggen, opslaan, opvragen en delen van gegevens en Excel is het beste voor het berekenen, analyseren en visualiseren van gegevens.
In twee artikelen, Access of Excel gebruiken om uw gegevens te beheren en 10 belangrijkste redenen om Access met Excel te gebruiken, wordt besproken welk programma het meest geschikt is voor een bepaalde taak en hoe u Excel en Access samen kunt gebruiken om een praktische oplossing te maken.
Wanneer u gegevens van Excel naar Access verplaatst, zijn er drie basisstappen voor het proces.
Opmerking: Zie Basisbeginselen van databaseontwerp voor meer informatie over gegevensmodellering en relaties in Access.
Stap 1: gegevens uit Excel importeren in Access
Het importeren van gegevens is een bewerking die veel soepeler kan verlopen als u even de tijd neemt om uw gegevens voor te bereiden en op te schonen. Het importeren van gegevens is als verhuizen naar een nieuw huis. Als je je bezittingen opruimt en ordent voordat je gaat verhuizen, is het veel gemakkelijker om je te vestigen in je nieuwe huis.
Uw gegevens opschonen voordat u importeert
Voordat u gegevens in Access importeert, is het een goed idee om in Excel het volgende te doen:
-
Cellen die niet-atomische gegevens bevatten (meerdere waarden in één cel) converteren naar meerdere kolommen. Een cel in een kolom 'Vaardigheden' die meerdere vaardigheidswaarden bevat, zoals 'C#-programmering', 'VBA-programmering' en 'Webontwerp' moet worden uitgesplitst om kolommen te scheiden die elk slechts één vaardigheidswaarde bevatten.
-
Gebruik de opdracht TRIM om voorloop-, volg- en meerdere ingesloten spaties te verwijderen.
-
Niet-afdrukbare tekens verwijderen.
-
Spelling- en interpunctiefouten zoeken en oplossen.
-
Dubbele rijen of dubbele velden verwijderen.
-
Zorg ervoor dat kolommen met gegevens geen gemengde notaties bevatten, met name getallen die zijn opgemaakt als tekst of datums die zijn opgemaakt als getallen.
Zie de volgende Help-onderwerpen in Excel voor meer informatie:
Opmerking: Als uw behoeften voor het opschonen van gegevens complex zijn, of als u niet de tijd of resources hebt om het proces zelf te automatiseren, kunt u overwegen een externe leverancier te gebruiken. Zoek voor meer informatie naar 'software voor het opschonen van gegevens' of 'gegevenskwaliteit' door uw favoriete zoekmachine in uw webbrowser.
Kies het beste gegevenstype bij het importeren
Tijdens de importbewerking in Access wilt u goede keuzes maken, zodat u weinig (indien aanwezig) conversiefouten ontvangt waarvoor handmatige interventie vereist is. De volgende tabel bevat een overzicht van de wijze waarop excel-getalnotaties en Access-gegevenstypen worden geconverteerd wanneer u gegevens uit Excel importeert in Access, en bevat enkele tips over de beste gegevenstypen die u kunt kiezen in de wizard Spreadsheet importeren.
Excel-getalnotatie |
Gegevenstype in Access |
Opmerkingen |
Aanbevolen procedures |
---|---|---|---|
Text |
Tekst, memo |
Het gegevenstype Access-tekst bevat alfanumerieke gegevens van maximaal 255 tekens. Het gegevenstype Access Memo slaat alfanumerieke gegevens op tot 65.535 tekens. |
Kies Memo om te voorkomen dat gegevens worden afgekapt. |
Getal, Percentage, Breuk, Wetenschappelijk |
Getal |
Access heeft één gegevenstype Getal dat varieert op basis van de eigenschap Veldgrootte (Byte, Integer, Lang geheel getal, Enkelvoudig, Dubbel, Decimaal). |
Kies Dubbel om eventuele gegevensconversiefouten te voorkomen. |
Datum |
Datum |
Access en Excel gebruiken beide hetzelfde seriële datumnummer om datums op te slaan. In Access is het datumbereik groter: van -657.434 (1 januari 100 na Chr.) tot 2.958.465 (31 december 9999 na Chr.). Omdat het datumsysteem 1904 (gebruikt in Excel voor de Macintosh) niet wordt herkend, moet u de datums converteren in Excel of In Access om verwarring te voorkomen. Zie Het datumsysteem, de notatie of tweecijferige jaarinterpretatie wijzigen en Gegevens importeren of koppelen aan gegevens in een Excel-werkmap voor meer informatie. |
Kies Datum. |
Time |
Tijd |
In Access en Excel worden tijdwaarden opgeslagen met hetzelfde gegevenstype. |
Kies Tijd. Dit is meestal de standaardinstelling. |
Valuta, Boekhouding |
Valuta |
In Access slaat het gegevenstype Valuta gegevens op als getallen van 8 bytes met precisie tot vier decimalen en wordt gebruikt om financiële gegevens op te slaan en het afronden van waarden te voorkomen. |
Kies Valuta. Dit is meestal de standaardinstelling. |
Booleaans |
Ja/Nee |
Access gebruikt -1 voor alle Ja-waarden en 0 voor alle Nee-waarden, terwijl in Excel 1 wordt gebruikt voor alle WAAR-waarden en 0 voor alle ONWAAR-waarden. |
Kies Ja/Nee, waarmee onderliggende waarden automatisch worden geconverteerd. |
Hyperlink |
Hyperlink |
Een hyperlink in Excel en Access bevat een URL of webadres waarop u kunt klikken en volgen. |
Kies Hyperlink, anders wordt standaard het gegevenstype Tekst gebruikt. |
Zodra de gegevens in Access zijn, kunt u de Excel-gegevens verwijderen. Vergeet niet eerst een back-up te maken van de oorspronkelijke Excel-werkmap voordat u deze verwijdert.
Zie het Help-onderwerp Van Access gegevens importeren of koppelen aan gegevens in een Excel-werkmap voor meer informatie.
Gegevens automatisch toevoegen op een eenvoudige manier
Een veelvoorkomend probleem met Excel-gebruikers is het toevoegen van gegevens met dezelfde kolommen aan één groot werkblad. U hebt bijvoorbeeld een oplossing voor het bijhouden van activa die is begonnen in Excel, maar nu is uitgebreid met bestanden van veel werkgroepen en afdelingen. Deze gegevens kunnen zich in verschillende werkbladen en werkmappen bevinden, of in tekstbestanden die gegevensfeeds van andere systemen zijn. Er is geen opdracht voor de gebruikersinterface of een eenvoudige manier om vergelijkbare gegevens toe te voegen in Excel.
De beste oplossing is om Access te gebruiken, waar u eenvoudig gegevens kunt importeren en toevoegen aan één tabel met behulp van de wizard Spreadsheet importeren. Bovendien kunt u veel gegevens toevoegen aan één tabel. U kunt de importbewerkingen opslaan, toevoegen als geplande Microsoft Outlook-taken en zelfs macro's gebruiken om het proces te automatiseren.
Stap 2: Gegevens normaliseren met behulp van de wizard Table Analyzer
Op het eerste gezicht kan het een lastige taak lijken om het proces van het normaliseren van uw gegevens te doorlopen. Gelukkig is het normaliseren van tabellen in Access een proces dat veel eenvoudiger is, dankzij de wizard Tabelanalyse.
1. Sleep geselecteerde kolommen naar een nieuwe tabel en maak automatisch relaties
2. Knopopdrachten gebruiken om de naam van een tabel te wijzigen, een primaire sleutel toe te voegen, van een bestaande kolom een primaire sleutel te maken en de laatste actie ongedaan te maken
U kunt deze wizard gebruiken om het volgende te doen:
-
Converteer een tabel naar een set kleinere tabellen en maak automatisch een primaire en refererende sleutelrelatie tussen de tabellen.
-
Voeg een primaire sleutel toe aan een bestaand veld dat unieke waarden bevat of maak een nieuw id-veld waarin het gegevenstype AutoNummering wordt gebruikt.
-
Automatisch relaties maken om referentiële integriteit af te dwingen met trapsgewijze updates. Trapsgewijze verwijderingen worden niet automatisch toegevoegd om te voorkomen dat gegevens per ongeluk worden verwijderd, maar u kunt later eenvoudig trapsgewijs verwijderen toevoegen.
-
Zoek in nieuwe tabellen naar redundante of dubbele gegevens (zoals dezelfde klant met twee verschillende telefoonnummers) en werk deze naar wens bij.
-
Maak een back-up van de oorspronkelijke tabel en wijzig de naam ervan door '_OLD' toe te voegen aan de naam. Vervolgens maakt u een query waarmee de oorspronkelijke tabel wordt gereconstrueerd, met de oorspronkelijke tabelnaam, zodat bestaande formulieren of rapporten op basis van de oorspronkelijke tabel werken met de nieuwe tabelstructuur.
Zie Uw gegevens normaliseren met table analyzer voor meer informatie.
Stap 3: Verbinding maken met Access-gegevens vanuit Excel
Nadat de gegevens in Access zijn genormaliseerd en er een query of tabel is gemaakt waarmee de oorspronkelijke gegevens worden gereconstrueerd, is het eenvoudig om verbinding te maken met de Access-gegevens vanuit Excel. Uw gegevens bevinden zich nu in Access als een externe gegevensbron en kunnen dus worden verbonden met de werkmap via een gegevensverbinding. Dit is een container met gegevens die wordt gebruikt om de externe gegevensbron te zoeken, aan te melden en er toegang toe te krijgen. Verbindingsgegevens worden opgeslagen in de werkmap en kunnen ook worden opgeslagen in een verbindingsbestand, zoals een ODC-bestand (Office Data Connection) (odc-bestandsnaamextensie) of een gegevensbronnaambestand (.dsn-extensie). Nadat u verbinding hebt gemaakt met externe gegevens, kunt u uw Excel-werkmap ook automatisch vernieuwen (of bijwerken) vanuit Access wanneer de gegevens worden bijgewerkt in Access.
Zie Gegevens importeren uit externe gegevensbronnen (Power Query) voor meer informatie.
Uw gegevens ophalen in Access
In deze sectie wordt u begeleid bij de volgende fasen van het normaliseren van uw gegevens: waarden in de kolommen Verkoper en Adres in hun meest atomische delen breken, gerelateerde onderwerpen in hun eigen tabellen scheiden, deze tabellen kopiëren en plakken vanuit Excel in Access, belangrijke relaties maken tussen de zojuist gemaakte Access-tabellen en een eenvoudige query maken en uitvoeren in Access om informatie te retourneren.
Voorbeeldgegevens in niet-genormaliseerde vorm
Het volgende werkblad bevat niet-atomische waarden in de kolom Verkoper en de kolom Adres. Beide kolommen moeten worden gesplitst in twee of meer afzonderlijke kolommen. Dit werkblad bevat ook informatie over verkopers, producten, klanten en orders. Deze informatie moet ook verder worden gesplitst, per onderwerp, in afzonderlijke tabellen.
Verkoper |
Order-id |
Orderdatum |
Product-id |
Aantal |
Prijs |
Naam klant |
Address |
Telefoon |
---|---|---|---|---|---|---|---|---|
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 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 |
Adventure Works |
1025 Columbia Circle 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 Circle Kirkland, WA 98234 |
425-555-0185 |
Hance, Jim |
2352 |
3/5/09 |
D-4420 |
3 |
$7.25 |
Adventure Works |
1025 Columbia Circle 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 |
Informatie in de kleinste delen: atomische gegevens
Als u werkt met de gegevens in dit voorbeeld, kunt u de opdracht Tekst naar kolom in Excel gebruiken om de 'atomische' delen van een cel (zoals adres, plaats, provincie en postcode) te scheiden in afzonderlijke kolommen.
In de volgende tabel ziet u de nieuwe kolommen in hetzelfde werkblad nadat ze zijn gesplitst om alle waarden atomisch te maken. Houd er rekening mee dat de gegevens in de kolom Verkoper zijn gesplitst in de kolommen Achternaam en Voornaam en dat de gegevens in de kolom Adres zijn gesplitst in de kolommen Adres, Plaats, Provincie en Postcode. Deze gegevens zijn in 'eerste normale vorm'.
Achternaam |
Voornaam |
|
Straat |
Plaats |
Staat |
Postcode |
---|---|---|---|---|---|---|
Li |
Yale |
2302 Harvard Ave |
Bellevue |
WA |
98227 |
|
Adams |
Ellen |
1025 Columbia Circle |
Haarlem |
WA |
98234 |
|
Hance |
Jimmy |
2302 Harvard Ave |
Bellevue |
WA |
98227 |
|
Koch |
Riet |
7007 Cornell St Redmond |
Redmond |
WA |
98199 |
Gegevens opsplitsen in georganiseerde onderwerpen in Excel
De verschillende tabellen met voorbeeldgegevens die volgen, tonen dezelfde informatie uit het Excel-werkblad nadat deze zijn opgesplitst in tabellen voor verkopers, producten, klanten en orders. Het tabelontwerp is niet definitief, maar het is op de goede weg.
De tabel Salespersons bevat alleen informatie over verkoopmedewerkers. Elke record heeft een unieke id (verkoper-id). De waarde verkoopmedewerker-id wordt gebruikt in de tabel Orders om orders te verbinden met verkopers.
Verkopers |
||
---|---|---|
Verkoper-id |
Achternaam |
Voornaam |
101 |
Li |
Yale |
103 |
Adams |
Ellen |
105 |
Hance |
Jimmy |
107 |
Koch |
Riet |
De tabel Producten bevat alleen informatie over producten. Houd er rekening mee dat elke record een unieke id (product-id) heeft. De waarde product-id wordt gebruikt om productgegevens te koppelen aan de tabel Ordergegevens.
Producten |
|
---|---|
Product-id |
Prijs |
A-2275 |
16.75 |
B-205 |
4.50 |
C-789 |
7,00 |
C-795 |
9.75 |
D-4420 |
7.25 |
F-198 |
5,25 |
De tabel Klanten bevat alleen informatie over klanten. Houd er rekening mee dat elke record een unieke id (klant-id) heeft. De waarde klant-id wordt gebruikt om klantgegevens te koppelen aan de tabel Orders.
Klanten |
||||||
---|---|---|---|---|---|---|
Klant-id |
Naam |
Straat |
Plaats |
Staat |
Postcode |
Telefoon |
1001 |
Contoso, Ltd. |
2302 Harvard Ave |
Bellevue |
WA |
98227 |
425-555-0222 |
1003 |
Adventure Works |
1025 Columbia Circle |
Haarlem |
WA |
98234 |
425-555-0185 |
1005 |
Fourth Coffee |
7007 Cornell St |
Redmond |
WA |
98199 |
425-555-0201 |
De tabel Orders bevat informatie over orders, verkopers, klanten en producten. Elke record heeft een unieke id (order-id). Een deel van de informatie in deze tabel moet worden gesplitst in een extra tabel die ordergegevens bevat, zodat de tabel Orders slechts vier kolommen bevat: de unieke order-id, de orderdatum, de verkoper-id en de klant-id. De tabel die hier wordt weergegeven, is nog niet gesplitst in de tabel Ordergegevens.
Bestellingen |
|||||
---|---|---|---|---|---|
Order-id |
Orderdatum |
Verkoopmedewerker-id |
Klant-id |
Product-id |
Aantal |
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 |
Ordergegevens, zoals de product-id en hoeveelheid, worden verplaatst uit de tabel Orders en opgeslagen in een tabel met de naam Orderdetails. Houd er rekening mee dat er 9 orders zijn, dus het is logisch dat er 9 records in deze tabel staan. Houd er rekening mee dat de tabel Orders een unieke id (Order-id) heeft, waarnaar wordt verwezen vanuit de tabel Ordergegevens.
Het uiteindelijke ontwerp van de tabel Orders moet er als volgt uitzien:
Bestellingen |
|||
---|---|---|---|
Order-id |
Orderdatum |
Verkoopmedewerker-id |
Klant-id |
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 |
De tabel Orderdetails bevat geen kolommen waarvoor unieke waarden zijn vereist (dat wil gezegd, er is geen primaire sleutel), dus het is prima dat een of meer kolommen 'redundante' gegevens bevatten. Er mogen echter geen twee records in deze tabel volledig identiek zijn (deze regel is van toepassing op elke tabel in een database). In deze tabel moeten er 17 records zijn, die elk overeenkomen met een product in een afzonderlijke bestelling. In order 2349 bestaan bijvoorbeeld drie C-789-producten uit een van de twee onderdelen van de hele bestelling.
De tabel Ordergegevens moet er daarom als volgt uitzien:
Details van bestelling |
||
---|---|---|
Order ID |
Product ID |
Aantal |
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 |
Gegevens uit Excel kopiëren en plakken in Access
Nu de informatie over verkopers, klanten, producten, orders en ordergegevens in Excel is onderverdeeld in afzonderlijke onderwerpen, kunt u die gegevens rechtstreeks kopiëren naar Access, waar ze tabellen worden.
Relaties tussen de Access-tabellen maken en een query uitvoeren
Nadat u uw gegevens hebt verplaatst naar Access, kunt u relaties tussen tabellen maken en vervolgens query's maken om informatie over verschillende onderwerpen te retourneren. U kunt bijvoorbeeld een query maken die de order-id en de namen van de verkopers retourneert voor orders die zijn ingevoerd tussen 05-03-09 en 08-03-09.
Daarnaast kunt u formulieren en rapporten maken om gegevensinvoer en verkoopanalyse eenvoudiger te maken.
Meer hulp nodig?
U kunt altijd uw vraag stellen aan een expert in de Excel Tech Community of ondersteuning vragen in de Communities.