Am 14. September 2017 haben wir eine überarbeitete Fassung unserer Datenschutzrichtlinie veröffentlicht. Wenn Sie video2brain.com weiterhin nutzen, erklären Sie sich mit diesem überarbeiteten Dokument einverstanden. Bitte lesen Sie es deshalb sorgfältig durch.

Excel 2016: Mehrere Arbeitsmappen und Arbeitsblätter

Arbeitsmappen in einer Pivot-Tabelle zusammenfassen

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Liegen Ihnen mehrere Arbeitsmappen mit ähnlich aufgebauten datenbankähnlichen Daten vor, können Sie diese Mappen in einer Pivot-Tabelle zusammenfassen und auf vielfältige Arten auswerten.
12:03

Transkript

Wenn Sie schon mit Pivot-Tabellen gearbeitet haben, dann wissen Sie sicher zu schätzen, wie flexibel Sie Ihre Daten mit diesem Excel-Werkzeug auswerten können. In der Regel ist es so, Sie haben eine Mappe, zum Beispiel wie hier, die Mappe Gartenzubehör, diese Mappe enthält eine Tabelle und die Daten dieser Tabelle können Sie in einer Pivot-Tabelle auswerten, wenn die Daten bestimmte Voraussetzungen erfüllen. Die Daten müssen nämlich Datenbank-ähnlich aufgebaut sein. Das bedeutet, in der ersten Zeile stehen die Überschriften und in den Folgezeilen stehen jeweils in sich abgeschlossene Informationseinheiten, die Spalten dürfen jeweils immer nur einen Datentyp enthalten, zum Beispiel hier Text, Datum oder hier Zahlen. Und die Daten dürfen keine Leerzeilen und keine Leerspalten enthalten. Wenn diese Bedingungen erfüllt sind, dann können Sie hier oben durch Klicken auf Einfügen, dann PivotTable, eine Pivot-Tabelle erstellen. Excel erkennt automatisch die Dimensionen der Tabelle und möchte dann die Pivot-Tabelle in ein neues Arbeitsblatt einfügen. Wenn ich hier mit OK bestätige, fügt Excel also die Pivot-Tabelle in ein neues Arbeitsblatt dieser Mappe ein und ich kann dort meine Daten dreidimensional auswerten. Ich klicke hier auf Abbrechen, denn hier geht es nicht darum, eine Arbeitsmappe in einer Pivot-Tabelle auszuwerten, sondern ich möchte mehrere Mappen in einer Pivot-Tabelle zusammenfassen und dann die Daten auswerten. Wenn ich hier auf die Registerkarte Ansicht klicke und dann den Befehl Fenster wechseln wähle, dann sehen Sie, ich habe hier schon vier weitere Mappen geöffnet, es handelt sich dabei um vier Filialen eines Lieferanten für Gartenzubehör. Die Mappen sind nach dem gleichen Prinzip aufgebaut. Ich wechsle hier mal zur Mappe Alpengarten und in dieser Filiale sehen Sie, dass bestimmte Produkte verkauft werden, die sind zeilenweise angeordnet und die Produkte werden in bestimmten Verkaufsgebieten verkauft und die sind spaltenweise angeordnet. Die Anforderungen an den Datenbank-ähnlichen Aufbau sind hier erfüllt. In der ersten Zeile stehen Überschriften, in den Folgezeilen stehen Informationseinheiten und jede Spalte enthält jeweils nur einen Datentyp. Außerdem enthält der Bereich keine Leerzeilen und auch keine Leerspalten. Ich wechsle über Ansicht Fenster wechseln noch y#zu einer anderen Filiale, zum Beispiel Wasser und Garten. Hier sehen Sie, der Aufbau ist genau gleich, in den Zeilen die Produkte, in den Spalten die Verkaufsgebiete. Hier werden allerdings mehr Produkte verkauft und es gibt auch mehr Verkaufsgebiete. Das heißt, nicht jede Filiale verkauft alle Produkte und nicht jede Filiale verkauft in allen Verkaufsgebieten. Die Daten selbst bestehen hier ausschließlich aus Umsatzzahlen und nicht wie im vorhergehenden Beispiel, da gab es dann Spalten mit Datumsangaben, mit zusätzlichem Text und so weiter. wen Ihre Arbeitsmappen in ähnlicher Form aufgebaut sind, dann können Sie sie in einer Pivot-Tabelle zusammenfassen und auswerten. Ich wechsle über die Registerkarte Ansicht mit dem Befehl Fenster wechseln zu einer leeren Mappe und hier möchte ich jetzt die Pivot-Tabelle erstellen. Wenn ich hier, wie gehabt, auf Einfügen und die Schaltfläche PivotTable klicke, dann kann ich hier genau eine Tabelle oder einen Bereich angeben. Das würde ich hier machen, auf der Registerkarte Ansicht, mit Fenster wechseln, klicke ich in die erste Filiale und markiere den entsprechenden Bereich und dann wird hier dieser Bereich angezeigt. Aber ich habe hier vier Bereiche, weil ich möchte vier Mappen zusammenfassen. Das heißt, hier komme ich nicht weiter und ich klicke auf Abbrechen. ich wechsle zurück zu meiner leeren Mappe. Hier möchte ich die Pivot-Tabelle erstellen und Sie müssen sich jetzt mit einem Trick behelfen, und zwar gab es früher den PivotTable-Assistenten. Und mit diesem Assistenten können Sie eine Pivot-Tabelle erstellen und der Assistent ermöglicht es, dass Sie verschiedene Bereiche angeben. Und genau das brauche ich hier mit meinen vier Mappen. Der Assistent kann leider nicht mehr hier über das Menüband aktiviert werden und Sie müssen also wissen, wie Sie ihn aktivieren und es geht mit der Tastenkombination Alt+N+P. Der Assistent wird geöffnet und mit dieser dritten Option Mehrere Konsolidierungsbereiche kann ich hier meine verschiedenen Mappen angeben. Ich klicke auf Weiter, dann übernehme ich hier die Standardeinstellungen und mit diesem Feld kann ich die verschiedenen Bereiche definieren. Ich klicke auf der Registerkarte Ansicht, auf Fenster wechseln, dann gehe ich hier in alphabetischer Reihenfolge vor. Ich beginne also mit der Filiale Alpengarten, dann markiere ich hier diesen Bereich und klicke auf Hinzufügen. Excel hat jetzt diesen Bereich übernommen und er ist eindeutig definiert, hier steht der Mappenname, dann kommt der Blattname und jeder Zellbereich. Das wiederhole ich jetzt für alle weiteren Filialen. Auf der Registerkarte Ansicht klicke ich auf Fenster wechseln, dann gehe ich in alphabetischer Reihenfolge weiter zum Gemüsegarten, definiere auch hier den Bereich, klicke auf Hinzufügen, dann dasselbe nochmal, Ansicht < Fenster wechseln, die nächste Filiale ist der moderne Garten, ich definiere den Bereich, klicke auf Hinzufügen und dann klicke ich nochmal auf Ansicht < Fenster wechseln, dann fehlt mir noch Wasser und Garten, ich markiere auch diesen Bereich und klicke auf Hinzufügen. Jetzt habe ich alle vier Mappen hier angegeben und Excel weiß genau, wo die Daten in jeder einzelnen Mappe stehen. Dann klicke ich hier auf Weiter, Excel möchte die Pivot-Tabelle in einem neuen Arbeitsblatt der aktuell angezeigten Mappe erstellen oder in einer Zelle im bestehenden Blatt. Ich möchte das nicht in dieser Mappe machen, sondern ich wechsle hier nochmal über Fenster wechseln zu meiner leeren Mappe. Und dort möchte ich in der ersten Zelle meine Pivot-Tabelle erstellen. Das wird hier übernommen und ich kann hier auf Fertig stellen klicken. Und Excel erstellt mir jetzt in der Mappe 1, in der Tabelle 1 die Pivot-Tabelle. Auf den ersten Blick sieht es ein bisschen enttäuschend aus, hier steht Zeile, Spalte, Wert, Seite, dasselbe finden Sie in diesem Bereich und auch hier oben in diesem Bereich. Excel konnte anscheinend den Beschriftungstext nicht mit übernehmen. Das heißt, hier muss ich nacharbeiten und je nachdem, wie viele Arbeitsmappen ich hier zusammenfasse, kann das auch viel Arbeit bedeuten, aber die Arbeit lohnt sich auf jeden Fall, weil Sie hinterher Ihre Mappen äußerst flexibel auswerten können. Ich wechsle zunächst über die kontextabhängige Registerkarte PivotTable-Tools auf die Registerkarte Entwurf, wähle dort Berichtslayout und dann in der Liste den Befehl Im Tabellenformat anzeigen. Dann kann ich hier in diese Zelle klicken, es steht Zeile drin und was steht in meinen Zeilen, die Produkte. also gebe ich hier einfach Produkt ein. Dann klicke ich hier auf Spalte, in den Spalten stehen die Gebiete, dann gebe ich hier Gebiet ein und dann klicke ich hier auf Seite 1. Wenn ich hier die Liste öffne, dann sehen Sie, die Liste enthält vier Elemente, es handelt sich hier um die vier Filialen. Das heißt, hier gebe ich Filiale ein und dann öffne ich die Liste erneut und kann dann zwar hier ein Element markieren, aber ich kann hier nichts überschreiben. Das heißt, ich muss hier kurz meine Struktur verändern. Ich ziehe den Filialenfilter hinunter in den Zeilenbereich und dann werden hier die Elemente angezeigt und Sie erinnern sich vielleicht, ich bin bei der Definition der Pivot-Tabelle, bei den Filialen in alphabetischer Reihenfolge vorgegangen. Deswegen weiß ich, dass Element 1 ist der Alpengarten, ich gebe hier Alpengarten ein, dann klicke ich auf Element 2, die zweite Filiale in alphabetischer Reihenfolge war der Gemüsegarten, dann bleiben noch Element 3 und Element 4, Element 3 war der moderne Garten und Element 4 war die Filiale Wasser und Garten. Damit habe ich alle Bereiche benannt. Und jetzt sieht die Pivot-Tabelle schon viel übersichtlicher aus. Ich ziehe die Filiale wieder in den Filter. Was kann ich jetzt auswerten? Ich kann zunächst Filter setzen. Zum Beispiel hier ein Produktfilter, ich möchte nicht alle Produkte anzeigen, sondern nur ein paar Produkte. Ich blättere hierunter und markiere einfach ein paar Produkte und bestätige dann mit OK, dann wird die Pivot-Tabelle nach Produkten gefiltert. Zusätzlich kann ich jetzt noch einen Gebitsfilter setzen, ich möchte nicht alle Gebiete anzeigen, sondern nur Ost und Süd, bestätige mit OK und dann kann ich als letztes und das ist dann der dreidimensionale Aspekt, unter den Filialen filtern. Ich möchte auch hier nicht alle anzeigen, sondern nur die Filialen Gemüsegarten und Moderner Garten. dann bestätige ich mit OK und sofort wird die Pivot-Tabelle entsprechend gefiltert. Sie sehen, Sie können hier nach den verschiedensten Aspekten Filteroptionen setzen und Ihre Pivot-Tabelle wird sofort gefiltert. Dann hebe ich die Filter wieder auf, hier klicke ich einfach auf Alle, dann werden wieder alle Filialen angezeigt. Bei den Produkten kann ich mit dem Befehl Filter löschen alle Produkte anzeigen. Und bei den Gebieten mache ich dasselbe, Filter löschen und dann werden wieder alle Daten angezeigt. Was kann ich noch machen? Zusätzlich zum Filtern ich kann den Aufbau der Tabelle komplett ändern. Ich kann hier den Filter Filiale in die Zeile ziehen, dann werden hier in den Zeilen die Filialen angezeigt und dann für jede Filiale das Produkt. Ich kann hier die zweite Ebene des Produkts ausblenden, dann sehe ich hier nur noch die Filiale und die Zusammenfassung. Das kann ich auch beim Gemüsegarten machen und entsprechend kann ich die Produkte aber auch wieder einblenden. Dann möchte ich zum Beispiel die Gebiete nicht mehr in den Spalten anzeigen lassen, sondern möchte das auch in den Zeilen angezeigt haben, und zwar zwischen den Filialen und den Produkten. Und schon habe ich hier diese dreigeteilte Tabelle, Filialen, Gebiet, Produkte und kann die entsprechenden Unterebenen ausblenden. Dann kann ich auch noch einen ganz neuen Filter setzen, ich möchte jetzt nach Gebieten filtern. Sie sehen, jetzt kann ich hier oben wählen, Ich möchte nur Nord und Ost anzeigen lassen, und dann habe ich schon wieder eine ganz andere Auswertung meiner Daten. Dann ziehe ich hier die Filialen wieder nach oben, ich kann auch mehrere Filter setzen und dann ziehe ich das Gebiet in die Zeilen und die Produkte in die Spalten und schon habe ich eine horizontale Tabelle. Da man aber dazu neigt, Tabellen von oben nach unten zu lesen, mache ich das Ganze wieder rückgängig, ziehe die Produkte in die Zeilen und die Gebiete in die Spalten und dann habe ich wieder Di ursprüngliche Darstellung hergestellt. Das Gesamtergebnis wird standardmäßig durch eine Summe ermittelt. Das kann ich ändern. Ich klicke hier unter Werte auf diese Pfeilspitze, wähle Wertfeldeinstellugen und dann kann ich hier eine andere zusammenfassende Funktion wählen, zum Beispiel Anzahl. Ich bestätige mit OK und dann wird hier die Anzahl der Verkäufe angezeigt. Ich denke, Sie haben das Prinzip und die Flexibilität verstanden. Sie können also zusammenhängende Daten aus verschiedenen Arbeitsmappen in einer Pivot-Tabelle zusammenfassen und dann mit der Flexibilität dieses Werkzeuges Ihre Mappen dreidimensional auswerten. Um mehrere Mappen für die Pivot-Tabelle angeben zu können, brauchen Sie den PivotTable-Assistenten und diesen Assistenten aktivieren Sie mit der Tastenkombination Alt+N+P.

Excel 2016: Mehrere Arbeitsmappen und Arbeitsblätter

Meistern Sie die Arbeit mit Daten, die über mehrere Blätter oder Dateien verteilt sind – navigieren, verknüpfen, konsolidieren, auswerten.

2 Std. 6 min (21 Videos)
Derzeit sind keine Feedbacks vorhanden...
 
Hersteller:
Software:
Exklusiv für Abo-Kunden
Erscheinungsdatum:29.06.2017

Dieser Online-Kurs ist als Download und als Streaming-Video verfügbar. Die gute Nachricht: Sie müssen sich nicht entscheiden - sobald Sie das Training erwerben, erhalten Sie Zugang zu beiden Optionen!

Der Download ermöglicht Ihnen die Offline-Nutzung des Trainings und bietet die Vorteile einer benutzerfreundlichen Abspielumgebung. Wenn Sie an verschiedenen Computern arbeiten, oder nicht den ganzen Kurs auf einmal herunterladen möchten, loggen Sie sich auf dieser Seite ein, um alle Videos des Trainings als Streaming-Video anzusehen.

Wir hoffen, dass Sie viel Freude und Erfolg mit diesem Video-Training haben werden. Falls Sie irgendwelche Fragen haben, zögern Sie nicht uns zu kontaktieren!