Tabele przestawne są zazwyczaj skonstruowane przy użyciu modułów OLAP i innych złożonych źródeł danych, które już mają rozbudowane połączenia między tabelami. Jednak w Excel można zaimportować wiele tabel i utworzyć własne połączenia między tabelami. Ta elastyczność jest zaawansowana, ale pozwala też łatwo ze sobą ze sobą zsumować dane, które nie są ze sobą powiązane, co prowadzi do dziwnego rezultatu.
Czy z kiedykolwiek tworzysz taką tabelę przestawną? Zamierzano utworzyć zestawienie zakupów według regionów, przez co pole kwoty zakupu było upuszczane w obszarze Wartości, a pole obszaru sprzedaży w obszarze Etykiety kolumn. Ale wyniki są błędne.
Jak rozwiązać ten problem?
Problem polega na tym, że pola dodane do tabeli przestawnej mogą być w tym samym skoroszycie, ale tabele zawierające każdą kolumnę nie są ze sobą powiązane. Na przykład możesz mieć tabelę z listą poszczególnych regionów sprzedaży, a inną tabelę z listą zakupów dla wszystkich regionów. Aby utworzyć tabelę przestawną i uzyskać poprawne wyniki, musisz utworzyć relację między obiemi tabelami.
Po utworzeniu relacji tabela przestawna połączy dane z tabeli zakupów z listą regionów prawidłowo, a wyniki będą wyglądać tak:
Excel 2013 zawiera technologię opracowaną przez firmę Microsoft Research (MSR, Microsoft Research) do automatycznego wykrywania i rozwiązywania problemów z relacjami, takich jak ten.
Korzystanie z automatycznego wykrywania
Funkcja automatycznego wykrywania sprawdza nowe pola, które dodaje się do skoroszytu zawierającego tabelę przestawną. Jeśli nowe pole nie jest związane z nagłówkami kolumn i wierszy tabeli przestawnej, w górnej części tabeli przestawnej w programie obszar powiadomień zostanie wyświetlony komunikat informujący o tym, że może być potrzebna relacja. Excel także analizować nowe dane w celu znalezienia potencjalnych relacji.
Nadal można zignorować ten komunikat i pracować z tabelą przestawną. jeśli jednak klikniesz przycisk Utwórz,algorytm przejdzie do działania i przeanalizuje Twoje dane. W zależności od wartości w nowych danych oraz rozmiaru i złożoności tabeli przestawnej oraz relacji, które zostały już utworzone, ten proces może potrwać kilka minut.
Proces składa się z dwóch faz:
-
Wykrywanie relacji. Po zakończeniu analizy możesz przejrzeć listę sugerowanych relacji. Jeśli nie zostanie on anulowany, Excel automatycznie przejdzie do następnego kroku tworzenia relacji.
-
Tworzenie relacji. Po zastosowaniu relacji zostanie wyświetlone okno dialogowe potwierdzenia i możesz kliknąć link Szczegóły, aby wyświetlić listę utworzonych relacji.
Proces wykrywania można anulować, ale nie można anulować procesu tworzenia.
Algorytm msr wyszukuje "najlepszy możliwy" zestaw relacji do łączenia tabel w modelu. Algorytm wykrywa wszystkie możliwe relacje dla nowych danych, uwzględniając nazwy kolumn, typy danych kolumn, wartości w kolumnach i kolumny, które znajdują się w tabelach przestawnych.
Excel następnie wybiera relację z najwyższą "jakością" wyznaczaną przez wewnętrzną heuristics. Aby uzyskać więcej informacji, zobacz Omówienie relacji iRozwiązywanie problemów z relacjami.
Jeśli funkcja automatycznego wykrywania nie daje poprawnych wyników, można edytować relacje, usuwać je lub tworzyć nowe ręcznie. Aby uzyskać więcej informacji, zobacz Tworzenie relacji między dwiema tabelami lub Tworzenie relacji w widoku diagramu.
Puste wiersze w tabelach przestawnych (nieznany członek)
Tabela przestawna łączy powiązane tabele danych, jeśli dowolna tabela zawiera dane, które nie mogą być powiązane przez klucz lub pasującą wartość, należy w jakiś sposób obsługiwać te dane. W wielowymiarowych bazach danych do obsługi niedopasowanych danych należy przypisać do nieznanego członka wszystkie wiersze, które nie mają pasujących wartości. W tabeli przestawnej nieznany członek jest wyświetlany jako pusty nagłówek.
Jeśli na przykład utworzysz tabelę przestawną, która ma grupować sprzedaż według sklepów, ale niektóre rekordy w tabeli sprzedaży nie mają nazwy sklepu, wszystkie rekordy bez prawidłowej nazwy sklepu są grupowane.
Jeśli wiersze są puste, masz do wyboru dwie opcje. Można albo zdefiniować relację pomiędzy tabelami, która działa, na przykład przez utworzenie łańcucha relacji między wieloma tabelami, albo usunąć pola z tabeli przestawnej, które powodują wystąpienia pustych wierszy.
