Agregacje w dodatku Power Pivot

Wybieranie Grupy agregacji

Podczas agregowania danych dane są grupowane według atrybutów, takich jak produkt, cena, region lub data, a następnie definiowane są formuły działające na wszystkich danych w grupie. Na przykład podczas tworzenia sumy dla roku tworzysz agregację. Jeśli następnie utworzysz stosunek tego roku w stosunku do poprzedniego roku i przedstawisz go jako wartości procentowe, będzie to inny typ agregacji.

Decyzja o tym, jak pogrupować dane, jest oparta na pytaniu biznesowym. Na przykład agregacje mogą odpowiadać na następujące pytania:

Liczy   Ile transakcji było w ciągu miesiąca?

Średnie    Jaka była średnia sprzedaż w tym miesiącu według sprzedawcy?

Wartości minimalne i maksymalne    Które okręgi sprzedaży były w pierwszej piątce pod względem sprzedanych jednostek?

Aby utworzyć obliczenie odpowiadające na te pytania, musisz mieć szczegółowe dane zawierające liczby do zliczenia lub zsumowania, a dane liczbowe muszą być w jakiś sposób powiązane z grupami używanymi do organizowania wyników.

Jeśli dane nie zawierają jeszcze wartości, których można użyć do grupowania, takich jak kategoria produktu lub nazwa regionu geograficznego, w którym znajduje się sklep, możesz wprowadzić grupy do danych, dodając kategorie. Podczas tworzenia grup w programie Excel musisz ręcznie wpisać lub wybrać grupy, z których chcesz korzystać, spośród kolumn w arkuszu. Jednak w systemie relacyjnym hierarchie, takie jak kategorie produktów, są często przechowywane w innej tabeli niż tabela faktów lub wartości. Zazwyczaj tabela kategorii jest połączona z danymi faktów za pomocą jakiegoś klucza. Załóżmy na przykład, że dane zawierają identyfikatory produktów, ale nie nazwy produktów i ich kategorie. Aby dodać kategorię do płaskiego arkusza programu Excel, należy skopiować ją w kolumnie zawierającej nazwy kategorii. Za pomocą Power Pivot możesz zaimportować tabelę kategorii produktów do modelu danych, utworzyć relację między tabelą zawierającą dane liczbowe i listę kategorii produktów, a następnie zgrupować dane za pomocą kategorii. Aby uzyskać więcej informacji, zobacz Twórca relacji między tabelami.

Wybieranie funkcji do agregacji

Po zidentyfikowaniu i dodaniu grup do użycia należy określić, które funkcje matematyczne mają być używane do agregacji. Często agregacja wyrazów jest używana jako synonim operacji matematycznych lub statystycznych używanych w agregacjach, takich jak sumy, średnie, minimum lub liczba. Jednak Power Pivot umożliwia tworzenie formuł niestandardowych na potrzeby agregacji, a także standardowych agregacji znalezionych zarówno w programie Power Pivot, jak i w programie Excel.

Na przykład mając ten sam zestaw wartości i grup, które zostały użyte w powyższych przykładach, można utworzyć agregacje niestandardowe odpowiadające na następujące pytania:

Liczba odfiltrowanych   Ile transakcji było w ciągu miesiąca, z wyłączeniem okna konserwacji na koniec miesiąca?

Ratios using averages over time    Jaki był procentowy wzrost lub spadek sprzedaży w porównaniu z tym samym okresem ubiegłego roku?

Zgrupowane wartości minimalne i maksymalne    Które okręgi sprzedaży znalazły się na pierwszym miejscu w poszczególnych kategoriach produktów lub w poszczególnych promocjach sprzedaży?

Dodawanie grup do tabeli przestawnej

Podczas projektowania tabeli przestawnej przeciągasz pola reprezentujące grupowanie, kategorie lub hierarchie do sekcji kolumn i wierszy tabeli przestawnej w celu zgrupowania danych. Następnie przeciągnij pola zawierające wartości liczbowe do obszaru wartości, aby można je było zliczać, obliczać średnią lub sumować.

Jeśli dodasz kategorie do tabeli przestawnej, ale dane kategorii nie są powiązane z danymi dotyczącymi faktów, może zostać wyświetlony błąd lub specyficzne wyniki. Zwykle Power Pivot spróbuje rozwiązać problem, automatycznie wykrywając i sugerując relacje. Aby uzyskać więcej informacji, zobacz Praca z relacjami w tabelach przestawnych.

Możesz również przeciągać pola do fragmentatorów, aby zaznaczyć określone grupy danych do wyświetlenia. Fragmentatory umożliwiają interakcyjne grupowanie, sortowanie i filtrowanie wyników w tabeli przestawnej.

Praca z grupowaniami w formule

Za pomocą grup i kategorii można również agregować dane przechowywane w tabelach, tworząc relacje między tabelami, a następnie tworząc formuły korzystające z tych relacji w celu wyszukiwania powiązanych wartości.

Innymi słowy, jeśli chcesz utworzyć formułę grupującą wartości według kategorii, najpierw użyj relacji, aby połączyć tabelę zawierającą dane szczegółowe i tabele zawierające kategorie, a następnie utworzyć formułę.

Aby uzyskać więcej informacji na temat tworzenia formuł korzystających z odnośników, zobacz Odnośniki w formułach dodatku Power Pivot.

Używanie filtrów w agregacjach

Nowa funkcja w Power Pivot to możliwość stosowania filtrów do kolumn i tabel danych, nie tylko w interfejsie użytkownika, tabeli przestawnej lub wykresie, ale także w samych formułach używanych do obliczania agregacji. Filtrów można używać w formułach zarówno w kolumnach obliczeniowych, jak i w kolumnach s.

Na przykład w nowych funkcjach agregacji języka DAX zamiast określania wartości do zsumowania lub zliczenia można określić jako argument całą tabelę. Jeśli do tej tabeli nie zostaną zastosowane żadne filtry, funkcja agregacji będzie działać względem wszystkich wartości w określonej kolumnie tabeli. Jednak w języku DAX można utworzyć dynamiczny lub statyczny filtr w tabeli, aby agregacja działała na innym podzbiorze danych w zależności od warunku filtru i bieżącego kontekstu.

Łącząc warunki i filtry w formułach, można tworzyć agregacje, które zmieniają się w zależności od wartości podanych w formułach lub zmieniają się w zależności od wyboru nagłówków wierszy i nagłówków kolumn w tabeli przestawnej.

Aby uzyskać więcej informacji, zobacz Filtrowanie danych w formułach.

Funkcje agregacji standardowej

Funkcje agregacji języka DAX

Język DAX zawiera funkcje agregacji umożliwiające określenie tabeli, nad którą ma zostać wykonana agregacja. Dlatego zamiast po prostu dodawać lub uśredniać wartości w kolumnie, te funkcje umożliwiają utworzenie wyrażenia definiującego dynamicznie dane do agregowania.

W poniższej tabeli wymieniono funkcje agregacji dostępne w języku DAX.

Różnice między funkcjami agregacji języka DAX i programu Excel

Mimo że te funkcje mają takie same nazwy jak ich odpowiedniki w programie Excel, używają Power Pivot aparatu analizy w pamięci i zostały przepisane do pracy z tabelami i kolumnami. W skoroszycie programu Excel nie można używać formuły języka DAX i odwrotnie. Mogą być używane tylko w oknie Power Pivot i w tabelach przestawnych opartych na Power Pivot danych. Ponadto, mimo że funkcje mają identyczne nazwy, zachowanie może się nieco różnić. Aby uzyskać więcej informacji, zobacz poszczególne tematy dotyczące funkcji.

Sposób obliczania kolumn w agregacji różni się również od sposobu, w jaki program Excel obsługuje agregacje. Przykład może pomóc w zilustrowaniu.

Załóżmy, że chcesz uzyskać sumę wartości w kolumnie Kwota w tabeli Sprzedaż, dlatego utworzysz następującą formułę:

=SUM('Sales'[Amount])

W najprostszym przypadku funkcja pobiera wartości z jednej niefiltrowanej kolumny, a wynik jest taki sam jak w programie Excel, który zawsze sumuje wartości w kolumnie Kwota. Jednak w Power Pivot formuła jest interpretowana jako "Pobierz wartość w kwocie dla każdego wiersza tabeli Sprzedaż, a następnie sumuj te poszczególne wartości. Power Pivot ocenia każdy wiersz, nad którym jest wykonywana agregacja, i oblicza pojedynczą wartość skalarną dla każdego wiersza, a następnie wykonuje agregację tych wartości. Dlatego wynik formuły może być inny, jeśli do tabeli zastosowano filtry lub wartości są obliczane na podstawie innych agregacji, które mogą być filtrowane. Aby uzyskać więcej informacji, zobacz Kontekst w formułach języka DAX.