Précédent Index Suivant
Chapitre 7 Listes et Tableaux croisés

Lorsque l'on manipule des quantités de données importantes telles que des listes de clients, des listes de produits, des résultats de ventes, il est important de pouvoir synthétiser les données pour servir de base aux décisions. Dans ce chapitre, nous étudions les fonctionnalités fournies par un tableur pour ce type de traitements.

7.1 Listes

Dans les figures 7.1, 7.2, on présente deux listes. la première liste est une liste d'élèves, la seconde est la liste des catégories sociaux-professionnelles. Une liste est la mémorisation dans Excel de données organisées, une liste est constituée d'une suite d'enregistrements. Tous les enregistrements ont la même structure. La structure d'une liste est définie par un certain nombre de champs. Chaque champ porte un nom qui correspond à l'entête de colonne. Les champs ont une valeur toujours prise dans un type, c'est-à-dire, l'ensemble de ses valeurs possibles. Dans la liste de la figure 7.1, les champs sont : , Noms, ..., test ; le champ est de type nombre, le champ Noms est de type caractère, le champ doublant est de type logique.



Figure 7.1 : liste des élèves



Figure 7.2 : La liste des catégories sociaux-professionnelles

7.1.1 Représentation

Il existe deux modes de représentation pour les listes : le mode tableau et le mode grille. On passe d'un mode de représentation à l'autre en utilisant le menu Données--Grille.



Figure 7.3 : Représentation sous forme de grille

7.1.2 Opérations

Les opérations permises sont souvent indépendantes du mode de représentation choisi.

Parcourir/Rechercher
En mode tableau, on parcourt la liste à l'aide des touches de déplacement ou des ascenseurs ; pour rechercher, on peut utiliser le menu Édition--Rechercher. En mode grille, on parcourt la liste à l'aide des boutons Précédente et Suivante ou des ascenseurs ; pour rechercher, on précise les critères de recherche après l'appui sur le bouton Critères.

Ajouter/Supprimer/Modifier
des enregistrements. Dans le mode tableau, ces actions sont réalisées par des actions sur les lignes ou les cellules. Pour ajouter un enregistrement, il suffit d'insérer une ligne, puis de renseigner les cellules. Il est conseillé d'insérer plutôt que d'ajouter une nouvelle ligne. En effet, lors de l'insertion d'une ligne, la taille de la liste est modifiée, et si vous avez nommé votre liste, le nom portera maintenant sur la liste avec le nouvel enregistrement compris. Supprimer un enregistrement consiste à supprimer une ligne. Modifier un enregistrement consiste à modifier les contenus des cellules de la ligne. Dans le mode grille, ces opérations sont facilitées à l'aide de boutons : Nouvelle pour Ajouter, Supprimer pour supprimer, les modifications sont faites dans la grille, on peut annuler une modification à l'aide du bouton Rétablir.

Trier
les enregistrements. On peut classer les enregistrements dans différents ordres. Un ordre est défini en précisant des critères de tri. Par exemple, la liste des personnes peut être triée dans l'ordre alphabétique des noms,

le critère de tri est :
numéro champ ordre
1 Nom croissant

On peut également souhaiter un affichage dans l'ordre des CSP. Comme il existe plusieurs élèves de même CSP, on souhaite que ces élèves soient classés dans l'ordre alphabétique.

le critère de tri est :
numéro champ ordre
1 CSP croissant
2 Nom croissant

Enfin, si on souhaite visualiser la liste dans l'ordre de mérite dans les résultats aux tests,

le critère de tri est :
numéro champ ordre
1 test décroissant

Pour appliquer un tri, il suffit de se placer n'importe où dans la liste et d'utiliser le menu Données--Trier.

Spécifications : vous devez préciser la zone de liste par son nom ou par les références à la plage de cellules et préciser les critères de tri comme dans les exemples précédents.

Filtrer/Extraire
Filtrer consiste à ne faire apparaître dans la liste que les enregistrements vérifiant certains critères. Extraire consiste à recopier la sous-liste des enregistrements vérifiant certains critères à un autre endroit dans le classeur. On peut filtrer à l'aide des filtres automatiques ou des filtres élaborés, on peut extraire à l'aide des filtres élaborés.



Figure 7.4 : liste des élèves de 11 ans

Filtres automatiques
On utilise le menu Données--Filtrer et le sous menu filtre automatique. On peut alors filtrer sur les différents champs. Par exemple, on souhaite ne voir apparaître que la liste des élèves de 11 ans, on filtre alors sur le champ age,

le critère de filtre est :
age
11

Le résultat obtenu est présenté dans la figure 7.4. Lorsqu'on filtre sur plusieurs colonnes, on filtre selon la conjonction des critères. Par exemple, si on filtre sur le champ age les élèves de 11 ans et sur le champ sexe les élèves de sexe masculin, on obtient la liste des élèves de 11 ans de sexe masculin,

le critère de filtre est :
age sexe
11 M

Filtres élaborés
Les filtres élaborés permettent de faire des filtres qui ne peuvent être réalisés à l'aide de l'outil Filtre automatique. Ce sont les filtres pour lesquels les critères de filtre contiennent des disjonctions (des ou) ou contiennent des expressions calculées. Ils permettent également de réaliser des extractions. Par exemple, on souhaite extraire la liste des les élèves de 10 ans ayant plus de 12 au test avec les élèves doublants. Cet exemple est développé dans le paragraphe suivant, le résultat est présenté dans la figure 7.5.

Spécifications des filtres :
il faut préciser la zone source (le nom ou les références de la plage contenant la liste), la zone de critères (le nom ou les références de la plage contenant les critères de filtre, la zone de destination (la cellule à partir de laquelle on va ranger la liste). Dans la définition des critères, on utilise la convention suivante : les critères sur une même ligne correspondent à une conjonction, les critères sur une ligne différente à une disjonction. Sur notre exemple, on aurait :

  • zone source : listeélèves (nom donné à la liste)
  • zone de critères : zonecritères (nom donné à la plage contenant les critères)
  • zone destination : résultat!A1 (à partir de la première cellule d'une feuille résultat du classeur)
  • critères : le critère de filtre est :
    doublant age test
    VRAI    
      10 ³ 12



    Figure 7.5 : liste des élèves doublant ou de plus de 10 ans avec plus de 12 au test




Figure 7.6 : moyenne par âge

Sous-totaux
Les sous-totaux permettent d'ajouter à la liste des lignes récapitulatrices, par exemple un décompte, une moyenne, une somme. Les sous-totaux portent sur des groupes. Un groupe est formé par des suites d'enregistrements pour lesquels la valeur d'un champ est identique.

Avertissement : pour que les groupes soient correctement formés, il faut préalablement trier sur le champ sur lequel doit porter le groupe. Pour faire des sous-totaux, on utilise le menu Données--Sous-totaux. Les fonctions de synthèse les plus courantes sont : Moyenne, Somme, Nb, Nbval, Min, Max. Par exemple, si on souhaite effectuer la moyenne au test pour chacune des tranches d'âge, on trie par âge puis on demande d'effectuer l'opération sous-total avec la fonction moyenne sur le champ test à chaque changement d'âge. Le résultat est présenté en figure 7.6. Si on souhaite obtenir l'effectif du nombre d'élèves par tranche d'âge et par sexe, on trie par âge et par sexe puis on demande d'effectuer l'opération sous-total avec la fonction nbval sur le champ test à chaque changement de sexe. Le résultat est présenté en figure 7.7.

Spécifications de sous-totaux : vous devez préciser la zone source, définir les groupes et donc les critères de tri, préciser la fonction de synthèse utilisée parmi les fonctions Moyenne, Somme, Nb, Nbval, Min, Max et les champs sur lesquels s'appliquent ces fonctions.


Figure 7.7 : effectifs par âge et par sexe

7.2 Tableaux croisés dynamiques

7.2.1 Qu'est-ce que c'est ?

Les tableaux croisés permettent de synthétiser des données en regroupant des résultats sous forme de tableaux en fonction de différents critères. Une entreprise effectuera par exemple des études de ventes de produit par produit, par gamme de produit, par gamme de produit par région... Les tableaux croisés Excel permettent de synthétiser des données provenant de une ou plusieurs listes. On peut facilement retirer ou ajouter des critères, ils peuvent être réactualisés lorsque les données d'origine sont modifiées. Les exemples présentés dans cette section utilisent la liste des élèves de la section précédente.

Le premier exemple consiste à calculer la moyenne au test par CSP. Le tableau résultat est présenté dans la figure 7.8.


Les critères sont :
champ ligne : csp
champ valeur : test
opération : Moyenne



Figure 7.8 : moyenne par csp

On peut également souhaiter voir l'influence de la csp et du sexe sur les résultats au test. Le tableau résultat est présenté dans la figure 7.9.


Les critères sont :
champ ligne : csp
champ colonne : sexe
champ valeur : test
opération : Moyenne



Figure 7.9 : moyenne par csp et par sexe

On souhaite maintenant connaître les effectifs d'élèves par âge, par sexe selon qu'ils soient doublant ou non. Le tableau résultat est présenté dans la figure 7.10.


Les critères sont :
champ ligne : age
  doublant
champ colonne : sexe
champ valeur :  
opération : Nbval



Figure 7.10 : effectifs par âge, par sexe, doublant ou non


Enfin, on souhaite maintenant connaître l'influence du sexe et d'être doublant sur les résultats aux tests pour chaque âge. Le tableau résultat est présenté dans la figure 7.11.


Les critères sont :
champ page : age
champ ligne : sexe
champ colonne : doublant
champ valeur : test
opération : Moyenne



Figure 7.11 : moyennes au test par sexe et doublant pour chaque âge ; l'âge de 9 ans a été choisi

Pour créer un tableau croisé, il faut utiliser le menu Données--Trier.

Spécifications : vous devez préciser :

7.2.2 Que peut on faire ensuite ?

La barre d'outils des TCD (Tableaux croisés dynamiques),


montre les actions possibles sur un TCD construit. Pour réaliser ces actions il est d'abord nécessaire de se placer dans un TCD construit.

Actualiser
Si les données d'origine, c'est-à-dire les données contenues dans la liste, ont changé, le TCD peut être recalculé avec cette action.
Assistant
On lance l'assistant pour permettre de modifier le TCD.
Champ dynamique
Si la sélection est sur:
Une valeur
(au milieu du tableau) on peut modifier l'opération de synthèse. On peut ajouter ou supprimer un champ valeur.
un élément ou un champ de ligne ou de colonne
on peut modifier les sous-totaux, ou masquer des éléments.

Grouper ou dissocier
cela concerne les éléments en ligne ou en colonne ou en page. C'est très intéressant surtout s'il y en beaucoup. Exemple : des dates, des notes, des sommes.

On peut grouper de deux façons :
automatique
on donne une base et un incrément (exemple : le CA est présenté à partir de 0 puis de kilofrancs en kilofrancs) ou dans le cas de dates ou d'heures on regroupe par minute, heure...mois, trimestre, année, ...
par sélection
On sélectionne à la souris les champs que l'on veut regrouper.
Afficher des détails
Comment a-t-on obtenu ce résultat ?

On sélectionne une valeur du TCD (synthétisée) Excel construit une nouvelle feuille de calcul montrant tous les élements qui ont contribué au résultat.

On sélectionne un élément en ligne ou colonne, on peut afficher pour cet élément des éléments d'autres champs.
Précédent Index Suivant