SUMME Formel verkürzen

05.09.2025 11:04:03

Christian

Hallo,

bitte helft mir.

https://www.herber.de/bbs/user/178831.xlsx

hier eine Bsp. Datei mit den bislang 4 Spalten L bis O und der Summenformel in Spalte I.
Künftig sollen es 52 Spalten werden (L:BK) entsprechend würden es auch 52 Summanden, wenn ich die Berechnung so wie sie jetzt ist beibehalte.

Gibt es da auch andere Lösungen? Ich würde allerdings weiterhin eine Formellösung ohne Hilfsspalte/Zeile bevorzugen.

Danke
Christian

Basiskenntnisse in Excel - verklickt owT

05.09.2025 11:10:28

Christian

AW: SUMME Formel verkürzen

05.09.2025 11:38:24

MCO

Moin!

Bei den Formeln kann ich dir nicht direkt helfen.
Für mich war es am einfachsten eine Funktion zu bauen, die deine Schritte ausführt.

Sie wird angewendet wir eine Formel:

=FeldwertMulti(L1:O1)

Die Logik dahinter gehört in ein VBA-Modul: ALT+F11
Anlegen, Modul, dann reinkopieren:

Function FeldwertMulti(felder As Range) As Long

    Dim feld As Range

    Dim max_wert As Double

    Dim summe As Long

    

    summe = 0

  

    For Each feld In felder

        If feld.Value > "" Then

            max_wert = WorksheetFunction.Max(feld.EntireColumn)

            summe = summe + (max_wert + 1 - feld.Value)

        End If

    Next feld

    

    FeldwertMulti = summe

End Function

Probier es mal aus!

Gruß, MCO

Bezogen auf deine...

05.09.2025 11:56:42

Case

Moin Christian. :-)

... Beispieldatei (ich denke du siehst, wo du für Spalte BK anpassen musst): ;-)

=LET(w;L1:O1;x;MAP(SEQUENZ(1;SPALTEN(w));LAMBDA(y;MAX(INDEX(L:O;;y))));z;WENN(w="";0;x+1-w);SUMME(z))

Servus
Case

AW: SUMME Formel verkürzen

05.09.2025 14:49:38

daniel

Hi

Noch ne Variante:

=SUMME(NACHSPALTE(L:O;LAMBDA(x;MAX(x)+1-ZEILENWAHL(x;ZEILE()))))

Hier kannst du die Spalten einfach anpassen.
Die Formel gilt aber nur für eine Zeile und muss nach unten kopiert werden.

Gruß Daniel

AW: Dir liegt eine gute Lösungsformel vor, ...

05.09.2025 14:59:31

neopa C

Hallo Christian,

... deren Funktion Dir Case zudem auch noch sicher gut erklärt hat (ich habe nur eine alte XL-Version im Einsatz und kann die Formel von Case nicht selbst einsetzen).

Mit einer Hilfszeile (welche Du aber warum auch immer nicht wolltest) kann man allerdings in uralt Excelversionen eine relative einfache Lösungsformel für Spalte I erstellen.
Dazu zunächst einer Hilfszeile, z.B 999 und da in L999 folgende Formel =MAX(L1:L998) und diese nach rechts bis BK999 ziehend kopieren.

Dann in I1 folgende Formel: =SUMMENPRODUKT(L$999:BK$999*(L1:BK1>0))-SUMME(L1:BK1)+ANZAHL(L1:BK1)
(wobei in Deiner XL-Version anstelle SUMMENPRODUKT() wohl auch SUMME() völlig ausreichend sein dürfte) und diese ziehend nach unten kopieren.

Gruß Werner
.. , - ...
aus C the unseen

AW: Dir liegt eine gute Lösungsformel vor, ...

05.09.2025 21:11:33

Christian

Hallo Werner,

um deine Frage zu beantworten, die Originalmappe ist mit all ihren Blättern voll von Formeln die sich auf dieses Blatt beziehen. alle mit dem vollen Spaltenbezug z.B. L:L oder C:C wenn ich jetzt eine Zeile mit den maximalwerten einfüge müsste ich überall diese Zeile aus den Bezügen ausklammern.
Das wollte ich mir sparen.

Klar ich hätte noch die Möglichkeit die Max Berechnung wo ganz andershin auszulagern, aber ich bin kein Freund davon alles total zu verstreuen.

Daher bat ich um eine Formel die ohne Hilfszellen auskommt.

Danke
Christian

PS: Wenn ich gekocht habe, werde ich deinen Vorschlag natürlich testen und dir Rückmeldung geben.

Testergebnis

05.09.2025 21:58:05

Christian

Hallo Werner,

funktioniert tadellos, mit SUMMENPRODUKT als auch SUMME,

ist leider nur wie schon geschildert sehr umständlich für mich.

Gruß
Christian

AW: so verständlich, doch ...

06.09.2025 11:04:10

neopa C

Hallo Christian,

... in solch einem Fall, wie von Dir beschrieben (in der andere Formeln Spaltenwerte im kompletten Zeilenbereich auswerten) würde ich dann anstelle der Hilfszeile, eine (auch ausblendbare) Hilfsspalte (außerhalb Deines Datenbereiches) in den Einsatz bringen. Die Hilfsspaltenformel wie auch die Auswertungsformel werden dann zwar ein wenig komplizierter.
Angenommen Hilfsspalte in BM (natürlich möglich auch BZ, ZZ, ...) dann in BM1: =MAX(INDEX(L:BK;;ZEILE())) und diese bis BM52 nach unten ziehend kopieren.

Dann Auswertungsformel in I1: =MMULT(SPALTE(L1:BK1)^0*(L1:BK1>0);BM$1:BM$52)-SUMME(L1:BK1)+ANZAHL(L1:BK1) und diese nach unten kopieren.

Gruß Werner
.. , - ...
aus C the unseen

AW: so verständlich, doch ...

06.09.2025 14:40:46

Christian

Hallo Werner,

hmm das geht jetzt mit MMULT in Bereiche hinein, von denen ich noch nie gesehen und gehört habe, im Gegensatz zu LET.
Ich werde bei meiner ohne Hilfsspalten Lösung wohl besser bleiben.

Gruß
Christian

AW: SUMME Formel verkürzen

05.09.2025 14:58:34

daniel

So, jetzt vollständig mit der Zusatzbedingung:

=SUMME(NACHSPALTE(L:O;LAMBDA(x;(ZEILENWAHL(x;ZEILE())>"")*(MAX(x)+1-ZEILENWAHL(x;ZEILE())))))

Korrektur die zweite Formel von dir geht owT

05.09.2025 21:07:40

Christian

AW: SUMME Formel verkürzen

05.09.2025 21:06:48

Christian

Hallo Daniel,

sorry aber die Formel gibt nicht in jeder Zeile dasselbe Ergebnis aus wie meine, z.B. Zeile 2 oder 7.

Gruß und trotzdem danke
Christian

AW: Bezogen auf deine...

05.09.2025 12:02:52

Christian

Hallo Case,

auch diese Formel funktioniert, danke.
Und ja, das O 2x in BK ändern habe ich hinbekommen.

Ich hab nur keine Ahnung, wie sie funktioniert. Aber das sie es tut reicht mir.

Danke
Christian

ok, ich versuche zusammenzufassen

05.09.2025 21:05:16

Christian

hoffe ich habe es verstanden

Mit LET kann ich in Excel Zwischenergebnissen einen Namen geben und später wiederverwenden.

w steht also für den Bereich L1:O1

x ist eine Liste mit den Maximalwerten jeder Spalte von L bis O.

z berechnet für jede Spalte die Differenz zwischen Max+1 und Zeile 1.

SUMME(z) Alle berechneten Werte in z werden summiert.

SPALTEN(w) Zählt, wie viele Spalten w hat

SEQUENZ(1;SPALTEN(w)) Erzeugt eine Reihe von Zahlen von 1 bis zur Anzahl der Spalten.

MAP(...;LAMBDA(y;...)) Führt eine Berechnung für jede dieser Spaltennummern durch

INDEX(L:O;;y) Wählt die y-te Spalte aus dem Bereich L:O

MAX(...) Nimmt den größten Wert aus dieser Spalte.

Aber das mit dem Punkt habe ich immer noch nicht verstanden. Er macht bei mir aber auch keine Probleme.

Danke für die Erklärung
Christian

Im Anhang ist...

05.09.2025 22:40:32

Case

Moin Christian, :-)

... ist ein Beispiel: ;-)
https://www.herber.de/bbs/user/178846.xlsx

Im Prinzip geht es darum den Bereich einzuschränken. Führende und nachfolgende Leerzeilen spielen dann keine Rolle mehr, ohne dass du den genauen Bereich angeben/errechnen musst. ;-)

Servus
Case

AW: Im Anhang ist...

05.09.2025 22:58:55

Christian

Hallo Case,

ok, jetzt verstehe ich du schneidest also Daten die 0 oder leer sind ab.

War denn meine Erklärung der anderen Formelteile korrekt?

Danke für deine viele Mühe
Christian

Das ist für...

06.09.2025 14:08:17

Case

Moin, :-)

... Tabellen mit vielen Formeln bequem, da du dich nicht mehr darum kümmern musst, wie lang die Spalte ist. ;-)

Manche Formeln (z. B. Matritzenberechnungen) werden langsam, wenn du die ganze Spalte angibst. In regelmäßigen Abständen kommt im Forum immer wieder die Frage auf "Meine Tabelle wird immer langsamer - was kann ich tun?". Es ist nicht der einzige - aber ein möglicher Grund, dass bei Formeln großzügig gearbeitet wird. ;-)

Wie gesagt - bei kleinen Tabellen merkst du es nicht. ;-)

Deine Erklärungen sind richtig und ich denke, du kannst dir jetzt deine eigenen LET-Konstruktionen aufbauen. ;-)

Servus
Case

AW: Das ist für...

06.09.2025 14:45:46

Christian

Ja ich denke das mit Let hab ich verstanden, ich kann sich wiederholende Teile von Formeln abkürzen und bestimme diese Abkürzungen zu Beginn und kann sie dann im weiteren Verlauf der Formel verwenden.

Fehlt nur noch die praktische Übung.

Gruß
Christian

Danke für all deine Mühe

Nachfolgend der Versuch...

05.09.2025 13:58:19

Case

Moin Christian, :-)

… einer Erklärung. ;-)

LET - damit weise ich Berechnungen Namen zu. Das vermeidet Redundanzen: ;-)
https://support.microsoft.com/de-de/office/let-funktion-34842dd8-b92b-4d3f-b325-b8b8f9908999

MAP/LAMBDA - dadurch wird ein Array von Werten zurückgegeben, dass durch LAMBDA berechnet wird: ;-)
https://support.microsoft.com/de-de/office/map-funktion-48006093-f97c-47c1-bfcc-749263bb1f01
https://support.microsoft.com/de-de/office/funktion-lambda-bd212d27-1cd1-4321-a34a-ccbf254b8b67

WENN/SUMME ist wie bei dir. Du könntest es auch so schreiben: ;-)

=LET(x;L1:O1;y;MAP(SEQUENZ(1;SPALTEN(x));LAMBDA(z;MAX(INDEX(L:.O;;z))));SUMME(WENN(x="";0;y+1-x)))

Der Punkt vor O bei Index könnte bei dir Probleme machen. Wenn ja - weglassen. ;-)

Du kannst die Formel auch zerlegen: ;-)

=MAX(INDEX(L:O;;1))

Oder: ;-)

=MAP(SEQUENZ(1;SPALTEN(L1:O1));LAMBDA(k;MAX(INDEX(L:O;;k))))

Die folgende Formel in die erste Zelle einer freien Spalte: ;-)

=INDEX(L:O;;1)

Wenn es bei dir mit dem Punkt geht - auch: ;-)

=INDEX(L:.O;;1)

Zum Punkt: ;-)
https://support.microsoft.com/de-de/office/trimrange-funktion-d7812248-3bc5-4c6b-901c-1afa9564f999

Vielleicht wird es nun etwas klarer. ;-)

Servus
Case

AW: SUMME Formel verkürzen

05.09.2025 11:43:37

Christian

Hallo MCO,

sorry an UDF hatte ich in dem Moment gar nicht gedacht.
Kein Problem, wenn es funktioniert, nehme ich auch die UDF.
Mache mich dann mal ans Testen und melde mich dann gleich.

Danke
Christian

Testergebnis

05.09.2025 11:54:50

Christian

Hallo MCO,

dein Vorschlag funktioniert wie ein Haus, dass nur 3 Wände hat - ein-wand-frei.

Vielen Dank und ein schönes Wochenende
Christian

SUMME Formel verkürzen

Forumthread: SUMME Formel verkürzen

21
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

SUMME Formel verkürzen

Forumthread: SUMME Formel verkürzen

21 Beiträge zum Forumthread Beiträge zu diesem Forumthread

21
Beiträge zum Forumthread

Beiträge zu diesem Forumthread