Utiliser Excel (Microsoft Office) pour étudier des données

L’exercice corrigé de cette fiche permet d’appliquer les notions du cours Analyse de l’information chiffrée :

• dresser un tableau croisé de deux caractères à partir d’un fichier de données ;
• utiliser un tableur pour représenter des données sous forme de diagramme ;
• déterminer dans un fichier de données un sous-ensemble d’individus répondant à un sous-caractère.

Énoncé

Afin de lutter contre le réchauffement climatique et la pollution, deux des objectifs sont la valorisation des déchets ménagers et l’utilisation d’un moyen de transport le moins polluant possible.

On s’intéresse aux $25$ départements de deux grandes régions françaises – Nouvelle-Aquitaine et Occitanie. Dans le fichier Excel à télécharger :

• la feuille intitulée « Voiture et valorisation » donne :
• d’une part, le taux d’utilisation, en 2014, de la voiture parmi l’ensemble des transports utilisés pour se rendre au travail,
• d’autre part, le taux de valorisation des déchets ménagers pour l’année 2013 ;
• la feuille intitulée « Comparaison 2009-2013 » donne les taux de valorisation des déchets ménagers en 2009 et 2013.

[Source : « Développement durable : 20 indicateurs régionaux et départementaux », 2018, INSEE]

Remarque :
Sur la première feuille, les données disponibles concernent les années 2013, pour le taux de valorisation des déchets, et 2014, pour l’utilisation de la voiture pour se rendre au travail. On considère que les années sont assez proches pour traiter les données ensemble.

Question 1

Télécharger le fichier Excel et se positionner sur la première feuille : « Voiture et valorisation ».

a. Trier les données par taux croissant de valorisation des déchets ménagers.
Combien de départements avaient un taux de valorisation inférieur ou égal à $35\,\%$ ? Parmi ceux-ci, combien avaient un taux d’utilisation de la voiture pour se rendre au travail inférieur ou égal à $80\,\%$ ?

b. Trier cette fois les données par taux croissant d’utilisation de la voiture pour se rendre au travail.
Combien de départements avaient un taux inférieur ou égal à $80\,\%$ ?

c. En se servant des résultats précédents, compléter le tableau croisé d’effectifs des départements suivant :

Question 2

À partir du tableau croisé d’effectifs obtenu à la question 1, répondre aux questions suivantes.

a. Quel est le pourcentage des départements étudiés dont le taux de valorisation des déchets ménagers dépassait $35\,\%$ en 2013 ?

b. En 2009, le gouvernement français prévoyait un objectif (strictement) supérieur à $35\,\%$ de valorisation pour l’ensemble des départements en 2012.
Le résultat de la question précédente montre-t-il que cet objectif a été atteint ? Justifier.

c. Quelle est la proportion des départements étudiés dont le taux de valorisation des déchets dépassait $35\,\%$ et dont le taux d’utilisation de la voiture était inférieur ou égal à $80\,\%$ ?

Question 3

Sur la seconde feuille de calcul, intitulée « Comparaison 2009-2013 », on donne le taux de valorisation des déchets ménagers en 2009 et en 2013, pour les départements étudiés précédemment.

a. Utiliser la feuille de calcul pour construire un diagramme en bâtons permettant de comparer les deux taux pour chaque département.

b. Utiliser ce graphique pour déterminer la part des départements pour lesquels le taux de valorisation a diminué entre 2009 et 2013.

Question 4

Un des objectifs nationaux pour 2020 était de parvenir à $55\,\%$ au moins de valorisation des déchets ménagers.

a. Toujours sur la feuille de calcul « Comparaison 2009-2013 », filtrer les données de 2013 pour afficher les départements qui atteignaient déjà cet objectif en 2013. Combien y en a-t-il ?

b. Afficher la liste des départements dont le taux, en 2013, se situait strictement entre $35\,\%$, objectif de 2012, et $55\,\%$, objectif de 2020.

Corrigé

Remarque :
Dans un tableur, il existe souvent plusieurs méthodes pour effectuer l’action désirée. On donnera dans ce corrigé celle qui nous semble la plus générale. On donnera parfois quelques astuces pour aller plus vite.
On précise aussi qu’on travaille ici avec la version 16.71 d’Excel pour Mac.

Question 1

a. Pour trier les données par taux croissant de valorisation des déchets ménagers :

• on sélectionne une cellule du tableau ;
• dans le menu Accueil, on clique sur Trier et filtrer, puis Tri personnalisé… ;
• dans la fenêtre qui s’ouvre :
• on laisse coché Ma liste contient des en-têtes, car on a bien des titres pour nos colonnes, ce sont alors ces titres qui apparaissent pour les critères du tri plus bas (plutôt que la numérotation des colonnes) ;
• on indique la colonne sur laquelle on veut trier, ici le Taux de valorisation…,
• puis on précise que l’on veut trier les Valeurs des cellules,
• enfin, on souhaite trier par ordre croissant, soit Du plus petit au plus grand :

• on peut maintenant valider.

Astuce

• Dans un tableau simple comme ici, on peut aussi sélectionner une cellule de la colonne sur laquelle on veut trier, soit une cellule contenant un taux de valorisation.
  Puis, après avoir cliqué sur Trier et filtrer, on choisit directement Trier du plus petit au plus grand.

• On peut aussi, dans le menu Données, utiliser l’un de ces trois boutons de raccourci :

Raccourcis pour trier des données

• Celui en haut à gauche trie par ordre croissant (« de A à Z » : ordre alphabétique).
• Celui en bas à gauche trie par ordre décroissant (« de Z à A » : ordre alphabétique inverse).
• Celui de droite ouvre la même fenêtre que précédemment.

Dans le tableau trié, on peut lire facilement les départements dont le taux de valorisation était inférieur ou égal à $35\,\%$ :

• Il y a ainsi $3$ départements qui avaient un taux de valorisation des déchets ménagers inférieur ou égal à $35\,\%$.
• On voit aussi rapidement que, parmi eux, $1$ seul avait un taux d’utilisation de la voiture inférieur ou égal à $80\,\%$ : la Lozère.

b. En procédant de la même façon que pour la question précédente, on obtient le tableau suivant, trié cette fois par taux croissant d’utilisation de la voiture :

• Il y a $6$ départements avec un taux d’utilisation de la voiture inférieur ou égal à $80\,\%$.

c. D’après les questions a et b :

• $\textcolor{#00CC00} 3$ départements avaient un taux de valorisation des déchets ménagers inférieur ou égal à $35\,\%$ ;
• $\textcolor{#FF8000}{6}$ départements avaient un taux d’utilisation de la voiture inférieur ou égal à $80\,\%$ ;
• $\textcolor{#CC00CC} 1$ département respectait les deux conditions.

En outre, on sait qu’il y a $\red{25}$ départements au total.
On commence donc par remplir le tableau avec ces premiers résultats :

On peut déduire les valeurs des autres cellules par soustractions successives. Donnons deux exemples de ces soustractions.

• Il y a $\red {25}$ départements au total, dont $\textcolor{#FF8000}{6}$ qui ont un taux d’utilisation de la voiture inférieur ou égal à $80\,\%$.
• Le nombre de départements avec un taux d’utilisation de la voiture strictement supérieur à $80\,\%$ est donc :
  $$\red{25}-\textcolor{#FF8000}{6}=19$$
• $\textcolor{#00CC00} 3$ départements ont un taux de valorisation inférieur ou égal à $35\,\%$, dont $\textcolor{#CC00CC} 1$ a un taux d’utilisation de la voiture inférieur ou égal à $80\,\%$.
• On en déduit le nombre de départements avec un taux de valorisation inférieur ou égal à $35\,\%$ et un taux d’utilisation de la voiture strictement supérieur à $80\,\%$ :
  $$\textcolor{#00CC00} 3-\textcolor{#CC00CC} 1=2$$
• On finit de remplir en procédant de même.
• On obtient le tableau suivant :

Question 2

a. Le tableau croisé des effectifs permet de voir que, en 2013, $\textcolor{#FF00FF}{22}$ des $\red{25}$ départements étudiés avaient un taux de valorisation des déchets ménagers supérieur à $35\,\%$ :

Le pourcentage correspondant s’obtient en faisant le calcul :
$$\dfrac {\textcolor{#FF00FF}{22}}{\red{25}}=0,88$$

• Ainsi, $88\,\%$ des départements étudiés valorisaient plus de $35\,\%$ de leurs déchets ménagers, en 2013.

b. D’après la question précédente, en 2013, $88\,\%$ des $25$ départements atteignaient un taux (strictement) supérieur à $35\,\%$ de valorisation des déchets ménagers.

• L’objectif n’était donc pas atteint pour les deux régions ; par conséquent, ce n’était pas non plus le cas sur l’ensemble du territoire français.

c. D’après le tableau croisé d’effectifs, $\textcolor{#FF00FF}5$ des départements étudiés valorisaient plus de $35\,\%$ des déchets et avaient un taux d’utilisation de la voiture inférieur ou égal à $80\,\%$.

La proportion correspondante est donc :
$$\dfrac {\textcolor{#FF00FF}5}{\red{25}}=\dfrac 15=0,2$$

• Ainsi, la proportion des départements avec un taux de valorisation supérieur à $35\,\%$ et un taux d’utilisation de la voiture inférieur à $80\,\%$ est de $\frac 15$, soit $20\,\%$.

Question 3

a. On veut comparer, dans la feuille « Comparaison 2009-2013 », les deux taux pour chaque département. On choisit alors de les représenter à l’aide d’un diagramme en bâtons dits multiples : pour chaque département, une barre représentera le taux de 2009 et l’autre, à côté, celui de 2013.
Pour cela :

• on sélectionne les données de la feuille de calcul (sans les titres) ;
• dans le menu Insertion, on clique sur Histogramme, puis on choisit Histogramme groupé, dans le sous-menu Histogramme 2D :

On obtient par défaut un diagramme du type :

On peut travailler sur ce graphique, pour en améliorer la présentation.

• Les échelles sont choisies automatiquement par Excel, en fonction de la grandeur des valeurs et de la taille du diagramme. Elles conviennent souvent et, lorsque c’est le cas, on les conserve.
• On peut modifier le titre, ou le supprimer, en cliquant sur le bloc du titre.
• On tape par exemple : « Valorisation des déchets ménagers en Occitanie et en Nouvelle-Aquitaine », et on met en forme avec les outils habituels.
• On peut ajouter la légende de l’axe des ordonnées, en allant dans le menu Création de graphique (qui apparaît lorsqu’on sélectionne le diagramme), puis en cliquant sur Ajouter un élément graphique > Titres des axes > Vertical principal :

• On tape par exemple : « Taux de valorisation (%) ».
• On voit que la légende des couleurs n’indique que « Série 1 » et « Série 2 ». La série 1 correspond aux données de 2009 et la série 2 celles de 2013. On indique donc ces informations, en allant toujours dans le menu Création de graphique, puis en cliquant sur Sélectionner des données.
  Dans la fenêtre qui s’ouvre, partie Entrées de légende (série) , pour chaque série, on indique dans le champ Nom la cellule qui contient la légende voulue.
• Cellule B2 pour la série 1, et cellule C2 pour la série 2 (on peut aussi taper directement le texte voulu).

• On peut enfin, pour rendre le diagramme plus lisible, modifier le style de la police des noms de département ou des pourcentages, etc., en cliquant sur le bloc correspondant.
• On obtient au final le diagramme :

Remarque :
Excel nomme les diagrammes en bâtons « histogrammes ». Il est important de se rappeler que, en mathématiques, un histogramme a une définition autre : il sert à représenter les effectifs par classe.

b. Ce graphique permet, pour chaque département, de comparer graphiquement leurs taux de 2009 et 2013.
On voit ainsi que, pour quatre départements, le bâton de 2009 est plus haut que celui de 2013 : Ariège, Charente-Maritime, Dordogne et Haute-Vienne.
On a alors $\frac 4{25}=0,16$.

• Ainsi, $16\,\%$ des départements d’Occitanie et de Nouvelle-Aquitaine ont vu leur taux de valorisation diminuer entre 2009 et 2013.

Question 4

a. Tout d’abord, les départements qui, en 2013, atteignaient déjà l’objectif de 2020 sont ceux avec un taux de valorisation supérieur ou égal à $55\,\%$.

Ensuite, pour filtrer les données de 2013 :

• on sélectionne les données de 2013, avec la cellule de titre ;
• dans le menu Accueil, on clique sur Trier et filtrer, puis Filtrer ;
• une petite flèche, symbolisant un menu déroulant, est apparue sur la cellule de titre :

Astuce

Pour ajouter un filtre, on peut aussi se placer dans le menu Données et cliquer sur le bouton de raccourci suivant :

Bouton de raccourci pour filtrer

• on peut maintenant filtrer les données selon certaines conditions ; on clique alors sur la petite flèche de la cellule de 2013 ;
• dans la fenêtre qui s’ouvre, on choisit Supérieur ou égal à et on entre $55$ :

• on voit que seuls quatre départements sont désormais affichés : Charente, Hérault, Landes et Deux-Sèvres.
• Il y a ainsi quatre départements qui, en 2013, atteignaient déjà l’objectif de 2020.

b. On cherche maintenant les départements dont le taux de 2013 était strictement compris entre $35\,\%$ et $55\,\%$, autrement dit, les départements qui avaient atteint l’objectif de 2012, mais qui n’avaient pas encore atteint celui de 2020.

On se sert là aussi d’un filtre :

• on met comme première condition : « Supérieur à $35$ » ;
• apparaît alors (sur notre version d’Excel) de nouveaux champs qui permettent d’ajouter une seconde condition, et on met : « Inférieur à $55$ ».

On veut que le taux soit à la fois supérieur à $35$ et inférieur à $55$. On veille donc à ce que ce soit la case Et qui soit cochée :

Remarque :
Dans des versions précédentes d’Excel, pour pouvoir ajouter un deuxième critère, il faudra choisir Filtre personnalisé. Une nouvelle fenêtre s’ouvre, et la procédure y est alors la même.

Astuce

On peut aussi filtrer sur plusieurs séries :

• on sélectionne la ligne avec les cellules de titres (ou on se place sur l’une d’entre elles) ;
• on filtre comme expliqué plus haut.
• Cette fois, chaque cellule de titre possède une petite flèche.

On peut alors, par exemple :

• mettre un filtre sur les données de 2009 pour n’afficher que les départements avec un taux supérieur ou égal à $50\,%$ ;
• mettre un filtre sur les données de 2013 pour n’afficher que les départements avec un taux supérieur ou égal à $55\,%$.
• On obtiendra ainsi la liste des départements qui avaient atteint en 2013 l’objectif de 2020 et qui valorisaient déjà plus de la moitié de leurs déchets ménagers en 2009.

Filtres sur deux séries