684 Aufrufe
Gefragt in Tabellenkalkulation von paulemann Mitglied (107 Punkte)
Hallo Freunde,


ich stehe mal wieder vor einer für mich schier unlösbaren Aufgabe, was
Euch sicher nur ein müdes Lächeln abringt... Aber ich habe es nun mal
nicht so mit Mathe und Klammern und Formeln...

Vielleicht hat jemand die Muse und Zeit mir unter die Arme zu greifen...


Was habe ich?

Ich möchte den Verpflegungsmehr-Aufwand und die
Übernachtungskosten für unsere Mitarbeiter errechnen, damit ich sie am
Monatsende so dem Steuerberater geben kann...

Ich habe dafür den Reiter "TM 01"
Die Sätze habe ich in Reiter "Sätze 2016"

Es gibt zwei unterschiedliche Sätze, einmal für Deutschland allgemein
und einmal für die restliche Welt, je nach Land...

Was prinzipiell erstmal super wäre, wenn dass geht, das wenn ich in
Spalte D, wenn ich den Landesnamen eintrage, je nachdem was ich
schreibe eventuell schon die Möglichkeiten kommen, also wenn ich dass
Ni schreibe, das er mir Niederlande anbietet oder gleich einträgt...
(geht das? außer mit Liste?)


In meiner Tabelle in "TM 01" habe ich für Deutschland 5 Spalten
(national). Dort setze ich Kreuze, je nachdem was der Mitarbeiter hatte...
RT ist ein Reisetag (immer 12,00 €), danach kommen drei Spalten für
Verpflegung, die in Deutschland zusammen immer bei 24,00 € liegt
(außer der Mitarbeiter hatte z.B. kein Frühstück, dann werden ihm von
den 24,00  20 % abgezogen - die entsprechenden Prozentzahlen stehen
oberhalb der Tabelle)... Und dann gibt es noch eine Übernachtung, die in
Deutschland immer mit 20,00 € berechnet wird...
Je nachdem wo die Kreuze sind soll er dann in Spalte R die Summe
errechnen...

Dann gibt es aber in der Tabelle auch 5 Spalten fürs Ausland. Dort das
gleiche Prinzip, bei einem Reisetag, den Eurobetrag aus der Liste und
dem jeweiligen Land (S Verweis ?), bei einem vollen Tag den Europreis,
der aber prozentual gekürzt sein kann, wenn z.B. kein Frühstück und
eventuell noch den Übernachtungsbetrag ...


Also zusammenfassend.

Toll wäre, wenn ich in Spalte D beim Eintrag des Landes schon die
möglichen Vorschläge bekommen könnte.
Und dann würde ich später gerne meine Kreuzchen setzen und dann
errechnet er automatisch in Spalte R anhand der Daten die in dem Reiter
"Sätze 2016" stehen...


Wäre toll wenn mir jemand dabei helfen könnte...

https://workupload.com/file/txuQtc4







LG Paul

[url=https://workupload.com/file/txuQtc4]https://workupload.com/file/txuQt
c4[/url]

9 Antworten

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Paul,

mit einer einfachen SVERWEIS-Formel ist es ja nicht getan. Du musst ja prüfen, wie die Kreuze gesetzt sind und dann ggf. die Teilbeträge addieren. Und das ganze für national und international.
Wenn ich dich richtig verstanden habe, dann sollte die folgenden Formel für den Satz dir helfen:
=WENN(F23="x";SVERWEIS(D23;'Sätze 2016'!$B$7:$E$245;2;FALSCH);)+WENN(G23="x";RUNDEN(SVERWEIS(D23;'Sätze 2016'!$B$7:$E$245;3;FALSCH)*$M$8;2);)+WENN(H23="x";RUNDEN(SVERWEIS(D23;'Sätze 2016'!$B$7:$E$245;3;FALSCH)*$N$8;2);)+WENN(I23="x";RUNDEN(SVERWEIS(D23;'Sätze 2016'!$B$7:$E$245;3;FALSCH)*$O$8;2);)+WENN(J23="x";SVERWEIS(D23;'Sätze 2016'!$B$7:$E$245;4;FALSCH);)+WENN(L23="x";SVERWEIS(D23;'Sätze 2016'!$B$7:$E$245;2;FALSCH);)+WENN(M23="x";RUNDEN(SVERWEIS(D23;'Sätze 2016'!$B$7:$E$245;3;FALSCH)*$M$8;2);)+WENN(N23="x";RUNDEN(SVERWEIS(D23;'Sätze 2016'!$B$7:$E$245;3;FALSCH)*$N$8;2);)+WENN(O23="x";RUNDEN(SVERWEIS(D23;'Sätze 2016'!$B$7:$E$245;3;FALSCH)*$O$8;2);)+WENN(P23="x";SVERWEIS(D23;'Sätze 2016'!$B$7:$E$245;4;FALSCH);)

Die Eingabe des Landes würde ich über [url=http://www.online-excel.de/excel/singsel.php?f=120]Gültigkeit[/url] regeln, da bei einer falschen Eingabe die Formel einen Fehler ausweist. Das mit dem Eingeben und Ergänzen klappt aber leider so nicht.

Gruß

M.O.
0 Punkte
Beantwortet von paulemann Mitglied (107 Punkte)
Hallo M.O.


vielen Dank für deine schnelle und lange Formelhilfe !!!
Leider funktioniert sie noch nicht ganz wie gewollt...


Die Länder löse ich wie du vorgeschlagen hast über Gültigkeit, auch wenn ich sicher
alles abtippen muss, da ich nicht weiß wie man über eine Formel die Zellen vom
auslesen kann, damit man alle Namen nacheinander hat, getrennt durch ein Semikolon
und Leerzeichen... Aber das ist meine Baustelle...


Ich habe dir mal die richtigen Lösungen nebenan geschrieben, vielleicht erklärt es das
besser als in meinem ersten Versuch...
Wäre schön wenn du oder jemand aus der Gruppe nochmal drüber schauen könntest...


LG Paul


https://workupload.com/file/kN9xXQW


[url=https://workupload.com/file/kN9xXQW]https://workupload.com/file/kN9xXQW[/url]
0 Punkte
Beantwortet von
Hallo zusammen,

da ich mich zufällig mit dem Thema ein wenig auskenne, häng ich mich hier mal mit rein.

[quote](außer der Mitarbeiter hatte z.B. kein Frühstück, dann werden ihm von
den 24,00 20 % abgezogen - die entsprechenden Prozentzahlen stehen
oberhalb der Tabelle)...[/quote]
Diese Aussage ist falsch (oder unglücklich formuliert). Der Abzug erfolgt dann, wenn man ein Frühstück hatte, weil dieses bereits über den Hotelbeleg bezahlt wird, aber auch vom Tagessatz abgedeckt wird (Doppelzahlung!) Daher wird der Tagessatz um den entsprechenden Anteil gekürzt. Gleiches gilt für die 40% Abzüge von Mittag und Abendessen. Dabei ist auch das Nullwert-Prinzip zu beachten. Damit jemand, der an einem (An-)Reisetag 2 oder 3 Mahlzeiten hatte, nicht mehr abgezogen bekommt, als ihm an Verpflegung für diesen Tag zur Verfügung steht.

Verstehe ich dein Formular richtig, dass du ein Kreuzchen dann setzt, wenn man keinen Abzug möchte weil man kein Frühstück hatte? Das dürfte viele Nutzer verwirren. Logischer wäre der umgekehrte Weg: Kreuz bei Mahlzeit.

Ich hab dir mal eine [url=https://www.dropbox.com/s/2j3ded34uqwt1cw/RKForm.xls?dl=0]logische[/url] und eine Variante nach [url=https://www.dropbox.com/s/0ok41vnpgbw974d/RKForm_unlogisch.xls?dl=0]deinem Schema[/url] hochgeladen.

Übrigens: Wenn du doppelt in ein Länderfeld klickst, kannst du wie gewünscht die ersten Buchstaben eingeben und das Land wird automatisch vervollständigt.

Hausaufgaben:
Deine Tagessätze sind evtl. nicht aktuell. Das BMF veröffentlicht (fast) jedes Jahr im Dezember die [url=http://www.bundesfinanzministerium.de/Content/DE/Downloads/BMF_Schreiben/Steuerarten/Lohnsteuer/2016-12-14-steuerliche-behandlung-reisekosten-reisekostenverguetungen-2017.pdf?__blob=publicationFile&v=4]Auslandssätze[/url] für das jeweils nächste Jahr. Änderungen werden dabei fett gedruckt. Außerdem sollte man unbedingt [url=http://www.bundesfinanzministerium.de/Content/DE/Downloads/BMF_Schreiben/Steuerarten/Lohnsteuer/2014-10-24-ergaenztes-bmf-schreiben-reform-steuerliches-reisekostenrecht.pdf?__blob=publicationFile&v=4]dieses[/url] BMF-Schreiben kennen, wenn man steuerrechtlich auf der sicheren Seite sein will. Zugegeben - viel Text. Aber es lohnt sich. Vielleicht braucht man dann dafür auch keinen Steuerberater mehr. :-)

Gruß Mr. K.
0 Punkte
Beantwortet von paulemann Mitglied (107 Punkte)
Hallo XL King,


vielen vielen herzlichen Dank für deine lange Ausformulierung und den sensationellen
Lösungsansatz !!! Dafür ganz herzlichen Dank...

Ja ich habe mich wohl falsch ausgedrückt, so wie du es als logisch meintest, war mein
Gedanke. Das was ich hatte, da setze ich ein Kreuz, also wenn ich z.B. ganzen
Tagessatz bekomme, aber im Hotel Frühstück hatte, dann gehen halt vom Tagessatz
20 % weg, weil ich das nicht doppelt bekommen darf. Also setze ich da auch kein
Kreuz...

Nach dem ersten Drüberschauen und testen scheint deine "logische Variante genau
das zu sein, das ich gesucht habe... Dafür nochmals tausend Dank...

Ich habe nur ein kleines Problem...

Ich nutze "Microsoft Excel für Mac, Version 15.32."

Beim Öffnen deiner Dateien, zeigt er mir den Fehler:
"Die Arbeitsmappe enthält Inhalte die von dieser Excel Version nicht unterstützt
werden..."

Ich tippe, dass es mit der Länderübernahme zu tun hat... Könntest du mir die Version
ohne die Länderübernahme noch einmal per Dropbox hochladen, ich mache das mit
den Ländernamen dann einfach über Copy Paste aus dem letzten Reiter...
Also nur mit deiner tollen Formel zur Berechnung, aber ohne den Code oder die Formel
für die Ländernamen-Übernahme...


Nochmals tausend Dank XL King und auch M.O. für eure tolle Hilfe !!!



LG Paul
0 Punkte
Beantwortet von paulemann Mitglied (107 Punkte)
PS: @XL King

mir ist bewusst, dass sich die Sätze von Jahr zu Jahr ändern und ich passe diese an,
aber ich muss jetzt erstmal ein Sheet für 2016 erstellen und wenn das passt, dann
mache ich daraus eines für 2017 mit den neuen Sätzen...
Aber danke für die Info !!!
0 Punkte
Beantwortet von
Hallo Paul,

sorry, dass ich mich jetzt erst melde. Dropbox hat offenbar die Funktion "öffentlichen Link kopieren" [url=https://www.ifun.de/dropbox-der-oeffentliche-ordner-faellt-2017-weg-101710/]eingestellt[/url]. Hat a bissl gedauert bis ich das rausgefunden hab.

...und ich war so glücklich, dass ich dir deinen Herzenswunsch mit dem Autovervollständigen erfüllen konnte. Leider kenn ich mich mit Excel for Mac nicht wirklich aus. Aber du tippst ganz richtig. Die von mir verwendeten ActiveX-Komponenten sind ein reines Windows-Produkt und somit für den Mac nicht zu gebrauchen. Das sagt zumindest [url=https://de.wikipedia.org/wiki/ActiveX]Wikipedia[/url].
Nach einiger Recherche konnte ich leider keine einfache Lösung für das Problem finden. Also musst du leider doch die [url=https://workupload.com/file/tfzXcxs]klassische Version[/url] mit Gültigkeitsliste verwenden. Bei meinem alten Excel noch mit Umweg über den Namensmanager.

Auf [url=https://www.youtube.com/watch?v=8B79iifmLeM]Youtube[/url] gibts aber Videos, die zeigen, dass man ähnliche Comboboxen auch in deiner Version zumindest im Userform erstellen kann. Ob man beim Mac diese Art Combobox auch im Worksheet einfügen kann (z.B. über das Ribbon "Entwicklertools") musst du selbst rausfinden.

Falls ja, habe ich folgende Eigenschaften (Rechtsklick -> Eigenschaften) angepasst:
ListFillRange: 'Sätze 2016'!B7:B245
LinkedCell: z.B. D22 - die Zelle, welche von der Combobox verdeckt wird
MatchEntry: 1 - fmMatchEntryComplete
MatchRequired: True
PrintObject: False
SpecialEffect: 0 - fmSpecialEffectFlat

Gruß Mr. K
0 Punkte
Beantwortet von paulemann Mitglied (107 Punkte)
@ PS: @XL King


danke für die Info...
Ich löse es über die Gültigkeitsform...

Könntest du mir bitte die Datei nochmal ohne die ActiveX-Komponente hochladen,
sonst erscheint bei mir immer ein Warnfelkd beim Öffnen und ich kann nicht auf die
Zellen zugreifen...


Das wäre sehr nett, dann könnte ich die Datei final anpassen und verwenden...


nochmals vielen vielen Dank...



LG Paul
0 Punkte
Beantwortet von
Hallo Paul.

Hab ich doch gemacht. Siehe Link: "Klassische Version". Kommt bei dieser Datei immer noch ein Fehler?
0 Punkte
Beantwortet von paulemann Mitglied (107 Punkte)
Servus XL King,


sorry ich hatte den Link in deiner ausführlichen Ausformulierung übersehen...
Passt alles wunderbar...

Nochmals

TAUSEND DANK !!!!!!


LG Paul
...