Boucle pour calculer la moyenne, quartile selon les valeurs d'une colonne

[misswise]

Salut;

Je suis débutante en VBA Excel, et j'aimerais bien trouvé quelqu'un pour m'aider car je me bloque à cette étape.

Mon problème est comme suit, je dispose d'une feuille excel comportant un grand nombre de lignes. Vous trouverez ci-joint un exemple de la feuille;
Je veux calculer la moyenne, l'écart type, les quartiles pour chaque code magasin et ceci pour chaque année et trimestre afin de détecter les valeurs qui s'écartent de la moyenne.
En fait je veux pas spécifier le code magasin car j'ai plein de codes différents (plus qu'une centaine)
je veux créer une variable qui diffère selon les classes des codes et permets de calculer la moyenne des prix, le quartile,... pour chaque groupe de codes identiques.

Merci pour votre intérêt

 

[Misange]

Re : Boucle pour calculer la moyenne, quartile selon les valeurs d'une colonne

Bonjour
Avec ta description, je ne vois pas trop ce qu'on peut faire si tu ne nous dis pas ce que tu appelles un groupe de code.
Que ce soit via un TCD ou via une fonction, il faut passer les bons paramètres à cette fonction ou à ce tcd.
Donne nous tes clés

 

[misswise]

Re : Boucle pour calculer la moyenne, quartile selon les valeurs d'une colonne

Bonjour;

D'abord merci pour ta réponse.
Pour ce que je veux faire, prenons par exemple le code magasin 1234 il y'a plusieurs ventes qui ont été faite au niveau de ce magasin dans un trimestre T1 et une année X. Dans mon cas, je m'intéresse au calcul du prix moyen de ses ventes et déterminer les valeur aberrantes qui s'écartent de la moyenne par la méthode du quartile.
Oui ça peut se faire au moyen d'un tableau croisé dynamique mais pas le calcul des quartiles en plus dans mon cas un TCD est un peu lourd.
Pour le même code, la même année et le même trimestre, ma macro doit calculer la moyenne, écart type et les inter quartile et supprimer les lignes qui dépassent un intervalle de valeur, cet intervalle dépend des valeurs calculées.

J'espère que j'ai pu éclaircir mon problème

Merci une autre fois pour votre intérêt

 

[Misange]

Re : Boucle pour calculer la moyenne, quartile selon les valeurs d'une colonne

tu dis
"En fait je veux pas spécifier le code magasin car j'ai plein de codes différents (plus qu'une centaine)"

puis

"variable qui diffère selon les classes des codes"

d'où ma question que je réitère :
sur quelle base doit-on faire les calculs ?
dans ton deuxième message tu reparles de codes magasin...

 

[misswise]

Re : Boucle pour calculer la moyenne, quartile selon les valeurs d'une colonne

Au niveau de la boucle je veux pas spécifier le numéro de code en disant code num 1234 par exemple je veux que la macro regroupe les lignes qui ont le même numéro de code dans un trimestre donné d'une année donnée et calcul pour ces lignes la moyenne, la médiane et les quartiles

 

[Misange]

Re : Boucle pour calculer la moyenne, quartile selon les valeurs d'une colonne

Bonjour

Alors voici une macro que j'utilise pour analyser mes données.

Function RngInput(Optional Prompt As String, Optional Defaut As Range) As Range
  Const Title As String = "Saisie utilisateur"
  
  If Prompt = "" Then Prompt = "Veuillez sélectionner une ou plusieurs cellules"
  With Application
    If Defaut Is Nothing Then
      If ActiveCell Is Nothing Then
        Set RngInput = .InputBox(Prompt, Title, , , , , , 8)
      Else
        Set RngInput = .InputBox(Prompt, Title, ActiveCell.Address, , , , , 8)
      End If
    Else
      On Error Resume Next
      Set RngInput = .InputBox(Prompt, Title, Defaut.Address, , , , , 8)
    End If
  End With
End Function

Sub boites()
Dim nb_crit As Integer
Dim Qx As Range
Dim Qy As Range
Dim Res As Range
Dim i As Integer
Dim c As Integer
Dim s As Integer 'N° de la ligne dans la plage sortie
Dim fin As Boolean
Dim Nouv As Boolean
Dim NouveauGroupe As Range
Dim crit() As Range
Dim DefRng As Range
Dim FirstY As Range
Dim Cat1 As String
Dim Cat2 As String

fin = False
Set DefRng = Range("A1")
Set Res = RngInput("cellule en haut à gauche de la plage de sortie", DefRng)
nb_crit = InputBox("Nombre de critères (colonnes) nécessaires pour définir une série")
For c = 1 To nb_crit
    ReDim Preserve crit(c)
    Set crit(c) = RngInput("Etiquette du critère N°" & c & " définissant la série", DefRng)
    Res.Offset(0, c - 1) = crit(c)
    Set DefRng = crit(c).Offset(0, 1)
Next c

Res.Offset(0, c - 1) = "Q1"
Res.Offset(0, c) = "min"
Res.Offset(0, c + 1) = "max"
Res.Offset(0, c + 2) = "Q3"
Res.Offset(0, c + 3) = "median"
Res.Offset(0, c + 4) = "mean"
Res.Offset(0, c + 5) = "SEM"
Res.Offset(0, c + 6) = "nbval"

Set FirstY = RngInput("Etiquette des données à analyser", DefRng)

i = 1
s = 1
    
Do While fin = False
    Nouv = False
    j = 1
    'nb de données identiques sur les n critères
    Cat1 = ""
    For A = 1 To nb_crit
        Cat1 = Cat1 & "-" & crit(1).Offset(i, A - 1)
    Next A
    
    Do While Nouv = False
        Cat2 = ""
        For A = 1 To nb_crit
            Cat2 = Cat2 & "-" & crit(1).Offset(i + j, A - 1)
        Next A
        If Cat2 = Cat1 Then
            j = j + 1
        Else
            Nouv = True
        End If
    Loop
    
    Set Qy = Range(FirstY.Offset(i, 0), FirstY.Offset(i + j - 1, 0))
    
    Q0 = Application.WorksheetFunction.Quartile(Qy, 0)
    Q1 = Application.WorksheetFunction.Quartile(Qy, 1)
    Q2 = Application.WorksheetFunction.Quartile(Qy, 2)
    Q3 = Application.WorksheetFunction.Quartile(Qy, 3)
    Q4 = Application.WorksheetFunction.Quartile(Qy, 4)
    Q5 = Application.WorksheetFunction.Average(Qy)
    Q6 = Application.WorksheetFunction.StDev(Qy) / Sqr(j)
    Q7 = j

    For A = 1 To nb_crit
        Res.Offset(s, A - 1) = crit(1).Offset(i, A - 1)
    Next A
    Res.Offset(s, nb_crit) = Q1
    Res.Offset(s, nb_crit + 1) = Q0
    Res.Offset(s, nb_crit + 2) = Q4
    Res.Offset(s, nb_crit + 3) = Q3
    Res.Offset(s, nb_crit + 4) = Q2
    Res.Offset(s, nb_crit + 5) = Q5
    Res.Offset(s, nb_crit + 6) = Q6
    Res.Offset(s, nb_crit + 7) = Q7


    If crit(1).Offset(i + j, 0) = "" Then
        fin = True
    Else
        i = i + j
        s = s + 1
    End If
Loop
End Sub
[COLOR=black]


[/COLOR]

Fonctionnement :
tu classes tes données comme tu le souhaites (par exemple en triant par code magasin dans la première colonne, année dans la seconde, trimestre dans la troisième.
Et tu lances la macro qui te demande successivement :
1) l'emplacement en haut à gauche d'une plage de sortie (prévois la place nécessaire)
2) le nombre de critères qui définis une série (N° + année + trimestre = 3 critères) (tu peux très mien n'avoir qu'un critère)
3) l'emplacement de la première étiquette de série, de la seconde, de la troisième
4) celui de l'étiquette de la colonne contenant les données à synthétiser
et c'est parti. Pour chaque série il te calcule les quartiles min maxi moyenne médiane, SEM (si tu veux seulement l'écartype remplace la ligne
Q6 = Application.WorksheetFunction.StDev(Qy) / Sqr(j)
par
Q6 = Application.WorksheetFunction.StDev(Qy)


Attention : si tu n'as qu'une seule donnée pour une série, la macro ne peut pas calculer d'écartype par exemple et plante d'où le on error resume next. A toi de gérer cela.

attention aussi si tu as +++ de lignes, modifies les variables integer en long pour celles concernant le nb de lignes

 

[misswise]

Re : Boucle pour calculer la moyenne, quartile selon les valeurs d'une colonne

Merci pour votre réponse.

En fait je veux que pour chaque plage de données la macro me calcule la moyenne+- interquartile, et me crée une colonne qui prens deux valeurs aberrants et non aberrants
abérrants si le prix moyen est inférieur moyenne -interquartile ou supérieur à la moyenne + interquartile
Non aberrant dans le cas contraire

Merci une autre fois pour votre intérêt

 

[Misange]

Re : Boucle pour calculer la moyenne, quartile selon les valeurs d'une colonne

Une fois que tu auras les valeurs que cette macro te fournis faire ce que tu demandes sera assez simple...