Agregacje w formułach
W tym temacie przedstawiono wprowadzenie do agregacji oraz zamieszczono przegląd typów agregacji, których można używać w programie PowerPivot for Excel. Program PowerPivot for Excel oferuje następujące narzędzia do tworzenia agregacji:
Można tworzyć tabele przestawne i wykresy przestawne oparte na danych programu PowerPivot. Tabele przestawne programu Excel to popularne narzędzie do grupowania i podsumowywania danych w arkuszach. Program PowerPivot jest zintegrowany z funkcjami tabel przestawnych w programie Excel i oferuje wiele ulepszeń.
Za pomocą języka formuł DAX można projektować agregacje niestandardowe. Języka DAX można używać w celu tworzenia kolumn obliczeniowych w tabelach programu PowerPivot oraz miar w tabelach przestawnych i na wykresach przestawnych.
W ostatniej sekcji tego tematu zamieszczono łącza do bardziej szczegółowych informacji dotyczących sposobu tworzenia agregacji.
Wprowadzenie do agregacji
Agregacje stanowią metodę zwijania, podsumowywania lub grupowania danych. Podczas rozpoczynania pracy z nieprzetworzonymi danymi 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. Dlatego ważną częścią pracy analityka jest zdefiniowanie agregacji upraszczających, streszczających lub podsumowujących wzorce w odpowiedzi na określone pytanie biznesowe.
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:
Liczebnoś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 zawierają jeszcze wartości, za pomocą których można je grupować, takich jak kategoria produktów lub nazwa regionu geograficznego, gdzie znajduje się sklep, można wprowadzić grupy do danych, dodając kategorie. Podczas tworzenia grup w programie Excel należy ręcznie wpisać lub wybrać grupy, które mają być używane, spośród kolumn w arkuszu.
Jednak w systemie relacyjnym hierarchie, takie jak kategorie produktów, często są przechowywane w tabeli innej niż tabela faktów lub wartości. Zazwyczaj tabela kategorii jest połączona z danymi faktów za pomocą pewnego rodzaju klucza. Na przykład można założyć, że dane zawierają identyfikatory produktów, ale nie zawierają ich nazw ani kategorii. Aby dodać kategorię do płaskiego arkusza programu Excel, można skopiować do niego kolumnę, która zawierała nazwy kategorii. Jednak do skoroszytu programu PowerPivot można zaimportować tabelę kategorii produktów, można utworzyć relację między tabelą z numerami i listą kategorii produktów, a następnie użyć kategorii w celu grupowania danych. Aby uzyskać więcej informacji, zobacz temat Relacje 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 program PowerPivot for Excel umożliwia, oprócz użycia standardowych agregacji dostępnych w 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 liczebnoś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. Program PowerPivot for Excel rozszerza i ulepsza możliwości programu Excel w zakresie tworzenia agregacji, takich jak sumy, zliczenia i średnie. W programie PowerPivot można tworzyć agregacje niestandardowe, używając okna programu PowerPivot lub obszaru tabeli przestawnej programu Excel.
W kolumnie obliczeniowej można tworzyć agregacje uwzględniające bieżący kontekst wiersza w celu pobierania pokrewnych wierszy z innej tabeli, a następnie sumowania, zliczania lub uśredniania tych wartości w powiązanych wierszach.
W mierze można tworzyć dynamiczne agregacje, w których są używane zarówno filtry zdefiniowane w formule, jak i filtry wynikające z projektu tabeli przestawnej oraz wyboru fragmentatorów, nagłówków kolumn i nagłówków wierszy.
Aby uzyskać więcej informacji, zobacz temat Tworzenie formuł na potrzeby obliczeń.
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 program PowerPivot for Excel będzie podejmował próby rozwiązania problemu, automatycznie wykrywając i sugerując relacje. Aby uzyskać więcej informacji, zobacz temat 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 to nowa funkcja w programach Excel i PowerPivot for Excel, która umożliwia 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ł używających odnośników, zobacz temat Relacje i odnośniki w formułach.
Używanie filtrów w agregacjach
Nową funkcją programu PowerPivot 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 także w formułach używanych do obliczania agregacji. Filtrów można używać w formułach znajdujących się w kolumnach obliczeniowych i miarach.
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 programie PowerPivot for Excel. 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 |
Zwraca średnią arytmetyczną wszystkich liczb w kolumnie. |
|
Zwraca średnią arytmetyczną wszystkich wartości w kolumnie. Obsługuje wartości tekstowe i 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ń obliczanych w tabeli. |
|
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 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 |
Oblicza średnią zestawu wyrażeń obliczanych dla tabeli. |
|
Zlicza wyrażenia w zestawie wyrażeń obliczanych dla tabeli. |
|
Zlicza puste wartości w kolumnie. |
|
Zlicza łączną liczbę wierszy w tabeli. |
|
Zlicza wiersze zwracane przez zagnieżdżoną funkcję tabeli, na przykład funkcję filtru. |
|
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 mają takie same nazwy jak ich odpowiedniki w programie Excel (w wersji angielskiej), wykorzystują aparat VertiPaq programu PowerPivot i zostały napisane ponownie, aby działały z tabelami i kolumnami. Formuł języka DAX nie można używać w skoroszycie programu Excel i odwrotnie. Można używać ich tylko w oknie programu PowerPivot i tabelach przestawnych utworzonych na podstawie danych programu PowerPivot. Ponadto, mimo że te funkcje mają identyczne nazwy, ich zachowanie może być nieco inne. Aby uzyskać więcej informacji, zobacz tematy z opisami 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 programie PowerPivot ta formuła 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”. Program PowerPivot 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 temat Kontekst w formułach języka DAX.
Funkcje analizy czasowej języka DAX
Oprócz nowych 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 |
Oblicza wartość dla kalendarzowego końca danego okresu. |
|
Oblicza wartość dla kalendarzowego końca okresu poprzedzającego dany okres. |
|
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 (język DAX)) 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.
Zobacz także
Koncepcje
Relacje i odnośniki w formułach