Discussion :

[cyrille.dev]

Bonjour,
j'aimerais résoudre le problème suivant:

Mise en situation:
J'ai la suite de nombre suivante (cf pièce jointe):
Pendant 10s, la valeur mesurée est 12 -/+ 0.3, puis à un instant t (ici t=11s), la valeur mesurée augmente fortement.
J'aimerais pouvoir détecter automatiquement le moment ou la valeur est >= a 12.5 (soit ici a t=11s), puis calculer la moyenne des 5 dernières valeurs (soit de t=6 a t=10).
Comment puis je faire? je sens que je vais devoir apprendre à écrire des macros et des scripts VBA?non?

1 12,1
2 12,2
3 12,3
4 11,9
5 11,8
6 12
7 12,1
8 12,2
9 12
10 11,7
11 12,5
12 13
13 13,5
14 14
15 14,5
16 16
17 18
18 20

Auriez vous un exemple à me donner ou une adresse ou je pourrais trouver un tutoriel sur ce genre de problème?
Ou bien le noms des fonctions qui pourraient m'aider, car je ne sais pas par ou commencer...

En vous remerciant

Cyrille

[Theze]

Bonsoir,

Comment sont entrée les valeurs dans les cellules ? Manuellement, automatiquement et dans ce cas, par quel moyen, un logiciel ? un Timer dans Excel ?

En attente d'en savoir plus.

Hervé.

[cyrille.dev]

Bonjour,
J'ai simplifié le fichier, il s'agit d'un fichier generé par un système d'acquisition de températures:
la 1ére colonne donne le temps en secondes (ex : 1 )
la 2éme colonne la température en °C (ex 12.1)

Dans l'exemple j'ai pris 12 en moyenne avant que la température s'éleve, mais ca pourrait être 20 ou 100

Il me faut reconnaitre le moment ou l'on quitte une relative stabilité afin de mesurer la moyenne de la température sur un temps donné (ici 5s) avant cette montée.

C'est pour faire du post-traitement de données d'un fichier d'acquisition.

[Theze]

Bonjour,

Il faudrait savoir si l'évènement "Change" est généré dans la feuille où sont entrée les valeurs, si oui, il est possible d'utiliser une code du genre :

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
 
Dim Moyenne As Double
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Target.Column <> 2 Then Exit Sub
    With Target
 
        If .Value >= .Offset(-1, 0) + 0.3 Then
 
            Moyenne = Application.WorksheetFunction.Average(Range(.Offset(-1, 0), .Offset(-5, 0)))
 
        End If
 
    End With
 
End Sub

Sinon, il te faut utiliser un chrono (GetTickCount ou Application.OnTime) et tester toute les secondes la valeur de la dernière cellule non vide de ta colonne.

Hervé.

[Invité]

Pas sur que la variation +/- 0.3 soit suffisante pour définir ton point de rupture.
Entre le point 3 et le point 4 il y a - 0.4...

Il vaut mieux étudier la variation de la dérivée à droite et fixer un seuil maxi de variation. Ou alors augmenter ta zone de tolérance mais ça me semble un peu arbitraire.

[cyrille.dev]

@theze : Je comprends pas ce que tu veux dire par evenement "change"?

@vcottineau : je me suis mal expliqué, dans cet exemple j'ai une valeur moyenne de 12 qui oscille de -/+ 0.3 donc potentiellement -/+0.6 crete a crete, mais bien sur suivant le choix de comparaison, on peut augmenter le seuil jusqu'à 1.0°C, l'important étant de détecter le moment ou la courbe commence a augmenter fortement pour mesurer la moyenne sur les 5 dernières valeurs précédant l'augmentation et le point d'inflexion.

@theze : Je vais essayer et tester ce morceau de code.
Y'a t'il un moyen pour faire un appel a cette fonction via une cellule de ma feuille exel?

Merci en tout cas pour ces 1ers elements de reponse

J'essaie et je vous tiens au courant

[Invité]

Est-ce que ce code correspond à tes attentes ?

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
Option Explicit
 
Public Sub GetInflexion()
 
  Dim nbLignes#, i#, tolerance#, moyenne#
 
  With Me
 
    tolerance# = 0.6
    nbLignes# = .Range("A" & .Rows.Count).End(xlUp).Row
 
    For i# = 1 To nbLignes#
      If Abs(.Cells(i# + 1, 2).Value - .Cells(i#, 2).Value) >= tolerance# Then
        moyenne# = Application.WorksheetFunction.Average(.Range(.Cells(i#, 2), .Cells(i#, 2).Offset(-4, 0)))
        MsgBox "Moyenne: " & moyenne# & vbCrLf & "Ligne: " & i# & vbCrLf & "Temps: " & .Cells(i#, 1).Value & " s"
        Exit For
      End If
    Next i#
 
  End With
 
End Sub

Code à intégrer dans le module de la feuille ou sont contenues les données.

[Theze]

Bonsoir,

vcottineau :

Code à intégrer dans le module de la feuille ou sont contenues les données.

Comment tu déclenche le contrôle pour connaître le moment où la variation importante commence ? Puisque Cyrille demande :

J'aimerais pouvoir détecter automatiquement le moment ou la valeur est >= a 12.5 (soit ici a t=11s), puis calculer la moyenne des 5 dernières valeurs (soit de t=6 a t=10)

Cyrille :

@theze : Je comprends pas ce que tu veux dire par évènement "change"?

Quand tu entre une valeur dans une cellule quelconque, un évènement est déclenché au niveau VBE au moment ou tu sors de la cellule (tabulation, Entrée, avec du code, etc...) mais il arrive que parfois, si la valeur est entrée automatiquement (par VBA ou un autre logiciel par exemple), que l'évènement ne soit pas déclenché et dans ce cas, il faut trouver une autre solution pour que l'automatisme soit possible.
Pour contrôler si un évènement est déclenché au moment où la valeur est saisie par ton logiciel, tu peut tester ce code qui est à mettre dans le module de la feuille qui recueille les valeurs. Une boite de message va s'afficher 3 fois à chaque fois que ton logiciel entrera une valeur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
Dim I As Integer
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
    If I <= 3 Then
 
        MsgBox "évènement déclanché !"
        I = I + 1
 
    End If
 
End Sub

Hervé.

[cyrille.dev]

@vcottineau
J'ai pas testé encore, si j'ai le temps je ferais des essais ce soir ou sinon demain...
mais il me semble que Msgbox sert a faire apparaitre une boite de dialogue dans la feuille exel. mais ce n'est pas tout a fait ce que je souhaite faire

Ce que je voudrais sur ma feuille exel c'est par exemple:
de A1 a A100 mes données de temps
De B1 a B100 mes données de températures
Dans la cellule C1, un calcul ou un code correspondant à une macro VBA ou autre, me donnant le resultat que je souhaite calculer (ici la moyenne des 5 dernières valeurs avant le point d'inflexion et d'augmentation de la temperature...) et que donc ce résultat de moyenne apparaisse directement et automatiquement dans la cellule C1

Et ce quelque soit les valeurs numériques utilisés...

suis-je clair?

Cordialement

[Theze]

Re,

(ici la moyenne des 5 dernières valeurs avant le point d'inflexion et d'augmentation de la temperature...)

A mettre dans le module de la feuille où sont entrée les valeurs :

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
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Target.Column <> 2 Then Exit Sub
 
    With Target
 
        If .Value >= .Offset(-1, 0) + 0.3 Then
 
            .Offset(, 1) = Application.WorksheetFunction.Average(Range(.Offset(-1, 0), .Offset(-5, 0)))
 
        End If
 
    End With
 
End Sub

Hervé.

[Qwazerty]

Salut

Et sinon pourquoi ne pas simplement utiliser une formule que tu déploies en colonne C? voila un exemple sur ton fichier. Le déploiement de la formule ne commence qu'à la 5ème ligne du tableau pour pouvoir faire la moyenne des 5 dernières valeurs. Bien sûr cela implique que le changement recherché n'apparaisse pas durant les 5 premières valeurs...

Si ça pose problème, 2 solutions, soit modifier la formule pour tenir compte du départ de tableau (je regarde si l'idée de la formule te convient).

Soit d’intégrer des lignes "neutre" (valeur de la cellule D2?), humm c'est peut-être pas génial ça.


[Edit]
Remplace la formule en colonne C par celle-ci en débutant cette fois à la ligne 1 et en la déployant jusqu'au bas de ton tableau (tu peux double-cliquer sur la poignée de recopie de la cellule C1, elle déploiera la formule automatiquement jusqu'en bas de ton tableau)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(ET(B2>=$D$2;B2-B1>=$E$2);MOYENNE(DECALER(B1;SI(LIGNE()<5;-LIGNE()+1;-4);0;SI(LIGNE()<5;LIGNE();5)));"")

[/Edit]
++
Qwaz

[Invité]

@Theze: Je ne l'ai pas compris comme ça. Pour moi c'est plus du post-traitement qu'une analyse en temps réel avec une tolérance de +/- 0.6 crête à crête comme je l'ai proposé.

Je te laisse continuer du coup.

[Qwazerty]

Salut

Je pense également que c'est du post traitement, enfin je l'ai compris comme ça (?)

Pour ton code Theze, je pense qu'il ne tient pas compte d'une contrainte qui est de tenir compte d'une valeur "cible", ici 12.
Si un écart important apparaît, par exemple de 10 à 10,6, seule la condition d'écart est respecté, par la contrainte de la valeur cible :p
[Edit]
vcottineau non plus
[/Edit]

++
Qwaz

[Invité]

Sauf que tu ne connais pas la valeur cible...

[Qwazerty]

Ah mince, tu penses?
Arf ça change "tout" en effet ! du moins pour ma formule qui prend du plomb dans l'aile!

cyrille.dev ?

[cyrille.dev]

Bon je viens d'essayer vos codes:
@theze : J'ai pas réussi a faire fonctionner ton 1er code mais le 2éme marche.
@vcotinneau: le tien marche également.

Vos codes donnent les mêmes résultats.
Par contre comme je l'ai dis plus haut, plutôt que d'avoir une message box comme dans vos exemples, j'aimerais que les 3 résultats contenus dans la messagebox soit écris directement dans des cellules de ma feuille exel...

Est ce possible?

Par exemple les 3 résultats de vos codes sont 12 10 et 10, j'aimerais une fois que l'on active la macro qu'ils viennent automatiquement s'écrire respectivement dans les cellules C1 D1 et E1 par exemple

Ou mieux encore dans les cellules C1 D1 et E1, faire appel a une fonction ou macro qui me calculerait et afficherait directement ces 3 chiffres.

oui c'est du post-traitement.
En fait j'aurais mon fichier d'acquisition, j'aimerais faire un copier coller de ces données dans une autre feuille exel ou des macros seraient déjà preparés pour me faire le calcul precité...

Je suis totalement débutant en macros et VBA et j'aimerais avoir qqs pistes de reflexion pour concevoir moi-memes ces macros si j'y arrive.

En realité le fichier brut d'acquisition sera beaucoup plus complexe que cela et demandeera d'autres macros supplémentaires...

mais je commence par un exemple simple pour me familiarisé avec l'environement...

@Qwazerty

tes idées et post me plaisent assez sur le papier... je test cela tout de suite et posterait mon opinion tout à l'heure

oui la c'était un exemple avec un palier a environ 12°C puis une forte montée (de l'ordre de 2°C/s).

Mais en réalité cette valeur de palier pourra etre comprise entre 10 et 100°C avec une relative stabilité de -/+0.3 mais toujours une augmentation brusque de 2°C/s environ)

Après le début du fichier peut être fixe sur un temps donné (par ex 5°C -/+0.3 pendant 10s si c'est plus pratique pour avoir un point de départ

Ensuite les différents fichiers d'acquisition a post-traité auront sensiblement la même allure, ce qui changera ce seront les valeurs moyenne des paliers, et sans doute le moment du point d'inflexion qui pourra varier de qqs secondes a qqs dizaines de secondes...

@Qwazerty : J'ai essayé tes formules directement dans les cases exel, mais soit ca marche pas,soit je me gourre qqpart, je continue d'essayer.
Mais si j'ai le choix entres plusieurs solutions, la tienne en passant par exel me plait davantage car elle m'est plus familliere que le VBA...

Je continue d'essayer de voir si tes formules peuvent marcher...

[Qwazerty]

Salut

J'ai mis un fichier dans le message #11

Mais il faut que je modifie la formule car comme la fait remarquer avec justesse vcottineau, j'ai fais fausse route en pensant que la température cible était une valeur connue, je te modifie la formule et je reviens.

[Edit]
Voila la nouvelle formule à placer en C1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(B2-B1>=$E$2;MOYENNE(DECALER(B1;SI(LIGNE()<5;-LIGNE()+1;-4);0;SI(LIGNE()<5;LIGNE();5)));"")

Si ça te convient et que tu veux plus d'explication sur la structure de la formule n'hésite pas.
[/Edt]

++
Qwaz

[cyrille.dev]

Pour que vous ayez une vision plus globale de ce que je souhaite faire (de préférence uniquement via exel sans VBA)
regardez le fichier exel ci joint:

Il s'agit d'un protocole de vérification d'un appareil générant une temperature programmée. sur l'appareil a testé je rentre un cycle type et mesure la temperature réelle qui sera enregistré dans le fichier exel

Je souhaite ensuite post-traité les données et effectués un certains nombre de calculs

Ce protocole sera identique pour tous les appareils a qqs secondes prets et suivants les appareils, les pics seront plus ou moins marquées et les paliers plus ou moins stables.

Le début du protocole (de t0 a t10) sera toujours le même et a la même temperature et aura la même durée.

Dans cette exemple a partir de t10, les températures et les durées de chaque
étape sont arbitraires (en réalité elle seront différentes de cet exemple et plus longues)

Ici l'exemple comprend 3 paliers de température (20 16 et 26). En realité ce cycle (a partir de t10) sera repeté une dizaines de fois.

Ce que je souhaite calculer pour chaque palier:
1er palier à 20°C : l'over ou l'undershoot soit la temperature de dépassement de consigne max ou min pour le palier a 20°C (ici 22.5 a t=16)
la moyenne des 5 dernières valeurs du palier a 20° qd la température chute brusquement (de t23 a t27)
et la vitesse de montée ou descente en temperature (en realité cette durée sera comprise entre 5 et 15s) donc j'aimerais faire le ratio entre le delta_t et delta_T
2éme palier à 16°C : Idem que palier précèdent mais avec une pente négative pour le changement de temperature

et ainsi de suite sur x cycles, montée puis descente en temperature...

j'aimerais que l'ensemble de ces calculs puisse être copiés dans une nouvelle colonne exel les uns à la suite des autres...

Exel en est t'il capable ou dois-je passer par une macro VBA?

Pour l'instant, je débute et me concentre uniquement sur une partie de mon problème a savoir pouvoir calculé la moyenne d'un palier à partir d'un point d'inflexion de la courbe qui début à un instant t variable...

Voila tous les conseils seront bons à prendre

[cyrille.dev]

Salut


[Edit]
Voila la nouvelle formule à placer en C1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(B2-B1>=$E$2;MOYENNE(DECALER(B1;SI(LIGNE()<5;-LIGNE()+1;-4);0;SI(LIGNE()<5;LIGNE();5)));"")

Si ça te convient et que tu veux plus d'explication sur la structure de la formule n'hésite pas.
[/Edt]

++
Qwaz

Merci, oui j'ai essayé ce que tu proposes a t10 ca marche bien, mais cela me crée des résultats en t11 jusqu'à t17 dont je n'ai pas besoin.
Mais je vois le principe, je vais essayer de modfier ta formule pour n'avoir que un calcul a t10 et rien dans les autres cases.

pour le code utilisé je veux bien qqs complements d'informations sur cette partie:
(DECALER(B1;SI(LIGNE()<5;-LIGNE()+1;-4);0;SI(LIGNE()<5;LIGNE();5)))

donc on prends B1 en reference et c'est a partir du SI que je comprends moyennement la formule meme si je vois ou tu veux en venir...
peux tu m'expliquer?

Merci en tout cas ca va déjà me servir de base de reflexion...

[Qwazerty]

Salut

En règle générale, je dirais qu'il est bien dans ce genre de cas de poster directement dans la section Excel car les gens qui ne viennent pas dans la section VBA sont souvent des gens qui maîtrise bien mieux la création de formule (du moins c'est mon cas), pourtant de base excel est un tableur et permet déjà de faire pas mal de choses. Autant utiliser les possibilité fournis par Excel avant de vouloir en inventer de nouvelles avec VBA

J'essaierai de modifier la formule mais je pense qu'il sera compliqué de n'avoir que les valeurs qui t’intéresse. Il faudra surement s'orienter vers une formule matricielle avec une mise en forme conditionnelle pour "cacher" les résultats non souhaité.

Du coup avec tes nouvelles explication la partie decaler ne sert pas à grand chose puisque le changement n'aura jamais lieu dans les 5 1ère valeurs. Regarde dans le fichier du post #11, tu auras la formule sans decaler, il suffira de modifié la condition de départ

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SI(B2-B1>=$E$2;

La fonction Decaler permet à partir d'une ou plusieurs cellule(s) de référence de modifier la taille de la plage.
On trouve ensuite le nombre de ligne et de colonne de décalage de la plage, si j'ai B1:C3 en référence et que je demande de décaler de 3 lignes et 1 colonne, j'aurais une nouvelle plage C46.
Les 2 derniers paramètres représentent le redimensionnement de la plage, si on à une plage de départ F5 et que je place en paramètre Hauteur 2 et Largeur 5, ma plage deviendra F5:J6

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DECALER(B1;SI(LIGNE()<5;-LIGNE()+1;-4);0;SI(LIGNE()<5;LIGNE();5))

Ici, on part de B1 (La formule se trouve donc à la ligne 1 de la feuille.
Ligne() retourne le numéro de la ligne sur laquelle se trouve la formule, donc ici 1.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SI(LIGNE()<5;-LIGNE()+1

Avant d'être à la ligne 5, je n'ai pas assez de valeurs passées pour faire une moyenne de 5 valeurs, donc en prenant comme valeur -ligne()+1, je m'assure que mon décalage ne dépasse jamais le nombre de ligne disponible au dessus, si on se trouve en ligne 3, j'ai donc 2 ligne au dessus de celle ou je me trouve (-ligne()+1 = -3+1 = 2). Au dela de la ligne 4, j'aurais toujours 5 lignes disponibles, c'est la valeur si faux de mon Si.

Le raisonnement est identique pour l'autre Si

++
Qwaz