Office: Abrechnung von Pauschalkosten bei Dienstreisen ins Ausland

Hallo Ihr Lieben,

seit gestern beschäftigt mich ein tierisches Problem und jetzt muss ich mich einfach mal an Euch wenden und hoffe, dass mir jemand helfen kann.

In meiner Firma soll ich ein Formular zur Abrechnung von Reisekosten entwerfen. Die Firma hat viele Reisen ins Ausland abzuwickeln. Für die Dauer der Abwesenheit erhält der Mitarbeiter eine steuerfreie Pauschale, je nach Land und je nach Abwesenheitsdauer. Die Dauer der Abwesenheit richtet sich nach folgenden Kriterien:
mind. 8 Stunden und nicht mehr als 14
mind. 14 Stunden und nicht mehr als 24 und
mehr als 24 Stunden.

Für jede Abwesenheitsdauer gibt es also einen Pauschbetrag.
Hier ein Beispiel:

Ich reise in die USA und bin 16 Stunden dort, dann erhalte ich eine Pauschale von z.B. 24 Euro. Bin ich länger als 24 Stunden dort, bekomme ich eine Pauschale von 36 Euro, bin ich weeniger als 14 Stunden dort, sinkt meine Pauschale auf 12 Euro.

Das bekomme ich ja noch hin, dass kann ich mit einer WENN-Funktion steuern.

Allerdings fliegen Mitarbeiter bis zu 100 Länder an und für die Länder gibt es unterschiedliche Pauschalbeträge. Und hier fängt mein Problem an, dass ich das nicht alles unterbekomme.

Excel muss mir doch zuerst das sog. "Länderkennzeichen" prüfen, um den richtigen Pauschalbetrag in Verbindung mit der Abwesenheitsdauer auswerfen zu können. Abwesenheitsdauer wird selber berechnet, dieser Wert steht in einer einzelnen Zelle. Auch die Länderkennzeichen werden in eine extra Zelle geschrieben (Habe eine Gültigkeitsprüfung eingeführt, dass nichts schief geht).

Tja, aber mehr bekomme ich auch nicht hin??

Weiss jemand von Euch Bescheid??


Vielen Dank schon mal im Vorraus!!!

 

Hallo!

Check mal den Anhang, ob ich dich richtig verstanden habe.

 

Hallo,

zur Sicherheit habe ich mal meine originale Datei angehängt. Ich denke, dann kann nichts schief gehen...

 

Hallo!

Passt es so (Formel in N16)?

Beachte, dass ich die Spalten AE bis AH in der Reihenfolge umgedreht habe.

 

Das ist ja der Hammer!!!
Das geht ja wirklich!!! VIELEN VIELEN DANK!!!

Aber so einfach kann ich Dich nicht gehen lassen. Einige Fragen musst Du mir noch beantworten, ich will die Formel ja auch verstehen und nicht nur anwenden um meinem Chef zu sagen, dass ich es geschafft habe.

Also: Die ganzen anderen Zahlen bzw. Spalten kann ich jetzt löschen? Du beziehst Dich rein auf die eingefärbte Tabelle??

Ich kann die Länder und die zugewiesenen Länderkennzahlen beliebig austauschen bzw. auch erweitern solange ich den Zellbezüg mit abändere?

Die einzige Formel, die Du erstellt hast, befindet sich in Spalte "N"? Sonst wurde nichts mehr irgendwo hinterlegt?

Du benutzt die Formel "Index" und "Vergleich", die Hilfefunktion bietet nicht wirklich viel dazu. Kannst Du mir in drei/vier Worten den Sinn und die Anwendungsbereich erläutern? Nur so im Groben?

Mein Gott, ist das klasse!!!!

Nochmal vielen vielen Dank!!!!!!

Gruß
Dominik

 

Hallo!

Ja, inkl. der Zeile darüber, da Excel mit den Werten in AF15:AH15 nichts anfangen kann.

Richtig!

Wenn die Excel-Hilfe aus Papier wäre, dann könnte man sie hervorragend auf Klo benutzen...

=INDEX(Bereich;Zeile;Spalte)
liefert den Wert aus Zeile/Spalte des Bereichs, z.B.

=INDEX(H4:P9;2;3)
liefert den Wert aus J5 (2. Zeile und 3. Spalte von H4:P9)

=VERGLEICH(Suchwert;Bereich;Anordnungsparameter)
liefert die Zeilen-/Spaltenzahl des Suchwerts im Zeilen-/Spaltenbereich, z.B.

=VERGLEICH("Hallo";D6:H6;0)
liefert 3, wenn "Hallo" in F6 steht (3. Spalte von D6:H6)

Ist der letzte Parameter gleich 0, dann wird nach einer EXAKTEN Übereinstimmung gesucht.
Ist der letzte Parameter gleich 1 (oder fehlt), dann wird in einer AUFsteigend sortierten Liste bei keiner exakten Übereistimmung der nächstkleinere Wert gewählt.

In der Kombination liefert die Formel also den von dir gewünschten Wert.

 

Also das passt alles Super... Hab den Bereich jetzt verschoben, die Bezugswerte neu angepasst und die Formal mit Dollars versehen, um den absoluten Zellbezug herzustellen. Funktioniert einwandfrei...

Vielen Vielen Dank dafür!!!

Aber jetzt taucht das nächste Problem auf. Wenn in den Feldern nichts drinsteht, erscheint ein #NV, was dazu führt, wenn einmal eine #NV drin steht, rechnet Excel unten in der Summe nicht zusammen.

Kann man das auch noch ausschalten?


Viele Liebe Grüße
Dominik

 

Hallo!

Dann versuch doch einen Fehlerabfang, z.B.

=WENN(E16="";"";INDEX(AE$16:AH$61;VERGLEICH(E16;AD$16:AD$61;);VERGLEICH(24*J16;AE$14:AH$14)))

 

Unglaublich... auch das funktioniert..

Aber wie ist der Sinn dieser Wenn-Funktion? Die Anführungsstriche bedeuten Text.. Achso.. er wirft nichts aus.. Damit wird der Zelle gesagt, wenn E16 ist gleich leer, dann soll auch hier leer ausgeworfen werden?

Hab ich das so richtig verstanden?

Mein gott, Du hasts voll drauf... DANKEDANKEDANKE


Viele Liebe Grüße
Dominik

 

Problem, Teil II

Hallo da draußen,

es dreht sich leider immer noch um die oben beschriebene Datei.

Nach Abgabe "meines" Ergebnisses hatte mein Chef noch einige Wünsche, die jedoch mit dem geltenden Reisekostenrecht zu tun haben.

Problem 1: Mal angenommen, es handelt sich um eine mehrtägige Dienstreise, z.B. vom 01. bis 04. eines Monats. In der derzeitigen Tabelle würde das bedeuten, dass man den einzelnen Tag bis 24 Uhr "abschließen" müsste, damit der Tagessatz in Spalte N richtig gerechnet wird (ich habe das beispielhaft schon mal erledigt).

Es soll daher die Möglichkeit geschaffen werden. "über Nacht" zu rechnen. Das heißt, der Anwender soll einfach eintragen, dass er vom 01.01. bis 04.01. mit den entsprechenden Uhrzeiten eine Dienstreise unternommen hat. Excel soll dann in der Lage sein, die mit 24 Stunden belegten Tage mit dem vollen Tagessatz zu rechnen und die angefangenen Tage eben bis 24 Uhr bzw. mit dem anteiligen Tagessatz ab 8 Stunden, ab 14 Stunden und 24 Stunden. Bei dieser Darstellung kämen also 140 Euro raus. Ich habe es mit einer Differenzfunktion probiert, dann bekomme ich jedoch die komplette Abwesenheitsdauer (79 Stunden) und dann wirft mir das System natürlich einen falschen Tagessatz raus.

Eine Idee über die Darstellung habe ich mal in der Spalte D gefertigt. Allerdings (und dafür könnte ich meinem Chef noch mehr an den Hals springen) soll die Eingabe so benutzerfreundlich wie möglich sein (z.B. beim Reisebeginn will er nur 09 oder 9 tippen und Excel soll auf 09:00 auffüllen *kopfschüttel). Auch meine Frage hierzu, kann das tatsächlich irgendwie benutzerfreundlicher gestaltet werden, evtl. auch mit mehreren Spalten)?


Schon mal vorab Vielen Dank!!!

 

Nachtrag: Evtl. kann man bei einer Abwesenheit von 24 Stunden auch n der entsprechenden Zeile ein Kreuzchen setzen...
Egal für was, für brauchbare Vorschläge für einen weltweiten Firmeneinsatz bin ich sehr dankbar!!!

 

Hallo!

Schau mal in den Anhang und teste es durch.
(Die Spalten B und C brauchst du nicht mehr.)

Wenn es sich bei den Stunden immer um volle Stunden handelt, dann kannst du deinem Chef vielleicht eine Freude machen, wenn du die Stundeneingabe über ein Gültigkeits-DropDown regelst.
Wenn dort aber auch noch minutengenaue Angaben gemacht werden sollen, dann wird das zu unübersichtlich und zu lang...

 

Unglaublich... Wenn ich das ganze in Deiner bearbeiteten Datei probiere, funktioniert alles. Habs sofort heute morgen ausprobiert. Nachdem ich die Formel in eine von mir optisch bearbeitete Datei kopiert habe, funktioniert nichts mehr :-(

Ich könnte gerade wahnsinnig werden. Wo liegt denn hier mein Fehler? Ich hab schon alles gemacht, Formel ausgedruckt und Zellbezug für Zellbezug verglichen. Aber als Ergebnis verbleibt es einfach bei #NV... Wenn ich Haare hätte, würde ich sie mir ausreißen...

Kannst Du noch mal reinschauen und mir einen Tipp geben? Bitte nicht korrigieren oder so, sonst versteh ich es wieder nicht. Wenn Du einen Tipp hast oder mir die neue Formel noch mal erklären kannst, komm ich der Sache vielleicht auf die Schliche.

Ich hänge meine Datei noch mal dran, die Formel ist teilweise schon bearbeitet.

Dann kommt glaube ich wieder das Problem, dass leere Zellen auch mit #NV ausgeworfen werden und dass dann unten keine Summe gebildet wird? Kann ich hier auch wieder WENN(F14="";"" voranstellen oder geht das bei dieser komplizierten Formel nicht mehr (reicht es, wenn ich dann am Ende noch zusätzlich eine ) einfüge?)

Viele Liebe Grüße
Dominik

 

So, ich bins schon wieder. Nachdem ich mich jetzt wieder Stunde um Stunde mit dem beschriebenen Fehler beschäftigt habe, ist es mir nun gelungen, die Formel so anzupassen, dass es jetzt auch bei mir funktioniert. Letztendlich ging das über die Fehlerprüfung, wo ich die falschen Bereich einfach in die richtigen geschoben habe.

Was ich jetzt aber immer noch nicht hin bekomme, ist die Fehlerabfrage, dass das Feld keine #NV auswirft, wenn im Länderkennzeichen oder so nichts drin steht. Wie gesagt, sonst wird keine automatische Summe mehr gebildet und dann stimmen auch die Verknüpfungen auf der Zusammenfassung des Sheets nichts mehr. Irgendwo habe ich sicherlich nur einen Kommafehler aber ich finde ihn einfach nicht. Kannst Du mir da noch mal behilflich sein?

Himmel was bin ich Dir zu Dank verpflichtet!!!

Viele Liebe Grüße
Dominik

PS: Nun bearbeitete Datei habe ich noch mall angehängt, dann sieht man auch noch das #NV

 

Hallo nachmal. Ich habs hinbekommen mit der Fehlerabfrage!! Irgendwo musste eine ")" hin und den Ort habe ich gefunden!!

Nochmals vielen vielen Dank!!!


Liebe Grüße
Dominik

:-D