AW: Mittelwert aus mehreren Spalten bilden
18.06.2007 14:52:00
ingUR
Hallo, Tobias,
was Du da berechnen willst, ist als "gleitender Durchnitt" (Average) bekannt.
Neben den hier bereits vorgestellten funktionierenden Lösungen, habe ich hier noch eine Zellenfunktionslösung, die ich in ihrer Anwendung der Zellenformel Mittelwert unter Einsatz der BEREICH.VERSCHIEBEN-Funktion vorstelle:
|
| |
| | A | B | C | D | | 1 | | | | | | 2 | Datum | Close | | | | 3 | 02.05.07 | 7.455,93 | | | | 4 | 03.05.07 | 7.476,69 | | | | 5 | 04.05.07 | 7.516,76 | | | | 6 | 07.05.07 | 7.525,69 | | | | 7 | 08.05.07 | 7.442,20 | 7.483,45 | 7.483,45 | | Formeln der Tabelle | C3 : =WENN(ZEILE()<7;"";GD(B3;5))
D3 : =WENN(ZEILE()<7;"";MITTELWERT(BEREICH.VERSCHIEBEN(B3;-4;0;5;1):B3))
C4 : =WENN(ZEILE()<7;"";GD(B4;5))
D4 : =WENN(ZEILE()<7;"";MITTELWERT(BEREICH.VERSCHIEBEN(B4;-4;0;5;1):B4))
C5 : =WENN(ZEILE()<7;"";GD(B5;5))
D5 : =WENN(ZEILE()<7;"";MITTELWERT(BEREICH.VERSCHIEBEN(B5;-4;0;5;1):B5))
C6 : =WENN(ZEILE()<7;"";GD(B6;5))
D6 : =WENN(ZEILE()<7;"";MITTELWERT(BEREICH.VERSCHIEBEN(B6;-4;0;5;1):B6))
C7 : =WENN(ZEILE()<7;"";GD(B7;5))
D7 : =WENN(ZEILE()<7;"";MITTELWERT(BEREICH.VERSCHIEBEN(B7;-4;0;5;1):B7))
|
|
| |
Die dazugehörige Funktionsprozedur kannst Du in den Standardmodulordner des VBA-Projekts legen:
Option Explicit
Const HEADERROWS As Long = 2
Function GD(ByRef rngC As Range, nView As Integer) As Double
Dim Spalte As Integer, Zeile As Long
Zeile = rngC.Row
If Zeile - HEADERROWS Je nach anzahl der Kopfzeilen über Deiner Tabelle kann der Konstanten HEADERROWS der entsprechende Wert zugewiiesen werden.
Die Anzahl der Werte mit denen der gleitende Durchnitswert berechnet wird, wird in der Zellenformel als zweiter Parameter der Funktion übergeben. Wegen der Flexibilität in der Anzahlgröße ist die WENN-Abfrage erforderlich, es sei den, mann möchte den mittelwert im Anfangsbereich mit der Anzahl der Werte ermitteln, die zur verfügung stehen, bis dann nur noch der Festwert anzuwenden ist:
GD(Datenspalte; MIN(10;Zeile()-Anzahl der Kopfzeilen))
z.B.
=GD(B6;MIN(5;ZEILE()-ZEILE($A$2)))
Wenn dieses Ergebnis gewünscht ist, dann wäre es auch möglich, dieses direkt in der Funktionprozedur zu berücksichtigen:
Option Explicit
Const HEADERROWS As Long = 2
Function GD(ByRef rngC As Range, nView As Integer) As Double
Dim Spalte As Integer, Zeile As Long
Zeile = rngC.Row
Spalte = rngC.Column
GD = Application.WorksheetFunction _
.Average(Range(Cells( Application.Max(HEADERROWS + 1, Zeile - nView + 1), _
Spalte), Cells(Zeile, Spalte)))
End Function
Die Zellenformel vereinfacht sich zu: =GD(B3;5)
Gruß,
Uwe