Préparez votre certification
Microsoft Office Specialist

Formatage et filtrage 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.

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

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.

Mettre en forme toutes les cellules selon leur valeur

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

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

Les cellules qui contiennent une valeur

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. Dans la boite de dialogue qui s'ouvre, fixer la valeur à 50000 puis choisir Format personnalisé dans la liste de droite

3. Dans la boite Format de cellule, onglet Remplissage, paramétrer la couleur et le style du motif

Les détails de toutes les étapes

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.

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

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.

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.

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 :

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.

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

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

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

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

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

Les étapes à suivre sont montrées par l'animation ci-aprè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

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)

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

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.

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

À vous la main

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

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é.

Encore un cas, retour sur le tout premier exemple

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.

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

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 :

On va utiliser ces syntaxes dans des exemples :

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.

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

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

Recevez ce cours au format PDF en envoyant un message ici