Effectuer des Statistiques sur des valeurs en relation avec un numéro de trimestres

[Mister Binaire]

Bonsoir le forum,

Voila en colonne B (voir fichier en PJ) une fonction me renvoie le trimestre correspondant à une date en colonne A
Je voudrais trouver une fonction qui me calcule des stats par trimestre: moyenne, écart type, médiane, mini, maxi, nombre de valeurs pour les valeurs se trouvant en colonne C correspondantes au trimestre.

J'ai préparé dans le fichier le résultat final que je désirerais obtenir colonne G2 à L5.
Bien évidemment le nombre de valeurs peut être différent d'un trimestre à un autre .

Merci de votre aide...

 

[excfl]

Re : Effectuer des Statistiques sur des valeurs en relation avec un numéro de trimest

Bonsoir le forum,

Remarque : pour simplifier (il est minuit !)
colonne E : j'ai mis TR
colonne F : j'ai mis 1 à 4

G3 : =SIERREUR(SOMME.SI(B:B;F3;C:C)/NB.SI(B:B;F3);"")

H3 : =SIERREUR(MEDIANE(SI($B$2:$B$100=F3;$C$2:$C$100));"")
Formule matricielle

etc ...

Et maintenant :

excfl

 

[R@chid]

Re : Effectuer des Statistiques sur des valeurs en relation avec un numéro de trimest

Bonsoir @ tous,
Sans la colonne B
pour les moyennes, en G3,

=MOYENNE(SI(ENT((MOIS(A$2:A$21)-1)/3+1)=LIGNES($3:3);C$2:C$21))

@ valider par Ctrl+Shift+Enter
@ tirer vers le bas

Adapter pour les autres calculs.


@ + +

 

[Mister Binaire]

Re : Effectuer des Statistiques sur des valeurs en relation avec un numéro de trimest

Bonjour R@chid, Excfl,

Merci à vous deux de votre aide .

La formule de R@chid me plaisait bien car elle ne prend pas en compte les trimestres de la colonne B mais malheureusement elle ne fonctionne pas.
La moyenne est calculée sur l'intégralité des valeurs de la colonne C le distinguo dès trimestres n'est pas effectué.

Merci de votre aide!!

 

[excfl]

Re : Effectuer des Statistiques sur des valeurs en relation avec un numéro de trimest

Bonjour,

La formule de R@chid me plaisait bien car elle ne prend pas en compte les trimestres de la colonne B mais malheureusement elle ne fonctionne pas.
La moyenne est calculée sur l'intégralité des valeurs de la colonne C le distinguo dès trimestres n'est pas effectué.

La formule indiquée par R@chid fonctionne parfaitement.

Les deux solutions proposées donnent les mêmes et bons résultats.

excfl

coucou chris

 

[R@chid]

Re : Effectuer des Statistiques sur des valeurs en relation avec un numéro de trimest

Bonsoir @ tous,
salut excfl
pour que ma formule fonctionne bien, il faut la valider par Ctrl+Shift+Enter c'est une formule matricielle.

@ + +

 

[Mister Binaire]

Re : Effectuer des Statistiques sur des valeurs en relation avec un numéro de trimest

Merci de ta réponse R@chid !!

Autant pour moi, je n'avais pas observé que c'était une formule matricielle.

Ce qui m’embête un peu dans cette forme Matricielle c'est que la matrice est construite sur une plage bien précise et que mon utilisateur va rentrer ses données tous les jours. je me vois mal refaire la matrice au quotidien.

Ce que je voulais c'est que les stats s'actualisent par trimestre au fur et à mesure que les datas sont rentrés sans aucune intervention de ma part une fois le fichier créé !!

Si une personne à une piste ??

Merci de votre aide !!

 

[chris]

Re : Effectuer des Statistiques sur des valeurs en relation avec un numéro de trimest

Bonjour
Coucou R@chid , excfl

Deux solutions basées sur un tableau en source :

• les formules matricielles de excfl (sauf la moyenne faite avec MOYENNE.SI)
• par TCD avec une colonne supplémentaire
  On pourrait supprimer le colonne B et modifier la formule en D car un TCD sait faire des trimestre à partir de dates
  

  =MEDIANE(SI(ARRONDI.SUP(MOIS([@DATES])/3;0)=ARRONDI.SUP(MOIS([DATES])/3;0);[RUPL]))

Pour l'une ou l'autre solution, avantage du tableau en source : les formules ou le TCD tiendront automatiquement compte des ajouts, les formules des colonnes B et D se recopieront automatiquement dès l'ajout d'une ligne...

 

[R@chid]

Re : Effectuer des Statistiques sur des valeurs en relation avec un numéro de trimest

Bonsoir @ tous,
Bonsoir le papillon, je ne sais pas si on peut dire "La papillonne "

Merci de ta réponse R@chid !!

Autant pour moi, je n'avais pas observé que c'était une formule matricielle.

Ce qui m’embête un peu dans cette forme Matricielle c'est que la matrice est construite sur une plage bien précise et que mon utilisateur va rentrer ses données tous les jours. je me vois mal refaire la matrice au quotidien.

Ce que je voulais c'est que les stats s'actualisent par trimestre au fur et à mesure que les datas sont rentrés sans aucune intervention de ma part une fois le fichier créé !!

Si une personne à une piste ??

Merci de votre aide !!

Tu peux faire avec des champs dynamiques nommés.

@ + +

 

[Mister Binaire]

Re : Effectuer des Statistiques sur des valeurs en relation avec un numéro de trimest

Bonjour Chris, R@chid, Excfl, le Forum,

Merci à vous de votre aide !!

J'achète le TCD de Chris !!

Cependant, je suis un peu embêté avec la formule qui détermine le trimestre "Colonne B" en effet quand nous allons passer au premier janvier 2015 la formule va dégager à nouveau 1 ce qui va fausser les stats de l'année 2014 puisque nous seront en 2015 ce que je voudrais c'est que la formule me dégage 1-2014, 2-2014 3-2014, 4-2014, 1-2015 etc etc ..

Pensez-vous également qu'il est possible de mettre la formule de Chris pour la médiane en Champs calculé dans le TCD ?

Car je vais avoir beaucoup d'attributs en colonne : RUPL, RUPT, ELOL, ELOT, etc etc.. et cela m'embête un peu d'avoir le calcul de la médiane à coté des noms des attributs

Merci à vous de votre aide bien précieuse !!

 

[chris]

Re : Effectuer des Statistiques sur des valeurs en relation avec un numéro de trimest

Bonjour

On peut enlever la colonne trimestre et faire un TCD par année et trimestre.

Par contre comme dans le fichier que j'ai fourni en aucun cas la taille du tableau ne doit dépasser son contenu sinon tu perds 90% de l'intérêt du tableau.

La médiane ne fait pas partie des calculs possible en TCD.

Si tu veux utiliser un TCD il vaut mieux mettre une colonne Attibut avec RUPL, ELOL etc et une colonne valeur avec la valeur de l'attribut sinon ce sera inexploitable ou du moins très difficilement.

Par ailleurs il faudra en conséquence modifier la formule de médiane selon la période et l'attibut...

Comme souvent, l'exemple n'était pas représentatif et on doit recommencer.

J'attends donc un exemple vraiment représentatif...

 

[Mister Binaire]

Re : Effectuer des Statistiques sur des valeurs en relation avec un numéro de trimest

Bonjour Chris,

J'ai fait le TCD par année et trimestre comme tu m'as dit j'ai juste gardé la colonne trimestre à titre indicatif.(Onglet Statistiques)
J'ai mis en forme l'Onglet "Data Base" pour que tu vois approximativement comment les données vont être saisies.
Je ne veux pas que la colonne de la médiane soit à coté du nom de la variable de peur que l'utilisateur rentre par erreur les data dans cette colonne.
C'est ce calcul de la mediane pour chaque attribut puis son incorporation dans le TCD qui me pose problème.

Si tu as une piste, je suis preneur !!

le fichier modifié est en PJ.

merci de l'aide que tu m'apportes .

 

[chris]

Re : Effectuer des Statistiques sur des valeurs en relation avec un numéro de trimest

RE

As-tu mes commentaires ?

Toujours un tableau plus long que nécessaire...

On peut déplacer la colonne médiane à gauche.

Mais si tu gardes cette disposition, on ne peut plus rien faire : on calcule alors la médiane sur quoi ? RUPT, ELOL, ELOT... ?

Et il faudra autant de champs de valeurs dans le TCD que de cas : comme je l'ai dit, difficilement gérable...

J'ai déjà proposé une autre piste : une colonne attribut et une colonne valeur, et pas autant de colonnes que d'attributs différents.

 

[Mister Binaire]

Re : Effectuer des Statistiques sur des valeurs en relation avec un numéro de trimest

Merci de ta réponse Christ.

Mais je ne vois pas bien concrètement la dispo que tu veux me parler avec une seule colonne pour les attributs et une autre pour les valeurs.

Comment l'utilisateur va faire le distinguo sur les attributs; sur un trimestre nous pouvons avoir 10 valeurs sur les attributs et sur un autre trimestre le double.

Si tu peux me concrétiser ton idée dans le fichier Excel .

Merci de ton aide..

 

[chris]

Re : Effectuer des Statistiques sur des valeurs en relation avec un numéro de trimest

Re

Il y aurait une colonne date, une colonne Attribut, une colonne valeur (ce que tu as actuellement sous le nom de RUPL et la colonne de calcul de la médiane placée à gauche ou droite peu importe.

Il faut donc saisir autant de lignes de de valeurs pour chaque attribut :

Si le 1/04 tu as 10 lignes de RUPL Comme dans ton exemple, tu auras aussi peut-être n lignes pour le ELOL, x lignes pour le ELOT, etc.

Bref autant de lignes qu'on a de valeurs pour une date et un attribut.

C'est le TCD qui synthétise.