Komplexere Berechnung: Lineares Gleichungssystem beliebigen GradesGleichungen eingeben | Programmierung mit vba | Etwas Design |
Excel ist ein wahrer Rechenmeister, man besehe sich nur die Funktionsübersicht an, die man erhält, wenn man den mit " fx" beschrifteten Button drückt. Die Lösung eines linearen Gleichungssystems gehört allerdings nicht dazu. Für Berechnungen, die viele Eingabewerte benötigen, ist Excel deshalb sehr geeignet, weil man die Eingabewerte übersichtlich darstellen und leicht korrigieren kann.
Als einfaches Beispiel betrachten wir die Lösung eines linearen Gleichungssystems mit hier, willkürlich festgelegt, maximal 15 Gleichungen mit 15 Unbekannten. Das können mittlerweile auch Taschenrechner; wer es damit bei so vielen Gleichungen einmal versucht hat, sieht sofort ein, daß Excel für diese Aufgabe besser geeignet ist.
Wir gehen mal davon aus, daß das Gleichungssystem irgendwo geordnet steht, auf Papier oder einem Ort, von dem aus es elektronisch importiert werden kann. Die übliche Darstellung eines linearen Gleichungssystems ist:
a11* x1+ a12* x2+ ... + a1n* xn= b1
... ...
an1* x1+ an2* x2+ ... + ann* xn= bn
Die Koeffizienten aij des Systems aus n Gleichungen mit n Unbekannten werden reihenweise in die Tabelle geschrieben, die rechten Seiten direkt daneben. Die xi erscheinen in der Exceltabelle natürlich zunächst nicht. Wie das aussehen könnte, zeigen wir gleich. Die Ränder mit ihren Beschriftungen dienen hier nur dem Verständnis. Für die Funktion sind sie entbehrlich. Ein geratenes Gleichungssystem ist schon eingegeben, damit man später probieren kann.
Excel enthält nun das Gleichungssystem; um daraus die Koeffizientenmatrix zu erzeugen, die wir für das noch nicht vorhandene Gleichungsprogramm brauchen, ist Programmierung nötig.
Um in Excel Dinge zu tun, die Excel selbst nicht kann, nutzt man die eingebaute Makrosprache vba (= "Visual Basic for Applications"). Unter <Extras>/<Makro>/<Visual Basic-Editor> klickt man sich in die Entwicklungsumgebung, genannt IDE, zu Deutsch : Integrated Development Environment, und holt sich dort aus der Menüleiste unter <Einfügen>/<Modul> ein Codeblatt, in das hinein das notwendige Programm geschrieben werden soll.
Nun sind mehrere Dinge notwendig. Wir müssen
Wir beginnen mit dem Makro, das später die ganze Aktivität starten soll. Es möge "Gleichung" heißen. Dann sieht unser Start nach der Kopfzeile und dem ersten "Return" so aus:
Schon jetzt kennt Excel dieses Makro, wovon man sich leicht überzeugen kann. (Im Menü <Extras>/<Makro>/<Makros...> wählen.)
Nun werden in diese Prozedur die folgenden Programmaktivitäten hinein komponiert.
Wenn das System so platziert ist, wie oben gezeigt, kann man darauf zugreifen, ohne es erst markieren zu müssen.
Zuerst müssen wir feststellen, wie viele Gleichungen und wie viele Unbekannte es im Gleichungssystem gibt. Dazu könnte man natürlich eine Variableneingabe organisieren. Aber wenn schon Programmhilfe, dann richtig. Excel soll das anhand unserer eingetragenen Wertematrix ruhig allein herausbekommen. Wir prüfen, wie viele beschriebene Spalten und Zeilen vorhanden sind. Bei Unstimmigkeiten müssen wir natürlich die Rechnung unterbinden.
| Dim zeile As Integer, spalte As Integer, grad As Integer 'Wie groß ist das System? zeile = 2 spalte = 2 While Cells(zeile, spalte).Text <> Empty spalte = spalte + 1 Wend spalte = spalte - 1 While Cells(zeile, spalte).Text <> Empty zeile = zeile + 1 Wend zeile = zeile - 1 'Stimmt die Zahl der Gleichungen? If zeile <> spalte - 1 Then MsgBox "Fehlerhafte Auswahl, bitte überprüfen Sie die Zahl der Variablen bzw. der Gleichungen!" Exit Sub End If 'Nun ist der Grad n des GS bestimmbar! grad = zeile - 1 |
Bisschen unklar mit den 1en?
Ja, das sind die Ränder, die wir jeweils berücksichtigen müssen. Vornehmerweise könnte man denen Variablennamen verpassen, dann wären sie variabel, aber brauchen wir das?
Wenn der Grad des Gleichungssystems bekannt ist, können wir die Werte in eine Matrix einlesen, die wir natürlich zuerst dimensionieren müssen. Deshalb fügen wir hinter die erste Dim-Zeile ein:
| Dim A(15, 16) As Double, x(15) As Double Dim i As Integer, j As Integer Dim b As Double |
Nun haben wir Platz für die Koeffizienten von 15 Gleichungen mit 15 Unbekannten, die wir in x() anzeigen können. Falls es doch lieber 150 sein sollen, müssen an dieser Stelle die Werte entsprechend verändert werden. Die anderen Variablen werden auch benötigt.
Denn nun wird vorbereitet und sogleich gerechnet:
| 'Werte einlesen und GS lösen For i = 1 To grad For j = 1 To grad + 1 A(i, j) = Cells(1 + i, 1 + j) Next Next gauss grad, A, x |
Hallo, mechanische Abschreiber! Testet doch mal! Die letzte Zeile bedeutet den Aufruf einer Prozedur, die es nicht gibt.
Wir brauchen eine Prozedur, die ein lineares Gleichungssystem löst und wissen, da gibt es doch diesen Gauss-Algorithmus. Ich, der Autor werde schnell fündig in meinem Visual-Basic-Kurs und hole mir von dort die gewünschte Prozedur:
| Sub gauss(n%, A() As Double, x() As Double) Dim i%, j%, k%, jmax%, kmax%, merk() As Integer Dim s As Double, max As Double, skal() As Double ReDim merk(n), skal(n) 'Reihenfolge sichern For i = 1 To n merk(i) = i Next 'Normalisierung For i = 1 To n s = 0 For j = 1 To n s = s + Abs(A(i, j)) Next skal(i) = 1 / s Next 'Vorwärtselimination For k = 1 To n - 1 max = skal(k) * Abs(A(k, k)) kmax = k'Spalte mit max jmax = k'Zeile mit max For j = k To n'Pivotzelle suchen For i = k To n If skal(j) * Abs(A(j, i))> max Then jmax = j kmax = i max = skal(j) * Abs(A(j, i)) End If Next Next If jmax <> k Then 'Zeilentausch, wenn nötig For j = k To n + 1 s = A(k, j) A(k, j) = A(jmax, j) A(jmax, j) = s Next s = skal(k) skal(k) = skal(jmax) skal(jmax) = s End If If kmax <> k Then'Spaltentausch, wenn nötig For i = 1 To n s = A(i, k) A(i, k) = A(i, kmax) A(i, kmax) = s Next j = merk(k) merk(k) = merk(kmax) merk(kmax) = j End If 'eigentliche Elimination For i = k + 1 To n s = A(i, k) / A(k, k) A(i, k) = 0# For j = k + 1 To n + 1 A(i, j) = A(i, j) - s * A(k, j) Next Next Next 'Auflösung rückwärts x(merk(n)) = A(n, n + 1) / A(n, n) For i = n - 1 To 1 Step -1 s = A(i, n + 1) For j = i + 1 To n s = s - A(i, j) * x(merk(j)) Next x(merk(i)) = s / A(i, i) Next End Sub |
Die wird natürlich nicht mehr in, sondern hinter Sub Gleichung() gesetzt!
Nun sollte, nach Ausführung des Programms in x() unsere Lösung liegen. Deshalb muss noch eine Ausgabe in die Sub Gleichung() eingefügt werden. Außerdem soll sogleich eine Probe-Rechnung mit aufgenommen werden.
| 'Ergebnisse und Probe zeigen For i = 1 To grad Cells(1 + grad + 1, 1 + i) = Format(x(i), "Standard") Next For i = 1 To grad b = 0 For j = 1 To grad b = b + Cells(1 + i, 1 + j).Value * x(j) Next Cells(1 + i, 1 + grad + 2) = Format(b, "Standard") Next |
Jetzt stehen die x-Werte unterhalb der Gleichung selbst in den richtigen Spalten, die Probewerte direkt neben den ehemaligen rechten Seiten, sogar alles schön formatiert, damit wir nicht von Nachkommastellen erschlagen werden. Wir sind fertig, aber so richtig gut sieht das alles trotzdem nicht aus. Auch käme nun bei neuem Start des Makros "Gleichung" Unsinn heraus.
Man könnte ja, etwa die Ausgabezellen rot unterlegen. Solche roten Zelle kann man evtl. auch später brauchen, um eine Lösung sauber wieder entfernen zu können.
Wie macht man das aus dem Programm heraus? Wenn einem der Code für so eine Aktivität nicht über den Weg läuft, was dann? Kleiner Tipp: Man nutze den Makrorekorder: <Extras></Makro>/<Aufzeichnen...>. Also nur starten, tun, was man tun möchte, dann so tun, als wolle man das Makro bearbeiten. Schon liegt der Code vor einem, mit den passenden Schlüsselwörtern. Die heißen hier, abgesehen von der vorherigen Markierung einer Zelle:
Nun kann der Codeschnipsel zur Ausgabe noch etwas korrigiert werden. Daß die nicht betroffenen Teile der Tabelle auch angehübscht werden können, versteht sich ja von selbst.
| 'Ergebnisse und Probe zeigen For i = 1 To grad Cells(1 + grad + 1, 1 + i).Interior.ColorIndex = 3 Cells(1 + grad + 1, 1 + i) = Format(x(i), "Standard") Next Cells(1 + grad + 1, 1 + i) = "<Lösungen" Cells(1 + grad + 1, 2 + i) = "^ Probe" For i = 1 To grad b = 0 For j = 1 To grad b = b + Cells(1 + i, 1 + j).Value * x(j) Next Cells(1 + i, 1 + grad + 2).Interior.ColorIndex = 3 Cells(1 + i, 1 + grad + 2) = Format(b, "Standard") Next |
Dies ist das Ergebnis nach der letzten Operation.
Das wär es ... fast.
Es gibt noch Arbeit:
Zuerst soll nun die Bedienbarkeit gesichert werden. Dazu wird ein Button auf der Tabelle benötigt. Den holt man, indem man mit <Ansicht>/<Symbolleisten>/<Formular> einen Werkzeugkasten holt. Daraus übernimmt man einen Button, dem man anschließend das zu startende Makro zuweist:
Also auf "Gleichung" klicken und "OK" geben.
Wenn man den neuen Button ausgiebig testet, passiert gleich beim zweiten Klick ein Unglück: Durch die erste Berechnung ist ja der belegte Tabellenbereich größer geworden. Das Probieren mit dem Gleichungssystem wird dadurch erheblich erschwert. Wenn man seine Excel-Anwendung speichert, würde die Lösung mit ihren roten Balken stehen bleiben und bei neuer Verwendung im Wege sein. Deshalb brauchen wir nun die Löschroutine, die diese Ränder samt Inhalt beseitigt:
Wozu wirklich? An sich ist man ja froh, wenn die Lösung vorliegt. Bloß, der zweite Druck auf den Rechne-Button schafft schon Probleme, wie oben festgestellt. Auch wenn man mit dem Gleichungssystem etwas experimentieren will, braucht man ihn.
Also gibt es ein neues Makro:
| Sub Löschen() Dim spalte As Integer, grad As Integer, i As Integer, muss As Boolean 'müssen wir etwas Löschen? muss = False For i = 2 To 17 If Cells(1, i).Interior.ColorIndex = 3 Then muss = True Next If muss Then spalte = 2 While Cells(2, spalte).Text <> Empty spalte = spalte + 1 Wend grad = spalte - 4 Cells(1 + grad + 1, 1).Interior.ColorIndex = 4 Cells(1 + grad + 1, 1) = "" For i = 1 To grad + 2 Cells(1 + grad + 1, 1 + i).Interior.ColorIndex = 0 Cells(1 + grad + 1, 1 + i) = "" Next Cells(1, 1 + grad + 2).Interior.ColorIndex = 4 Cells(1, 1 + grad + 2) = "" For i = 1 To grad Cells(1 + i, 1 + grad + 2).Interior.ColorIndex = 0 Cells(1 + i, 1 + grad + 2) = "" Next End If End Sub |
Hier wird nicht einfach gelöscht, sondern nur dann, wenn es etwas zu Löschen gibt. Deshalb die anfängliche Abfrage und die nur bedingte Löschung. Umgekehrt darf nur dann neu gerechnet werden, wenn zuvor noch nicht gerechnet oder aber gerade gelöscht wurde. Deshalb muss das Löschmakro am Beginn des Makros "Gleichung" eingefügt werden.
Um eventuelle Probierarbeit zu erleichtern und um das Tabellenblatt "sauber", also ohne rot unterlegte Lösungsfelder speichern zu können, erhält die Löschroutine einen eigenen Button.
Nun sind alle Erfordernisse befriedigt und Fehlbedienungen der beiden Makros nicht mehr möglich.
Was nun noch zu tun bleibt, ist die Sicherung des Eingabebereiches. Dazu gleich mehr.
Die Sicherung von Excelprojekten soll die Bedienung durch Uneingewiesene Personen ermöglichen, ohne daß Schaden entstehen kann. Es wäre wünschenswert, nur den Bereich der Gleichung beschreibbar zu machen, während der Rest der Tabelle geschützt, also unveränderlich ist.
Der Schutz wird bei Excel im Menü <Extras>/<Schutz>/<Blattschutz ...> gesetzt oder aufgehoben. Man kann die Bereiche, die veränderlich bleiben sollen, zuvor aus dem Schutz ausnehmen. Dazu müssen sie markiert werden. Dann kann man unter <Format>/<Zellen>/<Sicherheit> diese Einstellung vornehmen.
Leider sind auch die vom Schutz ausgenommenen Zellen bei eingeschaltetem Blattschutz zwar beschreibbar und löschbar, aber nicht mehr formatierbar. Im geschützten Zustand der Tabelle funktioniert die hier vorgesehene Einfärbung bzw. Einfärbung der Zellen nicht mehr.
Ausweg:
 |  | Kurs-Unterlagen Informatik © Rolf Hirte, zuletzt bearbeitet: 30.11.2003 |