Pivot-ähnliche Berichte mit Formeln

Eine PivotTable stellt Daten als Kreuztabelle dar: Zeilen für eine Kategorie, Spalten für eine andere und Summen, die das Raster ausfüllen. Ein klassisches Beispiel sind Regionen an der Seite, Quartale oben und Verkäufe in den einzelnen Zellen.

PivotTables sind sehr nützlich, müssen jedoch manuell aktualisiert werden und befinden sich in einem festen Bereich. Eine formelgesteuerte PivotTable erstellt sich bei jeder Datenänderung automatisch neu.

In dieser Lektion ordnen Sie Zeilenüberschriften und Spaltenüberschriften an und erstellen einen Bereich mit SUMIFS-Formeln, die automatisch jeden Schnittpunkt berechnen.

Wir verwenden ein Tabellenblatt namens Sales mit den folgenden Spalten: Region in A, Quartal in B und Betrag in C, jeweils in den Zeilen 2 bis 500.

Der gewünschte Bericht sieht folgendermaßen aus:

• Zeilenbeschriftungen: jede eindeutige Region untereinander in Spalte E.
• Spaltenbeschriftungen: Q1, Q2, Q3, Q4 nebeneinander in Zeile 1 von F bis I.
• Inhalt: der Gesamtbetrag für jedes Paar aus Region und Quartal.

Jede Zelle im Inhaltsbereich beantwortet eine Frage: Wie viel hat diese Region in diesem Quartal verkauft?

Die Zeilenüberschriften sind die eindeutigen Regionen. Verwenden Sie UNIQUE zusammen mit SORT, damit sie in Spalte E nach unten überlaufen und geordnet bleiben.

Geben Sie dies in E2 ein:

Die Regionen füllen nun E2 und die darunterliegenden Zellen selbstständig. Wie bei Zusammenfassungstabellen ist diese Liste der Anker, auf den das gesamte Raster zurückverweist.

Die Spaltenüberschriften sind die über eine Zeile verteilten Quartale. Sie können Q1, Q2, Q3 und Q4 von Hand eingeben oder sie mithilfe von TRANSPOSE und UNIQUE horizontal überlaufen lassen.

In F1 werden die eindeutigen Quartale damit über den oberen Bereich verteilt:

TRANSPOSE wandelt eine vertikale Liste in eine horizontale um. So wird aus einer Spalte mit Quartalen eine Zeile mit Überschriften. Nun sind beide Achsen des Rasters vorhanden.

Füllen Sie nun den Inhaltsbereich aus. Jede Zelle benötigt die Summe für die Region ihrer Zeile und das Quartal ihrer Spalte. SUMIFS kann problemlos zwei Bedingungen verarbeiten.

Schreiben Sie in die erste Zelle des Inhaltsbereichs, F2:

Damit werden die Beträge ausgewählt, deren Region der Beschriftung links entspricht und deren Quartal der Überschrift darüber entspricht. Es handelt sich um einen einzelnen Schnittpunkt der PivotTable.

Die Dollarzeichen ermöglichen es, eine Formel durch Kopieren im gesamten Raster zu verwenden. Sehen Sie sich die gemischten Bezüge genau an:

• $E2 fixiert die Spalte E, lässt aber die Zeile wechseln, sodass jede Zeile ihre eigene Region verwendet.
• F$1 fixiert die Zeile 1, lässt aber die Spalte wechseln, sodass jede Spalte ihr eigenes Quartal verwendet.
• $C$2:$C$500 ist vollständig fixiert, da sich der Datenbereich nicht verschiebt.

Kopieren Sie F2 über alle Quartale und nach unten über alle Regionen. Jeder Bezug passt sich genau an.

Wenn F2 korrekt eingegeben ist, wählen Sie die Zelle aus und ziehen Sie das Ausfüllkästchen nach rechts über die Quartalsspalten und anschließend nach unten über die Regionszeilen. Excel passt die relativen Bestandteile für Sie an.

• In Zelle G2 wird Region zu $E2 und Quarter zu G$1.
• In Zelle F3 wird Region zu $E3 und Quarter zu F$1.

Das Ergebnis ist eine vollständige Kreuztabelle mit Summen für jeden Schnittpunkt. Ein Pivot-Assistent ist nicht erforderlich, und die Berechnung wird sofort aktualisiert, sobald sich die Sales-Daten ändern.

Eine echte PivotTable zeigt Gesamtsummen. Fügen Sie rechts eine Summenspalte und unten eine Summenzeile hinzu, indem Sie mit SUM die Werte jeder Zeile beziehungsweise Spalte addieren.

Platzieren Sie für die Zeilensumme der ersten Region Folgendes in der Spalte nach dem letzten Quartal:

Für eine Spaltensumme addieren Sie die Inhaltszellen dieses Quartals über alle Zeilen hinweg. Diese Randsummen machen den Bericht vollständig und ermöglichen es den Lesern, die Zahlen auf einen Blick zu überprüfen.

Wenn Ihr Werkzeug dies unterstützt, können Sie das Kopieren vermeiden, indem Sie Überlaufbezüge direkt an SUMIFS übergeben. Verwenden Sie die übergelaufenen Überschriften als Kriterien.

Mit dieser einzigen Formel werden die Summen für jeden Schnittpunkt aus Region und Quartal berechnet:

Hier ist E2# die vertikale Regionsliste und F1# die horizontale Quartalsliste. Excel kombiniert sie in einem Schritt zu einem vollständigen Raster. Die Methode mit dem Ziehen ist kompatibler, aber dies ist die elegante moderne Variante.

Berichte werden aussagekräftiger, wenn sie nicht nur Beträge, sondern auch Anteile anzeigen. Fügen Sie eine Spalte hinzu, die die Summe jeder Region als Prozentsatz der Gesamtsumme darstellt.

Wenn sich die Zeilensumme der Region in J2 und die Gesamtsumme in J10 befindet, schreiben Sie:

Wenn Sie die Gesamtsumme mit $J$10 fixieren, können Sie die Formel für alle Regionen nach unten ausfüllen, wobei immer derselbe Nenner verwendet wird. Formatieren Sie die Spalte als Prozentsatz, damit die Leser sofort erkennen, welche Regionen den größten Anteil haben.

Einige Gewohnheiten sorgen dafür, dass eine formelbasierte PivotTable zuverlässig bleibt:

• Verwenden Sie vollständige, großzügig bemessene Bereiche wie die Zeilen 2 bis 500, damit neue Zeilen berücksichtigt werden.
• Fixieren Sie Datenbereiche mit vollständigen $-Verankerungen. Nur die Bezüge auf Überschriften sollten sich ändern.
• Lassen Sie unterhalb und rechts davon freien Platz, damit übergelaufene Überschriften und Summen genügend Raum haben.

Wenn Sie dies richtig umsetzen, benötigt der Bericht keinerlei Wartung. Geben Sie neue Verkäufe ein, und Raster, Summen sowie Beschriftungen aktualisieren sich selbst.

Sie haben eine PivotTable ausschließlich mit Formeln nachgebildet:

• UNIQUE zusammen mit SORT erstellte die Zeilenüberschriften in einer übergelaufenen Spalte.
• TRANSPOSE verteilte die Spaltenüberschriften über eine Zeile.
• SUMIFS füllte mit den gemischten Bezügen $E2 und F$1 jeden Schnittpunkt aus, entweder durch Ziehen oder mit Überlaufbezügen wie E2# und F1#.
• SUM fügte die Ränder mit den Gesamtsummen hinzu.

Das gesamte Raster wird laufend neu berechnet. Als Nächstes machen Sie das Dashboard mit Dropdownlisten interaktiv, die die Kennzahlen steuern.