MON 2 : Utilisation d'Excel dans l'analyse de données

Dans ce MON l'objectif est d'apprendre à utiliser Excel dans le but d'analyser des données.

Introduction

Au sein de mon entreprise d'alternance beaucoup de fichiers Excel sont utilisés dans tous les services afin de faire du suivi de tâches, de réaliser des processus de relevé de prix ou encore d'analyser des données... Mon rôle en tant qu'alternante en digitalisation est de minimiser au maximum l'utilisation d'Excel en utilisant des applications collaboratives, nottament Power BI issus de la suite de Microsoft pour l'analyse des données. Je trouve donc pertinent de comprendre comment Excel fonctionne dans l'analyse de données afin de mieux comprendre l'utilisation d'Excel par les utilisateurs pour lesquels il m'arrive de créer des Power BI.

Sommaire

1. Qu'est-ce qu'Excel ?
2. Création d'une base de données sur Excel
3. Nettoyage de données sur Excel
4. Analyse de données sur Excel
5. Visualisation de données
6. Mise en application par l'analyse complète d'un jeu de données

Ressources et Outils utilisés

• Découvrir Microsoft Excel
• Les bases de l'analyse de données sur Excel

1. Qu'est-ce qu'Excel ?

Microsoft Excel, un logiciel de tableur faisant partie de la suite bureautique Office 365, présente des tableaux organisés en lignes et colonnes dans des onglets distincts appelés feuilles de calcul. Cette application intègre des fonctionnalités de calcul numérique, de représentation graphique, d'analyse de données, ainsi qu'une capacité de programmation grâce à son propre langage.

En tant qu'élément considéré comme le plus complexe au sein de la suite Office, Microsoft Excel offre une gamme variée de fonctionnalités, notamment :

• Calculs à partir de formules mathématiques : Vous pouvez entrer des formules dans une cellule en utilisant le symbole « = » suivi de l'opération à effectuer.

• Calculs à partir de fonctions Excel : Vous avez également la possibilité d'utiliser des fonctions prédéfinies telles que « SOMME », « MOYENNE », ou encore « MAINTENANT » pour afficher la date et l'heure actuelles dans une cellule désignée.

• Formules matricielles : Certaines formules ont la particularité de générer des calculs pour une matrice entière.

• Graphiques : Excel permet de créer des graphiques à partir de données contenues dans un tableau. Ces graphiques se mettent automatiquement à jour si les données sont modifiées.

• Tableaux croisés dynamiques : Cet outil permet de regrouper des données selon un ou plusieurs critères et de les présenter sous forme de sommes, moyennes ou comptages.

• Macros : Les macros Excel sont des séquences d'actions qui automatisent des tâches. Elles sont enregistrées en utilisant le langage de programmation Visual Basic pour applications.

Une autre caractéristique importante de Microsoft Excel est la possibilité de protéger une feuille de calcul avec un mot de passe, limitant ainsi l'accès non autorisé et les modifications indésirables.

Les fichiers générés par Excel sont généralement enregistrés aux formats .xls ou .xlsx pour les feuilles de calcul, et .csv ou .txt pour les fichiers contenant du texte.

2. Création d'une base de données sur Excel

Le plus souvent la base de données est le fichier Excel que les équipes utilisent pour suivre leurs données de programme mais elle peut aussi être sur papier, sous un format texte ou un format plus complexe. Une base de donnée est un élément indispensable pour trouver facilement et rapidement les données que l'on souhaite.

Comment créer une BDD sur Excel ?

Pour créer une BDD sur Excel on a besoin de 3 étapes :

• Définir les lignes
• Définir les colonnes
• Designer les colonnes

Définir les lignes Chaque ligne correspond à un enregistrement. Le plus souvent cela correspond à un ensemble de réponses d'une collecte de données. Il est PRIMORDIALE que chaque enregistrement est un identifiant unique qui permette de remonter à la ligne en question. Le plus souvent on utilise une suite de nombre (1, 2, 3, 4,...).

Définir les colonnes Chaque colonne correspond à une information. On indique en haut de chaque colonne de cellule le nom de la variable informative par exemple auteur , date , durée , taille de la toile , type,...

Designer les colonnes La première colonne sera toujours l'ID de l'enregistrement. Les en-tête de colonnes sont uniques, courts et clairs. On peux rajouter si on le souhaite des en-tête supplémentaire pour définir des parties afin d'améliorer la lisibilité de la BDD.

Exemple : mini-BDD de peintures

Principes de bases

• La référence absolue Lors de l'utilisation d'Excel on fait souvent référence à des cellules. Si l'on désire que la référence ne soit pas modifier lorsqu'on recopie sa formule il faut penser à rajouter le symbole $.

• Nommer une plage de cellules Nommer une plage de cellules permet de gagner du temps sur l'entrée et la lisibilité des formules. Pour ce faire il suffit de sélectionner la plage des cellules à nommer et d'entrer le nom voulu dans la plage associée.

3. Nettoyage de données sur Excel

Maintenant que l'on sait créer une BDD sous Excel nous allons voir comment nettoyer des données c'est à dire à analyser la cohérence des données et à effectuer une triangulation avec d'autres informations. Le nettoyage de données permet d'avoir une base de données à jour sans risquer des erreurs d'analyse.

Il y a trois grand principes aux nettoyages des données :

• Vérification de la cohérence logique
• Vérification de la fiabilité
• Correction des erreurs Alors, le but est de déceler : les valeurs manquantes , celulles vides , fautes de frappes , doublons , valeurs extrêmes , textes en chiffres et inversement , ...

Erreurs courantes faciles à rectifier

• Nombres enregistrés sous forme de texte : Il faut sélectionner toute la plage de données concernée faire un clic droit et choisir "Convertir en nombre"
• Trouver un point au lieu d'une virgule pour les nombres : Il faut utiliser la fonction "Rechercher et remplacer" pour modifier les points par des virgules.
• Les espaces inutiles : On utilise la formule SUPPRESPACE() qui permet de supprimeer les espaces du texte à l'exception des espaces simples entre les mots.
• Doublons et valeurs extrêmes : Une valeur extrême est une valeur aberrante anormalement différente de la distribution d'une variable. Comme les doublons il est important de trouver ce type d'anomalie afin de ne pas induire d'erreur dans l'analyse. Pour cela on peut utiliser l'outil de Mise en forme conditionelle dans l'onglet Acceuil. On trouve alors facilement les valeur extrême en utilisant Supérieur à ... ou Inférieur à... et les doublons à l'aide de Valeurs en double....

Exemples : Nettoyage de la mini-BDD peintures avec des erreurs et des doublons intégrés exprès

Ces méthodes mettent en exerbe des comportements étrange sur les données néanmoins cela ne veut pas focément signifier qu'il y a une erreure. Il est tout à fait possible que deux peinture représente la même chose ou qu'une peinture soit bien plus longue à réaliser que la moyenne, il est important de garder un esprit critique lors du nettoyage des données.

Pour en savoir plus sur les outils disponibles pour le nettoyage des données : Nettoyer ses données.

4. Analyse de données sur Excel

Introduction

L'analyse des données permet de donner une interprétation aux données que l'on a relevé. Beaucoup de calculs sont possibles pour les variables de type nombre (effectif, minimum et maximum, moyenne et médiane, écartype et variance, somme,...) mais il faut choisir celles qui auront le plus de sens en fonction du cas que l'on étudie.

Statistique descriptives

On peut s'y pencher selon 3 axes :

• La distribution qui concerne la fréquence de chaque valeur.
• La tendance centrale qui concerne les valeurs moyennes.
• La variabilité qui concerne la distribution des valeurs.

Dénombrement, maximum et minimum

• Dénombrement L'objectif est de pouvoir compter l'effectif d'une donnée au sein d'une série statistique. La fonction NB.SI permet de compter le nombre de cellules qui répondent à un critère donné.

Exemple : Nombre de peintures qui ont nécessités plus de 2H de travail

Avec cette fonction les données manquantes sont automatiquement exclues de l'anlyse. Pour savoir combien de données manquent on peut utiliser la fonction NB qui permet de compter le nombre de cellules remplies. Il suffira donc de soustraire ce nombre au nombre total d'enregistrement dans la base de données.

• Maximum et minimum L'objectif est ici d'obtenir la plus grande ou la plus peite valeur d'une plage de données. Pour ce faire on utilise les fonction MIN et MAX.

• D'autres fonctions On utilise aussi la moyenne afn d'obtenir la tendance centrale, ou encore la médiane. La grosse différence entre les deux c'est que la moyenne va être bien plus sensibles aux valeurs abbérantes que la médiane.

• Mesures de variabilités Elles permettent d'avoir une idée de la distribution des enregistrement de la BDD. Parmi elles on retrouve l'étandue, l'écart-type, la variance.

5. Visualisation de données

Introduction

Une fois différentes analyses menées il est toujours plus parlant de pouvoir les visualiser pour les interpréter. En fournissant une représentation visuelle notre cerveaux appréhende plus naturellement des données. Afin d'utiliser une représentation adéquate il faut se poser deux questions:

• Qu'est-ce que j'essaie de dire?
• A qui j'essaie de le dire?

Comment représenter des données quantitatives?

Il existe une variété de représentations statistiques mais toutes ne sont pas adaptés à nos données. Voici les trois grandes catégories :

• Modèle de comparaison
• Modèle de composition
• Modèle de distribution

Dans chacune en fonction du type et du nombre de variables que l'on souhaite réalisée différents sous-modèle sont plus ou moins appropriés.

Exemples : Il y a-t-il une corrélation entre le temps passé sur une peinture et son appréciation ?

Pour déterminer si il y'a une corrélation entre les deux données on peut tracer le nuage de points d'une variable par rapport à l'autre. On se rend compte qu'il n'y a pas nécessairement de tendance mise en lumière mais comme il s'agit d'un tout petit échantillon de données c'est plutôt normal surtout que les données ont été inventé. Sur de vraie jeux de données il est très utile d'analyse ce genre de comportement.

6. Mise en application par l'analyse complète d'un jeu de données

Afin d'appliquer les différentes notions vu précédemment j'ai décidé de télécharger une base de données à partir du site M DATA. Je me suis penchée sur une BDD sur la fréquentation des musées : Fréquentation des musées que j'ai téléchargé en format Excel.

Etape 1 : Mise en forme des données

Lorsqu'on ouvre le fichier voici ce que l'on obtient :

Télécharger le fichier Excel

Etape 2 : Analyse des données

J'ai travaillé sur plusieurs thématiques pour répondre aux questions suivantes :

On va utiliser les formules vues plus tôt afin d'analyser nos données. La première chose que je trouve intéressante est de chercher l'enregistrement possédant la plus grande fréquentation toutes années comprises. Pour celà je savais déterminer le maximum mais comment récupérer l'ID du musée correspondant ainsi que son nom et l'année de l'enregistrement ? Je me suis donc renseigné pour trouver la formule =RECHERCHERV. Seulement cette formule ne fonctionne que si la valeur rechercher se trouve dans la première colonne ce qui n'est pas le cas ici. J'ai donc du utilisé deux formules pour séquencer mon travail :

• Trouver la ligne correspondante à la valeur cherchée J'utilise alors la formule =EQUIV
• Trouver les informations d'une ligne J'utilise alors la formule =INDEX

On obtient alors ce type de données pour les premières réponses :

Pour les quatres questions j'ai utilisé des feuilles de calculs à part car il s'agit de renvoyer plusieurs lignes et non pas une seule.

Q5 : Quel est le pourcentage de la fréquentation totale attribuable aux visiteurs payants pour chaque musée ?

Q6 : Quels musées ont eu une fréquentation gratuite supérieure à leur fréquentation payante en 2006 ?

On peut visualiser l'évolution de cette variable au fil des années :

Q7 : Quels musées ont enregistré une baisse de la fréquentation totale de l'année 2015 à 2016 ?

Je me suis demandé si on pouvait trouver les musées gratuits de la région en filtrant la colonne PAYANT sur la valeur 0. Et voici ce que j'ai obtenu :

Etape 3 : Visualisation des données

Maintenant j'ai envie de rendre mes données plus interprétables pour des personnes n'ayant pas travaillé sur la BDD.

Grâce à ces visualisations on peut remarquer qu'il y a une une augmentation significative de la fréquentation des musées entre l'année 2012 et 2013 : elle a quasiment triplé ! J'ai voulu me renseigner pour savoir à quoi cela était dû et j'ai trouveé cet article du Figaro Musées : un millésime 2013 riche en fréquentation. L'article parle d'une hausse de fréquentation dans toute la France et pas que dans les Bouches-Du-Rhônes. Un événément qui a sûrement permis de faire grimper les fréquentations cette année là c'est l'inauguration du MuCem à Marseille le 4 juin 2013. Comme le montre ce graphique, le MuCem était le musée le plus fréquenté cette année là et de loin:

On peut aussi constater l'impact de la crise du COVID qui a engendré une brutale chute des fréquentations des musées à partir de 2020.

J'ai aussi voulu comprendre le pic de 2006. Et voilà le graphique de cette année là :

7. Pour aller plus loin

Après avoir réaliser l'application sur la fréquentation des musées j'ai voulu voir ce qui était possible de réaliser en allant plus loin dans l'utilisation d'Excel. Pour ce faire je me suis appuyée sur la ressource de Zeste de savoir et plus particulièrement sur la partie Analyse des données et dynamisme du classeur. Je suis repassée très rapidement sur tout ce que j'avais déjà vu pour m'attarder sur une nouveauté : les tableaux dynamiques croisés.

Horodateur