“Magische” Summenfunktion 7

Ein Trick in der SUMME-Funktion kann viel Arbeit ersparen!
 

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

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

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

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

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

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!

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

7 Gedanken zu ““Magische” Summenfunktion

  • Gerhard Pundt

    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

  • Roswitha

    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

    • Martin Weiß Beitragsautor

      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