Excel für Profis

Excel-Tricks zu Formeln und Makros

23.08.2010
Von 
Geboren Mitte der 70er Jahre, ist er mit dem PC aufgewachsen. Erste Basic-Programme auf dem 8086, beeindruckende CGA- und EGA-Adventures auf dem 80286 - der PC war immer da, als Werkzeug, als Spielzeug, als Chance, einzigartige Dinge zu tun. Schon während des Physik-Studiums machte er dann sein Hobby langsam zum Beruf, indem er als studentische Aushilfskraft die PC-WELT-Redaktion bei allerlei kleinen Aufgaben unterstützte. Von da an war der Studienabschluss eigentlich nur noch ein notwendiges, aber eher lästiges Projekt. Der echte Spaß ging immer erst nach den Vorlesungen in der Redaktion los - tüfteln, querdenken, schreiben, Menschen erreichen. Klar, dass es nach Abschluss des Studiums nur einen Weg geben konnte – mit voller Kraft zur PC-WELT. Seither besteht seine Begeisterung für Wissenschaft, Technik und deren Einfluss auf die Menschen fort. Die Vernetzung und Digitalisierung der Welt ist sicherlich eine der größten Entwicklungen dieser Generationen. Er ist froh, diese spannende Zeit mit der und für die PC-WELT und Macwelt gestalten zu können.
Weitere Artikel des Autors

So berechnen Sie den Tilgungsplan eines Kredits

Die Zellen mit den Über- und Unterstunden müssen mit einer Formel als Text formatiert sein, damit auch negative Zeitwerte dargestellt werden können. Die Farben der Zellen legen Sie mit einer bedingten Formatierung fest.
Die Zellen mit den Über- und Unterstunden müssen mit einer Formel als Text formatiert sein, damit auch negative Zeitwerte dargestellt werden können. Die Farben der Zellen legen Sie mit einer bedingten Formatierung fest.

Etwas komplizierter, aber unentbehrlich für jeden, der eine Immobilie, ein Auto oder eine andere höherwertige Anschaffung mit einem Kredit finanzieren möchte, ist ein Tilgungsplan für dieses Darlehen. Im Ordner mit den Beispieldateien finden Sie als Muster die Datei "Finanzierung.xls". Bei ihr kommen Sie ebenfalls ohne Programmierung aus.

Zellen als Währung oder Prozent formatieren:

Bei Krediten geht es immer um Geld, um Zinsen und um Tilgung. Sie brauchen also zumindest Zellen für die Eingabe von Geldbeträgen und Prozenten. In der Zelle D4 der Beispieldatei wird etwa der Gesamtkreditbetrag eingegeben. Wenn Sie mit der rechten Maustaste auf diese Zelle klicken und "Zellen formatieren" wählen, sehen Sie, dass diese Zelle das Format "Währung" in Euro (€) mit zwei Dezimalstellen hat. Genau dasselbe Format haben alle Zellen auf diesem Arbeitsblatt, in denen Geldbeträge stehen (erkennbar am €-Zeichen am Ende).

Die Zelle D5 ist das Feld für den anfänglichen Zinssatz des Kredits und hat somit das Format "Prozent". Das gilt für alle anderen Zellen dieses Arbeitsblattes mit Prozentangaben. Außerdem finden Sie in D8 und D20:D379 die aus Beispiel 1 bekannten Datumszellen.

Existenz von Zellwerten an Bedingungen knüpfen: Die Länge des Tilgungsplanes ab Zeile 20 ist abhängig von der Dauer des Kredites, hier zwischen 1 und 30 Jahren. Das bedeutet aber auch, dass maximal 30x12=360 Raten berücksichtigt werden können und somit auch 360 Zeilen mit Formeln vorbereitet werden müssen. Wenn Sie in den Spalten A bis I jeweils die Zelle 379 markieren, sehen Sie deshalb darin noch Formeln, eine Zeile darunter nicht mehr.

Damit die Zellen nach Ende der gewünschten Kreditlaufzeit wirklich leer bleiben und nicht 0 oder gar Fehler anzeigen, setzen Sie die Excel-Funktion WENN ein. Mit ihr geben Sie eine Bedingung an, nach der ein bestimmter Zellwert angezeigt wird. Andernfalls bleibt die Zelle leer.

Wenn Sie etwa die Zelle I20 unter "Restschuld Monatsende" markieren, sehen Sie die Formel =WENN(A20<>„“;C20-G20;„“). Durch sie wird zunächst geprüft, ob der Kredit überhaupt läuft und in Zelle A20 etwas steht. Falls ja, dann errechnet Excel die Restschuld zum Monatsanfang (Zelle C20) minus dem Tilgungsbetrag (G20). Falls nein, dann bleibt die Zelle leer. Eine etwas komplexere Funktion mit zwei verschachtelten WENN-Funktionen 6nden Sie beispielsweise in Zelle E20 unter „Sondertilgung“.

Da Sondertilgungen bei Krediten in der Regel jährlich bezahlt werden, gibt es für diese Zellen drei mögliche Zustände: Sie haben während der Kreditlaufzeit elf Mal im Jahr den Wert 0,00 € und einmal den Wert von Zelle D9, und nach Kreditende bleibt die Zelle leer. Die Formel dafür lautet: =WENN(A20<>"";WENN(REST(A20;12)=0;$D$9;0);"")

Die erste WENN-Abfrage prüft, ob der Kredit läuft. Falls ja, wird eine weitere WENN-Abfrage gestartet, die überprüft, ob die Zahl in Spalte A sich ohne Rest durch 12 teilen lässt. Falls ja, wird jeder 12. Ratenzahlung der vorgesehene Betrag für die Sondertilgung hinzugefügt (D9).

Zur Startseite