Hinweis
Microsoft Access unterstützt das Importieren von Excel-Daten mit einer angewendeten Vertraulichkeitsbezeichnung nicht. Um dieses Problem zu umgehen, können Sie die Bezeichnung vor dem Importieren entfernen und nach dem Importieren erneut anwenden. Weitere Informationen finden Sie unter Anwenden von Vertraulichkeitsbezeichnungen auf Ihre Dateien und E-Mails in Office.
In diesem Artikel wird gezeigt, wie Sie Ihre Daten von Excel nach Access verschieben und 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 am besten zum Berechnen, Analysieren und Visualisieren von Daten geeignet ist.
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 erörtert, welches Programm für eine bestimmte Aufgabe am besten geeignet ist und wie Sie Excel und Access zusammen verwenden können, um eine praktische Lösung zu schaffen.
Wenn Sie Daten aus Excel nach Access verschieben, umfasst der Prozess drei grundlegende Schritte.
Hinweis
Informationen zur Datenmodellierung und zu Beziehungen in Access finden Sie unter Grundlagen des Datenbankentwurfs.
Schritt 1: Importieren von Daten aus Excel in Access
Das Importieren von Daten kann wesentlich reibungsloser ablaufen, wenn Sie sich etwas Zeit nehmen, um Ihre Daten vorzubereiten und zu sauber zu machen. Das Importieren von Daten ist wie der Umzug in ein neues Zuhause. Wenn Sie Ihre Besitztümer vor dem Umzug sauber ausräumen und organisieren, ist es viel einfacher, sich in Ihrem neuen Zuhause einzuleben.
Bereinigen Ihrer Daten vor dem Import
Bevor Sie Daten in Access importieren, empfiehlt es sich in Excel, Folgendes zu tun:
- Konvertieren von Zellen, die nicht atomare Daten enthalten (d. h. mehrere Werte in einer Zelle) in mehrere Spalten. Beispielsweise sollte eine Zelle in einer Spalte "Fähigkeiten", die mehrere Fähigkeitswerte enthält, wie z. B. "C#-Programmierung", "VBA-Programmierung" und "Webdesign", in separate Spalten unterteilt werden, die jeweils nur einen Fähigkeitswert enthalten.
- Verwenden Sie den Befehl GLÄTTEN, um führende, nachgestellte und mehrere eingebettete Leerzeichen zu entfernen.
- Entfernen nicht druckbarer Zeichen.
- Suchen und beheben Sie Rechtschreib- und Zeichensetzungsfehler.
- Entfernen Sie doppelte Zeilen oder doppelte Felder.
- Stellen Sie sicher, dass Datenspalten keine gemischten Formate enthalten, insbesondere keine als Text formatierten Zahlen oder Datumsangaben, die als Zahlen formatiert sind.
Weitere Informationen finden Sie in den folgenden Excel-Hilfethemen:
- Die zehn besten Methoden zum Bereinigen von Daten
- Filtern nach eindeutigen Werten und Entfernen von doppelten Werten
- Konvertieren von Zahlen, die als Text gespeichert wurden
- Konvertieren von Datumsangaben, die als Text gespeichert wurden, in Datumswerte
Hinweis
Wenn Ihre Anforderungen an die Datenbereinigung komplex sind oder Sie nicht über die Zeit oder die Ressourcen verfügen, um den Prozess selbst zu automatisieren, können Sie die Zusammenarbeit mit einem Drittanbieter in Betracht ziehen. Weitere Informationen finden Sie, wenn Sie in Ihrem Webbrowser nach "Datenbereinigungssoftware" oder "Datenqualität" bei Ihrer bevorzugten Suchmaschine suchen.
Auswählen des optimalen Datentyps beim Import
Während des Importvorgangs in Access sollten Sie gute Entscheidungen treffen, damit Sie nur wenige (wenn überhaupt) Konvertierungsfehler erhalten, die ein manuelles Eingreifen erfordern. In der folgenden Tabelle wird zusammengefasst, wie Excel-Zahlenformate und Access-Datentypen beim Importieren von Daten aus Excel in Access konvertiert werden. Außerdem erhalten Sie einige Tipps zu den besten Datentypen, die Sie im Import-Kalkulationstabellen-Assistenten auswählen können.
| Excel-Zahlenformat | Access-Datentyp | Kommentare | Bewährte Methode |
|---|---|---|---|
| Text | Text, Memo | Der Datentyp "Access-Text" speichert alphanumerische Daten mit bis zu 255 Zeichen. Der Datentyp "Access Memo" speichert alphanumerische Daten mit 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 Datentyp "Zahl", der basierend auf einer Feldgrößeneigenschaft (Byte, Ganzzahl, Long Integer, Single, Double, Dezimal) variiert. | Wählen Sie "Doppelt", um Fehler bei der Datenkonvertierung zu vermeiden. |
| Datum | Datum | Sowohl Access als auch Excel verwenden dieselbe fortlaufende Datumszahl zum Speichern von Datumsangaben. 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 Datumssystem 1904 (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 sowie Importieren oder Verknüpfen von Daten in einer Excel-Arbeitsmappe. |
Wählen Sie Datum aus. |
| Zeit | Zeit | Access und Excel speichern Zeitwerte mithilfe desselben Datentyps. | Wählen Sie "Zeit" aus, was normalerweise die Standardeinstellung ist. |
| Währung, Buchhaltung | Währung | In Access speichert der Datentyp "Währung" Daten als 8-Byte-Zahlen mit Genauigkeit auf vier Dezimalstellen und wird zum Speichern von Finanzdaten und zum Verhindern der Rundung von Werten verwendet. | Wählen Sie "Währung" aus, was normalerweise 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 WAHR-Werte und 0 für alle FALSCH-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 der Sie folgen können. | Wählen Sie "Link" aus. Andernfalls verwendet Access möglicherweise standardmäßig den Datentyp "Text". |
Sobald sich die Daten in Access befinden, 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 von oder Verknüpfen mit Daten in einer Excel-Arbeitsmappe.
Automatisches Anfügen von Daten auf einfache Weise
Ein häufiges Problem für Excel-Benutzer ist das Anfügen von Daten mit denselben Spalten in einem einzigen großen Arbeitsblatt. Vielleicht verfügen Sie über eine Lösung für die Bestandsverfolgung, die ursprünglich in Excel verwendet wurde, nun aber Dateien aus vielen Arbeitsgruppen und Abteilungen enthält. Diese Daten können sich in anderen Arbeitsblättern und Arbeitsmappen oder in Textdateien befinden, bei denen es sich um Datenfeeds von anderen Systemen handelt. Es gibt keinen Benutzeroberflächenbefehl oder eine einfache Möglichkeit, ähnliche Daten in Excel anzufügen.
Die beste Lösung ist die Verwendung von Access, wo Sie mit dem Import-Assistenten für das Importieren von Tabellenkalkulationstabellen Daten ganz einfach importieren und in einer Tabelle anfügen können. Außerdem können Sie viele Daten in einer 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 mit dem Tabellenanalyse-Assistenten
Auf den ersten Blick mag es wie eine entmutigende Aufgabe erscheinen, den Prozess der Normalisierung Ihrer Daten Schritt für Schritt zu sein. Glücklicherweise ist das Normalisieren von Tabellen in Access dank des Tabellenanalyse-Assistenten viel einfacher.
1. Urheberrecht Ziehen Sie ausgewählte Spalten in eine neue Tabelle und erstellen Sie automatisch Beziehungen
2. Urheberrecht 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
Sie können diesen Assistenten für folgende Aufgaben verwenden:
- Konvertieren Sie eine Tabelle in eine Reihe 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. Löschkaskadierende werden nicht automatisch hinzugefügt, um ein versehentliches Löschen von Daten zu verhindern, aber Sie können später problemlos kaskadierende Löschungen hinzufügen.
- Durchsuchen Sie neue Tabellen nach redundanten oder doppelten Daten (z. B. derselbe Kunde 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. Dann 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 von Daten mit dem Tabellenanalysetools.
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 Originaldaten rekonstruiert, müssen Sie ganz einfach eine Verbindung zu den Access-Daten aus Excel herstellen. 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 Container mit Informationen, der zum Suchen, Anmelden 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 (Office Data Connection) (ODC-Dateinamenerweiterung) oder einer Datenquellennamensdatei (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).
Übertragen Ihrer Daten in Access
In diesem Abschnitt werden Sie durch die folgenden Phasen der Normalisierung von Daten geführt: Aufteilen von Werten in den Spalten "Verkäufer" und "Adresse" in ihre atomarsten Teile, Trennen verwandter Themen in eigenen Tabellen, Kopieren und Einfügen dieser Tabellen aus Excel in Access, Erstellen von Schlüsselbeziehungen zwischen den neu erstellten Access-Tabellen sowie 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 "Verkäufer" und "Adresse". Beide Spalten sollten in zwei oder mehr separate Spalten aufgeteilt werden. Dieses Arbeitsblatt enthält auch Informationen zu Vertriebsmitarbeitern, Produkten, Kunden und Bestellungen. Diese Informationen sollten auch weiter nach Themen in separate Tabellen aufgeteilt werden.
| Verkäufer | Auftrags-ID | Bestelldatum | Produkt-ID | Menge | Preis | Customer Name | Address | Telefon |
|---|---|---|---|---|---|---|---|---|
| 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 |
Information in ihren kleinsten Bestandteilen: atomare Daten
Wenn Sie mit den Daten in diesem Beispiel arbeiten, können Sie den Befehl Text in Spalte in Excel verwenden, um die "atomaren" Teile einer Zelle (z. B. Straße, Ort, Bundesland und Postleitzahl) in einzelne Spalten zu trennen.
In der folgenden Tabelle werden die neuen Spalten im selben Arbeitsblatt angezeigt, nachdem sie aufgeteilt wurden, um alle Werte atomar zu machen. Beachten Sie, dass die Informationen in der Spalte "Verkäufer" in die Spalten "Nachname" und "Vorname" aufgeteilt wurden und dass die Informationen in der Spalte "Adresse" in die Spalten "Straße", "Ort", "Bundesland" und "PLZ" aufgeteilt wurden. Diese Daten sind in "erster Normalform".
| Nachname | Vorname | Straße | Ort | Zustand | ZIP Code |
|---|---|---|---|---|---|
| Li | Yale (Englisch) | 2302 Harvard Ave | Wiesbaden | WA | 98227 |
| Adams | Ellen | 1025 Columbia Circle | Köln | WA | 98234 |
| Hance | Jim | 2302 Harvard Ave | Wiesbaden | WA | 98227 |
| Koch | Schilf | 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 Tischdesign ist noch nicht endgültig, aber auf dem richtigen Weg.
Die Tabelle "Verkäufer" enthält nur Informationen zu den Vertriebsmitarbeitern. Beachten Sie, dass jeder Datensatz eine eindeutige ID (SalesPerson-ID) hat. Der Wert der SalesPerson-ID wird in der Tabelle Orders verwendet, um Bestellungen mit Vertriebsmitarbeitern zu verbinden.
| Verkäufer | ||
|---|---|---|
| Vertriebsmitarbeiter-ID | Nachname | Vorname |
| 101 | Li | Yale (Englisch) |
| 103 | Adams | Ellen |
| 105 | Hance | Jim |
| 107 | Koch | Schilf |
Die Tabelle "Artikel" enthält nur Informationen zu Artikeln. Beachten Sie, dass jeder Datensatz eine eindeutige ID (Produkt-ID) hat. Der Wert der 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 "Kunden" enthält nur Informationen zu Kunden. Beachten Sie, dass jeder Datensatz eine eindeutige ID (Kunden-ID) hat. Der Kunden-ID-Wert wird verwendet, um Kundeninformationen mit der Tabelle Bestellungen zu verbinden.
| Customers | ||||||
|---|---|---|---|---|---|---|
| Kunden-ID | Name | Straße | Ort | Zustand | ZIP Code | 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 Bestellungen enthält Informationen zu Bestellungen, Vertriebsmitarbeitern, Kunden und Produkten. Beachten Sie, dass jeder Datensatz eine eindeutige ID (Auftrags-ID) hat. Einige der Informationen in dieser Tabelle müssen in eine zusätzliche Tabelle mit Bestelldetails aufgeteilt werden, damit die Tabelle Bestellungen nur vier Spalten enthält: die eindeutige Bestell-ID, das Bestelldatum, die Verkäufer-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 | Menge |
| 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 wie die Produkt-ID und die Menge werden aus der Tabelle "Bestellungen" verschoben und in einer Tabelle mit dem Namen "Bestelldetails" 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 "Bestellungen" eine eindeutige ID (Auftrags-ID) hat, auf die in der Tabelle "Bestelldetails" verwiesen wird.
Der endgültige Entwurf der Tabelle "Bestellungen" 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 (es gibt also keinen Primärschlüssel), daher ist es in Ordnung, wenn 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). Diese Tabelle sollte 17 Datensätze enthalten, die jeweils einem Produkt in einem einzelnen Auftrag entsprechen. In der Bestellung 2349 beispielsweise umfassen drei C-789-Produkte einen der beiden Teile der gesamten Bestellung.
Die Tabelle "Bestelldetails" sollte daher wie folgt aussehen:
| Auftragsdetails | ||
|---|---|---|
| Auftrags-ID | Produkt-ID | Menge |
| 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 Verkäufern, Kunden, Produkten, Bestellungen und Bestelldetails in Excel in separate Themen unterteilt 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 die 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 z. B. eine Abfrage erstellen, die die Auftrags-ID und die Namen der Vertriebsmitarbeiter für Bestellungen zurückgibt, die zwischen dem 05.05.09 und dem 08.03.09 eingegeben wurden.
Darüber hinaus können Sie Formulare und Berichte erstellen, um die Dateneingabe und Verkaufsanalyse zu vereinfachen.
Benötigen Sie weitere Hilfe?
Sie können jederzeit einen Experten in der Excel Tech Community fragen oder in Communitys Unterstützung erhalten.