Tworzenie modelu danych w programie Excel

Model danych umożliwia integrację danych z wielu tabel, co pozwala efektywnie utworzyć relacyjne źródło danych wewnątrz skoroszytu programu Excel. W programie Excel modele danych są używane w sposób przezroczysty, zapewniając dane tabelaryczne używane w tabelach przestawnych i na wykresach przestawnych. Model danych jest wizualizowany jako zbiór tabel na liście pól i w większości przypadków nigdy nawet nie dowiesz się, że jest tam.

Przed rozpoczęciem pracy z modelem danych należy uzyskać pewne dane. W tym celu użyjemy funkcji Uzyskaj & Przekształcanie (Power Query), więc możesz zrobić krok wstecz i obejrzeć klip wideo lub skorzystać z naszego przewodnika szkoleniowego dotyczącego przekształcania & i dodatku Power Pivot.

Gdzie znajduje się dodatek Power Pivot?

Excel 2016 & Excel dla Microsoft 365 — dodatek Power Pivot jest dołączony do Wstążki.
Excel 2013 — dodatek Power Pivot jest częścią Office Professional Plus wersji programu Excel 2013, ale domyślnie nie jest włączony. Dowiedz się więcej o uruchamianiu dodatku Power Pivot dla programu Excel 2013.
Excel 2010 — pobierz dodatek Power Pivot, a następnie zainstaluj dodatek Power Pivot.

Gdzie znajduje się funkcja Przekształcanie & (Power Query)?

Excel 2016 & Excel dla Microsoft 365 — integracja funkcji Przekształcanie & (Power Query) z programem Excel na karcie Dane.
Excel 2013 — Power Query to dodatek dołączony do programu Excel, ale musi zostać aktywowany. Przejdź do pozycji Opcje > plików > dodatki, a następnie z listy rozwijanej Zarządzanie u dołu okienka wybierz pozycję Dodatki COM > Przejdź. Sprawdź program Microsoft Power Query dla programu Excel, a następnie kliknij przycisk OK, aby go aktywować. Do wstążki zostanie dodana karta Power Query.
Excel 2010 — pobieranie i instalowanie dodatku Power Query.. Po aktywowaniu do wstążki zostanie dodana karta Power Query.

Wprowadzenie

Najpierw musisz uzyskać trochę danych.

W Excel 2016 i Excel dla Microsoft 365 użyj > Danych & Przekształcanie danych > Pobieranie danych w celu zaimportowania danych z dowolnej liczby zewnętrznych źródeł danych, takich jak plik tekstowy, skoroszyt programu Excel, witryna internetowa, program Microsoft Access, SQL Server lub inna relacyjna baza danych zawierająca wiele powiązanych tabel.

W programach Excel 2013 i 2010 przejdź do Power Query > Pobieranie danych zewnętrznych i wybierz źródło danych.
Program Excel wyświetli monit o wybranie tabeli. Jeśli chcesz pobrać wiele tabel z tego samego źródła danych, zaznacz opcję Włącz wybór wielu tabel . Po zaznaczeniu wielu tabel program Excel automatycznie tworzy model danych.

Uwaga: W tych przykładach używamy skoroszytu programu Excel z fikcyjnymi szczegółami uczniów na temat zajęć i ocen. Możesz pobrać przykładowy skoroszyt modelu danych uczniów i przejść dalej. Możesz również pobrać wersję z ukończonym modelem danych..
Zaznacz co najmniej jedną tabelę, a następnie kliknij pozycję Załaduj.

Jeśli chcesz edytować dane źródłowe, możesz wybrać opcję Edytuj . Aby uzyskać więcej szczegółowych informacji, zobacz: Wprowadzenie do Edytor Power Query (Power Query).

Teraz masz model danych zawierający wszystkie zaimportowane tabele, które będą wyświetlane na liście pól tabeli przestawnej.

Uwagi:

Modele są tworzone niejawnie podczas jednoczesnego importowania dwóch lub większej liczby tabel w programie Excel.
Modele są tworzone jawnie podczas importowania danych za pomocą dodatku Power Pivot. W dodatku model jest reprezentowany w układzie na kartach podobnym do programu Excel, w którym każda karta zawiera dane tabelaryczne. Zobacz Pobieranie danych za pomocą dodatku Power Pivot, aby poznać podstawy importowania danych przy użyciu bazy danych SQL Server.
Model może zawierać jedną tabelę. Aby utworzyć model oparty tylko na jednej tabeli, zaznacz tabelę i kliknij pozycję Dodaj do modelu danych w Power Pivot. Możesz to zrobić, jeśli chcesz używać funkcji Power Pivot, takich jak filtrowane zestawy danych, kolumny obliczeniowe, pola obliczeniowe, wskaźniki KPI i hierarchie.
Relacje pomiędzy tabelami można tworzyć automatycznie w przypadku importowania tabel pokrewnych, które mają relacje klucza podstawowego i obcego. Program Excel zazwyczaj używa zaimportowanych informacji o relacjach jako podstawy relacji pomiędzy tabelami w modelu danych.
Aby uzyskać porady dotyczące zmniejszania rozmiaru modelu danych, zobacz Tworzenie modelu danych zapewniającego wydajną obsługę pamięci za pomocą programu Excel i dodatku Power Pivot.
Aby uzyskać więcej informacji, zobacz Samouczek: importowanie danych do programu Excel i Tworzenie modelu danych.

Porada: Jak sprawdzić, czy skoroszyt zawiera model danych? Przejdź do Power Pivot > Zarządzaj. Jeśli widzisz dane podobne do arkusza, wówczas istnieje model. Zobacz: Dowiedz się, które źródła danych są używane w modelu danych skoroszytu , aby dowiedzieć się więcej.

Tworzenie relacji między tabelami

Następnym krokiem jest utworzenie relacji między tabelami, dzięki czemu będzie można pobierać dane z dowolnej z nich. Każda tabela musi mieć klucz podstawowy lub unikatowy identyfikator pola, taki jak Identyfikator ucznia lub Numer zajęć. Najłatwiejszym sposobem jest przeciągnięcie i upuszczenie tych pól w celu ich połączenia w widoku diagramu dodatku Power Pivot.

Przejdź do pozycji Power Pivot > Zarządzaj.
Na karcie Narzędzia główne wybierz pozycję Widok diagramu.
Zostaną wyświetlone wszystkie zaimportowane tabele i może być konieczne trochę czasu, aby zmienić ich rozmiar w zależności od liczby pól, które każdy z nich ma.
Następnie przeciągnij pole klucza podstawowego z jednej tabeli do następnej. Poniższy przykład przedstawia widok diagramu tabel uczniów:

Utworowaliśmy następujące linki:
- tbl_Students | Identyfikator ucznia > tbl_Grades | Identyfikator ucznia
  
  Innymi słowy przeciągnij pole Identyfikator studenta z tabeli Uczniowie do pola Identyfikator studenta w tabeli Oceny.
- tbl_Semesters | Identyfikator semestru > tbl_Grades | Semestr
- tbl_Classes | > tbl_Grades | numeru zajęć Numer zajęć
Uwagi:
- Aby utworzyć relację, nazwy pól nie muszą być takie same, ale muszą być tego samego typu danych.
- Łączniki w widoku diagramu mają po jednej stronie znak "1", a po drugiej "*". Oznacza to, że między tabelami istnieje relacja jeden-do-wielu, która określa sposób wykorzystania danych w tabelach przestawnych. Aby dowiedzieć się więcej, zobacz: Relacje między tabelami w modelu danych .
- Łączniki wskazują jedynie, że istnieje relacja między tabelami. W rzeczywistości nie będą one pokazywały, które pola są ze sobą połączone. Aby wyświetlić linki, przejdź do Zarządzanie relacjami > > do dodatku Power Pivot > Zarządzanie> projektu >. W programie Excel możesz przejść do pozycji Dane > relacje.

Tworzenie tabeli przestawnej lub wykresu przestawnego za pomocą modelu danych

Skoroszyt programu Excel może zawierać tylko jeden model danych, ale ten model może zawierać wiele tabel, których można wielokrotnie używać w całym skoroszycie. W dowolnym momencie możesz dodać więcej tabel do istniejącego modelu danych.

W Power Pivotprzejdź do pozycji Zarządzaj.
Na karcie Narzędzia główne wybierz pozycję Tabela przestawna.
Wybierz miejsce, w którym chcesz umieścić tabelę przestawną: nowy arkusz lub bieżącą lokalizację.
Kliknij przycisk OK, a program Excel doda pustą tabelę przestawną z wyświetlonym po prawej stronie okienkiem Lista pól.

Następnie utwórz tabelę przestawną lub utwórz wykres przestawny. Jeśli relacje między tabelami zostały już utworzone, możesz użyć dowolnego z ich pól w tabeli przestawnej. W przykładowym skoroszycie modelu danych uczniów utworzyliśmy już relacje.

Dodawanie istniejących, niepowiązanych danych do modelu danych

Załóżmy, że zaimportowano lub skopiowano wiele danych, których chcesz użyć w modelu, ale nie dodano ich do modelu danych. Wypychanie nowych danych do modelu jest łatwiejsze niż myślisz.

Zacznij od zaznaczenia dowolnej komórki w danych, które chcesz dodać do modelu. Może to być dowolny zakres danych, ale najlepiej sformatowane jako tabela programu Excel .
Użyj jednej z tych metod, aby dodać dane:
Kliknij pozycjęPower Pivot > Dodaj do modelu danych.
Kliknij pozycję Wstaw > tabeli przestawnej, a następnie zaznacz pole wyboru Dodaj te dane do modelu danych w oknie dialogowym Tworzenie tabeli przestawnej.

Zakres lub tabela zostanie dodana do modelu jako tabela połączona. Aby dowiedzieć się więcej o pracy z tabelami połączonymi w modelu, zobacz Dodawanie danych przy użyciu tabel połączonych programu Excel w dodatku Power Pivot.

Dodawanie danych do tabeli Power Pivot

W Power Pivot nie można dodać wiersza do tabeli, wpisując bezpośrednio w nowym wierszu, tak jak w arkuszu programu Excel. Możesz jednak dodawać wiersze, kopiując i wklejając lub aktualizując dane źródłowe i odświeżając model dodatku Power Pivot.