Formelalternative(n) gesucht ...

[slowboarder]

stimmt Luc, du bist ja auch jemand, der lieber anderen Rätsel stellt.

Gruß, Daniel

[Luc$:-?]

:-?

[neopa]

Hallo Luc,

ich hatte heute Morgen Daniels Formel nur in beide bereitgestellte Tabellenblätter eingesetzt und deren Ergebnisse überprüft. Nachdem ich seinen Schreibfehler gefunden und korrigiert hatte, stellte ich fest dass die Formel korrekte Ergebnisse ergibt. Da mich aber seine Lösungsformel wegen des ZÄHLENWENNS() nicht weiter interessiert hat, hatte ich sie noch nicht auf "Sortierstabilität" getestet. Diese ist natürlich auch wichtig. Aber sie wäre mE mit Daniels Formel auch gewährleistet.
Da die Formel eben sowieso noch nicht die gesuchte Formel ist, wäre eine von Dir hierzu festgestellte "Sortier-Instabilität" nicht entscheidend. Doch würde mich Deine Feststellung trotzdem interessieren, als dass ich meine Formel auch diesbzgl. nochmal testen würde wollen.

@Daniel,

Ohne ZählenWenns wirds schwierig.

wem schreibst Du das ;-) Dies war mein Grund warum ich zunächst in Herbers Forum und dann hier meine Fragestellung eingebracht habe.

Aber vielleicht findet die nächsten Tage doch noch ein Anderer eine Formellösung, die alle Bedingungen berücksichtigt. Aus diesem Grund stellte/stelle ich auch meine Formel hier noch nicht ein. Dies obwohl ich in Herbers Forum am 04.09 anderes angekündigt hatte. Je nach evtl. weiterer Beteiligung, stelle meine Formel nun spätestens am 17.09. hier im Forum ein, weil der thread in Herbers Forum da möglicherweise schon keine aktiven Beiträge mehr ermöglicht.

Hier sei noch ergänzend angemerkt, dass ich in meinem Eröffnungsbeitrag in Herbers Forum auch schrieb:
"Eine sinnvolle Strukturierung der Formel mittels benannten Formeln werde ich dann abschließend noch vornehmen, könnte aber auch bereits mit vorgeschlagen werden" Auch in insofern ist die Formellänge für mich nebensächlich.

Jetzt wünsch allen aktiven und passiven Teilnehmern an diesen thread noch einen schönen Restsonntag und einen guten Start in die neue Woche.

[slowboarder]

Hi Werner

wenn du jemanden suchst, der alle Bedingungen berücksichtigst, solltest du auch alle Bedingungen so konkret wie möglich beschreiben.
Wenn du schon eine Lösung hast und eine bessere suchst, dann solltest du diese auch zeigen, damit man weiß, in welche richtung man nicht gehen muss.

aber ich glaube kaum, dass du mit deiner Vorgehensweise da jemanden findest




und jetzt zeige bitte endlich deine Formel, wenn du uns schon hier für dich schuften lässt


Gruß Daniel

[neopa]

Hallo Daniel,

... im anderen Forum hatte ich auf eine ähnliche Aussage wie Deine eingangs hier folgendes geschrieben:

- Meine Aufgabenstellung scheint doch so schwierig zu sein, wie ich nicht nur vermutet sondern auch in meinem Eröffungsbeitrag hier im Forum so geschrieben hatte.
- Meine Zielstellung war und ist durch kompletter Vorgabe zu ermittelnder Ergebniswerte erkennbar.
- Ich habe niemanden aufgefordert für mich zu arbeiten bzw. seine Zeit für mich zu opfern. Sondern eingangs nur geschrieben: "Wer von Euch Lust und die nötige Zeit hat,..."

Das die gesuchte Formel auch in Excel 2002/2003 funktionieren soll (kann ich übrigens zur Zeit selbst nicht wirklich zeitnah prüfen) und damit ZÄHLENWENNS() leider ausgeschlossen ist, hatte ich heute Morgen nochmal präzisiert und auch das Deine Formel in neueren Excelversionen (dazu zähle ich alle Versionen ab 2007) mit ZÄHLENWENNS() die erwarteten Ergebniswerte erzielen, wie ich es heute Morgen auch schon kundgetan habe.

Die Einstellung meiner Formel hatte ich erst vorhin verschoben. Gut, ich terminiere ihn nochmals um. Sollte sich einschließlich Morgen hier niemand weiteres beteiligen (aber niemand sollte sich verpflichtet fühlen dafür zu "schuften" sondern nur wer Zeit, Lust und Laune hat), dann werde ich meine Formel nicht erst am 17.09.sondern nun am Dienstag den 12.09. einstellen. Bei diesem Termin belasse ich es nun aber.

[neopa]

Hallo @all,

heute stelle ich versprochen, meine Formel bereit. Luc hatte in seinem letzten Beitrag von gestern im anderen Forum indirekt darum gebeten, die Formel heute noch nicht zu veröffentlichen. Da sich bisher außer ihm und Daniel niemand weiter dazu geäußert hat, hab ich nachfolgenden Kompromiss getroffen. Zeitgleich zu diesem Beitrag stelle ich Daniel per PN meine Formel bereit. Die Veröffentlichung dieser hier im thread werde ich erst kommendes Wochenende hier vornehmen. Dieser Kompromiss sollte mE allen gerecht werden.

[Luc$:-?]

Nach dem, was Du nun bei Herber geschrieben hast, Werner,
ist mir das inzwischen auch egal (Näheres später dort). Wenn nach über einer Woche die grundlegende Ermittlungsregel (nicht die Fml!) immer noch nicht klar ist, lasse ich es, zumal ich nicht (inzwischen 100e von) UDFs schreibe, um dann mit Steinzeit-Methoden arbeiten zu müssen. Das ist eher was für experimentelle Archäologen! Mein Ansatz mit Chance auf Kürze und evtl auch Schnelligkeit, kommt ohnehin nicht gänzlich ohne von Dir ausgeschlossene Methoden aus und scheint außerdem nicht Deiner Ermittlungsregel zu folgen, auch, wenn ich gleiche Ergebnisse bei allem bisher von Dir Gezeigten erziele...
Gruß, Luc :-?

[Luc$:-?]

...hast Du recht, Werner,
und Daniels Fml liefert in allen Fällen das zu erwartende Ergebnis, denn sie zählt tatsächlich den jeweiligen BZR bis zur relevanten ZR-Ebene durch. Insofern ist das schon ziemlich raffiniert, vor allem die Ausnutzung der ObjektRückgabe durch INDEX (wozu ich auch schon längst eine Demo fertighaben wollte...)!
Das ich erst falsche Ergebnisse hatte, lag nicht an meiner Fml-Anpassung, sondern an einer FehlEinschätzung ihrer MatrixFktionalität, die aus ihrem Einsatz in der sortierten TestVariante herrührte (inzwischen hat Xl auch die Rechenlust verloren, zumindest lt jedesmal aufpoppender Mitteilung...).
Aber wie gesagt, dazu später dort mehr, aber wohl keine neuen Bspp, denn die kann ich mir ja jetzt sparen. Aber viell ein Vorschlag, der evtl den Aufwand verringern könnte (falls Du da nicht ohnehin schon drauf verfallen bist). Aber Daniels Fml mit Xl9-11-Mitteln nachstellen (und ohne UDF - AUSWERTEN ist ungeeignet, weil weder aktiv noch passiv matrixfmlfähig), wird wohl stets sehr aufwendig. Deshalb wird wohl so oW eher keine Optimierung Deiner Fml möglich sein...
Morrn, Luc :-?

[neopa]

Hallo Luc,

mit Deiner aktuellen Feststellung wären auch unsere bis dahin offenen diesbzgl. Differenzen abschließend geklärt.

INDEX() war und ist natürlich auch in meiner Formel der Basispfeiler. Und mittlerweile bin ich dank Daniels Formel und Deiner Bemühungen auch überzeugt, dass unter den von mir vorgegebenen Bedingungen an meiner Formel wohl eher nur noch Detailverbesserungen möglich sein dürften. Insofern betrachte ich meine Problemstellung als erledigt.
Euch beiden deshalb dafür noch mal meinen Dank.

Ich interpretiere Deine letzten beiden Aussagen nun so, dass ich meine Formel nun doch schon vor dem Wochenende einstelle.

Nun, meine Formel in E3 (in Langform mit den 586 Zeichen):

Code: Alles auswählen

{=MAX(MMULT((MTRANS(B$3:B3)<=ZEILE(INDEX(A:A;MAX(MIN(B$3:B$100);W$1-TAG(W$1)+1)):INDEX(A:A;MIN(MAX(C$3:C$100);W$1-TAG(W$1)+300-TAG(W$1-TAG(W$1)+300)))))*(ZEILE(INDEX(A:A;MAX(MIN(B$3:B$100);W$1-TAG(W$1)+1)):INDEX(A:A;MIN(MAX(C$3:C$100);W$1-TAG(W$1)+300-TAG(W$1-TAG(W$1)+300))))<=MTRANS(C$3:C3))*(B3<=ZEILE(INDEX(A:A;MAX(MIN(B$3:B$100);W$1-TAG(W$1)+1)):INDEX(A:A;MIN(MAX(C$3:C$100);W$1-TAG(W$1)+300-TAG(W$1-TAG(W$1)+300)))))*(ZEILE(INDEX(A:A;MAX(MIN(B$3:B$100);W$1-TAG(W$1)+1)):INDEX(A:A;MIN(MAX(C$3:C$100);W$1-TAG(W$1)+300-TAG(W$1-TAG(W$1)+300))))<=C3)*MTRANS(A$3:A3=A3);ZEILE(B$1:B1)^0))}

Ohne es aktuell überprüfen zu können (kann ich wohl erst nächste Woche) sollte diese und die daraus abgeleitete nachfolgende, mit benannten Formeln strukturierte, genau so in Excel 2002/2003 anwendbar sein.

Einsetzen würde ich in E3 demnach:

Code: Alles auswählen

=Nr

Meine benannte Formel Nr ist wie folgt definiert:

Code: Alles auswählen

=MAX(MMULT((MTRANS(!B$3:B3)<=ZeitRaum)*(ZeitRaum<=MTRANS(!C$3:C3))*(!B3<=ZeitRaum)*(ZeitRaum<=!C3)*MTRANS(!A$3:A3=!A3);ZEILE(!B$1:B1)^0))

Die darin genutzten weiteren 3 benannten Formeln so:
Zeitraum:

Code: Alles auswählen

=ZEILE(INDEX(!A:A;Anf):INDEX(!A:A;End))

Anf:

Code: Alles auswählen

=MAX(MIN(!B$3:B$100);!W$1-TAG(!W$1)+1)

End:

Code: Alles auswählen

=MIN(MAX(!C$3:C$100);!W$1-TAG(!W$1)+300-TAG(!W$1-TAG(!W$1)+300))

Zeitgleich stelle ich dies so noch im thread in Herbers Excel-Forum hier: http://www.herber.de/forum/archiv/1576t ... 578462.htm ein.

[Josef B]

Hallo Werner

Ich konnte mich leider erst diese Woche intensiv mit deiner Anfrage beschäftigen.

Ich verstehe leider nicht ganz, was für dich eine optimale Lösung ist.
Das du den Start und den Endbereich der Berechnung mittels benannten Formeln "verstecken" willst, finde ich, wie auch Daniel, für den Benutzer nicht optimal.

Hier nun meinen Vorschlag nach deinen Angaben

Code: Alles auswählen

{=WENN(ODER(C3<=W$1-TAG(W$1);B3>DATUM(JAHR(W$1);MONAT(W$1)+9;0));"";MAX(MMULT((ZEILE(INDEX(A:A;MAX(B3;1+W$1-TAG(W$1))):INDEX(A:A;MIN(C3;DATUM(JAHR(W$1);MONAT(W$1)+9;0))))>=MTRANS(B$3:B3))*(ZEILE(INDEX(A:A;MAX(B3;1+W$1-TAG(W$1))):INDEX(A:A;MIN(C3;DATUM(JAHR(W$1);MONAT(W$1)+9;0))))<=MTRANS(C$3:C3*(A$3:A3=A3)));ZEILE(Z$3:Z3)^0)))}

Hier, so wie ich das machen würde:

In E3

Code: Alles auswählen

{=WENN(ODER(C3<X$1;B3>Y$1);"";MAX(MMULT((ZEILE(INDEX(A:A;MAX(B3;X$1)):INDEX(A:A;MIN(C3;Y$1)))>=MTRANS(B$3:B3))*(ZEILE(INDEX(A:A;MAX(B3;X$1)):INDEX(A:A;MIN(C3;Y$1)))<=MTRANS(C$3:C3*(A$3:A3=A3)));ZEILE(Z$3:Z3)^0)))}

In X1:

Code: Alles auswählen

 =W1-TAG(W1)+1

In Y1:

Code: Alles auswählen

=DATUM(JAHR(W$1);MONAT(W$1)+9;0)

Gruss Sepp

[neopa]

Hallo Sepp,

super, Deine Formel komprimiert den Lösungsansatz und soweit ich das momentan feststellen kann damit auch die Auswertungszeit.

Den Start und den Endbereich der Berechnung wollte ich keinesfalls mittels mittels benannten Formeln "verstecken". Für die Nutzung soll der ZeitRaumBereich zwar dynamisch aber dafür fix bleiben. Es sollen und dürfen im Tabellenblatt durch den Endnutzer aber auch Zeilen und Spalten (außer A:C) gelöscht werden dürfen. Da ich darauf keinen Einfluss habe, wollte und will ich mich mit der Auswertung auf die etwas "sichere Seite" begeben und hatte deshalb keine Hilfszellen (außer W1) als Bedingung ausgewiesen jedoch benannte Formeln ausdrücklich zugelassen.

Vielen Dank für Deine Formellösung. Ich hatte Deinen Beitrag erhofft, der mir nun auch das angestrebte ergeben hat

[lupo1]

Den alten Hasen braucht man es vermutlich nicht zu sagen, aber sicherheitshalber sei noch mal festgehalten:

Contra:
- benannte Formeln kürzen nichts, weder bei der Größe der Datei, noch beim Rechenaufwand (alles wird genauso gerechnet, als hätte man keine eingesetzt). Somit sind sie niemals Hilfszellenersatz.

Pro:
- benannte Formeln ermöglichen aber mehr als 8 (31) Funktions- (Klammerungs-) ebenen in xl2003-, was manchmal nötig ist. Ohne Ausreißer zu nennen, bewegen sich bspw. meine Formeln maximal auf etwa 10 Funktionsebenen.
- außerdem ist ihre Vektoreigenschaft wertvoll gegen Kompromittierung von Modellen (Löschen oder Einfügen von Zeilen und damit einhergehende Bezugsverformung)
- ohne sie ist der Einsatz von Excel4Makrofunktionen nicht möglich (außer: UDF)
- wenn sie auch nicht Hilfszellenersatz sein können, so kann man mit ihnen doch immerhin Funktionen sauber über Ebenen strukturieren. Als Beispiel die Gauß'sche Osterformel, die man auch mit benannten Formeln statt mit Hilfszellen verwenden kann (Anmerkung: Die Mehrfachberechnungen ggü. der dort genannten optimalen Hilfszellenlösung sind angesichts rein mathematischer Funktionen nicht so gravierend)

[neopa]

Hallo Lupo,

Prinzipiell sehe ich das auch so.
Ergänzend hierzu: Deine Angabe zum Contra ist mE noch nicht präzise. Gemeint ist Deinerseits sicherlich, dass benannte Formeln 1:1 anstelle von Hilfszellen keinerlei Einsparung ergeben. Allerdings können benannte Formeln auch viele Hilfszellen ersetzen, u.a. wenn sie in mehreren/vielen Formeln zum Einsatz gelangen. Dann reduziert sich wenigstens die Dateigröße und verbessert die Übersichtlichkeit.

Und bei Deiner Auflistung der Pro-Punkte hätte ich vielleicht ein etwas andere Reihenfolge vorgenommen aber auch einen Punkt noch ergänzt: Es ist mE schon von Vorteil, wenn eine Teilformel einen direkt zugewiesen "Namen" hat, um leichter/schneller eine Formel auch zu einem späteren Zeitpunkt (wieder) zu verstehen.

Man kann und sollte also immer abwägen.

[Luc$:-?]

Da geht's doch nicht darum, Leute,
irgendwas vor Nutzern zu verstecken; wer so etwas verwendet, kennt sich ja damit aus, und die Xl-Dödel haben einfach die Finger davon zu lassen. Außerdem ist das eine eigene, in ihren Wirkungen wenig bekannte Welt, die zu durchforschen schon interessant sein kann, denn hier übernimmt die Xl-Steuerung weniger, was in seinen Auswirkungen mitunter Pgmmierung näher kommt. Interessant wäre auch, ob das Auswirkungen auf die Rechengeschwindigkeit haben könnte, falls dadurch bestimmte FmlTeile seltener berechnet würden. Ansonsten kommen sicher noch Lupos Pro-Argumente hinzu.
Meine abschließende Lösung verfolgt einen anderen Ansatz (s. meine Bemerkung im o.g. Herber-Original-Thread ganz unten), wobei ich natürlich nicht weiß, ob sie schneller als die anderen Lösungen ist, weil ich für die keine (Test-)Zeit hatte. Auf jeden Fall war sie wahrscheinlich die arbeitsaufwendigste. So etwas mache ich bestimmt nicht so schnell wieder...
Aber die Arbeit daran hat auch tiefere Einsichten in den defNamen-Raum gebracht, weshalb sie nicht wirklich umsonst war...
Morrn, Luc :-?

[lupo1]

Ergänzend hierzu: Deine Angabe zum Contra ist mE noch nicht präzise. Gemeint ist Deinerseits sicherlich, dass benannte Formeln 1:1 anstelle von Hilfszellen keinerlei Einsparung ergeben. Allerdings können benannte Formeln auch viele Hilfszellen ersetzen, u.a. wenn sie in mehreren/vielen Formeln zum Einsatz gelangen. Dann reduziert sich wenigstens die Dateigröße und verbessert die Übersichtlichkeit.

Nein, auch die Dateigröße reduziert sich nie dadurch. Teste es einmal selbst. Selbst, wenn die benannten Formeln seit .xlsx tatsächlich in der geschriebenen Datei hier vielleicht aufgrund der Kompression Wiederholungen erkennen und eliminieren (bei .xls jedenfalls ist das nicht so), so benötigt das Modell im Hauptspeicher weiterhin jeden einzelnen Platz, wie ohne benF. Es wird nur "Packpapier um die Formel gemacht", durch welches man nicht durchschauen kann, aber es wird keinerlei Luft heraus gelassen.

Excel beherrscht nämlich bis heute keine virtuellen Variablen im Tabellenbereich (gemeint: außerhalb von VBA). Laurent Longré war (seltsamerweise) der bislang einzige, der genau das mit dem dortigen PUT und GET in MOREFUNC anging. Das wäre ein mächtiges Werkzeug, bis vielleicht gleich hin zur Rekursion ... (kommt gleich noch in meine Excelwünsche-Rubrik). Man könnte Zwischenschritte in der Formel mit PUT aufbewahren und mit GET weiterverwenden. Man könnte damit den Einsatz von VBA-Functions zurückdrängen und in der schnelleren eigenen Excel-Sphäre bleiben.

Ich veranschauliche das nochmal anhand der Osterformel (nicht testbar und unreal):

in A1 steht: Jahreszahl, z.B. 2017
in A3 steht =GANZZAHL(A1/100)
in A4 steht =REST(19*REST(A1;19)+A3-GANZZAHL(A3/4)-GANZZAHL((A3-GANZZAHL((A3+8)/25)+1)/3)+15;30)
in A5 steht =REST(32+2*REST(A3;4)+2*GANZZAHL(REST(A1;100)/4)-A4-REST(REST(A1;100);4);7)
in A6 steht =A4+A5-7*GANZZAHL((REST(A1;19)+11*A4+22*A5)/451)+22
in A7 steht =TEXT(WENN(A6-31 < 1;A6;A6-31);"0#")&"."&WENN(A6 > 31;"04.";"03.")&A1

Einfachheitshalber nenne ich - anders als Longré - die PUT's und GET's hardcodiert, also nicht indiziert, entsprechend der obigen Zellen, damit der Klammer- und Parameterwust etwas geringer wird:

=TEXT(WENN(
PUT_6(GET_4+
PUT_5(REST(32+2*REST(GET_3;4)+2*GANZZAHL(REST(A1;100)/4)-
PUT_4(REST(19*REST(A1;19)+
PUT_3(GANZZAHL(A1/100))
-GANZZAHL(GET_3/4)-GANZZAHL((GET_3-GANZZAHL((GET_3+8)/25)+1)/3)+15;30)-REST(REST(A1;100);4);7))-7*GANZZAHL((REST(A1;19)+11*GET_4+22*GET_5)/451)+22)-31 < 1;GET_6;GET_6-31);"0#")&"."&WENN(GET_6 > 31;"04.";"03.")&A1

Wir sehen 4 virtuelle Variablen, die die 4 Hilfszellen A3:A6 ersetzen. Es gibt nur noch die direkte Referenz auf A1, denn A3:A6 fallen weg! Die Formel ist kaum länger, als die optimale Kürze, die die Hilfszellen in Summe bieten.

Worauf ich nicht geachtet habe: Möglicherweise müssen innere Berechnungen auf einer Formelebene zuerst die Variable PUTten, um sie "außen" zu GETten (kann auch sein, dass diese beiden Begriffe andersrum verwendet werden). Da Longré es ja versucht hatte, müsste er sich darüber Gedanken gemacht haben. So einfach ist das nämlich nicht, da es in einer Formel nicht unbedingt hierarchisch zugehen muss. Auch in VBA (allgemein: Code) kommt es ja entscheidend auf die Reihenfolge an, in welcher Variablen etwas zugewiesen wird und sie dann verwendet werden. Excel müsste also, um den Nutzer davon zu befreien bzw. um das Konzept überhaupt zu ermöglichen, die Formel-PUTs zunächst rauslesen, bevor es die GETs ab dann in beliebiger Reihenfolge einsetzt. Denn einmal definierte Variablen wären garantiert statisch, im Gegensatz zu VBA-Functions.