Jak tworzyć relacje między wieloma tabelami za pomocą modelu danych w programie Excel

Jak tworzyć relacje między wieloma tabelami za pomocą modelu danych w programie Excel

Excel to potężne narzędzie do analizy danych i późniejszej automatyzacji podczas obsługi dużych zestawów danych. Możesz spędzać dużo czasu na analizowaniu ton danych za pomocą funkcji WYSZUKAJ.PIONOWO, INDEX-MATCH, SUMIF itp.





Dzięki modelowi danych Excel możesz zaoszczędzić cenny czas dzięki automatycznym raportom danych. Dowiedz się, jak łatwo można przypisać relację między dwiema tabelami, korzystając z modelu danych i ilustracji takiej relacji w tabeli przestawnej w poniższej sekcji.



Podstawowe wymagania

Będziesz potrzebować Power Pivot i Power Query (Pobierz i przekształć), aby wykonać kilka zadań podczas tworzenia Przewyższać Model danych. Oto, jak uzyskać te funkcje w skoroszycie programu Excel:





Jak uzyskać Power Pivot

1. Excel 2010: Musisz pobrać dodatek Power Pivot z Microsoft a następnie zainstaluj go dla swojego programu Excel na swoim komputerze.

2. Excel 2013: Wersja Office Professional Plus programu Excel 2013 zawiera dodatek Power Pivot. Ale musisz go aktywować przed pierwszym użyciem. Oto jak:



  1. Kliknij Plik na Wstążka skoroszytu programu Excel.
  2. Następnie kliknij Opcje otworzyć Opcje programu Excel .
  3. Teraz kliknij Dodatki .
  4. Wybierz Dodatki COM klikając na rozwijane menu Zarządzać skrzynka.
  5. Kliknij Udać się a następnie zaznacz pole wyboru dla Microsoft Power Pivot dla programu Excel .

3. Excel 2016 i nowsze: Menu Power Pivot znajdziesz na Wstążka .

Związane z: Jak dodać kartę dewelopera do wstążki w programach Microsoft Word i Excel?



Jak uzyskać dodatek Power Query (pobierz i przekształć)

1. Excel 2010: Dodatek Power Query można pobrać z Microsoft . Po instalacji, Zapytanie o moc pojawi się na Wstążka .

2. Excel 2013: Musisz aktywować dodatek Power Query, wykonując te same kroki, które właśnie wykonałeś, aby dodatek Power Pivot działał w programie Excel 2013.



3. Excel 2016 i nowsze: Dodatek Power Query (pobieranie i przekształcanie) można znaleźć, przechodząc do Dane zakładka w Excelu Wstążka .

Utwórz model danych, importując dane do skoroszytu programu Excel

W tym samouczku możesz uzyskać wstępnie sformatowane przykładowe dane od firmy Microsoft:

Pobierać : Przykładowy uczeń dane (tylko dane) | Przykładowy uczeń dane (kompletny model)

Możesz zaimportować bazę danych z wieloma powiązanymi tabelami z wielu źródeł, takich jak skoroszyty programu Excel, Microsoft Access, witryny internetowe, SQL Server itp. Następnie musisz sformatować zestaw danych, aby program Excel mógł z niego korzystać. Oto kroki, które możesz wypróbować:

1. W programie Excel 2016 i nowszych wersjach kliknij przycisk Dane tab i wybierz Nowe zapytanie .

2. Znajdziesz kilka sposobów na importowanie danych ze źródeł zewnętrznych lub wewnętrznych. Wybierać ten, który Ci odpowiada.

3. Jeśli używasz edycji Excel 2013, kliknij Zapytanie o moc na Wstążka a następnie wybierz Uzyskaj dane zewnętrzne wybrać dane do importu.

4. Zobaczysz Nawigator pole, w którym musisz wybrać, które tabele chcesz zaimportować. Kliknij pole wyboru dla Wybierz wiele przedmiotów aby wybrać kilka tabel do importu.

5. Kliknij Obciążenie aby zakończyć proces importu.

6. Excel utworzy dla Ciebie model danych, korzystając z tych tabel. Możesz zobaczyć nagłówki kolumn tabeli w Pola tabeli przestawnej aukcje.

Możesz również korzystać z funkcji Power Pivot, takich jak kolumny obliczeniowe, wskaźniki KPI, hierarchie, pola obliczeniowe i filtrowane zestawy danych z modelu danych programu Excel. W tym celu musisz wygenerować model danych z jednej tabeli. Możesz spróbować tych kroków:

1. Sformatuj swoje dane w modelu tabelarycznym, zaznaczając wszystkie komórki zawierające dane, a następnie kliknij Ctrl+T .

2. Teraz zaznacz całą tabelę, a następnie kliknij Power Pivot zakładka na Wstążka .

3. Od Stoły sekcji, kliknij Dodaj do modelu danych .

Excel utworzy relacje tabel między powiązanymi danymi z modelu danych. W tym celu w importowanych tabelach powinny istnieć relacje klucza podstawowego i obcego.

Program Excel wykorzystuje informacje o relacjach z importowanej tabeli jako podstawę do generowania połączeń między tabelami w modelu danych.

Związane z: Jak utworzyć analizę warunkową w programie Microsoft Excel

Buduj relacje między tabelami w modelu danych

Teraz, gdy masz już model danych w skoroszycie programu Excel, musisz zdefiniować relacje między tabelami, aby tworzyć sensowne raporty. Do każdej tabeli należy przypisać unikalny identyfikator pola lub klucz podstawowy, taki jak identyfikator semestru, numer klasy, identyfikator ucznia itp.

Funkcja Widok diagramu w dodatku Power Pivot umożliwia przeciąganie i upuszczanie tych pól w celu zbudowania relacji. Wykonaj następujące kroki, aby utworzyć łącza do tabeli w modelu danych programu Excel:

1. Na Wstążka skoroszytu programu Excel, kliknij Power Pivot menu.

2. Teraz kliknij Zarządzać w Model danych Sekcja. Zobaczysz Power Pivot edytor, jak pokazano poniżej:

3. Kliknij na Widok diagramu przycisk znajdujący się w Pogląd sekcja Power Pivot Dom patka. Zobaczysz nagłówki kolumn tabeli pogrupowane według nazwy tabeli.

4. Teraz będziesz mógł przeciągać i upuszczać unikalny identyfikator pola z jednej tabeli do drugiej. Poniżej przedstawiono schemat relacji między czterema tabelami modelu danych programu Excel:

Poniżej opisano powiązanie między tabelami:

  • Uczniowie stołu | Legitymacja studencka do tabeli ocen | legitymacja studencka
  • Semestry tabeli | ID semestru do tabeli Oceny | Semestr
  • Klasy tabeli | Numer klasy do tabeli Oceny | Identyfikator klasy

5. Możesz tworzyć relacje, wybierając parę unikalnych kolumn wartości. Jeśli są jakieś duplikaty, zobaczysz następujący błąd:

6. Zauważysz Gwiazdka (*) z jednej strony i Jeden 1) z drugiej strony w widoku diagramu relacji. Określa, że ​​między tabelami istnieje relacja jeden-do-wielu.

7. W edytorze Power Pivot kliknij Projekt zakładkę, a następnie wybierz Zarządzaj relacjami wiedzieć, które pola tworzą połączenia.

Generowanie tabeli przestawnej za pomocą modelu danych programu Excel

Teraz możesz utworzyć tabelę przestawną lub wykres przestawny, aby zwizualizować dane z modelu danych programu Excel. Skoroszyt programu Excel może zawierać tylko jeden model danych, ale możesz aktualizować tabele.

Powiązane: Co to jest eksploracja danych i czy jest to nielegalne?

Ponieważ dane zmieniają się w czasie, możesz nadal używać tego samego modelu i oszczędzać czas podczas pracy z tym samym zestawem danych. Zauważysz większą oszczędność czasu podczas pracy z danymi w tysiącach wierszy i kolumn. Aby utworzyć raport oparty na tabeli przestawnej, wykonaj następujące kroki:

1. W edytorze Power Pivot kliknij ikonę Dom patka.

2. Na Wstążka , Kliknij Stół obrotowy .

3. Wybierać dowolny pomiędzy nowym arkuszem roboczym a istniejącym arkuszem roboczym.

jak przenieść jeden komputer na drugi

4. Wybierz ok . Excel doda Stół obrotowy który pokaże Lista pól okienko po prawej stronie.

Poniżej znajduje się całościowy widok tabeli przestawnej utworzonej przy użyciu modelu danych programu Excel dla przykładowych danych uczniów używanych w tym samouczku. Możesz także tworzyć profesjonalne tabele przestawne lub wykresy na podstawie danych big data, korzystając z narzędzia Model danych programu Excel.

Przekształć złożone zestawy danych w proste raporty za pomocą modelu danych programu Excel

Model danych programu Excel wykorzystuje zalety tworzenia relacji między tabelami w celu tworzenia znaczących tabel przestawnych lub wykresów do celów raportowania danych.

Możesz stale aktualizować istniejący skoroszyt i publikować raporty dotyczące zaktualizowanych danych. Nie musisz edytować formuł ani inwestować czasu w przewijanie tysięcy kolumn i wierszy za każdym razem, gdy dane źródłowe są aktualizowane.

Udział Udział Ćwierkać E-mail Jak utworzyć tabelę przestawną w programie Excel

Dowiedz się, jak tworzyć tabele przestawne w programie Excel i jak ich używać do rysowania informacji, które chcesz zobaczyć.

Czytaj dalej Powiązane tematy
  • Wydajność
  • Wskazówki dotyczące arkuszy kalkulacyjnych
  • Microsoft Excel
  • Porady dotyczące pakietu Microsoft Office
  • Analiza danych
O autorze Tamal Das(100 opublikowanych artykułów)

Tamal jest niezależnym pisarzem w MakeUseOf. Po zdobyciu dużego doświadczenia w technologii, finansach i procesach biznesowych w swojej poprzedniej pracy w firmie konsultingowej IT, 3 lata temu przyjął pisanie jako pełnoetatowy zawód. Nie pisząc o produktywności i najnowszych nowinkach technicznych, uwielbia grać w Splinter Cell i oglądać filmy na Netflix/Prime Video.

Więcej od Tamala Das

Zapisz się do naszego newslettera

Dołącz do naszego newslettera, aby otrzymywać porady techniczne, recenzje, bezpłatne e-booki i ekskluzywne oferty!

Kliknij tutaj, aby zasubskrybować