I. Introduction▲
Un tableau (fonctionnalité qui était intitulée "Liste" dans Excel2003) est utilisé pour
gérer et analyser vos bases de données.
Si vous êtes amené à stocker des informations dans une feuille de calcul,
les tableaux facilitent ensuite le classement et l'exploitation de ces données.
Vous pouvez grâce à cette fonctionnalité:
* Filtrer et trier les données des colonnes.
* Utiliser les références du tableau dans vos formules.
* Ajouter une ligne de totaux.
* Différencier facilement les lignes et les colonnes en appliquant
un style dans le tableau.
* Servir de base pour un tableau croisé dynamique ou un graphique.
* Ajouter facilement de nouveaux enregistrements et de nouvelles
colonnes (possibilité de redimensionnement automatique).
* Mettre à jour automatiquement les objets liés au tableau (formules,
TCD, graphiques ...).
* Supprimer les doublons.
* Ajouter en une seule fois des formules dans toutes les cellules
d'une colonne.
L'efficacité du tableau repose sur une base de données correctement structurée :
* Chaque cellule de la première ligne contient le nom des champs (en-tête).
* Le nom de chaque champ doit être le plus explicite possible.
* Les lignes suivantes contiennent les enregistrements.
* La base ne doit pas contenir deux noms de champs identiques.
* Evitez les cellules vides dans les champs qui stockent des données numériques.
* La base ne doit pas contenir de colonnes vides.
* La base ne doit pas contenir de lignes vides.
* Evitez des types de données différents dans une même colonne (texte,
date, numérique).
* La base doit stocker uniquement des données brutes
(pas de lignes de sous totaux, pas de sous ensembles ...). Nous verrons dans l'article que le tableau
gère directement les lignes de totaux dont vous pouvez avoir besoin.
II. Description▲
Pour créer un tableau dans Excel2007, après avoir défini et préparé la plage de cellules source:
Positionnez vous sur une des cellules qui contient les données. Excel identifiera toutes
les cellules contigües comme étant la source de données. Sinon, sélectionnez uniquement la partie des
cellules concernées par l'analyse, en incluant les en-têtes de colonnes.
Sélectionnez l'onglet "Insertion" dans le ruban, puis cliquez sur le bouton "Tableau" dans
le groupe "Tableaux".
La fenêtre suivante permet de reconfirmer l'emplacement des données source.
Si vous décochez l'option "Mon tableau contient des en-têtes", l'application ajoute une première
ligne dans le tableau et des noms de
champ s'affichent par défaut: Colonne1, Colonne2, Colonne3 ...
Cliquez sur le bouton OK pour valider.
Votre tableau est créé. Vous constatez que la table reçoit une mise en forme particulière
et des boutons sont accessibles dans l'entête de chaque colonne, afin de filtrer et trier les
données.
Remarque:
il n'est pas possible de définir une table à partir de cellules discontinues.
Il est ensuite facile d'ajouter un enregistrement ou une nouvelle colonne.
Un petit symbole bleu est visible dans l'angle inférieur droit du tableau.
Il permet de redimensionner le tableau manuellement. Passez le curseur de la souris dessus
jusqu'à faire apparaitre la flèche noire puis effectuez un glisser/Déposer afin de réduire ou
d'agrandir le tableau.
Quand un tableau est redimensionné, toutes les références à cette plage de cellules sont
automatiquement actualisées.
La modification de taille affecte automatiquement les objets (formules, tableaux croisés
dynamiques, graphiques ...) qui sont liés au tableau.
Les listes de validation sont automatiquement recopiées, ainsi que les
formules dans les colonnes calculées.
Pour ajouter une ligne sans avoir besoin de redimensionner manuellement la taille du tableau:
Lorsque vous êtes dans la dernière cellule de la dernière colonne (hors ligne de totaux), utilisez
la touche clavier "Tabulation". Un nouvel enregistrement est inséré dans le tableau. Il ne
vous reste plus qu'à compléter les informations dans la ligne.
Une autre solution pour ajouter un enregistrement (la ligne de totaux doit être en mode masqué):
Placez-vous dans la première cellule vide sous la première colonne du tableau. Saisissez une nouvelle
donnée et appuyez sur la touche "Entrée". Un nouvel enregistrement est intégré à la table.
Dans ce cas, la balise active d'options de correction automatique s'affiche dès que la ligne est
insérée.
L'option "Annuler le développement automatique du tableau" supprime l'ajout de la nouvelle
ligne dans le tableau. Si vous recliquez sur le bouton, l'option devient
"Répéter le développement automatique du tableau".
L'option "Arrêter le développement automatique des tableaux" empêche tout nouvel ajout
automatique dans le tableau.
L'option "Contrôler les options de correction automatique" permet d'afficher l'onglet
"Mise en forme automatique au cours de la frappe" de la boîte de dialogue
"Correction automatique".
L'option "Inclure de nouvelles lignes et colonnes dans le tableau automatiquement" définit
le développement automatique du tableau lorsque vous écrivez dans les cellules contigües au
tableau.
L'option "Formules de remplissage dans les colonnes pour créer des colonnes calculées" permet
l'insertion et la mise à jour automatique des formules dans toutes les cellules d'une colonne.
Si vous avez besoin d'afficher directement la fenêtre "Correction automatique", notamment
pour réactiver l'option d'intégration automatique des nouveaux enregistrements dans le tableau:
Cliquez sur le bouton "Office".
Cliquez sur le bouton "Options Excel".
Sélectionnez le menu "Vérification".
Cliquez sur le bouton "Options de correction automatique".
Sélectionnez l'onglet "Mise en forme automatique au cours de la frappe".
Appliquez vos options.
Cliquez sur le bouton OK pour valider.
L'ajout de nouvelles colonnes fonctionne sur le même principe que pour l'insertion de nouvelles
lignes.
Placez-vous dans la première cellule vide de la ligne d'en-tête, à droite de la dernière colonne du
tableau. Saisissez un nouveau nom de champ et appuyez sur la touche "Entrée".
La nouvelle colonne est intégrée à la table.
Une remarque pratique:
Lorsque vous utilisez la barre de défilement verticale pour visualiser
les derniers enregistrements d'une table,
les noms de champs s'affichent automatiquement dans les en-têtes de colonnes sans que vous ayez
besoin de figer les volets.
Dans ce cas, les boutons de tri et de filtrage ne sont plus accessibles.
Il existe deux autres méthodes pour créer un tableau:
* Sélectionnez une des cellules dans la plage de données puis utilisez le raccourci
clavier Ctrl + Maj + L.
* Sélectionnez une des cellules dans la plage de données source.
Sélectionnez l'onglet "Accueil" dans le ruban.
Cliquez sur le bouton "Mettre sous forme de tableau" dans le groupe "Style".
Choisissez un style dans la galerie.
Cliquez sur le bouton OK pour valider.
Remarque (informations issues de l'aide Excel):
Il n'est pas possible d'utiliser un tableau dans un classeur partagé. Une solution palliative
consiste à exporter le contenu du tableau sur un site SharePoint
afin que les autres utilisateurs puissent lire et modifier ces données depuis le serveur.
Vous pouvez ensuite synchroniser les données du tableau Excel avec les données partagées
dans SharePoint pour que ces données soient constamment actualisées.
Il existe un raccourci clavier pour sélectionner les données d'une ligne:
Sélectionnez une cellule du tableau.
Le raccourci Maj + barre d'espace sélectionne la ligne active dans le tableau.
Une deuxième utilisation de Maj + barre d'espace sélectionne la ligne complète dans
la feuille de calcul.
Il existe un raccourci clavier pour sélectionner les données d'une colonne:
Sélectionnez une cellule du tableau.
Le raccourci Ctrl + barre d'espace sélectionne les enregistrements de la colonne active.
Une deuxième utilisation de Ctrl + barre d'espace sélectionne la colonne complète du tableau.
Une troisième utilisation de Ctrl + barre d'espace sélectionne la colonne complète dans la
feuille de calcul.
Les noms de tableaux sont pris en compte dans le gestionnaire de noms (Onglet Formules/Groupe
Noms définis). En conséquence, un classeur ne peut pas contenir un tableau et une cellule
(ou plage) nommée qui seraient intitulés de la même manière.
II-A. Les références structurées dans les formules▲
II-A-1. Les références structurées▲
En sélectionnant des cellules dans un tableau, pour par exemple créer une formule,
vous constatez que la zone de sélection affiche les références sous la forme:
=Tableau1[[#Totaux];[Montant]]/Tableau1[[#Totaux];[NbHeures]]
Cette nouveauté Excel2007 est appelée Référence structurée.
Elle permet de faire facilement référence aux différentes parties du tableau et réduit les
risques d'erreurs dans vos feuilles de calcul car vous n'avez plus besoin de
modifier les formules lorsque des lignes et des colonnes sont ajoutées ou supprimées dans le
tableau.
Les références structurées de tableaux sont utilisables dans:
* Les formules.
* Les plages nommées.
* Les graphiques.
* Les tableaux croisés dynamiques.
Quand vous créez une formule, les références structurées peuvent être générées aisément en
sélectionnant les plages de cellules dans le tableau.
Les références structurées vont donc faire appel aux noms de champs (colonnes)
alors que dans les versions précédentes, Excel utilisait des références de cellules ou
des références nommées que vous deviez réadapter après chaque redimensionnement de votre base.
Il est bien entendu toujours possible de nommer des cellules
dynamiquement pour définir des plages variables mais cette nouveauté Excel2007 simplifie grandement le
travail.
Lorsque vous saisissez le nom du tableau dans une formule, suivi d'un crochet
ouvert =Tableau1[, Excel propose les arguments (spécificateurs) disponibles, dans
la liste de saisie semi automatique:
En plus des noms de colonnes, la liste contient des spécificateurs généraux:
[#Tout] fait référence au tableau complet (en-têtes et ligne de
totaux compris).
[#Données] fait référence aux données, sans les en-têtes ni
la ligne de totaux.
[#En-tête] fait référence aux données d'en-tête.
[#Totaux] fait référence à la ligne de totaux.
[#Cette ligne] fait référence à la même ligne que celle qui
contient la formule (est souvent utilisée dans les colonnes calculées).
Les références peuvent être schématisées ainsi:
Quelques exemples d'utilisation:
Pour faire référence à la colonne "NbHeures" complète:
Tableau1[[#Tout];[NbHeures]]
Pour faire référence à l'entête et aux données de la colonne "NbHeures":
Tableau1[[#En-têtes];[#Données];[NbHeures]]
Additionne les valeurs de la colonne "Montant":
=SOMME(Tableau1[[#Données];[Montant]])
Affiche la valeur maxi contenue dans la colonne "NbHeures":
=MAX(Tableau1[NbHeures])
Divise le résultat de la ligne de totaux "Montant" par le résultat de la ligne de
totaux "NbHeures":
=Tableau1[[#Totaux];[Montant]]/Tableau1[[#Totaux];[NbHeures]]
Chaque argument doit être placé entre crochets et séparé par des
points-virgules.
II-A-2. Les colonnes calculées▲
Si vous insérez la formule ci-dessous dans une cellule du tableau,
par exemple en F2, et que vous appuyez sur la touche "Entrée":
=Tableau1[[#Cette ligne];[NbHeures]]*Tableau1[[#Cette ligne];[CoutHoraire]]*Tableau1[[#Cette ligne];[Coefficient]]
La formule est automatiquement recopiée dans toutes les autres cellules de la colonne. De la même manière,
si une formule est modifiée dans le tableau, toutes les autres formules sont
également mises à jour dans la colonne.
Cette fonctionnalité est appelée Colonne calculée. Elle simplifie aussi la gestion de vos
tableaux. Vous écrivez une seule formule pour toute la colonne et n'avez pas besoin d'utiliser les
poignées de recopies. Les risques d'erreur sont donc limités.
Une balise active de correction automatique s'affiche ensuite dans la cellule et permet de gérer les
nouvelles colonnes calculées:
Le bouton "Annuler la colonne calculée" supprime les formules dans la colonne.
Le bouton "Arrêter la création automatique de colonnes calculées" empêche tout nouvel ajout
automatique de colonne calculée.
L'option "Contrôler les options de correction automatique" permet d'afficher l'onglet
"Mise en forme automatique au cours de la frappe" de la boîte de dialogue
"Correction automatique".
Remarques:
* Lorsque vous faites référence aux données d'une table dans une formule contenue dans le tableau,
vous n'avez pas besoin de préciser le nom de ce tableau.
* Les formules des colonnes calculées sont automatiquement dupliquées lorsque vous ajoutez un
enregistrement.
Une colonne calculée peut également contenir des formules différentes de la formule de colonne initiale.
Cette particularité est appelée Exception de colonne calculée.
Si votre projet nécessite de créer une exception de colonne calculée:
Saisissez votre formule spécifique dans une cellule de colonne calculée.
Appuyez sur la touche "Entrée" (Toutes les formules de la colonne sont ainsi mises à jour).
Puis cliquez sur le bouton "Annuler" dans la barre d'outils Accès rapide (La modification de toutes
les formules est annulée et la cellule a conservé votre nouvelle formule spécifique).
Vous noterez que la balise active de gestion des erreurs signale l'incohérence détectée
dans la colonne calculée.
II-B. Trier les données▲
Chaque colonne du tableau dispose d'une fonction de tri dans son en-tête, des menus déroulants
faisant apparaitre les options de classement.
L'application reconnait les types de données (texte, date, numérique) et permet de trier les
tableaux par ordre croissant, décroissant, ou de façon personnalisée.
Excel2007 permet d'utiliser 64 références de tri.
Pour masquer les boutons de tri et de filtre dans les en-têtes du tableau:
Sélectionnez une cellule du tableau.
Sélectionnez l'onglet "Accueil" dans le ruban.
Cliquez sur le bouton "Trier et filtrer" dans le groupe "Edition".
Décochez l'option "Filtrer" pour masquer les boutons.
Refaites la même opération pour réafficher les boutons de filtre et de tri.
II-C. Filtrer les données▲
Les tableaux disposent de filtres pour afficher uniquement les enregistrements qui répondent aux
critères que vous aurez spécifiés.
La fonction de filtre est accessible à partir du même menu déroulant que pour les tris.
L'application reconnait les types de données (texte, numérique, date) et adapte en conséquence
la liste des filtres prédéfinis (filtres textuels, filtres
numériques, filtres chronologiques).
Vous pouvez également appliquer des filtres personnalisés.
Les formules des lignes de totaux sont mises à jour automatiquement et ne prennent pas en compte
les enregistrements masqués suite à l'utilisation d'un filtre.
III. L'onglet 'Outils de tableau'▲
Un nouvel onglet "Outils de tableau/Création" est disponible dans le ruban lorsqu'une cellule du
tableau est sélectionnée. Vous avez ainsi accès à une série de fonctionnalités pour mettre en
forme et personnaliser vos tableaux.
III-A. Le groupe Propriétés▲
Le groupe "Propriétés" permet de lire et de modifier le nom du tableau actif. Par défaut, les
tables sont nommées Tableau1, Tableau2, Tableau3 ...
Le bouton "Redimensionner le tableau" affiche la boîte de dialogue pour
modifier la taille du tableau actif.
III-B. Le groupe Outils▲
Le bouton "Synthétiser avec un tableau croisé dynamique" utilise les données du tableau
pour créer un TCD.
Le bouton "Supprimer les doublons" supprime les enregistrements en double dans la base.
La boîte de dialogue permet de spécifier les paramètres pour la suppression des doublons:
* Précisez si la première ligne est un en-tête (Celle-ci ne sera pas prise en compte pour
la suppression).
* Par défaut, toutes les colonnes de la plage sont cochées. Cela signifie que la
recherche de doublon est effectuée sur des lignes complètes. Vous pouvez décocher
certains champs afin d'effectuer la requête sur une ou plusieurs colonnes spécifiques
du tableau.
Le bouton "Convertir en plage" permet de transformer le tableau en plage de cellules standard.
Toutes les données et la mise en forme sont conservées. Les boutons de filtre et de tri
disparaissent. Les formules sont figées et les références structurées sont remplacées par
des références de cellules classiques.
III-C. Le groupe Données de table externe▲
Une source de données externe peut être:
Un mappage XML.
Un tableau stocké sur un serveur SharePoint.
Le résultat d'une requête depuis une base de données (Access,
un autre classeur, un fichier txt, des données Web ... etc ...).
Le bouton "Exporter" exporte les données du tableau vers un serveur
SharePoint (pour partager les données et
permettre à d'autres utilisateurs de lire ou de modifier les informations).
Le bouton "Actualiser" gère la mise à jour du tableau.
Une option en bas du menu déroulant permet d'administrer les propriétés de connexion.
Le bouton "Ouvrir dans le navigateur" permet d'afficher la liste SharePoint dans une page
html.
Le bouton "Supprimer la liaison" rompt le lien entre le tableau et la source de données
externe.
III-D. Le groupe Options de style de tableau▲
L'option "Ligne d'en-tête" permet d'afficher ou de masquer la première ligne contenant
le nom des champs.
L'option "Ligne des totaux" d'ajouter ou de supprimer une ligne de totaux en bas du tableau.
L'option "Ligne à bandes" applique des couleurs de remplissages différentes sur les lignes
paires et impaires afin d'améliorer la lisibilité des lignes du tableau.
L'option "A la première colonne" applique une mise en forme particulière dans la première
colonne du tableau lorsque la case est cochée.
L'option "A la dernière colonne" applique une mise en forme particulière dans la dernière
colonne du tableau lorsque la case est cochée.
L'option "Colonne à bandes" applique des couleurs de remplissages différentes sur les colonnes
paires et impaires afin d'améliorer la lisibilité des colonnes du tableau.
III-E. Le groupe Style de tableau▲
Ce groupe propose une série de styles prédéfinis et personnels pour mettre en forme votre
tableau. Le passage de la souris sur les différents styles de la galerie permet de pré-visualiser
rapidement le résultat.
Deux options en bas de la fenêtre permettent de :
* Créer des styles personnalisés.
* Effacer le style appliqué au tableau actif.
IV. Les lignes de totaux▲
L'option "Ligne de totaux" est affichée dans la dernière ligne du tableau à la suite des
enregistrements et permet d'effectuer des calculs dans chaque colonne (Somme, Moyenne, Min,
Max, EcartType ...etc...).
L'intérêt de la ligne de totaux réside dans son actualisation automatique lorsque
vous ajoutez, masquez ou supprimez des lignes. Le style utilisé permet de mieux visualiser
cette dernière ligne particulière.
Il existe plusieurs solutions pour insérer une ligne de totaux:
* Insérer une formule de somme automatique en bas d'une colonne numérique.
* Clic droit dans le tableau/Table/Ligne des totaux.
* Sélectionner une cellule du tableau/Onglet "Création" dans le ruban/Cocher "Ligne des totaux"
dans le groupe "Options de style de tableau".
Par défaut, lorsque vous ajoutez une ligne de totaux, l'application insère le mot "Total" dans la
première colonne et applique une formule de somme dans la dernière colonne du tableau. Vous pouvez
bien entendu modifier ou supprimer ces paramètres affichés par défaut.
Ensuite, lorsque vous cliquez dans une cellule d'une ligne de totaux, Excel vous propose une
liste de fonctions à appliquer sur les données de la colonne:
Les formules prédéfinies sont basées sur la fonction SOUS.TOTAL.
Par exemple dans la cellule C11 du classeur démo qui contient la formule "Somme", vous pouvez
observer:
=SOUS.TOTAL(109;[NbHeures])
Si vous masquez puis réaffichez la ligne de totaux, les paramètres de calcul sont gardés en mémoire
et s'adaptent, même si vous ajoutez ou supprimez des enregistrements entre temps.
V. Les options du menu contextuel▲
Le menu contextuel est la fenêtre qui s'affiche lorsque vous effectuez un clic droit dans une
des cellules du tableau.
Actualiser:
met à jour les sources de données externes.
Insérer:
permet d'insérer une nouvelle colonne à gauche de la cellule active ou une nouvelle ligne
au dessus de la cellule active.
Supprimer:
permet de supprimer les lignes ou les colonnes actives.
Sélectionner:
permet de sélectionner des éléments du tableau à partir de la cellule active.
Trier:
permet d'appliquer un tri dans la colonne active.
Filtrer:
permet d'appliquer un filtre dans la colonne active.
Table:
*L'option "Ligne des totaux" permet d'afficher et de masquer la ligne de totaux.
*L'option "Convertir en plage" permet de transformer le tableau en plage de cellule standard
tout en conservant le format de style qui était appliqué au tableau.
VI. Liens▲
Les tableaux dans la FAQ Excel.
Synchroniser des tables Excel2007 avec des listes SharePoint.
Consultez la page d'aide.
VII. Remerciements▲
Je remercie toute l'équipe Office de DVP et particulièrement
Philippe Jochmans, pour la
relecture et la correction du tutoriel.
VIII. Téléchargement▲
Téléchargez le classeur démo qui a servi de support pour les captures d'écran et pour les exemples
de formules.