Agregacje w dodatku Power Pivot

Agregacje są metodą zwijania, podsumowywania lub grupowania danych. Gdy zaczynasz pracę od nieprzetworzonych danych z tabel lub innych źródeł danych, dane są często płaskie, co oznacza, że zawiera wiele szczegółów, ale nie zostały one w żaden sposób zorganizowane ani pogrupowane. Ten brak podsumowań lub struktury może utrudniać odnajdowanie wzorców w danych. Ważną częścią modelowania danych jest definiowanie agregacji, które upraszczają, abstrahują lub podsumowują wzorce w odpowiedzi na określone pytanie biznesowe.

Większość typowych agregacji, takich jak funkcje ŚREDNIA,ILE.LICZB, DISTINCTCOUNT,MAX, MIN lub SUMA , można tworzyć dla miar automatycznie za pomocą funkcji Autosumowanie. Inne typy agregacji, takie jak AVERAGEX, COUNTX, COUNTROWS lub SUMX, zwracają tabelę i wymagają formuły utworzonej przy użyciu języka DAX (Data Analysis Expressions).

Opis agregacji w dodatku Power Pivot

Wybieranie grup do agregacji

Podczas agregowania danych grupuje się je według atrybutów, takich jak produkt, cena, region lub data, a następnie definiuje się formułę działającą dla wszystkich danych w grupie. Na przykład utworzenie sumy dla roku spowoduje utworzenie agregacji. Jeśli następnie utworzysz stosunek tego roku w stosunku do roku poprzedniego i przedstawisz je jako wartości procentowe, będzie to inny rodzaj agregacji.

Decyzja o sposobie grupowania danych jest podejmowana na podstawie pytania biznesowego. Agregacje pozwalają na przykład odpowiedzieć na następujące pytania:

Liczniki Ile transakcji było w miesiącu?

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

Wartości minimalne i maksymalne Które dzielnice sprzedaży znalazły się w pierwszej piątce pod względem sprzedanych lokali?

Aby utworzyć obliczenie odpowiadające na te pytania, trzeba dysponować szczegółowymi danymi liczbowymi zawierającymi liczby do zliczenia lub zsumowania oraz danymi liczbowymi powiązanymi w jakiś sposób z grupami, za pomocą których będą organizowane wyniki.

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żna wprowadzić do danych grupy, dodając kategorie. Podczas tworzenia grup w programie Excel musisz ręcznie wpisać lub wybrać grupy, których chcesz użyć, 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 tabela wartości. Zazwyczaj tabela kategorii jest połączona z danymi faktów za pomocą pewnego rodzaju klucza. Załóżmy na przykład, że dane zawierają identyfikatory produktów, ale nie nazwy produktów ani ich kategorii. Aby dodać kategorię do płaskiego arkusza programu Excel, należy skopiować kolumnę zawierającą nazwy kategorii. Za pomocą dodatku Power Pivot można 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 pogrupować dane za pomocą kategorii. Aby uzyskać więcej informacji, zobacz Tworzenie relacji między tabelami.

Wybieranie funkcji agregacji

Po zidentyfikowaniu i dodaniu grup, które mają być używane, należy zdecydować, których funkcji matematycznych użyć do agregacji. Wyraz "agregacja" jest często używany jako synonim operacji matematycznych i statystycznych używanych w agregacjach, takich jak sumy, średnie, wartości minimalne lub liczebność. Dodatek Power Pivot umożliwia jednak tworzenie niestandardowych formuł agregacji, oprócz standardowych agregacji dostępnych w programach Power Pivot i Excel.

Na przykład, korzystając z tego samego zestawu wartości i grup, który został użyty w poprzednich przykładach, można utworzyć agregacje niestandardowe, które odpowiadają na następujące pytania:

Przefiltrowane liczniki Ile transakcji było w miesiącu, z wyłączeniem okna obsługi na koniec miesiąca?

Współczynniki wykorzystujące średnie w czasie Jaki był procentowy wzrost lub spadek sprzedaży w porównaniu z analogicznym okresem ubiegłego roku?

Zgrupowane wartości minimalne i maksymalne Które okręgi sprzedaży zajęły pierwsze miejsca w poszczególnych kategoriach produktów lub w poszczególnych promocjach sprzedaży?

Dodawanie agregacji do formuł i tabel przestawnych

Mając ogólny zarys sposobu grupowania danych, aby były one zrozumiałe, oraz wartości, z którymi chcesz pracować, możesz zdecydować, czy utworzyć tabelę przestawną, czy też utworzyć obliczenia w tabeli. Dodatek Power Pivot rozszerza i ulepsza natywne możliwości programu Excel do tworzenia agregacji, takich jak sumy, liczniki czy średnie. W dodatku Power Pivot można tworzyć agregacje niestandardowe w oknie dodatku Power Pivot lub w obszarze tabeli przestawnej programu Excel.

• W kolumnie obliczeniowej można tworzyć agregacje uwzględniające kontekst bieżącego wiersza w celu pobrania powiązanych wierszy z innej tabeli, a następnie zsumować, policzyć lub obliczyć średnią z tych wartości w powiązanych wierszach.
• W miarę można tworzyć agregacje dynamiczne używające zarówno filtrów zdefiniowanych w formule, jak i filtrów narzuconych przez projekt tabeli przestawnej i wybór fragmentatorów, nagłówków kolumn i nagłówków wierszy. Miary używające standardowych agregacji można tworzyć w dodatku Power Pivot za pomocą funkcji Autosumowanie lub formuły. Miary niejawne można również tworzyć za pomocą standardowych agregacji w tabeli przestawnej w programie Excel.

Dodawanie grupowań do tabeli przestawnej

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

Jeśli dodasz kategorie do tabeli przestawnej, ale dane kategorii nie będą powiązane z danymi faktów, możesz otrzymać błąd lub dziwne wyniki. Zazwyczaj dodatek Power Pivot próbuje rozwiązać problem, automatycznie wykrywając i sugerując relacje. Aby uzyskać więcej informacji, zobacz Praca z relacjami w tabelach przestawnych.

Do fragmentatorów można również przeciągać pola, 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

Grupowania i kategorie można także używać do agregowania danych przechowywanych w tabelach przez utworzenie relacji między tabelami, a następnie utworzenie formuł korzystających z tych relacji w celu wyszukiwania powiązanych wartości.

Innymi słowy, aby utworzyć formułę grupującą wartości według kategorii, należy najpierw połączyć tabelę zawierającą dane szczegółowe z tabelami zawierającymi kategorie za pomocą relacji, a następnie utworzyć formułę.

Aby uzyskać więcej informacji o tworzeniu formuł używających odnośników, zobacz temat Odnośniki w formułach dodatku Power Pivot.

Używanie filtrów w agregacjach

Nowością w dodatku Power Pivot jest możliwość stosowania filtrów do kolumn i tabel danych nie tylko w interfejsie użytkownika oraz w obrębie tabeli przestawnej lub wykresu, ale także w formułach używanych do obliczania agregacji. Filtrów można używać w formułach zarówno w kolumnach obliczeniowych, jak i w kolumnie s.

Na przykład w nowych funkcjach agregujących języka DAX zamiast określać wartości, które mają być sumowane lub zliczane, można określić jako argument całą tabelę. Jeśli do tabeli nie zastosowano żadnych filtrów, funkcja agregacji działałaby dla wszystkich wartości w określonej kolumnie tabeli. W języku DAX można jednak utworzyć dynamiczny lub statyczny filtr tabeli, dzięki czemu agregacja będzie działać względem różnych podzbiorów 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 zaznaczenia wierszy, nagłówków i nagłówków kolumn w tabeli przestawnej.

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

Porównanie funkcji agregacji w programie Excel i funkcji agregujących języka DAX

W poniższej tabeli wymieniono niektóre standardowe funkcje agregacji udostępniane przez program Excel wraz z linkami do implementacji tych funkcji w dodatku Power Pivot. Wersja DAX tych funkcji działa podobnie jak wersja programu Excel, z pewnymi niewielkimi różnicami w składni i obsłudze niektórych typów danych.

Standardowe funkcje agregacji

Funkcja	Użyj
ŚREDNIA	Zwraca średnią (średnią arytmetyczną) wszystkich liczb w kolumnie.
ŚREDNIA.A	Zwraca średnią (średnią arytmetyczną) wszystkich wartości w kolumnie. Obsługuje tekst i wartości nieliczbowe.
ILE.LICZB	Zlicza wartości liczbowe w kolumnie.
ILE.NIEPUSTYCH	Zwraca liczbę niepustych wartości w kolumnie.
MAX	Zwraca największą wartość liczbową w kolumnie.
MAXX	Zwraca największą wartość z zestawu wyrażeń obliczanych dla tabeli.
MIN	Zwraca najmniejszą wartość liczbową w kolumnie.
MINX	Zwraca najmniejszą wartość z zestawu wyrażeń obliczanych dla tabeli.
SUMA	Dodaje wszystkie liczby w kolumnie.

Funkcje agregacji języka DAX

Język DAX zawiera funkcje agregacji umożliwiające określenie tabeli, w której ma być wykonywana agregacja. Dlatego zamiast tylko dodawania lub uśredniania wartości w kolumnie te funkcje umożliwiają utworzenie wyrażenia, które dynamicznie definiuje dane do zagregowania.

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

Funkcja	Użyj
ŚREDNIAX	Oblicza średnią z zestawu wyrażeń obliczanych dla tabeli.
PODATEK KRAJOWY	Zlicza zestaw wyrażeń obliczanych dla tabeli.
LICZ.PUSTE	Zlicza puste wartości w kolumnie.
COUNTX	Zlicza całkowitą liczbę wierszy w tabeli.
COUNTROWS	Zlicza wiersze zwrócone przez funkcję tabeli zagnieżdżonej, taką jak funkcja filtru.
Funkcja SUMX	Zwraca sumę zestawu wyrażeń obliczanych dla tabeli.

Różnice między językiem DAX a funkcjami agregacji programu Excel

Chociaż te funkcje mają takie same nazwy jak ich odpowiedniki w programie Excel, korzystają one z aparatu analizy w pamięci dodatku Power Pivot i zostały przepisane pod kątem pracy z tabelami i kolumnami. Formuły języka DAX nie można używać w skoroszycie programu Excel i na odwrót. Można ich używać tylko w oknie dodatku Power Pivot i w tabelach przestawnych opartych na danych dodatku Power Pivot. Ponadto, mimo że funkcje mają identyczne nazwy, zachowanie może się nieco różnić. Aby uzyskać więcej informacji, zobacz tematy z opisem poszczególnych funkcji.

Sposób, w jaki kolumny są szacowane w ramach agregacji, również różni się od sposobu, w jaki program Excel obsługuje agregacje. Pomocne może być zilustrowanie tego przykładu.

Aby uzyskać sumę wartości z kolumny Kwota w tabeli Sprzedaż, należy utworzyć następującą formułę:

=SUM('Sales'[Amount])

W najprostszym przypadku funkcja pobiera wartości z pojedynczej niefiltrowanej kolumny, a wynik jest taki sam jak w programie Excel, który zawsze sumuje wartości w kolumnie Kwota. Jednak w dodatku Power Pivot ta formuła jest interpretowana następująco: "Pobierz wartość w kolumnie Kwota dla każdego wiersza tabeli Sprzedaż, a następnie dodaj te poszczególne wartości. Dodatek Power Pivot ocenia każdy wiersz, w którym wykonywana jest agregacja, i oblicza jedną 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 jeśli 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.

Funkcje analizy czasowej języka DAX

Oprócz funkcji agregujących tabele opisanych w poprzedniej sekcji język DAX zawiera funkcje agregacji, które działają z datami i godzinami określonymi przez użytkownika, zapewniając wbudowaną analizę czasu. Te funkcje używają zakresów dat do pobierania powiązanych wartości i agregowania wartości. Możesz również porównywać wartości dla różnych zakresów dat.

W poniższej tabeli wymieniono funkcje analizy czasowej, których można używać do agregacji.

Funkcja	Użyj
CLOSINGBALANCEMONTH CLOSINGBALANCEQUARTER CLOSESINGBALANCEYEAR	Oblicza wartość na końcu kalendarza danego okresu.
OPENINGBALANCEMONTH (miesiąc równowagi otwarcia) BILANS_OTWARCIAKWARTAŁ OPENINGBALANCEYEAR	Oblicza wartość na końcu kalendarza okresu poprzedzającego dany okres.
TOTALMTD TOTALYTD TOTALQTD	Oblicza wartość w interwale, który rozpoczyna się pierwszego dnia okresu i kończy najpóźniejszą datą w określonej kolumnie daty.

Pozostałe funkcje w sekcji funkcji analizy czasowej (Funkcje analizy czasowej) to funkcje, których można używać do pobierania dat lub niestandardowych zakresów dat do użycia w agregacji. Można na przykład użyć funkcji DATESINPERIOD, aby zwrócić zakres dat, i użyć tego zestawu dat jako argumentu innej funkcji w celu obliczenia agregacji niestandardowej właśnie dla tych dat.