Hinweis: Microsoft Access unterstützt das Importieren von Excel-Daten mit einer angewendeten Vertraulichkeitsbezeichnung nicht. Als Problemumgehung können Sie die Bezeichnung vor dem Importieren entfernen und die Bezeichnung nach dem Importieren erneut anwenden. Weitere Informationen finden Sie unter Anwenden von Vertraulichkeitsbezeichnungen auf Ihre Dateien und E-Mails in Office.
In diesem Artikel erfahren Sie, wie Sie Ihre Daten aus Excel nach Access verschieben und Ihre Daten in relationale Tabellen konvertieren, damit Sie Microsoft Excel und Access zusammen verwenden können. Zusammenfassend lässt sich sagen, dass Access am besten zum Erfassen, Speichern, Abfragen und Freigeben von Daten geeignet ist, und Excel eignet sich am besten zum Berechnen, Analysieren und Visualisieren von Daten.
In zwei Artikeln, Verwenden von Access oder Excel zum Verwalten Ihrer Daten und Top 10 Gründe für die Verwendung von Access mit Excel, wird erläutert, welches Programm für eine bestimmte Aufgabe am besten geeignet ist und wie Excel und Access zusammen verwendet werden, um eine praktische Lösung zu erstellen.
Wenn Sie Daten aus Excel nach Access verschieben, gibt es drei grundlegende Schritte für den Prozess.
Hinweis: Informationen zur Datenmodellierung und zu Beziehungen in Access finden Sie unter Grundlagen zum Datenbankentwurf.
Schritt 1: Importieren von Daten aus Excel in Access
Das Importieren von Daten ist ein Vorgang, der viel reibungsloser ablaufen kann, wenn Sie sich etwas Zeit zum Vorbereiten und Bereinigen Ihrer Daten nehmen. Das Importieren von Daten ist wie das Verschieben in ein neues Zuhause. Wenn Sie Ihre Besitztümer bereinigen und organisieren, bevor Sie umziehen, ist es viel einfacher, sich in Ihrem neuen Zuhause niederzulassen.
Bereinigen Der Daten vor dem Import
Bevor Sie Daten in Access importieren, ist es in Excel eine gute Idee:
-
Konvertieren Sie Zellen, die nicht atomare Daten (d. h. mehrere Werte in einer Zelle) in mehrere Spalten enthalten. Beispielsweise sollte eine Zelle in einer Spalte "Skills", die mehrere Skillwerte enthält, z. B. "C#-Programmierung", "VBA-Programmierung" und "Webdesign", in separate Spalten aufgeteilt werden, die jeweils nur einen Skillwert enthalten.
-
Verwenden Sie den Befehl TRIM, um führende, nachfolgende und mehrere eingebettete Leerzeichen zu entfernen.
-
Entfernen Sie nicht druckbare Zeichen.
-
Suchen und Beheben von Rechtschreib- und Interpunktionsfehlern.
-
Entfernen Sie doppelte Zeilen oder doppelte Felder.
-
Stellen Sie sicher, dass Datenspalten keine gemischten Formate enthalten, insbesondere Zahlen, die als Text formatiert sind, oder Datumsangaben, die als Zahlen formatiert sind.
Weitere Informationen finden Sie in den folgenden Excel-Hilfethemen:
-
Filtern nach eindeutigen Werten und Entfernen von doppelten Werten
-
Konvertieren von Datumsangaben, die als Text gespeichert wurden, in Datumswerte
Hinweis: Wenn Ihre Datenbereinigungsanforderungen komplex sind oder Sie nicht über die Zeit oder Ressourcen verfügen, den Prozess selbst zu automatisieren, sollten Sie die Verwendung eines Drittanbieters in Betracht ziehen. Weitere Informationen finden Sie, indem Sie in Ihrem Webbrowser nach "Datenbereinigungssoftware" oder "Datenqualität" ihrer bevorzugten Suchmaschine suchen.
Auswählen des besten Datentyps beim Importieren
Während des Importvorgangs in Access möchten Sie gute Entscheidungen treffen, damit Sie nur wenige (falls vorhanden) Konvertierungsfehler erhalten, die einen manuellen Eingriff erfordern. Die folgende Tabelle fasst zusammen, wie Excel-Zahlenformate und Access-Datentypen konvertiert werden, wenn Sie Daten aus Excel in Access importieren, und enthält einige Tipps zu den besten Datentypen, die im Import Spreadsheet-Assistenten ausgewählt werden können.
Excel-Zahlenformat |
Access-Datentyp |
Kommentare |
Bewährte Methode |
---|---|---|---|
Text |
Text, Memo |
Der Access Text-Datentyp speichert alphanumerische Daten bis zu 255 Zeichen. Der Access Memo-Datentyp speichert alphanumerische Daten bis zu 65.535 Zeichen. |
Wählen Sie Memo aus, um das Abschneiden von Daten zu vermeiden. |
Zahl, Prozentsatz, Bruch, Wissenschaftlich |
Zahl |
Access verfügt über einen Zahlendatentyp, der je nach Feldgröße-Eigenschaft variiert (Byte, Integer, Long Integer, Single, Double, Decimal). |
Wählen Sie Doppelt aus, um Datenkonvertierungsfehler zu vermeiden. |
Datum |
Datum |
Access und Excel verwenden zum Speichern von Datumsangaben dieselbe fortlaufende Datumsnummer. In Access ist der Datumsbereich größer: von -657.434 (1. Januar 100 n. Chr.) bis 2.958.465 (31. Dezember 9999 n. Chr.). Da Access das 1904-Datumssystem (das in Excel für Macintosh verwendet wird) nicht erkennt, müssen Sie die Datumsangaben entweder in Excel oder Access konvertieren, um Verwechslungen zu vermeiden. Weitere Informationen finden Sie unter Ändern des Datumssystems, des Formats oder der zweistelligen Jahresinterpretation und Importieren oder Verknüpfen von Daten in einer Excel-Arbeitsmappe. |
Wählen Sie Datum aus. |
Zeit |
Zeit |
Access und Excel speichern Zeitwerte mit demselben Datentyp. |
Wählen Sie Zeit aus, was in der Regel die Standardeinstellung ist. |
Währung, Buchhaltung |
Währung |
In Access speichert der Datentyp Currency Daten als 8-Byte-Zahlen mit Genauigkeit bis zu vier Dezimalstellen und wird verwendet, um Finanzdaten zu speichern und das Runden von Werten zu verhindern. |
Wählen Sie Währung aus, was in der Regel die Standardeinstellung ist. |
Boolesch |
Ja/Nein |
Access verwendet -1 für alle Ja-Werte und 0 für alle Nein-Werte, während Excel 1 für alle TRUE-Werte und 0 für alle FALSE-Werte verwendet. |
Wählen Sie Ja/Nein aus, wodurch die zugrunde liegenden Werte automatisch konvertiert werden. |
Link |
Link |
Ein Link in Excel und Access enthält eine URL oder Webadresse, auf die Sie klicken und folgen können. |
Wählen Sie Hyperlink aus, andernfalls verwendet Access möglicherweise standardmäßig den Datentyp Text. |
Sobald sich die Daten in Access befindet, können Sie die Excel-Daten löschen. Vergessen Sie nicht, die ursprüngliche Excel-Arbeitsmappe zuerst zu sichern, bevor Sie sie löschen.
Weitere Informationen finden Sie im Access-Hilfethema Importieren oder Verknüpfen von Daten in einer Excel-Arbeitsmappe.
Automatisches Anfügen von Daten auf einfache Weise
Ein häufiges Problem, das Excel-Benutzer haben, ist das Anfügen von Daten mit den gleichen Spalten an ein großes Arbeitsblatt. Beispielsweise verfügen Sie möglicherweise über eine Lösung für die Ressourcennachverfolgung, die in Excel begonnen hat, jetzt aber dateien aus vielen Arbeitsgruppen und Abteilungen umfasst. Diese Daten können sich in verschiedenen Arbeitsblättern und Arbeitsmappen oder in Textdateien befinden, die Datenfeeds aus anderen Systemen sind. Es gibt keinen Benutzeroberflächenbefehl oder eine einfache Möglichkeit, ähnliche Daten in Excel anzufügen.
Die beste Lösung besteht darin, Access zu verwenden, in dem Sie Mithilfe des Assistenten zum Importieren von Kalkulationstabellen ganz einfach Daten in eine Tabelle importieren und anfügen können. Darüber hinaus können Sie viele Daten an eine Tabelle anfügen. Sie können die Importvorgänge speichern, sie als geplante Microsoft Outlook-Aufgaben hinzufügen und sogar Makros verwenden, um den Prozess zu automatisieren.
Schritt 2: Normalisieren von Daten mithilfe des Tabellenanalyse-Assistenten
Auf den ersten Blick mag es eine entmutigende Aufgabe erscheinen, den Prozess der Normalisierung Ihrer Daten zu durchlaufen. Glücklicherweise ist das Normalisieren von Tabellen in Access ein Prozess, der dank des Tabellenanalyse-Assistenten viel einfacher ist.
1. Ausgewählte Spalten in eine neue Tabelle ziehen und automatisch Beziehungen erstellen
2. Verwenden Sie Schaltflächenbefehle, um eine Tabelle umzubenennen, einen Primärschlüssel hinzuzufügen, eine vorhandene Spalte zu einem Primärschlüssel zu machen und die letzte Aktion rückgängig zu machen.
Mit diesem Assistenten können Sie folgende Aktionen ausführen:
-
Konvertieren Sie eine Tabelle in eine Gruppe kleinerer Tabellen, und erstellen Sie automatisch eine Primär- und Fremdschlüsselbeziehung zwischen den Tabellen.
-
Fügen Sie einem vorhandenen Feld, das eindeutige Werte enthält, einen Primärschlüssel hinzu, oder erstellen Sie ein neues ID-Feld, das den Datentyp AutoWert verwendet.
-
Erstellen Sie automatisch Beziehungen, um die referenzielle Integrität mit kaskadierenden Updates zu erzwingen. Kaskadierende Löschvorgänge werden nicht automatisch hinzugefügt, um das versehentliche Löschen von Daten zu verhindern, aber Sie können später problemlos kaskadierende Löschvorgänge hinzufügen.
-
Durchsuchen Sie neue Tabellen nach redundanten oder doppelten Daten (z. B. denselben Kunden mit zwei unterschiedlichen Telefonnummern), und aktualisieren Sie diese nach Bedarf.
-
Sichern Sie die ursprüngliche Tabelle, und benennen Sie sie um, indem Sie "_OLD" an ihren Namen anfügen. Anschließend erstellen Sie eine Abfrage, die die ursprüngliche Tabelle mit dem ursprünglichen Tabellennamen rekonstruiert, sodass alle vorhandenen Formulare oder Berichte, die auf der ursprünglichen Tabelle basieren, mit der neuen Tabellenstruktur funktionieren.
Weitere Informationen finden Sie unter Normalisieren Ihrer Daten mithilfe von Table Analyzer.
Schritt 3: Herstellen einer Verbindung mit Access-Daten aus Excel
Nachdem die Daten in Access normalisiert wurden und eine Abfrage oder Tabelle erstellt wurde, die die ursprünglichen Daten rekonstruiert, ist es eine einfache Sache, eine Verbindung mit den Access-Daten aus Excel herzustellen. Ihre Daten befinden sich jetzt in Access als externe Datenquelle und können daher über eine Datenverbindung mit der Arbeitsmappe verbunden werden. Dabei handelt es sich um einen Informationscontainer, der zum Suchen, Anmelden bei und Zugreifen auf die externe Datenquelle verwendet wird. Verbindungsinformationen werden in der Arbeitsmappe gespeichert und können auch in einer Verbindungsdatei gespeichert werden, z. B. in einer ODC-Datei (Odc-Dateierweiterung) oder in einer Datenquellennamendatei (DSN-Erweiterung). Nachdem Sie eine Verbindung mit externen Daten hergestellt haben, können Sie Ihre Excel-Arbeitsmappe auch automatisch aus Access aktualisieren (oder aktualisieren), wenn die Daten in Access aktualisiert werden.
Weitere Informationen finden Sie unter Importieren von Daten aus externen Datenquellen (Power Query).
Abrufen Ihrer Daten in Access
Dieser Abschnitt führt Sie durch die folgenden Phasen der Normalisierung Ihrer Daten: Aufteilen von Werten in den Spalten Salesperson und Address in die unteilbarsten Teile, Trennen verwandter Themen in eigene Tabellen, Kopieren und Einfügen dieser Tabellen aus Excel in Access, Erstellen von Schlüsselbeziehungen zwischen den neu erstellten Access-Tabellen und Erstellen und Ausführen einer einfachen Abfrage in Access, um Informationen zurückzugeben.
Beispieldaten in nicht normalisierter Form
Das folgende Arbeitsblatt enthält nicht atomare Werte in den Spalten Salesperson und Address. Beide Spalten sollten in zwei oder mehr separate Spalten aufgeteilt werden. Dieses Arbeitsblatt enthält auch Informationen zu Verkäufern, Produkten, Kunden und Bestellungen. Diese Informationen sollten auch weiter nach Thema in separate Tabellen aufgeteilt werden.
Verkäufer |
Auftrags-ID |
Bestelldatum |
Produkt-ID |
Qty |
Preis |
Customer Name |
Address |
Telefon |
---|---|---|---|---|---|---|---|---|
Li, Yale |
2349 |
3/4/09 |
C-789 |
3 |
7,00 USD |
Fourth Coffee |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Li, Yale |
2349 |
3/4/09 |
C-795 |
6 |
9,75 USD |
Fourth Coffee |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Adams, Ellen |
2350 |
3/4/09 |
A-2275 |
2 |
16,75 USD |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Adams, Ellen |
2350 |
3/4/09 |
F-198 |
6 |
5,25 USD |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Adams, Ellen |
2350 |
3/4/09 |
B-205 |
1 |
4,50 USD |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Hance, Jim |
2351 |
3/4/09 |
C-795 |
6 |
9,75 USD |
Contoso, Ltd. |
2302 Harvard Ave Bellevue, WA 98227 |
425-555-0222 |
Hance, Jim |
2352 |
3/5/09 |
A-2275 |
2 |
16,75 USD |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Hance, Jim |
2352 |
3/5/09 |
D-4420 |
3 |
7,25 USD |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Koch, Reed |
2353 |
3/7/09 |
A-2275 |
6 |
16,75 USD |
Fourth Coffee |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Koch, Reed |
2353 |
3/7/09 |
C-789 |
5 |
7,00 USD |
Fourth Coffee |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Informationen in ihren kleinsten Teilen: atomische Daten
Wenn Sie mit den Daten in diesem Beispiel arbeiten, können Sie den Befehl Text in Spalte in Excel verwenden, um die "atomischen" Teile einer Zelle (z. B. Straße, Stadt, Bundesland und Postleitzahl) in einzelne Spalten zu trennen.
Die folgende Tabelle zeigt die neuen Spalten im selben Arbeitsblatt, nachdem sie aufgeteilt wurden, um alle Werte atomar zu machen. Beachten Sie, dass die Informationen in der Spalte Salesperson in Die Spalten Nachname und Vorname unterteilt wurden und dass die Informationen in der Spalte Adresse in die Spalten Straße, Ort, Bundesland und Postleitzahl aufgeteilt wurden. Diese Daten befinden sich in "erster Normalform".
Nachname |
Vorname |
|
Straße |
Ort |
Bundesstaat |
PLZ |
---|---|---|---|---|---|---|
Li |
Yale |
2302 Harvard Ave |
Wiesbaden |
WA |
98227 |
|
Adams |
Ellen |
1025 Columbia Circle |
Köln |
WA |
98234 |
|
Hance |
Jim |
2302 Harvard Ave |
Wiesbaden |
WA |
98227 |
|
Koch |
Schilfrohr |
7007 Cornell St Redmond |
Redmond |
WA |
98199 |
Aufteilen von Daten in organisierte Themen in Excel
Die folgenden Tabellen mit Beispieldaten zeigen die gleichen Informationen aus dem Excel-Arbeitsblatt, nachdem es in Tabellen für Verkäufer, Produkte, Kunden und Bestellungen aufgeteilt wurde. Das Tabellendesign ist nicht endgültig, aber auf dem richtigen Weg.
Die Tabelle Salespersons enthält nur Informationen zum Vertriebspersonal. Beachten Sie, dass jeder Datensatz über eine eindeutige ID (SalesPerson-ID) verfügt. Der Wert salesPerson ID wird in der Tabelle Orders verwendet, um Bestellungen mit Vertriebsmitarbeitern zu verbinden.
Verkäufer |
||
---|---|---|
Vertriebsmitarbeiter-ID |
Nachname |
Vorname |
101 |
Li |
Yale |
103 |
Adams |
Ellen |
105 |
Hance |
Jim |
107 |
Koch |
Schilfrohr |
Die Tabelle Products enthält nur Informationen zu Produkten. Beachten Sie, dass jeder Datensatz über eine eindeutige ID (Produkt-ID) verfügt. Der Wert "Produkt-ID" wird verwendet, um Produktinformationen mit der Tabelle "Bestelldetails" zu verbinden.
Produkte |
|
---|---|
Produkt-ID |
Preis |
A-2275 |
16.75 |
B-205 |
4.50 |
C-789 |
7,00 |
C-795 |
9.75 |
D-4420 |
7.25 |
F-198 |
5,25 |
Die Tabelle Customers enthält nur Informationen zu Kunden. Beachten Sie, dass jeder Datensatz über eine eindeutige ID (Kunden-ID) verfügt. Der Wert der Kunden-ID wird verwendet, um Kundeninformationen mit der Tabelle "Bestellungen" zu verbinden.
Customers |
||||||
---|---|---|---|---|---|---|
Kunden-ID |
Name |
Straße |
Ort |
Bundesstaat |
PLZ |
Telefon |
1001 |
Contoso, Ltd. |
2302 Harvard Ave |
Wiesbaden |
WA |
98227 |
425-555-0222 |
1003 |
Adventure Works |
1025 Columbia Circle |
Köln |
WA |
98234 |
425-555-0185 |
1005 |
Fourth Coffee |
7007 Cornell St |
Redmond |
WA |
98199 |
425-555-0201 |
Die Tabelle Orders enthält Informationen zu Bestellungen, Verkäufern, Kunden und Produkten. Beachten Sie, dass jeder Datensatz über eine eindeutige ID (Bestell-ID) verfügt. Einige der Informationen in dieser Tabelle müssen in eine zusätzliche Tabelle aufgeteilt werden, die Auftragsdetails enthält, sodass die Tabelle Orders nur vier Spalten enthält: die eindeutige Auftrags-ID, das Bestelldatum, die Vertriebsmitarbeiter-ID und die Kunden-ID. Die hier gezeigte Tabelle wurde noch nicht in die Tabelle Bestelldetails aufgeteilt.
Bestellungen |
|||||
---|---|---|---|---|---|
Auftrags-ID |
Bestelldatum |
SalesPerson-ID |
Kunden-ID |
Produkt-ID |
Qty |
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 |
Auftragsdetails, z. B. die Produkt-ID und die Menge, werden aus der Tabelle Orders verschoben und in einer Tabelle mit dem Namen Auftragsdetails gespeichert. Denken Sie daran, dass es 9 Bestellungen gibt, daher ist es sinnvoll, dass diese Tabelle 9 Datensätze enthält. Beachten Sie, dass die Tabelle Orders eine eindeutige ID (Bestell-ID) aufweist, auf die in der Tabelle Bestelldetails verwiesen wird.
Der endgültige Entwurf der Tabelle Orders sollte wie folgt aussehen:
Bestellungen |
|||
---|---|---|---|
Auftrags-ID |
Bestelldatum |
SalesPerson-ID |
Kunden-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 |
Die Tabelle "Bestelldetails" enthält keine Spalten, die eindeutige Werte erfordern (d. h., es gibt keinen Primärschlüssel), sodass es in Ordnung ist, dass eine oder alle Spalten "redundante" Daten enthalten. Allerdings sollten keine zwei Datensätze in dieser Tabelle vollständig identisch sein (diese Regel gilt für jede Tabelle in einer Datenbank). In dieser Tabelle sollten 17 Datensätze vorhanden sein, die jeweils einem Produkt in einer individuellen Bestellung entsprechen. In Der Reihenfolge 2349 bilden beispielsweise drei C-789-Produkte einen der beiden Teile des gesamten Auftrags.
Die Tabelle "Bestelldetails" sollte daher wie folgt aussehen:
Auftragsdetails |
||
---|---|---|
Auftrags-ID |
Produkt-ID |
Qty |
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 |
Kopieren und Einfügen von Daten aus Excel in Access
Nachdem die Informationen zu Vertriebsmitarbeitern, Kunden, Produkten, Bestellungen und Bestelldetails in separate Themen in Excel aufgeteilt wurden, können Sie diese Daten direkt in Access kopieren, wo sie zu Tabellen werden.
Erstellen von Beziehungen zwischen den Access-Tabellen und Ausführen einer Abfrage
Nachdem Sie Ihre Daten in Access verschoben haben, können Sie Beziehungen zwischen Tabellen erstellen und dann Abfragen erstellen, um Informationen zu verschiedenen Themen zurückzugeben. Sie können beispielsweise eine Abfrage erstellen, die die Auftrags-ID und die Namen der Verkäufer für Bestellungen zurückgibt, die zwischen dem 05.03.09 und dem 08.09. eingegeben wurden.
Darüber hinaus können Sie Formulare und Berichte erstellen, um die Dateneingabe und Vertriebsanalyse zu vereinfachen.
Benötigen Sie weitere Hilfe?
Sie können jederzeit einen Experten in der Excel Tech Community fragen oder Unterstützung in den Communities erhalten.