B. EXTRACTION D'ENREGISTREMENTS

B.             EXTRACTION D'ENREGISTREMENTS

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.

ONGLET "DONNÉES"
groupe "trier et filtrer" (4è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 – cliquer de nouveau sur le filtre pour les enlever

 

 

BOUTON 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

 

 

 

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

 

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"

 

 

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 :

filtres textuels affiche les opérateurs de comparaison de texte

 

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

 

L'un ou l'autre s'affiche selon la nature des données de la colonne

 

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

 

 

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

 

 

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

etintersection - les deux conditions doivent être simultanées

ouunion - 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

 

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

BOUTON DROIT
sélectionner la cellule
<clic d> 
<clic g> sur
seuls les enregistrements ayant pour le champ donné la même valeur restent affichés

 

 

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

 

Enfin, il est encore possible de filtrer ou trier 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.

BOUTON GAUCHE
pointer sur le triangle bas à droite du titre de colonne
<clic g> sur ou
<clic g> sur la couleur à prendre en compte

 

 

 

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

 

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 à traiter. 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.

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

 

 

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

ONGLET "DONNÉES"
groupe "trier et filtrer" (4è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
pour valider
si une cellule de données est déjà sélectionnée, Excel détermine seul la plage de données

 

 

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 "solution3")

 

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

d)               LA ZONE D'EXTRACTION

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

ONGLET "DONNÉES"
groupe "trier et filtrer" (4ème bloc)
une cellule de données étant sélectionnée
<clic g> sur  
activer 
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

 

 

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

 

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

 

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)

 

Dans base.xlsx, feuille "base de données"
Extraire (à partir de la cellule 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.

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

 

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

 

Pour cerner la valeur d'un champ, ajouter ou 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
ex :

 

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 g1:l4)

 

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.

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

 

 

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")