Traitement de données avec tableur appliqué à l'Economie et la Gestion. - Modèle 1 : Le salaire en fonction de l'ancienneté

Question

Q1 : Recherche du modèle M1 : y=ax+b

En utilisant la fonction DROITEREG du tableur, trouver les valeurs a et b correspondant à l'équation de régression Salaire = a Ancienneté + b + erreur minimisant les erreurs (voir figure 5.21). Le paramètre b représente le salaire à l'embauche (0 année d'ancienneté) et la valeur de a représente l'augmentation annuelle de salaire, si ce modèle correspond à la réalité, bien sûr.

Figure 5.21 : modèle de régression Salaire=a.Ancienneté + b sur les données du fichier « salaires.txt »

Solution

Question

Q2 : Utilisation du modèle M1 trouvé pour les prédictions et la visualisation

Si on remplace l'ancienneté par un nombre quelconque, on a une estimation du salaire correspondant à cette ancienneté selon le modèle. Écrire dans le tableur quelques valeurs d'ancienneté puis calculer le salaire estimé correspondant en utilisant les valeurs de a et de b obtenues (voir colonnes K et L de la figure 5.22). Faire le graphique permettant de comparer valeurs observées et valeurs estimées comme en figure 5.22.

Figure 5.22 : Utilisation du modèle de régression pour dessiner la droite de régression

Solution

Question

Q3 : Utilisation de l'outil « ajout d'une courbe de régression linéaire » du tableur

Le tableur permet de dessiner directement les courbes de régression sans faire de calcul. Certains tableurs donnent même l'équation de régression (comme Excel, voir figure 5.23 et OpenOffice3.1). On accède à ces tracés en sélectionnant la série de données, puis en choisissant « Graphique> Ajouter une courbe de tendance » pour Excel et « Insertion>Statistiques » sous OpenOffice. On a alors le choix entre plusieurs courbes de régression. Toutefois cela n'est possible que pour les modèles très simples, comportant une seule variable explicative. Contrôler que la droite que vous avez tracée et la droite de régression dessinée automatiquement par le tableur coïncident, et que l'équation indiquée, ainsi que le coefficient de détermination correspondent bien aux valeurs trouvées avec la fonction DROITEREG.

Figure 5.23 : En rouge la droite de régression linéaire figurant dans les options du graphique avec Excel 2003

Solution

Question

Q4 : Les résidus au modèle M1

Ajouter une colonne intitulée « écarts » (colonne F de la figure 5.24) contenant pour chaque sujet la différence entre son salaire réel, figurant dans la colonne correspondante, et son salaire estimé d'après l'équation de régression (on utilisera dans la formule, les cellules où figurent a et b, et celles ou figurent x et y). Vérifier qu'on a bien pour moyenne de ces écarts approximativement 0, et pour écart-type, approximativement la valeur figurant dans le tableau à côté de R² (cellules K12 et K13 de la figure 5.24).

Si le modèle est bon, les erreurs suivent une loi normale d'espérance (moyenne) 0, et écart-type s, valeur qui a été rendue par le tableur à côté de R² dans la matrice de résultats. Dans ce cas, on ne doit trouver que 34% des valeurs au maximum à l'extérieur de la bande où y est compris entre –s et s, et au moins 95% des valeurs entre 2s et 2s, le tout étant réparti de façon équilibrée (symétriquement) autour de la droite y=0. Faire un graphique où figurent les points (en utilisant un nuage de points construit sur la seule colonne des écarts) et les droites y=-2s, y=-s, y=0, y=s et y=2s, en insérant des nouvelles données au graphique, ces séries étant situées dans un tableau avec 2 valeurs de x (1 et 100), et pour chacune de ces valeurs, les 5 valeurs de y correspondant aux droites. On doit obtenir le graphique à droite de la figure 5.24.

Figure 5.24 : Valeurs des résidus à gauche en colonne F, graphique à droite avec leur répartition en 6 zones devant contenir, en théorie, respectivement 2,5%, 14,5%, 33%, 33%, 14,5%, 2,5% des points.[Zoom...]

On voit que les conditions de pourcentage paraissent vérifiées (par exemple, on a 4 points dans la première zone à partir du haut, ce qui n'est pas loin des 2,5% attendus), mais pas les conditions de symétrie (au dessus de la droite d'équation y=0, les points s'étalent en hauteur, alors qu'ils sont quasiment alignés sur une droite en dessous de l'axe des x). Pour repérer les individus, trions l'ensemble des colonnes selon le sexe, les femmes se trouvant de la ligne 2 à 58 et des hommes après. On ne voit pas de différences notables entre ces deux parties (figure 5.25 à gauche, F : triangle rouge, M : losange bleu). Faire la même chose en triant selon la responsabilité. On voit (figure 5.25 à gauche, c1 : losange marron, c2 : carré vert, c3 : croix bleue) que pour la responsabilité c1, tous les écarts sont systématiquement négatifs, ce qui signifie que le salaire réel est bien inférieur au salaire estimé, et c'est le contraire pour les 2 autres niveaux de responsabilité. Les conditions de normalité des résidus ne sont plus respectées. On en conclut que le modèle M1 est insuffisant, que la variable « responsabilité » doit intervenir.

Figure 5.25 : Graphique de la figure 5.24 après avoir ordonné par sexe (à gauche), par responsabilité (à droite)[Zoom...]

Solution

Dans la cellule F2, on tape la formule =B2-C2*$H$4-$I$4, et on la recopie vers le bas.

Dans la cellule K12, =MOYENNE(F2:F99) qui doit être 0. On peut toutefois obtenir une valeur très proche de 0 sans lui être égale. En effet les calculs se font en gardant un nombre limité de chiffres après la virgule (la mémoire infinie n'existe pas en informatique), ce qui aboutit à de légers écarts aux valeurs exactes. Ces écarts sont plus importants quand les calculs sont plus lourds, comme pour ceux des coefficients de régression par les moindres carrés qui nécessitent d'inverser une matrice.

Dans la cellule K13, =ECARTYPE(F2:F99), qui donne un résultat proche de celui de la cellule I6, qui est la valeur estimée de l'écart-type des résidus, noté s.

On sélectionne alors la colonne des résidus et on la représente graphiquement par un nuage de points. Les résidus doivent suivre la loi normale N(0,s). Selon cette loi, on connaît les proportions attendues de valeurs dans les intervalles ]-infini, -2s), (-2s,-s), (-s,0), (0,s), (s,2s), (2s, infini[, qui sont respectivement de (ces intervalles sont choisis ainsi par tradition, mais on pourrait en choisir d'autres). En dessinant les 5 droites, cela permet de séparer les points des 6 zones. Pour les tracer, on remplit la zone de H15 à M17 comme indiqué dans la figure 2.24, puis on ajoute ces séries au graphique, en faisant un clic droit sur le graphique sélectionné, puis en choisissant plage de données, et en procédant comme indiqué figure 5.14. Signalons que sous Excel, on peut ajouter en une seule fois la plage correspondant aux 5 droites. On modifie alors le format de chacune pour obtenir la ligne attendue au lieu des 2 points de ses extrémités.

Figure 5.28 : Ajout dans le graphique de la droite de régression linéaire sous OpenOffice 3.1

Figure 5.29 : Les 3 séries décalées de résidus selon la responsabilité, avec ajout des numéros pour c2 et c3

Pour voir la dépendance entre les résidus et le sexe, on sélectionne la plage formée des 6 colonnes et des 99 lignes et on trie selon le sexe. Si on désire différencier les points selon les sexes, comme c'est fait dans le graphique de la figure 5.25, il faut créer 2 séries différentes. Comme la série totale est déjà représentée, on peut la modifier en remplaçant la plage F2 :F99 par F2 :F58, et en insérant une nouvelle série F59 :F99. On peut alors changer le format de chacune. On voit que les résidus ne dépendent apparemment pas du sexe. Inutile donc d'ajouter le sexe dans le modèle M1.

Ce qu'on a fait pour la variable « sexe » est fait de la même façon pour la variable « responsabilité ». On voit cette fois que les résidus dépendent fortement de la responsabilité, qui doit donc être mise dans le modèle.

Sous Excel, il est plus pratique d'insérer directement de nouvelles données, mais il faut les décaler par rapport aux anciennes pour en faire une nouvelle série et non de nouveaux points de la même série, ce qui reviendrait à ce qu'on avait avant. Une fois le graphique fait, on peut les remettre en place (voir figure 5.29).