Comment analyser des données avec Excel (Tri, Filtre, Filtre élaboré, tableaux croisés dynamiques)
Souvent, lorsque l’on se retrouve à devoir exploiter une base contenant un (très) grand nombre de données (comme des documents comptables, des registres de ventes etc…), il arrive que l’on ne sache par quel bout commencer. Dans cet article nous allons voir dans le détail comment traiter des grandes bases de données afin d'en tirer le meilleur, et de récupérer les informations les plus pertinentes.
Nous serons ainsi amenés à utiliser les outils d’analyse d’Excel tels que :
- Le tri de base de données, qui permet d’obtenir les informations les plus pertinentes en début de base,
- Le filtre de base de données, qui permet de n’afficher que les données qui nous nous intéresse, en masquant les données inutiles,
- Le filtre élaboré, il s’agit d’un outil puissant d’Excel, qui permet de passer outre les limitations du filtre simple, mais qui nécessite de bien comprendre son fonctionnement, tant son usage est complexe,
- Les tableaux croisés dynamiques vont ensuite nous permettre de mettre en évidence les données qui nous intéressent le plus, tout en effectuant un certain nombre de calculs de manière automatisée,
- Dans la dernière partie de cet article, nous verrons comment créer simplement la pyramide des âges d'une entreprise
Avant toute chose, je vous invite à télécharger le fichier Excel contenant notre base de données afin de pouvoir suivre chacune des étapes de cet article en même temps que moi.
Télécharger le fichier d'exemple
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
1. Comment trier des données
Pour suivre cet article dans les meilleures conditions, je vous invite à télécharger le fichier dans le lien disponible en début d’article (cliquez ici pour y accéder).
La base de données que vous venez de télécharger est extraite d’un logiciel de gestion de paie d’une grande entreprise nationale et reprend les données propres à chaque personne de cette société.
Exploiter les données contenues dans ces presque 1500 lignes peut sembler impossible. Mais ne vous inquiété pas, nous allons y aller progressivement.
La première chose à faire va être d’ordonner les informations afin de pouvoir visionner dans le haut du tableau les informations les plus pertinentes.
L'outil de tri d’Excel se trouve dans l'onglet « Données » du Ruban.
Nous y retrouvons trois boutons :
- Le bouton de tri automatique, suivant un ordre croissant,
- Le bouton de tri automatique, suivant un ordre décroissant,
- Et le bouton qui permet de lancer l’outil de tri manuel
Il est également possible d’accéder aux options de tri à partir du menu contextuel qui s’affiche lorsque l’on effectue un clic droit sur une cellule contenue dans une base de données. Il suffit ensuite de choisir le sous-menu « Trier ».
1.1. Le tri alphabétique
Supposons que nous souhaitions trier les effectifs présents dans notre base de données, en fonction de leur sexe :
- Commençons par sélectionner une des cellules contenues dans cette colonne, afin d’indiquer à Excel sur quelle colonne effectuer le tri,
- Puis, il nous suffit de cliquer sur le bouton A→Z
De cette manière, l’ordre de chacune des lignes de la base de données va être revu, et les femmes vont être placées en premier, puis les hommes à la suite.
Pour obtenir un tri inverse, il suffit de cliquer sur l’autre bouton du ruban (Z→A).
1.2. Le tri numérique
Nous pouvons également souhaiter que notre base de données soit classée en suivant les âges de chaque personne, en affichant d’abord les personnes les plus jeunes.
Pour cela nous allons devoir ordonner nos données, non pas manière alphabétique comme nous venons juste de le faire, mais de manière numérique : les plus jeunes au début et les plus âgés à la fin. Nous allons alors procéder de la même manière :
- Sélectionnons une des cellules de la colonne âge,
- Puis, cliquons sur le bouton A→Z
1.3. Le tri chronologique
Pour effectuer un tri chronologique, nous allons toujours procéder de la même manière, mais en sélectionnant cette fois ci une cellule qui se trouve dans une colonne constituée de dates.
Le filtre chronologique (filtre A→Z) exposera alors dans un premier temps les dates les plus anciens, puis à la suite les dates les plus récentes. Tandis qu’à l’inverse le filtre antichronologique nous présentera en début de liste les dates les plus élevées.
1.4. Le tri multicritère
Il est également possible de trier les données en fonction de plusieurs critères. Pour cela il faut se rendre dans l'outil « Trier » de l’onglet « Données » (après avoir sélectionné une des cellules de la base de données à filtrer).
Une nouvelle fenêtre s’affiche alors à l’écran, et l’on peut constater en fond qu’Excel a déjà automatiquement mis en sélection notre base de données.
Dans cette fenêtre, nous allons :
- Choisir la colonne sur laquelle nous voulons effectuer le tri,
- Sur quel type de critère nous voulons effectuer le tri : est-ce que nous souhaitons trier la base en fonction des valeurs des cellules, …
- Et enfin l'ordre dans lequel nous souhaitons trier nos données (chronologique, ou antichronologique)
Supposons que nous voulions trier chaque personne du personnel pour que ces derniers soient réunis dans un premier temps en fonction de leur région puis ensuite en fonction de leur nom :
- Commençons par sélectionner une des cellules de la base de données,
- Puis nous allons cliquer sur le bouton « Trier » du ruban,
- Dans la fenêtre qui s’affiche à l’écran, choisissons sur la première ligne les éléments suivants :
- Colonne - Trier par : Région
- Trier sur : Valeurs de cellule
- Ordre : De A à Z
- Pour ajouter un second critère de tri, nous allons à présent cliquer sur le bouton « Ajouter un niveau »,
- Une seconde ligne s’ajoute alors à la suite, que nous allons remplir de la manière suivante pour filtrer ensuite en fonction du nom :
- Colonne - Puis par : Nom
- Trier sur : Valeurs de cellule
- Ordre : De A à Z
- Puis nous validons en appuyant sur le bouton OK
Dès lors, notre base est réordonnée en fonction des critères que nous lui avons définis juste avant : chaque personne est classée en fonction de sa région, puis de son nom.
Nous pourrions à présent simplement inverser l’ordre de tri (le nom en premier, puis ensuite la région). Nous cela, il nous suffit de réafficher la fenêtre de tri, puis de réorganiser l’ordre des critères à l’aide des flèches haut et bas.
1.5. Le tri personnalisé
Supposons maintenant que nous voulions sélectionner certaines personnes de notre base pour les mettre en haut, sans que le choix de ces personnes ne réponde à un critère précis.
Il suffit alors de cliquer sur ces dernières et d'appliquer une couleur quelconque au fond de la cellule.
Puis, nous allons effectuer un clic droit sur l'une des cellules et choisir « Placer la couleur de la cellule sélectionnée sur le de dessus » du menu « Trier ».
Il est également possible d’utiliser la couleur de fond des cellules depuis l’outil « Trier » du ruban.
Dans l’exemple ci-dessus, nous souhaitons que les personnes soient triées :
- Dans un premier temps en fonction de la couleur de fond de la cellule, ce qui va nous permettre d’affiche en début de liste des personnes que nous allons sélectionner manuellement,
- Puis dans un second temps en appliquant un tri alphabétique sur leur prénom
2. Comment filtrer les données
Dans cette seconde partie sur l'analyse des données nous allons voir comment filtrer des données. En d’autres termes, nous allons voir comment il est possible de sélectionner à l’écran uniquement les informations que nous jugeons les plus pertinentes (les autres informations seront ainsi masquées).
Pour suivre cet article dans les meilleures conditions, je vous invite à télécharger le fichier dans le lien disponible en début d’article (cliquez ici pour y accéder).
2.1. Sélectionner les données à filtrer
Tout comme pour accéder aux options de tri, le filtrage se trouve dans l'onglet « Données ».
Pour activer le filtre sur un tableau, il suffit de procéder de la manière suivante :
- Sélectionner une des cellules du tableau,
- Puis cliquer sur le bouton « Filtrer »
Automatiquement, un menu apparaît en haut de chaque colonne. Celui-ci permet de définir les différents filtres à mettre en place.
Par exemple ici nous souhaitons n’afficher que les lignes concernant les femmes :
- Nous commençons par cliquer sur le bouton en forme de triangle et situé sur l’entête de la colonne,
- Dans le menu qui s’affiche alors, nous pouvons cocher et décocher directement les éléments que nous souhaitons afficher ou masquer. Pour ne sélectionner qu’un seul élément, il faut au préalable décocher « (Sélectionner tout) ».
Pour effacer rapidement les filtres en place, il suffit de cliquer sur le bouton « Effacer » de l’onglet « Données » du ruban.
Il existe également un raccourci pour filtrer directement une colonne :
- Dans la colonne que nous souhaitons filtrer, nous allons cliquer sur une cellule qui contient la valeur que nous souhaitons afficher,
- Clic droit sur cette cellule,
- Dans le menu contextuel, option « Filtrer », nous choisissons « Filtrer par la valeur de la cellule sélectionnée »
2.2. Filtrer en fonction de plusieurs critères
Il est également possible de multiplier les critères de filtre.
Par exemple imaginons que nous voulions uniquement afficher à l’écran les informations concernant les hommes qui ont exactement 24 ans. Pour ce faire, nous allons dans un premier temps filtrer la colonne « Sexe » pour n’afficher que les hommes, puis dans un second temps, nous allons pouvoir filtrer la colonne « Âge » pour ne choisir que les personnes de 24 ans :
Vous noterez au passage qu’Excel filtre automatiquement les informations présentent dans les menus en fonction des filtres déjà mis en place.
2.3. Le filtre numérique
Lorsque les données sont de type numérique il est également possible d'appliquer une règle numérique. Nous allons ainsi pouvoir sélectionner les lignes concernant les hommes de plus de 30 ans :
2.4. Le filtre par couleur de fond
Il est possible de filtrer les cellules en fonction du fond de celles-ci, ce qui va nous permettre de sélectionner manuellement les lignes que nous allons souhaiter filtrer. Il nous suffit alors de cliquer sur les cellules à conserver à l’écran, puis dans le menu de filtre de la colonne, rendez-vous dans le sous-menu « Filtrer par colonne », et choisissez la couleur que vous venez d’appliquer.
Au besoin, nous pouvons filtrer les données qui ne possèdent pas de couleur de fond.
3. Comment utiliser le filtre élaboré
Dans la précédente partie nous avons vu qu'il était très simple de filtrer des données, en cliquant simplement sur le bouton « Filtrer », puis dans les menus qui apparaissent en en-tête de colonne de sélectionner les éléments que l’on désire afficher.
Nous allons maintenant voir comment utiliser le filtre élaboré (ou filtre avancé) afin d'aller beaucoup plus loin, grâce à l'utilisation de filtres plus complexes.
Pour suivre cet article dans les meilleures conditions, je vous invite à télécharger le fichier dans le lien disponible en début d’article (cliquez ici pour y accéder).
3.1. Le filtre élaboré (ou filtre avancé)
L’utilisation du filtre élaboré passe par l’utilisation d’une seconde plage de cellules au sein de la laquelle nous allons venir saisir les critères à utiliser pour réaliser le filtre.
Ainsi, nous allons insérer des lignes tout en haut de notre feuille de calcul (au-dessus de la base de données), afin d’y insérer un nouveau tableau dans lequel nous allons reprendre les intitulés de colonne de notre base de données sur lesquels nous allons vouloir effectuer un filtre avancé :
- Pour commencer, nous sélectionnons la première ligne de la feuille de calcul (en cliquant sur le chiffre « 1 » à gauche des cellules),
- Puis nous insérons des lignes au-dessus en appuyant simultanément sur les touches [Ctrl]+[+] du clavier,
- Notre base de données se décale alors vers le bas, et une nouvelle ligne s’insère en haut de la feuille,
- Répétons l’opération deux fois, afin que trois nouvelles lignes au totale soient insérées,
- Nous allons maintenant sélectionner les en-têtes de la base de données à l’aide de la souris,
- Puis les copier en appuyant simultanément sur les touches [Ctrl]+[c],
- Puis nous allons sélectionner la première cellule de la feuille de calcul, sur laquelle nous allons vouloir insérer les en-têtes,
- Enfin, nous allons les coller sur la première ligne en appuyant simultanément sur les touches [Ctrl]+[v]
Attention, ici nous avons effectué un copier-coller des en-têtes de colonnes, ce qui nous permet de récupérer exactement les bons intitulés. En effet, un intitulé qui serait différent dans le tableau de filtre et dans le tableau de données aboutirait à un échec de notre filtre (sauf pour les critères calculés, mais nous verrons cela plus loin dans cet article).
Notre filtre avancé est maintenant préparé, il nous suffira de saisir dans ce nouveau tableau les critères qui vont nous permettre de filtrer les données.
Supposons par exemple que nous voulions n’afficher que les hommes dans notre base de données :
- Dans la colonne « Sexe », nous saisissons le mot-clé : « homme »,
- Puis nous cliquons sur le bouton « Avancé » du groupe de filtre du ruban,
- Une nouvelle fenêtre s’affiche alors :
- Dans la zone « Données », nous allons sélectionner la plage de cellules qui correspondent à la base de données que nous souhaitons filtrer,
- Dans la zone de « Critère », nous sélectionnons les cellules qui correspondent à la plage que nous venons d'insérer tout en haut, et sur laquelle nous retrouvons à la fois les en-têtes (titres de colonne) et les critères,
- Enfin nous pouvons valider en cliquant sur le bouton [OK]
Nous constatons alors que les hommes sont affichés mais que les femmes sont masquées.
3.2. Faciliter la saisie de critères grâce à la validation des données
Pour simplifier la création des requêtes nous pouvons également utiliser la validation de données, afin que les données soient présentées au sein d’un menu déroulant. Pour cela, nous sélectionnons toutes les en-têtes de colonnes sur lequel nous allons venir saisir nos critères, puis dans l'onglet « Données » du ruban, nous cliquons sur « Validation de données », et « Validation de données » à nouveau.
Nous choisissons d’autoriser uniquement une liste, et dans la zone « Source », nous allons sélectionner l'ensemble des lignes de la base de données de la première colonne. En validant avec la touche [Entrée] du clavier, Excel insère les coordonnées de la plage de cellules sélectionnées.
Attention, les coordonnées insérées sont exprimées avec des références dites « absolues », ce qui signifie que les validations de données de l’ensemble des cellules feront référence à la plage exacte de la zone « Source ».
Il faut donc supprimer les signes « $ » insérés devant les lettres.
Ainsi, la validation de données de la cellule « B2 » fera référence à la plage « B$5:B$100 », celle de la cellule « C2 » fera référence à la plage « C$5:C$100 », et ainsi de suite…
Avant de valider la validation de données, nous allons nous rendre dans l’onglet « Alerte d'erreur » afin de décocher « Quand des données non valides sont tapées », ce qui nous permettra par la suite de pouvoir saisir des critères de filtre qui ne sont pas forcément déjà présent dans la base de données.
Nous pouvons maintenant valider la validation de données en appuyant sur le bouton [OK].
Maintenant, lorsque nous cliquons sur l’une des cellules devant recevoir les critères de filtre (par exemple la cellule « A2 »), un nouveau menu apparaît. Celui-ci nous permet alors de choisir directement les critères de filtre exactes contenus dans la base de données (prénom, noms, …).
3.3. Utiliser plusieurs critères de filtre
Il est tout à fait possible de saisir plusieurs critères cumulatifs. Par exemple, supposons maintenant que nous voulions afficher uniquement les lignes qui concerne les femmes de la région Languedoc-Roussillon :
- Dans la colonne « Sexe », nous saisissons le mot-clé : « Femme »,
- Dans la colonne « Région », nous saisissons le mot-clé : « Languedoc-Roussillon » (en nous aidant de la validation de données que nous venons de mettre en place, afin de récupérer la chaîne de caractère exacte),
- Puis nous utilisons le filtre avancé, exactement de la même manière que nous venons de le voir juste avant :
- Cliquer sur le bouton « Avancé »,
- Sélection des plages de données et de critères,
- Validation en cliquant sur le bouton [OK],
Nous retrouvons alors bien les lignes qui ne concerne que les trois femmes de la région Languedoc-Roussillon.
3.4. Les caractères jokers
Lorsque que nous ne connaissons pas exactement le contenu d’une chaîne, ou que nous souhaitons réaliser un filtre sur une partie de champ uniquement, nous pourrons utiliser les caractères « joker », c’est-à-dire :
- Le caractère « ? » permet de remplacer un seul caractère d’une chaîne de caractères, par exemple « AB?D » pourra être égal à « ABAD », « ABBD » , « ABCD », « AB8D », …
- Le caractère « * » permet de remplacer plusieurs caractères d’une chaîne de caractères, par exemple « AB*D » pourra être égal à « ABAD », « ABBBBD » , « AB3158D », « AB une infinité d’autres caractères D »,…
Par exemple, supposons que nous voulions toutes les personnes qui possèdent une voiture de la marque Toyota. Si nous regardons attentivement les données de la colonne « Voiture », nous constatons que celle-ci est constituée de trois éléments :
- En premier, nous retrouvons le millésime de la voiture,
- Puis en second la marque de la voiture (et c’est cette information qui va nous intéresser),
- Et enfin, en troisième position, le modèle de la voiture
Ainsi pour filtrer les voitures de la marque Toyota, nous allons ainsi venir saisir dans la colonne marque le critère suivant : « * Toyota * ». Le premier joker remplacera les années, tandis que le second remplacera n’importe quel modèle de la marque.
3.5. Les filtres numériques
Nous savons maintenant comment afficher à l’écran les personnes qui possèdent une voiture de la marque Toyota. Supposons qu’en plus de cela, nous voulions afficher parmi ces personnes celles qui ont un salaire annuel d’au moins 50000€.
Pour cela, nous allons devoir ajouter un second critère de filtre, sur la colonne « Salaire ». Il s’agira d’un critère numérique : « >50000 ».
Après avoir appliqué le filtre, nous nous retrouvons bien avec les lignes concernant les personnes qui roulent en Toyota et dont le salaire est supérieur à 50000 euros.
3.6. Le filtre d’exclusion
Pour continuer avec notre exemple, nous allons maintenant vouloir afficher les personnes qui roulent en Toyota et qui gagnent plus de 50000 euros par an (cela, nous savons déjà le faire), mais qui en plus n’habitent PAS à Lyon.
Pour cela, nous allons devoir utiliser l’opérateur « différent de… », représenté par les deux symboles « <> » : « <>LYON »
3.7. Les filtres alternatifs
Jusqu'à maintenant nous avons été amenés à utiliser des filtres en suivant des critères cumulatifs. C’est-à-dire que les lignes à afficher devaient remplir un certain nombre de critères en même temps (marque de la voiture, rémunération minimum, ville d’habitation, …).
Mais toute la puissance du filtre avancé d’Excel réside dans le fait qu’il est également possible d’utiliser des critères alternatifs. En d’autres termes, nous allons être en mesure de demander à Excel d’afficher les lignes qui répondent à au moins un critère parmi plusieurs.
Pour illustrer cela, imaginons que nous voulions uniquement « les hommes de plus de 50 ans » OU « les femmes de moins de 30 ans ».
Chaque critère doit alors figurer sur une ligne différente. Donc pour cela nous allons insérer une nouvelle ligne dans notre plage de critères située tout en haut de la feuille de calcul :
- Pour commencer nous allons sélectionner toutes les cellules de la ligne « 2 », en réalisant un clic-gauche sur le chiffre correspondant tout à gauche de la fenêtre,
- Dans le menu contextuel, nous allons cliquer sur « Insérer »
Ainsi, nous allons pouvoir saisir nos critères de filtres alternatifs sur deux lignes :
- Sur la première ligne :
- Colonne Sexe : « Homme »,
- Colonne Âge : « >50 »
- Sur la seconde ligne :
- Colonne Sexe : « Femme »,
- Colonne Âge : « <=30 »
3.8. Filtres de champs calculés
Maintenant supposons que nous voulions avoir toutes les personnes qui sont nées en avril, peu importe l'année. Pour cela nous allons devoir utiliser le filtre de champs de calcul. C’est-à-dire qu’en guise de critère nous allons utiliser une formule booléenne (qui ne peut renvoyer que deux valeurs : « VRAI » ou « FAUX »). Cette formule va faire référence à la première ligne de la base de données.
Contrairement à tout ce que nous venons de voir jusque maintenant, pour utiliser un filtre de champs calculé, il faut que le libellé de la colonne sur la plage de critère soit différent de tous les libellés de la base.
Pour notre exemple, nous allons donc procéder de la manière suivante :
- Pour commencer, il faut que nous modifiions l'intitulé de la colonne afin que celui-ci ne se retrouve pas dans les colonnes de la base de données. Nous allons donc renommer l’en-tête « Naissance » par « Naissance_ » (en rajoutant un tiret à la suite)
- Puis dans le critère de filtres nous allons utiliser une formule qui ne pourra nous renvoyer que la valeur « VRAI » ou la valeur « FAUX » : « =MOIS(C5)=4 ».
Dans le cas présent, la formule va nous renvoyer « FAUX » car le mois de naissance de Gilles Ayot est le mois de décembre.
Cette ligne sera donc masquée par le filtre.
En revanche, la seconde ligne (Iva Arnoux) sera bien affichée, car cette dernière est née en avril (de l'année 1988).
3.9. Extraire les données dans un nouvel emplacement
Le filtre avancé permet en outre d’extraire des données qui répondent aux critères spécifiés. En effet, jusqu'à maintenant, ces derniers nous ont permis de filtrer directement les informations contenues dans une base de données, mais nous allons à présent voir qu'il est également possible d'extraire les données afin de les exploiter dans un nouveau tableau.
Pour cela, nous allons continuer avec notre exemple dans lequel nous souhaitons n’avoir sous les yeux que les lignes concernant des personnes nées au cours du mois d’avril (peu importe l’année).
Nous allons alors procéder de la même manière que ce que nous venons de voir à l’instant dans le point précédent, sauf qu’au sein de l’outil de filtre avancé, nous allons ajouter les options suivantes :
- Nous allons cocher l’option « Copier vers un autre emplacement »,
- Et dans le champ « Copier dans », nous allons sélectionner les coordonnées d’une cellule sur laquelle nous souhaitons que les données soient extraites. Attention toutefois, la cellule que nous spécifions doit impérativement être sur la feuille de calcul actuelle. Nous verrons par la suite comment contourner cette limitation d’Excel.
- Enfin, nous pouvons valider le filtre avancé en appuyant sur le bouton [OK]
Nous constatons alors qu’effectivement les données concernant les personnes nées en avril sont dupliquées à l’endroit désiré.
Pour extraire des données au sein d’une autre feuille de calcul du classeur, il est nécessaire de lancer l’outil depuis la feuille de destination, puis d’aller chercher les zones de données et de critère sur la feuille contenant la base de données.
Enfin, la dernière astuce que nous allons voir ici concernant les filtres avancés va nous permettre de sélectionner les colonnes que nous souhaitons récupérer.
Pour cela, il va nous falloir utiliser un tableau de destination dans lequel nous aurons saisies les en-têtes de colonne. Puis, dans l’outil de filtre avancé, nous allons sélectionner ces en-têtes comme cellules de destination des données.
Par exemple, imaginons que nous voulions récupérer les régions (et uniquement les régions, sans autre information) :
- Nous allons commencer par saisir en dessus de notre base de données le nom de la colonne qui nous intéresse : « Région »,
- Puis nous allons utiliser le filtre avancé,
- Nous allons sélectionner la plage des cellules contenant les données,
- Nous allons sélectionner la plage des cellules contenant les critères de filtrage,
- Nous allons cocher l’option « Copier vers un autre emplacement »,
- Puis dans la zone « Copier dans », nous allons sélectionner la cellule dans laquelle nous avons saisi le mot « Région »,
- Pour terminer, nous allons valider avec le bouton [OK]
De cette manière, Excel va créer un nouveau tableau composé d’autant de lignes que de résultats répondant au(x) critère(s) défini(s).
Pour éviter que des résultats se retrouvent plusieurs fois, c’est-à-dire pour retrouver les noms de régions sans les doublons, il nous suffira avant de valider le filtre, de cocher l’option « extraction sans doublon ».
Ainsi nous retrouvons bien uniquement les régions sans doublons.
Pour rappel, vous pouvez simplement télécharger le fichier d’exemple en cliquant sur le lien disponible en début d’article (cliquez ici pour y accéder).