Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 15:03:35
Unterschiedliche Dateien miteinander abgleichen lassen
25.04.2024 14:05:48
Makro in aktueller Zelle ausführen mit Doppelklick
25.04.2024 12:56:25
Werte von Kunden und Artikel stehen untereinander
25.04.2024 12:55:42
Funktionsparameter auslesen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1664to1668
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1664to1668
1664to1668
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Zirkelbezug auflösen / umgehen?

Zirkelbezug auflösen / umgehen?
03.01.2019 13:22:38
erichm
Hallo,
ich habe eine Tabelle (LLDirneu) aus deren Werten Formelergebnisse in der Tabelle Appneu errechnet werden.
Problem: die Tabelle LLDirneu enthält in verschiedenen Zeilen wiederum Werte, die sich zum Teil aus den berechneten Ergebnissen der Tabelle Appneu ergeben.
Dies führt in der Konsequenz zu Zirkelbezügen.
Meine derzeitige Lösung:
Ich berechne in einer Hilfstabelle (Vorb) die Werte von Appneu, die wiederum in die Tabelle LLDirneu übertragen werden müssen. Diese Übertragung erfolgt per Kopie, wobei EINFÜGEN dann mit „Werte“ (also keine Formeln) erfolgt. Somit muss die Datei derzeit immer 2x berechnet werden:
1. Aktualisierung der Hilfstabelle (Vorb) mit anschließendem kopieren in LLDirneu
2. Endgültige Berechnung
Die Lösung funktioniert, ist aber mittlerweile sehr zeitraubend weil jede Berechnung sehr lange dauert und die Daten in unregelmäßigen zeitlichen Abständen immer wieder erweitert / aktualisiert werden.
Habe jetzt schon alles mögliche probiert (z.B. auch mit Iteration), aber keine andere Lösung gefunden.
Gibt es da vielleicht doch eine bessere Möglichkeit? Evtl. wäre ja eine Lösung mit VBA denkbar?
Danke für eine Hilfe / Rückmeldung.
mfg

25
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zirkelbezug auflösen / umgehen?
03.01.2019 13:35:57
onur
Deine ganze Erklärung ist leider verschwendet, wenn man weder die Datei vor sich hat noch wirklich versteht, worum es geht.
AW: Zirkelbezug auflösen / umgehen?
03.01.2019 17:34:43
erichm
Danke für die Rückmeldung; ich versuche die Datei auf eine Musterdatei zu reduzieren (wird aber etwas dauern).
mfg
AW: Zirkelbezug auflösen / umgehen?
03.01.2019 19:17:48
onur
Und worum geht es bei der Datei? Was bedeuten die Einträge? Was wird berechnet? Was willst du mit dem Ganzen erreichen?
AW: Zirkelbezug auflösen / umgehen?
03.01.2019 19:43:20
erichm
Hallo onur,
hier geht es um eine Vielzahl statistischer Erhebungen / Auswertungen. Eine Erläuterung der einzelnen Werte würde den Rahmen hier sprengen.
Ziel der Datei ist, die statistischen Daten nach verschiedensten Kriterien auszuwerten (deswegen auch die teils sehr unterschiedlichen Inhalte der Zellen).
Im Prinzip habe ich die Ergebnisse in der Tabelle "Appneu" grundsätzlich erreicht - aber eben mit dem Umweg über eine Hilfstabelle, weil es Zirkelbezüge gibt. Eine "direkte" Lösung ohne Zirkelbezug wäre eben besser, weil (wie im ersten Fragebeitrag geschrieben) die Berechnungszeiten derzeit sehr intensiv sind.
Besten Dank.
mfg
Anzeige
AW: Zirkelbezug auflösen / umgehen?
03.01.2019 19:46:13
onur
Alle deine Formeln und Tabellen zu analysieren, nur um dir helfen zu dürfen, würde MEINEN Rahmen sprengen.
Deswegen bin ich raus.
AW: ein Schritt nach den anderen ...
03.01.2019 15:29:26
neopa
Hallo Erich,
... vermutlich geht es Dir um Deine Arbeitsmappe, die Du hier: https://www.herber.de/forum/archiv/1664to1668/t1665521.htm eingestellt hast, oder?
Mit Iteration zu arbeiten halte ich für keine gute Lösung. Weil diese mE eher die Auswertung verlangsamen dürfte. Evtl. könnten zunächst die notwendige Berechnungen optimiert werden?
Gruß Werner
.. , - ...
AW: ein Schritt nach den anderen ...
03.01.2019 17:37:58
erichm
Hallo Werner,
stimmt. Ich hatte es ohne Musterdatei versucht, weil die Reduzierung der Originaldatei auf die Musterdatei recht aufwändig ist. Ich werde jetzt aber eine Musterdatei aufbereiten.
Die Iteration hat wirklich die Berechnung verlangsamt - und das Ergebnis hat trotzdem nicht gepasst.
mfg
Anzeige
AW: ein Schritt nach den anderen ...
03.01.2019 19:10:13
erichm
Hallo Werner,
zunächst die Musterdatei, die den Zirkelbezug enthält (Tabelle LLDirneu ab FA16):
https://www.herber.de/bbs/user/126464.xlsx
Hier die Musterdatei mit meiner Lösung (zusätzliche Tabelle Vorb):
https://www.herber.de/bbs/user/126465.xlsx
Ich hoffe, die Erklärungen sind jetzt soweit ausreichend. Wie bereits erwähnt, ist diese Datei sehr stark verkürzt, der strukturelle Aufbau ist mit der Originaldatei aber identisch.
Vielen Dank nochmals.
mfg
Anzeige
AW: ein erster Schritt ...
04.01.2019 08:26:02
neopa
Hallo Erich,
... ich hab mir jetzt nur die 2. Datei ("Deine Lösung") angeschaut.
Deine Formeln darin beinhalten mE noch Einsparungspotential bzgl. Rechenzeit in Anspruchsnahme. So:
1.) Dein Bezug auf [Filtertranchenformel.xlsx] beinhaltet SVERWEIS() mit dem 4. Parameter 0. Günstiger wäre es wenn die dort auszuwertenden Daten sortiert sind und SVERWEIS() mit dem Standardargument 1 auswerten kann. Auch sollte die Formel so umgebaut werden, dass zuerst geprüft wird ob K##Deinen Vorgabewerten in Appneu ist. Analoges gilt für J2
2.) Deine beiden benannte Namen "leztez" und lezterz" werten den gesamten Spaltenbereich aus. Dies könntest Du einschränken auf das max wirklich notwendige.
3.) Deine Formeln in Appneu!BF2:XX### einhalten auch noch Einsparpotential.
Gruß Werner
.. , - ...
Anzeige
AW: ein erster Schritt ...
04.01.2019 09:45:24
erichm
Hallo Werner,
danke schon mal.
zu 2.: auszuwertende Zeilen pro Spalte sind von Zeile 2 bis Zeile 17.300; aber wo muss hier die Zeilenbegrenzung gesetzt werden?
=INDEX(!2:2;VERWEIS(9;1/(LINKS(!$C2:INDEX(!2:2;SPALTE()-1);1)=LINKS(" "&!$C2;1));SPALTE(!2:2))-1)
mfg
AW: oh: wirklich bis zu 17300 Datensätze? ...
04.01.2019 10:06:17
neopa
Hallo Erich,
... doch zunächst zu 2.) meinte ich den Formelteil: ...SPALTE(2:2)...
Damit wird je Zeile 16384 Spaltenwerte durchsucht. Davon wird doch sicherlich nur ein Bruchteil an Spalten mit Werten genutzt.
Aber Deine nunmehrige Angabe "Zeile 2 bis Zeile 17.300" macht Dein eigentliches Problem sichtbar. Bei derartig vielen Datensätze und evtl. bei z.B. "nur" 100 auszuwertenden Spalten sind das allein schon für die Tabelle Appneu knapp 2Mio erforderliche Formeln. Kein Wunder, dass die Berechnung sehr lange braucht.
Hier ist dann wohl eine reine Formellösung mE zwar vielleicht noch machbar aber kaum vertretbar. Da bedarf es wohl eines neuen Lösungsansatzes, zumal ja auch dann Deine gewünschte "Iteration" noch hinzukommt.
Gruß Werner
.. , - ...
Anzeige
AW: oh: wirklich bis zu 17300 Datensätze? ...
04.01.2019 10:34:50
erichm
Hallo Werner,
ja, die Datenwerte sind sehr umfangreich, der genannte Bereich geht bis 17.300 Zeilen.
Der Formelteil SPALTE(2:2) kommt in der Tabelle LLDirneu in Spalte BX zum Einsatz. Diese Formel ist hier entstanden, durch Spaltenergänzungen hat sich lediglich die betroffene Spalte auf BX geändert:
https://www.herber.de/cgi-bin/callthread.pl?index=1651439
Ein neuer Lösungsansatz wird schwierig sein, da ein Umbau der umfangreichen Tabelle kaum realisierbar sein dürfte. Die Iteration muss ich notfalls dann doch mit den 2 Berechnungen "lösen".
mfg
Anzeige
AW: wie viele Spalten sind max. auszuwerten? owT
04.01.2019 10:56:16
neopa
Gruß Werner
.. , - ...
AW: wie viele Spalten sind max. auszuwerten? owT
04.01.2019 11:01:51
erichm
Auszuwerten sind nur die Spalten M bis einschl. BV
Hinweis: Die Spalten BM bis BV werden sukzessive befüllt. Wenn BU befüllt ist, werden 10 neue Spalten eingefügt, so dass sich der "Spaltenumfang" quasi erweitert.
mfg
AW: ok, dann aber zunächst nachgefragt ...
04.01.2019 11:08:38
neopa
Hallo Erich,
... Du möchtest also mit der Formellösung weiter arbeiten? Denn nun sind es immer noch ca. 1,3 Mio Formel in AppNeu.
Gruß Werner
.. , - ...
AW: ok, dann aber zunächst nachgefragt ...
04.01.2019 11:26:13
erichm
JA - den Zeitbedarf muss ich in Kauf nehmen. Hinzu kommt, dass ich mich mit Formellösungen "sicherer" bei den Berechnungsergebnissen fühle! Ferner bin ich wesentlich flexibler, wenn mal ein Änderungsbedarf ist - bei VBA werde ich das nicht schaffen.
Es wird auch in Kürze (größere) Teilbereiche geben, bei denen sich die Ausgangswerte nicht mehr ändern, dann werde ich die Formeln in Werte umwandeln, so dass sich die Berechnungszeiten auch zwangsläufig wieder verkürzen werden.
Ich werde auch nochmals alle Formeln durchgehen, bei denen ich den auszuwertenden Zeilenbereich verkürzen kann; aus Vereinfachungsgründen habe ich da oft die komplette Spalte fixiert, obwohl es nicht zwingend notwendig ist.
Da muss ich einfach viel mehr an der Disziplin arbeiten...........
DANKE!
mfg
Anzeige
AW: nun, wenn dem so ist ...
04.01.2019 12:26:31
neopa
Hallo Erich,
... dann können wir wohl nur versuchen etwas an den Formeln "herum zu schrauben". Aber all zu viel kann man da nicht mehr herausholen. Bzgl. der Änderung der Formel die Daten der andere Datei auswertet hatte ich mich schon geäußert. Die Änderung der benannten Formeln z.B. von 2:2 auf $A2:$BV2 kannst Du sicherlich jetzt allein vornehmen.
Da ist dann noch die Formel in Appneu!BF2. Dazu fällt mir keine grundsätzlich andere Lösung ein.
Doch da SUMMENPRODKT() schneller auswertet als {SUMME()} würde ich dafür folgende Formel vorschlagen:
=SUMMENPRODUKT(ZÄHLENWENN(LLDirneu!FA$3:FA$50;$D2:$U2))+
(SUMMENPRODUKT(ZÄHLENWENN(LLDirneu!FA$3:FA$50;$V2:$W2))>1)+
(SUMMENPRODUKT(ZÄHLENWENN(LLDirneu!FA$3:FA$50;$X2:$Y2))>1)+
(SUMMENPRODUKT(ZÄHLENWENN(LLDirneu!FA$3:FA$50;$Z2:$AA2))>=1)+
SUMMENPRODUKT(ZÄHLENWENN(LLDirneu!FA$3:FA$50;$AB2:$AC2)>=1)
Gruß Werner
.. , - ...
Anzeige
kann mich erst morgen melden o.w.T.
05.01.2019 08:51:39
erichm
...
AW: nun, wenn dem so ist ...
06.01.2019 15:37:41
erichm
Hallo Werner,
da habe ich jetzt einige Fortschritte erzielt.
SVERWEIS: davon habe ich in verschiedenen Tabellen sehr viele, diese habe ich alle auf WAHR (mit Sortierung) umgestellt, vorneweg mit einer Prüfung, z.B.
=WENN($C2&"_1v"&$B2=SVERWEIS($C2&"_1v"&$B2;ABTR!$E$2:$E$50;1;WAHR);SVERWEIS($C2&"_1v"&$B2; ABTR!$E$2:$F$50;2;WAHR);"")
Diese Umstellung brachte wohl einiges an schnellerer Rechenzeit!
Da habe ich Deinen Hinweis in der 1. Antwort, Ziffer 1 zu "J2" nicht verstanden?
SUMMENPRODUKT statt Matrix mit Summe: auch davon hatte ich mehrere die ich alle umgestellt habe.
nur teilweise erfolgreich war ich bei der Umstellung bei den Spaltenauswertungen
von 3:3 auf $A3:$BV3
In der Formel in der Zelle hat dies geklappt. In den Formeln in den Namensmanagern nicht?
letztez: =INDEX(!$A3:$BV3;VERWEIS(9;1/(LINKS(!$C3:INDEX(!$A3:$BV3;SPALTE()-1);1)=LINKS(" "&!$C3;1));SPALTE(!$A3:$BV3))-1)
letzterz: =VERWEIS(9;1/(!$C3:letztez"");!$A3:$BV3)
Ich habs auch mit verschiedenen anderen Varianten probiert (z. B. $C3:$BV3), aber alle ohne Erfolg?
https://www.herber.de/bbs/user/126529.xlsx
Die Musterdatei habe ich nur wie folgt aktualisiert:
neue Tabelle ABTR wegen Muster der neuen SVERWEIS-Formel in Tabelle Appneu, Spalte AK
Formel in Spalte BX, Tabelle LLDirneu wegen Anpassung der Spaltenauswertungen; Änderung auf $A3:$BV3
Auf alle Fälle wieder mal besten Dank für die Hinweise / Vorschläge.
mfg
Anzeige
AW: hierzu folgendes ...
06.01.2019 16:52:27
neopa
Hallo Erich,
... zu 1) die benannten Formeln: Wenn Du eine beliebige Zelle in der zweiten Zeile aktiviert hast, dann ändere die Definitionen im Namensmanager zu folgendem:
=VERWEIS(9;1/(!$C2:letztez"");!$A2:$BV2) und
=INDEX(!$A2:$BV2;VERWEIS(9;1/(LINKS(!$C2:INDEX(!$A2:$BV2;SPALTE()-1);1)=LINKS(" "&!$C2;1));SPALTE(!$A2:$BV2))-1)
Zu der von mir eingestellten SUMMENPRODUKT()-Formel in Appneu!BF2.BV### In der von Dir neu eingestellten Datei ist diese noch nicht vorgenommen.
Gruß Werner
.. , - ...
AW: hierzu folgendes ...
07.01.2019 10:57:12
erichm
Hallo Werner,
die Änderungen hatte ich schon so vorgenommen; ich habe die Musterdatei mit den Vorschlägen per Kopie nochmals aktualisiert, es bleibt jedoch weiter der Fehler BEZUG, siehe neue Musterdatei, Tabelle LLDirneu, Spalte BX:
https://www.herber.de/bbs/user/126550.xlsx
Kannst Du Dir das bitte nochmals anschauen.
(ich hatte auch die damalige Datei, bei der die Formel im Forum entstanden ist angepasst, ohne Erfolg)
Die Änderungen mit SUMMENPRODUKT habe ich nur in meinen Originaldateien aktualisiert.
Danke.
mfg
AW: nachgefragt ...
08.01.2019 08:42:07
neopa
Hallo Erich,
... kannst Du noch mal kurz darstellen, was in BX ermittelt werden soll und wie die ursprüngliche Formel dafür definiert war und in welchen Beitrag (Tag und Zeit) genau diese aufgestellt wurde.
Gruß Werner
.. , - ...
AW: nachgefragt ...
08.01.2019 09:18:59
erichm
Hallo Werner,
die Formel ist aus diesem Thread:
https://www.herber.de/cgi-bin/callthread.pl?index=1651439#1651439
konkrete Anfrage für diese Spalte BX (damals noch Spalte AT):
wie oft kommt der letzte Wert (damals in Spalte AP, aktuell in Spalte BL) in dieser Zeile zuletzt direkt hintereinander vor;
letzte Anpassung dann hier:
https://www.herber.de/cgi-bin/callthread.pl?index=1651439#1652461
(da ich sukzessive Spalten einfüge, haben sich die betroffenen Spalten geändert; die Struktur der zwei unterschiedlichen Modelle ist unverändert)
Danke.
mfg
AW: nun dann ...
08.01.2019 16:28:52
neopa
Hallo Erich,
... wenn die Spalte BV stets leer bleibt, kannst Du Dir Ermittlung des ben. Formel "letztez" ganz sparen und nur noch:
letzterz: =VERWEIS(9;1/(!$C3:$BV3"");!$A3:$BV3) definieren.
Dann in BX3:

=WENN(BW3="";"";AGGREGAT(14;6;SPALTE($A3:$BV3)/($A3:$BV3=letzterz);1)-
AGGREGAT(14;6;SPALTE($A3:INDEX($A3:$BV3;AGGREGAT(14;6;SPALTE($A3:$BV3)/
($A3:$BV3=letzterz);1)))/($A3:$BV3letzterz);1))
Wenn evtl. doch in Spalte BV ein Wert zu gewiesen werden müsste, fügst Du einfach noch Spalten ein und änderst in den Formeln BV zu der Spaltenbez. neuen ersten freien Spalte.
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige