Excel-Tabellen verbinden – 3-D Kalkulationen

DARUM GEHT’S

Sie haben in einer Datei mehrere Tabellen angelegt, welche die Umsatzzahlen verschiedener Eiscreme-Sorten mehrerer Standorte für Januar bis März auflisten. Sie haben die Umsatzzahlen pro Monat auf jeweils einem Arbeitsblatt aufgeführt, d. h. es gibt ein Arbeitsblatt für Januar, ein weiteres für Februar und ein drittes für März. Auf allen drei Arbeitsblättern sind die Tabellen an der gleichen Position und mit dem gleichen Layout angeordnet.

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

Da die drei Ausgangstabellen die gleiche Form und das gleiche Layout haben, können Sie dazu die so genannte 3-D Kalkulation anwenden. Diese erlaubt Berechnungen mittels identischer Zellbezüge über mehrere Arbeitsblätter hinweg.

Wie Sie in Excel Tabellen verbinden können, erfahren Sie im folgenden Lernobjekt.

SO WIRD’S GEMACHT

Die Funktion Konsolidieren ermöglicht es Ihnen, Daten, die auf verschiedenen Arbeitsblättern stehen, zu addieren. Dabei werden jeweils die Werte gleicher Zellen summiert, somit können Sie in Excel Tabellen verbinden.

Um eine Summenkalkulation über mehrere Arbeitsblätter hinweg einzugeben, öffnen Sie das Arbeitsblatt, auf dem die konsolidierte Tabelle entstehen soll. In diesem Beispiel möchten Sie die Daten der Arbeitsblätter Januar, Februar und März auf dem Arbeitsblatt Quartal 1 zusammenführen. Als erstes möchten Sie die Umsätze der Sorte “Schokolade” für den Standort Berlin ermitteln. Klicken Sie in die entsprechende Zelle B5 und wählen Sie dann die Schaltfläche  aus, die sich innerhalb der Gruppe Bearbeiten im Register Start befindet.

Daraufhin wird die Summenfunktion =SUMME() in die Zelle eingefügt.

Es müssen jetzt noch die Zellbezüge eingegeben werden. Da in diesem Fall die zu konsolidierenden Daten alle in der gleichen Zelle vorhanden sind (B5), können Sie mit einem 3-D-Bezug arbeiten. Ein 3-D-Bezug verweist in mehreren Arbeitsblättern jeweils auf den gleichen Zellbereich. Dazu klicken Sie zunächst auf den Reiter des Arbeitsblattes Januar, anschließend klicken Sie bei gleichzeitig gedrückter [Shift]-Taste auf den Reiter des Arbeitsblattes März. Nachdem Sie die [Shift]-Taste wieder losgelassen haben, wird nun das Arbeitsblatt Januar angezeigt. In dessen Formelleiste können Sie die noch unvollständige Zellreferenz =SUMME(Januar:März!) einsehen. Dabei stellt der Ausdruck “Januar:März!” den Bereich der Arbeitsblätter von Januar bis März dar. Um den noch fehlenden Bezug zu der Zelle herzustellen, die aufaddiert werden soll, klicken Sie auf dem Arbeitsblatt Januar in die Zelle B4 und bestätigen die Eingabe mit der [Enter]-Taste. Auf dem Arbeitsblatt Quartal 1 wird nun in Zelle B5 der konsolidierte Wert für die ersten drei Monate des Jahres von Schokoladen-Eiscreme in Berlin angezeigt.

TIPP

Anstatt die folgenden Zellbezüge für die restlichen Eiscreme-Sorten und Standorte manuell einzugeben, können Sie auch die AutoAusfüllen-Funktion benutzen. Dazu positionieren Sie den Mauszeiger an der unteren rechten Ecke der Zelle B5 so, dass dieser die Form eines schwarzen Kreuzes annimmt. Nun ziehen Sie mit gedrückter Maustaste den Zeiger bis auf Zelle B8. Die Zellbezüge werden ausgefüllt und die Werte für die anderen Eiscreme-Sorten sowie der Gesamtwert angezeigt. Die Daten für die restlichen Spalten können Sie ebenfalls mittels der AutoAusfüllen-Funktion ausfüllen, indem Sie die Daten für die Spalte Berlin komplett markieren und den gesamten Bereich durch Ziehen mit der Maus nach rechts übertragen.

ACHTUNG

Die Nutzung der AutoAusfüllen-Funktion ist nur dann möglich, wenn die betreffenden Tabellen identisch aufgebaut sind.

ACHTUNG

Sie werden bemerken, dass Sie bei Nutzung der AutoAusfüllen-Funktion die Formatierung der Zeile überschreiben. Um dies rückgängig zu machen, klicken Sie, nachdem Sie die Werte bis in Zelle B7 eingefügt haben, auf das SmartTag , welches unterhalb des aufgefüllten Bereiches erscheint. Aus dem daraufhin angezeigten Menü wählen Sie  aus.

Aufgrund der von Ihnen festgelegten Zellbezüge enthält Ihre Tabelle nun stets die aktuellen, konsolidierten Umsatzzahlen für das erste Quartal.

SCHRITT FÜR SCHRITT

Sie möchten nun in Excel diese Tabellen verbinden, um die Umsatzzahlen der Monate Januar bis März in einer Tabelle zusammenzuführen. Öffnen Sie die Datei Eiscreme_Verkauf_3_8_4_1.xlsx, um die folgenden Schritte besser nachvollziehen zu können.

1. Stellen Sie zunächst sicher, dass Sie sich auf dem Arbeitsblatt Quartal 1 befinden.

2. Wählen Sie die Zelle B5 aus und klicken Sie auf die Schaltfläche , die sich innerhalb der Gruppe Bearbeiten im Register Start befindet.

3. Stellen Sie sicher, dass sich der Cursor zwischen den Klammern innerhalb der Summenfunktion befindet: .

4. Klicken Sie auf den Reiter des Arbeitsblattes Januar, halten Sie danach die [Shift]-Taste gedrückt und klicken Sie gleichzeitig auf den Reiter des Arbeitsblattes März.

5. Lassen Sie die [Shift]-Taste wieder los und überprüfen Sie, dass in der Formelleiste die noch unvollständige Funktion als =SUMME(Januar:März!) angezeigt wird.

6. Klicken Sie auf dem Arbeitsblatt Januar in Zelle B4 und bestätigen Sie Ihre Eingabe mit der [Enter]-Taste.

7. Die Ansicht zeigt nun wieder das Arbeitsblatt Quartal 1. Wählen Sie erneut die Zelle B5 aus und positionieren Sie Ihren Mauszeiger an der unteren rechten Ecke der Zelle, so dass er als schwarzes Kreuz angezeigt wird.

8. Ziehen Sie Ihren Mauszeiger mit gedrückter linker Maustaste bis auf Zelle B8.

9. Lassen Sie die Maus los – die Zellen B5 bis B8 zeigen jetzt die konsolidierten Daten für Berlin an.

10. Klicken Sie nun auf den SmartTag, welches unterhalb des aufgefüllten Bereiches erscheint .

11. Aus dem daraufhin angezeigten Menü wählen Sie bitte .

12. Markieren Sie die Zellen B5 bis B8 und positionieren Sie Ihren Mauszeiger auf der unteren rechten Ecke des markierten Bereiches, so dass er als schwarzes Kreuz angezeigt wird.

13. Ziehen Sie Ihren Mauszeiger mit gedrückter linker Maustaste nach rechts bis in die Spalte F.

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

Das Ergebnis sollte wie folgt aussehen:

UND JETZT SIND SIE DRAN!

Öffnen Sie die Datei Verkaufszahlen_3_8_4_1_u.xlsx, um auf dem Arbeitsblatt Quartal 1 eine konsolidierte Tabelle für die Umsatzzahlen des ersten Quartals zu erstellen. Fügen Sie dazu eine 3-D Kalkulation ein.

Sie können Ihr Ergebnis mit der folgenden Datei vergleichen:

Verkaufszahlen_3_8_4_1_ue.xlsx. 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

*