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.
1 Antwort
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).
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 ,)
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.
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.
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;
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.
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
OK super! Vielen Dank für die Hilfe! Soweit bin ich glücklich.
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
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.
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)