EXCEL 07/ Chap 12 - TABLEAU, PLAN, SOUS-TOTAUX
1. TABLEAUX DE DONNEES
Création d'un tableau de données
Exemple :
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 
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 :
- Sélectionnez une plage de cellules, vide ou contenant déjà des données.
- Puis :
- Soit : sous l'onglet "Insertion", activez le bouton "Tableau".
Le tableau reçoit la mise en forme prédéfinie par Excel 2007.
- Soit vous souhaitez choisir un style applicable dès sa création : sous l'onglet "Accueil", dans le groupe "Style", activez le bouton "Mettre sous forme de tableau", et choisissez un style.
- La fenêtre "Créer un tableau" s'affiche. Renseignez-la si nécessaire, puis validez.
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 :
- Une mise en forme, qui le distingue des autres données de la feuille de calcul (sauf s'il a été choisi "Aucune" comme Style de tableau).
- 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, terminée par une poignée de dimensionnement.
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.
- "Annuler le développement automatique du tableau" : la nouvelle ligne (ou colonne) ne sera pas intégrée au tableau.
- "Arrêter le développement automatique du tableau" : toute nouvelle donnée adjacente au tableau n'en fera pas partie.
Pour rétablir le développement automatique du tableau, affichez la fenêtre "Correction automatique" : activez le bouton Office > Vérification > Options de correction automatique. Au premier onglet de la fenêtre, cochez la case "Inclure de nouvelles lignes et colonnes dans le tableau".
- La dernière option "Contrôler les options de correction automatique" affiche cette fenêtre "Correction automatique".
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"
- "Supprimer les doublons" : les lignes en double n'apparaissent qu'une fois. La fenêtre "Supprimer les doublons" permet de spécifier les doublons ne portant que sur certaines colonnes.
Dans l'exemple, deux personnes habitent Caen. Si, dans cette fenêtre, on ne laisse cochée que la colonne Ville, la deuxième personne habitant Caen sera masquée.
- "Convertir en plage" : la liste est transformée en plage "normale". Si elle est présente, la ligne "Total" demeure.
- "Synthétiser avec un tableau croisé dynamique" : le tableau croisé dynamique fait l'objet du chapitre 14 - LES TABLEAUX CROISES DYNAMIQUES.
• 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"
- Options de styles : cochez les cases des lignes ou/et des colonnes que vous souhaitez mettre en valeur. Vous pouvez n'en cocher aucune, les cocher toutes, ou en cocher plusieurs.
En cliquant sur les flèches à droite de la galerie, vous faites défiler les nombreux styles proposés. Pour modifier la taille de la galerie, pointez sur la bordure inférieure ; quand le pointeur a la forme d'une double-flèche, cliquez-glissez.
Quand le pointeur passe sur un style, on peut visualiser son effet sur le tableau, et une info-bulle indique les caractéristiques (trame, accent) du style.
- L'option "Nouveau style de tableau" proposée en bas de la galerie, permet de créer un nouveau style.
- L'option "Effacer" supprime la mise en forme du tableau actif.
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 :
- Tri par ordre alphabétique des noms.
- En prévision de noms identiques, ajout du tri par ordre alphabétique des prénoms.
- En prévision de noms et prénoms identiques, ajout du tri par ordre alphabétique des villes.
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 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 
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 :
- La première ligne contient des en-têtes du tableau 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 précisé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 :
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 :
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é".
- "Plages" : dans la zone de saisie, sont affichées les références absolues de la plage du tableau 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 d'abord les références inscrites), puis sélectionnez la plage de la zone de critères.
(Dans l'exemple, sélectionnez la plage $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 "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'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 :
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 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 :
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 :
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 :
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é. 
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 : 
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 :
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 :
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 :
- Si nécessaire, commencez par trier les données en fonction du sous-total à calculer.
Pour afficher la fenêtre "Tri" : sous l'onglet "Données", activez le bouton "Tri".
- Puis, une cellule de la plage étant sélectionnée, affichez la fenêtre "Sous-total" : sous l'onglet "Données", dans le groupe "Plan", activez le bouton "Sous-total". 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, 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 ?
- Triez les données selon la colonne Facteur, en renseignant la fenêtre "Tri". Pour l'afficher : sous l'onglet "Données", activez le bouton "Tri".
- Une cellule de la plage étant activée, affichez la fenêtre "Sous-total" : sous l'onglet "Données", dans le groupe "Plan", cliquez sur le bouton "Sous-total".
- 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.
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.
- 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.
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é ?
- 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.
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.
Cours BARDON - Microsoft Office
Chapitre précédent
Chapitre suivant
