Agregacje w dodatku Power Pivot

Ważne: Ten artykuł został przetłumaczony maszynowo, zobacz zastrzeżenie. Angielskojęzyczną wersję tego artykułu można znaleźć tutaj .

Agregacje stanowią metodę zwijania, podsumowywania lub grupowania danych. Podczas rozpoczynania pracy z nieprzetworzonymi danymi pochodzącymi z tabel lub innych źródeł danych dane często są płaskie, co oznacza, że zawierają wiele szczegółów, ale nie są w żaden sposób zorganizowane ani zgrupowane. Ten brak podsumowań oraz struktury może utrudniać odnajdowanie wzorców w danych. Ważną częścią procesu modelowania danych jest definiowanie agregacji upraszczających, streszczających lub podsumowujących wzorce w odpowiedzi na określone pytanie biznesowe.

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:

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

Średnie   Jaką średnią sprzedaż osiągnął 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:

Filtrowane liczności   Ile transakcji zostało zrealizowanych w ciągu miesiąca, z wyłączeniem okresu konserwacji na koniec miesiąca?

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

Zgrupowane wartości minimalne i maksymalne   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 obliczeniowej można tworzyć agregacje uwzględniające bieżący kontekst wiersza w celu pobierania powiązanych wierszy z innej tabeli, a następnie sumowania, zliczania lub uśredniania tych wartości w powiązanych wierszach.

  • 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 do tabeli przestawnej zostaną dodane kategorie, ale dane kategorii nie będą powiązane z danymi faktów, może wystąpić błąd albo mogą zostać zwrócone dziwne wyniki. Zazwyczaj dodatek Power Pivot będzie podejmował próby rozwiązania problemu, automatycznie wykrywając i sugerując 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 dotyczących sposobu tworzenia 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 temat 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.

ILE.LICZB

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.

COUNTBLANK

Zlicza puste wartości w kolumnie.

COUNTX

Zlicza łączną liczbę wierszy w tabeli.

COUNTROWS

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 najprostszym przypadku funkcja pobiera wartości z jednej nieprzefiltrowanej kolumny, a wynik jest wtedy taki sam jak w programie Excel — wartości z kolumny Amount są po prostu dodawane. Jednak w dodatku Power Pivot formuła ta jest interpretowana w następujący sposób: „pobierz wartość z kolumny Amount dla każdego wiersza tabeli Sales, a następnie dodaj te pojedyncze wartości”. Dodatek Power Pivot oblicza każdy wiersz, który jest objęty agregacją, i oblicza dla każdego wiersza jedną wartość skalarną, a następnie wykonuje agregację tych wartości. Dlatego wynik formuły będzie inny, jeśli do tabeli zostaną zastosowane filtry lub wartości będą 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 agregacji tabel opisanych w poprzedniej sekcji, język DAX zawiera funkcje agregacji działające z datami i godzinami określonymi przez użytkownika. Dzięki tym funkcjom można korzystać z wbudowanej analizy czasowej. Te funkcje używają zakresów dat w celu pobierania powiązanych wartości i agregowania wartości. Można także porównywać wartości w zakresach dat.

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 wymienione w sekcji funkcji analizy czasowej (Funkcje analizy czasowej) to funkcje, których można używać w celu pobierania dat lub niestandardowych zakresów dat do użycia w agregacji. Na przykład za pomocą funkcji DATESINPERIOD można zwrócić zakres dat, a następnie użyć uzyskanego zestawu dat jako argumentu innej funkcji w celu obliczenia dla tych dat agregacji niestandardowej.

Uwaga: Zrzeczenie dotyczące tłumaczenia maszynowego: Ten artykuł został przetłumaczony przez system komputerowy bez interwencji człowieka. Firma Microsoft udostępnia te tłumaczenia maszynowe, aby ułatwić użytkownikom, którzy nie znają języka angielskiego, korzystanie z zawartości dotyczącej produktów, usług i technologii firmy Microsoft. Ponieważ ten artykuł został przetłumaczony maszynowo, może zawierać błędy w słownictwie, składniowe lub gramatyczne.

Rozwijaj swoje umiejętności
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.

×