Verschieben Sie Daten von Excel nach Access
Hinweis: Microsoft Access unterstützt das Importieren von Excel-Daten mit einer angewendeten Vertraulichkeitsbezeichnung nicht. Als Problemumgehung können Sie das Etikett vor dem Importieren entfernen und das Etikett nach dem Importieren erneut anwenden. Weitere Informationen finden Sie unter Anwenden von Vertraulichkeitsbezeichnungen auf Ihre Dateien und E-Mails in Office .
Dieser Artikel zeigt Ihnen, wie Sie Ihre Daten von 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 am besten zum Berechnen, Analysieren und Visualisieren von Daten.
In den beiden Artikeln „ Using Access or Excel to manage your data " und „ Die 10 wichtigsten 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 man Excel und Access zusammen verwendet, um eine praktische Lösung zu schaffen.
Wenn Sie Daten von Excel nach Access verschieben, gibt es drei grundlegende Schritte für den Prozess.
Hinweis: Informationen zur Datenmodellierung und Beziehungen in Access finden Sie unter Grundlagen des Datenbankentwurfs .
Schritt 1: Importieren Sie Daten aus Excel in Access
Das Importieren von Daten ist ein Vorgang, der viel reibungsloser ablaufen kann, wenn Sie sich etwas Zeit nehmen, um Ihre Daten vorzubereiten und zu bereinigen. Das Importieren von Daten ist wie ein Umzug in ein neues Zuhause. Wenn Sie Ihre Besitztümer vor dem Umzug ausräumen und organisieren, ist das Einleben in Ihrem neuen Zuhause viel einfacher.
Bereinigen Sie Ihre Daten vor dem Import
Bevor Sie Daten in Access importieren, sollten Sie in Excel Folgendes tun:
Konvertieren Sie Zellen, die nicht-atomare Daten (d. h. mehrere Werte in einer Zelle) enthalten, in mehrere Spalten. Beispielsweise sollte eine Zelle in einer Spalte „Fertigkeiten", die mehrere Fertigkeitswerte enthält, wie „C#-Programmierung", „VBA-Programmierung" und „Webdesign", in separate Spalten aufgeteilt werden, die jeweils nur einen Fertigkeitswert enthalten.
Verwenden Sie den TRIM-Befehl, um führende, nachgestellte und mehrfach eingebettete Leerzeichen zu entfernen.
Entfernen Sie nicht druckbare 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 Zahlen, die als Text formatiert sind, oder Datumsangaben, die als Zahlen formatiert sind.
Weitere Informationen finden Sie in den folgenden Excel-Hilfethemen:
Filtern Sie nach eindeutigen Werten oder entfernen Sie doppelte Werte
Als Text gespeicherte Datumsangaben in Datumsangaben umwandeln
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, sollten Sie die Verwendung eines Drittanbieters in Erwägung ziehen. Suchen Sie für weitere Informationen in Ihrer bevorzugten Suchmaschine in Ihrem Webbrowser nach „Datenbereinigungssoftware" oder „Datenqualität".
Wählen Sie beim Importieren den besten Datentyp
Während des Importvorgangs in Access möchten Sie gute Entscheidungen treffen, damit Sie (wenn überhaupt) nur wenige Konvertierungsfehler erhalten, die ein manuelles Eingreifen erfordern. Die folgende Tabelle fasst zusammen, wie Excel-Zahlenformate und Access-Datentypen konvertiert werden, wenn Sie Daten aus Excel in Access importieren, und bietet einige Tipps zu den besten Datentypen zur Auswahl im Assistenten zum Importieren von Tabellenkalkulationen.
Excel-Zahlenformat | Datentyp zugreifen | Kommentare | Beste Übung |
|---|---|---|---|
Text | Texte, Notizen | 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 , um das Abschneiden von Daten zu vermeiden. |
Zahl, Prozent, Bruch, Wissenschaftlich | Nummer | Access verfügt über einen Zahlendatentyp, der je nach Feldgrößeneigenschaft variiert (Byte, Ganzzahl, lange Ganzzahl, Einfach, Doppelt, Dezimal). | Wählen Sie Double , um Datenkonvertierungsfehler zu vermeiden. |
Datum | Datum | Access und Excel verwenden beide dieselbe fortlaufende Datumsnummer 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 1904-Datumssystem (das in Excel für den 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 . |
Zeit | Zeit | Access und Excel speichern beide Zeitwerte unter Verwendung desselben Datentyps. | Wählen Sie Zeit , was normalerweise die Standardeinstellung ist. |
Währung, Buchhaltung | Währung | In Access speichert der Datentyp „Währung" Daten als 8-Byte-Zahlen mit einer Genauigkeit von vier Dezimalstellen und wird verwendet, um Finanzdaten zu speichern und das Runden von Werten zu verhindern. | Wählen Sie Währung , 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 TRUE-Werte und 0 für alle FALSE-Werte verwendet. | Wählen Sie Ja/Nein , wodurch die zugrunde liegenden Werte automatisch konvertiert werden. |
Hyperlinks | Hyperlinks | Ein Hyperlink in Excel und Access enthält eine URL oder Webadresse, auf die Sie klicken und der Sie folgen können. | Wählen Sie Hyperlink , 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 oder Verknüpfen von Daten in einer Excel-Arbeitsmappe .
Daten ganz einfach automatisch anhängen
Ein häufiges Problem von Excel-Benutzern ist das Anhängen von Daten mit denselben Spalten in ein großes Arbeitsblatt. Beispielsweise haben Sie möglicherweise eine Asset-Tracking-Lösung, die in Excel begann, jetzt aber so gewachsen ist, dass sie Dateien aus vielen Arbeitsgruppen und Abteilungen enthält. 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 anzuhängen.
Die beste Lösung ist die Verwendung von Access, wo Sie Daten einfach importieren und in eine Tabelle einfügen können, indem Sie den Assistenten zum Importieren von Tabellenkalkulationen verwenden. Außerdem können Sie viele Daten in eine Tabelle einfü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 Sie Daten mithilfe des Tabellenanalyse-Assistenten
Auf den ersten Blick mag es eine entmutigende Aufgabe sein, den Prozess der Normalisierung Ihrer Daten zu durchlaufen. Glücklicherweise ist das Normalisieren von Tabellen in Access dank des Tabellenanalyse-Assistenten viel einfacher.
1. Ziehen Sie ausgewählte Spalten in eine neue Tabelle und erstellen Sie automatisch Beziehungen
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 Folgendes tun:
Wandeln Sie eine Tabelle in einen Satz kleinerer Tabellen um 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 Aktualisierungen zu erzwingen. Kaskadierende Löschungen 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 anhängen. 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 Daten mit dem Table Analyzer normalisieren .
Schritt 3: Stellen Sie eine Verbindung her, um auf Daten aus Excel zuzugreifen
Nachdem die Daten in Access normalisiert und eine Abfrage oder Tabelle erstellt wurde, die die ursprünglichen Daten rekonstruiert, ist es einfach, eine Verbindung zu den Access-Daten von Excel herzustellen. Ihre Daten befinden sich jetzt in Access als externe Datenquelle und können daher über eine Datenverbindung mit der Arbeitsmappe verbunden werden, bei der es sich um einen Informationscontainer handelt, 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 in einer Datenquellennamendatei (Erweiterung .dsn). 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) .
Holen Sie sich Ihre Daten in Access
Dieser Abschnitt führt Sie durch die folgenden Phasen der Normalisierung Ihrer Daten: Zerlegen von Werten in den Spalten „Verkäufer" und „Adresse" in ihre kleinsten Teile, Trennen verwandter Themen in ihre eigenen Tabellen, Kopieren und Einfügen dieser Tabellen aus Excel in Access, Erstellen von Schlüsselbeziehungen zwischen den neu erstellte 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 Themen in separate Tabellen aufgeteilt werden.
Verkäufer | Auftragsnummer | Bestelldatum | Produkt ID | Menge | Preis | Kundenname | Adresse | Telefon |
|---|---|---|---|---|---|---|---|---|
Li, Yale | 2349 | 4.3.09 | C-789 | 3 | $7,00 | Vierter Kaffee | 7007 Cornell St. Redmond, WA 98199 | 425-555-0201 |
Li, Yale | 2349 | 4.3.09 | C-795 | 6 | 9,75 $ | Vierter Kaffee | 7007 Cornell St. Redmond, WA 98199 | 425-555-0201 |
Adams, Ellen | 2350 | 4.3.09 | A-2275 | 2 | 16,75 $ | Abenteuer funktioniert | 1025 Columbia Circle Kirkland, WA 98234 | 425-555-0185 |
Adams, Ellen | 2350 | 4.3.09 | F-198 | 6 | 5,25 $ | Abenteuer funktioniert | 1025 Columbia Circle Kirkland, WA 98234 | 425-555-0185 |
Adams, Ellen | 2350 | 4.3.09 | B-205 | 1 | 4,50 $ | Abenteuer funktioniert | 1025 Columbia Circle Kirkland, WA 98234 | 425-555-0185 |
Hance, Jim | 2351 | 4.3.09 | C-795 | 6 | 9,75 $ | Contoso, Ltd. | 2302 Harvard Avenue Bellevue, WA 98227 | 425-555-0222 |
Hance, Jim | 2352 | 05.03.09 | A-2275 | 2 | 16,75 $ | Abenteuer funktioniert | 1025 Columbia Circle Kirkland, WA 98234 | 425-555-0185 |
Hance, Jim | 2352 | 05.03.09 | D-4420 | 3 | 7,25 $ | Abenteuer funktioniert | 1025 Columbia Circle Kirkland, WA 98234 | 425-555-0185 |
Koch, Schied | 2353 | 07.03.09 | A-2275 | 6 | 16,75 $ | Vierter Kaffee | 7007 Cornell St. Redmond, WA 98199 | 425-555-0201 |
Koch, Schied | 2353 | 07.03.09 | C-789 | 5 | $7,00 | Vierter Kaffee | 7007 Cornell St. Redmond, WA 98199 | 425-555-0201 |
Information in ihren kleinsten Teilen: 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 aufzuteilen.
Die folgende Tabelle zeigt die neuen Spalten in demselben Arbeitsblatt, 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 und die Informationen in der Spalte Adresse in die Spalten Straße, Ort, Bundesland und Postleitzahl aufgeteilt wurden. Diese Daten liegen in "erster Normalform" vor.
Nachname | Vorname |
| Adresse | Stadt | Bundesland | Postleitzahl |
|---|---|---|---|---|---|---|
Li | Jale | 2302 Harvard Avenue | Bellevue | WA | 98227 | |
Adams | Ellen | 1025 Columbia-Kreis | Kirkland | WA | 98234 | |
Hance | Jim | 2302 Harvard Avenue | Bellevue | WA | 98227 | |
Koch | Schilf | 7007 Cornell Street Redmond | Redmond | WA | 98199 |
Aufteilen von Daten in organisierte Themen in Excel
Die folgenden Tabellen mit Beispieldaten zeigen dieselben Informationen aus dem Excel-Arbeitsblatt, nachdem es in Tabellen für Verkäufer, Produkte, Kunden und Bestellungen aufgeteilt wurde. Das Tischdesign ist noch nicht final, aber auf dem richtigen Weg.
Die Tabelle Salespersons enthält nur Informationen zum Verkaufspersonal. Beachten Sie, dass jeder Datensatz eine eindeutige ID (SalesPerson ID) hat. Der SalesPerson-ID-Wert wird in der Orders-Tabelle verwendet, um Bestellungen mit Verkäufern zu verknüpfen.
Verkäufer | ||
|---|---|---|
Verkäufer-ID | Nachname | Vorname |
101 | Li | Jale |
103 | Adams | Ellen |
105 | Hance | Jim |
107 | Koch | Schilf |
Die Tabelle Produkte enthält nur Informationen zu Produkten. Beachten Sie, dass jeder Datensatz eine eindeutige ID (Produkt-ID) hat. Der Produkt-ID-Wert wird verwendet, um Produktinformationen mit der Bestelldetails-Tabelle 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 Customers-Tabelle 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 Orders-Tabelle zu verbinden.
Kunden | ||||||
|---|---|---|---|---|---|---|
Kundennummer | Name | Adresse | Stadt | Bundesland | Postleitzahl | Telefon |
1001 | Contoso, Ltd. | 2302 Harvard Avenue | Bellevue | WA | 98227 | 425-555-0222 |
1003 | Abenteuer funktioniert | 1025 Columbia-Kreis | Kirkland | WA | 98234 | 425-555-0185 |
1005 | Vierter Kaffee | 7007 Cornellstr | Redmond | WA | 98199 | 425-555-0201 |
Die Tabelle Bestellungen enthält Informationen zu Bestellungen, Verkäufern, Kunden und Produkten. Beachten Sie, dass jeder Datensatz eine eindeutige ID (Bestell-ID) hat. Einige der Informationen in dieser Tabelle müssen in eine zusätzliche Tabelle aufgeteilt werden, die Bestelldetails enthält, sodass 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.
Aufträge | |||||
|---|---|---|---|---|---|
Auftragsnummer | Bestelldatum | Verkäufer-ID | Kundennummer | Produkt ID | Menge |
2349 | 4.3.09 | 101 | 1005 | C-789 | 3 |
2349 | 4.3.09 | 101 | 1005 | C-795 | 6 |
2350 | 4.3.09 | 103 | 1003 | A-2275 | 2 |
2350 | 4.3.09 | 103 | 1003 | F-198 | 6 |
2350 | 4.3.09 | 103 | 1003 | B-205 | 1 |
2351 | 4.3.09 | 105 | 1001 | C-795 | 6 |
2352 | 05.03.09 | 105 | 1003 | A-2275 | 2 |
2352 | 05.03.09 | 105 | 1003 | D-4420 | 3 |
2353 | 07.03.09 | 107 | 1005 | A-2275 | 6 |
2353 | 07.03.09 | 107 | 1005 | C-789 | 5 |
Bestelldetails wie die Produkt-ID und die Menge werden aus der Orders-Tabelle verschoben und in einer Tabelle namens Order Details gespeichert. Denken Sie daran, dass es 9 Bestellungen gibt, also macht es Sinn, dass es 9 Datensätze in dieser Tabelle gibt. Beachten Sie, dass die Orders-Tabelle eine eindeutige ID (Order ID) hat, auf die von der Order Details-Tabelle verwiesen wird.
Das endgültige Design der Orders-Tabelle sollte wie folgt aussehen:
Aufträge | |||
|---|---|---|---|
Auftragsnummer | Bestelldatum | Verkäufer-ID | Kundennummer |
2349 | 4.3.09 | 101 | 1005 |
2350 | 4.3.09 | 103 | 1003 |
2351 | 4.3.09 | 105 | 1001 |
2352 | 05.03.09 | 105 | 1003 |
2353 | 07.03.09 | 107 | 1005 |
Die Tabelle „Bestelldetails" enthält keine Spalten, die eindeutige Werte erfordern (d. h. es gibt keinen Primärschlüssel), daher können einige oder alle Spalten „redundante" Daten enthalten. Allerdings sollten keine zwei Datensätze in dieser Tabelle völlig identisch sein (diese Regel gilt für alle Tabellen in einer Datenbank). In dieser Tabelle sollten 17 Datensätze vorhanden sein – jeder entspricht einem Produkt in einer einzelnen Bestellung. Beispielsweise umfassen in Bestellung 2349 drei C-789-Produkte einen der beiden Teile der gesamten Bestellung.
Die Tabelle mit den Bestelldetails sollte daher wie folgt aussehen:
Bestelldetails | ||
|---|---|---|
Auftragsnummer | 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 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. Beispielsweise können Sie eine Abfrage erstellen, die die Bestell-ID und die Namen der Verkäufer für Bestellungen zurückgibt, die zwischen dem 05.03.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 Unterstützung in der Answers-Community erhalten.
No comments:
Post a Comment