Freigeben über

  • Artikel

Erstellen von Formeln für Berechnungen

In diesem Thema werden die Grundlagen zur Erstellung von Formeln in PowerPivot für Excel beschrieben, ein Beispiel für die Erstellung einer berechneten Spalte veranschaulicht und die Arbeit mit Tabellen beschrieben. Das Thema enthält die folgenden Abschnitte:

  • Measures und berechnete Spalten

  • Formelgrundlagen

  • Arbeiten mit Tabellen und Spalten

  • Beheben von Fehlern in Formeln

Zusätzliche Informationen finden Sie in den folgenden Themen:

Measures und berechnete Spalten

Innerhalb einer PowerPivot-Arbeitsmappe können Sie Formeln in berechneten Spalten und Measures verwenden:

  • Eine berechnete Spalte ist eine Spalte, die Sie einer vorhandenen PowerPivot-Tabelle hinzufügen. Anstatt Werte in die Spalte einzufügen oder zu importieren, erstellen Sie eine Data Analysis Expressions (DAX)-Formel, die die Spaltenwerte definiert. Wenn Sie eine PowerPivot-Tabelle in eine PivotTable (oder ein PivotChart) integrieren, kann die berechnete Spalte wie alle anderen Datenspalten verwendet werden.

  • Ein Measure ist eine Formel, die speziell zur Verwendung in einer PivotTable (oder einem PivotChart) erstellt wird, in der PowerPivot-Daten verwendet werden. Measures können auf Standardaggregationsfunktionen basieren, z. B. COUNT oder SUM, oder Sie können mit DAX eigene Formeln definieren. Ein Measure wird im Bereich Werte einer PivotTable verwendet. Wenn Sie berechnete Ergebnisse in einem anderen Bereich einer PivotTable einfügen möchten, verwenden Sie stattdessen eine berechnete Spalte.

Formelgrundlagen

PowerPivot für Excel stellt eine neue Formelsprache (DAX) zum Erstellen benutzerdefinierter Berechnungen bereit. DAX ermöglicht Benutzern, benutzerdefinierte Berechnungen in PowerPivot-Tabellen und Excel PivotTables zu definieren. DAX enthält einige der in Excel-Formeln verwendeten Funktionen sowie zusätzliche Funktionen, die für die Verwendung mit relationalen Daten und für dynamische Aggregation entwickelt wurden. Weitere Informationen finden Sie unter Übersicht über Data Analysis Expressions (DAX).

Formeln können komplex sein, aber die folgende Tabelle enthält grundlegende Formeln, die in einer berechneten PowerPivot-Spalte verwendet werden können.

Formel

Beschreibung

=TODAY()

Fügt das aktuelle Datum in jede Zeile der Spalte ein.

=3

Fügt den Wert 3 in jede Zeile der Spalte ein.

=[Column1] + [Column2]

Addiert die Werte in der gleichen Zeile von [Column1] und [Column2] und fügt die Ergebnisse in die gleiche Zeile der berechneten Spalte ein.

Das Erstellen von PowerPivot-Formeln für berechnete Spalten ähnelt dem Erstellen von Formeln in Microsoft Excel. Sie erstellen Formeln für Measures in einem der folgenden Dialogfelder: Measureeinstellungen (Dialogfeld), Standardaggregation oder Measureeinstellungen (Dialogfeld), benutzerdefinierte Aggregation.

Führen Sie die folgenden Schritte aus, um eine Formel zu erstellen:

  1. Alle Formeln müssen mit einem Gleichheitszeichen beginnen.

  2. Sie können einen Funktionsnamen eingeben oder auswählen oder einen Ausdruck eingeben.

  3. Geben Sie die ersten Buchstaben der gewünschten Funktion oder des Namens ein. Mithilfe von AutoVervollständigen wird eine Liste der verfügbaren Funktionen, Tabellen und Spalten angezeigt. Drücken Sie TAB-TASTE, um ein Element aus der AutoVervollständigen-Liste zur Formel hinzuzufügen.

  4. Klicken Sie auf die Schaltfläche Fx, um eine Liste der verfügbaren Funktionen anzuzeigen. Um in der Dropdownliste eine Funktion auszuwählen, heben Sie diese mit den Pfeiltasten hervor, und klicken Sie auf OK, um die Formel der Funktion hinzuzufügen.

  5. Geben Sie die Argumente für die Funktion an, indem Sie sie aus einer Dropdownliste möglicher Tabellen und Spalten auswählen oder manuell eingeben.

  6. Überprüfen Sie die Syntax auf Fehler: Stellen Sie sicher, dass alle Klammern geschlossen sind und dass alle Verweise auf Spalten, Tabellen und Werte gültig sind.

  7. Drücken Sie die EINGABETASTE, um die Formel zu übernehmen.

    HinweisHinweis

    Eine berechneten Spalte wird mit Werten gefüllt, sobald die Formel akzeptiert wurde. In einem Measure wird durch Drücken der EINGABETASTE die Measuredefinition gespeichert. Ist das Measure neu, wird das Measure von PowerPivot automatisch dem Wertebereich der PivotTable hinzugefügt.

Erstellen einer einfachen Formel

Im folgenden Beispiel wird gezeigt, wie auf Grundlage der folgenden Daten mit einer einfachen Formel eine berechnete Spalte erstellt wird:

SalesDate

Subcategory

Product

Sales

Quantity

1/5/2009

Accessories

Carrying Case

254995

68

1/5/2009

Accessories

Mini Battery Charger

1099.56

44

1/5/2009

Digital

Slim Digital

6512

44

1/6/2009

Accessories

Telephoto Conversion Lens

1662.5

18

1/6/2009

Accessories

Tripod

938.34

18

1/6/2009

Accessories

USB Cable

1230.25

26

So erstellen Sie eine berechnete Spalte mit einer einfachen Formel

  1. Wählen Sie in der vorhergehenden Tabelle Daten aus, einschließlich der Tabellenüberschriften, und kopieren Sie sie.

  2. Klicken Sie im PowerPivot-Fenster auf der Registerkarte Home auf Einfügen.

  3. Klicken Sie im Dialogfeld Vorschau einfügen auf OK.

  4. Klicken Sie auf der Registerkarte Entwurf in der Gruppe Spalten auf Hinzufügen.

  5. Geben Sie in der Bearbeitungsleiste über der Tabelle die folgende Formel ein.

    =[Sales] / [Quantity]

  6. Drücken Sie die EINGABETASTE, um die Formel zu übernehmen.

    Werte werden für alle Zeilen in der berechneten Spalte eingetragen.

Tipps zum Verwenden von AutoVervollständigen

  • Sie können AutoVervollständigen für Formeln auch mitten in einer vorhandenen Formel mit geschachtelten Funktionen verwenden. Anhand des Textes unmittelbar vor der Einfügemarke werden Werte in der Dropdownliste angezeigt. Der Text nach der Einfügemarke bleibt unverändert.

  • In PowerPivot werden keine schließenden Funktionsklammern hinzugefügt oder Klammern automatisch abgeglichen. Jede Funktion muss syntaktisch richtig sein, oder Sie können die Formel nicht speichern oder verwenden. Von PowerPivot werden Klammern hervorgehoben, wodurch die Prüfung auf ordnungsgemäße Verwendung (Klammern geschlossen) vereinfacht wird.

Weitere Informationen zum Verwenden von AutoComplete finden Sie unter Erstellen einer berechneten Spalte und Erstellen eines Measures.

Arbeiten mit Tabellen und Spalten

PowerPivot-Tabellen sind rein äußerlich mit Excel-Tabellen vergleichbar, allerdings werden Daten und Formeln anders von ihnen verarbeitet:

  • Formeln funktionieren nur für Tabellen und Spalten, nicht für einzelne Zellen, Bereichsverweise oder Arrays.

  • Formeln können Beziehungen verwenden, um Werte aus verknüpften Tabellen abzurufen. Die Werte, die abgerufen werden, beziehen sich immer auf den aktuellen Zeilenwert.

  • Sie können DAX-Formeln (Data Analysis Expressions) nicht in eine Excel-Arbeitsmappe einfügen und umgekehrt.

  • Sie können keine unregelmäßigen Daten verwenden, so wie dies in einem Excel-Arbeitsblatt möglich ist. Jede Zeile in einer Tabelle muss die gleiche Anzahl von Spalten enthalten. In einigen Spalten können jedoch leere Werte stehen. Excel- und PowerPivot-Datentabellen sind nicht austauschbar, aber Sie können von PowerPivot aus auf Excel-Tabellen verweisen und Excel-Daten in PowerPivot einfügen. Weitere Informationen finden Sie unter Hinzufügen von Daten mithilfe verknüpfter Excel-Tabellen und Kopieren und Einfügen von Daten in PowerPivot.

Verweisen auf Tabellen und Spalten in Formeln und Ausdrücken

Sie können auf alle Tabellen und Spalten anhand des Namens verweisen. Die folgende Formel veranschaulicht z. B., wie Sie auf die Spalten aus zwei Tabellen verweisen, indem Sie den vollqualifizierten Namen verwenden:

=SUM('New Sales'[Amount]) + SUM('Past Sales'[Amount])

Wenn eine Formel ausgewertet wird, überprüft PowerPivot für Excel zuerst die allgemeine Syntax und vergleicht dann die von Ihnen bereitgestellten Namen der Spalten und Tabellen mit möglichen Spalten und Tabellen im aktuellen Kontext. Wenn der Name mehrdeutig ist oder die Spalte oder die Tabelle nicht gefunden werden kann, wird für die Formel (statt eines Datenwerts wird in Zellen, in denen der Fehler auftritt, eine #ERROR-Zeichenfolge angezeigt) ein Fehler ausgegeben. Weitere Informationen zum Benennen von Anforderungen für Tabellen, Spalten und andere Objekte finden Sie unter "Benennungsanforderungen" in DAX-Syntaxspezifikation für PowerPivot.

HinweisHinweis

Der Kontext ist ein wichtiges Feature von PowerPivot-Arbeitsmappen, mit dem Sie dynamische Formeln erstellen können. Der Kontext wird von den Tabellen in der Arbeitsmappe, den Beziehungen zwischen den Tabellen und den angewendeten Filtern bestimmt. Weitere Informationen finden Sie unter Kontext in DAX-Formeln.

Tabellenbeziehungen

Tabellen können sich auf andere Tabellen beziehen. Das Erstellen von Beziehungen bietet die Möglichkeit, in einer anderen Tabelle nach Daten zu suchen und verknüpfte Werte zu verwenden, um komplexe Berechnungen durchzuführen. Sie können z. B. eine berechnete Spalte verwenden, um alle Versanddatensätze für den aktuellen Wiederverkäufer nachzuschlagen und anschließend die jeweiligen Versandkosten zu addieren. Der Effekt ist vergleichbar mit einer parametrisierten Abfrage: Sie können für jede Zeile in der aktuellen Tabelle eine andere Summe berechnen.

Viele DAX-Funktionen erfordern, dass zwischen zwei Tabellen oder unter mehreren Tabellen eine Beziehung besteht, um die referenzierten Spalten zu finden und sinnvolle Ergebnisse zurückzugeben. Andere Funktionen versuchen, die Beziehung zu ermitteln. Um jedoch die besten Ergebnisse zu erzielen, sollten Sie nach Möglichkeit immer eine Beziehung erstellen. Weitere Informationen finden Sie in den folgenden Themen:

Wenn Sie mit PivotTables arbeiten, ist es besonders wichtig, dass Sie alle Tabellen verbinden, die in der PivotTable verwendet werden, damit die Zusammenfassungsdaten korrekt berechnet werden können. Weitere Informationen finden Sie unter Verwenden von Beziehungen in PivotTables.

Beheben von Fehlern in Formeln

Falls Sie beim Definieren einer berechneten Spalte einen Fehler erhalten, enthält die Formel ggf. entweder einen Syntaxfehler oder einen Semantikfehler.

Syntaxfehler sind am einfachsten zu beheben. Meist geht es um eine fehlende Klammer oder ein fehlendes Komma. Hilfe zur Syntax einzelner Funktionen finden Sie unter DAX-Funktionsreferenz für PowerPivot.

Der andere Fehlertyp tritt auf, wenn die Syntax keinen Fehler aufweist, aber der Wert oder die Spalte, auf den bzw. die verwiesen wird, im Kontext der Formel keinen Sinn ergibt. Semantikfehler dieser Art können die folgenden Ursachen haben:

  • Die Formel verweist auf eine nicht vorhandene Spalte, Tabelle oder Funktion.

  • Die Formel scheint richtig zu sein. Wenn das PowerPivot-Datenmodul jedoch Daten abruft, wird ein Typenkonflikt erkannt und ein Fehler ausgelöst.

  • Die Formel übergibt eine falsche Zahl oder einen falschen Typ von Parametern an eine Funktion.

  • Die Formel verweist auf eine andere Spalte mit einem Fehler, weshalb die Werte ungültig sind.

  • Die Formel verweist auf eine Spalte, die nicht verarbeitet wurde. Dies kann eintreten, wenn Sie für die Arbeitsmappe in den manuellen Modus gewechselt, Änderungen vorgenommen und dann die Daten oder Berechnungen nicht aktualisiert haben.

In den ersten vier Fällen kennzeichnet DAX die gesamte Spalte mit der ungültigen Formel. Im letzten Fall blendet DAX die Spalte ab, um anzugeben, dass sich die Spalte in einem nicht verarbeiteten Zustand befindet.