Formelgenerator
Nachdem man Kurslisten mit Aktienkursen oder ähnliche Daten vom Internet eingelesen hat stehen einem die ganzen Möglichkeiten von Excel zur Verfügung. Man kann jetzt eigene Berechnungen durchführen und diese immer wieder mit den Daten im Internet aktualisieren. Das spannende an dieser Geschichte ist natürlich die Anwendung eigener Formeln. Hierzu ist ein Formelgenerator ein nützlicher Helfer, der im folgenden näher vorgestellt wird.
Angenommen man hat eine Kursliste nach folgender Form:
| A | B | C | D | E | F | G | |
| DAX | |||||||
| Datum | Open | High | Low | Close | Eigene Formel: Hoch - Tief |
Formel in Spalte F | |
| 3 | 01.02.2007 | 6821,31 | 6874,77 | 6820,58 | 6851,28 | 54,19 | C3-D3 |
| 4 | 02.02.2007 | 6853,77 | 6904,88 | 6848,17 | 6885,76 | 56,71 | C4-D4 |
| 5 | 05.02.2007 | 6878,67 | 6891,13 | 6864,19 | 6874,06 | 26,94 | C5-D5 |
| 6 | 06.02.2007 | 6881,9 | 6906,68 | 6862,19 | 6875,7 | 44,49 | C6-D6 |
| 7 | 07.02.2007 | 6886,01 | 6924,46 | 6885,08 | 6915,56 | 39,38 | C7-D7 |
| 8 | 08.02.2007 | 6911,24 | 6918,04 | 6849,85 | 6876,73 | 68,19 | C8-D8 |
| 9 | 09.02.2007 | 6890,65 | 6928,98 | 6890,64 | 6911,11 | 38,34 | C9-D9 |
| 10 | 12.02.2007 | 6892,03 | 6892,03 | 6844,39 | 6859,45 | 47,64 | C10-D10 |
| 11 | 13.02.2007 | 6868,68 | 6895,52 | 6866,07 | 6895,34 | 29,45 | C11-D11 |
| 12 | 14.02.2007 | 6912,56 | 6965,39 | 6907,91 | 6961,18 | 57,48 | C12-D12 |
| 13 | 15.02.2007 | 6958,14 | 6972,44 | 6942,25 | 6958,62 | 30,19 | C13-D13 |
| 14 | 16.02.2007 | 6955,71 | 6979,02 | 6941,03 | 6957,07 | 37,99 | C14-D14 |
| 15 | 19.02.2007 | 6967,53 | 6996,01 | 6965,91 | 6987,08 | 30,1 | C15-D15 |
| 16 | 20.02.2007 | 6978,52 | 6987,67 | 6936,08 | 6982,91 | 51,59 | C16-D16 |
| 17 | 21.02.2007 | 6999,18 | 7005,34 | 6922,36 | 6941,66 | 82,98 | C17-D17 |
| 18 | 22.02.2007 | 6959,37 | 7012,34 | 6955,57 | 6973,73 | 56,77 | C18-D18 |
| 19 | 23.02.2007 | 6986,07 | 6998,27 | 6964,5 | 6992,58 | 33,77 | C19-D19 |
| 20 | 26.02.2007 | 6992,12 | 7040,2 | 6980,25 | 7027,59 | 59,95 | C20-D20 |
In unserem Beispiel soll also die Formel (Hoch-Tief) in Spalte F implementiert werden. Die übliche Vorgehenseise besteht darin, dass man in Zelle F3 die hierfür erforderliche Formel einträgt, also "=C3-D3". Nun markiert man die Zelle F3 und kopiert bei gedrückter Maus die Formel nach unten, bis man das Ende der Tabelle erreicht. Beim Loslassen der Maus werden dann alle Formeln berechnet und die Tabelle automatisch aktualisiert. Diese Methode hat einen entscheidenden Nachteil: Wenn die Tabelle Kurse über einen längeren Zeitraum enthält, können schon mal mehrere tausend Zellen zusammenkommen. Dann ist die eben vorgestellte Methode nervig und dauert relativ lange. Man kann dies aber dank VBA automatisieren.
Formel automatisch füllen
Das obige Beispiel kann noch verfeinert werden, indem man die Formel, die kopiert werden soll in die entsprechende Zelle schreibt, anschliessend diese Zelle selektiert und dann einfach über einen Mausklick das Makro zum Befüllen der Formel startet.
 |
Makro:
Public Sub FillFormula()
Set ws = Worksheets("Tabelle1")
nmax = ws.Cells(Rows.count, 1).End(xlUp).Row
col = ActiveCell.Column
zielspalte$ = Chr$(64 + col)
Curcel$ = ActiveCell.Address
Selection.AutoFill Destination:=Range(Curcel$ & ":" & zielspalte$ & nmax),
Type:=xlFillDefault
End Sub
Formelgenerator
Eine weitere Möglichkeit im VBA Code Formeln zu generieren ist noch flexibler und kann ohne vorherige Eingabe der Formel und Selektion der Zelle implementiert werden.
Dazu definiert man zuerst die zu berechnende Formel und schreibt diese in eine Variable:
Formel = "=C3-D3"
Anschliessend ermittelt man den Start und das Ende der Zeilen, für die das Kopieren der Formel gültig sein soll.
nstart=3
nmax=20
Nun fehlt noch die Zielspate der Formel, hier Spalte F:
ZielSpalte = "F"
Zu guter letzt kann man das Kopieren der Formel automatisieren:
Worksheets("Tabelle1").Range(ZielSpalte & nstart & ":" & ZielSpalte & nmax).FormulaLocal = Formel
Beispiel
Im folgenden Beispiel wird in Spalte G der Gleitende Durschnitt der letzten drei Schlusskurse berechnet. Dazu kann folgendes Makro eingesetzt werden:
Private Sub Formelgenerator()
Formel = "=(E5+E4+E3)/3"
nstart = 5
nmax = 20
ZielSpalte = "F"
Worksheets("Tabelle1").Range(ZielSpalte & nstart & ":" & ZielSpalte & nmax).FormulaLocal
= Formel
End Sub