Funktion für Quantildurchschnitt

Bild

Betrifft: Funktion für Quantildurchschnitt
von: VB 5000
Geschrieben am: 24.07.2015 15:30:07

Hallo zusammen!
Ich versuche folgendes: ich moechte eine Funktion in VBA programmieren, in die ich einen Bereich (in der Regel eine Spalte) sowie ein Quantil (z.B. 10% fuer das obere 10% Quantil) eingeben kann und welche mir dann fuer die Werte im ausgewaehlten Bereich den Mittelwert aller Werte im oberen 10%-Quantil ausgibt.
Meine Idee ist: ich zaehle wieviele Werte im Bereich ueberhaupt sind (z.B. 190). Berechne dann von der Anzahl den entsprechenden Quantilswert (z.B. 10%, also hier: 19) und schreibe dann den Wert der Funktion KGRÖßTE fuer k= 190-19=171, 190-18=172, 190-17=173 usw. in einen Vektor. Anschließend lasse ich mir den Mittelwert fuer alle Eintraege im Vektor ausgeben.
Mein Code sieht bisher wie folgt aus:

Function UpperTailMean(Bereich As Range, Quantil As Integer) As Double 
    Dim n As Integer
    Dim i As Integer
    Dim start As Integer
    Dim taillength As Integer
    Dim vektor() As Double
    
   
    
    Debug.Print "test1"
    
    n = Application.WorksheetFunction.Count(Bereich)
    taillength = Application.WorksheetFunction.Round((Quantil / 100) * n, 0)        
    start = n - taillength + 1       
    ReDim vektor(taillength + 1)
        
  
    Debug.Print "test2"
    
   
    For i = 1 To taillength 
        vektor(i) = Application.WorksheetFunction.Large(Bereich, start + i)
    Next i
    
        
UpperTailMean = Application.WorksheetFunction.Average(vektor) 
   
      
End Function

Aufruf: debug.Print UpperTailMean(Application.Workbooks("Test.xlsm").Worksheets("Tabelle1").Range("A3:A210"),10)
Fehler: Laufzeitfehler 9 : Index außerhalb des gültigen Bereichs
Wer kann mir weiterhelfen?

Bild

Betrifft: AW: Funktion für Quantildurchschnitt
von: Michael
Geschrieben am: 26.07.2015 18:14:48
Hi VB 5000,
ich würde mal sagen, in der Zeile

vektor(i) = Application.WorksheetFunction.Large(Bereich, start + i - 1)

mußt Du "-1" einfügen, sonst kommst Du aus dem Bereich raus.
Ich habe keine Ahnung, was Du da treibst, aber so, wie es codiert ist, bekommst Du die *kleinsten*, nicht die größten Werte.
Außerdem beginnt ein Array standardmäßig mit der Position 0, und Du dimensionierst es mit ReDim vektor(taillength + 1) auch noch auf eine Stelle "oben" mehr - d.h. Dein Code rechnet beim Mittelwert zwei Nullen mit.
Richtig wäre es denn so (wenn ich die Geschichte richtig verstanden habe):
Function UpperTailMean(Bereich As Range, Quantil As Integer) As Double
    Dim n As Integer
    Dim i As Integer
    Dim start As Integer
    Dim taillength As Integer
    Dim vektor() As Double
    
   
    
    Debug.Print "test1"
    
    n = Application.WorksheetFunction.Count(Bereich)
    taillength = Application.WorksheetFunction.Round((Quantil / 100) * n, 0)
    start = n - taillength + 1
    ReDim vektor(taillength - 1) ' hier minus 1!
        
  
    Debug.Print "test2"
    
    Stop
   
    For i = 1 To taillength      ' kleinste Werte
        vektor(i - 1) = Application.WorksheetFunction.Large(Bereich, start + i - 1)
    Next i
    Debug.Print "kleinste: " & Application.WorksheetFunction.Average(vektor)
    For i = 1 To taillength       ' größte Werte
        vektor(i - 1) = Application.WorksheetFunction.Large(Bereich, i)
    Next i
    Debug.Print "größte: " & Application.WorksheetFunction.Average(vektor)
    Debug.Print "u: " & UBound(vektor) & " l: " & LBound(vektor)
UpperTailMean = Application.WorksheetFunction.Average(vektor)
   
End Function
Sub aufrufen()
Debug.Print UpperTailMean(Range("b2:b53"), 10)
' habe da schnell die Werte von =runden(zufallszahl()*100;0) reingesetzt
End Sub
Happy Exceling,
Michael

Bild

Betrifft: AW: Funktion für Quantildurchschnitt
von: VB 5000
Geschrieben am: 27.07.2015 09:40:27
perfekt, vielen Dank! muss mit den +/- 1 durcheinander gekommen sein

Bild

Betrifft: freut mich, danke für die Rückmeldung
von: Michael
Geschrieben am: 27.07.2015 16:34:27
kann passieren...
Hilfreich isses immer, den Code mit F8 schrittweise durchzugehen und mal die Maus über die Variablen zu ziehen; dann sieht man die Werte, die sie jeweils (pro Schleifendurchgang) enthalten.
Happy Exceling,
Michael

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Funktion für Quantildurchschnitt"