Agregacje w dodatku Power Pivot

Agregacje to sposób na zwinięcie, podsumowanie lub zgrupowanie danych. Po rozpoczęciu pracy z danymi nieprzetworzonymi z tabel lub innych źródeł danych dane są często płaskie, co oznacza, że istnieje wiele szczegółów, które nie zostały zorganizowane ani zgrupowane w żaden sposób. Ten brak podsumowań lub struktury może utrudnić odnajdowanie wzorców w danych. Ważną częścią modelowania danych jest definiowanie agregacji, które upraszczają, abstrakcyjne lub podsumowują wzorce w odpowiedzi na konkretne pytanie biznesowe.

Większość typowych agregacji, takich jak te, które używają średnich, zliczania, DistinctCount, Max, minlub sum , można tworzyć w mierze automatycznie przy użyciu funkcji Autosumowanie. Inne typy agregacji, takie jak Funkcja AVERAGEX, COUNTX, COUNTROWSlub SUMX, zwracają tabelę i wymagają formuły utworzonej przy użyciu wyrażeń analizy danych (DAX).

Opis agregacji w Power Pivot

Wybieranie grup do agregacji

W przypadku agregacji danych dane są grupowane według atrybutów, takich jak produkt, Cena, region lub data, a następnie określana jest formuła, która działa na wszystkich danych w grupie. Na przykład podczas tworzenia sumy dla roku tworzona jest agregacja. Jeśli następnie utworzysz stosunek tego roku w poprzednim roku i prezentują je jako wartości procentowe, jest to inny typ agregacji.

Decyzja o sposobie grupowania danych jest obsługiwana przez pytanie biznesowe. Na przykład agregacja może odpowiedzieć na następujące pytania:

Zlicza   Ile transakcji było w danym miesiącu?

Średnie    Co to jest średnia sprzedaż w tym miesiącu według sprzedawcy?

Wartości minimalne i maksymalne    Które okręgi sprzedaży były pięć pierwszych w kategoriach sprzedanych jednostek?

Aby utworzyć obliczenie odnoszące się do tych pytań, należy mieć szczegółowe dane zawierające liczby, które mają zostać zliczone lub zsumowane, a dane liczbowe muszą być powiązane w jakiś sposób z grupami, które będą używane do organizowania wyników.

Jeśli dane nie zawierają jeszcze wartości, których można używać do grupowania, na przykład kategorii produktów lub nazwy regionu geograficznego, w którym znajduje się sklep, warto dodać grupy do danych, dodając kategorie. Podczas tworzenia grup w programie Excel należy 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 dla produktów, często są przechowywane w innej tabeli niż tabela faktów lub wartości. Zazwyczaj tabela kategorii jest połączona z danymi faktów przez pewien rodzaj klucza. Załóżmy na przykład, że dane zawierają identyfikatory produktów, ale nie nazwy produktów lub ich kategorie. Aby dodać kategorię do płaskiego arkusza programu Excel, należy ją skopiować w kolumnie zawierającej nazwy kategorii. W 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 użyć kategorii do grupowania danych. Aby uzyskać więcej informacji, zobacz Tworzenie relacji między tabelami.

Wybieranie funkcji agregacji

Po zidentyfikowaniu i dodaniu grup do użycia należy zdecydować, które funkcje matematyczne mają być używane do agregacji. Często agregacja wyrazów jest używana jako synonim dla operacji matematycznych lub statystycznych używanych w agregacjach, takich jak sumy, wartości średnie, wartości minimalne i liczby. Jednak Power Pivot umożliwia tworzenie niestandardowych formuł do agregacji oprócz agregacji standardowych znalezionych w obu Power Pivot ach i programie Excel.

Na przykład przy użyciu tego samego zestawu wartości i grupowania, które zostały użyte w powyższych przykładach, można utworzyć agregacje niestandardowe, aby odpowiedzieć na następujące pytania:

Liczba filtrowanych   Ile transakcji było w danym miesiącu, z wyłączeniem okna obsługi w ciągu końca miesiąca?

Współczynniki używające średnich w czasie    Jaki był wzrost procentowy lub spadek sprzedaży w porównaniu do tego samego okresu w ubiegłym roku?

Grupowanie wartości minimum i maksimum    Które okręgi sprzedaży są uszeregowane według kategorii dla poszczególnych produktów lub dla poszczególnych promocji sprzedaży?

Dodawanie agregacji do formuł i tabel przestawnych

Jeśli masz ogólny pogląd, w jaki sposób dane powinny być grupowane, a wartości, z którymi chcesz pracować, możesz zdecydować, czy utworzyć tabelę przestawną, czy utworzyć obliczenia w tabeli. Power Pivot rozszerza i usprawnia natywną zdolność programu Excel do tworzenia agregacji, takich jak sumy, liczniki lub średnie. Agregacje niestandardowe można tworzyć w Power Pivot w oknie Power Pivot lub w obszarze tabeli przestawnej programu Excel.

  • W kolumnie obliczeniowejmożna tworzyć agregacje, które uwzględniają bieżący kontekst wiersza w celu pobrania powiązanych wierszy z innej tabeli, a następnie sumowania, zliczania lub uśredniania tych wartości w powiązanych wierszach.

  • W ramach miarymożna tworzyć agregacje dynamiczne, w których są używane oba filtry zdefiniowane w formule, oraz filtry narzucone przez projekt tabeli przestawnej oraz Zaznaczanie fragmentatorów, nagłówków kolumn i nagłówków wierszy. Miary używające agregacji standardowej można utworzyć w Power Pivot za pomocą Autosumowania lub tworząc formułę. W programie Excel można też tworzyć niejawne miary przy użyciu standardowych agregacji w tabeli przestawnej.

Dodawanie grup do tabeli przestawnej

Podczas projektowania tabeli przestawnej przeciągam pola reprezentujące grupy, kategorie lub hierarchie do sekcji kolumny i wiersze tabeli przestawnej w celu pogrupowania danych. Następnie przeciągaj pola zawierające wartości liczbowe w obszar wartości, tak aby można było je liczyć, obliczyć lub zsumować.

Jeśli dodasz kategorie do tabeli przestawnej, ale dane kategorii nie są powiązane z danymi faktów, może zostać wyświetlony błąd lub szczególne wyniki. Zazwyczaj Power Pivot będzie próbować 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 wybrać określone grupy danych do wyświetlenia. Fragmentatory umożliwiają interaktywne grupowanie, sortowanie i filtrowanie wyników w tabeli przestawnej.

Praca z grupami w formule

Możesz również użyć grup i kategorii do agregacji danych przechowywanych w tabelach, tworząc relacje między tabelami, tworząc formuły korzystające z tych relacji w celu wyszukania 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ł, w których są używane odnośniki, 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, a nie tylko w interfejsie użytkownika i w tabeli przestawnej lub na wykresie, ale także w formułach, które służą do obliczania agregacji. Filtry mogą być używane w formułach zarówno w kolumnach obliczeniowych, jak i w polach s.

Na przykład w nowych funkcjach agregacji języka DAX zamiast określać wartości, które mają zostać zsumowane lub zliczone, można określić całą tabelę jako argument. Jeśli do tej tabeli nie zastosowano żadnych filtrów, funkcja agregacji będzie działać na wszystkich wartościach w określonej kolumnie tabeli. Jednak w języku DAX można utworzyć filtr dynamiczny lub statyczny w tabeli, aby agregacja działała na różnych podzbiorach danych w zależności od warunków 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 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 agregacji programu Excel i funkcji agregacji języka DAX

W poniższej tabeli wymieniono niektóre standardowe funkcje agregacji dostępne w programie Excel, a także łącza do implementacji tych funkcji w Power Pivot. Wersja językowa języka DAX działa tak samo jak wersja programu Excel z niewielkimi różnicami w składni i obsługiwaniem określonych typów danych.

Standardowe funkcje agregacji

Funkcja

Zastosowanie

Średnia

Zwraca średnią (średnią arytmetyczną) wszystkich liczb w kolumnie.

Średnia

Zwraca średnią (średnią arytmetyczną) wszystkich wartości w kolumnie. Obsługuje wartości tekstowe i nieliczbowe.

Zliczanie

Zlicza liczby wartości liczbowych w kolumnie.

Liczba

Zlicza wartości w kolumnie, która nie jest pusta.

MAX

Zwraca największą wartość liczbową w kolumnie.

MAXX

Zwraca największą wartość z zestawu wyrażeń obliczanych w tabeli.

MIN

Zwraca najmniejszą wartość liczbową w kolumnie.

MINX

Zwraca najmniejszą wartość z zestawu wyrażeń obliczanych w tabeli.

Suma

Umożliwia dodanie wszystkich liczb w kolumnie.

Funkcje agregacji języka DAX

Język DAX zawiera funkcje agregacji, które umożliwiają określenie tabeli, w której ma zostać wykonane agregacja. Dlatego też zamiast dodawać lub uśredniać wartości w kolumnie, te funkcje umożliwiają utworzenie wyrażenia umożliwiającego dynamiczne zdefiniowanie danych do zagregowania.

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

Funkcja

Zastosowanie

Funkcja averagex

Oblicza średnią z zestawu wyrażeń obliczanych w tabeli.

COUNTAX

Zlicza zbiór wyrażeń obliczanych w tabeli.

Licz. puste

Zwraca liczbę pustych wartości w kolumnie.

COUNTX

Zlicza całkowitą liczbę wierszy w tabeli.

COUNTROWS

Zlicza wiersze zwracane z funkcji tabeli zagnieżdżonej, takiej jak funkcja Filter.

SUMX

Zwraca sumę zestawu wyrażeń obliczanych w tabeli.

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

Chociaż te funkcje mają takie same nazwy jak odpowiadające im osoby korzystające z programu Excel, wykorzystują aparat analizy w pamięci, w którym są Power Pivot, i zostały poprawnie zapisane, aby pracować z tabelami i kolumnami. Nie można użyć formuły języka DAX w skoroszycie programu Excel i odwrotnie. Można ich użyć tylko w oknie Power Pivot i w tabelach przestawnych opartych na Power Pivot danych. Ponadto chociaż funkcje mają identyczne nazwy, zachowanie może być nieco inne. Aby uzyskać więcej informacji, zobacz tematy dotyczące poszczególnych funkcji.

Sposób szacowania kolumn w agregacji jest również różny od sposobu, w jaki program Excel obsługuje agregacje. Przykład może ułatwić zilustrowanie.

Załóżmy, że chcemy uzyskać sumę wartości w kolumnie Kwota w tabeli sprzedaż, więc utworzysz 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, co zawsze powoduje dodanie wartości w kolumnie — suma. Jednak w Power Pivot formuła jest interpretowana jako "Pobierz wartość w polu Kwota dla każdego wiersza tabeli sprzedaż, a następnie Dodaj te poszczególne wartości. Power Pivot ocenia każdy wiersz, do którego jest przeprowadzana agregacja, i oblicza pojedynczą wartość skalarną dla każdego wiersza, a następnie wykonuje agregację na tych wartościach. Dlatego wynik formuły może być inny, jeśli zastosowano filtry do tabeli lub jeśli wartości są obliczane na podstawie innych agregacji, które mogą być filtrowane. Aby uzyskać więcej informacji, zobacz temat kontekst w formułach języka DAX.

Funkcje analizy czasowej języka DAX

Oprócz funkcji agregacji tabel opisanych w poprzedniej sekcji język DAX zawiera funkcje agregacji, które działają z datami i czasami określonymi przez użytkownika, aby zapewnić wbudowaną analizę czasu. Te funkcje używają zakresów dat w celu uzyskania powiązanych wartości i zsumowania wartości. Można też porównywać wartości w zakresach dat.

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

Funkcja

Zastosowanie

CLOSINGBALANCEMONTH

CLOSINGBALANCEQUARTER

CLOSINGBALANCEYEAR

Oblicza wartość na końcu danego okresu w kalendarzu.

OPENINGBALANCEMONTH

OPENINGBALANCEQUARTER

OPENINGBALANCEYEAR

Oblicza wartość na końcu okresu sprzed określonego okresu.

TOTALMTD

TOTALYTD

TOTALQTD

Oblicza wartość w interwale rozpoczynającym się w pierwszym dniu okresu i kończy się najpóźniej w określonej kolumnie Date.

Inne funkcje w sekcji Analiza czasu (funkcje analizyczasowej) to funkcje, których można używać do pobierania dat lub niestandardowych zakresów dat używanych w agregacji. Na przykład można użyć funkcji DATESINPERIOD, aby zwrócić zakres dat, i użyć tego zestawu dat jako argumentu innej funkcji w celu obliczenia agregacji niestandardowej tylko dla tych dat.

Uwaga:  Ta strona została przetłumaczona automatycznie i może zawierać błędy gramatyczne lub nieścisłości. Chcemy, aby ta zawartość była dla Ciebie przydatna. Czy możesz dać nam znać, czy te informacje były pomocne? Oto angielskojęzyczny artykuł do wglądu.

Potrzebna dalsza pomoc?

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×