Zaawansowana analiza danych - model danych

Model danych jest dostępny w programie Excel 2013 i nowszych wersjach. W programie Excel można używać modelu danych do integracji danych z wielu tabel w bieżącym skoroszycie i / lub z zaimportowanych danych i / lub ze źródeł danych połączonych ze skoroszytem za pośrednictwem połączeń danych.

Za pomocą modelu danych można tworzyć relacje między tabelami. Model danych jest używany w sposób przejrzysty w raportach tabeli przestawnej, wykresie przestawnym, PowerPivot i Power View.

Tworzenie modelu danych podczas importowania danych

Podczas importowania danych z relacyjnych baz danych, takich jak baza danych Microsoft Access, które zawierają wiele powiązanych tabel, model danych jest tworzony automatycznie, jeśli importujesz więcej niż jedną tabelę naraz.

Opcjonalnie możesz dodać tabele do modelu danych podczas importowania danych z następujących źródeł danych -

Relacyjne bazy danych, jedna tabela na raz
Pliki tekstowe
Skoroszyty programu Excel

Na przykład podczas importowania danych ze skoroszytu programu Excel można zaobserwować tę opcję Add this data to the Data Model, z włączonym polem wyboru.

Jeśli chcesz dodać dane, które importujesz do modelu danych, zaznacz to pole.

Tworzenie modelu danych z tabel programu Excel

Model danych można utworzyć z tabel programu Excel za pomocą poleceń programu PowerPivot. Szczegółowe informacje na temat programu PowerPivot znajdują się w dalszych rozdziałach.

Wszystkie polecenia modelu danych są dostępne na karcie PowerPivot na Wstążce. Za pomocą tych poleceń można dodawać tabele programu Excel do modelu danych.

Rozważ następujący skoroszyt danych sprzedaży, w którym masz arkusz Katalogu produktów zawierający produkt, identyfikator produktu i cenę. Masz cztery arkusze do sprzedaży w 4 regionach - wschodnim, północnym, południowym i zachodnim.

Każdy z tych czterech arkuszy zawiera liczbę sprzedanych jednostek i całkowitą ilość dla każdego produktu w każdym miesiącu. Musisz obliczyć całkowitą kwotę dla każdego produktu w każdym regionie i całkowitą kwotę sprzedaży w każdym regionie.

Poniższe kroki umożliwiają osiągnięcie pożądanych rezultatów -

Zacznij od stworzenia modelu danych.
Kliknij arkusz roboczy Katalog produktów.
Kliknij kartę POWERPIVOT na Wstążce.
Kliknij opcję Dodaj do modelu danych. Pojawi się okno dialogowe Utwórz tabelę.
Wybierz zakres tabeli.
Zaznacz pole Moja tabela ma nagłówki. Kliknij OK.

Pojawi się nowe okno - PowerPivot for Excel - <nazwa pliku programu Excel>.

Na środku pustego okna pojawia się następujący komunikat -

Tabela Backlog produktu dodana do modelu danych jest wyświetlana jako arkusz w oknie programu PowerPivot. Każdy wiersz w tabeli jest rekordem i można przechodzić między rekordami za pomocą przycisków strzałek w lewo i w prawo na dole okna.

Kliknij kartę Połączona tabela w oknie programu PowerPivot.
Kliknij opcję Przejdź do tabeli programu Excel.

Zostanie wyświetlone okno danych programu Excel.

Kliknij kartę arkusza - Wschód.
Kliknij kartę POWERPIVOT na Wstążce.
Kliknij opcję Dodaj do modelu danych.

W oknie programu PowerPivot pojawi się inny arkusz zawierający tabelę wschodnią.

Powtórz dla arkuszy roboczych - północ, południe i zachód. W sumie dodałeś pięć tabel do modelu danych. Twoje okno PowerPivot wygląda jak poniżej -

Tworzenie relacji między tabelami

Jeśli chcesz wykonywać obliczenia w tabelach, musisz najpierw zdefiniować relacje między nimi.

Kliknij kartę Narzędzia główne na Wstążce w oknie programu PowerPivot. Jak widać, tabele są wyświetlane w Widoku danych.
Kliknij Widok diagramu.

Tabele pojawią się w widoku diagramu. Jak widać, niektóre tabele mogą znajdować się poza obszarem wyświetlania, a wszystkie pola w tabelach mogą być niewidoczne.

Zmień rozmiar każdej tabeli, aby wyświetlić wszystkie pola w tej tabeli.
Przeciągnij i ułóż tabele tak, aby wszystkie były wyświetlane.

W tabeli Wschód kliknij Identyfikator produktu.
Kliknij kartę Projekt na Wstążce.
Kliknij opcję Utwórz relację. Pojawi się okno dialogowe Utwórz relację.

W polu pod tabelą wyświetlany jest wschód. W polu pod kolumną wyświetlany jest identyfikator produktu.

W polu pod Powiązaną tabelą przeglądową wybierz opcję Katalog produktów.
Identyfikator produktu pojawi się w polu pod Powiązaną kolumną odnośników.
Kliknij przycisk Utwórz.

Pojawi się wiersz przedstawiający relacje między tabelami Wschód i Backlog produktu.

Powtórz te same kroki dla tabel - północ, południe i zachód. Pojawiają się linie relacji.

Podsumowanie danych w tabelach w modelu danych

Teraz możesz podsumować dane sprzedaży dla każdego produktu w każdym regionie w zaledwie kilku krokach.

Kliknij kartę Strona główna.
Kliknij opcję Tabela przestawna.
Z listy rozwijanej wybierz opcję Tabela przestawna.

W oknie tabel programu Excel pojawi się okno dialogowe Utwórz tabelę przestawną. Wybierz Nowy arkusz roboczy.

W nowym arkuszu pojawi się pusta tabela przestawna. Jak można zauważyć, Lista pól zawiera wszystkie tabele w modelu danych ze wszystkimi wyświetlonymi polami.

Wybierz identyfikator produktu z tabeli 1 (katalog produktów).
Wybierz opcję Łączna kwota z pozostałych czterech tabel.
Dla każdego pola w ∑ Wartości zmień nazwę niestandardową w Ustawieniach pola wartości, aby nazwy regionów były wyświetlane jako etykiety kolumn.

Suma łącznej kwoty zostanie zastąpiona podaną przez Ciebie etykietą. Tabela przestawna z podsumowanymi wartościami ze wszystkich tabel danych przedstawia wymagane wyniki.

Dodawanie danych do modelu danych

Możesz dodać nową tabelę danych do modelu danych lub nowe wiersze danych do istniejących tabel w modelu danych.

Dodaj nową tabelę danych do modelu danych, wykonując następujące czynności.

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. Zostaną wyświetlone nazwy wszystkich tabel w skoroszycie.
Kliknij nazwę tabeli, którą chcesz dodać do modelu danych.

Kliknij przycisk Otwórz. Pojawi się okno dialogowe Importuj dane.

Jak wiesz, podczas importowania tabeli danych jest ona automatycznie dodawana do modelu danych. Nowo dodana tabela pojawi się w oknie programu PowerPivot.

Dodaj nowe wiersze danych do istniejących tabel w modelu danych.

Odśwież połączenie danych. Nowe wiersze danych ze źródła danych są dodawane do modelu danych.