QUBA – Pivot-Tabellen

Status: GEPRÜFT (1,0) — PIVOTDATENZUORDNEN() (GETPIVOTDATA) inkl. Auto-Erzeugung, „harte Werte“ und Bruchgefahr über Folienskript bestätigt; berechnetes Feld vs. Hilfsspalte korrekt.

Cluster: Pivot-Tabellen als Analysewerkzeug, die Funktion PIVOTDATENZUORDNEN(), und der Aufbau konkreter Pivot-Analysen.

1. Pivottabellen – historischer Ballast oder unentbehrlich?

(Klausur 27.02.2024, Aufgabe 8, 7 Punkte)

Frage: Pivottabellen wurden 1993 in Excel eingeführt, das Konzept ist also über 30 Jahre alt. In der Praxis ist die Beliebtheit der „PivotTables“ jedoch ungebrochen. Ist das Pivottabellenfeature historischer Ballast oder ein weiterhin unentbehrliches Werkzeug für die tägliche Zahlenarbeit? Begründen Sie Ihr Urteil.

Musterlösung (Urteil: weiterhin unentbehrlich, nicht Ballast):

1. Effizienz und Geschwindigkeit: Pivot-Tabellen fassen große Datenmengen mit wenigen Klicks zusammen und analysieren sie – ohne tiefe technische Kenntnisse, ohne manuelle Formeln.
2. Flexibilität: Zeilen, Spalten und Werte lassen sich frei umstellen; verschiedene Dimensionen und Aggregationen (Summe, Mittelwert, Anzahl) plus benutzerdefinierte Felder.
3. Benutzerfreundlichkeit: intuitive Drag-and-Drop-Bedienung, breit einsetzbar von Finanzanalyse bis Projektmanagement.
4. Integration: kombinierbar mit Diagrammen, bedingter Formatierung, Power Query und Power Pivot; per VBA automatisierbar.
5. Weiterentwicklung: Microsoft hat Pivot kontinuierlich ausgebaut (Slicer/Datenschnitte, Zeitachse, Datenmodell, Verbindung zu Datenbanken und Big-Data-Quellen).

Ausgewogenheit (für Top-Note): Einschränkungen benennen, die moderne BI-Tools (Power BI) besser lösen – Leistung bei sehr großen Datensätzen, komplexere Datenmodellierung, cloudbasierte Kollaboration in Echtzeit. Fazit: kein Ballast, aber im Zusammenspiel mit dem Datenmodell/Power BI zu denken.

2. PIVOTDATENZUORDNEN()

(Klausur 26.07.2022, Aufgabe 7, 10 Punkte)

Frage: a) (4 P) Wozu genau dient die Tabellenblattfunktion PIVOTDATENZUORDNEN()? Warum wird sie gebraucht? b) (6 P) Erläutern Sie drei Nachteile dieser Tabellenblattfunktion.

Funktionsname: PIVOTDATENZUORDNEN() (englisch GETPIVOTDATA).

Musterlösung a): PIVOTDATENZUORDNEN() liest gezielt einzelne, aggregierte Werte aus einer Pivot-Tabelle in eine normale Zelle außerhalb der Pivot-Tabelle aus. Dadurch werden zusammengefasste Pivot-Ergebnisse in eigenen Berichten, Dashboards oder Weiterberechnungen verfügbar – ohne die Pivot-Struktur direkt anzuzeigen. Excel erzeugt die Funktion automatisch, wenn man beim Formelaufbau auf eine Pivot-Zelle klickt.

Warum gebraucht: stabiler Zugriff auf einen bestimmten Wert unabhängig von der aktuellen Zeilen-/Spaltenposition in der Pivot-Tabelle; erlaubt maßgeschneiderte Auswertungen und Berichte, die auf Pivot-Ergebnissen aufsetzen.

Musterlösung b) – drei Nachteile:

1. Abhängigkeit von der Pivot-Struktur / Bruchgefahr. Die Funktion referenziert Feld- und Elementnamen der Pivot-Tabelle. Ändert sich das Pivot-Layout, wird ein Feld/Element entfernt oder umbenannt, liefert die Formel #BEZUG!. [korrigiert: Der im Scan genannte „statisch/aktualisiert sich nicht"-Punkt ist irreführend – PIVOTDATENZUORDNEN() aktualisiert sich sehr wohl bei jeder Pivot-Neuberechnung. Der reale Nachteil ist die Zerbrechlichkeit gegenüber Strukturänderungen.]
2. Sperrige, schwer lesbare Syntax. Die automatisch erzeugten Formeln sind lang und mit vielen Feld/Element-Paaren schwer manuell zu bearbeiten und zu übertragen (nicht einfach „nach unten ziehbar“ wie ein relativer Bezug).
3. Eingeschränkte Flexibilität. Man kann nur bereits in der Pivot-Tabelle aggregierte Werte abrufen. Für Detaildaten oder andere Aggregationen muss erst die Pivot-Tabelle angepasst werden – kein direkter Zugriff auf die Rohdaten.

3. Pivotanalysen – Jahresumsatz pro Produkt und Region

(Klausur 29.07.2021, Aufgabe 3, 10 Punkte)

Frage: Sie wollen den Jahresumsatz pro Produkt und Region per Pivotanalyse ermitteln. Das Feld „Jahresumsatz“ (A), das Sie auswerten wollen, ist in den Quelldaten und damit in der Feldliste (B) nicht vorhanden. Die nötigen Quartalsumsätze sind jedoch vorhanden. a) (6 P) Erläutern Sie zwei unterschiedliche Wege, die gewünschte Pivotanalyse durchzuführen. b) (4 P) Nennen Sie für jeden Weg je einen spezifischen Vor- und Nachteil.

Musterlösung a) – zwei Wege:

Weg 1 – Berechnetes Feld in der PivotTable:

1. Produkt und Region als Zeilen-/Spaltenfelder anlegen.
2. In der PivotTable ein berechnetes Feld definieren (PivotTable-Analyse → Felder, Elemente und Gruppen → Berechnetes Feld).
3. Formel als Summe der Quartalsfelder: Jahresumsatz = Umsatz Q1 + Umsatz Q2 + Umsatz Q3 + Umsatz Q4.
4. Das neue Feld in den Wertebereich ziehen.

Weg 2 – Hilfsspalte in den Quelldaten:

1. In den Quelldaten neben den Quartalsspalten eine neue Spalte anlegen: =SUMME(Q1:Q4) bzw. =Q1+Q2+Q3+Q4 je Zeile.
2. Quelldaten speichern, PivotTable-Datenbereich um die neue Spalte erweitern und aktualisieren.
3. Das nun vorhandene Feld „Jahresumsatz“ in den Wertebereich ziehen.

Musterlösung b) – je ein Vor- und Nachteil:

🎯 Übungsaufgaben im Rohleder-Stil — gelöst

Selbst gebaute Aufgaben nach dem gemessenen Muster der echten Klausuren: nur „Erläutern Sie“, konkrete Formeln lesen und kritisieren, Presse-Fälle, Rollenwechsel. Grün umrandet = kein Original.

a) 6 P. – Drei Situationen für die Pivot-Tabelle

Situation 1: Eine große, saubere Einzelsatzliste soll nach mehreren Dimensionen verdichtet werden. Liegen die Daten als echte Liste vor, in der eine Zeile genau einen Vorgang beschreibt und jede Spalte ein Merkmal, dann verdichtet die Pivot-Tabelle mit wenigen Klicks und ohne eine einzige Formel, wofür man sonst ein Formelwerk aus SUMMEWENNS() bauen müsste. ⟨1⟩ Bei 40.000 Rechnungszeilen entsteht die Auswertung „Umsatz je Region, aufgegliedert nach Produktkategorie und Quartal" damit in Minuten, und sie ist gegen die typischen Kopier- und Bereichsfehler eines handgebauten Formelblatts immun, weil nichts kopiert wird. ⟨2⟩

Situation 2: Die Fragestellung steht noch nicht fest. In der explorativen Phase einer Analyse weiß niemand vorher, ob die interessante Sicht nach Region, nach Kunde oder nach Monat läuft; die Pivot-Tabelle erlaubt es, Felder per Drag-and-Drop zwischen Zeilen, Spalten und Werten zu tauschen und die Aggregation zwischen Summe, Mittelwert und Anzahl umzuschalten, ohne die Auswertung neu zu bauen. ⟨3⟩ Genau deshalb ist sie auch das schnellste Werkzeug zur Datenqualitätsprüfung, weil eine Zeilengruppierung über ein Feld sofort alle vorkommenden Ausprägungen zeigt und Tippfehler, doppelte Schreibweisen und unerwartete Leerwerte auf einen Blick sichtbar macht. ⟨4⟩

Situation 3: Ein wiederkehrender Standardbericht auf gleichbleibender Datenstruktur. Ändert sich von Monat zu Monat nur der Dateninhalt und nicht der Aufbau, genügt es, die Quelle zu aktualisieren und „Aktualisieren" zu klicken, sodass der Berichtsaufbau einmal erstellt und danach dauerhaft genutzt wird. ⟨5⟩ Die Pivot-Tabelle steht dabei nicht allein, sondern lässt sich mit PivotChart, bedingter Formatierung, Datenschnitten und Zeitachse zu einem bedienbaren Bericht ausbauen, mit Power Query für die Aufbereitung und Power Pivot beziehungsweise dem Datenmodell für mehrere verknüpfte Tabellen kombinieren und bei Bedarf per VBA automatisieren. ⟨6⟩

b) 4 P. – Zwei Situationen gegen die Pivot-Tabelle

Situation 1: Die Datenbasis ist keine Liste, oder es wird Modelllogik statt Aggregation gebraucht. Enthalten die Quelldaten verbundene Zellen, Zwischenüberschriften, mehrzeilige Kopfbereiche oder liegen sie bereits als Kreuztabelle vor, ist die Pivot-Tabelle das falsche Werkzeug, ebenso wenn die Auswertung eine Zeilenlogik verlangt, etwa eine laufende Bestandsfortschreibung oder eine Zuordnung über einen Schlüssel. ⟨1⟩ Richtig ist dann, die Daten zuerst mit Power Query in eine saubere Liste zu überführen und die eigentliche Rechnung mit SUMMEWENNS() und XVERWEIS() in einem bewusst gebauten Modell abzubilden, denn eine Pivot-Tabelle verdichtet Daten, sie rechnet keine Geschäftslogik. ⟨2⟩

Situation 2: Sehr große Datenmengen, mehrere verknüpfte Quellen oder ein revisionssicheres Ergebnis. Bei Datenmengen jenseits der Excel-Grenzen, bei komplexer Datenmodellierung über mehrere Systeme hinweg und bei der Anforderung, dass mehrere Personen gleichzeitig und versioniert an derselben Auswertung arbeiten, stößt die klassische Pivot-Tabelle an ihre Grenzen; hier sind das Datenmodell mit Power Pivot und DAX oder Power BI die passenden Werkzeuge. ⟨3⟩ Ungeeignet ist sie außerdem überall dort, wo ein Ergebnis zeilengenau belegt und von einem Prüfer nachgerechnet werden muss, denn die Pivot-Tabelle zeigt zwar auf Doppelklick die zugrunde liegenden Datensätze, dokumentiert aber keine prüfbare Rechenkette; in diesem Fall gehört die Kennzahl in ein formelbasiertes Modell mit sichtbaren Zwischenschritten. ⟨4⟩

Rohleders Erwartung: Er will ein begründetes Urteil und keine Werbebroschüre; die volle Punktzahl gibt es erst, wenn die Grenzen des Werkzeugs mit derselben Ernsthaftigkeit benannt werden wie seine Stärken. Jede genannte Situation braucht ein konkretes Beispiel, sonst bleibt es bei der Hälfte der Punkte.

a) 2 P. – Ausgabe und Bestandteile

Die Formel gibt den in der Pivot-Tabelle bereits aggregierten Umsatzwert für die Kombination Region „Nord" und Produkt „Kaffeemühle" als eine einzelne Zahl in Zelle H5 aus; sie liest also einen fertigen Kreuzungspunkt der Pivot-Tabelle in eine normale Zelle außerhalb davon, ohne dass die Pivot-Struktur selbst angezeigt werden muss. ⟨1⟩

Bestandteil für Bestandteil bedeutet das: "Umsatz" benennt das auszulesende Datenfeld genau so, wie es in der Pivot-Tabelle heißt (bei Standardbeschriftung gegebenenfalls „Summe von Umsatz"), $A$3 ist eine beliebige Zelle innerhalb der Ziel-Pivot und dient nur dazu, diese eindeutig zu identifizieren, weshalb sie absolut gesetzt ist, und danach folgen Paare aus Feldname und Element, die den gesuchten Wert eingrenzen, jeweils durch das Semikolon als Argumenttrennzeichen getrennt. ⟨2⟩

b) 6 P. – Drei verschiedene Nachteile

Nachteil 1: Abhängigkeit von der Pivot-Struktur und damit Bruchgefahr. Die Funktion spricht Feld- und Elementnamen als Text an; wird das Feld „Region" umbenannt, aus der Pivot entfernt oder das Element „Nord" durch einen Filter ausgeblendet, findet die Formel ihr Ziel nicht mehr und liefert #BEZUG!, obwohl die zugrunde liegenden Daten unverändert vorhanden sind. ⟨1⟩ Wichtig ist dabei die Abgrenzung zu einem verbreiteten Irrtum: Die Funktion ist nicht statisch und liefert keine veralteten Werte, denn sie aktualisiert sich bei jeder Neuberechnung der Pivot-Tabelle mit; der eigentliche Nachteil ist ausschließlich ihre Zerbrechlichkeit gegenüber Strukturänderungen. ⟨2⟩

Nachteil 2: Sperrige, schwer lesbare Syntax und fehlende Kopierbarkeit. Schon bei zwei Kriterien ist die Formel doppelt so lang wie ein einfacher Zellbezug, und mit jedem weiteren Feld wächst sie um ein weiteres Namenspaar, was ein Dashboard mit vierzig Kennzahlen praktisch unwartbar macht. ⟨3⟩ Da die Feldnamen als feste Textkonstanten in der Formel stehen, lässt sie sich zudem nicht wie ein relativer Bezug einfach nach unten ziehen, denn beim Kopieren passt sich nichts an; wer sie dennoch in einer Matrix einsetzen will, muss die Textkonstanten durch Zellbezüge auf Beschriftungszellen ersetzen, was die Lesbarkeit weiter verschlechtert. ⟨4⟩

Nachteil 3: Eingeschränkte Flexibilität, weil nur Aggregiertes verfügbar ist. Abrufbar ist ausschließlich das, was die Pivot-Tabelle bereits berechnet und anzeigt; braucht der Bericht eine andere Aggregation, eine feinere Gliederung oder einen Einzelwert, muss zuerst die Pivot-Tabelle selbst umgebaut werden, was wiederum die bestehenden Formeln gefährdet. ⟨5⟩ Ein direkter Zugriff auf die Rohdaten ist nicht möglich, und der aktuelle Filterzustand der Pivot-Tabelle wirkt immer mit, sodass ein unbeabsichtigt gesetzter Datenschnitt die Werte des ganzen Berichtsblatts still verändert. ⟨6⟩

c) 2 P. – Entstehung und Abschaltung

Solche Formeln schreibt in aller Regel niemand von Hand: Excel erzeugt sie automatisch, sobald man beim Aufbau einer Formel mit der Maus auf eine Zelle innerhalb einer Pivot-Tabelle klickt, und ersetzt den erwarteten Zellbezug durch den vollständigen PIVOTDATENZUORDNEN-Aufruf. ⟨1⟩

Abstellen lässt sich das über „PivotTable-Analyse", dort im Dropdown neben der Schaltfläche „PivotTable" bei den Optionen den Eintrag „GetPivotData-Funktionen generieren" abwählen; danach entsteht beim Anklicken ein gewöhnlicher Zellbezug, was allerdings eine bewusste Entscheidung sein sollte, weil dieser Bezug positionsabhängig ist und beim Umsortieren der Pivot-Tabelle auf den falschen Wert zeigt. ⟨2⟩

Rohleders Erwartung: Er prüft hier zwei Dinge zugleich, das Lesen einer konkreten Formel und die Fähigkeit zur nüchternen Abwägung. Wer bei den Nachteilen den verbreiteten Irrtum „die Werte sind statisch" ungeprüft übernimmt, verliert Punkte; richtig ist die Bruchgefahr bei Strukturänderungen.

📜 Original-Klausuraufgaben aus den Altmeisterklausuren — gelöst

Rohleders eigener Wortlaut aus neun Klausuren 2021–2025. Wortgleiche Wiederholer sind in der Box markiert.

Vorüberlegung. Der Jahresumsatz ist eine abgeleitete Größe: Er existiert nicht als eigenes Feld, lässt sich aber vollständig aus den vier vorhandenen Quartalsumsätzen berechnen. Für jede Zeile gilt: Jahresumsatz = Q1-Umsatz + Q2-Umsatz + Q3-Umsatz + Q4-Umsatz. Der Jahresumsatz ist ausdrücklich die Summe der vier Quartale und nicht „ein Quartal mal vier“; letzteres wäre nur richtig, wenn alle vier Quartale exakt gleich hoch wären. Es gibt zwei grundsätzlich verschiedene Ansatzpunkte, nämlich die Berechnung innerhalb der PivotTable oder die Berechnung vorher in den Quelldaten.

a) 6 P. – Zwei unterschiedliche Wege

Weg 1 – Berechnetes Feld innerhalb der PivotTable

⟨1⟩ Man erzeugt das fehlende Feld direkt in der PivotTable, ohne die Quelldaten anzufassen. Dazu wird die PivotTable zunächst aufgebaut, indem Produkt und Region in den Zeilenbereich gezogen werden.

⟨2⟩ Anschließend setzt man den Cursor in die PivotTable und ruft im Menüband den Reiter PivotTable-Analyse, Gruppe Berechnungen, Schaltfläche Felder, Elemente und Gruppen, Eintrag Berechnetes Feld … auf. Im Dialog trägt man bei Name Jahresumsatz ein und bei Formel die Addition der Quartalsfelder:

= 'Q1-Umsatz' + 'Q2-Umsatz' + 'Q3-Umsatz' + 'Q4-Umsatz'

⟨3⟩ Nach der Bestätigung erscheint das neue Feld Jahresumsatz automatisch in der Feldliste und kann in den Wertebereich gezogen werden. Bestandteil für Bestandteil: = leitet die Formel ein; 'Q1-Umsatz' ist ein Feldbezug auf ein vorhandenes Wertfeld, wobei die einfachen Hochkommas '…' nötig sind, weil der Feldname mit dem Bindestrich ein Sonderzeichen enthält und Excel ihn sonst als Minus-Operator lesen würde; + ist der Additionsoperator; die weiteren Feldbezüge liefern die Quartale zwei bis vier. Wichtig für das Verständnis ist, dass ein berechnetes Feld auf Ebene der Feld-Summen rechnet, also Summe(Q1) + Summe(Q2) + Summe(Q3) + Summe(Q4) bildet.

Weg 2 – Hilfsspalte in den Quelldaten

⟨4⟩ Man ergänzt das Feld bereits in der Datenquelle, sodass es in der Feldliste als ganz normales Feld auftaucht. Dazu wird rechts neben die Quartalsspalten eine neue Spalte mit der Überschrift Jahresumsatz eingefügt; die Überschrift ist Pflicht, weil die Pivot eine Spalte ohne Überschrift nicht als Feld übernimmt.

⟨5⟩ In die erste Datenzeile schreibt man die Summenformel und kopiert sie per Ausfüllkästchen über alle Datenzeilen nach unten:

=SUMME(C2:F2)

Bestandteil für Bestandteil: = startet die Formel; SUMME ist die Tabellenblattfunktion, die alle Werte des übergebenen Bereichs addiert; ( und ) umschließen das Argument; C2:F2 ist der zu summierende Zellbereich, also die vier Quartalsspalten der Zeile 2; : ist der Bereichsoperator und bedeutet „von … bis“.

⟨6⟩ Anschließend setzt man die PivotTable auf den erweiterten Datenbereich auf beziehungsweise passt bei einer bestehenden Pivot die Datenquelle über PivotTable-Analyse ▸ Datenquelle ändern an und aktualisiert die Pivot über die rechte Maustaste ▸ Aktualisieren. Danach steht Jahresumsatz in der Feldliste und wird in den Wertebereich gezogen.

b) 4 P. – Je ein spezifischer Vor- und Nachteil pro Weg

⟨1⟩ Weg 1, Vorteil: Die Quelldaten bleiben unverändert, sodass die Ursprungsliste nicht angefasst wird und keine versehentliche Änderung der Rohdaten riskiert wird.

⟨2⟩ Weg 1, Nachteil: Ein berechnetes Feld rechnet nur auf Ebene der Feld-Summen und nicht zeilenweise; für die reine Addition ist das hier korrekt, bei nichtlinearen Größen wie Mittelwerten, Anteilen oder Quotienten liefert es dagegen falsche Ergebnisse.

⟨3⟩ Weg 2, Vorteil: Es steht die volle, zeilenweise Excel-Funktionalität zur Verfügung, und das Ergebnis ist direkt in den Rohdaten sicht- und nachprüfbar.

⟨4⟩ Weg 2, Nachteil: Die Quelldaten werden verändert und der Pivot-Datenbereich muss angepasst und aktualisiert werden; kommen neue Zeilen hinzu, muss die Formel mitgezogen werden, sonst fehlen Werte in der Auswertung.

Fazit. Beide Wege führen zum selben Ergebnis. Weg 1 schont die Quelldaten, ist aber funktional begrenzt; Weg 2 ist flexibel und prüfbar, verändert aber die Datenbasis.

Rohleders Erwartung: Er will zwei wirklich unterschiedliche Ansatzpunkte – einen innerhalb, einen außerhalb der PivotTable – mit konkretem Bedienweg und vollständig erklärter Formel. In Teil b) zählt Genauigkeit statt Menge: genau ein Vorteil und genau ein Nachteil je Weg.

a) 4 P. – Zweck und Notwendigkeit

PIVOTDATENZUORDNEN() (englisch GETPIVOTDATA) ruft einen einzelnen, bereits in einer Pivot-Tabelle berechneten Ergebniswert ab und stellt ihn an beliebiger anderer Stelle der Arbeitsmappe zur Verfügung. Der Wert wird dabei nicht über die Zelladresse wie B7 angesprochen, sondern über seine logischen Koordinaten, also die Kombination aus Wertfeld, Feldern und Elementen. ⟨1⟩

Gebraucht wird sie wegen der Bezugsstabilität gegenüber Layout-Änderungen: Ein starrer Bezug =B7 zeigt nach dem Umsortieren von Zeilen oder Spalten, nach dem Aufklappen einer Ebene oder nach dem Hinzukommen eines neuen Elements plötzlich auf einen anderen Wert – und zwar ohne Fehlermeldung. PIVOTDATENZUORDNEN() folgt dagegen der Logik „Umsatz der Region Nord im Quartal Q1" und liefert auch nach einer Umstrukturierung noch denselben richtigen Wert. ⟨2⟩ Der Wert ist zudem live: Wird die Pivot aktualisiert, aktualisiert sich das Ergebnis der Funktion mit, es handelt sich also nicht um eine eingefrorene Zahl. ⟨3⟩

Der typische Einsatz sind feste Managementberichte und Dashboards, in denen einzelne Kennzahlen aus einer sich verändernden Pivot an definierten Positionen erscheinen sollen. Excel erzeugt die Formel automatisch, sobald man beim Schreiben einer Formel in eine Pivot-Ergebniszelle klickt:

=PIVOTDATENZUORDNEN("Umsatz";$A$3;"Region";"Nord";"Quartal";"Q1")

Jeder Bestandteil einzeln: = startet die Formel; PIVOTDATENZUORDNEN( ist Funktionsname plus öffnende Klammer; "Umsatz" ist das Datenfeld, also der Name des Wertfelds, als Text in doppelten Anführungszeichen; ; ist der Argumenttrenner der deutschen Excel-Version; $A$3 ist das Argument PivotTable, ein Bezug auf irgendeine Zelle innerhalb der Ziel-Pivot, wobei die beiden $ Spalte und Zeile fixieren, damit der Bezug beim Kopieren nicht verrutscht; "Region" ist Feld1 und "Nord" das zugehörige Element1; "Quartal" und "Q1" bilden das zweite Feld-Element-Paar und grenzen die gesuchte Zelle eindeutig ein; ) beendet die Argumentliste. ⟨4⟩

b) 6 P. – Drei Nachteile (je 2 P.)

Nachteil 1: Feld- und Elementnamen stehen als fest verdrahtete Textkonstanten in der Formel. Bezeichnungen wie "Region", "Nord" oder "Q1" sind reine Zeichenketten. Wird in der Datenquelle ein Feld oder Element umbenannt – aus „Nord" wird „Norden", aus „Umsatz" wird „Nettoumsatz" –, findet die Funktion die Koordinate nicht mehr. ⟨1⟩ Sie liefert dann #BEZUG!, und zwar an jeder einzelnen Stelle des Berichts, an der diese Bezeichnung vorkommt; die Formel ist damit ausgesprochen wartungsanfällig gegenüber Änderungen an der Quelle, die fachlich völlig harmlos aussehen. ⟨2⟩

Nachteil 2: Es lassen sich nur aktuell sichtbare Pivot-Werte lesen. Die Funktion kann ausschließlich Werte abrufen, die in der Pivot gerade als Ergebniszelle angezeigt werden. ⟨3⟩ Wird das gesuchte Element weggefiltert, über einen Datenschnitt abgewählt, zugeklappt oder ist es im aktuellen Layout gar nicht enthalten, kommt wiederum #BEZUG! zurück. Der Bericht hängt damit an der momentanen Filter- und Layout-Einstellung einer Pivot, die jeder Nutzer jederzeit verändern darf – ein Dashboard, das bricht, sobald jemand einen Datenschnitt anklickt, ist im Alltag schwer zu verantworten. ⟨4⟩

Nachteil 3: Die Formel ist nicht relativ kopierbar, und ihre Auto-Erzeugung stört das gewohnte Arbeiten. Weil die Koordinaten Textkonstanten und keine relativen Zellbezüge sind, erzeugt das Herunterziehen mit dem Ausfüllkästchen nicht die logische Nachbarzelle: Aus "Q1" wird beim Ausfüllen nicht "Q2", sondern es bleibt "Q1". Wer eine Quartalsreihe bauen will, muss die Element-Argumente erst mühsam auf Zellbezüge umstellen. ⟨5⟩ Hinzu kommt, dass Excel beim Anklicken einer Pivot-Zelle während der Formeleingabe automatisch eine PIVOTDATENZUORDNEN()-Formel schreibt, statt den erwarteten einfachen Bezug =B7 zu setzen; das behindert das gewohnte Kopieren von Bezügen so sehr, dass die Automatik in der Praxis häufig erst abgeschaltet werden muss (PivotTable-Analyse → Optionen → „GetPivotData generieren"). ⟨6⟩

Rohleders Erwartung: Bei a) will er den Gegensatz „logische Koordinaten statt Zelladresse" und den daraus folgenden Nutzen hören, nicht nur eine Syntaxbeschreibung. Bei b) müssen drei verschiedene Fehlerbilder kommen; dreimal #BEZUG! mit derselben Begründung zählt als ein Nachteil.

Urteil

Kein Ballast. Pivottabellen sind weiterhin unentbehrlich, weil das Alter des Konzepts nichts über seine Tauglichkeit aussagt: Die Aufgabe, große Datenmengen nach frei wählbaren Merkmalen zu gruppieren und zu verdichten, hat sich seit 1993 nicht verändert, und das Feature ist seither laufend erweitert worden. ⟨1⟩

Begründung

1. Geschwindigkeit ohne Formelaufwand. Eine Pivottabelle verdichtet zehntausende Zeilen in wenigen Klicks zu einer Auswertung nach Kunde, Region, Monat oder Produkt, ohne dass eine einzige Formel geschrieben werden muss; derselbe Aufbau mit SUMMEWENNS() erfordert für jede Kombination eine eigene Formel und ist bei Strukturänderungen komplett neu zu bauen. ⟨2⟩

2. Freie Umgruppierung als eigentliche Stärke. Zeilen-, Spalten- und Wertfelder lassen sich per Ziehen vertauschen, Aggregationen zwischen Summe, Mittelwert, Anzahl und Anteil umschalten und Ebenen auf- und zuklappen; damit ist die Pivottabelle das einzige Bordmittel, das explorative Fragen in Sekunden beantwortet, statt jede Frage vorher zu kennen. ⟨3⟩

3. Niedrige Einstiegshürde und hohe Verbreitung. Die Bedienung per Drag-and-Drop ist ohne Programmierkenntnisse erlernbar, und weil praktisch jede Fachabteilung sie beherrscht, sind Pivot-Auswertungen ohne Erklärung austauschbar; das ist ein organisatorischer Vorteil, den ein technisch überlegenes, aber unbekanntes Werkzeug nicht hat. ⟨4⟩

4. Anschluss an die moderne Excel-Architektur. Pivottabellen sind gerade nicht stehengeblieben: Datenschnitte und Zeitachsen kamen hinzu, sie lassen sich auf das Datenmodell und auf Power-Query-Abfragen aufsetzen, mit Measures in DAX rechnen und direkt an Datenbanken anbinden. ⟨5⟩ Damit sind sie heute die Auswertungsoberfläche über sauber angebundenen Quellen und nicht mehr nur ein Werkzeug für Zellbereiche im Blatt.

5. Grenzen, die zum ehrlichen Urteil gehören. Bei sehr großen Datenmengen, komplexer Datenmodellierung über mehrere Tabellen und bei echter Mehrbenutzer-Zusammenarbeit in Echtzeit sind spezialisierte BI-Werkzeuge wie Power BI überlegen, und ein per PIVOTDATENZUORDNEN() angebundener Bericht bricht, sobald jemand das Pivot-Layout ändert. ⟨6⟩

Fazit

Die Frage ist keine Entweder-oder-Frage: Pivottabellen bleiben das Standardwerkzeug für die tägliche Zahlenarbeit und den schnellen Blick in die Daten, während BI-Werkzeuge das dauerhafte, unternehmensweite Berichtswesen übernehmen. Historischer Ballast wären sie nur, wenn es einen gleichwertigen Ersatz mit derselben Verfügbarkeit und derselben niedrigen Einstiegshürde gäbe, und den gibt es nicht. ⟨7⟩

Rohleders Erwartung: Er will ein eindeutiges Urteil mit Begründung, und er honoriert ausdrücklich die Ausgewogenheit: Wer nur lobt, ohne die Grenzen gegenüber echten BI-Werkzeugen zu benennen, bekommt nicht die volle Punktzahl. Das Alter des Features ist in der Aufgabenstellung als Köder gesetzt und sollte als Argument ausdrücklich zurückgewiesen werden.

🎙️ Aus der Vorlesung gebaut — gelöst

Aufgaben zu Themen, die er in den Aufzeichnungen betont, angedeutet oder live vorgeführt hat. Grün umrandet = kein Original.

a) 3 P. – Maslows Law of the Instrument und drei Alternativen

Abraham Maslow formulierte 1966, wer als Werkzeug nur einen Hammer habe, sehe in jedem Problem einen Nagel. Übertragen heißt das: Die Pivot-Tabelle gilt in der Praxis als Krönung der Excel-Schöpfung, und deshalb wird sie auch dort eingesetzt, wo sie gar nicht gebraucht wird. ⟨1⟩

Der Preis dafür ist vermeidbare Komplexität: eine zweite Datenhaltung im Cache, ein Objekt mit eigener Konfiguration, das nicht mitrechnet, und ein Layout, das sich bei jeder Aktualisierung ändern kann, für eine Frage, die eine Formel in einer Zelle beantwortet hätte. Rohleders Formulierung dafür lautet, man schieße mit Kanonen auf Spatzen. ⟨2⟩

Drei Alternativen für eine Kreuztabellierung sind PIVOTMIT() für den zweidimensionalen Aufriss, GRUPPIERENNACH() für den eindimensionalen und SUMMEWENNS() für die punktgenaue Abfrage nach beliebig vielen Kriterien; ebenso funktioniert SUMME() über einen FILTER(). Diese Techniken leisten nach Rohleders Einschätzung rund 90 Prozent dessen, wofür Pivot-Tabellen eingesetzt werden. ⟨3⟩

b) 4 P. – Vier Fehlerquellen

Fehlerquelle 1: Doppelte Datenhaltung im Cache. Die Pivot-Tabelle speichert eine Kopie aller verdichteten Daten und verdoppelt damit die Größe der Arbeitsmappe. Die Kopie ist zugleich ein Vertraulichkeitsrisiko bei der Weitergabe. ⟨1⟩

Fehlerquelle 2: Keine automatische Aktualisierung. Änderungen an den Quelldaten schlagen nicht durch. Wer die Zahlen abliest, ohne vorher zu aktualisieren, liest einen historischen Stand und merkt es nicht, weil nichts darauf hinweist. ⟨2⟩

Fehlerquelle 3: Der Quelldatenbereich ist der Pivot-Tabelle nicht anzusehen. Er steht in der Konfiguration und nicht im Blatt; ist die Quelle ein starrer Zellbereich statt einer formatierten Tabelle, wachsen neue Zeilen gar nicht erst mit hinein. ⟨3⟩

Fehlerquelle 4: Unbemerkt gesetzte Filter. Über Feld-Dropdowns, Datenschnitte oder eine Zeitachse kann ein Teil der Daten ausgeblendet sein, ohne dass die angezeigten Summen als Teilmenge erkennbar wären; ergänzend ändert sich das Layout bei jeder Aktualisierung, weshalb ein Zellbezug auf eine Pivot-Zelle nach einer Aktualisierung auf etwas anderes zeigen kann. ⟨4⟩

c) 3 P. – Aktualisierungsverhalten, Ursache, Optionen

Die Pivot-Engine stammt aus dem Jahr 1993. Damals hätte die Neuberechnung sämtlicher Pivot-Tabellen einer Arbeitsmappe bei jeder Änderung der Quelldaten jeweils mehrere Minuten Pause bedeutet, weshalb man die Berechnung bewusst nur auf Knopfdruck auslöste. Dieses Verhalten ist bis heute unverändert, obwohl es technisch längst nicht mehr nötig wäre; in der Community wird eine Nachrüstung der automatischen Aktualisierung erwartet. ⟨1⟩

Option 1: Spaltenbreiten schützen. In den Pivot-Table-Optionen wird der Haken bei „Spaltenbreiten bei Aktualisierung automatisch anpassen" entfernt, weil sonst jede Aktualisierung die von Hand gesetzten Breiten zerstört. ⟨2⟩

Option 2: Aktualisieren beim Öffnen. Die Option „Beim Öffnen der Datei aktualisieren" wird gesetzt, damit die Analyse wenigstens einmal je Sitzung auf den aktuellen Stand kommt; sie ist im Standard aktiv, wird aber häufig unbewusst deaktiviert. Zusätzlich lohnt es sich, gleich zu Beginn über Entwurf die Teil- und Gesamtergebnisse sowie das Berichtslayout festzulegen. ⟨3⟩

Rohleders Erwartung: Er bekennt sich ausdrücklich dazu, kein Fan der Pivot-Analyse zu sein, hebt aber das explorative Arbeiten hervor, für das es nichts Besseres gebe. Eine Antwort, die Pivot pauschal verurteilt, trifft seinen Punkt genauso wenig wie eine, die sie feiert. Er will die Abwägung und die vier Fehlerquellen als konkrete Fehlerbilder, nicht als Schlagworte.

4. Pivottabellen (20 P.)

✅ von 2 Profs geprüft

📝 Lösung

Lösung

a) Die vier Ablagebereiche einer Pivottabelle (4 P.)

Beim Erstellen einer Pivottabelle öffnet sich rechts die PivotTable-Feldliste. Jedes Datenfeld (= jede Spaltenüberschrift der Rohdaten) kann in einen von vier Bereichen gezogen werden:

Merksatz: Zeilen und Spalten spannen das Gitter auf, Werte fuellen die Zellen, der Filter schaltet der gesamten Auswertung eine Bedingung vor.

b) Schrittweise Erstellung: Umsatzsumme je Region x Produktkategorie (4 P.)

1. Eine beliebige Zelle innerhalb des Datenbereichs Tabelle1!A1:E481 anklicken (Excel erkennt den zusammenhängenden Bereich dann automatisch).
2. Menüband Einfügen > PivotTable.
3. Im Dialog den vorgeschlagenen Bereich prüfen (Tabelle1!$A$1:$E$481) und als Ziel Neues Arbeitsblatt wählen, dann OK.
4. In der Feldliste das Feld Region in den Bereich ZEILEN ziehen.
5. Das Feld Produktkategorie in den Bereich SPALTEN ziehen.
6. Das Feld Umsatz in den Bereich WERTE ziehen.
7. Kontrolle: Steht im Wertebereich versehentlich "Anzahl von Umsatz" (passiert, wenn die Spalte Leerzellen oder Text enthält), auf das Wertfeld klicken > Wertfeldeinstellungen > Summe wählen.

Ergebnis ist eine Kreuztabelle mit vier Zeilen (Regionen), drei Spalten (Kategorien), automatischen Zeilen-/Spaltensummen und dem Gesamtergebnis unten rechts.

c) Prozentanteil der Zelle "Süd / Möbel" (3 P.)

Rechenweg (Anteil = Teilwert geteilt durch Gesamtwert):

$$\text{Anteil} = \frac{12.480\ \text{EUR}}{84.320\ \text{EUR}} = 0{,}148007\ldots = \mathbf{14{,}80\ \%}$$

(auf zwei Nachkommastellen kaufmännisch gerundet).

Ohne eigene Formel erhält man diese Ansicht so: Rechtsklick auf einen Wert im Wertebereich > Werte anzeigen als > % des Gesamtergebnisses. Excel zeigt dann jede Zelle als Prozentanteil am Gesamtergebnis an, hier 14,80 %. Der absolute EUR-Betrag lässt sich parallel anzeigen, indem man das Feld Umsatz ein zweites Mal in den Werte-Bereich zieht.

d) Zellbezug ausserhalb der Pivottabelle: PIVOTDATENZUORDNEN vs. SUMMEWENNS (5 P.)

Empfohlene Formel (greift direkt auf die Pivottabelle zu):

=PIVOTDATENZUORDNEN("Umsatz";$A$3;"Region";"Süd";"Produktkategorie";"Möbel")

Bestandteil für Bestandteil (QUBA-Regel):

Ergebnis: exakt der Wert der Kreuzung Süd/Möbel (12.480). Vorteil: Der Bezug bleibt korrekt, auch wenn die Pivottabelle später umsortiert wird, weil nach Feldnamen und nicht nach Zellposition gesucht wird.

Äquivalente Lösung ohne Pivottabelle (direkt auf den Rohdaten):

=SUMMEWENNS(Tabelle1!E:E;Tabelle1!B:B;"Süd";Tabelle1!C:C;"Möbel")

Bestandteil für Bestandteil:

Gegenüberstellung / Kernaussage: SUMMEWENNS liefert eine Kombination und muss für jede weitere Kombination (Nord/Technik, Ost/Deko, ...) neu geschrieben oder heruntergezogen werden - bei 4 Regionen x 3 Kategorien sind das 12 Formeln plus manuelle Summenzeilen. Die Pivottabelle erzeugt genau dieselbe Kreuztabelle per Drag & Drop in Sekunden und aktualisiert sie auf Knopfdruck. Das illustriert den eigentlichen Nutzen der Pivottabelle.

e) Drei sachlich getrennte Vorteile (3 P.)

1. Tempo ohne Formelaufwand: Grosse Datenmengen werden per Drag & Drop zusammengefasst; man braucht weder SUMMEWENNS noch Formelketten, sondern zieht Felder in Bereiche.
2. Flexibilität: Das Berichtslayout lässt sich in Sekunden umbauen - Zeilen und Spalten tauschen, weitere Dimensionen ergänzen, Ansicht von Summe auf Mittelwert oder Prozent umstellen -, ohne die Rohdaten anzufassen.
3. Integration mit anderen Excel-Werkzeugen: Pivottabellen lassen sich mit PivotCharts, Datenschnitten (Slicern), Zeitachsen, bedingter Formatierung und Power Query verbinden und so zu einem interaktiven Dashboard ausbauen.

(Genau drei Punkte, jeweils eine eigene Nutzendimension - nicht dieselbe Aussage doppelt.)

f) Urteil: Ballast oder unentbehrlich? (1 P.)

Unentbehrlich, kein historischer Ballast. Das Feature ist zwar über 30 Jahre alt, seine Funktion und Bedienoberfläche werden aber laufend modernisiert (Datenschnitte, Datenmodell/Power Pivot, Empfohlene PivotTables). Für die schnelle, formelfreie Zusammenfassung grosser Datenmengen gibt es in Excel kein gleichwertig einfaches Werkzeug; Pivottabellen bleiben damit Standard der täglichen Zahlenarbeit. Das Alter eines Werkzeugs sagt nichts über seine Tauglichkeit - entscheidend ist, dass es die Aufgabe weiterhin am besten löst, und das tut es.

Qualität von Arbeitsmappen

5. f) Urteil: Ballast oder unentbehrlich? (1 P.)

✅ von 2 Profs geprüft

a) (6 P.) Erläutern Sie, was "Qualität" in Bezug auf eine Arbeitsmappe bedeutet, und nennen und erkläürn Sie sechs Qualitätsdimensionen (je 1 P.).

b) (8 P.) Erstellen Sie eine praxistaugliche Prüf-Checkliste mit acht konkreten, in Excel überprüfbaren Kriterien (je 1 P.). Belegen Sie mindestens zwei Kriterien mit einer geeigneten Excel-Formel.

📝 Lösung

a) Definition und sechs Qualitätsdimensionen (6 P.)

Definition. Die Qualität einer Arbeitsmappe ist der Grad, in dem sie die fachlichen Anforderungen ihrer Nutzer erfuellt — und zwar korrekt, nachvollziehbar und dauerhaft zuverlässig. Entscheidend ist nicht, ob die Mappe heute zufällig das richtige Ergebnis anzeigt, sondern ob sie auch dann noch richtig, prüfbar und pflegbar bleibt, wenn sich Daten ändern, die Datenmenge wächst oder ein anderer Bearbeiter sie übernimmt (Grundsatz "fitness for purpose"). Optik ist dabei nur ein kleiner Teilaspekt.

Sechs unterscheidbare Qualitätsdimensionen:

Reserve-Dimension (falls eine Nennung nicht zählt): Performance/Skalierbarkeit — die Mappe bleibt auch bei grossen Datenmengen schnell und funktioniert bei wachsendem Umfang weiter.

b) Prüf-Checkliste: acht in Excel überprüfbare Kriterien (8 P.)

Jedes Kriterium ist als Ja/Nein-Frage formuliert und lässt sich direkt an der Mappe prüfen.

1. Trennung von Eingabe, Berechnung und Ausgabe? Sind reine Eingabezellen optisch markiert (z. B. eigene Fuellfarbe) und von Formel-/Ergebniszellen getrennt? Schnelltest: mit Strg + # alle Formeln einblenden — Eingaben dürfen keine Formeln, Formeln keine fest eingetippten Werte sein.

2. Keine Hartcodierung fachlicher Konstanten in Formeln? Stehen Werte wie Steuersatz oder Kurs als Zahl mitten in einer Formel (=A2*1,19) statt in einer eigenen, benannten Parameterzelle? Solche versteckten Konstanten sind der häufigste Wartungsfehler.

3. Werden Fehler kontrolliert abgefangen? Sind Nachschlage- und Divisionsformeln gegen Fehlerwerte abgesichert? Beispielformel:

=WENNFEHLER(SVERWEIS(B2;Artikel!$A$2:$D$500;4;FALSCH);"Artikel fehlt")

Bestandteil-Erklärung (QUBA-Regel — jeder Teil einzeln):

• = — leitet jede Formel ein; sagt Excel "ab hier rechnen, nicht Text anzeigen".
• WENNFEHLER( … ; … ) — Funktion mit genau 2 Argumenten: sie gibt das erste Argument zurück, ausser es ergibt einen Fehler; dann das zweite.
• 1. Argument SVERWEIS(B2;Artikel!$A$2:$D$500;4;FALSCH) — der eigentlich gewünschte Wert.
• ; (innerhalb WENNFEHLER) — Argumenttrennzeichen; trennt "Normalwert" vom "Ausweichwert".
• 2. Argument "Artikel fehlt" — der Ausweichwert bei Fehler.
• " … " — Textbegrenzer: alles dazwischen ist fester Text, keine Formel.
• Innerer SVERWEIS(...) = Senkrecht-Verweis (Nachschlagefunktion), 4 Argumente:
  • B2 — 1. Arg. Suchkriterium: wonach gesucht wird; relativer Bezug (ohne $) — passt sich beim Kopieren nach unten automatisch an (B3, B4 …).
  • ; — Argumenttrenner.
  • Artikel!$A$2:$D$500 — 2. Arg. Matrix (Suchtabelle). Artikel = Name des Tabellenblatts; ! = Blatttrennzeichen (trennt Blattname vom Zellbezug); $A$2 = Startzelle, die $-Zeichen frieren Spalte A und Zeile 2 ein (absoluter Bezug, rutscht beim Kopieren nicht weg); : = Bereichsoperator ("von … bis"); $D$500 = absolutes Bereichsende.
  • ; — Argumenttrenner.
  • 4 — 3. Arg. Spaltenindex: aus der 4. Spalte der Matrix wird der Ergebniswert geholt.
  • ; — Argumenttrenner.
  • FALSCH — 4. Arg. Bereich_Verweis: FALSCH erzwingt exakte Übereinstimmung. (WAHR/weggelassen suchte nur ungefähr und liefert bei unsortierten Daten still falsche Treffer — ein Qualitätsrisiko.)
  • ) — schliesst den SVERWEIS; das äussere ) schliesst WENNFEHLER.

4. Sind Eingaben validiert? Ist auf Eingabezellen eine Datenüberprüfung aktiv (Menü Daten > Datenüberprüfung) — z. B. Dropdown-Liste, Zahl in Grenzen, Datumsbereich? Das verhindert Fehleingaben an der Quelle (stärkt Robustheit + Benutzerfreundlichkeit).

5. Sind Formeln über zusammenhängende Bereiche konsistent? Enthält eine Spalte, die durchgängig dieselbe Formel haben sollte, an einzelnen Stellen abweichende Formeln oder feste Werte? Excel markiert das mit der Fehlerprüfung ("abweichende Formel", grünes Dreieck oben links in der Zelle) — diese Warnungen durchgehen.

6. Sprechende Bezüge statt kryptischem Zellsalat? Werden benannte Bereiche oder strukturierte Tabellenbezüge verwendet statt =C2*D2*'Blatt3'!$F$8? Beispielformel mit Tabellenbezug:

=SUMMEWENN(tblUmsatz[Region];"Süd";tblUmsatz[Betrag])

Bestandteil-Erklärung:

• = — leitet die Formel ein.
• SUMMEWENN( … ; … ; … ) — Funktion "summiere nur, wenn eine Bedingung zutrifft"; 3 Argumente.
• 1. Arg. tblUmsatz[Region] — Suchbereich (Spalte, in der die Bedingung geprüft wird). tblUmsatz = Name der als Tabelle formatierten Datenliste; [Region] = Spaltenbezug, die eckigen Klammern [ ] schliessen einen Spaltennamen innerhalb der Tabelle ein.
• ; — Argumenttrenner.
• 2. Arg. "Süd" — Kriterium; " " als Textbegrenzer: nur Zeilen mit Region = Süd zählen.
• ; — Argumenttrenner.
• 3. Arg. tblUmsatz[Betrag] — Summenbereich: aus dieser Spalte werden die passenden Werte addiert.
• ) — schliesst die Funktion.

Ergänzend die weiteren Strukturbezug-Zeichen (zur Vollständigkeit): [@Betrag] — das @ bedeutet "diese Zeile" (Wert der Spalte Betrag in der aktuellen Zeile); tblUmsatz[#Daten], [#Kopfzeilen], [#Alle] — das # kennzeichnet einen Bereichsteil der Tabelle (nur Datenzeilen / nur Überschriften / alles). Vorteil für die Qualität: die Formel bleibt lesbar und wächst automatisch mit, wenn Zeilen hinzukommen (Wartbarkeit + Skalierbarkeit).

7. Keine volatilen oder unnötig langsamen Konstrukte? Werden flüchtige ("volatile") Funktionen wie JETZT(), HEUTE(), INDIREKT(), BEREICH.VERSCHIEBEN() oder ZUFALLSZAHL() sparsam eingesetzt? Werden statt punktgenauer Bereiche ganze Spalten (A:A) in SUMMEWENN/SVERWEIS referenziert? Beides bremst grosse Mappen unnötig (Dimension Performance). Erkennbar an spürbarer Verzoegerung bei jeder Eingabe.

8. Dokumentation und Schutz vorhanden? Gibt es ein Deckblatt/eine Legende (Zweck, Annahmen, Farbcode der Eingaben), aussagekräftig benannte Tabellenblätter (nicht "Tabelle1", "Tabelle2") und einen Blattschutz auf den Formelbereichen (Menü Überprüfen > Blatt schützen), sodass Berechnungen nicht versehentlich überschrieben werden? Deckt Nachvollziehbarkeit, Wartbarkeit und Sicherheit ab.

Erwartungshorizont / Punktelogik

• a) 6 klar unterscheidbare Dimensionen mit je eigener Definition (keine inhaltlichen Doppelungen) = 6 P. Der einleitende Definitionssatz ("fitness for purpose") verankert die Nennungen und verhindert reine Aufzählung.
• b) 8 konkrete, prüfbare Ja/Nein-Kriterien = 8 P.; die geforderten Formeln (WENNFEHLER+SVERWEIS, SUMMEWENN mit Tabellenbezug) sind vollständig zerlegt. Reine Schlagworte ohne "wie prüfe ich das in Excel" würden Punkte kosten.

⟨+1⟩ Ein neuntes Prüfkriterium als Reserve: Wird jedes wesentliche Ergebnis gegen eine vorab formulierte Ergebniserwartung plausibilisiert (Überschlag, Vorjahreswert, Sachlogik)? Ohne Maßstab wird jede Zahl geglaubt — erst die Erwartung macht das Modellergebnis prüfbar.

Prognosen mit Excel

6. PIVOTDATENZUORDNEN() (10 P.)

✅ Prof-Gate: korrigiert & freigegeben

📝 Lösung

a) Zweck und Notwendigkeit von PIVOTDATENZUORDNEN() (4 P.)

Kernaussage: PIVOTDATENZUORDNEN() (englisch GETPIVOTDATA) ruft einen einzelnen, bereits in einer Pivot-Tabelle berechneten Ergebniswert ab und stellt ihn an einer beliebigen anderen Stelle der Arbeitsmappe zur Verfügung. Der Wert wird dabei nicht über die Zell-Adresse (z. B. B7), sondern über seine logischen Koordinaten — Kombination aus Wertfeld, Feldern und Elementen — angesprochen.

Warum wird sie gebraucht? Der entscheidende Vorteil ist Bezugsstabilität gegenüber Layout-Änderungen der Pivot-Tabelle:

• Ein starrer Bezug wie =B7 zeigt nach dem Umsortieren von Zeilen/Spalten oder nach Hinzufügen eines Elements plötzlich auf den falschen Wert.
• PIVOTDATENZUORDNEN() folgt der Logik ("Umsatz der Region Nord im Quartal Q1") und liefert deshalb auch nach einer Umstrukturierung der Pivot noch denselben richtigen Wert.
• Der Wert ist live/dynamisch: Wird die Pivot aktualisiert (Rechtsklick > Aktualisieren), aktualisiert sich auch das Ergebnis der Funktion — es ist also kein eingefrorener Zahlenwert, sondern folgt dem aktuellen Pivot-Cache.

Typischer Einsatz: feste Management-Berichte und Dashboards, in denen einzelne Kennzahlen aus einer sich verändernden Pivot an definierten Positionen erscheinen sollen. Excel erzeugt die Formel automatisch, sobald man beim Schreiben einer Formel in eine Pivot-Ergebniszelle klickt.

Bestandteil-für-Bestandteil (QUBA-Regel)

Allgemeine Syntax: =PIVOTDATENZUORDNEN(Datenfeld; PivotTable; [Feld1; Element1]; [Feld2; Element2]; ...)

• Die eckigen Klammern [ ] in der Syntax bedeuten: optionale Argumente. Die Feld/Element-Paare sind also nicht zwingend, grenzen aber den gesuchten Wert weiter ein. (Sie werden NICHT mit in die Formel getippt — sie sind nur Schreibweise der Hilfe.)

Beispiel-Formel: =PIVOTDATENZUORDNEN("Umsatz";$A$3;"Region";"Nord";"Quartal";"Q1")

Hinweis zu weiteren QUBA-Trennzeichen: Der Doppelpunkt : (Bereich, z. B. A1:A10), die Raute # (Überlaufbereich/Spilled Range, z. B. A1#) und das @ (impliziter Schnittmengen-Operator) kommen in dieser Formel nicht vor und werden von PIVOTDATENZUORDNEN() nicht benötigt — die Funktion arbeitet ausschliesslich mit Text-Koordinaten und dem einen absoluten Zellbezug.

b) Drei Nachteile (6 P. — je 2 P.)

Nachteil 1 — Feld- und Elementnamen sind fest verdrahtete Textkonstanten. Feld- und Elementbezeichnungen ("Region", "Nord", "Q1") stehen als feste Zeichenketten in der Formel. Wird in der Datenquelle ein Feld oder Element umbenannt (z. B. "Nord" -> "Norden"), findet die Funktion die Koordinate nicht mehr und liefert den Fehlerwert #BEZUG!. Die Formel ist damit wartungsanfällig gegenüber Änderungen an der Quelle.

Nachteil 2 — Es können nur aktuell sichtbare Pivot-Werte gelesen werden. PIVOTDATENZUORDNEN() kann nur Werte abrufen, die in der Pivot gerade als Ergebniszelle angezeigt werden. Wird das gesuchte Element weggefiltert, ausgeblendet oder ist es nicht im aktuellen Layout enthalten, gibt die Funktion #BEZUG! zurück. Der Bericht ist also von der momentanen Filter-/Layout-Einstellung der Pivot abhängig und bricht, sobald jemand die Ansicht ändert.

Nachteil 3 — Nicht relativ kopier-/ausfuellbar; die Auto-Erzeugung stört. Da die Argumente Textkonstanten und kein relativer Zellbezug sind, erzeugt ein Herunterziehen/Autofill nicht automatisch die logische Nachbarzelle ("Q1" wird beim Ausfuellen nicht zu "Q2") — man muss die Element-Argumente erst mühsam auf Zellbezüge umbauen. Zusätzlich schreibt Excel beim Anklicken einer Pivot-Zelle automatisch eine PIVOTDATENZUORDNEN()-Formel statt eines einfachen Bezugs (z. B. =B7), was das gewohnte Kopieren von Bezügen behindert und oft erst abgeschaltet werden muss.

(Weitere mögliche Nachteile — z. B. sehr lange, schlecht lesbare Formeln bei vielen Feld/Element-Paaren, oder Abhängigkeit von genau der einen referenzierten Pivot — sind für die volle Punktzahl nicht nötig; drei sauber begründete genügen.)

⟨+1⟩ Die saubere Praxis als Reservepunkt: Excel schreibt die Kriterien als harte Textkonstanten in die Formel, was Rohleders erster Modellierungsregel widerspricht — sauber ist, „Region" und „Quartal" in beschriftete Eingabezellen auszulagern und in der Formel auf diese Zellen zu verweisen; damit wird der Bericht per Dropdown steuerbar und Nachteil 3 entfällt.

Arbeiten mit Vorlagen

7. Pivotanalysen (10 P.)

✅ Prof-Gate: korrigiert & freigegeben

📝 Lösung

Vorüberlegung (Kern der Aufgabe)

Der Jahresumsatz ist eine abgeleitete Größe: Er existiert nicht als eigenes Feld, lässt sich aber vollständig aus den vier vorhandenen Quartalsumsätzen berechnen. Für jede Zeile (Produkt/Region) gilt:

Jahresumsatz = Q1-Umsatz + Q2-Umsatz + Q3-Umsatz + Q4-Umsatz

Hinweis / typische Falle: Der Jahresumsatz ist die Summe der vier Quartale, nicht „ein Quartal mal 4". Die Formel Quartalsumsatz * 4 wäre nur richtig, wenn alle vier Quartale exakt gleich hoch wären – das ist praktisch nie der Fall und wäre in der Klausur ein Fehler.

Es gibt zwei grundsätzlich verschiedene Ansatzpunkte: die Berechnung innerhalb der PivotTable (Weg 1) oder vor der PivotTable in den Quelldaten (Weg 2).

a) Zwei Wege zur Auswertung (6 P.)

Weg 1 – Berechnetes Feld innerhalb der PivotTable

Man erzeugt das fehlende Feld direkt in der PivotTable, ohne die Quelldaten anzufassen.

1. PivotTable aufbauen: Produkt und Region in den Zeilenbereich ziehen.
2. Cursor in die PivotTable setzen. Im Menüband: Reiter PivotTable-Analyse > Gruppe Berechnungen > Schaltfläche Felder, Elemente und Gruppen > Berechnetes Feld….
3. Im Dialog bei Name Jahresumsatz eintragen, bei Formel die Quartalsfelder addieren:

= 'Q1-Umsatz' + 'Q2-Umsatz' + 'Q3-Umsatz' + 'Q4-Umsatz'

4. Mit Hinzufügen / OK bestätigen. Das neue Feld Jahresumsatz erscheint automatisch in der Feldliste und kann in den Wertebereich gezogen werden.

Vollständige Erklärung jedes Formelbestandteils:

Wichtig zum Verständnis: Ein berechnetes Feld rechnet auf Ebene der Feld-Summen, d. h. Excel bildet je Zelle Summe(Q1) + Summe(Q2) + Summe(Q3) + Summe(Q4). Bei einer reinen Addition liefert das exakt das gewünschte Ergebnis.

Weg 2 – Hilfsspalte in den Quelldaten

Man ergänzt das Feld schon in der Datenquelle, sodass es in der Feldliste als „normales" Feld auftaucht.

1. In den Quelldaten rechts neben die Quartalsspalten eine neue Spalte mit der Überschrift Jahresumsatz einfügen (Überschrift ist Pflicht, sonst nimmt die Pivot die Spalte nicht als Feld auf).
2. In die erste Datenzeile (hier Zeile 2, Quartale in C:F) die Summenformel schreiben:

=SUMME(C2:F2)

3. Die Formel per Ausfüllkästchen nach unten über alle Datenzeilen kopieren.
4. Die PivotTable auf den erweiterten Datenbereich aufsetzen bzw. bei bestehender Pivot die Datenquelle anpassen (PivotTable-Analyse > Datenquelle ändern) und anschließend Aktualisieren (rechte Maustaste > Aktualisieren). Jahresumsatz steht danach in der Feldliste und wird in den Wertebereich gezogen.

Vollständige Erklärung jedes Formelbestandteils:

b) Je genau ein Vor- und ein Nachteil pro Weg (4 P.)

Weg 1 – Berechnetes Feld in der PivotTable

• Vorteil (genau einer): Die Quelldaten bleiben unverändert – man muss die Ursprungsliste nicht anfassen und riskiert keine versehentliche Änderung der Rohdaten.
• Nachteil (genau einer): Ein berechnetes Feld rechnet nur auf Ebene der Feld-Summen, nicht zeilenweise – für die reine Addition hier zwar korrekt, aber bei nichtlinearen Größen (Mittelwerte, Anteile, Quotienten) liefert es dadurch falsche Ergebnisse.

Weg 2 – Hilfsspalte in den Quelldaten

• Vorteil (genau einer): Es steht die volle, zeilenweise Excel-Funktionalität zur Verfügung, und das Ergebnis ist direkt in den Rohdaten sicht- und nachprüfbar.
• Nachteil (genau einer): Die Quelldaten werden verändert und der Pivot-Datenbereich muss angepasst und aktualisiert werden – bei neu hinzukommenden Zeilen muss die Formel mitgezogen werden, sonst fehlen Werte in der Auswertung.

Fazit (klausurtauglicher Zweizeiler)

Beide Wege führen zum selben Ergebnis (Jahresumsatz = Summe der vier Quartale). Weg 1 (berechnetes Feld) schont die Quelldaten, ist aber funktional begrenzt; Weg 2 (Hilfsspalte) ist flexibel und prüfbar, verändert aber die Datenbasis.

⟨+1⟩ Der dritte Weg als Reserve: Im Datenmodell übernimmt ein DAX-Measure die Rolle des berechneten Feldes und ist ihm deutlich überlegen — einmal definiert gilt es mappenweit und rechnet im jeweiligen Auswertungskontext; der Preis ist Power Pivot samt Lizenz- und Lernaufwand.

Tabellenblätter: Best Practice