Przenoszenie danych z programu Excel do programu Access

W tym artykule pokazano, jak przenieść dane z programu Excel do programu Access i przekonwertować je na relacyjne tabele, aby można było korzystać z programu Microsoft Excel i uzyskać do niego dostęp razem. Aby uzyskać podsumowanie, program Access jest najlepszym rozwiązaniem w przypadku przechwytywania, przechowywania, wysyłania kwerend i udostępniania danych, a program Excel doskonale nadaje się do obliczania, analizowania i wizualizacji danych.

Dwa artykuły, za pomocą programu Access lub Excel do zarządzania danymi i 10 najważniejszych powodów do korzystania z programu Access w programie Excel, omówiono, który program najlepiej nadaje się do konkretnego zadania oraz jak używać programu Excel i programu Access w celu utworzenia rozwiązania praktycznego.

Po przeniesieniu danych z programu Excel do programu Access istnieją trzy podstawowe kroki procesu.

Trzy podstawowe kroki

Uwaga: Aby uzyskać informacje na temat modelowania i relacji danych 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 jest bardziej gładka, jeśli przygotowujesz i czyścisz dane, gdy zajmiesz trochę czasu. Importowanie danych jest podobne do przechodzenia do nowego mieszkania. Jeśli wyczyścisz i organizujesz posiadane rzeczy przed przełączeniem, rozliczanie w nowym domu jest o wiele łatwiejsze.

Czyszczenie danych przed zaimportowaniem

Przed zaimportowaniem danych do programu Access w programie Excel warto uwzględnić następujące zagadnienia:

  • Przekonwertuj komórki zawierające dane nieatomowe (czyli wiele wartości w jednej komórce) na wiele kolumn. Na przykład komórka w kolumnie "umiejętności", która zawiera wiele wartości umiejętności, takich jak "Programowanie w języku C#", "Programowanie VBA" i "projektowanie sieci Web", powinna zostać podzielona na oddzielne kolumny zawierające tylko jedną wartość umiejętności.

  • Użyj polecenia Przytnij, aby usunąć spacje wiodące, końcowe i wielokrotne.

  • Usuwanie znaków niedrukowalnych.

  • Znajdowanie i poprawianie błędów pisowni i interpunkcji.

  • Usuwanie zduplikowanych wierszy lub zduplikowanych pól.

  • Upewnij się, że kolumny danych nie zawierają mieszanych formatów, w szczególności 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 wymagania dotyczące oczyszczania danych są skomplikowane lub nie masz czasu ani zasobów, aby zautomatyzować proces samodzielnie, możesz wziąć pod niego możliwość użycia innego dostawcy. Aby uzyskać więcej informacji, wyszukaj "oprogramowanie do czyszczenia danych" lub "jakość danych" przez ulubiony aparat wyszukiwania w przeglądarce sieci Web.

Wybieranie najlepszego typu danych podczas importowania

Podczas operacji importowania w programie Access należy wybrać odpowiednie opcje, aby otrzymać niewielką liczbę błędów konwersji, które będą wymagały ręcznej interwencji. W poniższej tabeli podsumowano, jak formaty liczb w programie Excel i typy danych programu Access są konwertowane podczas importowania danych z programu Excel do programu Access i są dostępne porady dotyczące najlepszego typu danych, które można wybrać w Kreatorze importu arkuszy.

Format liczb w programie Excel

Typ danych programu Access

Komentarze

Najważniejsze wskazówki

Tekst

Tekst, Nota

Typ danych tekst programu Access zawiera dane alfanumeryczne o długości do 255 znaków. Typ danych Nota programu Access przechowuje dane alfanumeryczne o długości do 65 535 znaków.

Wybierz pozycję Nota , aby uniknąć obcinania danych.

Liczba, procent, ułamki, naukowe

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, liczba całkowita długa, pojedyncza, podwójna, dziesiętna).

Wybierz pozycję podwójne , aby uniknąć błędów konwersji danych.

Data

Data

Program Access i program Excel używają tego samego numeru seryjnego, aby przechowywać daty. W programie Access zakres dat jest większy: od-657 434 (1 stycznia 100 r A.D.) do 2 958 465 (31 grudnia 9999 A.D.).

Ponieważ program Access nie rozpoznaje systemowej daty 1904 (używanej w programie Excel dla komputerów Macintosh), należy przekonwertować daty w programie Excel lub programie Access, aby uniknąć nieporozumień.

Aby uzyskać więcej informacji, zobacz Zmienianie systemu daty, formatowanie lub interpretacja roku w dwóch cyfrach oraz Importowanie lub łączenie danych w skoroszycie programu Excel.

Wybierz pozycję Data.

Godzina

Godzina

W programie Access i programie Excel są przechowywane wartości czasu, korzystając z tego samego typu danych.

Wybierz czas, który zwykle jest wartością domyślną.

Waluta, księgowanie

Waluta

W programie Access dane typu waluta są przechowywane jako liczby 8-bajtowe z dokładnością do czterech miejsc dziesiętnych i są używane do przechowywania danych finansowych i zapobiegania zaokrąglaniu wartości.

Wybierz walutę, która zwykle jest wartością domyślną.

wartość logiczna

Tak/Nie

Program Access używa-1 dla wszystkich wartości tak i 0 dla wszystkich wartości bez, podczas gdy program Excel używa 1 dla wszystkich wartości rzeczywistych i 0 dla wszystkich wartości fałszywych.

Wybierz pozycję tak/nie, co spowoduje automatyczne konwertowanie wartości podstawowych.

Hiperlink

Hiperlink

Hiperłącze w programie Excel i programie Access zawiera adres URL lub adres internetowy, który można kliknąć i obserwować.

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

Gdy dane będą w programie Access, możesz usunąć dane programu Excel. Przed usunięciem oryginalnego skoroszytu programu Excel należy pamiętać o konieczności utworzenia jego kopii zapasowej.

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

Automatyczne dołączanie danych w łatwy sposób

Często występujący problem użytkownicy programu Excel dołączają dane z tych samych kolumn do jednego dużego arkusza. Na przykład możesz mieć rozwiązanie do śledzenia aktywów, które rozpoczęło się w programie Excel, ale teraz można uwzględnić pliki z wielu grup roboczych i działów. Te dane mogą znajdować się w różnych arkuszach i skoroszytach lub w plikach tekstowych, które są źródłami danych z innych systemów. W programie Excel nie ma polecenia interfejsu użytkownika ani łatwego sposobu dołączania podobnych danych.

Najlepszym rozwiązaniem jest użycie programu Access, gdzie można łatwo importować i dołączać dane do jednej tabeli przy użyciu Kreatora importu arkuszy. Ponadto możesz dołączyć wiele danych do jednej tabeli. Możesz zapisać operacje importowania, dodawać je jako zaplanowane zadania programu Microsoft Outlook, a nawet używać makr do automatyzacji procesu.

Krok 2: normalizowanie danych przy użyciu Kreatora analizatora tabel

Po pierwsze, przechodzenie przez proces normalizacji danych może wydawać się zadaniem daunting. Na szczęście normalizacja tabel w programie Access to proces, który jest znacznie łatwiejszy, 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, utworzyć istniejącą kolumnę jako klucz podstawowy i cofnąć ostatnią akcję.

Za pomocą tego kreatora możesz wykonywać następujące czynności:

  • Konwertowanie tabeli na zestaw mniejszych tabel i automatyczne tworzenie relacji między tabelami.

  • Dodaj klucz podstawowy do istniejącego pola, które zawiera unikatowe wartości, lub Utwórz nowe pole identyfikatora, w którym jest używany typ danych Autonumerowanie.

  • Automatyczne tworzenie relacji w celu wymuszenia więzów integralności przy użyciu aktualizacji kaskadowych. Usunięcia kaskadowe nie są automatycznie dodawane, aby zapobiec przypadkowemu usunięciu danych, ale można je łatwo dodać później.

  • Wyszukiwanie nowych tabel w celu uzyskania nadmiarowych lub zduplikowanych danych (takich jak ten sam Klient z dwoma różnymi numerami telefonów) i zaktualizowanie go stosownie do potrzeb.

  • Wykonaj kopię zapasową oryginalnej tabeli i zmień jej nazwę, dołączając do niej "_OLD". Następnie utworzysz kwerendę, która rekonstruuje pierwotną tabelę, z oryginalną nazwą tabeli, tak aby wszystkie istniejące formularze lub raporty oparte na oryginalnej tabeli były współdziałać z nową strukturą tabeli.

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

Krok 3. Łączenie z danymi programu Access z programu Excel

Po znormalizowaniu danych w programie Access, utworzeniu zapytania lub tabeli w celu odtworzenia oryginalnych danych, jest to prosta kwestia nawiązywania połączenia z danymi programu Access z programu Excel. Dane są teraz w programie Access jako zewnętrzne źródło danych i mogą być połączone ze skoroszytem za pośrednictwem połączenia danych, który jest kontenerem informacji, które służą do lokalizowania, logowania się do i uzyskiwania dostępu do zewnętrznego źródła danych. Informacje o połączeniu są przechowywane w skoroszycie i można je również przechowywać w pliku połączenia, na przykład w pliku połączenia danych pakietu Office (ODC) (pliku odc) lub pliku nazwy źródła danych (rozszerzenie nazwy DSN). Po połączeniu z danymi zewnętrznymi możesz również automatycznie odświeżać (aktualizować) skoroszyty programu Excel w programie Access po zaktualizowaniu danych w programie Access.

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

Uzyskiwanie dostępu do danych

W tej sekcji przedstawiono następujące etapy normalizacji danych: dzielenie wartości w kolumnach sprzedawca i adres na ich najbardziej nieznajome elementy, oddzielając te powiązane tematy do własnych tabel, kopiując i wklejając te tabele z programu Excel do Program Access, tworzenie relacji kluczy między nowo utworzonymi tabelami programu Access i tworzenie i uruchamianie prostej kwerendy w programie Access w celu zwrócenia informacji.

Przykładowe dane w nieznormalizowanym formularzu

Poniższy arkusz zawiera wartości nieatomowe w kolumnie sprzedawca oraz adres. Obie kolumny należy podzielić na dwie lub więcej osobnych kolumn. Ten arkusz zawiera również informacje o sprzedawcach, produktach, klientach i zamówieniach. Informacje te powinny być również podzielone na osobne tabele.

Sprzedawca

Identyfikator zamówienia

Data zamówienia

Identyfikator produktu

Bran

Cena

Nazwa klienta

Adres

Phone (Telefon)

Li, Yale

2349

3/4/09

C-789

3

$7,00

Firma E

7007 Cornell Saint Redmond, WA 98199

425-555-0201

Li, Yale

2349

3/4/09

C-795

6

$9,75

Firma E

7007 Cornell Saint Redmond, WA 98199

425-555-0201

Olecka wyewidencjonowała, Anety

2350

3/4/09

A-2275

2

$16,75

Adventure Works

1025 Kolumbia Circle Kirkland, WA 98234

425-555-0185

Olecka wyewidencjonowała, Anety

2350

3/4/09

F-198

6

$5,25

Adventure Works

1025 Kolumbia Circle Kirkland, WA 98234

425-555-0185

Olecka wyewidencjonowała, Anety

2350

3/4/09

B-205

1

$4,50

Adventure Works

1025 Kolumbia Circle Kirkland, WA 98234

425-555-0185

Hance, Tomek

2351

3/4/09

C-795

6

$9,75

Contoso, Ltd.

2302 Harvard Ave Międzyzdrojach, WA 98227

425-555-0222

Hance, Tomek

2352

3/5/09

A-2275

2

$16,75

Adventure Works

1025 Kolumbia Circle Kirkland, WA 98234

425-555-0185

Hance, Tomek

2352

3/5/09

D-4420

3

$7,25

Adventure Works

1025 Kolumbia Circle Kirkland, WA 98234

425-555-0185

Koch, Reed

2353

3/7/09

A-2275

6

$16,75

Firma E

7007 Cornell Saint Redmond, WA 98199

425-555-0201

Koch, Reed

2353

3/7/09

C-789

5

$7,00

Firma E

7007 Cornell Saint Redmond, WA 98199

425-555-0201

Informacje w najmniejszych częściach: dane w postaci atomowej

Praca z danymi w tym przykładzie można użyć polecenia tekst do kolumny w programie Excel, aby oddzielić "pojedyncze" części komórki (na przykład adres ulicy, miasto, Województwo i kod pocztowy) do kolumn dyskretnych.

W poniższej tabeli przedstawiono nowe kolumny w tym samym arkuszu po ich poddzieleniu, aby wszystkie wartości były nierówne. Zwróć uwagę, że informacje w kolumnie sprzedawca zostały podzielone na nazwiska i kolumny imion i nazwiska oraz że informacje w kolumnie adres zostały podzielone na kolumny adres, miasto, Województwo i kod pocztowy. Te dane są na początku formularza "pierwszy normalny".

Nazwisko

Imię

 

Ulica

Miasto

Stan

Kod pocztowy

Litow

Yale

2302 Harvard Ave

Bellevue

WA

98227

Olecka wyewidencjonowała

Anety

1025 Kolumbia, okrąg

Kirkland

WA

98234

Hance

Jim

2302 Harvard Ave

Bellevue

WA

98227

Koch

Reed

7007 Cornell Saint Redmond

Redmond

WA

98199

Dzielenie danych na organizowane tematy w programie Excel

W kilku tabelach przykładowych danych, które obserwujesz, przedstawiono te same informacje z arkusza programu Excel po poddzieleniu ich na tabele dla sprzedawców, produktów, klientów i zamówień. Projekt tabeli nie jest ostateczny, ale znajduje się na prawej ścieżce.

Tabela sprzedawcy zawiera tylko informacje dotyczące pracowników działu sprzedaży. Zwróć uwagę, że każdy rekord ma unikatowy identyfikator (Identyfikator sprzedawcy). Wartość Identyfikator sprzedawcy będzie używana w tabeli zamówienia do łączenia zamówień ze sprzedawcami.

Sprzedawcy

Identyfikator sprzedawcy

Nazwisko

Imię

101

Litow

Yale

103

Olecka wyewidencjonowała

Anety

105

Hance

Jim

107

Koch

Reed

Tabela Produkty zawiera tylko informacje o produktach. Zwróć uwagę, że każdy rekord ma unikatowy identyfikator (identyfikator produktu). Wartość identyfikatora produktu zostanie wykorzystana w celu połączenia informacji o produktach z tabelą Szczegóły zamówień.

Niesie

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ść identyfikatora klienta będzie używana do łą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 Kolumbia, okrąg

Kirkland

WA

98234

425-555-0185

1005

Firma E

7007 Cornell ŚT

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 zawarte w tej tabeli muszą zostać podzielone na dodatkową tabelę zawierającą Szczegóły zamówień, aby tabela zamówienia zawierała tylko cztery kolumny — unikatowy identyfikator zamówienia, Data zamówienia, identyfikator sprzedawcy i identyfikator klienta. Pokazana tu 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

Bran

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 poza tabelę zamówienia i przechowywane w tabeli o nazwie szczegóły zamówienia. Pamiętaj, że istnieją 9 zamówień, więc warto pamiętać, że w tej tabeli znajdują się 9 rekordów. Pamiętaj, że tabela zamówienia ma unikatowy identyfikator (Identyfikator zamówienia), do którego odwołuje się tabela Szczegóły zamówień.

Ostateczny projekt tabeli Orders 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 żadnych kolumn wymagających unikatowych wartości (to znaczy, że nie ma klucza podstawowego), dlatego w przypadku każdej kolumny można umieścić dane "nadmiarowe". Jednak dwa rekordy w tej tabeli nie powinny być całkowicie identyczne (Ta reguła dotyczy dowolnej tabeli w bazie danych). W tej tabeli powinny istnieć 17 rekordów — każde odpowiadające produktowi w poszczególnych zamówieniach. 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.

Tabela Szczegóły zamówień powinna więc wyglądać następująco:

Szczegóły zamówienia

Identyfikator zamówienia

Identyfikator produktu

Bran

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, w którym staną się one tabelami.

Tworzenie relacji między tabelami programu Access i uruchamianie zapytania

Po przeniesieniu danych do programu Access można utworzyć relacje między tabelami, a następnie utworzyć zapytania, aby zwrócić informacje dotyczące różnych tematów. Możesz na przykład utworzyć zapytanie zwracające identyfikator zamówienia oraz imiona i nazwiska sprzedawców zamówień wprowadzonych między 3/05/09 a 3/08/09.

Ponadto możesz tworzyć formularze i raporty, aby ułatwić ich wprowadzanie danych i analizę 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.

Bądź o krok do przodu dzięki platformie Microsoft 365

Potrzebna dalsza pomoc?

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×