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

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.

drie basisstappen

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.

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.

Meer hulp nodig?

Meer opties?

Verken abonnementsvoordelen, blader door trainingscursussen, leer hoe u uw apparaat kunt beveiligen en meer.

Community's helpen u vragen te stellen en te beantwoorden, feedback te geven en te leren van experts met uitgebreide kennis.