Aggregationen in Formeln
Dieses Thema gibt eine Einführung in Aggregationen und stellt eine Übersicht über die Typen von Aggregationen bereit, die mit PowerPivot für Excel möglich sind. PowerPivot für Excel enthält die Tools zum Erstellen von Aggregationen:
Sie können auf PowerPivot-Daten basierende PivotTables und PivotCharts erstellen. Excel-PivotTables sind ein gängiges Tool zum Gruppieren und Zusammenfassen von Daten in Arbeitsblättern. PowerPivot ist in die PivotTable-Funktionen in Excel integriert und stellt viele Erweiterungen bereit.
Sie können benutzerdefinierte Aggregationen mithilfe der DAX-Formelsprache entwerfen. Mit DAX können berechnete Spalten in PowerPivot-Tabellen und Measures in PivotTables und PivotCharts erstellt werden.
Der abschließende Abschnitt dieses Themas enthält Links zu ausführlicheren Informationen zur Erstellung von Aggregationen.
Einführung in Aggregationen
Aggregationen sind eine Art der Reduzierung, Zusammenfassung oder Gruppierung von Daten. Wenn Sie mit Rohdaten von Tabellen oder anderen Datenquellen beginnen, sind die Daten häufig sehr detailliert aber ohne Struktur oder Gruppierungen dargestellt. Ohne Zusammenfassungen oder Struktur kann das Ermitteln von Datenmustern schwierig sein. Daher besteht eine wichtige Aufgabe des Analytikers darin, Aggregationen zu definieren, die Muster vereinfachen, abstrahieren oder zusammenfassen, um eine bestimmte Geschäftsanforderung zu erfüllen.
Auswählen von Gruppen zur Aggregation
Beim Aggregieren von Daten gruppieren Sie die Daten nach Attributen, wie z. B. Produkt, Preis, Region oder Datum, und definieren dann eine Formel, mit der alle Daten in der Gruppe verarbeitet werden. Beispielsweise erstellen Sie mit einem Gesamtbetrag für ein Jahr eine Aggregation. Wenn Sie dann das Verhältnis des aktuellen Jahres gegenüber dem vorherigen Jahr erstellen und die zugehörigen Prozentsätze darstellen, ist dies eine andere Art von Aggregation.
Die Entscheidung über die Gruppierung der Daten ist von der Geschäftsfrage abhängig. Mit Aggregationen können z. B. die folgenden Fragen beantwortet werden:
Zählungen Wie viele Transaktionen gab es in einem Monat?
Durchschnittsberechnung Was waren die durchschnittlichen Verkäufe eines Vertriebsmitarbeiters in diesem Monat?
Mindest- und Höchstwerte Welche Verkaufsgebiete zählen in Hinsicht auf verkaufte Einheiten zu den oberen fünf?
Um eine Berechnung zu erstellen, die diese Fragen beantwortet, müssen Sie über detaillierte Daten mit den zu zählenden oder zu addierenden Werten verfügen, und diese numerischen Daten müssen in einer Beziehung mit den Gruppen stehen, die Sie zum Organisieren der Ergebnisse verwenden.
Wenn die Daten noch keine Werte zum Gruppieren enthalten, z. B. eine Produktkategorie oder den Namen des geografischen Orts, an dem sich das Geschäft befindet, sollten Sie durch Hinzufügen von Kategorien Ihre Daten gruppieren. Wenn Sie Gruppen in Excel erstellen, müssen Sie die Gruppen, die Sie aus den Spalten im Arbeitsblatt verwenden möchten, manuell eingeben oder auswählen.
In einem relationalen System werden jedoch Hierarchien, wie z. B. Kategorien für Produkte, oft in einer anderen Tabelle als der Fakten- oder Wertetabelle gespeichert. Normalerweise wird die Kategorietabelle durch eine Art von Schlüssel mit den Faktendaten verknüpft. Angenommen, Ihre Daten enthalten Produkt-IDs, jedoch nicht die Namen oder Kategorien der Produkte. Sie müssten den Namen in die Spalte mit den Kategorienamen kopieren, um die Kategorie einer Excel-Flatfile hinzuzufügen. In einer PowerPivot-Arbeitsmappe können Sie jedoch die Produktkategorietabelle in die Arbeitsmappe importieren, eine Beziehung zwischen der Tabelle mit den Zahlendaten und der Produktkategorieliste erstellen und anschließend die Kategorien zum Gruppieren von Daten verwenden. Weitere Informationen finden Sie unter Beziehungen zwischen Tabellen.
Auswählen einer Funktion zur Aggregation
Nachdem Sie die zu verwendenden Gruppen festgelegt und hinzugefügt haben, müssen Sie entscheiden, welche mathematischen Funktionen für die Aggregation verwendet werden sollen. Das Wort Aggregation wird oft als Synonym für die mathematischen oder statistischen Operationen verwendet, die in Aggregationen zum Einsatz kommen, wie z. B. Summen, Mittelwerte, Minimum oder Anzahl. Mit PowerPivot für Excel können Sie jedoch neben den Standardaggregationen von Excel auch benutzerdefinierte Aggregationsformeln erstellen.
Beispielsweise könnten Sie mit demselben Satz von Werten und Gruppierungen aus den vorherigen Beispielen benutzerdefinierte Aggregationen erstellen, um die folgenden Fragen zu beantworten:
Gefilterte Zählungen Wie viele Transaktionen gab es in einem Monat ohne den Wartungszeitraum am Ende des Monats?
Verhältnisse mit Mittelwerten im Zeitverlauf Wie hoch war das prozentuale Wachstum oder der Rückgang beim Vertrieb im Vergleich zum selben Zeitraum des letzten Jahres?
Gruppierte Mindest- und Höchstwerte Welche Verkaufsgebiete wurden für einzelne Produktkategorien oder Werbeaktionen am besten bewertet?
Hinzufügen von Aggregationen zu Formeln und PivotTables
Wenn Sie eine allgemeine Vorstellung davon haben, wie Ihre Daten sinnvoll gruppiert werden sollen, und über die zu verarbeitenden Werte verfügen, können Sie eine PivotTable erstellen oder Berechnungen innerhalb einer Tabelle durchführen. Mit PowerPivot für Excel werden die systemeigenen Möglichkeiten von Excel zum Erstellen von Aggregationen, wie Summen, Zählungen oder Mittelwerten, erweitert und verbessert. Sie können benutzerdefinierte Aggregationen in PowerPivot entweder innerhalb des PowerPivot-Fensters oder innerhalb des Excel-PivotTable-Bereichs erstellen.
In einer berechneten Spalte können Sie Aggregationen erstellen, die den aktuellen Zeilenkontext berücksichtigen, um verknüpfte Zeilen aus einer anderen Tabelle abzurufen, und diese Werte in den verknüpften Zeilen dann summieren, zählen oder deren Mittelwert bilden.
In einem Measure können Sie dynamische Aggregationen erstellen, die sowohl Filter verwenden, die in der Formel definiert sind, als auch Filter, die durch den Aufbau der PivotTable und die Auswahl von Datenschnitten, Spaltenüberschriften und Zeilenüberschriften vorgegeben werden.
Weitere Informationen finden Sie unter Erstellen von Formeln für Berechnungen.
Hinzufügen von Gruppierungen zu einer PivotTable
Wenn Sie eine PivotTable entwerfen, ziehen Sie Felder, die die Gruppierungen, Kategorien oder Hierarchien darstellen, in die Spalten- und Zeilenabschnitte der PivotTable, um die Daten zu gruppieren. Anschließend ziehen Sie Felder mit numerischen Werten in den Wertebereich, damit diese gezählt werden können oder deren Mittelwert oder Summe gebildet werden kann.
Wenn Sie einer PivotTable Kategorien hinzufügen, die Kategoriedaten jedoch keinen Bezug zu den Faktendaten haben, werden möglicherweise Fehler oder unerwartete Ergebnisse angezeigt. Normalerweise versucht PowerPivot für Excel das Problem zu beheben, indem Beziehungen automatisch erkannt und vorgeschlagen werden. Weitere Informationen finden Sie unter Verwenden von Beziehungen in PivotTables.
Sie können Felder auch in Datenschnitte ziehen, um bestimmte Gruppen von Daten zum Anzeigen auszuwählen. Datenschnitte sind ein neues Feature in Excel und PowerPivot für Excel, mit der Sie Ergebnisse in einer PivotTable interaktiv gruppieren, sortieren und filtern können.
Arbeiten mit Gruppierungen in einer Formel
Sie können auch Gruppierungen und Kategorien verwenden, um in Tabellen gespeicherte Daten zu aggregieren, indem Sie Beziehungen zwischen Tabellen erstellen und dann Formeln angeben, die anhand dieser Beziehungen nach verknüpften Werten suchen.
Wenn Sie eine Formel erstellen möchten, die Werte nach einer Kategorie gruppiert, verknüpfen Sie mithilfe einer Beziehung zuerst die Tabelle, die die ausführlichen Daten enthält und die Tabellen, die die Kategorien enthalten, und erstellen Sie dann die Formel.
Weitere Informationen zum Erstellen von Formeln für Suchvorgänge finden Sie im folgenden Thema: Beziehungen and Suchvorgänge in Formeln.
Verwenden von Filtern in Aggregationen
Ein neues Feature in PowerPivot ist die Möglichkeit, Filter auf Datenspalten und -tabellen anzuwenden, und dies kann nicht nur auf der Benutzeroberfläche und innerhalb einer PivotTable oder eines PivotCharts erfolgen, sondern auch in den Formeln, mit denen Sie Aggregationen berechnen. Filter können in Formeln sowohl in berechneten Spalten als auch in Measures verwendet werden.
Zum Beispiel können Sie in den neuen DAX-Aggregationsfunktionen anstelle von Werten, die addiert oder gezählt werden sollen, eine ganze Tabelle als Argument angeben. Wenn Sie keine Filter auf diese Tabelle angewendet haben, werden mit der Aggregationsfunktion alle Werte in der angegebenen Spalte der Tabelle verarbeitet. In DAX können Sie jedoch dynamische oder statische Filter für die Tabelle erstellen, sodass die Aggregation je nach Filterbedingung und aktuellem Kontext für unterschiedliche Teilmengen ausgeführt wird.
Durch Kombinieren von Bedingungen und Filtern in Formeln können Sie Aggregationen erstellen, die je nach den in den Formeln bereitgestellten Werten oder je nach Auswahl von Zeilen- oder Spaltenüberschriften in einer PivotTable geändert werden.
Weitere Informationen finden Sie unter Filtern von Daten in Formeln.
Vergleich von Excel-Aggregationsfunktionen und DAX-Aggregationsfunktionen
In der folgenden Tabelle sind einige der Standardaggregationsfunktionen in Excel ausgeführt. Außerdem werden Links zur Implementierung dieser Funktionen in PowerPivot für Excel bereitgestellt. Die DAX-Version dieser Funktionen verhält sich im Wesentlichen wie in Excel. Dabei gibt es nur einige kleinere Unterschiede in der Syntax und in der Verarbeitung bestimmter Datentypen.
Standardaggregationsfunktionen
Funktion |
Verwendung |
Gibt den Mittelwert (arithmetisches Mittel) aller Zahlen in einer Spalte zurück. |
|
Gibt den Mittelwert (arithmetisches Mittel) aller Werte in einer Spalte zurück. Unterstützt Text und nicht numerische Werte. |
|
Zählt die numerischen Werte in einer Spalte. |
|
Zählt die Anzahl der Werte in einer Spalte, die nicht leer sind. |
|
Gibt den größten numerischen Wert in einer Spalte zurück. |
|
Gibt den größten Wert in einem Satz von Ausdrücken zurück, die für eine Tabelle ausgewertet wurden. |
|
Gibt den kleinsten numerischen Wert in einer Spalte zurück. |
|
Gibt den kleinsten Wert in einem Satz von Ausdrücken zurück, die für eine Tabelle ausgewertet wurden. |
|
Addiert alle Zahlen in einer Spalte. |
DAX-Aggregationsfunktionen
DAX enthält Aggregationsfunktionen, mit denen Sie eine Tabelle angeben können, für die die Aggregation ausgeführt werden soll. Statt die Werte in einer Spalte lediglich zu addieren oder ihren Mittelwert zu berechnen, können Sie mit diesen Funktionen einen Ausdruck erstellen, der die zu aggregierenden Daten dynamisch definiert.
In der folgenden Tabelle sind die in DAX verfügbaren Aggregatfunktionen aufgelistet.
Funktion |
Verwendung |
Ermittelt den Mittelwert der für eine Tabelle ausgewerteten Ausdrücke. |
|
Zählt die für eine Tabelle ausgewerteten Ausdrücke. |
|
Zählt die Anzahl leerer Werte in einer Spalte. |
|
Zählt die Gesamtzahl der Zeilen in einer Tabelle. |
|
Zählt die Anzahl der Zeilen, die von einer geschachtelten Tabellenfunktion zurückgegeben wird, z. B. einer Filterfunktion. |
|
Gibt die Summe einer Reihe von Ausdrücken zurück, die für eine Tabelle ausgewertet wird. |
Unterschiede zwischen Aggregationsfunktionen in DAX und in Excel
Obwohl diese Funktionen die gleichen Namen wie ihre Excel-Äquivalente aufweisen, verwenden sie das PowerPivot-VertiPaq-Modul und wurden umgeschrieben, um die Kompatibilität mit Tabellen und Spalten zu gewährleisten. Sie können keine DAX-Formel in einer Excel-Arbeitsmappe verwenden (und umgekehrt). Sie können nur im PowerPivot-Fenster und in PivotTables verwendet werden, die auf PowerPivot-Daten basieren. Darüber hinaus kann das Verhalten dieser Funktionen leicht abweichen, obwohl sie die gleichen Namen haben. Weitere Informationen finden Sie in den Themen zu den einzelnen Funktionen.
Die Auswertung von Spalten in einer Aggregation unterscheidet sich ebenfalls von der Behandlung von Aggregationen in Excel. Durch folgendes Beispiel wird dies veranschaulicht.
Angenommen, Sie möchten die Summe der Werte in der Amount-Spalte der Sales-Tabelle berechnen, weshalb Sie die folgende Formel erstellen:
=SUM('Sales'[Amount])
Im einfachsten Fall ruft die Funktion die Werte in einer einzelnen ungefilterten Spalte ab, und das Ergebnis entspricht dem Ergebnis in Excel, das lediglich die Werte in der Amount-Spalte addiert. In PowerPivot jedoch wird die Formel so interpretiert, dass der Wert in Amount für jede Zeile der Tabelle Sales abgerufen werden soll, und anschließend werden die individuellen Werte addiert. In PowerPivot wird jede Zeile ausgewertet, für die die Aggregation durchgeführt wird, und eine einzelner Skalarwert wird für jede Zeile berechnet. Anschließend wird eine Aggregation für diese Werte durchgeführt. Daher kann das Ergebnis einer Formel abweichen, wenn Filter auf eine Tabelle angewendet wurden, oder wenn die Werte auf Grundlage anderer Aggregationen berechnet werden, die möglicherweise gefiltert sind. Weitere Informationen finden Sie unter Kontext in DAX-Formeln.
DAX-Zeitintelligenzfunktionen
Neben den im vorherigen Abschnitt beschriebenen neuen Tabellenaggregationsfunktionen verfügt DAX über Aggregationsfunktionen, die mit von Ihnen angegebenen Datums- und Uhrzeitangaben arbeiten, um Zeitintelligenz bereitzustellen. Bei diesen Funktionen werden Datumsbereiche verwendet, um verknüpfte Werte abzurufen und die Werte zu aggregieren. Außerdem können Werte über Datumsbereiche hinweg verglichen werden.
In der folgenden Tabelle sind die Zeitintelligenzfunktionen aufgeführt, die zur Aggregation verwendet werden können.
Funktion |
Verwendung |
Berechnet einen Wert am Kalenderende des angegebenen Zeitraums. |
|
Berechnet vor dem angegebenen Zeitraum einen Wert am Kalenderende des Punkts. |
|
Berechnet einen Wert für das Intervall, das mit dem ersten Tag des Zeitraums beginnt und mit dem letzten Datum in der angegebenen Datumsspalte endet. |
Die anderen Funktionen im Abschnitt für Zeitintelligenzfunktionen (Zeitintelligenzfunktionen (DAX)) sind Funktionen, mit denen Datumsangaben oder benutzerdefinierte Datumsbereiche zu Verwendung in Aggregation abgerufen werden können. Sie können mithilfe der DATESINPERIOD-Funktion einen Datumsbereich zurückgeben und diesen Satz von Datumsangaben als Argument für eine andere Funktion verwenden, um eine benutzerdefinierte Aggregation ausschließlich für diese Datumsangaben zu berechnen.
Siehe auch
Konzepte
Andere Ressourcen
Key Concepts