Opmerking
Microsoft Access biedt geen ondersteuning voor het importeren van Excel-gegevens met een toegepast gevoeligheidslabel. Als tijdelijke oplossing kunt u het label verwijderen voordat u het importeert en het na het importeren opnieuw aanbrengen. Zie Gevoeligheidslabels toepassen op uw bestanden en e-mail in Office voor meer informatie.
In dit artikel leest u hoe u uw gegevens kunt verplaatsen van Excel naar Access en uw gegevens kunt converteren naar relationele tabellen zodat u Microsoft Excel en Access samen kunt gebruiken. Kort samengevat is Access het meest geschikt voor het vastleggen, opslaan, opvragen en delen van gegevens en is Excel het meest geschikt voor het berekenen, analyseren en visualiseren van gegevens.
In twee artikelen, Access of Excel gebruiken om uw gegevens te beheren en de tien 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, moet u drie basisstappen uitvoeren.
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 verloopt veel soepeler, als u de tijd neemt om uw gegevens voor te bereiden en op te schonen. Het importeren van gegevens is alsof u naar een nieuwe woning verhuist. Als u uw bezittingen opruimt en organiseert voordat u verhuist, is het veel gemakkelijker om u in uw nieuwe huis te vestigen.
Uw gegevens opschonen voordat u importeert
Voordat u gegevens in Access importeert, is het raadzaam om in Excel het volgende te doen:
- Cellen met niet-atomaire gegevens (meerdere waarden in één cel) converteren naar meerdere kolommen. Bijvoorbeeld: een cel in de kolom 'Vaardigheden' die meerdere vaardigheidswaarden bevat, zoals 'C#-programmeren', 'VBA-programmeren' en 'Webdesign', moet worden onderverdeeld in aparte kolommen die elk slechts één vaardigheidswaarde bevatten.
- Gebruik de opdracht SPATIES.WISSEN om voorloopspaties, volgspaties en meerdere ingesloten spaties te verwijderen.
- Niet-afdrukbare tekens verwijderen.
- Spelfouten en fouten in interpunctie zoeken en corrigeren.
- Dubbele rijen of dubbele velden verwijderen.
- Zorg ervoor dat kolommen met gegevens geen gemengde indelingen bevatten, met name getallen die zijn opgemaakt als tekst of datums die zijn opgemaakt als getallen.
Zie de volgende Help-onderwerpen voor Excel voor meer informatie:
- De tien beste manieren om uw gegevens op te schonen
- Filteren op unieke waarden of dubbele waarden verwijderen
- Tekstnotatie van getallen converteren naar getalnotatie
- Tekstnotatie van datums converteren naar datumnotatie
Opmerking
Als uw behoeften op het gebied van het opschonen van gegevens complex zijn, of als u niet over de tijd of de middelen beschikt om het proces zelf te automatiseren, kunt u de hulp van een externe leverancier overwegen. Zoek voor meer informatie naar 'software voor het opschonen van gegevens' of 'gegevenskwaliteit' met uw favoriete zoekmachine in uw webbrowser.
Het beste gegevenstype kiezen bij het importeren
Tijdens de importbewerking in Access wilt u de juiste keuzen maken, zodat er weinig (of geen) conversiefouten optreden die handmatig moeten worden ingegrepen. In de volgende tabel wordt een overzicht gegeven van de manier waarop Excel-getalnotaties en Access-gegevenstypen worden geconverteerd wanneer u gegevens uit Excel in Access importeert. U vindt er ook enkele tips voor de beste gegevenstypen die u kunt kiezen in de wizard Werkblad importeren.
| Getalnotatie van Excel | Gegevenstype in Access | Opmerkingen | Aanbevolen procedures |
|---|---|---|---|
| Text | Tekst, memo | Met het gegevenstype Access Text kunnen alfanumerieke gegevens maximaal 255 tekens worden opgeslagen. In het gegevenstype Memo van Access kunnen alfanumerieke gegevens maximaal 65.535 tekens worden opgeslagen. | Kies Memo om te voorkomen dat er gegevens worden afgekapt. |
| Numeriek, Percentage, Breuk, Wetenschappelijk | Getal | Access heeft één numeriek gegevenstype dat varieert op basis van een veldlengte-eigenschap (byte, geheel getal, lange integer, enkel, dubbel, decimaal). | Kies Double om fouten bij de gegevensconversie te voorkomen. |
| Datum | Datum | In Access en Excel wordt hetzelfde seriële getal gebruikt voor het opslaan van datums. In Access is het datumbereik groter: van -657.434 (1 januari 100 n.Chr.) tot 2.958.465 (31 december 9999 n.Chr.). Omdat het datumsysteem 1904 (dat wordt gebruikt in Excel voor de Macintosh) niet wordt herkend, moet u de datums in Excel of Access converteren om verwarring te voorkomen. Zie Het datumsysteem, de datumnotatie of de interpretatie van jaartallen met twee cijfers wijzigen en Gegevens importeren uit of een koppeling maken naar gegevens in een Excel-werkmap. |
Kies Datum. |
| Time | Tijd | In Access en Excel worden tijdwaarden beide opgeslagen met hetzelfde gegevenstype. | Kies Tijd, meestal de standaardinstelling. |
| Valuta, Financieel | Valuta | Met het gegevenstype Valuta worden gegevens in Access opgeslagen als getallen van 8 bytes met een precisie tot op vier decimalen. Het gegevenstype wordt gebruikt om financiële gegevens op te slaan en het afronden van waarden te voorkomen. | Kies Valuta, wat meestal de standaardinstelling is. |
| Booleaans | Ja/Nee | In Access wordt -1 gebruikt voor alle Ja-waarden en 0 voor alle Nee-waarden, terwijl Excel 1 gebruikt voor alle WAAR-waarden en 0 voor alle ONWAAR-waarden. | Kies Ja/Nee, waarmee de onderliggende waarden automatisch worden geconverteerd. |
| Hyperlink | Hyperlink | Een hyperlink in Excel en Access bevat een URL of webadres waarop u kunt klikken en die u kunt volgen. | Kies Hyperlink. Anders wordt mogelijk standaard het gegevenstype Tekst gebruikt. |
Als de gegevens eenmaal in Access staan, 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 uit of een koppeling maken naar gegevens in een Excel-werkmap.
Gegevens automatisch toevoegen op een eenvoudige manier
Een veelvoorkomend probleem dat Excel-gebruikers ondervinden, is het toevoegen van gegevens met dezelfde kolommen aan één groot werkblad. Zo hebt u een oplossing voor activaregistratie die oorspronkelijk in Excel is ontwikkeld, maar inmiddels is uitgegroeid tot bestanden van vele 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 Werkblad importeren. Bovendien kunt u een grote hoeveelheid gegevens aan één tabel toevoegen. 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 de wizard Tabelanalyse
Op het eerste gezicht lijkt het stappen om het proces van normaliseren van uw gegevens te doorlopen misschien een ontmoedigende taak. Gelukkig is het normaliseren van tabellen in Access een proces dat veel eenvoudiger is, dankzij de wizard Tabelanalyse.
1. okt. Geselecteerde kolommen naar een nieuwe tabel slepen en automatisch relaties maken
2. okt. Gebruik knopopdrachten om de naam van een tabel te wijzigen, een primaire sleutel toe te voegen, een bestaande kolom in te stellen als primaire sleutel en de laatste bewerking ongedaan te maken
U kunt met deze wizard het volgende doen:
- Een tabel converteren naar een reeks kleinere tabellen en automatisch een primaire relatie en refererende-sleutelrelatie tussen de tabellen maken.
- 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.
- Maak automatisch relaties om referentiële integriteit af te dwingen met trapsgewijze updates. Trapsgewijs verwijderen wordt niet automatisch toegevoegd om te voorkomen dat gegevens per ongeluk worden verwijderd, maar u kunt trapsgewijs verwijderen later eenvoudig 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 van de tabel door "_OLD" achter de naam toe te voegen. Vervolgens maakt u een query waarmee de oorspronkelijke tabel wordt gereconstrueerd, met de oorspronkelijke tabelnaam, zodat bestaande formulieren of rapporten die op de oorspronkelijke tabel zijn gebaseerd, met de nieuwe tabelstructuur werken.
Zie Uw gegevens normaliseren met Tabelanalyse voor 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 worden gereconstrueerd, is het eenvoudig om vanuit Excel verbinding te maken met de Access-gegevens. Uw gegevens bevinden zich nu in Access als een externe gegevensbron en kunnen dus met de werkmap worden verbonden via een gegevensverbinding, een container met informatie die wordt gebruikt voor het zoeken naar, aanmelden bij en openen van de externe gegevensbron. De verbindingsgegevens worden opgeslagen in de werkmap en kunnen ook worden opgeslagen in een verbindingsbestand, zoals een ODC-bestand (Office Data Connection, een ODC-bestand) (de bestandsnaamextensie .odc) of een gegevensbronnaambestand (de extensie .dsn). 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 uit externe gegevensbronnen importeren (Power Query) voor meer informatie.
Uw gegevens in Access opnemen
In deze sectie worden de volgende fasen van het normaliseren van uw gegevens beschreven: waarden in de kolommen Verkoper en Adres opsplitsen in de meest atomaire stukken, verwante onderwerpen scheiden in eigen tabellen, deze tabellen kopiëren en plakken van Excel in Access, sleutelrelaties definiëren tussen de nieuw gemaakte Access-tabellen en een eenvoudige query maken en uitvoeren in Access om gegevens op te halen.
Voorbeeldgegevens in niet-genormaliseerde vorm
Het volgende werkblad bevat niet-atomaire 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, 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 | US$ 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, Riet | 2353 | 3/7/09 | A-2275 | 6 | $16.75 | Fourth Coffee | 7007 Cornell St Redmond, WA 98199 | 425-555-0201 |
| Koch, Riet | 2353 | 3/7/09 | C-789 | 5 | US$ 7,00 | Fourth Coffee | 7007 Cornell St Redmond, WA 98199 | 425-555-0201 |
Informatie in de kleinste delen: atomaire gegevens
Wanneer u werkt met de gegevens in dit voorbeeld, kunt u de opdracht Tekst naar kolom in Excel gebruiken om de 'atomaire' 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 atomair te maken. U ziet dat de gegevens in de kolom Verkoper zijn opgesplitst in de kolommen Achternaam en Voornaam en dat de gegevens in de kolom Adres zijn opgesplitst in de kolommen Adres, Plaats, Staat en Postcode. Deze gegevens bevinden zich in de 'eerste normaalvorm'.
| Achternaam | Voornaam | Straat | Plaats | Stand | 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 geordende onderwerpen in Excel
De volgende tabellen met voorbeeldgegevens bevatten dezelfde informatie uit het Excel-werkblad nadat deze is opgesplitst in tabellen voor verkopers, producten, klanten en orders. Het tabelontwerp is nog niet definitief, maar het is op de goede weg.
De tabel Verkopers bevat alleen gegevens over het verkooppersoneel. Elke record heeft een unieke id (verkoper-id). De waarde Verkoper-id wordt in de tabel Orders gebruikt om orders aan verkopers te koppelen.
| 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. Elke record heeft een unieke product-id. De waarde Product-id wordt gebruikt om productinformatie te koppelen aan de tabel Orderinformatie.
| 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 gegevens over klanten. Elke record heeft een unieke id (klant-id). De waarde van de klant-id wordt gebruikt om klantgegevens aan de tabel Orders te koppelen.
| Klanten | ||||||
|---|---|---|---|---|---|---|
| Klant-id | Naam | Straat | Plaats | Stand | 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 gegevens in deze tabel moet worden opgesplitst in een extra tabel met ordergegevens, zodat de tabel Orders maar vier kolommen bevat: de unieke order-id, de orderdatum, de verkoper-id en de klant-id. De hier weergegeven tabel is nog niet gesplitst in de tabel Orderinformatie.
| Bestellingen | |||||
|---|---|---|---|---|---|
| Order-id | Orderdatum | Verkoper-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 uit de tabel Orders gehaald en opgeslagen in de tabel Orderdetails. Houd er rekening mee dat er negen orders zijn, dus is het logisch dat er negen records in deze tabel staan. De tabel Orders heeft een unieke id (order-id) waarnaar wordt verwezen vanuit de tabel Orderinformatie.
Het uiteindelijke ontwerp van de tabel Orders moet er als volgt uitzien:
| Bestellingen | |||
|---|---|---|---|
| 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 Orderinformatie bevat geen kolommen waarvoor unieke waarden zijn vereist (dat wil zeggen, er is geen primaire sleutel), dus het is geen probleem dat een of alle kolommen 'redundante' gegevens bevatten. Twee records in deze tabel mogen echter niet volledig identiek zijn (deze regel geldt voor alle tabellen in een database). Deze tabel moet 17 records bevatten, die elk overeenkomen met een product in een afzonderlijke order. In order 2349 vormen bijvoorbeeld drie C-789-producten een van de twee delen van de hele order.
De tabel Orderinformatie 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 van Excel kopiëren en plakken in Access
Nu de informatie over verkopers, klanten, producten, orders en orderdetails in Excel is opgedeeld in afzonderlijke onderwerpen, kunt u die gegevens rechtstreeks kopiëren naar Access, waar het tabellen worden.
Relaties tussen de Access-tabellen maken en een query uitvoeren
Nadat u uw gegevens hebt verplaatst naar Access, kunt u relaties maken tussen tabellen en vervolgens query's maken om gegevens over verschillende onderwerpen op te halen. U kunt bijvoorbeeld een query maken die de order-id en de namen van de verkopers als resultaat geeft voor orders die tussen 5-3-09 en 8-3-2009 zijn ingevoerd.
Daarnaast kunt u formulieren en rapporten maken om gegevensinvoer en verkoopanalyse te vereenvoudigen.
Meer hulp nodig?
U kunt altijd uw vraag stellen aan een expert in de Excel Tech Community of ondersteuning vragen in Community's.