Agregacje w dodatku Power Pivot

Uwaga:  Staramy się udostępniać najnowszą zawartość Pomocy w Twoim języku tak szybko, jak to możliwe. 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. Prosimy o powiadomienie nas, czy te informacje były pomocne, u dołu tej strony. Oto angielskojęzyczny artykuł do wglądu.

Agregacje służą zwijanie, podsumowywania lub grupowanie danych. Po rozpoczęciu dane z tabel i innych źródeł danych, dane są często płaskie, czyli istnieje wiele szczegółów, ale nie został zorganizowane lub pogrupowane w żaden sposób. Ten brak podsumowania lub struktury mogą utrudnić identyfikować wzorce w danych. Ważne część modelowanie danych jest określenie agregacji uprościć abstrakcyjne i podsumowywanie wzorce w odpowiedzi na pytanie firmy.

Najczęściej używane agregacji, takie jak kontakty za pomocą Średnia, ZLICZANIE, DISTINCTCOUNT, MAX, MINlub Suma mogą być tworzone w miarę automatycznie za pomocą funkcji AutoSumowanie. Inne rodzaje agregacji, takie jak AVERAGEX, COUNTX, COUNTROWSlub SUMXzwracają tabelę i wymagają formuły utworzony przy użyciu Data Analysis Expressions (DAX).

Opis agregacji w dodatku Power Pivot

Wybieranie grup do agregacji

Podczas agregowania dane są grupowane według atrybutów, takich jak produkt, cena, region lub data, i jest definiowana formuła wykonująca operacje na wszystkich danych w grupie. Na przykład agregacja jest tworzona podczas tworzenia sumy dla roku. Jeśli następnie zostanie utworzony stosunek tego roku do roku poprzedniego i zostanie on wyświetlony w formie wartości procentowej, będzie to inny typ agregacji.

Decyzja dotycząca sposobu grupowania danych jest podejmowana na podstawie pytania biznesowego. Na przykład agregacje mogą dawać odpowiedzi na następujące pytania:

Zlicza   Ile transakcji zostało zrealizowanych w ciągu miesiąca?

Średnie    Jaką średnią sprzedaż w tym miesiącu każdy sprzedawca?

Wartości minimalne i maksymalne    Które Okręgi sprzedaży należały do pięciu najlepszych pod względem sprzedanych jednostek?

Aby utworzyć obliczenie odpowiadające na te pytania, trzeba 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 używanymi do organizowania wyników.

Jeśli dane nie zawiera wartości, które służą do grupowania, takich jak kategoria produktów lub nazwę regionu geograficznego, w którym znajduje się magazyn, może być wprowadzenie grup danych przez dodanie kategorii. Podczas tworzenia grupy w programie Excel, możesz ręcznie wpisz lub wybierz pozycję grupy, który ma być używany spośród kolumn w arkuszu. Jednak w układzie relacyjnych hierarchii, takich jak kategorie produktów są często przechowywane w tabelę inną niż fakt lub tabela wartości. Zazwyczaj tabeli Kategoria jest połączony z danymi faktów przy niektórych rodzajów klucza. Załóżmy, że znajdą się, że dane zawierają identyfikatory produktów, ale nie nazwy produktów lub ich kategorii. Aby dodać kategorię do arkusza programu Excel prostym, trzeba skopiować kolumnę zawierającą nazwy kategorii. Z Power Pivot Importowanie tabeli kategorii produktów do modelu danych, tworzenie relacji między tabeli zawierającej dane liczbowe i listy kategorii produktów, a następnie użyj kategorii umożliwia grupowanie danych. Aby uzyskać więcej informacji zobacz Tworzenie relacji między tabelami.

Wybieranie funkcji dla agregacji

Po zidentyfikowaniu i dodaniu grupowań, które mają być używane, należy wybrać funkcje matematyczne, które będą używane w agregacji. Słowo „agregacja” często jest używane jako synonim operacji matematycznych lub statystycznych, które są używane w agregacjach, takich jak sumowanie, wyznaczanie wartości średniej lub minimalnej albo zliczanie. Jednak dodatek Power Pivot umożliwia, oprócz użycia standardowych agregacji dostępnych w dodatku Power Pivot i programie Excel, tworzenie dla agregacji formuł niestandardowych.

Na przykład używając takich samych zestawów wartości i grupowań jak w poprzednich przykładach, można utworzyć agregacje niestandardowe odpowiadające na następujące pytania:

Zlicza, przefiltrowana   Ile transakcji zostało zrealizowanych w ciągu miesiąca, z wyłączeniem okna koniec miesiąca?

Współczynniki użyciem średnich za okres    Jaki był procentowy wzrost lub spadek sprzedaży w porównaniu z tym samym okresem w ubiegłym roku?

Wartości minimalne i maksymalne grupowanego    Które Okręgi sprzedaży były najlepsze w każdej kategorii produktów lub w każdej promocji handlowej?

Dodawanie agregacji do formuł i tabel przestawnych

Po określeniu ogólnej koncepcji dotyczącej sposobu sensownego grupowania danych oraz wartości, które mają być używane, można zdecydować, czy utworzyć tabelę przestawną czy obliczenia w obrębie tabeli. Dodatek Power Pivot rozszerza i ulepsza możliwości programu Excel w zakresie tworzenia agregacji, takich jak sumy, liczności i średnie. W dodatku Power Pivot można tworzyć agregacje niestandardowe, używając okna dodatku Power Pivot lub obszaru tabeli przestawnej programu Excel.

  • W kolumnie obliczeniowejmożna utworzyć agregacje, które wziąć pod uwagę bieżący kontekst wiersza do pobrania wierszy powiązanych z innej tabeli i sumuje, Zliczanie lub średnia tych wartości powiązane wiersze.

  • W miaręmożna utworzyć dynamiczne agregacji, których są używane filtry zdefiniowane w formule i filtry narzucane przez projekt tabeli przestawnej i wybór fragmentatory, nagłówki kolumn i nagłówków wierszy. Miary przy użyciu standardowej agregacji można tworzyć w Power Pivot przy użyciu funkcji Autosumowania lub przy użyciu utworzonej formuły. Można także tworzyć niejawne miary za pomocą standardowej agregacji w tabeli przestawnej w programie Excel.

Dodawanie grupowań do tabeli przestawnej

Podczas projektowania tabeli przestawnej należy przeciągać pola reprezentujące grupowania, kategorie lub hierarchie do sekcji kolumn i wierszy tabeli przestawnej w celu grupowania danych. Pola zawierające wartości liczbowe należy następnie przeciągać do obszaru wartości, dzięki czemu będzie można je zliczać, uśredniać lub sumować.

Jeśli dodawanie kategorii do tabeli przestawnej, ale dane kategorii nie jest powiązany z danych faktów, może zostać wyświetlony komunikat o błędzie lub szczególne wyniki. Zazwyczaj Power Pivot spróbuje rozwiązać ten problem, automatyczne wykrywanie i sugerowania relacje. Aby uzyskać więcej informacji zobacz Praca z relacjami w tabelach przestawnych.

Pola można także przeciągać do fragmentatorów, aby wybrać pewne grupy danych do wyświetlenia. Fragmentatory umożliwiają interakcyjne grupowanie, sortowanie i filtrowanie wyników w tabeli przestawnej.

Praca z grupowaniami w formule

Grupowań i kategorii można używać także do agregowania danych przechowywanych w tabelach, tworząc relacje między tabelami, a następnie tworząc formuły wykorzystujące te relacje do wyszukiwania powiązanych wartości.

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

Aby uzyskać więcej informacji o tworzeniu formuł, w których są używane odnośniki zobacz temat odnośniki w formułach dodatku Power Pivot.

Używanie filtrów w agregacjach

Nowa funkcja Power Pivot jest możliwość stosowanie filtrów do kolumn i tabel danych, nie tylko w interfejsie użytkownika, a w tabeli przestawnej lub wykresu, ale również w formułach bardzo używanych do obliczania agregacji. Filtry mogą zostać użyte w formułach zarówno w kolumnach obliczeniowych i s.

Na przykład w nowych funkcjach agregacji języka DAX jako argument można określić całą tabelę, a nie wartości, które mają zostać zsumowane lub zliczone. Jeśli do tabeli nie zastosowano żadnych filtrów, funkcja agregacji będzie wykonywać operacje na wszystkich wartościach w określonej kolumnie tabeli. Jednak w języku DAX można tworzyć dynamiczne lub statyczne filtry w tabeli, dzięki czemu agregacje będą wykonywane na różnych podzbiorach danych w zależności od warunku filtru i bieżącego kontekstu.

Łącząc warunki i filtry w formułach, można tworzyć agregacje zmieniające się w zależności od wartości dostarczonych w formułach lub w zależności od wybranych nagłówków wierszy i 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 programu Excel oraz łącza do implementacji tych funkcji w dodatku Power Pivot. Wersje tych funkcji dostępne w języku DAX zachowują się bardzo podobnie do funkcji programu Excel. Występują tylko drobne różnice w składni i obsłudze pewnych typów danych.

Standardowe funkcje agregacji

Funkcja

Zastosowanie

ŚREDNIA

Zwraca średnią arytmetyczną wszystkich liczb w kolumnie.

ŚREDNIA.A

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

LICZBA

Zlicza wartości liczbowe w kolumnie.

ILE.NIEPUSTYCH

Zlicza niepuste wartości w kolumnie.

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

Dodaje wszystkie liczby w kolumnie.

Funkcje agregacji języka DAX

Język DAX zawiera funkcje agregacji umożliwiające określenie tabeli, na której jest wykonywana agregacja. Dlatego, zamiast po prostu obliczać sumę lub średnią wartości w kolumnie, funkcje te umożliwiają tworzenie wyrażeń, które dynamicznie definiują dane do agregacji.

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

Funkcja

Zastosowanie

AVERAGEX

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

COUNTAX

Zlicza wyrażenia w zestawie wyrażeń obliczanych dla tabeli.

LICZ.PUSTE

Zlicza puste wartości w kolumnie.

COUNTX

Zlicza łączną liczbę wierszy w tabeli.

NUMER WIERSZA

Zlicza wiersze zwracane przez zagnieżdżoną funkcję tabeli, na przykład funkcję filtru.

SUMX

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

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

Mimo że te funkcje takich samych nazwach, jak ich odpowiedniki programu Excel, są używane aparat analizy w pamięci Power Pivot i została ponownie napisana do pracy z tabelami i kolumnami. Nie można używać formuł języka DAX w skoroszycie programu Excel i odwrotnie. Ich można używać tylko w oknie Power Pivot i w tabelach przestawnych, które są oparte na danych Power Pivot. Ponadto mimo że funkcji identyczne nazwy, zachowanie może się nieco różnić. Aby uzyskać więcej informacji zobacz Tematy odwołanie poszczególnych funkcji.

Sposób obliczania kolumn w agregacji również jest inny niż sposób obsługi agregacji w programie Excel. Poniższy przykład umożliwia zilustrowanie różnic.

Na przykład trzeba pobrać sumę wartości z kolumny Amount w tabeli Sales. W tym celu można utworzyć następującą formułę:

=SUM('Sales'[Amount])

W przypadku najłatwiejszym funkcja pobiera wartości z pojedynczej kolumny niefiltrowanym, a wynik jest taki sam, jak w programie Excel, które zawsze sumuje wartości w kolumnie Kwota. Jednak w Power Pivot formuła jest interpretowana jako "uzyskać wartość dla każdego wiersza w tabeli Sales Amount, a następnie zsumowanie tych poszczególnych wartości. Power Pivot oblicza każdego wiersza, w którym agregacji jest wykonywane oblicza pojedynczą wartość skalarną dla każdego wiersza, a następnie wykonuje agregację na te wartości. W związku z tym w wyniku formuły może być różne filtry zostały zastosowane do tabeli lub jeśli wartości są obliczane oparte na inne agregacje, 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 tabeli opisane w poprzedniej sekcji języka DAX ma funkcje agregacji, które współpracują z daty i godziny możesz określić, zapewniające analizy czasowej wbudowane. Te funkcje za pomocą zakresów dat do pobierania wartości powiązane i agregowanie wartości. Możesz również porównać wartości w zakresach daty.

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

Funkcja

Zastosowanie

CLOSINGBALANCEMONTH

CLOSINGBALANCEQUARTER

CLOSINGBALANCEYEAR

Oblicza wartość dla kalendarzowego końca danego okresu.

OPENINGBALANCEMONTH

OPENINGBALANCEQUARTER

OPENINGBALANCEYEAR

Oblicza wartość dla kalendarzowego końca okresu poprzedzającego dany okres.

TOTALMTD

TOTALYTD

TOTALQTD

Oblicza wartość dla interwału rozpoczynającego się pierwszego dnia okresu i kończącego w dniu wskazywanym przez ostatnią datę w określonej kolumnie dat.

Inne funkcje w sekcji funkcji analizy czasowej (Funkcje analizy czasowej) są funkcje, których można używać do pobierania dat lub niestandardowych zakresów dat, aby używać w agregacji. Można na przykład funkcja DATESINPERIOD i zwraca zakres dat i użycie tego zestawu dat jako argumentu do innej funkcji do obliczania agregacji niestandardowej dla tylko te daty.

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.

×