EXCEL-Aufgaben zur Krankenversicherung

Berechnungen zur Krankenversicherung im Jahr 2017

Erstellen Sie folgende Tabelle. Die grauen Zellen enthalten Formeln. Die gelben Zellen sind Eingabefelder. Diese Aufgabe wird bei den folgenden Themen fortgesetzt. Die Zeilen 15 bis 21 werden später gebraucht. Die Spalte J ebenfalls.

Übersicht der Sozialversicherungsbeiträge und Beitragsbemessungsgrenzen 2017

In den Zellen B3 und B4 werden mit der Datenüberprüfung auf Zellen mögliche Zelleinträge festgelegt. Die Optionen für die Datenüberprüfung befinden sich auf der Registerkarte Daten in der Gruppe Datentools. In B3 ist nur Ost und West zulässig. In B4 ist nur ja und nein zulässig. Wählen Sie im Feld Zulassen die Option Liste aus. Geben Sie in das Feld Quelle die zwei Listenwerte mit Semikolon als Trennzeichen ein. Abbildung für Zelle B3:

In C4 ist mit einer WENN-Funktion bei einer unsinnigen Eingabe (B3 gleich West und B4 gleich ja) folgende Fehlermeldung zu erzeugen.

Als Wahrheitsprüfung in der WENN-Funktion brauchen Sie die UND-Funktion. Die Formel in C4 sieht so aus:
=WENN(UND(B3="West";B4="ja");"Fehler: Sachsen ist Rechtskreis Ost!";"")

2015 wurde der allgemeine Beitragssatz für die Gesetzlichen Krankenkassen bei 14,6 Prozent festgeschrieben. Beim besondernen Beitragssatz wurden 14,0 Prozent festgeschrieben.
Von dem Beitragssatz tragen Arbeitnehmer und Arbeitgeber jeweils die Hälfte, also 7,3 Prozent bzw. 7,0 Prozent.
Brauchen die Kassen mehr Geld, können sie einkommensabhängige Zusatzbeiträge erheben. Den kasseninidividuellen Zusatzbeitrag hingegen zahlt der Arbeitnehmer selbst.

Die Formeln in C12 und D12 nehmen Bezug auf B12 und B14. Bei einer Beitragssatzänderung in B12 und B14 muss sich die Anzeige in C12 und D12 entsprechend ändern.

Die Formeln in C13 und D13 nehmen Bezug auf B13 und B14. Bei einer Beitragssatzänderung in B13 und B14 muss sich die Anzeige in C13 und D13 entsprechend ändern.

In den Bereichen E23 bis E28 und F23 bis F28 werden mit der Datenüberprüfung auf Zellen mögliche Zelleinträge festgelegt. In E23 bis E28 ist nur allgemein oder ermäßigt zulässig. In F23 bis F28 ist nur ja oder nein zulässig. Wählen Sie im Feld Zulassen die Option Liste aus. Geben Sie in das Feld Quelle die zwei Listenwerte mit Semikolon als Trennzeichen ein.

Die Zusatzbeiträge werden in einer Tabelle mit den Krankenkassen aufgenommen (G10 bis H14).
Im Bereich D23 bis D28 werden mit der Datenüberprüfung auf Zellen mögliche Zelleinträge festgelegt. Es dürfen nur die in G11 bis G14 aufgeführten Krankenkassen ausgewählt werden. Wählen Sie im Feld Zulassen die Option Liste aus. Geben Sie in das Feld Quelle den Zellbereich G11 bis G14 ein.

Die Formeln in H23 und I23 nehmen Bezug auf E23. Je nach Beitragssatz des Arbeitnehmers wird der entsprechende Prozentsatz geholt (Bezug auf C12 bis D13). Sie brauchen die WENN-Funktion. Die Formeln aus Zeile 23 müssen kopierfähig bis Zeile 28 sein.
In H23 müssen Sie zusätzlich mit einer SVERWEIS-Funktion den Zusatzbeitrag addieren. Die Formel in H23 sieht so aus:
=WENN(E23="allgemein";$C$12;$C$13)+SVERWEIS(D23;$G$11:$H$14;2;FALSCH)
Die Formel in I23 sieht so aus:
=WENN(E23="allgemein";$D$12;$D$13)

Mit den Formeln in K23 und L23 berechnen Sie die Beiträge für Arbeitnehmer und Arbeitgeber. Das beitragspflichtige Arbeitsentgelt steht in Spalte G und die für den jeweiligen Arbeitnehmer zutreffenden Beitragssätze in den Spalten H und I. Beachten Sie die Beitragsbemessungsgrenze in B8 (verbundene Zelle). Sie brauchen die WENN-Funktion. Wenn das beitragspflichtige Arbeitsentgelt größer als die Beitragsbemessungsgrenze ist, erfolgt die Beitragsberechnung nur von der Beitragsbemessungsgrenze. Die Formeln aus Zeile 23 müssen kopierfähig bis Zeile 28 sein.

Damit die Summen in Zeile 30 stimmen müssen Sie die Funktion RUNDEN (2 Dezimalstellen) in den Formeln K23 und L23 verwenden.

Die Formel in K23 sieht so aus:
=RUNDEN(WENN(G23>$B$8;$B$8*H23;G23*H23);2)
Die Formel in L23 sieht so aus:
=RUNDEN(WENN(G23>$B$8;$B$8*I23;G23*I23);2)

Diese Aufgabe wird beim Punkt Pflegeversicherung fortgesetzt. Weitere Fortsetzungen erfolgen bei den Punkten Rentenversicherung und Arbeitslosenversicherung.