PDA

Vollständige Version anzeigen : umfangreiche WENN Formel um ein weiteres pauschales WENN ergänzen


mannie
20.04.2009, 08:32
Hallo,
momentan behelfe ich mir zur Lösung mit einem Zwischenschritt, der funktioniert aber ich hätte ihn gerne weg - dazu bräuchte ich eine Ergänzung zu folgender Formel:


=NICHT(E17=0)*(WENN($C$26="A";'HPA-Sätze'!F$3;WENN($C$26="B";'HPA-Sätze'!F$4;WENN($C$26="C";'HPA-Sätze'!F$5;WENN($C$26="D";'HPA-Sätze'!F$6;WENN($C$26="E";'HPA-Sätze'!F$7;WENN($C$26="F";'HPA-Sätze'!F$8;WENN($C$26="Stud. Hilfskraft";'HPA-Sätze'!F$9*E17;WENN($C$26="Wiss. Hilfskraft";'HPA-Sätze'!F$10*E17;)))))))))


In dem Arbeitsblatt HPA-Sätze sind 2 Spalten mit Werten (F und H) hinterlegt. Ich würde die Formel gerne dahingehend ergänzen, das WENN in dem Arbeitsblatt Sigma-Export im Feld B7 20 steht, dann soll unter Berücksichtigung der anderen WENNS die Spalte F ausgewählt werden (so wie auch oben in den Formel hinterlegt) - Wenn in Arbeitsblatt Sigma-Export im Feld B7 22 steht sollte die Spalte H ausgewählt werden.

was anderes - wie kann ich eigentlich Smilies vermeiden wenn in den Beispielformeln z.b. ein ;) vorkommt.???

Bin gespannt ob das geht.

Danke & Gruß

mannie

jinx
20.04.2009, 08:35
Moin, mannie,

unterhalb des großen Antwortfensters steht der Bereich Zusätzliche Einstellungen zur Verfügung, dort können durch ein Kontrollkästchen Grafische Smileys deaktiviert werden (gilt dann aber auch für gewollte).

Ich werde den Beitrag editieren und den anderen "in die Wüste" schicken... ;)

IngGi
20.04.2009, 11:44
Hallo mannie,

versuch's mal mit dieser Formel:

=WENN(E17=0;"";INDEX(BEREICH.VERSCHIEBEN('HPA-Sätze'!$F$3:$F$10;0;$B$7-20);VERGLEICH($C$26;{"A";"B";"C";"D";"E";"F";"Stud. Hilfskraft";"Wiss. Hilfskraft"};0))*E17)

Gruß Ingolf

mannie
20.04.2009, 12:55
Hallo Ingolf,

haut noch nicht so recht hin - habe eine Beispieldatei vorbereitet und hochgeladen...

Geändert werden soll in dem Arbeitsblatt SfP1 die Formel in der blau markierten Spalte - Dadurch will ich mir die Spalte F in dem Arbeitsblatt HPA-Sätze mit dem eingebauten Zwischenschritt sparen - diese Spalte will ich dann auch löschen. Verwendet werden sollen die Werte in Spalte C wenn der Wert B7 in dem Arbeitsblatt Sigma Export "20" entspricht, oder eben die Spalte E wenn der Wert nicht 20 entspricht (generell steht in B7 entweder 20 oder 22)

Danke

mannie

IngGi
20.04.2009, 13:49
Hallo mannie,

in X17 müsste die Formel dann so lauten:

=E17*INDEX(BEREICH.VERSCHIEBEN('HPA-Sätze'!$C$3:$C$10;0;'SIGMA-Export'!$B$7-20);VERGLEICH($C$26;{"A";"B";"C";"D";"E";"F";"Stud. Hilfskraft";"Wiss. Hilfskraft"};0))

Gruß Ingolf

mannie
20.04.2009, 14:07
Hallo Ingolf - SUPER

Nur leider verstehe ich diese Formeln fast nicht mehr - buhhu - wäre klasse wenn du sie mir etwas erläutern würdest...
wohert erkennt excel z.b. das er von C nach E schauen muss - wenn statt 20 was anderes in B7 steht


Gibt es eigentlich Literatur die es einem möglich macht - solches leichter zu verstehen und zu begreifen?

Wäre es auch möglich in diese Formel noch einzubauen - WENN in $C$26 Praktikant steht dann keinen Wert einzutragen - Dies kommt leider auch vor und da erscheint momentan ein optisch etwas unschönes #NV
und wenn in $C$26 kein Wert steht, sollte auch nichts eingetragen werden...

Danke nochmals für deine Hilfe!

mannie

IngGi
20.04.2009, 15:05
Hallo mannie,

wenn sichergestellt ist, dass in den Zellen C11 und E11 des Tabellenblattes HPA-Sätze nichts drinsteht, dann kann man die Bereiche C3:C10, bzw. E3:E10 ja einfach um eine Zelle auf C3:C11, bzw E3:E11 erweitern und die Liste der Suchwerte um den Eintrag "Praktikant" erweitern:

=E17*INDEX(BEREICH.VERSCHIEBEN('HPA-Sätze'!$C$3:$C$11;0;'SIGMA-Export'!$B$7-20);VERGLEICH($C$26;{"A";"B";"C";"D";"E";"F";"Stud. Hilfskraft";"Wiss. Hilfskraft";"Praktikant"};0))

Die Formel ist gar nicht so schwer zu verstehen, wie sie aussieht. Schauen wir uns mal die Funktion INDEX in ihrer einfachsten Form an:

=INDEX(A1:E1;3)

Vor dem Semikolon wird ein Zellbereich angegeben, dahinter eine Zahl, die aussagt, die wievielte Zelle aus dem Zellbereich genommen werden soll. In diesem Fall stellt die Funktion also einen Bezug auf die Zelle C1 her, da diese die dritte Zelle im Zellbereich ist und gibt den Inhalt dieser Zelle zurück.

Bei deiner Problemstellung steht der Zellbereich nicht von vorneherein fest. Er ist entweder 'HPA-Sätze'!C3:C11 oder 'HPA-Sätze'!E3:E11, je nach dem, ob die Zelle 'SIGMA-Export'!B7 den Wert 20 oder 22 enthält. Die Spalte E liegt nun 2 Spalten rechts von Spalte C, was zufällig genau der Differenz zwischen 20 und 22 entspricht. Wenn ich also 20 von dem Wert in Zelle 'SIGMA-Export'!B7 abziehe, dann erhalte ich den Wert 0, wenn ich die Spalte C nehmen muss und den Wert 2, wenn ich die Spalte E nehmen muss. Ich kann also sagen, gehe von dem Zellbereich 'HPA-Sätze'!C3:C11 aus und gib mir den Zellbereich zurück, der um 0 oder 2 Spalten nach rechts versetzt liegt, je nach Wert in 'SIGMA-Export'!B7. Genau das erreiche ich mit der Funktion BEREICH.VERSCHIEBEN:

=BEREICH.VERSCHIEBEN('HPA-Sätze'!C3:C11;0;2)

bedeutet: Gehe vom Zellbereich 'HPA-Sätze'!C3:C11 aus und gib mir den Zellbereich zurück, der um 0 Zeilen nach unten und um 2 Spalten nach rechts versetzt liegt. Das wäre dann der Bereich 'HPA-Sätze'!E3:E11. Anstatt nun einen Versatz von 2 Spalten fest in der Formel vorzugeben, kann ich einfach 20 vom Wert in der Zelle 'SIGMA-Export'!B7 abziehen und erhalte:

=BEREICH.VERSCHIEBEN('HPA-Sätze'!C3:C11;0;'SIGMA-Export'!B7-20)

Jetzt müssen wir uns noch darum kümmern, die wievielte Zelle aus dem angegebenen Zellbereich die INDEX-Funktion zurückgeben soll. Dabei kommt es ja darauf an, was in der Zelle $C$26 steht. Dort steht einer der Werte A, B, C, D, E, F, Stud. Hilfskraft, Wiss. Hilfskraft oder Praktikant, was den Zellen 1 bis 9 aus unserem Zellbereich entspricht. Steht in $C$26 also zum Beispiel ein C, dann ist das der dritte Wert aus unserer Liste und demnach soll die INDEX-Funktion auch den Inhalt der dritten Zelle aus dem Zellbereich zurückgeben. Hier hilft uns die Funktion VERGLEICH weiter. Mit

=VERGLEICH($C$26;$A$1:$A$9;0)

wird der Inhalt der Zelle C26 mit dem Inhalt der Zellen A1 bis A9 verglichen. Wird der Inhalt von C26 zum Beispiel in A5 gefunden, gibt die VERGLEICH-Funktion eine 5 zurück, da die Zelle A5 die fünfte Zelle im durchsuchten Bereich A1:A9 ist. Wir können uns nun nicht auf einen Zellberich beziehen, da die zu durchsuchenden Werte nicht in Zellen stehen, sonder fest vorgegeben sind. Eine Liste mit fest vorgegebenen Werten kann ich nun ganz einfach mit folgendem Konstrukt fest in die Formel einbauen:

{"A";"B";"C";"D";"E";"F";"Stud. Hilfskraft";"Wiss. Hilfskraft";"Praktikant"}

Jetzt haben wir alles, was wir brauchen und müssen die Formel nur noch zusammensetzen und zusätzlich mit E17 multiplizieren:

=E17*INDEX(BEREICH.VERSCHIEBEN('HPA-Sätze'!$C$3:$C$11;0;'SIGMA-Export'!$B$7-20);VERGLEICH($C$26;{"A";"B";"C";"D";"E";"F";"Stud. Hilfskraft";"Wiss. Hilfskraft";"Praktikant"};0))

Literatur zu Excelformeln gibt es wie Sand am Meer. Eine Empfehlung kann ich nicht aussprechen. Bei mir ist das einfach zu lange her.

Gruß Ingolf

BoskoBiati
20.04.2009, 15:06
Hallo mannie,

in Deinem anderen Thread habe ich Dir geschrieben, dass es fast für alles eine Lösung gibt. Hier wäre meine für Deine Tabelle. Es geht auch mit Sverweis.

mannie
20.04.2009, 15:12
Besten Dank für die Mühen - muss mir nun alles erst mal in Ruhe anschaun...

S U P E R !!!!

ist das auch noch möglich?

"wenn in $C$26 kein Wert steht, sollte auch nichts eingetragen werden..."

IngGi
20.04.2009, 15:30
Hallo mannie,

dafür kannst du einfach noch eine WENN-Abfrage um die Formel herum setzen:

=WENN($C$26="";"";E17*INDEX(BEREICH.VERSCHIEBEN('HPA-Sätze'!$C$3:$C$11;0;'SIGMA-Export'!$B$7-20);VERGLEICH($C$26;{"A";"B";"C";"D";"E";"F";"Stud. Hilfskraft";"Wiss. Hilfskraft";"Praktikant"};0)))

Gruß Ingolf

BoskoBiati
20.04.2009, 15:36
Hallo mannie,

hier mit der gewünschten Änderung:

mannie
21.04.2009, 09:16
danke euch beidem - eien Super Hilfe - ein letztes Problemchen hätte ich noch - dann wäre ich fertig und könnte mich mal der Analyse eurer Hilfen widmen..

Diese Formel
=WENN(E36<>0;RUNDEN(S36/T36;5);0)

Es kan sein das durch die Formel in S36 dort kein Wert steht - wenn dies so ist, wid mir in der Zelle in der die o.g. Formel steht #WERT! ausgebenen, da Excel ja nicht mit 0 dividieren ;) kann - was muss ich anstellen um auch in einem solchen Fall die Zell leer zu haben anstatt #WERT! stehen zu haben...habe es schon mit ein paar Wenns versucht aber noch nicht erfolgreich ..

merci

mannie

jinx
21.04.2009, 09:21
Moin, mannie,

=WENN(UND(E36<>0;T36<>0);RUNDEN(S36/T36;5);0)
Multiplizieren kann Excel mit 0 schon, aber nicht divideren... ;)

mannie
21.04.2009, 10:27
Hallo Jinx,

funktioniert leider immer noch nicht - habe nun eine Datei hochgeladen - es geht um die Formel in der Spalte V - habe noch etwas mit deiner Formel experimentiert - aber leider kein Ergebnis...

danke

mannie

jinx
21.04.2009, 10:56
Moin, mannie,

kein Wunder bei einer Formel in der referenzierten Zelle (hier mein Vorschlag für die Zelle V37):

=WENN(UND(E37<>0;ISTFEHLER(S37*1)=FALSCH);RUNDEN(S37/T37;5);0)

mannie
21.04.2009, 12:16
PERFEKT!!


denke nun kann ich Ruhe geben und auch in Ruhe versuchen alles mal nachzuvollziehen um beim nächsten Problem schlauer zu sein :)

Danke an euch alle!!

mannie

mannie
23.04.2009, 15:29
hmm jetzt habe ich doch noch ein letztes Anliegen - durch das Verändern der verschiedenen Formeln ist eine kleine Unlogic in das Gebilde geraten und ich müsste diese Formel noch anpassen:



=WENN(C$50="Praktikant";G44;WENN(ODER(C$50={"A";"B";"C";"D";"E";"F";"Stud. Hilfskraft";"Wiss. Hilfskraft"});X44;))

und zwar soll hier nur was ausgeben werden, wenn in E44 ein Wert steht. In E44 wiederum ist eine Formel, und zwar diese =WENN(S40>0;Q40;)

das ganze hat sich nun ergeben weil ich diese Formel

=WENN($C$26="";"";E17*INDEX(BEREICH.VERSCHIEBEN('HPA-Sätze'!$C$3:$C$11;0;'SIGMA-Export'!$B$7-20);VERGLEICH($C$26;{"A";"B";"C";"D";"E";"F";"Stud. Hilfskraft";"Wiss. Hilfskraft";"Praktikant"};0)))

umändern musste in

=WENN($C$26="";"";1*INDEX(BEREICH.VERSCHIEBEN('HPA-Sätze'!$C$3:$C$11;0;'SIGMA-Export'!$B$7-20);VERGLEICH($C$26;{"A";"B";"C";"D";"E";"F";"Stud. Hilfskraft";"Wiss. Hilfskraft";"Praktikant"};0)))

BoskoBiati
23.04.2009, 16:23
Hallo mannie,

Du hast jetzt jede Menge Formeln umändern müssen. Wenn Du die Tabelle betrachtest, die ich mit Sverweis gestaltet habe, dann ist nur eine zusätzliche Spalte geändert, nämlich L, und Die Tabelle sieht immer noch aus wie Dein Original.

BoskoBiati
24.04.2009, 07:06
Hallo mannie,

anbei noch eine Version, bei der der Praktikant in die HPA-Tabelle eingearbeitet ist.

mannie
24.04.2009, 07:41
Hallo Edgar,
ich hatte gestern abend schon al versucht auf deine S-Verweis Variante zurückzugreifen - hatte aber dann noch einen Fehler, da ich nicht alle veränderten Formeln zurückverändert hatte (langsam wird das ganze etwas unübersichtlich ;) ) - aber nun hats geklappt

der einzige Schönheitsfehler bei der S-Verweisvariante ist der das wenn z.B. in C26 Praktikant erscheint als HPA Satz eon #NV ausgeworfen wird, weil es den Wert ja nicht gibt. Aber ich denke das ist verkraftbar - deine HPA Variante bringt mir nichts, da es für Praktikant keinen Wert gibt.

Habe jetzt auch für die Nicht S-verweis Variante eine Lösung gefunden, die aber auch noch einen kleinen Haken hat, der aber auch hinnehmbar ist.

Aber so habe ich nun 2 fertige Lösungen wer weiß was noch kommt.

Danke

mannie

BoskoBiati
24.04.2009, 08:15
Hallo mannie,

s. meinen Beitrag #19

mannie
24.04.2009, 08:19
den (#19) habe ich schon gesehen - aber da es für Praktiant keinen HPA Wert gibt, bringt mir das auch nix....

danke

BoskoBiati
24.04.2009, 08:27
Hallo mannie,

dann trage doch einfach nichts oder 0 ein.