Microsoft 365 Référence automatique de tableaux structurés

[AlexiaC]

Bonjour Forum,

Deux petites questions pour vous aujourd'hui... la première sur une nomenclature de formule, qui va sans doute vous paraitre plutôt nono mais j'ai de la difficulté à la formuler en quelques mots clés qui feraient que Google pourrait être mon ami...! et l'autre, peut-être moins basique!

1. Dans le fichier en pj, j'ai une base de données - Onglet Données, qui comprend le nombre d'heures assignées à divers projets par période. Dans l'onglet Test, j'utilise une formule en sommeprod pour faire la somme du nombre d'heures totales passées dans chaque projet:

SOMMEPROD((t_Test[[#En-têtes];[Projet1]]=t_Test4[[#En-têtes];[Projet1]:[Projet4]])*(t_Test4[[Période]:[Période]]=t_Test[@[Période]:[Période]])*(t_Test4[[Projet1]:[Projet4]]))

Jusque-là, ça roule. Là où ça commence à être un enjeu, c'est quand je veux que se calculent, dans mon onglet Test, d'autres heures relatives à de nouveaux projets, qui sont au préalable ajoutées dans l'onglet Données. Si j'ajoute les en-têtes de colonne Projet5 et Projet6 dans les colonnes F1 et G1, est-ce possible que la partie de la formule en rouge ci-dessous soit "capable de comprendre" que le tableau a été agrandi et qu'il faut désormais aller jusqu'à "Projet6" (soit la dernière colonne du tableau) pour que les cellules en vert se complètent automatiquement, et ce, sans vba? Ce serait hyper simpliste (je l'ai testé hahaha), mais ce serait de remplacer le [Projet4] par "[DERNIERECOLONNE] ou qqch de même en fait... et si elle existe, c'est cette nomenclature de formule que je rechercherais.

SOMMEPROD((t_Test[[#En-têtes];[Projet1]]=t_Test4[[#En-têtes];[Projet1]:[Projet4]])*(t_Test4[[Période]:[Période]]=t_Test[@[Période]:[Période]])*(t_Test4[[Projet1]:[Projet4]]))

2. Ma 2e question porte un peu sur la même chose, mais à savoir si ce serait possible que la formule s'étende automatiquement dans les nouvelles cellules du tableau de l'onglet Test (colonnes F et G), sans avoir à l'étirer à la mitaine? j'imagine que ca prendrait du vba ici, mais je m'essaie pareil au cas où!

Merci beaucoup

 

[goube]

Bonsoir
Pour le premier point, avec la fonction indirect

=SOMMEPROD((t_Test[[#En-têtes];[Projet1]]=INDIRECT("t_Test4[[#En-têtes];[Projet1]:["&RECHERCHE("ZZ";t_Test4[#En-têtes])&"]]"))*(t_Test4[[Période]:[Période]]=t_Test[@[Période]:[Période]])*(INDIRECT("t_Test4[[Projet1]:["&RECHERCHE("ZZ";t_Test4[#En-têtes])&"]]")))

Cordialement

 

[AlexiaC]

Bonsoir
Pour le premier point, avec la fonction indirect

=SOMMEPROD((t_Test[[#En-têtes];[Projet1]]=INDIRECT("t_Test4[[#En-têtes];[Projet1]:["&RECHERCHE("ZZ";t_Test4[#En-têtes])&"]]"))*(t_Test4[[Période]:[Période]]=t_Test[@[Période]:[Période]])*(INDIRECT("t_Test4[[Projet1]:["&RECHERCHE("ZZ";t_Test4[#En-têtes])&"]]")))

Cordialement

Merci infiniment!! faut que je décortique la formule pour la comprendre et être capable de l'appliquer ailleurs mais elle fonctionne, c'est parfait Merci de votre aide!!

 

[goube]

Merci pour le retour.
Cordialement

 

[Phil69970]

Bonjour à tous

Pour la 2eme question:

si ce serait possible que la formule s'étende automatiquement dans les nouvelles cellules du tableau de l'onglet Test (colonnes F et G), sans avoir à l'étirer à la mitaine?

Cela tombe bien tu as un TS donc les formules se propagent automatiquement
Plus fort encore si tu supprimes toutes les lignes de ton TS et bien miracle tes formules reviendront automatiquement lors de la création de ta nouvelle 1ere ligne

Fais l'essai et tu verras un miracle

TS = tableau structuré

Merci de tonretour

 

[goube]

Bonsoir Phil69970,

Je pense qu'elle veut dire sur les nouvelles colonnes et non les nouvelles lignes. Cela ne semble pas possible sans VBA.
Cordialement

 

[AlexiaC]

Bonsoir Phil69970,

Je pense qu'elle veut dire sur les nouvelles colonnes et non les nouvelles lignes. Cela ne semble pas possible sans VBA.
Cordialement

Exact, je parlais du contenu des colonnes et non des lignes. Au nombre de tableaux que j'ai, je vais pour l'instant continuer à les étirer à la main dans ce cas. Je verrai pour adapter une formule VBA plus tard!

Merci pour vos retours!

 

[Phil69970]

Pour les colonnes il faut copier la formule au moins 1 fois et elle se propagera toute seule
ou bien sur du VBA

 

[AlexiaC]

Bonjour à tous

Pour la 2eme question:



Cela tombe bien tu as un TS donc les formules se propagent automatiquement
Plus fort encore si tu supprimes toutes les lignes de ton TS et bien miracle tes formules reviendront automatiquement lors de la création de ta nouvelle 1ere ligne

Fais l'essai et tu verras un miracle

TS = tableau structuré

Merci de tonretour

Pour les colonnes il faut copier la formule au moins 1 fois et elle se propagera toute seule

Oui je comprends ce bout-là je me demandais si c'était possible qu'elle se propage dans l'ensemble du tableau sans avoir à la la copier-coller dans chacune des colonnes au moins une fois. Mais je comprends aussi que ca prend du VBA, j'ai beaucoup de tableaux donc ce serait fastidieux à rajouter pour l'instant. On verra pour l'année prochaine dans mon modèle de feuille que j'exporte si c'est possible de l'ajouter mais rien que la formule donnée par Goube ci-dessus va déjà me gagner des heures et des heures de modifications, on va prendre ce qu'on a, ça me satisfait déjà beaucoup!

Merci à vous deux

 

[Phil69970]

@AlexiaC

C'est le post #2 de @goube que tu dois valider pas le tien

 

[AlexiaC]

@AlexiaC

C'est le post #2 de @goube que tu dois valider pas le tien

Effectivement, désolée pour l'erreur!

 

[mapomme]

Bonsoir à tous ,

On peut aussi prendre en compte les colonnes supplémentaires et aboutir au tableau désiré sans aucune formule.

Il faut utiliser un certain type de TCD (type : "Plages de feuilles de calcul avec étiquettes") qui fait tout le travail pour nous.
Ce type de TCD est accessible par la commande de menu "Assistant tableau croisé dynamique".

J'ai installé le bouton de cette commande dans la barre d'outils Accès rapide via le menu déroulant de cette barre en choisissant le sous-menu "Autres commandes..."

En cliquant sur le bouton "Assistant tableau croisé dynamique" nouvellement installé, Excel va nous permettre de construire en quelques cliques un TCD répondant à votre souhait (si j'ai bien compris).

Le processus de création du TCD est décrit en image sur la feuille "Données".

Comme les TCD d'Excel ne se mettent pas à jour automatiquement, j'ai écrit une procédure évènementielle qui met à jour le TCD quand on active la feuille où il se trouve :

Private Sub Worksheet_Activate()
   ActiveSheet.PivotTables("TCD-Test").PivotCache.Refresh
End Sub

Maintenant à ce stade, le TCD de la feuille Test est automatiquement mis à jour. Il intègre automatiquement toute modification de la source (valeurs, nombre de lignes, nombre de colonnes, etc.).

nota 1 : le TCD a été appelé "TCD-Test". Pour modifier le nom d'un TCD :

• se placer dans le TCD
• puis sélectionner le menu "Analyse du tableau croisé dynamique"
• et dans le ruban apparait à gauche une zone de saisie "nom" qui permet de modifier le nom du TCD.

nota 2 : ce type de TCD existe depuis belle lurette (au moins 2010 me semble-t-il) et souvent on l'ignore ou bien si on l'a su on l'a oublié (comme moi).

Rem : décrire la méthode est beaucoup plus long que le faire.

 

[AlexiaC]

@mapomme
Merci pour l'info, j'aime l'idée, je vais aller voir ce que vous proposez!