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

EXCEL 07/ Chap 12 - TABLEAU, PLAN, SOUS-TOTAUX

1. TABLEAUX DE DONNEES

Création d'un tableau de données

Exemple :

exemple de tableau de données

Le tableau de l'exemple occupe la plage A3:D8. Il comporte les en-têtes Nom, Prénom, Ville et Date naissance.

On peut sélectionner une colonne de la liste en pointant sur la bordure supérieure de sa case d'en-tête, 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'en-tête).

Le tableau reçoit une mise en forme prédéfinie par Excel 2007.
L'intitulé de chaque colonne est doté d'un menu déroulant menu déroulant permettant de trier et de filtrer les données.
La dernière ligne est une ligne supplémentaire qui permet d'ajouter les données d'un nouvel enregistrement. A sa droite, une poignée de dimensionnement permet d'agrandir ou de réduire le tableau par cliqué-glissé.
Dès qu'on ajoute une donnée dans une cellule adjacente à la plage, après validation, sa ligne ou sa colonne est intégrée dans le tableau (sauf si la donnée est saisie juste au dessous d'une ligne de totaux).
Le tableau est actif dès qu'une cellule est sélectionnée.
Plusieurs tableaux peuvent être créés sur une même feuille.

Création

Pour créer un tableau de données : L'onglet "Création" des "Outils de tableau" s'affiche juste après création, puis dès que le tableau est actif (c'est-à-dire dès qu'une cellule est sélectionnée).

Le tableau créé comporte :

Après validation d'une donnée juste sous cette ligne, ou bien juste à droite du tableau, le tableau s'agrandit automatiquement et une balise s'affiche.

Commandes relatives au tableau

Les commandes spécifiques au tableau sont rassemblées au sein des différents groupes de l'onglet "Création" des "Outils de tableau".
Certaines sont accessibles après clic droit sur une cellule du tableau.

Précisions sur certaines fonctionnalités proposées :

•  Groupe "Propriétés"
"Redimensionner le tableau" : pour indiquer la nouvelle plage de cellules, il est rapide de la sélectionner.
Il est également possible de cliquer-glisser sur la poignée située sur l'angle inférieur droit du tableau.

•  Groupe "Outils"

•  Groupe "Données de tableau externe"
Les commandes de ce groupe concernent les tableaux publiés sur un serveur équipé de Microsoft Windows SharePoint Team Services.

•  Groupe "Styles de tableau"

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.
Après application d'un tri, une flèche apparait dans la case du menu déroulant de la colonne concernée. Dirigée vers le haut, elle indique un tri croissant ; vers le bas, un tri décroissant.
On peut effectuer un tri en fonction des valeurs, également en fonction de la couleur de cellule ou de police, ou de l'icône de la cellule.
En fonction des valeurs, un tri croissant peut être : de A à Z (textes), du plus petit au plus grand (nombres), du plus ancien au plus récent (dates).
Quel que soit l'ordre (croissant ou décroissant), les cellules vides sont placées en dernier.
En fonction des couleurs ou des icônes, on peut placer les cellules en haut ou en bas.

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, puis choisissez un tri par valeurs ou par couleurs.
Ou bien, affichez la fenêtre "Tri" (cf. paragraphe suivant).

Plusieurs clés de tri

Exemple avec trois niveaux : Le tableau étant actif, affichez la fenêtre "Tri" : activez sous l'onglet "Données" (onglet "Création" des "Outils de tableau"), le bouton "Trier".
Ou bien faites un clic droit sur la liste > Trier > Tri personnalisé.
La fenêtre "Tri" permet de classer les données selon plusieurs niveaux de critères successifs. Certains critères peuvent porter sur des valeurs, d'autres sur des couleurs ou des icônes.
On peut définir jusqu'à 64 niveaux de critères.

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 affichées peuvent être triées avant ou après filtrage.
Certaines combinaisons de critères ne peuvent pas être appliquées en mode "Filtre automatique". Elles requièrent un filtre avancé, qui permet de définir une zone de critères (voir plus loin).

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" : activez le tableau en cliquant dans l'une de ses cellules, puis sous l'onglet "Données", cliquez sur le bouton "Filtrer" (désactivez ce bouton pour sortir du mode "Filtre automatique").
Le filtre automatique s'applique en utilisant les menus déroulants menu déroulant des en-têtes de colonnes.
En fonction des données de la colonne, il est proposé un type de filtre : textuel, numérique ou chronologique. Si une cellule au moins se distingue par sa couleur, il est également proposé un filtre par couleur. Chaque type de filtre propose un menu.
Décocher des cases permet de masquer les enregistrements correspondants. Après avoir cliqué dans le cadre où sont listées les données de la colonne, taper l'initiale d'une donnée permet de la mettre en évidence, ce qui peut être utile lorsque la liste est longue.
Plusieurs filtres peuvent être appliqués sur une même colonne.
Des filtres peuvent être appliqués sur plusieurs colonnes, restreignant d'autant les données qui seront affichées.
L'application d'un filtre est indiquée par un symbole filtre  Filtre  dans la case d'en-tête de la colonne concernée.
Pour supprimer tous les filtres du tableau, et afficher ainsi toutes les données : sous l'onglet "Données", dans le groupe "Trier et filtrer", activez le bouton "Effacer".

Filtre avancé

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

On commence par définir la zone de critères, avant d'appliquer le filtre.

•  Définition du filtre

Il s'agit de définir la 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 :
Définition de la 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 :

tableau définissant la 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, un 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 le tableau de données (cliquez dessus).
Puis affichez la fenêtre "Filtre avancé" : sous l'onglet "Données", dans le groupe "Trier et filtrer", cliquez sur le bouton "Avancé". Position du tableau de données après filtrage :
Vous pouvez choisir : En cochant la case "Extraction sans doublon", on n'affichera que les lignes distinctes.

Exemple :
Après application du filtre avancé et avec l'option "Copier vers un autre emplacement" à partir de la cellule L1 (c'est-à-dire $L$1 en référence absolue), on obtient le nouveau tableau :

tableau après application du filtre avancé

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

On peut ajouter des colonnes, dotées de noms d'en-tête différents de ceux du tableau des données, et comportant des formules ayant pour résultat "Vrai" ou "Faux" (seuls pourront s'afficher les enregistrements ayant pour résultat "Vrai").

Exemple :
Si on veut connaître les habitants listés dans le tableau, qui sont nés en octobre ou en novembre, on établira la zone de critères suivante :

colonne de zone de critères avec formule
Des colonnes contenant des formules peuvent faire partie d'une zone de critères, au lieu, comme dans cet exemple, de constituer laseule zone de critères.
La référence D4 étant relative, la formule pourra être appliquée aux autres enregistrements.
Après application du filtre avancé et ayant choisi l'option "Copier vers un autre emplacement" à partir de la cellule $L$12, on obtient le nouveau tableau suivant :

tableau après filtrage

2. CONSTITUTION D'UN PLAN

Certaines plages de données gagnent à être organisées en plan.
Un plan permet d'avoir une vue d'ensemble en visualisant aisément les titres, et d'accéder rapidement aux données détaillées.
L'onglet "Données" contient le groupe "Plan" qui rassemble les commandes concernant le plan.
Un plan peut être en lignes (exemple ci-après) ou en colonnes, les principes restent 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 :

tableau

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.

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 cliquez sur le bouton "Grouper" (onglet "Données", groupe "Plan").
Groupez ainsi les deux lignes sélectionnées.
Un trait vertical relie alors ces données (marquées chacune d'un point), avec le symbole - à une extrémité.      regroupement dans un plan
Si vous cliquez sur ce signe moins, les données du groupe sont masquées. Le symbole + remplace alors le -.
Cliquez sur le signe plus pour afficher à nouveau les données du groupe.

Dans l'exemple, les numéros 1 et 2 apparaissent en haut à gauche, indiquant que le plan comporte maintenant deux niveaux.

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 en activant le bouton "Grouper", comme précédemment.
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é.

On obtient l'affichage ci-après : plan final

Cliquer sur un bouton contenant un numéro de niveau, permet d'afficher les lignes correspondant à ce niveau, ainsi que les lignes 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 :

plan - affichage sur deux niveaux

Pour afficher toutes les données

Activez le bouton "Afficher les détails".

Pour masquer toutes les données

Activez le bouton "Masquer".

Pour dissocier des données

Sélectionnez les lignes (ou colonnes) correspondantes, puis cliquez sur le bouton "Dissocier".

Pour supprimer un plan

Sélectionnez une cellule de la plage du plan, puis ouvrez le menu déroulant du bouton "Dissocier" > Effacer le plan.
La suppression de plan est définitive.

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 :

données synthétisées, avec la fonction Somme

Sélectionnez une cellule de la liste, puis activez le menu déroulant du bouton "Grouper" > Plan automatique.
Les lignes 14, 15 et 16 sont automatiquement groupées.

3. UTILISATION DE FONCTIONS DE SYNTHESE

On peut appliquer diverses fonctions de synthèse pour calculer des sous-totaux de données d'une plage de cellules.
Cette plage ne doit pas constituer un "tableau de données" (cf. § 1 "Tableaux de données").
Sinon convertissez d'abord ce tableau en plage normale : sous l'onglet "Création" des "Outils de tableau", dans le groupe "Outils", activez le bouton "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, Chiffres (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 constitution automatique d'un plan : les cellules concernées par ce sous-total sont automatiquement groupées. Cela explique la présence de la commande "Sous-total" dans le groupe Plan.
Chaque sous-total est affiché, ainsi que le total correspondant.

L'exemple est simple, des sous-totaux informatisés sont inutiles.
L'étude est menée pour la compréhension de l'application des fonctions de synthèse.

Quel est le nombre de voies desservies par facteur ?

On obtient le nombre de voies desservies par 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.