Microsoft Excel

Herbers Excel/VBA-Archiv

=SUMME(INDIREKT(ADRESSE(...

Betrifft: =SUMME(INDIREKT(ADRESSE(... von: Kulo
Geschrieben am: 28.10.2020 20:31:20

Hallo liebe Excelianer,

ich habe in einer Arbeitsmappe drei Tabellenblätter mit je 12 Spalten für die Monate eines Jahres.
In einem vierten Tabellenblatt sind die Monate der drei Jahre hintereinander aufgelistet. In diesen Spalten soll die Summe aus Zeile 3, 4 und 5 der Spalte des jeweiligen Monats der entsprechenden Tabellenblätter mit den einzelnen Jahreswerten errechnet werden.

Ich experimentiere mit Summe, Indirekt und Adresse.

Meine Formel lautet:
=SUMME(INDIREKT(ADRESSE(3;MONAT(B$1)+1;;;JAHR(B$1))&":"&ADRESSE(5;MONAT(B$1)+1)))
Sie berechnet was ich will. Aber irgendwie sieht das komisch aus.

Gibt es da eine Vereinfachung? Vielleicht auch ein ganz anderer Ansatz?

Ich würde mich freuen, wenn mal jemand über meine angehängte Datei schauen würde.

https://www.herber.de/bbs/user/141157.xlsm

Viele Grüße
Kulo

Betrifft: AW: =SUMME(INDIREKT(ADRESSE(...
von: onur
Geschrieben am: 28.10.2020 21:58:58

=SUMME(INDIREKT(JAHR(Z1S)&"!Z3S" & SPALTE()&":Z5S" & SPALTE();FALSCH))


Betrifft: AW: =SUMME(INDIREKT(ADRESSE(...
von: onur
Geschrieben am: 28.10.2020 22:07:18

ODER NOCH KÜRZER:
=SUMME(INDIREKT(JAHR(Z1S)&"!Z3S:Z5S";FALSCH))


Betrifft: AW: =SUMME(INDIREKT(ADRESSE(...
von: onur
Geschrieben am: 28.10.2020 22:15:12

Sorry, fehler gefunden.
=SUMME(INDIREKT(JAHR(Z1S)&"!Z3S"&MONAT(Z1S)+1&":Z5S"&MONAT(Z1S)+1;FALSCH))

Betrifft: AW: eine einfache Möglichkeit ohne INDIREKT() ..
von: neopa C
Geschrieben am: 29.10.2020 09:30:50

Hallo Kulo,

... gibt es. Dies setzt jedoch voraus, dass Du zumindest Dein erstes Tabellenblatt umbenennst, weil ein Zahl als Tabellenblsttname für folgendes nicht geeignet ist.

Wenn Du das Tabellenblatt "2020" z.B. zu "J2020" umbenennst, reicht in Deinem Fall einfach nur:

=SUMME('J2020:2022'!B3) und diese nach rechts kopieren.

Gruß Werner
.. , - ...

Betrifft: AW: eine einfache Möglichkeit ohne INDIREKT() ..
von: onur
Geschrieben am: 29.10.2020 10:38:07

Wird wohl nicht gehen, da er die Formel 35 Spalten nach rechts kopieren muss, und deswegen auch den Monat/ die Spalte nur indirekt berechnen kann.

Betrifft: AW: eine einfache Möglichkeit ohne INDIREKT() ..
von: onur
Geschrieben am: 29.10.2020 10:38:08

Wird wohl nicht gehen, da er die Formel 35 Spalten nach rechts kopieren muss, und deswegen auch den Monat/ die Spalte nur indirekt berechnen kann.

Betrifft: AW: geht schon ...
von: neopa C
Geschrieben am: 29.10.2020 11:01:45

Hallo onur,

... ab Spalte N muss dann eben lediglich die Formel =SUMME('J2020:2022'!B4) und ab Spalte Z eben
=SUMME('J2020:2022'!B5) eingesetzt werden.


Gruß Werner
.. , - ...

Betrifft: AW: geht schon ...
von: Kulo
Geschrieben am: 29.10.2020 20:31:20

Hallo Onur, Hallo Werner,

vielen Dank, dass Ihr Euch meiner Frage gewidmet habt.

Werners Lösung entspricht leider nicht meinen Vorstellungen. Die Umbenennung des Tabellenblattes ist kein Problem. Doch diese Formel rechnet drei Monatswerte der einzelnen Jahre in der gleichen Zeile zusammen und nicht die drei untereinanderstehenden Werte pro Monat.

Onurs Lösung funktioniert, wenn mann in den Optionen von Excel bei Formeln die Z1S1-Bezugsart festgelegt hat.
Die Angabe von Falsch als Argument bei der Indexformel scheint nicht das in Klammen stehende bei Jahr(...) und Monat(...) als Z1S1-Bezugsart zu definieren, sondern nur das Ergebnis aus dieser Teilberechnung.
Ich habe daher bei Jahr(...) und Monat(...) die A1-Schreibweise verwendet. Der Rest der Formel wird ja dann durch die Angabe von "Falsch" in der Indexformel als Z1S1-Bezugsart definiert:

=SUMME(INDIREKT(JAHR(B$1)&"!Z3S"&MONAT(B$1)+1&":Z5S"&MONAT(B$1)+1;FALSCH))

Nachdem ich mir die Funktion der Indirekt-Formel nochmal genauer angeschaut habe, denke ich, dass der Ansatz von mir dem von Onur ähnelt. Blos eben ein anderer Weg. Die Formel selbst sieht genauso "komisch" aus. Dies ist aber der Funktionsweise der Formel geschuldet. Sie gibt den Bezug eines Textwertes zurück. Und der wird nunmal so mit den Kaufmanns-Und und den Gänsefüßchen "zusammengestückelt".

Vielen Dank nochmal an Euch beide.
Es hat mich wieder etwas weiter gebracht.

Viele Grüße

Kulo

Betrifft: AW: nun dafür dann mit INDEX() ...
von: neopa C
Geschrieben am: 30.10.2020 09:49:52

Hallo Kulo,

... ich hatte Deine angestrebte Zielstellung gestern wirklich so interpretiert wie Du hier festgestellt hast. Dies u.a. auch, weil Deine Daten damit auch (zufällig) das gleiche Ergebnis wie von Dir aufgezeigt.

Hierfür würde ich nun dann folgende Formel vorschlagen:

=SUMME(INDEX(INDIREKT("'"&JAHR(B1)&"'!3:5");;REST(SPALTE()-2;12)+2))

und diese nach rechts ziehend kopieren

Gruß Werner
.. , - ...