Ecart type avec critères
Je souhaite calculer l'écart type d'un indice en conditionnant par les dates (sur une année seulement, par trimestre...).
Peut-être sauriez-vous m'aider à la construire par une matricielle ou bien avec =BDECARTYPE (je n'y suis pas arrivé).
Vous trouverez ci-joint le fichier contenant les données et les tableaux à remplir. J'ai aussi détaillé le calcul de l'écart type pour faciliter sa compréhension.
Merci beaucoup ! c'est parfait
Peux-tu rapidement m'expliquer le principe de la forme matricielle et comment je l'implémente ?
Ok,
pour l'année:
=ECARTYPE.STANDARD(SI(ANNEE($A$3:$A$337)=$H5;$B$3:$B$337))on met la condition SI(Annee(Plage des dates)=année inscrite en cellule H5, on va ainsi appliquer la formule de l'écart-type uniquement sur l'année notée en H5.
*********************************************************************************************************************
Pour les trimestres, il faut inclure les mois en plus de l'année, il faut donc faire un ET logique, or si cela fonctionne bien pour l'année, cela ne fonctionne pas pour les mois, il faut faire un autre type de ET logique, c'est l'astérisque (*) qui fera le lien entre les deux:
SI((ANNEE($A$3:$A$337)=$G11)*(MOIS($A$3:$A$337)<=3), dans ce cas, si l'année en G11=2022 et le mois est inférieur ou égal à 3 (les 3 premiers mois de l'année), le calcul ne s'appliquera que si ces 2 conditions sont réunies.
Pour les autres trimestres, on insère une 3ème condition.
=SIERREUR(ECARTYPE.STANDARD(SI((ANNEE($A$3:$A$337)=$G12)*(MOIS($A$3:$A$337)>3)*(MOIS($A$3:$A$337)<=6);$B$3:$B$337));""), dans ce cas si les mois sont > à 3 et <= à 6, alors le calcul se fera sur les mois d'avril à juin inclus.
S'il y a une erreur dans le calcul (exemple en 2023, l'année est incomplète), on ne retourne rien.
J'ai limité la formule à la ligne 337, vous pouvez augmenter cette valeur si votre base de données doit s'agrandir.
Une formule matricielle, se valide avec les touches combinées CTRL + SHIFT + ENTREE (pour votre version d'excel ce n'est peut-être pas nécessaire, mais je n'en suis pas sûr) et étire la formule sur les autres cellules.
Cdlt
Merci beaucoup pour vos explications, j'en apprends beaucoup!
J'ai un petit problème avec la formule : lorsqu'elle n'est pas sous forme matricielle elle fonctionne mais dès que je l'active sous forme matricielle elle me mets #VALEUR!. Savez-vous pourquoi ?
lorsqu'elle n'est pas sous forme matricielle elle fonctionne mais dès que je l'active sous forme matricielle elle me mets #VALEUR!. Savez-vous pourquoi ?
j'avais préciser ceci:
Une formule matricielle, se valide avec les touches combinées CTRL + SHIFT + ENTREE (pour votre version d'excel ce n'est peut-être pas nécessaire, mais je n'en suis pas sûr) et étire la formule sur les autres cellules.
Donc c'est qu'il n'y a pas besoin de valider avec CTRL + SHIFT + ENTREE
Je viens de comprendre pourquoi mais je n'arrive pas à le résoudre :
J'ai nommé la colonne A (toute la colonne) comme étant "Dates"; j'ai nommé la colonne B comme étant "SX5E 50 Perf"
La formule avant était : =ECARTYPE.STANDARD(SI(ANNEE($A$3:$A$337)=$H5;$B$3:$B$337))
Elle devient : =ECARTYPE.STANDARD(SI(ANNEE(Dates)=$H5;SX5E_50_Perf))
Et lorsque je mets le format matricielle j'obtiens #VALEUR!
Je l'avais bien lu mais je vois que si je ne mets pas la forme matricielle la valeur change et n'est pas bonne (1,15% au lieu de 1,26%)
Après avoir lu le post précédent, ne déclarez pas un colonne entière en plage, limitez-les:
Dates=Feuil1!$A3:$A100000
$H5;SX5E_50_Perf=Feuil1!$B3:$B100000
le problème vient du fait que vous prenez la ligne d'entête qui ne contient pas de valeurs numériques
D'accord très bien. Merci beaucoup
Sélectionner toute la colonne (donc prise en compte de l'en-tête) et sélectionner que les valeurs (en allant jusqu'à la dernière cellule de la feuille Excel) sont des méthodes équivalentes ?
Est-ce que si un jour j'ai un nombre élevé de données et que la plage (la colonne) a été nommée en ne prenant pas toute la colonne, ça peut me poser un problème de valeur non prise en compte un jour (parce que les valeurs ne s'inséreront plus dans la plage nommée) ?
Sélectionner toute la colonne (donc prise en compte de l'en-tête) et sélectionner que les valeurs (en allant jusqu'à la dernière cellule de la feuille Excel) sont des méthodes équivalentes ? NON, comme écrit précédemment, l'entête étant non numérique, cela vient perturber les calculs.
Est-ce que si un jour j'ai un nombre élevé de données et que la plage (la colonne) a été nommée en ne prenant pas toute la colonne, ça peut me poser un problème de valeur non prise en compte un jour (parce que les valeurs ne s'inséreront plus dans la plage nommée) ? Prenez une plage suffisamment grande pour ne plus être embêté.
Mais la meilleure option est de sélectionner l'ensemble de vos données plage A3:B337 et de convertir en tableau structuré, ainsi plus de problème, les formules s'adapteront automatiquement à la longueur des plages.
Le fichier avec le tableau convertit en tableau structuré