XL 2010 Excel Tableau conserver les nom de colonnes dans les formules (Power Query) [Résolu][Partiellement]

[Guy_M]

Bonjour,

J'utilise les tableaux Excel, c'est pratique et cela rend beaucoup plus lisible des formules car à la place d'une référence de colonne (A), on utilise le nom de la colonne. Il y a plein d'autres avantages.

Naïvement, je pensais que cela fonctionne comme pour une base de données, si une formule fait référence à une colonne d'un tableau (par son nom), on peut ajouter ou supprimer une colonne dans le tableau source sans impact sur les formules y faisant référence.
Que nenni, si on ajoute ou supprime une colonne dans un tableau, le nom dans une formule qui y fait référence change. Il semblerait que Excel, pour les noms de colonne utilise un numéro absolue de cellule ($A$1).

Auriez-vous un truc pour que le nom dans les formules ne change pas quand on modifie la structure d'un tableau ?

Si vous me proposez "INDIRECT" comment puis-je transformer toutes les formules d'une feuille pour y mettre "INDIRECT" ?

Par avance, je vous remercie de vos réponses.

A bientôt
Guy Marty

 

[kan3pz]

Bonsoir Guy_M,
Un fichier exemple avec ce que tu souhaites faire pourrait nous être utile pour t'aider.
Ceci étant dit normalement pas de problème pour utiliser le nom des colonnes entre crochets dans les formules... A confirmer avec ton fichier.
A+

 

[chris]

Bonjour

Demande peu claire : si je fais référence à une cellule de tableau ou bien une colonne de tableau, la formule fait toujours référence au même nom.

Par ailleurs même quand on utilise les $ dans une formule l'ajout ou la suppression de colonne avant la colonne concernée fait évoluer la formule afin de pointer sur la cellule initiale qui s'est déplacée.

 

[Guy_M]

Bonjour,
Merci pour ces premières réponses.
Il semble donc que personne n'ait rencontré ce problème.
Après quelques tests, je pense que cela n'arrive quand j'utilise powerquery pour modifier la source de données.
Peut-être cela vous donnera-t-il quelques pistes de réflexion ?

 

[gosselien]

Bonjour,

tu peux montrer un exemple avec AVANT/APRES ?

P.

 

[Guy_M]

Bonjour,

Elle est bien bonne, j'ai posté un message le 18/5 avec le fichier avant/après, il semble qu'il ne soit jamais arrivé ici

Pour reproduire le problème, vous devez utiliser Power Query.

Le fichier contient 5 feuilles.

• 2 "Avant" et "Après" pour montrer ce qu'il se passe à ce qui n'utilisent pas PowerQuery
  
• 1 "Calculs" qui ne devraient pas changer, elle va chercher ses valeurs dans la feuille "BaseDeDonnées"
• 2 feuilles "Origine1" et "OrigineB" qui simulent des données externes, la différence entre les deux est qu'il y a une colonne de titre "BB" insérée au milieu du tableau.
  
• 1 feuille "BaseDeDonnées" qui est mise à jour avec Power Query.

Pour reproduire le problème, il suffit de changer la source dans la requête Power Query de la feuille "BaseDeDonnées".

Dans "Calculs", les formules faisant référence aux colonnes de "BaseDeDonnées" par leur nom, je pensais naïvement que cela n'affecterai pas "Calculs". Il en est rien, quand dans "BaseDeDonnées" on passe de "Origine1" à "OrigineB", les formules dans les colonnes de titre "A5" et "A10" changent.

En espérant que cela sera plus clair.

Par avance, je vous remercie de vos réponses.

 

[chris]

Bonjour

Déjà tes formules ne sont pas correctes : tu utilises INDEX et EQUIV sur des cellules uniques et non des plages ce qui n'a pas de sens.
INDEX s'utilise sur une matrice et il est plus fiable de préciser un numéro de colonne qui peut être obtenue, entre autres solutions, par la recherche du titre...

Que ce soit avec ou sans PowerQuery, les références tableaux ne sont pas absolues : en fonction des calculs s'y référant, on peut utiliser différente méthodes.

A noter qu'un tableau a une logique table de base de données qui normalement évolue en nombre de lignes et pas en nombre de colonnes ce qui a des effets de bord sur la logique Excel.

Avec PowerQuery s'ajoute le fait que BDD est un tableau issu d'une connexion et non un tableau interne à ton classeur (même si la source de la requête est dans le même classeur).
Ce n'est pas géré comme un tableau habituel mais par position.
Si tu sais que ton tableau va évoluer en largeur, Calculs (dont j'ai renommé le tableau Calcul) devrait utiliser une formule du type

=SIERREUR(INDEX(BDD;EQUIV(Calcul[@[Index]:[Index]];BDD[[Index]:[Index]];0);EQUIV(Calcul[[#En-têtes];[A1]];BDD[#En-têtes];0));"")

ou

=SIERREUR(INDEX(BDD;EQUIV(INDIRECT("Calcul[@Index]");INDIRECT("BDD[Index]");0);EQUIV(Calcul[[#En-têtes];[A1]];BDD[#En-têtes];0));"")

 

[Guy_M]

Bonjour,

Merci beaucoup, la première formule aboutit au résultat escompté, je pense avoir compris pourquoi (j'ai pris l'habitude de remplacer RECHERCHEV par INDEX/EQUIV et donc je n'utilise pas le numéro de colonne qui n'est pas nécessaire mais il est diablement utile pour maitriser le changement de la position d'une colonne). Petite question dans la formule : BDD[[Index]:[Index]] n'est-il pas identique à BDD[Index] ?

Je suis d'accord, le nombre de colonnes ne devrait pas évoluer. L'évolution du nombre de colonne est un effet de l'importation de données extérieures.

La solution que j'applique actuellement est s'il y a de nouvelles colonnes, je les supprimes ou si elles sont intéressantes, je les place à la fin comme cela ce qui ne me casse pas tout ce que j'avais fait sur les données jusque là.

Ma difficulté est de repérer les colonnes ajoutée (ou supprimées) quand on importe plusieurs jeux de données liés entre eux contenant plusieurs centaines de colonnes pour produire plusieurs analyses.

Pour expliquer le problème, j'ai simplifié l'exemple à l’extrême : l'équivalent d'un tableau "Calcul" va chercher les données dans plusieurs tableaux issus d'importations séparées et le contenu d'une cellule peut contenir une formule basées sur des données de plusieurs tableaux.

J'ai donc des dizaines de modifications de formules à faire.

Puis à résoudre le problème posé par "=NBVAL(BDD[A5])"

 

[chris]

Bonjour

BDD[[Index]:[Index]] n'est-il pas identique à BDD[Index] ?

Non justement car elle équivaut en quelque sorte à $INDEX

 

[Guy_M]

Bonjour,

Non justement car elle équivaut en quelque sorte à $INDEX

Du coup, j'ai essayé "=NBVAL(BDD[[A5]:A5]])" et cela n'a pas eu le résultat escompté.

Il faut que je trouve une autre solution pour ce cas.
Edit :
Note pour moi-même
Faire un NBVAL sur la colonne récupérée par son numéro avec une formule du type EQUIV(Calcul[[#En-têtes];[A1]];BDD[#En-têtes])
A tester, quand j'aurai Excel entre les mains.