W tym artykule pokazano, jak przenieść dane z programu Excel do programu Access i przekonwertować je na tabele relacyjne, aby można było używać ich razem Excel Microsoft i Access. Podsumowując, program Access najlepiej się do przechwytywania, przechowywania, wykonywania zapytań i udostępniania danych, a Excel najlepiej do obliczania, analizowania i wizualizowania danych.

W dwóch artykułach, Zarządzanie danymi za pomocą programu Access lub programu Excel oraz 10najlepszych powodów, dla których warto korzystać z programu Access z programem Excel, omówienie programu, który najlepiej odpowiada danemu zadaniu, oraz sposoby używania programów Excel i Access razem w celu utworzenia praktycznego rozwiązania.

Przeniesienie danych z programu Excel do programu Access pozwala na przejście do niego w trzech podstawowych krokach.

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 sprawniej, jeśli trzeba będzie trochę czasu przygotować i wyczyścić dane. Importowanie danych przypomina przenoszenie się do nowego domu. Jeśli posprzątasz swoje posiadania przed wyjazdem, zadomowisz się w nowym domu znacznie łatwiej.

Czyszczenie danych przed zaimportowaniem

Przed zaimportowaniem danych do programu Access warto Excel:

  • Konwertuj komórki zawierające niepodzielne dane (czyli wiele wartości w jednej komórce) na wiele kolumn. Na przykład komórkę w kolumnie "Umiejętności" zawierającą wiele wartości umiejętności, na przykład "Programowanie C#", "Programowanie VBA" i "Projektowanie sieci Web", należy podzielić na oddzielne kolumny, w których każda z nich zawiera tylko jedną wartość umiejętności.

  • Użyj polecenia USUŃ.ODSTĘPY, aby usunąć spacje wiodące, końcowe i wiele osadzonych.

  • Usuwanie znaków niedrukowych.

  • Znajdowanie i poprawianie błędów pisowni i znaków 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 Excel pomocy:

Uwaga: Jeśli Twoje potrzeby w zakresie oczyszczania danych są złożone lub nie masz czasu ani zasobów, aby samodzielnie zautomatyzować proces, możesz rozważyć użycie dostawcy z innej firmy. Aby uzyskać więcej informacji, wyszukaj "oprogramowanie oczyszczania danych" lub "jakość danych" według ulubionej wyszukiwarki w przeglądarce internetowej.

Wybieranie typu danych najlepszego podczas importowania

Podczas operacji importowania w programie Access należy dokonać dobrych wyborów, aby otrzymać kilka (jeśli wie) błędów konwersji wymagających ręcznej interwencji. W Excel poniższej tabeli podsumowano sposób konwertowania formatów liczbowych i typów danych programu Access podczas importowania danych z programu Excel do programu Access, a także przedstawiono porady dotyczące najlepszych typów danych do wyboru w Kreatorze importu arkuszy kalkulacyjnych.

Excel formatu liczb

Typ danych programu Access

Komentarze

Najważniejsze wskazówki

Tekst

Text, Memo

Typ danych Tekst programu Access przechowuje dane alfanumeryczne o wysokości do 255 znaków. Typ danych Nota programu Access przechowuje dane alfanumeryczne o wysokości 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 różni się w zależności od właściwości Rozmiar pola (Bajt, 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 programie Access Excel daty są przechowywane w tym samym numerze seryjnym. W programie Access zakres dat jest większy: od -657 434 (1 stycznia 100 do 2 958 465 (31 grudnia 9999 r.).

Program Access nie rozpoznaje systemu daty 1904 (używanego w programie Excel dla komputerów Macintosh), dlatego w celu uniknięcia nieporozumień należy przekonwertować daty w programie Excel lub Access.

Aby uzyskać więcej informacji, zobacz Zmienianie systemu daty, formatu lub interpretacji letniego dwucyfrowego oraz Importowanie lub łączenie danych w skoroszycie Excel skoroszycie.

Wybierz pozycję Data.

Godzina

Godzina

W programie Access Excel wartości czasu są przechowywane przy użyciu tego samego typu danych.

Wybierz pozycjęCzas (zazwyczaj jest to ustawienie domyślne).

Walutowe, księgowe

Waluta

W programie Access typ danych Waluta służy do przechowywania danych w postaci 8-bajtowych liczb z dokładnością do czterech miejsc dziesiętnych. Służy do przechowywania danych finansowych i uniemożliwia zaokrąglanie wartości.

Zazwyczaj domyślnąwartością jest Waluta.

wartość logiczna

Tak/Nie

Program Access używa wartości -1 dla wszystkich wartości Tak i 0 dla wszystkich wartości Nie, natomiast Excel ma wartość 1 dla wszystkich wartości PRAWDA, a 0 dla wszystkich wartości FAŁSZ.

Wybierz pozycję Tak/Nie,co spowoduje automatyczne przekonwertowanie wartości źródłowych.

Hiperlink

Hiperlink

Hiperlink w programie Excel access zawiera adres URL lub adres internetowy, który możesz kliknąć i śledzić.

Wybierz pozycję Hiperlink,w przeciwnym razie program Access może domyślnie używać typu danych Tekst.

Gdy dane będą już w programie Access, możesz usunąć Excel danych. Pamiętaj, aby wykonać kopię zapasową oryginalnego Excel przed jego usunięciem.

Aby uzyskać więcej informacji, zobacz temat pomocy programu Access Importowanie lub łączenie danych w skoroszycie Excel skoroszycie.

Łatwe automatyczne dołączanie danych

Najczęstszym problemem Excel użytkowników jest dołączanie danych z tych samych kolumn do jednego dużego arkusza. Na przykład możesz mieć rozwiązanie do śledzenia środków trwałych, które rozpoczęło się w Excel, ale teraz zostało wyrosło, aby uwzględnić pliki z wielu grup roboczych i działów. Te dane mogą być w różnych arkuszach i skoroszytach lub w plikach tekstowych, które są ź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 importować dane do jednej tabeli i dołączać je przy użyciu Kreatora importu arkuszy. Ponadto można dołączyć dużą część danych do jednej tabeli. Możesz zapisać operacje importowania, dodać je jako zaplanowane zadania Outlook Microsoft, a nawet zautomatyzować proces za pomocą makr.

Krok 2. Normalizowanie danych przy użyciu Kreatora analizatora tabel

Na pierwszy rzut oka przechodzenie przez proces normalizowania danych może wydawać się nieumiejętne. Na szczęście normalizowanie tabel w programie Access jest procesem znacznie łatwiejszym dzięki Kreatorowi analizatora tabel.

Kreator analizatora tabel

1. Przeciąganie zaznaczonych kolumn do nowej tabeli i automatyczne tworzenie relacji

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żna wykonać następujące czynności:

  • Przekonwertuj 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 wartości unikatowe lub utwórz nowe pole identyfikatora, które korzysta z typu danych Autonumeruj.

  • Automatyczne tworzenie relacji w celu wymuszania więzów integralności za pomocą aktualizacji kaskadowych. Usuwanie kaskadowe nie jest dodawane automatycznie, aby zapobiec przypadkowemu usunięciu danych, ale możesz łatwo dodać usuwanie 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 te dane zgodnie z potrzebami.

  • Należy wrócić do oryginalnej tabeli i zmienić jej nazwę, dołączając do _OLD "nazwa tabeli". Następnie tworzysz zapytanie, które odbuduje oryginalną tabelę z oryginalną nazwą tabeli, dzięki czemu wszystkie istniejące formularze lub raporty oparte na oryginalnej tabeli będą działać z nową strukturą tabeli.

Aby uzyskać więcej informacji, zobacz Normalizowanie danych przy użyciu Analizatora tabel.

Krok 3. Połączenie dostępu do danych z Excel

Po znormalizowaniu danych w programie Access i utworzeniu zapytania lub tabeli, które odtąd odtwarza oryginalne dane, połączenie się z danymi programu Access z programu Excel jest proste. Dane są teraz w programie Access jako zewnętrzne źródło danych, więc mogą być połączone 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ć także przechowywane w pliku połączenia, takim jak plik połączenia danych programu Office (odc, rozszerzenie nazwy pliku odc) lub plik nazwy źródła danych (rozszerzenie dsn). Po nawiązania połączenia z danymi zewnętrznymi można też automatycznie odświeżać (lub aktualizować) skoroszyt programu Excel programie Access po każdej aktualizacji danych w programie Access.

Aby uzyskać więcej informacji, zobacz Importowanie danych z zewnętrznych źródeł danych (Power Query).

Uzyskiwanie danych do programu Access

W tej sekcji przedstawiono następujące fazy normalizowania danych: podzielenie wartości z kolumn Sprzedawca i Adres na ich najbardziej niepodzielne części, 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 postaci nie znormalizowanych

Poniższy arkusz zawiera wartości niepodzielne w kolumnie Sprzedawca i Adres. Obie kolumny powinny być podzielone na dwie lub więcej osobnych kolumn. Ten arkusz zawiera również informacje o sprzedawcach, produktach, klientach i zamówieniach. Te informacje należy również podzielić dalej, według tematu, na osobne tabele.

Sprzedawca

Identyfikator zamówienia

Data zamówienia

Identyfikator produktu

Ilość

Cena

Nazwa klienta

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 zł

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 Circle Kirkland, WA 98234

425-555-0185

Adams, Ellen

2350

3/4/09

F-198

6

5,25 zł

Adventure Works

1025 Circle Kirkland, WA 98234

425-555-0185

Adams, Ellen

2350

3/4/09

B-205

1

4,50 zł

Adventure Works

1025 Circle Kirkland, WA 98234

425-555-0185

Hance, Jim

2351

3/4/09

C-795

6

9,75 zł

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 Circle Kirkland, WA 98234

425-555-0185

Hance, Jim

2352

3/5/09

D-4420

3

7,25 zł

Adventure Works

1025 Circle Kirkland, WA 98234

425-555-0185

Koch, Reed

2353

3/7/09

A-2275

6

16,75 zł

Firma E

7007 Cornell St Redmond, WA 98199

425-555-0201

Koch, Reed

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 niepodzielne

Podczas pracy z danymi w tym przykładzie można użyć polecenia Tekst jako kolumny w programie Excel w celu oddzielenia "niepodzielnych" części komórki (takich jak ulica, miasto, województwo i kod pocztowy) na osobne kolumny.

W poniższej tabeli przedstawiono nowe kolumny w tym samym arkuszu po podzieleniu ich na wartości niepodzielne. Pamiętaj, że informacje w kolumnie Sprzedawca zostały podzielone na kolumny Nazwisko i Imię, a informacje w kolumnie Adres zostały podzielone na kolumny Ulica, Miasto, Województwo i Kod pocztowy. Te dane mają postać "pierwsza normalna".

Nazwisko

Imię

 

Ulica

Miasto

Stan

Kod pocztowy

Li

Yale

2302 Harvard Ave

Bellevue

WA

98227

Adams

Llen

1025 Columbia Circle

Kirkland

WA

98234

Hance

Jim

2302 Harvard Ave

Bellevue

WA

98227

Koch

Reed

7007 Cornell St Redmond

Redmond

WA

98199

Podział danych na tematy zorganizowane w programie Excel

W poniższych tabelach przykładowych są wyświetlane te same informacje z arkusza Excel po podzieleniu ich na tabele dla sprzedawcy, produktów, klientów i zamówień. Projekt tabeli nie jest ostateczny, ale jest we właściwym torze.

Tabela Sprzedawca zawiera tylko informacje o pracowniku działu sprzedaży. Zwróć uwagę, że każdy rekord ma unikatowy identyfikator (Identyfikator sprzedawcy). Wartość identyfikatora sprzedawcy zostanie użyta w tabeli Zamówienia do połączenia zamówień z sprzedawcami.

Sprzedawcy

Identyfikator sprzedawcy

Nazwisko

Imię

101

Li

Yale

103

Adams

Llen

105

Hance

Jim

107

Koch

Reed

Tabela Produkty zawiera tylko informacje o produktach. Pamiętaj, że każdy rekord ma unikatowy identyfikator (identyfikator produktu). Wartość Identyfikator 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. 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. Każdy rekord ma unikatowy identyfikator (Identyfikator zamówienia). Niektóre informacje w tej tabeli należy podzielić na dodatkową tabelę ze szczegółami zamówień, tak aby tabela Zamówienia zawierała tylko cztery kolumny — unikatowy identyfikator zamówienia, datę zamówienia, identyfikator sprzedawcy i identyfikator klienta. Tabela pokazana w tym miejscu 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 jest 9 zamówień, więc warto pamiętać, że w tej tabeli jest 9 rekordów. Zwróć uwagę, że tabela Zamówienia ma unikatowy identyfikator (Identyfikator zamówienia), który będzie się odzywał z tabeli Szczegóły zamówienia.

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ą wartości unikatowych (czyli nie zawiera klucza podstawowego), więc "nadmiarowe" dane mogą być zawarte w kolumnach lub we wszystkich kolumnach. Dwa rekordy w tej tabeli nie powinny być jednak całkowicie identyczne (ta reguła dotyczy każdej tabeli w bazie danych). W tej tabeli powinno być 17 rekordów — każdy odpowiadający produktowi w indywidualnym zamówieniu. Na przykład zamówienie 2349 zawiera trzy produkty od C-789 do 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 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 przeniesioniu danych do programu Access możesz utworzyć relacje między tabelami, a następnie utworzyć zapytania w celu zwrócenia informacji dotyczących różnych tematów. Można na przykład utworzyć zapytanie, które zwraca identyfikator zamówienia i nazwiska sprzedawcy w przypadku zamówień wprowadzonych między 2009-03-05 a 2009-03-08.

Ponadto można tworzyć formularze i raporty, aby ułatwić wprowadzanie danych i analizowanie sprzedaży.

Potrzebujesz dodatkowej pomocy?

Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel, uzyskać pomoc techniczną w społeczności witryny Answers bądź zasugerować nową funkcję lub ulepszenie w witrynie UserVoice dotyczącej programu Excel.

Potrzebna dalsza pomoc?

Rozwijaj swoje umiejętności
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów Microsoft Office

Czy te informacje były pomocne?

Jaka jest jakość języka?
Co wpłynęło na Twoje wrażenia?

Dziękujemy za opinię!

×