Présentation structurée de tables de données Excel

S'applique à quoi ?

Une table de données 1 se reconnait à quelques caractéristiques clés:

  • Une ligne de titre renseigne le nom de chaque colonne
  • Le nombre de lignes peut être variable dans le temps
  • L'ordre des colonnes et l'ordre des lignes n'ont pas de signification particulière
  • Les données à l'intérieur d'une colonne sont d'un même type: Chaîne, Date, Nombre, Montant, Enuméré, ...
  • En colonne beaucoup de données sont répétées d'une ligne à l'autre
  • On utilise des filtres et des tris pour la consulter ou la mettre à jour


double clic

Les tableaux qui sont déjà des présentations de données sont moins propices à être améliorés par l'extension. Ils se reconnaissent à:

  • Un nombre le plus souvent fixe de lignes
  • Chaque ligne possède un titre
  • L'ordre des colonnes (ou des lignes) est important
  • Contiennent parfois des sommes/calculs intermédiaires

Quel bénéfice ?

Ce que permet l'extension peTable:

  • Tri facile par double clic (critères imbriqués)
  • Mise en forme automatique qui met visuellement en facteur les valeurs répétées
  • Format persistant réactif aux modifications de données même sans l'extension
  • Utilisation du tri naturel pour les noms contenant des nombres (Chap2 < Chap13 < Chap111)

Installation

  1. télécharger peTable.xlam dans un dossier permanent.
  2. ouvrir Excel et ajouter la macro complémentaire (Add-In):

    • 2007-2010-2013: Fichier/Options/Compléments/Compléments Excel/Atteindre... puis Parcourir
    • 2011 (Mac): Outils/Macros complémentaires..., puis Sélectionner
  3. désigner peTable.xlam

La désinstallation s'effectue en décliquant le complément dans la liste des extensions actives.
Il est conseillé de télécharger peTableDemo.xlsx pour expérimenter plus facilement les capacités de cette extension.

Mettre les données en Table

En général les données dans Excel vivent dans une banale plage de données ($A$2:$T$28 par exemple): Il faut les convertir en Table (Tableau) Excel pour que les fonctions s'activent. Réceptacle trop méconnu, introduit en Excel 2007, qui offre des bénéfices substantiels: un nom pour la Table indépendant de sa position, extension automatique de la taille, contrôle du style/format global, références nommées par [Table][Colonne] dans les formules, propagation uniforme des formules par colonnes, ajout de lignes de totalisation des colonnes (somme, min, max, ...), cohabitation de plusieurs filtres dans la même feuille, ...

  1. sélectionner le rectangle de données avec obligatoirement la ligne de titre/en-têtes.
  2. Insertion/Table ou Tableau convertit la plage en table. Raccourcis ctrl-L (PC) ou ctrl-T (Mac).



je recommande vivement de choisir un style de table plus sobre que celui par défaut: Dans le ruban Tableau décliquer Lignes à bandes (ainsi que Colonnes à bandes) et sélectionner de préférence une des premières variantes sans bordure entre chaque ligne.

Mode d'emploi

L'extension rend toutes les tables des classeurs Excel ouverts réactives au double clic:


double clics

  • double clic sur titre de colonne: initialise le tri de la table selon l'ordre de la colonne (croissant/décroissant en alternance). Les valeurs répétées de la colonne sont visuellement mises en facteur. Le début et la fin d'une série sont encadrés d'une bordure horizontale.
  • double clic dans corps de colonne: ajoute un tri secondaire à la hiérarchie actuelle de mise en forme et/ou alterne un ordre de tri précédent. Le tri secondaire s'effectue à l'intérieur des valeurs répétées sans modifier l'ordre global précédent.
  • double clic sur un titre de colonne réinitialise un nouveau cycle de tri.

    double clics

La cerise sur le tableau: Déplacer les colonnes

Excel offre une pépite enfouie qui permet de changer l'ordre des colonnes par glisser/déplacer.

  1. sélectionner une colonne: Mettre le curseur légèrement en dessous de la bordure supérieure du titre afin qu'il devienne ou . Sans bouger, un premier clic sélectionne la colonne sans le titre, un deuxième clic sélectionne toute la colonne. Et alterne. On veut la colonne avec son titre.
  2. prendre la colonne: Mettre le curseur exactement sur la bordure supérieure du titre pour qu'il devienne ou . Appuyer: clic et maintenir appuyé.
  3. déplacer horizontalement la colonne. Le curseur devient un I majuscule pointillé aussi haut que la colonne. Attention à ne pas sortir de la table, ni à monter ou descendre de niveau (le curseur se transforme alors en rectangle). Il s'agit d'insérer la colonne avant ou après une colonne existante.
  4. relâcher le bouton pour indiquer la position finale du déplacement.

Si un message demandant confirmation de remplacement de cellule apparait ou qu'une nouvelle colonne est créée, c'est que la manipulation a échouée. Défaire et recommencer. C'est plus long à expliquer que de le montrer.


Cela parait simple normalement dès le deuxième succès.

Cette modification ne modifie pas la table mais permet de la présenter pour faire apparaitre une hiérarchie des attributs.

Bénéfices

Non intrusif

Les données ne sont aucunement affectées. Il s'agit d'une simple mise en forme à usage du lecteur humain. Le texte est rendu invisible en le colorant comme le fond de la cellule. La table formatée reste une source valide pour un tableau croisé dynamique par exemple. Ou à l'inverse être le résultat d'une requête à une base de données externe.

Présenter les réponses qui répondent aux questions intéressantes

Le tri par colonne permet de répondre à des questions ponctuelles: Qui est le plus grand, le plus petit, ... Mais aussi d'organiser globalement les données pour comprendre et faire comprendre leurs relations.

Persistance de la mise en forme

Le classeur qui contient des tables formatées grâce à l'extension peut être transmis à des utilisateurs qui n'ont pas installé peTable. Elle perd bien sûr les tris imbriqués par colonne au double clic mais conserve son format réactif qui masque les données répétées, y compris par ajout ou suppression de lignes, tri global et même après une conversion en plage de cellules.

Des exemples

Tables plus claires

Il est facile de transformer de vilaines tables surchargées en des versions plus digestes.
Le régime amaigrissant est le suivant:

  1. Alignements: Textes à gauche, Nombres à droite
  2. Elimination: des bordures, des bandes et des remplissages colorés de cellules
  3. Largeurs ajustées au contenu par sélection des colonnes et Format/Colonne/Ajustement automatique.
  4. Factorisation des éléments répétés par double clic sur les colonnes les plus structurantes

Chapitres d'un document

Les premiers chapitres de la norme ISO 27001 triés alphabétiquement par Excel plongent généralement l'utilisateur dans des doutes profonds sur l'informatique. Le tri naturel permet de retrouver un peu d'humanité.

Tracabilité

Soit l'analyse fonctionnelle d'un modeste vélo. A partir de la même table de données à deux colonnes, il est simple de présenter visuellement la relation "Quels éléments supportent une fonction ?" et la relation inverse "Quelles fonctions utilisent cet élément ?"

Analyse des risques

Il s'agit généralement de tables qui ne contiennent que du texte: des descriptions et des échelles pour graduer la probabilité et la gravité.

Gestion de projets

Avec un peu d'astuce une formule qui répète un caractère permet de faire des diagrammes de Gantt réactifs tout à fait honorables.


Le tri de la table permet de présenter les données selon les différents points de vues utiles: par Client, par date de début, nature de tâche, reste à faire, ...

Carte de restaurant

Des relations contenus/contenants avec des groupements par catégories.

Règles de codage

Une arborescence de répertoire et fichiers croisée avec des règles de codage elles mêmes caractérisées en niveaux (Required, Advisory) et des comptages.

Résultat de Football

Une grande liste de résultats à plat se transforme en une table capable de répondre à des questions qui combinent plusieurs champs: "Quel est le pays qui a marqué le plus lors du premier tour depuis 1960 ?"


FAQ

  • Q: Je ne comprend pas bien le bénéfice cette extension, le tri et les formats peuvent être facilement appliqués manuellement ?
  • R: Le format est appliqué automatiquement sans effort à chaque double clic et prend en compte les ajouts, suppressions et modifications de la table. Le tri par colonnes imbriquées serait très laborieux à réaliser avec les fonctions natives. Le tri naturel très utile dans beaucoup de cas est totalement inconnu d'Excel.

  • Q: L'extension a formaté la table, mais comment puis je retrouver les données à plat comme à l'origine ?
  • R: Il suffit d'effacer les formats conditionnels qui créé la représentation structurée: sélectionner la table ou les colonnes à dé-formater et Mise en forme conditionnelle/Effacer les règles des cellules sélectionnées.

  • Q: Il y a un problème de format sur la table. Couleur non homogène, bordure manquante ou en trop, ...
  • R: Excel gère 3 couches successives de formats. Chacune venant masquer celles en dessous (ou pas selon la transparence des couleurs). De la plus basse à la plus haute.
    1. le style du tableau (police, remplissage, bordure)
    2. le format/style de la cellule (police, remplissage, bordure)
    3. le format conditionnel que l'extension utilise pour moduler l'apparence de la table.

Cela concerne la couleur du remplissage de la cellule ainsi que les éventuelles bordures. La transparence d'une couche laisse voir ce qu'il y a dessous.
L'extension suppose que la coloration des cellules est uniforme, elle modifie les formats conditionnels pour la couleur de la police soit la même que le fond et devienne ainsi invisible.

Il arrive que les couleurs se marchent dessus dans un ordre non souhaité. En cas de problème il faut:

  1. éliminer/uniformiser les formats fixes des cellules. Sélectionner la table/Format/Cellule/Remplissage, Bordure,...
  2. effacer les formats conditionnels: sélectionner la table et Mise en forme conditionnelle/Effacer les règles des cellules sélectionnées
  3. ajuster éventuellement le choix du style de Tables/Tableau

Et relancer un tri par double clic.


  • Q: Comment imposer un format conditionnel (Intensité de rouge par exemple ou barre de données) sur une colonne ? A chaque fois que j'ajoute un format conditionnel un tri provoque l'effacement du format.
  • R: Il faut procéder en deux temps:
    1. éliminer tous les formats conditionnels présents dans la colonne: Sélectionner la colonne et Mise en forme conditionnelle/Effacer les règles des cellules sélectionnées,
    2. appliquer le format que l'on souhaite. L'extension n'ajoute pas son format si la place est déjà occupée par un autre.

  • Q: L'installation a été faite mais il n'y a aucune réaction au double clic ou des messages d'erreur VBA apparaissent.
  • R: Vérifier que les données sont dans une Table/Tableau avec une ligne de titre. Si d'autres extensions Excel sont actives essayer de les désactiver provisoirement. Il arrive parfois que deux extensions ne soient pas compatibles entre elles. Sortir complétement et relancer Excel peut également résoudre certains problèmes.

  • Q: J'avais une plage de données qui était formatée manuellement avec des cellules fusionnées, la conversion en Table/Tableau et le tri ont détruits la cohérence de l'ordre des lignes.
  • R: Les cellules fusionnées sont un raccourci pour mettre en forme une zone rectangulaire comme si elle ne contenait que la valeur de la cellule la plus en haut à gauche. Mais lorsque la zone est défusionnée (comme lors de la conversion en Table/Tableau), les autres cellules sont initialisées à vide. Avant de lancer un tri il faut donc impérativement remplir les cellules vides par la valeur globale. En colonne, le glisser/déplacer est assez efficace.

La fusion de cellules n'est pas qu'une mise en forme. Il est prudent de défusionner les cellules manuellement avant la conversion en table et de les remplir de la même valeur.


  • Q: J'ajoute des lignes à une table existante mais les bordures ne se placent pas comme je le souhaite et le tri du tableau provoque l'éparpillement de ces nouvelles lignes.
  • R: Le masquage des valeurs répétées donne l'impression que certains champs sont vides, et qu'une nouvelle ligne ne doit être renseignée que dans les champs utiles. C'est faux, toutes les colonnes y compris les valeurs répétées doivent être remplies (éventuellement par copier/coller ou glisser/coller), pour que la table soit cohérente.

  • Q: Le double clic efface les filtres des colonnes.
  • R: C'est le comportement normal de la version actuelle de l'extension. Il faut réactiver le filtre après chaque tri.

  • Q: L'utilisation des filtres de colonnes fait que certains champs sont invisibles alors qu'ils ne devraient pas. La lecture de la Table est complétement faussée.
  • R: Si le cas se produit il est faut effacer les formats conditionnels qui masquent les champs de la colonne filtrée. Sélectionner la colonne et Mise en forme conditionnelle/Effacer les règles des cellules sélectionnées.

  • Q: Les tableaux croisés dynamiques permettent des présentations similaires, quelle est la différence ?
  • R: L'extension agit sur la présentation de la table de données, qui peut elle même être synthétisée par des tableaux croisés dynamiques qui sont capables de faire des consolidations numériques complexes. Rien de contradictoires avec les présentations structurées par double clic, le format réactif à la modification directe des données, ou l'ordre naturel de la table source des données.

  • Q: It doesn't work at all on my non-french version of Excel.
  • A: This is a known limitation of the current version. Conditional formatting uses localized translation of formulaes. I may release an US/UK version in a near future.

  1. Le terme table en Excel français est utilisé de façon très incohérente par Microsoft lui même et ce depuis longtemps. Je choisi d'utiliser les mots les plus intuitifs sans me préoccuper des télescospages éventuels mais en précisant et donnant des exemples. Table de données par exemple est aussi le nom français de la fonction qui permet de faire varier une formule selon 2 paramètres. (accessible par Données/Table de données_, et qui n'a absolument RIEN à voir avec le sujet développé ici). La terminologie anglaise est plus cohérente._