Wie oft haben mich Seminar-Teilnehmer nach einer einfachen Möglichkeit gefragt, Summen über die Tabellenblätter einer Excel-Datei zu bilden (z.B. Kostenstellenbericht). Vor allem nach einer Variante, die es erlaubt, hinzugefügte Tabellenblätter automatisch in die Berechnung mit aufzunehmen. Im Seminar EXCEL im Controlling und Finanzwesen habe ich seit 1993 zur allgemeinen Begeisterung der Teilnehmenden dafür den 3D-Bezug vorgestellt. Ein „3D“-Bezug ist ein Bezug auf die gleiche Zelle mehrerer Tabellenblätter innerhalb einer Arbeitsmappe unter Verwendung einer Funktion.
Dreidimensionalen Bezug berechnen
Dafür benötigen wir eine Funktion, z.B. Summe() und die <Shift>-Taste. Die <Shift>-Taste wird erst dann gedrückt, wenn Sie das Register des letzten Sheets anklicken, das in Berechnung mit einbezogen werden soll.
Diese Technik ist nur dann möglich, wenn alle Blätter einer Arbeitsmappe gleichartig aufgebaut sind. In dieser Technik stecken Chance und Risiko:
- Wenn Sie Arbeitsblätter zwischen Sheet2 und dem letzten Sheet einfügen oder kopieren, schließt Excel alle Werte der hinzugefügten Arbeitsblättern in die Berechnungen ein. Damit haben Sie eine potenzielle Szenario-Technik, wenn Sie Arbeitsblätter außerhalb des referenzierten Arbeitsblattbereichs verschieben. In diesem Fall entfernt Excel deren Werte aus der Berechnung.
- Benötigen Sie aber eine „stabile“ Berechnung der Werte , dann sollten Sie dieses Verschieben, Löschen oder Kopieren unterbinden. Das könnten Sie mit dem Menü Überprüfen • Arbeitsmappe schützen erreichen. Alternativ fügen Sie zwei zusätzliche Sheets ein, die Sie „Anfang“ und „Ende“ nennen und so anordnen, dass alle zu berechnenden Blätter dazwischen positioniert sind und die Funktion (bspw.) =SUMME(Anfang:Ende!B1) berechnet. Danach werden die Sheets „Anfang“ und „Ende“ ausgeblendet.
Diese Funktionen können verwendet werden
| Beschreibung | Funktion |
| Addiert Zahlen. | SUMME |
| Berechnet den Mittelwert (arithmetisches Mittel) von Zahlen. | MITTELWERT |
| Berechnet den Mittelwert (arithmetisches Mittel) von Zahlen; enthält Text und Logisches. | MITTELWERTA |
| Zählt Zellen, die Zahlen enthalten. | ANZAHL |
| Zählt Zellen, die nicht leer sind. | ANZAHL2 |
| Fügt Arrays horizontal und nacheinander an, um ein größeres Array zurückzugeben. Gibt es nur in Excel365. | HSTAPELN |
| Sucht den größten Wert in einer Gruppe von Werten. | MAX |
| Sucht den größten Wert in einer Gruppe von Werten; enthält Text und Logisches. | MAXA |
| Sucht den kleinsten Wert in einer Gruppe von Werten. | MIN |
| Sucht den kleinsten Wert in einer Gruppe von Werten; enthält Text und Logisches. | MINA |
| Multipliziert Zahlen. | PRODUKT |
| Berechnet die Standardabweichung basierend auf einer Stichprobe. | STABW |
| Berechnet die Standardabweichung basierend auf einer Stichprobe; enthält Text und Logisches. | STABWA |
| Berechnet die Standardabweichung einer Grundgesamtheit. | STABWN |
| Berechnet die Standardabweichung einer Grundgesamtheit; enthält Text und Logisches. | STABWNA |
| Schätzt die Varianz auf der Basis einer Stichprobe. | VARIANZ |
| Schätzt die Varianz auf der Grundlage einer Stichprobe; enthält Text und Logisches. | VARA |
| Berechnet die Varianz für eine gesamte Grundgesamtheit. | VARIANZEN |
| Berechnet die Varianz für eine grundgesamte Grundgesamtheit; enthält Text und Logisches. | VARIANZENA |
| Fügt Arrays vertikal und sequenziert an, um ein größeres Array zurückzugeben. Gibt es nur in Excel365. | VSTAPELN |
Power Query
Dank Power Query gibt es seit 10 Jahren eine weitere Möglichkeit. Die ist eigentlich „robuster“ und wird in diesem Beitrag erläutert. 🙂
