Filtrer et extraire les données Excel selon critères

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Filtrer les données de tableaux Excel

Dans cette formation, nous abordons la notion de filtres. Excel met à disposition des flèches de listes permettant de filtrer, de façon automatique ou personnalisée les données. Ces filtres sont généralement exploités sur des tableaux denses ou des sources de bases de données. Ils permettent d'offrir un vue simplifiée d'un tableau, à l'instant t. Les données filtrées correspondent aux critères, posés pour une analyse plus fine des résultats sur demande. Les autres valeurs ne sont pas supprimées pour autant. Elles sont temporairement masquées. Si bien qu'il est possible de manipuler à l'infini une source de données, en modifiant les critères selon les besoins de l'analyse. Il s'agit d'outils à la fois puissants et très souples à exploiter. Pour présenter ce concept et nous entraîner, nous avons besoin de tableaux Excel prédisposés.

Données à filtrer et à trier

• Télécharger le classeur tableaux-pour-tris-et-filtres.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel,

Ce classeur est constitué de trois feuilles. La feuille Sorties propose un tableau très volumineux. Il est constitué de 998 lignes dans lesquelles sont recensées des activités de loisirs par départements. Ce tableau présente des données répétitives comme les activités ou noms de départements. C'est pourquoi nous avions exploité, dans une autre formation, les tableaux croisés dynamiques sur cette source. Ils avaient permis de recouper les données redondantes, afin de proposer une vue synthétisée du tableau, pour en faciliter l'interprétation. Mais ici, ce sont les filtres qui nous permettront par exemple, d'isoler certaines activités, pour connaître leur répartition géographique. Il s'agit d'une autre méthode efficace pour analyser rapidement des données importantes.

La feuille Ventes Trimestre énumère les chiffres d'affaires réalisés par des vendeurs au cours du premier trimestre de l'activité. Contrairement à la feuille Sorties, ce tableau propose des données numériques. Les filtres, avec des critères personnalisés, permettront de mettre en évidence les bons résultats comme les moins bons. L'objectif étant de permettre à l'entreprise de tirer les conclusions nécessaires.

Enfin, la feuille Catalogue est vide. Nous allons l'exploiter pour réaliser une simulation complète. Ces filtres permettent d'analyser des données denses pour une analyse fine. Ces tableaux à filtrer et trier sont souvent importés depuis des sources externes, issues de bases de données. D'ailleurs une formation apprend à exporter des données d'une base Access vers un tableau Excel. Mais ici, nous allons importer une source au format .CSV dont les données ont été exportées depuis une base MySql. Il s'agit d'articles vendus sur un site Web marchand.

Importer des sources de bases de données dans Excel

• Télécharger le fichier products.csv dans le même dossier que le classeur,
• Activer la feuille Catalogue en cliquant sur son onglet,
• Cliquer sur l'onglet Données, en haut de la fenêtre Excel, pour activer son ruban,
• Dans le groupe Données externes, sur la gauche du ruban, cliquer sur le bouton Fichier texte,
• Dans la boîte de dialogue qui suit, double cliquer sur le fichier téléchargé products.csv,

Une nouvelle boîte de dialogue apparaît, proposant un aperçu des données brutes à importer. Certains réglages sont à opérer. Il faut définir un système d'encodage UTF8, correspondant à la source de données. Il faut indiquer à Excel que les données sont séparées par des caractères spéciaux pour définir les colonnes. On parle de séparateurs de liste. Ici, c'est le point-virgule qui est utilisé pour séparer une donnée d'une colonne avec celle de la colonne suivante. Le support de formation VBA Excel pour importer des données explique d'ailleurs comment découper les données sur ces séparateurs de liste, afin de reconstituer les colonnes du tableau à l'issue.

• Cocher la case Délimité,
• Dans la liste Origine du fichier, choisir Unicode (UTF-8),
• Si nécessaire, décocher la case Mes données ont des en-têtes,

En effet, les noms des champs de colonnes ne figurent pas dans les données sources. Elles ne disposent donc pas d'en-têtes de colonnes. Nous les ajouterons une fois les données importées.

• Cliquer sur Suivant en bas de la boîte de dialogue,
• Dans la zone Séparateurs de l'étape qui suit, décocher la case Tabulation,
• Cocher la case Point-virgule à la place,

Vous remarquez instantanément des délimitations apparaître, sous forme de bordures entre les colonnes, dans l'aperçu qui est donné, en bas de la boîte de dialogue. En indiquant à Excel que les données étaient séparées par des points-virgules, vous l'avez aidé à reconnaître les colonnes.

• Cliquer sur Suivant en bas de la boîte de dialogue,

Dans cette étape, Excel vous propose de formater précisément les données que vous importez dans leur colonne. L'objectif est que des montants par exemple, puissent apparaître directement au format numérique et non au format texte, afin de permettre les opérations et critères de filtres. Mais comme c'est écrit sur la boîte de dialogue, l'option Standard permet de convertir automatiquement les données reconnues dans leur format approprié.

• Cliquer sur le bouton Terminer,
• Dans la dernière étape, cliquer sur la cellule B4 de la feuille pour indiquer le point de départ de l'importation,
• Et valider par Ok,

Toutes les données de la source sont ainsi importées dans leur colonne respective. Ce tableau contient beaucoup d'informations et nous allons le démontrer. D'où la nécessité de pouvoir le filtrer pour réaliser des analyses précises selon des critères bien définis.

• Réaliser le raccourci clavier CTRL + Fin,

Cette combinaison de touches qui est entre autres enseignée par le support de formation sur les raccourcis claviers dans Excel, permet d'atteindre la dernière ligne non vide de la feuille. Vous activez ainsi la cellule G3789. Le tableau comporte donc près de 3800 lignes, d'où la nécessité de le filtrer si vous souhaitez analyser les données.

• Réaliser le raccourci CTRL + Home afin de revenir tout en haut de la feuille,
• A partir de la cellule B3 jusqu'à la cellule G3, saisir les en-têtes de colonnes, comme le propose la capture ci-dessous,

Formater les données externes importées
Avant de filtrer le tableau, nous devons le mettre en forme, pour faciliter sa lecture. Mais avant de le mettre en forme, nous devons corriger certains petits défauts de formats. Dans la colonne P_prix (colonne E), figurent les prix des articles vendus sur Internet. Or en français, la décimale est la virgule contrairement aux anglo-saxons qui utilisent le point. Dans ce contexte, nos prix ne sont pas interprétés comme des données numériques par Excel, mais comme du texte. Nous devons donc remplacer les points par des virgules sur toute la colonne. De plus, les formats de date sont trop détaillés. Nous n'avons pas besoin de la précision sur l'heure. Nous allons adapter ce format.

• Réaliser le raccourci clavier CTRL + Fin pour atteindre la dernière ligne du tableau,
• Sélectionner alors la dernière cellule du prix en colonne E, soit E3789,
• Remonter tout en haut du classeur avec la barre de défilement verticale,
• Puis, tout en maintenant la toucheMAJ (Shift) enfoncée, cliquer sur le premier prix, soit E4,

Vous sélectionnez ainsi précisément toutes les cellules numériques de la colonne E, de la dernière à la première. Cette technique, à l'aide de la touche Maj, est enseignée dans le support de formation sur les trucs et astuces dans Excel.

• Réaliser le raccourci CTRL + H pour afficher la boîte de dialogue Rechercher et remplacer,
• Dans la zone Rechercher, taper un point (.),
• Dans la zone Remplacer, taper une virgule (,),
• Cliquer sur le bouton Remplacer tout puis sur le bouton Fermer,

Tous les points sont instantanément remplacés par des virgules dans la colonne des prix. D'ailleurs vous remarquez qu'elles s'alignent automatiquement sur la droite, confirmant qu'Excel, les interprètent désormais comme des données numériques. Lorsqu'elles restent alignées sur la gauche de la cellule, Excel les considère comme du texte, sur lesquel aucune formule n'est possible. Tous les prix étant toujours sélectionnés :

• Réaliser le raccourci clavier CTRL + M,

Vous appliquez ainsi le format Euros à l'ensemble des prix. Cette combinaison est un raccourci du bouton pour le format monétaire sur le ruban Accueil. Nous allons maintenant nous occuper de réduire la précision des dates, par leur format.

• Réaliser le raccourci CTRL + Fin pour sélectionner la dernière date du tableau, soit G3789,
• Remonter tout en haut du classeur à l'aide de la barre de défilement verticale,
• Puis, tout en maintenant MAJ (Shift) enfoncée, sélectionner la première date, soit F4,

En un temps record, vous englobez ainsi précisément toutes les dates dans la sélection, soit les deux colonnes entières.

• Activer le ruban Accueil en cliquant sur son onglet,
• Déployer la liste déroulante de la section Nombre du ruban,
• Dans les choix proposés, cliquer sur Date courte,

En un clic, tous les formats s'appliquent, comme souhaité, à l'ensemble des dates du tableau. Nous devons maintenant améliorer la présentation des données dans leur ensemble. Mais étant donnée la densité des informations, nous allons appliquer des préférences de mise en forme. Nous éviterons ainsi toutes les opérations fastidieuses.

• Sélectionner l'une des cellules de l'en-tête du tableau, par exemple C3,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mettre sous forme de tableau,
• Dans la liste des visuels proposés, choisir par exemple le Style de tableau moyen 21,
• Dans la boîte de dialogue qui suit, valider les choix par défaut en cliquant sur Ok,

En effet, comme vous l'avez constaté, Excel vous demandait de confirmer les bornes du tableau sélectionné. Mais comme nous avions pris soin de présélectionner une cellule de l'en-tête, il a parfaitement détecté les limites du tableau. Une case était également cochée concernant la ligne d'en-tête du tableau afin de faire ressortir différemment les titres. Toutes les données, sur plusieurs milliers de lignes sont ainsi automatiquement mises en forme avec des préférences d'attributs de police, de cellules et de couleurs. Le support de formation Excel sur les techniques de mise en forme aborde des notions avancées pour créer des tableaux professionnels et efficaces.

Filtres automatiques sur les tableaux Excel
Comme vous l'avez remarqué, dès l'application d'un style de mise en forme, le ruban Création pour le tableau, apparaît en haut de la fenêtre Excel. Pour que ce ruban soit disponible, l'une des cellules du tableau doit être active. Ce ruban permet de modifier les préférences de mise en forme du tableau, en changeant de style notamment, mais pas seulement. Vous notez la présence d'une case, Bouton de filtre qui est cochée par défaut. C'est pour cette raison que des petites flèches apparaissent sur les en-têtes de colonnes, avec les noms des champs. Il s'agit de listes de filtres. Si vous décochez cette case, ces filtres disparaissent. Ils ne sont naturellement pas proposés pour les tableaux que vous mettez en forme vous-même. Dans ce ruban Création, un autre outil Insérer un segment, nous intéresse. Il permet de filtrer automatiquement les données d'un tableau, sur l'un des champs, comme nous l'avons déjà vu avec les tableaux croisés dynamiques.

• Dans la section Outils du ruban Création, cliquer sur le bouton Insérer un segment,
• Dans la boîte de dialogue qui suit, cocher la case du champ P_date et valider par Ok,

Une petite fenêtre flottante du segment P_date apparaît sur la feuille. Elle est remplie de toutes les entrées de la colonne afin de réaliser des filtres sur certaines de ces valeurs.

• Dans les segments, cliquer sur l'entrée 01/07/2009,
• Tout en maintenant la touche CTRL enfoncée, cliquer sur l'entrée 08/07/2009,

Vous demandez ainsi de n'afficher les enregistrements, que pour les produits dont la date de création est le 01/07/2009 et le 08/07/2009. Vous filtrez le tableau sur ces deux dates. Les données sont beaucoup moins nombreuses, regroupées pour ce filtre spécifique et donc, plus faciles à analyser que dans le tableau d'origine. Vous remarquez à ce titre, l'information de filtrage donnée par Excel dans sa barre d'état, en bas à gauche : 17 enregistrement(s) trouvé(s) sur 3786. De même, vous constatez que les étiquettes de lignes sur la gauche apparaissent en bleu, indiquant la présence d'un filtre et de lignes masquées, ne correspondant pas au critère. Enfin, vous notez la présence d'un symbole sous forme de petit entonnoir, accompagnant la flèche du filtre sur le champ P_date, en en-tête de colonne du tableau. Ce symbole indique qu'un filtre est en cours sur ce champ, pour n'afficher que les données du tableau correspondant aux conditions sélectionnées, les deux dates de segments.

• Cliquer sur la flèche du champ P_date du tableau,
• Dans la liste, choisir Effacer le filtre de P_date,

Toutes les données du tableau réapparaissent. D'ailleurs, tous les segments de la petite fenêtre P_date sont de nouveau sélectionnés. Nous aurions aussi pu cliquer sur le bouton Effacer le filtre en haut à droite de la fenêtre Segment.

• Cliquer avec le bouton droit de la souris sur la fenêtre du segment,
• Dans le menu contextuel, choisir Supprimer P_date,
• Cliquer sur le bouton de filtre du champ P_contenance en colonne C,

Comme vous le remarquez et comme l'illustre la capture ci-dessus, lorsque des champs textuels ou numériques ne proposent pas de redondance, les filtres automatiques ne sont pas de grande utilité. Chaque entrée qui est différente, est référencée dans la liste et associée à une case qui peut être cochée ou décochée. L'intérêt des filtres est de pouvoir rapidement filtrer des données d'un même groupe, répondant à un même critère, ce qui ne peut pas être réalisé dans ce cas. Pour les dates en revanche, les filtres automatiques sont plus intéressants. Ils permettent de filtrer sur des données regroupées sur une même année, voire un même mois.

• Cliquer sur la flèche du bouton de filtre du champ P_date,
• Dans la liste qui apparaît, décocher la case Sélectionner tout,
• Puis cocher la case 2011 et cliquer sur son symbole + en regard,
• Dans la liste déployée, décocher Février et Mars,

De cette manière, bien que les dates aient toutes été saisies au jour près, le filtre automatique vous permet de n'afficher que les articles pour lesquels la date de création a eu lieu en Janvier 2011. Nous allons voir que ces filtres automatiques sont d'autant plus efficaces que les tableaux proposent des données à recouper.

• Cliquer de nouveau sur le bouton de filtre du champ P_date,
• Dans la liste, choisir Effacer le filtre de P_date,

Vous retrouvez ainsi le tableau d'origine, avec toutes les données visibles, donc non filtrées.

• Activer la feuille Ventes Trimestre en cliquant sur son onglet,

Le tableau de cette feuille présente les chiffres réalisés par des vendeurs, au cours des trois premiers mois de l'activité. Comme ces opérations ont été inscrites au fur et à mesure, un même vendeur apparaît autant de fois qu'il a réalisé de ventes. Enfin, comme ce tableau n'a pas été mis en forme automatiquement, les boutons de filtres ne sont pas proposés par défaut, sur les en-têtes de colonnes. Avant de les ajouter, et bien que ce tableau ne semble pas très adapté, nous allons construire un graphique pour représenter ces données. Et vous allez voir que les initiatives d'Excel sont très intéressantes afin de proposer une vue synthétique des données, pour favoriser l'analyse graphique des résultats.

• Sélectionner l'une des cellules du tableau, par exemple B7,
• Cliquer sur l'onglet Insérer, en haut de la fenêtre, pour activer son ruban,
• Cliquer alors sur le bouton Graphiques recommandés,

Comme Excel constate que les données sources ne sont pas très adaptées pour être représentées graphiquement de façon pertinente, il vous suggère des graphiques groupés. Cela signifie qu'il recoupe les informations redondantes (Noms des vendeurs) pour représenter les données avec des opérations de synthèse (Somme des ventes), qui permettent d'interpréter les résultats.

• Sélectionner la proposition de l'histogramme groupé Somme de Réalisé par Vendeur et cliquer sur Ok,

Le graphique est créé dans une nouvelle feuille accompagné d'un petit tableau de synthèse, où les informations sont recoupées et synthétisées. Il s'agit ni plus ni moins d'un tableau croisé dynamique associé à un graphique croisé dynamique. Vous remarquez la présence de boutons de filtres sur le tableau croisé dynamique et sur le graphique croisé dynamique. En plus de la synthèse, ils vous permettent d'isoler certains résultats précis pour une analyse pertinente. Cette manipulation consistait en une petite démonstration. Mais nous partons maintenant du principe que nous souhaitons filtrer nous-même les données, directement depuis le tableau source, afin d'y voir plus clair.

• Revenir sur la feuille Ventes Trimestre,
• Sélectionner l'une des cellules du tableau, par exemple B7,
• Dans le ruban Accueil, cliquer sur le bouton Trier et filtrer du groupe Edition,
• Dans la liste, choisir Filtrer,

Les boutons de filtres apparaissent instantanément sur les en-têtes de colonnes pour chacun des champs.

• Cliquer sur le bouton de Filtre du champ Vendeur,
• Dans la liste, décocher les cases Céhef et Hamalibou,
• Cliquer de nouveau sur ce bouton de Filtre et choisir Trier de A à Z,

Non seulement vous filtrez les enregistrements, puisqu'il n'en reste plus que 12 sur 24, mais en plus, vous les regroupez par vendeur grâce au tri croissant.

• Cliquer sur le bouton de Filtre du champ Mois,
• Dans la liste, décocher les cases Février et Mars,

Vous ajoutez un critère supplémentaire au filtre précédent. Les deux filtres sont recoupés (l'un ET l'autre), si bien qu'il ne reste plus que les chiffres des deux vendeurs, seulement pour le mois de Janvier. Vous obtenez un tableau filtré sur 4 lignes au lieu de 24 au départ, permettant de comparer facilement les résultats. Ainsi, il apparaît évident que le vendeur Houda a réalisé de biens meilleures ventes que Galls sur cette période. De plus, il ressort que le vendeur Galls n'a pas atteint ses objectifs sur le mois de Janvier. Ces constats sont devenus facilement remarquables grâces aux filtres automatiques recoupés. Depuis le tableau d'origine, l'étude des chiffres sur le mois de Janvier aurait été plus fastidieuse. L'intérêt de ces filtres automatiques est donc de faire ressortir des données à comparer pour une analyse pertinente.

• Activer la feuille Sorties en cliquant sur son onglet en bas de la fenêtre Excel,

Ce tableau très dense, de 998 lignes, liste des idées de sorties dans différents départements, selon un type d'activité. Il y a donc beaucoup d'activités du même genre dans les mêmes départements. Sauf que, depuis le tableau d'origine, non filtré, il est très difficile de se faire une idée pour exploiter les résultats. Les filtres automatiques vont nous permettre de recouper les données, afin d'offrir une vue synthétique temporaire, pour exploiter les résultats sur des critères précis. Nous souhaitons par exemple afficher uniquement les Hôtels pour la ville de Valence dans la Drôme, département 26.

• Sélectionner n'importe quelle cellule du tableau de la feuille Sorties,
• Dans le ruban Accueil, cliquer sur le bouton Trier et filtrer,
• Dans la liste, choisir Filtrer,

Vous remarquez l'apparition des boutons de filtres sur les en-têtes de colonnes, soit les cellules de titres. Un filtre automatique permet de réaliser des filtres en fonction du contenu de sa colonne. Plus il y a de valeurs redondantes, plus il sera pertinent à utiliser, puisqu'il permet de les recouper. Moins les données se répètent, comme dans la colonne ID, moins il est pertinent. Un ID, identifiant, est en effet unique.

• Cliquer sur la flèche du bouton de filtre du champ Activité,

Comme les activités sont très nombreuses, il serait fastidieux de tout désélectionner pour ensuite rechercher dans la longue liste, les valeurs de champs qui doivent être conservées. Afin de filtrer et croiser les données de l'ensemble du tableau sur les hôtels, nous allons exploiter la zone de recherche du bouton de Filtre :

• Dans la zone Rechercher du bouton de filtre, taper les trois premières lettres de l'activité recherchée, soit hôt,

Les valeurs de ce champ sont ainsi filtrées sur les termes de la requête saisie, comme l'illustre la capture ci-dessus. Toutes les données ne correspondant pas, sont automatiquement exclues pour le filtre.

• Décocher les cases des activités Chambres d'Hôtes et Chambres/Tables d'Hôtes,
• Valider ce choix par Ok,

Seules les cases Hôtel et Hôtel/Restaurant sont restées cochées. Si bien qu'après validation du filtre, le tableau n'affiche plus que les sorties pour ces deux types d'activités. Deux critères cumulés sur un même champ agissent donc comme un OU. Il peut s'agir d'un hôtel OU d'un hôtel/restaurant. Il reste 406 lignes au lieu de 998 au départ, par l'application d'un simple filtre automatique. Poursuivons nos tris par recoupements :

• Cliquer sur le bouton de filtre du champ Département,
• Dans la zone Rechercher, taper 26 et cliquer sur Ok,

En saisissant les premiers caractères du critère, nous avons isolé le département de la Drôme. Si bien qu'à validation, il ne reste plus que 32 enregistrements. Nous obtenons donc une vue déjà beaucoup plus facile à exploiter. Elle n'affiche que les Hôtels et Hôtels/Restaurants pour le département de la Drôme. Seuls 32 établissements subsistent donc. Et nous pouvons déjà en conclure que l'offre n'est pas très riche. Le filtre automatique a agi de façon exclusive de manière à recouper les deux critères posés sur deux champs différents. Seuls les enregistrements vérifiant à la fois le critère sur le champ Activité ET sur le champ Département restent visibles. Les autres sont filtrés car ils ne correspondent pas au recoupement, soit à l'un des deux critères, soit aux deux à la fois. Néanmoins, le tri réalisé n'est pas encore assez précis. Nous souhaitons augmenter le niveau de précision jusqu'à la ville dans le département :

• Cliquer sur le bouton de filtre du champ Ville,
• Dans la zone Rechercher, taper Valence,
• Décocher la case Portes les valence afin de ne conserver que la ville de Valence,
• Valider ce filtre supplémentaire par Ok,

Cette fois la vue résultante est on ne peut plus synthétique. Il ne reste plus que 9 enregistrements, comparés aux plusieurs milliers du départ. Seules 9 idées de sorties sont donc des Hôtels ou Hôtels restaurants de la Drôme, plus précisément dans la ville de Valence. Il y a 3 recoupements de filtres successifs pour atteindre ce résultat. Comme vous l'avez compris, l'intérêt d'un filtre automatique est de pouvoir isoler des résultats précis, en recoupant les critères, en lieu et place du tableau d'origine. Les exploitations de données s'en trouvent facilités et le tableau source n'est pas altéré. En effet, il suffit d'annuler les filtres pour retrouver la vue d'origine. Si bien que d'autres critères peuvent être posés afin d'analyser d'autres résultats.

• Activer n'importe quelle cellule du tableau filtré de la feuille Sortie,
• Cliquer sur le bouton Trier et filtrer du ruban Accueil,
• Dans la liste, choisir Effacer,

Cette commande permet d'annuler tous les filtres recoupés ensemble. Si nous étions passés par les boutons de filtre des champs du tableau, nous aurions dû annuler les filtres un à un, soit trois fois au total ici. Au final, nous retrouvons le tableau d'origine avec ses 998 lignes.

• De la même façon, annuler les filtres en cours sur les tableaux des feuilles Ventes Trimestre et Catalogue,

Les filtres personnalisés
Les filtres personnalisés sont plus puissants que les filtres automatiques. En effet, ces derniers permettent de recouper les données sur des suggestions faites par défaut, en fonction du contenu de la colonne. Par exemple, sur des champs numériques, un filtre automatique propose de filtrer sur des valeurs précises et non pas sur des tranches de valeurs comprises entre une borne inférieure et une borne supérieure. C'est tout l'intérêt des filtres personnalisés qui permettent de construire des expressions de critères avec notamment des opérateurs de comparaison. Comme ils peuvent être cumulés entre eux, ou même avec les filtres automatiques, ils permettent d'obtenir des tableaux très synthétisés sur des critères fins. Donc ils favorisent la pertinence de l'analyse et l'exploitation des données. Il existe quatre types de filtres personnalisés proposés depuis les boutons de filtre automatique. Ils dépendent de la nature des données du champ. Ainsi pour un champ de type texte, les filtres textuels permettent de filtrer les données selon le contenu ou une partie du contenu. Sur les colonnes de nombres, les filtres numériques permettent, à l'aide de comparateurs, d'isoler des valeurs dans des tranches précisément définies. Sur les champs de dates, les filtres chronologiques permettent de trier les données sur des périodes précises. Un filtre automatique lui, n'aurait permis de filtrer que sur une ou plusieurs dates précises. Enfin, lorsqu'un format dynamique a été mis en place pour faire ressortir explicitement certaines valeurs, les filtres par couleur permettent de les regrouper rapidement sur une même vue.

Les filtres textuels
Sur la feuille Sorties, nous souhaitons afficher uniquement les idées de loisirs et parcs, qu'il s'agisse de parcs d'attractions ou de parcs animaliers, pour les départements de la Drôme et de l'Ardèche. Avec les filtres automatiques, nous aurions besoin de cocher et décocher de nombreuses cases dans les propositions par défaut, des boutons de filtres des champs Activité et Département. Avec les filtres personnalisés, une fois pris en main, c'est plus simple.

• Activer la feuille Sorties,
• Activer les boutons de filtre sur le tableau s'ils ne sont plus présents,
• Cliquer sur le bouton de Filtre du champ Activité,
• Dans la liste, pointer sur Filtres textuels,
• Dans le sous menu qui apparaît, cliquer sur Est égal à,

Une boîte de dialogue de filtre automatique personnalisé apparaît. Elle permet de définir des critères avec des opérateurs de comparaison de texte comme Commence par, Contient ou Est égal à par exemple. Ces opérateurs sont accessibles par le biais d'une liste déroulante. Ces critères peuvent être combinés ou recoupés car la boîte de dialogue propose deux lignes de construction.

• Dans la zone située à droite de l'opérateur Est égal à, taper le texte loisir,
• Valider ce critère par Ok,

A notre grande surprise, le filtre conduit à un tableau vide. En effet, l'opérateur égal est strict. Seules les activités strictement égales à loisir doivent apparaître. Or même les plus proches comme Visite/Loisir, Loisir/Sport et Loisirs- Enfants ne correspondent pas. Dans la boîte de dialogue du filtre automatique personnalisé, nous aurions pu choisir l'une des activités proposées par la liste déroulante plutôt que de la saisir. Nous aurions obtenu un résultat mais seulement sur l'une des activités, alors que nous souhaitons en regrouper plusieurs.

• Cliquer sur le bouton de filtre du champ Activité,
• Dans la liste, choisir Effacer le filtre,
• Cliquer de nouveau sur le bouton de filtre du champ Activité,
• Dans la liste, pointer sur Filtres textuels,
• Puis, choisir Contient dans le sous menu,

L'opérateur contient n'est pas strict comme le précédent. Il suffit que le bout de texte saisi soit trouvé dans l'ensemble de la chaîne de caractères de l'activité, pour que le critère soit validé.

• Dans la zone de saisie, à droite de l'opérateur Contient, taper loisir,
• Valider ce critère par Ok,

A un opérateur près, le critère est vérifié. Dans les filtres automatiques personnalisés, il convient donc d'être précis. Nous obtenons ainsi une vue filtrée du tableau d'origine. Seuls 128 enregistrements sur les 997 de départ valident le critère posé. Il s'agit de toutes les activités contenant le texte loisir. Les trois activités précédemment citées ressortent donc, tandis que toutes les autres sont filtrées. Mais nous souhaitions afficher en même temps, tous les parcs, qu'il s'agisse de parcs d'attractions ou de parcs animaliers. Nous devons donc améliorer le filtre existant sur le champ Activité.

• Cliquer sur le bouton de filtre du champ Activité,
• Pointer sur Filtres contextuels et cliquer sur Contient,

Nous sommes de retour dans la boîte de dialogue du filtre automatique personnalisé qui a bien enregistré le critère précédent.

• Dérouler la liste de la deuxième ligne et choisir de nouveau l'opérateur Contient,
• Dans la zone de droite, saisir parc et valider par Ok,

Même surprise que précédemment, plus aucun enregistrement n'apparaît. Cela est dû à un réglage que nous avons omis dans la liaison des critères de la boîte de dialogue du filtre personnalisé. L'opérateur logique Et, entre les deux critères était coché par défaut. Cela signifie que la valeur dans le champ activité doit vérifier en même temps les deux critères pour être affiché. Or aucune activité ne comprend à la fois le mot parc et le mot loisir. Le Et est exclusif, si l'un des deux critères n'est pas vérifié, la condition est considérée comme non valide. Nous devons donc remplacer l'opérateur Et par l'opérateur Ou. Ainsi le filtre automatique personnalisé sélectionnera bien les enregistrements pour lesquels l'activité contient soit le mot loisir, soit le mot attraction. Si l'un des deux critères est vérifié, la condition est considérée comme remplie.

• Cliquer sur le bouton de filtre du champ Activité,
• Pointer sur Filtres textuels et cliquer sur Filtre personnalisé,
• Dans la boîte de dialogue, cocher la case Ou et valider par Ok,

Cette fois, nous obtenons bien une vue filtrée sur tous les enregistrements pour lesquels l'activité contient au moins l'un des deux mots clés. En plus des activités de loisirs, nous affichons les parcs animaliers et parcs d'attractions. Il reste 192 enregistrements sur 997 au départ. C'est mieux mais pas encore assez synthétique. Nous allons croiser ces critères avec de nouvelles conditions posées sur le champ Département. L'objectif est de visualiser ces activités pour les départements 07 et 26 ensemble. Le principe est le même, un département peut être soit du 26, soit du 07, mais pas les deux à la fois.

• Cliquer sur le bouton de filtre du champ Département,
• Pointer sur Filtres textuels et cliquer sur Contient,
• Dans la zone de saisie de droite de la boîte de dialogue, saisir 26,
• Cocher la case Ou,
• Avec la liste déroulante du second critère, choisir l'opérateur Contient,
• Saisir 07 dans la seconde zone de saisie et valider par Ok,

Cette fois nous obtenons un filtre satisfaisant et pertinent sur la base de données des sorties, comme l'illustre la capture ci-dessous. Il reste seulement 42 enregistrements sur les 997 du départ. Les données sont donc plus simples à analyser et exploiter. Elles affichent les informations de plusieurs critères recoupés. Il s'agit des enregistrements pour lesquels l'activité est soit Parc animalier, soit parc d'attraction, soit Loisir/Sport, soit Visite/Loisir ou Loisirs-Enfants ET pour lesquels le département est soit l'Ardèche, soit la Drôme. Ces nombreux critères sont recoupés simplement avec deux filtres personnalisés sur du texte. Les filtres automatiques personnalisés apportent donc de la puissance et de l'efficacité. Ils se conçoivent très rapidement pour commander des vues filtrées selon les besoins de l'analyse à l'instant t.


Les filtres chronologiques
Comme leur nom l'indique, ces filtres sont proposés dès lors que des champs contiennent des informations au format date et/ou heure. Ils permettent d'isoler des résultats sur une période donnée, définie très précisément, d'où l'intérêt. Et même si les filtres automatiques sont déjà performants sur les champs de type date, les filtres chronologiques permettent d'aller plus loin.

• Activer la feuille Ventes Trimestre,
• Activer les boutons de filtre du tableau s'ils ne sont plus présents,
• Cliquer sur le bouton de filtre du champ Mois,
• Dans la liste, pointer sur Filtres chronologiques puis cliquer tout en bas sur Filtre personnalisé,
• Pour le premier opérateur, choisirPostérieur ou égal à,
• Saisir à côté la date 01/01/2017,
• Choisir Antérieur ou égal à pour le second opérateur,
• Puis, saisir la date 21/01/2017 dans la zone de saisie et valider par Ok,

Grâce à ce filtre chronologique personnalisé, nous obtenons seulement les résultats des ventes réalisées par les vendeurs entre ces deux dates. C'est comme si nous avions pu choisir l'opérateur Compris entre. C'est pourquoi, contrairement au cas précédent, il est cette fois impératif de conserver l'opérateur logique de liaison ET. Il s'agit de filtrer les enregistrements pour lesquels la date est comprise entre le 01/01/2017 et le 21/01/2017. La valeur du champ doit donc vérifier en même temps le critère Supérieur ou égal à 01/01/2017 ET inférieur ou égal à 21/01/2017. Nous obtenons ainsi une vue filtrée sur une période de 3 semaines, ce qui n'aurait pas été possible avec les filtres automatiques. Il nous reste seulement 5 enregistrements au lieu de 24 au départ. Les résultats des vendeurs sont donc plus simples à analyser sur cette période donnée. Les filtres chronologiques, tout comme les filtres textuels peuvent être recoupés avec d'autres filtres sur d'autres champs, afin d'affiner les résultats.

• Cliquer sur le bouton de Filtre du champ Vendeur,
• Dans la liste, décocher les cases Galls et Houda,

Vous n'obtenez plus que 3 enregistrements. Il s'agit des chiffres réalisés par les vendeurs Céhef ET Hamalibou sur une période comprise entre le 01/01/2017 ET le 21/01/2017.

Filtres numériques
Les filtres numériques permettent de poser des critères personnalisés sur les champs contenant des données de type nombre. Ils proposent des opérateurs de comparaison adaptés, permettant de filtrer des plages de résultats numériques précis, afin de faciliter l'analyse des données.

• Cliquer sur l'onglet Catalogue en bas de la fenêtre pour activer sa feuille,
• Activer les boutons de filtres du tableau s'ils ne sont plus présents,
• Cliquer sur le bouton de filtre du champ P_prix,
• Dans la liste, pointer sur Filtres numériques et cliquer tout en bas sur Filtre personnalisé,
• Définir le premier opérateur sur Est supérieur ou égal à et saisir 10 à côté,
• Définir le second opérateur sur Est inférieur ou égal à et saisir 20 à côté,
• Conserver l'opérateur logique de liaison coché sur Et, puis valider par Ok,

Ce simple filtre numérique permet de restreindre l'affichage des données à 1199 enregistrements au lieu de 3786 au départ. Il permet de recouper deux conditions grâce à l'opérateur logique ET. Les prix doivent à la fois être supérieurs ou égaux à 10 et inférieurs ou égaux à 20, soit compris entre les deux. Si nous avions choisi l'opérateur logique de liaison OU, les deux critères se seraient annulés, ne filtrant aucune donnée. Comme vous l'avez remarqué, des filtres numériques sont proposés par défaut afin de filtrer encore plus rapidement les données. Ainsi nous aurions pu facilement n'afficher que les résultats au-dessus ou en dessous de la moyenne, constituée par l'ensemble de ces données numériques.


La vue obtenue n'est pas encore suffisamment exploitable car elle contient trop d'enregistrements. Nous allons recouper le filtre numérique personnalisé avec un filtre chronologique personnalisé. L'objectif est de pouvoir visualiser uniquement les articles dont le prix est compris entre 10 et 20 Euros, ET pour lesquels la date de création a eu lieu la première quinzaine de Juillet 2009.

• Cliquer sur le bouton de filtre du champ P_date,
• Pointer sur Filtres chronologiques et cliquer tout en bas sur filtre personnalisé,
• Choisir Postérieur ou égal au pour le premier opérateur,
• Saisir la date de début 01/07/2009 dans la zone de saisie à côté,
• Définir le second opérateur de comparaison sur Antérieur ou égal au,
• Puis, saisir la date de fin 15/07/2009 dans le champ d'à côté,
• Valider ce filtre chronologique personnalisé par Ok,

Ce filtre recoupé sur les dates grâce à l'opérateur logique Et, a permis de considérablement restreindre le nombre d'enregistrements satisfaisant aux critères. Il ne reste plus que 10 enregistrements sur les 3786 du départ. Seuls sont affichés les articles pour lesquels le prix est compris entre 10 et 20 Euros, et pour lesquels la date de création a eu lieu entre le 1^er et le 15 Juillet 2009.

Filtres par couleur
Pour pouvoir appliquer les filtres par couleur, nous allons parer le tableau de quelques couleurs dynamiques grâce à la mise en forme conditionnelle. Ces couleurs doivent changer selon la valeur des nombres, en fonction des critères que nous définissons. Leur objectif est de faire ressortir de façon évidente des valeurs remarquables, afin de faciliter l'analyse du tableau. Ce format dynamique est traité en détail par le support de formation Excel des échéances de paiements. Nous souhaitons faire apparaître en vert, tous les prix de vente inférieurs à 10 Euros et en rouge, tous les prix de vente supérieurs à 100 Euros. L'intérêt d'un tel format est d'adapter dynamiquement les couleurs en fonction des valeurs, si elles venaient à évoluer.

• Sélectionner l'une des cellules du tableau filtré de la feuille Catalogue,
• Dans le ruban Accueil, cliquer sur le bouton Trier et filtrer,
• Dans la liste, choisir Effacer,

Nous avons ainsi annulé tous les filtres numériques et chronologiques précédents. Nous aurions pu cliquer sur le choix Filtrer dans la même liste. Cette action aurait eu pour effet de désactiver tous les filtres en cours sur le tableau. Mais les boutons de filtre auraient disparu. Nous souhaitons faire ressortir en vert tous les prix de vente inférieurs ou égaux à 10 Euros et en rouge, tous les prix de vente supérieurs ou égaux à 100 Euros.

• Réaliser le raccourci clavier CTRL + Fin pour atteindre la toute dernière cellule du tableau,
• Cliquer sur la cellule du dernier prix, E3789, pour l'activer,
• Remonter tout en haut du tableau à l'aide de la barre de défilement verticale,
• Tout en maintenant la touche MAJ enfoncée, sélectionner le tout premier prix, E4,

Vous sélectionnez ainsi tous les prix de vente du tableau. En effet, une mise en forme conditionnelle ne peut s'appliquer sur des données, que si ces dernières ont préalablement été sélectionnées.

• Cliquer sur le bouton Mise en forme conditionnelle du ruban Accueil,
• Dans la liste, pointer sur Règles de mise en surbrillance des cellules,
• Puis, cliquer sur Autres règles, tout en bas,
• A l'aide de la deuxième liste déroulante, choisir l'opérateur Inférieure ou égale à,
• Dans la zone de saisie sur sa droite, taper la valeur 10,

Nous venons de définir un critère sur cette plage de cellules sélectionnées. Il consiste à vérifier si la valeur numérique de la cellule est inférieure ou égale à 10. Lorsque cette condition est vérifiée, une mise en forme dynamique doit se déclencher. Pour ce faire, nous devons associer un format de cellule à la règle :

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la nouvelle boîte de dialogue qui suit, cliquer sur l'onglet Remplissage,
• Choisir un vert assez foncé pour l'arrière-plan de la cellule,
• Activer l'onglet Police de la boîte de dialogue,
• Choisir un style gras pour le texte,
• Puis définir la couleur de police sur du blanc à l'aide de la liste déroulante,
• Valider ces réglages par Ok,

Vous êtes de retour sur la boîte de dialogue qui a permis de définir la règle de mise en forme conditionnelle. Attachée à la condition, vous pouvez désormais visualiser la conséquence sur la cellule, soit le format qui vient d'être défini. C'est ce qu'illustre la capture ci-dessous.

• Valider cette règle en cliquant sur Ok,

Vous remarquez que tous les prix inférieurs ou égaux à 10 Euros ressortent de façon évidente. Ce format étant dynamique, si l'un des prix venait à passer la barre des 10 Euros, il abandonnerait automatiquement son habillage sur fond vert en police grasse, pour revêtir son formatage par défaut, sans attribut personnalisé.

• De la même façon, faire ressortir sur fond rouge, police blanche et grasse tous les prix supérieurs ou égaux à 100 euros,

Les mises en forme conditionnelles peuvent donc se cumuler et cohabiter sur une même plage de cellules, dans la mesure où elles ne recoupent pas le même critère. Sinon, c'est la dernière règle posée qui prend le dessus. C'est un moyen intéressant pour faire ressortir plusieurs types de valeurs remarquables sur une même plage. Il est désormais possible de filtrer les enregistrements de ce tableau directement par les couleurs du format dynamique. Comme cette mise en forme répond à un critère personnalisé, le filtre appliquera la même condition de tri sur les données.

• Cliquer sur le bouton de filtre du champ P_prix,
• Dans la liste, pointer sur Filtres par couleur et cliquer sur le rectangle rouge,

Instantanément, vous filtrez tous les articles selon leur code couleur. Comme ce format dynamique se déclenche pour les prix supérieurs ou égaux à 100 Euros, vous ne visualisez plus que les articles au-delà de ce prix. Il ne reste plus que 46 enregistrements sur 3786 au départ. Mais il n'est pas possible de recouper les deux. Pour cela, il faudrait un filtre numérique personnalisé avec l'opérateur logique de liaison défini sur le OU.


Filtres avancés
Comme nous l'avons vu précédemment, les filtres personnalisés permettent d'augmenter la puissance du tri sur les données par rapport aux filtres automatiques. Notamment, dans le dernier cas, l'opérateur OU permet d'afficher à la fois les articles dont le prix est inférieur à 10 Euros et supérieur à 100. Mais si nous souhaitons par exemple afficher en même temps, les articles dont le prix est compris entre 10 et 20 Euros, et les articles dont le prix est compris entre 100 et 200 Euros, nous atteignons les limites des filtres personnalisés. Ici, nous avons quatre critères à vérifier ensemble, alors que les filtres personnalisés ne permettent d'en vérifier que deux au maximum sur un même champ. Les filtres avancés ne se contentent pas de filtrer les données, ils les extraient. Le tableau d'origine reste inchangé, les données filtrées apparaissent dans un nouveau tableau, elles sont extraites.

Le filtre avancé consiste à écrire des critères directement dans des cellules Excel, sous les noms de champs répliqués du tableau d'origine. Il est même possible de définir les champs pour lesquels nous souhaitons obtenir l'information correspondant aux critères. Pour cela, il faut répliquer, dans des cellules de destinations, spécifiquement les noms des champs répliqués souhaités. Commençons simplement par obtenir une extraction de données similaire au filtre précédent : Prix inférieur ou égal à 10 Euros ou prix supérieur ou égal à 100 Euros.

• Sélectionner les titres du tableau d'origine, soit B3:G3,
• Copier ces champs (CTRL + C),
• Sélectionner une cellule à droite du tableau, par exemple I3,
• Coller ces champs (CTRL + V), pour définir les critères,
• Puis les coller plus bas, par exemple en I9 pour définir la zone d'extraction,
• Sur cette zone d'extraction, couper (CTRL + X) les champs de P_ref à P_modifié,
• Puis les coller sur le champ P_contenance,

De cette manière, dans la zone d'extraction, nous définissons explicitement que nous souhaitons obtenir les informations sur tous les champs, sauf sur le champ P_contenance qui a disparu.

• Sélectionner la cellule L4 dans la zone de critère pour le champ P_prix,
• Taper la condition <=10,
• Sélectionner la cellule du dessous, soit L5,
• Taper le critère >=100,
• Sélectionner l'une des cellules du tableau d'origine, par exemple C7,
• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Dans le groupe Trier et filtrer, cliquer sur le bouton Avancé,

Dans la petite boîte de dialogue qui apparaît, vous remarquez que le tableau dont les données sont à extraire, a correctement été identifié par Excel dans la zone Plages.

• Cliquer dans Zone de critères juste en dessous,
• Sélectionner la plage de critères, précédemment construite, soit I3:N5,
• Cocher la case Copier vers un autre emplacement,
• Cliquer dans la zone Copier dans pour l'activer,
• Sélectionner les cellules pour le début de l'extraction, soit I9:M9,
• Cliquer sur Ok pour lancer l'extraction de données,

Comme vous le remarquez, à partir de la cellule I10, tous les articles correspondant aux deux critères définis dans les cellules du dessus, sont extraits. Cette extraction se fait avec les détails et informations explicitement demandés. Toutes les informations de champ apparaissent sauf pour P_contenance qui avait volontairement été exclu de la liste, pour une vue simplifiée. Comme dans la zone Copier dans, nous avons sélectionné précisément les champs que nous souhaitions pour le filtre, Excel a extrait toutes les données correspondant aux champs spécifiés. Concernant la zone de critères, le filtre avancé d'Excel a fait la correspondance entre les noms des champs des critères et ceux du tableau d'origine. Lorsque les critères sont listés les uns sous les autres, comme c'est le cas ici, sur deux lignes, c'est l'opérateur OU qui est utilisé. Ainsi le filtre avancé d'Excel, a extrait tous champs demandés pour tous les enregistrements pour lesquels les prix étaient soit inférieurs ou égaux à 10 Euros, soit supérieurs ou égaux à 100 Euros.

Hormis la zone d'extraction qui a permis de spécifier les champs que nous souhaitions visualiser, ce filtre avancé n'a pas réalisé un tri plus complexe que celui que permet de réaliser un filtre automatique personnalisé, grâce à l'opérateur OU. Mais si nous souhaitons extraire ensemble tous les articles pour lesquels les prix sont compris entre 10 et 20 Euros, avec les articles dont les prix sont compris entre 100 et 200 Euros, nous dépassons les capacités d'un filtre automatique personnalisé. Seul le filtre avancé permettra de répondre à cette demande qui recoupe un grand nombre de conditions. Comme nous l'avons vu précédemment, les critères énumérés les uns sous les autres permettent de traduire l'opération logique OU. Pour réaliser l'opération logique ET, afin de recouper les conditions, les critères doivent être écrits les uns à côté des autres. Pour cela, dans la zone de critère, il faut répéter l'en-tête du champ sur lequel le critère à recouper doit être vérifié. Nous devons donc répliquer l'en-tête du champ P_prix dans la zone de critère.

• Sélectionner la cellule du champ P_prix de la zone de critère, soit L3,
• La copier (CTRL + C),
• Cliquer avec le bouton droit de la souris sur le champ P_date sur sa droite, soit M3,
• Dans le menu contextuel, choisir Insérer les cellules copiées,
• Dans la petite boîte de dialogue qui suit, choisir Décaler les cellules vers la droite et valider,

Vous obtenez une copie du champ P_prix sans avoir inséré de colonnes, donc sans avoir modifié la structure du tableau d'extraction, situé juste en dessous.

• En dessous du premier champ P_prix, modifier le critère <=10 par >=10,
• Pour le champ P_prix juste à sa droite, ajouter le critère <=20,

Vous spécifiez ainsi le ET. Les prix doivent à la fois être supérieurs ou égaux à 10 et inférieurs ou égaux à 20. Nous devons spécifier le second critère sur les prix, compris entre 100 et 200. Nous conservons le premier critère de la deuxième ligne (>=100).

• Pour le champ P_prix, en M5, taper le critère suivant : <=200,

• Ajouter ensuite deux fois le critère <01/10/2009 pour le champ P_date,

Nous avons ainsi bâti une zone de critères relativement complexe. Nous demandons à Excel d'extraire du tableau source, uniquement les articles dont le prix est compris entre 10 et 20 Euros et pour lesquels la date de création a eu lieu avant le 1^er Octobre 2009, ainsi que les articles dont le prix est compris entre 100 et 200 Euros et dont la date de création a également eu lieu avant le 1^er Octobre 2009. Avant de réaliser cette extraction complexe, nous devons commencer par supprimer toutes les données de l'extraction précédente.

• Sélectionner la première cellule de l'extraction, soit I10,
• Réaliser le raccourci clavier CTRL + MAF + FIN,

Vous sélectionnez ainsi toutes les cellules comprises entre la première cellule de la sélection et la dernière cellule de la feuille Excel.

• Enfoncer la touche Suppr pour effacer les données de l'ancienne extraction,
• Sélectionner l'une des cellules du tableau source, par exemple C5,
• Cliquer sur le bouton Avancé du ruban Données,
• Dans la boîte de dialogue, sélectionner la nouvelle zone de critère, I3:O5,
• Cocher la case Copier vers un autre emplacement,
• Cliquer dans la zone Copier dans et sélectionner les champs de la zone d'extraction, soit I9:M9,
• Valider par Ok,

Vous obtenez en effet une extraction uniquement pour les données correspondant à l'ensemble des critères recoupés. En faisant défiler les données vers le bas, vous constaterez que les articles dont les prix sont compris entre 100 et 200 Euros, sont également inclus. Encore une fois, c'est précisément par le recoupement de ces critères personnalisés que nous avons dépassé les capacités des filtres automatiques personnalisés.