Excel: Verschiedene Arbeitsblätter per Formel ansteuern

Zeitersparnis und Flexibilität beim Kopieren von Formeln sind stets Ziel beim Erstellen komplexerer Kalkulationstabellen. Zudem sollen auch ungeübtere Mitarbeiter oder Kollegen in der Lage sein, beim Erweitern von Arbeitsblättern Formeln ohne Anpassung kopieren zu können. Hier zeigen wir Ihnen, wie Sie mit einer kopierbaren Formel Daten aus verschiedenen Arbeitsblättern ansteuern können. Sie müssen dazu den Blattnamen lediglich in eine Zelle schreiben.

Das Szenario
Eine Mappe enthält vier gleich aufgebaute Arbeitsblätter mit Quartalsdaten, je ein Blatt pro Quartal. Die Blätter sind mit „Quartal 1“ bis „Quartal 4“ beschriftet.

Arbeitsblaetter_01

In einem Gesamtblatt sollen die Gesamtergebnisse der Einzelblätter aus B9:D9 zusammengetragen werden. Der Tabellenaufbau ist ähnlich aber nicht gleich. Als Zeilenbeschriftungen sind in A4:A7 die Blattnamen der Quartalsblätter eingetragen.

Arbeitsblaetter_02

Ziel ist es, eine Formel in B4 einzutragen, die im optimalen Fall in den ganzen Bereich B4:D7 kopiert werden kann.

Eine einfachere Formelvariante
Generell bietet sich die Funktion INDIREKT an, um Daten aus verschiedenen Blättern auszulesen. Mit ihr wird eine Zelladresse, die man zunächst in Textform konstruiert, in einen echten Bezug übersetzt. Sofern dieser Bezug existiert, liest die INDIREKT den Eintrag aus der konstruierten Zellkoordinate aus. Geht es um den Umsatz aus dem Quartal 1, dann wird folgender Bezug in Textform benötigt:
=’Quartal 1′!B9
Beachten Sie hierbei die Hochkommata, die den Beginn und das Ende des Blattnamens anzeigen. Diese müssen in der Schreibweise enthalten sein, wenn der Blattname Leerzeichen enthält! Eine einfache – wenn auch noch nicht völlig flexible – Lösung wäre die Konstruktion des Zellbezugs mit der Formel
=“‚“ & $A4 & „‚!B9“
(Ein Hochkomma wird verkettet mit dem Wert aus der Zelle A4 des Arbeitsblattes, dies wiederum mit einem Hochkomma, einem Ausrufezeichen und dem festen Text „B9“.)
Als endgültige Formel ergibt sich:
=INDIREKT(„‚“ & $A4 & „‚!B9“)
Diese Formel lässt sich nach unten kopieren. Beim Kopieren nach rechts muss allerdings nachträglich der Text „B9“ auf „C9“ und „D9“ geändert werden, bevor man wiederum nach unten kopiert.

Die volle Flexibilität
Eleganter wird es, wenn die Formel auch für das Ausfüllen nach rechts flexibel reagiert. Hierzu wird der Zellbezug mithilfe der Funktion ADRESSE erzeugt:

Arbeitsblaetter_03
Im Screenshot des Assistenten wird sichtbar, wie sich der Bezug aufbaut. Um die Spalte flexibel zu halten, wird diese mit der Funktion SPALTE() erzeugt. Diese liefert, wenn die Klammern nicht ausgefüllt werden, die Spaltennummer der aktiven Zelle – also „2“, da die Zelle B4 ausgewählt ist. Das Argument „Abs“=4 gibt an, dass ein relativer Bezug ohne $-Zeichen geliefert wird, was hier aber nicht zwingend nötig ist. Mit INDIREKT wird die Zelladresse wieder in einen echten Bezug übersetzt. Es ergibt sich somit als Formel für die Zelle B4:

=INDIREKT(ADRESSE(9;SPALTE();4;;$A4))
Diese lässt sich über den gesamten Auswertungsbereich kopieren und liefert folgendes Ergebnis:
Arbeitsblaetter_04

Weitere Tipps zum Arbeiten mit Excel erhalten Sie in unserem Seminar „Excel – Formeln und Funktionen„. Wir freuen uns auf Sie!

Beitrag drucken

Hinterlasse jetzt einen Kommentar

Kommentar hinterlassen