Office: Wenn-oder-und Formel wie richtig zusammenstellen?

Hallo Zusammen

Da mir bei meinem letzten Problem so gut geholfen wurde, hoffe ich kann man mir dieses Mal wieder so gut helfen.

Das Problem dieses Mal ist folgende Formel:

=WENN(ODER(I20=2;I20=4)UND(J10="CH");'Auftrag Partner'!M23*'Bezüge Kalkulation'!I16;'Auftrag Partner'!M22*'Bezüge Kalkulation'!I15);WENN(ODER(I20=2;I20=4)UND(J10="DE");'Auftrag Partner'!M23*'Bezüge Kalkulation'!J16;'Auftrag Partner'!M22*'Bezüge Kalkulation'!J15)...

Der rote Teil wird mir als falsch gemeldet. Ich muss die genau gleiche Formel noch weitergehen lassen; nähmlich soll die nicht nur nach CH unterscheiden sondern auch noch nach DE, AT und LUX, die bezüge währen dementsprechend auch anders aber diese könnte ich dann selber noch wählen.

Ich könnte mir vorstellen, dass wenn die Formel so noch weitergezogen wird auch noch ein Verschachtelungsproblem hinzukommen könnte.


Ich hoffe ich habe mich einigermassen verständlich äussern können ansonsten Fragt bitte nach!

Ich nutze Excel 2010.

Danke schon im Voraus!

Grüsse

ICAS

 

Hallo,

=WENN(UND(ODER(I20=2;I20=4);J10="CH");

 

Super, danke silex1.

Funktioniert! Hab mich glaube ich mit den ganzen Infos im Web verwirrt. Versuche Sie jetzt noch auf alle Länder zu vervielfältigen und schreib dann mal die Vollständige Formel hier rein, ist vielleicht von Nutzen :)

Danke nochmals!

 

Haai, habe es gleich versucht auf ein weiteres Land zu übertragen:

=WENN(UND(ODER(I20=2;I20=4);J10="CH");M23*'Bezüge Kalkulation'!I16;'Auftrag Partner'!M22*'Bezüge Kalkulation'!I15);WENN(UND(ODER(I20=2;I20=4);J10="DE");O23*'Bezüge Kalkulation'!K16;'Auftrag Partner'!O22*'Bezüge Kalkulation'!K15)

Seht ihr bzw. du silex1 einen Fehler? Weil so kalkuliert es nicht mehr korrekt.

Gruss & danke

 

Hallo,

da Du in dem anderen Beitrag ja schon ne Lösung für Dein Problem bekommen hast und nun "das Gleiche in grün" auftaucht, poste doch mal ne anonymisierte kleine Bsp.-Datei, damit wir hier im Forum nicht im Kreis laufen!

 

Hallo,

ich habe Dir vorstehend die Formelbestandteile eingefärbt. Hier wird deutlich sichtbar, dass Du einfach 2 WENN-Funktionen strichpunktgetrennt aneinandergereiht hast, statt sie in irgendeiner Weise zu verschachteln. Aneinandergereihte WENN-Funktionen gibt es nicht in einem Formelausdruck - deshalb auch die Fehlermeldung.
Es liefert nämlich jede WENN-Funktion einen Funktionswert zurück. Somit würden aufgrund der 2 WENN-Funktionen zwei Werte in einem Ausdruck für eine einzelne Zelle produziert - und damit kann Excel überhaupt nichts anfangen.

Daher ist, so meine auch ich, der Vorschlag von silex1 am besten, wenn Du eine (anonymisierte) Datei hochlädst, sodass man dann entscheiden kann, in welcher Weise die Verschachtelung mehrerer WENN-Funktionen durchgeführt werden soll - oder ob man gegebenenfalls eine ganz andere Formellösung machen sollte ("anonymisiert" soll heißen, dass alle Elemente entfernt oder ersetzt sind, die wir als Helfer nicht sehen sollen, dass aber all die Dinge vorhanden sind, die eine vollständige Darstellung/Wiedergabe des Problems gewährleisten).

Generell ist zu WENN-Funktionen Folgendes zu sagen: Sie besitzen 3 Parameter:
=WENN(logAusdruck;WahrAusdruck;FalschAusdruck)
Der logische Wert (WAHR bzw. FALSCH) von "logAusdruck" entscheidet darüber, ob die WENN-Funktion den Wert von "WahrAusdruck" oder von "FalschAusdruck" zurückliefert: Falls "logAusdruck" den Wert WAHR (bzw. ungleich 0) hat, liefert die WENN-Funktion den Wert von "WahrAusdruck"; hat "logAusdruck" den Wert FALSCH (bzw. 0), liefert die WENN-Funktion den Wert von "FalschAusdruck".
Im Prinzip kann jeder der 3 Parameter (logAusruck, WahrAusdruck, FalschAusdruck) wieder aus einer WENN-Funktion bestehen - aber meistens setzt man eine solche nur für "WahrAusdruck" bzw. "FalschAusdruck" ein. Diesen Vorgang nennt man Verschachteln von (WENN-)Funktionen.
Beispiel für Verschachtelung:

Code:

=[COLOR="#FF0000"]WENN(A<B;[/COLOR][COLOR="#0000CD"]WENN(C>D;3;5,5)[/COLOR][COLOR="#FF0000"];[/COLOR][COLOR="#008000"]WENN(UND(E=F;G<>H);9,5;-17,3)[/COLOR][COLOR="#FF0000"])[/COLOR]

Die äußere WENN-Funktion ist die rote. An die Stelle von "WahrAudruck" dieser roten WENN-Funktion tritt die blaue WENN-Funktion, und an die Stelle von "FalschAusdruck" der roten WENN-Funktion tritt die grüne WENN-Funktion. Ist z.B. nun tatsächlich A größer B, liefert die rote WENN-Funktion das Ergebnis der grünen WENN-Funktion zurück. Somit ist in diesem Fall das Ergebnis des gesamten Formelausdruckes entweder 9,5 oder -17,3 je nachdem, welchen logischen Wert die UND-Funktion besitzt. Dabei ist die UND-Funktion an die Stelle des "logAusdruck"s der grünen WENN-Funktion (hinein) verschachtelt.

 

Hallo zusammen

Danke schonmal für die Antworten. Habe jetzt eine Beispieltabelle zusammengestellt.

Jetzt aber noch ein paar Infos, damit es auch einigermassen verständlich ist was mein Ziel ist;

Ihr findet in der Zelle F20 die Beispielformel, wie sie auf die anderen Länder übertragen werden muss.

Das Ziel ist, dass mann nur in den Zellen M22 - U22 eingeben muss wieviele Stunden es dauert und anschliessend rechnet die Formel anhand des gewählten Services den Preis. Der Service nr. 2 und 4 sind express und haben deshalb einen anderen Preis (deshalb dieser Zusatz in der Formel ODER(I20=2;I20=4)) und das ist auch nach Land entsprechend unterschiedlich. Das Land kann durch einen Dropdown angewählt werden und ist mit der Formel verlinkt (seht ihr in der Tabelle dann).

Ich hoffe das ich mich irgendwie verständlich für euch ausdrücke, ansonsten fragt nach, versuche dann so gut wie möglich weiterzuhelfen.

Danke nochmals und Gruss

ICAS

 

Hallo Zusammen

Ich denke meine Frage ist leider in Vergessenheit geraten. Kann mir trotzdem jemand versuchen zu helfen und einen Blick in das alte Thema werfen (Link): Danke vielmals im Voraus!

An Silex1:
Es war nicht böse gemeint, dass ich nachgefragt habe ob es vergessen gegangen ist.

Ich hatte keine Ahnung, dass die Beiträge nach oben rutschen wenn mann wieder eine neue Antwort einfügt. Als neuling in einem Forum weiss man noch vieles nicht.

Natürlich sind die antworten kostenfrei und werden in der Freizeit beantwortet. Ich denke man sieht auch dass ich freundlich schreibe und mich auch ausführlich bei den Helfenden bedanke. Ich bringe die nötige Dankbarkeit und Respekt mit und wenn dem nicht so ist, dann lass es mich bitte wissen.

Trotzdem danke für die Infos.

Grüsse

ICAS

 

Hallo,

ich war nicht böse und empfand Deine Beiträge auch nicht so. Und als Neuling muss man sich halt auch erst zurecht finden, dies ist schon verständlich.
Leider fielen mir allerdings erneut die Beitragserstellungszeiten auf, die in das von mir angemerkte hinein passten wie "die Faust aufs Auge".

Da wir hier kein "Laber-Rhababer-Forum" sind, wie manch andere, sondern es doch sehr fachspezifisch ist, ist es für Helfer immer schwer sich nach Tagen erneut in das Problem reindenken zu müssen. Und leider gibt es viele User, die Freitags schnell noch ne Frage haben und erwarten das am Montag dann die Komplettlösung nur noch runter geladen werden muss.
Dies wird es allerdings hier nicht geben, denn dieses Forum ist gedacht für "Hilfe zur Selbsthilfe".

Leider fehlen bei Dir noch die DropDownMenüs für die Länder und ich weiß auch nicht was die Spalten M22:T22 so machen sollen, denn die Zeiten stehen doch schon im Blatt "Bezüge Kalkulation"?!?
Vielleicht kannst Du dies mal genauer erläutern, vor allem den gewünschten Rechenweg.

Vorab schon mal ne Lösungsmöglichkeit (mit schatzi´s Formel) um die Daten aus dem anderen Blatt zu holen:

Arbeitsblatt mit dem Namen 'Auftrag Partner'
	A	B	C	D	E	F	G	H	I
18	Benötigte Dienstleistungen	Datum & Zeit	Dauer	Verrechenbares Honorar	Beschreibung	Zusätzliche Kosten
19						Reisezeit	Reisespesen	Vorbereitung
20	Vor Ort Einsatz / Express			0		63			2
21	0								Service No.?
22	0								Service No.?
23	0								Service No.?

Zelle	Formel
F20	=INDEX('Bezüge Kalkulation'!$I$15:$P$16;ISTZAHL(FINDEN("Express";A20))+1;VERGLEICH(J10;{"CH";"DE";"AT";"LUX"};0)*2-1)

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

 

- Genau deshalb habe ich mich bei euch registriert!

Guten Morgen,

danke für die Formel Silex1. Klappt leider irgendwie nicht..

Ich habe jetzt die Tabelle ergänzt (hänge ich gleich mit an) damit ihr mal in die Vollständige Version reinschauen könnt.

Ich denke ich äußere mich auch nicht wirklich sehr verständlich, dafür mal ein *Tut mir Leid* von meiner Seite!

Hier ganz genau und in die Länge gezogen, was ich in eine Formel umzuwandeln versuche (Bezüge stimmen mit denen aus der Tabelle überein):

In der Zelle K20 soll folgendes geschehen:

--> Wenn O10="CH" und N20 = 1,3,5,6,7 oder 8
Dann O20 * Bezüge Kalkulation I15
Sonst(wenn bei N20 2 oder4 steht) O20 * Bezüge Kalkulation I16

--> Wenn O10="DE" und N20 = 1,3,5,6,7 oder 8
Dann Q20* Bezüge Kalkulation K15
Sonst(wenn bei N20 2 oder4 steht) Q20 * Bezüge Kalkulation K16

--> Wenn O10="AT" und N20 = 1,3,5,6,7 oder 8
Dann S20 * Bezüge Kalkulation M15
Sonst(wenn bei N20 2 oder4 steht) S20 * Bezüge Kalkulation M16

--> Wenn O10="LUX" und N20 = 1,3,5,6,7 oder 8
Dann U20 * Bezüge Kalkulation O15
Sonst(wenn bei N20 2 oder4 steht) U20 * Bezüge Kalkulation O16


Ich hoffe das ist ansatzweise Verständlich und ihr könnt was mit der Tabelle anfangen.

Bei Fragen wendet euch gerne an mich und sorry für dieses doofe und komplizierte Thema.

Gruss

ICAS

 

Hallo,

in Deiner beiliegenden Excel-Datei habe ich Dir 5 Formeln ins Arbeitsblatt "Auftrag Partner" eingefügt, in denen nirgends eine WENN-Funktion vorkommt:

1) In Zelle O12 habe ich folgende Formel eingefügt:
=VERGLEICH(O10;{"CH";"DE";"AT";"LUX"};0)*2-1
Den Wert, den diese Formel liefert, habe ich so formatiert, dass er unsichtbar bleibt - die Zelle darf nur nicht anderweitig verwendet werden.
Der Sinn dieser Funktion ist folgender: Du greifst auf 2 Tabellenbereiche zu:
Die 1. Tabelle liegt in 'Auftrag Partner'!O20:V23, die 2. Tabelle liegt in 'Bezüge Kalkulation'!I15:P16. In beiden verwendest Du 2 Spalten für ein Land und die Länder folgen in derselben Reihenfolge. Die Formel liefert die Nummer der jeweils 1. Spalte eines Landes, d.h. also für "CH" die Nummer 1, für "DE" die Nummer 3, für "AT" die 5 und für "LUX" die 7.

2) In der Zelle K20 habe ich folgende Formel eingefügt:
=INDEX($O20:$W20;1;$O$12)*INDEX('Bezüge Kalkulation'!$I$15:$P$16;INDEX({1;2;1;2;1;1;1;1};$N20);$O$12)
Hier werden 2 INDEX-Funktionen miteinander multipliziert: die erste holt sich den Std-Wert aus dem jeweiligen Land-Bereich des Bereiches O20:W20 und die zweite holt sich ebenfalls aus dem jeweiligen Land-Bereich des Bereichs 'Bezüge Kalkulation'!$I$15:$P$16 die Reisezeit, wobei diese 2. INDEX-Formel noch unterscheidet, ob "Normal" (1.Zeile) oder "Express" (2.Zeile) auszuwählen ist. Diese Zeilenermittlung erfolgt mittels einer weiteren INDEX-Funktion INDEX({1;2;1;2;1;1;1;1};$N20), die in die 2. INDEX-Funktion hinein verschachtelt ist.

3) In die Zellen K21, K22 und K23 habe ich Dir in analoger Weise die Formeln wie unter Punkt 2) eingefügt.

 

Hallo,

müsste doch aber auch mit meiner abgewandelten Formel von schatzi funktionieren:
=WENN(F20<>"";INDEX('Bezüge Kalkulation'!$I$15:$P$16;ISTZAHL(FINDEN("Express";F20))+1;VERGLEICH($O$10;{"CH";"DE";"AT";"LUX"};0)*2-1)*INDEX(O20:W20;VERGLEICH(O10;{"CH";"DE";"AT";"LUX"};0)*2-1);"")

Tip:Montag nach 8 Uhr!

 

Hallo Silex1

Finde deine die erste Version super! Genial!!!! Möchte die ganze Tabelle ausser ein paar Zellen sperren, demzufolge gehe ich davon aus, dass sich nichts verschieben wird!

Noch eine andere Frage, ich habe die Formel auf die Originaltabelle genommen, Sie funktioniert (!) aber wenn keine Werte eingegeben sind, kommt bei mir in der Zelle ein #NV - wie kann ich die Zelle leer oder mit einer 0 haben?

Und was muss ich an der Formel anpassen, das auf G20 : G23 die Formel stimmt um die Km (Spesen) mit den Preisen aus der Bezüge Kalkulation Tabelle J15 J16, L15 L16, N15 N16 und O15 O16 zu berechnen?

Ich kann die Formel ja genau gleich rüber ziehen, muss jedoch eine kleine anpassung machen, weiss aber nicht welche.

Danke nochmals silex1! Ganz ehrlich eure Hilfe hier im Forum ist sehr nützlich!! Würde mich auch gegen eine Gebühr anmelden um euch für die ganzen Mühen entgegenzukommen.

Gruss José

P.S:

was meinst du damit?

 

Die #NV-Fehlermeldung habe ich mit der Formel "Wennfehler" behoben. Diese Frage hat sich also erledigt.

Gruss

ICAS

 

Hallo Zusammen

Sorry dass ich schon wieder mit einer Frage zu diesem Thema komme, aber ich verstehe die Matrix der INDEX - Formel nicht, und kann sie so nicht weiter anwenden.

Wie oben gesagt:

Ich weiss nicht was genau ich anpassen muss dass sich die Formel auf die richtigen Zellen bezieht.

Danke für eure Erklärungen im Voraus. Ich habe 10 Tage gewartet um es selber zu versuchen aber die Erklärungen im Google sind einfach nicht verständlich für mich.

Gruss ICAS