« Passez aux tableaux ! » et découvrez enfin le potentiel d’Excel

La vie d’un utilisateur d’Excel est ponctuée de petites et grandes découvertes. Nouvelle fonction, nouvelle astuce qui permet de se simplifier la vie et de gagner un temps assez conséquent. En général, on se souvient toujours de la personne qui nous a donné le tuyau et de ce que ça a pu changer par la suite. Comme quoi on apprend bien souvent sur le tas, et trop souvent au pied du mur… Dans mon cas, il y aura un avant et un après l’utilisation des tableaux.
Ma pratique d’Excel a radicalement changé, mes calculs se sont structurés en des flux de données bien plus ordonnés !

Bien entendu, encore faut-il savoir ce qu’est un tableau sous Excel… Le langage courant parle de tableau Excel pour parler le plus souvent d’une plage de cellules, remplie de données et de calculs, ayant une certaine cohérence de présentation et le plus souvent joliment mis en forme… Et bien non ! Cet article décrit l’outil « Tableau » sous Excel que vous trouvez juste à côté du bouton « Tableau Croisé Dynamique » dans le menu Insertion.

Pour commencer avec les tableaux Excel…

Pour réaliser un tableau Excel, commencez par sélectionner une plage de données, contenant des données ou pas.

Cliquez sur « Insertion » / « Tableau » (ou Ctrl+L)

La première ligne va constituer vos entêtes, les lignes suivantes constitueront les valeurs de ce champs (ou colonnes).

Un menu spécial « Outils de tableau » apparaît dans le ruban avec les options propres à la gestion du tableau lorsque celui-ci est sélectionné.

Quelques principes de base des tableaux Excel

Les tableaux ont quelques spécificités qu’il faut intégrer pour bien profiter de leur énorme potentiel. On ne gagne pas en efficacité sans changer un peu ses habitudes ou apprendre quelques éléments nouveaux. Mais il y a fort à parier que vous ne verrez plus Excel de la même manière une fois arrivé à la fin de ce post !

Les dénominations dans les tableaux Excel

• Sous Excel, les tableaux portent un nom, que l’on peut modifier dans le menu « Outils de tableau », première section du ruban, tout à gauche.
• Dans un même tableau Excel, les entêtes (en haut des colonnes) doivent avoir un nom unique. Sinon Excel ajoutera un numéro à la fin de son nom, en l’appelant « Colonne » par défaut si la cellule d’entête est vide. En effet, les formules faisant référence à des éléments présents dans un tableau se basent presque toujours sur le nom des entêtes.

Bonnes pratiques

La verticalité

Un tableau Excel fonctionne dans la dimension verticale, comme toute base de données (type Access). Chaque ligne constitue une entrée supplémentaire. C’est juste vital pour une bonne exploitation des données dans un tableau croisé dynamique (TCD).

Une formule unique par colonne

L’usage optimal des tableaux Excel (et que je recommande très fortement !) préconise d’avoir une formule unique et homogène par colonne. 

Si vous saisissez une formule dans une colonne vide, Excel l’appliquera à toutes les cellules de la colonne du tableau dès que vous taperez sur « entrée ».

De même si les formules d’une colonne sont bien identiques, toutes les cellules de celle-ci seront modifiées si vous en changez une seule. C’est simple et vous gagnez beaucoup de temps. Afin de signifier cette modification des formules sur l’ensemble de la colonne du tableau, un « flash noir » clignotera une fois sur toute la colonne lorsque vous taperez « entrée ». Si ce flash n’apparaît pas, cela signifie que les formules n’étaient pas forcément identiques et que vous n’avez modifié qu’une seule cellule. Sauf si c’est là le but recherché, il faut donc sélectionner toute la colonne et d’appliquer la formule modifiée à tout l’ensemble… La fiabilité de vos fichiers Excel s’en ressentira. 

Des références… avec un format nouveau

• Pour faire référence aux données d’une colonne d’un tableau Excel dans une formule, on tapera : nomdutableau[nomdel’entête]. Le nomdutableau est inutile s’il s’agit d’une formule au sein dudit tableau. Les raccourcis clavier que nous verrons plus bas vous éviterons de tout taper, mais il faut au moins savoir le lire.
• Pour faire référence à une donnée située sur la même ligne dans le même tableau : [@[nomdel’entête]]. Lors de la rédaction de la formule, il suffit d’utiliser les flèches pour sélectionner la bonne cellule, comme d’habitude.
• Vous pouvez aussi être amené à faire référence à une cellule d’entête : nomdutableau[[#En-têtes];[nomdel’entête]]. Même chose, ceci s’affichera tout seul en utilisant les flèches.

Voilà déjà de quoi bien commencer… L’exploration du menu spécifique, de l’aide Excel (assez bien faite) et surtout un peu de pratique complèteront ces premières indications. Sinon, faites-le savoir dans les commentaires de cet article et je détaillerai les éléments qui vous manqueraient !

Sélection des données des tableaux Excel

Pour accélérer la rédaction de vos formules, abandonnez la souris et utilisez les raccourcis claviers. Certains de ceux décrits dans un précédent article trouvent ici une pertinence renforcée, notamment :

• Ctrl + espace :
  • Utilisé à l’intérieur d’un tableau, ce raccourci permet de sélectionner verticalement toutes les cellules de la colonne, à l’exception de l’entête. C’est idéal pour la rédaction d’une formule faisant référence aux valeurs d’une colonne : déplacement avec les flèches vers la colonne à sélectionner, suivi d’un « ctrl + espace » et le tour est joué.
  • Répétez cette commande, et vous étendrez la sélection à la cellule d’entête. C’est pratique pour insérer une colonne dans votre tableau, sans impacter les cellules qui se trouvent autour de votre tableau dans le même onglet.
  • Répétez une troisième fois cette commande, et cette fois-ci c’est bien toute la colonne de l’onglet qui sera sélectionnée. Ceci vous permettra d’insérer (ou de supprimer) une colonne en impactant les cellules qui se trouvent autour de votre tableau dans le même onglet.
• Shift + espace :
  • Permet de sélectionner tout une ligne de votre tableau, le plus souvent pour supprimer une entrée.
  • Si vous répétez cette commande, c’est toute la ligne de l’onglet qui sera alors sélectionnée.
• Ctrl + Shift + L : active ou désactive le filtre sur les entêtes, vous permettant de trier plus facilement vos données.
• Ajoutons Ctrl +  » qui permet d’afficher les formules (pareil revenir à l’affichage normal). Ceci permet de passer rapidement vos tableaux en revue pour s’assurer que toutes les formules sont bien homogènes.

Avantages des tableaux Excel

Sécurisation des calculs réguliers

Les tableaux Excel sont parfaits dès lors que vous effectuez régulièrement les mêmes calculs (mise à jour mensuelle par exemple), particulièrement avec un volume de données important. Les données transitent par le tableau sans que vous ayez à en modifier les formules. Il suffit éventuellement d’en ajuster les dimensions (nombre de lignes), et encore cela peut se faire automatiquement comme nous le verrons plus bas !

Lisibilité

Les tableaux vont structurer l’organisation de vos onglets et de vos fichiers. Vous gagnerez sensiblement en lisibilité : des fichiers bien construits, un tableau correspondant à une étape du calcul, chaque chose a sa place… Et en prime, les formules sont bien plus lisibles ! Finis les fichiers où l’on ne sait plus par où commencer avec des formules aux airs de code wifi mélangeant joyeusement les lettres, les chiffres, les « $ », les « ! » et autres « : » !

Voici un exemple simple permettant de comparer la rédaction d’une formule appliquée à un tableau ou à une plage de données :

Avouez que c’est un peu plus facile de s’y retrouver !

Réduction des erreurs

Si vous prenez soin de bien avoir une formule unique par colonne, vous éliminerez beaucoup d’erreurs. Vous ne dépendrez plus de ces  « plug » ou autres ajustements auxquels on a eu recours pour un temps, mais qui finissent par pourrir les calculs… Si vous devez toujours ajuster manuellement vos données, il faudra le faire proprement dans une colonne dédiée. Ça se voit dans le tableau, c’est un peu moche mais on assume et on les met à zéro quand on n’a plus besoin. Ça peut sembler un peu extrême, mais au moins vous éviterez de vous faire rattraper par ces petits bricolages. Vous gagnerez en fiabilité et surtout vous éviterez de perdre du temps à vérifier vos calculs sans aucune valeur ajoutée !

Moins de manipulation sans intérêt

L’application d’une formule unique sur tout une colonne simplifie grandement les choses. Vous n’aurez plus à tirer une formule avec votre souris jusqu’à la dernière cellule en bas de votre feuille, au risque de déraper, d’écraser d’autres données, de manipuler les ascenseurs, d’ajuster la mise en forme pour que ça reste joli etc…

Des références solides et faciles à saisir

Faire référence aux éléments d’un tableau Excel sécurise et simplifie la rédaction de formules, quel que soit son emplacement dans le fichier :

• Commencez à taper le nom du tableau. Le nom complet vous sera suggéré par Excel. Les tableaux étant identifiés avec une icône spécifique les distinguant des fonctions :

• Ouvrez un « [ », et le nom des entêtes apparaitra en pré-saisie,
  

• Sélectionnez et tapez « ] ». Notez que vous pouvez saisir autant les données des colonnes que les éléments du tableau Excel (entêtes, la ligne sélectionnée etc…)

Voilà ! Sans comparaison avec la sélection d’une plage à la souris sur un onglet à l’autre bout du fichier !!

Une base de données idéale pour un tableau croisé dynamique (TCD)…

Les données sont bien structurées, rangées sous leurs entêtes. La preuve ? Lorsque vous double-cliquez sur un chiffre dans un TCD, les données sources s’affichent dans un nouvel onglet sous forme… de tableau !

Plus besoin de redimensionner les données sources du TCD si la taille du tableau a été ajustée. En effet, le TCD retient le nom du Tableau comme source de données, quels que soient son emplacement dans le fichier ou ses dimensions… De belles erreurs évitées en oubliant les 3 dernières lignes rajoutées en bas d’une plage de données !

Et c’est pareil pour les graphiques !

Si la colonne d’un tableau contient les données d’un de vos graphiques, le redimensionnement du tableau impactera automatiquement le graphique. Fini les données des séries à réajuster une par une avec sa souris après les avoir sélectionnées tant bien que mal dans le graphique !

Et pour terminer par une notion esthétique…

Pas besoin de se battre avec la mise en forme de vos cellules. L’essentiel des options est disponible dans le menu propre aux tableaux. Le redimensionnement du tableau mettra automatiquement en forme les nouvelles cellules. A l’inverse, Excel rendra leur format par défaut aux cellules qui seraient désormais situées hors du tableau…

Quand je vous disais qu’il y avait un avant et un après l’utilisation des tableaux sous Excel. A tel point que vous en retrouverez dans pratiquement tous les tutos proposés sur ce site… Normal, on vous propose des fichiers solides et fiables pour gagner du temps et doper la valeur ajoutée de votre travail !

Inconvénients des tableaux Excel

Franchement, j’ai du mal à en trouver… je suis certainement loin d’en avoir trouvé les limites. Alors n’hésitez pas à partager celles que vous avez pu rencontrer dans les commentaires ci-dessous ! Cela m’intéresse grandement !

Exemple d’utilisation des tableaux Excel

Couplé avec les filtres élaborés (ou filtres avancés), vous revisiterez bon nombre de vos fichiers traitant une quantité significative de données.

Ils permettent idéalement de structurer le flux de vos calculs, déversant les données d’un tableau Excel vers un autre… où vous enrichirez ces données en réalisant de nouveaux calculs.

Exemple :

A partir d’un tableau Excel reprenant tout le détail de votre chiffre d’affaires en valeur et volume par année / mois / produit / pays / client / scénario (Budget / réel / reprévision), vous sélectionnez et déversez via un filtre élaboré les données qui vous intéressent pour calculer la rentabilité de ces ventes dans un autre tableau…

Grâce à une macro disponible dans notre rubrique Tutos, vous pourrez en 1 clic sur un bouton :

1. filtrer et déverser les données qui vous intéressent d’un tableau Excel vers un autre,
2. remettre à dimension le tableau d’arrivée selon le nombre de données sélectionnées,
3. et rafraîchir les TCD qui s’y réfèrent.

Gain de temps et fiabilité garantis grâce à ce tuto !