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 tabele relacyjne, aby można było jednocześnie korzystać z programów Microsoft Excel i Access. Podsumowując, program Access najlepiej nadaje się do rejestrowania, przechowywania, wykonywania zapytań i udostępniania danych, a program Excel najlepiej nadaje się do obliczania, analizowania i wizualizowania danych.

W dwóch artykułach, Zarządzanie danymi przy użyciu programu Access lub programu Excel oraz 10 głównych powodów, dla których warto używać programu Access z programem Excel, omówiono, który program najlepiej nadaje się do konkretnego zadania i jak używać programów Excel i Access razem w celu utworzenia praktycznego rozwiązania.

Proces przenoszenia danych z programu Excel do programu Access obejmuje trzy podstawowe etapy.

Krok 1. Importowanie danych z programu Excel do programu Access

Importowanie danych może przebiegać o wiele sprawniej, jeśli poświęcisz trochę czasu na przygotowanie i oczyszczenie danych. Importowanie danych przypomina przeprowadzkę do nowego domu. Jeśli posprzątasz i uporządkujesz swój dobytek przed przeprowadzką, osiedlenie się w nowym domu jest znacznie łatwiejsze.

Oczyść dane przed zaimportowaniem

Przed zaimportowaniem danych do programu Access w programie Excel warto wykonać następujące czynności:

• Konwertowanie komórek zawierających dane niepodzielne (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 w języku VBA" i "Projektowanie sieci Web", powinna zostać podzielona na osobne 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 niedrukowalnych.
• Znajdowanie i poprawianie błędów pisowni i błędów interpunkcyjnych.
• Usuwanie zduplikowanych wierszy lub zduplikowanych pól.
• Upewnij się, że kolumny danych nie zawierają formatów mieszanych, zwłaszcza liczb sformatowanych jako tekst ani dat sformatowanych jako liczby.

Aby uzyskać więcej informacji, zobacz następujące tematy Pomocy dotyczące programu Excel:

• Dziesięć najlepszych sposobów oczyszczania danych
• Filtrowanie w celu znalezienia wartości unikatowych lub usuwanie wartości zduplikowanych
• Konwertowanie liczb przechowywanych jako tekst na liczby
• Konwertowanie dat przechowywanych jako tekst na daty

Wybieranie najlepszego typu danych podczas importowania

Podczas operacji importowania w programie Access należy wybrać odpowiednie opcje, aby zminimalizować błędy konwersji (o ile w ogóle wystąpiły), które wymagałyby interwencji ręcznej. W poniższej tabeli podsumowano konwertowanie formatów liczb programu Excel i typów danych programu Access podczas importowania danych z programu Excel do programu Access, a także podano kilka porad dotyczących typów danych, które najlepiej wybrać w Kreatorze importowania arkuszy.

Format liczb w programie Excel	Typ danych programu Access	Komentarze	Najważniejsze wskazówki
Text (Tekst)	Tekst, Nota	Typ danych Tekst w programie Access przechowuje dane alfanumeryczne o maksymalnej długości 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, wartość procentowa, ułamek, naukowy	Liczba	Program Access ma jeden typ danych Liczba, który zmienia się w zależności od właściwości Rozmiar pola (Bajt, Liczba całkowita, Liczba całkowita długa, Pojedyncza, Podwójna precyzja, Liczba dziesiętna).	Wybierz pozycję Podwójna , aby uniknąć błędów konwersji danych.
Data	Data	W programach Access i Excel daty są przechowywane przy użyciu tej samej liczby kolejnej. 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), należy przekonwertować daty w programie Excel lub Access, aby uniknąć pomyłek. Aby uzyskać więcej informacji, zobacz Zmienianie systemu daty, formatu lub interpretacji lat dwucyfrowych oraz Importowanie lub łączenie danych w skoroszycie programu Excel.	Wybierz pozycję Data.
Godzina	Czas	Programy Access i Excel przechowują wartości czasu przy użyciu tego samego typu danych.	Wybierz pozycję Czas, która jest zazwyczaj ustawieniem domyślnym.
Waluta, Księgowość	Waluta	W programie Access typ danych Waluta przechowuje dane w postaci liczb 8-bajtowych z dokładnością do czterech miejsc dziesiętnych i służy do przechowywania danych finansowych oraz zapobiegania zaokrąglaniu wartości.	Wybierz pole Waluta, które jest zazwyczaj ustawieniem domyślnym.
wartość logiczna	Tak/Nie	W programie Access dla wszystkich wartości Tak jest używana wartość -1, a dla wszystkich wartości Nie — 0, natomiast w programie Excel wartość 1 oznacza wszystkie wartości PRAWDA, a wartość 0 wszystkie wartości FAŁSZ.	Wybierz pozycję Tak/Nie, która automatycznie przekonwertuje wartości bazowe.
Hiperlink	Hiperlink	Hiperlink w programach Excel i Access zawiera adres URL lub adres internetowy, który możesz kliknąć i prześledzić.	Wybierz pozycję Hiperłącze. W przeciwnym razie program Access może domyślnie używać typu danych Tekst.

Po umieszczeniu danych w programie Access możesz usunąć dane programu Excel. Pamiętaj, aby przed usunięciem oryginalnego skoroszytu programu Excel utworzyć jego kopię zapasową.

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

Automatyczne dołączanie danych w prosty sposób

Typowym problemem użytkowników programu Excel jest dołączanie danych z takimi samymi kolumnami do jednego dużego arkusza. Na przykład może istnieć rozwiązanie do śledzenia składników majątku, które zaczynało się w programie Excel, ale teraz rozrosło się i obejmuje pliki z wielu grup roboczych i działów. Te dane mogą znajdować się w innych arkuszach i skoroszytach lub w plikach tekstowych będących strumieniowymi źródłami danych z innych systemów. W programie Excel nie ma poleceń interfejsu użytkownika ani łatwych sposobów dołączania podobnych danych.

Najlepszym rozwiązaniem jest skorzystanie z programu Access, w którym można łatwo zaimportować dane do jednej tabeli i dołączyć je do jednej tabeli za pomocą Kreatora importowania arkuszy. Ponadto do jednej tabeli można dołączyć wiele danych. 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. Przy użyciu poleceń przycisków można zmienić nazwę tabeli, dodać klucz podstawowy, ustawić istniejącą kolumnę jako klucz podstawowy i cofnąć ostatnią akcję

Za pomocą tego kreatora możesz wykonywać 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 korzystające z typu danych Autonumerowanie.
• Automatyczne tworzenie relacji w celu wymuszenia więzów integralności przy aktualizacjach kaskadowych. Usuwanie kaskadowe nie jest dodawane automatycznie, aby zapobiec przypadkowemu usunięciu danych, ale można łatwo dodać usuwanie kaskadowe później.
• Należy wyszukać w nowych tabelach nadmiarowe lub zduplikowane dane (na przykład ten sam klient z dwoma różnymi numerami telefonów) i zaktualizować je zgodnie z potrzebami.
• Utwórz kopię zapasową pierwotnej tabeli i zmień jej nazwę, dołączając do jej nazwy ciąg "_OLD". Następnie należy utworzyć zapytanie, które odtworzy oryginalną tabelę pod oryginalną nazwą — tak, aby wszelkie istniejące formularze i raporty oparte na oryginalnej tabeli działały z nową strukturą tabeli.

Aby uzyskać więcej informacji, zobacz Normalizowanie danych przy użyciu 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 odtwarzającej oryginalne dane wystarczy nawiązać połączenie z danymi programu Access z poziomu programu Excel. Dane znajdują się teraz w programie Access jako zewnętrzne źródło danych, a więc mogą być połączone ze skoroszytem za pośrednictwem połączenia danych, czyli kontenera informacji służącego 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 pakietu Office (rozszerzenie nazwy pliku odc) lub plik nazwy źródła danych (rozszerzenie dsn). Po nawiązaniu połączenia z danymi zewnętrznymi można również automatycznie odświeżać (lub aktualizować) skoroszyt programu Excel z programu 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).

Wprowadzanie danych do programu Access

W tej sekcji opisano następujące etapy normalizowania danych: podział wartości w kolumnach Sprzedawca i Adres na najbardziej niepodzielne fragmenty, rozdzielenie powiązanych tematów do ich własnych tabel, skopiowanie i wklejenie tych tabel z programu Excel do programu Access, utworzenie relacji między nowo utworzonymi tabelami programu Access oraz utworzenie i uruchomienie w programie Access prostego zapytania zwracającego informacje.

Przykładowe dane w postaci nieznormalizowanej

Poniższy arkusz zawiera wartości niepodzielne w kolumnach 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. Informacje te powinny być również podzielone tematycznie, 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 dolarów	Firma E	7007 Cornell St Redmond, WA 98199	425-555-0201
Li, Yale	2349	3/4/09	C-795	6	9,75 dolarów	Firma E	7007 Cornell St Redmond, WA 98199	425-555-0201
Adams, Ellen	2350	3/4/09	A-2275	2	16,75 dolarów	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Adams, Ellen	2350	3/4/09	F-198	6	5,25 dolarów	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Adams, Ellen	2350	3/4/09	B-205	1	4,50 dolarów	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Hance, Jim	2351	3/4/09	C-795	6	9,75 dolarów	Contoso, Ltd.	2302 Harvard Ave Bellevue, WA 98227	425-555-0222
Hance, Jim	2352	3/5/09	A-2275	2	16,75 dolarów	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Hance, Jim	2352	3/5/09	D-4420	3	7,25 dolarów	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Koch, Trzcina	2353	3/7/09	A-2275	6	16,75 dolarów	Firma E	7007 Cornell St Redmond, WA 98199	425-555-0201
Koch, Trzcina	2353	3/7/09	C-789	5	7,00 dolarów	Firma E	7007 Cornell St Redmond, WA 98199	425-555-0201

Informacje w najmniejszych częściach: dane niepodzielne

Pracując z danymi przedstawionymi w tym przykładzie, możesz użyć polecenia Tekst jako kolumny w programie Excel, aby oddzielić "atomowe" 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 w celu uczynienia wszystkich wartości niepodzielnymi. Zwróć uwagę, ż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 są w "pierwszej postaci normalnej".

Nazwisko	Imię	Ulica	Miasto	Stan	Kod pocztowy
Li	Uniwersytet Yale	ul. Harvarda 2302	Bellevue	WA	98227
Adamsa	Elżbieta	1025 Krąg Kolumbii	Kirkland	WA	98234
Hance	Jim	ul. Harvarda 2302	Bellevue	WA	98227
Koch	Trzcina	7007 Cornell St, Redmond	Redmond	WA	98199

Dzielenie danych na zorganizowane tematy w programie Excel

Kilka tabel przykładowych danych przedstawia te same informacje z arkusza programu Excel, mimo że został on podzielony na tabele Sprzedawcy, Produkty, Klienci i Zamówienia. Projekt stołu nie jest ostateczny, ale jest na dobrej drodze.

Tabela Sprzedawcy zawiera tylko informacje na temat sprzedawców. Każdy rekord ma unikatowy identyfikator (Identyfikator sprzedawcy). Wartość identyfikatora sprzedawcy będzie używana w tabeli Zamówienia w celu łączenia zamówień ze sprzedawcami.

Sprzedawcy
Identyfikator sprzedawcy	Nazwisko	Imię
101	Li	Uniwersytet Yale
103	Adamsa	Elżbieta
105	Hance	Jim
107	Koch	Trzcina

Tabela Produkty zawiera tylko informacje o produktach. Każdy rekord ma unikatowy identyfikator (identyfikator produktu). Wartość Identyfikator produktu będzie używana do łą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. Należy pamiętać, ż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.	ul. Harvarda 2302	Bellevue	WA	98227	425-555-0222
1003	Adventure Works	1025 Krąg Kolumbii	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. Należy zwrócić uwagę, że każdy rekord ma unikatowy identyfikator (identyfikator zamówienia). Niektóre informacje w tej tabeli należy podzielić do dodatkowej tabeli zawierającej 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 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	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ówień, takie jak identyfikator produktu i ilość, są przenoszone z tabeli Zamówienia i przechowywane w tabeli o nazwie Szczegóły zamówień. Należy pamiętać, że jest 9 zamówień, więc w tej tabeli dostępnych jest 9 rekordów. Należy zauważyć, że tabela Zamówienia ma unikatowy identyfikator (Identyfikator zamówienia), do którego będzie odwoływać 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 wymagających unikatowych wartości (czyli nie zawiera klucza podstawowego), dlatego wszystkie kolumny mogą zawierać "nadmiarowe" dane. Jednak żadne dwa rekordy w tej tabeli nie powinny być identyczne (ta reguła dotyczy dowolnej tabeli w bazie danych). Ta tabela powinna zawierać 17 rekordów — każdy odpowiadający produktowi w indywidualnym zamówieniu. Na przykład w zamówieniu 2349 trzy produkty C-789 stanowią jedną z dwóch części całego zamówienia.

W związku z tym 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 zostaną one przekształcone w tabele.

Tworzenie relacji między tabelami programu Access i uruchamianie kwerendy

Po przeniesieniu danych do programu Access można utworzyć relacje między tabelami, a następnie utworzyć kwerendy zwracające informacje dotyczące różnych tematów. Można na przykład utworzyć zapytanie zwracające identyfikator zamówienia i nazwiska sprzedawców w przypadku zamówień wprowadzonych między 05-03-09 a 08-03-09.

Ponadto można tworzyć formularze i raporty ułatwiające 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.