Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Formelverkürzung durch laufenden Index möglich?
#1
Hallo Leute,

ich frage mich seit längerer Zeit, ob es möglich ist, Formeln der Art FORMEL(A1)+FORMEL(A2)+FORMEL(A...)+FORMEL(A100) so zu verschlanken, dass ich einfach FORMEL(A1:A100) schreiben kann. Ich bräuchte das für ganz verschiedene Anwendungsbereiche, aber um mal ein konkretes Beispiel zu geben:

Eine Arbeitsmappe mit 100 Tabs mit gleichem Aufbau gibt Aufträge wieder. Jeweils in Zelle F13 steht ein Ergebnis, das ich für alle Tabellenblätter addieren muss. Die Tabellenblätter folgen keinem einheitlichen Namensschema, zudem können neue hinzukommen und alte wegfallen.

Die übliche Vorgehensweise wäre nun
='213'!F13+'709'!F13+'12281'!F13+'Sommerfest'!F13+'Z8031'!F13 etc. Dass man damit auf Dauer wenig Spaß hat, dürfte klar sein. Meine Idee ist es, die Aufträge in einer Tabelle durchzunummerieren, einen SVerweis dranzuhängen und davon die Summe zu ermitteln, in etwa

[Bild: cfaja7ja.png]

Hier mal exemplarisch durchgeführt mit drei Termen für die ersten drei Zeilen, der Wert 87 ergibt sich aus den drei Werten je in F13 der entsprechenden Tabellenblätter.

In einem letzten Schritt müsste ich nun noch die Möglichkeit haben, die Formel irgendwie kurz und knackig auf {=SUMME(INDIREKT(SVERWEIS(1:100;A1:B100;2;0)&"!F13")} oder so ähnlich zu verkürzen, dass also die Summe aller SVerweise von 1 bis 100 für diese Formel gezogen wird (laufender Index). Aber ich weiß nicht wie! Ich habe es schon mit Arrayformeln versucht, mit Super-SVerweis, mit Summenprodukt und dabei jede erdenkliche Syntax ausprobiert. Gibt es dafür überhaupt eine solche Kurzschreibweise und kennt sie jemand? Ich bin mir fast sicher, dass es eine mögliche Lösung mit den Funktionen Summenprodukt, Indirekt und Zeile geben müsste, aber ich kriegs einfach nicht zusammen.


PS: An einer VBA-Lösung bin ich in diesem Fall nicht interessiert. Grund sind Zugriffsprobleme auf Makrodateien im Netzwerk sowie mein inzwischen gesteigerter Ehrgeiz, diese Nuss endlich auf Formelebene zu knacken - sofern möglich! Danke!
Antwortento top
#2
Hallo, das hier sollte dich interessieren..:

http://www.excelformeln.de/formeln.html?welcher=206
cu Jörg eine Rückmeldung wäre ganz reizend XL2003 bis XL2019
[-] Folgende(r) 2 Benutzer sagt Danke an Jockel für diesen Beitrag:
  • Frogger1986, Gimlee
Antwortento top
#3
Jockel war schneller.. Das wäre auch mein Tip dafür...
Eine Menge reden, aber nichts sagen können viele...
[-] Folgende(r) 1 Benutzer sagt Danke an Frogger1986 für diesen Beitrag:
  • Gimlee
Antwortento top
#4
Danke euch beiden für die Hilfe, die Formel sieht super und vor allem einfach aus! Damit kann ich schon viel machen, aber die ultimative Wunschlösung ist das natürlich trotzdem nicht (da sie nicht flexibel auf andere Funktionen erweiterbar ist, sondern offenbar nur für Summen greift). Ich habe indes noch mal auf einem anderen Weg versucht, mich einer Lösung dafür zu nähern und inzwischen sieht es für mich eher doch danach aus, dass Excel mein Vorhaben nicht unterstützt. Falls sich doch jemand an einer Lösung versuchen will, bitte sehr gerne!Smile

Mein Feedback und Rückfragen zur verlinkten Lösung:

- in meiner Office-Version (365) hat die Formel =SUMME(Tabelle5:Tabelle25!A2) nicht direkt funktioniert, nur in der Form =SUMME('Tabelle5:Tabelle25'!A2), also mit den ' '
- ich habe natürlich sofort versucht, die vorgestellte Lösung anhand von SVerweis zu testen. Mit =SVERWEIS(Parameter1;'Tabelle5:Tabelle25'!A1:B10;2;0) kommt man freilich nicht ans Ziel, da SVERWEIS auf mehrere Tabellenblätter verweist, er aber nur einen einzigen Wert ausgeben kann. Doch auch mit =SUMME(SVERWEIS(Parameter1;'Tabelle5:Tabelle25'!A1:B10;2;0)) bzw. als Arrayformel ist hier leider keine automatische Aufsummierung möglich. Sehr schade, das hätte mir meine Arbeit sehr erleichtert
- anstatt die Tabellenblätter direkt zu benennen ("Tabelle5, Tabelle25") wäre für mich ein indirekter Verweis notwendig: =SUMME(INDIREKT("'"&C1&":"C2&"'!A2")) [mit C1 = "Tabelle5" und C2 = "Tabelle25"]. Das funktioniert leider nicht. Ich habe ziemlich alle mir bekannten Syntaxkombinationen durchprobiert. Habe ich bloß etwas falsch gemacht oder unterstützt Excel hierbei kein INDIREKT? 

Liebe Grüße und eine erholsame Nacht!
Antwortento top
#5
Hallo, magst du (d)eine (Beispiel)Datei posten - kein Bild!!!

Vielleicht hilft aber schon das..: http://www.excelformeln.de/formeln.html?welcher=233
cu Jörg eine Rückmeldung wäre ganz reizend XL2003 bis XL2019
[-] Folgende(r) 1 Benutzer sagt Danke an Jockel für diesen Beitrag:
  • Gimlee
Antwortento top
#6
Hi,

warum 100 Tabellen? Alle Daten in eine Tabelle, dann ist das Auswerten kein Problem mehr.
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antwortento top
#7
so?

Arbeitsblatt mit dem Namen 'Tabelle5'
AB
6Tabelle15
7Tabelle25
8Tabelle35
9Tabelle45
10Tabelle55
1125

ZelleFormel
B6=INDIREKT(A6&"!B4")
B7=INDIREKT(A7&"!B4")
B8=INDIREKT(A8&"!B4")
B9=INDIREKT(A9&"!B4")
B10=INDIREKT(A10&"!B4")
B11=SUMME(B6:B10)
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg


oder so..

Arbeitsblatt mit dem Namen 'Tabelle5'
A
6Tabelle1
7Tabelle2
8Tabelle3
9Tabelle4
10Tabelle5
1125

ZelleFormel
A11=SUMME(WENNFEHLER(INDIREKT(A6&"!B4");0);WENNFEHLER(INDIREKT(A7&"!B4");0);WENNFEHLER(INDIREKT(A8&"!B4");0);WENNFEHLER(INDIREKT(A9&"!B4");0);WENNFEHLER(INDIREKT(A10&"!B4");0))
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg
Eine Menge reden, aber nichts sagen können viele...
[-] Folgende(r) 1 Benutzer sagt Danke an Frogger1986 für diesen Beitrag:
  • Gimlee
Antwortento top
#8
Ich möchte nochmal auf Boskos Post hinweisen:

Warum braucht man 100 Tabellen, das macht doch keinen Sinn. Alle Daten in eine Tabelle und diese auswerten. Abgesehen davon, dass so viele Tabellen völlig unübersichtlich sind, ist eine ordentliche Struktur idR zumindest noch einigermaßen verwendbar, wenn ich die Daten in eine Datenbank überführen möchte oder die Anforderungen erweitern will.

Bei einer Erweiterung um ein Feld müssen 100+ Tabellen geändert werden. Bei ordentlichem Konzept wird in einer Tabelle eine Spalte hinzugefügt.
Gruß
Michael
[-] Folgende(r) 1 Benutzer sagt Danke an Der Steuerfuzzi für diesen Beitrag:
  • Gimlee
Antwortento top
#9
Guten Abend,

die letzten zwei Tage hatte ich wirklich null Zeit, daher melde ich mich erst heute zurück. Danke aber für die Rückmeldungen bisher!


(05.09.2018, 23:26)Jockel schrieb: Hallo, magst du (d)eine (Beispiel)Datei posten - kein Bild!!!

Vielleicht hilft aber schon das..: http://www.excelformeln.de/formeln.html?welcher=233
Hallo Jockel, der Link hilft mir bei meinem Anliegen leider nicht weiter, aber ich habe mal die eine Beispieldatei mit kurzen Erklärungen angefügt.

(06.09.2018, 06:40)Frogger1986 schrieb: so?

[...]
Danke dir Frogger für die Mühe, aber die Hilfsspaltenlösung ist für mich nicht praktikabel. Ich muss nicht bloß einen Wert über die Tabellenblätter hinweg aufsummieren (das eingangs gezeigte Beispiel sollte nur das allgemeine Schema verdeutlichen), sondern rund 2.000. Deswegen frage ich explizit nach einer Formellösung, da ich nicht mit 2.000 Hilfsspalten arbeiten kann.

(06.09.2018, 08:39)Der Steuerfuzzi schrieb: Warum braucht man 100 Tabellen, das macht doch keinen Sinn. Alle Daten in eine Tabelle und diese auswerten. Abgesehen davon, dass so viele Tabellen völlig unübersichtlich sind, ist eine ordentliche Struktur idR zumindest noch einigermaßen verwendbar, wenn ich die Daten in eine Datenbank überführen möchte oder die Anforderungen erweitern will.

Bei einer Erweiterung um ein Feld müssen 100+ Tabellen geändert werden. Bei ordentlichem Konzept wird in einer Tabelle eine Spalte hinzugefügt.
Ich bin damit auch alles andere als glücklich, 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. Praktikabel ist eine Darstellung auf einer Seite allerdings auch nicht, da jedes Tabellenblatt mehrere Tabellen mit hunderten Zahlen beinhaltet mit immer unterschiedlich langen Anhängen (die mich hier aber nicht interessieren, da der jeweils obige relevante Teil einheitlich ist).


Angehängte Dateien
.xlsx   beispiel.xlsx (Größe: 23,82 KB / Downloads: 11)
Antwortento top
#10
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
Der Künstler lebt auch vom Applaus
Antwortento top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste