PDA

Vollständige Version anzeigen : Ineinander verschachtelte Formeln


Micky25
18.07.2014, 14:02
Hallo liebe Forumsuser,

ich habe an der Uni ein etwas größeres Projekt und bräuchte an einer Stelle eure Expertise:

Ich habe mehrere mögliche Eingabewerte. Abhängig welche Werte gegeben sind möchte ich nun eine bestimmte Zelle berechnen lassen (auf Grund der Eingabewerte). Die berechnete Zelle wiederrum erhöht die Zahl der Bekannten wodurch sich ggf. wieder neue Unbekannte durch entsprechende Formeln berechnen lassen.

Ich habe bisher über das Summenprodukt mir definieren lassen welche Werte bekannt sind und wollte dann mittels VBA über eine CASE select Anweisung arbeiten aber irgendwie hat mich auf halben Weg der Mut verlassen und ich hinterfrage grade diese Möglichkeit.

Habt ihr vielleicht eine bessere Idee oder könnt mir helfen meine Idee bis zum Ende zu denken?

Vielen dank vorab und besten Gruß

Micky

Hajo_Zi
18.07.2014, 14:04
löse es so.
<br/><b><em>Statistik</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:93px;" /><col style="width:93px;" /><col style="width:88px;" /><col style="width:100px;" /><col style="width:96px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>R</td><td>S</td><td>T</td><td>U</td><td>V</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">3.983</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">128</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0,568888888888889</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0,79585798816568</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">-0,226969099276792</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">38142</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1.616</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">7,18 </td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">8,27 </td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">-1,08700854700855</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">3.806</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">220</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0,977777777777778</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0,600591715976331</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">0,377186061801446</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">8.055</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1.713</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">7,61 </td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">8,36 </td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">-0,750571992110453</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td> <td>N/A</td></tr><tr><td>S2:S3,S5:S6</td><td>=INDIREKT(ADRESSE(ZEILE();SPALTE()-1))-INDIREKT(ADRESSE(ZEILE();SPALTE()-2))</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>T2:T3,T5:T6</td><td>=(INDIREKT(ADRESSE(ZEILE();SPALTE()-2))-INDIREKT(ADRESSE(ZEILE();SPALTE()-3)) )/(HEUTE()-$B$30+1)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>U2:U3,U5:U6</td><td>=(INDIREKT(ADRESSE(ZEILE();SPALTE()-4))-INDIREKT(ADRESSE(ZEILE();SPALTE()-5)) )/($B$30-1-$B$31)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>V2:V3,V5:V6</td><td>=T2-U2</td><td>&nbsp;</td><td>&nbsp;</td></tr></table><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 18.08 einschl. 64 Bit</td></tr></table><br/>

Deine Tabelle sehe ich nicht, da habe ich meine benutzt.

<img src="http://Hajo-Excel.de/images/grusz1.gif" align="middle" height="40" alt="Grußformel"><a href="http://Hajo-Excel.de/index.htm"><img border="0" src="http://Hajo-Excel.de/images/logo_hajo3.gif" align="middle" height="40" alt="Homepage"></a>

Mc Santa
18.07.2014, 14:21
Hallo,

möglicherweise hilft die die Möglichkeit der Iteration in Excel weiter.
Um dir genau Tipps geben zu können, muss man deine Exceldatei sehen können.

Viele Grüße

Micky25
18.07.2014, 14:26
Ich habe jetzt die Datei mal angehangen.

Mittels einer Userform wollte ich in der Rubrik Eingabewerte die Daten erfassen. Links daneben prüfen die Summenprodukte welche Werte sich berechnen ließen woraufhin die fehlenden Werte (mit geeigneten Formeln die noch nicht hinterlegt sind) der Rubrik Eingabewerte berechnet werden sollen.

Gruß Micky

EarlFred
18.07.2014, 14:29
Hallo Micky,

per Formel geht sowas bis zu einer gewissen Komplexität:
=WENN((A1<>"")*(A2<>"");A1+A2;WENN((A1<>"")*(A3<>"");A3-A1;WENN((A2<>"")*(A3<>"");A3-A2)))
Das wird aber sehr schnell unübersichtlich bzw. stößt an Grenzen.
Ob es mit dem Solver ginge, müsste man sich im konkreten Fall anschauen.


Ich hatte vor längerem mal ein ähnliches Problem (die Datei allerdings habe ich nicht mehr), wo ich mich entschieden habe, die Abhängigkeiten aufzuschlüsseln:
Bsp:
Für den Wert A1 braucht man A2 und A3
Für den Wert A3 braucht man A4 und C3
...

Das kann man z. B. mittels VBA und Schleife leicht mehrfach durchlaufen, solange bis entweder alle Zellen gefüllt sind oder aber eine maximale Iterationszahl erreicht ist, sofern nicht genügend Bedingungen / Vorgabewerte vorhanden sind.
Da war nicht sonderlich elegant, aber dafür auch nicht sonderlich schwer und schnell zu programmieren; aber es war dennoch für die Aufgabe effektiv genug.

Grüße
EarlFred

Micky25
18.07.2014, 14:34
@EarlFred:

Das Problem ist, dass ich zum beispiel A1 mit den Werten aus A2 und A3 oder aber A4 und A5 berechnen lassen könnte.

Dadurch hab ich wie in meiner angehängten Excel die Summenprodukte gebildet und wollte dann mittels VBA prüfen lassen welche Produkte WAHR sind und dann diese Werte berechnen. Durch die neuen Werte sollten andere Produkte WAHR werden wodurch sich neue Werte finden usw.

Also irgendwie ne Art Iteration hatte ich im Sinn...

Gruß Micky

Mc Santa
18.07.2014, 14:42
Hallo,

ich glaube ihr meint beide das gleiche.
Kannst du mal alle Formeln für die jeweilige Variable in Spalte D schreiben?

VG

EarlFred
18.07.2014, 14:50
Hallo,

Das Problem ist, dass ich zum beispiel A1 mit den Werten aus A2 und A3 oder aber A4 und A5 berechnen lassen könnte.
das sehe ich keineswegs als Problem:
"Wenn A1 unbekannt UND A2 bekannt UND A3 bekannt, dann berechne A1 aus A2 und A3"
"Wenn A1 unbekannt UND A4 bekannt UND A5 bekannt, dann berechne A1 aus A4 und A5"
Wenn die erste Prüfung bestanden wird, greift eben die 2. nicht mehr.

Man muss natürlich davon ausgehen können, dass bei der Berechnung über A2 und A3 das gleiche herauskommt wie bei der Berechnung über A4 und A5.

An einem konkreten Beispiel wird's konkret.

Grüße
EarlFred

Micky25
18.07.2014, 14:55
Du meinst Spaöte C oder?

Ich glaube das das ein wenig den Rahmen sprengen würde darum mache ich ein Beispiel für den Volumenstrom (C14):

Volumenstrom: C14 = C21*C22 = PI * C5 * C6 * C27

mit dem jetzt bekannten Volumenstrom und ggf. anderen bekannten Werten könnten weitere Werte berechnet werden (ich denke meine Excel liefert da nen ganz guten Überblick welche Bekannten man für die einzelnen Werte braucht).

Gruß Micky

Mc Santa
18.07.2014, 14:59
Hallo,

mit allen Formeln in Spalte D (sic* (http://de.wikipedia.org/wiki/Sic)) biete ich dir an, mich an einer Lösung zu versuchen.

VG

EarlFred
18.07.2014, 15:09
Hallo Micky,

ich hatte die später hinzugefügte Mustermappe nicht gesehen. Selbstredend meine ich das, was bei Dir in Spalte C steht ;)

Ein Grobgerüst:
Option Explicit

Sub VielleichtSo()
Dim lngCounterAbbruch As Long

Do
Select Case True
'Versuch, Volumenstrom (Zelle C14) zu berechnen:
'C14 = C21 * C22
Case Range("C14").Value <> "" And (Range("C21").Value <> "" And Range("C22").Value <> "")
Range("C14").Value = Range("C21").Value * Range("C22").Value

'alternativer Versuch, Volumenstrom (Zelle C14) zu berechnen:
'C14 = Pi * C5 * C6 * C27
Case Range("C14").Value <> "" And (Range("C5").Value <> "" And Range("C6").Value <> "" And Range("C27").Value <> "")
Range("C14").Value = Application.Pi() * Range("C5").Value * Range("C6").Value * Range("C27").Value

'und so weiter...


End Select

lngCounterAbbruch = lngCounterAbbruch + 1
Loop Until lngCounterAbbruch > 32 Or Application.Count(Range("C4:C12,C14:C22,C24:C37")) = 32

MsgBox "Fertig!" & vbLf & IIf(Application.Count(Range("C4:C12,C14:C22,C24:C37")) = 32, "Alles berechnet!", "Abbruch, weil nicht lösbar")
End Sub

Mit Leben füllen musst Du selbst - aber wie Du nach etwas Hinschauen sicher feststellen wirst: Das Schema ist simpel und leicht erweiterbar.

Wichtig ist das Erhöhen des Abbruchzählers, um Endlosschleifen zu vermeiden.

Grüße
EarlFred

Micky25
18.07.2014, 15:13
Das ist nett aber ich glaube ich würde es ganz gerne selbst mit ein paar Denkhilfen lösen.

Ich mache mal ein ganz konkretes Beispiel:

gegebene Werte: V, n, d2, b2, beta2, c1u=0

gesucht Förderhöhe Ht

Ht = (1/constante) * (u2*c2u-u1*c1u)

Unbekannt sind also u2 und c2u (da c1u = 0 ist).

u2 = PI*n*d2 (also berechenbar) --> c2u unbekannt.

c2m = w2m = V / (PI*d2*b2) (bekannt)

daraus folgt w2u = tan(beta2) / w2m

und damit c2u = u2 - w2u und schließlich sind alle Werte für Ht bekannt.

Ich hoffe das macht es deutlich.

Gruß Micky

EarlFred
18.07.2014, 15:16
Hallo Micky,

ich hatte bereits Dein erstes Formelbeispiel (Volumenstrom) im Mustercode ergänzt. Die restlichen Formeln bekommst Du sicher selbst hin. Wenn nicht, frage konkret nach.
Ich klinke mich hier erstmal wieder aus.

Grüße
EarlFred

Micky25
18.07.2014, 16:04
@EarlFred:

Eine Frage noch: wie mache ich meinem Programm denn nun verständlich, dass der Volumenstrom vorhanden ist (nach Ausführung deiner Funktion) und das damit andere Funktionen ausgeführt werden können? Ich kann ja nicht simultan mehrere Funktionen aufrufen wenn manche Ergebnisse für andere Funktionen gebraucht werden (siehe quasi zweites Beispiel)

Gruß Micky

EarlFred
18.07.2014, 16:21
Hallo Micky,

einfach alles munter hintereinander im gleichen Muster dahinter tackern:

anstelle
'und so weiter...
kommt

'Prüfe, ob Berechnung1 ausführbar ist, wenn Zielzelle noch leer:
Case ZelleWoDasErgebnisHinSoll <> "" und (benötigte Zelle1 <> "" und benötigte Zelle2 <>"" und und und)
'Prüfe, ob Berechnung2 ausführbar ist, wenn Zielzelle noch leer:
Case ZelleWoDasErgebnisHinSoll <> "" und (benötigte Zelle1 <> "" und benötigte Zelle2 <>"" und und und)
'Prüfe, ob Berechnung3 ausführbar ist, wenn Zielzelle noch leer:
Case ZelleWoDasErgebnisHinSoll <> "" und (benötigte Zelle1 <> "" und benötigte Zelle2 <>"" und und und)
'Prüfe, ob Berechnung4 ausführbar ist, wenn Zielzelle noch leer:
Case ZelleWoDasErgebnisHinSoll <> "" und (benötigte Zelle1 <> "" und benötigte Zelle2 <>"" und und und)
'Prüfe, ob Berechnung5 ausführbar ist, wenn Zielzelle noch leer:
Case ZelleWoDasErgebnisHinSoll <> "" und (benötigte Zelle1 <> "" und benötigte Zelle2 <>"" und und und)
'Prüfe, ob Berechnung6 ausführbar ist, wenn Zielzelle noch leer:
Case ZelleWoDasErgebnisHinSoll <> "" und (benötigte Zelle1 <> "" und benötigte Zelle2 <>"" und und und)
'UND SO WEITER


'BIS
'Prüfe, ob Berechnung98214 ausführbar ist, wenn Zielzelle noch leer:
Case ZelleWoDasErgebnisHinSoll <> "" und (benötigte Zelle1 <> "" und benötigte Zelle2 <>"" und und und)

Prinzip:
Der Code läuft das erste mal durch und prüft alle Bedingungen von oben nach unten ab, solange, bis bei der "erstbesten" Formel alle Voraussetzungen erfüllt sind. Das wird dann berechnet und eingetragen.
Dann wird ein Zähler erhöht.
Dann geht es von vorne los: prüfen, bis irgendwas berechenbar ist
Zähler erhöhen......usw.

Solange, bis der Zähler bei 32 ist (32 Ergebnisse brauchst Du maximal) ODER bis 32 Zahlenwert in den 32 Zielzellen stehen, d. h. alles berechnet ist.

Grüße
EarlFred