Pivot-Tabellen – GrundlagenDie Datenbasis für die Pivot-Tabelle vorbereiten
Worum geht es bei der Datenprüfung?
Fehlt eine Umsatzposition in der Auswertung oder wird sie doppelt gezählt, liegt die Ursache möglicherweise schon in der Datenquelle. Bevor Sie eine Pivot-Tabelle anlegen, prüfen Sie deshalb die Daten:
- Was stellt jede Zeile dar?
- Sind Werte tatsächlich als Zahlen beziehungsweise im passenden Format gespeichert?
- Gehören alle Zeilen zur Auswertung?
Ziel ist eine zusammenhängende Liste, in der jede Zeile einen gleichartigen Vorgang beschreibt. Jede Spalte enthält ein bestimmtes Merkmal und besitzt eine eindeutige Überschrift. Zahlen, Texte und Datumswerte müssen zur jeweiligen Spalte passen.
Anschließend wird die Liste als Excel-Tabelle eingerichtet. Im folgenden Beispiel sind die Daten als Tabelle tblVerkaufsdaten vorbereitet. Sie können daran den richtigen Aufbau prüfen.
Warum ist die Datenüberprüfung wichtig?
Eine Pivot-Tabelle wertet aus, was die Datenquelle enthält:
- Ist eine Position doppelt erfasst, geht ihr Umsatz bei einer Summierung zweimal ein.
- Steht zwischen den Positionen zusätzlich eine Zwischensumme als gewöhnliche Datenzeile, wird auch dieser Betrag mitgerechnet.
Das Ergebnis wäre damit zu hoch. Die Auswertung falsch.
Eine dynamische Excel-Tabelle erleichtert die Pflege
- Neue Datensätze, die Sie der Tabelle hinzufügen, gehören zu ihrer Datenquelle.
- Verwendet die Pivot-Tabelle den Tabellennamen als Quelle, werden diese Datensätze beim Aktualisieren berücksichtigt.
Excel-Datei als Muster für die Arbeit mit Pivot-Tabellen
Nutzen Sie die folgende Excel-Datei als Muster für Ihre Arbeit mit Pivot-Tabellen. Sie passt zu den Erklärungen in diesem Beitrag, sodass Sie schnell erkennen, woraus eine Pivot-Tabelle besteht und wie Sie damit arbeiten.
Wie geht man bei der Datenüberprüfung und Bereinigung vor?
Eine Zeile je Vorgang als einen Datensatz festlegen
In einer Liste (= Datenquelle) steht jede Zeile für eine Bestellposition, nicht für eine vollständige Bestellung. Deshalb dürfen BestellID, Kunde und Bestelldatum in mehreren Zeilen wiederkehren. Die PositionsID bezeichnet dagegen genau eine Position. In der Ausgangsdatei sind alle 4.320 PositionsIDs eindeutig.
Sie können die eindeutigen Werte in dieser Spalte zählen mit der Formel:
=ANZAHL2(EINDEUTIG(tblVerkaufsdaten[PositionsID];1;0))
Entspricht das Ergebnis der Anzahl der Zeilen mit Daten, sind die PositionIDs eindeutig.
Prüfen Sie außerdem:
- Befinden sich zwischen den Datensätzen Zwischensummen? Zum Beispiel Monats- oder Regionssummen?
- Enthält der Datenbereich vollständig leere Zeilen oder Spalten?
Überschriften und Datentypen prüfen
Kontrollieren Sie die Kopfzeile: Jede Spalte braucht einen eindeutigen, eigenen, aussagekräftigen Namen.
Und: Verbinden Sie in der Kopfzeile keine Zellen.
| Feld der Beispieldatei | Was darin stehen soll |
|---|---|
| PositionsID / BestellID | Kennungen als Text, zum Beispiel B000002-01 |
| Bestelldatum | Ein von Excel erkannter Datumswert |
| Menge / Umsatz | Zahl; keine angehängten Textzusätze wie „Stück“ |
| Bewertung | Eine Zahl, sofern eine Bewertung vorliegt; sonst leer |
Ob ein Wert als Zahl vorliegt, lässt sich mit ISTZAHL prüfen. Dazu prüfen Sie beispielsweise, ob die Werte in der Spalte Umsatz alle als Währung (= Zahl) formatiert sind, mit der Formel:
=ANZAHL2(ISTZAHL(tblVerkaufsdaten[Umsatz]))
Prüfen Sie entsprechend auch das Bestelldatum. Excel speichert Datumsangaben als Zahlen. Ob Tag, Monat und Jahr stimmen, müssen Sie gesondert kontrollieren.
Eine fehlerhafte Umsatzliste bereinigen
Beispiel:
- Eine Datentabelle enthält eine doppelte Position.
- Zudem gibt es eine Leerzeile und eine Summenzeile.
- Die Überschrift über der Spalte Region fehlt.
- Ein Umsatzwert ist als Text gespeichert.
Diese Fehler beheben Schritt für Schritt folgendermaßen:
Ergänzen Sie in der betreffenden Spalte die Überschrift Region.
Wandeln Sie die Umsatzwerte in Zahlen um. Geben Sie dazu in einer freien Spalte ein:
=WERT(tblVerkaufsdaten[Umsatz])
Kopieren Sie diese Hilfsspalte in die Spalte Umsatz mit Start > Einfügen > Inhalte einfügen die Option Werte. Löschen Sie anschließend die Hilfsspalte.
Löschen Sie doppelte Datensätze. Markieren Sie dazu die Spalte PositionsIDs ohne Überschrift. Über Start > Bedingte Formatierung > Regeln zum Hervorheben von Zellen > Doppelte Werte machen Sie die wiederholte ID sichtbar.
Vergleichen Sie die zugehörigen Zeilen vollständig, ob wirklich alle Einträge identisch sind. Falls ja:
- Markieren Sie die Tabelle.
- Wählen Sie Daten > Duplikate entfernen.
- Aktivieren Sie Daten haben Überschriften und wählen Sie alle Spalten aus.
- Bestätigen Sie mit OK.
Excel behält den ersten identischen Datensatz und entfernt den zweiten. Kontrollieren Sie das Ergebnis.
Die bereinigte Liste als dynamische Excel-Tabelle formatieren
Markieren Sie den gesamten Datenbereich, um die Liste als dynamische Excel-Tabelle zu formatieren.
Wählen Sie Start > Als Tabelle formatieren. Prüfen Sie im Dialog den Bereich und aktivieren Sie Tabelle hat Überschriften. Bestätigen Sie mit OK.
Hinweis: Sie können den Bereich auch per Tastenkombination Strg + T in eine dynamische Tabelle umwandeln.
Klicken Sie in die neue Tabelle. Tragen Sie auf der Registerkarte Tabellenentwurf im Feld Tabellenname den Namen tblVerkaufsdaten ein. Ein Tabellenname darf keine Leerzeichen enthalten und muss innerhalb der Arbeitsmappe eindeutig sein.
Welche Fehler oder Fallen gibt es bei der Datenprüfung?
Wiederholte Werte vorschnell löschen
Prüfen Sie die Bedeutung einer Wiederholung, bevor Sie Daten löschen. Würden Sie bei der Funktion Duplikate entfernen nur die Spalte BestellID auswählen, bliebe je Bestellung nur eine Position übrig.
Excel entfernt dabei die ganze Zeile innerhalb des markierten Bereichs, auch die Inhalte der nicht ausgewählten Spalten. Zellen außerhalb des Bereichs bleiben unverändert. Außerdem vergleicht Excel den angezeigten, nicht den gespeicherten Wert. Wird derselbe Betrag einmal als 156,80 und einmal als 156,8 angezeigt, gilt er nicht als doppelt.
Prüfen Sie daher zuerst, wodurch ein Datensatz eindeutig bestimmt wird, und arbeiten Sie bei Löschaktionen vorsichtshalber mit einer Kopie Ihrer Ursprungsdatei.
Jede leere Zelle als Fehler behandeln
Vollständig leere Zeilen unterbrechen den zusammenhängenden Datenblock und sollten entfernt werden. Eine leere Bewertung bedeutet dagegen: Für diese Position liegt keine Bewertung vor.
Setzen Sie dort nicht pauschal null ein; damit würden Sie eine fehlende Angabe durch einen Zahlenwert ersetzen. Eine fehlende PositionsID müssten Sie hingegen aufklären.
Beachten Sie außerdem: Enthält ein Zahlenfeld leere Zellen, verwendet Excel im Bereich Werte standardmäßig Anzahl statt Summe. Das betrifft später auch das Feld Bewertung.
Nur das Aussehen eines Eintrags ändern – nicht das Format
Das Währungsformat allein ist keine verlässliche Umwandlung eines gespeicherten Textwerts. Umgekehrt sind Kennungen wie B000002-01 absichtlich Text und sollen es bleiben.
Eine optisch formatierte Liste für eine Excel-Tabelle halten
Farbige Zeilen und fett gesetzte Überschriften reichen nicht aus, um aus einer Liste eine dynamische Excel-Tabelle zu machen. Entscheidend ist, dass die Daten mit Als Tabelle formatieren oder Einfügen > Tabelle als Tabellenobjekt angelegt wurden.
Sie erkennen dies daran, dass beim Klick in den Datenbereich die Registerkarte Tabellenentwurf erscheint.
