Les références structurées dans les tableaux Excel

A la fin de cet article vous trouverez des exemples d’utilisation des références structurées.

Rappel sur les tableaux dans Excel …

Dans Excel, nous allons mettre en place des tableaux pour bénéficier d’un ensemble de fonctionnalités qui vont simplifier la maintenance et l’utilisation des données en listes.
Parmi ces fonctionnalités :

  • L’activation des tris et filtres.
  • La mise en forme de la liste.
  • La propagation des formats aux nouvelles lignes (mais aussi des mises en forme conditionnelles, des listes déroulantes, etc).
  • Le dimensionnement automatique de la plage du tableau (pratique pour les tableaux croisés, pour les formules)
  • La recopie des formules

Une fois mis en place un tableau, ça ressemble à cela :

Tableau Excel

Pour le mettre en place : sélectionnez une cellule
de la liste, cliquez sur le bouton “Mettre sous forme de tableau”, puis sélectionnez un design dans la liste.

Mettre sous forme de tableau

Contrôlez que la plage est correcte, que la case à cocher des en-têtes est bien cochée.

Boite de dialogue Tableau

Les tableaux prennent automatique un nom : Tableau1, Tableau2, etc … ce qui ne serait pas très explicite dans les formules, vous renommez donc votre tableau :
Cliquez dans une cellule du tableau, activez l’onglet “Création” de “Outils de tableau”, et saisissez un nom en haut à gauche dans “Nom du Tableau” : ce nom doit commencer par une lettre ou “_”,  être sans espace, et ne doit pas avoir la syntaxe d’une référence de cellule (par exemple : tab10 est interdit comme nom car la cellule tab10 existe).

Nommer le tableau

Ce nom est listé dans le “gestionnaire de noms”, l’icone est un petit tableau. (pour faire apparaître le gestionnaire de Noms : Crtl + F3)

Gestionnaire de Noms

Mais passons maintenant aux références structurées, c’est à dire aux références à des cellules d’un tableau.

Les références structurées dans les formules

Une référence structurée, c’est quoi ? Dans cet exemple, la référence structurée c’est le [@Montant].

References_Structurees

Pour saisir cette formule, placez vous en E2, saisissez “=”, puis cliquez sur D2,  [@Montant] s’écrit dans la formule (et non pas D2), puis continuez avec “*20%, faites “Entrée”.
La formule se recopie automatiquement jusqu’en bas du tableau (ça c’est vraiment génial !)

Formule recopiée

Notez que la recopie de la formule est une option qui se paramètre en cliquant sur le bouton avec l’éclair et en choisissant “Contrôler les options de correction…”.

option de colonne calculée

option de recopie de la formule

Tant que nous sommes dans les options, c’est dans Fichier / Options / Formules qu’est réglée par défaut l’option qui fait que ce sont les références structurées qui sont utilisées et non pas les références de cellule.

options_calcul

La syntaxe des références structurées

La syntaxe diffère légèrement dans Excel 2007, ce qui suit est valable à partir d’Excel 2010.

Pour saisir la formule, le plus simple est saisir “=” puis de cliquer sur la (ou les) cellules à inclure dans la formule.
Si vous saisissez la formule dans le tableau le nom du tableau n’apparaît pas.
Si vous saisissez la formule en dehors du tableau le nom de celui-ci apparaît.
Si vous préférez saisir la formule en toute lettre, le nom du tableau va être listé (comme les fonctions, ici le tableau est nommé “TabFact”).
Ci-dessous vous voyez la formule saisie dans le tableau : =[@Montant HT], en dehors du tableau : =TabFact[@Montant HT], et le début d’une saisie en toute lettre.

Saisie de la formule

Voici les syntaxes les plus classiques :

Sur la ligne courante, la référence à une colonne. =[@Montant HT]
Sur la ligne courante, la référence à 2 colonnes consécutives.
Ici une somme en dehors du tableau donc avec TabFact[…]
=SOMME(TabFact[@[Montant HT]:[TVA]])
Sur la ligne courante, la référence à toutes les colonnes.
Ici un NB (comptage des nombres) à utiliser uniquement en dehors
du tableau sinon il y aura référence circulaire.
=NB(TabFact[@])
Une colonne entière (sans le titre, sans le total). =SOMME(TabFact[Montant HT])
Plusieurs colonnes (de la colonne “Montant HT” à la colonne “TVA” =SOMME(TabFact[[Montant HT]:[TVA]])

 

et celles qui font appel aux zones du tableau :

Zones du tableau

L’en-tête d’une colonne. =TabFact[[#En-têtes];[Port]]
Toute la ligne d’en-tête. TabFact[#En-têtes]
Le total d’une colonne. =TabFact[[#Totaux];[Montant HT]]
Les totaux de plusieurs colonnes. =SOMME(TabFact[[#Totaux];[Montant HT]:[Port]])
Une colonne entière avec le titre et le total. =TabFact[[#Tout];[Montant HT]]
Tout le tableau. TabFact[#Tout]

Certaines utilisations sont rares dans les formules, mais elle peuvent servir par exemple pour des plages nommées.

Recopie des références structurées

Si vous tirez avec la poignée de recopie une formule qui contient des références structurées, celles -ci ce comportent comme des références relatives : elles se décalent.
ci dessous “=([@Février]-[@Janvier])/[@Janvier]” est devenue “=([@Mars]-[@Février])/[@Février]” en tirant sur la droite.
Si vous utilisez un copier coller d’une référence structurée, et non la poignée de recopie, la référence reste la même … comme une référence absolue.

Recopie de formule

Enfin la technique pour “verrouiller” une référence consiste à la doubler :
Dans l’exemple ci dessous vous voulez qu’en tirant vers la droite la colonne Total ne se décale pas.
Donc au lieu de saisir [@Total], saisissez  TabVentes[@[Total]:[Total]], TabVentes étant le nom du tableau.

Recopie de formule avec référence structurée "absolue"

Exemples de formules en référence structurées

Colonne avec cumul

Cumul
avec la formule =SOMME($B$2:[@Montant])

Colonne avec cumul conditionnel

Cumul conditionnel

ici la formule =SOMME.SI.ENS($C$2:Tableau_2[@[NbH]:[NbH]];$B$2:Tableau_2[@[typ]:[typ]];Tableau_2[[#En-têtes];[SamDim]]) est “recopiable” vers la droite.

Deux listes déroulantes dépendantes

Exemple_3

Les colonnes Service et Nom contiennent des listes déroulantes (Onglet Données / Validation des données / Autoriser liste).
Dans la colonne Service :
=INDIRECT(“Tableau_4[#En-têtes]”)
Dans la colonne Nom :
=INDIRECT(“Tableau_4[“&$B2&”]”)
Choisissez un service, la liste des noms se met à jour dans la 2ème liste déroulante … mais ce qui est intéressant c’est que si vous ajoutez des personnes ou des services le contenu des listes se met à jour.

 

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Vous pouvez utiliser ces balises et attributs HTML:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>