Analyse statistique par intervalle
Coucou la compagnie
Je viens à vous avec un challenge que j'ai commencé (timidement
J'ai joins un fichier qui en dira bien plus long sur ce que je tente de faire.
J'ai 2 colonnes qui définissent un intervalle (valeur mini et maxi). Cet intervalle peut apparaitre plusieurs fois sur plusieurs lignes, et chaque lignes renvoies des informations chiffrées. Je voudrais calculer 5 grandeurs statistiques pour chaque intervalles.
D'abord je fais un tri croissant sur la borne inférieur de la colonne Mini :
Range("A6:N" & DerLig).Sort Key1:=Range("B5"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormalEt la çà se corse sérieusement, je voudrais lui faire comprendre que dès que la valeur à la ligne suivante est différente, alors on insère 5 lignes et on calcul la moyennes, écart type, minima, maxima, et nombre des grandeurs associés à cet intervalle ?
Bref, j'ai fais un fichier avec ce que j'ai avant traitement, et ce que je souhaite avoir, et qui parlera bien mieux que moi qui suis en train de mimer ce que je voudrais faire
Jenny.
bonjour
je te propose cela
les plages a analyser sont decrites donc tu peux les utiliser avec INDIRECT
Je tai mis un exemple avec MOYENNE pour col F
pour les essais j'ai saisi en dur tu adapteras a tes plges
cordialement
Et bien re-Coucou ,
J'ai encore été à moitié endormie, et j'aurais du dire que je cherchais à créer une macro vba pour automatiser tout çà en un seul clic.
Je suis vraiment navré, mais je vais essayer de comprendre les formules pour voir si je peux extrapoler cet exemple à un tableau plus grand.
Je reviens très vite, merci
Jenny
ps: la difficulté de ma base c'est que le nombre de ligne pour un intervalle peut être variable du coup c'est impossible de formulé ça en dur.
Hop, j'avance un peu.
1) je fais un tri sur une des 2 colonnes (borne inférieur, borne supérieur).
2) Je parcours la colonne borne inférieur de haut en bas, quand il trouve une valeur différente d'une ligne à l'autre, il insère 5 lignes (qui me serviront à placer mes 5 grandeurs statistiques). De cette façon je sépare mes différents intervalles.
Ce qui me donne pour le moment :
Sub analyseStat()
Dim i As Long
Dim DerLig As Long
Dim LignenTete As Byte
DerLig = [B64000].End(xlUp).Row 'réference
LignenTete = 5
'on fait d'abord un tri croissant sur la colonne "taille mini"
Range("A6:N" & DerLig).Sort Key1:=Range("B5"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'une fois ordonné, on peut séparrer les intervalles en insérant 5 nouvelles lignes
For i = LignenTete + 1 To DerLig Step 1
If Cells(i - 1, 2).Value <> Cells(i, 2).Value Then
Worksheets(1).Rows(i).Insert Shift:=xlDown
Worksheets(1).Rows(i + 1).Insert Shift:=xlDown
Worksheets(1).Rows(i + 2).Insert Shift:=xlDown
Worksheets(1).Rows(i + 3).Insert Shift:=xlDown
Worksheets(1).Rows(i + 4).Insert Shift:=xlDown
End If
Next i
'ActiveCell.FormulaR1C1 = "=COUNT(R[-6]C:R[-1]C)"
'ActiveCell.FormulaR1C1 = "=AVERAGE(R[-8]C:R[-3]C)"
' ActiveCell.FormulaR1C1 = "=STDEV(R[-9]C:R[-5]C)"
' ActiveCell.FormulaR1C1 = "=MIN(R[-7]C:R[-3]C)"
' ActiveCell.FormulaR1C1 = "=MAX(R[-8]C:R[-4]C)"
End SubJe continue de chercher une solution, si quelqu'un à une idée je suis preneuse
Jenny.
Bonjour,
Un début de réponse pour la mise en forme.
Sélectionner Feuil1 et démarrer la macro "Mise_en_Forme".
J'essaie de traiter la suite mais j'avoue avoir besoin d'aide.
Cordialement.
Option Explicit
Public Sub Mise_en_Forme()
Dim DerLigne As Integer
Dim i As Integer
With Application
.ScreenUpdating = False
End With
With Worksheets(3)
DerLigne = ActiveSheet.UsedRange.Rows.Count
End With
Cells(DerLigne + 1, 5) = "Nombre"
Cells(DerLigne + 2, 5) = "Moyenne"
Cells(DerLigne + 3, 5) = "Ecart Type"
Cells(DerLigne + 4, 5) = "Mini"
Cells(DerLigne + 5, 5) = "Maxi"
For i = DerLigne To 3 Step -1
If Cells(i - 1, 2) <> Cells(i, 2) Then
Rows(CStr(i) & ":" & CStr(i + 4)).Select
Selection.Insert Shift:=xlDown
Cells(i, 5) = "Nombre"
Cells(i + 1, 5) = "Moyenne"
Cells(i + 2, 5) = "Ecart Type"
Cells(i + 3, 5) = "Mini"
Cells(i + 4, 5) = "Maxi"
DerLigne = ActiveSheet.UsedRange.Rows.Count
End If
Next
'Range("A1").CurrentRegion.Select
With Application
.ScreenUpdating = True
End With
End SubCoucou Eric et merci de te pencher sur mon sujet
De mon coté j'ai plutôt bien avancé (même s'il reste à faire). Je vais m'aider de ce que tu as fait pour embellir mon script
J'ai fait les modifications suivante :
- Une procédure principale qui appelle plusieurs sous procédures.
- Je créais un nouveau classeur, avec autant d'onglet qu''il y a d'intervalle (il faut que je revois mon script de création d'onglet qui fait vraiment usine à gaz)
- Je voudrais me servir des lignes vides pour la détection. Je m’explique :
'--- 4) QUATRIEME ETAPE :
' Sur le premier onglet du classeur crée (ma base de donnée), j'ai fait un tri croissant sur la colonne "Taille Mini"
' Je balaye la colonne "Taille mini" de haut en bas, et pour marquer les intervalles je vais insérer 6 lignes des on change de valeur 'd'une ligne à l'autre ...
'(parce que j'ai 5 lignes de grandeurs stat à calculer: min, max, moyenne, E.T, nombre et comme ça je laisse une ligne vide en bas 'qui me sera utile pour la détection du copié à envoyer vers les onglets) ...
' à chaque fois qu'on change de valeur, hop on insère 6 lignes (de cette façon je crée les intervalles)
Private Sub sousproc4()
Dim i As Byte
Dim LignenTete As Byte
Dim DerLig As Long
WbStat.Worksheets(1).Activate
LignenTete = 5
DerLig = [B64000].End(xlUp).Row
'on balaye la colonne B de haut en bas, chaque fois qu'on détecte une valeur différente _
' alors on insère 6 lignes
For i = LignenTete + 1 To DerLig Step 1
If Cells(i, 2).Value <> Cells(i + 1, 2).Value Then 'Si la valeur de cette ligne est différente de la ligne suivante alors ...
Worksheets(1).Rows(i + 1).Insert Shift:=xlDown 'on insère 6 lignes
End If
Next i
End SubUne fois que j'aurais, mis en forme ma base, je n'aurais plus qu'a alimenter mes onglets. Et je comptais utiliser justement ces lignes vides. En gros lui dire : tu coupes tout jusqu'à détecter une ligne vide et tu envoies dans le premier onglet. Puis tu recommences jusqu’à détecter une nouvelles lignes vide, et tu envoies dans le deuxièmes onglet, etc ...
J'ai édité mon fichier qui tourne bien sur les 3 premières étapes. Il me reste cette 4éme étapes à traiter (et je galère comme une folle
Aussitôt dit, aussitôt fait !
Merci Éric j'ai intégré ton traitement à mon fichier (et tout ce que j'avais déjà fais toute seule comme une grande
Ce qu'il me reste à traiter c'est :
1) les calcul stat, et une fois fait,
2) je dois répartir chaque bloc selon son intervalle dans l'onglet auquel il appartient (et là j’utiliserais la ligne vide du dessous).
J'ai joins le fichier (qui est opérationnel
Merci encore
Jenny.
Bonjour Jenny,
Une petite question à propos de ta macro étape 1 :
Le "ClasseurMain" que tu crées, correspond il à la feuille nommée "Ce que j'ai avant"?
Dans l'affirmative, est-ce un fichier que tu reçois périodiquement, avec des modifications, etc...?
Cordialement.
Jenny,
Sans réponse de ta part, j'ai interprété ton besoin.
Je me trompe peut-être.
Je suis parti de l'idée que tu avais un fichier "source" que tu devais mettre en forme.
Je joints le fichier.
J'attends tes commentaires.
Cordialement
Coucou à tous !
Alors je continue d’avancer.
Ma macro il me reste une étape et demie à traiter sur les 5 étapes de ma macro-commande d’appel. La création du fichier, des onglets, la séparation en intervalle est opérationnelle (testable via le clic du bouton macro du fichier joins). Il me reste l’étape 5 à traiter et qui pour le moment ne fonctionne pas (je pense que le souci vient de la sélection, mais je ne parviens pas à trouver où est l’erreur).
Voici mon script (qui est plein de bonne intention
'--- 4) QUATRIEME ETAPE :
' ma base de données est mise en forme
' il faut que je calcul les 5 grandeurs stats pour chaque intervalle
' ensuite je renvoie les valeurs et grandeurs stats dans l'onglet correspondant à l'intervalle
Private Sub sousproc5()
Dim i As Integer, col As Integer
Dim LignenTete As Byte
Dim DerLig As Long
LignenTete = 5
DerLig = [E65536].End(xlUp).Row
With Worksheets(1)
For col = 6 To 14 Step 1
'1) calcul du "Nombre"
For i = LignenTete + 1 To DerLig Step 1
If Cells(i, 5).Value = "Nombre" Then
Cells(i, col).Value = WorksheetFunction.Count(Range(Cells(i - 2, col), Cells(Range("F").End(xlUp).Row, col)))
End If
'2) calcul de la "moyenne"
If Cells(i, 5).Value = "Moyenne" Then
Cells(i, col).Value = WorksheetFunction.Average(Range(Cells(i - 3, col), Cells(Range("F").End(xlUp).Row, col)))
End If
'3) calcul de l'écart type
If Cells(i, 5).Value = "Ecart Type" Then
Cells(i, col).Value = WorksheetFunction.StDev(Range(Cells(i - 4, col), Cells(Range("F").End(xlUp).Row, col)))
End If
'4) calcul du mini
If Cells(i, 5).Value = "Mini" Then
Cells(i, col).Value = WorksheetFunction.Min(Range(Cells(i - 5, col), Cells(Range("F").End(xlUp).Row, col)))
End If
'5) calcul du maxi
If Cells(i, 5).Value = "Maxi" Then
Cells(i, col).Value = WorksheetFunction.Max(Range(Cells(i - 6, col), Cells(Range("F").End(xlUp).Row, col)))
End If
Next i
Next col
End With
End SubToutes les bonnes volontés sont les bienvenues si vous avez des idées :p
Jenny.
Jenny,
Peux-tu regarder le fichier envoyé, démarrer la macro et me donner tes commentaires?
Il reprend, je pense 95% de ta demande initiale
J'attends de toi de savoir si tu es intéressé par mon aide dans ton projet.
Cordialement
fichier précédent : Jenny v2.xlsm
Oh zut Eric, j'ai loupé tes réponses, pardon mille fois
Je jette un coup d’œil, ma version d'excel est de 2003, je dois installer un pack de compatibilité qui ne veut pas s’installer
Merci, et à très vite.
Jenny
J'ai enfin put me pencher sur le fichier que tu as fait, et je suis juste impressionné qu'en quelques lignes tu parviens à faire ce que j'ai fais avec un gros pavé
En fait, j'aurais souhaiter créer un nouveau fichier, avec tous les onglets qui correspondent aux différents intervalles (comme j'étais parvenue à le faire dans mon fichier
Sur ce nouveau fichier je comptais calculer toutes les grandeurs stat sur le premier onglet, et une fois cela fait envoyer les données des intervalles avec les 5 grandeurs stat dans l'onglet correspondant (c'est la raison pour laquelle j'ai insérée des espace en haut et en bas).
Idéalement, je pourrait même supprimer le premier onglet (la base de donnée) du classeur crée en fin de traitement.
J’essaie de voir si je peux me servir de ton code, mais c'est d'un niveau largement supérieur au mien, j'ai peur de nager
Je rejoins en pièce jointe mon fichier tel qu'il est, et je continue d'essayer de embellir.
Merci sincèrement pour le coup de main,
Jenny.
Bonjour Jenny,
Tout d'abord, mes excuses
Pas vu Excel 2003...
Je te renvoie le fichier en xls et j'ai supprimé la mise en forme des feuilles.
Il y a aussi des explications pour le code.
Cordialement
Salut Jean-Éric et merci pour les explications sur ton code. Je le trouve bien plus accessible et je m’en inspire fortement pour le cas que je traite. L’algorithme que tu as fais est tout simplement mieux (pour ne pas dire très bien) foutu.
Je viens de trouver un truc simpa qui permet d’avoir un template pour les onglets ajoutés :
Sheets.Add after:=Sheets(Sheets.Count), Type:=chemin & "\" & templateOù chemin et template(.xls) sont des variables définies.
Il reste en suspend 2 difficultés que j'essaie de traiter :
1) Si dans mes champs j’ai des zéros ou des chaines de caractère, j’ai une erreur script et ça plante quand je lance une moyenne ou un écart type.
2) Comment faire pour à partir de mon onglet 1 (où j’ai tout traité), venir alimenter chaque onglet avec les champs et les grandeurs statistiques pour chaque intervalle ?
Je vais continuer de travailler avec ton fichier (il y a tellement d'info que je vais prendre plusieurs jours) pour voir si je peux solutionner ces 2 difficultés.
Je te remercie Jean-Éric d’avoir pris le temps d’expliquer pourquoi et comment.
Jenny