Zur Hauptseite ... Zum Onlinearchiv ... Zum Abonnement ... Zum Newsletter ... Zu den Tools ... Zum Impressum ... Zum Login ...

Achtung: Dies ist nicht der vollständige Artikel, sondern nur ein paar Seiten davon. Wenn Sie hier nicht erfahren, was Sie wissen möchten, finden Sie am Ende Informationen darüber, wie Sie den ganzen Artikel lesen können.

Kompletten Artikel lesen?

Einfach für den Newsletter anmelden, dann lesen Sie schon in einer Minute den kompletten Artikel und erhalten die Beispieldatenbanken.

E-Mail:

Gedrucktes Heft

Diesen Beitrag finden Sie in Ausgabe 5/2006.

Unser Angebot für Sie!

Lesen Sie diesen Beitrag und 500 andere sofort im Onlinearchiv, und erhalten Sie alle zwei Monate brandheißes Access-Know-how auf 72 gedruckten Seiten! Plus attraktive Präsente, zum Beispiel das bald erscheinende Buch 'Access 2010 - Das Grundlagenbuch für Entwickler'!

Diesen Beitrag twittern

Zusammenfassung

Programmieren Sie eine Excel-Anwendung, die Daten in Access speichert und ausliest.

Techniken

Excel, VBA

Voraussetzungen

Access 2000 und höher

Beispieldatenbank

Darlehenskalkulator.mdb, Darlehenskalkulator.xls

Darlehenskalkulator mit Excel und Access

André Minhorst, Duisburg

Wer mal eine größere Sache wie ein Haus oder ähnliches kauft, muss ordentlich rechnen - zumindest, wenn er nicht gerade im Lotto gewonnen hat. Dabei geht es darum, das Darlehen, Zinssatz, Zahlungszeitraum, Sondertilgungen und mehr unter einen Hut zu bringen - eigentlich kein Fall für Access, sondern für Excel. Dennoch kommt hier aus Access zum Zuge - wie genau, erfahren Sie in diesem Beitrag.

Annuitätendarlehen

Die in diesem Beitrag vorgestellte Lösung berechnet Annuitätendarlehen. Diese Art des Darlehens kommt zum Beispiel oft bei der Immobilienfinanzierung vor.

Die wichtigste Eigenschaft einer solchen Finanzierung ist eine konstante Annuitätenrate, die sich aus einem Zins- und einem Tilgungsanteil zusammensetzt, wobei der Zinsanteil durch das schrumpfende Restdarlehen ab- und der Tilgungsanteil zunimmt.

Formeln

Für die Umsetzung einer automatischen Berechnung eines solchen Kredits sind einige Formeln notwendig.

Laufzeit

Die erste Formel bestimmt die Laufzeit eines Kredits anhand des Zinssatzes und der Tilgung:

Dabei ist n die Laufzeit in Jahren, i der Zinssatz und t der Tilgungssatz.

Mit einem Zinssatz von 4 % und einer Tilgung von 1 % liefert diese Formel etwa eine Laufzeit von etwas mehr als 41 Jahren.

Gegebenenfalls erfolgt die Zahlung nicht einmal, sondern mehrmals im Jahr - in dem Fall gilt die folgende Formel für die Berechnung der Laufzeit:

Dabei entspricht m der Anzahl der Zahlungen pro Jahr.

Abb. 1: Darlehensberechnung mit Excel

Annuität

Mit der Laufzeit lässt sich die Annuität, also der jährlich zu zahlende Betrag, berechnen:

Dabei ist R die Annuität, S0 die Kreditsumme, i der Zinssatz und n die Laufzeit.

Wenn man die aus der obigen Formel berechnete Laufzeit einsetzt, lässt sich die Formel vereinfachen:

Für die monatliche Annuität teilt man das Ergebnis noch durch die Anzahl der Monate.

Umsetzung in Excel

Excel ist prädestiniert für die Berechnung von Annuitätendarlehen. Dazu benötigt man nur die Angabe von drei Werten (Darlehen, Zinssatz, Tilgung) und eine Reihe Formeln. Abb. 1 zeigt die Berechnung in Excel, die Sie auch in der Beispieldatei finden. Die oberen drei Zeilen enthalten die für die Berechnung notwendigen Informationen; wenn Sie diese ändern, passt Excel die in der darunter befindlichen Tabelle enthaltenen Werte ebenfalls an.

Die Tabelle mit dem Verlauf des Annuitätendarlehens legen Sie folgendermaßen an: Die erste Spalte enthält die Monate. Dabei ist der nullte Monat in der ersten Zeile derjenige, in dem die Auszahlung erfolgt. Die Rückzahlung beginnt nach dem ersten Monat. Tragen Sie in die Spalte mit den Monaten den Wert 1 ein. Nun könnten Sie die übrigen Werte händisch eintragen, eine hilfreiche Funktion von Excel nimmt Ihnen diese Arbeit jedoch ab: Tragen Sie für den zweiten Monat eine Formel ein, die den Inhalt der darüber liegenden Zelle um eins erhöht - hier etwa =A6+1.

Diesen Vorgang sollen Sie natürlich nicht endlos wiederholen. Stattdessen markieren Sie die Zelle A7 und ziehen das Viereck in der linken unteren Ecke der Zelle soweit nach unten, wie Sie Monate benötigen (s. Abb. 2).

Wenn Sie noch nicht wissen, wie viele das sind, setzen Sie ruhig ab - Sie können den Inhalt der untersten kopierten Zelle in einem weiteren Schritt weiter "nach unten ziehen". Der Clou ist, das Excel nicht nur den Zelleninhalt kopiert, sondern auch die relativen Bezüge beibehält - jede der neu gefüllten Zellen addiert den Wert 1 zum Wert der darüber liegenden Zelle.

Abb. 2: Kopieren von Zellen nach unten

Die zweite und dritte Spalte sollen die im Kopf des Tabellenblatts enthaltenen Werte für Zinssatz und Tilgung enthalten. Dementsprechend legen Sie für die Zelle B7 zunächst den Inhalt =$B$2 und für C7 den Inhalt =$B$3 fest. Was sollen nun die Dollar-Zeichen ($) in den Formeln? Ganz einfach: Sie erzeugen absolute statt relativer Bezüge zu den Zellen, damit beim Kopieren dieser Zellen nach unten jeweils der Wert der Zellen B2 und B3 erhalten bleibt. Wenn Sie die Dollar-Zeichen weglassen und die Felder wie bereits bei den Monaten nach unten kopieren, würde beispielsweise die Zelle B8 nicht mehr den Inhalt von B2, sondern den von B3 anzeigen.

Aber ist dies überhaupt so sinnvoll? Wenn der Benutzer etwa für den Zeitraum ab dem zehnten Jahr einen anderen Zinssatz eintragen möchte, müsste er dies für jede Zeile einzeln tun. Wenn Sie erreichen möchten, dass die Änderung in dieser Zelle in alle darunter befindlichen Zeilen übertragen wird, setzen Sie doch wieder eine Formel mit Bezug zur darüber liegenden Zelle ein. Die Zelle B7 enthielte dann nach wie vor den Wert =$B$2, die Zelle B8 den Wert =B7, die Zelle B9 den Wert =B8 und so weiter.

Die Spalte Sondertilgungen bleibt zunächst leer, hier kann der Benutzer später nach Belieben Sondertilgungen eintragen.

Die Annuität berechnet man mit der Formel =$B$1*(B7+C7)/12. Eigentlich könnte man hier auch =$B$1*($B$2+$B$3)/12 verwenden. Normalerweise ist aber beispielsweise der Zinssatz nur auf eine Laufzeit von 5, 10 oder 15 Jahren festgelegt. Durch die hier verwendete Formel können Sie nach Ablauf dieser Zeit einen neuen Zinssatz festlegen, die Annuität wird entsprechend angepasst. Auch diese Formel tragen Sie in der ersten Zeile in der Zelle E7 ein und kopieren diese in die darunter liegenden Zellen.

Der Zins wird ganz einfach aus dem Produkt des Restdarlehens und des Zinssatzes ermittelt und lautet für die erste Zeile =H6*B7/12. Auch den Inhalt dieses Feldes kopieren Sie nach unten.

Da die Annuität über die gesamte Laufzeit konstant bleiben soll, der Zins sich aber gemeinsam mit dem Restdarlehen vermindert, wächst die Tilgung. Die Formel für die entsprechende Spalte lautet ganz einfach =E7-F7.

Bleibt noch das Restdarlehen: Dabei handelt es sich um die Differenz des Restdarlehens des vorherigen Monats und der Summe der Tilgungen, also der Tilgung und der Sondertilgung des aktuellen Monats - für die erste Zahlungsperiode also etwa =H6-G7-D7.

Mit dem Kopieren aller Zeilen nach unten ist die erste Fassung dieser Berechnung fertig. Erste Fassung deshalb, weil man der Tabelle doch noch einiges an Komfort hinzufügen könnte - beispielsweise sollte diese möglichst automatisch die benötigte Anzahl Zeilen anzeigen. Bevor Sie solche kosmetischen Veränderungen vornehmen, bauen wie die Tabelle jedoch zunächst in eine Access-Anwendung ein.

Damit Sie später per VBA auf das hier angelegte Tabellenblatt zugreifen können, vergeben Sie dafür den Namen Darlehenskalkulator. Am einfachsten geht dies, wenn Sie einfach doppelt auf den Reiter mit der Beschriftung Tabelle 1 im unteren Bereich des Excel-Fensters klicken und diese Bezeichnung anpassen.

Fixieren für bessere Übersicht

Die Tabelle kann mitunter einige hundert Zeilen enthalten. Beim Herunterscrollen nervt es dann unter Umständen, wenn man die Spaltenüberschriften und die übrigen Darlehensinformationen nicht mehr lesen kann. Sie können allerdings einen bestimmten Bereich der Tabelle fixieren. Dazu markieren Sie einfach die Zeile, oberhalb derer alle Informationen beim Scrollen stehen bleiben sollen und wählen den Menübefehl Fenster/Fenster fixieren aus.

Excel-Frontend mit Access-Backend

Da Excel genauso wie Access VBA unterstützt, könnte man die notwendigen Erweiterungen zum Steigern des Komforts auch komplett in Excel vornehmen. Access bietet jedoch eine sehr angenehme Eigenschaft: die Möglichkeit, Daten in relationalen Tabellen zu speichern. Wenn Sie mit der aktuellen Fassung der Excel-Anwendung zum Berechnen von Finanzierungen herumspielen und diverse Varianten durchprobieren, löschen Sie entweder jeweils die zuvor erstellte Berechnung oder Sie kopieren das Arbeitsblatt und erzeugen so verschiedene Berechnungen.

Abb. 3: Ein neues Steuerelement im Excel-Tabellenblatt und seine Eigenschaften

Übersichtlich ist das alles allerdings nicht. Immerhin ließe sich auch mit Excel eine Übersichtsseite bauen, mit der man auf die unterschiedlichen Varianten verweisen kann, aber dazu bräuchten Sie ja kein Access.

Abb. 4: Die Toolbox mit der Schaltfläche zum Aktivieren und Deaktivieren des Entwurfsmodus

Die nachfolgend vorgestellte Lösung soll nun so aussehen: Access arbeitet lediglich im Hintergrund und speichert die Daten der unterschiedlichen Berechnungen.

Excel bietet einige Steuerelemente, mit denen sich die aktuelle Berechnung speichern oder eine alte Berechnung wiederherstellen lässt. Damit greifen Sie also vom Excel-Tabellenblatt direkt auf die Datenbank zu.

Steuerelemente in Excel

Um die Anwendung möglichst einfach zu halten, sollen nur zwei Steuerelemente zum Einsatz kommen:

  • eine Schaltfläche zum Speichern der aktuellen Berechnung und
  • ein Kombinationsfeld zum Auswählen und Wiederherstellen einer der gespeicherten Berechnungen.
  • Die notwendigen Steuerelemente sind schnell angelegt: Dazu aktivieren Sie einfach die Symbolleiste Steuerelement-Toolbox (Menüeintrag Ansicht ( Symbolleisten ( Steuerelement-Toolbox), klicken auf die gewünschten Steuerelemente und ziehen Rahmen in der passenden Größe auf (s. Abb. 4).

    Entwurf ein/aus

    Die Steuerelement-Toolbox unterscheidet sich im Übrigen von der von Access bekannten Toolbox durch eine Schaltfläche zum An- und Ausschalten der Entwurfsansicht. Um etwa das Beim Klicken-Ereignis einer Schaltfläche per Mausklick auslösen zu können, müssen Sie zunächst den Entwurfsmodus beenden.

    Eigenschaftsfenster

    Das Ändern wichtiger Eigenschaften wie dem Namen oder der Bezeichnung nehmen Sie im Eigenschaftsfenster vor, dass Sie im Kontextmenü des jeweiligen Steuerelements über den Eintrag Eigenschaften anzeigen (s. Abb. 3).

    Excel ist nicht standardmäßig für den Zugriff auf Access-Datenbanken vorbereitet, daher müssen Sie noch den fehlenden Verweis einstellen. Dazu wechseln Sie in den VBA-Editor: Ändern Sie zunächst den Namen der neuen Schaltfläche in cmdSpeichern und klicken Sie dann doppelt auf diese, dann zeigt der VBA-Editor direkt die beim Klicken auf diese Schaltfläche ausgelöste Routine an.

    Dort öffnen Sie über den von der VBA-Entwicklungsumgebung gewohnten Menüeintrag Extras/Verweise den entsprechenden Dialog und fügen den Verweis auf die Bibliothek Microsoft DAO 3.6 Object Library hinzu (s. Abb. 5).

    Abb. 5: Hinzufügen eines Verweises auf die DAO-Bibliothek zum VBA-Projekt der Excel-Anwendung

    Speichern der Darlehen-Basisdaten

    Die Basisdaten eines Darlehens befinden sich im oberen Bereich des Excel-Tabellenblatts. Per Mausklick auf die Schaltfläche Speichern sollen die passenden Daten in einer Tabelle namens tblDarlehen gespeichert werden. Diese Tabelle ist wie in Abb. 6 aufgebaut.

    Wichtig ist, dass die Eigenschaft Indiziert des Feldes Bezeichnung den Wert Ja (Ohne Duplikate) hat, damit dieses Feld nur eindeutige Werte enthalten kann. Außerdem müssen Sie für die beiden Felder Zinssatz und Tilgung als Feldgröße Single einstellen.

    Abb. 6: Aufbau der Tabelle tblDarlehen

    Die Informationen für die einzelnen Zeiträume (in dem Fall Monate), die sich im unteren Bereich des Tabellenblatts befinden, werden teilweise berechnet, teilweise benutzerdefiniert (wie die Sondertilgungen) und teilweise beides - die Zinssätze und Tilgungssätze etwa werden zunächst festgelegt, können aber vom Benutzer angepasst werden. Die beiden letzteren Kategorien von Informationen speichert die Anwendung in einer weiteren Tabelle namens tblZahlungperioden. Die Datensätze dieser Tabelle sind jeweils mit einem Datensatz der Tabelle tblDarlehen verknüpft, sodass eine eindeutige Zuordnung erfolgen kann (s. Abb. 7). Die Felder Zinssatz und Tilgung erhalten wiederum die Feldgröße Single.

    Abb. 7: Die Tabelle für die Finanzierungsdetails

    Abb. 8: Die Eigenschaften der Beziehung zwischen den Tabellen tblDarlehen und tblZahlungsperioden

    Beim Speichern eines Darlehens inklusive Zahlungsperioden sollen eventuell bereits unter der gleichen Bezeichnung vorhandene Daten gelöscht werden. Damit dies mit einer einzigen DELETE-Anweisung erledigt werden kann, stellen Sie für die Beziehung zwischen den beiden Tabellen referentielle Integrität und Löschweitergabe ein (s. Abb. 8).

    Das Speichern der in der Excel-Tabelle enthaltenen Daten bedarf einiger Vorüberlegungen. Die wichtigste davon ist: Wie viele Zahlungsperioden sollen zu einem Darlehen gespeichert werden? Wichtig sind eigentlich alle Zahlungsperioden bis zur vollständigen Tilgung des Darlehens, also bis der Inhalt des Feldes Restdarlehen kleiner oder gleich 0 ist.

    Prozedur zum Speichern einer
    Finanzierung

    Die Routine aus Quellcode 1 übernimmt das Speichern der relevanten Informationen in die beiden Tabellen der Access-Datenbank.

    Nach dem Deklarationsteil weist die Routine zunächst der Variablen xlsWorksheet einen Verweis auf das Tabellenblatt mit den zu speichernden Daten zu. Anschließend folgt schon das Herstellen des Verweises auf die Datenbankdatei mit den Zieltabellen. Dazu verwenden Sie die Methode OpenDatabase des DBEngine-Objekts. In diesem Falle reicht die Angabe des Dateinamens inklusive Pfad als Parameter aus.

    Der Einfachheit halber sollen die Excel-Datei und die Datenbank im gleichen Verzeichnis liegen. Auf diese Weise treffen Sie mit ActiveWorkbook.Path immer das richtige Verzeichnis.

    Die folgende DELETE-Anwiesung löscht alle Datensätze der Tabelle tblDarlehen, deren Feld Bezeichnung den aktuellen Wert der Zelle E1 des Tabellenblatts enthält. Da dieses Feld als eindeutig indiziert ist, kann dies maximal einen Datensatz betreffen.

    Durch die Festlegung der Löschweitergabe in der Beziehung zur Tabelle tblZahlungsperioden werden die in dieser Tabelle enthalten und mit dem gelöschten Datensatz verknüpften Datensätze direkt mitgelöscht. Anschließend öffnet die Routine eine leere Datensatzgruppe auf Basis der Tabelle tblDarlehen und fügt dieser einen neuen Datensatz mit den im Kopf des Tabellenblatts enthaltenen Daten ein.

    Dabei merkt sich die Routine den Wert des Primärschlüsselfeldes, denn dieser soll später beim Anlegen der einzelnen Perioden der Finanzierung ja ebenfalls mitgespeichert werden - und zwar als Inhalt des Fremdschlüsselfeldes DarlehenID.

    Beim Anlegen dieser Daten durchläuft die Routine alle Zeilen der Excel-Tabelle von Zeile 7 - das ist die Zeile mit der ersten Zahlungsperiode - bis zu der Zeile, in der das Restdarlehen erstmals kleiner oder gleich 0 ist.

    Für jede Zeile der Excel-Tabelle schreibt die Routine einen Datensatz in die Tabelle tblZahlungsperioden.

    Quellcode 1: Speichern einer Finanzierung in Access-Tabellen

    Private Sub cmdSpeichern_Click()

        Dim xlsWorksheet As Worksheet

        Dim db As DAO.database

        Dim strBezeichnung As String

        Dim rstDarlehen As DAO.Recordset

        Dim lngDarlehenID As Long

        Dim rstZahlungsperioden As DAO.Recordset

        Dim intZeile As Integer

        Set xlsWorksheet = Worksheets("Darlehenskalkulator")

        Set db = DBEngine.OpenDatabase(ActiveWorkbook.Path & "\Darlehenskalkulator.mdb")

        strBezeichnung = xlsWorksheet.Range("E1")

        db.Execute "DELETE FROM tblDarlehen WHERE Bezeichnung = '" & strBezeichnung & "'"

        Set rstDarlehen = db.OpenRecordset("SELECT * FROM tblDarlehen WHERE 1=0", dbOpenDynaset)

        With rstDarlehen

            .AddNew

            !Bezeichnung = strBezeichnung

            !Datum = xlsWorksheet.Range("E2")

            !Darlehen = xlsWorksheet.Range("B1")

            !Zinssatz = xlsWorksheet.Range("B2")

            !Tilgung = xlsWorksheet.Range("B3")

            lngDarlehenID = !DarlehenID

            .Update

        End With

        rstDarlehen.Close

        Set rstDarlehen = Nothing

        Set rstZahlungsperioden = db.OpenRecordset("SELECT * FROM tblZahlungsperioden WHERE 1=0", _
            dbOpenDynaset)

        With rstZahlungsperioden 

            intZeile = 7

            Do While xlsWorksheet.Range("H" & intZeile) > 0

                .AddNew

                !DarlehenID = lngDarlehenID

                !Monat = xlsWorksheet.Range("A" & intZeile)

                !Zinssatz = xlsWorksheet.Range("B" & intZeile)

                !Tilgung = xlsWorksheet.Range("C" & intZeile)

                !Sondertilgung = xlsWorksheet.Range("D" & intZeile)

                .Update

                intZeile = intZeile + 1

            Loop

        End With

        rstZahlungsperioden .Close

        Set rstZahlungsperioden = Nothing

        Set db = Nothing

    End Sub

    Sie haben das Ende des frei verfügbaren Teils des Artikels erreicht. Lesen Sie weiter, um zu erfahren, wie Sie den vollständigen Artikel lesen und auf viele hundert weitere Artikel zugreifen können.

    Sind Sie Abonnent?Jetzt einloggen ...
     

    Kompletten Artikel lesen?

    Einfach für den Newsletter anmelden, dann lesen Sie schon in einer Minute den kompletten Artikel und erhalten die Beispieldatenbanken.

    E-Mail:

    © 2003-2015 André Minhorst Alle Rechte vorbehalten.