verschachtelter "wenn dann" mit sverweis

[tessa]

Hallo zusammen, 

kann mir jemand bei der Berechnung von Stundensätzen helfen?
Grundsätzlich ist es so, dass Stunde 1-8, 9+10 und über 10 jeweils unterschiedlich berechnet werden.
Zusätzlich wird unterschieden nach Art der Ausbildung, Fahrtzeit und Tag des Einsatzes.
Es geht um die Berechnung der Spalte S in der Tabelle "Berechnung NEU".
Beschreibung der Thematik und Testbeispiel im Blatt " Erklärung".
Ich würde mich freuen, wenn mir jemand helfen kann.
Meine Emailadresse ist stef-kaiser[ät]web.de

Viele Grüße
Stefanie

[WillWissen]

Hallo Stefanie,

ich habe deine E-Mailadresse etwas verfälscht, um Robotern das Auslesen zu erschweren. Zudem ist eine E-Mailadresse nicht nötig, da Antworten aus Gründen der Fairness im Forum veröffentlicht werden sollen, damit alle User etwas davon haben.

[Matthias84]

Hallo,

ergänze mal noch die folgende Tabelle in einem neuen Tabellenreiter namens "Hilfstabelle":
[
Bild bitte so als Datei hochladen: Klick mich!
]
In den Spalten trägst du die einzelnen Tage ein, in den Spalten die Untergrenze der Stunden (z.B. wird für 7 Stunden in Spalte B gesucht).

Anschließend ersetzt du deine Formel in Spalte S durch

Code:

=SVERWEIS(C18;'Rates Arbeitszeit'!A:J;INDEX(Hilfstabelle!A1:D4;VERGLEICH('Berechnung NEU'!B18;Hilfstabelle!A1:A4;0);VERGLEICH(SUMME('Berechnung NEU'!N18:O18);Hilfstabelle!A1:D1;1));FALSCH)

Diese Formel sucht aus der Hilfstabelle die Spalte heraus, aus der du den jeweiligen Wert haben möchtest.

[BoskoBiati]

Hi,

ich kann nicht verstehen, warum Du diese Sch...-Formel immer noch bringst, obwohl Du hier:

http://www.clever-excel-forum.de/Thread-...t-zutrifft

schon eine Lösung hattest?

[PAndersen]

Hallo Stefanie.

Ich kann Dir eine Lösung ohne Hilfstabelle vorschlagen.

Die Fahrzeit ist relativ klar

                     (N18+P18)*SVERWEIS(B18;'Rates Fahrzeit'!$A$2:$D$4;WAHL(Q18;2;2;2;2;2;2;2;2;3;3;4;4;4;4;4);FALSCH)

Bei der Arbeitszeit ist die Unterscheidung der 9 Spalten komplex. Ich würde die Nummer der auszuwählenden Spalte so ermitteln

                      WAHL(Q18;2;2;2;2;2;2;2;2;2;3;3;4;4;4)+WENN(B18="Wochenende";3;WENN(B18="Feiertag";6;0)

Damit sieht die Formel zur Arbeitszeit so aus, die Du zur Fahrzeit addieren müsstest:

                     O18*SVERWEIS(C18;'Rates Arbeitszeit'!$A$2:$J$6;WAHL(Q18;2;2;2;2;2;2;2;2;2;3;3;4;4;4)+WENN(B18="Wochenende";3;WENN(B18="Feiertag";6;0));FALSCH)

Ich hoffe, damit Deine Aufgabenstellung getroffen zu haben.

LG Peter

[tessa]

Danke für die Anregung @matthias84, aber das hilft mir leider nicht weiter, da ich dann doch wieder manuell die Stunden berechnen muss, die mit den einzelnen Aufschlägen belegt werden sollen. Aber dennoch danke für die Idee! Viele Grüße - Stefanie

[tessa]

Hallo BoskoBiati,
weil dieser Ansatz leider nicht die Lösung gebracht hat. Drum hab ich die Thematik nochmals detaillierter beschrieben.
Viele Grüße
Stefanie

[tessa]

Danke @PAndersen, aber auch das ist leider nicht so trivial, da die Hinfahrt zum Beispiel ohne Aufschlag ist, die Rückfahrt ja aber schon mit Aufschlag sein muss (zumindest im Beispielfall) - und dann eben entsprechend mit dem Aufschlag für 9+10 oder über 10 und dann entsprechend mit dem Wochentag, Wochenende oder Feiertag.
Aber es beruhigt mich etwas... die Konstellation ist wirklich hoch komplex - schon bei der Fahrtzeit und wird noch komplexer bei der Arbeitszeit...

[Matthias84]

Achso, vielleicht habe ich dich auch falsch verstanden.. Also ist nicht die Gesamtzeit entscheiden, sondern die Stunden 1 bis 8 werden immer mit dem Wert aus Spalte B berechnet, auch wenn derjenige insgesamt 11 Stunden an dem Tag gearbeitet hat?

[tessa]

Hej Matthias,
nein, du hast schon ganz Recht,  die Staffel gibt es schon.
ich gehe davon aus, dass du von der Arbeitszeit redest und dann muss ich ja erst einmal wissen, wie viele Stunden vorher schon Fahrtzeit gelaufen sind.
Das heißt, im Beispielfall müsste ich Stunde 1-5 mit 100 % berechnen (also Spalte B) und Stunde 6+7 mit 125 % (Spalte C) oder eben entsprechend E und F , resp. H und I
Vielleicht bin ich ja diejenige, die es unnötig kompliziert macht und es ist ganz einfach?!
Viele Grüße - Stefanie