Tabele przestawne tradycyjnie były konstruowane przy użyciu modułów OLAP i innych złożonych źródeł danych, które mają już rozbudowane połączenia między tabelami. Jednak w programie Excel możesz dowolnie importować wiele tabel i tworzyć własne połączenia między tabelami. Ta elastyczność jest znacząca, ale ułatwia łączenie danych, które nie są ze sobą powiązane, co prowadzi do dziwnych wyników.
Czy kiedykolwiek utworzono taką tabelę przestawną? Zamierzano utworzyć zestawienie zakupów według regionów, dlatego w obszarze Wartości upuszczono pole kwoty zakupu, a w obszarze Etykiety kolumn — pole Region sprzedaży. Ale wyniki są błędne.
Jak możesz rozwiązać ten problem?
Problem polega na tym, że pola dodane do tabeli przestawnej mogą znajdować się w tym samym skoroszycie, ale tabele zawierające poszczególne kolumny nie są ze sobą powiązane. Na przykład możesz mieć tabelę z wymienionymi wszystkimi regionami sprzedaży oraz drugą, która zawiera zakupy dla wszystkich regionów. Aby utworzyć tabelę przestawną i uzyskać prawidłowe wyniki, musisz utworzyć relację między obiema tabelami.
Po utworzeniu relacji dane z tabeli zakupów zostaną poprawnie połączone z listą regionów, a wyniki będą wyglądały następująco:
Program Excel zawiera technologię opracowaną przez firmę Microsoft Research (MSR) do automatycznego wykrywania i rozwiązywania problemów z relacjami, takich jak ten.
Używanie automatycznego wykrywania
Funkcja wykrywania automatycznego sprawdza nowe pola dodawane do skoroszytu zawierającego tabelę przestawną. Jeśli nowe pole nie jest powiązane z nagłówkami kolumn i wierszy tabeli przestawnej, w obszarze powiadomień u góry tabeli przestawnej zostanie wyświetlony komunikat informujący, że może być konieczne utworzenie relacji. Program Excel przeanalizuje także nowe dane, aby znaleźć potencjalne relacje.
Możesz nadal ignorować komunikat i pracować z tabelą przestawną; Jeśli jednak klikniesz przycisk Utwórz, algorytm zacznie działać i przeanalizuje Twoje dane. W zależności od wartości zawartych w nowych danych oraz rozmiaru i złożoności tabeli przestawnej oraz relacji, które zostały już utworzone, proces ten może potrwać do kilku minut.
Proces składa się z dwóch etapów:
- Wykrywanie relacji. Listę sugerowanych relacji można przejrzeć po zakończeniu analizy. Jeśli ten proces nie zostanie anulowany, program Excel automatycznie przejdzie do następnego kroku, polegającego na utworzeniu relacji.
- Tworzenie relacji. Po zastosowaniu relacji zostanie wyświetlone okno dialogowe potwierdzenia i będzie można kliknąć łącze Szczegóły , aby wyświetlić listę utworzonych relacji.
Możesz anulować proces wykrywania, ale nie możesz anulować procesu tworzenia.
Algorytm MSR wyszukuje "najlepszy możliwy" zestaw relacji, aby połączyć tabele w modelu. Algorytm wykrywa wszystkie możliwe relacje dla nowych danych, biorąc pod uwagę nazwy kolumn, typy danych kolumn, wartości w obrębie kolumn oraz kolumny znajdujące się w tabelach przestawnych.
Następnie program Excel wybiera relację o najwyższym wyniku "jakości", określonym przez wewnętrzną heurystykę. Aby uzyskać więcej informacji, zobacz Omówienie relacji i Rozwiązywanie problemów z relacjami.
Jeśli automatyczne wykrywanie nie daje prawidłowych wyników, można edytować relacje, usunąć je lub ręcznie utworzyć nowe. 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)
Ponieważ tabela przestawna skupia powiązane tabele danych, jeśli którakolwiek tabela zawiera dane, których nie można powiązać za pomocą klucza ani zgodnej wartości, należy jakoś obsłużyć te dane. W wielowymiarowych bazach danych sposób obsługi niedopasowanych danych polega na przypisaniu wszystkich wierszy, które nie mają zgodnej wartości, do nieznanego elementu członkowskiego. W tabeli przestawnej nieznany element jest wyświetlany jako pusty nagłówek.
Jeśli na przykład zostanie utworzona tabela przestawna, która ma grupować transakcje sprzedaży według sklepów, ale niektóre rekordy w tabeli sprzedaży nie mają nazwy sklepu, wszystkie rekordy bez prawidłowej nazwy sklepu zostaną zgrupowane razem.
Jeśli pojawią się puste wiersze, istnieją dwie możliwości. Można albo zdefiniować działającą relację pomiędzy tabelami, na przykład tworząc łańcuch relacji między wieloma tabelami, albo usunąć z tabeli przestawnej pola powodujące występowanie pustych wierszy.