mosprepaPréparez votre certification
Microsoft Office Specialist

MS Excel 2013

Formatage et filtrage avancés

Une feuille Excel pourrait bien être intéressante avec du bon contenu, mais sans la mise en forme, sa lecture serait fastidieuse et on aurait du mal à trouver les informations importantes. Une mise en forme fait parler les données de la feuille, et lorsqu'elle est conditionnelle, elle devient dynamique en s'adaptant au contenu de la feuille.

Dans un chapitre précédent, on a vu comment appliquer des mises en forme conditionnelles prédéfinies dans Excel. La continuité logique des faits et conformément aux compétences évaluées dans l'examen 77-427 de la certification MOS Niveau Expert, on va voir, comment créer des mises en forme conditionnelles personnalisées et aussi comment créer des filtres avancés, en abordant les points suivants :

  1. Créer des mises en forme conditionnelles personnalisées
  2. Utiliser des fonctions pour formater des cellules
  3. Gérer les règles de formatage conditionnel
  4. Créer des filtres avancés

Recevez ce cours au format PDF en envoyant un message ici

Pour créer des mises en forme conditionnelles personanlisées (MFCP), je vous invite à reproduire sur un nouveau classeur, la feuille CA suivante, qui affiche les civilités, les noms, les dates d'embauche et les chiffres d'affaires (CA) concernant des revendeurs ambulants fictifs. Si vous avez la flemme de saisir, vous pouvez télécharger le classeur ici.

Formatage conditionnel personnalisé

Créer des mises en forme conditionnelles personnalisées

Dans un chapitre précédent, que vous pouvez consulter en cliquant ici, nous avions vu comment appliquer des mises en forme conditionnelles prédéfinies ; mais bien que Excel propose un grand nombre de règles de mise en forme riches et variées, on rencontre toujours des situations où l'on est dans le besoin de personnaliser ces règles, ou carrément d'en créer d'autres qui conviennent mieux à notre cas, que ce soit au niveau de la règle elle-même ou bien au niveau de la mise en forme.

En étudiant les mises en forme conditionnelles personnalisées ou (MFCP) on va voir comment modifier une règle prédéfinie pour répondre à notre attente, et aussi comment créer de nouvelles règles de formatage basées sur des formules.

Personnaliser une règle prédéfinie

L'accès à la personnalisation des règles existantes ou de création de nouvelles règles, se fait en ouvrant le bouton Mise en forme conditionnelle puis en cliquant sur la ligne Nouvelle règle

Formatage conditionnel personnalisé

Cette manipulation nous amène vers la boite de dialogue Nouvelle règle de mise en forme où l'on peut faire les changements souhaités.

Formatage conditionnel personnalisé

Mettre en forme toutes les cellules selon leur valeur

Situation 1 :

Dans le but de respecter la charte graphique, on aimerait représenter les chiffres d'affaire à l'aide de barres de données avec remplissage uni de couleur RVB(174,60,60) et sans afficher les valeurs.

Formatage conditionnel personnalisé

Cette opération n'étant pas possible avec les MFC automatiques, il faut créer une nouvelle règle personnalisée en améliorant une règle déjà existante (une des barres de données). Pour ce faire, on suit les étapes suivantes :

  1. Sélectionner les cellules des chiffres d'affaires
  2. Ouvrir le bouton Mises en forme personnalisées et cliquer sur la ligne Nouvelle règle (ou bien sur Autres règles)
  3. Dans la boite de dialogue qui s'ouvre, choisir la ligne Mettre en forme toutes les cellules selon leurs valeurs
  4. Dans la zone Modifier la description de la règle, ouvrir la liste Style de lise en forme et choisir Barre de données, puis cocher la case Afficher la barre uniquement
  5. Ouvrir la liste Couleur, puis cliquer sur la ligne Autres couleurs
  6. Dans la petite boite qui apparait, choisir la palette de couleur RVB, puis donner les valeurs correspondantes à chacune des composantes : 174 pour le rouge, 60 pour le vert et 60 pour le bleu
  7. Valider tout en cliquant sur les boutons OK

Formatage conditionnel personnalisé

L'annimation suivante montre le déroulement de la manipulation.

Formatage conditionnel personnalisé

Les cellules qui contiennent une valeur

Situation 2 :

On voudrait appliquer le motif Rayure diagonale fine couleur RVB(174,60,60), pour tous les chiffres d'affaire Supérieurs à 50000.

Formatage conditionnel personnalisé

Pour répondre à ce besoin, on va se baser sur une MFC prédéfinie qu'on va personnaliser. La règle de MFC la plus proche est : Règles de mise en surbrillance des cellules > Supérieur à . On va donc utiliser cette règle comme point de départ puis apporter les modifications nécessaires pour la personnaliser.

  1. Ouvrir le bouton Mise en forme conditionnelle et choisir la première ligne
  2. Formatage conditionnel personnalisé
  3. Dans la boite de dialogue qui s'ouvre, fixer la valeur à 50000 puis choisir Format personnalisé dans la liste de droite
  4. Formatage conditionnel personnalisé
  5. Dans la boite Format de cellule, onglet Remplissage, paramétrer la couleur et le style du motif
  6. Formatage conditionnel personnalisé

Les détails de toutes les étapes

Formatage conditionnel personnalisé

Les premières valeurs ou bien les dernières

Le marquage des valeurs rangées parmi les premières ou bien parmi les dernières, se fait par une mise en forme ou bien par des icônes. Notre but dans ce paragraphe est de marquer uniquement les premières valeurs par des icônes distinctives.

Situation 3 :

Par défaut le jeu d'icônes utilise 3, 4 ou 5 symboles pour marquer chacune des valeurs de la zone sélectionnée, en fonction de son rang dans l'ensemble des valeurs. Ce que nous voulons ici c'est d'afficher une coche verte uniquement pour les CA supérieurs à 50000 et rien pour les autres.

Formatage conditionnel personnalisé

On part de la catégorie Jeux d'icônes puis autres règles pour personnaliser les paramétrages.

Formatage conditionnel personnalisé

  1. Choisir le style des icônes (dans notre cas : 3 symboles sans cercle)
  2. Définir la valeur pour le cas "Si la valeur est Supérieure ou égale" (>=) avec son type ici Valeur : 50000 et type : Nombre
  3. Définir la valeur "Aucune icône de cellule" pour les autres cas

L'animation suivante montre les étapes pour marquer uniquement les premières valeurs par des icônes distinctives.

Formatage conditionnel personnalisé

Créer une nouvelle règle

Voici une fonctionnalité très puissante pour créer les MFCP les plus complexes. Le principe est relativement simple, l'application ou la non application de la mise en forme est basée sur le retour d'une expression booléenne formulée par l'utilisateur. Si cette expression retourne un VRAI (True), la mise en forme est appliquée, si le retour est un FAUX (False), la mise en forme n'est pas appliquée. La seule petite difficulté que l'on peut rencontrer, réside dans la façon de formuler l'expression booléenne, celle-ci doit être pertinente et précise.

Une expression booléenne est une expression qui vérifie la véracité d'une proposition et renvoie un VRAI (True) si la proposition est vraie ou bien un FAUX (False) si la proposition est fausse.

Situation 4 :

Mettre en gras les noms des revendeurs dont la civilité est "Homme".

Formatage conditionnel personnalisé

La particularité de cette situation c'est que les données que l'on désire mettre en forme se situent dans la colonne B alors que le critère sur lequel on va se baser, se trouve sur une autre colonne A. La question à se poser doit être formulée de telle façon que la réponse soit ou bien OUI (VRAI) ou bien NON (FAUX). Exemple : Est-ce que la civilité est "Homme" ? Pour exprimer cette question dans la cellule E2 on va donc écrire l'expression booléenne suivante :

Formatage conditionnel personnalisé

La civilité du premier revendeur étant dans la cellule A2, cette écriture s'explique de la façon suivante : La cellule E2 reçoit la réponse à la proposition civilité = Homme, cette réponse ne peut pas être autre chose que Vrai ou Faux.

Formatage conditionnel personnalisé

C'est en se basant sur cette réponse que la MFCP sera appliquée ou non.

Pour mettre en application cette MFCP, on doit créer une nouvelle règle en ouvrant la boite de dialogue Nouvelle règle de mise en forme et en choisissant la dernière option Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.

  1. Sélectionner la plage de cellules à mettre en forme, puis choisir Nouvelle règle dans Mise en forme conditionnelle
  2. Dans la boite de dialogue Nouvelle règle de mise en forme, choisir l'option Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué
  3. Dans la zone Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vraie, taper l'expression =A2="Homme"
  4. Cliquer sur le bouton Format
  5. Dans la boite de dialogue Format de cellule qui s'ouvre, définir la mise en forme demandée, Style gras dans notre cas
  6. Valider les boites de dialogue ouvertes

Formatage conditionnel personnalisé

L'animation suivante montre comment mettre en gras les noms des revendeurs dont la civilité est "Homme".

Formatage conditionnel personnalisé

Situation 5 :

Créer une règle qui met en surbrillance :

  • Remplissage : RVB(174,60,60)
  • Couleur Police : Blanc, Arrière-plan 1
  • Style : Gras

toutes les lignes des revendeurs respectant les conditions :

  • Civilité = "Femme"
  • CA > 50000

Formatage conditionnel personnalisé

La spécificité de cette situation réside en deux points :

  • Le premier point, c'est que la condition est une condition composée de deux sous-conditions liées par l'opérateur logique ET puisqu'elles doivent être vérifiées toutes les deux à la fois.
  • Le deuxième point c'est qu'on doit mettre en surbrillance non pas une seule cellule mais toute la ligne

Pour mettre en évidence les revendeurs qui répondent à ces conditions on doit suivre les étapes ci-après :

  1. Sélectionner tout le tableau (sans la ligne des en-têtes)
  2. Saisir la formule : =ET($A2="Femme";$D2>50000) dans le champ Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vraie
  3. Définir la mise en forme demandée : le remplissage et les attributs de la police

Formatage conditionnel personnalisé

Notez bien

Dans la formule remarquez le symbole $ devant le A et devant le D qui a pour but de fixer ces deux colonnes pour faire référencer toutes les cellules à ces deux colonnes.

Formatage conditionnel personnalisé

L'animation suivante montre comment mettre en évidence les revendeurs Femmes qui ont un chiffre d'affaire supérieur à 50000.

Formatage conditionnel personnalisé

Gérer les règles de formatage conditionnel

La gestion des règles de MFCP (créées par l'utilisateur), se fait dans la boite de dialogue Gestionnaire des règles de mise en forme conditionnelle. Cette fenêtre accessible en cliquant sur la ligne Gérer les règles du bouton Mise en forme conditionnelle, permet d'effectuer les différentes opérations de gestion des règles de mise en forme : Modification, Suppression, Création, Affichage, Réorganisation

Formatage conditionnel personnalisé

Afficher les règles

La liste Afficher les règles de mise en forme pour sert à afficher les règles appliquées à une zone du classeur. Ouvrez cette liste et choisissez la plage de cellules, le tableau ou la feuille dont vous voulez gérer les règles.

Formatage conditionnel personnalisé

Modifier une règle

Pour modifier une règle, sélectionnez-la dans la liste puis cliquez sur le bouton Modifier la règle pour ouvrir la boite de dialogue Modifier la règle de mise en forme et ainsi vous pouvez apporter vos modifications au niveau de l'expression de la règle et aussi au niveau de la mise en forme si vous le souhaitez.

Formatage conditionnel personnalisé

Changer la priorité des règles

Les règles de mise en forme sont affichées dans la liste dans un ordre prioritaire, une règle située au-dessus d'une autre est prioritaire par rapport à elle. Dans la capture ci-dessous, on voit que la première règle en haut de la liste est dominante et ne laisse pas celle de dessous s'exprimer.

Formatage conditionnel personnalisé

Par exemple dans les lignes 4 et 5, le chiffre d'affaire (CA) est bien inférieur à 20000 et pourtant elles n'ont pas pris la mise en forme paramétrée dans la deuxième règle, parce que celle-ci est écrasée par la première qui est au-dessus d'elle.

Pour rendre la deuxième règle prioritaire, il faut la mettre en tête de liste et ce en la sélectionnant et cliquant sur le bouton Monter puis cliquez sur appliquer pour voir le résultat et le bouton OK pour valider.

Formatage conditionnel personnalisé

Interrompre si vrai

Si plusieurs règles de MFCP sont appliquées sur une cellule, cette fonctionnalité ordonne à Excel de ne pas chercher à appliquer sur cette même cellule, une règle de rang inférieur si une règle prioritaire est déjà appliquée.

Pour mettre en évidence cette fonctionnalité, on va ajouter une règle qui s'applique à toutes les cellules de la colonne (CA), comme un jeu d'icônes par exemple, que l'on va placer au dernier rang tout en bas de la liste.

Formatage conditionnel personnalisé

Remarquez que bien que la règle de jeu d'icônes est classée tout en bas de la liste, les icônes s'affichent dans toutes les cellules même celles pour lesquelles les deux premières règles sont appliquées.

Ce que nous souhaitons faire, c'est ne faire apparaitre ces icônes que dans les cellules qui ne répondent pas aux deux premières règles (lignes 6 et 11). Pour ce faire, on doit cocher les cases Interrompre si vrai pour les deux premières règles, ce qui veut dire : Si ces règles sont vraies, ne pas afficher l'icône, tout simplement. Ne pas oublier de valider.

Formatage conditionnel personnalisé

Supprimer une règle

Pour supprimer une règle, il suffit de la sélectionner dans la boite de dialogue Gestionnaire des règles de mise en forme conditionnelle et de cliquer sur le bouton Supprimer la règle.

Formatage conditionnel personnalisé

Une autre méthode pour supprimer les règles de mise en forme conditionnelle consiste à ouvrir le bouton Mise en forme conditionnelle puis pointer sur la ligne Effacer les règles et choisir la commande qui convient.

Formatage conditionnel personnalisé

Création de filtres avancés

Les filtres avancés ou élaborés, sont des filtres très puissants qui comblent les lacunes des filtres automatiques.

Les limites des filtres automatiques

Avec les filtres automatiques on ne peut pas faire les opérations suivantes :

  • filtrer selon des conditions complexes
  • extraire vers d'autres zones
  • créer des champs calculés

Exemple de manipulation qu'on ne peut pas faire en une seule opération avec les filtres automatiques, afficher uniquement les revendeurs ayant réalisé un CA supérieur à 45000 et ceux qui sont embauchés avant le 01/01/2002. Les revendeurs doivent donc respecter au moins l'un des deux critères.

Filtres avancés

Pour faire ce genre de sélection, il faut faire appel aux filtres avancés (élaborés) accessibles via le bouton Avancé dans le groupe Trier et filtrer de l'onglet DONNÉES

Filtres avancés

Les paramètres d'un filtre avancé

Pour exécuter un filtre avancé, on a besoin de deux paramètres obligatoires :

  • Les données sources : c'est le tableau à filtrer
  • La zone de critères : C'est la plage de cellules qui reproduit exactement les en-têtes des données source avec les critères de sélection

Ces paramètres, avec d'autres paramètres facultatifs qui dépendent des besoins particuliers, seront renseignés dans la boite de dialogue Filtre avancé.

Commençons par un exemple simple qu'on peut bien faire avec les filtres automatiques mais qui va nous permettre de débuter en douceur.

Exemple 1 :

On veut afficher uniquement les revendeurs qui sont embauchés avant le 01/01/2002 et masquer tous ceux qui ne répondent pas à ce critère.

Les étapes à suivre sont montrées par l'animation ci-après :

Filtres avancés

  1. Copier la ligne d'en-tête du tableau à filtrer vers une autre zone
  2. Écrire le critère sous le champ souhaité
  3. Ouvrir la boite de dialogue Filtre avancé en cliquant sur le bouton Avancé
  4. Dans cette boite, cliquer dans la zone Plages puis sélectionner la totalité du tableau à filtrer
  5. Cliquer dans la zone Zone de critères puis sélectionner toute la zone de critères
  6. Valider en cliquant sur OK

Filtres avancés

Résultat : un tableau filtré qui n'affiche que les enregistrements qui répondent au critère prédéfini (Revendeurs embauchés avant le 01/01/2002)

Filtres avancés

Pour supprimer le filtre et réafficher l'intégralité du tableau, il suffit de cliquer sur le bouton Effacer du groupe Trier et filtrer.

Filtres avancés

Maintenant c'est à vous de faire ces deux exercices en utilisant les filtres avancés.

Exercice 1 :

Afficher uniquement les revendeurs qui ont réalisé un CA supérieur à 45000

Exercice 2 :

Afficher uniquement les revendeurs Femme

Filtrer ou extraire

Dans le paragraphe précédent nous avons effectué un filtre sur place, c'est à dire que nous avons affiché uniquement les enregistrements qui répondaient au critère de sélection et nous avons masqué ceux qui ne répondaient pas.

L'un des avantages des filtres élaborés c'est qu'on peut faire des extractions c'est à dire, recopier les enregistrements filtrés vers une autre zone de la feuille, ou même vers une autre feuille, tout en laissant le tableau source intact. Dans ce cas, il faut choisir l'option Copier vers un autre emplacement dans la boite de dialogue Filtre avancé et définir l'emplacement de destination.

Exemple 2 :

On veut extraire vers la plage G3:J3 toutes les informations concernant les derniers embauchés (embauche supérieure ou égale à 01/01/2009).

Filtres avancés

Dans la boite de dialogue Filtre avancé, on choisit l'option Copier vers un autre emplacement puis on définit la plage de destination dans le champ Copier dans

Filtres avancés

Filtres avancés

À vous la main

Exercice 3 :

Extraire les revendeurs Femme vers la plage G8:J8 et les revendeurs Homme vers la plage G16:J16

Filtres avancés

Le critère "ET" et le critère "OU"

Exemple 3 :

Nouvelle situation : On veut extraire les revendeurs récemment embauchés embauche supérieure ou égale à 01/01/2009 et qui ont un CA supérieur à 40000.

Filtres avancés

Nous avons donc deux contraintes à respecter en même temps :

embauche >= 01/01/2009 ET CA > 40000

C'est une condition composée de deux critères reliés par l'opérateur logique ET. Pour exprimer cette condition, il faut écrire les deux critères sur la même ligne puis exécuter le filtre avancé.

Filtres avancés

Exercice 4 :

Faites cette extraction

Encore un cas, retour sur le tout premier exemple

Exemple 4 :

Filtrer le tableau pour n'afficher que les revendeurs ayant réalisé un CA supérieur à 45000 et ceux qui sont embauchés avant le 01/01/2002. Les revendeurs doivent donc respecter au moins l'un des deux critères.

Filtres avancés

Il s'agit ici d'une condition composée de deux critères liés par l'opérateur logique OU, c'est à dire que pour retenir un revendeur, il suffit qu'il vérifie l'un des deux critères. Ces critères, car ils sont liés par l'opérateur logique OU, doivent être écrits dans la zone de critères sur deux lignes différentes.

Filtres avancés

Deux choses à noter :

  • Il n'est pas demandé d'extraire mais plutôt de filtrer, on va donc faire un filtre sur place de la liste.
  • On doit écrire les deux critères sur deux lignes différentes

Filtres avancés

À retenir :

  • Des critères liés par l'opérateur logique ET, on les met sur la même ligne
  • Des critères liés par l'opérateur logique OU, on les met sur des lignes différentes

Utiliser les caractères génériques

Les caractères génériques sont utilisés pour effectuer des recherches poussées sur les données textes. Le caractère Étoile (*) sert à remplacer un nombre quelconque de caractères, et le caractère Point d'interrogation (?) est utilisé pour remplacer un seul caractère.

Exemples :

ExpressionSignification
A* Une chaine de caractères qui commence par A.
*e Une chaine de caractères qui se termine par e.
*l* Une chaine de caractères qui contient la lettre l.
?d* Une chaine de caractères dont la deuxième lettre est un d.
????? Une chaine de caractères composée de 5 lettres.

On va utiliser ces syntaxes dans des exemples :

Exemple 5 :

Afficher les revendeurs dont le nom commence par A

Filtres avancés

On écrit bien sous le champ nom la syntaxe ="=A*" comme c'est indiqué dans la barre de formules. Après validation, l'écriture change dans la cellule mais garde bien sa syntaxe dans la barre de formules.

Filtres avancés

Exercice 5 :

Afficher tous les revendeurs dont le nom est composé de 6 caractères

Filtres avancés

Exercice 6 :

Afficher tous les revendeurs dont le nom comporte un "a" en deuxième position

Filtres avancés

Trouver les champs vides

La syntaxe pour exprimer une valeur vide est un peu particulière puisqu'il faut écrire ="=". Utilisons cette écriture sous le champ embauche, pour trouver les enregistrements dont la date d'embauche n'est pas saisie.

Filtres avancés

Filtrer avec un champ calculé

Les possibilités des filtres avancés sont illimitées avec l'utilisation de formules comme critères. La création d'un champ calculé doit respecter les points suivants :

  • La formule doit retourner une valeur booléenne VRAI ou FAUX
  • Le nom du critère ne doit pas être le nom d'une colonne du tableau source
  • La formule doit faire référence à une valeur de la première ligne du tableau source

Exemple 6 :

Afficher les revendeurs qui ont une ancienneté inférieure ou égale à 15 ans.

Utiliser la formule =(DATEDIF(C2;AUJOURDHUI();"Y")<=15)

Filtres avancés

  1. On doit créer un nouveau champ nommé par exemple ancienneté, il ne doit pas avoir comme nom l'une des valeurs : "civilité", "nom", "embauche", "CA".
  2. On lui donne comme critère =(DATEDIF(C2;AUJOURDHUI();"Y")<=15)
  3. On effectue le filtre avancé comme dans l'animation ci-dessous

Filtres avancés

Remarquez que nous avons utilisé dans la formule deux fonctions Excel :

  • la fonction AUJOURDHUI() qui renvoie la date du jour en cours
  • la fonction DATEDIF() qui calcule la différence entre deux dates. Le paramètre "Y" permet de donner le résultat de la différence en nombre d'années

Exercice 7 :

Extraire vers la plage G3:J3 les revendeurs embauché en 2007

Indication : créer le champ calculé anneeEmbauche auquel vous donnez l'expression =(ANNEE(C2)=2007) comme critère

Filtres avancés

Recevez ce cours au format PDF en envoyant un message ici

MOS Prepa