XL 2019 erreur 1004

[Kapioss]

Bonjour,
Je suis chimiste analyticien et dans le cadre de mes recherches j'essaye de me programmer une petite macro sur VBA afin de faciliter le traitement de mes données.
J'essaye ici simplement d'exclure les valeurs aberrantes lors de mes analyses.
Aujourd'hui j'ouvre mon fichier excel avec mes données et en traçant celle-ci j'identifie les valeurs aberrantes et je les retire "à la main". Ensuite je calcule ma moyenne et mon écart type et encore une fois je retire les valeurs qui sortent de + ou - 2x mon écart type "à la main".

Comme vous pouvez le supposer cela est très chronophage surtout quand dans une séquence d'analyse isotopique j'ai parfois 200 à 300 analyses elles-même composés de 30 mesures de plusieurs isotopes différents et ainsi pour vérifier la précision interne de mon appareil il me faut ouvrir les 200/300 fichiers de 30 mesures et faire cette exclusion à la main sur chaque isotope....

j'ai donc voulu essayer sur la colonne C qui est mon isotope 234U et dont les valeurs sont sur les ligne 24 à 53 et voici mon code VBA

Sub precision_interne234U()

Dim i As Integer
Dim moy As Double
Dim SD As Double
Dim moy2 As Double
Dim SD2 As Double

moy = WorksheetFunction.Average(Range("C24:C53"))
SD = WorksheetFunction.StDev(Range("C24:C53"))

For i = 24 To 53

If Cells(i, 3) >= moy + 2 * SD Then Cells(i, 3) = ""
If Cells(i, 3) <= moy + 2 * SD Then Cells(i, 3) = ""

Next
'recalcule des moy et ecart type sans les valeurs abérrantes

moy2 = WorksheetFunction.Average(Range("C24:C53"))
SD2 = WorksheetFunction.StDev(Range("C24:C53"))

End Sub

Mon problème est que lorsque je fait F5, j'ai une erreur d’exécution 1004 et je n'arrive pas à résoudre ce problème en fouillant sur le forum ni même a très bien comprendre ce qu'est cette fameuse erreur.

En vous remerciant par avance

 

[chagatte]

Bonjour,

Pouvez-vous me joindre votre fichier ou me dire à quelle ligne il y a un bug.

 

[chagatte]

Essayez avec ce code :

Sub precision_interne234U()

Dim i As Integer
Dim moy As Double
Dim SD As Double
Dim moy2 As Double
Dim SD2 As Double
With Sheets("Feuil1")
moy = Application.Average(.Range("C24:C53").Value)
SD = Application.StDev(.Range("C24:C53").Value)

For i = 24 To 53

If .Cells(i, 3) >= moy + 2 * SD Then .Cells(i, 3) = 0
If .Cells(i, 3) <= moy + 2 * SD Then .Cells(i, 3) = 0

Next
'recalcule des moy et ecart type sans les valeurs abérrantes

moy2 = Application.Average(.Range("C24:C53").Value)
SD2 = Application.StDev(.Range("C24:C53").Value)
End With
End Sub

Il faut par contre que vous modifiez la ligne With Sheets("Feuil1") par le nom de votre feuille.

Deux erreurs dans votre code :
1) Il faut lui sélectionner la feuille dans laquelle vous souhaitez travailler.
2) Dans la boucle, vous mettez des vides "" sur les cellules puis essayez de faire une moyenne dessus. Une moyenne entre des valeurs numériques et valeurs texte va faire ressortir l'erreur incompatibilité de type.

 

[Dranreb]

Bonjour.
Moi je crois surtout qu'il faut
If Abs(.Cells(i, 3) - moy) >= 2 * SD Then .Cells(i, 3) = ""
Parce que là il y a deux fois moy + 2 * SD. Au pire il faudrait au moins pour le second <= moy - 2 * SD
Alors ça doit planter tout à la fin parce que toutes les cellules sont mises à "" vu qu'elles sont toujours >= ou <= à moy + 2 * SD !

 

[Kapioss]

Bonjour,
Premièrement merci de votre aide !
Il y avait effectivement une faute de frappe avec le + au lieu d'un - et en faisant appelle à la feuilles mon programme tourne. Je vous remercie.
Cependant je voulait mettre du ''vide'' pour les valeur sortant de 2*l'écart-type afin de ne pas recalculer ma moyenne avec un 0 qui donc fausserait ma moyenne. En mettant du 'vide' je n'ai pas l'impression que cela pose pb.
De plus y aurait-il un moyen d'éviter d'avoir à changer dans le code à chaque fois le nom de la feuille afin d'ouvrir tout mes fichiers et de faire tourner ma macro d'un seul coup sur toutes les feuilles ou fenêtre ouvertes ?

En vous remerciant,

Bonne journée

 

[Dranreb]

Bonjour.
Vous pouvez transmettre en argument un Range représentant la plage à traiter :

Option Explicit
Sub Test()
   SupprAberr ActiveSheet.[C24:C53]
   End Sub
Sub SupprAberr(ByVal RngDon As Range)
   Dim TDon(), L As Long, Av As Double, SD As Double
   TDon = RngDon.Value
   Av = WorksheetFunction.Average(TDon)
   SD = WorksheetFunction.StDev(TDon)
   For L = 1 To UBound(TDon, 1)
      If Abs(TDon(L, 1) - Av) >= 2 * SD Then TDon(L, 1) = Empty
      Next L
   RngDon.Value = TDon
   End Sub

Je me suis aperçu d'un problème en testant : les valeurs aberrantes déplacent la moyenne et augmentent l'écart type, de sorte que toutes les valeurs peuvent passer.
Peut être vaudrait-il mieux se baser sur la médiane. Supprimer une seule valeur, celle qui s'en écarte le plus, et recommencer jusqu'à ce qu'une condition soit atteinte. Mais je ne sais pas encore laquelle. Probablement une expression combinant la nouvelle médiane et la moyenne. Ou peut être tant que l'écart type est chaque fois diminué de moitié. À vous de me le dire …

 

[Kapioss]

Bonjour,

J'ai un peu avancé mon programme. Je parcours donc les colonnes 3 à 6 où sont mes différents isotopes et j'ai mit un fonction while pour tester la variation de l'écart type. Mais j'ai de nouveaux un problème d'erreur 1004.

Pour le moment j'ai mit arbitrairement comme paramètre de sortie de la boucle while 25% de variation de mon écart type.
Je vous joint mon fichier excel de donnée afin que vous puissier voir la forme du fichier excel que m'exporte le logiciel de mon appareil de mesure.
Il me donne déjà une moyenne et un écart type mais ils sont souvent érroné et le logiciel n'exclu pas les valeurs sortant de 2x l'écart type c'est pourquoi je retravaille les fichiers ensuite.

De plus je me penche sur ce que vous m'avez envoyé car je ne connais pas bon nombre de fonction que vous utilisez.

 

[Kapioss]

Sub precision_interne234U()

Dim i As Integer
Dim moy As Double
Dim SD As Double
Dim moy2 As Double
Dim SD2 As Double
With Sheets("005-IRMM100")

'on nomme les cellules de debut ou l'on mettra les différents résultat*

    Range(.Cells(2, 58)).Name = "moyenne"
    Range(.Cells(2, 59)).Name = "StandDev(x2)"
    Range(.Cells(2, 60)).Name = "%SD"
'boucle for qui permettra des faire les isotopes 234U à 238U qui sont des colonnes 3 à 6, on parcours donc les colonnes 3 à 6

    For j = 3 To 6
    
        moy = Application.Average(.Range(.Cells(j, 24), .Cells(j, 53)).Value)
        SD = Application.StDev(.Range(.Cells(j, 24), .Cells(j, 53)).Value)
        moytest = Application.Average(.Range(.Cells(j, 24), .Cells(j, 53)).Value)
        SDtest = Application.StDev(.Range(.Cells(j, 24), .Cells(j, 53)).Value)
        
        'paramètre de test arbitraire on fera des test pour voir ce qui est le plus adequat 50%, 75% 80% etc
        Do While SD / SDtest >= 0.75
        
        'on parcours les lignes 24 à 53 ou les 30 mesures sont avec la boucle for
            For i = 24 To 53

                If .Cells(i, j) >= moy + SD Then .Cells(i, j) = ""
                If .Cells(i, j) <= moy - SD Then .Cells(i, j) = ""
            
            Next
            
            'on stocke l'ancienne moyenne et l'ancien SD dans les variables XXXtest pour faire ensuite le test du while
            moytest = moy
            SDtest = SD
            moy = Application.Average(.Range(.Cells(j, 24), .Cells(j, 53)).Value)
            SD = Application.StDev(.Range(.Cells(j, 24), .Cells(j, 53)).Value)
            
        Loop
        
        'on calcule les moy et SD finaux et on les affiche dans tel ou tel colonnes/lignes correspondante
        moyfinal = Application.Average(.Range(.Cells(j, 24), .Cells(j, 53)).Value)
        SDfinal = Application.StDev(.Range(.Cells(j, 24), .Cells(j, 53)).Value)
        Range(.Cells(j, 58)).Value = moyfinal
        Range(.Cells(j, 59)).Value = SDfinal * 2
        Range(.Cells(j, 60)).Value = (moyfinal / (2 * SDfinal)) * 100
      
        
    Next
'recalcule des moy et ecart type sans les valeurs abérrantes



End With
End Sub

 

[Dranreb]

Pourquoi ne testez vous pas en une seule instruction la valeur absolue de la différence entre l'élément et la moyenne ?
Pourquoi ne prenez vous pas ma procédure telle que je l'ai écrite ?

 

[Kapioss]

Pourquoi ne testez vous pas en une seule instruction la valeur absolue de la différence entre l'élément et la moyenne ?
Pourquoi ne prenez vous pas ma procédure telle que je l'ai écrite ?

Bonjour,

je ne n'ai pas utilisé votre procédure car je ne comprends pas votre code. Et j'aimerais avoir une compréhension du code que j'utilise pour pouvoir l'adapter à mes données si jamais je fais plus de 30 mesures ou plus d'isotope (de 234 à 238 en mesurant les demies masses des isotopes par exemple).

Ce que vous avez écrit dépasse mes compétences... j'ai essayé de comprendre pendant une demi journée ce que vous avez fait et je vous avoue que je n'ai pas réussi. J'ai donc fini par avancer mon code.

 

[Dranreb]

Qu'est-ce que vous ne comprenez pas au juste ?

 

[Kapioss]

C'est un peu l'intégralité du code qui me perturbe.

Pour quoi créer vous un Sub Test () ?
Quelle est le but de ''(ByVal RngDon as Range)" ?
TDon prend la valeur de RngDon si je comprends bien TDon rassemble donc les données de toute la colonne c'est donc un tableau de dimension 1x30 vu que j'ai 30 mesures mais comment fonctionne la "détection" des dimension du tableau ?

Vous parcourez ensuite ce tableau avec un for et vérifier si les valeurs sortent de 2x l'ecart type.

Je comprends donc votre test mais tout les outils que vu utilisez pour si j'ai bien compris parcourir la colonne sont trop complexe pour moi. Pourquoi ne pas simplement faire une boucle for qui vas de ligne en ligne et créer une variable TDon ?
Mes connaissances ce limite a une demie journée de TD de VBA en école de chimie il y a 5ans donc je vous avoue être un peu perdu. C'est donc une compréhension global du votre code qui me pose problème.

bonne journée et encore une fois merci

 

[Dranreb]

— Parce que cette Sub Test n'est là que pour montrer un exemple de l'invocation de la Sub SupprAberr, à adapter aux codes où vous souhaiterez l'invoquer.
— C'est l'argument transmis à la procédure indiquant la plage à traiter.
— Pour un tableau constituant la Value d'un Range, il a toujours 2 dimensions commençant à 1 chacune. Ubound(TDon, 1) donne le nombre de lignes, UBound(TDon, 2) le nombre de colonnes.
— Parce que c'est considérablement plus long de parcourir les cellules une par une. Le chargement/déchargement en tableau de la Value d'un Range de plusieurs cellules n'est pratiquement pas plus long à exécuter que l'accès à une seule cellule, remangé, lui, pour autant de fois qu'il y en a. Par contre l'accès à un élément de tableau VBA est sans commune mesure extrêmement rapide.

 

[Kapioss]

Merci de votre réponse.

je comprends que votre code est bien plus optimal mais je ne suis pas à l'aise avec les outils que vous utilisez. Je sais pas implementer votre code pour parcourir comme je l'ai fait les colonnes et les lignes de mon tableau. Serait-il possible de ne pas créer de variable TDon car même si cela est moins optimal niveaux rapidité je préfèrerais rester sur un code que je comprends.

 

[Kapioss]

Par contre je comprends bien l'intérêt de votre test qui permets de tester en une seul instruction ma condition.