TP Algorithmique Master 1 – IMI 2010/2011

Courbes d’ajustement au sens des moindres carrées avec VBA

K.MOUATS 1

1. Objectif du travail :

Le premier objectif de ce travail est de réaliser une macro VBA qui permet de calculer

et visualiser la droite d’ajustement (régression linéaire) au sens des moindres carrés d’un

nuage de points (x,y).

La macro a été étendue afin de tracer les courbes d’ajustement (régression polynomiale)

d’ordre 2 et 3.

2. Définitions :

2.1. Courbe d’ajustement au sens des moindres carrées :

L'ensemble des points répartis sur un système d'axes donne l'aspect d'une nuée de particules

de poussière qui se disperse dans l'air. Ce système est d'ailleurs appelé diagramme de

dispersion. Dans la plupart des cas, on peut, à partir de ce diagramme, faire la représentation

d'une courbe continue autour de laquelle les points semblent se regrouper. Cette courbe est

nommée courbe d'ajustement.

Il existe plusieurs types communs de courbes d'ajustement possédant chacun leurs propres

équations. La liste ci-dessous en fait l'énumération (non exhaustive):

Droite:

Parabole:

Courbe cubique:

Courbe du 4e degré:

Courbe du ne degré:

Hyperbole:

…. etc

2.2. Méthode graphique d'ajustement d'une courbe:

On peut facilement déterminer une courbe d'ajustement d'un ensemble de données. C'est

l'action de faire l'esquisse d'une courbe qui soit proche de l’ensemble des points (x,y)

considérés.

2.3. La droite d’ajustement

La droite est certes le type de courbe d'approximation le plus simple. Tel que mentionné ci-

dessus, son équation est:

TP-Algorithmique

Sujet : Approximation linéaire - droite d’ajustement au sens des moindres carrés

Réalisation : Kamel MOUATS

Master 1 – IMI Université Henri Poincaré 2010/2011

TP Algorithmique Master 1 – IMI 2010/2011

Courbes d’ajustement au sens des moindres carrées avec VBA

K.MOUATS 2

Considérons deux points quelconques: et qui se retrouvent sur la droite. À partir

de ceux-ci, il devient possible de découvrir les constantes et . L'équation de la droite

prend alors la forme suivante:

On peut encore réduire l'équation si ont remplace

par m qui est la pente de la droite. Celle-ci représente la variation de Y sur la variation de x

correspondante. Reprenant l'équation de la droite à sa forme initiale, la constante est égale à

la pente et la seconde constante est égale à la valeur de Y lorsque X = 0.

2.4. La méthode des moindres carrés

Tel que mentionné plus haut, il est préférable d'éviter de porter un jugement personnel pour

l'obtention d'une courbe d'ajustement. On doit donc trouver la meilleure courbe d'ajustement.

C'est le rôle de la méthode des moindres carrés.

Examinons la figure suivante:

Figure 1. Exemple de courbe d’ajustement au sens des moindres carrés

Pour une valeur quelconque de X, par exemple X1, il y aura une différence ou erreur entre la

valeur de Y1 et la valeur qui lui correspond sur la courbe. Cette erreur est indiquée par E sur

la figure 1. Les valeurs que peuvent prendre celles-ci peuvent être positives, négatives ou

simplement, nulles.

On peut mesurer l'efficacité d'un ajustement en effectuant la somme des carrés des erreurs de

l'ensemble des données. Si cette somme est petite, l'ajustement est considéré comme étant

bon. À l'inverse, si elle est grande, l'ajustement est mauvais. On dit donc que parmi toutes les

courbes possibles qui s'approchent d'un ensemble de données, celle révélant le meilleur

ajustement est celle qui correspond à la propriété suivante:

TP Algorithmique Master 1 – IMI 2010/2011

Courbes d’ajustement au sens des moindres carrées avec VBA

K.MOUATS 3

minimum =

On appelle courbe des moindres carrées une courbe qui vérifie cette propriété.

La droite des moindres carrés ajustant les points , ,..., possède

l'équation suivante:

Les constantes et peuvent être déterminées en résolvant les équations suivantes

simultanément:

Ces équations se nomment 'équations normales de la droite des moindres carrés'. On peut

calculer les constantes et à l'aide des formules suivantes:

et

TP Algorithmique Master 1 – IMI 2010/2011

Courbes d’ajustement au sens des moindres carrées avec VBA

K.MOUATS 4

3. Réalisation :

La Macro VBA qui calcule les paramètres de la courbe d’ajustement au sens des moindres

carrés :

Nous avons réussi à implémenter la macro qui lit une série de données (x,y), affiche la

dispersion (nuage de points) dans un graphique et calcule les paramètres de la droite

d’ajustement (pente et déplacement). En voici le programme associé.

‘Lecture des coordonnées (x,y) et calcul des paramètres de la droite d’ajustement

Dim dimension As String; Dim nb_pt As String; Dim degre_MC As String

Dim espace_MC As Integer; Dim point As Integer; Dim x_point As String

Dim y_pont As String; Dim i As Integer

dimension = InputBox("donner le degre de MC :") ‘ MC = Moindres carrées

espace_MC = CSng(dimension)

nb_pt = InputBox("donner le nombre de points :")

point = CSng(nb_pt)

Option Explicit

Option Base 1

Dim x(point) As Single ; Dim y(point) As Single ; Dim x_moy As Single

Dim y_moy As Single; Dim s_num As Single ;Dim s_den As Single

Dim A As Single; Dim B As Single

For i = 1 To point

x_point = InputBox("donner la valeur de x" & i)

x(i) = CSng(x_point)

y_point = InputBox("donner la valeur de y" & i)

y(i) = CSng(y_point)

Next i

x_moy = 0

y_moy = 0

For i = 1 To point

x_moy = x_moy + x(i)

y_moy = y_moy + y(i)

Next i

x_moy = x_moy / point

y_moy = y_moy / point

If espace_MC = 1 Then

s_num = 0

s_den = 0

For i = 1 To point

s_num = s_num + (x(i) - x_moy) * (y(i) - y_moy)

s_den = s_den + (x(i) - x_moy) ^ 2

Next i

A = s_num / s_den ‘ la pente de la droite

B = y_moy - A * x_moy ‘ le déplacement

End If

‘ Droite d’ajustement au sens des moindres carrées : Y = AX + B

End Sub

TP Algorithmique Master 1 – IMI 2010/2011

Courbes d’ajustement au sens des moindres carrées avec VBA

K.MOUATS 5

Nous n’avons pas réussi à trouver la fonction/procédure VBA qui permet de tracer une courbe

à partir des deux paramètres précédemment calculé. Nous avons alors décidé d’opter pour le

principe de la feuille de calcul EXCEL.

Dans la deuxième partie, les données X et Y sont introduites dans une feuille de calcul

EXCEL (2 colonnes). La macro affiche le nuage de points dans une fenêtre graphique

EXCEL. En voici le corps de la macro

A l’aide de ce programme, on sélectionne la feuille de calcul, source des données pour

lesquelles on veut tracer la courbe d’ajustement au sens des moindres carrées.

Les deux colonnes X et Y sont délimitées (sélectionnées) et le nuage de points est affiché

dans un graphique.

On fait appel à une fonction VBA, Trendelines(), paramétrable, et qui permet de tracer la

courbe d’ajustement (régression, tendance) : linéaire, parabolique et cubique.

Pour la première partie de la réalisation, nous avons implémenté une macro que nous avons

insérée dans la barre d’outils et qui peut être lancée en ouvrant le fichier TP-Moindres-

Carrees.xls. La macro trace la droite d’ajustement associée aux données sélectionnée X et Y et trace

également leur dispersion (nuages de point).

L’icône associée à la macro est Macro1

:

NB. Remplir les colonnes des données X et Y

La macro permet également d’afficher l’équation de la droite d’ajustement.

‘Lecture des coordonnées (x,y) et calcul des paramètres de la droite d’ajustement

La macro pour graphique par selection

Dim mongraph As Chart, mafeuille As Worksheet, plagedonnees As Range

Set mafeuille = ThisWorkbook.Worksheets("Feuil1")

With mafeuille

Set plagedonnees = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Resize(, 2)

plagedonnees.Select

End With

Set mongraph = ThisWorkbook.Charts.Add

mongraph.ChartType = xlXYScatter

‘Sélection de 2 premières colonnes de données dans la feuille de calcul et affichage du

graphique EXCEL – nuage de points

TP Algorithmique Master 1 – IMI 2010/2011

Courbes d’ajustement au sens des moindres carrées avec VBA

K.MOUATS 6

Voici un exemple de ce que cette macro donne comme résultat.

Données X Données Y(X)

2 111

3 211

1 14

5 22

11 7

17 9

5 32

6 41

21 13

8 11

18 211

9 61

19 14

7 12

27 1

11 3

La droite d’ajustement au sens des moindres carrées résultante el exécutant la macro est :

Droite d ajuustement au sens des moindres carrees

y = -1,694x + 66,311

0

50

100

150

200

250

0 5 10 15 20 25 30

Données Y(X)

Linéaire (Données Y(X))

TP Algorithmique Master 1 – IMI 2010/2011

Courbes d’ajustement au sens des moindres carrées avec VBA

K.MOUATS 7

Droite d ajuustement au sens des moindres carrees

y = -4,4638x + 82,364

-50

0

50

100

150

200

250

0 5 10 15 20 25

Données Y(X)

Linéaire (Données Y(X))

Pour tester la fiabilité de notre programme, on vire la moitié des données X et Y et on relance

la macro.

Données X Données Y(X)

2 111

3 211

1 14

5 22

11 7

17 9

5 32

6 41

21 13

8 11 La droite d’ajustement calculée :

On constate parfaitement le changement de l’équation de la droite et le nombre de points

affichés.

TP Algorithmique Master 1 – IMI 2010/2011

Courbes d’ajustement au sens des moindres carrées avec VBA

K.MOUATS 8

parabole d ajuustement au sens des moindres carrees

y = 0,0958x2 - 4,1954x + 76,847

0

50

100

150

200

250

0 5 10 15 20 25 30

Données Y(X)

Polynomial (Données Y(X))

Dans la deuxième partie de la réalisation, nous avons mis à la disposition de l’utilisateur le

choix entre un ajustement linéaire (droite), parabolique ou cubique via un UseForm1 nommé

Courbes d’Ajustement Khi2.

La transition entre les différentes demandes de formats de courbes d’ajustement doit se faire

obligatoirement en cliquant sur le bouton vert : Again qui préparera la requête suivante.

Les données X et Y peuvent êtres modifiées ou étendues à chaque moment de l’exécution.

Voici quelques imprimes d’écrans de tests réalisés sur le tableau mentionné dans la page 6.

TP Algorithmique Master 1 – IMI 2010/2011

Courbes d’ajustement au sens des moindres carrées avec VBA

K.MOUATS 9

courbe cubique d ajuustement au sens des moindres carrees

y = -0,0455x3 + 1,9716x

2 - 24,804x + 127,59

-50

0

50

100

150

200

250

0 5 10 15 20 25 30

Données Y(X)

Polynomial (Données Y(X))

Remarque :

Pour le bon déroulement des différentes exécutions, il faut que la page active d’EXCEL soit

celle des données. Un graphique affiché pourrait bloquer un lancement du programme

(problème de référence SetSourceData de VBA).

4. Conclusion :

Dans ce travail, nous avons utilisé VBA pour la mise en œuvre d’un noyau de

réalisation de calcul et traçage de courbes d’ajustement au sens des moindres carrés :

droite d’ajustement linéaire, parabolique et cubique.

La difficulté à trouver la fonction VBA pour tracer une courbe à partir de sa pente et

son déplacement nous a orientés vers l’utilisation de fonctions VBA prédéfinies pour

le traçage de courbes de tendances.

Nous avons obtenu une bonne représentation graphique des courbes d’ajustement, un

calcul qui s’adapte avec la plage de données sélectionnées. Un calcul de la qualité de

la solution (courbe) : valeur de l’erreur de l’ajustement aurait été un plus pour

l’évaluation de la solution.

Un module de traitement d’aléas / erreurs de manipulation, de données … etc. est à

prévoir car il n’a pas été intégré dans cette réalisation.

Vu la lourdeur de VBA (constat personnel) et la difficulté rencontrée dans son

utilisation, l’exécutable n’a pas été généré mais tout le code source a été fourni.