Excel Power Pivot - zarządzanie modelem danych
Głównym zastosowaniem dodatku Power Pivot jest możliwość zarządzania tabelami danych i relacjami między nimi w celu ułatwienia analizy danych z kilku tabel. Możesz dodać tabelę programu Excel do modelu danych podczas tworzenia tabeli przestawnej lub bezpośrednio ze wstążki programu PowerPivot.
Dane z wielu tabel można analizować tylko wtedy, gdy istnieją między nimi relacje. Za pomocą dodatku Power Pivot można tworzyć relacje w widoku danych lub widoku diagramu. Ponadto, jeśli zdecydowałeś się dodać tabelę do dodatku Power Pivot, musisz również dodać relację.
Dodawanie tabel programu Excel do modelu danych za pomocą tabeli przestawnej
Podczas tworzenia tabeli przestawnej w programie Excel jest ona oparta tylko na jednej tabeli / zakresie. Jeśli chcesz dodać więcej tabel do tabeli przestawnej, możesz to zrobić za pomocą modelu danych.
Załóżmy, że masz w skoroszycie dwa arkusze -
Jeden zawierający dane sprzedawców i reprezentowanych przez nich regionów w tabeli - Sprzedawca.
Kolejna zawierająca dane sprzedaży według regionu i miesiąca, w tabeli - Sprzedaż.
Możesz podsumować sprzedaż - z punktu widzenia sprzedawcy, jak podano poniżej.
Kliknij tabelę - Sprzedaż.
Kliknij kartę WSTAW na Wstążce.
Wybierz tabelę przestawną w grupie tabele.
Zostanie utworzona pusta tabela przestawna z polami z tabeli Sales - Region, Month i Order Amount. Jak widać, istnieje plikMORE TABLES polecenie poniżej listy pól tabeli przestawnej.
Kliknij WIĘCEJ TABEL.
Plik Create a New PivotTablepojawi się okno komunikatu. Wyświetlany komunikat brzmi: Aby użyć wielu tabel w analizie, należy utworzyć nową tabelę przestawną przy użyciu modelu danych. Kliknij Tak
Zostanie utworzona nowa tabela przestawna, jak pokazano poniżej -
W obszarze Pola tabeli przestawnej można zauważyć, że istnieją dwie zakładki - ACTIVE i ALL.
Kliknij kartę WSZYSTKIE.
Na liście Pola tabeli przestawnej pojawią się dwie tabele - Sales i Salesperson z odpowiednimi polami.
Kliknij pole Sprzedawca w tabeli Sprzedawca i przeciągnij je do obszaru WIERSZE.
Kliknij pole Miesiąc w tabeli Sprzedaż i przeciągnij je do obszaru WIERSZE.
Kliknij pole Kwota zamówienia w tabeli Sprzedaż i przeciągnij je do obszaru ∑ WARTOŚCI.
Zostanie utworzona tabela przestawna. W polach tabeli przestawnej pojawia się komunikat -Relationships between tables may be needed.
Kliknij przycisk UTWÓRZ obok wiadomości. PlikCreate Relationship pojawi się okno dialogowe.
Pod Tablewybierz Sprzedaż.
Pod Column (Foreign) wybierz Region.
Pod Related Tablewybierz Sprzedawca.
Pod Related Column (Primary) wybierz Region.
Kliknij OK.
Twoja tabela przestawna z dwóch tabel na dwóch arkuszach jest gotowa.
Ponadto, jak stwierdził Excel podczas dodawania drugiej tabeli do tabeli przestawnej, tabela przestawna została utworzona za pomocą modelu danych. Aby zweryfikować, wykonaj następujące czynności -
Kliknij kartę POWERPIVOT na Wstążce.
Kliknij Managew grupie Model danych. Zostanie wyświetlony widok danych dodatku Power Pivot.
Można zauważyć, że dwie tabele programu Excel, które zostały użyte podczas tworzenia tabeli przestawnej, są konwertowane na tabele danych w modelu danych.
Dodawanie tabel programu Excel z innego skoroszytu do modelu danych
Załóżmy, że dwie tabele - Sprzedawca i Sprzedaż znajdują się w dwóch różnych skoroszytach.
Możesz dodać tabelę programu Excel z innego skoroszytu do modelu danych w następujący sposób -
Kliknij tabelę Sales.
Kliknij kartę WSTAW.
Kliknij opcję Tabela przestawna w grupie Tabele. PlikCreate PivotTable pojawi się okno dialogowe.
W polu Tabela / zakres wpisz Sales.
Kliknij Nowy arkusz roboczy.
Zaznacz pole Dodaj te dane do modelu danych.
Kliknij OK.
Otrzymasz pustą tabelę przestawną w nowym arkuszu z tylko polami odpowiadającymi tabeli Sales.
Dodano dane tabeli Sales do modelu danych. Następnie musisz pobrać dane tabeli sprzedawcy również do modelu danych w następujący sposób -
Kliknij arkusz zawierający tabelę sprzedaży.
Kliknij kartę DANE na Wstążce.
Kliknij opcję Istniejące połączenia w grupie Pobierz dane zewnętrzne. Pojawi się okno dialogowe Istniejące połączenia.
Kliknij kartę Tabele.
Pod This Workbook Data Model, 1 tablejest wyświetlany (jest to tabela Sales dodana wcześniej). Znajdziesz również dwa skoroszyty wyświetlające zawarte w nich tabele.
Kliknij opcję Sprzedawca w sekcji Salesperson.xlsx.
Kliknij Otwórz. PlikImport Data pojawi się okno dialogowe.
Kliknij Raport tabeli przestawnej.
Kliknij Nowy arkusz roboczy.
Widać, że pudełko - Add this data to the Data Modeljest zaznaczone i nieaktywne. Kliknij OK.
Zostanie utworzona tabela przestawna.
Jak widać, dwie tabele znajdują się w modelu danych. Może być konieczne utworzenie relacji między dwiema tabelami, tak jak w poprzedniej sekcji.
Dodawanie tabel programu Excel do modelu danych ze wstążki programu PowerPivot
Innym sposobem dodawania tabel programu Excel do modelu danych jest robienie so from the PowerPivot Ribbon.
Załóżmy, że masz w skoroszycie dwa arkusze -
Jeden zawierający dane sprzedawców i reprezentowanych przez nich regionów w tabeli - Sprzedawca.
Kolejna zawierająca dane sprzedaży według regionu i miesiąca, w tabeli - Sprzedaż.
Możesz najpierw dodać te tabele programu Excel do modelu danych, zanim wykonasz jakąkolwiek analizę.
Kliknij tabelę Excela - Sprzedaż.
Kliknij kartę POWERPIVOT na Wstążce.
Kliknij opcję Dodaj do modelu danych w grupie Tabele.
Zostanie wyświetlone okno dodatku Power Pivot z dodaną tabelą danych Sprzedawca. Kolejna karta - połączona tabela pojawia się na Wstążce w oknie Power Pivot.
Kliknij kartę Połączona tabela na Wstążce.
Kliknij Tabela Excel: Sprzedawca.
Możesz zauważyć, że nazwy dwóch tabel obecnych w skoroszycie są wyświetlane, a nazwa Sprzedawca jest zaznaczona. Oznacza to, że tabela danych Sprzedawca jest połączona z tabelą programu Excel Sprzedawca.
Kliknij Go to Excel Table.
Pojawi się okno programu Excel z arkuszem roboczym zawierającym tabelę handlowców.
Kliknij kartę Arkusz sprzedaży.
Kliknij tabelę Sales.
Kliknij opcję Dodaj do modelu danych w grupie Tabele na Wstążce.
Tabela sprzedaży programu Excel jest również dodawana do modelu danych.
Jeśli chcesz przeprowadzić analizę na podstawie tych dwóch tabel, jak wiesz, musisz utworzyć relację między dwiema tabelami danych. W dodatku Power Pivot możesz to zrobić na dwa sposoby -
Z widoku danych
Z widoku diagramu
Tworzenie relacji z widoku danych
Jak wiesz, w widoku danych możesz wyświetlać tabele danych zawierające rekordy jako wiersze, a pola jako kolumny.
Kliknij kartę Projekt w oknie Power Pivot.
Kliknij Utwórz relację w grupie Relacje. PlikCreate Relationship pojawi się okno dialogowe.
Kliknij Sprzedaż w polu Tabela. To jest tabela, od której zaczyna się związek. Jak wiesz, kolumna powinna być polem obecnym w powiązanej tabeli Sprzedawca, która zawiera unikatowe wartości.
Kliknij Region w polu Kolumna.
Kliknij Sprzedawca w polu Powiązana połączona tabela.
Powiązana połączona kolumna zostanie automatycznie wypełniona regionem.
Kliknij przycisk Utwórz. Powstaje relacja.
Tworzenie relacji z widoku diagramu
Tworzenie relacji z widoku diagramu jest stosunkowo łatwiejsze. Postępuj zgodnie z podanymi krokami.
Kliknij kartę Narzędzia główne w oknie Power Pivot.
Kliknij opcję Widok diagramu w grupie Widok.
Widok diagramu modelu danych zostanie wyświetlony w oknie dodatku Power Pivot.
Kliknij Region w tabeli Sales. Region w tabeli Sales jest podświetlony.
Przeciągnij do regionu w tabeli Sprzedawca. Podświetlony jest również region w tabeli Sprzedawca. W kierunku przeciągnięcia pojawi się linia.
Z tabeli Sprzedaż do tabeli Sprzedawca pojawia się wiersz wskazujący relację.
Jak widać, pojawia się wiersz z tabeli Sales do tabeli Salesperson, wskazujący relację i kierunek.
Jeśli chcesz poznać pole, które jest częścią relacji, kliknij linię relacji. Wiersz i pole w obu tabelach są podświetlone.
Zarządzanie relacjami
Możesz edytować lub usunąć istniejącą relację w modelu danych.
Kliknij kartę Projekt w oknie Power Pivot.
Kliknij opcję Zarządzaj relacjami w grupie Relacje. Pojawi się okno dialogowe Zarządzaj relacjami.
Wyświetlane są wszystkie relacje istniejące w modelu danych.
Aby edytować relację
Kliknij związek.
Kliknij Editprzycisk. PlikEdit Relationship pojawi się okno dialogowe.
Wprowadź wymagane zmiany w relacji.
Kliknij OK. Zmiany znajdują odzwierciedlenie w relacji.
Aby usunąć związek
Kliknij związek.
Kliknij przycisk Usuń. Zostanie wyświetlony komunikat ostrzegawczy pokazujący, jak tabele, na które ma wpływ usunięcie relacji, wpłyną na raporty.
Kliknij OK, jeśli na pewno chcesz usunąć. Wybrana relacja zostanie usunięta.
Odświeżanie danych dodatku Power Pivot
Załóżmy, że modyfikujesz dane w tabeli programu Excel. Możesz dodawać / zmieniać / usuwać dane w tabeli Excel.
Aby odświeżyć dane programu PowerPivot, wykonaj następujące czynności -
Kliknij kartę Połączona tabela w oknie Power Pivot.
Kliknij Aktualizuj wszystko.
Tabela danych jest aktualizowana o modyfikacje dokonane w tabeli Excel.
Jak widać, nie można bezpośrednio modyfikować danych w tabelach danych. Dlatego lepiej jest przechowywać dane w tabelach programu Excel, które są połączone z tabelami danych podczas dodawania ich do modelu danych. Ułatwia to aktualizację danych w tabelach danych podczas aktualizacji danych w tabelach programu Excel.