Zdarzyło Ci się kiedyś przenosić kolumnę z jednej tabeli do drugiej za pomocą funkcji WYSZUKAJ.PIONOWO? Teraz w programie Excel jest dostępny wbudowany model danych, więc funkcja WYSZUKAJ.PIONOWO jest przestarzała. Relację między dwiema tabelami danych można utworzyć na podstawie pasujących danych w poszczególnych tabelach. Następnie można tworzyć arkusze programu Power View i konstruować tabele przestawne oraz inne raporty z polami z poszczególnych tabel, nawet jeśli tabele pochodzą z różnych źródeł. Na przykład w przypadku danych sprzedaży na rzecz klientów można zaimportować i powiązać dane analizy czasowej, aby przeanalizować wzorce sprzedaży według roku i miesiąca.
Wszystkie tabele w skoroszycie są wymienione na listach pól tabeli przestawnej i programu Power View.
W przypadku importowania połączonych relacjami tabel z relacyjnej bazy danych program Excel często jest w stanie utworzyć te relacje w modelu danych, który jest tworzony w tle. We wszystkich innych przypadkach trzeba ręcznie utworzyć relacje.
-
Upewnij się, że skoroszyt zawiera przynajmniej dwie tabele i że każda tabela zawiera kolumnę, którą można zamapować na kolumnę w innej tabeli.
-
Wykonaj jedną z następujących czynności: Sformatuj dane jako tabelę lub Importuj dane zewnętrzne jako tabelę w nowym arkuszu.
-
Nadaj tabelom nazwy znaczące: Na karcie Narzędzia tabel kliknij pozycję Projektowanie > Nazwa tabeli, aby wprowadzić nazwę.
-
Upewnij się, że kolumna w jednej z tabel ma unikatowe wartości danych bez duplikatów. Program Excel może utworzyć relację tylko pod warunkiem, że jedna kolumna zawiera wartości unikatowe.
Aby na przykład powiązać sprzedaż klientom z danymi analizy czasowej, obydwie tabele muszą zawierać daty w tym samym formacie (na przykład 2012-01-01) i w co najmniej jednej tabeli (analiza czasowa) każda data musi być wymieniania tylko raz w obrębie kolumny.
-
Kliknij pozycję Dane > Relacje.
Jeśli pozycja Relacje jest wyszarzona, oznacza to, że skoroszyt zawiera tylko jedną tabelę.
-
W oknie dialogowym Zarządzanie relacjami kliknij przycisk Nowa.
-
W oknie dialogowym Tworzenie relacji kliknij strzałkę w polu Tabela i wybierz tabelę z listy. Ta tabela powinna znajdować się po stronie „wielu” relacji jeden-do-wielu. W naszym przykładzie dotyczącym klientów i analizy czasowej należałoby najpierw wybrać tabelę sprzedaży klientom, ponieważ do każdego dnia może być przypisanych wiele sprzedaży.
-
W polu Kolumna (obiekt obcy) wybierz kolumnę zawierającą dane powiązane z polem Pokrewna kolumna (obiekt podstawowy). Jeśli na przykład w obu tabelach znajduje się kolumna z datami, należy wybrać tę kolumnę.
-
W polu Powiązana tabela wybierz tabelę zawierającą co najmniej jedną kolumnę danych, która jest powiązana z tabelą wybraną w polu Tabela.
-
W polu Pokrewna kolumna (obiekt podstawowy) wybierz kolumnę zawierającą unikatowe wartości zgodne z wartościami w kolumnie wybranej w polu Kolumna.
-
Kliknij przycisk OK.
Więcej o relacjach między tabelami w programie Excel
Uwagi dotyczące relacji
-
Aby sprawdzić, czy istnieją relacje, wystarczy przeciągnąć pola z różnych tabel na listę pól tabeli przestawnej. Jeśli nie zostanie wyświetlony monit o utworzenia relacji, będzie to oznaczać, że program Excel ma już informacje potrzebne do połączenia danych.
-
Tworzenie relacji jest podobne do używania funkcji WYSZUKAJ.PIONOWO: potrzebne są kolumny zawierające pasujące dane, aby program Excel mógł tworzyć odsyłacze między wierszami w obu tabelach. W przykładzie z analizą czasową tabela Klient powinna zawierać wartości dat, które znajdują się także w tabeli analizy czasowej.
-
W modelu danych relacje między tabelami mogą być typu jeden-do-jednego (każdy pasażer ma jedną kartę pokładową) lub jeden-do-wielu (na każdy lot przypada wielu pasażerów), ale nie wiele-do-wielu. Relacje wiele-do-wielu powodują błędy współzależności cyklicznej, takie jak „Wykryto współzależność cykliczną”. Ten błąd wystąpi, jeśli utworzysz bezpośrednie połączenie między dwiema tabelami typu wiele-do-wielu lub pośrednie połączenia (łańcuch relacji między tabelami), które są typu jeden-do-wielu w obrębie każdej relacji, lecz wiele-do-wielu, gdy są rozpatrywane całościowo. Przeczytaj więcej: Relacje między tabelami w modelu danych.
-
Typy danych w tych dwóch kolumnach muszą być zgodne. Aby uzyskać szczegółowe informacje, zobacz Typy danych w modelach danych programu Excel.
-
Inne metody tworzenia relacji mogą być bardziej intuicyjne, zwłaszcza w przypadku braku pewności, których kolumn należy użyć. Zobacz Tworzenie relacji w widoku diagramu w dodatku Power Pivot.
Przykład: tworzenie relacji między danymi analizy czasowej i danymi lotu samolotu
Przy użyciu bezpłatnych danych w usłudze Microsoft Azure Marketplace możesz dowiedzieć się, co to są relacje pomiędzy tabelami i analiza czasowa. Niektóre z tych zestawów danych są bardzo duże i pobranie ich w rozsądnym przedziale czasu wymaga szybkiego połączenia internetowego.
-
Uruchom dodatek Power Pivot w programie Microsoft Excel i otwórz okno dodatku Power Pivot.
-
Kliknij pozycję Pobierz dane zewnętrzne > Z usługi danych > Z usługi Microsoft Azure Marketplace. W Kreatorze importu tabeli zostanie otwarta strona główna usługi Microsoft Azure Marketplace.
-
W obszarze Price (Cena) kliknij pozycję Free (Bezpłatne).
-
W obszarze Category (Kategoria) kliknij pozycję Science & Statistics (Nauka i statystyka).
-
Znajdowanie DateStream i kliknij przycisk Subskrybuj.
-
Wprowadź dane konta Microsoft i kliknij przycisk Sign in (Zaloguj się). W oknie powinien zostać wyświetlony podgląd danych.
-
Przewiń do dołu i kliknij pozycję Select Query (Zapytanie wybierające).
-
Kliknij przycisk Dalej.
-
Wybierz pozycję BasicCalendarUS (KalendarzPodstawowyUS), a następnie kliknij przycisk Finish (Zakończ), aby zaimportować dane. W przypadku szybkiego połączenia internetowego import powinien trwać około minuty. Po zakończeniu powinien zostać wyświetlony raport o stanie z informacją o przetransferowaniu 73 414 wierszy. Kliknij przycisk Close (Zamknij).
-
Kliknij pozycję Pobierz dane zewnętrzne > Z usługi danych > Z usługi Microsoft Azure Marketplace, aby zaimportować drugi zestaw danych.
-
W obszarze Type (Typ) kliknij pozycję Data (Dane).
-
W obszarze Price (Cena) kliknij pozycję Free (Bezpłatne).
-
Znajdź pozycję US Air Carrier Flight Delays (Opóźnienia lotów linii lotniczych US) i kliknij przycisk Select (Wybierz).
-
Przewiń do dołu i kliknij pozycję Select Query (Zapytanie wybierające).
-
Kliknij przycisk Dalej.
-
Kliknij przycisk Finish (Zakończ), aby zaimportować dane. W przypadku szybkiego połączenia internetowego import może potrwać około 15 minut. Po zakończeniu powinien zostać wyświetlony raport o stanie z informacją o przetransferowaniu 2.427.284 wierszy. Kliknij przycisk Close (Zamknij). W modelu danych powinny się teraz znajdować dwie tabele. Aby utworzyć relację pomiędzy nimi, każda tabela musi zawierać zgodne kolumny.
-
Zauważ, że kolumna DateKey (KluczDaty) w tabeli BasicCalendarUS (KalendarzPodstawowyUS) zawiera dane w formacie 1/1/2012 12:00:00. Tabela On_Time_Performance (Punktualność) także zawiera kolumnę z datą i godziną, FlightDate (DataLotu), której wartości zostały określone w tym samym formacie: 1/1/2012 12:00:00. Te dwie kolumny zawierają pasujące dane tego samego typu i co najmniej jedna z tych kolumn — DateKey (KluczDaty) — zawiera tylko wartości unikatowe. W kilku kolejnych krokach przy użyciu tych kolumn utworzysz relację pomiędzy tabelami.
-
W oknie dodatku Power Pivot kliknij pozycję Tabela przestawna, aby utworzyć tabelę przestawną w nowym lub istniejącym arkuszu.
-
Na liście pól rozwiń tabelę On_Time_Performance (Punktualność) i kliknij kolumnę ArrDelayMinutes (OpóźnienieMinuty), aby dodać ją do obszaru Wartości. W tabeli przestawnej powinien zostać wyświetlony całkowity czas opóźnień lotów w minutach.
-
Rozwiń tabelę BasicCalendarUS (KalendarzPodstawowyUS) i kliknij kolumnę MonthInCalendar (MiesiącWKalendarzu), aby dodać ją do obszaru Wiersze.
-
Zauważ, że teraz tabela przestawna zawiera listę miesięcy, ale całkowita suma minut jest taka sama dla każdego miesiąca. Powtarzające się, identyczne wartości wskazują, że konieczne jest utworzenie relacji.
-
Na liście pól w obszarze „Mogą być wymagane relacje między tabelami” kliknij przycisk Utwórz.
-
W polu Powiązana tabela wybierz pozycję On_Time_Performance (Punktualność), a w polu Powiązana kolumna (podstawowa) wybierz pozycję FlightDate (DataLotu).
-
W polu Tabela wybierz pozycję BasicCalendarUS (KalendarzPodstawowyUS), a w polu Kolumna (obca) wybierz pozycję DateKey (KluczDaty). Kliknij przycisk OK, aby utworzyć relację.
-
Zauważ, że teraz dla każdego miesiąca suma minut opóźnienia jest różna.
-
W tabeli BasicCalendarUS (KalendarzPodstawowyUS) przeciągnij kolumnę YearKey (KluczRoku) do obszaru Wiersze powyżej pozycji MonthInCalendar (MiesiącWKalendarzu).
Możesz teraz wycinać opóźnienia przylotów według roku i miesiąca lub innych wartości w kalendarzu.
Porady: Domyślnie miesiące są ułożone w porządku alfabetycznym. Przy użyciu dodatku Power Pivot możesz zmienić sposób sortowania, aby miesiące były wyświetlane w porządku chronologicznym.
-
Upewnij się, że tabela BasicCalendarUS (KalendarzPodstawowyUS) jest otwarta oknie dodatku Power Pivot.
-
Na karcie Narzędzia główne kliknij pozycję Sortuj według kolumny.
-
W polu Sortowanie wybierz pozycję MonthInCalendar (MiesiącWKalendarzu).
-
W polu Według wybierz pozycję MonthOfYear (MiesiącRoku).
W tabeli przestawnej każda kombinacja miesiąc-rok (październik 2011, listopad 2011) jest teraz sortowana według numeru miesiąca w roku (10, 11). Zmiana kolejności sortowania jest łatwa, ponieważ strumieniowe źródło danych DateStream dostarcza wszystkich potrzebnych kolumn do działania tego scenariusza. Jeśli korzystasz z innej tabeli analizy czasowej, ten krok będzie inny.
„Mogą być potrzebne relacje między tabelami”
Podczas dodawania pól do tabeli przestawnej zostanie wyświetlona informacja, czy jest wymagana relacja pomiędzy tabelami, aby pola wybrane w tabeli przestawnej miały znaczenie.
Mimo że program Excel może poinformować Cię, kiedy jest konieczna relacja, to nie może podpowiedzieć, których tabel i kolumn użyć, ani czy relacja pomiędzy tabelami jest w ogóle możliwa. Wykonaj poniższe czynności, aby uzyskać potrzebne odpowiedzi.
Krok 1. Ustalanie, które tabele określić w relacji
Jeśli model zawiera tylko kilka tabel, natychmiast może być wiadomo, których z nich należy użyć. Jednak w przypadku większych modeli przydałaby się pomoc. Jeden ze sposobów to użycie widoku diagramu w dodatku Power Pivot. Widok diagramu zapewnia wizualną reprezentację wszystkich tabel w modelu danych. Przy użyciu widoku diagramu można szybko ustalić, które tabele są oddzielone od reszty modelu.
Uwaga: Można utworzyć relacje niejednoznaczne, które są nieprawidłowe w przypadku użycia ich w tabeli przestawnej lub w raporcie programu Power View. Załóżmy, że wszystkie tabele są w jakiś sposób powiązane z pozostałymi tabelami w modelu, jednak gdy próbujesz połączyć pola z różnych tabel, jest wyświetlany komunikat „Mogą być potrzebne relacje między tabelami”. Najbardziej prawdopodobną przyczyną jest relacja wiele-do-wielu. Jeśli przeanalizujesz łańcuch relacji pomiędzy tabelami łączących tabele, których chcesz użyć, prawdopodobnie zauważysz, że dwie lub większa liczba relacji jest typu jeden-do-wielu. Nie ma łatwego rozwiązania, które działa w każdej sytuacji, ale możesz spróbować utworzyć kolumny obliczeniowe, aby skonsolidować kolumny, których chcesz użyć, w jedną tabelę.
Krok 2. Znajdowanie kolumn, których można użyć, aby utworzyć ścieżkę od jednej tabeli do drugiej
Po zidentyfikowaniu tabeli odłączonej od reszty modelu przejrzyj jej kolumny, aby ustalić, czy inna kolumna w innym miejscu modelu zawiera pasujące wartości.
Załóżmy na przykład, że nasz model zawiera dane sprzedaży produktów według obszaru i że następnie zaimportowaliśmy dane demograficzne, aby sprawdzić, czy istnieje korelacja między sprzedażą i trendami demograficznymi na każdym obszarze. Dane demograficzne pochodzą z innego źródła danych, dlatego te tabele są początkowo oddzielone od reszty modelu. Aby zintegrować dane demograficzne z pozostałą częścią modelu, trzeba znaleźć kolumnę w jednej z tabel demograficznych, która odpowiada już używanej tabeli. Jeśli na przykład dane demograficzne zostały zorganizowane według regionu, a w danych sprzedaży określono, w którym regionie miała miejsce sprzedaż, możesz powiązać te dwa zestawy danych, znajdując wspólną kolumnę, taką jak Województwo, Kod pocztowy lub Region, aby zapewnić odnośnik.
Oprócz pasujących wartości istnieje kilka dodatkowych wymagań dotyczących tworzenia relacji:
-
Wartości danych w kolumnie odnośnika muszą być unikatowe. Oznacza to, że ta kolumna nie może zawierać duplikatów. W modelu danych wartości null i puste ciągi są równoważne pustym wartościom, które są traktowane jako odrębne wartości danych. Oznacza to, że kolumna odnośnika nie może zawierać wielu wartości null.
-
Typy danych kolumny źródłowej i kolumny odnośnika muszą być zgodne. Aby uzyskać więcej informacji o typach danych, zobacz Typy danych w modelach danych.
Aby dowiedzieć się więcej o relacjach pomiędzy tabelami, zobacz Relacje pomiędzy tabelami w modelu danych.
