Tutorials

Master-Tutorial: PivotTable Berechnete Elemente & Lösungsreihenfolge

Melden

Hier ist ein ausführliches Tutorial zu den fortgeschrittenen PivotTable-Funktionen „Berechnete Elemente“ (Calculated Items) und der „Lösungsreihenfolge“ (Solve Order) in Microsoft Excel.

In PivotTables werden Berechnungen meist über „Berechnete Felder“ (Calculated Fields) gelöst. Wenn Sie jedoch Berechnungen innerhalb einer Kategorie (z. B. „Umsatz 2023“ minus „Umsatz 2022“) durchführen möchten, benötigen Sie Berechnete Elemente. Sobald Sie mehrere solcher Berechnungen in Zeilen und Spalten kombinieren, wird die Lösungsreihenfolge entscheidend.

Teil 1: Berechnete Elemente (Calculated Items)

Was ist ein berechnetes Element?

Im Gegensatz zu einem berechneten Feld (das eine neue Datenspalte simuliert), erstellt ein berechnetes Element einen neuen Datensatz innerhalb eines bestehenden Feldes.

Beispiel: Sie haben ein Feld „Region“ mit „Nord“ und „Süd“. Sie möchten ein neues Element „Gesamt Nord-Süd“ erstellen, das beide summiert.

Schritt-für-Schritt-Anleitung:

  1. Klicken Sie in Ihre PivotTable.
  2. Wichtig: Markieren Sie in der PivotTable eine Überschrift des Feldes, in dem Sie das Element hinzufügen wollen (z. B. auf das Wort „Nord“).
  3. Gehen Sie im Menüband zu PivotTable-Analyse > Felder, Elemente und Sätze > Berechnetes Element...
  4. Name: Geben Sie einen Namen ein (z. B. „Differenz_Ost_West“).
  5. Formel: Wählen Sie aus der Liste der Elemente die gewünschten aus.
    • Beispiel: = Ost - West
  6. Klicken Sie auf Hinzufügen und dann auf OK.

Hinweis: Berechnete Elemente sind nicht verfügbar, wenn die PivotTable auf dem "Datenmodell" (Power Pivot) basiert.

Teil 2: Das Problem der Überschneidung (Die Lösungsreihenfolge)

Wenn Sie sowohl in den Zeilen als auch in den Spalten berechnete Elemente haben, entsteht ein mathematischer Konflikt an dem Punkt, an dem sie sich kreuzen.

Ein Praxisbeispiel:

Stellen Sie sich vor:

  • In den Zeilen: Ein berechnetes Element „Bonus“ (Umsatz * 10%).
  • In den Spalten: Ein berechnetes Element „Abweichung“ (Jahr 2024 - Jahr 2023).

In der Zelle, in der sich „Bonus“ und „Abweichung“ treffen, stellt sich die Frage:

  1. Soll Excel zuerst den Bonus berechnen und davon die Abweichung nehmen?
  2. Oder soll Excel zuerst die Abweichung der Jahre berechnen und darauf den Bonus anwenden?

Je nach Reihenfolge kann das Ergebnis unterschiedlich sein!

Teil 3: Die Lösungsreihenfolge (Solve Order) anpassen

Excel bietet ein verstecktes Menü, um genau diese Priorität festzulegen.

So verwalten Sie die Lösungsreihenfolge:

  1. Klicken Sie in die PivotTable.
  2. Navigieren Sie zu PivotTable-Analyse > Felder, Elemente und Sätze.
  3. Wählen Sie ganz unten den Punkt Lösungsreihenfolge... (Solve Order).
  4. Im nun erscheinenden Fenster sehen Sie alle berechneten Elemente Ihrer PivotTable.
  5. Klicken Sie auf eine Formel und nutzen Sie die Schaltflächen Nach oben oder Nach unten, um die Priorität zu ändern.
    • Die unterste Formel in der Liste gewinnt! Das heißt, die Formel mit der höchsten Nummer (die zuletzt ausgeführt wird) bestimmt den Endwert in der Schnittmenge-Zelle.

Teil 4: Formeln auflisten (Kontrolle)

Bei komplexen PivotTables verliert man leicht den Überblick, welche Berechnungen im Hintergrund laufen.

Tipp: Gehen Sie zu PivotTable-Analyse > Felder, Elemente und Sätze > Formeln auflisten. Excel erstellt automatisch ein neues Tabellenblatt, in dem alle berechneten Felder, Elemente und deren Lösungsreihenfolge detailliert aufgelistet sind. Dies ist ideal für die Dokumentation und Fehlersuche.

Teil 5: Wichtige Einschränkungen und Best Practices

Damit Ihre PivotTable stabil bleibt, beachten Sie folgende Punkte:

  1. Speicherplatz: Berechnete Elemente können bei sehr großen Datenmengen die Performance verschlechtern, da sie viele virtuelle Zeilen im Cache erzeugen.
  2. Gruppierung: Sie können keine berechneten Elemente in einem Feld erstellen, das bereits gruppiert ist (z. B. nach Datum/Monaten). Heben Sie die Gruppierung zuerst auf.
  3. Filter: Wenn Sie Filter verwenden, stellen Sie sicher, dass die in der Formel verwendeten Elemente nicht weggefiltert werden, da dies zu Fehlern führen kann.
  4. Alternative: Wenn Sie mit sehr großen Datenmengen arbeiten, ist es oft besser, Berechnungen über DAX-Measures in Power Pivot durchzuführen, da diese flexibler und performanter sind.

Zusammenfassung

  • Berechnete Elemente verändern die Struktur innerhalb eines Feldes (Zeile/Spalte).
  • Die Lösungsreihenfolge entscheidet bei Überschneidungen zweier Formeln, welche Berechnung Vorrang hat.
  • Nutzen Sie Formeln auflisten, um den Überblick zu behalten.
0