[Résolu] TCD : faire des calculs en fonctions de valeurs et compter nbre d'occurences

[Akortys]

Bonjour à tous,

Voila je suis un peu dans la panade c'est pour cela que je me tourne vers vous.
Dans le fichier joint j'ai une plage de données (feuille "Global"). Cette plage de données est variable en fonction d'un export.
J'ai créé un TCD (feuille "Synthese").
Ce TCD permet la synthèse de l'ensemble des notes pour une personne donnée sur l'ensemble des exercices.

Je souhaiterais faire les choses suivantes :
1 - Pouvoir donner une note en chiffre en fonction d'un algorithme sur le nbre de A, B, C, ...
(Par exemple un A vaut 3 points, un B vaut 2 points, C ....).
L'algorithme je l'ai donc c'est pas trop le souci mais c'est de savoir le nbre de A, de B, de C, ...

2 - En fonction du filtre Nom du candidat je souhaiterais alimenter un deuxième tableau pour remonter des informations liées au candidat choisi.

J'ai beau retourné dans tous les sens mais je n'arrive pas à compter le nbre d'occurence en TCD ainsi que de créer un deuxième tableau en fonction du premier filtre

Je pense que la solution est en VBA mais comme je ne maitrise pas beaucoup ça se complique. J'aurais bien fait une boucle for sur le nom du candidat et scruter l'ensemble des notes de l'exercice 1 ou faire un champ calculé pour compter le nbre d’occurrence avec =NB.SI(C;"A") ....

En bref, je nage et coule...

Si vous avez des infos je suis preneur.

 

[JCGL]

Re : TCD : faire des calculs en fonctions de valeurs et compter nbre d'occurences

Bonjour à tous,

Un essai basique

A+ à tous

 

[Akortys]

Re : TCD : faire des calculs en fonctions de valeurs et compter nbre d'occurences

Merci JCGL,
Le cadeau est presque fini d'emballer. J'ai adapté en fonction de ce que j'avais besoin.

Par contre ce qui me dérange c'est que pour la lecture du TCD, il faille préciser une plage de données fixe. Dans l'exemple, pour le calcul des notes il est indiqué :
=NB.SI(F7:F23;"A")*3+NB.SI(F7:F23;"B")*2+NB.SI(F7:F23;"C")*1
ce qui sous entend que si mon TCD dépasse la ligne 23, je suis chocolat pour un calcul correct. Alors je pourrais mettre une valeur de type F10000 mais je bloque du coup a 10000 ( bon ça à la limité c'est pas trop grave) mais je bloque egalement la possiblité de mettre des infos dans les colonnes qui vont bien en-dessous du TCD (quoique si mon TCD est plus grand je ne verrais pas mes infos pour autant).
Bon bref, je cherche une formule plus générique du coup mais je ne sais pas si cela existe.

En tout cas, un grand merci pour le travail déjà effectué et en si peu de temps.

 

[Misange]

Re : TCD : faire des calculs en fonctions de valeurs et compter nbre d'occurences

Bonjour

transforme ton tableau de données en vrai tableau excel (onglet acceuil/style/mettre sous forme de tableau). Créée le TCD à partir de là. De même clique sur les colonnes du tableau source pour faire les sommeprod. C'est magique, tout se mettra à jour dès que tu modifieras ton tableau.

 

[Akortys]

Re : TCD : faire des calculs en fonctions de valeurs et compter nbre d'occurences

Bonjour Misange,

Euuuhhhh, perso j'ai pas très bien saisi car j'ai fait ce que tu as écrit mais :
1 - il me transforme ma plage de données en tableau avec les colonnes filtrées je pense normal au vu de ce qui est indiqué
2 - Mon nouveau TCD ne ressort q'une seule entrée
3 - Je n'ai pas trop saisi le rapport entre le calcul de la note et la mise a jour auto du TCD

Merci d'avance

 

[Misange]

Re : TCD : faire des calculs en fonctions de valeurs et compter nbre d'occurences

Regarde la feuille 1, elle est identique à celle de JC sauf que le tableau de la feuille global est maintenant un tableau et que les formules en ligne 2 y font référence, que le TCD est construit à partir de ce tableau ce qui le rend automatiquement dynamique.
Je n'ai en revanche pas modifié les formules de la ligne 6, il suffit de prévoir des plages englobant le nombre total d'exercices différents que tu auras au maximum. (remplace 23 par 223 si tu as 200 exercices). Ce sont des formules simples (nb.si), pas des matricielles, il n'y a donc pas grand risque d'avoir des problèmes de calculs très longs.

 

[chris]

Re : TCD : faire des calculs en fonctions de valeurs et compter nbre d'occurences

Bonjour

Bise à JCGL et Misange

On peut remplacer la formule par

=SOMMEPROD((DECALER(C$7;0;0;NBVAL($B:$B)-3;1)="A")*3)+SOMMEPROD((DECALER(C$7;0;0;NBVAL($B:$B)-3;1)="B")*2)+SOMMEPROD((DECALER(C$7;0;0;NBVAL($B:$B)-3;1)="C")*1)

avec la disposition actuelle, sinon on peut aussi obtenir directement le résultat dans le Tableau source par filtre car ici le TCD ne fait pas grand chose !

 

[Akortys]

Re : TCD : faire des calculs en fonctions de valeurs et compter nbre d'occurences

Merci à tous pour vos indications.

@JCGL : Encore merci

@Chris : Merci pour l'info mais ma feuille Global comportera par la suite d'autres informations donc pour une meilleure lisibilité le TCD est ce qui est le mieux car il n'affiche que les informations souhaitées.

@Misange : Merci pour la technique de création d'une plage de référence plus rapidement que ce que je faisais par le gestionnaire de noms.
Par contre cela ne résoud pas mon pb sur le calcul de la note. C'est à ce niveau que je souhaitais ne pas indiquer de limite.
A ce jour, la formule est =NB.SI(C7:C23;"A")*3+NB.SI(C7:C23;"B")*2+NB.SI(C7:C23;"C")*1
ce qui implique que si le TCD est plus bas que la ligne C23 le calcul sera erroné.
C'est à ce niveau que je souhaitais intervenir.

Si vous avez une idée pour le calcul, je suis preneur.
Mais en attendant encore un grand merci à tous.
Cordialement

 

[chris]

Re : TCD : faire des calculs en fonctions de valeurs et compter nbre d'occurences

Bonjour

J'ai pris la peine de modifier la formule au dessus du TCD dans la pièce jointe et de la reporter dans mon post : merci de prendre cellle de lire nos réponses un peu plus qu'en diagonale...

 

[Akortys]

Re : TCD : faire des calculs en fonctions de valeurs et compter nbre d'occurences

@Chris : C'est exact que c'est indiqué mais à un détail prêt c'est que je n'arrive pas a obtenir les sous-totaux de ma feuille global, donc je ne peux pas me baser dessus pour effectuer mon calcul de note.
Dans la feuille global, j'ai fait :
Clic droit - table - ligne des sous-totaux
pour la note de l'exercice 1 j'ai mis la formule :
=SOMMEPROD(SOUS.TOTAL(42;DECALER(AP$1;LIGNE(INDIRECT("1:"&LIGNES([Note du TH1])));0))*([Note du TH1]="A")*3)+SOMMEPROD(SOUS.TOTAL(42;DECALER(AP$1;LIGNE(INDIRECT("1:"&LIGNES([Note du TH1])));0))*([Note du TH1]="B")*2)+SOMMEPROD(SOUS.TOTAL(42;DECALER(AP$1;LIGNE(INDIRECT("1:"&LIGNES([Note du TH1])));0))*([Note du TH1]="C")*1)

42 étant la colonne de ma note de l'exercice 1
AP la colonne de l'exercice 1
Note du TH1 le libellé

mais j'ai : #VALEUR!

C'est pour cela que je n'ai pas été plus loin.

 

[Dugenou]

Re : TCD : faire des calculs en fonctions de valeurs et compter nbre d'occurences

Bonjour Akortis, bises Misange et Chris,

D'accord avec Chris : le TCD ne sert pas à grand chose.
Une proposition sans TCD avec formules dont une matricielle
Cordialement

 

[Akortys]

Re : TCD : faire des calculs en fonctions de valeurs et compter nbre d'occurences

Bonjour Dugenou,

Merci c'est nickel. cela me permettra de ne pas avoir ces fameux filtres qui se baladent en plein milieu d'une cellule. De plus, faire appel à une feuille de paramétrage c'est pas mal également.

Pour autant, je suis bloqué au même endroit que pour la solution de chris, c.â.d que je n'arrive pas à obtenir les sous-totaux dans ma feuille Global, donc je ne peux pas avancer bcp plus.

Merci.

 

[chris]

Re : TCD : faire des calculs en fonctions de valeurs et compter nbre d'occurences

Re

@Chris : C'est exact que c'est indiqué mais à un détail prêt c'est que je n'arrive pas a obtenir les sous-totaux de ma feuille global, donc je ne peux pas me baser dessus pour effectuer mon calcul de note.
Dans la feuille global, j'ai fait :
Clic droit - table - ligne des sous-totaux
pour la note de l'exercice 1 j'ai mis la formule :
=SOMMEPROD(SOUS.TOTAL(42;DECALER(AP$1;LIGNE(INDIRECT("1:"&LIGNES([Note du TH1])));0))*([Note du TH1]="A")*3)+SOMMEPROD(SOUS.TOTAL(42;DECALER(AP$1;LIGNE(INDIRECT("1:"&LIGNES([Note du TH1])));0))*([Note du TH1]="B")*2)+SOMMEPROD(SOUS.TOTAL(42;DECALER(AP$1;LIGNE(INDIRECT("1:"&LIGNES([Note du TH1])));0))*([Note du TH1]="C")*1)

42 étant la colonne de ma note de l'exercice 1
AP la colonne de l'exercice 1
Note du TH1 le libellé

mais j'ai : #VALEUR!

C'est pour cela que je n'ai pas été plus loin.

Je te parle des formules que j'ai placé au dessus du TCD pas de celles du tableau source !

Il y avait deux solutions différentes dans mon exemple.

Edit : tu peux désactiver l'affichage des filtres dans le TCD... c'est une option des TCD

Edit2 : SOUS.TOTAL(3 et pas 42 : 3 est le type de calcul fait pas la fonction sous-total pas une référence

 

[Dugenou]

Re : TCD : faire des calculs en fonctions de valeurs et compter nbre d'occurences

Re,
même type de réponse : je ne comprends pourquoi tu as de besoin de sous totaux dans le tableau de données. La note est calculé dans le tableau final (au dessus du tableau.
A te lire

 

[Akortys]

Re : TCD : faire des calculs en fonctions de valeurs et compter nbre d'occurences

hallelujah, hallelujah, hallelujah...

Merci Chris.

Je n'avais pas capté qu'il y avait solutions différentes. Pour moi elles étaient liées.
Me reste à tester la synthèse sans le besoin de faire un TCD mais en attendant c'est du tout bon.

Encore merci à tous.