Licence de Psychologie - TD n 3

Calculs statistiques à l'aide d'un tableur : l'exemple d'Excel

 

Vous avez utilisé l'an dernier deux logiciels tableurs : le module tableur d'AppleWorks et Microsoft Excel. Leurs fonctionnalités de base sont semblables. Une caractéristique différencie cependant Excel d'AppleWorks : dans Excel, le document de base n'est pas la traditionnelle "feuille de calcul", mais un "classeur", empilement de plusieurs feuilles mémorisées dans un unique fichier du disque.

1 - Gérer les feuilles d'un classeur : renommer, supprimer, insérer

Ouvrez le document W:\PSY3\TD EXCEL\MANAGE.XLS et réenregistrez-le sur un disque accessible en lecture/écriture (le disque E: ou votre disquette).

Le document comporte trois feuilles. Dans la première, on a inséré un objet "Document WordPad" avec quelques indications concernant la situation proposée. Dans la deuxième, ont été rassemblées les données observées.

On passe d'une feuille à l'autre en cliquant sur l'onglet voulu :

Renommez (à l'aide d'un double clic sur leur onglet) les deux premières feuilles du classeur. La première pourra être appelée Enoncé, et la deuxième Données.

Supprimez la dernière feuille du classeur (clic sur l'onglet pour sélectionner une feuille, puis menu Edition-Supprimer une feuille).

Revenez à la feuille Données et insérez une colonne devant la colonne A. Celle-ci nous servira ultérieurement, pour indiquer des légendes.

2 - Calculs simples. Recopier vers la droite et vers le bas

Les données constituent deux échantillons appariés (chaque ligne correspond à un trimestre donné). Il est donc naturel de s'intéresser à la différence des valeurs portées dans les colonnes C et D.

Saisissez en cellule E1 le texte Différence, puis en cellule E2 la formule : =C2-D2

Recopiez ensuite cette formule jusqu'en E16 (menu Edition - Recopier... En bas, ou faites glisser le coin inférieur droit de la cellule E2).

Indiquez les légendes suivantes :

Saisissez ensuite les formules suivantes :

Remarque 1. Pour rentrer ces formules, vous pourrez utiliser l'assistant, à condition d'apprendre à réduire ou déplacer sa fenêtre, puis qu'il a la bonne habitude de masquer les choses utiles...

Remarque 2. Alors qu'AppleWorks utilise une notation du type C2..C16 pour désigner une plage de cellules, Excel utilise la notation C2:C16.

Remarque 3. Les fonctions VAR() et ECARTYPE() calculent la variance et l'écart type corrigés (variance obtenue en divisant la somme des carrés des écarts à la moyenne par N-1) alors que VAR.P() et ECARTYPEP() calculent la variance et l'écart type "habituels".

Recopiez la plage C18:C24 vers la droite jusqu'en colonne E.

3 - Mise en forme de la feuille- Formats de nombres

Utilisez le menu Format-Cellule - onglet Nombre pour afficher les valeurs des variances et écarts types avec deux décimales.

Améliorez ensuite la présentation de l'ensemble en modifiant les styles de polices de caractères, en définissant des bordures, etc.

4 - L'utilitaire d'analyse

Du point de vue des traitements statistiques, les fonctions de base d'Excel n'en font guère qu'une calculatrice améliorée avec des tables numériques intégrées. Cependant, le logiciel possède un langage de programmation très complet, et il est possible d'ajouter au programme de base des modules additionnels spécialisés dans des traitements particuliers. Microsoft fournit en standard le module "Utilitaire d'analyse", dont nous allons maintenant donner un premier aperçu.

Selon la façon dont Excel a été installé sur le poste sur lequel vous travaillez, plusieurs cas sont possibles :

4.1. Statistiques descriptives avec l'utilitaire d'analyse

 

Grâce à l'utilitaire d'analyse, nous allons retrouver la plupart des résultats précédents, sans avoir à écrire de formules.

Sélectionnez ensuite le menu Outils-Utilitaire d'analyse, puis, dans la liste affichée, l'item Statistiques Descriptives.

Pour calculer les paramètres des variables H_Incident et D_Incident, on pourra compléter la fenêtre de dialogue de la façon suivante :

Excel compose alors une feuille supplémentaire ayant l'allure ci-dessous, dans laquelle nous pouvons prélever les résultats qui nous intéressent.

H_Incident

D_Incident
Moyenne

13.13333333

Moyenne

11.26666667

Erreur-type

0.798013406

Erreur-type

0.658039416

Médiane

12

Médiane

12

Mode

12

Mode

13

Écart-type

3.090692633

Écart-type

2.548575699

Variance de l'échantillon

9.552380952

Variance de l'échantillon

6.495238095

Kurstosis (Coefficient d'applatissement)

1.706985417

Kurstosis (Coefficient d'applatissement)

0.076336964

Coefficient d'assymétrie

1.196117834

Coefficient d'assymétrie

0.582368159

Plage

12

Plage

9

Minimum

9

Minimum

8

Maximum

21

Maximum

17

Somme

197

Somme

169

Nombre d'échantillons

15

Nombre d'échantillons

15

 

5 - Adressage relatif et absolu. Nommer des cellules

L'utilitaire d'analyse ne résout malheureusement pas tous les problèmes. En particulier, il ne fournit aucun item permettant de calculer une distance du khi-2 ou de faire un test du même nom.

5.1 Références absolues et références relatives

Ouvrez le document W:\PSY3\TD EXCEL\OPTIQUES SANS HIC.XLS et réenregistrez-le sur un disque accessible en lecture/écriture (le disque E: ou votre disquette).

Dans la feuille Enoncé, on a inséré un objet "Document WordPad" avec quelques indications concernant la situation proposée. Lisez-le attentivement.

Dans la deuxième, un tableau de contingence résume les données observées.

Dans la plage A11:F15, on veut construire un tableau des fréquences, calculées par rapport à l'effectif total.

Pour calculer la fréquence du premier couple de modalités, on peut saisir en B12 la formule =B4/F7. Cependant, cette formule ne peut pas être recopiée vers la droite ou vers le bas. Pourquoi ?

Pour résoudre ce problème, deux méthodes peuvent être utilisées. Chacune d'elles va être expérimentée sur une partie du tableau.

Première méthode : utiliser une référence absolue pour l'effectif total.

Deuxième méthode : définir un nom

Comme on peut le voir sur cet exemple, un nom, lorsqu'il est appliqué à une cellule unique, équivaut à une référence absolue.

A l'aide de Format-Cellule onglet Nombre catégorie Pourcentage, affichez les fréquences sous forme de pourcentages à deux décimales.

5.2 Références mixtes : calcul du tableau des effectifs théoriques

Nous voulons constituer dans la plage H3:L6 le tableau des effectifs théoriques (sous hypothèse d'indépendance entre les deux variables étudiées). Autrement dit, nous voulons que chacune des cellules de la plage I4:L6 contienne la "formule" : (total ligne)*(total colonne)/(total général).

Ni les références relatives, ni les références absolues ne vont ici convenir. Pourquoi ? Excel autorise cependant également les références mixtes, relatives sur la ligne et absolues sur la colonne, ou l'inverse.

5.3 Calcul des contributions au khi-2. Première méthode

Le calcul des contributions au khi-2 sera fait dans les cellules H11:L14.

Calcul du khi-2

Remarque 1 : On peut aussi nommer Contrib la plage I12:L14 et saisir la formule =SOMME(Contrib).

Remarque 2 : En calcul manuel, nous calculons en général le khi-2 à l'aide d'un tableau dans lequel les contributions sont rassemblées dans une seule colonne. Mais un tableur fait aussi bien une somme sur un tableau rectangulaire, et les contributions sont ainsi plus faciles à calculer.

5.4 Calcul des contributions au khi-2. Deuxième méthode : calcul sur des "matrices"

 

Nous allons utiliser la plage H20:L23 pour calculer les contributions au khi-2 en utilisant les possibilités de calcul "matriciel" d'Excel.

Remarque. Nous verrons dans une prochaine séance qu'il est en fait inutile de calculer les contributions au khi-2 pour faire faire à Excel un test du khi-2. En revanche, il est indispensable de calculer les effectifs théoriques.

6. Manipuler un tableau de grande taille. Tris à plat

Ouvrez le document W:\PSY3\TD EXCEL\MIREAULT.XLS.

La deuxième feuille du classeur contient un tableau de grande taille (20 variables observés et 381 sujets, mais certaines valeurs sont manquantes).

Dans une première phase de l'étude de ces données, on veut effectuer un recensement ou tri à plat pour chaque variable. En TD, nous traiterons le cas de la variable YearColl. Les autres variables pourront être traitées en monitorat.

6.1 Diviser la fenêtre pour afficher simultanément les premières et les dernières lignes du tableau.

Affichez la feuille Données MIREAULT.

Sélectionnez la colonne D (clic sur la tête de colonne), copiez-la puis collez-la comme colonne A d'une nouvelle feuille. Cette nouvelle feuille sera nommée : YearColl.

Affichez la feuille YearColl. Déplacez le rectangle situé au dessus de l'ascenseur vertical pour diviser la fenêtre en deux sous-fenêtres.

   

Explorez l'ensemble des modalités de la variable YearColl, en calculant en D2, D3 et D4 respectivement le nombre d'observations, la modalité minimum et la modalité maximum. Utilisez pour cela les fonctions MAX, MIN,et NB.

Remarquez l'intérêt présenté par la division de notre fenêtre. La formule =NB(A2:A382), par exemple, pourra être saisie en :

6.2 Fonctions renvoyant une matrice. L'exemple de la fonction FREQUENCE.

On veut faire un tri à plat selon les valeurs de la variable YearColl, c'est-à-dire constituer un tableau d'effectifs relatif à cette variable. L'outil fourni par Excel pour réaliser ce travail est la fonction FREQUENCE. Celle-ci présente la particularité d'être une fonction matricielle : elle renvoie, non pas une, mais plusieurs valeurs numériques et il faut donc saisir la formule dans une matrice de taille convenablement choisie.

Saisissez les différentes modalités dans les cellules C7 à C10 :

C

D

7

1

8

2

9

3

10

4

 

Sélectionnez ensuite la plage de cellules D7:D10 et saisissez la formule :

Validez par Ctrl+Maj+Entrée.

Remarque.

1) Selon les bonnes habitudes américaines, la fonction FREQUENCE renvoie des effectifs et non des fréquences...

2) La fonction FREQUENCE permet d'autres calculs d'effectifs :

- Si toutes les modalités ne sont pas présentes dans le tableau passé en deuxième paramètre, les résultats fournis sont les effectifs de classes ]ai-1, ai] déclarées implicitement par le tableau.

- Si on spécifie un nombre au lieu d'un tableau en deuxième paramètre, FREQUENCE calcule alors l'effectif cumulé correspondant à ce paramètre. Il est inutile dans ce cas de définir une matrice en validant la formule.

Exemple :

Recopiez dans une nouvelle feuille la série d'observations de la variable SomT. Calculez, comme précédemment, le nombre d'observations, le minimum et le maximum des modalités observées. Réalisez un tableau d'effectifs et d'effectifs cumulés relatifs au découpage en classes :

0 ; 41 ; 50 ; 55 ; 60 ; 65 ; 70 ; 75 ; 80

en saisissant les données et formules suivantes :

C

D

E
6 Effectifs Cumuls
7 41 ={FREQUENCE(A2:A382;C7:C14)} =FREQUENCE($A$2:$A$382;C7)
8 50 ={FREQUENCE(A2:A382;C7:C14)} =FREQUENCE($A$2:$A$382;C8)
9 55 ={FREQUENCE(A2:A382;C7:C14)} =FREQUENCE($A$2:$A$382;C9)
10 60 ={FREQUENCE(A2:A382;C7:C14)} =FREQUENCE($A$2:$A$382;C10)
11 65 ={FREQUENCE(A2:A382;C7:C14)} =FREQUENCE($A$2:$A$382;C11)
12 70 ={FREQUENCE(A2:A382;C7:C14)} =FREQUENCE($A$2:$A$382;C12)
13 75 ={FREQUENCE(A2:A382;C7:C14)} =FREQUENCE($A$2:$A$382;C13)
14 80 ={FREQUENCE(A2:A382;C7:C14)} =FREQUENCE($A$2:$A$382;C14)
15 =SOMME(D7:D14)

 

On veut déterminer à líaide díun test du khi-2 si le type díétudes suivies (variable College) dépend du sexe (variable Gender). Copiez les colonnes concernées dans une autre feuille et faites des tris sur ces colonnes de manière à pouvoir constituer un tableau de contingence. Calculez ensuite le khi-2 observé sur ce tableau.

6.3 Etablissement d'un tableau de contingence

Il ne semble pas exister de fonction donnant directement un tableau de contingence à partir d'un tableau protocole. Cependant, le menu Données - Trier ... et la fonction FREQUENCE permettent de s'en sortir sans trop de peine.

On veut, par exemple, croiser les variables Gender (sexe) et College (type d'études).

Pour chacun des deux sous-groupes définis par les deux valeurs de la variable Gender, utilisez la fonction FREQUENCE pour calculer les effectifs. On obtiendra par exemple le tableau suivant :

D

E

F

7
Coll\Gender

1

2

8
1 {=FREQUENCE(B2:B144;D8:D12)} {=FREQUENCE(B145:B382;D8:D12)}

9
2 {=FREQUENCE(B2:B144;D8:D12)} {=FREQUENCE(B145:B382;D8:D12)}

10
3 {=FREQUENCE(B2:B144;D8:D12)} {=FREQUENCE(B145:B382;D8:D12)}

11
4 {=FREQUENCE(B2:B144;D8:D12)} {=FREQUENCE(B145:B382;D8:D12)}

12
5 {=FREQUENCE(B2:B144;D8:D12)} {=FREQUENCE(B145:B382;D8:D12)}

 

7. Exercices de monitorat

Exercice 1

Ouvrez le document W:\PSY3\TD EXCEL\INTERNAT.XLS.

Comme dans le cas du document MANAGE.XLS, calculez les paramètres (moyenne, variance díéchantillon et variance corrigée, etc) des deux séries proposées, ainsi que les paramètres de la différence.

Exercice 2

Reprendre líexercice 6 de la fiche de TD de statistiques.

Líénoncé est le suivant :

Soit deux questions díun test, A et B, auxquelles un groupe de 184 sujets a répondu. On voudrait savoir si la fréquence des réussites et des échecs est la même pour A et B. Le tableau des effectifs observés est le suivant :

  B : réussite B : Echec
A : réussite

62

23
A : Echec

58

41

Construire le tableau díeffectifs théoriques permettant díutiliser le khi-2 de Mac Nemar et calculer la distance du khi-2 entre les deux tableaux.

Exercice 3 : Reprendre le document W:\PSY3\TD EXCEL\MIREAULT.XLS.

1) Poursuivre le travail commencé en 5.3 : calculez le tableau d'effectifs théoriques et le khi-2 observé. Conclure à l'aide d'un test au seuil de 5%.

2) Effectuez des tris à plat pour les autres variables du document.

3) Constituez de même un tableau de contingence pour les variables Group et GPA.

4) Les sujets sont répartis en trois groupes selon les valeurs de la variable Group. Calculez les moyennes observées pour les variables des colonnes I à U pour chacun des trois groupes.

8. Les fonctions statistiques d'Excel

Ci-dessous, une liste de fonctions statistiques disponibles sous Excel (source : aide en ligne).