Gegevens verplaatsen van Excel naar Access

In dit artikel leest u hoe u uw gegevens van Excel kunt verplaatsen naar uw gegevens en hoe u deze kunt converteren naar relationele tabellen, zodat u Microsoft Excel samen met elkaar kunt gebruiken. Als u wilt samenvatten, kunt u dit het beste doen om gegevens te leggen, op te slaan, te bevragen en te delen, en Excel is het meest geschikt voor het berekenen, analyseren en visualiseren van gegevens.

Twee artikelen, met Access of Excel om uw gegevens en de tien belangrijkste redenen voor het gebruik van Access met Excelte bespreken, te bespreken welk programma geschikt is voor een bepaalde taak en hoe u Excel en Access samen kunt gebruiken om een concrete oplossing te maken.

Wanneer u gegevens verplaatst van Excel naar Access, moet u drie basisstappen uitvoeren voor het proces.

drie basisstappen

Opmerking: Zie beginselen van database ontwerpvoor meer informatie over gegevensmodellering en relaties in Access.

Stap 1: gegevens importeren van Excel in Access

Het importeren van gegevens is een bewerking die een heleboel soepeler verloopt als u uw gegevens zo nodig kunt voorbereiden en wissen. Het importeren van gegevens is vergelijkbaar met het overstappen naar een nieuw thuis. Als u uw bezit opschoont en organiseert voordat u overstapt, kunt u veel eenvoudiger overstappen op de nieuwe woning.

Uw gegevens wissen voordat u gaat importeren

Voor het importeren van gegevens in Access, kunt u het beste in Excel het volgende doen:

  • Converteer cellen die niet-atomaire gegevens bevatten (dat wil zeggen, meerdere waarden in één cel) naar meerdere kolommen. Een cel in de kolom ' vaardigheden ' die meerdere vaardigheids waarden bevat, zoals ' C#-programmering ', ' VBA-programmering ' en ' Webontwerp ' moeten bijvoorbeeld worden afgebroken voor afzonderlijke kolommen die minstens één vaardigheids waarde bevatten.

  • Gebruik de opdracht spaties. wissen om voorloop, achteraan en meerdere ingesloten spaties te verwijderen.

  • Verwijder niet-afdrukbare tekens.

  • Spel-en leesfouten zoeken en corrigeren.

  • Dubbele rijen of dubbele velden verwijderen.

  • Zorg ervoor dat kolommen met gegevens geen gemengde opmaak bevatten, met name getallen die zijn opgemaakt als tekst of datums die als getallen zijn opgemaakt.

Zie de volgende Help-onderwerpen voor Excel voor meer informatie:

Opmerking: Als uw behoeften aan gegevens complex zijn, of als u de tijd of bronnen niet hebt om het proces zelf te automatiseren, kunt u overwegen een leverancier van derden te gebruiken. Als u meer informatie zoekt, zoekt u naar ' gegevens opschonen ' of ' gegevenskwaliteit ' op basis van uw favoriete zoekprogramma in uw webbrowser.

Het beste gegevenstype kiezen bij het importeren

Tijdens de importbewerking in Access, wilt u goede keuzes maken, zodat u enkele Conversiefouten ontvangt die handmatig moeten worden aangemeld. De volgende tabel bevat een overzicht van de manier waarop Excel-getalnotaties en Access-gegevenstypen worden geconverteerd wanneer u gegevens uit Excel importeert in Access, en een aantal tips voor de beste gegevenstypen die u kunt kiezen in de wizard Werkblad importeren.

Getalnotatie in Excel

Gegevenstype in Access

Opmerkingen

Aanbevolen procedures

Tekst

Tekst, Memo

In het gegevenstype Access-tekst worden alfanumerieke gegevens van maximaal 255 tekens opgeslagen. In het gegevenstype van Access worden alfanumerieke gegevens van maximaal 65.535 tekens opgeslagen.

Kies Memo om te voorkomen dat gegevens worden afgekapt.

Getal, percentage, breuk, wetenschappelijk

Getal

Er geldt een gegevenstype van één getal dat afhankelijk is van een eigenschap Veldlengte (byte, geheel getal, lange integer, enkel, dubbel, decimaal).

Kies dubbel om gegevensconversie fouten te voorkomen.

Datum

Datum

Access en Excel gebruiken allebei hetzelfde seriële datum nummer voor het opslaan van datums. In Access is het datumbereik groter: van-657.434 (1 januari 100 na 1 januari) tot 2.958.465 (31 december 9999 n).

Aangezien het 1904-datumsysteem (gebruikt in Excel voor de Macintosh) niet wordt herkend in Access, moet u de datums in Excel of Access converteren om verwarring te voorkomen.

Zie voor meer informatie het datumsysteem, de notatie of de interpretatie van het datumsysteem, de notatie of de tweecijferig jaar wijzigen en gegevens importeren uit of een koppeling maken naar gegevens in een Excel-werkmap.

Kies date.

Time

Tijd

In Access en Excel worden beide tijdwaarden opgeslagen met hetzelfde gegevenstype.

Kies tijd, meestal de standaardinstelling.

Valuta, financieel

Valuta

In Access wordt in het gegevenstype valutagegevens opgeslagen als 8-byte getallen met een precisie van vier decimalen, en wordt gebruikt voor het opslaan van financiële gegevens en voorkomen dat waarden worden afgerond.

Kies valuta, meestal de standaardinstelling.

Booleaans

Ja/Nee

Access gebruikt-1 voor alle Ja-waarden en 0 voor alle waarden, terwijl in Excel 1 voor alle waar waarden en 0 voor alle onwaar-waarden worden gebruikt.

Kies Ja/Nee, zodat de onderliggende waarden automatisch worden geconverteerd.

Hyperlink

Hyperlink

Een hyperlink in Excel en Access bevat een URL of een webadres waarop u kunt klikken en die u kunt volgen.

Kies hyperlink, anders wordt het gegevenstype tekst standaard gebruikt.

Wanneer de gegevens in Access zijn, kunt u de Excel-gegevens verwijderen. Vergeet niet eerst een back-up van de oorspronkelijke Excel-werkmap te maken voordat u deze verwijdert.

Voor meer informatie raadpleegt u het Help-onderwerp voor Access- gegevens importeren of een koppeling maken naar gegevens in een Excel-werkmap.

Gegevens op een eenvoudige manier automatisch toevoegen

Een veelvoorkomend probleem moet Excel-gebruikersgegevens toevoegen aan dezelfde kolommen in één groot werkblad. U kunt bijvoorbeeld een oplossing voor het bijhouden van activa hebben die is gestart in Excel, maar nu ook bestanden van een groot aantal werkgroepen en afdelingen bevat. Deze gegevens kunnen in verschillende werkbladen en werkmappen worden gebruikt of in tekstbestanden die gegevensinvoer van andere systemen zijn. Er is geen opdracht van de gebruikersinterface of een eenvoudige manier om soortgelijke gegevens toe te voegen in Excel.

De beste oplossing is om Access te gebruiken, waarmee u eenvoudig gegevens kunt importeren en toevoegen in één tabel met behulp van de wizard Werkblad importeren. Daarnaast kunt u een groot aantal gegevens toevoegen aan één tabel. U kunt de importbewerkingen opslaan, toevoegen als geplande Microsoft Outlook-taken en ook macro's gebruiken om het proces te automatiseren.

Stap 2: gegevens normaliseren met de wizard Tabelanalyse

Allereerst moet u het proces van het normaliseren van uw gegevens laten zien, waarschijnlijk een opvallende taak lijken. Gelukkig is het normaliseren van tabellen in Access een proces dat veel eenvoudiger is, dankzij de wizard Tabelanalyse.

de wizard Tabelanalyse

1. geselecteerde kolommen naar een nieuwe tabel slepen en automatisch relaties maken

2. de knop opdrachten gebruiken om de naam van een tabel te wijzigen, een primaire sleutel toe te voegen, een bestaande kolom een primaire sleutel te maken en de laatste bewerking ongedaan te maken

U kunt deze wizard gebruiken om de volgende handelingen uit te voeren:

  • Converteer een tabel naar een set kleinere tabellen en maak automatisch een primaire en refererende-sleutel relatie tussen de tabellen.

  • Voeg een primaire sleutel toe aan een bestaand veld dat unieke waarden bevat of maak een nieuw ID-veld dat gebruikmaakt van het gegevenstype AutoNummering.

  • Automatisch relaties maken om referentiële integriteit af te dwingen met trapsgewijze updates. Trapsgewijze verwijderingen worden niet automatisch toegevoegd om per ongeluk gegevens te verwijderen, maar u kunt later eenvoudig trapsgewijze verwijderingen toevoegen.

  • Zoek nieuwe tabellen voor overtollige of dubbele gegevens (zoals dezelfde klant met twee verschillende telefoonnummers) en werk deze naar wens bij.

  • U kunt een back-up van de oorspronkelijke tabel maken en de naam ervan wijzigen door ' _OLD ' toe te voegen aan de naam. Vervolgens maakt u een query die de oorspronkelijke tabel opnieuw maakt, met de oorspronkelijke tabelnaam, zodat bestaande formulieren of rapporten op basis van de oorspronkelijke tabel met de nieuwe tabelstructuur werken.

Zie uw gegevens normaliseren met de wizard Tabelanalysevoor meer informatie.

Stap 3: verbinding maken met Access-gegevens vanuit Excel

Nadat de gegevens zijn genormaliseerd in Access en er een query of tabel is gemaakt waarmee de oorspronkelijke gegevens opnieuw worden opgebouwd, is het een eenvoudige manier om verbinding te maken met de Access-gegevens vanuit Excel. Uw gegevens bevinden zich nu in Access als een externe gegevensbron, en daarom kan verbinding worden gemaakt met de werkmap via een gegevensverbinding, wat een container met informatie is die wordt gebruikt om te zoeken, aan te melden en toegang te krijgen tot de externe gegevensbron. De verbindingsgegevens zijn opgeslagen in de werkmap en kunnen ook worden opgeslagen in een verbindingsbestand, zoals een ODC-bestand (Office Data Connection) (ODC)-bestand (. ODC) of een data source name File (. 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 in Access worden bijgewerkt.

Zie gegevens uit externe gegevensbronnen importeren (Power query)voor meer informatie.

Uw gegevens verkrijgen in Access

In deze sectie wordt u stapsgewijs begeleid bij het normaliseren van uw gegevens: waarden in de kolommen Verkoper en adres opsplitsen in de meest atomische gedeelten, zodat verwante onderwerpen in hun eigen tabellen worden gescheiden, en de tabellen in Excel kunnen worden gekopieerd en geplakt. Access, belangrijkste relaties maken tussen de nieuwe Access-tabellen en een eenvoudige query maken en uitvoeren in Access om informatie te retourneren.

Voorbeeldgegevens in een niet-genormaliseerd formulier

Het volgende werkblad bevat niet-atoom 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 opgesplitst in afzonderlijke tabellen, op basis van het onderwerp.

Verkoper

Order-id

Orderdatum

Product-id

Verbruik

Prijs

Naam van klant

Adres

Telefoon

Li, Yale

2349

3/4/09

C-789

3

$7,00

Fourth Coffee

7007 Cornell Saint Redmond, WA 98199

425-555-0201

Li, Yale

2349

3/4/09

C-795

6

$9,75

Fourth Coffee

7007 Cornell Saint 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

De graaf, Jim

2351

3/4/09

C-795

6

$9,75

Contoso, Ltd.

2302 Harvard Ave Bellevue, WA 98227

425-555-0222

De graaf, Jim

2352

3/5/09

A-2275

2

$16,75

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

De graaf, 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 Saint Redmond, WA 98199

425-555-0201

Koch, Reed

2353

3/7/09

C-789

5

$7,00

Fourth Coffee

7007 Cornell Saint Redmond, WA 98199

425-555-0201

Gegevens in de kleinste gedeelten: atoom gegevens

Werken met de gegevens in dit voorbeeld kunt u met de opdracht tekst naar kolom in Excel de ' Atoom ' delen van een cel (zoals adres, plaats, provincie en postcode) scheiden in afzonderlijke kolommen.

In de volgende tabel worden de nieuwe kolommen in hetzelfde werkblad weergegeven nadat ze zijn gesplitst, zodat alle waarden atomisch zijn. Houd er rekening mee dat de informatie in de kolom Verkoper is opgesplitst in de achternaam en de kolommen Voornaam, en dat de informatie in de kolom adres is opgesplitst in de kolommen adres, plaats, provincie en post code. Deze gegevens bevindt zich in ' eerste normaal formulier '.

Achternaam

Voornaam

 

Straat

Plaats

Staat

Postcode

Li

Yale

2302 Harvard Ave

Bellevue

WA

98227

Adams

Ellen

1025 Columbia cirkel

Haarlem

WA

98234

De graaf

Jimmy

2302 Harvard Ave

Bellevue

WA

98227

Koch

Reed

7007 Cornell Saint Redmond

Redmond

WA

98199

Gegevens afbreken in georganiseerde onderwerpen in Excel

In de verschillende tabellen van de voorbeeldgegevens die worden weergegeven, worden dezelfde gegevens uit het Excel-werkblad weergegeven nadat deze in tabellen voor verkopers, producten, klanten en orders is opgesplitst. Het tabelontwerp is niet definitief, maar het is aan de rechterkant van het spoor.

De tabel verkopers bevat alleen informatie over verkooppersoneel. Houd er rekening mee dat elke record een unieke ID heeft (verkoper-ID). De waarde verkoper-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

De graaf

Jimmy

107

Koch

Reed

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 product informatie te verbinden met de tabel Order informatie.

Aquicultuurproducten

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 verbinden met 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 cirkel

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. Houd er rekening mee dat elke record een unieke ID heeft (Order-ID). Een deel van de informatie in deze tabel moet worden opgesplitst in een extra tabel die de Order Details 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 Order informatie.

Orders

Order-id

Orderdatum

Verkoper-ID

Klant-id

Product-id

Verbruik

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

Order informatie, zoals het productnummer en de hoeveelheid, worden uit de tabel Orders verplaatst en opgeslagen in een tabel met de naam order informatie. Houd er rekening mee dat er 9 orders zijn, zodat deze tabel wel 9 records bevat. Houd er rekening mee dat de tabel Orders een unieke ID (Order-ID) heeft waarnaar wordt verwezen in de tabel Order informatie.

Het uiteindelijke ontwerp van de tabel Orders moet er als volgt uitzien:

Orders

Order-id

Orderdatum

Verkoper-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 Order informatie bevat geen kolommen waarin unieke waarden zijn vereist (dat wil zeggen dat er geen primaire sleutel is), dus het is aan te raden om alle kolommen ' overtollige ' gegevens te bevatten. U moet echter niet beide records in deze tabel volledig identiek zijn (deze regel geldt voor elke tabel in een database). De volgende tabel bevat zeven records, elk met een product in een afzonderlijke volgorde. In volgorde 2349 zijn drie C-789-producten bijvoorbeeld één van de twee delen van de volledige order.

De tabel Order informatie moet er daarom als volgt uitzien:

Details van bestelling

Order ID

Product ID

Verbruik

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 kopiëren en plakken van Excel naar Access

Nu de informatie over verkopers, klanten, producten, bestellingen en Orderinformatie is opgesplitst in afzonderlijke onderwerpen in Excel, kunt u die gegevens rechtstreeks naar Access kopiëren, waar ze tabellen worden.

Relaties maken tussen de Access-tabellen en een query uitvoeren

Nadat u uw gegevens naar Access hebt verplaatst, kunt u relaties maken tussen tabellen en vervolgens query's maken om informatie over verschillende onderwerpen te retourneren. U kunt bijvoorbeeld een query maken waarmee de order-ID en de namen van de verkopers voor orders die zijn ingevoerd tussen 3/05/09 en 3/08/09, worden geretourneerd.

Daarnaast kunt u formulieren en rapporten maken om de gegevensinvoer en de verkoopanalyses eenvoudiger te maken.

Meer hulp nodig?

U kunt altijd uw vraag stellen aan een expert in de Excel Tech Community, ondersteuning vragen in de Answer-community of een nieuwe functie of verbetering voorstellen in Excel User Voice.

Uw Office-vaardigheden uitbreiden
Training verkennen
Als eerste nieuwe functies krijgen
Deelnemen aan Office Insiders

Was deze informatie nuttig?

Bedankt voor uw feedback.

Hartelijk dank voor uw feedback! Het lijkt ons een goed idee om u in contact te brengen met een van onze Office-ondersteuningsagenten.

×