Agregacje w formułach
W tym temacie przedstawiono agregacji oraz zawiera omówienie typów agregacji, które są możliwe przy PowerPivot for Excel. PowerPivot for Excel zawiera te narzędzia do budowania skupiska:
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.PowerPivotjest zintegrowany z funkcjami tabela przestawna w programie Excel i zawiera wiele rozszerzeń.
Za pomocą języka formuł DAX można projektować agregacje niestandardowe.DAX mogą być używane do tworzenia kolumn obliczeniowych w tabelach PowerPivot i utworzyć środki w tabel i wykresów przestawnych.
W ostatniej sekcji tego tematu zamieszczono łącza do bardziej szczegółowych informacji dotyczących sposobu tworzenia agregacji.
Wprowadzenie do agregacji
Skupiska sposób zwijanie, podsumowujące lub grupowanie 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:
Zlicza liczbę transakcji były w miesiącu?
Średnie co było średniej sprzedaży w tym miesiącu przez sprzedawcę?
Wartości minimalne i maksymalne Okręgi sprzedaży, które zostały góry pięciu kategoriach liczby 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 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że PowerPivot for Excel umożliwia tworzenie niestandardowych formuł dla agregacja, oprócz standardowych agregacja w programie Excel.
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 zlicza liczbę transakcji zostały tam w miesiącu, z wyłączeniem okno koniec miesiąca?
Współczynniki, za pomocą średnie przez czas co było procentowy wzrost lub spadek sprzedaży w porównaniu do tego samego okresu w ubiegłym roku?
Grupowane wartości minimalne i maksymalne Okręgi sprzedaży, które zostały ujęte w rankingu najlepszych dla każdej kategorii produktów lub dla każdego promocji sprzedaży?
Dodawanie agregacji do formuł i tabel przestawnych
When you have a general idea of how your data should be grouped to be meaningful, and the values that you want to work with, you can decide whether to build a PivotTable or create calculations within a table.PowerPivot for Excel extends and improves the native ability of Excel to create aggregations such as sums, counts, or averages.Można tworzyć niestandardowe agregacji w PowerPivot albo w PowerPivot okna, lub w obrębie obszaru tabela przestawna 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 miara, można tworzyć dynamiczne filtry zdefiniowane w formule i filtry nałożonych przez projekt tabela przestawna i wybór Fragmentator, nagłówki kolumn oraz nagłówkami wierszy agregacji.
Aby uzyskać więcej informacji, zobacz Tworzenie formuł w celu wykonywania 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 PowerPivot for Excel próbuje rozwiązać problem, poprzez automatyczne wykrywanie i sugerowanie 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.Fragmentator są nową funkcją w programie Excel i w PowerPivot for Excel , umożliwiają interaktywnie grupowanie, sortowanie i filtrować wyniki tabela przestawna.
Praca z grupowaniami w formule
Można również użyć grup i kategorii, aby wartość zagregowana dane przechowywane w tabelach tworzenia relacji między tabelami, a następnie tworzenie formuł, wykorzystujące te relacje odszukać powiązane wartości.
Innymi słowy Jeśli chcesz utworzyć formułę wartości tej grupy według kategorii będzie najpierw użyć relacji do łączenia tabela zawierające szczegółowe dane i tabele zawierające kategorie, a następnie utworzenie formuły.
Aby uzyskać więcej informacji na temat sposobu tworzenia formuł wykorzystujących wyszukiwań, zobacz Relacje i odnośniki w formułach.
Używanie filtrów w agregacjach
Nowa funkcja w PowerPivot jest możliwość zastosowania filtrów do kolumn i tabel danych, nie tylko w interfejs użytkownika i tabela przestawna lub wykresu, ale również w bardzo formuły, które służy 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 Filtrowanie danych w formułach.
Porównanie funkcji agregacji programu Excel i funkcji agregacji języka DAX
W poniższej tabela wymieniono niektóre standardowe agregacja funkcje dostarczane przez program Excel i zawierają łącza do wykonania tych funkcji w 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
Chociaż te funkcje mają takie same nazwy, jak ich odpowiedniki w programie Excel, mogą wykorzystywać PowerPivot VertiPaq silnika i zostały przerobione do pracy z tabel i kolumn. Nie można użyć formuły DAX skoroszyt programu Excel i vice versa.Można ich używać tylko w PowerPivot okna i w tabelach przestawnych, oparty na PowerPivot danych.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.
Załóżmy, że chcemy uzyskać sumy wartości w Amount kolumna w tabela sprzedaży, tak aby utworzyć następującą formułę:
=SUM('Sales'[Amount])
W najprostszym przypadek funkcja pobiera wartości z jednej kolumna niefiltrowanym, a wynik jest taka sama, jak w programie Excel, zawsze tylko sumuje wartości kolumna, Amount.However, in PowerPivot, the formula is interpreted as "Get the value in Amount for each row of the Sales table, and then add up those individual values.PowerPivot evaluates each row over which the aggregation is performed and calculates a single scalar value for each row, and then performs an aggregation on those values.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 nowych funkcji agregacja tabela opisane w poprzedniej sekcji, DAX ma funkcje agregacja, które działają z datami i czas określony, zapewniające wbudowane 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ść na koniec okresu kalendarza. |
|
Oblicza wartość na koniec okresu poprzedzającego okresu kalendarza. |
|
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. |
Drugi funkcjas w analizy czasowej funkcja sekcji (Funkcje analizy czasowej (język DAX)) są funkcjas, używany do pobierania dat lub niestandardowe zakresy dat w agregacja.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
Inne zasoby
Key Concepts in DAX