Beim Tauchen durch die Tiefen des Internets kann man auch auf wahre Excel-Perlen stoßen. Eine dieser Perlen fand ich bei einem Excel-Blogger aus Hong Kong.
Ming Fung Wong, so der Name des Bloggers, hat eine fast „magische“ Anwendung der bekannten SUMME-Funktion beschrieben, die mir völlig neu war und die ich auch dir nicht vorenthalten möchte.
Wer Arbeitsmappen nutzt, die viele einzelne Arbeitsblätter enthalten, wird diese Funktion zukünftig nicht mehr missen wollen.
Und so geht’s:
„Normale“ 3D-Summe
Viele Analysten und Controller kennen das Szenario: Umsatzanalysen, Forecasts und Budgets können schnell zu umfangreichen Excel-Dateien führen. Um einigermaßen den Überblick über die Zahlenfriedhöfe zu bewahren, werden die Daten üblicherweise auf einzelne Arbeitsblätter unterteilt, wie etwa in diesem Beispiel:
Aufteilung nach Monaten
Idealerweise sorgt man von Anfang an für einen identischen Aufbau der Arbeitsblätter, so dass man anschließend sehr einfach Summen bilden kann, die sich über mehrere Arbeitsblätter erstrecken. Sogenannte 3D-Summen:
Summe über mehrere Arbeitsblätter
Das ist noch einigermaßen praktisch, wenn es sich um einige wenige und unmittelbar aufeinanderfolgende Arbeitsblätter handelt, die in der SUMME-Funktion referenziert werden sollen. So addiert folgende Formel die Zellinhalte von B2 auf den Blättern „Jan“, „Feb“ und „Mär“.
=SUMME(Jan:Mär!B2)
Diese Funktion ist nicht neu und wird dich wahrscheinlich auch nicht vom Hocker reißen.
Deutlich umständlicher (und damit fehleranfälliger) wird es, wenn sich zwischendrin Arbeitsblätter befinden, die nicht summiert werden sollen. Dann kann das Ganze schnell zur Klick- und Tipporgie ausufern:
Umständliche Summenbildung
Einmal falsch geklickt oder das Semikolon vergessen, und schon kann man wieder von vorne beginnen.
Jetzt kommt die „magische“ Summenfunktion!
Summen-Magie
Was mir bisher völlig neu war: Man kann in der SUMME-Funktion auch die üblichen Jokerzeichen ‚*‘ und ‚?“ verwenden. Dabei steht der Stern für eine beliebige Anzahl von Zeichen, das Fragezeichen hingegen steht jeweils für genau ein Zeichen.
In meinem Beispiel habe ich die Arbeitsblätter nach den einzelnen Monaten benannt, und zwar jeweils genau 3 Zeichen lang: Jan, Feb, Mär usw. Dazwischen liegen die Quartalsblätter Q1, Q2, Q3 und Q4.
Um nun eine 3D-Summe über alle Monatsblätter zu bilden, tippe ich folgende Funktion in Zelle B2 ein:
=SUMME('???'!B2)
Die Magie passiert, sobald man die Formel mit der Eingabe-Taste abschließt. Dann ersetzt Excel die drei Fragezeichen durch alle Arbeitsblätter, deren Namen genau drei Zeichen lang sind!
Summen-Magie im Einsatz
Wahnsinn, oder?
Um z.B. alle Quartalsblätter zu addieren – die Blattnamen beginnen mit dem Buchstaben Q -, können wir auch den Stern als Jokerzeichen verwenden:
=SUMME('Q*'!B2)
Das heißt im Klartext: Summiere die Zelle B2 über alle Blätter, deren Name mit Q beginnt:
Noch mehr Magie
Auf diesem Weg lässt sich Frust und viele Eingabefehler vermeiden, die Grenze setzt nur deine Fantasie.
Warnung:
Wenn man mit den Jokerzeichen arbeitet, werden auch etwaige ausgeblendete Tabellen mit in die Berechnung einbezogen, sofern sie in das Muster passen. Etwas Vorsicht ist also angebracht.
Übrigens:
Die beiden Joker lassen sich nicht nur in der SUMME-Funktion einsetzen. Auch in anderen Funktionen, wie ANZAHL, MIN, MAX usw. steckt die gleiche Magie!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo,
das ist wirklich ein Knüller für die Anwendung,
Danke
Dieter
Hallo Martin,
Danke für den interessanten Beitrag. Das ist ja eine geniale und doch so einfache Formel. Und sie funktioniert auch noch. Ich habe wieder dazu gelernt.
Viele Grüße
Gerhard
Hallo Gerhard,
gern geschehen, ich war auch ganz begeistert
Schöne Grüße,
Martin
Grandios!
Das meint man schon vieles zu kennen und dann das! 🙂 🙂
Herzlichen Dank!
Hallo Martin,
diese Funktion war mir ebenfalls neu, und ist einfach genial, und wieder mal sehr anschaulich erklärt.
wobei mir die gleiche Funktion zu MIN und MAX nicht ganz klar ist. Hättest du ein kurzes Beispiel dazu?
Schon jetzt recht herzlichen Dank
und liebe Grüße
Roswitha
Hallo Roswitha,
der gleiche Trick lässt sich anwenden, wenn Du z.B. das Minimum oder Maximum über mehrere Arbeitsblätter berechnen möchtest. Dieser Anwendungsfall ist sicherlich nicht so häufig, wie die Summe, aber man weiß ja nie.
Mit der folgenden Formel berechnet man z.B. den kleinsten Wert in A1 aller Arbeitsblätter, deren Namen zwei Zeichen lang ist:
=MIN(‚??‘!A1)
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank, für die kurze Ausführung, jetzt kann ich die Funktion nachvollziehen.
Liebe Grüße
Rosiwitha
Hallo Martin
Wirklich eine coole Formel, die auch mich mit bald Jahren 40 Excelerfahrung erstaunt!
Eine kleine Ergänzung wäre aber doch noch anzubringen. Die Formel berücksichtigt, wie bei dir schon beschrieben, zwar ausgeblendete Blätter, aber nicht das Blatt selber, in welcher die Formel steht! Dies wird wahrscheinlich weniger notwendig sein,
da die Formel eher in Zusammenstellungen benützt wird – aber man weiss ja nie…
Besten Dank für die vielen tollen Tipps mit den ausgiebigen Erläuterungen
Grüsse
Adrian
Hallo Adrian,
vielen Dank für deinen berechtigten Hinweis, dieses Verhalten ist mir bisher tatsächlich noch gar nicht aufgefallen.
Schöne Grüße,
Martin
Hallo,
das Thema ist ja echt schon was älter, aber ich bin jetzt erst auf die Möglichkeit von 3D-Bezügen gestoßen. Allerdings benötige ich eine andere Funktionsweise, denn ich möchte nicht alle Blätter summieren oder sonstige kalkulationen durchführen, sondern ganz simpel verweisen. Also im Prinzip einen Blattnamen als Kriterium eingeben, ähnlich wie die Zeilen- oder Spaltenkriterien beim X-Verweis. Mir ist bewusst, dass ich das Thema auch über die INDIREKT-Funktion lösen kann, aber das möchte ich aufgrund ihrer volatilen Eigenschaft unbedingt vermeiden, das die Funktion für Umsatz-Berechnungen sämtlicher lagerlogistischen Vorgänge täglich herangezogen wird – da sammelt sich schnell eine hohe Anzahl volatil berechneter Zellen an und die Datei wird unglaublich langsam.
Gibt es hier dynamische Möglichkeiten ähnlich der bestehenden 3D-Bezüge?
Hallo Julius,
habe ich dich richtig verstanden, du willst also einen Blattnamen dynamisch in einer Formel austauschen? Außer über die INDIREKT-Funktion ist mir hier keine andere Variante bekannt.
Da aber offensichtlich die Performance in deinem Szenario eine große Rolle spielt, werfe ich mal ein paar Fragen in den Raum:
Könntest du die verschiedenen Quelltabellen nicht mit Hilfe von Power Query zu einer „Mastertabelle“ zusammenführen und dann ohne Umwege darauf zurückgreifen?
Oder vielleicht sogar mit Pivot-Tabellen auswerten, was von der Performance ohnehin unschlagbar wäre?
Schöne Grüße,
Martin