IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

Comment créer un fichier calculant écarts types et moyennes à partir d'autres fichiers ? [XL-2010]


Sujet :

Macros et VBA Excel

  1. #1
    Membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    avril 2011
    Messages
    86
    Détails du profil
    Informations personnelles :
    Sexe : Homme

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : avril 2011
    Messages : 86
    Points : 61
    Points
    61
    Par défaut Comment créer un fichier calculant écarts types et moyennes à partir d'autres fichiers ?
    Bonjour,

    Je travaille sur un repertoire nommé "Dossier" contenant deux fichiers (il contient en réalité plus que 2 mais cela suffit pour exposer le problème) la macro devrait explorer tout les fichiers de "Dossier" pour leur appliquer le traitement
    Chacun de ces 2 fichiers contient 3 colonnes.

    Je voudrai créer un fichier ".csv" dont les colonnes seraient les suivantes:

    ET1: Ecart Type relativement à la premiere colonne
    ET2: Ecart Type relativement à la deuxième colonne et ainsi de suite jusquà la 3 eme colonne
    Ce qui nous fait pour l'instant 3 colonnes dans le nouveau fichier créer
    MY1: La moyenne relativement à la premiere colonne et ainsi de suite jusqu'à la Moyenne relativement à la la 3 eme colonne
    MI 1 : contient la plus petite valeur de la colonne 1 et ainsi de suite jusqu'à la plus petite valeure de la colonne 3
    MA1: contient la plus grande valeur de la colonne 1 et ainsi de suite jusqu'à la plus petite valeure de la colonne 3

    Le traitement décrit concerne un fichier du "Dossier" nous aurons donc en tout 2 lignes une pour chaque fichier
    J'ai cherché et j'ai trouvé les fonctions Excel calculant les grandeurs ci dessous :

    MOYENNE pour le calcul de la moyenne
    ECARTYPE.STANDARD pour le calcul de l'ecart type d'une colonne
    MIN qui renvoie la plus petite valeur d'une colonne
    MAX qui renvoie la plus grande valeur d'une colonne
    Mais je ne parviens pas à géneraliser ce traitement pour tout les fichiers et comme décrit ci dessus

    Je joints des captures d'écrans montrant le type de fichiers(nommé Un_des_deux_fichiers_de_Dossier.PNG) se trouvant dans "Dossier" et un exmple de résultat de traitement(nommé Fichier_Resultat.PNG)


    Pièce jointe 146279


    J'éspère que vous pourrez m'aider.

    Je vous remercie pour la patience que vous avez eu à lire ce message et merci pour votre temps et votre attention.

  2. #2
    Expert éminent sénior Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    juin 2007
    Messages
    16 034
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : juin 2007
    Messages : 16 034
    Points : 32 858
    Points
    32 858
    Par défaut
    Parfois, il faut oublier les fonctions toutes faites et revenir aux fondamentaux.
    (Waoo ! C'est beau comme du Roger Couderc )

    Tu crée une variable Stat(3,5) de type Long
    Le premier indice indique le numéro de colonne.
    Les autres indices les résultats que tu vas relevé en scrutant chaque donnée.

    Pour chacune (que je désignerai ci-dessous comme XXX, avec Col comme n) de colonne) :

    Stat(Col,1) = Stat(Col,1) + 1
    Stat(Col,2) = Stat(Col,2) + XXX
    If Stat(Col,3) > XXX then Stat(Col,3) = XXX
    If Stat(Col,4) < XXX then Stat(Col,4) = XXX
    Stat(Col,5) = Stat(Col,5) + (XXX * XXX)

    Une fois toutes les valeurs scrutées, tu peux en tirer les informations que tu souhaites :
    MY1 = Stat (1,2) / Stat(1,1)
    MI1 = Stat (1,3)
    MA1 = Stat (1,4)
    ET1 = sqr( ( Stat(1,5) / (Stat(1,1) - 1) - ( MY1 * MY1 ) )

    Et ainsi de suite pour les 2 et 3.
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  3. #3
    Membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    avril 2011
    Messages
    86
    Détails du profil
    Informations personnelles :
    Sexe : Homme

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : avril 2011
    Messages : 86
    Points : 61
    Points
    61
    Par défaut
    Merci pour ta réponse Menhir j'ai bien compris que tu voulai revenir au fondamentaux en reproduisant les formules.
    Cependant comme les fonctions sont déjaà implémentée mon problème c'est :
    1/ Ouvrir chaque fichier du repertoire "Dossier" (en entrant le chemin)
    2/Calculer les grandeurs décrites (moyennes , min , max etc..)

    Apres 2/ on aura une ligne dans le fichiers resultat (extension .csv)

    on continue pour les autres fichiers et on aura dans le fichier résultat autant de ligne que de fichiers dans le repertoire "Dossier"

    C'est ce procedé "automatique" de parcourt du repertoire et calcul des grandeurs (moyennes etc...) que je n'ai pas su faire en une macro.

    J'espere que vous pourrez m'aider.
    Merci pour votre temps et votre attention.

    Bonsoir,

    Voici à quoi ressemble la macro mais je ne parviens pas à :
    créer un classeur ".csv"
    inscrire dans ce classeur les grandeurs Ecart Type , moyenne etc... comme décrit lors du premier post.
    Je joints une image d'un des fichiers que pourrait contenir le repertoire "Dossier" ce à quoi pourrait ressembler le classeur ".csv" une fois crée et contenant les grandeurs.

    J'ai vraiment besoin de votre aide.
    Merci pour votre temps.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
     
     
    Sub MacroO()
    Dim FolderName As String, FName As String
    Dim Wbk As Workbook
     
    Application.ScreenUpdating = False
    FolderName = "C:\Users\SONY\Desktop\Dossier\"
     
    FName = Dir(FolderName & "*.csv")
    Do While Len(FName)
        Set Wbk = Workbooks.Open(FolderName & FName)
        With Wbk
            With .Worksheets(1)
               'A ce niveau je voudrai(mais je n'y parviens pas) créer un fichier pour y inscrire les grandeurs(moyenne, Min etc...)
     
              End With
            .Close True
        End With
        Set Wbk = Nothing
        FName = Dir
    Loop
    End Sub
    Dossier_2.rar

  4. #4
    Expert éminent sénior Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    juin 2007
    Messages
    16 034
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : juin 2007
    Messages : 16 034
    Points : 32 858
    Points
    32 858
    Par défaut
    Citation Envoyé par hichemnho Voir le message
    1/ Ouvrir chaque fichier du repertoire "Dossier" (en entrant le chemin)
    Si ton problème c'est de récupérer les noms des fichiers présents dans "Dossier" pour pouvoir les ouvrir, utilise la fonction Dir().
    Elle n'est pas simple à utiliser mais je pense que c'est la seule qui fasse ça correctement. Tu peux trouver sa syntaxe et des exemple d'utilisation dans l'aide Excel.

    2/Calculer les grandeurs décrites (moyennes , min , max etc..)
    Déjà répondu.

    La seule autre solution que je vois est de copier les données de chaque fichier dans une seule feuille de calcul pour pouvoir ensuite utiliser des formules standard sur ces données copiées. Mais ce n'est ni plus simple ni plus rapide que la méthode que je t'ai indiquée.

    Apres 2/ on aura une ligne dans le fichiers resultat (extension .csv)
    Tu mets tes résultats dans une feuille standard et tu la sauvegardes en CSV avec l'option fileFormat:=xlCSV de la méthode SaveAs.

    on continue pour les autres fichiers et on aura dans le fichier résultat autant de ligne que de fichiers dans le repertoire "Dossier"
    Dans tes messages précédents, tu n'as pas indiqué que les résultats devaient être donnés par fichier.
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  5. #5
    Membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    avril 2011
    Messages
    86
    Détails du profil
    Informations personnelles :
    Sexe : Homme

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : avril 2011
    Messages : 86
    Points : 61
    Points
    61
    Par défaut
    Merci pour ta réponse détaillée.

    La fonction Dir est utilisée dans la Macro que j'ai mis dans le dernier message.
    Je ne suis plus proche du débutant en Excel que d'un utilisateur ou programmeur confirmé, donc quand tu dis copier dans une fichier ou autre chose je ne sais pas le faire( ce que j'ai aussi indiqué lors du dernier message)
    Dans la boucle(le traitement dans la boucle concerne un fichier dans le Dossier spécifié par le chemin) de la macro que j'ai écrite dans le dernier message c'est la ou devrait se trouver les calcul de la moyenne, ecart type etc... avant qu'ils soient affichées dans un fichier ".csv".
    C'est la création , calcul de grandeur puis affichage que je n'ai pas su faire.
    Excuse donc mon niveau limité en Excel ce qui explique ma lenteur dans la compréhension de ton aide qui m'est précieuse.

    Merci encore pour ta compréhension ta patience.

    J'éspère réellement que tu pourra me proposer quelques lignes à ajouter dans la macro pour avancer dans mon problème.

    Merci encore.

  6. #6
    Expert éminent sénior Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    juin 2007
    Messages
    16 034
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : juin 2007
    Messages : 16 034
    Points : 32 858
    Points
    32 858
    Par défaut
    Citation Envoyé par hichemnho Voir le message
    quand tu dis copier dans une fichier ou autre chose je ne sais pas le faire
    Workbooks.Add pour créer un classeur vide.

    Feuille destination.Value = Feuille source.Value pour transférer les valeur en remplaçant "Feuille destination" et "Feuille source" par tes classeurs et Onglets, le tous mis dans une boucle qui parcoure toutes les cellules.
    Ou, plus rapide, faire un Copy sur l'objet Range de la zone de tes données source et un PasteSpecial sur une cellule (Cells) de ta zone de destination.

    Dans la boucle de la macro que j'ai écrite dans le dernier message c'est la ou devrait se trouver les calcul de la moyenne, ecart type etc... avant qu'ils soient affichées dans un fichier ".csv".
    C'est la création , calcul de grandeur puis affichage que je n'ai pas su faire.
    Pour les calculs, voir mon premier message.

    Pour le fichier, il ne faut pas penser CSV au moment de la création ni au moment d'écrire les données. Cette partie se fait classiquement dans une feuille de calcul. C'est au moment de l'enregistrement qu'il faut préciser le format de fichier.
    Comme je l'ai dit dans mon précédent message, c'est l'option FileFormat:=xlCSV de la méthode SaveAs.

    Je ne vois pas ce que tu entends par "affichage".
    Si tu veux parler de l'écriture d'une valeur dans une cellule, il suffit juste de renseigner la propriété Value de l'objet Cells où tu comptes placer ta valeur.
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  7. #7
    Membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    avril 2011
    Messages
    86
    Détails du profil
    Informations personnelles :
    Sexe : Homme

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : avril 2011
    Messages : 86
    Points : 61
    Points
    61
    Par défaut
    Merci énormément pour ta réponse Menhir.

    J'ai tenté de modifier la macro en conséquence mais ça ne fait pas ce que je recherche.
    Je suis débutant c'est pourquoi je n'arrive pas réaliser certains résonnements qui paraissent en théorie simple .
    Ca serait très aimable pour les personnes voulant m'aider de le faire en modifiant le code de cette macro,ça m'aiderai beaucoup.
    J'ai vraiment besoin de votre aide sur cette macro.
    Merci encore Menhir.
    Je vous remercie pour votre temps et pour votre aide.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
     
    Sub Macro1()
     
    Dim FolderName As String, FName As String
    Dim Wbk As Workbook
     
     
     
     
    Application.ScreenUpdating = False
    FolderName = "C:\Users\Desktop\Dossier\"
     Workbooks.Add 'le classeur crée se nomme classeur3 qui va acceuillir les grandeurs...
     '...moyenne , ecart type de chaque fichiers
    FName = Dir(FolderName & "*.csv")
    Do While Len(FName)
        Set Wbk = Workbooks.Open(FolderName & FName)
        With Wbk
            With .Worksheets(1)
              'Calcul des grandeur au niveau du fichier ouvert et se trouvant dans Dossier
     
                .D1 = ECARTYPE.STANDARD("A:A")
                .D2 = ECARTYPE.STANDARD("B:B")
                .D3 = ECARTYPE.STANDARD("C:C")
                .D4 = MOYENNE("A:A")
                .D5 = MOYENNE("B:B")
                .D6 = MOYENNE("C:C")
                .D7 = Min("A:A")
                .D8 = Min("B:B")
                .D9 = Min("C:C")
                .D10 = Max("A:A")
                .D11 = Max("B:B")
                .D12 = Max("C:C")
           Classeur3.Range("A1:L1").Value = Array("ET1", "ET2", "ET3", "MY1", "MY2", "MY3", "MIN1", "MIN2", "MIN3", "MAX1", "MAX2", "MAX3")
        'Le fichier resultat nommé classeur3 aura pour premiere ligne
        'Le nom de chaque grandeur relativement à la colonne concernée
        'ET1 pour ecart type de la colonne1
        'La deuxieme ligne pour la valeur des grandeurs
        'pour les colonne du fichier ouvert et se trouvant dans Dossier
        'La troisieme ligne concernera le deuxieme fichier du "Dossier" et
        'ainsi de suite
        'Mais je n'ai pas su programmer ce raisonnement
     
        Classeur3.Range("A2:L2").Value=Array(.D1,.D2,.D3,.D4,.D5,.D6,.D7,.D8,.D9,.D10,.D11,.D12)
     
            End With
     
            .Close False          'ferme le fichier contenu dans Dossier sans enregistrer
        End With
        Set Wbk = Nothing
        FName = Dir             'Passe au prochain fichier du repertoire "Dossier" pour...
                                       'Calculer les ecart type, moyenne etc... relativement à ce fichier 
                                       'et les enregistrer dans le classeur crée en début de programme
     
    Loop
     
    End Sub
     
     
    '

  8. #8
    Expert éminent sénior Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    juin 2007
    Messages
    16 034
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : juin 2007
    Messages : 16 034
    Points : 32 858
    Points
    32 858
    Par défaut
    On ne donne pas directement l'adresse d'une cellule directement comme une propriété de Worksheet. Il faut utiliser la propriété Range ou la propriété Cells.
    Regarde leur syntaxe d'ans l'aide Excel.
    Tant que tu y es, consulte aussi la liste des membres de l'objet Worksheet, ce sera très instructif.

    Penser à consulter l'aide Excel, c'est le meilleur réflexe quand on veut progresser.
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  9. #9
    Expert éminent sénior Avatar de mercatog
    Homme Profil pro
    Inscrit en
    juillet 2008
    Messages
    9 432
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Autre

    Informations forums :
    Inscription : juillet 2008
    Messages : 9 432
    Points : 31 859
    Points
    31 859
    Par défaut
    Interférence d'insomnie
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    Sub Macro1()
    Dim FolderName As String, FName As String
    Dim WbkS As Workbook, WbkR As Workbook
    Dim Ws As Worksheet
    Dim Lig As Long
     
    Application.ScreenUpdating = False
    FolderName = "C:\Users\Desktop\Dossier\"
     
    Set WbkR = Workbooks.Add(1)
    Set Ws = WbkR.Worksheets(1)
     
    Ws.Range("A1:L1").Value = Array("ET1", "ET2", "ET3", "MY1", "MY2", "MY3", "MIN1", "MIN2", "MIN3", "MAX1", "MAX2", "MAX3")
    Lig = 1
    FName = Dir(FolderName & "*.csv")
     
    Do While Len(FName)
        Set WbkS = Workbooks.Open(FolderName & FName)
        Lig = Lig + 1
     
        With WbkS
            With .Worksheets(1)
                Ws.Range("A" & Lig) = Application.WorksheetFunction.StDev_S(.Range("A:A"))
                Ws.Range("B" & Lig) = Application.WorksheetFunction.StDev_S(.Range("B:B"))
                Ws.Range("C" & Lig) = Application.WorksheetFunction.StDev_S(.Range("C:C"))
                Ws.Range("D" & Lig) = Application.WorksheetFunction.Average(.Range("A:A"))
                Ws.Range("E" & Lig) = Application.WorksheetFunction.Average(.Range("B:B"))
                Ws.Range("F" & Lig) = Application.WorksheetFunction.Average(.Range("C:C"))
                Ws.Range("G" & Lig) = Application.WorksheetFunction.Min(.Range("A:A"))
                Ws.Range("H" & Lig) = Application.WorksheetFunction.Min(.Range("B:B"))
                Ws.Range("I" & Lig) = Application.WorksheetFunction.Min(.Range("C:C"))
                Ws.Range("J" & Lig) = Application.WorksheetFunction.Max(.Range("A:A"))
                Ws.Range("K" & Lig) = Application.WorksheetFunction.Max(.Range("B:B"))
                Ws.Range("L" & Lig) = Application.WorksheetFunction.Max(.Range("C:C"))
            End With
            .Close False
        End With
        Set WbkS = Nothing
        FName = Dir
    Loop
    Set Ws = Nothing
     
    Application.DisplayAlerts = False
    WbkR.SaveAs ThisWorkbook.Path & "\Synthèse", FileFormat:=xlCSV
    Application.DisplayAlerts = True
     
    WbkR.Close False
    Set WbkR = Nothing
    End Sub
    Cordialement.
    J'utilise toujours le point comme séparateur décimal dans mes tests.

  10. #10
    Membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    avril 2011
    Messages
    86
    Détails du profil
    Informations personnelles :
    Sexe : Homme

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : avril 2011
    Messages : 86
    Points : 61
    Points
    61
    Par défaut
    mercatog, c'est exactement ça que je cherchai. Je te remercie énormement, j'aprecie infiniment ton aide.


    Merci à Menhir qui m'a beaucoup aidé pour ce problème et encore merci mercatog !!!

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 6
    Dernier message: 25/10/2011, 10h19
  2. [2.1.2] Calcul écart type dans BIRT
    Par nicolep dans le forum BIRT
    Réponses: 2
    Dernier message: 12/04/2007, 12h09
  3. [C#] écriture fichier .txt + calcul écart-type
    Par titaB dans le forum Windows Forms
    Réponses: 6
    Dernier message: 26/05/2005, 14h09

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo