Co to jest model danych w programie Excel?
Model danych w programie Excel to rodzaj tabeli danych, w której dwie lub więcej niż dwie tabele są ze sobą powiązane za pośrednictwem wspólnej lub większej liczby serii danych, w tabelach modelu danych i dane z różnych innych arkuszy lub źródeł łączą się, tworząc unikalną tabela, która może mieć dostęp do danych ze wszystkich tabel.
Wyjaśnienie
- Umożliwia integrację danych z wielu tabel poprzez tworzenie relacji opartych na wspólnej kolumnie.
- Modele danych są używane w sposób przejrzysty, dostarczając dane tabelaryczne, których można używać w tabeli przestawnej w programie Excel i na wykresach przestawnych w programie Excel. Integruje tabele, umożliwiając obszerną analizę przy użyciu tabel przestawnych, dodatku Power Pivot i programu Power View w programie Excel.
- Model danych umożliwia ładowanie danych do pamięci programu Excel.
- Jest zapisywany w pamięci, gdzie nie możemy go bezpośrednio zobaczyć. Następnie można poinstruować program Excel, aby powiązał dane ze sobą za pomocą wspólnej kolumny. Część „Model” modelu danych odnosi się do relacji między wszystkimi tabelami.
- Model danych może uzyskać dostęp do wszystkich potrzebnych informacji, nawet jeśli znajdują się one w wielu tabelach. Po utworzeniu modelu danych Excel ma dane dostępne w swojej pamięci. Mając dane w pamięci, można uzyskać do nich dostęp na wiele sposobów.
Przykłady
Przykład 1
Jeśli mamy trzy zbiory danych dotyczące sprzedawcy: pierwszy zawierający informacje o przychodach, drugi zawierający dochody sprzedawcy, a trzeci zawierający wydatki sprzedawcy.
Aby połączyć te trzy zbiory danych i nawiązać z nimi relację, tworzymy model danych z następującymi krokami:
- Przekonwertuj zestawy danych na obiekty tabeli:
Nie możemy stworzyć relacji ze zwykłymi zbiorami danych. Model danych działa tylko z obiektami tabel programu Excel. Aby to zrobić:
- Krok 1 - Kliknij w dowolnym miejscu w zestawie danych, a następnie kliknij kartę „Wstaw”, a następnie kliknij opcję „Tabela” w grupie „Tabele”.
- Krok 2 - Zaznacz lub odznacz opcję: „Moja tabela ma nagłówki” i kliknij OK.
- Krok 3 - Po wybraniu nowej tabeli wprowadź nazwę tabeli w polu „Nazwa tabeli” w grupie „Narzędzia”.
- Krok 4 - Teraz widzimy, że pierwszy zestaw danych jest konwertowany na obiekt „Tabela”. Powtarzając te kroki dla pozostałych dwóch zestawów danych, widzimy, że są one również konwertowane na obiekty „Tabela”, jak poniżej:
Dodawanie obiektów „Tabela” do modelu danych: poprzez połączenia lub relacje.
Za pośrednictwem połączeń
- Wybierz jedną tabelę i kliknij kartę „Dane”, a następnie kliknij opcję „Połączenia”.
- W wyświetlonym oknie dialogowym znajduje się ikona „Dodaj”. Rozwiń menu „Dodaj” i kliknij „Dodaj do modelu danych”.
- Kliknij „Tabele” w wyświetlonym oknie dialogowym, a następnie wybierz jedną z tabel i kliknij „Otwórz”.
W ten sposób utworzony zostanie skoroszyt Model danych z jedną tabelą i pojawi się następujące okno dialogowe:
Jeśli więc powtórzymy te kroki również dla pozostałych dwóch tabel, model danych będzie teraz zawierał wszystkie trzy tabele.
Widzimy teraz, że wszystkie trzy tabele pojawiają się w połączeniach skoroszytu.
Poprzez relacje
Utwórz relację: gdy oba zestawy danych są obiektami tabeli, możemy utworzyć relację między nimi. Aby to zrobić:
- Kliknij kartę „Dane”, a następnie kliknij opcję „Relacje”.
- Zobaczymy puste okno dialogowe, ponieważ nie ma bieżących połączeń.
- Kliknij „Nowy”, po czym pojawi się kolejne okno dialogowe.
- Rozwiń menu „Tabela” i „Powiązana tabela”: Pojawi się okno dialogowe „Utwórz relację”, w którym można wybrać tabele i kolumny, które mają być używane w relacji. W rozszerzeniu „Tabele” wybierz zbiór danych, który chcemy w jakiś sposób przeanalizować, a w „Powiązanej tabeli” wybierz zestaw danych, który zawiera wartości wyszukiwania.
- Tabela przeglądowa w programie Excel jest mniejszą tabelą w przypadku relacji jeden do wielu i nie zawiera powtarzających się wartości we wspólnej kolumnie. W rozwinięciu „Kolumna (obce)” wybierz wspólną kolumnę w tabeli głównej, w polu „Powiązana kolumna (podstawowa)” wybierz wspólną kolumnę w powiązanej tabeli.
- Po wybraniu wszystkich tych czterech ustawień kliknij „OK”. Po kliknięciu przycisku „OK” pojawi się następujące okno dialogowe.
Jeśli powtórzymy te kroki, aby powiązać pozostałe dwie tabele: Tabela przychodów z tabelą wydatków, wtedy one również zostaną powiązane w modelu danych w następujący sposób:
Program Excel tworzy teraz relację za kulisami, łącząc dane w modelu danych w oparciu o wspólną kolumnę: identyfikator sprzedawcy (w tym przypadku).
Przykład nr 2
Powiedzmy teraz, że w powyższym przykładzie chcemy utworzyć tabelę przestawną, która ocenia lub analizuje obiekty Table:
- Kliknij „Wstaw” -> „Tabela przestawna”.
- W wyświetlonym oknie dialogowym kliknij opcję „Użyj zewnętrznego źródła danych”, a następnie kliknij „Wybierz połączenie”.
- Kliknij „Tabele” w wyświetlonym oknie dialogowym i wybierz model danych skoroszytu zawierający trzy tabele i kliknij „Otwórz”.
- Wybierz opcję „Nowy arkusz roboczy” w lokalizacji i kliknij przycisk „OK”.
- W okienku Pola tabeli przestawnej zostaną wyświetlone obiekty tabeli.
- Teraz można odpowiednio zmienić tabelę przestawną, aby przeanalizować obiekty tabeli zgodnie z wymaganiami.
Na przykład w tym przypadku, jeśli chcemy znaleźć całkowity przychód lub przychód dla konkretnego sprzedawcy, wtedy tabela przestawna jest tworzona w następujący sposób:
Jest to ogromna pomoc w przypadku modelu / tabeli zawierającej dużą liczbę obserwacji.
Widzimy więc, że tabela przestawna natychmiast używa modelu danych (wybierając go przez wybranie połączenia) w pamięci programu Excel, aby pokazać relacje między tabelami.
Rzeczy do zapamiętania
- Korzystając z modelu danych, możemy analizować dane z kilku tabel jednocześnie.
- Tworząc relacje z modelem danych, przekraczamy potrzebę korzystania z funkcji WYSZUKAJ.PIONOWO, SUMA.JEŻELI, funkcji INDEKSU i formuł PODAJ.POZYCJĘ, ponieważ nie musimy umieszczać wszystkich kolumn w jednej tabeli.
- Gdy zestawy danych są importowane do programu Excel ze źródeł zewnętrznych, modele są tworzone niejawnie.
- Relacje między tabelami mogą być tworzone automatycznie, jeśli importujemy powiązane tabele, które mają relacje klucza podstawowego i obcego.
- Podczas tworzenia relacji kolumny, które łączymy w tabelach powinny mieć ten sam typ danych.
- Dzięki tabelom przestawnym utworzonym za pomocą modelu danych możemy również dodawać fragmentatory i wycinać tabele przestawne w dowolnym polu.
- Zaletą modelu danych w porównaniu z funkcjami LOOKUP () jest to, że wymaga on znacznie mniej pamięci.
- Excel 2013 obsługuje tylko relacje jeden do jednego lub jeden do wielu, tj. Jedna z tabel nie może mieć zduplikowanych wartości w kolumnie, do której tworzy łącze.
