Discussion :

[Dermochelys]

Bonjour,

Voici, une nouvelle petite question pour nous occuper le weekend (... comme si on n'avait que ça à faire ) J'ai mis le tout dans un fichier excel (ci-joint) car c'est plus visuel, mais le but est de faire faire cela par Access via VBA.


J'ai donc une table Access qui content des séries de données chronologiques (ce sont des relevés horaires de températures).
Dans cette table, il y a toujours une série de données ser_0 (= référence) et il peut y avoir une ou plusieurs séries supplémentaires (ici ser_1 à ser_4).
Comme vous pouvez le voir sur le graphique (séries décalées) toutes les autres sont décalées/déphasées par rapport à la série de référence.

Ce que je souhaiterai faire, c'est d'estimer de combien (en nombre d'heure, dans mon cas) les séries sont décalées par rapport à la série de référence, et ensuite ajuster les données (donc les décaler de x heures, dans un sens ou l'autre) pour obtenir des séries en phase (comme dans le second graphique). Visuellement c'est assez facile.

Pour l'estimation du décalage existant, je me disais de faire peut-être une recherche du premier maximum dans chacune des séries 1 à 4, puis de comparer l'indice (l'heure correspondante) de ce maximum à l'indice du premier maximum de la série de référence....
Serait-ce suffisant pour évaluer l'écart existant entre les séries et la série de référence?
N'y a-t-il pas de risque de faux maximum (par exemple, un pic de température à 3h de l'après-midi causé par un incendie, alors que le pic de la journée (= le sommet de la sinusoïde) se situe bien à midi)? Si oui comment éviter cela...



Pour l'ajustement des données, je pense voir comment faire via manipulation de recordset basées sur les séries en questions.

Je suis intéressé par vos suggestions/idées (sachant que j'ai déjà parcouru les réponses liées aux recherches de maximum et/ou minimum de plusieurs posts du forum).


D'avance merci pour votre lecture et bon weekend à tout le monde!

[EricDgn]

Bonjour,

A mon avis, il faut chercher le décalage qui minimise la somme des carrés des écarts entre séries, sur la période pertinente la plus longue possible. Un exemple en attaché.

Maintenant, un vrai statisticien devrait vous confirmer cela.

Cordialement.

[Dermochelys]

Bonjour EricDgn,

Un grand merci pour ta réponse! Mais je ne suis pas sûr de tout comprendre dans le raisonnement derrière les calculs présents sur la feuille excel.

Pour autant que je comprenne, dans les colonnes I à M, tu réduits en quelque sorte l'ensemble des données pour en tirer les valeurs max et min (moyennes) pour chaque séries. Mais, j'ai l'impression que tu ne te sers pas de ces informations pour la suite ...

J'ai supposé que ce que tu mettais en O1:R1 était la valeur de décalage observée entre la valeur minimum de la série de référence et la valeur minimum de la série à ajuster. Mais ca ne colle pas car en Q1, tu mets -7 au lieu de -5 comme je m'y attendais. Par contre, avec ce -7 en Q1, la série est mieux ajustée.
Ca ne colle pas plus si je considère que c'est l'écart entre les valeurs maximum...

La fonction décaler va rechercher x la valeur existantes x lignes avant (ou après) par rapport a la ligne de la valeur de référence.Est-ce bien ça?

Minimiser la somme des carrés des écarts semble en effet être la bonne piste, vu la manière dont "s'alignent" les séries... Faut que je vois comment traduire ça en VBA...
Dans ce sens, tes explications complémentaires pourront certainement m'éclairer

Cordialement

[EricDgn]

Bonjour,

Oui, pour les colonnes I à M, c'était juste pour voir ce que cela donne et où se trouvent les minimums et maximums dans chacune des séries.

La fonction décaler va rechercher x la valeur existantes x lignes avant (ou après) par rapport a la ligne de la valeur de référence.

Oui, c'est cela. Pour les colonnes O à R, ce sont les calculs des sommes des carrés des écarts, avec le décalage indiqué en première ligne (O1, P1, Q1, R1), les sommes étant affichées en deuxième ligne. Il semble que le décalage ne dépasse pas 8 heures.

Cordialement.

[EricDgn]

Bonjour,

Un début de solution avec Access. En attaché les requêtes qSerie_1SCE et qSerie_2SCE permettent de voir les minimums pour les séries 1 et 2.

Cordialement.

[Dermochelys]

Bonjour EricDgn,

Merci de tes réponses!! Bon comme quoi, j'ai quand même bien traduit ta proposition excel

Je pense comprendre ce que tu proposes comme solution dans Access.
Tu crées des requêtes en introduisant d'office un décalage au niveau de l'ID, et ceci jusqu'à 8 décalages.
Tout ces ID sont rassemblés dans la requête qID
A partir de là, tu crées une requête qui va piocher dans la table les valeurs correspondantes (mais décalées) de la série de référence et mettre cela en vis-à-vis de la série à décaler.
Tu calcules le carré des écarts (pour chaque couple de valeurs: série 1 vs série de référence décalée de x) et en final, tu calcules la somme des CE dans la dernière requête.

Si ma compréhension est bonne, va falloir que j'adapte cela dans mon code...

Dans l'exemple que je fournissais, il y a une série de référence et 4 séries à "aligner". Mais en pratique, je vais avoir un nombre variable de séries à aligner (entre 4 et 20, au minimum).
La seule constante de départ est ce tableau avec la série de référence et les x séries à "aligner".
Du coup, j'étais parti dans l'idée de travailler avec des arrays et d'introduire ainsi les décalages et faire les calculs adéquats, pour en final, fournir à l'utilisateur, juste le chiffre du décalage détecté.
Bon pour l'instant, ce que j'ai n'est pas convainquant du tout... on va dire que je rame bien
Ta dernière proposition va peut-être bien me (re)mettre sur la voie

Un grand merci pour ton aide certaine!!

A bientôt pour plus de nouvelles ou questions

Cordialement

[EricDgn]

Oui, tu as bien compris ma proposition. Maintenant il y a comme très souvent plusieurs façons possibles de résoudre un problème et passer par des tableaux peut très bien aussi en être une. Yapluka à en élaborer une qui fonctionne complètement.
Disons qu'avec les requêtes, il est possible d'utiliser la fonction Replace() pour remplacer "ser_1" par "ser_2", ou "ser_3", etc., dans son expression SQL.
Cordialement.

[Dermochelys]

Bonjour EricDgn,

Merci pour ton dernier message. En effet, ça fonctionne d'utiliser un Replace().

A mon niveau, après différents tests et quelques cafés, j'ai laissé tomber l'utilisation d'Arrays pour simplement partir sur des recodsets.

L'idée est d'avoir deux recodsets identiques (on clone celui qui est basé sur la table de données).
Puis, dans l'un OU l'autre recordset (en fonction du signe du décalage), se positionner à x lignes du début des séries (ce qui permet de tester des décalages allant de +6 heures à -6heures par rapport au recodset qui n'a "pas bougé")
Et pour chaque série, calculer la somme des carrés des écarts entre la série de référence (situé dans un recordset) et la série à tester (situé dans le clone)
Et enfin, ne garder que la plus petite valeur ainsi que le décalage correspondant pour utiliser cela plus tard.

Me reste plus qu'à vérifier si cela est cohérent avec différents cas de figures


Maintenant, je ne sais pas si via les recodsets, c'est plus ou moins rapide que via des arrays.... si quelqu'un se sent l'envie de produire le code avec des arrays, je serait curieux du résultat


Comme tu dis, il y a plusieurs façon de résoudre le problème

Un tout grand merci pour ton aide!! Et très bonne fin de journée!

PS: bon ... c'est pas encore tout à fait au point. Le code me donne toujours la même valeur de SCE et de décalage quelque soit la situation... Je creuse pour trouver le problème

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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
 
'.... autres déclarations ici
Dim oRst3Clone As DAO.Recordset
Dim SCE As Long, a As Long
Dim decal As Integer
Dim strdecal As String
 
'.... 
 
Set oRst3 = oDb.OpenRecordset("Table")
oRst3.MoveLast
oRst3.MoveFirst
 
'....
 
'.Fields(2).Name = le champ X = référence ser_0
'.Fields(i).Name = le champ Y = ser_1 à ser_....
Set oRst3Clone = oRst3.Clone
oRst3Clone.MoveLast
oRst3Clone.MoveFirst
 
strdecal = ""
For i = 3 To (oRst3.Fields.count - 1)
a = 0
SCE = 0
decal = 0
    For j = 6 To -6 'on évalue ici un décalage de +6 à -6 heures => heures = enregistrements/lignes
        decal = j
        If j < 0 Then
            oRst3.Move -j
            'on parcours tous les enregistrements/lignes
            For k = 0 To oRst3.RecordCount - (1 + Abs(j))
                'calcul de la somme des carrés des écarts
                SCE = SCE + ((oRst3Clone.Fields(i) - oRst3.Fields(2)) ^ 2)
            Next
        End If
        If j = 0 Then
            'on parcours tous les enregistrements/lignes
            For k = 0 To oRst3.RecordCount - (1 + Abs(j))
                'calcul de la somme des carrés des écarts
                SCE = SCE + ((oRst3Clone.Fields(i) - oRst3.Fields(2)) ^ 2)
            Next
        End If
        If j > 0 Then
            oRst3Clone.Move j
            'on parcours tous les enregistrements/lignes
            For k = 0 To oRst3Clone.RecordCount - (1 + Abs(j))
                'calcul de la somme des carrés des écarts
                SCE = SCE + ((oRst3Clone.Fields(i) - oRst3.Fields(2)) ^ 2)
            Next
        End If
        'une fois que SCE n'est plus NULL, on vérifie si la nouvelle valeur de SCE est inférieure à l'ancienne
        'si oui on conserve cette nouvelle valeur et on stocke la valeur du décalage correspondant
        'sinon on ne fait rien
        If SCE <> 0 Then
            If a = 0 Then a = SCE
            If a < SCE Then
                SCE = a
                decal = j
            End If
        End If
        'on se repositionne au début des deux recordsets avant d'introduire le décalage j suivant
        oRst3.MoveFirst
        oRst3Clone.MoveFirst
    Next
    'avant de passer à la colonne suivante, on stocke la valeur du décalage dans la table Temp5CorrelationResult
    'temporairement, j'affiche juste une msgbox avec les différentes valeurs (SCE et décalage de chaque série)
    strdecal = strdecal & oRst3.Fields(i).Name & ": SCE = " & SCE & ", décalage = " & decal & vbCrLf
Next
 
MsgBox strdecal
 
'....
'on clôture les variables et libère la mémoire

[EricDgn]

Bonjour,

Disons que si l'on est certain que l'allure de la courbe des SCE est convexe dans la plage testée, comme montré dans le graphe ci-dessous

alors il est possible de déterminer le n° du minimum en partant d'un calcul de ce genre (donc à adapter):

qui, pour l'exemple ci-dessus, donne 0 si SCE8 est le minimum, -1 si c'est SCE7, -2 si c'est SCE6, et ainsi de suite.
En attaché, c'est présenté dans les requêtes qSerie_1_n et qSerie_2_n.

Cordialement.

[Dermochelys]

Bonjour EricDgn,

Malheureusement, je ne peux pas garantir que la courbe des SCE dans la plage testée sera toujours convexe... Ta recherche de minimum est efficace ceci dit

Ca m'a aussi inspiré pour modifier mon code qui fonctionne maintenant Je viens de tester sur un bloc de 12 séries de 349 enregistrements chacune, et ça me propose bien un décalage pour chacune d'entre elle correspondant à un SCE minimum.

Reste plus qu'à passer à l'étape suivante ....

Voici le code corrigé pour ceux qui sont intéressés :

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
 
For i = 3 To (oRst3.Fields.count - 1)
    For j = 0 To 11 'on évalue ici un décalage de -5 à +6 heures; 0 = 0 décalage; 1 à 5 = décalage de -1 à -5 ; 6 à 11 = décalage +1 à 6
        SCE = 0
        If j = 0 Then
            'on parcours tous les enregistrements/lignes
            For k = 0 To oRst3.RecordCount - (1 + Abs(j))
                'calcul de la somme des carrés des écarts
                SCE = SCE + ((oRst3Clone.Fields(i) - oRst3.Fields(2)) ^ 2)
            Next
        End If
        If j > 0 And j < 6 Then 'ici on recule dans le temps, le décalage est négatif!
            oRst3.Move j
            'on parcours tous les enregistrements/lignes
            For k = 0 To oRst3.RecordCount - (1 + Abs(j))
                'calcul de la somme des carrés des écarts
                SCE = SCE + ((oRst3Clone.Fields(i) - oRst3.Fields(2)) ^ 2)
            Next
        End If
        If j >= 6 Then  'ici on avance dans le temps, le décalage est positif!
            oRst3Clone.Move 12 - j
            'on parcours tous les enregistrements/lignes
            For k = 0 To oRst3Clone.RecordCount - (1 + Abs(12 - j))
                'calcul de la somme des carrés des écarts
                SCE = SCE + ((oRst3Clone.Fields(i) - oRst3.Fields(2)) ^ 2)
            Next
        End If
        'une fois que SCE n'est plus NULL, on vérifie si la nouvelle valeur de SCE est inférieure à l'ancienne
        'si oui on conserve cette nouvelle valeur et on stocke la valeur du décalage correspondant
        'sinon on ne fait rien
        If a = 0 Then
            a = SCE
            decal = j
        End If
        If a <= SCE Then
        Else
            a = SCE
            decal = j
        End If
        'on se repositionne au début des deux recordsets avant d'introduire le décalage j suivant
        oRst3.MoveFirst
        oRst3Clone.MoveFirst
    Next
    'avant de passer à la colonne suivante, on stocke la valeur du décalage dans la table Temp5CorrelationResult
    'temporairement, j'affiche juste une msgbox avec les différentes valeurs (SCE et décalage de chaque série)
    'on formate le décalage pour qu'il soit explicite
    If decal = 0 Then decal = 0
    If decal > 0 And decal < 6 Then decal = -decal
    If decal >= 6 Then decal = 12 - decal
strdecal = strdecal & oRst3.Fields(i).Name & ": SCE = " & SCE & ", décalage = " & decal & vbCrLf
MsgBox strdecal
Next

Un grand merci pour ton aide!
Très bon weekend!
Cordialement