„Magische“ Summenfunktion 11

Artikelbild-117-neu
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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

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

11 Gedanken zu “„Magische“ Summenfunktion

  • Avatar-Foto
    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

  • Avatar-Foto
    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Roswitha

    Hallo Martin,
    vielen Dank, für die kurze Ausführung, jetzt kann ich die Funktion nachvollziehen.
    Liebe Grüße
    Rosiwitha

  • Avatar-Foto
    Adrian Stöckli

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Julius

    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?

    • Avatar-Foto
      Martin Weiß

      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