Tworzenie modelu danych w programie Excel

Dotyczy

Excel dla Microsoft 365 Excel 2024 Excel 2021

Model danych umożliwia integrowanie danych z wielu tabel, tworząc relacyjne źródło danych wewnątrz skoroszytu programu Excel. W programie Excel modele danych są używane w tle i udostępniają dane tabelaryczne używane w tabelach przestawnych i wykresach przestawnych. Model danych jest wizualizowany jako zbiór tabel na liście pól i przez większość czasu pracujesz z nim za pośrednictwem listy pól tabeli przestawnej i możesz tego nie zauważyć.

Przed rozpoczęciem pracy z modelem danych musisz uzyskać pewne dane. W tym celu skorzystamy ze środowiska Power Query Pobierz & przekształcanie, więc może zechcesz cofnąć się o krok i obejrzeć klip wideo lub skorzystać z naszego przewodnika szkoleniowego dotyczącego uzyskiwania & przekształcania i dodatku Power Pivot. Dane powinny znajdować się w tabelach (a nie tylko w zakresach komórek), aby umożliwić ich prawidłowe ładowanie i powiązanie.

Wymagania wstępne

Gdzie znajduje się dodatek Power Pivot?

Excel dla Microsoft 365 — dodatek Power Pivot jest dostępny na Wstążce.

Gdzie jest Get & Transform (Power Query)?

Excel dla Microsoft 365 — Pobieranie & przekształcania (Power Query) została zintegrowana z programem Excel na karcie Dane.

Wprowadzenie

Najpierw potrzebujesz danych.

Utwórz nowy lub otwórz skoroszyt, który nie zawiera danych.
Na Wstążce w programie Excel dla Microsoft 365 wybierz kartę Dane. W sekcji Pobieranie & przekształcanie danych wybierz pozycję Pobierz dane, aby zaimportować dane z dowolnego zewnętrznego źródła danych, takiego 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 programie Excel zostanie wyświetlony monit o wybranie jednej lub większej liczby tabel. Jeśli chcesz pobrać wiele tabel z tego samego źródła danych, zaznacz pole wyboru Wybierz wiele elementów .
1. Wybierz pozycję Przekształć. Po wybraniu wielu tabel program Excel automatycznie tworzy model danych. Aby uzyskać więcej informacji, zobacz: Tworzenie, ładowanie lub edytowanie zapytania w programie Excel (dodatku Power Query).
  
  Uwaga
  
  W tych przykładach używamy skoroszytu programu Excel z fikcyjnymi szczegółami uczniów na temat zajęć i ocen. Możesz pobrać nasz przykładowy skoroszyt modelu danych ucznia i wykonać poniższe czynności. Możesz również pobrać wersję z kompletnym modelem danych.
Masz teraz model danych, który zawiera wszystkie zaimportowane tabele, które zostaną wyświetlone na liście pól tabeli przestawnej.

Uwaga

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 tym dodatku model jest reprezentowany w układzie kart 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 programu SQL Server.
Model może zawierać jedną tabelę. Aby utworzyć model oparty tylko na jednej tabeli, zaznacz tę tabelę i kliknij pozycję Dodaj do modelu danych w dodatku Power Pivot. Możesz to zrobić, jeśli chcesz używać funkcji dodatku Power Pivot, takich jak filtrowane zestawy danych, kolumny obliczeniowe, pola obliczeniowe, wskaźniki KPI i hierarchie.
Relacje pomiędzy tabelami mogą być tworzone automatycznie w przypadku importowania tabel pokrewnych, które mają relacje klucza podstawowego i obcego. W programie Excel zaimportowane informacje o relacjach mogą zazwyczaj służyć jako podstawa relacji mię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 rozpoznać, czy skoroszyt zawiera model danych? Przejdź dopozycji Zarządzaniew dodatku Power Pivot>. Jeśli są wyświetlane dane podobne do arkusza, oznacza to, że model istnieje. 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, aby można było pobierać dane z dowolnych tabel. Każda tabela musi mieć klucz podstawowy lub unikatowy identyfikator pola, taki jak ID studenta lub numer zajęć. Najprostszym sposobem jest przeciągnięcie i upuszczenie tych pól w celu połączenia ich w widoku diagramu dodatku Power Pivot.

Przejdź dopozycji Zarządzaniew dodatku Power Pivot>.
Na karcie Narzędzia główne wybierz pozycję Widok diagramu.
Zostaną wyświetlone wszystkie zaimportowane tabele, więc może być konieczne poprawienie ich rozmiarów w zależności od liczby pól w każdej tabeli.
Następnie przeciągnij pole klucza podstawowego z jednej tabeli do drugiej. Poniższy przykład to widok diagramu naszych tabel uczniów:

Utworzyliśmy następujące linki:
- tbl_Students | Legitymacja > studencka tbl_Grades | ID studenta
  Innymi słowy przeciągnij pole ID studenta z tabeli Studenci do pola ID studenta w tabeli Oceny.
- tbl_Semesters | Identyfikator > semestru tbl_Grades | Semestr
- tbl_Classes | Numer > zajęć tbl_Grades | Numer zajęć
Uwaga
- Nazwy pól nie muszą być takie same, aby utworzyć relację, ale muszą mieć ten sam typ danych.
- Łączniki w widoku diagramu mają z jednej strony cyfrę "1", a cyfrę "*" z drugiej. Oznacza to, że między tabelami istnieje relacja jeden-do-wielu, która określa sposób użycia 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 widać w nich, które pola są ze sobą połączone. Aby wyświetlić linki, przejdź do dodatku Power Pivot>— Zarządzanie>relacjami>projektu>Zarządzanie relacjami. W programie Excel możesz przejść dorelacjidanych>.

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óre mogą być wielokrotnie używane w całym skoroszycie. W dowolnym momencie możesz dodać więcej tabel do istniejącego modelu danych.

W dodatku Power Pivot przejdź 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. Program Excel doda pustą tabelę przestawną z okienkiem Lista pól wyświetlanym po prawej stronie.

Następnie utwórz tabelę przestawną lub wykres przestawny. Jeśli między tabelami zostały już utworzone relacje, w tabeli przestawnej można użyć dowolnych pól należących do tych tabel. Relacje zostały już utworzone w przykładowym skoroszycie modelu danych uczniów.

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

Załóżmy, że zaimportowano lub skopiowano dużo 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 obrębie danych, które chcesz dodać do modelu. Może to być dowolny zakres danych, ale najlepiej sprawdzają się dane sformatowane jako tabela programu Excel .
Aby dodać dane, użyj jednej z następujących metod:
Kliknij pozycjęDodaj do modelu danychdodatku Power Pivot>.
Kliknij pozycję Wstaw>tabelę przestawną, a następnie zaznacz pole 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 na temat 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 dodatku Power Pivot

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