Zdarzyło Ci się kiedyś przenosić kolumnę z jednej tabeli do drugiej za pomocą funkcji WYSZUKAJ.PIONOWO? Program Excel zawiera również wbudowany model danych, który umożliwia tworzenie relacji między tabelami, co może stanowić alternatywę dla funkcji wyszukiwania, 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żna tworzyć tabele przestawne i 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ą wyświetlane na liście pól tabeli przestawnej.
Relacje są najczęściej używane podczas tworzenia tabel przestawnych na podstawie wielu tabel w modelu danych. Umożliwia to analizowanie powiązanych danych bez łączenia ich w jedną tabelę.
Uwaga
Jeśli skoroszyt zawiera model danych, możesz zarządzać relacjami między tabelami z poziomu karty Dane.
W przypadku importowania powiązanych 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 zaimportuj dane zewnętrzne jako tabelę do nowego arkusza.
- Nadaj poszczególnym tabelom opisowe nazwy: W obszarze Narzędzia tabel kliknij pozycję Projektuj>nazwę> tabeli Wprowadź 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, obie tabele muszą zawierać daty w tym samym formacie (na przykład 2026-01-01) i co najmniej jedna tabela (analiza czasowa) wymienia każdą datę tylko raz w kolumnie. - Wybierzpozycję Relacjemiędzy danymi>.
Jeśli pozycja Relacje jest wyszarzona, oznacza to, że skoroszyt zawiera tylko jedną tabelę.
- W oknie dialogowym 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
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 utworzenie 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 programu Excel relacje są zazwyczaj typu jeden-do-jednego lub jeden-do-wielu. Relacje wiele-do-wielu wymagają dodatkowego modelowania (na przykład z użyciem tabeli odnośników). 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.
W przeciwieństwie do formuł odnośników relacje nie duplikują danych. Zamiast tego tabele są łączone, 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 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. 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 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.