Formelalternative(n) gesucht ...

[Luc$:-?]

Bei meinen Tests ist auch klar geworden, Lupo & Werner (& all),
dass die Xl-Steuerung jede Art von 2dimensional abbildbarem regulär-rechteckigem Array (Matrix oder Vektor, auch Vektoren aus Vektoren) automatisch normiert. Damit kann selbst eine UDF, die garantiert den letztgenannten Typ erzeugt, diesen nicht bewahren, weder bei direkter Abbildung auf einen ZellBereich noch als Datenfeld für ein Argument einer anderen Fkt oder pur in einer ZellFml. Per SubProzedur erzeugte derartige Felder müssen aber stets in der SubProz selbst normiert wdn, bevor sie in einem Rutsch auf einen ZellBereich übertragen wdn können.
Damit ist auch klar, dass die Xl-Steuerung nichts mit 3dTensoren und anderen exotischen Datenfeldern anfangen kann. Wdn diese in einer Fml verwendet, führt das unweigerlich zu einem Fehler, der bis zum EndErgebnis weitergereicht wird, falls er nicht zuvor durch einschlägige Verfahren eliminiert wird. Wird so etwas durch eine UDF erzeugt, darf sie es nicht in dieser Form weitergeben. Die 3dDatensammlung mittels N(INDIREKT(-Verwendung erzeugt demggüber keine echten 3dTensoren, sondern eher ein Bündel von Matrizen (über einen Vektor angesteuert), was ebenfalls an die festgestellte Praxis benannter Fmln erinnert. Insofern würde das dann auch zusammenpassen. INDEX ist so etwas ja bekanntlich nicht möglich und alle anderen 3d-fähigen Xl-Fktt liefern ja (aus gutem Grund) auch stets verdichtete EndErgebnisse, niemals einen 3dTensor, denn der ließe sich nicht auf einen normalen ZellBereich abbilden (mittels eines Stapels von MatrixKonstanten, ggf unter Inanspruchnahme spezieller VerbundZellen, aber schon, was aber unüblich und in Xl standardmäßig nicht vorgesehen ist).
Bei diesen Tests wurde auch noch einmal deutlich, dass Xl und vbXl hier analog arbeiten, ZellBereiche sind stets 2dimensional, auch, wenn es sich nur um einen Zeilen- oder SpaltenBereich handelt (nur EinzelZellen sollten auch eher 1- als 0dimensional sein, aber das ist eine Interpretationsfrage, da hier ohnehin nur Zeilen u/o Spalten gezählt wdn können). MatrixKonstanten hingg wdn wie ihre VBA-Pendants behandelt: horizontale Vektoren sind 1-, vertikale Vektoren und Matrizen stets 2dimensional (für höhere Tensoren existiert keine offizielle Notationsform, weil ohnehin standardmäßig nicht verwendbar). Nur reine SkalarKonstanten sind 0dimensional (zB eine beliebige Zahl, die direkt in einer Fml verwendet wird), ein 1elementiger Vektor dagg 1dimensional, zB {1} bzw ZEILE|SPALTE(A1). Mehrzellige Bereiche wdn aber auf Grund der Xl-Regie nur in MatrixFmln erkannt, MatrixKonstanten dagg immer.
Morrn, Luc :-?
PS @ Werner: Ich stelle Dir in Deinem Herber-Thread gleich noch eine kurze Nachricht ein, da es vor allem mit der abgespeckten Variante meiner diesbzgl TestDatei noch etwas dauern wird.

[neopa]

Hallo Luc,

ehrlich, bereits Deinen Ausführungen aus den vergangenen Tagen konnte ich (wie Du weißt, bin ich lediglich Hobby-(Formel-)-Excelianer ohne jegliche VBA- noch sonstige Programmierpraxis und -erfahrung) schon nicht ganz folgen, bei Deiner heutigen letzten nun fast gar nicht mehr.

Ich hatte aber gestern ein paar Anmerkungen zu Deinen vorangegangen Ausführungen versprochen, den ich nun nachkomme. Konkret zu Deinen Aussagen vom 19. Sep 2017, 01:09:

zu 2.) erklärt mir aber noch nicht meine Aussage vom 18. Sep 2017, 14:29 gegenüber Lupo. Hier gab es keine "Historie" vor dem sofortigen Wieder-Abspeichen der Datei.

zu 4.) wie von mir bereits an Lupo geschrieben, kann es aber doch zu Problemen bei Löschungen von Zeilen/Spalten kommen. Habe es eben an meiner hier am 17.09. eingestellten Datei: test_ben_Formel.xls auch nachvollziehen können. Brauchst da nur mal Spalten vor Spalte W zu löschen. Bei mir in Excel 2010 kommt es zu einer Meldung: "Ressorcenproblen" und in den ben. Fml. Anf und End hat sich der Bezug auf W1 auch nicht geändert. Das gleiche passiert übrigens auch bei lediglich Verschieben der Zelle W1 und auch wenn die benannten Formeln nicht global sondern nur lokal definiert sind.

zu 7) Dein aufgeführtes Beispiel ist mE nicht anderes, als wenn Du in eine beliebige Zelle: =INDEX(Tabelle1!$A$1:$A$10*Tabelle1!$B$1:$B$10;ZEILE(A1)) schreibst und diese nach unten kopierst. Das ist "normale" INDEX()-Matrixversionsfähigkeit nicht mehr aber auch nicht weniger.

[Luc$:-?]

...Werner:
Vorbemerkung: So wie Dir wird's wahrscheinlich sehr vielen NormalNutzern bzgl meiner Ausführungen gehen, weshalb evtl eine Quintessenz erforderlich wäre... ;-)
Zu Deinen PktBezügen:
2. Lupo war ja bereits 'zurückgerudert'; hier sollte auch nur festgestellt wdn, dass es sinnvoller ist, eine .xlsx- mit einer .xlsb-Datei zu vgln. Fehlen in letzterer jedwede VBA-Pgmm und XLM-Fktt, wird sie idR kleiner als die erstere sein, wenn beide keine oder die gleichen HistorienInfos enthalten (was für Letzteres eher unwahrscheinlich ist, aber man kann diese ja auch löschen!). Zip hin oder her, es kommen bei .xlsm-Dateien noch diverse XML-Infos dazu und außerdem soll das Ganze dann ja auch universell mit anderen Systemen austauschbar sein. Das sollte den größeren Umfang erklären (was durch zippen eingespart wird, wird durch die Universalität wieder reingeholt und meist noch übertroffen).
4. Ja, das ist so und hat mit dem NamensKonzept zu tun. Das unterscheidet sich vom TabBlatt-Konzept in einem wesentlichen Pkt: Namen, in deren Bezug relative Adressen verwendet wdn, beziehen sich auch in der Wiedergabe im TabBlatt immer auf genau diesen Standort ihrer Notation dort. Folglich wird eine benannte Fml diesbzgl an den Standort der ihren Namen verwendenden Fml angepasst. Demzufolge wäre eine Abänderung solcher Bezüge im Prinzip unnötig, denn eine diesen Namen verwendende Fml im TabBlatt liefert nur in einer exakt korrespondierenden Zelle, Zeile bzw Spalte den richtigen Wert. Wird zB in A1:J10 eines Blattes eine Tabelle notiert und dafür eine SummenFml als benannte Fml mit relativer Adressierung angelegt, also =SUMME(Tab1!A1:J10), kann keine Fml, die ihren Namen verwendet, in einer Zelle auf dem gleichen Blatt angelegt wdn. Ihr Ergebnis wäre immer falsch! Auf einem anderen Blatt müsste diese Fml dann aber auch genau in A1 stehen, wenn diese Summe herauskommen soll. Mit jeder FolgeZelle verschiebt sich der summierte Bereich um eine Zeile u/o Spalte! Die ursprüngliche Bereichsangabe für A1 entspräche dann quasi nur Unter- und Obergrenzen des SummierungsBereichs, denn darüber hinaus würde eine solche Fml nur noch 0 liefern. Dass diese Grenzen nun nicht automatisch geändert wdn, wenn auf dem Daten- bzw AuswertungsBlatt Zeilen oder Spalten gelöscht oder hinzugefügt wdn, kann/muss Absicht sein, denn der Nutzer, der so etwas anwendet, sollte auch das Konzept dahinter kennen! Tut er so etwas also, muss das ebenfalls von ihm beabsichtigt sein, davon gingen die Erfinder wohl aus! Will man eine automatische Adress(-Teil-)Anpassung erreichen, muss man folglich (teil-)absolute Adressierung verwenden!
Auf diese Weise kann man bei Beachtung dieses Konzepts und entsprd angelegter MappenStruktur mit strikter Trennung von Daten- und AuswertungsTeil und intelligent gewählten Bezügen in der benannten Fml und ebenso gewählten Standorten im Berechnungsblatt leicht umgekehrte Kumulationen erzeugen, d.h., die Endsumme, zB Dezember, müsste ganz oben stehen, und die erste Einheit (Januar) ganz unten. Das fktt aber natürlich nur, wenn auf dem DatenBlatt auch nur die dafür benötigten Zeilen und Spalten genutzt wdn! Kommen nun DatenZeilen u/o -Spalten hinzu, wdn die derart sortiert ältesten nicht mehr einbezogen; umgekehrt (Löschung) können andere, neue Daten einbezogen wdn. Das sieht mir aber doch sehr nach Absicht aus! Unbedarfte Eingriffe könnten hierbei aber natürlich das ganze schöne Konzept aushebeln. Insofern ist das also nur etwas für Leute, die genau wissen, was sie tun, und hat mit Fmln (oder ihren Teilen) vor den Nutzern verstecken herzlich wenig zu tun!
Damit wäre auch eine Normierung aller NamensBezüge auf A1 nicht unbedingt erforderlich, es sei denn zu DokuZwecken (mit dem Xl-Tool).
Übrigens, in meiner auf das ursprüngl Problem bezogenen Datei hat eine Verschiebung von W1 keinerlei Auswirkungen, weil die benannten BZRanf- und -end-Fmln (voll-)absolut adressiert wurden, sich also dem anpassen! ;-]
Hier gilt also eher, soviel (teil-)absolut adressieren wie möglich, nicht wie nötig wie im TabBlatt!
7. Jein, nicht ganz! INDEX wird zwar im HptArgument eine ganze Matrix (als Datenfeld!) übergeben, die nachfolgd Argumente verlangen aber Einzelwerte, skalar oder als 1elementiges Datenfeld wie es bspw von ZEILE() und SPALTE() geliefert wird. Gibt man dort ebenfalls ein Feld an, variiert die Xl-Steuerung die über ihren Rahmen (und bei voll-relativer Adressierung wg der AutoAnpassung auch darüber hinaus; logisch ist hierbei, dass, wenn man ZEILE(1:$9) schreibt, ab Zeile10 die Zeile 9 wiederholt wird, und andersherum bzw voll-absolut adressiert, es immer Zeile1 ist). Insofern besitzt INDEX nur eine bedingte MatrixFktionalität, nämlich dann, wenn man Zeilen- u/o SpaltenArgument 0 setzt (das klappt aber schon beim 4., dem BereichsArgument nicht mehr, was aber der DarstellungsProblematik geschuldet sein wird bzw kann). Wendet man INDEX auf das jetzt hier genannte Bsp an, also auf die benannte SummenFml, fktt es nur mit diesen 0-Argumenten, wohl weil in diesem Fall kein Datenfeld wie beim MultiplikationsBsp aufgebaut wird, sondern sich nur die Grenzen verschieben, was man genauso auch mit dieser SummenFml (ohne INDEX) in einer ZellFml erreichen könnte (ebenfalls mit Wiederholung der GesamtSumme in allen datenhaltigen Spalten von FolgeZeilen und von 0 in darüber hinaus gehenden Spalten, was auf die Xl-übliche Bevorzugung buchhaltungsgerechter vertikaler SummenBildung hinzuweisen scheint, weshalb ja auch TEILERGEBNIS und AGGREGAT nur auf das Ausblenden von Zeilen entsprd reagieren!). Auch unter Einbeziehung von INDEX wäre das Verhalten genau gleich und eine plurale MatrixFml würde in beiden Fällen stets nur die GesamtSumme liefern.
Im vorherigen Bsp wird aber eine ganze DatenfeldMatrix (im Namensraum) gebildet, für die deshalb sehr wahrscheinlich ein ganzer ZugriffsVektor angelegt wird. Die Elemente, auf die er verweist, können und müssen dann auch mit INDEX einzeln abgerufen wdn, anderenfalls erhielte man stets den 1.Wert der ganzen Matrix. Das ist bei der ZellFml zwar formal alles genauso, inkl Verhalten als plurale MatrixFml, aber ich bezweifle ja, dass Xl die Fml-Optimierung auch über Zellgrenzen hinaus betreibt, falls die jeweilige Fml normal oder eine singulare MatrixFml ist. In benannten Fmln ist diese Unterscheidung aber irrelevant. Hier wird immer alles berechnet. Die Auswahl der wiederzugebenden Ergebnisse erfolgt dann über die FmlEintragung im TabBlatt. D.h., gibt man in diesem Fall nur den Namen pro Zelle oder für alle Zellen als plurale MxFml an, hat Xl keine Info darüber, welcher Wert wiedergegeben wdn soll, und verwendet deshalb den ersten. Mit INDEX kann aber ein bestimmter Wert ausgewählt wdn. Im ZellFml-Fall gehe ich demggüber davon aus, dass Xl in jeder Zelle den gesamten Vektor berechnet, der INDEX zu übergeben ist, und erst dann einen Wert auswählt. Es könnte aber auch sein, dass INDEX erst die Werte auswählt und dann berechnet (in Analogie zu WENN & WAHL, aber das ist eher nicht miteinander vglbar). Aber das glaube ich nicht, da es dem Konzept des Fml-Interpreters widersprechen würde, der das HptArgument ja hier erst mal zur Berechnung übergeben müsste (das könnte eher bei reinen BereichsBezügen der Fall sein!) und erst danach auswählen könnte. Alles andere wäre ungleich komplexer (und WENN und WAHL machen das bei Datenfeldern als Auswahl-Argument ja auch nicht!). In einer pluralen MxFml sähe das natürlich anders aus, denn das HptArgument würde sicher nur 1x berechnet und dann alle diese Werte auch per Auswahl=0 zellenweise zurückgegeben.
Genau das scheint auch Lupo mit seinem, zugegebenermaßen leicht hinkenden*, Zug-Vgl (und der Alzheimer-Bemerkung) zu meinen, denn das entspräche einer geschlossenen AblaufLogik, die auch von speziellen Fktt nicht oW durchbrochen wdn kann (mit einer UDF übrigens auch nur nachträglich, was der Nutzer sicher nicht bemerken würde, aber Xl berechnet vorher trotzdem alles und dann nochmal den Teil, also quasi das 1.Mal umsonst!). (Beachte, FmlTexte müssen erst erkannt, dann interpretiert und in der Fml vorhandenen Fktt mundgerecht serviert wdn, bevor ein EndErgebnis berechnet wdn kann!)
* Besser wäre es wohl, das mit der jedesmaligen Anfertigung einer ganzen ProduktPalette zu vgln, obwohl nur Bestellungen für bestimmte Produkte vorliegen!
Deshalb halte ich plurale MatrixFmln, zumindest bei größeren Datenmengen, für performanter als ihre künstliche Aufsplittung in EinzelWerte. Aber genau in diesen Fällen ist der EinzelWert flexibler, weshalb dann auf benannte Fmln zurückgegriffen wdn sollte. INDEX erhält dann jedesmal ein nur 1x berechnetes, fertiges Datenfeld und muss aus diesem nur noch auswählen.
Es sollte mich doch sehr wundern, wenn die Xl-Macher hier anders vorgegangen wären, denn das würde doch irgendwie an Mystik erinnern und die ist in der EDV (noch!) weder üblich noch möglich. (Mit echter KI mag's einstens anders aussehen...)
Deshalb ist auch nur der primäre Zugriff auf Zellen und ihre Bereiche 100%ig eindeutig und durchschaubar, weshalb der auch die Grundlage meines FAN-Konzepts bildet (ZwischenErgebnisse für andere Berechnungen müssen deshalb immer in ZellBereichen - oder viell auch mal benannten Konstanten bzw internen Variablen - abgelegt wdn).
Morrn, Luc :-?

[neopa]

Hallo Luc,

zu 2. Für mich nur teilweise plausibel. Hab meinerseits heute dazu noch folgendes festgestellt:
Es gibt möglicherweise ein Problem mit neueren Excelversionen (nach 2003) mit Lesen und Schreiben von alten xls-Dateien mit benannten Namen.
Konkret bei mir: Wenn ich in meiner XL2010 meine hier im thread eingestellte test_ben_Formel.xls einlese und diese unmittelbar danach als xlsx-Datei abspeichere scheint zunächst noch alles ok zu sein. Dann schließe ich diese Datei und wenn ich danach diese wieder öffne, sagt Excel mir, das eine Ressourcenproblem vorliegt. Da muss doch was faul sein

zu 4. Deine bisherigen Aussagen hierzu überzeugen mich nicht wirklich.
Wie ich schon schrieb, ist es das von mir geschilderte Problem (z.B. nach Löschen von Zeilen und Spalten vor einer Bezugszelle einer benannten Formel) unabhängig davon, ob die Formel lokal oder global definiert ist. Ich erweitere jetzt: dies ist auch unabhängig ob ein Zellbezug relativ oder absolut gesetzt wurde.
Weiter herausgefunden hab ich, dass ich meine frühere Aussage, wonach wohl nur "innere" benannte Formeln eingebettet in anderen benannten Formeln betroffen wären, revidieren muss. Hab nämlich festgestellt, dass das Problem auch bei nicht gestapelten benannten Formeln auftreten kann.
Meine vorläufige (ungesicherte) Erkenntnis ist, dass die Problematik wohl vor allem bei Zellbezügen in benannten Formeln auftreten kann, wo diese Formel als normale Zellformel als Matrixformel abgeschlossen werden müsste.

zu 7. sorry, hier kann ich Deinen Ausführungen wieder nicht folgen, Zumindest für mich sind diese viel zu theoretisch.

[lupo1]

meine XL-2010-Version ist 14.0.7188.5002

Meine auch. 32 bit.

[neopa]

Hallo Lupo,

ja, auch ich hab die 32bit Version in Einsatz. Kannst Du meine aktuellen Feststellungen zu 2. nachvollziehen?

[lupo1]

Hm ... ich hatte ja allgemein vor !A1 gewarnt. Und im Besonderen hatte ich (xl2010) festgestellt, dass, wenn ich einen definierten Namen mit !A1 editieren will, in manchen Fällen die Bezüge verschoben sind. Unter xl2000 gab es andere Beobachtungen zw. Fehlfunktionen damit. Daher habe ich es gelassen. Links zum Thema sind verschwunden oder nicht auffindbar.

Ich konnte die richtigen Bezüge nur über einen anderen Aufruf sicherstellen.

[Luc$:-?]

Hallo, Werner;
zu Deinen neuen Anmerkungen:
2. Da scheint tatsächlich etwas faul (nur bei Dir?) zu sein oder es liegt tatsächlich ein KonvertierungsFehler vor, denn normalerweise sollte das nicht sein. Oder das RessourcenProblem entsteht anderweitig...
4. Persönliche Überzeugung ist das Eine, Realität das Andere! ;-)
Ich hatte das natürlich zuvor getestet und bin dadurch zu meinen AnalyseAussagen gekommen. Wenn nicht wenigstens (teil-)absolut adressierte Fmln an Änderungen innerhalb(!) dieser Grenzen (nicht außerhalb davon!) angepasst würden, das müsste dann ja auch bei benannten Bereichen so sein, würden alle unsere diesbzgl Empfehlungen zur Handhabung dynamischer Bereiche in VBA-Pgmm hinfällig sein. Das konnte ich aber noch nie beobachten (im Einsatzplan-LangProjekt vom Frühjahr dieses Jahres habe ich das ebenfalls problemlos verwendet!). Ich schrieb ja auch, dass bei mir eine Verschiebung von W1 (aktuelles Datum) keine Auswirkungen hat, da die Zelle in den benannten Fmln voll-absolut adressiert ist, also die Adresse angepasst wird.
Ich konnte Deine Beobachtung auch bei matrix- (bzw im MultiplikationsBsp vektor-)bildenden benannten Fmln nicht nachvollziehen. Absolute Adressierungen innerhalb dieser wdn an Lösch- bzw EinfügeOperationen innerhalb ihres Bereichs angepasst.
Das ist übrigens auch bei Fmln in BedingtFormatierungen so. Nicht nur der GeltungsBereich, sondern auch absolute Adressen in Fmln wdn angepasst, sogar ggf auch relative, die dann natürlich intern variiert wdn (früher war das auch extern sichtbar)! Insofern machen benannte Fmln und Bereiche hier eine Ausnahme, wenn sie relativ adressiert wurden. Ihr Bezug ändert sich nicht! Deshalb zeigt die AutoVorgabe von Xl hier (und auch bei BedingtFormat-Fmln!) wohl auch immer absolute Adressierung, quasi als Standard, an.
Das kann normalerweise alles testweise nachvollzogen wdn.
7. Hier gilt ebenfalls der SchlussSatz des VorPktes. Es handelt sich um SchlussFolgerungen aus meinen TestErgebnissen...
Luc :-?

[neopa]

Hallo,

wenn ich einen definierten Namen mit !A1 editieren will, in manchen Fällen die Bezüge verschoben sind

das kann ich nicht bestätigen. Zumindest nicht, wenn ich nur mit Excel 2010 arbeite. Und erst seit dieser Zeit setze ich überhaupt globale benannte Formeln ein.

Ich hab jetzt testweise mal in test_ben_Formel.xls die globalen benannten Formeln in lokale zurückverwandelt. Danach die Datei zunächst nochmal als xls abgespeichert. Diese neu aufgerufen und dann als xlsx-Datei abgespeichert, geschlossen und wieder neu aufgerufen. Da tritt das von mir vorhin beschriebene Problem nicht auf. Somit kann ich Deine Erinnerung betätigen, offensichtlich hatte Excel vor 2010 ein Problem mit global def. benannten Formeln.

[lupo1]

1. Öffne Deine Datei test_ben_Formel
2. Gehe auf E3
3. Öffne den Namens-Manager und klicke (nicht: doppeltklicke!) auf Anf
4. Im "beziehtSichAuf" a) oben daneben und b) unten isoliert findest Du 2mal die korrekten Bezüge
5. Jetzt doppeltklicke dort auf die Zeile "Anf"
6. Der Bezug im Dialogfenster ist im vorderen Teil verrutscht, nämlich von B nach IX. Das ist lustig! Denn xl4 ging bis IV. IW entspräche dann A und IX entspräche B. Ein Doppel-Bug, der 1) einen Fehler (warum an 3 Spalten vorbei?) und 2) eine Teil-Nichtberücksichtigung der Erweiterung von IV zu XFD enthält.

[neopa]

Hallo,

Deine Feststellung kann ich nun bestätigen. Aber dies tritt wohl nur bei einer xls-Datei auf, die aus einer XLSX-Datei gespeichert wurde.
In der Originalen- XLSX--Datei ist das, logischerweise nicht der Fall. Damit sind nun unsere diesbzgl. Feststellungen kompatibel (auch der "Historie"übernahme-Hinweis von Luc) und erklärt mir nun einiges.

Interessant wäre jetzt nur noch, was passiert (oder mE wahrscheinlicher ist, was nicht passiert), wenn in einer "alten" Excelversion (also vor 2007) eine Datei mit global definierten benannten Formeln erzeugt und diese in Excel 2010 eingelesen wird.

[lupo1]

Ich habe den Namen noch mal in einer jungfräulichen .XLSX unter xl2010 definiert. Alles ok.

Dann habe ich ihn in einer .xls unter xl2010 gespeichert, aber diese noch nicht verlassen. Alles ok.

Nach Neuöffnen der .xls (in xl2010) ist der Bug wieder da.

[slowboarder]

Hi
wie wird denn die Formel angezeigt,, wenn die Z1S1-Bezugsart ausgewählt ist?
tritt der Fehler dann auch auf?
Gruß Daniel

[lupo1]

Ja, ...!Z3S(253)...

E = 5
IV = 256
IX = 258

IX-E = 253

Wie gesagt: Einfachklick zeigt den Fehler nicht. Und bei Einfachklick kann man die Formel kopieren. Ist also eine Anzeigeproblematik, allerdings mit Folgen, wenn man die Doppeltklick-Formel dann in die Zwischenablage nimmt.

[neopa]

Hallo Lupo,

aber das schrieb ich doch schon, dass dies nur auftritt, wenn man die Datei aus XL2010 in eine xls-Datei abspeichert.
Ich schrieb weiter,: .".. wenn in einer "alten" Excelversion (also vor 2007) eine Datei mit global definierten benannten Formeln erzeugt und diese in Excel 2010 eingelesen wird"