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

...komplette Frage anzeigen

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).

Heribert10 16.08.2016, 15:14

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)

0
MaximusD 16.08.2016, 15:23
@Heribert10

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 ,)

0
Heribert10 16.08.2016, 15:36
@MaximusD

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.

0
MaximusD 16.08.2016, 15:42
@Heribert10

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.

0
Heribert10 16.08.2016, 15:47
@MaximusD

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;

0
MaximusD 16.08.2016, 15:52
@Heribert10

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.

0
Heribert10 16.08.2016, 15:55
@MaximusD

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

0
Heribert10 17.08.2016, 09:35
@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

0
MaximusD 17.08.2016, 16:00
@Heribert10

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.

0

Was möchtest Du wissen?