Fachinformationen

Lohnsteuer

Steuerfreiheit "Job-Ticket" ab 2019

Begriff

Als Jobtickets werden Monats- oder Jahresfahrkarten bezeichnet, die Unternehmen bei einem Verkehrsbetrieb erwerben und entgeltlich oder unentgeltlich an ihre Mitarbeiter aus-geben. Das Jobticket berechtigt den Eigentümer dazu, öffentliche Verkehrsmittel innerhalb einer bestimmten Region oder Verkehrszone zu nutzen. Oft erhalten die Unternehmen besondere Konditionen vom Verkehrsbetrieb für ihre Mitarbeiter. Überlässt der Arbeitgeber dem Arbeitnehmer ein Jobticket, handelt es sich grundsätzlich um einen Sachbezug und damit um steuerpflichtigen Arbeitslohn bzw. sozialversiche-rungspflichtiges Arbeitsentgelt Zahlt der Arbeitgeber ab 2019 einen Zuschuss zum Jobticket oder übernimmt er die kom-pletten Kosten, ist der dadurch entstehende geldwerte Vorteil steuerfrei. Die 44-EUR-Grenze muss hier nicht beachtet werden. Der Arbeitgeber kann ab 1.1.2019 auch ein Jah-resticket bezuschussen oder einen jährlichen Einmalbetrag zahlen. Wichtig ist, dass die Leistungen des Arbeitgebers zusätzlich zum ohnehin geschuldeten Arbeitslohn erfolgen.

Gesetze, Vorschriften und Rechtsprechung

• Lohnsteuer: Ab 2019 ist die zusätzlich zum ohnehin geschuldeten Arbeitslohn erbrachte unentgeltliche oder verbilligte Gestellung eines Jobtickets nach § 3 Nr. 15 EStG steuerfrei. Bis 2018 und in den Fällen der Barlohnumwandlung auch in den nachfolgenden Jahren liegt steuerpflichtiger Arbeitslohn vor. In diesen Fällen gilt, dass Jobtickets grundsätzlich Sach-zuwendungen sind, die nach den Vorschriften des § 8 Abs. 2 EStG (44-EUR-Freigrenze) oder § 8 Abs. 3 EStG (Belegschaftsrabatt) zu bewerten sind. Zusätzliche Regelungen und Beispiele zur Bewertung des Sachbezugs Jobticket finden sich in R 8.1 LStR und H 8.1.LStH. Zur Bewertung und zum Zuflusszeitpunkt s. auch BFH, Urteil v. 14.11.2012, VI R 56/11, BFH/NV 2013 S. 628, BStBl 2013 II S. 382 und Bayerisches Landesamt für Steu-ern, Verfügung v. 12.8.2015, S 2334.2.1-98/5 St 32.

• Sozialversicherung: Die Beitragspflicht des Arbeitsentgelts in der Sozialversicherung ergibt sich aus § 14 Abs. 1 SGB IV. Die Beitragsfreiheit von lohnsteuerfreien (Sach-) Zu-wendungen ergibt sich aus § 1 Abs. 1 Satz 1 SvEV. Pauschalversteuerte Sachzuwendun-gen sind nach § 1 Abs. 1 Satz 1 Nr. 3 SvEV beitragsfrei. Die Beitragsfreiheit geldwerter Vorteile im Rahmen der Rabattregelungen für Mitarbeiter von Verkehrsunternehmen orien-tiert sich an den Regelungen des § 3 Abs. 1 Satz 4 SvEV. Zur Umwandlung laufender Lohnvereinbarungen enthält das BSG-Urteil v. 2.3.2010, B 12 R 5/09 R, entsprechende Hinweise.

Lohnsteuer - Steuerfreiheit nach §3 Nr. 15 EStG

Sachbezüge in Form der unentgeltlichen oder verbilligten Zurverfügungstellung von Fahr-ausweisen (Jobtickets), die mit Rücksicht auf das Dienstverhältnis erbracht werden, sind ab 1.1.2019 von der Steuer befreit. Voraussetzung ist allerdings, dass die Leistungen zu-sätzlich zum ohnehin geschuldeten Arbeitslohn erbracht werden. Aufgrund dessen greift die Steuerbefreiung nicht für Arbeitgeberleistungen, die durch (Barlohn-) Umwand-lung des ohnehin geschuldeten Arbeitslohns finanziert werden. In einem solchen Fall liegt weiterhin steuerpflichtiger Arbeitslohn vor.

Künftig werden zusätzlich zum ohnehin geschuldeten Arbeitslohn gewährte Zuschüsse und Sachbezüge des Arbeitgebers für die Nutzung öffentlicher Verkehrsmittel im Linienverkehr der Arbeitnehmerinnen und Arbeitnehmer zwischen Wohnung und erster Tätigkeits-stätte, zu einem weiträumigen Tätigkeitsgebiet (z. B. Forstgebiet) oder zu einem vom Arbeitgeber dauerhaft festgelegten Sammelpunkt (z. B. Busdepot oder Fährhafen) steuer-frei gestellt. Begünstigt ist der Linienverkehr, sofern er nicht den Luftverkehr betrifft. Ta-xis sind dadurch von der Steuerbefreiung ausdrücklich ausgenommen.

Die steuerfreien Leistungen werden auf die Entfernungspauschale angerechnet, um eine systemwidrige Überbegünstigung gegenüber denjenigen Arbeitnehmern zu verhindern, die die betreffenden Aufwendungen selbst aus ihrem versteuerten Einkommen bezahlen.

': Beispiel:'

Die Vollzeit beschäftigte Arbeitnehmerin S fährt 8 km zur Park & Ride-Station und von dort aus mit öffentlichen Verkehrsmitteln im Linienverkehr (Straßenbahn) zur ersten Tätig-keitsstätte (weitere 30 km). Die Kosten für die Monatskarte zur Nutzung der öffentlichen Verkehrsmittel betragen 75 EUR.

Der Arbeitgeber kann die Kosten für die Monatskarte nach Maßgabe von § 3 Nr. 15 EStG steuerfrei übernehmen. Einer Pauschalierung der Lohnsteuer bedarf es insoweit nicht (mehr).

Nach Maßgabe von § 40 Abs. 2 Satz 2 EStG kann in Höhe der als Werbungskosten abzieh-baren Entfernungspauschale eine Lohnsteuerpauschalierung mit 15 % erfolgen. Die Ent-fernungspauschale ist um die steuerfreie Arbeitgeberleistung nach § 3 Nr. 15 EStG zu mindern.

Berechnung
15 Fahrten (pauschal) x 38 Entfernungskilometer x 0,30 EUR =	171,00 EUR
abzüglich steuerfreie Arbeitgeberleistung:	-75,00 EUR
verbleiben:	96,00 EUR

Die steuerfreien Leistungen müssen vom Arbeitgeber in Zeile 17 der Lohnsteuerbe-scheinigung ausgewiesen werden; diese mindern den bei der Steuererklärung als Entfer-nungspauschale abziehbaren Betrag.

Hinweis:

Steuerfreies Jobticket gilt auch für private Fahrten im Personennahverkehr: Die ab 2019 geltende neue Steuerbefreiung bezieht auch private Fahrten im öffentlichen Nahverkehr in die Steuerbefreiung ein. Aufgrund dessen entfällt für den Arbeitgeber die Prüfung, ob das Jobticket auch für Privatfahrten oder aber auch von Familienangehörigen mitgenutzt wird. Diese geldwerten Vorteile fallen nicht mehr unter die monatliche Frei-grenze von 44 Euro.

Begünstigt werden Sachbezüge in Form der unentgeltlichen oder verbilligten Zurverfü-gungstellung von Fahrausweisen, Zuschüsse des Arbeitgebers zum Erwerb von Fahraus-weisen und Leistungen (Zuschüsse und Sachbezüge) Dritter, die mit Rücksicht auf das Dienstverhältnis erbracht werden.

Umsetzung in LohnAS

• Die Lohnart mit welcher der steuerfreie „Geldwerte Vorteil“ erfasst wird, muss mit der Kennziffer 22 = Steuer- und SV-freie Fahrtkosten Wohnung/Tätigkeitsstätte geschlüsselt sein.

• Diese Kennziffer steuert die Werte in die Zeile 17 der LStB ein

Meldewesen

Unfallversicherung - Sondermeldegründe

Aus gegebenem Anlass möchten wir hier noch einmal ein Thema aus dem Bereich der Unfallversicherung aufgreifen, welches uns auch innerhalb eines Jahres beschäftigen kann.

Neben dem Umlagelohnnachweis (UV-Meldegrund UV01), der nach abgeschlossener Dezemberabrechnung eines jeden Meldejahres abzugeben ist, gibt es sogenannte Sondermeldegründe für einen UV-Lohnnachweis.

Liegt ein entsprechender Anlass vor, so muss ein UV-Lohnnachweis auch unterjährig an den UV-Träger übermittelt werden.

Welche Sondermeldegründe gibt es? Aus welchem Anlass ist ein UV-Lohnnachweis mit einem Sondermeldegrund zu erstellen?

Meldegrund A05

• Einstellung eines Unternehmens

Vollständige Einstellung eines Unternehmens.

• Änderung der Zuständigkeit des Unfallversicherungsträgers

Änderung des Unternehmensgegenstandes eines Unternehmens, wenn der bisherige Unfallversicherungsträger für den neuen Gewerbezweig nicht mehr zuständig ist.

• Unternehmerwechsel / Wechsel der Rechtsform / Wechsel der Mitgliedsnummer

Wird aufgrund eines Unternehmerwechsels eine neue Mitgliedsnummer vergeben, endet die Mitgliedsnummer des bisherigen Unternehmens.

Der Lohnnachweis für das bisherige Unternehmen ist daher entsprechend der Betriebseinstellung mit Meldegrund UV05 abzugeben.

Auch bei einem Wechsel der Rechtsform – z.B. Umwandlung eines Einzelunternehmens in eine GmbH – wird im Regelfall eine neue Mitgliedsnummer vergeben.

Meldegrund A06

• Wegfall einer meldenden/abrechnenden Stelle
• Beendigung aller Beschäftigungsverhältnisse

Das Unternehmen wird nicht vollständig eingestellt, sondern lediglich ohne Beschäftigte fortgeführt.

Achtung - dies gilt nur, soweit absehbar ist, dass im selben Jahr keine Beschäftigten mehr eingestellt werden.

• Systemwechsel

Meldegrund A08

• Insolvenz

Nach Eröffnung eines Insolvenzverfahrens oder Abweisung des Antrags auf Eröffnung.

Handling in LohnAs Kanzleilohn

Fallbeispiel – vollständige Einstellung eines Unternehmens und Erstellung elektronischer Lohnnachweis mit Sondermeldegrund UV05

Das Unternehmen hat zu Beginn des Meldejahres die Stammdatenabfrage (DSAS) für das betroffene Meldejahr durchgeführt.

Die Rückmeldung des Unfallversicherungsträgers (DSSD) auf die Stammdatenabfrage liegt vor.

1. Mandantenstamm -> Adressdaten -> Registerkarte Allgemein -> Feld Betriebsaufgabe
   Im Feld Betriebsaufgabe zum, ist ein Datum zu hinterlegen.
2. Über Button Unfallversicherung (unter Feld Betriebsaufgabe zum) wird in die Angaben zur Unfallversicherung verlinkt.
3. Es ist unterjährig ein elektronischer Lohnnachweis (DSLN) mit Sondermeldegrund (UV05) mit der letzten Entgeltabrechnung, spätestens jedoch innerhalb von 6 Wochen nach Bekanntgabe des Bescheides über das Ende der Zuständigkeit, zu übermitteln.
   Unfallversicherung (Berufsgenossenschaft) -> Registerkarte DGUV-Verfahren Buchungsjahr -> Feld Meldegrund DSLN -> Auswahl Lohnnachweis Einstellung des Unternehmens/Änderung der formellen Zuständigkeit = Übersetzungstext UV05
4. Über Button UV-Hilfsliste, Liste Lohnnachweis -> Button UV-Hilfsliste 2.0 für das betreffende Meldejahr erstellen und prüfen.
5. UV-Liste Lohnnachweis erstellen.
6. Beitragsabrechnung-UV erstellen.
7. Registerkarte DGUV-Verfahren Buchungsjahr -> Button erstelle Datensatz elektronischer Lohnnachweis
8. Meldewesen -> Registerkarte Meldungen DGUV -> Button DGUV elekt. Lohnnachweis in den Meldespool sowie DGUV Protokoll elekt. Lohnnachweis
   
   

Fallbeispiel – unterjähriges Ausscheiden des letzten Mitarbeiters elektronischer Lohnnachweis mit Sondermeldegrund UV06

Ein Unternehmen betreibt eine Schreinerei. Der einzige Beschäftigte scheidet zum 30.06.des Meldejahres aus.

Das Unternehmen wird vom Inhaber – voraussichtlich dauerhaft – alleine weiter betrieben.
Das Unternehmen hat zu Beginn des Meldejahres die Stammdatenabfrage (DSAS) für das betroffene Meldejahr durchgeführt. Die Rückmeldung (DSSD) auf die Stammdatenabfrage liegt vor.

1. Personalverwaltung -> Im Feld Austritt ist ein Datum zu hinterlegen.
   Für das Feld Austrittsgrund ist eine Auswahl zu treffen.
2. Es ist unterjährig ein elektronischer Lohnnachweis (DSLN) mit Sondermeldegrund (UV06) mit der letzten Entgeltabrechnung -spätestens aber innerhalb von 6 Wochen- zu übermitteln.
   Meldewesen -> Registerkarte Meldungen DGUV -> Button MPD, elektronischer Lohnnachweis -> Registerkarte DGUV-Verfahren Buchungsjahr -> Feld Meldegrund DSLN -> Auswahl Lohnnachweis bei Beendigung einer meldenden Stelle = Übersetzungstext UV06
3. Über Button UV-Hilfsliste, Liste Lohnnachweis -> Button UV-Hilfsliste 2.0 für das betreffende Meldejahr erstellen und prüfen.
4. UV-Liste Lohnnachweis erstellen.
5. Beitragsabrechnung-UV erstellen.
6. Registerkarte DGUV-Verfahren Buchungsjahr -> Button erstelle Datensatz elektronisches Lohnnachweis
7. Meldewesen -> Registerkarte Meldungen DGUV -> Button DGUV elekt. Lohnnachweis in den Meldespool sowie DGUV Protokoll elekt. Lohnnachweis

Achtung – sollte nach Abgabe des elektronischen Lohnnachweises mit UV06 wider Erwarten ein uv-pflichtiger Arbeitnehmer für das betroffene Meldejahr beschäftigt werden, ist der

elektronische Lohnnachweis mit UV06 im Eintrittsmonat des Arbeitnehmers zu stornieren. Sie erstellen die Stornierung über den Button Stornierung einer gelieferten Datenmeldung elekt. Lohnnachweis auf der Registerkarte DGUV-Verfahren Buchungsjahr.
Nach der Dezemberabrechnung des Meldejahres ist ein elektronischer Lohnnachweis mit UV01 (Umlagelohnnachweis), wie bekannt, zu erstellen.

Fallbeispiel – Insolvenzverfahren elektronischer Lohnnachweis mit Sondermeldegrund UV08

Ein Unternehmen betreibt eine Schreinerei. Es wird ein Insolvenzverfahren eröffnet.

Das Unternehmen hat zu Beginn des Meldejahres die Stammdatenabfrage (DSAS) für das betroffene Meldejahr durchgeführt. Die Rückmeldung (DSSD) auf die Stammdatenabfrage liegt vor.

1. Mandantenstamm -> Insolvenz -> Im Feld Tag des Insolvenzereignisses ist ein Datum zu hinterlegen.
2. Die Frist für die Übermittlung des elektronischen Lohnnachweises (DSLN) mit Sondermeldegrund (UV08) beginnt mit dem Tag, der auf den Erlass des Beschlusses des Insolvenzgerichtes folgt.
   Meldewesen -> Registerkarte Meldungen DGUV -> Button MPD, elektronischer Lohnnachweis -> Registerkarte DGUV-Verfahren Buchungsjahr -> Feld Meldegrund DSLN -> Auswahl Lohnnachweis bei Insolvenz = Übersetzungstext UV08
3. Über Button UV-Hilfsliste, Liste Lohnnachweis -> Button UV-Hilfsliste 2.0 für das betreffende Meldejahr erstellen und prüfen.
4. UV-Liste Lohnnachweis erstellen.
5. Beitragsabrechnung-UV erstellen.
6. Registerkarte DGUV-Verfahren Buchungsjahr -> Button erstelle Datensatz elektronisches Lohnnachweis
7. Meldewesen -> Registerkarte Meldungen DGUV -> Button DGUV elekt. Lohnnachweis in den Meldespool sowie DGUV Protokoll elekt. Lohnnachweis

Öffentlicher Dienst

Berechnung der Jahressonderzahlung im November

Die Berechnung der Sonderzahlung im November wird durch die Konstanten und individuellen Einstellungen gesteuert. Im Tarifrecht des öffentlichen Dienstes bestehen 4 Grundmodelle (TVöD Bund, TVL, SuE und VKA), von denen im Rahmen von individuellen Vereinbarungen und Öffnungsklauseln abgewichen werden kann.:

Prüfung der Konstanten
Zunächst ermitteln Sie bitte die für Sie zutreffende tarifliche Regelung. In der Kanzlei sind die beiden Grundmodelle als Konstanten hinterlegt.

Die Jahressonderzahlung beträgt je nach Tarif (TVöD Bund, TVL, SuE und VKA) und Entgeltgruppe den angegebenen Prozentsatz vom Entgelt der Monate Juli, August und September des laufenden Jahres.

Die Jahressonderzahlung bei Beschäftigungsbeginn nach dem Juli bzw. bei nicht vorhandenen Entgelten im Bemessungszeitraum Juli / August / September wird programmseitig automatisch ein Ersatzbemessungsentgelt ermittelt. Besteht bei Ihnen eine abweichende Regelung z. B. ein Prozentsatz für alle Entgeltgruppen, können diese hier verändert werden (z. B. AVR EKD).

Hinweis:

BITTE BEACHTEN SIE, DASS SICH DIE ÄNDERUNGEN AUF ALLE MANDANTEN AUSWIRKEN! Individuell geänderte Konstanten werden im Rahmen von Updates auf die Standardwerte zurückgesetzt und müssen nach einem Update erneut erfasst werden!

Einstellungen im Personalstamm
Wählen Sie im Personalstamm > Register ÖD und klicken Sie auf „Jahressonderzahlung ermitteln“.

1. In der Schaltfläche wird angezeigt, wenn für den MA ein Tarif aus dem Tarifgebiet Ost im Personalstamm > Entgeltabellen hinterlegt ist.

2. Bitte wählen Sie einen Tarifvertrag, nach dem die Berechnung erfolgen soll. Mit der Schaltfläche „Einstellungen kopieren“ wird die Einstellung die ausgewählten Mitarbeiter übernommen.

3. Für die Jahressonderzahlung werden grundsätzlich die Monate Juli, August und September als Bemessungszeitraum zugrunde gelegt. Wählen Sie einen oder mehrere Durchschnittsspeicher die für die Berechnung verwendet werden sollen. Über die Schaltfläche „Durschnittspeicher“ können diese nochmal geprüft und ggf. angepasst werden. Mit der Schaltfläche „Einstellungen kopieren“ wird die ausgewählten Mitarbeiter übernommen. Falls die Durchschnittsspeicher nicht verwendet werden sollen, kann die Berechnung auch nach Lohngruppen aus einem bestimmten Monat erfolgen. Hierzu muss eine Lohngruppe im Reiter „Zusätzliche Einstellungen“ eingerichtet werden.

Geben Sie den Referenzmonat und die Variante der Lohngruppe (In der Regel die 1) an und klicken Sie auf „Lohngruppe zuordnen“. Es öffnet sich die folgende Eingabemaske.

Sonstiges

Tabellen in Excel mithilfe des SVERWEIS miteinander in Verbindung setzen

Sie haben Daten, die in Beziehung zueinanderstehen, auf unterschiedlichen Tabellenblättern in Excel (oder gar in unterschiedlichen Exceldateien) gespeichert. Nun möchten Sie sich die Pflege der Tabellen vereinfachen. Beispielsweise durch die Nutzung von Kundennummern automatisch den Kundennamen einfügen lassen. Dafür nutzen wir eine Funktion in Excel von der Sie womöglich sogar schon gehört haben. Diese Funktion nennt sich „SVERWEIS“.

Ausgangssituation:
Die haben eine Exceltabelle (oder Exceldatei), in der Sie Ihre Kunden aufgeführt haben. Eine Zweite, in der Sie beispielsweise Kundenaufträge fortlaufend eintragen. Anstatt nun immer wieder den Kundennamen neu einzutippen, ist es doch angenehm, nur die Kundennummer selber und den Namen von Excel automatisch eintragen zu lassen. Dies verhindert auch Tippfehler, die bei weiterführenden Excelfunktionen zu Problemen führen können.

Vorbereitung:
Ihre Quelltabelle sieht ungefähr so aus.

Wichtig dabei ist, dass die Kundennummer (bzw. die Spalte, mit der die Werte später verglichen werden sollen) links steht. Dazu gehen wir später nochmal bei der Erklärung der Matrix genauer ein.

1. Die Tabelle als Tabelle formatieren
Falls Ihre Tabelle noch nicht als Tabelle formatiert ist, sollten Sie dies jetzt tun. Dafür klicken Sie oben auf den Reiter „Einfügen“ und anschließend auf das Symbol für „Tabelle“. In der Regel erkennt und markiert Excel automatisch den Tabellenbereich inklusive Überschriften. Sie müssen nur noch prüfen, ob Excel dies korrekt erkannt hat und können bestätigen. Hat Excel den Tabellenbereich nicht korrekt erkannt, so können sie diesen manuell festlegen. Markieren Sie dabei die Überschriften mit, achten aber bitte beim Bestätigen darauf, dass das entsprechende Häkchen gesetzt ist.

Klicken Sie auf „OK“. Ihnen wird auffallen, dass die Tabelle nun anders aussieht.

Sie können das Aussehen noch anpassen, wenn Sie mit der Maus in die Tabelle klicken. Es taucht oben über der Menüleiste ein neuer Reiter namens „Tabellentools Entwurf“ auf. Klicken Sie dort und suchen Sie rechts aus den Formatvorlagen eine für Sie passende aus. Ihre Quelltabelle ist nun fertig formatiert. Dies ist wichtig, denn wenn Sie neue Einträge einfügen, werden diese nun automatisch als zur Tabelle zugehörig erkannt und der Tabellenbereich entsprechend angepasst. Besonders für eine Langzeitnutzung ist dies sinnvoll, für einmalige Projekte ist die Tabellenformatierung tatsächlich nicht zwingend erforderlich.

2. Platz schaffen in der Zieltabelle
Womöglich möchten Sie eine bereits bestehende Tabelle ausbauen. Falls Sie nicht wissen wie Sie eine leere neue Spalte hinzufügen und ihre neuen Einträge nicht am Ende der Tabelle haben möchten, schauen wir uns dies jetzt kurz an.
Ausgangstabelle:

Wir hätten gern die Namen der Kunden in Spalte B, dort steht allerdings schon der Firmensitz. Bei einer kleinen Tabelle kann man noch recht einfach ausschneiden und kopieren. Bei größeren Tabellen endet dies allerdings schnell in einem unübersichtlichen Scrollchaos. Klicken Sie mit der rechten Maustaste in der Nähe des Buchstabens „B“ der Spalte B und wählen in dem sich öffnenden Menü „Zellen einfügen“. Nun haben Sie Platz für die neuen Einträge.

3. Formatierungen prüfen
Nun müssen wir noch prüfen, ob die Kundennummer in der Ziel- und in der Quelltabelle die gleiche Formatierung haben. Ob dies nun als Zahl, Text oder Standard formatiert ist, ist für unsere Zwecke nicht wichtig. Jedoch muss es in beiden Tabellen gleich sein, sonst kann es später zu Fehlern kommen.

Klicken Sie die die Felder, in der die Kundennummern eingetragen sind, an und prüfen Sie die Formatierung. Wo Sie die Information finden, können Sie dem Bild entnehmen. Sie können, um sicher zu gehen auch die komplette Spalte markieren (im Beispiel würden Sie dabei auf das A der „Spalte A“ links klicken) und die Formatierung im angezeigten Feld manuell auf Text, Zahl oder Standard setzen.

4. Bemerkung zur Vorbereitung
Alle Tabellen sind nun in einem Zustand, mit dem Sie gut weiterarbeiten können, ohne dass Sie sich zwischenzeitlich mit Formatierungen beschäftigen müssen. Mit dem Abgleich der Formatierungen aus Schritt 3 haben Sie auch schon vorab eine der häufigsten Fehlerquellen ausgeschlossen. Es wurde nur die Quelltabelle als Tabelle formatiert und nicht die Zieldatei. Dies können Sie natürlich tun, ist aber für das Beispiel nicht relevant.

Der SVERWEIS, so funktioniert es!

Es gibt 2 Möglichkeiten, Formeln in Excel einzugeben. Wir gehen hier den einfachen Weg über die von Excel bereitgestellte Hilfsmaske.

Hinweis:

Geübte Anwender können das Formelschema auch per Hand direkt in die Eingabezeile eingeben. Dies würde dann wie folgt aussehen: „=SVERWEIS(A5;Tabelle1[#Alle];2;0)“

1. Aufrufen der Formelmaske
Klicken Sie in das Feld, in welchem das Ergebnis stehen soll (im Beispiel ist dies Feld B4) und dann auf „fx“.

Stellen Sie sicher, dass im sich nun öffnenden Fenster unter Kategorie „Alle“ steht oder wählen Sie es über den Pfeil rechts am Feld aus. Drücken Sie nun die Tasten „S“ und „V“ auf Ihrer Tastatur kurz hintereinander, nun sollte der SVERWEIS bereits blau hinterlegt (ausgewählt) sein. Ist dies der Fall, drücken Sie auf „OK“. Ansonsten suchen Sie den Eintrag SVERWEIS aus der Liste, wählen diesen aus (Linksklick) und klicken dann auf „OK“.

Nun öffnet sich folgendes Fenster:

Hinweis:

Begriffserklärung:

• Suchkriterium: das Suchkriterium ist das Feld in unserer Zieltabelle, welches mit den Daten in der Quelldatei verglichen wird (im Beispiel die Kundennummern der Zieltabelle)
• Matrix: die Matrix (auch Suchmatrix) beschreibt den Bereich, in der Quelltabelle in dem nach einem passenden Wert zum Suchkriterium gesucht wird (im Beispiel die Tabelle die wir auch als Tabelle formatiert haben / die Quelltabelle)
• Spaltenindex: der Spaltenindex ist der Wert mit dem Sie Excel mitteilen aus welcher Spalte innerhalb der Matrix der Eintrag übernommen werden soll (im Beispiel die Kundennamen der Quelltabelle)
• Bereich Verweis: der Bereich Verweis ist der Wert, mit dem Sie Excel mitteilen, wie genau das Suchkriterium mit den in der Matrix vorhandenen Werten übereinstimmen soll

0=genaue Übereinstimmung

1=Wert der am dichtesten am Suchkriterium liegt

  2. Ausfüllen der Formelmaske
Klicken Sie mit der Maus in das weiße Feld hinter Suchkriterium, damit teilen Sie Excel mit welchen Wert Sie nun angeben möchten. Klicken Sie nun auf das Feld A4 oder ein in Ihrer Tabelle vergleichbares Feld oder geben die entsprechende Feldbezeichnung über Tastatur in das Feld Suchkriterium ein.

Hinweis:

Eingabemöglichkeiten: Sie können nun entweder über die Tastatureingabe das Feld eingeben (im Beispiel wäre dies A4) oder Sie klicken das Feld einfach mit der Maus an (die Eingabemaske kann verschoben werden wie alle Fenster in Windows, sollte Sie sich vor dem Feld befinden). Die dritte Möglichkeit ist die auf den Pfeil am Ende des Eingabefeldes zu klicken. Dabei öffnet sich ein neues kleines Eingabefeld, welches ebenfalls über Tastatur oder Mausklick gefüllt werden kann. Man hat jedoch einen besseren Überblick über die gesamte Tabelle. Klickt man in diesem Feld wieder rechts am Rand auf das Symbol, wechselt man wieder in die große Eingabemaske und die Einträge werden übernommen.

Klicken Sie nun in das weiße Feld hinter Matrix, wechseln Sie das Tabellenblatt auf das in dem Ihre Quelltabelle enthalten ist und markieren Sie die komplette Tabelle inkl. Überschriften.

Hinweis:

Tipp: Ist Ihre Tabelle sehr umfangreich klicken Sie mit der Maus in das erste linke Feld und drücken dann auf Ihrer Tastatur die Großschreibtaste und gleichzeitig die Taste „Strg“, halten Sie beide Tasten gedrückt und steuern sie nun mit den Pfeiltasten ihren endgültigen Markierungsbereich an.

Im Bereich Matrix sollte nun folgendes stehen: Tabelle1[#Alle]. Es kann sein, das „Tabelle1“ bei Ihnen mit einem individuellen Namen angegeben ist. Dies ist der Fall, wenn Sie ihrer Tabelle eine Überschrift gegeben haben. Das „[#Alle]“ bedeutet, dass der komplette Tabellenbereich als Matrix angesehen wird. Kommen also irgendwann neue Einträge in der Quelltabelle hinzu, werden diese ebenfalls erfasst und durchsucht. Die Quelltabelle kann sich auch in einer separaten Exceldatei befinden. Die Markierung kann dennoch vorgenommen werden wie hier beschrieben, der Dateipfad wird dann von Excel automatisch zusätzlich hinterlegt.

Wichtig: Die Spalte, in der die Vergleichswerte gesucht werden, befindet sich ganz links in der Matrix. Rechts davon befinden sich die Spalten, aus denen die Daten gezogen und in die Zieltabelle eingetragen werden sollen. Befände sich die Spalte mit den Vergleichswerten (im Beispiel die Kundennummern) rechts von den Daten, die übernommen werden sollen, funktioniert der SVERWEIS nicht und es kommt am Ende eine Fehlermeldung.

Im Bereich Spaltenindex tippen Sie über die Tastatur die Zahl 2 ein (diese Zahl ist für unser Beispiel gültig und kann bei Ihrem Projekt abweichen).

Hinweis:

Erklärung: Die Matrix im Beispiel besteht aus 2 Spalten. In der ersten Spalte steht der Wert nach dem gesucht wird (das muss auch so sein), in der zweiten Spalte die dazu gehörigen Namen. Daraus ergibt sich für das Beispiel: zweite Spalte soll ausgegeben werden -> Wert muss 2 sein. Sie können auch auf Spalte 3, 4, 5 usw. zugreifen, Sie müssen dies Excel nur mitteilen und zwar mit diesem Wert.

Im Bereich_Verweis tippen Sie eine 0 (Null) ein. Wir wollen nur genaue Übereinstimmungen. Es gibt auch Fälle, in denen ein Näherungswert sinnvoll sein kann. Im Beispiel und vermutlich in den meisten ähnlichen Anwendungsgebieten ist die 0 jedoch der Wert, der an dieser Stelle sinnvoller ist. Nun sind alle Felder ausgefüllt und Sie klicken auf „OK“. Die Formel ist nun in dem anfangs ausgewählten Feld und sollte ein Ergebnis zeigen.

3. Fertige Formel, und weiter?
Um dies jetzt nicht für jede Zeile einzeln wiederholen zu müssen nutzen wir einfach eine simple aber sehr nützliche Funktion von Excel und Ziehen die Formel runter.

Markieren Sie das Feld mit der Formel (Linksklick) und klicken Sie dann das kleine grüne Viereck in der rechten unteren Ecke des markierten Feldes an, halten Sie die linke Maustaste gedrückt und fahren mit der Maus nach unten so weit wie Sie die Formel benötigen. Sobald Sie loslassen, werden alle Zellen bis zu dem Punkt mit der Formel ausgefüllt. Zellenbezüge werden automatisch in Relation angepasst sofern diese nicht festgesetzt sind.