3d summe mit array

[rodlof]

Liebe Freunde

Ich möchte gerne eine 3D-Summe über mehrere Register mit einem Array erstellen, indem jede Zeile summiert wird. Ich kriege das aber nicht hin. Ich hoffe ihr könnt mir da weiter helfen.

Als Beispiel:
Die Register heissen AAA, BBB, CCC. Im Register ZZZ möchte ich die Summen haben.
Im Register AAA beinhalten die Zellen A5: 1, A6: 2, A7: 3
Im Register BBB beinhalten die Zellen A5: 10, A6: 11, A7: 12
Im Register CCC beinhalten die Zellen A5: 22, A6: 33, A7: 44

Nun sollte im Register ZZZ in den Zellen folgendes erscheinen: A5: 33, A6: 46, A7: 59

Natürlich kann ich im Register ZZZ in jede Zeile eine einzelne Formel schreiben A5: =summe(AAA:CCC!A5), A6: =summe(AAA:CCC!A6), A7: =summe(AAA:CCC!A7)
Ich möchte das aber mit einem Array lösen etwa so: A5:A7 {=summe(AAA:CCC!A5:A7)}

Das Ziel ist, das ganze dann über indirekt() zu steuern. Dies ist natürlich nur praktikabel mit einer Array-Formel, da ich in der Realität 350 Zeilen und 45 Register habe.

Geht das überhaupt? Vielen Dank für eure Hilfe.

Viele Grüsse aus der Schweiz
Rodlof

[BoskoBiati]

Hi,

Geht das überhaupt?

nein!

[snb]

Das sind nur 15750 Zeillen in einem Arbeitsblatt (das mehr als 1.000.000 Zeilen enthalten kann).

[Fennek]

Hallo,

mit vba sollte es möglich sein, etwas zu "basteln", sei es mit einer Schleife über alle relevanten Sheets oder ein 3-dim Array.

Ohne kleine, aber aussagekräftige Datei wird das nicht gehen und ob sich dann jemand findet, ist offen.


mfg

[Castor]

Falls mit INDIREKT steuern auswählen bedeutet, Rodlof,
wird's komplizierter als für jede Summe über die Blätter zu summieren, vor allem, wenn die Blätter nicht flfd nummeriert sind, sondern jedes einen eigenen Namen hat. Eine Blattauswahl für jede einzelne Summe ist auch etwas einfacher als eine Matrixformel über den ganzen ZZZ-Bereich, weshalb ich im Folgenden nur auf Ersteres eingehe.
Ansonsten wäre INDIREKT schon der richtige Ansatz, obwohl es unter den Xl-Funktionen keine gibt, die Daten über mehrere Blätter hinweg (auswählend) zusammen­stellt, weshalb hier auch INDEX nicht funktioniert. Per INDIREKT wird aber etwas anders vorgegangen, weshalb das unter zusätzlicher Verwendung von N, also N(INDIREKT(…)) funktionieren kann. Für dein Bsp und alle Blätter sähe das dann so aus:
ZZZ!A5[:A7]:=SUMME(N(INDIREKT({"AAA"\"BBB"\"CCC"}&"!A"&ZEILE())))
Diese Formel hat allerdings den Nachteil, dass stets alle relevanten Blätter namentlich in der inneren Matrixkonstante aufgeführt werden müssen. Wären die Blätter durchnummeriert, zB Tab1:Tab3, könnte die Formel so aussehen:
ZZZ!A5[:A7]:=SUMMENPRODUKT(N(INDIREKT("Tab"&ZEILE(A$1:A$3)&"!A"&ZEILE())))
…oder als singulare (1zellig-1wertige) Matrixformel so:
ZZZ!A5[:A7]:{=SUMME(N(INDIREKT("Tab"&ZEILE(A$1:A$3)&"!A"&ZEILE())))}
Wollte man nun nur bestimmte Blätter wählen, müssten im 1.Fall nur die irrelevanten weggelassen werden. Im 2.Fall wäre es komplizierter, es sei denn, es soll mit nur einem Von-Bis-Blattnrn-Intervall gearbeitet werden:
=SUMMENPRODUKT(N(INDIREKT("Tab"&ZEILE(A$1:A$2)&"!A"&ZEILE())))
Anderenfalls müsste auch hier auf die Aufzählung zurückgegriffen werden, die dann aber einfacher ausfiele:
=SUMME(N(INDIREKT("Tab"&{1\3}&"!A"&ZEILE())))
Im Falle separater einzelner Namen kann man natürlich auch so vorgehen, dass man diese in einen Zellbereich einträgt, auf den die Formel dann Bezug nimmt. Dabei können irrelevante Namen ent­weder ganz weggelassen oder die relevanten aus den Namen ausgewählt werden. Bei Letzterem kann aber eine Kom­plikation entstehen, wenn keine Intervalle, sondern einzelne Namen ausgewählt werden sollen (besonders, wenn zur Auswahl INDEX verwendet wird), weshalb dann besser (mit singularer Matrixformel) wie folgt vorgegangen werden sollte (die TabNamen stehen in ZZZ!A1:A3):
ZZZ!A5[:A7]:{=SUMME(N(INDIREKT(WAHL(SPALTE(A1:B1);A$1;A$3)&"!A"&ZEILE())))}
Das wird dann allerdings auch wieder eine Aufzählung, diesmal aber von Einzelzellbezügen.
Elegantere flexible Lösungen bedürfen in Formelform einer entsprechend leistungsfähigen UDF, die aus den Daten der relevanten Blätter Vektoren erstellt, über deren Elemente dann summiert werden kann.
Gruß, Castor