Frage von Heribert10, 71

Excel: Formel für Reisekostenabrechnung (Wenn Abwesenheit>8 und <24h UND "LAnd", Dann xx Euro. Wie geht das?

Also ich möchte eine Mappe erstellen, bei der man die REisekostenabrechnung sieht. Dabei habe ich auf einem Tabellenblatt den Monat. zb. August. Es gibt also 12 Blätter, a ein Monat.

Ich habe dann noch ein Blatt eingefügt mit der BEschriftung "Länder". Hierbei sind die einzelnen Länder mit den individuellen Sätzen.

Ich möchte auf dem Monatsblatt eine Dropdownliste auf der man dann das Land auswählt (erledigt) Verweis auf "Länder" Blatt.

Danach habe ich die Abfahrt und Ankuftszeit zum eingeben. Daraus ergibt sich die "Abwesenheit" in Uhrzeitformat.

Jetzt möchte ich, dass dahinter automatisch der jeweilige Satz kommt. 1. Spalte: 8 bis 24 Stunden abwesenheit, 2. Spalte 24 Stunden Abwesenheit, 3. Spalte Übernachtungspauschale. Diese Werte sollen aus der Tabelle "Länder" genommen werden, da hier auch die Pauschalen hinterlegt sind.

Wie mache ich das? Also es soll Rücksicht auf das eingegebene Land nehmen und zeitgleich auf die Abwesenheit.

Antwort
von MaximusD, 44

Du musst hier einen sverweis machen, also wenn du im Blatt "Länder" die Einträge wie z.B.

Land   Satz1   Satz2  Pauschale3
DE      20         30      40

stehen hast, dann

sverweis(Spalte mit dem Land aus dem aktuellen Monat;Spalte mit dem Land aus Blatt Länder von oben bis unten;1;0)

und dann in den Spalten daneben dasselbe mit  ;2;0) und ;3;0) am Schluss statt des ;1;0).

Kommentar von Heribert10 ,

Mhhm. Dann zeigt er mir immer "Deutschland" an, statt die dazugehörige Zahl. Davon abgesehen soll die Formel gleichzeitig die Abwesenheitszeit berücksichtigen.

Tabellenblatt Länder ist so aufgebaut

Land     8-24 h           24h              Übernachtungspauschale

De       12 Euro          24 Euro        20

Tabellenblatt Monat:

Abfahrt Ankunft Abwesenheit Land     8-24h     24h     Übernacht

 10:00    20:00     10:00:00    DE         12€         0             0

Die fett unterlegte Zahl soll automatisch kommen sobald man DE eingibt; Abwesenheit wird automatisch ausgerechnet wenn man Abfahrt und Ankunft eingibt.

Die Formel soll sich auf Abwesenheit und Land beschränken.

Wenn Abwesenheit 8-24 h UND DE dann 12 Euro (quasi)

Kommentar von MaximusD ,

Versuch mal in das Feld E2, wo die 12€ stehen soll, folgende Formel (ich nehm mal an die Spalten heißen A, B, C, usw, und du stehst in Zeile 2):
=wenn(C2<1;sverweis(D2;Länder!A:A;2;0);0)

und in F2 dann:
=wenn(C2>=1;sverweis(D2;Länder!A:A;3;0);0)

und in G2 dann:
=wenn(F2=0;0;sverweis(D2;Länder!A:A;4;0))

Hoffe ich hab mich hier nicht vertippt ,)

Kommentar von Heribert10 ,

Leider kommt andauernd nur "Bezug". Ich wäre auch damit einverstanden wenn man die Prämisse auf "Abwesenheit" weg lässt und diese per Hand einträgt. Ich glaube das gibt Probleme mit der Formatierung als Zeit.

Kommentar von MaximusD ,

Wenn du das Feld mit der Zeitberechnung als Zeit formatiert hast, entsprechen 24 Stunden dem Wert 1 und 12 Stunden dem Wert 0,5 - das hab ich in der Formel oben so berücksichtigt.

Bezug deutet eher auf einen Fehler beim sverweis hin, du kannst diesen ja testweise ohne das wenn vorher machen, bis er klappt.

Kommentar von Heribert10 ,

Ja es liegt am SVERWEIS. Komisch.. Ich habe es genau so gemacht wie du geschrieben hast und wie Excel es mir vorgibt. Muss ich die Spalte ("2") auch auf das Blatt "Länder" beziehen oder reicht da wirklich nur ;2;0;

Kommentar von MaximusD ,

Die 2 heißt dass von der Spalte Land im Blatt Länder (= 1 = die erste Spalte) eins weiter nach links gegangen wird, also 2. Und bei der anderen Formel die 3 heißt dann die nächste Spalte.

Die 0 danach bedeutet FALSCH, also dass es eine genaue Übereinstimmung sein muss und nicht eine ungefähre (beim Vergleich des Landes).

Versuch den sverweis mit dem Assistenten neu aufzubauen, bei dir heißen die Felder bzw. Spalten ja anders.

Kommentar von Heribert10 ,

Ok ich denke ich habe es. Man muss die komplette Tabelle als Matrix angeben. Hatte bisher nur die 1. Spalte :D Also SVERWEIS soweit geglückt. Nur noch das mit der Zeit

Kommentar von Heribert10 ,

OK super! Vielen Dank für die Hilfe! Soweit bin ich glücklich.

Kommentar von Heribert10 ,

Neues Problem. :D

Ich möchte nun auf einer Übersichtstabelle die gesamten Reisekosten ausweisen!

Also auf dem Monatsblatt wird eine Gesamtsumme gezogen und diese soll auf der Hauptübersicht stehen bei dem jeweiligen Mitarbeiter.

Hierbei soll die Information vom Tabellenblatt Monat (zB Juni) angezeigt werden, automatisch. bei mir geht das zwar mit Hilfe eines SVerweises ganz gut, aber bei Mitarbeitern die keine Kosten haben kommt NV... Und dann kann keine Summe gezogen werden. Summe zeigt auch NV an. sieht 1. nicht schön aus das NV (lieber leer lassen) und die Summe wird nicht gezogen. Wie löse ich das?

Mitarbeiter Jun  Jul  Aug    .....     Summe

1 Ulf           2         3                           5

2 Kai         NV       2                          NV

3 Teo        3        NV                         NV

               nv          Nv                      NV

Kommentar von MaximusD ,

Hi,

also wenn ein sverweis einen NV ergibt, kann man das wie folgt abfangen:

=wenn(istfehler(sverweis(xxxx));0;sverweis(xxxx))

also du musst denselben sverweis (oben mit xxxx) gekürzt zweimal einfügen, einmal wird geprüft ob ein Fehler rauskommt (wenn ja, wird 0 hingeschrieben) und einmal dann wenn kein Fehler rauskommt, dann steht das Ergebnis des sverweis da.

Keine passende Antwort gefunden?

Fragen Sie die Community