Applies ToExcel dla Microsoft 365 Excel 2024 Access 2024 Excel 2021 Access 2021 Excel 2019 Access 2019 Excel 2016 Access 2016

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.

Trzy podstawowe kroki

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.

Kreator 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.

Potrzebujesz dalszej pomocy?

Chcesz uzyskać więcej opcji?

Poznaj korzyści z subskrypcji, przeglądaj kursy szkoleniowe, dowiedz się, jak zabezpieczyć urządzenie i nie tylko.

Społeczności pomagają zadawać i odpowiadać na pytania, przekazywać opinie i słuchać ekspertów z bogatą wiedzą.