Analyser des séries de données

Vous avez collecté des résultats de tests, recueilli des réponses à un questionnaire ou saisi des notes d’examen. Il s’agit maintenant de tirer des enseignements statistiques de ces données. Nous avons réuni, dans cette page, plusieurs problèmes pratiques, tous indépendants, pour lesquels nous détaillons la marche à suivre. Vous pourrez facilement les adapter à vos données personnelles, voire à d’autres tableurs. Mais comme nous nous adressons ici à des utilisateurs d’Excel avertis, nous ferons l’impasse sur des fonctions élémentaires telles que l’attribution d’un nom à un champ.

01. Classez les valeurs par plages

Vous avez demandé à plusieurs personnes de noter un même produit de 0 à 20. Dans notre exemple, ces notes figurent dans le champ A2:A16 en jaune. Pour savoir comment se répartissent les notes, saisissez des valeurs de seuils dans une colonne (C4:C7 en bleu). Sélectionnez le champ D4:D7 et saisissez la formule =FREQUENCE(A2:A16;C4:C7). Attention : ce n’est pas une formule classique : elle est matricielle, ne la validez pas en tapant Entrée mais Ctrl + Maj + Entrée. Une fois saisie, la formule apparaît entre accolades. Voici comment lire les résultats : deux notes sont inférieures ou égales à 5, cinq notes comprises entre 6 et 10, quatre notes entre 11 et 15 et quatre notes supérieures à 15. Vous pouvez choisir des seuils différents. De plus, rien ne vous oblige à respecter un intervalle constant entre les seuils. Le seul impératif est que ces paliers soient en ordre croissant.

02. Activez les utilitaires d’analyse

Pour appliquer plusieurs des exemples suivants, vous devez activer les utilitaires d’analyse. Comme ils sont déjà présents sur le disque dur, vous n’aurez donc pas besoin du DVD Office 2007 et n’aurez rien à télécharger. Cliquez sur le bouton Office et choisissez Options Excel. Activez l’onglet Compléments, cliquez sur Atteindre, cochez Utilitaires d’analyse (cette option se nomme parfois Analysis toolpak). Inutile de cocher Utilitaires d’analyse VBA. Enfin, validez par OK.

03. Utilisez les fonctions statistiques

Vous avez collecté des notes de candidats à un examen. Bien sûr, vous pouvez vous contenter d’en calculer la moyenne. Mais cette dernière ne vous renseignera pas sur la distribution de ces notes. Ainsi, les deux triplets de notes (2, 10 et 18) et (9, 10 et 11) ont tous les deux une moyenne de 10, mais sont pourtant fort différents ! Pour évaluer la dispersion des notes par rapport à la moyenne, utilisez la fonction ÉCART. MOYEN. Elle évalue la moyenne des écarts des valeurs par rapport à leur moyenne arithmétique. Ainsi, les deux séries illustrées ci-contre ont la même moyenne, mais présentent des écarts moyens très différents. Attention : ne confondez pas l’écart moyen avec l’écart-type, dont la définition est légèrement différente.

04. Extrayez les plus grandes valeurs d’une liste

Pour obtenir la valeur la plus élevée d’une liste de nombres, vous utilisez la fonction =MAX. Mais cette fonction ne vous donne qu’un seul résultat. S’il vous faut, par exemple la seconde ou troisième plus haute valeur de la liste, vous emploierez plutôt la fonction GRANDE. VALEUR. Dans l’exemple suivant, des températures sont notées dans le champ A3:A14. Pour obtenir les trois plus hautes valeurs en D4, D5 et D6, saisissez les rangs souhaités (1, 2 et 3) en C4, C5 et C6. Puis saisissez en D4 la formule =GRANDE. VALEUR($A$3:$A$14;C4) et recopiez cette formule en D5 et D6. Remarquez que pour n’écrire qu’une seule fois la formule et pouvoir la recopier en dessous, nous avons panaché dans la formule deux types de références. La référence absolue, identifiée par le symbole $ désigne la plage de cellules à analyser : cette plage est la même pour toutes les formules. Quant à la référence relative (celle qu’Excel utilise par défaut), elle spécifie le rang à extraire.

05. Collectez des données dans un tableau croisé

Paul, Martine, Jacques et Sylvie font régulièrement des voyages linguistiques à l’étranger. Pour chaque voyage, vous notez le nom de la personne, le pays visité et la durée en jours dans une liste au sommet de laquelle figure une ligne d’en-têtes. Rien n’interdit à la même personne de faire plusieurs voyages, voire de visiter plusieurs fois le même pays. Pour extraire des données chiffrées de cette liste, sélectionnez-la en totalité (en incorporant la ligne d’en-tête), activez le menu Insertion et cliquez sur Tableau croisé dynamique. Cochez Nouvelle feuille de calcul et validez par OK. Dans le volet de gauche, faites glisser respectivement les champs Prénom dans la zone Étiquettes de ligne, Pays dans Étiquettes de colonnes et Jours dans Valeurs. Vous obtenez immédiatement un tableau des jours de voyage totalisés par personne et par pays. Si vous voulez ne faire apparaître que quelques prénoms dans la liste, cliquez sur la flèche placée à droite de Étiquettes de lignes et décochez les personnes que vous ne voulez pas afficher. Un tableau croisé peut vous donner autre chose que le cumul des jours. Voici, par exemple, comment obtenir la durée maximale des séjours, par pays et par personne : faites un clic droit sur des nombres affichés dans la zone centrale et choisissez Paramètres des champs de valeurs. Dans le champ Nom personnalisé, saisissez Durée maximale et, dans la liste, sélectionnez Max puis validez par OK. Vous voyez, par exemple, que c’est Sylvie qui a fait le plus long séjour en Espagne et qu’il a duré 30 jours.

06. Organisez un vote

Nous terminerons avec un problème assez courant, mais assez complexe à traiter avec un tableur : un groupe doit nommer deux personnes à un poste quelconque. Pour cela, chaque membre du groupe désigne deux candidats parmi les postulants (un premier et un second choix). Un candidat reçoit 2 points chaque fois qu’il est cité comme premier choix par un votant ; et 1 point s’il est cité en deuxième choix. Les deux candidats vainqueurs sont ceux qui obtiennent le plus grand nombre de points. Dans notre exemple, les cinq candidats figurent dans le champ E3:E7 (en bleu) et les sept votants dans le champ A2:A8 (en jaune). Chaque votant saisit, en face de son nom, les deux candidats de son choix (colonnes B et C). Une macro va se charger du décompte des points de chaque candidat. Commencez par donner le nom Votes1 au champ B2:B8, le nom Votes2 au champ C2:C8 et le nom Points au champ F3:F7. Ouvrez alors l’éditeur VBA en tapant Alt + F11, déroulez le menu Insertion, Module et saisissez le listing ci-dessous. Précisons que cette macro n’est pas optimisée : on peut la faire plus courte, mais elle perdrait en lisibilité : nous avons en, effet décomposé en deux boucles distinctes le cumul des points pour les premiers et deuxièmes choix. Retournez à Excel, ouvrez la fenêtre Macros (Alt + F8), sélectionnez Vote et cliquez sur Exécuter. Les points de chaque candidat sont affichés en face de leur nom. Dans l’exemple ci-dessous, Martin et Oscar ont gagné… Attention : comme votre feuille Excel contient des macros, vous ne pourrez pas l’enregistrer avec le format XLSX classique : il vous faudra opter pour le XLSM, un format spécial permettant l’incorporation de macros. Pour cela, cliquez sur le bouton Office, choisissez Enregistrer sous et cliquez sur Classeur Excel prenant en charge les macros.