Suche der richtigen Formel ?

eazy_262479 · 15. September 2006 um 13:06

erstmal ein freundliches hallo in die experten runde, da dies mein erstes posting ist.

ich habe eine datei, in der ist den zellen J3, K3, L3, M3 und N3 Felder sind, in denen man einen haken setzen kann. je nach haken soll er in den weiter unten liegenden feldern unterschiedliche berechnungen durchführen. es ist aber nicht nur so, dass er nach den oben genannten felder unterschiedliche brechnungten durchführen soll, sondern auch noch gucken soll, ob er in den feldern, in denen die zu berechnenden daten stehen, bestimmte werte stehen und auch dahingehen bestimmte berechnungen durchführen. beispiel: wenn in zelle J3 ein „x“ gesetzt ist und du in zelle F10 den wert „10“ findest, dann rechne das und das. wenn in zelle J3 ein „x“ ist und in zelle F10 der wert „12“ steht, dann rechne das und das…
ich hab das ganze mal ausprobiert, und auf irgendeine art und weise klappt das auch. allerdings muss ich so viele kriterien abfragen, dass der platz für die formel nich ausreicht (in F10 können ca. 15 verschiedene werte stehen und dann gibt es ja auch noch die haken in J3…). hier mal ein teil der formel(wie gesagt: alle kriterien abzufragen ging nicht):
WENN(UND(F11=10;J5=„x“);AF11+(((Q11*(W11/X11))*O11*Y11)*(V11-R11+U11));WENN(UND(F11=30;J5=„x“);AF11+(((Q11*(W11/X11))*O11*Y11)*(V11-R11+U11));WENN(UND(F11=31;J5=„x“);AF11+(((Q11*(W11/X11))*O11*Y11)*(V11-R11+U11));WENN(UND(F11=12;k5=„x“);AF11+(((Q11*(W11/X11))*O11*Y11)*(V11-R11+U11));WENN(UND(F11=33;k5=„x“);AF11+(((Q11*(W11/X11))*O11*Y11)*(V11-R11+U11));WENN(UND(F11=34;k5=„x“);AF11+(((Q11*(W11/X11))*O11*Y11)*(V11-R11+U11));WENN(UND(F11=35;k5=„x“);AF11+(((Q11*(W11/X11))*O11*Y11)*(V11-R11+U11));WENN(UND(F11=48;l5=„x“);AF11+(((Q11*(W11/X11))*O11*Y11)*(V11-R11+U11));WENN(UND(F11=36;l5=„x“);AF11+(((Q11*(W11/X11))*O11*Y11)*(V11-R11+U11));WENN(UND(F11=37;k5=„x“);AF11+(((Q11*(W11/X11))*O11*Y11)*(V11-R11+U11));AF11+(((Q11*(W11/X11))*O11*Y11)*(U11)))))))))))

hat jemand eine idee, wie man das besser lösen kann???

problem:

in einer anderen datei habe ich einen datenbereich, in dem viele werte ausgegeben werden sollen (aus anderen bestehenden excel dateien). jetzt möchte ich oben felder haben, in denen ich z.b. eingeben kann:
jahr1: 2003
jahr2: 2004
etc. und excel soll mir dann in den datenbereich die entsprechenden werte aus den excel dateien des jeweiligen jahres ziehen (pro jahr eine datei). mit welcher formel kann man das realisieren??

schonmal vielen vielen dank für eure hilfe.

gruss
eazy

Reinhard_fb8cb7 · 15. September 2006 um 14:04

Hi eazy,
du hast 75 öffnende und nur 74 schliessende Klammern.
Nachfolgend nochmal dein Text ohne Formel damit er besser in den Bildschirm passt
Gruß
Reinhard

erstmal ein freundliches hallo in die experten runde, da dies mein erstes posting ist.

ich habe eine datei, in der ist den zellen J3, K3, L3, M3 und N3 Felder sind, in denen man einen haken setzen kann. je nach haken soll er in den weiter unten liegenden feldern unterschiedliche berechnungen durchführen. es ist aber nicht nur so, dass er nach den oben genannten felder unterschiedliche brechnungten durchführen soll, sondern auch noch gucken soll, ob er in den feldern, in denen die zu berechnenden daten stehen, bestimmte werte stehen und auch dahingehen bestimmte berechnungen durchführen. beispiel: wenn in zelle J3 ein „x“ gesetzt ist und du in zelle F10 den wert „10“ findest, dann rechne das und das. wenn in zelle J3 ein „x“ ist und in zelle F10 der wert „12“ steht, dann rechne das und das…
ich hab das ganze mal ausprobiert, und auf irgendeine art und weise klappt das auch. allerdings muss ich so viele kriterien abfragen, dass der platz für die formel nich ausreicht (in F10 können ca. 15 verschiedene werte stehen und dann gibt es ja auch noch die haken in J3…). hier mal ein teil der formel(wie gesagt: alle kriterien abzufragen ging nicht):

hat jemand eine idee, wie man das besser lösen kann???

problem:

in einer anderen datei habe ich einen datenbereich, in dem viele werte ausgegeben werden sollen (aus anderen bestehenden excel dateien). jetzt möchte ich oben felder haben, in denen ich z.b. eingeben kann:
jahr1: 2003
jahr2: 2004
etc. und excel soll mir dann in den datenbereich die entsprechenden werte aus den excel dateien des jeweiligen jahres ziehen (pro jahr eine datei). mit welcher formel kann man das realisieren??

schonmal vielen vielen dank für eure hilfe.

gruss
eazy

Reinhard_fb8cb7 · 15. September 2006 um 14:33

Hi eazy,
1)
probiermal
=WENN(UND(J5=„x“;ODER(F11=10;F11=30;F11=31;F11=12;F11=33;F11=34;F11=35;F11=48;F11=36;F11=37));Q11*W11/X11*O11*Y11*(V11-R11+U11);Q11*W11/X11*O11*Y11*U11)

Allerdings wenn da noch mehr rein soll würde sich Vba anbieten, dann sähe die Formel z.B. so aus:

=Rechne(J5;F11)

wie heißt denn die Datei von 2003, die von 2004 usw?

Gruß
Reinhard

ich habe eine datei, in der ist den zellen J3, K3, L3, M3 und
N3 Felder sind, in denen man einen haken setzen kann. je nach
haken soll er in den weiter unten liegenden feldern
unterschiedliche berechnungen durchführen. es ist aber nicht
nur so, dass er nach den oben genannten felder
unterschiedliche brechnungten durchführen soll, sondern auch
noch gucken soll, ob er in den feldern, in denen die zu
berechnenden daten stehen, bestimmte werte stehen und auch
dahingehen bestimmte berechnungen durchführen. beispiel: wenn
in zelle J3 ein „x“ gesetzt ist und du in zelle F10 den wert
„10“ findest, dann rechne das und das. wenn in zelle J3 ein
„x“ ist und in zelle F10 der wert „12“ steht, dann rechne das
und das…
ich hab das ganze mal ausprobiert, und auf irgendeine art und
weise klappt das auch. allerdings muss ich so viele kriterien
abfragen, dass der platz für die formel nich ausreicht (in F10
können ca. 15 verschiedene werte stehen und dann gibt es ja
auch noch die haken in J3…). hier mal ein teil der
formel(wie gesagt: alle kriterien abzufragen ging nicht):

hat jemand eine idee, wie man das besser lösen kann???

problem:

in einer anderen datei habe ich einen datenbereich, in dem
viele werte ausgegeben werden sollen (aus anderen bestehenden
excel dateien). jetzt möchte ich oben felder haben, in denen
ich z.b. eingeben kann:
jahr1: 2003
jahr2: 2004
etc. und excel soll mir dann in den datenbereich die
entsprechenden werte aus den excel dateien des jeweiligen
jahres ziehen (pro jahr eine datei). mit welcher formel kann
man das realisieren??

schonmal vielen vielen dank für eure hilfe.

gruss
eazy

eazy_262479 · 16. September 2006 um 10:18

hallo reinhard

erstmal vielen dank für deine antwort:

ja, es muss noch wesentlich mehr rein. allerdings habe ich überhaupt keinen plan von VBA und kann auch die formel nicht interpretieren. kannst du das vielleicht noch näher erleutern?
die dateien für die vorjahre heißen:
Eckwerte2003
Eckwerte2004 etc.

gruss
eazy

Hi eazy,
1)
probiermal
=WENN(UND(J5=„x“;ODER(F11=10;F11=30;F11=31;F11=12;F11=33;F11=34;F11=35;F11=48;F11=36;F11=37));Q11*W11/X11*O11*Y11*(V11-R11+U11);Q11*W11/X11*O11*Y11*U11)

Allerdings wenn da noch mehr rein soll würde sich Vba
anbieten, dann sähe die Formel z.B. so aus:

=Rechne(J5;F11)

wie heißt denn die Datei von 2003, die von 2004 usw?

Gruß
Reinhard

ich habe eine datei, in der ist den zellen J3, K3, L3, M3 und
N3 Felder sind, in denen man einen haken setzen kann. je nach
haken soll er in den weiter unten liegenden feldern
unterschiedliche berechnungen durchführen. es ist aber nicht
nur so, dass er nach den oben genannten felder
unterschiedliche brechnungten durchführen soll, sondern auch
noch gucken soll, ob er in den feldern, in denen die zu
berechnenden daten stehen, bestimmte werte stehen und auch
dahingehen bestimmte berechnungen durchführen. beispiel: wenn
in zelle J3 ein „x“ gesetzt ist und du in zelle F10 den wert
„10“ findest, da

nn rechne das und das. wenn in zelle J3 ein

„x“ ist und in zelle F10 der wert „12“ steht, dann rechne das
und das…
ich hab das ganze mal ausprobiert, und auf irgendeine art und
weise klappt das auch. allerdings muss ich so viele kriterien
abfragen, dass der platz für die formel nich ausreicht (in F10
können ca. 15 verschiedene werte stehen und dann gibt es ja
auch noch die haken in J3…). hier mal ein teil der
formel(wie gesagt: alle kriterien abzufragen ging nicht):

hat jemand eine idee, wie man das besser lösen kann???

problem:

in einer anderen datei habe ich einen datenbereich, in dem
viele werte ausgegeben werden sollen (aus anderen bestehenden
excel dateien). jetzt möchte ich oben felder haben, in denen
ich z.b. eingeben kann:
jahr1: 2003
jahr2: 2004
etc. und excel soll mir dann in den datenbereich die
entsprechenden werte aus den excel dateien des jeweiligen
jahres ziehen (pro jahr eine datei). mit welcher formel kann
man das realisieren??

schonmal vielen vielen dank für eure hilfe.

gruss
eazy

Alex_Winter · 16. September 2006 um 13:27

hallo eazy!

Ich bin zwar nicht Reinhard, habe mir aber dein Problem auch angesehen.

ja, es muss noch wesentlich mehr rein. allerdings habe ich
überhaupt keinen plan von VBA und kann auch die formel nicht
interpretieren. kannst du das vielleicht noch näher erleutern?

=WENN(UND(J5=„x“;ODER(F11=10;F11=30;F11=31;F11=12;F11=33;F11=34;F11=35;F11=48;F11=36;F11=37));Q11*W11/X11*O11*Y11*(V11-R11+U11);Q11*W11/X11*O11*Y11*U11)
Diese Formel fragt der Reihe nach die Werte ab, die in der Zelle F11 stehen. Steht da 10, 30, 31, 12 etc drin und dazu in J5 ein x, recnnet Excel folgendes:
Q11*W11/X11*O11*Y11*(V11-R11+U11)

Steht was anderes drin, wird folgendes berechnet:
Q11*W11/X11*O11*Y11*U11

Mit der Funktion Rechnen meinte Reinhard eine selbstgeschriebene Funktion, die du als Add-In speichern kannst. Dieser Funktion musst du nurmehr die Werte übergeben und der Rest wird von der Funktion erledigt. Die ganzen Abfragen sind also in der Funktion gespeichert. Du kannst diese Funktion dann immer aus dem Funktionsassistenten aufrufen.

Gruß Alex

Franz_e6478d · 16. September 2006 um 14:47

Hallo eazy,

Die Formel läßt sich folgt verkleinern:

=WENN(ODER(UND(ODER(F11=10;F11=30;F11=31);J5="x");UND(ODER(F11=12;F11=33;F11=34;F11=35;F11=37);K5="x");
 UND(ODER(F11=48;F11=36);L5="x"));AF11+Q11\*W11/X11\*O11\*Y11\*(V11-R11+U11);AF11+Q11\*W11/X11\*O11\*Y11\*U11)

hat jemand eine idee, wie man das besser lösen kann???

Reinhard’s Vorschlag mit der benutzerdefinierten Funktion ist wahrscheinlich ein guter Ausweg. Diese Funktion wird unter VBA in einem Modul definiert und könnte für deine angegbenen Bedingungen wie folgt aussehen.

Function WertBerechnen(F\_Zelle As Integer, X1 As String, X2 As String, X3 As String, \_
 X4 As String, X5 As String, AF As Double, Q As Double, W As Double, X As Double, \_
 O As Double, Y As Double, V As Double, R As Double, U As Double) As Double
 'F\_Zelle = Kriteriumswert aus Spalte F
 'X1 bis X5 sind die Ankreuzfelder
 'AF, Q, W, X, O, Y, V, R und U sind die Werte aus diesen Spalten zur F-Zelle
 'hier kann man natürlich auch Variablennamen verwenden die den Inhalt besser beschreiben
 Select Case F\_Zelle
 Case 10, 30, 31
 If X1 = "x" Or X1 = "X" Then
 WertBerechnen = AF + Q \* W / X \* O \* Y \* (V - R + U)
 End If
 Case 12, 33 To 35, 37
 If X2 = "x" Or X2 = "X" Then
 WertBerechnen = AF + Q \* W / X \* O \* Y \* (V - R + U)
 End If
 Case 36, 48
 If X3 = "x" Or X3 = "X" Then
 WertBerechnen = AF + Q \* W / X \* O \* Y \* (V - R + U)
 End If
 Case Else
 WertBerechnen = AF + Q \* W / X \* O \* Y \* U ' Wert wenn keine der Bedingungen erfüllt wird
 End Select
End Function

Diese Funktion kannst du jetzt um weitere Case und/oder If-Bedingungen erweitern.

In der Tabelle gibts du die Funktion wie jede andere Funktion ein und kannst auch die Bezüge absolut oder relativ setzen. Zur obigen Funktion schaut es so aus:

=WertBerechnen(F11;$J$5;$K$5;$L$5;$M$5;$N$5;AF11;Q11;W11;X11;O11;Y11;V11;R11;U11)

Eine Alternative wäre noch, wenn du die Ergebnisse für die Kriterien in eine Ergebnismatrix einträgst. Dann kann man per WENN und SVERWEIS den zugehörigen Prüfwert festlegen/prüfen und das entsprechenden Ergebnis berechnen.
Das kann dann etwa so aussehen, wobei die Ergebnismatrix beliebig in der Datei angelegt werden kann, z.B. auch in einer anderen Tabelle

Tabellenblattname: Tabelle1

 A B C D E F G H I J K L M N 
 4 X1 X2 X3 X4 X5 
 5 x 
 6 
 7 
 8 
 9 
10 Kriterium 
11 1 -2 33oxooo 33 
12 
13 
14 
15 Ergebnis -Matrix 
16 Wert F11 X1 X2 X3 X4 X5 Kriterium Ergebnis 
17 10 x 10xoooo 1 
18 12 x 12oxooo 1 
19 30 x 30xoooo 1 
20 31 x 31xoooo 1 
21 33 x 33oxooo 1 
22 34 x 34oxooo 1 
23 35 x 35oxooo 1 
24 36 x 36ooxoo 1 
25 37 x 37oxooo 1 
26 48 x 48ooxoo 1 

Benutzte Formeln:
B11: =WENN(ISTFEHLER(SVERWEIS(D11;$H$17:blush:I$27;2;FALSCH));AF11+Q11\*W11/X11\*O11\*Y11\*U11;
WENN(SVERWEIS(D11;$H$17:blush:I$27;2;FALSCH)=1;AF11+Q11\*W11/X11\*O11\*Y11\*(V11-R11+U11);"Was anderes"))
D11: =F11&WENN(ISTLEER($J$5);"o";$J$5)&WENN(ISTLEER($K$5);"o";$K$5)&WENN(ISTLEER($L$5);"o";$L$5)
 &WENN(ISTLEER($M$5);"o";$M$5)&WENN(ISTLEER($N$5);"o";$N$5)
H17: =B17&WENN(ISTLEER(C17);"o";C17)&WENN(ISTLEER(D17);"o";D17)&WENN(ISTLEER(E17);"o";E17)
 &WENN(ISTLEER(F17);"o";F17)&WENN(ISTLEER(G17);"o";G17)
Formel in H17 nach unten kopieren

problem:

in einer anderen datei habe ich einen datenbereich, in dem
viele werte ausgegeben werden sollen (aus anderen bestehenden
excel dateien). jetzt möchte ich oben felder haben, in denen
ich z.b. eingeben kann:
jahr1: 2003
jahr2: 2004
etc. und excel soll mir dann in den datenbereich die
entsprechenden werte aus den excel dateien des jeweiligen
jahres ziehen (pro jahr eine datei). mit welcher formel kann
man das realisieren??

Die Funktion INDIREKT funktioniert leider nur wenn die Datei(en) aus der(denen) die Daten geholt werden sollen geöffnet sind. Werden die Dateien geschlossen, dann wird nach der nächsten Neuberechnung in den Zellen mit INDIREKT ein Fehlerwert angezeigt, der erst wieder verschwindet wenn die entsprechenden Dateien geöffnet werden.

Du müsstest also per Formel immer direkt die Werte aus der entsprechenden Jahrestabelle holen. Wenn du das Jahr wechseln willst, dann kannst du mit Suchen/Ersetzen in den entsprechendne Spalten die Dateinamen ersetzen. Ggf. kann man sich hier die Arbeit auch mit einem kleinen VBA-Makro erleichtern.

Gruß
Franz

Reinhard_fb8cb7 · 16. September 2006 um 15:36

Hi eazy,

ja, es muss noch wesentlich mehr rein. allerdings habe ich
überhaupt keinen plan von VBA und kann auch die formel nicht
interpretieren. kannst du das vielleicht noch näher erleutern?

Dies ist die gleiche, nur weniger Werte für F11, probier sie einfach mit Testwerten in J3 und F11 aus und schau wie sich die Zelle wo die Formel steht verändert je nach F11 und J5.
=WENN(UND(J5=„x“;ODER(F11=10;F11=30;F11=31));„Berechnung1“;„Berechnung2“)

die dateien für die vorjahre heißen:
Eckwerte2003
Eckwerte2004 etc.

Prinzipiell geht das so:

Datei: Huptmappe.xls Tabelle1
 B 
1 Eckwerte2003
2 Bin E5 
3 Bin E6 
4 Bin E7 
5 Bin E8 

Benutzte Formeln:
B2: =INDIREKT("["&$B$1&"]Tabelle1!E"&ZEILE()+3)
B3: =INDIREKT("["&$B$1&"]Tabelle1!E"&ZEILE()+3)
B4: =INDIREKT("["&$B$1&"]Tabelle1!E"&ZEILE()+3)
B5: =INDIREKT("["&$B$1&"]Tabelle1!E"&ZEILE()+3)


Datei: Eckwerte2003.xls Tabelle1
 E
5 Bin E5
6 Bin E6
7 Bin E7
8 Bin E8

Gruß
Reinhard

Reinhard_fb8cb7 · 16. September 2006 um 16:30

=WENN(UND(J5=„x“;ODER(F11=10;F11=30;F11=31;F11=12;F11=33;F11=34;F11=35;F11=48;…
…F11=36;F11=37));Q11*W11/X11*O11*Y11*(V11-R11+U11);Q11*W11/X11*O11*Y11*U11)

Hi eazy,
neuer Ansatz, Formel bleibt nahezu gleichlang, egal wieviele Möglichkeiten F11 hat:

=WENN((J5=„x“)*NICHT(ISTFEHLER(SVERWEIS(F11;Tabelle2!A1:A10;1;0)));„Berechnung1“;„Berechnung2“)

Dazu geört dann:

Tabellenblattname: Tabelle2
 A 
 1 10 
 2 30 
 3 31 
 4 12 
 5 33 
 6 34 
 7 35 
 8 48 
 9 36 
10 37

Gruß
Reinhard