Agregacje są sposobem zwijania, podsumowywania lub grupowania danych. Gdy zaczynasz 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 zorganizowane ani zgrupowane w żaden sposób. Ten brak podsumowań lub struktury może utrudniać odnajdowanie wzorców danych. Ważną częścią modelowania danych jest definiowanie agregacji, które upraszczają, abstrakcyjne lub podsumowują wzorce w odpowiedzi na określone pytanie biznesowe.
Większość typowych agregacji, takich jak przy użyciu funkcji ŚREDNIA, ILE.NIELICZBOWYCH, DISTINCTCOUNT,MAX,MINlub SUMA, można utworzyć automatycznie w miarę przy użyciu funkcji Autosumowanie. Inne typy agregacji, takie jak AVERAGEX,COUNTX,COUNTROWSlub SUMX,zwracają tabelę i wymagają formuły utworzonej przy użyciu języka DAX (Data Analysis Expressions).
Opis agregacji w Power Pivot
Wybieranie grup dla agregacji
Podczas agregowania danych dane są grupowane według atrybutów, takich jak produkt, cena, region lub data, a następnie definiowanie formuły, która działa we wszystkich danych w grupie. Na przykład podczas tworzenia sumy dla roku tworzysz agregację. Jeśli następnie utworzysz współczynnik tego roku w stosunku do poprzedniego roku i przedstawisz je jako wartości procentowe, będzie to inny typ agregacji.
Decyzja o tym, jak grupować dane, jest kierowana przez pytanie biznesowe. Na przykład agregacje mogą odpowiadać na następujące pytania:
Liczniki Ile transakcji transakcji transakcji było w miesiącu?
Średnie Jaka była średnia sprzedaż w tym miesiącu według sprzedawcy?
Wartości minimalne i maksymalne W których okręgach sprzedaży sprzedano pięć najlepszych jednostek?
Aby można było utworzyć obliczenia, które będą odpowiadać na te pytania, musisz mieć szczegółowe dane zawierające liczby do zliczenia lub zsumowania, a te dane liczbowe muszą być w jakiś sposób powiązane z grupami, których będziesz używać 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 Excel musisz ręcznie wpisać lub wybrać grupy, z których chcesz korzystać, spośród kolumn w arkuszu. W systemie relacyjnej hierarchie, takie jak kategorie produktów, często są przechowywane w innej tabeli niż tabela fakt lub wartość. Zazwyczaj tabela kategorii jest połączona z danymi faktycznie za pomocą jakiegoś 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 Excel, należy skopiować ją w kolumnie zawierającej nazwy kategorii. Za pomocą Power Pivot można zaimportować tabelę kategorii produktu do modelu danych, utworzyć relację między tabelą z danymi liczb i listą kategorii produktów, a następnie użyć kategorii do grupowania danych. Aby uzyskać więcej informacji, zobacz Tworzenie relacji między tabelami.
Wybieranie funkcji dla agregacji
Po zidentyfikowaniu i dodaniu grup, których chcesz użyć, musisz zdecydować, których funkcji matematycznych używać do agregacji. Agregacji wyrazów używa się często jako synonimu operacji matematycznych lub statystycznych używanych w agregacjach, takich jak sumy, średnie, minimum lub zliczenia. Jednak Power Pivot umożliwia tworzenie formuł niestandardowych dla agregacji, a nie tylko standardowych agregacji w Power Pivot i Excel.
Mając na przykład ten sam zestaw wartości i grup, które zostały użyte w poprzednich przykładach, można utworzyć agregacje niestandardowe, które odpowiadają na następujące pytania:
Liczba odfiltrowanych Ile transakcji transakcji transakcji było w miesiącu z wyjątkiem okna konserwacji na koniec miesiąca?
Współczynniki używające średnich w czasie Jaki był wzrost lub spadek procentowy sprzedaży w porównaniu z tym samym okresem w zeszłym roku?
Grupowane wartości minimalne i maksymalne Które okręgi sprzedaży zostały sklasyfikowane jako pierwsze w poszczególnych kategoriach produktów lub w poszczególnych promocjach sprzedaży?
Dodawanie agregacji do formuł i tabel przestawnych
Jeśli masz ogólny pomysł na sposób grupowania danych, aby były zrozumiałe, oraz wartości, z których chcesz pracować, możesz zdecydować, czy utworzyć tabelę przestawną, czy też tworzyć obliczenia w tabeli. Power Pivot rozszerza i ulepsza natywne możliwości Excel tworzenia agregacji, takich jak sumy, liczniki lub średnie. Agregacje niestandardowe można tworzyć Power Pivot w Power Pivot tabeli przestawnej lub w Excel tabeli przestawnej.
-
W kolumnie obliczeniowejmożna tworzyć agregacje uwzględniające bieżący kontekst wiersza w celu pobrania powiązanych wierszy z innej tabeli, a następnie zsumować, zliczyć lub uśrednić te wartości w powiązanych wierszach.
-
W miarę, można tworzyć agregacje dynamiczne, które używają obu filtrów zdefiniowanych w formule, jak również filtrów narzuconych w projekcie tabeli przestawnej oraz wyboru fragmentatorów, nagłówków kolumn i nagłówków wierszy. Miary używające standardowych agregacji można tworzyć Power Pivot za pomocą funkcji Autosumowanie lub tworząc formułę. Możesz również tworzyć niejawne miary, używając standardowych agregacji w tabeli przestawnej w programie Excel.
Dodawanie grupowania do tabeli przestawnej
Podczas projektowania tabeli przestawnej można przeciągać pola reprezentujące grupy, kategorie lub hierarchie do sekcji kolumn i wierszy tabeli przestawnej, aby zgrupować dane. Następnie przeciągnij pola zawierające wartości liczbowe do obszaru wartości, aby można je było zliczać, uśredniać lub sumować.
Jeśli dodasz kategorie do tabeli przestawnej, ale dane kategorii nie są powiązane z danymi faktycznie, może to spowodować błąd lub wyniki wyszukiwania. Zazwyczaj 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 także 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, aby utworzyć formułę grupową wartości według kategorii, należy najpierw 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ł, które korzystają z odnośników, zobacz Odnośniki w formułach dodatku Power Pivot.
Używanie filtrów w agregacjach
Nową funkcją w programie Power Pivot jest możliwość stosowania filtrów do kolumn i tabel danych nie tylko w interfejsie użytkownika i w tabeli przestawnej lub na wykresie, ale również w bardzo formułach, których używasz do obliczania agregacji. Filtrów można używać w formułach, zarówno w kolumnach obliczeniowych, jak i w s.
Na przykład w nowych funkcjach agregacji języka DAX zamiast określać wartości do zsumowania lub zliczenia można określić jako argument całą tabelę. Jeśli do tej tabeli nie zostały stosowane żadne filtry, funkcja agregacji będzie działać na wszystkich wartościach w określonej kolumnie tabeli. W języku DAX można jednak utworzyć w tabeli filtr dynamiczny lub statyczny, aby agregacja miała inny podzbiór 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 które zmieniają się w zależności od zaznaczenia nagłówków wierszy i nagłówków kolumn w tabeli przestawnej.
Aby uzyskać więcej informacji, zobacz Filtrowanie danych w formułach.
Porównanie funkcji Excel agregacji i funkcji agregacji języka DAX
W poniższej tabeli wymieniono niektóre ze standardowych funkcji agregowania dostarczanych przez program Excel oraz linki do implementacji tych funkcji w programie Power Pivot. Wersja języka DAX tych funkcji działa bardzo podobnie do wersji Excel, z pewnymi niewielkimi różnicami w składni i obsłudze niektórych typów danych.
Standardowe funkcje agregacji
Funkcja |
Zastosowanie |
Zwraca średnią (średnią arytmetyczną) wszystkich liczb w kolumnie. |
|
Zwraca średnią (średnią arytmetyczną) wszystkich wartości w kolumnie. Obsługuje tekst i wartości nieliczbowe. |
|
Zlicza wartości liczbowe w kolumnie. |
|
Zlicza niepuste wartości w kolumnie. |
|
Zwraca największą wartość liczbową w kolumnie. |
|
Zwraca największą wartość z zestawu wyrażeń obliczanego nad tabelą. |
|
Zwraca najmniejszą wartość liczbową w kolumnie. |
|
Zwraca najmniejszą wartość z zestawu wyrażeń obliczanych w tabeli. |
|
Dodaje wszystkie liczby w kolumnie. |
Funkcje agregacji języka DAX
Język DAX zawiera funkcje agregacji, które umożliwiają określenie tabeli, nad którą ma zostać wykonana agregacja. W związku z tym zamiast tylko dodawania lub uśredniania wartości w kolumnie te funkcje umożliwiają tworzenie wyrażenia, które dynamicznie definiuje dane do agregowania.
W poniższej tabeli wymieniono funkcje agregacji dostępne w języku DAX.
Funkcja |
Zastosowanie |
Oblicza średnią zestaw wyrażeń obliczanych w tabeli. |
|
Zlicza zestaw wyrażeń obliczanych w tabeli. |
|
Zlicza puste wartości w kolumnie. |
|
Zlicza całkowitą liczbę wierszy w tabeli. |
|
Zlicza wiersze zwrócone przez funkcję tabeli zagnieżdżonych, taką jak funkcja filtru. |
|
Zwraca sumę zestawu wyrażeń obliczanych w tabeli. |
Różnice między językem DAX a Excel agregacji
Mimo że te funkcje mają takie same nazwy jak ich odpowiedniki Excel, korzystają z aparatu analizy w pamięci firmy Power Pivot i zostały ponownie napisane do pracy z tabelami i kolumnami. Nie można używać formuły języka DAX w skoroszycie Excel i odwrotnie. Można ich używać tylko w oknie Power Pivot i w tabelach przestawnych opartych na Power Pivot danych. Chociaż funkcje mają identyczne nazwy, zachowanie może się nieco różnić. Aby uzyskać więcej informacji, zobacz tematy dotyczące poszczególnych funkcji.
Sposób, w jaki wartości kolumn są obliczane w agregacji, różni się również od sposobu, w jaki Excel agregacji. Przykład może ułatwić zilustrować.
Załóżmy, że chcesz uzyskać sumę wartości w kolumnie Kwota w tabeli Sprzedaż, utwórz więc następującą formułę:
=SUM('Sales'[Amount])
W najprostszym przypadku funkcja pobiera wartości z pojedynczej kolumny niefiltrowane, a wynik jest taki sam jak w programie Excel, co zawsze powoduje tylko sumę 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 zsuń te poszczególne wartości. Power Pivot oblicza każdy wiersz, w którym jest wykonywana agregacja, i oblicza pojedynczą wartość skalarną dla każdego wiersza, a następnie wykonuje agregację dla tych wartości. Dlatego wyniki formuły mogą być inne, jeśli do tabeli zastosowano filtry lub czy 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 agregowania tabel opisanych w poprzedniej sekcji język DAX zawiera funkcje agregacji, które działają z datami i godzinami, które są określone przez Ciebie, aby zapewnić wbudowaną analizę czasową. Te funkcje używają zakresów dat do uzyskania powiązanych wartości i agregowania wartości. Możesz również porównywać wartości we wszystkich zakresach dat.
W poniższej tabeli wymieniono funkcje analizy czasowej, których można używać dla agregacji.
Funkcja |
Zastosowanie |
Oblicza wartość na końcu kalendarza danego okresu. |
|
Oblicza wartość na końcu kalendarza okresu wcześniejszego niż ten okres. |
|
Oblicza wartość dla interwału, który rozpoczyna się od pierwszego dnia okresu i kończy się najpóźniejszą datą w określonej kolumnie daty. |
Pozostałe funkcje w sekcji Funkcje analizy czasowej(funkcjeanalizy 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 DATA.INPERIOD w celu zwrócenia zakresu dat i użyć tego zestawu dat jako argumentu innej funkcji w celu obliczenia agregacji niestandardowej tylko dla tych dat.