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

Exemple :
exemple de 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

Pour créer une liste de données : La liste créée comporte : La barre d'outils "Liste" s'affiche à la création, puis dès que la liste est active, c'est-à-dire dès qu'une cellule de la liste est sélectionnée.

Commandes relatives à la liste

Elles sont accessibles par diverses méthodes :

Certaines commandes ne sont accessibles que par certaines méthodes.

Parmi les commandes proposées :

Trier les données

On peut trier les données en utilisant un ou plusieurs critères, appelés aussi "clés", chacun correspondant à un en-tête de colonne.

Une seule clé de tri

Par exemple : tri par ordre alphabétique des noms.
Cliquez sur le menu déroulant de l'en-tête de colonne dont les données sont à trier > Tri croissant ou Tri décroissant.

Deux ou trois clés de tri

Exemple avec trois clés : La liste étant active, faites : menu Données > Trier (ou bien clic droit sur la liste > Trier).
La fenêtre "Trier" permet d'effectuer des tris jusqu'à trois en-têtes de colonnes différents. Choisissez les options souhaitées.
Les cellules vides sont placées en dernier.

Filtrer les données

Filtrer des données permet de ne laisser affichées que celles qui répondent à des critères définis. Les autres données demeurent, elles sont juste masquées.
Les données qui restent affichées peuvent être triées.
Certaines combinaisons de critères ne peuvent pas être appliquées en mode "Filtre automatique". Elles requièrent un filtre élaboré, qui permet de définir une zone de critères.

Filtre automatique

Le filtre automatique est par défaut activé, d'où la présence d'un menu déroulant dans la case d'en-tête de chaque colonne.
Sinon, pour passer en mode "Filtre automatique" : après avoir activé la liste, faites : menu Données > Filtrer > Filtre automatique.
Le filtre automatique s'applique en utilisant les menus déroulants des en-têtes de colonnes.
En plus des valeurs de la colonne, chaque menu contient les trois options suivantes : Pour afficher à nouveau toutes les données : menu Données > Filtrer > Afficher tout. Pour quitter le mode "Filtre automatique" : menu Données > Filtrer > Décochez l'option "Filtre automatique".

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

Il s'agit de définir une zone de critères.
Il s'agit d'une plage de cellules dont :

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 :

exemple de zone de critères

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

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

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 du filtre élaboré

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 :

exemple de plan

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

Sélectionnez les lignes (ou les colonnes), puis faites : menu Données > Grouper et créer un plan > Dissocier.

Pour supprimer un plan

Sélectionnez une cellule de la plage du plan, puis faites : menu Données > Grouper et créer un plan > Effacer le plan.
Cette opération n'est pas annulable.

Plan automatique

Un plan automatique peut être créé si des données ont été synthétisées par exemple avec une formule contenant la fonction Somme.

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

On peut appliquer des fonctions de synthèses pour calculer des sous-totaux de données d'une plage de cellules.
Cette plage ne doit pas constituer une liste de données. Sinon convertissez d'abord cette liste en plage normale : menu Données > Liste > Convertir en plage.

Application d'une fonction de synthèse

Pour appliquer une fonction de synthèse :

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 ?

L'exemple est simple, des sous-totaux seraient inutiles, mais l'étude est menée pour la compréhension de l'application des fonctions de synthèse. On obtient le nombre de voies desservie pour chaque facteur (3 Nombre Claude, 2 Nombre Jean et 2 Nombre Pierre), ainsi que le nombre de voies totales (7 Nbval).

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.

On obtient le nombre de lettres distribuées pour chaque facteur (Total Claude 9000, Total Jean 15000 et Total Pierre 10000), ainsi que le Total général 34000).

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

On obtient le nombre de lettres distribuées par mode de transport utilisé (9000 Total à pied, 25000 Total vélo), ainsi que 34000 Total général.