Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema Werkzeug
BildScreenshot zu Werkzeug Werkzeug-Seite mit Beispielarbeitsmappe aufrufen

Ressourcenverbrauch durch Formeln

Betrifft: Ressourcenverbrauch durch Formeln von: erichm
Geschrieben am: 17.11.2014 07:17:28

Hallo,

habe in der Recherche von Herber schon einige Hinweise darauf gefunden, warum und welche Formeln dazu führen, dass durch Ressourcenverbrauch die Daten nicht mehr berechnet werden können. Ich habe schon einiges geändert und erhalte immer noch die Fehlermeldung:



Die Datei hat aktuell ca. 23 MB und in einer Tabelle diese Formeln, die das wohl auslösen:
Tippvorschlag

 VWXYZAAABAC
564607.10.20141112416.8.2014261112.6.2013

Formeln der Tabelle
ZelleFormel
V5=ZÄHLENWENN(INDIREKT($IC5&"$5:"&$IC5&"$5350"); V$2)
W5=VERWEIS(2;1/(INDEX($AN$5:$IA$5350;0;VERGLEICH($A5;$AN$4:$IA$4;0))=V$2); $AM$5:$AM$5350)
X5=$Z$1-INDEX(Gewinnzahlen!$B$5:$C$5350;VERGLEICH(W5;Gewinnzahlen!$C$5:$C$5350;0); 1)
Y5=ZÄHLENWENN(INDIREKT($IC5&"$5:"&$IC5&"$5350"); Y$2)
Z5=VERWEIS(2;1/(INDEX($AN$5:$IA$5350;0;VERGLEICH($A5;$AN$4:$IA$4;0))=Y$2); $AM$5:$AM$5350)
AA5=$Z$1-INDEX(Gewinnzahlen!$B$5:$C$5350;VERGLEICH(Z5;Gewinnzahlen!$C$5:$C$5350;0); 1)
AB5=ZÄHLENWENN(INDIREKT($IC5&"$5:"&$IC5&"$5350"); AB$2)
AC5=VERWEIS(2;1/(INDEX($AN$5:$IA$5350;0;VERGLEICH($A5;$AN$4:$IA$4;0))=AB$2); $AM$5:$AM$5350)


Tippvorschlag

 ANAOAP
5113

Formeln der Tabelle
ZelleFormel
AN5{=WENN(SUMME(ZÄHLENWENN(INDIREKT("B"&AN$3&":M"&AN$3); Gewinnzahlen!$E5:$J5))>0;SUMME(ZÄHLENWENN(INDIREKT("B"&AN$3&":M"&AN$3); Gewinnzahlen!$E5:$J5)); "")}
AO5{=WENN(SUMME(ZÄHLENWENN(INDIREKT("B"&AO$3&":M"&AO$3); Gewinnzahlen!$E5:$J5))>0;SUMME(ZÄHLENWENN(INDIREKT("B"&AO$3&":M"&AO$3); Gewinnzahlen!$E5:$J5)); "")}
AP5{=WENN(SUMME(ZÄHLENWENN(INDIREKT("B"&AP$3&":M"&AP$3); Gewinnzahlen!$E5:$J5))>0;SUMME(ZÄHLENWENN(INDIREKT("B"&AP$3&":M"&AP$3); Gewinnzahlen!$E5:$J5)); "")}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Die Formeln V bis AC sind in ca. 170 Zeilen nach unten kopiert.

Die Formeln AN bis AP sind
ca. 200 Spalten nach rechts und
ca. 5.350 Zeilen nach unten kopiert.

Tippvorschlag

 AHAIAJAK
514122015

Formeln der Tabelle
ZelleFormel
AH5=SUMMENPRODUKT(($AL$5:$AL$5350=AH$3)*(INDIREKT($IC5&"$5:"&$IC5&"$5350")=$V$2))
AI5=SUMMENPRODUKT(($AL$5:$AL$5350=AI$3)*(INDIREKT($IC5&"$5:"&$IC5&"$5350")=$V$2))
AJ5=SUMMENPRODUKT(($AL$5:$AL$5350=AJ$3)*(INDIREKT($IC5&"$5:"&$IC5&"$5350")=$V$2))
AK5=SUMMENPRODUKT(($AL$5:$AL$5350=AK$3)*(INDIREKT($IC5&"$5:"&$IC5&"$5350")=$V$2))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Diese Formeln sind ebenfalls ca. 170 Zeilen nach unten kopiert.

Kann ich die Formeln optimieren oder könnte man die Matrixformeln per VBA lösen?

Besten Dank für eine Hilfe!

mfg

  

Betrifft: über eine 1Mio Matrixformeln ... von: neopa C (paneo)
Geschrieben am: 17.11.2014 07:47:04

Hallo Erich,

... da geht wohl fast jeder Normal PC in die Knie, unabhängig davon, ob diese für sich nun optimal konstruiert sind oder nicht.

Gruß Werner
.. , - ...


  

Betrifft: AW: Matrixformel verkleinert von: erichm
Geschrieben am: 17.11.2014 08:15:42

Danke, das habe ich mir schon gedacht; jetzt habe ich die Matrixformel durch eine Hilfsspalte verkleinert; die sieht jetzt so aus:

Tippvorschlag

 IGIHII
5113

Formeln der Tabelle
ZelleFormel
IG5{=SUMME(ZÄHLENWENN(INDIREKT("B"&AN$3&":M"&AN$3); Gewinnzahlen!$E5:$J5))}
IH5{=SUMME(ZÄHLENWENN(INDIREKT("B"&AO$3&":M"&AO$3); Gewinnzahlen!$E5:$J5))}
II5{=SUMME(ZÄHLENWENN(INDIREKT("B"&AP$3&":M"&AP$3); Gewinnzahlen!$E5:$J5))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Gibt es für diese Matrix eine bessere Alternative oder kann man das vielleicht sogar mit VBA lösen?
(die kleinere Formel bringt noch keinen Ressourcenvorteil)

Danke nochmal!
mfg


  

Betrifft: offen ... von: neopa C (paneo)
Geschrieben am: 17.11.2014 08:43:38

Hallo Erich,

... hier ist mE eine einfache Kosmetik nicht ausreichend sondern eine große "OP" notwendig.
Doch für eine solche ist zunächst eine gute Diagnose notwendig. Diese kann man aber nur auf der Basis des Kenntnis des Istzustandes sowie einer klar definierten Zielstellung vornehmen.
Das wäre hier mE mit möglicherweise nicht unerheblichen Zeitaufwendungen verbunden. Außerdem läuft das ganze wahrscheinlich wirklich auf eine VBA-Lösung hinaus und da fehlt es mir an den nötigen "OP-Besteck" aber auch an Willen, da es sich möglicherweise um Gewinnspielauswertungen geht.

Ich stelle den Thread deshalb wieder auf offen.

Gruß Werner
.. , - ...


  

Betrifft: AW: offen ... von: erichm
Geschrieben am: 17.11.2014 09:06:24

Danke; stimmt, ich stelle mir ein Tool zusammen um nach bestimmten Kriterien Lottoziehungen auszuwerten :)
Für eine VBA-Lösung reichen meine Kenntnisse (noch) nicht. Und eine alternative Formel für die (verkleinerte) Matrixformel habe ich noch nicht gefunden - aber ich bastle und probiere noch..........

mfg


  

Betrifft: AW: offen ... von: Rudi Maintaire
Geschrieben am: 17.11.2014 12:59:16

Hallo,
1. solltest du zusehen, dass du INDIREKT() los wirst. Statt Indirekt("B" &an3 &":M" &an3) besser INDEX(B:M;an3)
2. durch den Einsatz von Hilfsspalten alle Matrixformeln vermeiden.

Gruß
Rudi


  

Betrifft: AW: offen ... von: erichm
Geschrieben am: 17.11.2014 18:48:13

DANKE - durch INDEX hat sich die Datei schon deutlich verkleinert. Leider ist das Ressourcenproblem aber trotzdem nicht gelöst.
Die bereits verkleinerte Matrixformel:{=SUMME(ZÄHLENWENN(INDIREKT("B"&AN$3&":M"&AN$3); Gewinnzahlen!$E5:$J5))}
kann ich aber nicht durch Hilfsspalten ersetzen, da benötige ich viel zu viele (es werden zwölf Spalten mit 6 Spalten auf übereinstimmende Zahlen geprüft!!).
Ich probiere weiter und tüftle bereits an einer VBA-Lösung.

mfg


  

Betrifft: vorläufig erledigt o.w.T. von: erichm
Geschrieben am: 20.11.2014 20:51:51

..........


  

Betrifft: Grundsätzliches von: Michael
Geschrieben am: 17.11.2014 16:43:06

Hallo Erich,

die gute Statistik liefert womöglich gute Hinweise, wenn eine Mindestmenge an Daten vorhanden ist.

Wenn ein Roulettespieler 100 Würfe beobachtet, kann er vielleicht Schlüsse ziehen, denn dann hat er 100 Ereignisse aus 37 möglichen gesehen.

Beim Lotto haben wir rund 14 Mio. mögliche Ereignisse, und die paar 1000 bisherigen Ziehungen genügen kaum dazu, ein statistisches Werkzeug anzusetzen.

Geh doch bitte im Jahr 3758 wieder ins Forum ;-)

Happy Exceling,

Michael


  

Betrifft: AW: Grundsätzliches von: erichm
Geschrieben am: 17.11.2014 18:43:11

Hallo Michael,
das ist mir schon alles klar - den großen Gewinn kann man auch nicht erzwingen. Trotzdem ist es interessant, diverse statistische Auswertungen zu machen :)

mfg


 

Beiträge aus den Excel-Beispielen zum Thema "Ressourcenverbrauch durch Formeln"