Annexe A Annexe A : Rappel sur Excel pour la science des données

Charger des données à l'aide d'Excel

Le chargement d'un jeu de données dans Excel s'effectue simplement en cliquant sur Fichier > Ouvrir un fichier. Cela ouvre une fenêtre d'explorateur de fichiers dans laquelle vous pouvez simplement sélectionner le fichier CSV que vous souhaitez charger. Nous allons utiliser une partie d'un jeu de données public, intitulé « SOCR- 1035 Records of Heights (in) and Weights (lbs) of Major League Baseball (MLB) Players » (ch1-SOCR-small.csv).

Une fois chargé, le jeu de données apparaît dans la fenêtre de la feuille de calcul, comme illustré à la Figure A4.

Figure A4 Jeu de données SOCR-small ouvert dans MS Excel (Utilisé avec l'autorisation de Microsoft)

Résumer des données à l'aide d'Excel

Nous continuerons à faire référence au même jeu de données sur les joueurs de la MLB. Par souci de simplicité, seuls 12 éléments relatifs aux voltigeurs (Outfielders) et lanceurs (Pitchers) de SD sont inclus dans la Figure A5.

Figure A5 ch1-SOCR-small.csv ouvert dans MS Excel

Rechercher des données à l'aide d'Excel

Vous pouvez rechercher des données dans un tableau de multiples manières avec Excel. Nous examinerons d'abord des méthodes sans formules Excel, puis nous aborderons quelques formules utiles.

Vous pouvez rechercher les cellules contenant un certain nombre ou du texte à l'aide de « Rechercher », comme à la Figure A6. La fenêtre contextuelle Rechercher comporte une zone de texte dans laquelle vous pouvez saisir n'importe quel nombre ou texte, et Excel recherchera et trouvera toutes les cellules qui contiennent le nombre ou le texte saisi.

Figure A6 Fonctionnalité Rechercher dans MS Excel (Utilisé avec l'autorisation de Microsoft)

Si vous souhaitez plutôt afficher toutes les lignes répondant à certains critères, vous pouvez utiliser « Filtrer », comme à la Figure A7. Une fois le filtre activé, vous verrez des flèches déroulantes sur chaque colonne du tableau. Ces flèches vous permettent de sélectionner des valeurs spécifiques que vous recherchez, puis de n'afficher que les lignes correspondant à ces valeurs.

Figure A7 Fonctionnalité Filtrer dans MS Excel (Utilisé avec l'autorisation de Microsoft)

Par exemple, si vous cliquez sur « Outfielder » dans la fenêtre Position, le tableau résultant ne contiendra que les lignes dont la Position est Outfielder, comme le montre la Figure A8.

Figure A8 Filtrer par position dans MS Excel (Utilisé avec l'autorisation de Microsoft)

Effectuons à présent des recherches plus sophistiquées à l'aide de formules Excel.

Représenter graphiquement des données à l'aide d'Excel

La représentation graphique des données, ou visualisation de données, peut être réalisée facilement avec Excel. Traçons un diagramme à barres à titre d'exemple. Vous pouvez commencer par cliquer simplement sur Insertion > Graphique à barres > icône Histogramme groupé (Figure A10).

Figure A10 Icône de graphique à barres dans MS Excel (Utilisé avec l'autorisation de Microsoft)

Une fois l'icône cliquée, un graphique à barres arbitraire apparaît. Il peut également s'agir d'un simple espace vide — cela dépend de la cellule qui était sélectionnée lorsque vous avez cliqué sur l'icône.

Cliquez maintenant sur la zone du graphique pour faire apparaître deux nouveaux onglets : Création de graphique et Format. Accédez à l'onglet Création de graphique et cliquez sur Sélectionner des données (Figure A11).

Figure A11 Menu Sélectionner des données dans MS Excel (Utilisé avec l'autorisation de Microsoft)

Dans la fenêtre Sélectionner la source de données, définissez la plage de données du graphique en surlignant A1:F13 (c'est-à-dire l'ensemble du tableau). Cela remplira automatiquement le reste de la fenêtre contextuelle (Figure A12).

Figure A12 Fenêtre contextuelle Sélectionner des données dans MS Excel (Utilisé avec l'autorisation de Microsoft)

Trois entrées — Height, Weight, Age — correspondent à chaque groupe de barres. Chaque groupe comportera 13 barres, chacune représentant différents joueurs de baseball. Pour l'instant, traçons uniquement la taille (Height). Vous pouvez le faire en supprimant les deux autres au moyen du bouton « - » situé sous la liste des entrées.

Cliquez sur OK et le diagramme à barres est généré comme à la Figure A13. Remarquez que les colonnes A à C s'affichent comme étiquettes de l'axe des x, comme indiqué dans la fenêtre contextuelle (voir la section Étiquettes de l'axe horizontal [catégorie] dans la Figure A12).

Figure A13 Le premier diagramme à barres avec trois types d'étiquettes sur l'axe des x (Position, Team et Name)

Si vous souhaitez n'afficher que les noms des joueurs, retournez dans la fenêtre Sélectionner la source de données et mettez à jour les étiquettes de l'axe horizontal pour qu'elles correspondent simplement à A2:A13 (Figure A14).

Figure A14 Conservation du seul nom sur l'étiquette de l'axe des x (Utilisé avec l'autorisation de Microsoft)

Le graphique résultant n'affichera plus que les noms des joueurs le long de l'axe des x (Figure A15).

Figure A15 Le deuxième graphique à barres affichant uniquement les noms sur l'axe des x

La modification de la mise en forme des différents éléments d'un graphique est également facile dans Excel. Vous pouvez soit 1) cliquer sur l'élément pour en modifier le texte ou le faire glisser pour le déplacer, soit 2) double-cliquer sur l'élément dans le menu contextuel correspondant. La Figure A16 montre le menu contextuel associé au titre du graphique. Notez que vous pouvez modifier divers aspects de la présentation du titre, tels que le Remplissage et la Bordure.

Figure A16 Modification de la mise en forme du graphique à barres (Utilisé avec l'autorisation de Microsoft)

EXEMPLE A.5

Problème

En utilisant MS Excel, tracez un nuage de points reliant le poids (Weight, axe des x) et la taille (Height, axe des y). Un nuage de points est un graphique qui représente chaque point de données sous la forme d'un point. L'emplacement de ce point est généralement déterminé par les axes x et y.

Solution

Une icône dédiée aux nuages de points se trouve sous Insertion (voir la Figure A17). Dans la fenêtre contextuelle Sélectionner la source de données, sélectionnez D1:E13 puisque les poids et les tailles se trouvent dans les colonnes D et E, comme illustré à la Figure A17 et à la Figure A18.

Figure A17 Icône de nuage de points (Utilisé avec l'autorisation de Microsoft)

Figure A18 Sélectionner des données pour tracer un nuage de points (Utilisé avec l'autorisation de Microsoft)

Le nuage de points obtenu est présenté à la Figure A19. Notez que les valeurs le long de l'axe des x sont les tailles, tandis que celles le long de l'axe des y sont les poids.

Figure A19 Le nuage de points initial

Vous pouvez modifier le titre du graphique ainsi que les titres des axes en double-cliquant sur le texte du titre et sur les axes eux-mêmes, comme à la Figure A20.

Figure A20 Modification du titre du nuage de points (Utilisé avec l'autorisation de Microsoft)

Dès que vous double-cliquez sur l'axe des x ou des y, un menu distinct apparaît et vous permet d'ajouter des titres d'axe, comme illustré à la Figure A21.

Figure A21 Modification du titre de l'axe du nuage de points (Utilisé avec l'autorisation de Microsoft)

Analyse Excel des mesures de tendance centrale et de dispersion

Excel vous permet de saisir une formule dans une cellule. Grâce à une formule, vous pouvez faire référence à la valeur d'une autre cellule ou produire un résultat basé sur la valeur d'une cellule (ou les valeurs de plusieurs cellules). Une formule commence toujours par le signe égal (=). Voici un exemple de formule calculant la moyenne de trois nombres situés dans les cellules A1, A2 et A3.

= AVERAGE(A1, A2, A3)

Supposons que vous ayez des nombres dans les cellules A1, A2 et A3 et que vous souhaitiez afficher la moyenne de ces trois nombres dans la cellule B1. La Figure A22 montre comment utiliser AVERAGE dans ce cas de figure.

Figure A22 Utilisation de AVERAGE dans MS Excel (Utilisé avec l'autorisation de Microsoft)

Une fois la saisie de la formule terminée dans B1, appuyez sur Entrée. La formule dans B1 sera remplacée par le résultat du calcul — 20 (voir la Figure A23).

Figure A23 Résultat de l'utilisation de AVERAGE dans MS Excel (Utilisé avec l'autorisation de Microsoft)

EXEMPLE A.6

Problème

Dans ch1-SOCR-small.csv, calculez la taille moyenne des joueurs de la MLB du jeu de données à l'aide de AVERAGE.

Solution

Sur la feuille de calcul où le jeu de données des joueurs de la MLB est ouvert, tapez cette formule dans n'importe quelle cellule vide et appuyez sur la touche Entrée (Figure A24).

=AVERAGE(D2:D13)

Cela calculera la moyenne des cellules D2, D3, D4, …, jusqu'à D13. La taille moyenne obtenue est de 73,3333.

Figure A24 Calcul de la taille moyenne à l'aide de AVERAGE dans MS Excel (Utilisé avec l'autorisation de Microsoft)

Autres formules utiles pour résumer des données

Il existe de nombreuses autres façons d'utiliser des formules Excel simples pour analyser les valeurs de données. Vous pouvez saisir chacune des formules d'exemple répertoriées dans le Tableau A1 pour observer son fonctionnement.

Formule	Description
=MEDIAN(D2:D13)	Calcule la médiane de D2, D3, …, et D13. Renvoie 72,5 comme résultat.
=STDEV(D2:D13)	Calcule l'écart type de D2, D3, …, et D13. Renvoie 3,28 comme résultat.
=MIN(D2:D13)	Trouve la valeur minimale parmi D2, D3, …, et D13. Renvoie 70 comme résultat.
=MAX(D2:D13)	Trouve la valeur maximale parmi D2, D3, …, et D13. Renvoie 82 comme résultat.
=SUM(D2:D13)	Additionne les valeurs de D2, D3, …, jusqu'à D13. Renvoie 880 comme résultat.
=COUNTIF(D2:D13, 72)	Compte le nombre d'occurrences de « 72 » dans la plage D2, D3, …, jusqu'à D13. Renvoie 3 comme résultat (c'est-à-dire qu'il y a trois 72 dans D2:D13).

Tableau A1 Exemples de formules MS Excel

EXEMPLE A.7

Problème

Dans ch1-SOCR-small.csv, écrivez une formule qui calcule le poids médian des joueurs.

Solution

La formule est

=MEDIAN(E2:E13)

Cette formule détermine la médiane pour les cellules E2, E3, E4, …, jusqu'à E13, qui est égale à 200. Si vous ignorez ce qu'est la médiane, ne vous inquiétez pas ! Vous en apprendrez davantage à ce sujet dans la section Mesures de position.

De même qu'il propose une fonction intégrée pour la moyenne (=AVERAGE), Excel fournit une fonction permettant de calculer l'écart type d'un échantillon pour un jeu de données : =STDEV.S (pour l'écart type échantillonnal). Pour obtenir ces résultats statistiques dans Excel, saisissez les valeurs des données dans une colonne. Supposons que les données soient placées dans les cellules A2 à A11. Dans une cellule quelconque, tapez la commande Excel =AVERAGE(A2:A11) et appuyez sur Entrée. Excel calculera la moyenne arithmétique dans cette cellule. Ensuite, dans une autre cellule, tapez la commande Excel =STDEV.S(A2:A11) et appuyez sur Entrée. Excel calculera l'écart type échantillonnal dans cette cellule. La Figure A25 montre la moyenne et l'écart type pour les 10 âges.

Figure A25 Moyenne et écart type dans MS Excel (Utilisé avec l'autorisation de Microsoft)

Voici une liste plus complète de diverses fonctions statistiques disponibles dans Excel. Le lecteur est invité à explorer le menu Formules dans Excel et à sélectionner l'un des sous-menus, tel que « Financier », « Maths et trigonométrie », « Recherche et référence », etc., pour découvrir différentes formules Excel. Sous l'option « Plus de fonctions », sélectionnez STATISTIQUE pour afficher un menu répertoriant les différentes fonctions statistiques offertes par Excel. Le Tableau A2 présente un échantillon de ces fonctions statistiques :

Fonction	Objectif
=AVERAGE(A1:A10)	Calcule la moyenne d'un ensemble de nombres.
=MEDIAN(A1:A10)	Calcule la médiane d'un ensemble de nombres.
=STDEV.S(A1:A10)	Calcule l'écart type pour un ensemble de nombres représentant un échantillon.
=STDEV.P(A1:A10)	Calcule l'écart type pour un ensemble de nombres représentant une population.
=VAR.S(A1:A10)	Calcule la variance pour un ensemble de nombres représentant un échantillon.
=VAR.P(A1:A10)	Calcule la variance pour un ensemble de nombres représentant une population.
=MIN(A1:A10)	Trouve le minimum d'un ensemble de nombres.
=MAX(A1:A10)	Trouve le maximum d'un ensemble de nombres.
=MAX(A1:A10) – MIN(A1:A10)	Calcule l'étendue d'un ensemble de nombres.
=CORREL(A1:A10, B1:B10)	Calcule le coefficient de corrélation « r » pour des données (x, y). Les données x se trouvent dans les cellules A1 à A10 ; les données y se trouvent dans les cellules B1 à B10.
=QUARTILE (A1:A10, 1)	Calcule le premier quartile d'un ensemble de nombres.
=QUARTILE (A1:A10, 2)	Calcule le deuxième quartile d'un ensemble de nombres (notez que le deuxième quartile est identique à la médiane).
=QUARTILE (A1:A10, 3)	Calcule le troisième quartile d'un ensemble de nombres.

Tableau A2 Fonctions statistiques disponibles dans MS Excel

Analyse Excel des lois de probabilité

Comme exposé dans la section Mesures de position, les spécialistes de la science des données s'intéressent souvent à diverses distributions de probabilité telles que la distribution normale, la loi binomiale et la loi de Poisson. Excel met à disposition des fonctions intégrées permettant d'analyser de nombreuses distributions de probabilité.

Excel utilise la commande =NORM.DIST pour déterminer l'aire sous la courbe normale située à gauche d'une valeur spécifiée :

=NORM.DIST(XVALUE, MEAN, STANDARD_DEV, TRUE)

Par exemple, supposons que, dans une société de conseil financier, le salaire moyen des employés soit de 60 000 $ avec un écart type de 7 500 $. Une courbe normale peut être tracée pour représenter cette situation, dans laquelle la moyenne de 60 000 $ est positionnée sur l'axe horizontal, correspondant au pic de la courbe. Dès lors, pour déterminer la probabilité qu'un employé gagne plus de 75 000 $, vous calculeriez l'aire sous la courbe normale située à droite de la valeur de données 75 000 $.

Par exemple, dans la société de conseil financier mentionnée précédemment, le salaire moyen des employés est de 60 000 $ avec un écart type de 7 500 $. Pour déterminer, à l'aide d'Excel, la probabilité que le salaire d'un employé choisi au hasard soit inférieur à 55 000 $, voici la commande à employer :

=NORM.DIST(55000, 60000, 7500, TRUE)

Résultat : 0,25249

Ainsi, il existe une probabilité d'environ 25 % qu'un employé choisi au hasard perçoive un salaire inférieur à 55 000 $.

Excel fournit également des fonctions intégrées pour les lois binomiale et de Poisson, comme le montre le Tableau A3 :

Analyse Excel pour la corrélation et la régression

Dans la section Statistiques inférentielles et analyse de régression, les notions de corrélation et de régression ont été abordées.

En analyse de corrélation, le spécialiste de la science des données cherche fréquemment à calculer le coefficient de corrélation, qui est une mesure numérique du sens et de la force de la corrélation linéaire entre deux grandeurs numériques.

La commande Excel permettant de calculer le coefficient de corrélation adopte la structure suivante :

=CORREL(A1:A10, B1:B10)

En analyse de régression, le spécialiste cherche à calculer l'équation du modèle linéaire du meilleur ajustement reliant deux grandeurs numériques (en supposant que la corrélation soit significative).

Rappelons que l'équation de la droite du meilleur ajustement est

où b est la pente de la droite et a est l'ordonnée à l'origine de la droite.

Pour calculer la pente et l'ordonnée à l'origine du modèle linéaire au moyen d'Excel, commencez par entrer les données (x, y) dans deux colonnes distinctes. Les commandes Excel =SLOPE (PENTE) et =INTERCEPT (ORDONNEE.ORIGINE) peuvent alors être utilisées pour calculer respectivement la pente et l'ordonnée à l'origine.

Le jeu de données du Tableau A4 servira d'illustration : le montant mensuel alloué à la publicité ainsi que les revenus mensuels d'une entreprise du Fortune 500 sur une période de 12 mois.

Pour calculer la pente du modèle de régression, utilisez la commande Excel

=SLOPE(plage de données y, plage de données x)

Il convient de remarquer que cette commande Excel exige de renseigner d'abord la plage de données y, puis la plage de données x. Puisque les revenus dépendent des sommes investies dans la publicité, la variable des revenus correspond à la variable y, et le montant publicitaire constitue la variable x. Notons que les données y figurent dans les cellules C2 à C13 et que les données x se trouvent dans les cellules B2 à B13. Par conséquent, la commande Excel permettant de déterminer la pente s'écrira

=SLOPE(C2:C13, B2:B13)

De la même façon, la commande Excel servant à déterminer l'ordonnée à l'origine du modèle de régression est

=INTERCEPT(plage de données y, plage de données x)

Pour le jeu de données exposé dans le tableau précédent, la commande s'exprime ainsi :

=INTERCEPT(C2:C13, B2:B13)

Les résultats se présentent comme suit (voir la Figure A26) :

Figure A26 Revenus en fonction des dépenses publicitaires pour une entreprise du Fortune 500 (en milliers de dollars) montrant le calcul de la pente et de l'ordonnée à l'origine (y-intercept) dans MS Excel (Utilisé avec l'autorisation de Microsoft)

En conséquence, l'équation de régression peut être formulée ainsi :

où x représente les dépenses consacrées à la publicité (en milliers de dollars) et y représente le chiffre d'affaires ou revenu réalisé (en milliers de dollars).