Les Tableaux

Cette page ne présente pas un truc et astuce mais, l'apport des tableaux est tel, qu'il mérite sa place ici.

On différencie deux grandes familles de tableaux dans Excel : les tableaux calculés, propres aux tableurs, et les listes de données qui s'apparentent aux tables des bases de données.

Dans la version 2003, Microsoft a ajouté une option au menu Données permettant de déclarer une liste en tant que telle mais l'essai a été transformé avec brio dans la version 2007.

Hormis la traduction inappropriée de Table, le Tableau offre 1000 et 1 avantages.

• Partie immergée de l'iceberg : le look
• Mettre sous forme de Tableau.
  Cette option de l'onglet Accueil propose diverses mises en forme basées sur les couleurs du thème actif.
  Le clic sur une des mises en forme affiche une boîte de dialogue proposant de mettre sous forme de tableau la plage de données automatiquement sélectionnée et signale la présence de la ligne d'en-têtes.
       
  Une fois la plage convertie en tableau, un nouvel onglet Outils de tableau apparait.
  A gauche du ruban figure le nom du tableau qu'il est conseillé de modifier pour l'utilisation ultérieure du tableau et, de l'autre côté, à gauche du groupe Styles de Tableau, sont offertes diverses options pour affiner le style choisi.
      
• Ce style de tableau s'applique un peu comme un calque sur la plage de données : si le style choisi comporte des lignes à bandes de couleur différente, en cas de tri, ces bandes restent insensibles au tri.
  Cependant si la liste de données comporte des couleurs, ces couleurs priment sur celles du style.
  Il est possible de créer ses propres styles de tableau. Cette page n'abordera pas le détail de ce paramétrage.
• La ligne de titre
• La ligne de titre a une importance capitale dans les tableaux.
• Si la cellule active fait partie du tableau, il suffit de descendre dans la liste avec la molette de la souris ou l'ascenseur, pour que les lettres des colonnes laissent place aux titres des colonnes du tableau : il n'est donc pas nécessaire de figer la première ligne pour conserver la ligne de titre.
• Plus important encore : si l'on clique sur le titre d'une colonne, lorsqu'il a ainsi pris la position d'une lettre de colonne, cela ne sélectionne ni la cellule de titre, ni la colonne complète de l'onglet, mais les données de la colonne du tableau, de la ligne située sous le titre à la dernière ligne du tableau.
  Sachant que la taille du tableau est automatiquement ajustée en cas d'ajout ou de suppression de ligne, l'avantage de cette sélection est de permettre une référence aux données d'une colonne indépendamment de l'adresse des cellules utilisées.
  Nous en reparlerons dans la partie formules.
• Dans certains cas, il arrive que le titre ne se substitue pas à la lettre de la colonne et on ne peut donc cliquer dessus pour sélectionner les données.
  La solution est alors de viser le haut de la cellule contenant le titre. En effet sur une hauteur d'environ 1 millimètre, juste sous la zone contenant la lettre de la colonne, on peut obtenir la sélection des données.
• Pour sélectionner les données de l'ensemble des colonnes, soit tout le tableau hors ligne d'en-têtes, il faut
  afficher la ligne d'en-têtes sous les lettres de colonnes et
  soit faire glisser le pointeur de souris de l'en-tête de la première colonne à l'en-tête de la derniere colonne du tableau,
  soit cliquer sur l'angle, en haut à gauche, de l'en-tête de la première colonne avec la flèche oblique
• A noter que les clics en tête d'une colonne ou avec la flèche oblique, s'ils sont répétés, ajoutent à la sélection la ligne d'en-tête et, si elle est active, la ligne des totaux (voir plus bas)
• Elasticité du tableau
• Que l'on ajoute des lignes ou des colonnes au tableau, celui-ci s'adapte automatiquement pour inclure ces lignes ou colonnes.
• Afin de conserver cette possibilité fondamentale, il convient en cas de suppression de ligne ou colonne, de supprimer physiquement celle-ci et non son contenu.
  Il est possible, en cas d'anomalie de replacer l'angle qui marque la fin du tableau à la position adéquate.
• Cette élasticité est un atout majeur : on utilisera avec profit les tableaux comme
    - source de Tableau croisé dynamique
    - source de liste de validation
    - plage de recherche pour les fonctions de recherche telles RECHERCHEV, EQUIV...
• Le nom du tableau
• Les noms donnés aux tableaux, sont stockés dans le classeur comme tous les autres noms définis par l'utilisateur.
  Ils sont visibles dans le gestionnaire de noms et apparaissent quand on commence à taper une formule et que la 1ère lettre tapée correspond au nom d'un tableau.
• Mais, car il y a un mais, et même plusieurs, on ne peut
  ni les lister au moyen de la touche F3,
  ni faire référence au nom d'un tableau ou d'une colonne d'un tableau dans une formule de validation.
  Dans ce dernier cas il faudra donner un nom à la colonne du tableau et c'est ce second nom qui sera utilisé dans la formule de validation.
• Les formules : une nouvelle syntaxe

• Comme dans une base de données, Excel se réfère au nom de la colonne et non à la cellule.
  A l’intérieur du tableau : la multiplication d’une cellule par une valeur donnera par exemple =[@Salaire]*10%
  et non une formule du type =F2*10%.
  A noter que, dès validation de la formule, celle-ci est automatiquement appliquée à toute la colonne, qu’elle ait été saisie en haut de la colonne ou ailleurs, que la colonne située à gauche soit ou non remplie.

• Une formule, externe au tableau, se référant au tableau complet, par exemple RECHERCHEV dans un tableau nommé ListeClients, aura cette syntaxe : =RECHERCHEV(J2;ListeClients;3;FAUX)

• Une formule utilisant une colonne du tableau par exemple =NBVAL(ListeClients[Nom]) ne comptera que les données hors en-tête de la colonne.
  Mais, dans le cas où l’on souhaite inclure la ligne d'en-tête, on utilisera
  pour le tableau complet ListeClients[#Tout]
  ou, pour la colonne Nom, ListeClients[[#Tout];[Nom]]. Exemple =NBVAL(ListeClients[[#Tout];[Nom]]).
  Enfin, pour se référer à la seule ligne d'en-tête,
     ListeClients[#En-têtes] pour l'ensemble du tableau
     ListeClients[[#En-têtes];[Nom]] pour la colonne Nom.

• Le fait d’utiliser le nom du tableau et les titres de colonnes
    - facilite la compréhension des formules,
    - évite la nécessité des références absolues,
    - permet le déplacement des colonnes du tableau,
    - et surtout tire parti de l’élasticité des tableaux.

• Il existe quelques cas où cette syntaxe peut être gênante dans la mesure où il n'est pas possible de se référer de façon absolue à une colonne par l'utilisation du symbole $.
  Exemple : un tableau de salaires, à gauche, dont on veut calculer les cotisations selon le tableau, de droite, spécifiant tranches et taux
  
  Il n'est pas possible de tirer la formule suivante :
  =SI([@Salaire]>T_Tranches[tranche A];MIN([@Salaire]-T_Tranches[tranche A];T_Tranches[tranche B]
  -T_Tranches[tranche A]);0)*T_Tranches[Taux B]
  de la colonne Cotisation Tranche B vers la colonne Cotisation Tranche C car la référence à la colonne Salaire, [@Salaire], ne peut être bloquée.
  Pour ces cas particuliers, on a le choix entre plusieurs solutions
    - retoucher la formule après copie,
    - utiliser l'adressage classique Excel,
    - utiliser la fonction INDIRECT : INDIRECT("Nom_du_tableau[@Salaire]") remplacera [@Salaire] dans la formule citée.
    - utiliser un adressage de type plage pour chaque colonne : Nom_du_tableau[@[Salaire]:[Salaire]] remplacera [@Salaire].
    - ou bien encore valider la formule comme une formule matricielle avec CTRL Shift Entrée.
  Dans nombre de cas, notamment si toutes les adresses doivent être absolues, on peut aussi recopier la formule dans les colonnes à droite en sélectionnant la 1ère formule et les colonnes cibles puis en utilisant le raccourci CTRL D (recopier à droite)
  
  
• Dernier point : dans les options de style de Tableau, évoquées plus haut, on trouve l'option Ligne des Totaux.
  Si cette option est cochée un ligne s'ajoute en pied de tableau avec une formule basée sur la fonction SOUS.TOTAL.
  Rappelons que cette fonction permet d'effectuer des calculs statistiques (Somme, Moyenne, Max, Min, etc.) sur une colonne en se référant aux seules lignes visibles, notamment en cas d'application d'un filtre.
  Lorsque cette ligne est présente, dans chaque colonne du tableau, une liste déroulante permet de choisir la fonction statistique.
  
  N.B : lorsque cette ligne est présente, elle gêne un peu pour l'ajout de données. On peut la désactiver temporairement (sans perdre les choix effectués pour les calculs), ou bien se placer dans la dernière cellule de données et utiliser la touche TAB pour insérer une ligne de données au-dessus de cette ligne de totaux.
• Les données liées
• Lorsqu'on lie des données depuis une base de données ou un fichier texte, Excel les place automatiquement dans un tableau. Toute formule juxtaposée au tableau de données liées, est intégrée automatiquement dans le tableau.
  Ainsi, lors de l'actualisation des données, la colonne de formules s'ajuste au nombre de lignes de données.
• Tour de passe passe pour mise en forme
• Dans les versions antérieures à 2007, Excel proposait des mises en forme automatiques.
  Cette fonctionnalité, bien pratique pour les urgences, a disparu.
  Mettre sous forme de tableau une plage de cellules, puis la reconvertir aussitôt en plage, (onglet Outils de Tableau) permet de bénéficier d'une mise en forme instantanée.
• Remarques
• A retenir : pour un fonctionnement optimum un Tableau doit
  - ne contenir aucune ligne entièrement vide
  - avoir des formules homogènes de la la 1ère ligne à la dernière ligne
  
• Les exemples de formules sont basées sur la version 2010 d'Excel : la syntaxe de la version 2007 est un peu plus lourde.
• Certaines des options citées peuvent être désactivées : recopie automatique des formules dans les colonnes de tableaux, syntaxe spécifique au tableaux mais, ces options nous paraissant intéressantes, nous ne nous attarderons pas sur cette possibilité.
• Depuis la version 2013 d'Excel, grâce à l'intégration de PowerPivot, il est possible d'établir entre les tableaux, comme dans une base de données, des relations de 1 à N.
  Cela peut éviter les formules à base de RECHERCHEV lorsque l'objectif est l'exploitation du tableau par un Tableau Croisé Dynamique.
• Depuis le version 2016 d'Excel, grâce à l'intégration de PowerQuery, on peut consolider ou croiser les tableaux structurés de façon simple pour les exploiter.
• En VBA le tableau est un Listobject qui se manipule différement d'une plage de cellules.

Trucs et astuces

Copyright Archétypes - Informations légales