Uwaga: Program Microsoft Access nie obsługuje importowania danych programu Excel z zastosowaną etykietą poufności. Aby obejść ten problem, możesz usunąć etykietę przed zaimportowaniem, a następnie ponownie zastosować etykietę po zaimportowaniu. Aby uzyskać więcej informacji, zobacz Stosowanie etykiet poufności do plików i wiadomości e-mail w pakiecie Office.
W tym artykule pokazano, jak przenieść dane z programu Excel do programu Access i przekonwertować dane na tabele relacyjne, aby umożliwić wspólne korzystanie z programów Microsoft Excel i Access. Podsumowując, program Access najlepiej nadaje się do przechwytywania, przechowywania, tworzenia zapytań i udostępniania danych, a program Excel najlepiej nadaje się do obliczania, analizowania i wizualizowania danych.
Dwa artykuły: Zarządzanie danymi za pomocą programu Access lub Excel oraz 10 głównych powodów korzystania z programu Access z programem Excel, omówienie, który program najlepiej nadaje się do określonego zadania, oraz sposoby używania programów Excel i Access razem w celu utworzenia praktycznego rozwiązania.
Podczas przenoszenia danych z programu Excel do programu Access są trzy podstawowe kroki procesu.
Uwaga: Aby uzyskać informacje na temat modelowania danych i relacji w programie Access, zobacz Podstawowe informacje o projekcie bazy danych.
Krok 1. Importowanie danych z programu Excel do programu Access
Importowanie danych to operacja, która może przebiegać o wiele sprawnie, jeśli przygotowanie i wyczyszczenie danych zajmie trochę czasu. Importowanie danych przypomina przejście do nowego domu. Jeśli oczyścić i zorganizować swoje posiadłości przed przeprowadzką, osiedlając się w nowym domu jest znacznie łatwiejsze.
Czyszczenie danych przed zaimportowaniem
Przed zaimportowaniem danych do programu Access w programie Excel warto wykonać następujące czynności:
-
Konwertuj komórki zawierające dane nie niepodzielne (czyli wiele wartości w jednej komórce) na wiele kolumn. Na przykład komórka w kolumnie "Umiejętności" zawierająca wiele wartości umiejętności, takich jak "programowanie C#", "programowanie VBA" i "Projekt sieci Web", powinna być podzielona na oddzielne kolumny, z których każda zawiera tylko jedną wartość umiejętności.
-
Użyj polecenia USUŃ.ZBĘDNE.ODSTĘPY, aby usunąć spacje wiodące, końcowe i wiele osadzonych.
-
Usuwanie znaków niedrukowych.
-
Znajdowanie i naprawianie błędów pisowni i interpunkcji.
-
Usuwanie zduplikowanych wierszy lub zduplikowanych pól.
-
Upewnij się, że kolumny danych nie zawierają formatów mieszanych, zwłaszcza liczb sformatowanych jako tekst lub daty sformatowane jako liczby.
Aby uzyskać więcej informacji, zobacz następujące tematy pomocy programu Excel:
Uwaga: Jeśli twoje potrzeby w zakresie czyszczenia danych są złożone lub nie masz czasu ani zasobów na samodzielne zautomatyzowanie tego procesu, możesz rozważyć skorzystanie z usług innego dostawcy. Aby uzyskać więcej informacji, wyszukaj frazę "oprogramowanie do oczyszczania danych" lub "jakość danych" w ulubionej wyszukiwarce w przeglądarce internetowej.
Wybieranie najlepszego typu danych podczas importowania
Podczas operacji importowania w programie Access chcesz dokonać dobrych wyborów, aby otrzymać niewiele (jeśli istnieją) błędów konwersji, które będą wymagały ręcznej interwencji. W poniższej tabeli podsumowano sposób konwertowania formatów liczb programu Excel i typów danych programu Access podczas importowania danych z programu Excel do programu Access oraz przedstawiono kilka porad dotyczących najlepszych typów danych do wyboru w Kreatorze importowania arkuszy kalkulacyjnych.
Format liczb w programie Excel |
Typ danych programu Access |
Komentarze |
Najważniejsze wskazówki |
---|---|---|---|
Text (Tekst) |
Tekst, Nota |
Typ danych Tekst programu Access przechowuje dane alfanumeryczne do 255 znaków. Typ danych Nota programu Access przechowuje dane alfanumeryczne do 65 535 znaków. |
Wybierz pozycję Nota , aby uniknąć obcinania danych. |
Liczba, Procent, Ułamek, Naukowy |
Liczba |
Program Access ma jeden typ danych Liczba, który zależy od właściwości Rozmiar pola (Bajt, Liczba całkowita, Liczba całkowita długa, Pojedyncza, Podwójna, Dziesiętna). |
Wybierz pozycję Double (Podwójne ), aby uniknąć błędów konwersji danych. |
Data |
Data |
W programach Access i Excel do przechowywania dat jest używany ten sam numer seryjny. W programie Access zakres dat jest większy: od -657 434 (1 stycznia 100 r.) do 2 958 465 (31 grudnia 9999 r.). Ponieważ program Access nie rozpoznaje systemu daty 1904 (używanego w programie Excel dla komputerów Macintosh), musisz przekonwertować daty w programie Excel lub Access, aby uniknąć nieporozumień. Aby uzyskać więcej informacji, zobacz Zmienianie interpretacji systemu daty, formatu lub roku dwucyfrowego oraz Importowanie lub łączenie danych w skoroszycie programu Excel. |
Wybierz pozycję Data. |
Godzina |
Czas |
Zarówno program Access, jak i program Excel przechowują wartości czasu przy użyciu tego samego typu danych. |
Wybierz pozycję Godzina, która jest zwykle domyślna. |
Waluta, Księgowość |
Waluta |
W programie Access typ danych Waluta przechowuje dane jako liczby 8-bajtowe z dokładnością do czterech miejsc dziesiętnych i służy do przechowywania danych finansowych i zapobiegania zaokrąglaniu wartości. |
Wybierz pozycję Waluta, która jest zwykle domyślna. |
wartość logiczna |
Tak/Nie |
Dla wszystkich wartości Tak program Access używa wartości -1, a dla wszystkich wartości Nie — wartości 0, natomiast dla wszystkich wartości FAŁSZ w programie Excel jest używana wartość 1. |
Wybierz pozycję Tak/Nie, co spowoduje automatyczne przekonwertowanie wartości źródłowych. |
Hiperlink |
Hiperlink |
Hiperlink w programach Excel i Access zawiera adres URL lub adres internetowy, który można klikać i obserwować. |
Wybierz pozycję Hiperlink. W przeciwnym razie program Access domyślnie może używać typu danych Tekst. |
Gdy dane będą już dostępne w programie Access, możesz usunąć dane programu Excel. Pamiętaj, aby przed usunięciem utworzyć kopię zapasową oryginalnego skoroszytu programu Excel.
Aby uzyskać więcej informacji, zobacz temat pomocy Programu Access Importowanie lub łączenie danych w skoroszycie programu Excel.
Automatyczne dołączanie danych w prosty sposób
Typowym problemem, który mają użytkownicy programu Excel, jest dołączanie danych z tymi samymi kolumnami do jednego dużego arkusza. Na przykład możesz mieć rozwiązanie do śledzenia składników majątku, które zaczęło się w programie Excel, ale teraz rozrosło się do plików z wielu grup roboczych i działów. Te dane mogą znajdować się w różnych arkuszach i skoroszytach lub w plikach tekstowych będących źródłami danych z innych systemów. Nie ma polecenia interfejsu użytkownika ani łatwego sposobu dołączania podobnych danych w programie Excel.
Najlepszym rozwiązaniem jest użycie programu Access, w którym można łatwo zaimportować i dołączyć dane do jednej tabeli za pomocą Kreatora importu arkuszy kalkulacyjnych. Ponadto można dołączyć wiele danych do jednej tabeli. Możesz zapisać operacje importowania, dodać je jako zaplanowane zadania programu Microsoft Outlook, a nawet zautomatyzować ten proces za pomocą makr.
Krok 2. Normalizowanie danych przy użyciu Kreatora analizatora tabel
Na pierwszy rzut oka przejście przez proces normalizowania danych może wydawać się trudnym zadaniem. Na szczęście normalizowanie tabel w programie Access jest procesem znacznie łatwiejszym dzięki Kreatorowi analizatora tabel.
1. Przeciągnij zaznaczone kolumny do nowej tabeli i automatycznie utwórz relacje
2. Użyj poleceń przycisków, aby zmienić nazwę tabeli, dodać klucz podstawowy, zmienić istniejącą kolumnę na klucz podstawowy i cofnąć ostatnią akcję
Za pomocą tego kreatora możesz wykonać następujące czynności:
-
Konwertuj tabelę na zestaw mniejszych tabel i automatycznie utwórz relację klucza podstawowego i obcego między tabelami.
-
Dodaj klucz podstawowy do istniejącego pola zawierającego unikatowe wartości lub utwórz nowe pole identyfikatora korzystające z typu danych Autonumerowanie.
-
Automatyczne tworzenie relacji w celu wymuszania więzów integralności za pomocą aktualizacji kaskadowych. Usuwanie kaskadowe nie jest automatycznie dodawane, aby zapobiec przypadkowemu usunięciu danych, ale można łatwo dodać usunięcia kaskadowe później.
-
Wyszukaj w nowych tabelach nadmiarowe lub zduplikowane dane (na przykład tego samego klienta z dwoma różnymi numerami telefonów) i zaktualizuj je w razie potrzeby.
-
Utwórz kopię zapasową oryginalnej tabeli i zmień jej nazwę, dołączając do niej "_OLD". Następnie tworzy się zapytanie odtwarzające oryginalną tabelę z oryginalną nazwą tabeli, dzięki czemu wszystkie istniejące formularze lub raporty oparte na oryginalnej tabeli będą współdziałać z nową strukturą tabeli.
Aby uzyskać więcej informacji, zobacz Normalizowanie danych za pomocą analizatora tabel.
Krok 3. Nawiązywanie połączenia z danymi programu Access z programu Excel
Po znormalizowaniu danych w programie Access i utworzeniu zapytania lub tabeli rekonstruującej oryginalne dane należy łatwo połączyć się z danymi programu Access z programu Excel. Dane są teraz w programie Access jako zewnętrzne źródło danych, więc można je połączyć ze skoroszytem za pośrednictwem połączenia danych, które jest kontenerem informacji używanym do lokalizowania zewnętrznego źródła danych, logowania się do niego i uzyskiwania do niego dostępu. Informacje o połączeniu są przechowywane w skoroszycie i mogą być również przechowywane w pliku połączenia, takim jak plik połączenia danych pakietu Office (ODC) (rozszerzenie nazwy pliku odc) lub plik nazwa źródła danych (rozszerzenie dsn). Po nawiązaniu połączenia z danymi zewnętrznymi możesz również automatycznie odświeżać (lub aktualizować) skoroszyt programu Excel z programu Access przy każdej aktualizacji danych w programie Access.
Aby uzyskać więcej informacji, zobacz Importowanie danych z zewnętrznych źródeł danych (Power Query).
Pobieranie danych do programu Access
W tej sekcji przedstawiono następujące fazy normalizowania danych: Podzielenie wartości w kolumnach Sprzedawca i Adres na najbardziej niepodzielne elementy, rozdzielenie powiązanych tematów na ich własne tabele, skopiowanie i wklejenie tych tabel z programu Excel do programu Access, utworzenie kluczowych relacji między nowo utworzonymi tabelami programu Access oraz utworzenie i uruchomienie prostego zapytania w programie Access w celu zwrócenia informacji.
Przykładowe dane w formie nieznormalizowanej
Poniższy arkusz zawiera wartości niepodzielne w kolumnie Sprzedawca i w kolumnie Adres. Obie kolumny powinny być podzielone na dwie lub więcej oddzielnych kolumn. Ten arkusz zawiera również informacje o sprzedawcach, produktach, klientach i zamówieniach. Informacje te należy również podzielić według tematu na osobne tabele.
Sprzedawca |
Identyfikator zamówienia |
Data zamówienia |
Identyfikator produktu |
Ilość |
Cena |
Nazwa klienta |
Address (Adres) |
Phone (Telefon) |
---|---|---|---|---|---|---|---|---|
Li, Yale |
2349 |
3/4/09 |
C-789 |
3 |
7,00 zł |
Firma E |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Li, Yale |
2349 |
3/4/09 |
C-795 |
6 |
9,75 usd |
Firma E |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Adams, Ellen |
2350 |
3/4/09 |
A-2275 |
2 |
16,75 zł |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Adams, Ellen |
2350 |
3/4/09 |
F-198 |
6 |
5,25 zł |
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 zł |
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, Trzcina |
2353 |
3/7/09 |
A-2275 |
6 |
16,75 zł |
Firma E |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Koch, Trzcina |
2353 |
3/7/09 |
C-789 |
5 |
7,00 zł |
Firma E |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Informacje w najmniejszych częściach: dane atomowe
Pracując z danymi w tym przykładzie, możesz użyć polecenia Tekst do kolumny w programie Excel, aby rozdzielić "niepodzielne" części komórki (takie jak ulica, miasto, województwo i kod pocztowy) na osobne kolumny.
W poniższej tabeli przedstawiono nowe kolumny w tym samym arkuszu po ich podzieleniu, aby wszystkie wartości były atomowe. Należy pamiętać, że informacje w kolumnie Sprzedawca zostały podzielone na kolumny Nazwisko i Imię oraz że informacje w kolumnie Adres zostały podzielone na kolumny Adres ulicy, Miasto, Województwo i Kod pocztowy. Te dane mają "pierwszą normalną formę".
Nazwisko |
Imię |
|
Ulica |
Miasto |
Stan |
Kod pocztowy |
---|---|---|---|---|---|---|
Li |
Yale |
2302 Harvard Ave |
Bellevue |
WA |
98227 |
|
Adams |
Ellen |
1025 Columbia Circle |
Kirkland |
WA |
98234 |
|
Hance |
Jim |
2302 Harvard Ave |
Bellevue |
WA |
98227 |
|
Koch |
Trzcina |
7007 Cornell St Redmond |
Redmond |
WA |
98199 |
Podział danych na uporządkowane tematy w programie Excel
W kilku kolejnych tabelach przykładowych danych są wyświetlane te same informacje z arkusza programu Excel po podzieleniu ich na tabele sprzedawców, produktów, klientów i zamówień. Projekt tabeli nie jest ostateczny, ale jest na dobrej drodze.
Tabela Sprzedawcy zawiera tylko informacje o pracownikach sprzedaży. Zwróć uwagę, że każdy rekord ma unikatowy identyfikator (Identyfikator sprzedawcy). Wartość Identyfikator sprzedawcy zostanie użyta w tabeli Zamówienia do połączenia zamówień ze sprzedawcami.
Sprzedawców |
||
---|---|---|
Identyfikator sprzedawcy |
Nazwisko |
Imię |
101 |
Li |
Yale |
103 |
Adams |
Ellen |
105 |
Hance |
Jim |
107 |
Koch |
Trzcina |
Tabela Produkty zawiera tylko informacje o produktach. Zwróć uwagę, że każdy rekord ma unikatowy identyfikator (Identyfikator produktu). Wartość identyfikatora produktu zostanie użyta do połączenia informacji o produkcie z tabelą Szczegóły zamówień.
Produkty |
|
---|---|
Identyfikator produktu |
Cena |
A-2275 |
16.75 |
B-205 |
4.50 |
C-789 |
7,00 |
C-795 |
9.75 |
D-4420 |
7.25 |
F-198 |
5.25 |
Tabela Klienci zawiera tylko informacje o klientach. Zwróć uwagę, że każdy rekord ma unikatowy identyfikator (Identyfikator klienta). Wartość Identyfikator klienta zostanie użyta do połączenia informacji o klientach z tabelą Zamówienia.
Klienci |
||||||
---|---|---|---|---|---|---|
Identyfikator kontrahenta |
Nazwa |
Ulica |
Miasto |
Stan |
Kod pocztowy |
Phone (Telefon) |
1001 |
Contoso, Ltd. |
2302 Harvard Ave |
Bellevue |
WA |
98227 |
425-555-0222 |
1003 |
Adventure Works |
1025 Columbia Circle |
Kirkland |
WA |
98234 |
425-555-0185 |
1005 |
Firma E |
7007 Cornell St |
Redmond |
WA |
98199 |
425-555-0201 |
Tabela Zamówienia zawiera informacje o zamówieniach, sprzedawcach, klientach i produktach. Zwróć uwagę, że każdy rekord ma unikatowy identyfikator (Identyfikator zamówienia). Niektóre informacje w tej tabeli należy podzielić na dodatkową tabelę zawierającą szczegóły zamówień, tak aby tabela Zamówienia zawierała tylko cztery kolumny — unikatowy identyfikator zamówienia, datę zamówienia, identyfikator sprzedawcy i identyfikator klienta. Przedstawiona tutaj tabela nie została jeszcze podzielona na tabelę Szczegóły zamówień.
Zamówienia |
|||||
---|---|---|---|---|---|
Identyfikator zamówienia |
Data zamówienia |
Identyfikator sprzedawcy |
Identyfikator kontrahenta |
Identyfikator produktu |
Ilość |
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 |
Szczegóły zamówienia, takie jak identyfikator produktu i ilość, są przenoszone z tabeli Zamówienia i przechowywane w tabeli o nazwie Szczegóły zamówień. Pamiętaj, że istnieje 9 zamówień, więc warto pamiętać, że w tej tabeli jest 9 rekordów. Należy pamiętać, że tabela Zamówienia ma unikatowy identyfikator (Identyfikator zamówienia), do którego odwołuje się tabela Szczegóły zamówień.
Ostateczny projekt tabeli Zamówienia powinien wyglądać następująco:
Zamówienia |
|||
---|---|---|---|
Identyfikator zamówienia |
Data zamówienia |
Identyfikator sprzedawcy |
Identyfikator kontrahenta |
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 |
Tabela Szczegóły zamówień nie zawiera kolumn, które wymagają unikatowych wartości (tj. nie ma klucza podstawowego), więc każda kolumna lub wszystkie kolumny mogą zawierać "nadmiarowe" dane. Jednak dwa rekordy w tej tabeli nie powinny być całkowicie identyczne (ta reguła dotyczy dowolnej tabeli w bazie danych). W tej tabeli powinno być 17 rekordów — każdy odpowiadający produktowi w pojedynczym zamówieniu. Na przykład w zamówieniu 2349 trzy produkty C-789 składają się z jednej z dwóch części całego zamówienia.
Dlatego tabela Szczegóły zamówień powinna wyglądać następująco:
Szczegóły zamówienia |
||
---|---|---|
Identyfikator zamówienia |
Identyfikator produktu |
Ilość |
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 |
Kopiowanie i wklejanie danych z programu Excel do programu Access
Teraz, gdy informacje o sprzedawcach, klientach, produktach, zamówieniach i szczegółach zamówień zostały podzielone na osobne tematy w programie Excel, możesz skopiować te dane bezpośrednio do programu Access, gdzie staną się tabelami.
Tworzenie relacji między tabelami programu Access i uruchamianie zapytania
Po przeniesieniu danych do programu Access możesz utworzyć relacje między tabelami, a następnie utworzyć zapytania, aby zwrócić informacje o różnych tematach. Można na przykład utworzyć zapytanie zwracające identyfikator zamówienia i nazwiska sprzedawców zamówień wprowadzonych między 09-03-05 a 08-03.
Ponadto można tworzyć formularze i raporty, aby ułatwić wprowadzanie danych i analizę sprzedaży.
Potrzebujesz dodatkowej pomocy?
Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel lub uzyskać pomoc techniczną w Społecznościach.