In Excel einen Verweis auf verschieden Daten

DARUM GEHT’S

Sie haben in mehreren Dateien Tabellen angelegt, welche die Umsatzzahlen nach Standorten sortiert für das erste Quartal des Jahres auflisten. Zur besseren Übersicht haben Sie je eine Datei pro Standort erstellt, d. h. es gibt eine Datei für Berlin, eine weitere für Frankfurt und eine dritte für München. In den drei Dateien sind die Tabellen an verschiedenen Positionen aufgeführt und beinhalten z. T. unterschiedliche Eiscreme-Sorten.
In einer vierten Datei wollen Sie nun eine zusammenfassende Tabelle erstellen, welche die Daten für das erste Quartal in konsolidierter Form darstellt, hierbei ist es Ihnen wichtig, dass die Daten darin automatisch aktualisiert werden, wenn sich die Ausgangsdaten verändern. Sie können in Excel einen Verweis auf diese Daten erstellen.
Aufgrund der unterschiedlichen Position der Tabellen sowie deren z. T. unterschiedlicher Inhalte müssen Sie hierzu die Funktion Konsolidieren benutzen.
Wie Sie die Funktion Konsolidieren dateiübergreifend aufrufen und so ausführen, dass die Konsolidierungstabelle aktualisiert wird, erfahren Sie im folgenden Lernobjekt.

Verweis

SO WIRD’S GEMACHT

Um die Konsolidierungsfunktion von Excel für mehrere Dateien einzusetzen, öffnen Sie bitte sämtliche benötigten Dateien. Dann wechseln Sie in die Datei, welche die neue Tabelle enthalten soll. Hierbei ist es wichtig, diejenige Zelle auszuwählen, in welcher die erste Zelle der Konsolidierungstabelle erscheinen soll. In unserem Fall wechseln Sie daher in die Datei Eiscreme_Verkauf_3_8_4_3.xlsx und wählen dort die Zelle A4 aus.

Verweis

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

Verweis

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

Verweis

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.

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

In diesem Fall belassen Sie das Feld Funktion: mit der Funktion Summe und klicken in das Feld von Excel-Verweis:, um den ersten Konsolidierungsbereich auszuwählen. Klicken Sie nun in die Datei Berlin_3_8_4_3.xlsx und wählen Sie dort den gesamten Datenbereich B4:E9 aus. Wenn die korrekte Zellbereichsbezeichung [Berlin_3_8_4_3.xlsx]Berlin!$B$4:$E$9 im Feld Verweis: angezeigt wird, klicken Sie auf die Schaltfläche Hinzufügen.

Verweis

Daraufhin wird dieser Bereich im Feld Vorhandene Verweise: aufgeführt.

Nun kann die Auswahl des zweiten Konsolidierungsbereiches innerhalb der Datei Frankfurt_3_8_4_3.xlsx erfolgen. Auch hierbei muss sicher gestellt werden, dass der bestehende Eintrag im Feld Verweis: markiert ist, bevor der Zellbereich ausgewählt und abschließend mit Hinzufügen in das Feld Vorhandene Verweise: kopiert wird.

Abschließend gehen Sie genauso mit der Datei Muenchen_3_8_4_3.xlsx 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.

Aktivieren Sie die Option Verknüpfung mit Quelldaten – dadurch wird sicher gestellt, dass sich die konsolidierte Tabelle aktualisiert, wenn sich die Ausgangstabellen verändern. Nun können Sie Ihre Einstellungen mit OK bestätigen.

Verweis

Die konsolidierte Tabelle wird nun angezeigt.

Verweis

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 und nicht in einem Fall abweichend Schoko.

TIPP

Wenn die Option Verknüpfung mit Quelldaten bei der Erstellung der konsolidierten Tabelle aktiviert wurde, kann die Zusammensetzung und Herkunft der einzelnen Werte eingesehen werden. Dazu nutzen Sie bitte den oberen Bereich der Gliederungsleiste, die nun am linken Bildschirmrand angezeigt wird. Wenn Sie hier auf die kleine Schaltfläche mit der Nummer “2″ klicken, werden in einer extra Spalte innerhalb der Tabelle die einzelnen Werte sowie deren Herkunftsdatei angezeigt.

Verweis

SCHRITT FÜR SCHRITT

Sie möchten nun in der Tabelle Eiscreme_Verkauf_3_8_4_3.xlsx die Umsatzzahlen von Berlin, Frankfurt und München konsolidieren.
Öffnen Sie die Dateien Eiscreme_Verkauf_3_8_4_3.xlsx,
Berlin_3_8_4_3.xlsx,
Frankfurt_3_8_4_3.xlsx und
Muenchen_3_8_4_3.xlsx, um die folgenden Schritte besser nachvollziehen zu können.

1.    Wechseln Sie in der Datei Eiscreme_Verkauf_3_8_4_3.xlsx auf das Arbeitsblatt Quartal 1 und klicken Sie in die Zelle A4.

2.    Klicken Sie nun auf die Schaltfläche Verweis innerhalb der Gruppe Datentools im Register Daten.

3.    Stellen Sie sicher, dass im Feld Funktion: Summe aufgeführt ist und klicken Sie in das Feld Verweis.

4.    Wechseln Sie in die Datei Berlin_3_8_4_3.xlsx 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.    Stellen Sie sicher, dass das Feld Excel-Verweis: noch komplett markiert ist und verfahren Sie analog für die beiden anderen Dateien, deren Daten Sie konsolidieren wollen.

8.    Abschließend aktivieren Sie im Bereich Beschriftung aus: die Optionen Oberster Zeile und Linker Spalte sowie die Option Verknüpfungen mit Quelldaten und klicken Sie auf OK Verweis.

Die Tabelle mit den konsolidierten Daten wird nun in der Datei Eiscreme_Verkauf_3_8_4_3.xlsx angezeigt; die konsolidierten Daten werden automatisch aktualisiert, wenn sie sich in den Quelldateien ändern..
Wenn Sie möchten, können Sie Ihr Ergebnis mit der Musterlösung vergleichen. Rufen Sie dazu die Datei Eiscreme_Verkauf_3_8_4_3_e.xlsx auf.

UND JETZT SIND SIE DRAN!

Nutzen Sie die Dateien Hamburg_3_8_4_3_u.xlsx,
Nuernberg_3_8_4_3_u.xlsx und
Koeln_3_8_4_3_u.xlsx,
um in der Datei Verkaufszahlen_3_8_4_3_u.xlsx eine zusammenfassende Tabelle für die Umsatzzahlen des ersten Quartals zu erstellen. Fügen Sie dazu eine sich aktualisierende Konsolidierungstabelle ein.
Das Resultat sollte wie in der DateiVerkaufszahlen_3_8_4_3_ue.xlsx aussehen:

Verweis

Sie können Ihr Ergebnis mit der Musterlösung Verkaufszahlen_3_8_4_3_ue.xlsx vergleichen.

TIPP

Sollten Sie in manchen Zellen anstelle von Zahlen Rauten sehen, müssen Sie die Spaltenbreite entsprechend vergrößern.

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

*