Formel gesucht

Bild

Betrifft: Formel gesucht
von: Franz W.
Geschrieben am: 21.10.2015 15:50:46

Hallo Fachleute,
ich hab eine Formel, die ich mal so begonnen habe:
=BT8+BT13+BT18+BT23+BT28+BT33+..................................
Es sollen die Werte addiert werden ab BT8 jede 5. Zeile. Und tatsächlich erweitert sich der Bereich auch noch, vorauss. noch sehr weit, vorauss. in den 4-stelligen Zeilenbereich.
Gibt es eine schlauere Formel, die das abdeckt? Drum bitte ich um Eure Hilfe.
Danke schonmal und Grüße
Franz

Bild

Betrifft: AW: Formel gesucht
von: SF
Geschrieben am: 21.10.2015 15:58:44
Hola,
hier mal bis Zeile 1000.

=SUMMENPRODUKT((REST(ZEILE(BT8:BT1000);5)=3)*(BT8:BT1000))
Gruß,
steve1da

Bild

Betrifft: AW: Formel gesucht
von: Franz W.
Geschrieben am: 21.10.2015 16:08:14
Hallo Steve,
schon mal super, vielen Dank!
Jetzt dachte ich, ich könnte das selber für folgende Formel umschreiben:
=BK9+BK16+BK23+BK30+BK37+BK44 - beginnend ab Zeile 9 in 7-er Schritten, aber das hier
=SUMMENPRODUKT((REST(ZEILE(BK9:BK1000);7)=3)*(BK9:BK1000)) haut nicht hin, was mache ich da noch falsch?
Grüße
Franz

Bild

Betrifft: AW: da ... (REST(ZEILE(A9:A999;7)=2) ... owT
von: ... neopa C
Geschrieben am: 21.10.2015 16:17:12
Gruß Werner
.. , - ...

Bild

Betrifft: AW: da ... (REST(ZEILE(A9:A999;7)=2) ... owT
von: Franz W.
Geschrieben am: 21.10.2015 16:35:08
Hallo Werner,
vielen Dank! Aber ich kapier nicht, worauf's ankommt: kopier ich die 1. Formel nur eine Zeile weiter runter, um in 5-er Schritten ab Zeile 9 zu beginnen:
=SUMMENPRODUKT((REST(ZEILE(BT9:BT1001);5)=3)*(BT9:BT1001))
kommt das selbe Ergebnis wie in der 1. Formel eine Zeile höher? Worauf muss ich achten?
Grüße
Franz

Bild

Betrifft: AW: da ... (REST(ZEILE(A9:A999;7)=2) ... owT
von: SF
Geschrieben am: 21.10.2015 16:39:09
Hola,
auf den Divisor bei REST.
Schreib mal

=rest(zeile();5)
in BT8 und zieh die Formel runter.
Gruß,
steve1da

Bild

Betrifft: AW: da ... (REST(ZEILE(A9:A999;7)=2) ... owT
von: Franz W.
Geschrieben am: 21.10.2015 16:57:23
Hallo Steve,
gut, jetzt hab ich's glaub ichkapiert. Es kommt auf den Divisor an und auf die Zeile in der mit der Zählung begonnen wird, richtig?
Somit ergibt sich bei der Formel mit den 5-er Schritten, beginnend in Zeile 8: "3" - ermittelt mit "=REST(ZEILE();5)"
Bei der Formel mit den 7-er Schritten, beginnend in Zeile 9: "2" - ermittelt mit "=REST(ZEILE();7)"
isses das?
Fragende Grüße
Franz

Bild

Betrifft: Das isses :) owT
von: SF
Geschrieben am: 21.10.2015 16:58:21


Bild

Betrifft: Vielen Dank an Euch beide! owt
von: Franz W.
Geschrieben am: 21.10.2015 17:05:16
...

Bild

Betrifft: AW: nun da müssen wir noch klären ...
von: ... neopa C
Geschrieben am: 21.10.2015 16:44:49
Hallo Franz,
... welche Zellwerte Du wirklich kopieren willst. Ich hatte es so verstanden, dass Du zuerst ab Zeile 8 jeden 5 Wert addieren willst und bei Deiner Nachfrage ab Zeile 9 jeden 7. Wert. Was willst Du nun getan tun?
Gruß Werner
.. , - ...

Bild

Betrifft: AW: nun da müssen wir noch klären ...
von: Franz W.
Geschrieben am: 21.10.2015 16:59:31
Hallo Werner,
ja, es gibt Unterschiedliches. Ich glaub ich hab's kapiert, siehe meine Antwort an Steve.....
..... und vielen Dank für Deine Hilfe!
Franz

Bild

Betrifft: AW: da ... (REST(ZEILE(A9:A999;7)=2) ... owT
von: Herbert Grom
Geschrieben am: 21.10.2015 16:35:29
Hallo Werner,
wieso einmal 3 und dann wieder 2? Was bedeutet dieser Parameter?
Servus

Bild

Betrifft: AW: dazu siehe ...
von: ... neopa C
Geschrieben am: 21.10.2015 16:50:53
Hallo Herbert,
... die Antwort von steve1da. Nur wenn die Zeilenwerte der Daten aus der Spalte diesen REST-wert entsprechen, gehen sie in die Addition auch ein, weil die Bedingung nur da eine 1 ergibt mit dem die entsprechenden Datenwerte der Spalte multipliziert werden und ansonsten ergeben sich 0-Summandenwerte.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: mit REST() ...
von: ... neopa C
Geschrieben am: 21.10.2015 16:00:27
Hallo Franz,
... so =SUMMENPRODUKT(B8:B9999*(REST(ZEILE(A8:A9999);5)=3))
Gruß Werner
.. , - ...

Bild

Betrifft: OT@neopa: REST & AUSWERTEN
von: Luc:-?
Geschrieben am: 21.10.2015 20:16:36
Hallo, Werner;
du erinnerst dich sicher an unsere Diskussionen zur MatrixFktionalität. Dort hatte ich u.a. erwähnt, dass bei Fktt, die primär ein skalares Argument verlangen, die XlSteuerung dieses dann über den angegebenen Bereich oder das Datenfeld variierend bereitstellt. Bei einigen XlFktt ist sogar das HptArgument davon betroffen, wozu neben TEXT auch REST gehört. Bestimmte XlFktt wie bspw SUMMENPRODUKT sind in der Lage, das zu erkennen und das ganze Datenfeld zu verwenden, andere wie zB SUMME schaffen das nur mittels der XlSteuerung über MxFmlForm.
Nun gibt's aber noch eine andere Möglichkeit, solche Fmln zu berechnen, nämlich sie als FmlText AUSzuWERTEN. Entgegen meiner früheren Auffassung habe ich nun feststellen müssen, dass hierbei die XlSteuerung nicht wirksam wird. Das war mir bisher nur in VBA bei Anwendung der entsprd WorksheetFunction offensichtlich und logisch. Es gilt aber genauso für die vbFkt Evaluate und die dieser weitgehend entsprechende XLM-Fkt AUSWERTEN.
Folglich würde die benannte Fml =AUSWERTEN("REST(ZEILE(A8:A9999);5)") stets nur den EinzelWert 3 liefern, kein Wertefeld! Gleiches gilt sowohl für direkte MatrixKonstanten- als auch Bereichs­Angabe! Im konkreten Fall müsste die zusammenfassende Fkt mit in den FmlText einbezogen wdn, damit das Ergebnis richtig berechnet wird. Das ist im Falle von SUMMENPRODUKT wieder deren Pgm geschuldet. Allerdings kann hierbei auch gleichwertig SUMME verwendet wdn, denn die Namenssteuerung sorgt hier für das gleiche Ergebnis. Daraus muss geschlossen wdn, dass die Pgmm derartiger Fktt generell Datenfelder erkennen, nur folgen einige (hier SUMMENPRODUKT) dem aktiv, überprüfen also ihre Argumente bis zu einer bestimmten Stufe, während andere (hier SUMME) sich auf das verlassen, was ihnen die externe XlSteuerung bereitstellt. Bei benannten Fmln wird generell das ganze evtl gelieferte DFeld bereitgestellt, bei ZellFmln in diesem Fall nur bei MxFmlForm.
Falls jemand mal in die Lage kommt, so etwas mit FmlTexten, die eine derartige XlFkt enthalten, tun zu wollen, darf er sich über ein ggf falsches Ergebnis nicht wundern… ;-)
Gruß, Luc :-?

Besser informiert mit …

Bild

Betrifft: AW: es geht auch SUMME(...REST() ...) ohne {} ...
von: ... neopa C
Geschrieben am: 22.10.2015 17:25:52
Hallo Luc,
... man kann z.B. hier im Beispiel die Summe auch mit SUMME() und ohne Matrixformeleingabe und ohne Namensdefinition ermitteln und zwar mit einer "Matrixfunktion(alität)sformel"
Dazu stelle ich mal die aus meiner Sicht zwei "verschiedenartigen" Lösungsvarianten gegenüber:
Als "echte" Matrixformel: {=SUMME(A8:A999*(REST(ZEILE(A8:A999);5)=3))}
Und zwei Matrixfunktion(alität)sformeln:
=SUMME(INDEX(A8:A999*(REST(ZEILE(A8:A999);5)=3);))
=SUMMENPRODUKT(A8:A999*(REST(ZEILE(A8:A999);5)=3))
Favorisieren tue ich hier im Beispiel natürlich die SUMMENPRODUKT()-Formel, da die Matrixaufbereitung zur Summierung von der Funktion selbst gelöst wird und es keiner zusätzlichen Aufbereitung (egal ob "extern" oder "intern") wie bei den anderen bedarf, weil dies in der SUMMENPRODUKT()-Funktion schon intern implementiert ist. Damit dürfte oder genauer, da meinerseits nicht wirklich geprüft, sollte die Auswertung wohl aus formeltechnischer Sicht betrachtet, am effektivsten sein.
Gruß Werner
.. , - ...



Bild

Betrifft: Ja, sicher, Werner, ...
von: Luc:-?
Geschrieben am: 23.10.2015 19:57:16
…aber mir ging's ja auch eher um die Feststellungen, …
1. das XlFktt, die in einem Argument einen skalaren Wert erwarten, diesen weder von sich aus noch über den (Um-)Weg einer FmlText-Auswertung mit der entsprd XLM-Fkt in benannter Fml variieren. Das macht die fktsexterne XlSteuerung, aber nur, wenn entsprd viele Zellen ausgewählt wurden;
2. dass bei komplexeren Fmln, die eine XlFkt enthalten, der eine solche Konstruktion als Argument übergeben wird, es möglich sein kann, dass diese Fkt so pgmmiert ist, dass sie die Berechnung ihres Arguments stets bis zu einem gewissen Grad nachvollzieht und somit oft alle ErgebnisDaten verwendet, während andere Fktt sich hierzu auf die XlSteuerung verlassen, die aber nur dann alle Werte wiedergibt, wenn in ZellFmln Xl mitgeteilt wird, dass es sich um eine MatrixFml handelt (die Anzeige von umschließenden {} ist nur NutzerInfo und kann von anderer Calc-Software auch anders gelöst wdn!);
3. in benannten Fmln in diesem Fall oft ebenfalls alle Werte erzeugt wdn, auch ohne Kennzeichnung als MatrixFml in der ZellFml; vgl hierzu:
6 aus =ResteSumme für =AUSWERTEN("summe(rest(zeile(1:5);3))")
1 aus =SUMME(REST(ZEILE(1:5);3))
6 aus {=SUMME(REST(ZEILE(1:5);3))}
REST liefert hier von sich aus keine Ergebnismatrix, nicht mal als ausgewertete TextFml in benannter Fml, auch, wenn ihr Name als MatrixFml verwendet wird!
In diesem Zusammenhang habe ich mal versucht, die Zusammenarbeit einer XlFkt mit der XlSteuerung in VBA zu simulieren, was nur näherungsweise gelingen kann, weil VBA natürlich nicht die Möglichkeiten bietet, die die OriginalPgmmierung hat(te). Bei Interesse (evtl auch generell) reiche ich das noch nach, muss jetzt aber erstmal abbrechen, da anderweitig verpflichtet… ;-)
Gruß, Luc :-?

Besser informiert mit …
Für WP-Nutzer besonders zu empfehlen ihre dunkle Seite und dazu dann das als Test.

Bild

Betrifft: AW: ganz so sicher bin ich mir da nicht, denn ...
von: ... neopa C
Geschrieben am: 25.10.2015 11:29:32
Hallo Luc,
... momentan denke ich noch, wir haben da teilweise etwas aneinander vorbei geschrieben. Sei es drum.
Gruß Werner
.. , - ...

Bild

Betrifft: Ja, sieht ganz so aus, ...
von: Luc:-?
Geschrieben am: 27.10.2015 02:48:35
…Werner! ;-)
Habe mich nun entschlossen, doch noch das bereits vorbereitete Bsp mit UDFs hier bzw dort zu bringen.
Luc :-?

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Formel gesucht"