Cours BARDON - Microsoft Office
ACCUEIL
•  Télécharger    •  Imprimer EXCEL 2003
SOMMAIRE
Chapitre précédent
Chapitre suivant

Excel 03/ Chap 12 - LISTE, PLAN, SOUS-TOTAUX

1. LISTE DE DONNEES

Création d'une liste de données

Entourée d'une bordure bleue, la liste de données de l'exemple occupe la plage A3 : D8.

On peut sélectionner une colonne de la liste en pointant sur la bordure supérieure de sa case d'étiquette, puis quand le pointeur se transforme en flèche noire, cliquez.
La méthode est similaire pour sélectionner une ligne (pointez sur la bordure gauche de sa case d'étiquette).
La dernière ligne, qui débute par un astérisque bleu, est une ligne supplémentaire qui permet d'ajouter les données d'un nouvel enregistrement.
Dès qu'on ajoute une donnée dans une cellule adjacente à la plage, sa ligne ou sa colonne est intégrée dans la liste (sauf si la donnée est saisie juste au dessous d'une ligne de totaux).
La liste est active dès qu'une cellule est sélectionnée.
Plusieurs listes peuvent être créées sur une même feuille.

Création

• Sélectionnez une plage de cellules, vide ou contenant déjà des données ;
• Clic droit sur la plage > Créer une liste. La fenêtre "Créer une liste" apparaît.

• une bordure bleue, qui la distingue des autres données de la feuille de calcul ;
• des en-têtes de colonnes (à défaut, ils sont nommés Colonne1, Colonne2...) dotés chacun d'un menu déroulant ;
• une ligne supplémentaire, vide, débutant par un astérisque bleu. Elle disparaît quand la liste n'est plus active.

Commandes relatives à la liste

Elles sont accessibles par diverses méthodes :

• soit par clic droit sur une cellule de la liste > Liste ;
• soit en passant par menu Données > Liste ;
• soit avec la barre d'outils "Liste".

• "Masquer la bordure des listes inactives" : l'activation de cette commande permet de n'afficher que la bordure bleue de la liste active ;
• "Redimensionner une liste"
  Il est également possible de cliquer-glisser sur la poignée située sur l'angle inférieur droit de la bordure de liste ;
• "Ligne Total" : sur cette ligne, dans chaque colonne, un menu déroulant propose plusieurs fonctions. Par exemple : Moyenne, Max et Somme.
  La fonction "Nombre" renvoie le nombre de toutes les données sous l'étiquette, tandis que l'option "Nb" renvoie le nombre des seules données numériques.
  Ces fonctions sont appelées "fonctions de synthèse" car elles portent sur tous les éléments d'une colonne.
  Le résultat d'une fonction de synthèse est appelé "sous-total", bien que la fonction peut être autre que Somme ;
• "Convertir en plage" : la liste est transformée en plage "normale".
  Si elle est présente, la ligne "Total" demeure ;
• "Publier la liste" et ses commandes rattachées concernent une liste publiée sur un serveur équipé de Microsoft Windows SharePoint Team Services.

Trier les données

Une seule clé de tri

Deux ou trois clés de tri

• tri par ordre alphabétique des noms ;
• puis quand les noms sont identiques, tri par ordre alphabétique des prénoms ;
• puis, en cas d'homonymes, tri par ordre alphabétique des villes.

Filtrer les données

Filtre automatique

• "Tous" : toutes les données sont affichées, il n'y a pas, ou plus, de filtre ;
• "10 premiers" : l'activation de cette option affiche la fenêtre "Les 10 premiers", qui contient plusieurs zones de saisie ;
• "Personnalisé" : l'activation de cette option affiche la fenêtre "Filtre automatique personnalisé", qui propose diverses options et qui contient également plusieurs zones de saisie.
  Par exemple : on peut afficher les amis dont la date de naissance est comprise entre le 01/01/1980 et le 01/01/1990.

Filtre élaboré

Un filtre élaboré combine des critères sur des données et permet d'obtenir des filtres qu'il serait impossible d'obtenir en mode "Filtre automatique".
Dans l'exemple précédent, un filtre élaboré doit être mis en place si on souhaite obtenir les habitants de Caen, nées après 1983, ainsi que les habitants de Limoges, nées après 1970. L'application d'un filtre automatique ne pouvant combiner tous ces critères, il convient d'appliquer un filtre élaboré.

Pour utiliser un filtre élaboré, il faut définir le filtre, puis l'appliquer :

•  Définition du filtre

• La première ligne contient des en-têtes de la liste de données. Il suffit de saisir ceux sur lesquels porteront les critères. Il peut y avoir duplication d'en-tête.
  Dans l'exemple donné, si on souhaite afficher les habitants de Caen nés après 1980 et avant 1985, on saisira deux colonnes Date Naissance, l'une indiquant >31/12/1980 et l'autre <01/01/1985, ainsi que la colonne Ville indiquant Caen ;
• Les lignes suivantes contiennent les définitions des critères.
  Les critères qui sont disposés sur une même ligne doivent être respectés simultanément (cela correspond à l'opérateur "et").

Placez la zone de critères de sorte qu'elle ne risque pas d'interférer avec le tableau de données, éventuellement sur une autre feuille du classeur, ou bien en haut et à droite du tableau (afin que le tableau, en s'étendant vers le bas, ne puisse atteindre la plage de définition de critères).

Exemple :
Zone de critères pour obtenir les habitants de Caen nés après 1983, ainsi que ceux de Limoges nés après 1970 :

EXCEL ne distingue pas la casse (noms et villes peuvent être écrits en minuscules).

On peut utiliser le signe ? pour remplacer un seul caractère, et le signe * pour remplacer zéro ou plusieurs caractères.
Ne resteront affichés que les enregistrements remplissant les conditions d'au moins une ligne de critères.

•  Application du filtre

Activez la liste de données.
Puis affichez la fenêtre "Filtre élaboré" : menu Données > Filtrer > Filtre élaboré.

• "Plages" : dans la zone de saisie, sont affichées les références de la plage de la liste de données (dans l'exemple $A$3:$D$7). Sinon, cliquez dans cette zone, puis sélectionnez la plage.
• "Zone de critères" : cliquez dans cette zone (éventuellement effacez les références inscrites), puis sélectionnez la plage de la zone de critères (dans l'exemple $H$1:$I$3).

Position du tableau de données après filtrage :
Vous pouvez choisir :

• de "Filtrer la liste sur place" : le tableau après filtrage remplacera le tableau original ;
• ou bien de "Copier vers un autre emplacement" : cliquez dans la zone "Copier dans", puis sélectionnez la première cellule de l'emplacement souhaité (éventuellement effacez d'abord les références inscrites).

En cochant la case "Extraction sans doublon", on n'affiche que les lignes distinctes.

Exemple :
Après application du filtre élaboré et avec l'option copie à partir de la cellule $L$1, on obtient :

Après application d'un filtre avancé, le tableau n'est plus en mode "Filtre automatique", d'où l'absence de menu déroulant à chaque case d'en-tête.

2. CONSTITUTION D'UN PLAN

Certaines plages de données gagnent à être organisées en plan. Un plan permet de visualiser aisément les titres, et d'accéder d'un clic aux données détaillées.
Le plan peut être en lignes (exemple ci-après) ou en colonnes, les principes sont les mêmes.
Un plan est constitué de lignes de synthèse, chacune regroupant des lignes de détail, qui peuvent être affichées ou masquées. Une ligne de détail peut à son tour être ligne de synthèse.
Un plan peut contenir ainsi jusqu'à huit niveaux.

Exemple :

Dans cet exemple, les lignes de synthèses sont : Fruits/Légumes, Fruits, Légumes et Pommes.
Pour mieux les distinguer, on leur applique des mises en forme différentes de celles des lignes de détail.

Pour établir un plan, commencez par afficher la fenêtre "Paramètres" : menu Données > Grouper et créer un plan > Paramètres.
Dans l'exemple, les lignes de synthèse étant au-dessus des lignes de détail, décochez les deux premières cases ("Lignes de synthèse sous les lignes de détail" et "Colonnes de synthèse à droite des colonnes de détail"), puis validez.

Sélectionnez des lignes qui seront des lignes de détail, sans être lignes de synthèse.
Dans l'exemple, sélectionnez les lignes 6 et 7, Canada et Reinette.
Puis : menu Données > Grouper et créer un plan > Grouper. La fenêtre "Grouper" s'affiche. Choisissez les options souhaitées. Groupez ainsi les deux lignes sélectionnées. Validez.
Un trait vertical relie alors ces données (marquées chacune d'un point), avec le symbole - à une extrémité.
Si vous cliquez sur ce signe moins, les données sont masquées. Le symbole + remplace alors le -. Cliquez sur le signe plus pour afficher à nouveau les données.
Dans l'exemple, les numéros 1 et 2 apparaissent en haut à gauche, indiquant que le plan comporte maintenant deux niveaux.
Cliquer sur un bouton de numéro, permet d'afficher les lignes correspondant à ce niveau, ainsi que celles des niveaux de numéros inférieurs.

Pour créer un niveau supplémentaire, sélectionnez des lignes dont l'une au moins est déjà ligne de synthèse, puis groupez-les.
Dans l'exemple, sélectionnez lignes 3 à 7, Fraises, Kiwis, Pommes, Canada, Reinette. (Pommes est ligne de synthèse pour Canada et Reinette). Puis, groupez ces cinq lignes : menu Données > Grouper et créer un plan > Grouper.
Un numéro 3 est ajouté, signalant que le plan comporte maintenant trois niveaux de regroupement.

Sélectionnez les lignes 9 et 10, Carottes et Oignons, et groupez-les. Pour terminer, sélectionnez puis groupez les lignes 2 à 10. Un niveau 4 est ajouté.

Cliquer sur un bouton de numéro, permet d'afficher les lignes correspondant à ce niveau, ainsi que celles des niveaux de numéros inférieurs.
Dans l'exemple, si on clique sur le bouton du niveau 2, on obtient l'affichage des numéros 1 et 2 :

Pour dissocier des données

Pour supprimer un plan

Plan automatique

Exemple :

Sélectionnez une cellule de la plage du plan, puis : menu Données > Grouper et créer un plan > Plan automatique.
Les lignes 14, 15 et 16 sont automatiquement groupées.

3. UTILISATION DE FONCTIONS DE SYNTHESE

Application d'une fonction de synthèse

• Si nécessaire, commencez par trier les données en fonction du sous-total à calculer.
  Pour afficher la fenêtre "Trier" : menu Données > Trier ;
• Puis, une cellule de la plage étant sélectionnée, affichez la fenêtre "Sous-total" : menu Données > Sous-totaux. Renseignez cette fenêtre.

Les principales fonctions de synthèse sont : somme, nombre (le sous-total correspondant est le nombre de données), moyenne, max, min, produit, nb (le sous-total correspondant est le nombre de données numériques uniquement), ecartype et var.
Quand un sous-total est appliqué, il y a plan automatique : les cellules concernées par ce sous-total sont automatiquement groupées.
Chaque sous-total est affiché, ainsi que le total correspondant.

Exemple :

Quel est le nombre de voies desservies par facteur ?

• Triez les données selon la colonne Facteur, en renseignant la fenêtre "Trier".
  Pour l'afficher : menu Données > Trier ;
• Une cellule de la plage étant activée, affichez la fenêtre "Sous-total" : menu Données > Sous-totaux ;
• Renseignez cette fenêtre : "A chaque changement de" Facteur, "Utiliser la fonction" Nombre. "Ajouter un sous-total à" Voie desservie.
  Seule la case "Voie desservie" doit être cochée.

Quel est le nombre de lettres distribuées par facteur ?

On garde le tri précédent. Affichez directement la fenêtre "Sous-total".
Renseignez-la ainsi : " "A chaque changement de" Facteur, "Utiliser la fonction" Somme. "Ajouter un sous-total à" Lettres distribuées.
Seule la case "lettres distribuées" doit être cochée.

• La case "Remplacer les sous-totaux existants" étant cochée, les nouveaux sous-totaux remplaceront les précédents.
• Si la case "Saut de page entre les groupes" est cochée, il y aura changement de page après chaque sous-total.
• Si la case "Synthèse sous les données" n'est pas cochée, les sous-totaux s'afficheront au-dessus des lignes de détail.

Quel est le nombre de lettres distribuées par mode de transport utilisé ?

• Triez cette fois les données selon la colonne "Transport utilisé" ;
• Renseignez ainsi la fenêtre "Sous-total" : "A chaque changement de" Transport utilisé, "Utiliser la fonction" Somme. "Ajouter un sous-total à" Lettres distribuées.
  Seule la case "Lettres distribuées" doit être cochée.