Czy zdarzyło Ci się kiedyś używać funkcji WYSZUKAJ.PIONOWO do przenoszenia kolumny z jednej tabeli do innej tabeli? Program Excel zawiera również wbudowany model danych, który umożliwia tworzenie relacji między tabelami, co może być alternatywą dla korzystania z funkcji odnośników, takich jak WYSZUKAJ.PIONOWO. Relację między dwiema tabelami danych można utworzyć na podstawie pasujących danych w poszczególnych tabelach. Następnie możesz tworzyć tabele przestawne i inne raporty z polami z każdej tabeli, 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 liście Pola tabeli przestawnej.
Relacje są najczęściej używane podczas tworzenia tabel przestawnych z wielu tabel w modelu danych. Dzięki temu można analizować powiązane dane bez łączenia ich w jedną tabelę.
Uwaga
Jeśli skoroszyt zawiera model danych, możesz zarządzać relacjami między tabelami na karcie Dane.
Podczas importowania tabel pokrewnych z relacyjnej bazy danych program Excel często może tworzyć te relacje w modelu danych, który tworzy w tle. We wszystkich innych przypadkach konieczne będzie ręczne utworzenie relacji.
- 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 każdej tabeli zrozumiałą nazwę: w obszarze Narzędzia tabel kliknij pozycjęNazwa> tabeli projektu>, 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 za pomocą analizy czasowej, obie tabele muszą zawierać daty w tym samym formacie (na przykład 2026-01-01), a co najmniej jedna tabela (analiza czasowa) musi wyświetlać każdą datę tylko raz w kolumnie. - Wybierz pozycjęRelacjedanych>.
Jeśli pozycja Relacje jest wyszarzona, oznacza to, że skoroszyt zawiera tylko jedną tabelę.
- W polu Zarządzanie relacjami wybierz pozycję Nowy.
- 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.
- Wybierz przycisk OK.
Więcej o relacjach między tabelami w programie Excel
Uwagi dotyczące relacji
Podczas przeciągania pól z różnych tabel na listę Pól tabeli przestawnej będziesz wiedzieć, czy istnieje relacja. Jeśli nie zostanie wyświetlony monit o utworzenie relacji, program Excel zawiera już informacje o relacji potrzebne do powiązania 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 programu Excel relacje są zazwyczaj jeden-do-jednego lub jeden-do-wielu. Relacje wiele-do-wielu wymagają dodatkowego modelowania (na przykład przy użyciu tabeli odnośników). Relacje wiele-do-wielu powodują błędy współzależności cyklicznej, takie jak "Wykryto zależność cykliczną". Ten błąd wystąpi w przypadku nawiązania bezpośredniego połączenia między dwiema tabelami będącymi połączeniami wiele-do-wielu lub pośrednimi (łańcuchem relacji pomiędzy tabelami, które są relacjami jeden-do-wielu w każdej relacji, ale wiele-do-wielu w widoku od końca do końca). Przeczytaj więcej: Relacje między tabelami w modelu danych.
W przeciwieństwie do formuł odnośników relacje nie duplikują danych. Zamiast tego łączą tabele, dzięki czemu pola z każdej tabeli mogą być używane razem w tabeli przestawnej.
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.
"Mogą być potrzebne relacje między tabelami"
Podczas dodawania pól do tabeli przestawnej będziesz wiedzieć, czy jest wymagana relacja pomiędzy tabelami, aby zrozumieć pola zaznaczone w tabeli przestawnej.
Program Excel może określić, kiedy jest potrzebna relacja, ale nie może określić, których tabel i kolumn użyć, ani czy relacja mię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
Istnieje możliwość utworzenia relacji niejednoznacznych, które są nieprawidłowe, gdy są używane w tabeli przestawnej. Załóżmy, że wszystkie tabele są w jakiś sposób powiązane z innymi tabelami w modelu, ale podczas próby połączenia pól z różnych tabel jest wyświetlany komunikat "Być może potrzebne są relacje między tabelami". Najbardziej prawdopodobną przyczyną jest to, że występuje 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 określeniu, która tabela jest odłączona 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, musisz znaleźć kolumnę w jednej z tabel demograficznych odpowiadających używanej już 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. Innymi słowy, 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 w kolumnie odnośnika nie można mieć 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.