Aperçu des sections

  • Généralités

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

    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

  • C. Tableaux croises dynamiques

    Les tableaux croisés offrent une présentation synthétique des données d'une liste Excel ou d'une base externe, en fonction des critères choisis par l'utilisateur.

    ACTION

     onglet "insertion"
    groupe "tableaux"
    (1er bloc)

    une cellule de données étant sélectionnée
    <clic g> 
    sur  
    confirmer
    l’origine des données
    spécifier l’emplacement de création du tableau croisé
     pour valider
    le volet "office" affiche la structure du tableau croisé
    faire glisser les champs à leur position

    infos

    Modifier la présentation du volet "Office" en cliquant sur
    Le déplacer en le faisant glisser par sa barre de titre sur le document

    Le tableau est créé dynamiquement et offre une présentation synthétique et vivante des données au fur et à mesure du positionnement des champs.

    exercice

    Ouvrir le classeur tabcrois.xlsx
    Créer à partir des données un tableau croisé permettant de lister les résultats par Région, par Ville et par Nom
    (voir solution : tabcrois terminé.xlsx feuille "solution 1")

    Un simple <clic g> dans le tableau croisé affiche sa structure dans le volet office ; toute modification est lors possible en temps réel.

    infos

    Pour n'afficher que les valeurs correspondant à certains critères des champs ligne ou colonnes, il suffit de décocher les valeurs autres

    MODIFIER LES PARAMÈTRES

    ACTION

    <clic g>  sur  
    décocher les éléments à ne pas prendre en compte
     pour valider

    infos

     coche toutes les valeurs

     

    exercice

    Afficher le classeur tabcrois.xlsx
    Modifier le tableau croisé en affichant  les ventes par Ville et par Produit
    (voir solution : tabcrois terminé.xlsx feuille "solution 2")

     Une autre fonction que la somme peut être utilisée pour les champs de valeur.

    CALCULS

    ACTION

    dans le tableau croisé

    <clic d>  sur un champ de valeur
    <clic g> 

    choisir
    le type de calcul
     pour valider

    dans le volet de structure du tableau

    <clic g>  sur   du champ de valeur
    <clic g> 

    choisir
    le type de calcul
     pour valider

    exercice

    Afficher le classeur tabcrois.xlsx
    Créer un tableau croisé de la moyenne des ventes par Région et par Produit
    (voir solution ; tabcrois terminé.xlsx feuille "solution 3")