Excel: Konsolidieren von Daten

DARUM GEHT’S

Sie haben in einer Datei mehrere Tabellen angelegt, welche die Umsatzzahlen verschiedener Standorte für das erste Quartal des Jahres auflisten. Zur besseren Übersicht haben Sie die Umsatzzahlen pro Standort auf jeweils einem Arbeitsblatt aufgeführt, d. h. es gibt ein Arbeitsblatt für Berlin, ein weiteres für Frankfurt und ein drittes für München. Auf den drei Arbeitsblättern sind die Tabellen an verschiedenen Positionen aufgeführt und beinhalten z. T. unterschiedliche Eiscreme-Sorten.

Auf einem vierten Arbeitsblatt wollen Sie nun eine zusammenfassende Tabelle erstellen, welche die Daten für das erste Quartal in konsolidierter Form darstellt.

Aufgrund der unterschiedlichen Positionen der Tabellen, sowie deren z. T. unterschiedlichen Inhalte, müssen Sie hierzu die Funktion von Excel Konsolidieren nutzen.

Wie Sie diese Funktion aufrufen und ausführen, um die Daten mehrerer Arbeitsblätter zu konsolidieren, erfahren Sie im folgenden Lernobjekt.

SO WIRD’S GEMACHT

Um die Konsolidierungsfunktion aufzurufen, öffnen Sie bitte das Arbeitsblatt, auf dem die neue Tabelle entstehen soll. Hierbei ist es wichtig, die Zelle auszuwählen, in welcher die erste Zelle der Konsolidierungstabelle erscheinen soll. Beginnen Sie in diesem Fall mit der Bearbeitung des Arbeitsblattes Quartal 1 und wählen Sie dort die Zelle A4 aus.

Die Konsolidierungsschaltfläche  befindet sich innerhalb des Registers Daten in der Gruppe Datentools.

Nach Klick auf die Schaltfläche erscheint das Dialogfenster in Excel Konsolidieren, welches die folgenden Eingaben erfordert:

1.Hier können Sie verschiedene mathematische und statistische Funktionen für die Konsolidierung auswählen. Standardmäßig ist die Summenfunktion voreingestellt.

2. Dieses Feld dient dazu, auf den verschiedenen Arbeitsblättern die zu konsolidierenden Bereiche zu markieren.

3. In diesem Feld können die im o. g. Verweisfeld markierten Zellbereiche gesammelt und bearbeitet werden. Hierzu können auch die Schaltflächen Hinzufügen und Löschen benutzt werden.

4. In diesem Bereich können zusätzliche Optionen eingeschaltet werden, welche die Zeilen- und Spaltenbeschriftung sowie die Datenverknüpfung der Konsolidierungstabelle steuern.

In diesem Fall belassen Sie im Feld Funktion: die Funktion Summe und klicken in das Feld Verweis:, um den ersten Konsolidierungsbereich auswählen zu können. Klicken Sie nun auf den Reiter des Arbeitsblattes Berlin und wählen Sie dort die gesamte Tabelle (B4:E9) aus. Daraufhin wird die korrekte Zellbereichsbezeichung Berlin!$B$4:$E$9 im Feld Verweis: angezeigt. Klicken Sie nun auf die Schaltfläche Hinzufügen. Daraufhin wird dieser Bereich in das Feld Vorhandene Verweise: kopiert.

Nun kann die Auswahl des zweiten Konsolidierungsbereiches innerhalb des Arbeitsblattes Frankfurt erfolgen. Auch hierbei muss zunächst sicher gestellt werden, dass sich der Cursor im Feld Verweis: befindet, bevor der Zellbereich ausgewählt und abschließend mit Hinzufügen in das Feld Vorhandene Verweise: kopiert wird.

Abschließend gehen Sie in gleicher Weise mit dem Arbeitsblatt München vor, so dass im Feld Vorhandene Verweise: drei Zellbereichsbezüge aufgelistet sind.

Um die konsolidierte Tabelle mit korrekter Spalten- und Zeilenbeschriftung in das Arbeitsblatt Quartal 1 einzufügen, ist es notwendig, die beiden Optionen Oberster Zeile und Linker Spalte im Bereich Beschriftung aus: zu aktivieren.

Nun kann der Vorgang mit OK bestätigt werden. Daraufhin wird die konsolidierte Tabelle wie gewünscht eingefügt.

ACHTUNG

Damit bei der Konsolidierung die Daten aus den Spalten und Zeilen korrekt zugeordnet werden können, ist es wichtig, übereinstimmende Bezeichnungen für die einzelnen Kategorien auszuwählen. Beispielsweise kann Excel nur dann alle Werte korrekt der Schokoladen-Eiscremesorte zuordnen, wenn in allen Ausgangstabellen die Bezeichnung übereinstimmend Schokolade lautet.

SCHRITT FÜR SCHRITT

Sie möchten nun die Daten auf den Arbeitsblättern Berlin, Frankfurt und München in einem neuen Arbeitsblatt zusammenfassen und dafür die Funktion von Excel Konsolidieren nutzen. Öffnen Sie die Datei Eiscreme_Verkauf_3_8_4_2.xlsx, um die folgenden Schritte besser nachvollziehen zu können.

1. Stellen Sie sicher, dass Sie sich im Arbeitsblatt Quartal 1 befinden.

2. Klicken Sie in Zelle A4 und aktivieren Sie die Schaltfläche  innerhalb der Gruppe Datentools im Register Daten.

3. Stellen Sie sicher, dass im Feld Funktion: SUMME eingestellt ist und klicken Sie in das Feld Verweis:.

4. Klicken Sie auf den Reiter des Arbeitsblattes Berlin und wählen Sie den Tabellenbereich B4:E9 aus.

5. Klicken Sie im Dialogfenster Konsolidieren auf die Schaltfläche Hinzufügen.

6. Der Zellbezug wird im Feld Vorhandene Verweise: aufgeführt.

7. Verfahren Sie analog für die beiden Arbeitsblätter Frankfurt und München.

8. Abschließend aktivieren Sie die beiden Optionen Oberster Zeile und Linker Spalte im Bereich Beschriftung aus: und klicken auf die Schaltfläche OK .

Die Tabelle mit den konsolidierten Daten wird nun auf dem Arbeitsblatt Quartal 1 angezeigt.

TIPP

Lassen Sie sich nicht davon irritieren, dass, wenn Sie ein neues Arbeitsblatt öffnen, zunächst ein anderer Bereich markiert ist. Das ist der Bereich, den Sie beim vorherigen Arbeitsblatt markiert haben. Sobald Sie in dem neuen Arbeitsblatt den nun gewünschten Bereich anklicken, verschwindet auch die anfangs angezeigte Markierung.

Wenn Sie möchten, können Sie Ihr Ergebnis mit der Musterlösung vergleichen. Rufen Sie dazu die Datei Eiscreme_Verkauf_3_8_4_2_e.xlsx auf.

Das Ergebnis sollte wie folgt aussehen:

UND JETZT SIND SIE DRAN!

Öffnen Sie die Date Verkaufszahlen_3_8_4_2_u.xlsx, um auf dem Arbeitsblatt Quartal 1 eine zusammenfassende Tabelle für die Umsatzzahlen des ersten Quartals zu erstellen. Fügen Sie dazu eine Konsolidierungstabelle ein.

Sie können Ihr Ergebnis mit der Datei Verkaufszahlen_3_8_4_2_ue.xlsx vergleichen. Das Resultat sollte wie folgt aussehen:

 

Dieser Artikel gefällt Ihnen?

Dann teilen Sie ihn mit Freunden oder erhalten Sie mit unserem Newsletter die neuesten Infos.

Ihr Kommentar zu diesem Artikel

Connect with Facebook

*