Unterlagen pc.base - EDV-Schulung BruneckEXCEL V-1 Richard Kammerer

LohnberechnungLohnberechnung

Dieses komplexe Übungsbeispiel hat die Lohnberechnung für Handelsangestellte

zum Inhalt. Die Daten beziehen sich auf den Kollektivvertrag. Den Kern bildet der Lohnstreifen selbst. Daneben benötigen wir eine ganze Reihe von Tabellen und Datenbanken, aus denen Daten importiert werden. Zur Motivation: Sehr wahrscheinlich haben Sie

nichts mit Lohnberechnungen zu tun, bzw. besitzen bereits ein professionelles Lohnprogramm. Das Beispiel eignet sich aber hervorragend, um Ihnen Makrofunktionen, relative und absolute Makrobezüge, Schleifen, eigene Symbole und Menüs, eigene Symbolleisten und Menüzeilen so nahezubringen, dass Sie die Funktionen mühelos auf Ihre Anwendungen übertragen können.

EINGABE ERGEBNIS

1. SCHRITTDer Lohnstreifen

Wir benötigen eine Arbeismappe mit 7 Tabellen. In die Tabelle2 kommt der Lohnstreifen.

Arbeitgeberdaten

Ausstellungsdatum =heute()

Name, Eintrittsdatum und Kategorie übernehmen wir aus der PersonaldatenbankIn der Regel werden 26 Tage gerechnet. Falls jemand nur 10 Tage arbeiten würde, müsste man den Grundlohn durch 26 dividieren und mit 10 multiplizieren.Den Grundlohn übernimmt man aus der Tabelle der Tarife, ebenso die Kontingenz. Die Dienstalterszulage berechnet man auf Grund der entsprechenden Tabelle, wobei das Dienstalter die Höhe bestimmt.Die Summe der Lohnelemente bildet den Bruttolohn. Der Sozialpflichtige Lohn wird auf 1 € kaufm. gerundet.

Die INPS berechnet man vom gerundeten Betrag und zählt sie vom Bruttolohn weg. Dies ergibt die Steuergrundlage.

Berechnung der Bruttosteuer (IRPEF) wird später erklärt.Die regionale Zusatzsteuer und die Gemeindezusatzsteuer wird von der Steuergrundlage berechnet. Der Prozentsatz ist regions- und gemeindeabhängig.Die Freibeträge übernehmen wir aus den Tabellen der Freibeträge.

Die Nettosteuer ergibt sich aus der Summe der drei Steuern abzüglich der Freibeträge. Steuergrundlage minus Nettosteuer ergibt den Nettolohn.

Unterlagen pc.base - EDV-Schulung BruneckEXCEL V-2 Richard Kammerer

EINGABE ERGEBNIS

II. SCHRITTWeitere Tabellen

Gib in die Tabelle3 die nebenstehenden Daten ein.

Doppelklicke auf das Register der Tabelle und benenne sie Tarife.

Gib in die Tabelle4 die nebenstehende Tabelle ein.

Doppelklicke auf das Register der Tabelle und benenne sie DAZ.

Gib in die Tabelle5 die nebenstehende Tabelle ein.

Doppelklicke auf das Register der Tabelle und benenne sie IRPEF.

Gib in die Tabelle6 die nebenstehende Tabelle ein.

Doppelklicke auf das Register der Tabelle und benenne sie Ehepartner.

Gib in die Tabelle6 die nebenstehende Tabelle ein.

Doppelklicke auf das Register der Tabelle und benenne sie Kinder.

Unterlagen pc.base - EDV-Schulung BruneckEXCEL V-3 Richard Kammerer

EINGABE ERGEBNIS

III. SCHRITTBereiche benennen

Die letzte Tabelle beinhaltet das Personal s. Abb.

Wir haben nun folgende Tabellen:Lohnstreifen, Tarife, DAZ, IRPEF, Ehepartner, Kinder, Personal.

Die Tabelle1 nennen wir Titelblatt.

Markiere im Blatt Personal die gesamte Datenbank mit <Strg>+<*>

Gib den Befehl Einfügen/Name Erstellen Oberste Zeile.

Markiere im Blatt Tarife die gesamte Datenbank und gib den Befehl Einfügen/Name Definieren. Als Name geben wir Tarife und klicken auf Ok

Markiere im Blatt DAZ die gesamte Datenbank und gib ihr den Namen DAZ. Ebenso im Blatt Kinder - Name Kinder.

Jede Spalte erhält als Namen die Spalten-überschrift. Im linken Feld in der Bearbeitungszeile können sie angewählt werden. So können wir mit der Funktion index auf die Daten zugreifen.

Wir werden später mit der Funktion sverweis auf diese Matrix (Tabelle) zugreifen.

IV. SCHRITTFormeln in der Tabelle IRPEF

Wähle die Tabelle IRPEF.Klicke in die Zelle D5 und gib folgende Formel ein: =runden(B5*C5%;2)

Die Formel in D6 wird ebenfalls auf zwei Kom-mastellen gerundet: =runden((B6-A6)*C6%;2)Fülle die Zellen darunter mit der Formel aus.

Klicke in die Zelle E5 und schreibe: =D5In die Zelle E6 schreibe =E5+D6Fülle die Zellen darunter mit dieser Formel aus.

Unterlagen pc.base - EDV-Schulung BruneckEXCEL V-4 Richard Kammerer

V. SCHRITTDialogfeld

EINGABE ERGEBNIS

Die Lohnempfänger sollen über eine Dialogbox ausgewählt werden können. Das erfüllt auch professionelle Ansprüche.

Klicke mit der rechten Maustaste auf die Blattregister und wähle Einfügen/Microsoft Excel5-Dialog.

Klicke in der Symbolleiste Dialog auf das Symbol Listenfeld und ziehe ein Listenfeld auf.

Doppelklicke auf das Listenfeld. Im erscheinenden Dialog gibst du als Eingabebereich Name ein. Bei der Zellverknüpfung klicken wir auf das Symbol neben dem Eingabefeld um danach auf die Zelle G4 im Lohnstreifen zu knüpfen.

Hier ist etwas Erklärung nötig. Als Eingabebe-reich haben wir den Bereich Name bestimmt. Diesen Bereich haben wir im III. Schritt benannt. Als Zellverknüpfung bestimmen wir die Zelle G4 in der Tabelle Lohnstreifen. Diese Zelle wird die Indexzahl beinhalten. Später werden wir die Spalte G, die einige Zwischenrechnungen aufnehmen soll, ausblenden.

Verbessere das Layout der Dialogbox (s. Abb.).

Klicke auf das Symbol um den Dialog zu testen. Beobachte die Zelle G4 im Lohnstreifen.

Unterlagen pc.base - EDV-Schulung BruneckEXCEL V-5 Richard Kammerer

VI. SCHRITTFunktion index

EINGABE ERGEBNIS

Klicke in der Tabelle Lohnstreifen in die Zelle C3 und gib das Datum ein =heute()

In der Zelle C4 soll der Name des Lohnempfängers stehen =index(Name;G4)

In der Zelle C5 soll das Eintrittsdatum stehen=index(Eintrittsdatum;G4)

Auf die selbe Art und Weise wird die Lohnkate-gorie eingefügt.

Teste den Ablauf, indem du im Blatt Dialog1 auf das Symbol Dialog ausführen klickst und andere Personen auswählst. Kontrolliere, ob die importierten Daten stimmen.

Gönn dir eine Pause!!!

Syntax: =index(Spaltenbereich;Indexzahl)d. h. suche im Bereich Name jene Position, die in G4 angegeben ist.

Sucht im Bereich Eintrittsdatum nach dem Eintrag, der als Indexzahl in G4 steht. Unter Umständen muss erst das Datumsformat eingestellt werden, da Excel das Datum als serielle Zahl darstellt.

VII. SCHRITTFunktion sverweis

Klicke in die Zelle C10. Hier soll der Grundlohn hinein: =sverweis(C6;Tarife;3;falsch)

Analog dazu lautet die Formel für die Kontingenz: =sverweis(C6;Tarife;4;falsch)

Und jene für das 3. Lohnelement?

Syntax: =sverweis(Suchkriterium;Matrix;Spaltenzahl)d. h. Schaut zunächst nach dem Suchkriterium in der Zelle C6, vergleicht es mit der ersten Spalte des Bereichs Tarife und liefert den Inhalt der gewünschten Spalte zurück. Durch das Argument falsch erscheint beim Nicht-Übereinstimmung eine Fehlermeldung.

Unterlagen pc.base - EDV-Schulung BruneckEXCEL V-6 Richard Kammerer

EINGABE ERGEBNISVIII. SCHRITTDienstalterszulage

Wir importieren zunächst die DAZ in die Zelle G12 =sverweis(C6;DAZ;3;falsch)

Die Formel in der Zelle B12: =ABRUNDEN((C3-C5)/365/3;0) ermittelt die Anzahl der Dienstalterszulagen.

In die Zelle C12 multipliziert man die Dienstalters-zulage aus G12 mit B12 (Anzahl) =G12*B12

Den Bruttolohn ergibt die Formel:=summe(C9:C12)

und den steuerpflichtigen Lohn:=runden(C14;0)

somit kommt in C17 =runden(C15*B17;2)und in die Zelle C18: =C14-C17

Die Dienstalterszulage hängt von der Lohnkate-gorie und vom Dienstalter ab. sverweis importiert den Betrag aus der Matrix DAZ.

Zerlegen wir diese Abrunden-Formel: C3-C5 berechnet das Dienstalter in Tagen. (C3-C5)/365 liefert die Dienstjahre. Nachdem man aber nur alle drei Jahre eine Dienstalterszulage dazu bekommt, muss durch drei dividiert werden (C3-C5)/365/3. Das Abrunden bewirkt schließlich dass nur eine Ganzzahl als Multiplikator herauskommt.

Ganz genau genommen, dürfte die Dienstalters-zulage max. 5-mal ausbezahlt werden. Ein Angestellter der beispielsweise 25 Jahre arbeitet, bekommt gleich viel Dienstalterszulage wie einer mit 15 Dienstjahren.

Die Sozialabgaben (INPS) werden von dem auf den € gerundeten Betrag errechnet, jedoch vom Bruttobetrag subtrahiert.

Unterlagen pc.base - EDV-Schulung BruneckEXCEL V-7 Richard Kammerer

EINGABE ERGEBNISVIII. SCHRITT

Bruttosteuer

Für den Import der Bruttosteuer (IRPEF) werden wir uns eines Makros bedienen.

Zunächst holen wir die Steuergrundlage in die Tabelle IRPEF.

Klicke in der Tabelle IRPEF in die Zelle B2, schreibe ein = und wähle aus der Tabelle Lohnstreifen C18.

Gib dieser Zelle B2 den Namen Steuergrundlage (Einfügen/Name Definieren).

Zur Erklärung: Steidl fällt mit 1456,05 € in die Kategorie 1291,15 - 2582,28, d. h. sie zahlt 258,32 und für die Differenz bis zur nächsthöheren Kategorie 32 %. Ganz schön kompliziert?!

Bei Steidl beträgt sie 1456,05 €.

IX. SCHRITTMakro mit Schleife

Klicke in die Zelle B5.Gib den Befehl Extras/Makro Aufzeichnen.Der Name des Makros ist Suche. Als Tastenkom-bination bestimmen wir ein a. Kontrolliere noch, ob das Makro in dieser Arbeitsmappe aufgezeichnet wird. Bestätige mit ok.

Es erscheint eine interaktive Symbolleiste: Aufzeichnung beenden. Sie hat das Symbol zu beenden der Aufzeichnung und zum Wechseln zwischen relativem und absolutem Verweis. Klicke auf Relativen Verweis.

Drücke den Pfeil nach unten und beende die Makroaufzeichnung.

Teste das Makro mit <Strg>+a.

Sehen wir uns das Makro an: Extras/Makro Makros Suche Bearbeiten (s. Abb.).

Wir lassen die Auswahl eine Zelle nach unten springen; später muss dafür die Bedingung gegeben sein, dass die aktive Zelle größer ist, als die Steuergrundlage.

Unterlagen pc.base - EDV-Schulung BruneckEXCEL V-8 Richard Kammerer

EINGABE ERGEBNIS

X. SCHRITTRelative Makroaufzeichnung

Füge dem Makro nach dem Makronamen Do Until ActiveCell >= Range("B2")und vor End Sub Loop hinzu.

Klicke im Blatt IRPEF die Zelle B5 an und teste das Makro. Die Auswahl bleibt in der 3. Zeile stehen.

Wir befinden uns in der Zelle B7 der IRPEF-Tabelle und beabsichtigen die Berechnung der Steuer in der Spalte F vorzunehmen.

Gib den Befehl Extras/Makro Aufzeichnen. Als Makroname passt Berechnung. Als Tasten-kombination <Strg>+b. Achte dass das Symbol Relativer Verweis hineingedrückt ist.

Klicke in die Zelle F7. Schreibe =(Wähle Bearbeiten/Gehezu Steuergrundlage. Tippe ein Minuszeichen.Klicke auf A7 und schließe die Klammer.Schreibe * klicke auf C7 und tippe %Schreibe + klicke auf E6 und <Return>-Taste

Die errechnete Bruttosteuer muss jetzt in den Lohnstreifen übertragen werden.

Klicke im IRPEF-Blatt in die Zelle F10 und bilde die Summe von F5 bis F9.

Klicke in den Lohnstreifen und schreibe in die Zelle C19 ein =Klicke im IRPEF-Blatt in die Zelle F10 und drücke die <Return>-Taste.

Durch den relativen Verweis werden im Makro nicht die Zellen aufgezeichnet, (A7 usw.) sondern nur die Abstände zur Ausgangsposition.

Unterlagen pc.base - EDV-Schulung BruneckEXCEL V-9 Richard Kammerer

EINGABE ERGEBNISXI. SCHRITT

Absolute Makroaufzeichnung

Unsere Makros haben nur in der Tabelle IRPEF einen Sinn. In allen anderen Tabellen richten sie nur Schaden an. Daher müssen wir noch aufzeichnen, wo die Makros beginnen sollen.

Klicke in die Tabelle Lohnstreifen.

Extras/Makro Aufzeichnen.Makroname Bruttosteuer Tastenkombination <Strg>+c, Relativer Verweis evtl. ausschalten.

Klicke auf das Blatt IRPEF.Markiere die Zellen F5 bis F9.Lösche die Inhalte mit der <Entf>-Taste.Klicke in die Zelle B5.Beende die Makroaufzeichnung.

Klicke in den Lohnstreifen und teste die Makros mit <Strg>+c, <Strg>+a, <Strg>+b

Mit dem nächsten Makro springen wir wieder zum Lohnstreifen.

Extras/Makro AufzeichnenName ZumLohnstreifen Short-Cut <Strg>+dKlicke auf den Lohnstreifen.Beende die Makroaufzeichnung.

XII. SCHRITTMakros zusammenfassen

Wir zeichnen nun ein Makro auf, das die Makros zusammenfasst.

Extras/Makro AufzeichnenName ZusammenBrutto Short-Cut <Strg>+e<Strg>+c, <Strg>+a, <Strg>+b, <Strg>+d Beende die Makroaufzeichnung.

Die regionale Zusatzsteuer und Gemeindezusatz-steuer werden aus der Steuergrundlage berechnet und kaufm. auf zwei Kommastellen gerundet.

Unterlagen pc.base - EDV-Schulung BruneckEXCEL V-10 Richard Kammerer

EINGABE ERGEBNISXIII. SCHRITT

Freibeträge

Informieren wir uns zunächst einmal darüber, ob der Lohnempfänger einen Ehepartner zu Lasten hat.

Schreibe in der Tabelle Lohnstreifen in die Zelle G25: =INDEX(Ehepartner_zu_Lasten;G4).

Markiere in der Tabelle Ehepartner die Datenbank und gib ihr den Namen Ehepartner (siehe III. SCHRITT).

Schreibe in die Zelle C25:=WENN(G25=“ja“;SVERWEIS(C18;Ehepartner;3);0)

Auch bei "Kinder zu Lasten holen wir mit der INDEX-Funktion zunächst die Anzahl der Kinder in die G-Spalte.Schreibe in G25: =INDEX(Kind;G4)

Mit der SVERWEIS-Funktion importiert man nun den Freibetrag.

Markiere den Datenbankbereich der Tabelle Kinder und benenne ihn Kinder.

Schreibe in die Zelle C26:=WENN(G26>0;SVERWEIS(G26;Kinder;2);0)

Finale: Die Nettosteuer wird berechnet aus der Addition der drei Steuern IRPEF, regionale und Gemein-dezusatzsteuer und der Subtraktion evtl. Freibeträge.

Der Nettolohn ergibt sich aus: Steuergrundlage minus Nettosteuer.

Zur Erklärung: Wenn G25 ja ist, vergleicht Excel mit der SVERWEIS-Funktion die Steuergrundlage aus C18 mit der 1. Spalte der Matrix Ehepartner und liefert die 3. Spalte zurück. SVERWEIS funktioniert in diesem Fall nur ohne Argument "falsch". Excel sucht bei Nichtübereinstimmungen zwischen Suchkriterium und erster Spalte immer die kleinere Zahl. Was wollen wir mehr?

Unterlagen pc.base - EDV-Schulung BruneckEXCEL V-11 Richard Kammerer

EINGABE ERGEBNISXIV. SCHRITTBenutzerführung

Es gibt jetzt mehrere Möglichkeiten, den Benutzer durch die Anwendung zu führen: mittels Schaltflächen, über ein Symbol in einer der Standardsymbolleisten oder in einer eigenen Symbolleiste oder über einen neuen Menüpunkt.

XV. SCHRITTSchaltfläche im Titelblatt

Erinnerst du dich noch an das Dialogfeld, das wir im Schritt V erstellt haben?

Doppelklicke auf das Register der Tabelle1 und benenne sie Titelblatt. Ok

Extras/Makro Aufzeichnen - Name Druck-Lohnstreifen, Tastenkombination <Strg>+f.Klicke auf Dialog1 und auf das Symbol Dialog ausführen. Wähle einen Lohnempfänger. Klicke in den Lohnstreifen und gib die Tastenkombination zum Berechnen der IRPEF <Strg>+e.Klicke auf Datei/Seitenansicht.Klicke zurück auf das Titelblatt.

Mit dem Befehl Extras/Makro Makros Beginn Bearbeiten wird das aufgezeichnete Makro sichtbar (s. Abb.).

Teste das Makro aus dem Titelblatt heraus.

Lösche aus dem VBA-Modul die Zeile:Sheets(„Dialog1“).Select

Klicke auf das Titelblatt.Klicke mit der rechten Maus in die Symbolleiste und öffne die Symbolleiste Formular.

Ziehe über die Zellen C4 bis D7 eine Schaltfläche auf. Drücke dabei die <Alt>-Taste, dadurch wird das Gitternetz magnetisch.

Weise der Schaltfläche das Makro Druck-Lohnstreifen zu. Schreibe in die Schaltfläche Lohnstreifen drucken.

Wir wählen Datei/Seitenansicht anstatt Datei/Drucken, damit wir in der Testphase nicht ständig ausdrucken. Später wird im Modul das Wort PrintPreview durch PrintOut ersetzt werden.

Formatiertes Titelblatt

Unterlagen pc.base - EDV-Schulung BruneckEXCEL V-12 Richard Kammerer

EINGABE ERGEBNISXVI. SCHRITT

Datenbanken dynamisch verlängern

Klicke auf das Titelblatt.Extras/Makro AufzeichnenName Personal Tastenkombination <Strg>+gKlicke in der Tabelle Personal in die Zelle A1Daten/Maske - schließen<Strg>*Einfügen/Name Erstellen - Oberste ZeileKlicke auf das Titelblatt in die Zelle A1Makroaufzeichnung beenden.

Zeichne im Titelblatt eine Schaltfläche und weise ihr das Makro Personal zu. Schreibe Personal bearbeiten in die Schaltfläche.

Teste den Ablauf: Falls die Datenbank länger wird, erscheinen Meldungen. Diese kann man mit einem Befehl unterdrücken: Application.DisplayAlerts = False (s. Abb.).

Am häufigsten werden sich die Daten der Tabelle Personal ändern. Diese Aufgabe werden wir jetzt komfortabel gestalten - natürlich mit einem Makro.

Mit <Strg>* wird die gesamte Datenbank mar-kiert, auch falls sie sich um einige neue Mitar-beiter verlängert hat; die Bereich können neu benannt werden.

Alle VBA Befehle können über Ansicht/Objekt-katalog eingesehen werden.

XVII. SCHRITTAusblenden

Teste die gesamte Anwendung. Auf kleine Fehler wird man erst später draufkommen.

Die Blatt Dialog1 werden wir ausblenden: Format/Blatt Ausblenden.

Beim Lohnstreifen werden wir die Spalte G ausblenden. Also Spalte G markieren und Format/Spalte Ausblenden.

Bei einigen Tabellen (z. B. Titelblatt und Lohnstreifen) können wir die Zeilen- und Spaltenüberschriften ausblenden: Extras/Optionen Zeilen- und Spaltenüberschriften.

Schließlich blenden wir die Blattregisterkarten aus: Extras/Optionen (s. Abb.).

Unterlagen pc.base - EDV-Schulung BruneckEXCEL V-13 Richard Kammerer

EINGABE ERGEBNISXVIII. SCHRITT

Benutzerdefinierte Symbole

Eine Alternative zu den Schaltflächen im Titelblatt bieten Symbole (Siehe Schritt XIV).

Klicke mit der rechten Maustaste in die Symbollei-ste und wähle Anpassen. Suche unter den Befehlen Makros und ziehe das Symbol Schaltfläche anpassen in die Standardsymbolleiste. Klicke mit der rechten Maus auf das Symbol und gib den Befehl Makro zuweisen (s. Abb.).

Wähle das Makro DruckLohnstreifen. Durch einen erneuten Rechtsklick kann bei Name das Quick-Info bestimmt, mit Schaltfläche bearbeiten eine neues Layout entworfen werden, usw.

Wiederhole den Schritt mit dem Makro Personal

XIX. SCHRITTBenutzerdefinierte Symbolleiste

Zieht man das benutzerdefinierte Symbol in den luftleeren Raum, entsteht eine neue Symbolleiste. Damit bleibt die Standardsymbolleiste wie Gates sie schuf. Diese Symbolleiste könnte interaktiv beim Arbeiten mit dem Lohnstreifen eingeblendet werden.

Bei Benutzerführung mit Symbolen und Menübefehlen empfiehlt sich ein etwas anderes Konzept. In das Makro sollte auch das Öffnen der Datei, besser noch einer Dokumentvorlage eingebaut werden. Dazu muss es in die persönliche Arbeitsmappe gespeichert werden. Sonst stehen die Makros erst nach Öffnen der Arbeitsmappe zur Verfügung.

XX. SCHRITTBenutzerdefinierte Menüs

Variante Nr. 3 sind benutzerdefinierte Menüs.

Klicke mit der rechten Maus in die Menüleiste und wähle anpassen. Wähle die Kategorie Neues Menü und schiebe Neues Menü in die Menüleiste. s. Abb.

Klicke mit rechts auf das Menü und gib ihm den Namen Lohnberechnung.

Klicke in der Anpassen Diaolg auf Makros und schiebe Benutzerdefiniertes Menüelement auf das Menü Lohnstreifen. Durch Rechtsklick kann der Name geändert und das Makro zugewiesen werden. Viel Spaß mit Excel