Comment faire une moyenne en VBA sans tenir compte des zéros

Question

Bonjour à tous,

J'ai un userform, avec une série de textbox, qui s'adapte en fonction des relevés à faire. J'ai une boucle qui stocke dans une variable tableau à une dimension les valeurs des toutes les textbox (y compris les textbox masquées) et qui enregistre des zéros si les textbox sont vide.

J'arrive à faire des moyenne sur certaines "plages" de la variable tableau, mais le résultat est faussé à cause des zéros. J'utilise pour ça " Application.WorksheetFunction.Average". J'ai vu qu'il existe "Application.WorksheetFunction.AverageIf" qui doit pouvoir me permettre de mettre une condition pour exclure les zéros.

Quelqu'un peut-il m'expliquer, si cela est faisable, la façon de faire en conservant la variable tableau ?

Ma seconde question concerne ce qui suit :

Tabl4(i) est ma variable tableau (VARIANT)

Tablo4(42) = Application.WorksheetFunction.AverageIf(Tablo4(18), Tablo4(19), Tablo4(20), Tablo4(21), Tablo4(22), Tablo4(23), Tablo4(24), Tablo4(25))

Est-il possible regrouper les valeurs Tablo4(18) à Tablo4(25) un peu comme un Range (Tablo4(18) : Tablo4(25))?

Answer 1

Bonjour

Pour la 1ere question : Création d'une fonction MoyenneDifZ

Sub Question1()
    Dim vTab As Variant
    
    vTab = Array(10, 0, 20)
    Debug.Print MoyenneDifZ(vTab)
End Sub

Function MoyenneDifZ(varTab) As Single
    Dim lg          As Long
    Dim dblSomme    As Double
    Dim lgCptVal    As Long
    
    For lg = LBound(varTab) To UBound(varTab)
        If IsNumeric(varTab(lg)) Then
            dblSomme = dblSomme + varTab(lg)
            If varTab(lg) <> 0 Then lgCptVal = lgCptVal + 1
        End If
    Next
    MoyenneDifZ = dblSomme / lgCptVal
End Function

Pour la 2eme question : amélioration de la fonction MoyenneDifZ

Si tu n'est pas amateur de Debug.Print remplace par Msgbox

Sub Question2()
    Dim vTab As Variant
    Dim vTabIndice As Variant

    vTab = Array(10, 20, 40, 100, 5, 80, 1000)
    vTabIndice = Array(1, 3, 5) '20, 100, 80
    
    Debug.Print MoyenneDifZ2(vTab)
    Debug.Print MoyenneDifZ2(vTab, vTabIndice)
End Sub

Function MoyenneDifZ2(varTab, Optional vTabIndices) As Single
    Dim lg          As Long
    Dim dblSomme    As Double
    Dim lgCptVal    As Long
    Dim lgIndice    As Long
    
    If IsMissing(vTabIndices) Then  ' Si aucun tableau d’indices n’est fourni, on construit la liste complète
        ReDim vTabIndices(LBound(varTab) To UBound(varTab))
        For lg = LBound(varTab) To UBound(varTab)
            vTabIndices(lg) = lg
        Next
    End If
    
    For lg = LBound(vTabIndices) To UBound(vTabIndices)
        lgIndice = vTabIndices(lg)
        If IsNumeric(varTab(lgIndice)) Then
            dblSomme = dblSomme + varTab(lgIndice)
            If varTab(lgIndice) <> 0 Then lgCptVal = lgCptVal + 1
        End If
    Next
    MoyenneDifZ2 = dblSomme / lgCptVal
End Function

Answer 2

Comme notre environnement de test actuel utilise l’interface en anglais, les captures d’écran seront également affichées en anglais. Cependant, vous pouvez tout à fait effectuer les mêmes opérations dans l’interface française.

Cette réponse a été traduite automatiquement. Il peut donc y avoir des erreurs grammaticales ou des expressions inhabituelles.

Bonjour @CHAMBET François (ACEPP),

Merci d’avoir publié votre question sur le forum Microsoft Q&A. Vous avez tout à fait raison : la fonction Application.WorksheetFunction.AverageIf est conçue pour fonctionner avec des plages de cellules Excel et ne s’applique malheureusement pas directement aux tableaux VBA. Cependant, il est tout à fait possible de calculer une moyenne en excluant les zéros en utilisant une approche en mémoire, sans écrire sur la feuille de calcul. Voici un exemple VBA révisé qui montre comment le faire entièrement en mémoire:

Sub AverageExcludingZeros() 
    Dim Tablo4(1 To 8) As Variant 
    Dim sumValues As Double 
    Dim countValues As Integer 
    Dim i As Integer 
    Dim cellValue As Variant 
  
    ' Load values from column B into array 
    For i = 1 To 8 
        Tablo4(i) = Cells(i, 2).Value ' Column B is column 2 
    Next i 
  
    ' Calculate average excluding zeros and non-numeric values 
    sumValues = 0 
    countValues = 0 
  
    For i = 1 To 8 
        cellValue = Tablo4(i) 
        If IsNumeric(cellValue) Then 
            If cellValue <> 0 Then 
                sumValues = sumValues + cellValue 
                countValues = countValues + 1 
            End If 
        End If 
    Next i 
  
    If countValues > 0 Then 
        MsgBox "Average (excluding zeros): " & Format(sumValues / countValues, "0.00") 
    Else 
        MsgBox "No non-zero numeric values to average." 
    End If 
End Sub 

L’utilisation de ce script VBA renvoie le résultat suivant:

Cette méthode parcourt votre tableau, additionne uniquement les valeurs numériques non nulles et calcule la moyenne en fonction du nombre d’entrées valides. Elle est efficace, s’exécute entièrement en mémoire et convient parfaitement aux formulaires utilisateur dynamiques où les performances et la simplicité sont essentielles.

Concernant votre deuxième question sur le regroupement des valeurs de Tablo4(18) à Tablo4(25), les tableaux VBA ne prennent pas en charge les références de type plage comme dans Excel (par exemple Tablo4(18):Tablo4(25)). Vous pouvez toutefois parcourir cette partie du tableau à l’aide d’une boucle For pour traiter cet ensemble de valeurs.

J’espère que cela vous aidera à calculer avec succès la moyenne des valeurs non nulles à l’aide de VBA. Je suis heureux d’avoir pu vous aider et j’espère sincèrement que les informations fournies vous ont été utiles. N’hésitez pas à me contacter à tout moment si vous avez besoin d’une assistance supplémentaire.

Si vous trouvez ce message utile, merci de bien vouloir le marquer comme réponse acceptée. Cela pourra aider d’autres membres de la communauté ayant des questions similaires à trouver plus rapidement une solution.

Merci encore pour votre question pertinente et pour votre contribution au forum.

---

Si cette réponse vous a été utile, veuillez cliquer sur « Accepter comme réponse » et pensez à lui attribuer un vote positif.

Si vous avez d’autres questions concernant cette réponse, veuillez cliquer sur « Commentaire ».

Remarque : veuillez suivre les étapes indiquées dans notre documentation pour activer les notifications par e-mail si vous souhaitez recevoir les notifications relatives à ce fil de discussion.