Tabellenblattnamen - Excel Hands on - Blog - HSC-Consulting, Controlling, Excel & Coaching

Direkt zum Seiteninhalt

Hauptmenü:

Excel, Formelzauber beim Konsolidieren

Herausgegeben von in Excel Standard ·
Tags: 3DFormelnTabellenblattnamenmitStellvertreterzeichenansprechen
 
Zaubern Sie in 3D-Summenformeln
 
In einem Projekt wurde die Frage gestellt: "sind Stellvertreterzeichen auch in  Auswertungen von Tabellenregisternamen möglich?"
 
Bisher war mir noch nie in den Sinn gekommen, die Summenbildung in 3D-Summen (Konsolidierung von mehreren Tabellenblättern) anzuwenden.
 
Eine durchdachte Architektur vorausgesetzt, bringt die Verwendung von Stellvertreterzeichen ("?" und "*") durchaus sinnvolle Anwendungsvarianten. Bei sehr umfangreichen Mappen erzielen Sie auch spürbare Zeitersparnisse beim Formelbaufbau.
 
 
Klassische Summe zur Konsolidierung mehrerer Tabellenblätter - 3D-Summe
 
Als Controller sind Sie mit der Aufgabe Summen über mehrerer Tabellenblätter zu bilden sehr vertraut. Das typische Beispiel, Sie bilden die Summe eines Artikels beispielsweise über die Monate Jan, Feb, Mrz und schreiben das Ergebnis in das gleiche Modell in Tabellenblatt "Q1"
(vgl. Abbildung 1.27).

Abbildung 1: Die Struktur der Mappe mit dem ersten Datenblatt 'Jan'


Sie werden das wahrscheinlich mit folgender Arbeitsweise und Formel erledigen:

In Zelle C5 des Tabellenblattes Q1 geben Sie ein:

=Summe(
Wechsel ins Tabellenblatt Jan und aktivieren dort Zelle C5
drücken und  halten dann die Umschalt-Taste
Aktivieren das Tabellenblatt Mrz und
klicken dort wieder auf die Zelle C5
drückenSie die Eingabe-Taste.
Aus dieser Aktion entsteht folgende Formel:  =SUMME('Jan:Mrz'!C5)
Die als Ergebnis den Wert 1050 berechnet.
So haben Sie die Formel auf einfache Weise eingegeben und das Ergebnis berechnet.

 
Selektive Auswahl von Tabellenblättern
 
So einfach wie im vorhergehenden Beispiel wird es aber nicht, wenn Sie beispielsweise eine sehr umfangreiche Mappe mit unterschiedlicher Kostenstellenstruktur konsolidieren wollen (vgl. Abb.).
 
Beispielsweise sollen alle 2-stelligen Kostenstellen, unabhängig vom Buchstaben summiert werden.
Ausgehend von dieser Forderung wird der Aufbau der Formel schon zu einer kleinen Herausforderung (und ab 50 Tabellenblättern auch zu einer sehr großen Herausforderung, wenn Sie das zügig und fehlerfrei zu erledigen wollen). Einmal falsch geklickt oder ein Semikolon vergessen und alles fängt von vorne an.
 
 
Der Summenzauber
 
Im Musterbeispiel bilden Sie die Summe aller 2-stelligen Kostenstellen (Ziffern), die mit » L « beginnen.
 
 
Wenn Sie die Stellvertreterzeichen "?" und "*" verwenden gelten die üblichen Regeln, Stern (*) ersetzt alle folgenden Zeichen und das Fragezeichen (?) ersetzt genau die Position.
 
 
Für die Fragestellung lautet die Lösungsformel:
 
=SUMME('L??'!C5)
 
Das Ergebnis in der Beispielmappe beläuft sich auf den Wert 1650 (vgl. Abbildung 1.28).

Abbildung 2: Das Ergebnis der Formel in Zelle C5 des Tabellenblattes Summe
 

 
Die eingegebene Formel wird von Excel umgewandelt und lautet nach der Umwandlung:
 
 
=SUMME('L10:L20'!C5;'H20:H30'!C5)
 
 
Der Weg über die Stellvertreterzeichen eröffnet neue Möglichkeiten für eine geschickte und effizente Formeleingabe und erspart sicherlich auch eine Menge Frust bei der Eingabe derartiger Formeln.
 
Nachfolgend einige Lösungsbeispiele:

Abbildung 3: Eine Tabelle mit verschiedenen Beispielformeln (immer bezogen auf des Ergebnis in Zelle C5)


Achtung:
Bei = SUMME('???'!c5), also drei Fragezeichen als Stellvertreterzeichen, werden neben den passenden Kostenstellen auch die Blätter der Monate Jan, Feb, Mrz (vorausgesetzt sie befinden sich inder gleichen Mappe) wegen der Dreistelligkeit der Namen in die Summe einbezogen. So  auch umgekehrt, wenn Sie nur die Monate konsolidieren wollen.
Auf diese Auswirkungen ist bei der Modellkonstruktion sicherlich ein Augenmerk zu legen.
 
Ebenso werden ausgeblendete Tabellenblätter in die Konsolidierung mit einbezogen.
 
 
Hinweis:
 
Die Stellvertreterzeichen lassen sich auch in weiteren Funktionen wie beispielsweise Anzahl, Anzahl2, Mittelwert usw. einsetzen.
____
HSC




Zurück zum Seiteninhalt | Zurück zum Hauptmenü