Abstrakcja: Jest to pierwszy samouczek z serii mającej na celu zapoznanie się i wygodę korzystania z programu Excel oraz wbudowanych funkcji połączonych danych i analizy. W tych samouczkach od podstaw jest tworzony i udoskonalany skoroszyt programu Excel, jest konstruowany model danych, a następnie są tworzone wysoce interakcyjne raporty przy użyciu programu Power View. Samouczki opracowano tak, aby zaprezentować funkcje i możliwości analizy biznesowej firmy Microsoft w programie Excel, tabelach przestawnych, dodatku Power Pivot i programie Power View.
W tych samouczkach dowiesz się, jak importować i eksplorować dane w programie Excel, tworzyć i uściślać model danych przy użyciu dodatku Power Pivot oraz tworzyć interakcyjne raporty w programie Power View, które można publikować, chronić i udostępniać.
W tej serii są dostępne następujące samouczki:
- Importowanie danych do Excel 2016 i tworzenie modelu danych
- Rozszerzanie relacji modelu danych przy użyciu programu Excel, dodatku Power Pivot i języka DAX
- Tworzenie raportów programu Power View opartych na mapie
- Dodawanie danych internetowych i ustawianie wartości domyślnych raportu programu Power View
- Dodatek Power Pivot — pomoc
- Tworzenie atrakcyjnych raportów programu Power View — część 2
W tym samouczku zaczynasz pracę od pustego skoroszytu programu Excel.
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ą.
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.
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 olympicmedals.accdb programu Access
> OlympicSports.xlsx skoroszyt programu Excel
> Population.xlsx skoroszyt programu Excel
> DiscImage_table.xlsx skoroszyt programu ExcelOtwórz pusty skoroszyt w programie Excel.
Kliknij pozycję Dane > pobierają dane > z bazy danych z bazy danych > programu Microsoft 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ższym ekranie.
Wybierz pobrany plik OlympicMedals.accdb i kliknij pozycję Importuj. Zostanie wyświetlone następujące okno Nawigator z wyświetlonym tabelami znajdującymi się w bazie danych. Tabele w bazie danych są podobne do arkuszy lub tabel w programie Excel. Zaznacz pole Wyboru wielu tabel i zaznacz wszystkie tabele. Następnie kliknij pozycję Załaduj załaduj > do.
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, dodatku 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 za pomocą dodatku Power Pivot. Model danych jest omówiony bardziej szczegółowo w dalszej części tego samouczka.
Zaznacz opcję Raport w formie tabeli przestawnej, która powoduje zaimportowanie tabel do programu Excel i przygotowanie tabeli przestawnej do analizy zaimportowanych tabel, i kliknij przycisk OK.
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.
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.
Następnie z tabeli Disciplines przeciągnij dyscyplinę do obszaru WIERSZE.
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.
- 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.
- 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.
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).
W tabeli Medals zaznacz ponownie pozycję Medal i przeciągnij ją do obszaru FILTRY.
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ć.
- W tabeli przestawnej kliknij menu rozwijane po prawej stronie obszaru Etykiety kolumn.
- Wybierz pozycję Filtry wartości i wybierz pozycję Większe niż...
- 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.
Wstaw nowy arkusz programu Excel i nadaj mu nazwę Sports.
Przejdź do folderu, który zawiera pobrane pliki danych przykładowych, i otwórz plik OlympicSports.xlsx.
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.
W arkuszu Sports umieść kursor w komórce A1 i wklej dane.
Gdy dane będą nadal zaznaczone, naciśnij klawisze Ctrl + T, aby sformatować dane jako tabelę. Możesz również sformatować dane jako tabelę na wstążce, wybierając pozycję Format HOME > jako tabelę. 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 daje wiele korzyści. Do tabeli można przypisać nazwę, która ułatwia jej identyfikowanie. Możesz również ustanowić relacje między tabelami, umożliwiając eksplorowanie i analizowanie w tabelach przestawnych, dodatku Power Pivot i programie Power View.
Nadaj nazwę tabeli. W obszarze WŁAŚCIWOŚCI PROJEKTU > TABELI znajdź pole Nazwa tabeli i wpisz Sports. Skoroszyt powinien wyglądać podobnie jak na poniższym obrazie.
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.
- Wstaw nowy arkusz programu Excel i nadaj mu nazwę Hosts.
- Zaznacz i skopiuj następującą tabelę wraz z nagłówkami tabeli.
- W programie Excel umieść kursor w komórce A1 arkusza Hosts i wklej dane.
- Sformatuj dane jako tabelę. Jak opisano wcześniej w tym samouczku, naciśnij klawisze Ctrl + T, aby sformatować dane jako tabelę, lub z formatu HOME > jako tabeli. Ponieważ dane mają nagłówki, w wyświetlonym oknie Tworzenie tabeli zaznacz pole wyboru Moja tabela zawiera nagłówki.
- Nadaj nazwę tabeli. We właściwościach PROJEKTU > TABELI znajdź pole Nazwa tabeli i wpisz Hosts.
- Zaznacz kolumnę Edition i przy użyciu karty NARZĘDZIA GŁÓWNE sformatuj ją jako typ Liczba z 0 miejscami dziesiętnymi.
- 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ł.
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.
Przewiń listę, aby zobaczyć nowe, właśnie dodane tabele.
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ę.Kliknij przycisk UTWÓRZ... w wyróżnionym obszarze okienka Pola tabeli przestawnej, aby otworzyć okno dialogowe Tworzenie relacji widoczne na poniższym obrazie.
W obszarze Tabela wybierz z listy rozwijanej pozycję Tabela modelu danych: Dyscypliny .
W polu Kolumna (obiekt obcy) wybierz pozycję SportID.
W polu Powiązana tabela wybierz pozycję Tabela modelu danych: Sport.
W polu Pokrewna kolumna (obiekt podstawowy) wybierz pozycję SportID.
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ść.
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 konstruuje model danych, którego można używać w całym skoroszycie, w dowolnej tabeli przestawnej, na wykresie przestawnym, w dodatku Power Pivot lub w 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ą programów Excel, Power Pivot**i 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:
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 wtedy, gdy są one wykluczone z modelu danych, są dostępne w tabelach przestawnych, dodatku Power Pivot i programie Power View.
C. Jeśli zaimportowane dane zostaną przekonwertowane na tabele, mogą zostać uwzględnione w modelu danych i udostępnione tabelom przestawnym, dodatkom Power Pivot i 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
- Prawidłowa odpowiedź: C
- Prawidłowa odpowiedź: D
- Prawidłowa odpowiedź: B
- Prawidłowa odpowiedź: D
Uwaga
Dane i obrazy wykorzystane w tym samouczku zostały przygotowane na podstawie następujących materiałów:
- Olympics Dataset od Guardian News & Media Ltd.
- Obrazy flag od CIA Factbook (cia.gov)
- Dane dotyczące populacji od Banku Światowego (worldbank.org)
- Piktogramy dyscyplin olimpijskich od użytkowników Thadius856 i Parutakupiu