Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Über mehrere Tabellenblätter summieren

Über mehrere Tabellenblätter summieren
03.09.2022 23:21:18
Thomas
Hallo,
mir ist bekannt, dass ich mit der folgenden Formel beginnend vom Tabellenblatt mit dem Namen "S1" über alle dazwischenliegenden Blätter bis zum Tabellenblatt mit dem Namen "ENDE" das Feld K5 aufsummiere:

=SUMME('S1:ENDE'!K5)
Nun zu meinem Problem: Meine Tabelle hat aktuell 99 Tabellenblätter, benannt nach folgendem Muster mit fortlaufender Nummerierung: S1, S2, S3, S4,..., S99. Anschließend folgt noch ein Tabellenblatt namens ENDE.
Ich möchte hier die Summe des Feldes K5 nicht mehr bis zum letzten, sondern nur bis zu einem beliebigen, variabel eingebbaren Tabellenblatt bilden. Dazu trage ich den Namen des Tabellenblatts, bis zu dem gezählt werden soll, in Feld K1 ein. Beispiel: Möchte ich von S1 bis zu Tabellenblatt S55 zählen, trage ich in Feld K1 den Wert S55 ein.
Den Wert aus Feld K1 kann ich auslesen mit:

=ZELLE("inhalt";K1)
Mein Gedanke war daher, beide obigen Formeln folgendermaßen zusammenzubauen:

=SUMME('S1:[ZELLE("inhalt";K1)]'!K5)
Ergebnis ist jedoch nicht meine gewünschte Summe der Tabellenblätter S1 bis zum in K1 eingegebenen Blatt (im Beispiel: S55), sondern die Ausgabe eines Bezugsfehlers: #BEZUG!
Gibt es einen einfachen Weg, eine solche Summe zu bilden? Danke für jede Idee!

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Über mehrere Tabellenblätter summieren
04.09.2022 00:56:36
{Boris}
Hi,
theoretisch sollte man meinen, dass es mit INDIREKT geht - aber 3D-Bezüge lassen sich nicht so einfach damit auswerten.
Wie heissen denn die Blätter? Sind sie eventuell (bestenfalls) indizierbar - also mit aufsteigenden Nummern benannt?
VG Boris
AW: Über mehrere Tabellenblätter summieren
04.09.2022 08:31:22
Thomas
Hallo,
die Blattnamen der 99 Blätter bestehen aktuell aus einer aufsteigenden Nummer mit einem "S" davor. Also S1, S2, S3, S4...durchgehend ohne Lücke bis S99. Am Ende folgt noch ein Blatt namens "ENDE", was für diese Auswertung aber irrelevant ist. Die Auswertung soll immer nur von S1 bis Sxx laufen (xx = beliebige ein- oder zweistellige Zahl).
Sofern das die Auswertung erleichtert, würde auch die Möglichkeit bestehen, das "S" im Blattnamen wegzunehmen. Die Blattnamen würden dann nur noch aus einer ansteigenden Zahl von 1 - 99 bestehen.
Anzeige
AW: Über mehrere Tabellenblätter summieren
04.09.2022 09:37:19
Eifeljoi5
Hallo
Da keine Beispieldatei mit Wunschergebnis , mit geliefert wurde habe ich es selbst erfunden.
Anbei mein Versuch mit PQ,
Einfach Register nach dem Muster hinzu fügen und PQ Tabelle Aktualisieren.
https://www.herber.de/bbs/user/154965.xlsx
AW: Über mehrere Tabellenblätter summieren
04.09.2022 12:22:06
Thomas
Hallo, danke auch für diese Idee!
Power Queries sind für mich noch völliges Neuland, daher muss ich gestehen, dass ich die Lösung noch nicht verstehe und daher vermutlich auch falsch anwende. Ich habe mit dem Ansatz von onur jedoch jetzt eine Lösung gefunden, die mein Problem löst.
Anzeige
AW: Über mehrere Tabellenblätter summieren
04.09.2022 12:36:30
Eifeljoi5
Hallo
Bei PQ ist für Anfänger sinnvoll mit Inteligente Tabellen zu arbeiten, wie in meiner Datei zu sehen, diese zusammen ein zu lesen.
Bei meiner Lösung kann man problemlos noch mehrere Tabellen hin zu fügen.
Die einzelnen Schritte die ich gemacht habe kann man entweder im PQ Editor rechte Seite oder im erweiterten PQ Editor nachlesen.
AW: Über mehrere Tabellenblätter summieren
04.09.2022 12:02:52
Thomas
Vielen Dank, damit habe ich nun eine Lösung für mich gefunden.
Ich habe noch ein wenig weiter daran herumgespielt, u.a. werde ich das "S" in der Tabellenblattbezeichnung der Einfachheit weglassen. In dem ich das Feld, in den ich den "bis"-Wert eintrage als Range übergebe, aktualisiert das Ergebnis auch automatisch, wenn ich den "bis"-Wert ändere.
Da bei einer Änderung in den Tabellenblättern das Ergebnis nicht automatisch aktualisiert hat, bin ich jetzt auch auf die "Application.Volatile"-Methode gestoßen und habe sie eingebaut.
Der Vollständigkeit halber mein Ergebnis in der Beispieldatei:
https://www.herber.de/bbs/user/154970.xlsm
Anzeige
3D-Summe mit INDIREKT
04.09.2022 09:55:12
{Boris}
Hi,
in K1 steht nur die Zahl des bis-Blattes - ohne "S" - also z.B. 22. Summiert wird dann Zelle K5 von Blatt S1 bis S22.
=SUMME(INDIREKT("S"&ZEILE(INDIREKT("1:"&K1))&"!K5"))
In Deiner Excelversion noch als ARRAYformel eingeben - also Formelabschluss nicht mit Enter allein, sondern mit gleichzeitig Strg+Shift+Enter.
Als "normale" Formel dürfte es aber mit SUMMENPRODUKT gehen (ich kann das halt nicht mehr testen):
=SUMMENPRODUKT(INDIREKT("S"&ZEILE(INDIREKT("1:"&K1))&"!K5"))
VG, Boris
AW: 3D-Summe mit INDIREKT
04.09.2022 12:17:41
Thomas
Danke für die Idee!
Wie als Antwort zum Beitrag von onur geschrieben, habe ich mein Problem über seinen Ansatz gerade schon lösen können. Ich habe den Weg über INDIREKT interessehalber (da ohne Makros) trotzdem einmal ausprobiert, aber bekomme damit leider keine richtigen Ergebnisse. Mit der Arrayformel wird immer nur der Wert vom ersten Tabellenblatt ausgegeben, mit der Summenprodukt-Formel ist das Ergebnis immer = 0. Mit INDIREKT habe ich bisher noch nie gearbeitet - dort muss ich mich vermutlich noch etwas tiefer eindenken um zu sehen, was ich falsch gemacht habe. Anbei einmal die Beispieldatei: https://www.herber.de/bbs/user/154971.xlsx
Anzeige
AW: 3D-Summe mit INDIREKT
04.09.2022 12:21:01
{Boris}
Hi,
ich sagte ja, dass ich es nicht (mehr) testen kann. Bau mal zusätzlich die Funktion N() ein und sag, was damit passiert:
=SUMMENPRODUKT(N(INDIREKT("S"&ZEILE(INDIREKT("1:"&K1))&"!K5")))
VG, Boris
AW: 3D-Summe mit INDIREKT
04.09.2022 19:00:41
Thomas
Hallo,
vielen Dank, das funktioniert ebenfalls genau wie von mir benötigt, noch dazu ohne Makros - das gefällt mir sehr gut!
Ich bin noch dabei zu verstehen, was dort genau passiert. Wenn ich dazu zur Vereinfachung einmal das "S" in der Tabellenblattbezeichnung weglasse (die Tabellenblätter sind dann nur noch eine fortlaufende Nummer - 1, 2, 3, 4,...) und in Feld K1 den festen Wert 3 setze, ergibt sich daraus ja zusammengefasst:

=SUMMENPRODUKT(N(INDIREKT(ZEILE(1:3)&"!K5")))
Im Ansatz verstehe ich es denke ich: Über ZEILE(1:3) werden, durch das Durchlaufen von SUMMENPRODUKT, sozusagen die Namen der zu summierenden Tabellenblätter (1, 2 und 3) gesetzt und durchlaufen. INDIREKT liest die eingetragenen Werte an den angegebenen Adressen (z.B. 2!K5 - Tabellenblatt 2, Feld K5) aus, die SUMMENPRODUKT dann addiert.
Gruß,
Thomas
Anzeige
Das hast Du gut erklärt...
04.09.2022 19:02:55
{Boris}
Hi Thomas,
...da habe ich wenig bis nichts hinzuzufügen :-)
VG, Boris
AW: Das hast Du gut erklärt...
04.09.2022 19:08:04
Thomas
Danke für die Bestätigung und noch einen schönen Abend!
Jetzt gilt es für mich nur noch, das Gelernte auch bei nächster Gelegenheit wieder abrufen zu können...

358 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige