Formelverkürzung durch laufenden Index möglich?

[Jockel]

Hallo

Tabelle "Ziel" Zelle "A2"

Code:

=SUMME('70101:70110'!A2)

Nach unten und nach rechts ziehen.
Das ist jetzt nur für deine 3 Tabellen.
Für deine 100 Tabellen wäre, die Kostenstellen-Tabellen Gruppieren. z.B. SUMME('70101:99999'!A2)
Mfg Guschti

Hallo, da stand schon im link aus Beitrag #2

[Rabe]

Hi,

hat hier noch niemand PQ in die Diskussion eingeworfen?

aber das ist nun mal die firmenintern historisch gewachsene Vorlage, die abteilungsübergreifend verwendet wird. Ich werde das langfristig über Makros zu ändern versuchen, aber das ist jetzt erst einmal meine Vorgabe.

nach meiner Erfahrung: wenn Du diese Vorgabe provisorisch lauffähig machst, dann wird dieses Provisorium die nächsten Jahre nie verändert! Es gibt ja auch keinen Grund, es läuft ja irgendwie so halbwegs.

[Jockel]

Hi,

hat hier noch niemand PQ in die Diskussion eingeworfen?

Hallo Ralf, ich habe den thread jetzt nochmal durchgelesen... Nö. Sehe ich nicht... Dann bist du der Erste... Mach mal...

[Rabe]

Hi Jörg,

Dann bist du der Erste... Mach mal...

pffff, da bin ich noch zu sehr der Anfänger. Da habe ich noch keine Idee.

Oder geht das mit PowerQuery nicht?


Ich verstehe aber auch nicht, warum diese Formel
=SUMME('70101:70110'!A2)
nicht gehen soll?

[Gimlee]

Hallo zusammen,

also ohne jemandem auf die Füße treten zu wollen, es ist ja lieb, dass ihr mir Alternativen vorschlagt, aber die brauche ich leider nicht. Ich kenne mich durchaus sehr gut in Excel aus denke daher auch, sehr genau zu wissen, was ich brauche. Die Datei ist so wie sie ist und das hat auch alles seine Gründe (ob die gut oder schlecht sind, sei mal dahingestellt). Eine Formel der Art 

Code:

=SUMME('70101:70110'!A2)

kann ich zum Beispiel deshalb nicht gebrauchen, weil ich je nach Datei auch mal mehrere Tabellenblattbereiche voneinander abgrenzen können muss. Ich muss daher definieren können, welcher Bereich welche Tabellenblätter umfasst. Das kann ich nur über meine Tabelle. Zudem haben rund 20 Personen Zugriff auf die Dateien und sortieren für ihre abteilungsinternen Zwecke auch Tabellenblätter um. Mein Anspruch ist es, Excellösungen zu konzipieren, die gegen Anwenderfehler gefeit sind und die immer funktionieren. Alles andere sorgt früher oder später für riesiges Chaos - und ratet mal, wer dann Schuld daran ist. ;) Eure sonstigen Einwände und Änderungsvorschläge sind ja berechtigt, aber ich habe die Dateien auch nur "geerbt" und kann sie kurzfristig nicht alle ändern - für das vorangestellte Problem benötige ich aber schon jetzt eine Lösung. Trotzdem noch mal danke an alle Tippgeber bisher! :)


Zurück zum Thema, ich bin jetzt auf anderem Weg einer möglichen Lösung doch sehr nahe gekommen, mir fehlt aber der wichtige letzte Schritt. Dieser hat mit der Funktion SUMMENPRODUKT zu tun, mit der ich zugegebenermaßen bisher recht wenig zu tun hatte, vielleicht ist die finale Lösung daher sogar recht einfach - oder ich bin ganz auf dem Holzweg.

Zum Nachbauen (ca. 30 Sekunden):

Gegeben seien drei Tabellenblätter mit beliebigen Namen, die je eine Zahl in F13 stehen haben und ein viertes Tabellenblatt mit einer Tabelle A1:B3, wobei Spalte A die Zahlen 1 bis 3 enthält und Spalte B die Namen der drei anderen Tabellenblätter.

Die folgende Formel sollte mir nun die Summe der drei Werte aus F13 auswerfen:
=SUMMENPRODUKT(INDIREKT("'"&SUMMEWENN(A1:A3;ZEILE($1:3);B1:B3)&"'!F13"))

Der letzte Berechnungsschritt sieht laut Formelanalyse so aus:
SUMMENPRODUKT({"'70101'!F13";"'70102'!F13";"'70103'!F13"})     (die 70...er-Nummern sind meine Tabellenblätter)


Danach wird das Ergebnis 0 ausgegeben - warum? Muss ich das Summenprodukt noch gegen ein zweites Array multiplizieren? Ich krieg das nicht zusammen.
Ich habe auch SUMME, INDIREKT und Arrayformatierung ausprobiert, aber bisher ohne Erfolg.

[BoskoBiati]

Hi,

ohne Dir zu nahe treten zu wollen, 

Ich kenne mich durchaus sehr gut in Excel aus 

da habe ich so meine Zweifel.

Zum Nachbauen (ca. 30 Sekunden):

Ist die Mühe nicht wert, das hättest Du auch direkt vorstellen können.
Daten gehören in ein Blatt und daraus wird ausgewertet, erspart einem solche Klimmzüge wie hier.

Code:

=SUMMENPRODUKT(SUMME(INDIREKT("'"&B1:B3&"'!F13")))

[Jockel]

Hi Jörg,
...
Oder geht das mit PowerQuery nicht?

Hallo Ralf. Weiß ich nicht. Aber ich habe offenbar die Frage auch nicht vestanden. Ist auch nicht so einfach, wenn der TE "durchaus sehr gut in Excel aus(kennt)" wird er ja schon alles ausprobiert haben und nur noch verfeinern wollen... Das zeigt sich ja auch bei der Datei. Das Wunschergebnis ist entweder nicht vorhanden oder für mich nicht nach zuvollziehen. Also - bin ich stiller Beobachter... Ich will ja lernen.

[Gimlee]

Hi,

ohne Dir zu nahe treten zu wollen, 


da habe ich so meine Zweifel.

Ist die Mühe nicht wert, das hättest Du auch direkt vorstellen können.
Daten gehören in ein Blatt und daraus wird ausgewertet, erspart einem solche Klimmzüge wie hier.

Code:

=SUMMENPRODUKT(SUMME(INDIREKT("'"&B1:B3&"'!F13")))

Hallo Bosko,

ist dein gutes Recht, auch wenn ich deine Zweifel nicht nachvollziehen kann.

Dass Daten immer in ein einzelnes Blatt gehören, ist ja Quatsch. Man kann das allgemein so sehen, als eine Art Konvention und dann ist das ok. Aber im konkreten Fall weißt du nichts über den Dateiaufbau, die Daten, ihre Herkunft, Weiterverwendung, Empfänger, Plugins, angeschlossene Software und dergleichen, um einen solchen Rat auszusprechen. Deswegen ist er halt auch für nicht hilfreich. Ich bearbeite beispielsweise Dateien mit mehr Zeilen als auf einem Datenblatt Platz hätten

Die von dir vorgestellte Lösung kommt sogar ohne SVERWEIS aus, was für mich auch völlig ausreichend ist, da ich den Datenaufbau in meiner Datei dafür nur minimal anpassen muss. Das wichtigste ist: Die Formel funktioniert und tut genau das, was ich mir gewünscht hatte! Damit kann ich auf datenblattintensiven Dateien künftig mit minimalem Aufwand ganze Tabellenblattbereiche in Berechnungen einbeziehen oder sie davon ausschließen, ohne dafür die Formeln selbst antasten zu müssen! Genial!

Ich werde mich in den kommenden Tagen eingehend mit der Funktion SUMMENPRODUKT auseinandersetzen und einen ganzen Haufen Dateien umstrukturieren. Ein fettes DANKE an dich für die Errettung aus meinem Dateienchaos! Und dem Rest sei versichert, dass ich die Firma schon noch in ein goldenes Excel-Zeitalter führen werde, aber alles zu seiner Zeit. Immerhin verbringe ich meine Feierabende damit, an besseren Lösungen zu arbeiten. Ein umso größeres Lob daher an alle, die ebenfalls ihre Feierabende aufwenden, um Fremden dabei zu helfen!

[Jockel]

Hallo... was genau stimmt mit SUMME() denn hier nicht? Warum lieber SUMMENPRODUKT() mit INDIREKT() Und jetzt bitte nicht, weil die Blätter anders heißen könnten oder (noch) nicht alle Blätter vorhanden sind oder gebraucht werden...

Arbeitsblatt mit dem Namen 'Kostenstellen'
	A	B	C	D	E	F
1	#	Kostenstelle
2	1	70101		53310	53310
3	2	70102
4	3	70110
5	4	70180
6	5	70210
7	6	70220
8	7	70250
9	8	70270

Zelle	Formel
D2	=SUMMENPRODUKT(SUMME(INDIREKT("'"&B2:B4&"'!A2")))
E2	=SUMME('70101:70110'!A2)

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[BoskoBiati]

Hi Jörg,


was ist, wenn zwischen 70102 und 70110 noch weitere Blätter stehen?