| | | Xometry verstärkt sein Angebot für die Blechbearbeitung: Mehr Fertigungsoptionen und bessere Sofort-Angebote, eine Pressemitteilung
|
Autor
|
Thema: Excel Fahrtenbuch mit Reisekostenauswertung (4462 mal gelesen)
|
Little Panda Mitglied selbständiger Techniker
Beiträge: 10 Registriert: 16.02.2011 Aspire 8930, Office 2010
|
erstellt am: 16. Feb. 2011 14:27 <-- editieren / zitieren --> Unities abgeben:
Hilfe!!! sitze seit Wochen an einem Problem mit meinem Excel-Fahrtenbuch. Ich möchte nicht extra eine Tabelle für die Reisekostenabrechnung eingeben müssen da mir die Informationen im Fahrtenbuch vorliegen. Mein Problem liegt in der Verknüpfung und Auswertung der Informationen. Das Fahrtenbuch ist wie folgt aufgebaut. Spalte A=Datum, B=Abfahrtszeit, C=Ankunftszeit, D=Fahrtroute, E;F;G=Zweck der Fahrt (geschäftlich;Wohnung/Arbeit;privat), H;I=Km-Stand Abfahrt/Ankunft, J;K;L=Gefahrene Km (geschäftlich;Wohnung/Arbeit;privat), M;N=Kraftstoff (Liter/Betrag), O=Liter Verbrauch/100 Km, P=sonstige Kosten, Q=Fahrer. In Spalte D=Fahrtroute liegt der Knackpunkt. Ich muß zwischen den Kombinationen Wohnort-Einsatzort / Einsatzort / Einsatzort-Wohnort und Wohnort-Einsatzort-Wohnort unterscheiden da bei der Route Wohnort-Einsatzort und Abfahrt um 07:00 die Zeit bis 24:00 als Reisezeit gerechnet wird, bei Einsatzort von 0:00 bis 24:00, und bei Einsatzort-Wohnort nur die Zeit von 0:00 bis zur Ankunft z.B. 14:00. Bei der Kombination Wohnort-Einsatzort-Wohnort ist nur die Zeit der Abfahrt bis zur Ankunft entscheidend. Jetzt fehlt noch die Bedingung weniger als 8 Stunden 0€, 8-14 Stunden 6€, 14-24 Stunden 12€ und 24Stunden 24€. Einen Teil konnte ich schon lösen (Fahrtrouten mit Leereichen-Leerzeichen also München - Stuttgart - München getrennt) =WENN(ISTZAHL(SUCHEN("Wohnort -";D1));"vorhanden";"nicht vorhanden") Vielen Dank für jede Hilfe Grüße vom kleinen Panda Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
Paulchen Mitglied Bauing./SW-Entwickler
Beiträge: 1227 Registriert: 19.08.2004 Büro: Win10 Enterprise 64bit, Office Professional Plus 2013 - Privat: Linux Mint 15, LibreOffice
|
erstellt am: 16. Feb. 2011 15:30 <-- editieren / zitieren --> Unities abgeben: Nur für Little Panda
Hallo kleiner Panda, willkommen im Forum ! Schreib' Dir Deine Bedingungen in einen Extra-Bereich, anfangs noch auf dem gleichen Blatt. (Schön machen mit Namensvergabe oder anderem Register im Hintergrund geht später auch noch.) So wie ich es verstanden habe, sind es vier Bedingungen? Drösel die mal auf, evtl. in Hilfsspalten, die sich ggf. ausblenden lassen. Reihenfolge: Fahrten - Stunden - Kosten. Alles in EINE Formel packen zu wollen, wird unübersichtlich und schlecht zu pflegen/nachzuvollziehen. Am Besten wärs natürlich, Du nimmst die sensiblen Daten 'raus und lädst eine (Dummy-)Mappe hoch... ------------------ DIN1055.de | Lastannahmen für Anwender Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
ThoMay Ehrenmitglied V.I.P. h.c. Konstrukteur
Beiträge: 5260 Registriert: 15.04.2007
|
erstellt am: 16. Feb. 2011 19:40 <-- editieren / zitieren --> Unities abgeben: Nur für Little Panda
|
Comos User Mitglied
Beiträge: 112 Registriert: 23.03.2010
|
erstellt am: 17. Feb. 2011 08:32 <-- editieren / zitieren --> Unities abgeben: Nur für Little Panda
|
Little Panda Mitglied selbständiger Techniker
Beiträge: 10 Registriert: 16.02.2011 Aspire 8930, Office 2010
|
erstellt am: 17. Feb. 2011 09:01 <-- editieren / zitieren --> Unities abgeben:
Danke für die schnelle Antwort, bin etwas weiter es fehlt noch die Einbindung der Spalten "Z" und "AA" für die Abwesenheit. Wäre schön wenn es mit weniger Spalten (ab Spalte T Richtung Spalte AC) gehen würde. Bin für jede Lösung dankbar und wenn mit Erklärung ist warum und weshalb noch viel dankbarer. Info zum Anhang: Es kommt eine Sicherheitsmeldung wegen Makro aber die Datei ist OK Grüße vom kleinen Panda Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
Little Panda Mitglied selbständiger Techniker
Beiträge: 10 Registriert: 16.02.2011 Aspire 8930, Office 2010
|
erstellt am: 17. Feb. 2011 21:28 <-- editieren / zitieren --> Unities abgeben:
Die Vorlage für die Fahrtenbuchtabelle war das Fahrtenbuch aus Papier, so wollte es mein Steuerberater, da ich die monatlichen Ergebnisse als Jahresübersicht auf einem Extrablatt mit Spritkosten Gesamt- und Durchschnittskosten habe. Wenn du keinen Freund beim Finanzamt sitzen hast versuchs du jeglichen Ärger aus dem Weg zu gehen und dann kommt halt so was raus. Mit Excel konnte ich das realisieren und ich hatte früher auch die Zeit für den Verpflegungsmehraufwand eine eigene Tabelle zu führen. Aber jetzt bin ich zeitlich mehr eingespannt und möchte meine Freizeit mit meiner Familie und nicht mit doppelter Arbeit verbringen. Kann ja sein das es mit einer Datenbankanwendung leichter wäre aber da kenn ich mich nicht mit aus. Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
CADdog Ehrenmitglied V.I.P. h.c.
Beiträge: 2280 Registriert: 30.04.2004 Intel Xeon 3,6 GHz 32 GB RAM NVIDIA Quadro P2000 Windows 10 Enterprise AutoCADmap 3D 2023 WS-Landcad 2023 QGIS 3.28.5
|
erstellt am: 18. Feb. 2011 07:44 <-- editieren / zitieren --> Unities abgeben: Nur für Little Panda
Hallo, ich weiß nicht, wie man die jetzt wirklich noch helfen kann, IMHO ist alles gesagt, was gesagt werden kann. zusätzliche Spalten sind sinnvoll, damit du verstehst, was deine Tabelle macht und damit du in der Lage bist, etwas zu ändern. Diese Spalten kannst du dann ausblenden oder später, wennn du es verstanden hast, zusammenführen. Nebenbei, da ich die Worte Finanzamt und Fahrtenbuch gelesen habe: ausgedruckte Excel-Listen werden IMHO nicht anerkannt, da sie einfach zu manipulieren sind. Nicht dass die ganze Arbeit (außer einem gewaltigem Lernerfolg) umsonst ist. ------------------ Gruß Thomas CADdog, the dog formerly known as TR AutoCAD spricht mit einem, aber viele hören nicht zu. Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
Thomas Harmening Moderator Arbeiter ツ
Beiträge: 2897 Registriert: 06.07.2001 Das Innerste geäussert und aufs Äusserste verinnerlicht
|
erstellt am: 18. Feb. 2011 13:10 <-- editieren / zitieren --> Unities abgeben: Nur für Little Panda
Nur zur Verständnis: in Zeile 23 03.01.2011 8:00 21:30 Wohnort - ES - TUT - RW - gilt hier 24:00-8:00 = 16 oder soll die tatsächliche Diff. aus 21:30-8:00 errechnet werden? Wenn dies bekannt, ist könnte ich ja dann heute Abend meine Mappe hochladen - bislang IMHO ohne Wenns
Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
Little Panda Mitglied selbständiger Techniker
Beiträge: 10 Registriert: 16.02.2011 Aspire 8930, Office 2010
|
erstellt am: 18. Feb. 2011 13:27 <-- editieren / zitieren --> Unities abgeben:
Hallo CADdog, die Sache mit dem Finanzamt ist mir bekannt und ich führe mein Fahrtenbuch natürlich handschriftlich wie vom Gesetzgeber verlangt. Mir nützt die Sache als Tabelle, da ich dann die Differenzkilometer nicht einzeln rausrechnen muß und einen schnellen Überblick habe was erstattete und noch abrechenbare Kilometer sind (die Nuss ist aber schon gecknackt). Trotzdem danke für den Hinweis Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
Little Panda Mitglied selbständiger Techniker
Beiträge: 10 Registriert: 16.02.2011 Aspire 8930, Office 2010
|
erstellt am: 18. Feb. 2011 14:21 <-- editieren / zitieren --> Unities abgeben:
Hallo und entschuldige wenn ich mich nicht klar ausgedrückt habe dann versuche ich das nochmal. Spalte A = Datum Spalte B = Abfahrt oder Arbeitsbeginn Spalte C = Ankunft oder Feierabend Spalte D = Fahrroute (Hier ist es wichtig an welcher Stelle der Wohnort steht) Spalte E = legt fest ob es eine geschäftliche Reise ist Spalte F = legt fest ob es eine Fahrt Wohnung/Arbeit ist und wird von mir nicht mehr benötigt Spalte G = legt fest ob es eine private Fahrt ist Auch noch wichtig! Reisekostenabrechnung (Verpflegungsmehraufwand) Steuerlich absetzbar bei Abwesenheit vom Wohnort 0 Std bis 8 Std = 0 € 8 Std bis 14 Std = 6 € 14 Std bis 24 Std = 12 € über 24 Std (Arbeit und Übernachtung nicht am Wohnort)= 24 € Um Unklarheiten zu beseitigen gibt es einige Beispiele Zeile 23 Spalte B Abfahrt am Wohnort um 08:00, Spalte C Ankunft in RW 21:30, Übernachtung in RW, Arbeitszeit von 08:00 bis 21:30 sind 13,5 Std, da nicht am Wohnort übernachtet wird sind die 2,5 Std bis 24:00 Uhr für die Reisekostenabrechnung (Verpflegungsmehraufwand) wichtig. Denn von 08:00 bis 24:00 sind es 16 Std und somit (14 Std bis 24 Std = 12 €) Zeile 24 Spalte B Abfahrt in RW um 08:00, Spalte C Ankunft in RW 20:00, Übernachtung in RW, Arbeitszeit von 08:00 bis 20:00 sind 12 Std, da nicht am Wohnort übernachtet wird spielt die Arbeitszeit keine Rolle sondern die Abwesenheit vom Wohnort also 24 Std. Denn von 00:00 bis 24:00 sind es 24 Std und somit(Arbeit und Übernachtung nicht am Wohnort)= 24 € Zeile 25 Spalte B Abfahrt in RW um 08:00, Spalte C Ankunft am Wohnort 22:00, Übernachtung am Wohnort, Arbeitszeit von 08:00 bis 22:00 sind 14 Std, da wieder am Wohnort übernachtet wird sind die 8 Std bis 8:00 Uhr für die Reisekostenabrechnung (Verpflegungsmehraufwand) wichtig. Denn von 00:00 bis 22:00 sind es 2 Std und somit (14 Std bis 24 Std = 12 €) Zeile 26 Privat und somit nicht absetzbar also unwichtig Zeile 27 und 28 Abwesenheit vom Wohnort weniger als 8 Std (0 Std bis 8 Std = 0 €) Zeile 29 Spalte B Abfahrt am Wohnort um 09:00, Spalte C Ankunft am Wohnort 18:30, Übernachtung am Wohnort, Arbeitszeit von 09:00 bis 18:30 sind 9,5 Std die wieder für die Reisekostenabrechnung (Verpflegungsmehraufwand) wichtig. Denn von 09:00 bis 18:30 sind es ,5 Std und somit (8 Std bis 14 Std = 6 €) Ich hoffe mein Problem ist jetzt besser verständlich und danke für jede Hilfe
Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
Little Panda Mitglied selbständiger Techniker
Beiträge: 10 Registriert: 16.02.2011 Aspire 8930, Office 2010
|
erstellt am: 18. Feb. 2011 22:10 <-- editieren / zitieren --> Unities abgeben:
Zitat: Original erstellt von Thomas Harmening: Nur zur Verständnis:in Zeile 23 03.01.2011 8:00 21:30 Wohnort - ES - TUT - RW - gilt hier 24:00-8:00 = 16 oder soll die tatsächliche Diff. aus 21:30-8:00 errechnet werden? Wenn dies bekannt, ist könnte ich ja dann heute Abend meine Mappe hochladen - bislang IMHO ohne Wenns
Hallo, ich habs gepackt durch austauschen der Spalten Z und AA ist ein Fehler verschwunden (lag evtl. an der Zellenformatierung) jetzt bekomme ich die Ergebnisse die ich brauche wenn ich die Spalten B bis G ausfülle. Das Ergebnis: =WENN(X22>8<=14;"6,00 €";WENN(UND(X22>=14;X22<24);"12,00 €";WENN(X22>=24;"24,00 €";WENN(Y22>=24;"24,00 €";WENN(Z22>8<=14;"6,00 €";WENN(UND(Z22>=14;Z22<24);"12,00 €";WENN(Z22>=24;"24,00 €";WENN(UND(AA22>8;AA22<=14);"6,00 €";WENN(AA22>14<=24;"12,00 €";WENN(AA22>=24;"24,00 €";"0,00 €")))))))))) Ist eigentlich garnicht so schwer wenn man's erst mal aufdröselt. Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
pi-design Mitglied Design Engineer
Beiträge: 950 Registriert: 09.08.2002 Excel 365 WIN 10 Enterprise 64 Bit
|
erstellt am: 19. Feb. 2011 08:47 <-- editieren / zitieren --> Unities abgeben: Nur für Little Panda
Hy, ich befürchte, daß Du einen Fehler in Deiner Formel hast. Zumindest in EXCEL2007 ist es ein Fehler: =WENN(X22>8<=14;"6,00 €";......... Richtig wäre: =WENN((X22>8)*(X22<=14);"6,00 €";........ ("*" = und / "+" = oder) oder =WENN(und(X22>8;X22<=14);"6,00 €";........ Sollte aber Deine Formel in EXCEL2010 so funktionieren, dann vergiß mein Thread.
------------------ Gruß Pille Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
Little Panda Mitglied selbständiger Techniker
Beiträge: 10 Registriert: 16.02.2011 Aspire 8930, Office 2010
|
erstellt am: 19. Feb. 2011 10:18 <-- editieren / zitieren --> Unities abgeben:
Zitat: Original erstellt von pi-design: Hy, ich befürchte, daß Du einen Fehler in Deiner Formel hast. Zumindest in EXCEL2007 ist es ein Fehler:=WENN([b]X22>8<=14;"6,00 €";......... Richtig wäre: =WENN((X22>8)*(X22<=14);"6,00 €";........ ("*" = und / "+" = oder) oder =WENN(und(X22>8;X22<=14);"6,00 €";........
Sollte aber Deine Formel in EXCEL2010 so funktionieren, dann vergiß mein Thread. [/B]
Hallo pi-design,
danke für deine Vorschläge, in Excel 2010 spuckt es die Ergebnisse aus die ich erwarte, aber ich versuche deine Möglichkeit. Es kann ja nicht schaden und wenns auch in 2010 klappt wieder was dazu gelernt. Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
pi-design Mitglied Design Engineer
Beiträge: 950 Registriert: 09.08.2002 Excel 365 WIN 10 Enterprise 64 Bit
|
erstellt am: 19. Feb. 2011 10:56 <-- editieren / zitieren --> Unities abgeben: Nur für Little Panda
|
Thomas Harmening Moderator Arbeiter ツ
Beiträge: 2897 Registriert: 06.07.2001 Das Innerste geäussert und aufs Äusserste verinnerlicht
|
erstellt am: 20. Feb. 2011 21:53 <-- editieren / zitieren --> Unities abgeben: Nur für Little Panda
Wie gut das Excel ab 07 auch mehr als 8 Wenn Verschachtelungen zulässt Die verschiedenen Zustände lassen sich auch so realisieren. Hilfsspalte: Code: NICHT(ISTFEHLER(SUCHEN("Wohnort -";D22)))*1&NICHT(ISTFEHLER(SUCHEN("- Wohnort";D22)))*1&(E22="x")*1
Abfahrt ja = 1 Ankunft ja = 1 Privat = 1 Einsatz Wohnort ... Wohnort = 00 Das ergibt dann die verschiedenen Kennziffern 10, 01, 11, 00 und als 3 Ziffer 1 wenn privat.Desweiteren wird eine Matrix für die Kostenerstattung definiert, auf die nachher mit Sverweis() zugegriffen wird. unter die jeweiligen Bedingungen für die Kostenerstattung den Kennziffern eintragen Zellen AB19-AE19 101 001 111 011 und mittels der Formel die sich auf die Zelle der Hilfsspalte und der Matrix Kostenerstattung und dem jeweiligen Schlüssel in AB19:AE19 lässt sich dann diese Formel erstellen, die nach links und runter kopiert werden kann.
Code: =($Y22=AB$19)*SVERWEIS((MAX((TEIL($Y22;1;2)="01")*$C22;(TEIL($Y22;1;2)="10")*(REST(1-$B22;1));(TEIL($Y22;1;2)="00")*1;(TEIL($Y22;1;2)="11")*($C22-$B22))*(1))*TEIL($Y22;3;1);K ostenerstattung;2;1)
Zum Schluss noch die Zelle als Währung formatieren um die Nullwerte durch ein - zu ersetzen.Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
| Anzeige.:
Anzeige: (Infos zum Werbeplatz >>)
|