Definition eigener Funktionen (ohne Makros!)
Hauke
Ich muss eine größere Tabelle pflegen, die etwa so aufgebaut ist:
Datum Abteilung A B C D E F
Jan 09 Abt. 1 100 500 1
Jan 09 Abt. 2 200 400 2
Jan 09 Abt. 3 300 300 3
Jan 09 Abt. 4 400 200 4
Feb 09 Abt. 1 100 500 1
Feb 09 Abt. 2 200 400 2
Feb 09 Abt. 3 300 300 3
Feb 09 Abt. 4 400 200 4
A und C enthalten bestimmte von Hand eingegebene Werte. B soll nun ab Dezember 09 die Summe der Werte aus A der letzten 12 Monate für eine Abteilung ausweisen. Vorher soll da halt noch nichts drin stehen. Das gleiche gilt auch für D (mit den Werten aus C). ==> Dafür verwende ich das selbstgeschriebene Makro "SUMME12MONATE".
E könnte man "Jahresendziel" nennen. In F wird dann zwischen dem Wert für eine Abteilung aus dem letzten Dezember und dem Jahresendziel für einen Monat linear berechnet. ==> Dafür verwende ich das selbstgeschriebene Makro "ZIELKALKULATION".
Die Makros sehen so aus:
Option Explicit
'Die Anzahl an Zeilen, die die Spaltenüberschriften benötigen
Public Const HeaderRowCount As Long = 1
' Die Anzahl an Zeilen, die ein Monatsblock hat ==> Anzahl der Abteilungen
Public Const BlockRowCount As Long = 4
Public Function SUMME12MONATE(Zelle As Range) As Variant
SUMME12MONATE = ""
' Teste, ob die Zelle bereits ein Jahr vorherige Daten hat...
If Zelle.Row > BlockRowCount * 12 + HeaderRowCount Then
' Wenn das der Fall ist, summiere die Daten der letzten 12 Monate auf.
SUMME12MONATE = CCur(0)
Dim I As Long
For I = 0 To 11
SUMME12MONATE = SUMME12MONATE + CCur(Cells(Zelle.Row - I * BlockRowCount, Zelle. _
Column))
Next
End If
End Function
Public Function ZIELKALKULATION(Datumszelle As Range, Jahresendzielzelle As Range) As Variant
'Entspricht ungefähr =INDIREKT("Z"&zeileletzterdezember&"S"&SPALTE(AI49);FALSCH)+(INDIREKT("Z"&( _
_
'#BEZUG!+20*12)&"S"&SPALTE(AI49);FALSCH)-INDIREKT("Z"BEZUG!&"S"&SPALTE(AI49);FALSCH))*
'(MONAT($A49)/12)
'...wobei zeileletzterdezember für die Zeilennummer steht, in der die Daten aus dem letzten _
Dezember für eine Abteilung stehen.
ZIELKALKULATION = ""
' Teste, ob die Zelle bereits ein Jahr vorherige Daten hat...
If Jahresendzielzelle.Row > BlockRowCount * 12 + HeaderRowCount Then
' Ermittle Position der Zelle vom letzten Dezember
Dim lastYearRow As Long
lastYearRow = Jahresendzielzelle.Row - BlockRowCount * Month(Datumszelle)
Dim lastYearTarget As Double
lastYearTarget = Cells(lastYearRow, Jahresendzielzelle.Column)
Dim nextYearTarget As Double
nextYearTarget = Jahresendzielzelle
ZIELKALKULATION = lastYearTarget + (nextYearTarget - lastYearTarget) * Month( _
Datumszelle) / 12
End If
End Function
Das funktioniert beides auch wunderbar, da jedoch meine Tabelle halt sehr viele Einträge enthält, dauert die Neuberechnung aller Zellen (die z.B. beim Einsatz von Autofiltern über Abteilungen von Excel schon recht häufig angestoßen wird) sehr lange.
Um die Lesbarkeit der Tabelle zu erhöhen und - was auch sehr wichtig ist - die Möglichkeit, problemlos Monatsblöcke per Kopieren/Einfügen am unteren Rand anzufügen, hatte ich die Idee, meine Makros mit Excel-Funktionen (ohne VBA) nachzubilden, diese jedoch irgendwie woanders zu definieren. Also so, dass in den Zellen tatsächlich weiter =ZIELKALKULATION(A49;AK49) statt sowas wie =INDIREKT("Z"&zeileletzterdezember&"S"&SPALTE(AI49);FALSCH)+(INDIREKT("Z"&(#BEZUG!+20*12)&"S"&SPALTE(AI49);FALSCH)-INDIREKT("Z"BEZUG!&"S"&SPALTE(AI49);FALSCH))*(MONAT($A49)/12) drin steht.
Hat jemand Tipps, wie ich so etwas umsetzen kann oder alternativ die Performance meiner VBA-Makros verbessern kann?
Vielen Dank für Antworten bereits im Voraus und viele Grüße aus dem hohen Norden! :-)
Hauke
Anzeige