9. Mindestens 10 Fragen die auftauchen, wenn man mit Pivot-Tabellen arbeitet

Pivot Fragen

Hier kommen die Antworten auf die wichtigsten Fragen, die der Arbeit mit Pivottabellen in Excel auftauchen…

DIE FRAGEN UND ANTWORTEN...

Wenn man Pivot-Tabellen erstellt, dann macht Excel die Summen immer über die Daten.

Das sieht so aus:

Pivot Tabelle Summe Oben

Pivot Tabelle Summe vom Monat ist oben

 

Vielen Menschen erschließt sich das nicht sofort. Also… wie bekommt man denn nun die Summen nach unten?

Teilergebnis unten anzeigen

So geht’s: Die Teilergebnisse unten anzeigen.

  1. Man wählt die betreffende Pivottabelle an.
  2. Man wählt den jetzt angezeigten Sonderreiter „Entwurf“
  3. Oben links klickt man auf das Symbol für „Teilergebnisse“
  4. Man wählt „Teilergebnis unten in der Gruppe anzeigen“

Das ist eigentlich einfach – wenn man es weiß! Denn in den Optionen für Pivot-Tabellen sucht man danach vergebens.

Wenn man hier etwas stöbert, findet man noch viele weitere Optionen…

Oftmals macht die Bildung von Summen für Spalten oder Zeilen keinen Sinn!

Wenn man zum Beispiel in einer Datentabelle Daten mit Plan-Zahlen und Tatsächlichen Ist-Werten hat, dann will man diese ja nicht am Ende addieren (die richtige Funktion wäre die Differenz – aber das kann Excel bei Pivot-Tabellen nicht).

Man kann die Gesamtsummen entfernen, indem man…

  1. Die Pivot-Tabelle anwählt
  2. den dann neu sichtbaren Reiter „Entwurf“ anwählt
  3. links oben zweiter Button „Gesamtsummen“ wählt
  4. …und dann die Summen z.B. nur für die Spalten aktiviert.

…macht keinen Sinn: Gesamtergebnis für „Plan“ und „Ist“

 

Und so kann man die Gesamtsumme entfernen…

In Pivot-Tabellen kann man die Felder der Spalten und Zeilen (also oben und links) sortieren.

Einfach das entsprechende Feld anwählen und in der Startleiste von Excel ganz rechts die Sortierfunktion wählen. Dann werden die Felder z.B. alphabetisch sortiert.

Genau so funktioniert das auch im Datenbereich. Einfach ein Datenfeld (in der Mitte der Pivot-Tabelle) anwählen und die Sortierfunktion wählen.

Was aber, wenn man die Reihenfolge manuell ändern will?

Das will man z.B. wenn man eine Einnahmeüberschussrechnung oder Kalkulation präsentiert und die Felder in einer typischen Reihenfolge erscheinen sollen (z.B. Personalkosten, Raumkosten und Abschreibungen weiter oben… Büromaterial und Beratungskosten, Versicherungen usw. weiter unten).

Man wählt einfach die Zelle an (z.B. in den Zeilen auf der linken Seite) und geht dann mit der (nicht gedrückten) Maus auf den oberen Rand dieser Zelle. Die Maus verändert sich zu einer kleinen Hand. Jetzt kann man die Zelle an eine andere Position ziehen. Dazu ist manchmal etwas Geschickt erforderlich… und manchmal muss man mehrmals klicken und es versuchen.

Wenn man bei einer Pivot-Tabelle ein Feld im Wertebereich erstellt, dann steht da immer so etwas wie…

„Summe von Betrag“.

Dieses ist eine sehr lange Bezeichnung, die viel Platz einnimmt. Gerade bei Summen (meist bildet man ja Summen), ist es ziemlich klar, dass es sich um eine Summe handelt.

Wenn man jetzt beim entsprechenden Feld im Pivot-Generator auf das kleine ‚i‘ im Kreis klickt, kann man den Namen des Feldes ändern.

Üblicherweise will man hier z.B. das Wort „Betrag“ eintragen.

Wenn man aber genau dieses Wort nutzt, also das gleiche Wort wie der Feldname in den Ursprungsdaten, dann erhält man eine Fehlermeldung.

Abhilfe: Man nutzt die gewünschte Bezeichnung und setzt ein Freizeichen dahinter. Das ist in der Tabellenansicht der Pivot-Tabelle unsichtbar und verhindert die Fehlermeldung.

Wenn man Pivot-Tabellen erstellt, beziehen sich diese immer auf einen bestimmten Bereich. Wenn man nur den Bereich anwählt (als Zellenbezug) hat man das genannte Problem nicht.

Es ist aber sinnvoll alle Spalten eines Datenbereiches als auszuwertenden Datenbereich anzuwählen. Am besten macht man das, indem man die Spalten anwählt und dann für diesen Bereich einen Namen vergibt (z.B. „Liste“ oder „Datenbank“).

Nun schaut die Pivot-Tabelle auf den ganzen Bereich aller Zeilen dieses Spaltenbereiches. Vorteil: Man kann Daten hinzufügen und dann die Pivot-Tabelle aktualisieren (rechte Maustaste, Daten aktualisieren), dann sind die neuen Daten gleich in der Auswertung enthalten.

Leider zeigt Excel aber immer mal eine Zeile oder Spalte mit der Auszeichnung „leer“ an. Das passiert dann, wenn in Datensätzen einzelne Felder schon gefüllt sind (z.B. berechnete Felder, die man sich nach unten kopiert hat), aber eben nicht alle Felder.

Lösung: Man wählt das Dropdown an der Pivot-Tabelle für Spalten oder Zeilen und wählt in der Feldliste, die man dann sieht einfach „leer“ einfach ab.

Aber Achtung: Wenn man manuell die Feldlisten bearbeitet, werden manchmal neue Werte nicht automatisch übernommen. Wenn man also die angezeigten Felder auf diese Weise manuell bearbeitet sollte man vorsichtig sein und vor der endgültigen Analyse der Pivot-Tabelle oder der Präsentation genau schauen, ob auch wirkliche alle gewünschten Daten in der Pivot-Tabelle enthalten sind.

Pivot Tabelle leer ausschalten

Es gibt Fälle, da will man z.B. rechts von der Pivot-Tabelle mit den Informationen der Pivot-Tabelle weitere Rechenoperationen ausführen.

Ein Beispiel: In einer Liste sind Plan-Daten für ein Budget gespeichert. Darauf kommen dann die tatsächlichen Ist-Werte. Das Ziel ist natürlich, in einer weiteren Spalte, die Differenz auszuwerfen.

Wenn man jetzt die Formel wie gewohnt erstellt – also über das „=“-Zeichen gefolgt von Klicks auf die entsprechenden Zellen, aus denen der neue Wert berechnet werden soll, dann erstellt Excel Bezüge, die irgendwie so aussehen: =PIVOTDATENZUORDNEN(„Netto „;$A$3;“Typ“;“Ausgabe“).

Wenn man diese nun automatisch z.B. nach unten ausfüllt (suche das kleine schwarze Kreuz am rechten unteren Zellenrand und ziehe die Zellen nach unten), dann erhält man immer den gleichen Wert, wie in der ersten Zelle.

Selbst wenn man bei „$A$3“ die Dollarzeichen entfernt, bleibt das so.

Lösung: Es gibt keinen anderen Weg, als in diesem speziellen Fall den Zellbezug ganz manuell einzugeben. Also z.B. „=E3“ oder eben eine Rechenoperation mit mehreren Zellen („=G3-E3“).

Wenn man das macht, kann man die Zellen nach unten kopieren und das Ergebnis ändert sich korrekt.

Wermutstropfen: Wenn man – und das ist bei Finanzplänen durchaus üblich – die Monate in den Spalten darstellt, erweitert sich die Pivot-Tabelle nach rechts. Wenn man Daten hinzufügt, dann werden die erstellten Formel überschrieben. Excel warnt davor. Man kann die Formeln vorher etwas nach rechts verschieben, so dass Raum für die neuen Daten entsteht. Aber Achtung: oftmals stimmen dann die Formeln nicht mehr. Dann muss man das schnell neu machen.

Gerade hier zeigt sich dann, dass mühevoll erstellte Formatierungen einfach so kaputt gehen. Daher sollte man (eigentlich) aufwendige manuelle Formatierungen in Excel vermeiden. „Keep it clean an simple“.

Trick für Formatierungen: Man kann einen Teil der farblich formatierten Pivot-Tabelle auswählen, der dem neu berechneten Bereich in der Größe entspricht und kopiert diesen in die Zwischenablage. Dann wählt man den berechneten Bereich an und  wählt ganz links im Startreiter Einfügen, (weitere Einfügeoptionen) „Formatierung. So wird der neu berechnete Bereich dann sehr schnell wieder so schick, wie die Pivot-Tabelle selbst.

NEIN. NEIN und noch mal NEIN.

Pivot-Tabellen kennen in der Werteausgabe nur die Excel-eigenen Funktionen, die sich immer auf ein Datenfeld mit Werten beziehen.

Man kann also keine Differenz oder Summe zwischen zwei Feldern bilden oder z.B. aus einem Datum den Monat per Funktion heraus ziehen.

Die einzige Abhilfe besteht darin, dass man im Datenbereich bereits berechnete Felder (z.B. H1… „=B1+G1“) hinzufügt bzw. Hilfsfelder für Monat und Jahr aus dem Datum erstellt („=Monat(B1)“).

Man kann auch unter oder neben Pivot-Tabellen weitere Rechnenoperationen mit eigenen manuell hinzugefügten Felder vornehmen.

Warnung: Die statistischen Funktionen von Pivot-Tabellen!!!!!

Ich warne ausdrücklich davor, die Funktionen „Mittelwert“ (und erst recht die anderen statistischen Funktionen wie z.B. Standardabweichung) in Pivot-Tabellen zu nutzen, ohne dass man Grundkenntnisse in Statistik hat. Durch den manchmal komplexen Aufbau von Pivot-Tabellen mit mehreren Schichten (Unterkategorien) ergeben sich dann nämlich „Mittelwerte“, die nicht wirklich die Ergebnisse sind, die man sich wünscht. …und das bezieht sich dann natürlich auch auf alle anderen Statistikfunktionen. Bevor man als die statistischen Funktionen wählt, sollte man die Ergebnisse mindestens einmal in Excel manuell nachrechnen oder zumindest die Plausibilität des Ergebnisses prüfen.

Ab der Excel-Version 2010 (und noch einmal erweitert für strukturierte Listen ab Version 2013) wurde in Excel die Funktion „Datenschnitt“ eingeführt.

Die Funktion Datenschnitt ist „nett“. Sie beschleunigt das Arbeiten mit Filtern in Listen und Pivot-Tabellen.

Aber: Man benötigt sie nicht wirklich!

Das Arbeiten mit „Datenschnitt“ funktioniert so:

  1. Klick in eine vorhandene Pivot-Tabelle.
  2. Auswahl Reiter Pivot-Datenanalyse
  3. Klick Datenschnitt einfügen
  4. Auswahl eine Feldes, das sich auch in der Pivot-Tabelle befindet (das macht Sinn). Dieses ist meist ein Feld im Detailbereich der Daten. Z.B. ein Feld der Zeilen (oder Spalten). Es kann auch ein Feld sein, das in der Pivot-Tabelle nicht sichtbar ist.
  5. Es entsteht rechts neben der Pivot-Tabelle ein Filterkästchen. Dort kann man jetzt einfach ein Feld anklicken. Die Pivot-Tabelle wird entsprechend gefiltert.
  6. Wenn man den Filter nicht mehr will einfach auf das Filtersymbol mit Kreuz klicken.
  7. Wenn man die Datenschnittfunktion nicht mehr will – einfach das Fenster anklicken und auf Entfernen klicken (Mac: Backspace Taste).

Datenschnitt und Pivot-Tabelle Excel

 

Vorteil:

Das geht wirklich schnell.

Aber:

Wenn es sich um die Filterung von Feldern dreht, die ohnehin in der Pivot-Tabelle sind, könnte man den normalen Pivot-Filter benutzen.

NEIN. Leider EIGENTLCIH nicht.

Man kann in Pivot-Tabellen keinen Text abbilden.

Wann bräuchte man das? Wenn man z.B. einen Einsatzplan von Mitarbeitern hat und in den Spalten z.B. die Schichten (Früh, Normal, Spät) und in den Zeilen die Tage, so will man ja als „Ergebnis“ in den Zellen die Mitarbeiternamen sehen. Das funktioniert nicht!

Ebenso funktioniert es EIGENTLICH nicht, dort eine Art Kreuz oder „X“ oder einen Buchstaben abzubilden.

Pivot-Tabellen kennen im Ergebnisbereich (also bei den Summen-Werten) nur Zahlen.

Es gibt einen ganz besonderen Trick über eine „Bedingte Formatierung“ bei bestimmten Zahleninhalten statt der Zahlen einen Buchstaben anzuzeigen.

Der Nachteil der Arbeit mit Daten und Informationen besteht darin, dass man die Informationen (in Excel und mit Tabellenkalkulationen) nicht immer so schön darstellen kann, wie man das auf einem Stück Papier machen würde! Bzw. muss man Umwege wählen, um das zu tun.

Es gibt aber viele Gründe trotzdem mit echten Daten zu arbeiten, da mit diesen immer Auswertungen möglich sind, die die Arbeit mit Papier nicht erlaubt.

Wenn man (meist im Bereich der Zeilen) in einer Pivot-Tabelle mehrere Felder angibt, so erstellt Excel für jedes dieser Felder in jeder Zeile ein Ergebnis.

Oftmals ist dieses Ergebnis immer das Gleiche. Man gibt die Zeilen dann nicht an, weil man diese als Rechenoperation benötigt, sondern nur als Information oder als zusätzlichen Filter.

Zu viele Male das gleiche Ergebnis – in diesem Fall in einer Pivot-Tabelle einer Personalplanung…

Pivot Tabelle zu viele Ergebnisse in Zeilen

Die Lösung:

  1. Man wählt ein Feld und klicke rechte Maustaste und wählt Feldeinstellungen. Noch übersichtlicher: Man wählt das kleine ‚i‘ im Pivot-Table-Generator für das entsprechende Feld.
  2. Man wählt die Option „Teilergebnisse“ … „keine“
  3. Das wiederholt man für alle Felder, bei denen keine Ergebnisse angezeigt werden sollen.

Ergebnis am Beispiel… – es wird nur noch die Arbeitszeit pro Woche aufsummiert. Die Zeiten pro Tag werden für die Mitarbeiter einfach angezeigt.

Weniger Ergebnisse anzeigen

Weniger Ergebnisse anzeigen

Deine Frage taucht hier nicht auf? Schicke mir eine E-Mail mit Deiner Frage und ich werde sie Dir persönlich beantworten (und danach hier für alle).

Excel Know-How in Ihrem Unternehmen...

Holen Sie sich mein Know-How in Ihre Firma. Als Unternehmensberater finde ich arbeitszeitfressende Excel-Dateien. Ich schule Sie, entwerfe für Sie (oder mit Ihnen) neue Modelle. Sparen Sie Zeit und entdecken Sie Potentiale in Ihren Unternehmensdaten. Einfach mal anfragen.

Winfrid Tiede
Unternehmensberater
Share
This