Kapitel 10

Geschrieben von Torsten Lenneper. Veröffentlicht in Excel 2000

Stern inaktivStern inaktivStern inaktivStern inaktivStern inaktiv
 

Pivot-Tabellen

Mit Excel können Sie umfangreiche Tabellen mit Zahlenmaterial erstellen. Diese Zahlen können für Berechnungen weiterverwendet werden, wie Sie in den bereits durchgearbeiteten Kapiteln gelernt haben. Je grösser aber die Tabellen sind, desto schwieriger wird es, diese nach speziellen Kriterien auszuwerten. Hier helfen Ihnen schnell und einfach die Pivot-Tabellen. Mit dieser Funktion lässt sich das Zahlenmaterial übersichtlich nach Ihren Vorstellungen darstellen und auswerten.

Daten auswählen

Sie können für Ihre Pivot-Tabelle Daten aus Excel oder aber auch aus externen Datenbanken verwenden. In diesem Beispiel nutzen Sie die Daten aus der Access‑Datenbank «Edv-Zubehör.mdb».

Übung 10.1
  • Öffnen Sie in Excel eine neue Arbeitsmappe
  • Gehen Sie in das Menu DATEN►PIVOT-TABLE UND PIVOTCHART-BERICHT

Es erscheint ein Assistent in Form eines Dialogfensters der Sie in drei Schritten zu Ihrer Pivot-Tabelle führen wird

  • Zuerst müssen Sie die Daten, die Sie analysieren möchten, auswählen. Dies können Daten aus einer Arbeitsmappe oder einer, wie in diesem Fall, aus einer externen Datenbank sein. Sollen die Daten aus verschiedenen Arbeitsmappen verwendet werden, markieren Sie die dritte Option. Da Sie eine Tabelle erstellen möchten, wählen Sie die erste Darstellungsform und klicken anschliessend auf
  • Als nächstes müssen Sie angeben, wo sich die Daten befinden. Klicken Sie also auf
  • Im nächsten Fenster müssen Sie nun die Datenbank, aus der Sie Daten importieren möchten, festlegen
  • Da es sich um eine mit Access erstellte Datenbank handelt, wählen Sie den Punkt durch Klick aus. Bestätigen Sie mit
  • Suchen Sie im nächsten Dialogfenster die Datei und klicken wieder auf
  • Nun müssen Sie die Tabelle oder Tabellen auswählen, da Sie nicht die komplette Datenbank verwenden können. Ein Klick auf die +-Zeichen zeigt die in der Tabelle enthaltenen Felder an. Wählen Sie ein Feld aus und klicken auf . Um ein oder alle Felder wieder zu entfernen, nutzen Sie die beiden anderen Schaltflächen. Für dieses Beispiel benötigen Sie die folgenden Felder aus der Tabelle «EDV‑Liste» und klicken danach wieder auf
  • Im nächsten Fenster können Sie die Daten wenn gewünscht auch noch ausfiltern. In diesem Beispiel sollen allerdings sämtliche Daten mit übernommen werden. Klicken Sie also auf
  • Sortieren Sie die Daten im nächsten Fenster wie abgebildet und klicken danach auf
  • Im letzten Fenster können Sie die gerade erstellte Abfrage nun speichern um sie auch später noch zur Verfügung zu haben. Das brauchen Sie in diesem Beispiel allerdings nicht. Wählen Sie nur die Option und klicken auf
  • Sie befinden sich jetzt wieder beim zweiten Schritt zum erstellen einer Pivot-Tabelle, nur diesmal sehen Sie, dass Daten gefunden wurden. Klicken Sie auf
  • Je nachdem, wo die Tabelle erstellt werden soll, können Sie eine der zwei Optionen wählen
  • Klicken Sie auf die Schaltfläche um die Art der Darstellung festzulegen. Es öffnet sich das folgende Dialogfenster. Ziehen Sie die Schaltflächen am rechten Rand in die Felder so wie hier abgebildet und klicken dann auf
  • Hinter der Schaltfläche finden Sie noch viele weitere Einstellungen, die Sie aber auch später noch vornehmen können. Belassen Sie es hier bei den Standardeinstellungen und klicken in diesem Fenster nur auf .
  • Jetzt hat der Assistent alle Daten, die er benötigt und Sie können ihn mit einem Klick auf beenden

In Ihrem Arbeitsblatt sehen Sie nun die neue Pivot-Tabelle. Zusätzlich erscheint noch eine neue Symbolleiste mit der Sie die wichtigsten Funktionen zum Bearbeiten schnell erreichen

Speichern Sie die Arbeitsmappe unter dem Namen «pivot extern» in Ihrem Übungsverzeichnis

Tabelle anpassen

Nachdem Sie nun die Tabelle aus den Daten erstellt haben, sollten Sie die einzelnen Spalten an die Breite der Einträge anpassen. Markieren Sie also mit Strg+a das gesamte Blatt und wählen aus dem Menu FORMAT►SPALTE►OPTIMALE BREITE BESTIMMEN.

Sehen Sie sich die Tabelle einmal genauer an. In der Spalte A finden Sie die einzelnen Artikel kurz beschrieben. Ab der Spalte C werden die einzelnen Artikelnummern aufgelistet. Der Schnittpunkt in dem die Daten wie in der Spalte B beschrieben eingetragen sind, zeigt die Zugehörigkeit von Artikelbeschreibung und Artikelnummer an. In der Spalte O und in den Zeilen 41 bis 43 sehen Sie die einzelnen Summen für die Artikel. Diese Zahlen sind allerdings in diesem Beispiel nicht aussagekräftig. Wichtiger sind da schon die Werte im Zellbereich O41:O43. Dort können Sie ablesen, welchen Betrag Sie erzielen, wenn Sie jeden Artikel einmal verkaufen würden, wie viele Artikel Sie insgesamt am Lager haben, wenn Sie immer nur den Mindestbestand bevorraten und wie viele Artikel Sie insgesamt am Lager haben.

Darstellung ändern

Der Sinn und Zweck einer solchen Tabelle ist das Analysieren der Daten. Momentan erscheinen sämtliche Artikel mit den entsprechenden Werten. Sie möchten aber z.B. nur die Artikel eines bestimmten Lieferanten sehen. Das ist kein Problem in dieser Ansicht.

Übung 10.2
  • In der Zelle B1 finden Sie eine Liste mit den verschiedenen Lieferantennummern. Standardmässig werden hier alle angezeigt. Öffnen Sie die Liste durch Klick auf den Listenpfeil und wählen den Lieferanten «3-LF» und klicken unten in der Liste auf . Jetzt werden nur noch Artikel dieses Lieferanten angezeigt
  • Wählen Sie wieder alle Lieferanten aus und lassen sich diesmal nur die Artikel anzeigen, deren Artikelnummer mit «099» beginnt. Diese Auswahl lässt sich zusätzlich noch weiter begrenzen, in dem Sie auch bei den Lieferanten noch zusätzlich selektieren

Formeln einfügen

Die Analyse ist das eine, das Auswerten der Daten, speziell der Zahlen, der andere Vorteil dieser Tabelle. Sie können nun Felder einfügen die Formeln enthalten und somit bestimmte Berechnungen durchführen. So wird die Tabelle natürlich auch aussagekräftiger.

Übung 10.3
  • Machen Sie die Zelle B5 durch Klick aktiv und wählen aus der Pivot-Symbolleiste die Schaltfläche . Dort finden Sie den Befehl FORMELN►BERECHNETES FELD. Alternativ können Sie natürlich auch mit der rechten Maustaste in die Zelle klicken und den Befehl aus dem Kontextmenu wählen
  • Im neuen Dialogfenster geben Sie oben einen Namen ein. Markieren Sie dann in der Liste den Eintrag «EK-Preis DM» und klicken auf . Der Eintrag wird in die Formel übernommen. Da Sie hier eine Multiplikation durchführen möchten, drücken Sie die *-Taste und fügen anschliessend den Eintrag «Lagerbestand» hinzu. Ein Klick auf übernimmt die Berechnung
  • Lassen Sie sich jetzt wieder alle Artikel aller Lieferanten anzeigen. Sie sehen, das Feld wurde bei allen Artikeln, nicht nur bei den sichtbaren eingefügt

Auf diese Weise lassen sich nun die Werte der einzelnen Lagerartikel ablesen und unten rechts sehen Sie den Gesamtwert aller Artikel. Wenn Sie schnell ablesen möchten, welchen Betrag Sie zahlen müssen, wenn Sie eine Bestellung mit der Mindestbestellmenge bei Ihrem Lieferanten auslösen, können Sie sich dies auch schnell in einem Feld anzeigen lassen.

Übung 10.4
  • Klicken Sie wie in der vorherigen Übung wieder in die Spalte B der Pivot-Tabelle (achten Sie darauf, dass Sie auch wirklich in die Tabelle klicken, denn der Bereich ausserhalb wird wie ein normales Excel-Arbeitsblatt behandelt und so stehen Ihnen die speziellen Funktionen natürlich auch nicht zur Verfügung)
  • Vergeben Sie wieder einen Namen und multiplizieren die entsprechenden Felder miteinander. Danach klicken Sie auf

Natürlich können Sie in weiteren Berechnungen, so wie in Excel üblich, auch die berechneten Felder in neuen Formeln verwenden. Diese erscheinen jetzt ebenfalls in der Liste der Felder. Da es sich hier um eine alte Tabelle mit Daten in DM-Werten handelt, möchten Sie die Werte nun in Euro ausgeben lassen. Auch dies erledigen Sie schnell mit einer Formel.

Übung 10.5
  • Öffnen Sie wieder das Dialogfenster um ein berechnetes Feld einzufügen
  • Vergeben Sie den Namen wie abgebildet
  • Tragen Sie die Formel in das Feld ein und klicken anschliessend auf

Wie Sie sehen, können Sie in den Formeln auch Konstanten verwenden. Es bleibt Ihnen also überlassen, wie Sie die Formeln gestalten um eine angepasste Auswertung zu erhalten.

Formeln ansehen

Nachdem Sie nun einige Formeln in die Tabelle eingegeben haben können Sie sich diese auch anzeigen lassen. Dies funktioniert allerdings nicht wie gewohnt mit der Tastenkombination Strg+# sondern über das Kontextmenu bzw. der Schaltfläche in der Symbolleiste. Wählen Sie dort FORMELN►FORMELN AUFLISTEN. Es wird ein neues Tabellenblatt geöffnet in dem Ihre Formeln dann aufgelistet werden. Sie können die Formeln hier aber nicht bearbeiten. Dies müssen Sie im Dialogfenster erledigen. Dort lässt sich eine bereits erstellte Formel nach Ihrem Namen auswählen und anschliessend bearbeiten.

Layout verändern

Im Assistenten haben Sie ja zu Beginn das Layout der Tabelle durch Ziehen der Felder festgelegt. Sollte Ihnen das Layout aber jetzt nicht zusagen, müssen Sie die Tabelle nicht neu erstellen. Sie können die Felder auch jetzt noch an eine andere Stelle verschieben. Sie sollten aber die Arbeitsmappe vorsichtshalber vor solchen Änderungen speichern (man sollte sich nicht immer auf den Befehl verlassen). Wenn Sie ein Feld anklicken und mit gedrückter Maustaste an einen anderen Ort ziehen, ändert sich der Mauszeiger und Sie sehen eine graue Linie, die Ihnen anzeigt wo die Daten anschliessend, wenn Sie die Maustaste wieder loslassen, erscheinen.

Übung 10.6
  • Blenden Sie bei den Daten alle Felder bis auf die abgebildeten aus. Die Daten werden jetzt nicht benötigt und die Tabelle wird so übersichtlicher
  • Nun ziehen Sie das Feld auf das Feld bis dass Sie vor der Spalte B eine graue Linie sehen. Lassen Sie dann die Maustaste wieder los. Die Tabelle sollte nun so aussehen

Für die hier verwendeten Daten ist die Darstellung in jedem Fall besser geeignet. Da es aber kein Problem ist, dass Layout auch nachträglich noch zu ändern, müssen Sie sich zu Beginn auch noch nicht zu viele Gedanken hierzu machen. Wichtig ist nur, dass auch alle benötigten Daten erscheinen.

Tabelle formatieren

Die Tabelle können Sie wie in Excel gewohnt formatieren. Dies wäre z.B. bei den Zahlen in der Spalte D nötig. Markieren Sie die Spalte und weisen Ihr das Zahlenformat «Zahl» mit zwei Dezimalstellen zu. Sie können auch den Zeilen verschiedene Hintergrundfarben zuweisen. Das geht in einer Pivot-Tabelle aber um einiges leichter. Führen Sie den Mauszeiger z.B. auf den linken inneren Rand der Zelle A6 und er verwandelt sich in einen kleinen schwarzen Pfeil . Wenn Sie jetzt klicken, werden alle Zeilen mit der gleichen Berechnung automatisch markiert und können von Ihnen formatiert werden. Das funktioniert auch mit den anderen in der Tabelle vorhandenen Elementen.

Automatisch formatieren

Sie können die Formatierung von Hand vornehmen oder dies automatisch von Excel erledigen lassen. Den Befehl hierzu finden Sie im Kontextmenu, hinter dem Symbol oder im Menu FORMAT►AUTOFORMAT. Es öffnet sich ein neues Dialogfenster, in dem Sie im oberen bereich verschiedene Formatierungen als Bericht und unten für die eigentliche Tabelle finden.

Pivot-Tabelle aus Excel-Liste erstellen

Diesmal werden Sie eine Pivot-Tabelle aus einer vorhandenen Liste erstellen. Öffnen Sie die Datei «pivot.xls». Hier sehen Sie die verkauften Stückzahlen verschiedener Automobilhersteller in den ersten zwei Quartalen eines Jahres. Zusätzlich werden die Stückzahlen noch in drei Bereiche und drei Fahrzeugtypen unterteilt.

Mit dieser Tabelle können Sie nur sehr schwierig Vergleiche anstellen und auch Berechnungen wären sehr aufwändig zu realisieren. Hier sehen Sie gleich den sinnvollen Einsatz einer Pivot-Tabelle.

Tabelle erstellen

Sie können die Daten vorher oder auch nach dem Start des Assistenten markieren. Die Vorgehensweise entspricht in etwa der aus der vorherigen Tabelle.

Übung 10.7
  • Markieren Sie den Zellbereich A1:E73. Dies müsste die komplette Liste sein
  • Gehen Sie in das Menu DATEN►PIVOT-TABLE UND PIVOTCHART-BERICHT
  • Diesmal wählen Sie die Option und klicken auf
  • Da Sie den Bereich bereits ausgewählt haben, müssen Sie in diesem Fenster nichts ändern. Ansonsten hätten Sie an dieser Stelle Ihre Auswahl festgelegt. Klicken Sie erneut auf
  • Da die Tabelle nicht in dem vorhandenen sondern in einem neuen Blatt erstellt werden soll, wählen Sie die erste Option und klicken auf . Stellen Sie das Layout wie nebenstehend ein und klicken auf und im nächsten Fenster auf

Aus der unübersichtlichen Liste ist nun eine kleine und überschaubare Tabelle geworden. Momentan können Sie ohne Probleme ablesen, welcher Fahrzeugtyp in welchem Bereich wie oft verkauft wurde.

Die Tabelle wurde nach Marken aufgelistet.

Weitere Tabellen

Sie können natürlich auch noch weitere Berichte auf die gleiche Weise erstellen und ändern einfach nur das Layout um eine andere Darstellung der Daten zu erhalten. Sie sollten hier aber den benötigten Speicherplatz berücksichtigen. Für die auszuwertenden Daten wird eine Kopie der Daten im Arbeitsspeicher und der Festplatte bereitgehalten. Auf dieser Kopie basiert Ihr Bericht. Wenn Sie nun einen neuen Bericht, der auf den selben Daten basiert, erstellen, wird eine weitere Kopie angelegt und im Speicher bereitgehalten. Das können Sie umgehen, in dem Sie als Quelle den Bericht auswählen.

Übung 10.8
  • Klicken Sie ausserhalb des Berichts in die Zelle A15 um diese aktiv zu machen
  • Klicken Sie in der Pivot-Symbolleiste auf das Symbol um den Assistenten zu starten oder wählen Sie den Befehl aus dem Menu Daten
  • Im ersten Schritt wählen Sie diesmal diese Option und klicken auf
  • Als nächstes sollen Sie die Pivot-Tabelle auswählen, aus der Sie die Daten verwenden möchten. Da es nur eine gibt ist sie auch schon gewählt. Klicken Sie also auf
  • Diesmal soll die Tabelle nicht in einem neuen sondern im bestehenden Blatt eingefügt werden. Die Zelle oben links der neuen Tabelle haben Sie ja schon vorher markiert, könnten das aber auch an dieser Stelle noch erledigen
  • Klicken Sie auf um die Darstellung folgendermassen festzulegen
  • Ein Klick auf und ein weiterer auf fügt die Tabelle ein

Gleiche Daten aber eine ganz andere Aussage haben Sie mit Ändern des Layouts umgesetzt. In der nächsten Übung werden Sie noch eine dritte Tabelle erstellen. Wieder wird ein anderer Weg gewählt.

Übung 10.9
  • Es ist egal welche Zelle gewählt ist und Sie müssen auch nichts markieren. Rufen Sie einfach nur wieder den Assistenten auf
  • Es sollen wieder die Daten aus einem vorhanden Bericht analysiert werden. Klicken Sie auf und wählen aus der Liste (jetzt sehen Sie zwei Einträge) den gleichen wie in der letzten Übung
  • Nun müssen Sie angeben, wo in dem bestehenden Arbeitsblatt die Tabelle erscheinen soll. Wählen Sie die Zelle A28 und klicken Sie auf denn ein Layout werden Sie diesmal nicht festlegen
  • Nun wurde ein leerer Bericht eingefügt, in dem Sie nun nachträglich das Layout festlegen. Die Felder die Sie in die Bereiche ziehen müssen, befinden sich in Ihrer Pivot-Symbolleiste. Werden die Felder nicht angezeigt klicken Sie auf das Symbol .
Übung 10.10
  • Ziehen Sie jetzt die Felder wie in der Abbildung aus der Symbolleiste in die verschiedenen Bereiche
  • Und wieder haben Sie aus den Daten eine ganz andere Ansicht erstellt und können die Zahlen neu analysieren.

Berechnungen durchführen

Die Tabellen bieten Ihnen nun eine viel bessere Übersicht und Sie können das Zahlenmaterial ja auch innerhalb der Tabelle neu anordnen oder einzelne Daten ausblenden. Richtig interessant werden die Berichte aber erst, wenn Sie auch Berechnungen durchführen. Weiter oben in diesem Kapitel haben Sie ja schon berechnete Felder eingefügt. Solche Berechnungen könnten Sie auch hier durchführen.

Ergebnisse finden

Sie sollen schnell die Frage «Welche Stückzahl der einzelnen Fahrzeugtypen hat VW in Asien verkauft?» beantworten. In der Liste müssten Sie die Zahlen nun mühsam heraussuchen und addieren (hier wurden nur zwei Quartale berücksichtigt. Überlegen Sie sich einmal die Arbeit die Sie mit zwölf Monaten hätten!). In der ersten Tabelle wählen Sie oben einfach nur die aus und entfernen die entsprechenden Häkchen im Feld .

Die zweite Tabelle könnte schnell die Frage nach der Marke mit den meistverkauften Cabrios beantworten. Den Bereich können Sie natürlich auch gleich mit ablesen. Einfach die Felder entsprechend anpassen und Sie sehen die nebenstehende Analyse.

Feldeigenschaften

Bisher wurde das Zahlenmaterial an allen Stellen automatisch summiert, es sei denn, Sie haben ein berechnetes Feld eingefügt. Es stehen Ihnen aber auch noch andere Möglichkeiten neben der Summe zur Verfügung.

Übung 10.11
  • Sie möchten die Daten der Tabelle aus Abb. 10.28 nicht als Stückzahl sondern lieber in Prozent anzeigen. Klicken Sie dazu doppelt auf . Alternativ können Sie auch den Befehl FELDEIGENSCHAFTEN aus dem Kontextmenu nutzen oder Sie markieren das Feld und klicken auf
  • Es öffnet sich ein Dialogfenster, in dem Sie auch andere Funktionen auswählen können. Die soll aber in diesem Fall nicht geändert werden, weil die prozentuale Anzeige ja von der Summe ausgehen muss. Klicken Sie auf
  • Das Dialogfenster wird nun nach unten hin um einige Auswahlmöglichkeiten erweitert. Wählen Sie aus der Liste und klicken danach auf

Die prozentualen Anteile, die bei Auswertungen noch wichtiger sind als nur das reine Zahlenmaterial, lassen sich jetzt schnell ablesen. Auch wenn Sie jetzt wieder alle Fahrzeugtypen anzeigen lassen, wird weiter der Prozentwert angezeigt.

In der dritten Tabelle ist ja klar erkennbar, dass Opel die wenigsten Fahrzeuge verkauft hat. Sie können die Tabelle nun so auswerten, dass auf dieser Basis die Daten ausgewertet werden.

Übung 10.12
  • Klicken Sie doppelt auf
  • Wählen Sie diesmal die folgenden Einstellungen

Sie können jetzt ablesen, wie viel Prozent mehr die anderen Hersteller in den einzelnen Typen verkauft haben. Es gibt noch einige andere Auswertungsmöglichkeiten, entscheiden müssen Sie selbst welche Ergebnisse Sie sehen möchten. Je nachdem, welche Daten Sie verwenden, macht natürlich nicht jede Auswertung Sinn.

Details einblenden

Sie haben die Möglichkeit, einen Teil der Daten in einer neuen Tabelle einzufügen. Das geht aus der Pivot-Tabelle heraus schneller als wenn Sie diese in der Liste markieren, kopieren und neu einfügen.

Übung 10.13
  • Klicken Sie in der ersten Tabelle in die Zelle direkt unter
  • Ein weiterer Klick auf das Symbol erstellt eine neue Tabelle

Selbstverständlich können Sie diese Tabelle wieder als Grundlage für neue Pivot-Tabellen nutzen, formatieren oder Berechnungen durchführen.

Daten aktualisieren

Falls Sie das Zahlenmaterial aktualisieren müssen, sei es weil Sie falsche Zahlen eingegeben haben oder neue Daten verwerten möchten, müssen Sie die Pivot‑Tabellen nicht alle neu erstellen, es reicht wenn Sie diese aktualisieren.

Übung 10.14
  • Öffnen Sie das Arbeitsblatt mit der Liste der Daten
  • Ändern Sie in der Zelle E7 den Wert für die verkauften Audi Cabrios in Asien von«215» auf «222». Vergessen Sie nicht die Eingabe zu bestätigen
  • Klicken Sie jetzt mit der rechten Maustaste in eine der Pivot-Tabellen und wählen aus dem Kontextmenu den Befehl DATEN AKTUALISIEREN. Sie können aber auch auf das Symbol klicken oder den Befehl aus dem Menu Daten wählen

Wenn Sie sich die Zahlen ansehen, werden Sie feststellen, das die Änderungen in der Liste jetzt mit übernommen wurden. Bedenken Sie aber, dass dies nicht automatisch sondern nur manuell funktioniert.

PivotChart

Viele Zahlen können für den Betrachter unübersichtlich wirken. Um die Tabelle grafisch umzusetzen, haben Sie ja bereits den Diagramm-Assistenten kennen gelernt. Natürlich lässt sich auch eine Pivot-Tabelle schnell und einfach in ein Diagramm umwandeln. Klicken Sie einfach in die gewünschte Tabelle und anschliessend auf das Symbol . Es wird ein neues Diagrammblatt in die Arbeitsmappe eingefügt. Auch dort stehen Ihnen wieder die Felder zur Verfügung und alle Möglichkeiten zum Anpassen des Diagramms und des Diagrammtyps haben Sie auch.

Ändern sich die Daten in der Pivot-Tabelle, wird auch das Diagramm aktualisiert. Andersherum wirkt sich eine bestimmte Auswahl im Diagramm auch wieder auf die Tabelle aus. Lassen Sie sich z.B. im Diagramm nur Kombis anzeigen und wechseln dann wieder zurück zur Tabelle, sehen Sie auch dort nur die Daten für die Kombis (Sie können danach ja wieder auswählen, was sich aber wieder auf das Diagramm auswirkt).

Es gibt noch viele Möglichkeiten mit Pivot-Tabellen zu arbeiten. Sie können Spalten ausblenden und auch die Detailansicht innerhalb der Tabelle verändern. Über die Tabelleneigenschaften lässt sich ein Name für die Tabelle vergeben und bestimmte Berechnungen ausblenden. Probieren Sie die einzelnen Optionen ruhig aus, die wichtigsten Dinge haben Sie in diesem Kapitel erfahren.

Deshalb verabschiedet sich das

Kapitel 10 jetzt