Microsoft Excel

Herbers Excel/VBA-Archiv

VBA Formel | Herbers Excel-Forum


Betrifft: VBA Formel von: Horst
Geschrieben am: 04.01.2012 00:24:24

Liebe Excel-Freunde!

Wie muss ich folgende Function umschreiben, damit der für die Formelberechnung erforderliche Parameter Mittelwert des Bereichs (Range) automatisch errechnet wird und das Formelergebnis durch Eingabe "SEMIVOLA(BEREICH)" erfolgt und nicht wie bisher durch "SEMIVOLA(BEREICH;MITTELWERT)?

Public Function semivola(Bereich As Range, Mittelwert As Double) As Double

Dim Anzahl%, varianz As Double, varianzsumme As Double, zelle

With Application.WorksheetFunction

Anzahl = .Count(Bereich)

varianzsumme = 0
For Each zelle In Bereich
If .IsNumber(zelle) = True Then
If zelle < Mittelwert Then
varianz = (zelle - Mittelwert) ^ 2
varianzsumme = varianzsumme + varianz
End If
End If
Next
semivola = (varianzsumme / Anzahl) ^ 0.5
End With
End Function
Besten Dank für Eure Vorschläge!

Gruß, Horst

  

Betrifft: AW: VBA Formel von: Josef Ehrensberger
Geschrieben am: 04.01.2012 00:49:22


Hallo Horst,

das geht aber auch ohne UDF.

{=(SUMME(WENN((A1:A20<MITTELWERT(A1:A20))*(ISTZAHL(A1:A20));((A1:A20-MITTELWERT(A1:A20))^2))) /ANZAHL(A1:A20)) ^0,5}

Public Function semivola(Bereich As Range) As Double
  Dim Anzahl%, varianz As Double, varianzsumme As Double
  Dim zelle As Range, Mittelwert As Double
  
  With Application.WorksheetFunction
    
    Anzahl = .Count(Bereich)
    Mittelwert = .Average(Bereich)
    
    For Each zelle In Bereich
      If .IsNumber(zelle) = True Then
        If zelle < Mittelwert Then
          varianz = (zelle - Mittelwert) ^ 2
          varianzsumme = varianzsumme + varianz
        End If
      End If
    Next
    semivola = (varianzsumme / Anzahl) ^ 0.5
  End With
End Function






« Gruß Sepp »



  

Betrifft: AW: VBA Formel von: Horst
Geschrieben am: 04.01.2012 13:11:36

Besten Dank für Eure Vorschläge, beide Varianten funktionieren großartig!


  

Betrifft: AW: VBA Formel von: Horst
Geschrieben am: 04.01.2012 22:10:07

Einen Punkt hätte ich noch: Wenn nun angenommen nur jene Zeilen der Spalte A in die Berechnung einfließen sollen, bei denen in Spalte B ein bestimmter Buchstabe steht, wie würde dann die unten angegebene Formel aussehen?

{=(SUMME(WENN((A1:A20<MITTELWERT(A1:A20))*(ISTZAHL(A1:A20));((A1:A20-MITTELWERT(A1:A20))^2))) /ANZAHL(A1:A20)) ^0,5}

Besten Dank vorab!


  

Betrifft: AW: VBA Formel von: Josef Ehrensberger
Geschrieben am: 04.01.2012 22:14:51


Hallo Horst,

einfach die Bedingung mit rein nehmen.

{=(SUMME(WENN((A1:A20<MITTELWERT(A1:A20))*(ISTZAHL(A1:A20))*(B1:B20="X"); ((A1:A20-MITTELWERT(A1:A20))^2))) /ANZAHL(A1:A20)) ^0,5}


« Gruß Sepp »



  

Betrifft: AW: VBA Formel von: Horst
Geschrieben am: 05.01.2012 23:22:58

Hallo Sepp,

ich glaube, damit das Ergebnis der Formel stimmt, müsste auch bei der Mittelwert- und Anzahl-Funktion die Bedingung (B1:B20="X") berücksichtigt werden, oder geschieht das ohnehin bereits durch die Bedingung in der Wenn-Abfrage? Ich komme nämlich mit der unten angegebenen VBA-Funktion (über jene Zeilen von A berechnet, bei denen in B ein X steht) und mit der obigen Formel auf unterschiedliche Ergebnisse.

lg, Horst

Hier nochmal die VBA-Funktion als Alternative zur Formel (leider bei großen .xls sehr langsam):

Public Function semivola(Bereich As Range) As Double
Dim Anzahl%, varianzsumme As Double, zelle As Range, Mittelwert As Double
With Application.WorksheetFunction
  Anzahl = .Count(Bereich)
  Mittelwert = .Average(Bereich)
  For Each zelle In Bereich
    If .IsNumber(zelle) = True Then
      If zelle < Mittelwert Then
        varianzsumme = varianzsumme + (zelle - Mittelwert) ^ 2
      End If
    End If
  Next
End With
semivola = (varianzsumme / Anzahl) ^ 0.5
End Function



  

Betrifft: AW: VBA Formel von: Josef Ehrensberger
Geschrieben am: 05.01.2012 23:45:05


Hallo Horst,

da magst du Recht haben.

{=(SUMME(WENN((A1:A20<MITTELWERT(A1:A20))*(ISTZAHL(A1:A20))*(B1:B20="X"); ((A1:A20-MITTELWERT(WENN(B1:B20="x";A1:A20)))^2)))/ANZAHL(WENN(B1:B20="X";A1:A20))) ^0,5}

Allerdings wirst du mit der UDF immer ein anderes Ergebnis erhalten, weil dort die Zusatzbedingungen nicht erfasst werden.




« Gruß Sepp »



  

Betrifft: AW: VBA Formel von: Horst
Geschrieben am: 06.01.2012 12:58:33

Servus Sepp,

Ergebnis stimmt! Besten Dank nochmal!

Gruß, Horst


  

Betrifft: AW: VBA Formel von: Reinhard
Geschrieben am: 04.01.2012 00:49:28

Moin Horst,

ungetestet vielleicht so:

Public Function semivola(Bereich As Range) As Double
Dim Anzahl%, varianzsumme As Double, zelle As Range, Mittelwert As Double
With Application.WorksheetFunction
  Anzahl = .Count(Bereich)
  Mittelwert = .Average(Bereich)
  For Each zelle In Bereich
    If .IsNumber(zelle) = True Then
      If zelle < Mittelwert Then
        varianzsumme = varianzsumme + (zelle - Mittelwert) ^ 2
      End If
    End If
  Next
End With
semivola = (varianzsumme / Anzahl) ^ 0.5
End Function

Gruß
Reinhard


Beiträge aus den Excel-Beispielen zum Thema "VBA Formel"