Relacje i odnośniki w formułach
Jedną z najważniejszych funkcji programu PowerPivot for Excel jest możliwość tworzenia relacji między tabelami, a następnie używania powiązanych tabel w celu wyszukiwania lub filtrowania powiązanych danych. Powiązane wartości są pobierane z tabel przy użyciu języka formuł DAX (Data Analysis Expressions) dostępnego w programie PowerPivot for Excel. W języku DAX jest używany model relacyjny i dlatego umożliwia on łatwe i dokładne pobieranie powiązanych lub odpowiednich wartości z innej tabeli lub kolumny.
Formuły przeprowadzające wyszukiwanie można tworzyć jako część kolumny obliczeniowej albo jako część miary używanej w tabeli przestawnej lub na wykresie przestawnym. Aby uzyskać więcej informacji, zobacz następujące tematy:
W tej sekcji opisano funkcje języka DAX służące do przeprowadzania wyszukiwania i przedstawiono kilka przykładów sposobu użycia tych funkcji.
[!UWAGA]
W zależności od typu operacji wyszukiwania lub formuły wyszukiwania, która ma zostać użyta, może być konieczne uprzednie utworzenie relacji między tabelami. Aby uzyskać informacje dotyczące tworzenia relacji, zobacz temat Relacje między tabelami.
Opis funkcji wyszukiwania
Możliwość wyszukiwania pasujących lub powiązanych danych z innej tabeli jest szczególnie przydatna w sytuacji, gdy bieżąca tabela ma tylko pewnego rodzaju identyfikator, ale potrzebne dane (takie jak cena produktu, jego nazwa lub inne szczegółowe wartości) są przechowywane w powiązanej tabeli. Jest ona również użyteczna, gdy inna tabela zawiera wiele wierszy powiązanych z bieżącym wierszem lub bieżącą wartością. Na przykład można z łatwością pobrać informacje dotyczące całej sprzedaży związane z danym regionem, sklepem lub sprzedawcą.
W odróżnieniu od funkcji wyszukiwania programu Excel, takich jak WYSZUKAJ.PIONOWO, które są oparte na tablicach, lub WYSZUKAJ, które pobierają pierwszą z wielu pasujących wartości, język DAX śledzi istniejące relacje między tabelami połączonymi za pomocą kluczy w celu pobrania pojedynczej powiązanej wartości stanowiącej dokładne dopasowanie. Język DAX umożliwia również pobieranie tabeli rekordów powiązanych z bieżącym rekordem.
[!UWAGA]
Użytkownicy zaznajomieni z relacyjnymi bazami danych mogą porównać działanie funkcji wyszukiwania w programie PowerPivot do działania instrukcji podwyboru w języku Transact-SQL.
Aby uzyskać więcej informacji dotyczących modelu relacyjnego używanego w programie PowerPivot, zobacz temat Omówienie relacji.
Pobieranie pojedynczej powiązanej wartości
Funkcja RELATED zwraca pojedynczą wartość z innej tabeli, która jest powiązana z bieżącą wartością w bieżącej tabeli. Należy określić kolumnę zawierającą żądane dane, a funkcja śledzi istniejące relacje między tabelami w celu pobrania wartości z określonej kolumny w powiązanej tabeli. W niektórych przypadkach funkcja musi prześledzić łańcuch relacji w celu pobrania danych.
Na przykład użytkownik otrzymał listę dzisiejszych wysyłek w formacie programu Excel. Jednak ta lista zawiera tylko numer identyfikacyjny pracownika, numer identyfikacyjny zamówienia i numer identyfikacyjny nadawcy, przez co raport jest mało czytelny. Aby uzyskać dodatkowe potrzebne informacje, można przekonwertować tę listę na połączoną tabelę programu PowerPivot, a następnie utworzyć relacje z tabelami Employee i Reseller, dopasowując pole EmployeeID do pola EmployeeKey i pole ResellerID do pola ResellerKey.
Aby wyświetlić informacje odnośnika w połączonej tabeli, należy dodać dwie nowe kolumny obliczeniowe, używając następujących formuł:
= RELATED('Employees'[EmployeeName])
= RELATED('Resellers'[CompanyName])
Przed wyszukiwaniem |
Po wyszukiwaniu |
||||||||||||||||||||||||||||||||||||||||||||
|
|
Funkcja używa relacji między połączoną tabelą a tabelami Employees i Resellers w celu pobrania poprawnego imienia i nazwiska dla każdego wiersza w raporcie. Powiązanych wartości można również używać w obliczeniach. Aby uzyskać więcej informacji i zapoznać się z innymi przykładami, zobacz temat Funkcja RELATED.
Pobieranie listy powiązanych wartości
Funkcja RELATEDTABLE śledzi istniejącą relację i zwraca tabelę zawierającą wszystkie pasujące wiersze z określonej tabeli. Na przykład użytkownik chce dowiedzieć się, ile zamówień złożył w tym roku każdy odsprzedawca. W tabeli Resellers można utworzyć kolumnę obliczeniową zawierającą poniższą formułę, która wyszukuje rekordy dla poszczególnych odsprzedawców w tabeli ResellerSales_USD i zlicza pojedyncze zamówienia złożone przez poszczególnych odsprzedawców. Te tabele stanowią część przykładowego skoroszytu języka DAX. Aby uzyskać więcej informacji dotyczących przykładowych danych, zobacz temat Pobieranie przykładowych danych dla programu PowerPivot.
=COUNTROWS(RELATEDTABLE(ResellerSales_USD))
W tej formule funkcja RELATEDTABLE najpierw pobiera wartości z kolumny ResellerKey dla każdego odsprzedawcy w bieżącej tabeli. (Użytkownik nie musi określać w formule kolumny identyfikatorów, ponieważ program PowerPivot używa istniejącej relacji między tabelami). Następnie funkcja RELATEDTABLE pobiera wszystkie wiersze z tabeli ResellerSales_USD, które są powiązane z każdym odsprzedawcą, i zlicza te wiersze. Należy zauważyć, że w przypadku braku relacji (bezpośredniej lub pośredniej) między tymi dwiema tabelami zostaną pobrane wszystkie wiersze z tabeli ResellerSales_USD.
Dla odsprzedawcy Modular Cycle Systems z przykładowej bazy danych w tabeli sprzedaży są zarejestrowane cztery zamówienia, więc funkcja zwraca wartość 4. Odsprzedawca Associated Bikes nie zrealizował żadnej sprzedaży, więc funkcja zwraca wartość pustą.
Odsprzedawca |
Rekordy w tabeli sprzedaży dla tego odsprzedawcy |
||||||||||
Modular Cycle Systems |
|
||||||||||
Associated Bikes |
|
[!UWAGA]
Funkcja RELATEDTABLE zwraca tabelę, a nie pojedynczą wartość, więc musi być używana jako argument funkcji wykonującej operacje na tabelach. Aby uzyskać więcej informacji, zobacz temat Funkcja RELATEDTABLE.
Zobacz także
Koncepcje
Dodawanie obliczeń do raportów, wykresów i tabel przestawnych
Tworzenie formuł na potrzeby obliczeń
Omówienie języka DAX (Data Analysis Expressions)