Microsoft 365 VBA Moyennes avec conditions

camcam59165 · 27 Mars 2023

Bonjour à tous !
Je souhaiterais faire une macro pour rendre les KPI automatiques.

Je cherche comment écrire une macro qui puisse me faire ceci :
Si il y a plusieurs valeurs dans la colonne A (feuille 2) qui sont égales à la valeur dans la colonne C2 (feuille3) alors faire la moyenne des valeurs de la colonne H (feuille 2). Ensuite afficher le résultat dans la cellule H2 (feuille3). Ainsi de suite, jusque la ligne 169 de la feuille 3. (Voir photo avec schéma)
Je vous met également le fichier au besoin.

Voici la macro que j'ai essayé d'écrire (qui me dit erreur 13 : Incompatibilité de type) :

VB:

Sub Moyenne_avec_conditions()

    Dim Plage1 As Range
    Dim Valeur1 As Variant
    Dim I As Integer
    Dim Total As Double
    Dim Nombre As Integer
    Dim derniere_ligne As Long
    Dim fichier As Workbook
    Dim onglet As Worksheet

Set fichier = ActiveWorkbook

    'Spécifiez la plage de cellules à vérifier
    Set Plage1 = Range("A:A")

    'Parcours chaque cellule de la plage1
    For Each Valeur1 In Plage1
    For I = 1 To 12
        'Vérifie si la valeur de la valeur 1 est égale à "2Amènagement_R2N_VE"
        If Valeur1.Value = I + "Amènagement_R2N_VE" Then
            'Ajoute la valeur au total
            Total = Total + Valeur1.Offset(0, 7).Value
            'Incrémente le compteur
            Nombre = Nombre + 1
        End If
         'Calcule la moyenne
    Dim Moyenne As Double
    Moyenne = Total / Nombre
    Range("H" & I + 1).Value = Moyenne
    
    Next I
    Next Valeur1
    
    'Affiche la moyenne
    MsgBox "La moyenne est de : " & Moyenne
    
End Sub

En vous remerciant par avance de votre aide !!!!

sylvanu · 27 Mars 2023

Bonjour Camcam, et bienvenu sur XLD,
Dans votre macro :
1- Pour concaténer des chaines, utilisez "&" et non "+"
( I + "Amènagement_R2N_VE" : il essaie de faire une addition ... et il n'y arrive pas. )
2- Il faut limiter Plage1 pour éviter d'analyser toutes les cellules de la colonne
3- Avant de faire la moyenne, vérifier que le quotient est non nul.
Donc un essai. Je ne sais pas si le résultat est le bon, mais au moins ça ne plante pas.

VB:

Sub Moyenne_avec_conditions()

    Dim Plage1 As Range, DL%
    Dim Valeur1 As Variant
    Dim I As Integer
    Dim Total As Double
    Dim Nombre As Integer
    Dim derniere_ligne As Long
    Dim fichier As Workbook
    Dim onglet As Worksheet

Set fichier = ActiveWorkbook

    'Spécifiez la plage de cellules à vérifier
    DL = Range("A65500").End(xlUp).Row          ' Dernière ligne occupée de la colonne A                ' MODIF
    Set Plage1 = Range("A1:A" & DL)             ' Limitation de la plage aux cellules non vides de A    ' MODIF

    'Parcours chaque cellule de la plage1
    For Each Valeur1 In Plage1
    For I = 1 To 12
        'Vérifie si la valeur de la valeur 1 est égale à "2Amènagement_R2N_VE"
        If Valeur1.Value = I & "Amènagement_R2N_VE" Then    ' Utilisez & et non + pour la concaténation ' MODIF
            'Ajoute la valeur au total
            Total = Total + Valeur1.Offset(0, 7).Value
            'Incrémente le compteur
            Nombre = Nombre + 1
        End If
         'Calcule la moyenne
    Dim Moyenne As Double
    If Nombre <> 0 Then Moyenne = Total / Nombre            ' Vérifier si le nombre est non nul pour faire la moyenne. ' MODIF
    Range("H" & I + 1).Value = Moyenne
    
    Next I
    Next Valeur1
    
    'Affiche la moyenne
    MsgBox "La moyenne est de : " & Moyenne
    
End Sub

camcam59165 · 27 Mars 2023

Merci pour votre aide et votre réactivité.

Effectivement il n'y a plus de bug, mais il n'arrive pas à m'afficher la bonne moyenne.
Normalement dans la cellule H3 il devrait m'indiquer 15%.

Je sais comment faire au cas par cas mais cela va me prendre trop de temps à écrire 150 macros, j'aimerais une boucle. (voir boucle for dans la macro suivante)

VB:

Sub Moyenne_avec_conditions()

    Dim Plage1 As Range, DL%
    Dim Valeur1 As Variant
    Dim I As Integer
    Dim Total As Double
    Dim Nombre As Integer
    Dim derniere_ligne As Long
    Dim fichier As Workbook
    Dim onglet As Worksheet

Set fichier = ActiveWorkbook

    'Spécifiez la plage de cellules à vérifier
    DL = Range("A65500").End(xlUp).Row          ' Dernière ligne occupée de la colonne A               
    Set Plage1 = Range("A1:A" & DL)             ' Limitation de la plage aux cellules non vides de A 

    'Parcours chaque cellule de la plage1
    For Each Valeur1 In Plage1
    For I = 1 To 12
        'Vérifie si la valeur de la valeur 1 est égale à "2Amènagement_R2N_VE"
        If Valeur1.Value = I & "Amènagement_R2N_VE" Then    ' Utilisez & et non + pour la concaténation ' MODIF
            'Ajoute la valeur au total
            Total = Total + Valeur1.Offset(0, 7).Value
            'Incrémente le compteur
            Nombre = Nombre + 1
        End If
         'Calcule la moyenne
    Dim Moyenne As Double
' Vérifier si le nombre est non nul pour faire la moyenne.
    If Nombre <> 0 Then Moyenne = Total / Nombre
    Worksheets(3).Range("H" & I + 1).Value = Moyenne 'MODIF
    
    Next I
    Next Valeur1
    
    'Affiche la moyenne
    MsgBox "La moyenne est de : " & Moyenne
    
End Sub

Je ne sais pas si ma demande est claire, je ne sais pas comment expliquer mon idée.

sylvanu · 27 Mars 2023

Re,
Je ne comprends pas ce que vous voulez faire et comment vous pouvez avoir 15% en H3.
1- La macro Moyenne_avec_conditions est en page base de données, mais semble s'appliquer à la feuille Planning 2023, déjà là je ne comprends pas.
2- Pour H3, cela semble concerner 2Amènagement_R2N_VE quel calcul faites vous pour obtenir 15% ?
Pouvez vous préciser exactement sur quelle feuille les données sont prélevées, et quel calcul exact vous faites pour trouver ces 15% ?

camcam59165 · 27 Mars 2023

Dans l'onglet "Base de données" il y a tous les informations en forme de liste. (photo ci-dessous)

Je souhaite faire faire la moyenne des valeurs de la colonne G (feuille Base de données) (%Ok) si elles sont égales à la cellule C2 (feuille Planning 2023) et mettre la valeur de la moyenne dans la cellule H2 (feuille Planning 2023) et ainsi de suite pour chaque cellule (C3 et H3, C4 et H4...).

Pour la question sur les 15%, on voit que sur la photo 1, la ligne 2 (Amènagement_R2N_VE) on voit qu'il apparait 1 seule fois et le résultat est 15%, donc le résultat de la moyenne doit être 15%.

J'espère que ces explications sont plus claires, merci.

sylvanu · 27 Mars 2023

Re,
Si j'ai bien compris, je ne vois pas l'intérêt de faire une macro pour ça.
Il suffit de faire :

VB:

=SIERREUR(SOMME.SI('Planning 2023'!A:A;A2;'Planning 2023'!F:F)/SOMME.SI('Planning 2023'!A:A;A2;'Planning 2023'!D:D);"")

C'est plus simple et plus rapide.
Voir PJ feuille Essai.

camcam59165 · 27 Mars 2023

Merci je ne sais pas pourquoi je n'ai pas pensé à ça plutôt ahah.

Bonne journée !

AtTheOne · 27 Mars 2023

Bonjour à toutes & à tous,
bonjour @camcam59165, bonjour @sylvanu
Comme d'hab un peu tard ...
En transformant le tableau de la feuille "Base de données" en tableau structuré (tb_BdD) et avec la fonction MOYENNE.SI.ENS

Enrichi (BBcode):

=SIERREUR(MOYENNE.SI.ENS(tb_BdD[% Ok];tb_BdD[Secteur];$A4;tb_BdD[Date];">="&$B4;tb_BdD[Date];"<="&FIN.MOIS($B4;0));"---")

Sans utiliser des colonnes concaténées et pour toute la colonne H de la feuille "Planning".

EDIT : Les données sont-elles bien à lire sur la feuille "Base de Données", ce que j'ai fait, ou toutes sur la feuille "Planning" ?

Microsoft 365 VBA Moyennes avec conditions

camcam59165

XLDnaute Nouveau

Pièces jointes

sylvanu

XLDnaute Barbatruc

camcam59165

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

camcam59165

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

Pièces jointes

camcam59165

XLDnaute Nouveau

AtTheOne

XLDnaute Accro

Discussions similaires