Excel permet de masquer les enregistrements ne répondant pas aux critères spécifiés.  Si ces critères sont simples, le filtre automatique peut être utilisé ; s'ils sont complexes ou calculés, c'est le filtre élaboré qui doit être utilisé.

1   LE FILTRE AUTOMATIQUE

Le filtre automatique va permettre d'utiliser les valeurs des champs dans des listes déroutantes.

ACTION

 onglet "données"
groupe "trier et filtrer"
(3ème bloc)

une cellule de données étant sélectionnée
<clic g> 
sur
  
des triangles pointés vers le bas  correspondant à des listes déroutantes s'affichent près des titres de colonne

 

infos

<clic g> sur  pour enlever les triangles pointés vers le bas  près des champs lorsqu'ils ne sont pas utiles

 

SÉLECTIONNER DES CRITÈRES

ACTION

Description : souris gauche.gifbouton gauche

pointer sur  le triangle bas à droite du titre de colonne
<clic g> pour dérouler la zone de liste
<clic g> sur la valeur à prendre en compte
le triangle du champ sélectionné affiche  - seuls les enregistrements répondant aux critères restent alors affichés ; les autres sont masqués. Les numéros de ligne des enregistrements sont alors en bleu

infos

 efface le filtre en cours
 annule tous les filtres et réaffiche tous les enregistrements

 

exercice

Ouvrir base.xlsx
Afficher la feuille nommée "base de données"
Afficher (en masquant les enregistrements ne répondant pas aux critères) les enregistrements concernant la région "Ouest"
puis ceux de la région "Ouest" et ceux de la région "Est"

avancé

Des critères s'appliquant à deux champs distincts peuvent être combinés

 Il est possible d'être plus précis dans la définition des filtres :
L'un ou l'autre s'affichent selon la nature des données de la colonne

*     filtres textuels affiche les opérateurs de comparaison de texte

*     filtres numériques affiche les opérateurs de comparaison numériques

                        

Description : barre copie.gif
exemple

Dans base.xlsx,
Pour afficher les enregistrements dont les ventes sont supérieures à 100 k€
je clique sur de
je pointe sur
je clique sur
je saisis 100

exercice

Dans base.xlsx, feuille "base de données"
Afficher les enregistrements concernant la région "Ouest"
puis affiner en ne prenant que les enregistrements de la région "Ouest" dont les ventes sont inférieures à 100 k€ (voir base terminé.xlsx, feuille "solution1")
Annuler le filtre "ventes" puis annuler le filtre automatique

avancé

Ce filtre peut aussi utiliser plusieurs valeurs combinées pour un même champ avec les opérateurs booléens "et", "ou" ainsi que les jokers "?", "*".

OPERATEURS

*     et     intersection - les deux conditions doivent être simultanées

*     ou    union - l'une ou l'autre des conditions est nécessaire et suffisante

*     ?       remplace un caractère (n'importe lequel)

*     *        remplace plusieurs caractères

*     ~       précède le ? ou le * considérés comme texte

exercice

Dans base.xlsx, feuille "base de données"
Afficher (en masquant les enregistrements ne répondant pas aux critères) les enregistrements de la région "Ouest" et la région "Est" dont les ventes sont inférieures ou égales à 93 k€
(voir base terminé.xlsx, feuille "solution2") puis tout réafficher

Il est aussi possible de filtrer les données à partir la cellule active ; dans ce cas, la valeur servant au filtre sera la valeur même de cette cellule.

ACTION

Description : souris droite.gifbouton droit

 sélectionner la cellule
<clic d>  filtrer
<clic g>  sur

seuls les enregistrements possédant pour le champ donné la même valeur que la cellule active restent affichés

exercice

Dans base.xlsx, feuille "base de données"
Masquer les enregistrements ne concernant pas la ville de Caen
Tout réafficher

 

Enfin, il est encore possible de filtrer les enregistrements en fonction de la couleur de leur police ou de leur remplissage (seules les couleurs utilisées dans le tableau sont proposées) ; c'est là un outil qui vient en complément du format conditionnel.

 

infos

Si les couleurs de police ou de cellule sont uniformes, la commande est en grisé

 

exercice

Dans base.xlsx, feuille "base de données"
Affecter une couleur de police différente aux 10 premières lignes
Affecter une couleur de remplissage différente aux 10 lignes suivante
Affecter une couleur de police différente aux 10 lignes suivante
Affecter une couleur de remplissage différente aux 10 lignes suivante
Trier par couleur de police puis de cellule puis annuler le tri

2   LE FILTRE ÉLABORE

Le filtre élaboré offre plus de possibilités que le filtre automatique. Il nécessite la détermination de la plage de données, d'une zone de critères (conditions de sélection) et accessoirement d'une zone d'extraction (zone d'affichage).

a) LA PLAGE DE DONNÉES

La plage de données correspond à l'ensemble des cellules contenant les informations qui devront être traitées. Elle peut comprendre la totalité ou une partie d'une feuille de calcul. On peut utiliser successivement des plages de données différentes de la feuille de travail.

b) LA ZONE DE CRITÈRES

La zone de critères est une zone de la feuille de travail dans laquelle vont être indiquées les différentes conditions de sélection des enregistrements.

CRÉATION DE LA ZONE DE CRITÈRES

ACTION

insérer plusieurs lignes vierges au-dessus des données
recopier la ligne des noms des champs de données comme 1ère ligne
indiquer les critères de recherche sur la deuxième ligne et suivantes

Description : attention copie.gifATTENTION

Ne pas mettre sur les mêmes lignes zone de critères et données car si l'une de ces lignes de données se trouve cachée, les critères de la même ligne le seront aussi

c) L'EXTRACTION

L'extraction va permettre de n'afficher que les enregistrements correspondant aux critères définis (les lignes des autres enregistrements sont masquées).

ACTION

 onglet "données"
groupe "trier et filtrer"
(3ème bloc)

une cellule de données étant sélectionnée
<clic g> 
sur
  
vérifier
la plage de données
indiquer
la zone de critères
si une cellule de données est déjà sélectionnée, Excel détermine seul la plage de données

exercice

Dans base.xlsx, feuille "base de données"
Extraire (en masquant les enregistrements)
les enregistrements de la région "Ouest" dont les ventes sont inférieures à 100 K€ et de la région "Est" dont les ventes sont supérieures à 35 K€ puis annuler l'opération
(voir base terminé.xlsx, feuille "solution2")

 

infos

  évite d'afficher des enregistrements identiques pour les champs concernés

 

d) LA ZONE D'EXTRACTION

C'est une zone de la feuille de travail où vont s'inscrire les enregistrements répondant aux critères spécifiés.

ACTION

 onglet "données"
groupe "trier et filtrer"
(3ème bloc)

une cellule de données étant sélectionnée
<clic g> 
sur
 
cocher

vérifier
la plage de données
indiquer
la zone de critères
<clic g> dans

<clic g> sur la 1ère cellule de destination
 pour valider
si une cellule de données est déjà sélectionnée, Excel détermine seul la plage de données

infos

Si les données ont reçu une mise en forme de type "tableau", Excel remplace les références "ligne" et "colonne" par le n° du tableau

 

Description : attention copie.gifATTENTION

La zone d'extraction ne peut pas être sur une autre feuille de travail

 

Description : ancre ios.gifCONSEIL

Vous pouvez spécifier les colonnes à inclure dans l’opération de copie
Copiez les noms des colonnes souhaitées vers la première ligne de la zone d'extraction puis lors du filtrage, incluez les références aux noms des colonnes copiées de la zone d'extraction. (Les lignes copiées incluront alors uniquement les colonnes dont vous avez copié les étiquettes)

 

exercice

Dans base.xlsx, feuille "base de données"
Extraire (en extrayant les enregistrements en O1 par exemple) les enregistrements de la région ouest dont les ventes sont inférieures à 100 K€ et de la région est dont les ventes sont supérieures à 35 K€ puis annuler l'opération (voir base terminé.xlsx, feuille "solution3")

e) LES CRITÈRES D'EXTRACTION

Ils peuvent être beaucoup plus élaborés que dans le cas du filtre automatique.

 

ACTION

insérer plusieurs lignes vierges au-dessus des données
recopier la ligne des noms des champs de données comme 1ère ligne
indiquer les critères de recherche sur la deuxième ligne et suivantes
Indiquer la valeur de comparaison et l'opérateur logique à partir de la 2ème ligne du champ de critères (format indifférent)

TEXTE

?         remplace un caractère
*          remplace plusieurs caractères
~          précède le ? ou le * considérés comme texte

CHIFFRE

=, <, <=,>,>=…
les opérateurs standard sont disponibles

 

 

infos

Le lien entre les critères présents sur la première ligne de sélection est automatiquement de type "ET" : les enregistrements satisfaisant à la fois à tous les critères seront sélectionnés.
Le lien entre les critères présents sur des lignes successives est automatiquement de type "OU" : les enregistrements satisfaisant l'un ou l'autre des critères sont sélectionnés

 

 

Description : ancre ios.gifCONSEIL

Pour cerner la valeur d'un champ, remplacer le nom d'un champ non utilisé dans la zone de critères par celui du champ à cerner et indiquer des formules complémentaires dans les deux cellules de la même ligne

 

exercice

Dans base.xlsx, feuille "base de données"
Extraire (en masquant les enregistrements ne répondant pas aux critères) les enregistrements de la région "Sud" entre 20 et 80 k€ et ceux de la région "Nord" inférieurs à 30 et supérieurs à 75 k€ puis annuler l'opération
(voir base terminé.xlsx, feuille "solution3", 2éme champ de critères)

 

Description : attention copie.gifATTENTION

En mode "tableau" (utilisation de la mise en forme prédéfinie des tableaux), des problèmes peuvent se poser avec les critères multiples – éviter ce mode sur les lignes de critères

Les critères utilisés peuvent aussi faire référence à une formule.

CRITÈRES DE SÉLECTION CALCULE

ACTION

modifier le nom du champ de critère utilisé
(
ex : ventes ètotaux)
indiquer la formule logique en faisant référence à la première cellule de valeurs du champ
(ex
 

exercice

Dans base.xlsx, feuille "base de données"
Extraire (en masquant les enregistrements ne répondant pas aux critères) les enregistrements dont les ventes sont supérieures à la moyenne
(voir base terminé.xlsx, feuille "solution4")

3   L'ANNULATION DU FILTRE

Les enregistrements ne correspondant pas aux critères sont masqués. Il suffit de réafficher les lignes par le menu ou avec la souris pour afficher la totalité de la base.

AFFICHER LES ENREGISTREMENTS MASQUES

ACTION

<clic g>  sur
éventuellement répéter l'opération pour enlever le filtre automatique

Modifié le: vendredi 9 mars 2012, 14:25