Samouczek: importowanie danych do programu Excel i tworzenie modelu danych

Niniejszy samouczek zawiera następujące sekcje:

• Importowanie danych z bazy danych

• Importowanie danych z arkusza kalkulacyjnego

• Importowanie danych za pomocą kopiowania i wklejania

• Tworzenie relacji między zaimportowanymi danymi

• Punkt kontrolny i test

Na końcu tego samouczka jest umieszczony test, który można wykonać, aby sprawdzić stopień opanowania materiału.

W tej serii samouczków użyto danych opisujących medale olimpijskie, kraje będące gospodarzami oraz różne wydarzenia sportowe na igrzyskach olimpijskich. Sugerujemy zapoznawanie się z poszczególnymi samouczkami zgodnie z ich kolejnością. W samouczkach jest używany program Excel 2013 z włączonym dodatkiem Power Pivot. Aby uzyskać więcej informacji o programie Excel 2013, kliknij tutaj. Aby uzyskać wskazówki dotyczące włączania dodatku Power Pivot, kliknij tutaj.

Importowanie danych z bazy danych

Niniejszy samouczek zaczynamy od pustego skoroszytu. Celem tej sekcji jest połączenie się z zewnętrznym źródłem danych i zaimportowanie danych do programu Excel na potrzeby dalszej analizy.

Zacznijmy od pobrania danych z Internetu. Dane opisują medale olimpijskie i są bazą danych programu Microsoft Access.

1. Kliknij poniższe linki, aby pobrać pliki, które są używane w tej serii samouczków. Pobierz każdy z czterech plików do łatwo dostępnej lokalizacji, takiej jak Pobrane lub Moje dokumenty, albo do nowo utworzonego folderu:
   baza danych
   programu > OlympicMedals.accdb Access > OlympicSports.xlsx skoroszyt
   programu Excel > Population.xlsx skoroszyt
   programu Excel > DiscImage_table.xlsx skoroszyt programu Excel

2. Otwórz pusty skoroszyt w programie Excel 2013.

3. Kliknij pozycję DANE > Pobieranie danych zewnętrznych > Z programu Access. Wstążka dostosowuje się dynamicznie na podstawie szerokości skoroszytu, więc polecenia na wstążce mogą wyglądać nieco inaczej niż na poniższych obrazach. Pierwszy obraz przedstawia wstążkę, gdy skoroszyt jest szeroki, drugi obraz przedstawia skoroszyt, którego rozmiar zmieniono, aby zajmował tylko część ekranu.
   
   
   
   
   
    

4. Wybierz pobrany plik OlympicMedals.accdb i kliknij przycisk Otwórz. Pojawi się następujące okno Wybieranie tabeli z wyświetlonymi tabelami znajdującymi się w bazie danych. Tabele w bazie danych są podobne do arkuszy lub tabel w programie Excel. Zaznacz pole wyboru Włącz zaznaczanie wielu tabel, a następnie zaznacz wszystkie tabele. Następnie kliknij przycisk OK.
   
   

5. Zostanie wyświetlone okno Importowanie danych.

   Uwaga: Zwróć uwagę na pole wyboru u dołu okna umożliwiające dodanie tych danych do modelu danych, które przedstawiono na poniższym ekranie. Model danych jest tworzony automatycznie podczas importowania lub jednoczesnej pracy z co najmniej dwiema tabelami. Model danych integruje tabele, umożliwiając kompleksową analizę przy użyciu tabel przestawnych, Power Pivot i programu Power View. Podczas importowania tabel z bazy danych istniejące relacje między tymi tabelami są używane do tworzenia modelu danych w programie Excel. Model danych jest przezroczysty w programie Excel, ale można go wyświetlać i modyfikować bezpośrednio przy użyciu dodatku Power Pivot. Model danych jest omówiony bardziej szczegółowo w dalszej części tego samouczka.

   
   Wybierz opcję Raport w formie tabeli przestawnej , która importuje tabele do programu Excel i przygotowuje tabelę przestawną do analizowania zaimportowanych tabel, i kliknij przycisk OK.
   
   

6. Po zaimportowaniu danych przy użyciu zaimportowanych tabel jest tworzona tabela przestawna.
   
   

Gdy dane są zaimportowane do programu Excel, a model danych został utworzony automatycznie, można rozpocząć eksplorowanie danych.

Eksplorowanie danych za pomocą tabeli przestawnej

Eksplorowanie zaimportowanych danych przy użyciu tabeli przestawnej jest łatwe. W tabeli przestawnej można przeciągać pola (podobnie jak kolumny w programie Excel) z tabel (takich jak tabele właśnie zaimportowane z bazy danych programu Access) do różnych obszarów tabeli przestawnej, aby dostosować sposób prezentowania danych. Tabela przestawna ma cztery obszary: FILTRY, KOLUMNY, WIERSZE i WARTOŚCI.

Znalezienie obszaru, do którego należy przeciągnąć pole, może wymagać nieco eksperymentowania. Z tabel można przeciągać dowolną liczbę pól do momentu, gdy dane będą przedstawiane w tabeli przestawnej w wymagany sposób. Możesz swobodnie przeciągać pola do różnych obszarów tabeli przestawnej — dane źródłowe pozostaną niezmienione podczas rozmieszczania pól w tabeli przestawnej.

Przyjrzyjmy się danym dotyczącym medali olimpijskich w tabeli przestawnej, zaczynając od medalistów olimpijskich uporządkowanych według dyscypliny, typu medalu i kraju lub regionu sportowca.

1. W obszarze Pola tabeli przestawnej rozwiń tabelę Medals, klikając strzałkę obok niej. Znajdź pole NOC_CountryRegion w rozwiniętej tabeli Medals i przeciągnij je do obszaru KOLUMNY. NOC to skrót od National Olympic Committees (Narodowy Komitet Olimpijski), który jest jednostką organizacyjną dla danego kraju lub regionu.

2. Następnie z tabeli Disciplines przeciągnij dyscyplinę do obszaru WIERSZE.

3. Przefiltrujemy dyscypliny, aby było wyświetlanych tylko pięć: Archery, Diving, Fencing, Figure Skating i Speed Skating. Można to zrobić z poziomu obszaru Pola tabeli przestawnej lub za pomocą filtru Etykiety wierszy w samej tabeli przestawnej.

   1. Kliknij dowolne miejsce w tabeli przestawnej, aby upewnić się, że jest zaznaczona tabela przestawna programu Excel. Na liście Pola tabeli przestawnej , na której jest rozwinięta tabela Disciplines , umieść wskaźnik myszy na polu Discipline, a po prawej stronie pola pojawi się strzałka listy rozwijanej. Kliknij listę rozwijaną, kliknij pozycję (Zaznacz wszystko), aby usunąć wszystkie zaznaczenia, a następnie przewiń w dół i wybierz pozycję Archery, Diving, Fencing, Figure Skating i Speed Skating. Kliknij przycisk OK.

   2. Ewentualnie w sekcji tabeli przestawnej Etykiety wierszy kliknij menu rozwijane obok pozycji Etykiety wierszy, kliknij pozycję (Zaznacz wszystko), aby usunąć wszystkie zaznaczenia, a następnie przewiń w dół i wybierz pozycje Archery, Diving, Fencing, Figure Skating i Speed Skating. Kliknij przycisk OK.

4. W obszarze Pola tabeli przestawnej przeciągnij pozycję Medal z tabeli Medals do obszaru WARTOŚCI. Ponieważ wartości muszą być liczbowe, program Excel automatycznie zmienia nazwę Medal na Count of Medal (Liczba Medal).

5. W tabeli Medals zaznacz ponownie pozycję Medal i przeciągnij ją do obszaru FILTRY.

6. Chcemy tak przefiltrować tabelę przestawną, aby były wyświetlane tylko te kraje lub regiony, które zdobyły w sumie ponad 90 medali. Poniżej wyjaśniono, jak to zrobić.

   1. W tabeli przestawnej kliknij menu rozwijane po prawej stronie obszaru Etykiety kolumn.

   2. Wybierz pozycję Filtry wartości i wybierz pozycję Większe niż...

   3. Wpisz 90 w ostatnim polu (po prawej). Kliknij przycisk OK.
      

Tabela przestawna powinna wyglądać podobnie jak na poniższym obrazie.

Przy niewielkim nakładzie pracy została utworzona podstawowa tabela przestawna, która zawiera pola z trzech różnych tabel. To zadanie było tak proste dzięki istniejącym wcześniej relacjom między tabelami. Ponieważ w źródłowej bazie danych istniały relacje między tabelami i wszystkie tabele zostały zaimportowane za pomocą jednej operacji, program Excel mógł odtworzyć te relacje tabel w modelu danych.

Ale co zrobić, jeśli dane pochodzą z różnych źródeł lub zostaną zaimportowane w późniejszym czasie? Zwykle relacje można utworzyć przy użyciu nowych danych na podstawie zgodnych kolumn. W następnym kroku zaimportujesz dodatkowe tabele i dowiesz się, jak tworzyć nowe relacje.

Importowanie danych z arkusza kalkulacyjnego

Teraz zaimportujemy dane z innego źródła, tym razem z istniejącego skoroszytu, a następnie określimy relacje między istniejącymi i nowymi danymi. Relacje umożliwiają analizowanie zbiorów danych w programie Excel oraz tworzenie ciekawych i immersywnych wizualizacji na podstawie zaimportowanych danych.

Zaczniemy od utworzenia pustego arkusza, a następnie zaimportujemy dane ze skoroszytu programu Excel.

1. Wstaw nowy arkusz programu Excel i nadaj mu nazwę Sports.

2. Przejdź do folderu, który zawiera pobrane pliki danych przykładowych, i otwórz plik OlympicSports.xlsx.

3. Zaznacz i skopiuj dane z arkusza Sheet1. Jeśli zaznaczysz komórkę z danymi, taką jak komórka A1, możesz nacisnąć klawisze Ctrl + A, aby zaznaczyć wszystkie dane przylegające. Zamknij skoroszyt OlympicSports.xlsx.

4. W arkuszu Sports umieść kursor w komórce A1 i wklej dane.

5. Gdy dane będą nadal zaznaczone, naciśnij klawisze Ctrl + T, aby sformatować dane jako tabelę. Możesz też sformatować dane jako tabelę, wybierając pozycję NARZĘDZIA GŁÓWNE > Formatuj jako tabelę na wstążce. Ponieważ dane mają nagłówki, zaznacz pole wyboru Moja tabela zawiera nagłówki w wyświetlonym oknie Tworzenie tabeli, jak pokazano tutaj.
   
   
   
   Formatowanie danych jako tabeli ma wiele zalet. Do tabeli można przypisać nazwę, która ułatwia jej identyfikowanie. Można również ustanowić relację między tabelami, co umożliwi eksplorowanie i analizowanie w tabelach przestawnych, dodatku Power Pivot i programie Power View.

6. Nadaj nazwę tabeli. W obszarze NARZĘDZIA TABEL > PROJEKTOWANIE > Właściwości znajdź pole Nazwa tabeli i wpisz Sports. Skoroszyt powinien wyglądać podobnie jak na poniższym obrazie.
   

7. Zapisz skoroszyt.

Importowanie danych za pomocą kopiowania i wklejania

Teraz, gdy dane zostały zaimportowane ze skoroszytu programu Excel, zaimportujemy dane z tabeli znalezionej na stronie sieci Web lub dowolnego innego źródła, z którego można je skopiować i wkleić do programu Excel. W poniższych krokach zostaną dodane miasta będące gospodarzami igrzysk olimpijskich z tabeli.

1. Wstaw nowy arkusz programu Excel i nadaj mu nazwę Hosts.

2. Zaznacz i skopiuj następującą tabelę wraz z nagłówkami tabeli.

1. W programie Excel umieść kursor w komórce A1 arkusza Hosts i wklej dane.

2. Sformatuj dane jako tabelę. Jak opisano wcześniej w tym samouczku, naciśnij klawisze Ctrl + T, aby sformatować dane jako tabelę, lub wybierz pozycję NARZĘDZIA GŁÓWNE > Formatuj jako tabelę. Ponieważ dane mają nagłówki, w wyświetlonym oknie Tworzenie tabeli zaznacz pole wyboru Moja tabela zawiera nagłówki.

3. Nadaj nazwę tabeli. W obszarze NARZĘDZIA TABEL > PROJEKTOWANIE > Właściwości znajdź pole Nazwa tabeli i wpisz Hosts.

4. Zaznacz kolumnę Edition i przy użyciu karty NARZĘDZIA GŁÓWNE sformatuj ją jako typ Liczba z 0 miejscami dziesiętnymi.

5. Zapisz skoroszyt. Skoroszyt powinien wyglądać podobnie jak na poniższym obrazie.

Teraz, gdy masz skoroszyt programu Excel z tabelami, możesz utworzyć relacje między nimi. Tworzenie relacji między tabelami pozwala na łączenie danych z dwóch tabel.

Tworzenie relacji między zaimportowanymi danymi

W tabeli przestawnej można od razu zacząć używać pól z zaimportowanych tabel. Jeśli program Excel nie może określić, jak włączyć pole do tabeli przestawnej, należy ustanowić relację z istniejącym modelem danych. W poniższych krokach dowiesz się, jak utworzyć relację między danymi, które zostały zaimportowane z różnych źródeł.

1. W arkuszu Arkusz1 u góry obszaruPola tabeli przestawnej kliknij pozycjęWszystkie , aby wyświetlić pełną listę dostępnych tabel, jak pokazano na poniższym obrazie.
   

2. Przewiń listę, aby zobaczyć nowe, właśnie dodane tabele.

3. Rozwiń tabelę Sports i wybierz pozycję Sport, aby dodać ją do tabeli przestawnej. W programie Excel zostanie wyświetlony monit o utworzenie relacji podobny do przedstawionego na poniższym obrazie.
   
    

   To powiadomienie jest wyświetlane, ponieważ użyto pól z tabeli, która nie jest częścią źródłowego modelu danych. Jednym ze sposobów dodania tabeli do modelu danych jest utworzenie relacji z tabelą, która już jest w modelu danych. Aby utworzyć relację, jedna z tabel musi zawierać kolumnę unikatowych, niepowtarzających się wartości. W danych przykładowych tabela Disciplines zaimportowana z bazy danych zawiera pole z kodami sportów o nazwie SportID. Te same kody sportów są obecne jako pole w zaimportowanych danych programu Excel. Utwórzmy relację.

4. Kliknij przycisk UTWÓRZ... w wyróżnionym obszarze okienka Pola tabeli przestawnej, aby otworzyć okno dialogowe Tworzenie relacji widoczne na poniższym obrazie.
   
   

5. Z listy rozwijanej Tabela wybierz pozycję Disciplines.

6. W polu Kolumna (obiekt obcy) wybierz pozycję SportID.

7. W polu Powiązana tabela wybierz pozycję Sports.

8. W polu Pokrewna kolumna (obiekt podstawowy) wybierz pozycję SportID.

9. Kliknij przycisk OK.

Tabela przestawna zmieni się w celu odzwierciedlenia nowej relacji. Jednak tabela przestawna nie wygląda jeszcze odpowiednio ze względu na kolejność pól w obszarze WIERSZE. Dyscyplina jest podkategorią danego sportu, ale ponieważ w obszarze WIERSZE pozycja Discipline jest umieszczona powyżej pozycji Sport, nie jest ona właściwie skategoryzowana. Na poniższym obrazie przedstawiono tę niewłaściwą kolejność.

1. W obszarze WIERSZE przenieś pozycję Sport powyżej pozycji Discipline. Teraz wygląda to o wiele lepiej i dane w tabeli przestawnej są wyświetlane odpowiednio, jak przedstawiono na poniższym obrazie.
   
   

W tle program Excel tworzy model danych, którego można używać globalnie w skoroszycie w dowolnych tabelach przestawnych, wykresach przestawnych, w dodatku Power Pivot lub dowolnym raporcie programu Power View. Relacje tabeli stanowią podstawę modelu danych, a także określają sposoby nawigacji i obliczeń.

W następnym samouczku Rozszerzanie relacji modelu danych za pomocą programu Excel 2013, Power Pivoti języka DAX opierasz się na tym, czego się tutaj nauczyliśmy, i krok po kroku rozszerzając model danych przy użyciu zaawansowanego i wizualnego dodatku programu Excel o nazwie Power Pivot. Dowiesz się także, jak obliczać kolumny w tabeli i używać tej kolumny obliczeniowej, aby można było dodać niepowiązaną tabelę do modelu danych.

Punkt kontrolny i test

Sprawdzenie zakresu opanowanego materiału

Masz teraz skoroszyt programu Excel zawierający tabelę przestawną uzyskującą dostęp do danych z wielu tabel, z których kilka zaimportowano oddzielnie. Wiesz już, jak importować z bazy danych, z innego skoroszytu programu Excel oraz przez kopiowanie i wklejanie danych do programu Excel.

Aby te dane współdziałały, trzeba było utworzyć relacje między tabelami, za pomocą których program Excel koreluje wiersze. Wiesz już także, że do tworzenia relacji i wyszukiwania powiązanych wierszy konieczne jest istnienie w jednej tabeli kolumn skorelowanych z danymi w innej tabeli.

Zdobyta wiedza pozwala na przejście do następnego samouczka w tej serii. Oto link:

Rozszerzanie relacji modelu danych przy użyciu programu Excel 2013, dodatku Power Pivot i języka DAX

TEST

Chcesz sprawdzić przyswojoną wiedzę? Możesz to teraz zrobić. Poniższy test koncentruje się na funkcjach, możliwościach i wymaganiach, o których była mowa w tym samouczku. Odpowiedzi znajdziesz u dołu strony. Powodzenia!

Pytanie 1. Dlaczego przekonwertowanie zaimportowanych danych na tabele jest ważne?

A. Nie trzeba konwertować ich na tabele, ponieważ wszystkie zaimportowane dane są automatycznie przekształcane w tabele.

B. Jeśli przekonwertujesz zaimportowane dane na tabele, zostaną one wykluczone z modelu danych. Tylko w przypadku wykluczenia z modelu danych są one dostępne w tabelach przestawnych, Power Pivot i programie Power View.

C. Jeśli zaimportowane dane zostaną przekonwertowane na tabele, będą mogły zostać uwzględnione w modelu danych i zostaną udostępnione dla tabel przestawnych, dodatku Power Pivot i programu Power View.

D. Nie można przekonwertować zaimportowanych danych na tabele.

Pytanie 2. Które z następujących źródeł danych można zaimportować do programu Excel i dołączyć do modelu danych?

A. Bazy danych programu Access, jak również wiele innych baz danych.

B. Istniejące pliki programu Excel.

C. Wszystko, co można skopiować i wkleić do programu Excel i sformatować jako tabelę, w tym tabele danych z witryn sieci Web, dokumentów lub dowolnych innych źródeł, które mogą być wklejane do programu Excel.

D. Wszystkie powyższe odpowiedzi są poprawne.

Pytanie 3. Co się stanie w tabeli przestawnej, gdy zmienisz kolejność pól w czterech obszarach okienka Pola tabeli przestawnej?

A. Nic — nie można zmienić kolejności pól po umieszczeniu ich w obszarach okienka Pola tabeli przestawnej.

B. Format tabeli przestawnej zostanie zmieniony w celu odzwierciedlenia układu, ale dane źródłowe pozostaną nienaruszone.

C. Format tabeli przestawnej zostanie zmieniony w celu odzwierciedlenia układu i wszystkie dane źródłowe zostaną trwale zmienione.

D. Dane źródłowe zostaną zmienione, co spowoduje powstanie nowych zestawów danych.

Pytanie 4. Co jest wymagane podczas tworzenia relacji między tabelami?

A. Żadna z tabel nie może mieć jakiejkolwiek kolumny, która zawiera unikatowe, niepowtarzające się wartości.

B. Jedna tabela nie może być częścią skoroszytu programu Excel.

C. Kolumny nie mogą być konwertowane na tabele.

D. Żadna z powyższych odpowiedzi nie jest poprawna.

Odpowiedzi do testu

1. Prawidłowa odpowiedź: C

2. Prawidłowa odpowiedź: D

3. Prawidłowa odpowiedź: B

4. Prawidłowa odpowiedź: D