Tworzenie modelu danych w programie Excel

Tworzenie modelu danych w programie Excel

Model danych umożliwia integrację danych z wielu tabel, co w praktyce umożliwia tworzenie relacyjnych źródeł danych wewnątrz skoroszytu programu Excel. W programie Excel modele danych są używane w przezroczysty sposób, zapewniając dane tabelarycznie używane w tabelach przestawnych i na wykresach przestawnych. Model danych jest wizualizowane jako kolekcja tabel na liście pól i w większości przypadków nie będziesz nawet wiedzieć, że go zawiera.

Przed rozpoczęciem pracy z modelem danych musisz pobrać pewne dane. W tym celu użyjemy funkcji Dodatku & Transform (Power Query), więc warto cofnieć się i obejrzeć klip wideo lub skorzystać z przewodnika po dodatku & Transform i Power Pivot.

Gdzie znajduje się dodatek Power Pivot?

Gdzie jest & (Power Query)?

  • Funkcje przekształcania & Excel dla Microsoft 365 w programie Excel 2016 & (Power Query) zostały zintegrowane z programem Excel na karcie Dane.

  • Excel 2013 — Power Query to dodatek, który jest zawarty w programie Excel, ale wymaga aktywowania. Przejdź do > Opcje > , a następnie z listy rozwijanej Zarządzaj u dołu okienka wybierz pozycję Dodatki COM, a następnie wybierz pozycję Dodatki COM > Przejdź. Sprawdź Microsoft Power Query dla programu Excel, a następnie ok, aby go aktywować. Do wstążki zostanie dodana karta Power Query.

  • Excel 2010pobieranie i instalowanie dodatku Power Query.. Po aktywowaniu karta Power Query zostanie dodana do wstążki.

Wprowadzenie

Najpierw musisz pobrać pewne dane.

  1. W programach Excel 2016 i Excel dla Microsoft 365 użyj polecenia Dane >& Pobierz, przekształć dane > Pobierz dane, aby zaimportować dane z dowolnej liczby zewnętrznych źródeł danych, takich jak plik tekstowy, skoroszyt programu Excel, witryna internetowa, program Microsoft Access, SQL Server lub inna relalna baza danych zawierająca wiele powiązanych tabel.

    W programie Excel 2013 i 2010 przejdź do dodatku Power Query, > pozycję Pobierz dane zewnętrznei wybierz źródło danych.

  2. W programie Excel zostanie wyświetlony monit o zaznaczenie tabeli. Jeśli chcesz pobrać wiele tabel z tego samego źródła danych, zaznacz opcję Włącz zaznaczanie wielu tabel. Po zaznaczeniu wielu tabel program Excel automatycznie tworzy model danych.

    Uwaga: W tych przykładach używamy skoroszytu programu Excel ze szczegółami fikcyjnych uczniów na temat zajęć i ocen. Możesz pobrać przykładowy skoroszyt modeludanych uczniów i wykonać te śledzenia. Możesz również pobrać wersję z ukończonym modelem danych..

    Get & Transform (Power Query) Navigator
  3. Zaznacz co najmniej jedną tabelę, a następnie kliknij przycisk Załaduj.

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

Masz teraz model danych zawierający wszystkie zaimportowane tabele i będą one 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, gdy używasz Power Pivot do importowania danych. Model w dodatku jest przedstawiany w układzie kart podobnym do układu programu Excel, w którym każda karta zawiera dane tabelaryarne. Zobacz Uzyskiwanie 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 na tylko jednej tabeli, zaznacz tabelę i kliknij pozycję Dodaj do modelu danych w Power Pivot. Możesz to zrobić, jeśli chcesz używać różnych Power Pivot, takich jak odfiltrowane zestawy danych, kolumny obliczeniowe, pola obliczeniowe, kluczowe wskaźniki wydajności i hierarchie.

  • Relacje pomiędzy tabelami mogą być tworzone automatycznie w przypadku importowania powiązanych tabel, które zawierają relacje klucza podstawowego i obcego. Program Excel może zazwyczaj używać informacji o zaimportowanych relacjach jako podstawy relacji pomiędzy tabelami w modelu danych.

  • Aby uzyskać porady dotyczące zmniejszania rozmiaru modelu danych, zobacz Tworzenie modelu danych wydajnie korzystającego z pamięci za pomocą programu Excel i dodatku Power Pivot.

  • Aby dodatkowo eksplorować, zobacz Samouczek: importowanie danych do programu Excel i Tworzenie modelu danych.

Porada: Jak sprawdzić, czy skoroszyt zawiera model danych? Przejdź do Power Pivot > Zarządzanie. Jeśli widzisz dane przypominane arkuszem, to istnieje model. Zobacz: Dowiedz się, jakie ź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 przeciągnąć dane z dowolnej z nich. Każda tabela musi mieć klucz podstawowy lub unikatowy identyfikator pola, taki jak Identyfikator studenta lub Numer klasy. Najłatwiejszym sposobem jest przeciągnięcie i upuszczenie tych pól w celu połączenia ich w widoku diagramu dodatku Power Pivot.

  1. Przejdź do dodatku Power Pivot >Zarządzanie.

  2. Na karcie Narzędzia główne wybierz pozycję Widok diagramu.

  3. Zostaną wyświetlone wszystkie zaimportowane tabele, których rozmiar może zająć trochę czasu w zależności od tego, ile pól ma każda z nich.

  4. Następnie przeciągnij pole klucza podstawowego z jednej tabeli do drugiej. Poniższy przykład przedstawia widok diagramu tabel uczniów:

    Widok diagramu relacji modelu danych dodatku Power Query

    Utworzono następujące linki:

    • tbl_Students | Identyfikator > tbl_Grades | Identyfikator studenta

      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 | Numer > tbl_Grades | Numer klasy

    Uwagi: 

    • Aby można było 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 "1" i "*" po drugiej. Oznacza to, że między tabelami istnieje relacja jeden-do-wielu, która określa sposób, w jaki dane są używane w tabelach przestawnych. Zobacz: Relacje między tabelami w modelu danych, aby dowiedzieć się więcej.

    • Łączniki wskazują tylko, że istnieje relacja między tabelami. W rzeczywistości nie będą wyświetlane pola połączone ze sobą. Aby wyświetlić linki, przejdź do dodatku Power Pivot i > zarządzanie > projektami > relacjami > Zarządzanie relacjami. W programie Excel możesz przejść do >relacje.

Tworzenie tabeli przestawnej lub wykresu przestawnego za pomocą modelu danych

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

  1. W Power Pivotprzejdź dozarządzania .

  2. Na karcie Narzędzia główne wybierz pozycję Tabela przestawna.

  3. Wybierz miejsce, w którym chcesz umieścić tabelę przestawną: nowy arkusz lub bieżącą lokalizację.

  4. Kliknij przycisk OK,a program Excel doda pustą tabelę przestawną z wyświetlonym okienkiem Lista pól po prawej stronie.

    Power Pivot PivotTable Field List

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 zostały już utworzone relacje.

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

Przypuść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.

  1. Zacznij od wybrania dowolnej komórki w danych, którą chcesz dodać do modelu. Może to być dowolny zakres danych, ale najlepsze są dane sformatowane jako tabela programu Excel.

  2. Aby dodać dane, użyj jednej z tych metod:

  3. Kliknij Power Pivot > Dodaj do modelu danych.

  4. Kliknij pozycję > 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 za pomocą tabel połączonych programu Excel w dodatku Power Pivot.

Dodawanie danych do Power Pivot tabeli

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 dodać wiersze, kopiująci wklejając lub aktualizując dane źródłowe i odświeżając model dodatku Power Pivot.

Potrzebujesz dodatkowej pomocy?

Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel, uzyskać pomoc techniczną w społeczności witryny Answers bądź zasugerować nową funkcję lub ulepszenie w witrynie UserVoice dotyczącej programu Excel.

Zobacz też

Pobierz & szkoleniowe dodatku Power Pivot i przekształć

Wprowadzenie do Edytora zapytań (Power Query)

Tworzenie modelu danych wydajnie korzystającego z pamięci za pomocą programu Excel i dodatku Power Pivot

Samouczek: importowanie danych do programu Excel i tworzenie modelu danych

Określanie źródeł danych używanych w modelu danych skoroszytu

Relacje między tabelami w modelu danych

Potrzebna dalsza pomoc?

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×