Tipps und Tricks rund um Excel

A	B	C	D	E	F	G	H	I/J	K
L	M	N	O	P/Q	R	S	T	U-W	X-Z

 

Bezug mit Blatt- und Zellnamen

In einer Datei befinden sich pro Monat jeweils ein Tabellenblatt sowie ein Tabellenblatt mit der Zusammenfassung. Ziel ist es, die Formeln für die Zusammenfassung zeitsparend zu erstellen.

Excel soll vom Blatt "Januar" die Zelle mit den Namen "Summe" hier anzeigen. Genauso soll das für die Zelle mit dem Namen "Abzug" gelten. Und das Ganze natürlich für alle Monate.

Dabei will ich nur eine Formel erstellen müssen, die sich kopieren lässt. Realisiert habe ich das mit

• Namen für die Zellen in den Monatsblättern
• der Funktion INDIREKT

 

Zellennamen auf den Monatsblättern

Links sehen Sie das Tabellenblatt für den Januar, der Blattname ist "Januar". Zelle B33 soll den Namen "summe", Zelle C33 den Namen "Abzug" erhalten. Markieren Sie Zelle B33 und rufen Sie "Formeln / Namen definieren" auf.
Tippen Sie den Zell-Namen "summe" ein, bestimmen Sie als Bereich das Tabellenblatt "Januar" - nicht die gesamte Arbeitsmappe! Kontrollieren Sie, dass er sich auf die richtige Zelle bezieht.

Fahren Sie fort für die Zelle C33 und vergeben Sie den Namen "abzug" - ebenfalls für das Tabellenblatt Januar. Dann machen Sie für die übrigen Tabellenblätter so weiter. Die Namen heißen immer "summe" und "abzug" und beziehen sich immer auf das Tabellenblatt, nicht auf die Arbeitsmappe.

Im "Namensmanager" sehen Sie alle Namen, die Sie in der Arbeitsmappge verwenden und worauf sich die Namen beziehen. Es muss immer so sein, dass pro Blatt (Januar, Februar etc.) ein Name für Summe und Abzug vorhanden ist.

 

Zusammenfassung mit INDIREKT

Auf dem Blatt "Zusammenfassung" erstellen Sie zuerst das Grundgerüst - in die Spalte A schreiben Sie die Monatsnamen, in die Zeile 1 schreiben Sie "Summe" und "Abzug". Die Spaltenbeschriftung muss mit den Namen, die Sie vergeben haben, übereinstimmen. Allerdings stört sich Excel nicht an unterschiedlicher Groß- und Kleinschreibung.

Das ist das Grundgerüst. Beginnen Sie in B2 mit der Formel für die Summe aus dem Blatt "Januar".
Damit sich die Formel gut kopieren lässt, werden einige Bezüge absolut gesetzt.

Die erste Formel lautet:

=INDIREKT($A2&"!"&B$1)

• In A2 steht der Blattname, also "Januar"
• mit &"!" wird in das trennende Ausrufezeichen zwischen Blattname und Zellbezug eingesetzt
• In B1 steht der Zellenname, also "Summe"
• Die Formel setzt also zusammen:
  =Januar!Summe
• Damit beim Kopieren nach unten der Bezug immer auf die erste Zeile weist, wird in B1 die Zeile fixiert: B$1
• Damit beim Kopieren nach rechts der Bezug immer auf die Spalte A weist, wird in A2 die Spalte fixiert: $A2

Nach dem Ausfüllen nach unten und nach rechts, sehen die Formeln so aus:

 

Die Spalte D mit dem Rest ist eine simple Berechnung =B2-C2

 

30.05.2013