Référence cellule VS table structurée

Question

Utilisant de plus en plus de tables structurées dans mes fichiers, j'aurai souhaité avoir l'avis des experts Excel/VBA/Programmation sur ce sujet : mieux vaut-il utiliser les références de tables structurées (=&#91;@Colonne1&#93;) ou la référence de la cellule sur la feuille (=H5) ? Pourquoi ? Personnellement je trouve plus lisible une formule, notamment qui contient beaucoup de fonctions imbriquées, avec des références de cellules. Cependant je ne sais pas si c'est la bonne méthode à appliquer et j'aimerais avoir votre avis dans ce cadre. J'ai l'impression qu'en codage, ou même sur POWERQUERY/POWERBI, la référence en table structurée est plus souvent utilisée (vous allez me dire c'est normal on travaille sur des tables, alors quid de Excel ?). En vous remerciant par avance pour vos retours constructifs. Bon dimanche...

3GB · Accepted Answer

Oui, pour la lisibilité, il faut des noms de colonnes courts et clairs. Mais je trouve que Articles&#91;Prix&#93; est plus clair que Feuil1!$B$2:$B100 ou 'Articles de mon formidable stock d'articles stockés en stock'!B:B . Perso, je ne connais pas vraiment les "vraies" bases de données même si je pense en comprendre la logique. Et en général, ça fonctionne avec des champs (des colonnes) définis et identifiables par un nom, dont la taille (en lignes) importe peu. Excel reproduit plus ou moins la même logique avec les tableaux structurés, en soulageant l'utilisateur de la contrainte du contrôle du nombre de lignes, celui-ci étant de fait supposé évolutif. Il faut donc un champ "clé unique" et on peut sonder les éléments à coup d'INDEX EQUIV. En effet, l'intérêt est de dynamiser l'exploitation de l'information, et JoyeuxNoel le montre bien avec l'exemple de la liste de validation, très utile ! Pour les formules à propager sur les autres colonnes, ça rallonge un peu mais il faut faire : =SOMME.SI(Tab&#91;&#91;ColonneFigée&#93;:&#91;ColonneFigée&#93;&#93;;&#91;@&#91;CritèreFigé&#93;:&#91;CritèreFigé&#93;&#93;;Tab&#91;ColonneLibre&#93;) Ici, la colonne figée est bloquée comme avec un $A. Le critère (du présent tableau, celui des résultats) est lui aussi figé et la colonne à sommer est laissée libre. Par exemple, si on a 3 colonnes HT, TVA et TTC, en saisissant cette formule sur HT, on obtient la somme conditionnelle sur TVA et TTC en la faisant glisser. Sinon, il est possible de copier/coller la formule pour obtenir la formule à l'identique. Ca permet un gain de place mais c'est moins "dynamique", il faut revenir sur les colonnes pour modifier la formule en y remplaçant le paramètre variable. Ca reste assez rapide vu qu'on bénéficie de la saisie assistée avec les plages nommées. En fait, tout dépend du besoin. Si on a par exemple un INDEX EQUIV à propager sur 20 colonnes, on saisit bien la première formule en figeant les paramètres fixes et en libérant les variables et on est tranquille...

3GB · Answer

En attendant les experts, je vais essayer donner mon avis. Je dirais qu'au sein même du tableau, il est quand même préférable d'utiliser les références de tables (qui sont relatives au nom donc à la colonne précisément) plutôt qu'aux adresses. Déjà, je trouve ça plus lisible, même s'il est vrai que les formules deviennent plus longues, et ça permet de rendre la formule dynamique et de ne pas tenir compte de la ligne en cas de petit changement au niveau des lignes, c'est donc une sécurité ! Après, je ne pense pas qu'il y ait de règle car tout dépend souvent du contexte. Il arrive qu'il faille faire référence à la ligne précédente, qu'on ait des plages "évolutives" avec juste la ligne de début figée. Dans ce cas, c'est plus simple d'utiliser la référence A1 que d'utiliser des DECALER et des EQUIV. Mais le but est d'avoir un tableau qui se restructure automatiquement (formules comprises) pour préserver l'information contenue et n'avoir que des références de tableau est une garantie de viabilité des formules. Et en général, on peut passer du temps à trouver les bonnes formules mais une fois qu'elles marchent bien, on ne revient pas dessus pour les contempler et on peut dormir tranquille. Et de mon point de vue personnel, je pense qu'il est bien d'utiliser ce qui facilite l'utilisation et ensuite de tout nommer et de faire référence aux noms (notamment pour VBA). Je pense que le nom est une propriété centrale ! Très bon dimanche à toi aussi ...

21Formatic · Answer

Pour une liste déroulante qui fait référence à une colonne de tableau structuré, tu as juste à faire comme ceci : =INDIRECT("Nom_tableau&#91;nom_entête&#93;") Ça marche nickel. Là où j'ai plus de mal à titre perso, c'est quand par exemple on fait un somme.si ou autres fonctions du style sur un tableau structuré. Quand on étend la formule, ça étend aussi la référence aux colonnes. Je n'ai pas encore la manipulation pour figer un nom de colonne. Une astuce consiste à copier coller la formule plutôt que de l'étendre, mais je ne trouve pas ça top...

21Formatic · Answer

Le pire, c'est que ça marche, en effet

Bon je n'avais pas exploré plus que ça mais, de mémoire, je n'étais pas arrivé à tomber sur cette information quand j'avais regardé. Merci beaucoup pour ce partage !

Nul doute que ça me servira à l'avenir !

thev · Answer

Pour compléter ce qui vient d'être dit, l'utilisation d'un tableau structuré apporte un certain nombre d'avantages, notamment : 1- il est borné, donc pas besoin de gérer une fin de tableau. Sa borne est affichée via un petit triangle dans la dernière cellule en bas à droite. 2- Toute formule standard dans une colonne du tableau se recopie automatiquement sur toutes ses lignes et sur toute nouvelle ligne qui y serait ajoutée. Et comme dit par 3GB: Et en général, on peut passer du temps à trouver les bonnes formules mais une fois qu'elles marchent bien, on ne revient pas dessus pour les contempler et on peut dormir tranquille. cf exemple : 2 tableau x structurés relatifs à des personnes et reliés par le même identifiant. Dans le 2éme tableau (personnes à charge), les noms et prénoms de la personne sont rappelés par formule à partir du premier tableau. Pour POWERQUERY/POWERBI, la référence en table structurée est plus souvent utilisée, en fait , elle est obligatoire. Par contre j'ai l'impression qu'on ne peut pas utiliser ces plages structurées dans des définitions de noms pour des validations de données. C'est vrai mais elles ne sont pas inutiles car elles permettent de redéfinir des références de nom sans se préoccuper des bornes de la plage. cf exemple : les noms F.1, F.2, F.3 font référence à des tableaux structurés et permettent sur la feuille Feuil2, d'afficher une validation de données à partir de la cellule A6 selon le choix du fournisseur...