Mettre sous forme de tableau, une bonne idée ?

Il existe dans les dernières versions d'Excel une fonctionnalité bien pratique accessible par le menu "Mettre sous forme de tableau" de l’onglet « Accueil ».

Excel demande où se trouve la plage à convertir en tableau.

Pour la majorité des utilisateurs, l'intérêt c'est surtout le formatage des tableaux sous forme de bandes de couleurs alternées.

Un autre avantage que découvre l'utilisateur à l'usage c'est la mise à jour automatique des formules de calcul qui évite de faire une recopie incrémentale et le formatage du tableau automatique que ce soit lors de l'ajout de lignes ou colonnes.

Microsoft Excel permet aussi de rajouter une ligne de totaux au bas du tableau comme des sommes, compter le nombre de valeurs ou une moyenne, etc.

De plus depuis la version d'Excel 2013, il est possible de rajouter des analyses à l'aide du bouton "Analyse rapide" qui s'affiche quand le curseur est situé dans le tableau.

Un comportement perturbant

Peut-être que je m'y prends mal, mais parfois je trouve le comportement très déstabilisant, car sans raisons apparentes, pour une même action, selon le tableau le résultat n'est pas toujours le même.

Par exemple, si j'ai déjà formaté un tableau, Excel n'applique pas toujours la mise en forme que je choisi.

Le cas des formules de calcul

Les formules de calcul prennent une apparence bien différente de l'habitude.

Au lieu d'utiliser des adresses de cellules, Excel se sert des étiquettes de colonnes.

Ainsi la formule :

• = G10 * I10

Devient :

• =[@[ARTICLE_QTE]]*[@[ARTICLE_PU]]

Là aussi sans faire quoi que ce soit de particulier, parfois il ne respecte pas ce fonctionnement.

Il semble que ce phénomène se produit quand c'est un classeur au format 2003 ".XLS" qui ont été converti en fichier ".XLSX".

Les formules existantes restent inchangées.

Rajout de bordures

Avec certains modèles, une bordure externe serait un plus.

Rajout manuel de bordures

Si on rajoute une bordure manuellement, lors de l'ajout d'une ligne, comme avec une plage, il faut refaire les bordures.

Mais ce n'est pas vrai dans tous les cas et parfois quand je rajoute une ligne, il positionne bien la bordure externe sur la dernière ligne.

Tableau personnalisé

Pour éviter toute surprise, le mieux c'est de créer un tableau personnalisé en choisissant l'option "Nouveau style de tableau" située en bas de la liste des modèles.

Dans la liste choisir l'entrée "Tableau entier" et cliquer sur le bouton "Format".

Quelques inconvénients

Noms de colonnes partiellement supporté

Validation de données

Quand j'autorise une liste pour une validation de données, Excel ne comprend pas les formules avec les noms de colonnes.

Il faut donner les adresses de la plage sous forme classique.

Du coup quand la plage s'étend, la validation de données ne reflète pas la nouvelle plage et il faut appliquer d'anciennes techniques lors de l'ajout de données.

Mises en forme conditionnelles (MFC)

Il en est de même avec des Mises en forme conditionnelles basées sur des formules.

Le cas des macros

Remarquez que l'enregistreur de macros comprend bien les noms des colonnes :

• Range("TBL_CDE[CLIENT_ID]").Select

Seulement lors de l'ajout de ligne, il reprend un fonctionnement basé sur des adresses ce qui oblige comme par le passé à déterminer la dernière ligne du tableau.

• ActiveSheet.ListObjects("TBL_COMMANDE").Resize Range("$C$9:$P$23")

Nom de tableau

Les tableaux sont nommés [Tableau1], [Tableau2], [Tableau3], etc.

Comme un classique nom de cellule, il faut modifier son nom sur le bouton « Gestionnaire de noms » de l’onglet « Formules ».

Nom de colonnes versus nom de cellules

Contrairement au nom de tableaux, les noms de colonnes créés par Microsoft Excel sont masqués. C’est-à-dire qu’ils n’apparaissent pas dans le « Gestionnaire de noms ».

Microsoft Excel a généré le nom [ARTICLE_PU] pour la colonne contenant le prix unitaire de l’article.

Imaginons que je créé un nom pour cette plage (par exemple [ART_PU] et que je l’utilise pour calculer le hors-taxes au lieu du nom donné par Excel.

Excel applique le calcul à l’ensemble de la plage, j’ai donc bien la formule : =[@[ARTICLE_QTE]]*ART_PU.

Pour éviter toutes confusions, j’appellerais les noms issus d’Excel des noms de colonnes pour les différencier des noms de cellules classiques.

Ligne de totaux

Il est possible de rajouter une ligne de totaux.

L’utilisateur a le choix entre plusieurs calculs.

C'est à mon avis le gros point noir de la solution.

Il est inacceptable qu'elle soit collée aux données et cela va à l'encontre de toutes les règles de gestion des données.

Publipostage

Si la ligne de total existe, un courrier est généré.

Si on supprime la ligne du total, un courrier avec des champs vides est créé.

Il faut donc enlever la ligne des totaux avant de créer le publipostage ou refermer le publipostage, enlever les totaux et ré ouvrir Word.

Exportation au format CSV ou autre

C'est le même problème, la ligne de total est exportée.

De plus Excel inscrit la mention "Total" sur la première colonne du tableau.

Et si je veux réaliser un calcul sur cette colonne ?

Copie de tableau

Quand on copie un tableau, il est converti en plage.

Il conserve la mise forme choisie.

Mais il conserve aussi les formules de calcul avec les noms de colonnes en rajoutant le nom du tableau en préfixe.

=TBL_COMMANDE[@[ARTICLE_QTE]]*TBL_COMMANDE[@[ARTICLE_PU]]

Or ces noms ne correspondent à rien pour le nouveau tableau et même si cette plage est mise sous forme de tableau, il conserve le nom du tableau d’origine.

Fonctionnalités désactivées

Certaines fonctions du logiciel deviennent tout simplement inaccessibles. Par bonheur, il se trouve que ce sont des menus que l'utilisateur ne connait généralement pas et donc qui sont souvent inusités.

Affichage personnalisés

Pour les personnes qui masquent souvent des colonnes, ce menu devrait être indispensable. C'est un vrai manque de ce passer de cette option.

Sous Total

Alors là, c'est une vraie absence de se priver de cette fonctionnalité.

Même si on fait la même chose avec des formules de regroupement.

Les autres fonctions concernent le partage des classeurs :

• Suivi des modifications ;
• Partage du classeur.

Discuter des apports

Système à bande

Celle qui saute aux yeux c'est la mise en forme automatique des tableaux et principalement le système à bandes.

Il n'est pas trop compliqué de reproduire ce fonctionnement avec une "Mise en Forme conditionnelle".

Il suffit de sélectionner sa plage, et choisir "Utiliser une formule...". On écrit ensuite la fonction =Mod(Ligne(); 2) = 0 pour colorer les ligne paires et =Mod(Ligne();2) <> 0 pour les lignes impaires.

Pour la mise à jour automatique, il suffit d'insérer n'importe où dans le tableau ou rajouter une ligne d'insertion en fin de tableau.

Pour la mise à jour automatique des formules, la recopie incrémentale fait bien son travail et prend moins d'une seconde à réaliser.

Relation 1 à N sans utiliser la RechercheV()

Mettre en relation des données, c'est l'art de structurer ses tableaux pour ne pas réécrire des données existantes dans un tableau dans un autre.

Si j'ai un tableau des commandes et un des clients, dans les commandes, je voudrais peut-être afficher le nom, prénom, adresse, code postal et ville du client.

L'objectif est d'écrire dans les commandes une information des clients (typiquement le code client) et que les colonnes concernant la personne se mettent à jour toutes seules.

C'est tout l'intérêt de la fonction RechercheV().

Depuis la version 2013, il n'est plus nécessaire de créer une formule car un menu "Relation" existe dans l'onglet "Données".

Critiques du modèle

• Noms de colonnes
  Ils ne sont pas supportés par toutes les fonctionnalités du logiciel.
  Ils n'apparaissent nulle part.
  Pourtant c'est bien pratique pour atteindre rapidement une zone précise du classeur.
  Il faut donc refaire des noms classiques pour les plages.
• La ligne de total collée aux données
  C'est pour moi rédhibitoire !
• Le modèle n'est pas utilisable partout
  Ce qui oblige à utiliser d'anciennes techniques.
• Que certaines fonctions importantes soient désactivées est un vrai problème.
• Il est très perturbant que les anciens classeurs convertis aux nouveaux formats ne comprennent pas complètement le modèle même quand on réécrit les formules.

Conclusions

Ce qui serait un vrai plus

Autant il est simple avec de l'astuce de régler les suppressions en fin de tableau, il est plus compliqué de détruire la première ligne de données.

Une solution (peu satisfaisante) c'est de créer sur la première ligne un enregistrement fantôme qui n'est jamais manipulé et contient les formules de calcul.

Solution incomplète qui demande à murir

Mais je garderais un œil sur son évolution. Avec PowerPivot, Microsoft Excel pourrait à terme se comporter comme une vraie base de données avec ses avantages et inconvénients. Quid de Microsoft Access ?

Et ce n'est pas par hasard si certaines entreprises interdisent à leurs employés d'utiliser Microsoft Access.

Je ne suis pas prêt aujourd'hui à adopter cette philosophie

Il n'est rien de ce qui est proposé que l'on ne puisse faire avec les outils existant, un peu d'astuce, et sans grandes compétences sur Excel.

De plus en plus d'utilisateurs connaissent la RechercheV().

La recopie incrémentale est devenue triviale pour la majorité des utilisateurs et faire une somme ou une moyenne fait partie du quotidien.