Un dernier commentaire :
ce n'est pas parce qu'on obtient une valeur qu'elle a la moindre
signification.
Tu prends un exemple avec 5 valeurs. Fais le avec 6 en mettant comme 6°
couple 10,200 et regarde ce que donne ton calcul. Tu choisis la valeur
la plus jolie qui t'arrange si tu veux mais tu n'appelles plus cela des
maths par pitié.
Une interpolation ne se fait pas sur la moyenne des valeurs mais sur la
moyenne des bornes encadrant la valeur manquante ce qui est EXTREMEMENT
différent. C'est un peu de ma faute : par souci de simplification, j'ai
pris des couples de valeurs qui s'alignent justement le long d'une
droite. Il aurait mieux valu que je donne comme exemple des valeurs du genre
1 15
5 25
8 30
13 48
20 62
40 98
pente : 2.13
je fais sauter 48 et 62 la pente devient 2.11
j'utilise ta formule magique, la pente est de 1,5 !!
Plus drole : je fais sauter 25 et 62, La vraie pente est 2.1. Avec ta
formule ... 4.9 !
tu prends quoi au juste ? Je te conseille 4.9 si tu as envie de montrer
que la production progresse de façon spectaculaire cette année. MAis
attention si l'année prochaine elle tombe = 0.6 on t'en voudra...
Pour reprendre ton exemple des températures. La correction ne se fait
pas dans le tableau mais au moment ou on entre les valeurs dans le cas
que tu cite. Le 15 du mois, quand tu fais ta mesure à midi
15/10/2008 12:00==> 0°
15/10/2008 12:05 ==> 21°
ou bien tu écris dans ton tableau seulement
15/10/2008 12:00==> 21°C
La correction est donc faite au moment de la saisie.
ou bien tu écris les deux couples de valeurs (ce que tu fais
actuellement) et tu supprimes le premier.
Mais en aucun cas tu ne dois écrire les deux et effacer la valeur 0°
sans enlever la date et l'heure correspondante.
Je te propose une solution qui devrait te satisfaire : une résolution
graphique. Au lieu de récupérer les paramètres de ta droite en utilisant
une formule, trace un graphique en nuages de points (c'est impératif,
pas en courbe). Tu fais un clic droit sur ta série, ajouter une courbe
de tendance. La tu choisis linéaire et dans les options tu coches
afficher l'équation sur le graphique et fficher le coéfficient de
régression.
Si maintenant dans ton tableau de valeurs, il manque des y, la droite ne
sera tracée qu'avec les valeurs "complètes" c'est à dire avec un comple
x,y. (Et oui, excel aime bien aussi les maths qui sont des maths... :-) )
Tu verras comment les valeurs manquantes influent ou pas sur la droite.
Le coeff de régression te permet de voir si tes valeurs sont ou non
réellement reliées de façon linéaire. LE coeff de régression varie de 0
à 1. Il faut savoir que les fonctions de droite de régression renvoient
TOUJOURS une valeur, quelles que soient les données entrées mais si le
coeff est du genre <0.6 tu peux être sur qu'en utilsant l'équantion de
la droite pour faire une prévision, celle ci a toutes les chances d'être
aussi juste que celle de Mme Irma dans sa caravane à la foire à la tomate...
Pour le reste, je te laisse à tes "calculs"
Pour ce qui est de la mesure, je suis biologiste. Les mesures c'est mon
métier et travailler sur du matériel biologique implique d'avoir une
variation importante dans les données et d'avoir une anlayse critique de
ces données. Les statistiques sont exactement faites pour savoir comment
interpréter des résultats qui par nature varient et savoir si on a le
droit ou non de conclure.
Que ce soit des températures, des relevés de production mensuelle, des
choux ou des centenaires, peu importe. Ou bien il y a une relation
mathématiques qui a un sens qui relie ces données et dans ce cas, il
faut appliquer correctement cette fonction, ou bien il n'y en a pas et
dans ce cas, il ne faut pas faire croire qu'on peut utiliser une
fonction mathématique pour prédire l'avenir. Beaucoup d'économistes s'y
sont d'ailleurs cassé les dents.
Misange migrateuse
XlWiki : Participez à un travail collaboratif sur excel !
http://xlwiki.free.fr/wiki
http://www.excelabo.net
smr78 a écrit :
> Bonjour,
> Ah, je vois que ça chauffe dans les stats. Bonne nouvelle, j'ai trouvé une
> formule qui me satisfait et fera un bon compromis.
> Comme je n'ai pas de formule pour éliminer les valeurs nulles ou inférieures
> à une seuil, j'ai réfléchi (!) et fait appel à Interpol comme le suggérait
> François.
>
> Au lieu de supprimer les valeurs nulles, je les remplace par une valeur
> moyenne des valeurs non nulles, automatiquement par formule. C'est illégal me
> direz-vous encore mais j'y reviendrai.
>
> Je prends comme exemple
> en colonne A 1,2,3,4,5 (pour les X) en colonne B 25,0,0,55,60
>
> n.b., je passe à 5 valeurs car mettre deux valeurs à 0 parmi 4, embrouille
> tout.
>
> ma première formule magique est :
> = {DROITEREG(N((B1:B5)=0)*MOYENNE(B1:B5)+(B1:B5))}
> là-dedans,
> N((B1:B5)=0 génère la matrice 0,1,1,0,0
> N((B1:B5)=0)*MOYENNE(B1:B5) génère une matrice 0, 28, 28, 0, 0 où les
> valeurs nulles sont remplacées par la moyenne des 3 valeurs non nulles (mais
> basée sur 5 mesures)
> et il suffit ensuite d'ajouter la matrice initiale pour obtenir 25, 28, 28,
> 55, 60
>
> ma deuxième formule magique veut faire pareil, mais à la place de la moyenne
> des 3 valeurs non nulles moyennées sur 5, calcule la moyenne sur 3 mesures!!
>
> =
> {DROITEREG(N((B1:B5)=0)*SOMME(N(B1:B5>0)*(B1:B5)/NB.SI(B1:B5;">0"))+(B1:B5))}
>
> Si l'on veut remplacer les valeurs inférieures à un seuil situé en C1, on
> remplace
> =0 par <=$C$1, >0 par >$C$1 et ">0" par ... ">" & $C$1
>
> Et je reviens têtument sur mon thermomètre :
> - supposons que l'on relève la température ou pression atmosphérique tous
> les jours à midi ; si comme dans certains films on voit que la personne
> lisant la mesure à midi le 15 du mois, doute du thermomètre/baromètre, tapote
> dessus et relève une "bonne" mesure à midi et 5 secondes et inscrit en plus
> de la mesure fausse 0°, la "bonne" mesure relevée à midi 5 secondes, je pense
> qu'il n'y aura pas de crime mathématique à éliminer les températures égales à
> 0°, sachant que le film se déroule au niveau de l'équateur, n'est-il pas?
> et l'on peut très bien, pour établir la courbe des températures du mois,
> considérer que la deuxième "bonne" mesure doit être datée du 15 du mois et
> non pas du 16.
>
> J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok, c'est de
> la mesure et non pas de la statistique théorique.
>
> Cela ne s'applique pas qu'aux températures, mon vrai problème concerne un
> relevé de production mensuelle, donc pas besoin de corriger les dates non
> plus.
>
> Bon, je m'y connais aussi en statistiques et analyses de marché et je
> suis... Auvergnat!
>
> Ah, oui reste le coup du 25,0,0,50, deux valeurs nulles sur 4, et bien on
> peut mettre n'importe quoi comme valeur de remplacement des 0 : 25,10,10, 50
> ou 25, 30, 30, 50 on obtient toujours la même pente, seule change l'ordonnée
> à l'origine.
Alors là !!! compare la pente de tes deux droites et tu m'en diras des
nouvelles. Fais ça par exemple avec des x du genre 1, 2,8, 24 au lieu de
1, 2, 3, 4 et tu verras que c'est complètement faux. Si ta formule
magique te donne la même valeur alors c'est qu'elle est surtout
magiquement fausse.
Je ne peux pas ne pas réagir, désolée si je te parais puriste mais
laisser ce genre de conclusion archivée dans google sans mettre en garde
un futur lecteur sur l'absence de validité de cette approche... je ne
peux pas. Ce n'est bien entendu pas du tout dirigé contre toi en tant
qu'individu ! Chacun sa spécialité et tu es surement meilleur que moi en
études de marché (et en choux auvergnats) :-)
>
> Encore merci pour vos tuyaux.
Un dernier commentaire :
ce n'est pas parce qu'on obtient une valeur qu'elle a la moindre
signification.
Tu prends un exemple avec 5 valeurs. Fais le avec 6 en mettant comme 6°
couple 10,200 et regarde ce que donne ton calcul. Tu choisis la valeur
la plus jolie qui t'arrange si tu veux mais tu n'appelles plus cela des
maths par pitié.
Une interpolation ne se fait pas sur la moyenne des valeurs mais sur la
moyenne des bornes encadrant la valeur manquante ce qui est EXTREMEMENT
différent. C'est un peu de ma faute : par souci de simplification, j'ai
pris des couples de valeurs qui s'alignent justement le long d'une
droite. Il aurait mieux valu que je donne comme exemple des valeurs du genre
1 15
5 25
8 30
13 48
20 62
40 98
pente : 2.13
je fais sauter 48 et 62 la pente devient 2.11
j'utilise ta formule magique, la pente est de 1,5 !!
Plus drole : je fais sauter 25 et 62, La vraie pente est 2.1. Avec ta
formule ... 4.9 !
tu prends quoi au juste ? Je te conseille 4.9 si tu as envie de montrer
que la production progresse de façon spectaculaire cette année. MAis
attention si l'année prochaine elle tombe = 0.6 on t'en voudra...
Pour reprendre ton exemple des températures. La correction ne se fait
pas dans le tableau mais au moment ou on entre les valeurs dans le cas
que tu cite. Le 15 du mois, quand tu fais ta mesure à midi
15/10/2008 12:00==> 0°
15/10/2008 12:05 ==> 21°
ou bien tu écris dans ton tableau seulement
15/10/2008 12:00==> 21°C
La correction est donc faite au moment de la saisie.
ou bien tu écris les deux couples de valeurs (ce que tu fais
actuellement) et tu supprimes le premier.
Mais en aucun cas tu ne dois écrire les deux et effacer la valeur 0°
sans enlever la date et l'heure correspondante.
Je te propose une solution qui devrait te satisfaire : une résolution
graphique. Au lieu de récupérer les paramètres de ta droite en utilisant
une formule, trace un graphique en nuages de points (c'est impératif,
pas en courbe). Tu fais un clic droit sur ta série, ajouter une courbe
de tendance. La tu choisis linéaire et dans les options tu coches
afficher l'équation sur le graphique et fficher le coéfficient de
régression.
Si maintenant dans ton tableau de valeurs, il manque des y, la droite ne
sera tracée qu'avec les valeurs "complètes" c'est à dire avec un comple
x,y. (Et oui, excel aime bien aussi les maths qui sont des maths... :-) )
Tu verras comment les valeurs manquantes influent ou pas sur la droite.
Le coeff de régression te permet de voir si tes valeurs sont ou non
réellement reliées de façon linéaire. LE coeff de régression varie de 0
à 1. Il faut savoir que les fonctions de droite de régression renvoient
TOUJOURS une valeur, quelles que soient les données entrées mais si le
coeff est du genre <0.6 tu peux être sur qu'en utilsant l'équantion de
la droite pour faire une prévision, celle ci a toutes les chances d'être
aussi juste que celle de Mme Irma dans sa caravane à la foire à la tomate...
Pour le reste, je te laisse à tes "calculs"
Pour ce qui est de la mesure, je suis biologiste. Les mesures c'est mon
métier et travailler sur du matériel biologique implique d'avoir une
variation importante dans les données et d'avoir une anlayse critique de
ces données. Les statistiques sont exactement faites pour savoir comment
interpréter des résultats qui par nature varient et savoir si on a le
droit ou non de conclure.
Que ce soit des températures, des relevés de production mensuelle, des
choux ou des centenaires, peu importe. Ou bien il y a une relation
mathématiques qui a un sens qui relie ces données et dans ce cas, il
faut appliquer correctement cette fonction, ou bien il n'y en a pas et
dans ce cas, il ne faut pas faire croire qu'on peut utiliser une
fonction mathématique pour prédire l'avenir. Beaucoup d'économistes s'y
sont d'ailleurs cassé les dents.
Misange migrateuse
XlWiki : Participez à un travail collaboratif sur excel !
http://xlwiki.free.fr/wiki
http://www.excelabo.net
smr78 a écrit :
> Bonjour,
> Ah, je vois que ça chauffe dans les stats. Bonne nouvelle, j'ai trouvé une
> formule qui me satisfait et fera un bon compromis.
> Comme je n'ai pas de formule pour éliminer les valeurs nulles ou inférieures
> à une seuil, j'ai réfléchi (!) et fait appel à Interpol comme le suggérait
> François.
>
> Au lieu de supprimer les valeurs nulles, je les remplace par une valeur
> moyenne des valeurs non nulles, automatiquement par formule. C'est illégal me
> direz-vous encore mais j'y reviendrai.
>
> Je prends comme exemple
> en colonne A 1,2,3,4,5 (pour les X) en colonne B 25,0,0,55,60
>
> n.b., je passe à 5 valeurs car mettre deux valeurs à 0 parmi 4, embrouille
> tout.
>
> ma première formule magique est :
> = {DROITEREG(N((B1:B5)=0)*MOYENNE(B1:B5)+(B1:B5))}
> là-dedans,
> N((B1:B5)=0 génère la matrice 0,1,1,0,0
> N((B1:B5)=0)*MOYENNE(B1:B5) génère une matrice 0, 28, 28, 0, 0 où les
> valeurs nulles sont remplacées par la moyenne des 3 valeurs non nulles (mais
> basée sur 5 mesures)
> et il suffit ensuite d'ajouter la matrice initiale pour obtenir 25, 28, 28,
> 55, 60
>
> ma deuxième formule magique veut faire pareil, mais à la place de la moyenne
> des 3 valeurs non nulles moyennées sur 5, calcule la moyenne sur 3 mesures!!
>
> =
> {DROITEREG(N((B1:B5)=0)*SOMME(N(B1:B5>0)*(B1:B5)/NB.SI(B1:B5;">0"))+(B1:B5))}
>
> Si l'on veut remplacer les valeurs inférieures à un seuil situé en C1, on
> remplace
> =0 par <=$C$1, >0 par >$C$1 et ">0" par ... ">" & $C$1
>
> Et je reviens têtument sur mon thermomètre :
> - supposons que l'on relève la température ou pression atmosphérique tous
> les jours à midi ; si comme dans certains films on voit que la personne
> lisant la mesure à midi le 15 du mois, doute du thermomètre/baromètre, tapote
> dessus et relève une "bonne" mesure à midi et 5 secondes et inscrit en plus
> de la mesure fausse 0°, la "bonne" mesure relevée à midi 5 secondes, je pense
> qu'il n'y aura pas de crime mathématique à éliminer les températures égales à
> 0°, sachant que le film se déroule au niveau de l'équateur, n'est-il pas?
> et l'on peut très bien, pour établir la courbe des températures du mois,
> considérer que la deuxième "bonne" mesure doit être datée du 15 du mois et
> non pas du 16.
>
> J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok, c'est de
> la mesure et non pas de la statistique théorique.
>
> Cela ne s'applique pas qu'aux températures, mon vrai problème concerne un
> relevé de production mensuelle, donc pas besoin de corriger les dates non
> plus.
>
> Bon, je m'y connais aussi en statistiques et analyses de marché et je
> suis... Auvergnat!
>
> Ah, oui reste le coup du 25,0,0,50, deux valeurs nulles sur 4, et bien on
> peut mettre n'importe quoi comme valeur de remplacement des 0 : 25,10,10, 50
> ou 25, 30, 30, 50 on obtient toujours la même pente, seule change l'ordonnée
> à l'origine.
Alors là !!! compare la pente de tes deux droites et tu m'en diras des
nouvelles. Fais ça par exemple avec des x du genre 1, 2,8, 24 au lieu de
1, 2, 3, 4 et tu verras que c'est complètement faux. Si ta formule
magique te donne la même valeur alors c'est qu'elle est surtout
magiquement fausse.
Je ne peux pas ne pas réagir, désolée si je te parais puriste mais
laisser ce genre de conclusion archivée dans google sans mettre en garde
un futur lecteur sur l'absence de validité de cette approche... je ne
peux pas. Ce n'est bien entendu pas du tout dirigé contre toi en tant
qu'individu ! Chacun sa spécialité et tu es surement meilleur que moi en
études de marché (et en choux auvergnats) :-)
>
> Encore merci pour vos tuyaux.
Un dernier commentaire :
ce n'est pas parce qu'on obtient une valeur qu'elle a la moindre
signification.
Tu prends un exemple avec 5 valeurs. Fais le avec 6 en mettant comme 6°
couple 10,200 et regarde ce que donne ton calcul. Tu choisis la valeur
la plus jolie qui t'arrange si tu veux mais tu n'appelles plus cela des
maths par pitié.
Une interpolation ne se fait pas sur la moyenne des valeurs mais sur la
moyenne des bornes encadrant la valeur manquante ce qui est EXTREMEMENT
différent. C'est un peu de ma faute : par souci de simplification, j'ai
pris des couples de valeurs qui s'alignent justement le long d'une
droite. Il aurait mieux valu que je donne comme exemple des valeurs du genre
1 15
5 25
8 30
13 48
20 62
40 98
pente : 2.13
je fais sauter 48 et 62 la pente devient 2.11
j'utilise ta formule magique, la pente est de 1,5 !!
Plus drole : je fais sauter 25 et 62, La vraie pente est 2.1. Avec ta
formule ... 4.9 !
tu prends quoi au juste ? Je te conseille 4.9 si tu as envie de montrer
que la production progresse de façon spectaculaire cette année. MAis
attention si l'année prochaine elle tombe = 0.6 on t'en voudra...
Pour reprendre ton exemple des températures. La correction ne se fait
pas dans le tableau mais au moment ou on entre les valeurs dans le cas
que tu cite. Le 15 du mois, quand tu fais ta mesure à midi
15/10/2008 12:00==> 0°
15/10/2008 12:05 ==> 21°
ou bien tu écris dans ton tableau seulement
15/10/2008 12:00==> 21°C
La correction est donc faite au moment de la saisie.
ou bien tu écris les deux couples de valeurs (ce que tu fais
actuellement) et tu supprimes le premier.
Mais en aucun cas tu ne dois écrire les deux et effacer la valeur 0°
sans enlever la date et l'heure correspondante.
Je te propose une solution qui devrait te satisfaire : une résolution
graphique. Au lieu de récupérer les paramètres de ta droite en utilisant
une formule, trace un graphique en nuages de points (c'est impératif,
pas en courbe). Tu fais un clic droit sur ta série, ajouter une courbe
de tendance. La tu choisis linéaire et dans les options tu coches
afficher l'équation sur le graphique et fficher le coéfficient de
régression.
Si maintenant dans ton tableau de valeurs, il manque des y, la droite ne
sera tracée qu'avec les valeurs "complètes" c'est à dire avec un comple
x,y. (Et oui, excel aime bien aussi les maths qui sont des maths... :-) )
Tu verras comment les valeurs manquantes influent ou pas sur la droite.
Le coeff de régression te permet de voir si tes valeurs sont ou non
réellement reliées de façon linéaire. LE coeff de régression varie de 0
à 1. Il faut savoir que les fonctions de droite de régression renvoient
TOUJOURS une valeur, quelles que soient les données entrées mais si le
coeff est du genre <0.6 tu peux être sur qu'en utilsant l'équantion de
la droite pour faire une prévision, celle ci a toutes les chances d'être
aussi juste que celle de Mme Irma dans sa caravane à la foire à la tomate...
Pour le reste, je te laisse à tes "calculs"
Pour ce qui est de la mesure, je suis biologiste. Les mesures c'est mon
métier et travailler sur du matériel biologique implique d'avoir une
variation importante dans les données et d'avoir une anlayse critique de
ces données. Les statistiques sont exactement faites pour savoir comment
interpréter des résultats qui par nature varient et savoir si on a le
droit ou non de conclure.
Que ce soit des températures, des relevés de production mensuelle, des
choux ou des centenaires, peu importe. Ou bien il y a une relation
mathématiques qui a un sens qui relie ces données et dans ce cas, il
faut appliquer correctement cette fonction, ou bien il n'y en a pas et
dans ce cas, il ne faut pas faire croire qu'on peut utiliser une
fonction mathématique pour prédire l'avenir. Beaucoup d'économistes s'y
sont d'ailleurs cassé les dents.
Misange migrateuse
XlWiki : Participez à un travail collaboratif sur excel !
http://xlwiki.free.fr/wiki
http://www.excelabo.net
smr78 a écrit :
> Bonjour,
> Ah, je vois que ça chauffe dans les stats. Bonne nouvelle, j'ai trouvé une
> formule qui me satisfait et fera un bon compromis.
> Comme je n'ai pas de formule pour éliminer les valeurs nulles ou inférieures
> à une seuil, j'ai réfléchi (!) et fait appel à Interpol comme le suggérait
> François.
>
> Au lieu de supprimer les valeurs nulles, je les remplace par une valeur
> moyenne des valeurs non nulles, automatiquement par formule. C'est illégal me
> direz-vous encore mais j'y reviendrai.
>
> Je prends comme exemple
> en colonne A 1,2,3,4,5 (pour les X) en colonne B 25,0,0,55,60
>
> n.b., je passe à 5 valeurs car mettre deux valeurs à 0 parmi 4, embrouille
> tout.
>
> ma première formule magique est :
> = {DROITEREG(N((B1:B5)=0)*MOYENNE(B1:B5)+(B1:B5))}
> là-dedans,
> N((B1:B5)=0 génère la matrice 0,1,1,0,0
> N((B1:B5)=0)*MOYENNE(B1:B5) génère une matrice 0, 28, 28, 0, 0 où les
> valeurs nulles sont remplacées par la moyenne des 3 valeurs non nulles (mais
> basée sur 5 mesures)
> et il suffit ensuite d'ajouter la matrice initiale pour obtenir 25, 28, 28,
> 55, 60
>
> ma deuxième formule magique veut faire pareil, mais à la place de la moyenne
> des 3 valeurs non nulles moyennées sur 5, calcule la moyenne sur 3 mesures!!
>
> =
> {DROITEREG(N((B1:B5)=0)*SOMME(N(B1:B5>0)*(B1:B5)/NB.SI(B1:B5;">0"))+(B1:B5))}
>
> Si l'on veut remplacer les valeurs inférieures à un seuil situé en C1, on
> remplace
> =0 par <=$C$1, >0 par >$C$1 et ">0" par ... ">" & $C$1
>
> Et je reviens têtument sur mon thermomètre :
> - supposons que l'on relève la température ou pression atmosphérique tous
> les jours à midi ; si comme dans certains films on voit que la personne
> lisant la mesure à midi le 15 du mois, doute du thermomètre/baromètre, tapote
> dessus et relève une "bonne" mesure à midi et 5 secondes et inscrit en plus
> de la mesure fausse 0°, la "bonne" mesure relevée à midi 5 secondes, je pense
> qu'il n'y aura pas de crime mathématique à éliminer les températures égales à
> 0°, sachant que le film se déroule au niveau de l'équateur, n'est-il pas?
> et l'on peut très bien, pour établir la courbe des températures du mois,
> considérer que la deuxième "bonne" mesure doit être datée du 15 du mois et
> non pas du 16.
>
> J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok, c'est de
> la mesure et non pas de la statistique théorique.
>
> Cela ne s'applique pas qu'aux températures, mon vrai problème concerne un
> relevé de production mensuelle, donc pas besoin de corriger les dates non
> plus.
>
> Bon, je m'y connais aussi en statistiques et analyses de marché et je
> suis... Auvergnat!
>
> Ah, oui reste le coup du 25,0,0,50, deux valeurs nulles sur 4, et bien on
> peut mettre n'importe quoi comme valeur de remplacement des 0 : 25,10,10, 50
> ou 25, 30, 30, 50 on obtient toujours la même pente, seule change l'ordonnée
> à l'origine.
Alors là !!! compare la pente de tes deux droites et tu m'en diras des
nouvelles. Fais ça par exemple avec des x du genre 1, 2,8, 24 au lieu de
1, 2, 3, 4 et tu verras que c'est complètement faux. Si ta formule
magique te donne la même valeur alors c'est qu'elle est surtout
magiquement fausse.
Je ne peux pas ne pas réagir, désolée si je te parais puriste mais
laisser ce genre de conclusion archivée dans google sans mettre en garde
un futur lecteur sur l'absence de validité de cette approche... je ne
peux pas. Ce n'est bien entendu pas du tout dirigé contre toi en tant
qu'individu ! Chacun sa spécialité et tu es surement meilleur que moi en
études de marché (et en choux auvergnats) :-)
>
> Encore merci pour vos tuyaux.
Bonjour Misange,
Je ne discute pas pour dire que j'ai raison mais parce que la discussion
m'enrichit (et d'autres j'espère).
- Une première remarque, mes X connus ont un intervalle de progression
constant (comme les dates du jour), on peut les assimiler à 1,2,3.. et c'est
ce que fait DROITEREG quand ils sont omis. Mais je ne pense pas que cela
influe.
- Bien sûr j'ai fait le graphique de mes données et ajouté les courbes de
tendances et les résultats donnent des pentes différentes et j'ai choisi
celles qui me "plaisaient" le plus. Il est vrai que selon la position des
valeurs nulles, les résultats varient beaucoup avec DROITEREG et moins avec
les miens (surprenant oui à interprêter mais ça me va).
- J'ai essayé de mettre des valeurs vides au lieu de valeurs nulles ; eh
bien la formule DROITEREG (B1:B6) coince et donne #valeur, alors que la
courbe de tendance est bien tracée et que l'équation sur le graphique
apparaît avec des valeurs, est-ce parce que les valeurs calculées débordent
de la plage? (Excel 2003)
- J'aurais bien voulu faire l'extrapolation sur les deux valeurs encadrant
la valeur manquante, mais je ne sais pas faire par formule.
- Dans mon exemple température, je suis d'accord sur le fait que la mauvaise
valeur devrait être supprimée à la saisie, mais pas possible car la saisie
est faite, disons automatiquement à des milliers de kilomètres d'où se trouve
mon amie (débutante, débutante en Excel) qui reçoit des centaines de
résultat, pas moyen de modifier la saisie, ni de lire les données une par une
pour corriger à la main, d'où ma question sur comment l'enlever au moment de
l'analyse? J'ai essayé et proposé de faire un filtre élaboré, mais ça fait
une étape de plus qui gêne. (Quand je dis enlever, c'est effectivement
enlever le couple date et valeur pour les valeurs "fausses").
Merci encore pour tous ces conseils bio-éclairés.
Cordialement
"Misange" a écrit :Un dernier commentaire :
ce n'est pas parce qu'on obtient une valeur qu'elle a la moindre
signification.
Tu prends un exemple avec 5 valeurs. Fais le avec 6 en mettant comme 6°
couple 10,200 et regarde ce que donne ton calcul. Tu choisis la valeur
la plus jolie qui t'arrange si tu veux mais tu n'appelles plus cela des
maths par pitié.
Une interpolation ne se fait pas sur la moyenne des valeurs mais sur la
moyenne des bornes encadrant la valeur manquante ce qui est EXTREMEMENT
différent. C'est un peu de ma faute : par souci de simplification, j'ai
pris des couples de valeurs qui s'alignent justement le long d'une
droite. Il aurait mieux valu que je donne comme exemple des valeurs du genre
1 15
5 25
8 30
13 48
20 62
40 98
pente : 2.13
je fais sauter 48 et 62 la pente devient 2.11
j'utilise ta formule magique, la pente est de 1,5 !!
Plus drole : je fais sauter 25 et 62, La vraie pente est 2.1. Avec ta
formule ... 4.9 !
tu prends quoi au juste ? Je te conseille 4.9 si tu as envie de montrer
que la production progresse de façon spectaculaire cette année. MAis
attention si l'année prochaine elle tombe = 0.6 on t'en voudra...
Pour reprendre ton exemple des températures. La correction ne se fait
pas dans le tableau mais au moment ou on entre les valeurs dans le cas
que tu cite. Le 15 du mois, quand tu fais ta mesure à midi
15/10/2008 12:00==> 0°
15/10/2008 12:05 ==> 21°
ou bien tu écris dans ton tableau seulement
15/10/2008 12:00==> 21°C
La correction est donc faite au moment de la saisie.
ou bien tu écris les deux couples de valeurs (ce que tu fais
actuellement) et tu supprimes le premier.
Mais en aucun cas tu ne dois écrire les deux et effacer la valeur 0°
sans enlever la date et l'heure correspondante.
Je te propose une solution qui devrait te satisfaire : une résolution
graphique. Au lieu de récupérer les paramètres de ta droite en utilisant
une formule, trace un graphique en nuages de points (c'est impératif,
pas en courbe). Tu fais un clic droit sur ta série, ajouter une courbe
de tendance. La tu choisis linéaire et dans les options tu coches
afficher l'équation sur le graphique et fficher le coéfficient de
régression.
Si maintenant dans ton tableau de valeurs, il manque des y, la droite ne
sera tracée qu'avec les valeurs "complètes" c'est à dire avec un comple
x,y. (Et oui, excel aime bien aussi les maths qui sont des maths... :-) )
Tu verras comment les valeurs manquantes influent ou pas sur la droite.
Le coeff de régression te permet de voir si tes valeurs sont ou non
réellement reliées de façon linéaire. LE coeff de régression varie de 0
à 1. Il faut savoir que les fonctions de droite de régression renvoient
TOUJOURS une valeur, quelles que soient les données entrées mais si le
coeff est du genre <0.6 tu peux être sur qu'en utilsant l'équantion de
la droite pour faire une prévision, celle ci a toutes les chances d'être
aussi juste que celle de Mme Irma dans sa caravane à la foire à la tomate...
Pour le reste, je te laisse à tes "calculs"
Pour ce qui est de la mesure, je suis biologiste. Les mesures c'est mon
métier et travailler sur du matériel biologique implique d'avoir une
variation importante dans les données et d'avoir une anlayse critique de
ces données. Les statistiques sont exactement faites pour savoir comment
interpréter des résultats qui par nature varient et savoir si on a le
droit ou non de conclure.
Que ce soit des températures, des relevés de production mensuelle, des
choux ou des centenaires, peu importe. Ou bien il y a une relation
mathématiques qui a un sens qui relie ces données et dans ce cas, il
faut appliquer correctement cette fonction, ou bien il n'y en a pas et
dans ce cas, il ne faut pas faire croire qu'on peut utiliser une
fonction mathématique pour prédire l'avenir. Beaucoup d'économistes s'y
sont d'ailleurs cassé les dents.
Misange migrateuse
XlWiki : Participez à un travail collaboratif sur excel !
http://xlwiki.free.fr/wiki
http://www.excelabo.net
smr78 a écrit :Bonjour,
Ah, je vois que ça chauffe dans les stats. Bonne nouvelle, j'ai trouvé une
formule qui me satisfait et fera un bon compromis.
Comme je n'ai pas de formule pour éliminer les valeurs nulles ou inférieures
à une seuil, j'ai réfléchi (!) et fait appel à Interpol comme le suggérait
François.
Au lieu de supprimer les valeurs nulles, je les remplace par une valeur
moyenne des valeurs non nulles, automatiquement par formule. C'est illégal me
direz-vous encore mais j'y reviendrai.
Je prends comme exemple
en colonne A 1,2,3,4,5 (pour les X) en colonne B 25,0,0,55,60
n.b., je passe à 5 valeurs car mettre deux valeurs à 0 parmi 4, embrouille
tout.
ma première formule magique est :
= {DROITEREG(N((B1:B5)=0)*MOYENNE(B1:B5)+(B1:B5))}
là-dedans,
N((B1:B5)=0 génère la matrice 0,1,1,0,0
N((B1:B5)=0)*MOYENNE(B1:B5) génère une matrice 0, 28, 28, 0, 0 où les
valeurs nulles sont remplacées par la moyenne des 3 valeurs non nulles (mais
basée sur 5 mesures)
et il suffit ensuite d'ajouter la matrice initiale pour obtenir 25, 28, 28,
55, 60
ma deuxième formule magique veut faire pareil, mais à la place de la moyenne
des 3 valeurs non nulles moyennées sur 5, calcule la moyenne sur 3 mesures!!
=
{DROITEREG(N((B1:B5)=0)*SOMME(N(B1:B5>0)*(B1:B5)/NB.SI(B1:B5;">0"))+(B1:B5))}
Si l'on veut remplacer les valeurs inférieures à un seuil situé en C1, on
remplace
=0 par <=$C$1, >0 par >$C$1 et ">0" par ... ">" & $C$1
Et je reviens têtument sur mon thermomètre :
- supposons que l'on relève la température ou pression atmosphérique tous
les jours à midi ; si comme dans certains films on voit que la personne
lisant la mesure à midi le 15 du mois, doute du thermomètre/baromètre, tapote
dessus et relève une "bonne" mesure à midi et 5 secondes et inscrit en plus
de la mesure fausse 0°, la "bonne" mesure relevée à midi 5 secondes, je pense
qu'il n'y aura pas de crime mathématique à éliminer les températures égales à
0°, sachant que le film se déroule au niveau de l'équateur, n'est-il pas?
et l'on peut très bien, pour établir la courbe des températures du mois,
considérer que la deuxième "bonne" mesure doit être datée du 15 du mois et
non pas du 16.
J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok, c'est de
la mesure et non pas de la statistique théorique.
Cela ne s'applique pas qu'aux températures, mon vrai problème concerne un
relevé de production mensuelle, donc pas besoin de corriger les dates non
plus.
Bon, je m'y connais aussi en statistiques et analyses de marché et je
suis... Auvergnat!
Ah, oui reste le coup du 25,0,0,50, deux valeurs nulles sur 4, et bien on
peut mettre n'importe quoi comme valeur de remplacement des 0 : 25,10,10, 50
ou 25, 30, 30, 50 on obtient toujours la même pente, seule change l'ordonnée
à l'origine.
Alors là !!! compare la pente de tes deux droites et tu m'en diras des
nouvelles. Fais ça par exemple avec des x du genre 1, 2,8, 24 au lieu de
1, 2, 3, 4 et tu verras que c'est complètement faux. Si ta formule
magique te donne la même valeur alors c'est qu'elle est surtout
magiquement fausse.
Je ne peux pas ne pas réagir, désolée si je te parais puriste mais
laisser ce genre de conclusion archivée dans google sans mettre en garde
un futur lecteur sur l'absence de validité de cette approche... je ne
peux pas. Ce n'est bien entendu pas du tout dirigé contre toi en tant
qu'individu ! Chacun sa spécialité et tu es surement meilleur que moi en
études de marché (et en choux auvergnats) :-)Encore merci pour vos tuyaux.
Bonjour Misange,
Je ne discute pas pour dire que j'ai raison mais parce que la discussion
m'enrichit (et d'autres j'espère).
- Une première remarque, mes X connus ont un intervalle de progression
constant (comme les dates du jour), on peut les assimiler à 1,2,3.. et c'est
ce que fait DROITEREG quand ils sont omis. Mais je ne pense pas que cela
influe.
- Bien sûr j'ai fait le graphique de mes données et ajouté les courbes de
tendances et les résultats donnent des pentes différentes et j'ai choisi
celles qui me "plaisaient" le plus. Il est vrai que selon la position des
valeurs nulles, les résultats varient beaucoup avec DROITEREG et moins avec
les miens (surprenant oui à interprêter mais ça me va).
- J'ai essayé de mettre des valeurs vides au lieu de valeurs nulles ; eh
bien la formule DROITEREG (B1:B6) coince et donne #valeur, alors que la
courbe de tendance est bien tracée et que l'équation sur le graphique
apparaît avec des valeurs, est-ce parce que les valeurs calculées débordent
de la plage? (Excel 2003)
- J'aurais bien voulu faire l'extrapolation sur les deux valeurs encadrant
la valeur manquante, mais je ne sais pas faire par formule.
- Dans mon exemple température, je suis d'accord sur le fait que la mauvaise
valeur devrait être supprimée à la saisie, mais pas possible car la saisie
est faite, disons automatiquement à des milliers de kilomètres d'où se trouve
mon amie (débutante, débutante en Excel) qui reçoit des centaines de
résultat, pas moyen de modifier la saisie, ni de lire les données une par une
pour corriger à la main, d'où ma question sur comment l'enlever au moment de
l'analyse? J'ai essayé et proposé de faire un filtre élaboré, mais ça fait
une étape de plus qui gêne. (Quand je dis enlever, c'est effectivement
enlever le couple date et valeur pour les valeurs "fausses").
Merci encore pour tous ces conseils bio-éclairés.
Cordialement
"Misange" a écrit :
Un dernier commentaire :
ce n'est pas parce qu'on obtient une valeur qu'elle a la moindre
signification.
Tu prends un exemple avec 5 valeurs. Fais le avec 6 en mettant comme 6°
couple 10,200 et regarde ce que donne ton calcul. Tu choisis la valeur
la plus jolie qui t'arrange si tu veux mais tu n'appelles plus cela des
maths par pitié.
Une interpolation ne se fait pas sur la moyenne des valeurs mais sur la
moyenne des bornes encadrant la valeur manquante ce qui est EXTREMEMENT
différent. C'est un peu de ma faute : par souci de simplification, j'ai
pris des couples de valeurs qui s'alignent justement le long d'une
droite. Il aurait mieux valu que je donne comme exemple des valeurs du genre
1 15
5 25
8 30
13 48
20 62
40 98
pente : 2.13
je fais sauter 48 et 62 la pente devient 2.11
j'utilise ta formule magique, la pente est de 1,5 !!
Plus drole : je fais sauter 25 et 62, La vraie pente est 2.1. Avec ta
formule ... 4.9 !
tu prends quoi au juste ? Je te conseille 4.9 si tu as envie de montrer
que la production progresse de façon spectaculaire cette année. MAis
attention si l'année prochaine elle tombe = 0.6 on t'en voudra...
Pour reprendre ton exemple des températures. La correction ne se fait
pas dans le tableau mais au moment ou on entre les valeurs dans le cas
que tu cite. Le 15 du mois, quand tu fais ta mesure à midi
15/10/2008 12:00==> 0°
15/10/2008 12:05 ==> 21°
ou bien tu écris dans ton tableau seulement
15/10/2008 12:00==> 21°C
La correction est donc faite au moment de la saisie.
ou bien tu écris les deux couples de valeurs (ce que tu fais
actuellement) et tu supprimes le premier.
Mais en aucun cas tu ne dois écrire les deux et effacer la valeur 0°
sans enlever la date et l'heure correspondante.
Je te propose une solution qui devrait te satisfaire : une résolution
graphique. Au lieu de récupérer les paramètres de ta droite en utilisant
une formule, trace un graphique en nuages de points (c'est impératif,
pas en courbe). Tu fais un clic droit sur ta série, ajouter une courbe
de tendance. La tu choisis linéaire et dans les options tu coches
afficher l'équation sur le graphique et fficher le coéfficient de
régression.
Si maintenant dans ton tableau de valeurs, il manque des y, la droite ne
sera tracée qu'avec les valeurs "complètes" c'est à dire avec un comple
x,y. (Et oui, excel aime bien aussi les maths qui sont des maths... :-) )
Tu verras comment les valeurs manquantes influent ou pas sur la droite.
Le coeff de régression te permet de voir si tes valeurs sont ou non
réellement reliées de façon linéaire. LE coeff de régression varie de 0
à 1. Il faut savoir que les fonctions de droite de régression renvoient
TOUJOURS une valeur, quelles que soient les données entrées mais si le
coeff est du genre <0.6 tu peux être sur qu'en utilsant l'équantion de
la droite pour faire une prévision, celle ci a toutes les chances d'être
aussi juste que celle de Mme Irma dans sa caravane à la foire à la tomate...
Pour le reste, je te laisse à tes "calculs"
Pour ce qui est de la mesure, je suis biologiste. Les mesures c'est mon
métier et travailler sur du matériel biologique implique d'avoir une
variation importante dans les données et d'avoir une anlayse critique de
ces données. Les statistiques sont exactement faites pour savoir comment
interpréter des résultats qui par nature varient et savoir si on a le
droit ou non de conclure.
Que ce soit des températures, des relevés de production mensuelle, des
choux ou des centenaires, peu importe. Ou bien il y a une relation
mathématiques qui a un sens qui relie ces données et dans ce cas, il
faut appliquer correctement cette fonction, ou bien il n'y en a pas et
dans ce cas, il ne faut pas faire croire qu'on peut utiliser une
fonction mathématique pour prédire l'avenir. Beaucoup d'économistes s'y
sont d'ailleurs cassé les dents.
Misange migrateuse
XlWiki : Participez à un travail collaboratif sur excel !
http://xlwiki.free.fr/wiki
http://www.excelabo.net
smr78 a écrit :
Bonjour,
Ah, je vois que ça chauffe dans les stats. Bonne nouvelle, j'ai trouvé une
formule qui me satisfait et fera un bon compromis.
Comme je n'ai pas de formule pour éliminer les valeurs nulles ou inférieures
à une seuil, j'ai réfléchi (!) et fait appel à Interpol comme le suggérait
François.
Au lieu de supprimer les valeurs nulles, je les remplace par une valeur
moyenne des valeurs non nulles, automatiquement par formule. C'est illégal me
direz-vous encore mais j'y reviendrai.
Je prends comme exemple
en colonne A 1,2,3,4,5 (pour les X) en colonne B 25,0,0,55,60
n.b., je passe à 5 valeurs car mettre deux valeurs à 0 parmi 4, embrouille
tout.
ma première formule magique est :
= {DROITEREG(N((B1:B5)=0)*MOYENNE(B1:B5)+(B1:B5))}
là-dedans,
N((B1:B5)=0 génère la matrice 0,1,1,0,0
N((B1:B5)=0)*MOYENNE(B1:B5) génère une matrice 0, 28, 28, 0, 0 où les
valeurs nulles sont remplacées par la moyenne des 3 valeurs non nulles (mais
basée sur 5 mesures)
et il suffit ensuite d'ajouter la matrice initiale pour obtenir 25, 28, 28,
55, 60
ma deuxième formule magique veut faire pareil, mais à la place de la moyenne
des 3 valeurs non nulles moyennées sur 5, calcule la moyenne sur 3 mesures!!
=
{DROITEREG(N((B1:B5)=0)*SOMME(N(B1:B5>0)*(B1:B5)/NB.SI(B1:B5;">0"))+(B1:B5))}
Si l'on veut remplacer les valeurs inférieures à un seuil situé en C1, on
remplace
=0 par <=$C$1, >0 par >$C$1 et ">0" par ... ">" & $C$1
Et je reviens têtument sur mon thermomètre :
- supposons que l'on relève la température ou pression atmosphérique tous
les jours à midi ; si comme dans certains films on voit que la personne
lisant la mesure à midi le 15 du mois, doute du thermomètre/baromètre, tapote
dessus et relève une "bonne" mesure à midi et 5 secondes et inscrit en plus
de la mesure fausse 0°, la "bonne" mesure relevée à midi 5 secondes, je pense
qu'il n'y aura pas de crime mathématique à éliminer les températures égales à
0°, sachant que le film se déroule au niveau de l'équateur, n'est-il pas?
et l'on peut très bien, pour établir la courbe des températures du mois,
considérer que la deuxième "bonne" mesure doit être datée du 15 du mois et
non pas du 16.
J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok, c'est de
la mesure et non pas de la statistique théorique.
Cela ne s'applique pas qu'aux températures, mon vrai problème concerne un
relevé de production mensuelle, donc pas besoin de corriger les dates non
plus.
Bon, je m'y connais aussi en statistiques et analyses de marché et je
suis... Auvergnat!
Ah, oui reste le coup du 25,0,0,50, deux valeurs nulles sur 4, et bien on
peut mettre n'importe quoi comme valeur de remplacement des 0 : 25,10,10, 50
ou 25, 30, 30, 50 on obtient toujours la même pente, seule change l'ordonnée
à l'origine.
Alors là !!! compare la pente de tes deux droites et tu m'en diras des
nouvelles. Fais ça par exemple avec des x du genre 1, 2,8, 24 au lieu de
1, 2, 3, 4 et tu verras que c'est complètement faux. Si ta formule
magique te donne la même valeur alors c'est qu'elle est surtout
magiquement fausse.
Je ne peux pas ne pas réagir, désolée si je te parais puriste mais
laisser ce genre de conclusion archivée dans google sans mettre en garde
un futur lecteur sur l'absence de validité de cette approche... je ne
peux pas. Ce n'est bien entendu pas du tout dirigé contre toi en tant
qu'individu ! Chacun sa spécialité et tu es surement meilleur que moi en
études de marché (et en choux auvergnats) :-)
Encore merci pour vos tuyaux.
Bonjour Misange,
Je ne discute pas pour dire que j'ai raison mais parce que la discussion
m'enrichit (et d'autres j'espère).
- Une première remarque, mes X connus ont un intervalle de progression
constant (comme les dates du jour), on peut les assimiler à 1,2,3.. et c'est
ce que fait DROITEREG quand ils sont omis. Mais je ne pense pas que cela
influe.
- Bien sûr j'ai fait le graphique de mes données et ajouté les courbes de
tendances et les résultats donnent des pentes différentes et j'ai choisi
celles qui me "plaisaient" le plus. Il est vrai que selon la position des
valeurs nulles, les résultats varient beaucoup avec DROITEREG et moins avec
les miens (surprenant oui à interprêter mais ça me va).
- J'ai essayé de mettre des valeurs vides au lieu de valeurs nulles ; eh
bien la formule DROITEREG (B1:B6) coince et donne #valeur, alors que la
courbe de tendance est bien tracée et que l'équation sur le graphique
apparaît avec des valeurs, est-ce parce que les valeurs calculées débordent
de la plage? (Excel 2003)
- J'aurais bien voulu faire l'extrapolation sur les deux valeurs encadrant
la valeur manquante, mais je ne sais pas faire par formule.
- Dans mon exemple température, je suis d'accord sur le fait que la mauvaise
valeur devrait être supprimée à la saisie, mais pas possible car la saisie
est faite, disons automatiquement à des milliers de kilomètres d'où se trouve
mon amie (débutante, débutante en Excel) qui reçoit des centaines de
résultat, pas moyen de modifier la saisie, ni de lire les données une par une
pour corriger à la main, d'où ma question sur comment l'enlever au moment de
l'analyse? J'ai essayé et proposé de faire un filtre élaboré, mais ça fait
une étape de plus qui gêne. (Quand je dis enlever, c'est effectivement
enlever le couple date et valeur pour les valeurs "fausses").
Merci encore pour tous ces conseils bio-éclairés.
Cordialement
"Misange" a écrit :Un dernier commentaire :
ce n'est pas parce qu'on obtient une valeur qu'elle a la moindre
signification.
Tu prends un exemple avec 5 valeurs. Fais le avec 6 en mettant comme 6°
couple 10,200 et regarde ce que donne ton calcul. Tu choisis la valeur
la plus jolie qui t'arrange si tu veux mais tu n'appelles plus cela des
maths par pitié.
Une interpolation ne se fait pas sur la moyenne des valeurs mais sur la
moyenne des bornes encadrant la valeur manquante ce qui est EXTREMEMENT
différent. C'est un peu de ma faute : par souci de simplification, j'ai
pris des couples de valeurs qui s'alignent justement le long d'une
droite. Il aurait mieux valu que je donne comme exemple des valeurs du genre
1 15
5 25
8 30
13 48
20 62
40 98
pente : 2.13
je fais sauter 48 et 62 la pente devient 2.11
j'utilise ta formule magique, la pente est de 1,5 !!
Plus drole : je fais sauter 25 et 62, La vraie pente est 2.1. Avec ta
formule ... 4.9 !
tu prends quoi au juste ? Je te conseille 4.9 si tu as envie de montrer
que la production progresse de façon spectaculaire cette année. MAis
attention si l'année prochaine elle tombe = 0.6 on t'en voudra...
Pour reprendre ton exemple des températures. La correction ne se fait
pas dans le tableau mais au moment ou on entre les valeurs dans le cas
que tu cite. Le 15 du mois, quand tu fais ta mesure à midi
15/10/2008 12:00==> 0°
15/10/2008 12:05 ==> 21°
ou bien tu écris dans ton tableau seulement
15/10/2008 12:00==> 21°C
La correction est donc faite au moment de la saisie.
ou bien tu écris les deux couples de valeurs (ce que tu fais
actuellement) et tu supprimes le premier.
Mais en aucun cas tu ne dois écrire les deux et effacer la valeur 0°
sans enlever la date et l'heure correspondante.
Je te propose une solution qui devrait te satisfaire : une résolution
graphique. Au lieu de récupérer les paramètres de ta droite en utilisant
une formule, trace un graphique en nuages de points (c'est impératif,
pas en courbe). Tu fais un clic droit sur ta série, ajouter une courbe
de tendance. La tu choisis linéaire et dans les options tu coches
afficher l'équation sur le graphique et fficher le coéfficient de
régression.
Si maintenant dans ton tableau de valeurs, il manque des y, la droite ne
sera tracée qu'avec les valeurs "complètes" c'est à dire avec un comple
x,y. (Et oui, excel aime bien aussi les maths qui sont des maths... :-) )
Tu verras comment les valeurs manquantes influent ou pas sur la droite.
Le coeff de régression te permet de voir si tes valeurs sont ou non
réellement reliées de façon linéaire. LE coeff de régression varie de 0
à 1. Il faut savoir que les fonctions de droite de régression renvoient
TOUJOURS une valeur, quelles que soient les données entrées mais si le
coeff est du genre <0.6 tu peux être sur qu'en utilsant l'équantion de
la droite pour faire une prévision, celle ci a toutes les chances d'être
aussi juste que celle de Mme Irma dans sa caravane à la foire à la tomate...
Pour le reste, je te laisse à tes "calculs"
Pour ce qui est de la mesure, je suis biologiste. Les mesures c'est mon
métier et travailler sur du matériel biologique implique d'avoir une
variation importante dans les données et d'avoir une anlayse critique de
ces données. Les statistiques sont exactement faites pour savoir comment
interpréter des résultats qui par nature varient et savoir si on a le
droit ou non de conclure.
Que ce soit des températures, des relevés de production mensuelle, des
choux ou des centenaires, peu importe. Ou bien il y a une relation
mathématiques qui a un sens qui relie ces données et dans ce cas, il
faut appliquer correctement cette fonction, ou bien il n'y en a pas et
dans ce cas, il ne faut pas faire croire qu'on peut utiliser une
fonction mathématique pour prédire l'avenir. Beaucoup d'économistes s'y
sont d'ailleurs cassé les dents.
Misange migrateuse
XlWiki : Participez à un travail collaboratif sur excel !
http://xlwiki.free.fr/wiki
http://www.excelabo.net
smr78 a écrit :Bonjour,
Ah, je vois que ça chauffe dans les stats. Bonne nouvelle, j'ai trouvé une
formule qui me satisfait et fera un bon compromis.
Comme je n'ai pas de formule pour éliminer les valeurs nulles ou inférieures
à une seuil, j'ai réfléchi (!) et fait appel à Interpol comme le suggérait
François.
Au lieu de supprimer les valeurs nulles, je les remplace par une valeur
moyenne des valeurs non nulles, automatiquement par formule. C'est illégal me
direz-vous encore mais j'y reviendrai.
Je prends comme exemple
en colonne A 1,2,3,4,5 (pour les X) en colonne B 25,0,0,55,60
n.b., je passe à 5 valeurs car mettre deux valeurs à 0 parmi 4, embrouille
tout.
ma première formule magique est :
= {DROITEREG(N((B1:B5)=0)*MOYENNE(B1:B5)+(B1:B5))}
là-dedans,
N((B1:B5)=0 génère la matrice 0,1,1,0,0
N((B1:B5)=0)*MOYENNE(B1:B5) génère une matrice 0, 28, 28, 0, 0 où les
valeurs nulles sont remplacées par la moyenne des 3 valeurs non nulles (mais
basée sur 5 mesures)
et il suffit ensuite d'ajouter la matrice initiale pour obtenir 25, 28, 28,
55, 60
ma deuxième formule magique veut faire pareil, mais à la place de la moyenne
des 3 valeurs non nulles moyennées sur 5, calcule la moyenne sur 3 mesures!!
=
{DROITEREG(N((B1:B5)=0)*SOMME(N(B1:B5>0)*(B1:B5)/NB.SI(B1:B5;">0"))+(B1:B5))}
Si l'on veut remplacer les valeurs inférieures à un seuil situé en C1, on
remplace
=0 par <=$C$1, >0 par >$C$1 et ">0" par ... ">" & $C$1
Et je reviens têtument sur mon thermomètre :
- supposons que l'on relève la température ou pression atmosphérique tous
les jours à midi ; si comme dans certains films on voit que la personne
lisant la mesure à midi le 15 du mois, doute du thermomètre/baromètre, tapote
dessus et relève une "bonne" mesure à midi et 5 secondes et inscrit en plus
de la mesure fausse 0°, la "bonne" mesure relevée à midi 5 secondes, je pense
qu'il n'y aura pas de crime mathématique à éliminer les températures égales à
0°, sachant que le film se déroule au niveau de l'équateur, n'est-il pas?
et l'on peut très bien, pour établir la courbe des températures du mois,
considérer que la deuxième "bonne" mesure doit être datée du 15 du mois et
non pas du 16.
J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok, c'est de
la mesure et non pas de la statistique théorique.
Cela ne s'applique pas qu'aux températures, mon vrai problème concerne un
relevé de production mensuelle, donc pas besoin de corriger les dates non
plus.
Bon, je m'y connais aussi en statistiques et analyses de marché et je
suis... Auvergnat!
Ah, oui reste le coup du 25,0,0,50, deux valeurs nulles sur 4, et bien on
peut mettre n'importe quoi comme valeur de remplacement des 0 : 25,10,10, 50
ou 25, 30, 30, 50 on obtient toujours la même pente, seule change l'ordonnée
à l'origine.
Alors là !!! compare la pente de tes deux droites et tu m'en diras des
nouvelles. Fais ça par exemple avec des x du genre 1, 2,8, 24 au lieu de
1, 2, 3, 4 et tu verras que c'est complètement faux. Si ta formule
magique te donne la même valeur alors c'est qu'elle est surtout
magiquement fausse.
Je ne peux pas ne pas réagir, désolée si je te parais puriste mais
laisser ce genre de conclusion archivée dans google sans mettre en garde
un futur lecteur sur l'absence de validité de cette approche... je ne
peux pas. Ce n'est bien entendu pas du tout dirigé contre toi en tant
qu'individu ! Chacun sa spécialité et tu es surement meilleur que moi en
études de marché (et en choux auvergnats) :-)Encore merci pour vos tuyaux.
J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok,
c'est de la mesure et non pas de la statistique théorique.
Bon, je m'y connais aussi en statistiques et analyses de marché et je
suis... Auvergnat!
J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok,
c'est de la mesure et non pas de la statistique théorique.
Bon, je m'y connais aussi en statistiques et analyses de marché et je
suis... Auvergnat!
J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok,
c'est de la mesure et non pas de la statistique théorique.
Bon, je m'y connais aussi en statistiques et analyses de marché et je
suis... Auvergnat!
J'arrête là désolée. Tu fais ce que tu veux, ce sont tes données. Ta
façon de procéder est fausse, ne peut en aucun cas être justifiée par un
quelconque calcul. Ce n'est pas parce que tu ne sais pas faire quelque
chose par formule que tu as le droit d'en utiliser une autre juste parce
qu'avec celle là tu obtiens un résultat qui te convient. J'attends de
voir la tête de ta collègue quand elle devra expliquer d'où sortent ses
prévisions...
Bonne chance pour la suite mais sans moi
Misange migrateuse
XlWiki : Participez à un travail collaboratif sur excel !
http://xlwiki.free.fr/wiki
http://www.excelabo.net
smr78 a écrit :
> Bonjour Misange,
> Je ne discute pas pour dire que j'ai raison mais parce que la discussion
> m'enrichit (et d'autres j'espère).
> - Une première remarque, mes X connus ont un intervalle de progression
> constant (comme les dates du jour), on peut les assimiler à 1,2,3.. et c'est
> ce que fait DROITEREG quand ils sont omis. Mais je ne pense pas que cela
> influe.
> - Bien sûr j'ai fait le graphique de mes données et ajouté les courbes de
> tendances et les résultats donnent des pentes différentes et j'ai choisi
> celles qui me "plaisaient" le plus. Il est vrai que selon la position des
> valeurs nulles, les résultats varient beaucoup avec DROITEREG et moins avec
> les miens (surprenant oui à interprêter mais ça me va).
> - J'ai essayé de mettre des valeurs vides au lieu de valeurs nulles ; eh
> bien la formule DROITEREG (B1:B6) coince et donne #valeur, alors que la
> courbe de tendance est bien tracée et que l'équation sur le graphique
> apparaît avec des valeurs, est-ce parce que les valeurs calculées débordent
> de la plage? (Excel 2003)
> - J'aurais bien voulu faire l'extrapolation sur les deux valeurs encadrant
> la valeur manquante, mais je ne sais pas faire par formule.
> - Dans mon exemple température, je suis d'accord sur le fait que la mauvaise
> valeur devrait être supprimée à la saisie, mais pas possible car la saisie
> est faite, disons automatiquement à des milliers de kilomètres d'où se trouve
> mon amie (débutante, débutante en Excel) qui reçoit des centaines de
> résultat, pas moyen de modifier la saisie, ni de lire les données une par une
> pour corriger à la main, d'où ma question sur comment l'enlever au moment de
> l'analyse? J'ai essayé et proposé de faire un filtre élaboré, mais ça fait
> une étape de plus qui gêne. (Quand je dis enlever, c'est effectivement
> enlever le couple date et valeur pour les valeurs "fausses").
>
> Merci encore pour tous ces conseils bio-éclairés.
> Cordialement
>
>
> "Misange" a écrit :
>
>> Un dernier commentaire :
>> ce n'est pas parce qu'on obtient une valeur qu'elle a la moindre
>> signification.
>> Tu prends un exemple avec 5 valeurs. Fais le avec 6 en mettant comme 6°
>> couple 10,200 et regarde ce que donne ton calcul. Tu choisis la valeur
>> la plus jolie qui t'arrange si tu veux mais tu n'appelles plus cela des
>> maths par pitié.
>> Une interpolation ne se fait pas sur la moyenne des valeurs mais sur la
>> moyenne des bornes encadrant la valeur manquante ce qui est EXTREMEMENT
>> différent. C'est un peu de ma faute : par souci de simplification, j'ai
>> pris des couples de valeurs qui s'alignent justement le long d'une
>> droite. Il aurait mieux valu que je donne comme exemple des valeurs du genre
>> 1 15
>> 5 25
>> 8 30
>> 13 48
>> 20 62
>> 40 98
>>
>> pente : 2.13
>> je fais sauter 48 et 62 la pente devient 2.11
>> j'utilise ta formule magique, la pente est de 1,5 !!
>> Plus drole : je fais sauter 25 et 62, La vraie pente est 2.1. Avec ta
>> formule ... 4.9 !
>> tu prends quoi au juste ? Je te conseille 4.9 si tu as envie de montrer
>> que la production progresse de façon spectaculaire cette année. MAis
>> attention si l'année prochaine elle tombe = 0.6 on t'en voudra...
>>
>>
>> Pour reprendre ton exemple des températures. La correction ne se fait
>> pas dans le tableau mais au moment ou on entre les valeurs dans le cas
>> que tu cite. Le 15 du mois, quand tu fais ta mesure à midi
>> 15/10/2008 12:00==> 0°
>> 15/10/2008 12:05 ==> 21°
>>
>> ou bien tu écris dans ton tableau seulement
>> 15/10/2008 12:00==> 21°C
>> La correction est donc faite au moment de la saisie.
>>
>> ou bien tu écris les deux couples de valeurs (ce que tu fais
>> actuellement) et tu supprimes le premier.
>> Mais en aucun cas tu ne dois écrire les deux et effacer la valeur 0°
>> sans enlever la date et l'heure correspondante.
>>
>> Je te propose une solution qui devrait te satisfaire : une résolution
>> graphique. Au lieu de récupérer les paramètres de ta droite en utilisant
>> une formule, trace un graphique en nuages de points (c'est impératif,
>> pas en courbe). Tu fais un clic droit sur ta série, ajouter une courbe
>> de tendance. La tu choisis linéaire et dans les options tu coches
>> afficher l'équation sur le graphique et fficher le coéfficient de
>> régression.
>> Si maintenant dans ton tableau de valeurs, il manque des y, la droite ne
>> sera tracée qu'avec les valeurs "complètes" c'est à dire avec un comple
>> x,y. (Et oui, excel aime bien aussi les maths qui sont des maths... :-) )
>> Tu verras comment les valeurs manquantes influent ou pas sur la droite.
>> Le coeff de régression te permet de voir si tes valeurs sont ou non
>> réellement reliées de façon linéaire. LE coeff de régression varie de 0
>> à 1. Il faut savoir que les fonctions de droite de régression renvoient
>> TOUJOURS une valeur, quelles que soient les données entrées mais si le
>> coeff est du genre <0.6 tu peux être sur qu'en utilsant l'équantion de
>> la droite pour faire une prévision, celle ci a toutes les chances d'être
>> aussi juste que celle de Mme Irma dans sa caravane à la foire à la tomate...
>>
>> Pour le reste, je te laisse à tes "calculs"
>> Pour ce qui est de la mesure, je suis biologiste. Les mesures c'est mon
>> métier et travailler sur du matériel biologique implique d'avoir une
>> variation importante dans les données et d'avoir une anlayse critique de
>> ces données. Les statistiques sont exactement faites pour savoir comment
>> interpréter des résultats qui par nature varient et savoir si on a le
>> droit ou non de conclure.
>> Que ce soit des températures, des relevés de production mensuelle, des
>> choux ou des centenaires, peu importe. Ou bien il y a une relation
>> mathématiques qui a un sens qui relie ces données et dans ce cas, il
>> faut appliquer correctement cette fonction, ou bien il n'y en a pas et
>> dans ce cas, il ne faut pas faire croire qu'on peut utiliser une
>> fonction mathématique pour prédire l'avenir. Beaucoup d'économistes s'y
>> sont d'ailleurs cassé les dents.
>>
>>
>> Misange migrateuse
>> XlWiki : Participez à un travail collaboratif sur excel !
>> http://xlwiki.free.fr/wiki
>> http://www.excelabo.net
>>
>> smr78 a écrit :
>>> Bonjour,
>>> Ah, je vois que ça chauffe dans les stats. Bonne nouvelle, j'ai trouvé une
>>> formule qui me satisfait et fera un bon compromis.
>>> Comme je n'ai pas de formule pour éliminer les valeurs nulles ou inférieures
>>> à une seuil, j'ai réfléchi (!) et fait appel à Interpol comme le suggérait
>>> François.
>>>
>>> Au lieu de supprimer les valeurs nulles, je les remplace par une valeur
>>> moyenne des valeurs non nulles, automatiquement par formule. C'est illégal me
>>> direz-vous encore mais j'y reviendrai.
>>>
>>> Je prends comme exemple
>>> en colonne A 1,2,3,4,5 (pour les X) en colonne B 25,0,0,55,60
>>>
>>> n.b., je passe à 5 valeurs car mettre deux valeurs à 0 parmi 4, embrouille
>>> tout.
>>>
>>> ma première formule magique est :
>>> = {DROITEREG(N((B1:B5)=0)*MOYENNE(B1:B5)+(B1:B5))}
>>> là-dedans,
>>> N((B1:B5)=0 génère la matrice 0,1,1,0,0
>>> N((B1:B5)=0)*MOYENNE(B1:B5) génère une matrice 0, 28, 28, 0, 0 où les
>>> valeurs nulles sont remplacées par la moyenne des 3 valeurs non nulles (mais
>>> basée sur 5 mesures)
>>> et il suffit ensuite d'ajouter la matrice initiale pour obtenir 25, 28, 28,
>>> 55, 60
>>>
>>> ma deuxième formule magique veut faire pareil, mais à la place de la moyenne
>>> des 3 valeurs non nulles moyennées sur 5, calcule la moyenne sur 3 mesures!!
>>>
>>> =
>>> {DROITEREG(N((B1:B5)=0)*SOMME(N(B1:B5>0)*(B1:B5)/NB.SI(B1:B5;">0"))+(B1:B5))}
>>>
>>> Si l'on veut remplacer les valeurs inférieures à un seuil situé en C1, on
>>> remplace
>>> =0 par <=$C$1, >0 par >$C$1 et ">0" par ... ">" & $C$1
>>>
>>> Et je reviens têtument sur mon thermomètre :
>>> - supposons que l'on relève la température ou pression atmosphérique tous
>>> les jours à midi ; si comme dans certains films on voit que la personne
>>> lisant la mesure à midi le 15 du mois, doute du thermomètre/baromètre, tapote
>>> dessus et relève une "bonne" mesure à midi et 5 secondes et inscrit en plus
>>> de la mesure fausse 0°, la "bonne" mesure relevée à midi 5 secondes, je pense
>>> qu'il n'y aura pas de crime mathématique à éliminer les températures égales à
>>> 0°, sachant que le film se déroule au niveau de l'équateur, n'est-il pas?
>>> et l'on peut très bien, pour établir la courbe des températures du mois,
>>> considérer que la deuxième "bonne" mesure doit être datée du 15 du mois et
>>> non pas du 16.
>>>
>>> J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok, c'est de
>>> la mesure et non pas de la statistique théorique.
>>>
>>> Cela ne s'applique pas qu'aux températures, mon vrai problème concerne un
>>> relevé de production mensuelle, donc pas besoin de corriger les dates non
>>> plus.
>>>
>>> Bon, je m'y connais aussi en statistiques et analyses de marché et je
>>> suis... Auvergnat!
>>>
>>> Ah, oui reste le coup du 25,0,0,50, deux valeurs nulles sur 4, et bien on
>>> peut mettre n'importe quoi comme valeur de remplacement des 0 : 25,10,10, 50
>>> ou 25, 30, 30, 50 on obtient toujours la même pente, seule change l'ordonnée
>>> à l'origine.
>> Alors là !!! compare la pente de tes deux droites et tu m'en diras des
>> nouvelles. Fais ça par exemple avec des x du genre 1, 2,8, 24 au lieu de
>> 1, 2, 3, 4 et tu verras que c'est complètement faux. Si ta formule
>> magique te donne la même valeur alors c'est qu'elle est surtout
>> magiquement fausse.
>> Je ne peux pas ne pas réagir, désolée si je te parais puriste mais
>> laisser ce genre de conclusion archivée dans google sans mettre en garde
>> un futur lecteur sur l'absence de validité de cette approche... je ne
>> peux pas. Ce n'est bien entendu pas du tout dirigé contre toi en tant
>> qu'individu ! Chacun sa spécialité et tu es surement meilleur que moi en
>> études de marché (et en choux auvergnats) :-)
>>> Encore merci pour vos tuyaux.
J'arrête là désolée. Tu fais ce que tu veux, ce sont tes données. Ta
façon de procéder est fausse, ne peut en aucun cas être justifiée par un
quelconque calcul. Ce n'est pas parce que tu ne sais pas faire quelque
chose par formule que tu as le droit d'en utiliser une autre juste parce
qu'avec celle là tu obtiens un résultat qui te convient. J'attends de
voir la tête de ta collègue quand elle devra expliquer d'où sortent ses
prévisions...
Bonne chance pour la suite mais sans moi
Misange migrateuse
XlWiki : Participez à un travail collaboratif sur excel !
http://xlwiki.free.fr/wiki
http://www.excelabo.net
smr78 a écrit :
> Bonjour Misange,
> Je ne discute pas pour dire que j'ai raison mais parce que la discussion
> m'enrichit (et d'autres j'espère).
> - Une première remarque, mes X connus ont un intervalle de progression
> constant (comme les dates du jour), on peut les assimiler à 1,2,3.. et c'est
> ce que fait DROITEREG quand ils sont omis. Mais je ne pense pas que cela
> influe.
> - Bien sûr j'ai fait le graphique de mes données et ajouté les courbes de
> tendances et les résultats donnent des pentes différentes et j'ai choisi
> celles qui me "plaisaient" le plus. Il est vrai que selon la position des
> valeurs nulles, les résultats varient beaucoup avec DROITEREG et moins avec
> les miens (surprenant oui à interprêter mais ça me va).
> - J'ai essayé de mettre des valeurs vides au lieu de valeurs nulles ; eh
> bien la formule DROITEREG (B1:B6) coince et donne #valeur, alors que la
> courbe de tendance est bien tracée et que l'équation sur le graphique
> apparaît avec des valeurs, est-ce parce que les valeurs calculées débordent
> de la plage? (Excel 2003)
> - J'aurais bien voulu faire l'extrapolation sur les deux valeurs encadrant
> la valeur manquante, mais je ne sais pas faire par formule.
> - Dans mon exemple température, je suis d'accord sur le fait que la mauvaise
> valeur devrait être supprimée à la saisie, mais pas possible car la saisie
> est faite, disons automatiquement à des milliers de kilomètres d'où se trouve
> mon amie (débutante, débutante en Excel) qui reçoit des centaines de
> résultat, pas moyen de modifier la saisie, ni de lire les données une par une
> pour corriger à la main, d'où ma question sur comment l'enlever au moment de
> l'analyse? J'ai essayé et proposé de faire un filtre élaboré, mais ça fait
> une étape de plus qui gêne. (Quand je dis enlever, c'est effectivement
> enlever le couple date et valeur pour les valeurs "fausses").
>
> Merci encore pour tous ces conseils bio-éclairés.
> Cordialement
>
>
> "Misange" a écrit :
>
>> Un dernier commentaire :
>> ce n'est pas parce qu'on obtient une valeur qu'elle a la moindre
>> signification.
>> Tu prends un exemple avec 5 valeurs. Fais le avec 6 en mettant comme 6°
>> couple 10,200 et regarde ce que donne ton calcul. Tu choisis la valeur
>> la plus jolie qui t'arrange si tu veux mais tu n'appelles plus cela des
>> maths par pitié.
>> Une interpolation ne se fait pas sur la moyenne des valeurs mais sur la
>> moyenne des bornes encadrant la valeur manquante ce qui est EXTREMEMENT
>> différent. C'est un peu de ma faute : par souci de simplification, j'ai
>> pris des couples de valeurs qui s'alignent justement le long d'une
>> droite. Il aurait mieux valu que je donne comme exemple des valeurs du genre
>> 1 15
>> 5 25
>> 8 30
>> 13 48
>> 20 62
>> 40 98
>>
>> pente : 2.13
>> je fais sauter 48 et 62 la pente devient 2.11
>> j'utilise ta formule magique, la pente est de 1,5 !!
>> Plus drole : je fais sauter 25 et 62, La vraie pente est 2.1. Avec ta
>> formule ... 4.9 !
>> tu prends quoi au juste ? Je te conseille 4.9 si tu as envie de montrer
>> que la production progresse de façon spectaculaire cette année. MAis
>> attention si l'année prochaine elle tombe = 0.6 on t'en voudra...
>>
>>
>> Pour reprendre ton exemple des températures. La correction ne se fait
>> pas dans le tableau mais au moment ou on entre les valeurs dans le cas
>> que tu cite. Le 15 du mois, quand tu fais ta mesure à midi
>> 15/10/2008 12:00==> 0°
>> 15/10/2008 12:05 ==> 21°
>>
>> ou bien tu écris dans ton tableau seulement
>> 15/10/2008 12:00==> 21°C
>> La correction est donc faite au moment de la saisie.
>>
>> ou bien tu écris les deux couples de valeurs (ce que tu fais
>> actuellement) et tu supprimes le premier.
>> Mais en aucun cas tu ne dois écrire les deux et effacer la valeur 0°
>> sans enlever la date et l'heure correspondante.
>>
>> Je te propose une solution qui devrait te satisfaire : une résolution
>> graphique. Au lieu de récupérer les paramètres de ta droite en utilisant
>> une formule, trace un graphique en nuages de points (c'est impératif,
>> pas en courbe). Tu fais un clic droit sur ta série, ajouter une courbe
>> de tendance. La tu choisis linéaire et dans les options tu coches
>> afficher l'équation sur le graphique et fficher le coéfficient de
>> régression.
>> Si maintenant dans ton tableau de valeurs, il manque des y, la droite ne
>> sera tracée qu'avec les valeurs "complètes" c'est à dire avec un comple
>> x,y. (Et oui, excel aime bien aussi les maths qui sont des maths... :-) )
>> Tu verras comment les valeurs manquantes influent ou pas sur la droite.
>> Le coeff de régression te permet de voir si tes valeurs sont ou non
>> réellement reliées de façon linéaire. LE coeff de régression varie de 0
>> à 1. Il faut savoir que les fonctions de droite de régression renvoient
>> TOUJOURS une valeur, quelles que soient les données entrées mais si le
>> coeff est du genre <0.6 tu peux être sur qu'en utilsant l'équantion de
>> la droite pour faire une prévision, celle ci a toutes les chances d'être
>> aussi juste que celle de Mme Irma dans sa caravane à la foire à la tomate...
>>
>> Pour le reste, je te laisse à tes "calculs"
>> Pour ce qui est de la mesure, je suis biologiste. Les mesures c'est mon
>> métier et travailler sur du matériel biologique implique d'avoir une
>> variation importante dans les données et d'avoir une anlayse critique de
>> ces données. Les statistiques sont exactement faites pour savoir comment
>> interpréter des résultats qui par nature varient et savoir si on a le
>> droit ou non de conclure.
>> Que ce soit des températures, des relevés de production mensuelle, des
>> choux ou des centenaires, peu importe. Ou bien il y a une relation
>> mathématiques qui a un sens qui relie ces données et dans ce cas, il
>> faut appliquer correctement cette fonction, ou bien il n'y en a pas et
>> dans ce cas, il ne faut pas faire croire qu'on peut utiliser une
>> fonction mathématique pour prédire l'avenir. Beaucoup d'économistes s'y
>> sont d'ailleurs cassé les dents.
>>
>>
>> Misange migrateuse
>> XlWiki : Participez à un travail collaboratif sur excel !
>> http://xlwiki.free.fr/wiki
>> http://www.excelabo.net
>>
>> smr78 a écrit :
>>> Bonjour,
>>> Ah, je vois que ça chauffe dans les stats. Bonne nouvelle, j'ai trouvé une
>>> formule qui me satisfait et fera un bon compromis.
>>> Comme je n'ai pas de formule pour éliminer les valeurs nulles ou inférieures
>>> à une seuil, j'ai réfléchi (!) et fait appel à Interpol comme le suggérait
>>> François.
>>>
>>> Au lieu de supprimer les valeurs nulles, je les remplace par une valeur
>>> moyenne des valeurs non nulles, automatiquement par formule. C'est illégal me
>>> direz-vous encore mais j'y reviendrai.
>>>
>>> Je prends comme exemple
>>> en colonne A 1,2,3,4,5 (pour les X) en colonne B 25,0,0,55,60
>>>
>>> n.b., je passe à 5 valeurs car mettre deux valeurs à 0 parmi 4, embrouille
>>> tout.
>>>
>>> ma première formule magique est :
>>> = {DROITEREG(N((B1:B5)=0)*MOYENNE(B1:B5)+(B1:B5))}
>>> là-dedans,
>>> N((B1:B5)=0 génère la matrice 0,1,1,0,0
>>> N((B1:B5)=0)*MOYENNE(B1:B5) génère une matrice 0, 28, 28, 0, 0 où les
>>> valeurs nulles sont remplacées par la moyenne des 3 valeurs non nulles (mais
>>> basée sur 5 mesures)
>>> et il suffit ensuite d'ajouter la matrice initiale pour obtenir 25, 28, 28,
>>> 55, 60
>>>
>>> ma deuxième formule magique veut faire pareil, mais à la place de la moyenne
>>> des 3 valeurs non nulles moyennées sur 5, calcule la moyenne sur 3 mesures!!
>>>
>>> =
>>> {DROITEREG(N((B1:B5)=0)*SOMME(N(B1:B5>0)*(B1:B5)/NB.SI(B1:B5;">0"))+(B1:B5))}
>>>
>>> Si l'on veut remplacer les valeurs inférieures à un seuil situé en C1, on
>>> remplace
>>> =0 par <=$C$1, >0 par >$C$1 et ">0" par ... ">" & $C$1
>>>
>>> Et je reviens têtument sur mon thermomètre :
>>> - supposons que l'on relève la température ou pression atmosphérique tous
>>> les jours à midi ; si comme dans certains films on voit que la personne
>>> lisant la mesure à midi le 15 du mois, doute du thermomètre/baromètre, tapote
>>> dessus et relève une "bonne" mesure à midi et 5 secondes et inscrit en plus
>>> de la mesure fausse 0°, la "bonne" mesure relevée à midi 5 secondes, je pense
>>> qu'il n'y aura pas de crime mathématique à éliminer les températures égales à
>>> 0°, sachant que le film se déroule au niveau de l'équateur, n'est-il pas?
>>> et l'on peut très bien, pour établir la courbe des températures du mois,
>>> considérer que la deuxième "bonne" mesure doit être datée du 15 du mois et
>>> non pas du 16.
>>>
>>> J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok, c'est de
>>> la mesure et non pas de la statistique théorique.
>>>
>>> Cela ne s'applique pas qu'aux températures, mon vrai problème concerne un
>>> relevé de production mensuelle, donc pas besoin de corriger les dates non
>>> plus.
>>>
>>> Bon, je m'y connais aussi en statistiques et analyses de marché et je
>>> suis... Auvergnat!
>>>
>>> Ah, oui reste le coup du 25,0,0,50, deux valeurs nulles sur 4, et bien on
>>> peut mettre n'importe quoi comme valeur de remplacement des 0 : 25,10,10, 50
>>> ou 25, 30, 30, 50 on obtient toujours la même pente, seule change l'ordonnée
>>> à l'origine.
>> Alors là !!! compare la pente de tes deux droites et tu m'en diras des
>> nouvelles. Fais ça par exemple avec des x du genre 1, 2,8, 24 au lieu de
>> 1, 2, 3, 4 et tu verras que c'est complètement faux. Si ta formule
>> magique te donne la même valeur alors c'est qu'elle est surtout
>> magiquement fausse.
>> Je ne peux pas ne pas réagir, désolée si je te parais puriste mais
>> laisser ce genre de conclusion archivée dans google sans mettre en garde
>> un futur lecteur sur l'absence de validité de cette approche... je ne
>> peux pas. Ce n'est bien entendu pas du tout dirigé contre toi en tant
>> qu'individu ! Chacun sa spécialité et tu es surement meilleur que moi en
>> études de marché (et en choux auvergnats) :-)
>>> Encore merci pour vos tuyaux.
J'arrête là désolée. Tu fais ce que tu veux, ce sont tes données. Ta
façon de procéder est fausse, ne peut en aucun cas être justifiée par un
quelconque calcul. Ce n'est pas parce que tu ne sais pas faire quelque
chose par formule que tu as le droit d'en utiliser une autre juste parce
qu'avec celle là tu obtiens un résultat qui te convient. J'attends de
voir la tête de ta collègue quand elle devra expliquer d'où sortent ses
prévisions...
Bonne chance pour la suite mais sans moi
Misange migrateuse
XlWiki : Participez à un travail collaboratif sur excel !
http://xlwiki.free.fr/wiki
http://www.excelabo.net
smr78 a écrit :
> Bonjour Misange,
> Je ne discute pas pour dire que j'ai raison mais parce que la discussion
> m'enrichit (et d'autres j'espère).
> - Une première remarque, mes X connus ont un intervalle de progression
> constant (comme les dates du jour), on peut les assimiler à 1,2,3.. et c'est
> ce que fait DROITEREG quand ils sont omis. Mais je ne pense pas que cela
> influe.
> - Bien sûr j'ai fait le graphique de mes données et ajouté les courbes de
> tendances et les résultats donnent des pentes différentes et j'ai choisi
> celles qui me "plaisaient" le plus. Il est vrai que selon la position des
> valeurs nulles, les résultats varient beaucoup avec DROITEREG et moins avec
> les miens (surprenant oui à interprêter mais ça me va).
> - J'ai essayé de mettre des valeurs vides au lieu de valeurs nulles ; eh
> bien la formule DROITEREG (B1:B6) coince et donne #valeur, alors que la
> courbe de tendance est bien tracée et que l'équation sur le graphique
> apparaît avec des valeurs, est-ce parce que les valeurs calculées débordent
> de la plage? (Excel 2003)
> - J'aurais bien voulu faire l'extrapolation sur les deux valeurs encadrant
> la valeur manquante, mais je ne sais pas faire par formule.
> - Dans mon exemple température, je suis d'accord sur le fait que la mauvaise
> valeur devrait être supprimée à la saisie, mais pas possible car la saisie
> est faite, disons automatiquement à des milliers de kilomètres d'où se trouve
> mon amie (débutante, débutante en Excel) qui reçoit des centaines de
> résultat, pas moyen de modifier la saisie, ni de lire les données une par une
> pour corriger à la main, d'où ma question sur comment l'enlever au moment de
> l'analyse? J'ai essayé et proposé de faire un filtre élaboré, mais ça fait
> une étape de plus qui gêne. (Quand je dis enlever, c'est effectivement
> enlever le couple date et valeur pour les valeurs "fausses").
>
> Merci encore pour tous ces conseils bio-éclairés.
> Cordialement
>
>
> "Misange" a écrit :
>
>> Un dernier commentaire :
>> ce n'est pas parce qu'on obtient une valeur qu'elle a la moindre
>> signification.
>> Tu prends un exemple avec 5 valeurs. Fais le avec 6 en mettant comme 6°
>> couple 10,200 et regarde ce que donne ton calcul. Tu choisis la valeur
>> la plus jolie qui t'arrange si tu veux mais tu n'appelles plus cela des
>> maths par pitié.
>> Une interpolation ne se fait pas sur la moyenne des valeurs mais sur la
>> moyenne des bornes encadrant la valeur manquante ce qui est EXTREMEMENT
>> différent. C'est un peu de ma faute : par souci de simplification, j'ai
>> pris des couples de valeurs qui s'alignent justement le long d'une
>> droite. Il aurait mieux valu que je donne comme exemple des valeurs du genre
>> 1 15
>> 5 25
>> 8 30
>> 13 48
>> 20 62
>> 40 98
>>
>> pente : 2.13
>> je fais sauter 48 et 62 la pente devient 2.11
>> j'utilise ta formule magique, la pente est de 1,5 !!
>> Plus drole : je fais sauter 25 et 62, La vraie pente est 2.1. Avec ta
>> formule ... 4.9 !
>> tu prends quoi au juste ? Je te conseille 4.9 si tu as envie de montrer
>> que la production progresse de façon spectaculaire cette année. MAis
>> attention si l'année prochaine elle tombe = 0.6 on t'en voudra...
>>
>>
>> Pour reprendre ton exemple des températures. La correction ne se fait
>> pas dans le tableau mais au moment ou on entre les valeurs dans le cas
>> que tu cite. Le 15 du mois, quand tu fais ta mesure à midi
>> 15/10/2008 12:00==> 0°
>> 15/10/2008 12:05 ==> 21°
>>
>> ou bien tu écris dans ton tableau seulement
>> 15/10/2008 12:00==> 21°C
>> La correction est donc faite au moment de la saisie.
>>
>> ou bien tu écris les deux couples de valeurs (ce que tu fais
>> actuellement) et tu supprimes le premier.
>> Mais en aucun cas tu ne dois écrire les deux et effacer la valeur 0°
>> sans enlever la date et l'heure correspondante.
>>
>> Je te propose une solution qui devrait te satisfaire : une résolution
>> graphique. Au lieu de récupérer les paramètres de ta droite en utilisant
>> une formule, trace un graphique en nuages de points (c'est impératif,
>> pas en courbe). Tu fais un clic droit sur ta série, ajouter une courbe
>> de tendance. La tu choisis linéaire et dans les options tu coches
>> afficher l'équation sur le graphique et fficher le coéfficient de
>> régression.
>> Si maintenant dans ton tableau de valeurs, il manque des y, la droite ne
>> sera tracée qu'avec les valeurs "complètes" c'est à dire avec un comple
>> x,y. (Et oui, excel aime bien aussi les maths qui sont des maths... :-) )
>> Tu verras comment les valeurs manquantes influent ou pas sur la droite.
>> Le coeff de régression te permet de voir si tes valeurs sont ou non
>> réellement reliées de façon linéaire. LE coeff de régression varie de 0
>> à 1. Il faut savoir que les fonctions de droite de régression renvoient
>> TOUJOURS une valeur, quelles que soient les données entrées mais si le
>> coeff est du genre <0.6 tu peux être sur qu'en utilsant l'équantion de
>> la droite pour faire une prévision, celle ci a toutes les chances d'être
>> aussi juste que celle de Mme Irma dans sa caravane à la foire à la tomate...
>>
>> Pour le reste, je te laisse à tes "calculs"
>> Pour ce qui est de la mesure, je suis biologiste. Les mesures c'est mon
>> métier et travailler sur du matériel biologique implique d'avoir une
>> variation importante dans les données et d'avoir une anlayse critique de
>> ces données. Les statistiques sont exactement faites pour savoir comment
>> interpréter des résultats qui par nature varient et savoir si on a le
>> droit ou non de conclure.
>> Que ce soit des températures, des relevés de production mensuelle, des
>> choux ou des centenaires, peu importe. Ou bien il y a une relation
>> mathématiques qui a un sens qui relie ces données et dans ce cas, il
>> faut appliquer correctement cette fonction, ou bien il n'y en a pas et
>> dans ce cas, il ne faut pas faire croire qu'on peut utiliser une
>> fonction mathématique pour prédire l'avenir. Beaucoup d'économistes s'y
>> sont d'ailleurs cassé les dents.
>>
>>
>> Misange migrateuse
>> XlWiki : Participez à un travail collaboratif sur excel !
>> http://xlwiki.free.fr/wiki
>> http://www.excelabo.net
>>
>> smr78 a écrit :
>>> Bonjour,
>>> Ah, je vois que ça chauffe dans les stats. Bonne nouvelle, j'ai trouvé une
>>> formule qui me satisfait et fera un bon compromis.
>>> Comme je n'ai pas de formule pour éliminer les valeurs nulles ou inférieures
>>> à une seuil, j'ai réfléchi (!) et fait appel à Interpol comme le suggérait
>>> François.
>>>
>>> Au lieu de supprimer les valeurs nulles, je les remplace par une valeur
>>> moyenne des valeurs non nulles, automatiquement par formule. C'est illégal me
>>> direz-vous encore mais j'y reviendrai.
>>>
>>> Je prends comme exemple
>>> en colonne A 1,2,3,4,5 (pour les X) en colonne B 25,0,0,55,60
>>>
>>> n.b., je passe à 5 valeurs car mettre deux valeurs à 0 parmi 4, embrouille
>>> tout.
>>>
>>> ma première formule magique est :
>>> = {DROITEREG(N((B1:B5)=0)*MOYENNE(B1:B5)+(B1:B5))}
>>> là-dedans,
>>> N((B1:B5)=0 génère la matrice 0,1,1,0,0
>>> N((B1:B5)=0)*MOYENNE(B1:B5) génère une matrice 0, 28, 28, 0, 0 où les
>>> valeurs nulles sont remplacées par la moyenne des 3 valeurs non nulles (mais
>>> basée sur 5 mesures)
>>> et il suffit ensuite d'ajouter la matrice initiale pour obtenir 25, 28, 28,
>>> 55, 60
>>>
>>> ma deuxième formule magique veut faire pareil, mais à la place de la moyenne
>>> des 3 valeurs non nulles moyennées sur 5, calcule la moyenne sur 3 mesures!!
>>>
>>> =
>>> {DROITEREG(N((B1:B5)=0)*SOMME(N(B1:B5>0)*(B1:B5)/NB.SI(B1:B5;">0"))+(B1:B5))}
>>>
>>> Si l'on veut remplacer les valeurs inférieures à un seuil situé en C1, on
>>> remplace
>>> =0 par <=$C$1, >0 par >$C$1 et ">0" par ... ">" & $C$1
>>>
>>> Et je reviens têtument sur mon thermomètre :
>>> - supposons que l'on relève la température ou pression atmosphérique tous
>>> les jours à midi ; si comme dans certains films on voit que la personne
>>> lisant la mesure à midi le 15 du mois, doute du thermomètre/baromètre, tapote
>>> dessus et relève une "bonne" mesure à midi et 5 secondes et inscrit en plus
>>> de la mesure fausse 0°, la "bonne" mesure relevée à midi 5 secondes, je pense
>>> qu'il n'y aura pas de crime mathématique à éliminer les températures égales à
>>> 0°, sachant que le film se déroule au niveau de l'équateur, n'est-il pas?
>>> et l'on peut très bien, pour établir la courbe des températures du mois,
>>> considérer que la deuxième "bonne" mesure doit être datée du 15 du mois et
>>> non pas du 16.
>>>
>>> J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok, c'est de
>>> la mesure et non pas de la statistique théorique.
>>>
>>> Cela ne s'applique pas qu'aux températures, mon vrai problème concerne un
>>> relevé de production mensuelle, donc pas besoin de corriger les dates non
>>> plus.
>>>
>>> Bon, je m'y connais aussi en statistiques et analyses de marché et je
>>> suis... Auvergnat!
>>>
>>> Ah, oui reste le coup du 25,0,0,50, deux valeurs nulles sur 4, et bien on
>>> peut mettre n'importe quoi comme valeur de remplacement des 0 : 25,10,10, 50
>>> ou 25, 30, 30, 50 on obtient toujours la même pente, seule change l'ordonnée
>>> à l'origine.
>> Alors là !!! compare la pente de tes deux droites et tu m'en diras des
>> nouvelles. Fais ça par exemple avec des x du genre 1, 2,8, 24 au lieu de
>> 1, 2, 3, 4 et tu verras que c'est complètement faux. Si ta formule
>> magique te donne la même valeur alors c'est qu'elle est surtout
>> magiquement fausse.
>> Je ne peux pas ne pas réagir, désolée si je te parais puriste mais
>> laisser ce genre de conclusion archivée dans google sans mettre en garde
>> un futur lecteur sur l'absence de validité de cette approche... je ne
>> peux pas. Ce n'est bien entendu pas du tout dirigé contre toi en tant
>> qu'individu ! Chacun sa spécialité et tu es surement meilleur que moi en
>> études de marché (et en choux auvergnats) :-)
>>> Encore merci pour vos tuyaux.
heuh, je viens de trouver un autre exemple peut-être plus parlant :
- je veux analyser la consommation d'essence du moteur de ma voiture à
partir d'un relevé journalier automatique.
- la consommation d'essence tombe à zéro les jours où je ne roule pas!
Ca ne m'intéresse pas de savoir quelle est la consommation d'essence de mon
moteur les jours où je ne roule pas, ces valeurs 0 vont influer sur la
tendance et fausser toute analyse ou prévisions.
Comme je ne peux bricoler l'appareil qui relève les consommations tous les
jours, je cherche donc à les ignorer et sans faire une étape correctrice et
sans routine.
Si la solution formule n'existe pas, bien sûr je ferai autrement.
Merci encore.
"Misange" a écrit :
> J'arrête là désolée. Tu fais ce que tu veux, ce sont tes données. Ta
> façon de procéder est fausse, ne peut en aucun cas être justifiée par un
> quelconque calcul. Ce n'est pas parce que tu ne sais pas faire quelque
> chose par formule que tu as le droit d'en utiliser une autre juste parce
> qu'avec celle là tu obtiens un résultat qui te convient. J'attends de
> voir la tête de ta collègue quand elle devra expliquer d'où sortent ses
> prévisions...
> Bonne chance pour la suite mais sans moi
> Misange migrateuse
> XlWiki : Participez à un travail collaboratif sur excel !
> http://xlwiki.free.fr/wiki
> http://www.excelabo.net
>
> smr78 a écrit :
> > Bonjour Misange,
> > Je ne discute pas pour dire que j'ai raison mais parce que la discussion
> > m'enrichit (et d'autres j'espère).
> > - Une première remarque, mes X connus ont un intervalle de progression
> > constant (comme les dates du jour), on peut les assimiler à 1,2,3.. et c'est
> > ce que fait DROITEREG quand ils sont omis. Mais je ne pense pas que cela
> > influe.
> > - Bien sûr j'ai fait le graphique de mes données et ajouté les courbes de
> > tendances et les résultats donnent des pentes différentes et j'ai choisi
> > celles qui me "plaisaient" le plus. Il est vrai que selon la position des
> > valeurs nulles, les résultats varient beaucoup avec DROITEREG et moins avec
> > les miens (surprenant oui à interprêter mais ça me va).
> > - J'ai essayé de mettre des valeurs vides au lieu de valeurs nulles ; eh
> > bien la formule DROITEREG (B1:B6) coince et donne #valeur, alors que la
> > courbe de tendance est bien tracée et que l'équation sur le graphique
> > apparaît avec des valeurs, est-ce parce que les valeurs calculées débordent
> > de la plage? (Excel 2003)
> > - J'aurais bien voulu faire l'extrapolation sur les deux valeurs encadrant
> > la valeur manquante, mais je ne sais pas faire par formule.
> > - Dans mon exemple température, je suis d'accord sur le fait que la mauvaise
> > valeur devrait être supprimée à la saisie, mais pas possible car la saisie
> > est faite, disons automatiquement à des milliers de kilomètres d'où se trouve
> > mon amie (débutante, débutante en Excel) qui reçoit des centaines de
> > résultat, pas moyen de modifier la saisie, ni de lire les données une par une
> > pour corriger à la main, d'où ma question sur comment l'enlever au moment de
> > l'analyse? J'ai essayé et proposé de faire un filtre élaboré, mais ça fait
> > une étape de plus qui gêne. (Quand je dis enlever, c'est effectivement
> > enlever le couple date et valeur pour les valeurs "fausses").
> >
> > Merci encore pour tous ces conseils bio-éclairés.
> > Cordialement
> >
> >
> > "Misange" a écrit :
> >
> >> Un dernier commentaire :
> >> ce n'est pas parce qu'on obtient une valeur qu'elle a la moindre
> >> signification.
> >> Tu prends un exemple avec 5 valeurs. Fais le avec 6 en mettant comme 6°
> >> couple 10,200 et regarde ce que donne ton calcul. Tu choisis la valeur
> >> la plus jolie qui t'arrange si tu veux mais tu n'appelles plus cela des
> >> maths par pitié.
> >> Une interpolation ne se fait pas sur la moyenne des valeurs mais sur la
> >> moyenne des bornes encadrant la valeur manquante ce qui est EXTREMEMENT
> >> différent. C'est un peu de ma faute : par souci de simplification, j'ai
> >> pris des couples de valeurs qui s'alignent justement le long d'une
> >> droite. Il aurait mieux valu que je donne comme exemple des valeurs du genre
> >> 1 15
> >> 5 25
> >> 8 30
> >> 13 48
> >> 20 62
> >> 40 98
> >>
> >> pente : 2.13
> >> je fais sauter 48 et 62 la pente devient 2.11
> >> j'utilise ta formule magique, la pente est de 1,5 !!
> >> Plus drole : je fais sauter 25 et 62, La vraie pente est 2.1. Avec ta
> >> formule ... 4.9 !
> >> tu prends quoi au juste ? Je te conseille 4.9 si tu as envie de montrer
> >> que la production progresse de façon spectaculaire cette année. MAis
> >> attention si l'année prochaine elle tombe = 0.6 on t'en voudra...
> >>
> >>
> >> Pour reprendre ton exemple des températures. La correction ne se fait
> >> pas dans le tableau mais au moment ou on entre les valeurs dans le cas
> >> que tu cite. Le 15 du mois, quand tu fais ta mesure à midi
> >> 15/10/2008 12:00==> 0°
> >> 15/10/2008 12:05 ==> 21°
> >>
> >> ou bien tu écris dans ton tableau seulement
> >> 15/10/2008 12:00==> 21°C
> >> La correction est donc faite au moment de la saisie.
> >>
> >> ou bien tu écris les deux couples de valeurs (ce que tu fais
> >> actuellement) et tu supprimes le premier.
> >> Mais en aucun cas tu ne dois écrire les deux et effacer la valeur 0°
> >> sans enlever la date et l'heure correspondante.
> >>
> >> Je te propose une solution qui devrait te satisfaire : une résolution
> >> graphique. Au lieu de récupérer les paramètres de ta droite en utilisant
> >> une formule, trace un graphique en nuages de points (c'est impératif,
> >> pas en courbe). Tu fais un clic droit sur ta série, ajouter une courbe
> >> de tendance. La tu choisis linéaire et dans les options tu coches
> >> afficher l'équation sur le graphique et fficher le coéfficient de
> >> régression.
> >> Si maintenant dans ton tableau de valeurs, il manque des y, la droite ne
> >> sera tracée qu'avec les valeurs "complètes" c'est à dire avec un comple
> >> x,y. (Et oui, excel aime bien aussi les maths qui sont des maths... :-) )
> >> Tu verras comment les valeurs manquantes influent ou pas sur la droite.
> >> Le coeff de régression te permet de voir si tes valeurs sont ou non
> >> réellement reliées de façon linéaire. LE coeff de régression varie de 0
> >> à 1. Il faut savoir que les fonctions de droite de régression renvoient
> >> TOUJOURS une valeur, quelles que soient les données entrées mais si le
> >> coeff est du genre <0.6 tu peux être sur qu'en utilsant l'équantion de
> >> la droite pour faire une prévision, celle ci a toutes les chances d'être
> >> aussi juste que celle de Mme Irma dans sa caravane à la foire à la tomate...
> >>
> >> Pour le reste, je te laisse à tes "calculs"
> >> Pour ce qui est de la mesure, je suis biologiste. Les mesures c'est mon
> >> métier et travailler sur du matériel biologique implique d'avoir une
> >> variation importante dans les données et d'avoir une anlayse critique de
> >> ces données. Les statistiques sont exactement faites pour savoir comment
> >> interpréter des résultats qui par nature varient et savoir si on a le
> >> droit ou non de conclure.
> >> Que ce soit des températures, des relevés de production mensuelle, des
> >> choux ou des centenaires, peu importe. Ou bien il y a une relation
> >> mathématiques qui a un sens qui relie ces données et dans ce cas, il
> >> faut appliquer correctement cette fonction, ou bien il n'y en a pas et
> >> dans ce cas, il ne faut pas faire croire qu'on peut utiliser une
> >> fonction mathématique pour prédire l'avenir. Beaucoup d'économistes s'y
> >> sont d'ailleurs cassé les dents.
> >>
> >>
> >> Misange migrateuse
> >> XlWiki : Participez à un travail collaboratif sur excel !
> >> http://xlwiki.free.fr/wiki
> >> http://www.excelabo.net
> >>
> >> smr78 a écrit :
> >>> Bonjour,
> >>> Ah, je vois que ça chauffe dans les stats. Bonne nouvelle, j'ai trouvé une
> >>> formule qui me satisfait et fera un bon compromis.
> >>> Comme je n'ai pas de formule pour éliminer les valeurs nulles ou inférieures
> >>> à une seuil, j'ai réfléchi (!) et fait appel à Interpol comme le suggérait
> >>> François.
> >>>
> >>> Au lieu de supprimer les valeurs nulles, je les remplace par une valeur
> >>> moyenne des valeurs non nulles, automatiquement par formule. C'est illégal me
> >>> direz-vous encore mais j'y reviendrai.
> >>>
> >>> Je prends comme exemple
> >>> en colonne A 1,2,3,4,5 (pour les X) en colonne B 25,0,0,55,60
> >>>
> >>> n.b., je passe à 5 valeurs car mettre deux valeurs à 0 parmi 4, embrouille
> >>> tout.
> >>>
> >>> ma première formule magique est :
> >>> = {DROITEREG(N((B1:B5)=0)*MOYENNE(B1:B5)+(B1:B5))}
> >>> là-dedans,
> >>> N((B1:B5)=0 génère la matrice 0,1,1,0,0
> >>> N((B1:B5)=0)*MOYENNE(B1:B5) génère une matrice 0, 28, 28, 0, 0 où les
> >>> valeurs nulles sont remplacées par la moyenne des 3 valeurs non nulles (mais
> >>> basée sur 5 mesures)
> >>> et il suffit ensuite d'ajouter la matrice initiale pour obtenir 25, 28, 28,
> >>> 55, 60
> >>>
> >>> ma deuxième formule magique veut faire pareil, mais à la place de la moyenne
> >>> des 3 valeurs non nulles moyennées sur 5, calcule la moyenne sur 3 mesures!!
> >>>
> >>> =
> >>> {DROITEREG(N((B1:B5)=0)*SOMME(N(B1:B5>0)*(B1:B5)/NB.SI(B1:B5;">0"))+(B1:B5))}
> >>>
> >>> Si l'on veut remplacer les valeurs inférieures à un seuil situé en C1, on
> >>> remplace
> >>> =0 par <=$C$1, >0 par >$C$1 et ">0" par ... ">" & $C$1
> >>>
> >>> Et je reviens têtument sur mon thermomètre :
> >>> - supposons que l'on relève la température ou pression atmosphérique tous
> >>> les jours à midi ; si comme dans certains films on voit que la personne
> >>> lisant la mesure à midi le 15 du mois, doute du thermomètre/baromètre, tapote
> >>> dessus et relève une "bonne" mesure à midi et 5 secondes et inscrit en plus
> >>> de la mesure fausse 0°, la "bonne" mesure relevée à midi 5 secondes, je pense
> >>> qu'il n'y aura pas de crime mathématique à éliminer les températures égales à
> >>> 0°, sachant que le film se déroule au niveau de l'équateur, n'est-il pas?
> >>> et l'on peut très bien, pour établir la courbe des températures du mois,
> >>> considérer que la deuxième "bonne" mesure doit être datée du 15 du mois et
> >>> non pas du 16.
> >>>
> >>> J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok, c'est de
> >>> la mesure et non pas de la statistique théorique.
> >>>
> >>> Cela ne s'applique pas qu'aux températures, mon vrai problème concerne un
> >>> relevé de production mensuelle, donc pas besoin de corriger les dates non
> >>> plus.
> >>>
> >>> Bon, je m'y connais aussi en statistiques et analyses de marché et je
> >>> suis... Auvergnat!
> >>>
> >>> Ah, oui reste le coup du 25,0,0,50, deux valeurs nulles sur 4, et bien on
> >>> peut mettre n'importe quoi comme valeur de remplacement des 0 : 25,10,10, 50
> >>> ou 25, 30, 30, 50 on obtient toujours la même pente, seule change l'ordonnée
> >>> à l'origine.
> >> Alors là !!! compare la pente de tes deux droites et tu m'en diras des
> >> nouvelles. Fais ça par exemple avec des x du genre 1, 2,8, 24 au lieu de
> >> 1, 2, 3, 4 et tu verras que c'est complètement faux. Si ta formule
> >> magique te donne la même valeur alors c'est qu'elle est surtout
> >> magiquement fausse.
> >> Je ne peux pas ne pas réagir, désolée si je te parais puriste mais
> >> laisser ce genre de conclusion archivée dans google sans mettre en garde
> >> un futur lecteur sur l'absence de validité de cette approche... je ne
> >> peux pas. Ce n'est bien entendu pas du tout dirigé contre toi en tant
> >> qu'individu ! Chacun sa spécialité et tu es surement meilleur que moi en
> >> études de marché (et en choux auvergnats) :-)
> >>> Encore merci pour vos tuyaux.
>
heuh, je viens de trouver un autre exemple peut-être plus parlant :
- je veux analyser la consommation d'essence du moteur de ma voiture à
partir d'un relevé journalier automatique.
- la consommation d'essence tombe à zéro les jours où je ne roule pas!
Ca ne m'intéresse pas de savoir quelle est la consommation d'essence de mon
moteur les jours où je ne roule pas, ces valeurs 0 vont influer sur la
tendance et fausser toute analyse ou prévisions.
Comme je ne peux bricoler l'appareil qui relève les consommations tous les
jours, je cherche donc à les ignorer et sans faire une étape correctrice et
sans routine.
Si la solution formule n'existe pas, bien sûr je ferai autrement.
Merci encore.
"Misange" a écrit :
> J'arrête là désolée. Tu fais ce que tu veux, ce sont tes données. Ta
> façon de procéder est fausse, ne peut en aucun cas être justifiée par un
> quelconque calcul. Ce n'est pas parce que tu ne sais pas faire quelque
> chose par formule que tu as le droit d'en utiliser une autre juste parce
> qu'avec celle là tu obtiens un résultat qui te convient. J'attends de
> voir la tête de ta collègue quand elle devra expliquer d'où sortent ses
> prévisions...
> Bonne chance pour la suite mais sans moi
> Misange migrateuse
> XlWiki : Participez à un travail collaboratif sur excel !
> http://xlwiki.free.fr/wiki
> http://www.excelabo.net
>
> smr78 a écrit :
> > Bonjour Misange,
> > Je ne discute pas pour dire que j'ai raison mais parce que la discussion
> > m'enrichit (et d'autres j'espère).
> > - Une première remarque, mes X connus ont un intervalle de progression
> > constant (comme les dates du jour), on peut les assimiler à 1,2,3.. et c'est
> > ce que fait DROITEREG quand ils sont omis. Mais je ne pense pas que cela
> > influe.
> > - Bien sûr j'ai fait le graphique de mes données et ajouté les courbes de
> > tendances et les résultats donnent des pentes différentes et j'ai choisi
> > celles qui me "plaisaient" le plus. Il est vrai que selon la position des
> > valeurs nulles, les résultats varient beaucoup avec DROITEREG et moins avec
> > les miens (surprenant oui à interprêter mais ça me va).
> > - J'ai essayé de mettre des valeurs vides au lieu de valeurs nulles ; eh
> > bien la formule DROITEREG (B1:B6) coince et donne #valeur, alors que la
> > courbe de tendance est bien tracée et que l'équation sur le graphique
> > apparaît avec des valeurs, est-ce parce que les valeurs calculées débordent
> > de la plage? (Excel 2003)
> > - J'aurais bien voulu faire l'extrapolation sur les deux valeurs encadrant
> > la valeur manquante, mais je ne sais pas faire par formule.
> > - Dans mon exemple température, je suis d'accord sur le fait que la mauvaise
> > valeur devrait être supprimée à la saisie, mais pas possible car la saisie
> > est faite, disons automatiquement à des milliers de kilomètres d'où se trouve
> > mon amie (débutante, débutante en Excel) qui reçoit des centaines de
> > résultat, pas moyen de modifier la saisie, ni de lire les données une par une
> > pour corriger à la main, d'où ma question sur comment l'enlever au moment de
> > l'analyse? J'ai essayé et proposé de faire un filtre élaboré, mais ça fait
> > une étape de plus qui gêne. (Quand je dis enlever, c'est effectivement
> > enlever le couple date et valeur pour les valeurs "fausses").
> >
> > Merci encore pour tous ces conseils bio-éclairés.
> > Cordialement
> >
> >
> > "Misange" a écrit :
> >
> >> Un dernier commentaire :
> >> ce n'est pas parce qu'on obtient une valeur qu'elle a la moindre
> >> signification.
> >> Tu prends un exemple avec 5 valeurs. Fais le avec 6 en mettant comme 6°
> >> couple 10,200 et regarde ce que donne ton calcul. Tu choisis la valeur
> >> la plus jolie qui t'arrange si tu veux mais tu n'appelles plus cela des
> >> maths par pitié.
> >> Une interpolation ne se fait pas sur la moyenne des valeurs mais sur la
> >> moyenne des bornes encadrant la valeur manquante ce qui est EXTREMEMENT
> >> différent. C'est un peu de ma faute : par souci de simplification, j'ai
> >> pris des couples de valeurs qui s'alignent justement le long d'une
> >> droite. Il aurait mieux valu que je donne comme exemple des valeurs du genre
> >> 1 15
> >> 5 25
> >> 8 30
> >> 13 48
> >> 20 62
> >> 40 98
> >>
> >> pente : 2.13
> >> je fais sauter 48 et 62 la pente devient 2.11
> >> j'utilise ta formule magique, la pente est de 1,5 !!
> >> Plus drole : je fais sauter 25 et 62, La vraie pente est 2.1. Avec ta
> >> formule ... 4.9 !
> >> tu prends quoi au juste ? Je te conseille 4.9 si tu as envie de montrer
> >> que la production progresse de façon spectaculaire cette année. MAis
> >> attention si l'année prochaine elle tombe = 0.6 on t'en voudra...
> >>
> >>
> >> Pour reprendre ton exemple des températures. La correction ne se fait
> >> pas dans le tableau mais au moment ou on entre les valeurs dans le cas
> >> que tu cite. Le 15 du mois, quand tu fais ta mesure à midi
> >> 15/10/2008 12:00==> 0°
> >> 15/10/2008 12:05 ==> 21°
> >>
> >> ou bien tu écris dans ton tableau seulement
> >> 15/10/2008 12:00==> 21°C
> >> La correction est donc faite au moment de la saisie.
> >>
> >> ou bien tu écris les deux couples de valeurs (ce que tu fais
> >> actuellement) et tu supprimes le premier.
> >> Mais en aucun cas tu ne dois écrire les deux et effacer la valeur 0°
> >> sans enlever la date et l'heure correspondante.
> >>
> >> Je te propose une solution qui devrait te satisfaire : une résolution
> >> graphique. Au lieu de récupérer les paramètres de ta droite en utilisant
> >> une formule, trace un graphique en nuages de points (c'est impératif,
> >> pas en courbe). Tu fais un clic droit sur ta série, ajouter une courbe
> >> de tendance. La tu choisis linéaire et dans les options tu coches
> >> afficher l'équation sur le graphique et fficher le coéfficient de
> >> régression.
> >> Si maintenant dans ton tableau de valeurs, il manque des y, la droite ne
> >> sera tracée qu'avec les valeurs "complètes" c'est à dire avec un comple
> >> x,y. (Et oui, excel aime bien aussi les maths qui sont des maths... :-) )
> >> Tu verras comment les valeurs manquantes influent ou pas sur la droite.
> >> Le coeff de régression te permet de voir si tes valeurs sont ou non
> >> réellement reliées de façon linéaire. LE coeff de régression varie de 0
> >> à 1. Il faut savoir que les fonctions de droite de régression renvoient
> >> TOUJOURS une valeur, quelles que soient les données entrées mais si le
> >> coeff est du genre <0.6 tu peux être sur qu'en utilsant l'équantion de
> >> la droite pour faire une prévision, celle ci a toutes les chances d'être
> >> aussi juste que celle de Mme Irma dans sa caravane à la foire à la tomate...
> >>
> >> Pour le reste, je te laisse à tes "calculs"
> >> Pour ce qui est de la mesure, je suis biologiste. Les mesures c'est mon
> >> métier et travailler sur du matériel biologique implique d'avoir une
> >> variation importante dans les données et d'avoir une anlayse critique de
> >> ces données. Les statistiques sont exactement faites pour savoir comment
> >> interpréter des résultats qui par nature varient et savoir si on a le
> >> droit ou non de conclure.
> >> Que ce soit des températures, des relevés de production mensuelle, des
> >> choux ou des centenaires, peu importe. Ou bien il y a une relation
> >> mathématiques qui a un sens qui relie ces données et dans ce cas, il
> >> faut appliquer correctement cette fonction, ou bien il n'y en a pas et
> >> dans ce cas, il ne faut pas faire croire qu'on peut utiliser une
> >> fonction mathématique pour prédire l'avenir. Beaucoup d'économistes s'y
> >> sont d'ailleurs cassé les dents.
> >>
> >>
> >> Misange migrateuse
> >> XlWiki : Participez à un travail collaboratif sur excel !
> >> http://xlwiki.free.fr/wiki
> >> http://www.excelabo.net
> >>
> >> smr78 a écrit :
> >>> Bonjour,
> >>> Ah, je vois que ça chauffe dans les stats. Bonne nouvelle, j'ai trouvé une
> >>> formule qui me satisfait et fera un bon compromis.
> >>> Comme je n'ai pas de formule pour éliminer les valeurs nulles ou inférieures
> >>> à une seuil, j'ai réfléchi (!) et fait appel à Interpol comme le suggérait
> >>> François.
> >>>
> >>> Au lieu de supprimer les valeurs nulles, je les remplace par une valeur
> >>> moyenne des valeurs non nulles, automatiquement par formule. C'est illégal me
> >>> direz-vous encore mais j'y reviendrai.
> >>>
> >>> Je prends comme exemple
> >>> en colonne A 1,2,3,4,5 (pour les X) en colonne B 25,0,0,55,60
> >>>
> >>> n.b., je passe à 5 valeurs car mettre deux valeurs à 0 parmi 4, embrouille
> >>> tout.
> >>>
> >>> ma première formule magique est :
> >>> = {DROITEREG(N((B1:B5)=0)*MOYENNE(B1:B5)+(B1:B5))}
> >>> là-dedans,
> >>> N((B1:B5)=0 génère la matrice 0,1,1,0,0
> >>> N((B1:B5)=0)*MOYENNE(B1:B5) génère une matrice 0, 28, 28, 0, 0 où les
> >>> valeurs nulles sont remplacées par la moyenne des 3 valeurs non nulles (mais
> >>> basée sur 5 mesures)
> >>> et il suffit ensuite d'ajouter la matrice initiale pour obtenir 25, 28, 28,
> >>> 55, 60
> >>>
> >>> ma deuxième formule magique veut faire pareil, mais à la place de la moyenne
> >>> des 3 valeurs non nulles moyennées sur 5, calcule la moyenne sur 3 mesures!!
> >>>
> >>> =
> >>> {DROITEREG(N((B1:B5)=0)*SOMME(N(B1:B5>0)*(B1:B5)/NB.SI(B1:B5;">0"))+(B1:B5))}
> >>>
> >>> Si l'on veut remplacer les valeurs inférieures à un seuil situé en C1, on
> >>> remplace
> >>> =0 par <=$C$1, >0 par >$C$1 et ">0" par ... ">" & $C$1
> >>>
> >>> Et je reviens têtument sur mon thermomètre :
> >>> - supposons que l'on relève la température ou pression atmosphérique tous
> >>> les jours à midi ; si comme dans certains films on voit que la personne
> >>> lisant la mesure à midi le 15 du mois, doute du thermomètre/baromètre, tapote
> >>> dessus et relève une "bonne" mesure à midi et 5 secondes et inscrit en plus
> >>> de la mesure fausse 0°, la "bonne" mesure relevée à midi 5 secondes, je pense
> >>> qu'il n'y aura pas de crime mathématique à éliminer les températures égales à
> >>> 0°, sachant que le film se déroule au niveau de l'équateur, n'est-il pas?
> >>> et l'on peut très bien, pour établir la courbe des températures du mois,
> >>> considérer que la deuxième "bonne" mesure doit être datée du 15 du mois et
> >>> non pas du 16.
> >>>
> >>> J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok, c'est de
> >>> la mesure et non pas de la statistique théorique.
> >>>
> >>> Cela ne s'applique pas qu'aux températures, mon vrai problème concerne un
> >>> relevé de production mensuelle, donc pas besoin de corriger les dates non
> >>> plus.
> >>>
> >>> Bon, je m'y connais aussi en statistiques et analyses de marché et je
> >>> suis... Auvergnat!
> >>>
> >>> Ah, oui reste le coup du 25,0,0,50, deux valeurs nulles sur 4, et bien on
> >>> peut mettre n'importe quoi comme valeur de remplacement des 0 : 25,10,10, 50
> >>> ou 25, 30, 30, 50 on obtient toujours la même pente, seule change l'ordonnée
> >>> à l'origine.
> >> Alors là !!! compare la pente de tes deux droites et tu m'en diras des
> >> nouvelles. Fais ça par exemple avec des x du genre 1, 2,8, 24 au lieu de
> >> 1, 2, 3, 4 et tu verras que c'est complètement faux. Si ta formule
> >> magique te donne la même valeur alors c'est qu'elle est surtout
> >> magiquement fausse.
> >> Je ne peux pas ne pas réagir, désolée si je te parais puriste mais
> >> laisser ce genre de conclusion archivée dans google sans mettre en garde
> >> un futur lecteur sur l'absence de validité de cette approche... je ne
> >> peux pas. Ce n'est bien entendu pas du tout dirigé contre toi en tant
> >> qu'individu ! Chacun sa spécialité et tu es surement meilleur que moi en
> >> études de marché (et en choux auvergnats) :-)
> >>> Encore merci pour vos tuyaux.
>
heuh, je viens de trouver un autre exemple peut-être plus parlant :
- je veux analyser la consommation d'essence du moteur de ma voiture à
partir d'un relevé journalier automatique.
- la consommation d'essence tombe à zéro les jours où je ne roule pas!
Ca ne m'intéresse pas de savoir quelle est la consommation d'essence de mon
moteur les jours où je ne roule pas, ces valeurs 0 vont influer sur la
tendance et fausser toute analyse ou prévisions.
Comme je ne peux bricoler l'appareil qui relève les consommations tous les
jours, je cherche donc à les ignorer et sans faire une étape correctrice et
sans routine.
Si la solution formule n'existe pas, bien sûr je ferai autrement.
Merci encore.
"Misange" a écrit :
> J'arrête là désolée. Tu fais ce que tu veux, ce sont tes données. Ta
> façon de procéder est fausse, ne peut en aucun cas être justifiée par un
> quelconque calcul. Ce n'est pas parce que tu ne sais pas faire quelque
> chose par formule que tu as le droit d'en utiliser une autre juste parce
> qu'avec celle là tu obtiens un résultat qui te convient. J'attends de
> voir la tête de ta collègue quand elle devra expliquer d'où sortent ses
> prévisions...
> Bonne chance pour la suite mais sans moi
> Misange migrateuse
> XlWiki : Participez à un travail collaboratif sur excel !
> http://xlwiki.free.fr/wiki
> http://www.excelabo.net
>
> smr78 a écrit :
> > Bonjour Misange,
> > Je ne discute pas pour dire que j'ai raison mais parce que la discussion
> > m'enrichit (et d'autres j'espère).
> > - Une première remarque, mes X connus ont un intervalle de progression
> > constant (comme les dates du jour), on peut les assimiler à 1,2,3.. et c'est
> > ce que fait DROITEREG quand ils sont omis. Mais je ne pense pas que cela
> > influe.
> > - Bien sûr j'ai fait le graphique de mes données et ajouté les courbes de
> > tendances et les résultats donnent des pentes différentes et j'ai choisi
> > celles qui me "plaisaient" le plus. Il est vrai que selon la position des
> > valeurs nulles, les résultats varient beaucoup avec DROITEREG et moins avec
> > les miens (surprenant oui à interprêter mais ça me va).
> > - J'ai essayé de mettre des valeurs vides au lieu de valeurs nulles ; eh
> > bien la formule DROITEREG (B1:B6) coince et donne #valeur, alors que la
> > courbe de tendance est bien tracée et que l'équation sur le graphique
> > apparaît avec des valeurs, est-ce parce que les valeurs calculées débordent
> > de la plage? (Excel 2003)
> > - J'aurais bien voulu faire l'extrapolation sur les deux valeurs encadrant
> > la valeur manquante, mais je ne sais pas faire par formule.
> > - Dans mon exemple température, je suis d'accord sur le fait que la mauvaise
> > valeur devrait être supprimée à la saisie, mais pas possible car la saisie
> > est faite, disons automatiquement à des milliers de kilomètres d'où se trouve
> > mon amie (débutante, débutante en Excel) qui reçoit des centaines de
> > résultat, pas moyen de modifier la saisie, ni de lire les données une par une
> > pour corriger à la main, d'où ma question sur comment l'enlever au moment de
> > l'analyse? J'ai essayé et proposé de faire un filtre élaboré, mais ça fait
> > une étape de plus qui gêne. (Quand je dis enlever, c'est effectivement
> > enlever le couple date et valeur pour les valeurs "fausses").
> >
> > Merci encore pour tous ces conseils bio-éclairés.
> > Cordialement
> >
> >
> > "Misange" a écrit :
> >
> >> Un dernier commentaire :
> >> ce n'est pas parce qu'on obtient une valeur qu'elle a la moindre
> >> signification.
> >> Tu prends un exemple avec 5 valeurs. Fais le avec 6 en mettant comme 6°
> >> couple 10,200 et regarde ce que donne ton calcul. Tu choisis la valeur
> >> la plus jolie qui t'arrange si tu veux mais tu n'appelles plus cela des
> >> maths par pitié.
> >> Une interpolation ne se fait pas sur la moyenne des valeurs mais sur la
> >> moyenne des bornes encadrant la valeur manquante ce qui est EXTREMEMENT
> >> différent. C'est un peu de ma faute : par souci de simplification, j'ai
> >> pris des couples de valeurs qui s'alignent justement le long d'une
> >> droite. Il aurait mieux valu que je donne comme exemple des valeurs du genre
> >> 1 15
> >> 5 25
> >> 8 30
> >> 13 48
> >> 20 62
> >> 40 98
> >>
> >> pente : 2.13
> >> je fais sauter 48 et 62 la pente devient 2.11
> >> j'utilise ta formule magique, la pente est de 1,5 !!
> >> Plus drole : je fais sauter 25 et 62, La vraie pente est 2.1. Avec ta
> >> formule ... 4.9 !
> >> tu prends quoi au juste ? Je te conseille 4.9 si tu as envie de montrer
> >> que la production progresse de façon spectaculaire cette année. MAis
> >> attention si l'année prochaine elle tombe = 0.6 on t'en voudra...
> >>
> >>
> >> Pour reprendre ton exemple des températures. La correction ne se fait
> >> pas dans le tableau mais au moment ou on entre les valeurs dans le cas
> >> que tu cite. Le 15 du mois, quand tu fais ta mesure à midi
> >> 15/10/2008 12:00==> 0°
> >> 15/10/2008 12:05 ==> 21°
> >>
> >> ou bien tu écris dans ton tableau seulement
> >> 15/10/2008 12:00==> 21°C
> >> La correction est donc faite au moment de la saisie.
> >>
> >> ou bien tu écris les deux couples de valeurs (ce que tu fais
> >> actuellement) et tu supprimes le premier.
> >> Mais en aucun cas tu ne dois écrire les deux et effacer la valeur 0°
> >> sans enlever la date et l'heure correspondante.
> >>
> >> Je te propose une solution qui devrait te satisfaire : une résolution
> >> graphique. Au lieu de récupérer les paramètres de ta droite en utilisant
> >> une formule, trace un graphique en nuages de points (c'est impératif,
> >> pas en courbe). Tu fais un clic droit sur ta série, ajouter une courbe
> >> de tendance. La tu choisis linéaire et dans les options tu coches
> >> afficher l'équation sur le graphique et fficher le coéfficient de
> >> régression.
> >> Si maintenant dans ton tableau de valeurs, il manque des y, la droite ne
> >> sera tracée qu'avec les valeurs "complètes" c'est à dire avec un comple
> >> x,y. (Et oui, excel aime bien aussi les maths qui sont des maths... :-) )
> >> Tu verras comment les valeurs manquantes influent ou pas sur la droite.
> >> Le coeff de régression te permet de voir si tes valeurs sont ou non
> >> réellement reliées de façon linéaire. LE coeff de régression varie de 0
> >> à 1. Il faut savoir que les fonctions de droite de régression renvoient
> >> TOUJOURS une valeur, quelles que soient les données entrées mais si le
> >> coeff est du genre <0.6 tu peux être sur qu'en utilsant l'équantion de
> >> la droite pour faire une prévision, celle ci a toutes les chances d'être
> >> aussi juste que celle de Mme Irma dans sa caravane à la foire à la tomate...
> >>
> >> Pour le reste, je te laisse à tes "calculs"
> >> Pour ce qui est de la mesure, je suis biologiste. Les mesures c'est mon
> >> métier et travailler sur du matériel biologique implique d'avoir une
> >> variation importante dans les données et d'avoir une anlayse critique de
> >> ces données. Les statistiques sont exactement faites pour savoir comment
> >> interpréter des résultats qui par nature varient et savoir si on a le
> >> droit ou non de conclure.
> >> Que ce soit des températures, des relevés de production mensuelle, des
> >> choux ou des centenaires, peu importe. Ou bien il y a une relation
> >> mathématiques qui a un sens qui relie ces données et dans ce cas, il
> >> faut appliquer correctement cette fonction, ou bien il n'y en a pas et
> >> dans ce cas, il ne faut pas faire croire qu'on peut utiliser une
> >> fonction mathématique pour prédire l'avenir. Beaucoup d'économistes s'y
> >> sont d'ailleurs cassé les dents.
> >>
> >>
> >> Misange migrateuse
> >> XlWiki : Participez à un travail collaboratif sur excel !
> >> http://xlwiki.free.fr/wiki
> >> http://www.excelabo.net
> >>
> >> smr78 a écrit :
> >>> Bonjour,
> >>> Ah, je vois que ça chauffe dans les stats. Bonne nouvelle, j'ai trouvé une
> >>> formule qui me satisfait et fera un bon compromis.
> >>> Comme je n'ai pas de formule pour éliminer les valeurs nulles ou inférieures
> >>> à une seuil, j'ai réfléchi (!) et fait appel à Interpol comme le suggérait
> >>> François.
> >>>
> >>> Au lieu de supprimer les valeurs nulles, je les remplace par une valeur
> >>> moyenne des valeurs non nulles, automatiquement par formule. C'est illégal me
> >>> direz-vous encore mais j'y reviendrai.
> >>>
> >>> Je prends comme exemple
> >>> en colonne A 1,2,3,4,5 (pour les X) en colonne B 25,0,0,55,60
> >>>
> >>> n.b., je passe à 5 valeurs car mettre deux valeurs à 0 parmi 4, embrouille
> >>> tout.
> >>>
> >>> ma première formule magique est :
> >>> = {DROITEREG(N((B1:B5)=0)*MOYENNE(B1:B5)+(B1:B5))}
> >>> là-dedans,
> >>> N((B1:B5)=0 génère la matrice 0,1,1,0,0
> >>> N((B1:B5)=0)*MOYENNE(B1:B5) génère une matrice 0, 28, 28, 0, 0 où les
> >>> valeurs nulles sont remplacées par la moyenne des 3 valeurs non nulles (mais
> >>> basée sur 5 mesures)
> >>> et il suffit ensuite d'ajouter la matrice initiale pour obtenir 25, 28, 28,
> >>> 55, 60
> >>>
> >>> ma deuxième formule magique veut faire pareil, mais à la place de la moyenne
> >>> des 3 valeurs non nulles moyennées sur 5, calcule la moyenne sur 3 mesures!!
> >>>
> >>> =
> >>> {DROITEREG(N((B1:B5)=0)*SOMME(N(B1:B5>0)*(B1:B5)/NB.SI(B1:B5;">0"))+(B1:B5))}
> >>>
> >>> Si l'on veut remplacer les valeurs inférieures à un seuil situé en C1, on
> >>> remplace
> >>> =0 par <=$C$1, >0 par >$C$1 et ">0" par ... ">" & $C$1
> >>>
> >>> Et je reviens têtument sur mon thermomètre :
> >>> - supposons que l'on relève la température ou pression atmosphérique tous
> >>> les jours à midi ; si comme dans certains films on voit que la personne
> >>> lisant la mesure à midi le 15 du mois, doute du thermomètre/baromètre, tapote
> >>> dessus et relève une "bonne" mesure à midi et 5 secondes et inscrit en plus
> >>> de la mesure fausse 0°, la "bonne" mesure relevée à midi 5 secondes, je pense
> >>> qu'il n'y aura pas de crime mathématique à éliminer les températures égales à
> >>> 0°, sachant que le film se déroule au niveau de l'équateur, n'est-il pas?
> >>> et l'on peut très bien, pour établir la courbe des températures du mois,
> >>> considérer que la deuxième "bonne" mesure doit être datée du 15 du mois et
> >>> non pas du 16.
> >>>
> >>> J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok, c'est de
> >>> la mesure et non pas de la statistique théorique.
> >>>
> >>> Cela ne s'applique pas qu'aux températures, mon vrai problème concerne un
> >>> relevé de production mensuelle, donc pas besoin de corriger les dates non
> >>> plus.
> >>>
> >>> Bon, je m'y connais aussi en statistiques et analyses de marché et je
> >>> suis... Auvergnat!
> >>>
> >>> Ah, oui reste le coup du 25,0,0,50, deux valeurs nulles sur 4, et bien on
> >>> peut mettre n'importe quoi comme valeur de remplacement des 0 : 25,10,10, 50
> >>> ou 25, 30, 30, 50 on obtient toujours la même pente, seule change l'ordonnée
> >>> à l'origine.
> >> Alors là !!! compare la pente de tes deux droites et tu m'en diras des
> >> nouvelles. Fais ça par exemple avec des x du genre 1, 2,8, 24 au lieu de
> >> 1, 2, 3, 4 et tu verras que c'est complètement faux. Si ta formule
> >> magique te donne la même valeur alors c'est qu'elle est surtout
> >> magiquement fausse.
> >> Je ne peux pas ne pas réagir, désolée si je te parais puriste mais
> >> laisser ce genre de conclusion archivée dans google sans mettre en garde
> >> un futur lecteur sur l'absence de validité de cette approche... je ne
> >> peux pas. Ce n'est bien entendu pas du tout dirigé contre toi en tant
> >> qu'individu ! Chacun sa spécialité et tu es surement meilleur que moi en
> >> études de marché (et en choux auvergnats) :-)
> >>> Encore merci pour vos tuyaux.
>
Bonsour® smr78 avec ferveur ;o))) vous nous disiez :
> J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok,
> c'est de la mesure et non pas de la statistique théorique.
>
> Bon, je m'y connais aussi en statistiques et analyses de marché et je
> suis... Auvergnat!
;o)))
tu aurais dit : écossais, juif, banquier ou mafieux...
on n'aurait pas vu la différence !!!!
Encore heureux que tu ne sois pas ingénieur en constructions (BTP, Mecanique, astronautique)
;o)))
--
--
@+
;o)))
Bonsour® smr78 avec ferveur ;o))) vous nous disiez :
> J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok,
> c'est de la mesure et non pas de la statistique théorique.
>
> Bon, je m'y connais aussi en statistiques et analyses de marché et je
> suis... Auvergnat!
;o)))
tu aurais dit : écossais, juif, banquier ou mafieux...
on n'aurait pas vu la différence !!!!
Encore heureux que tu ne sois pas ingénieur en constructions (BTP, Mecanique, astronautique)
;o)))
--
--
@+
;o)))
Bonsour® smr78 avec ferveur ;o))) vous nous disiez :
> J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok,
> c'est de la mesure et non pas de la statistique théorique.
>
> Bon, je m'y connais aussi en statistiques et analyses de marché et je
> suis... Auvergnat!
;o)))
tu aurais dit : écossais, juif, banquier ou mafieux...
on n'aurait pas vu la différence !!!!
Encore heureux que tu ne sois pas ingénieur en constructions (BTP, Mecanique, astronautique)
;o)))
--
--
@+
;o)))
heuh, je viens de trouver un autre exemple peut-être plus parlant :
- je veux analyser la consommation d'essence du moteur de ma voiture Ã
partir d'un relevé journalier automatique.
- la consommation d'essence tombe à zéro les jours où je ne roule pas!
Ca ne m'intéresse pas de savoir quelle est la consommation d'essence
de mon moteur les jours où je ne roule pas, ces valeurs 0 vont
influer sur la tendance et fausser toute analyse ou prévisions.
Comme je ne peux bricoler l'appareil qui relève les consommations
tous les jours, je cherche donc à les ignorer et sans faire une étape
correctrice et sans routine.
heuh, je viens de trouver un autre exemple peut-être plus parlant :
- je veux analyser la consommation d'essence du moteur de ma voiture Ã
partir d'un relevé journalier automatique.
- la consommation d'essence tombe à zéro les jours où je ne roule pas!
Ca ne m'intéresse pas de savoir quelle est la consommation d'essence
de mon moteur les jours où je ne roule pas, ces valeurs 0 vont
influer sur la tendance et fausser toute analyse ou prévisions.
Comme je ne peux bricoler l'appareil qui relève les consommations
tous les jours, je cherche donc à les ignorer et sans faire une étape
correctrice et sans routine.
heuh, je viens de trouver un autre exemple peut-être plus parlant :
- je veux analyser la consommation d'essence du moteur de ma voiture Ã
partir d'un relevé journalier automatique.
- la consommation d'essence tombe à zéro les jours où je ne roule pas!
Ca ne m'intéresse pas de savoir quelle est la consommation d'essence
de mon moteur les jours où je ne roule pas, ces valeurs 0 vont
influer sur la tendance et fausser toute analyse ou prévisions.
Comme je ne peux bricoler l'appareil qui relève les consommations
tous les jours, je cherche donc à les ignorer et sans faire une étape
correctrice et sans routine.
heuh, je viens de trouver un autre exemple peut-être plus parlant :
- je veux analyser la consommation d'essence du moteur de ma voiture à
partir d'un relevé journalier automatique.
- la consommation d'essence tombe à zéro les jours où je ne roule pas!
Ca ne m'intéresse pas de savoir quelle est la consommation d'essence de mon
moteur les jours où je ne roule pas, ces valeurs 0 vont influer sur la
tendance et fausser toute analyse ou prévisions.
Comme je ne peux bricoler l'appareil qui relève les consommations tous les
jours, je cherche donc à les ignorer et sans faire une étape correctrice et
sans routine.
Si la solution formule n'existe pas, bien sûr je ferai autrement.
Merci encore.
"Misange" a écrit :J'arrête là désolée. Tu fais ce que tu veux, ce sont tes données. Ta
façon de procéder est fausse, ne peut en aucun cas être justifiée par un
quelconque calcul. Ce n'est pas parce que tu ne sais pas faire quelque
chose par formule que tu as le droit d'en utiliser une autre juste parce
qu'avec celle là tu obtiens un résultat qui te convient. J'attends de
voir la tête de ta collègue quand elle devra expliquer d'où sortent ses
prévisions...
Bonne chance pour la suite mais sans moi
Misange migrateuse
XlWiki : Participez à un travail collaboratif sur excel !
http://xlwiki.free.fr/wiki
http://www.excelabo.net
smr78 a écrit :Bonjour Misange,
Je ne discute pas pour dire que j'ai raison mais parce que la discussion
m'enrichit (et d'autres j'espère).
- Une première remarque, mes X connus ont un intervalle de progression
constant (comme les dates du jour), on peut les assimiler à 1,2,3.. et c'est
ce que fait DROITEREG quand ils sont omis. Mais je ne pense pas que cela
influe.
- Bien sûr j'ai fait le graphique de mes données et ajouté les courbes de
tendances et les résultats donnent des pentes différentes et j'ai choisi
celles qui me "plaisaient" le plus. Il est vrai que selon la position des
valeurs nulles, les résultats varient beaucoup avec DROITEREG et moins avec
les miens (surprenant oui à interprêter mais ça me va).
- J'ai essayé de mettre des valeurs vides au lieu de valeurs nulles ; eh
bien la formule DROITEREG (B1:B6) coince et donne #valeur, alors que la
courbe de tendance est bien tracée et que l'équation sur le graphique
apparaît avec des valeurs, est-ce parce que les valeurs calculées débordent
de la plage? (Excel 2003)
- J'aurais bien voulu faire l'extrapolation sur les deux valeurs encadrant
la valeur manquante, mais je ne sais pas faire par formule.
- Dans mon exemple température, je suis d'accord sur le fait que la mauvaise
valeur devrait être supprimée à la saisie, mais pas possible car la saisie
est faite, disons automatiquement à des milliers de kilomètres d'où se trouve
mon amie (débutante, débutante en Excel) qui reçoit des centaines de
résultat, pas moyen de modifier la saisie, ni de lire les données une par une
pour corriger à la main, d'où ma question sur comment l'enlever au moment de
l'analyse? J'ai essayé et proposé de faire un filtre élaboré, mais ça fait
une étape de plus qui gêne. (Quand je dis enlever, c'est effectivement
enlever le couple date et valeur pour les valeurs "fausses").
Merci encore pour tous ces conseils bio-éclairés.
Cordialement
"Misange" a écrit :Un dernier commentaire :
ce n'est pas parce qu'on obtient une valeur qu'elle a la moindre
signification.
Tu prends un exemple avec 5 valeurs. Fais le avec 6 en mettant comme 6°
couple 10,200 et regarde ce que donne ton calcul. Tu choisis la valeur
la plus jolie qui t'arrange si tu veux mais tu n'appelles plus cela des
maths par pitié.
Une interpolation ne se fait pas sur la moyenne des valeurs mais sur la
moyenne des bornes encadrant la valeur manquante ce qui est EXTREMEMENT
différent. C'est un peu de ma faute : par souci de simplification, j'ai
pris des couples de valeurs qui s'alignent justement le long d'une
droite. Il aurait mieux valu que je donne comme exemple des valeurs du genre
1 15
5 25
8 30
13 48
20 62
40 98
pente : 2.13
je fais sauter 48 et 62 la pente devient 2.11
j'utilise ta formule magique, la pente est de 1,5 !!
Plus drole : je fais sauter 25 et 62, La vraie pente est 2.1. Avec ta
formule ... 4.9 !
tu prends quoi au juste ? Je te conseille 4.9 si tu as envie de montrer
que la production progresse de façon spectaculaire cette année. MAis
attention si l'année prochaine elle tombe = 0.6 on t'en voudra...
Pour reprendre ton exemple des températures. La correction ne se fait
pas dans le tableau mais au moment ou on entre les valeurs dans le cas
que tu cite. Le 15 du mois, quand tu fais ta mesure à midi
15/10/2008 12:00==> 0°
15/10/2008 12:05 ==> 21°
ou bien tu écris dans ton tableau seulement
15/10/2008 12:00==> 21°C
La correction est donc faite au moment de la saisie.
ou bien tu écris les deux couples de valeurs (ce que tu fais
actuellement) et tu supprimes le premier.
Mais en aucun cas tu ne dois écrire les deux et effacer la valeur 0°
sans enlever la date et l'heure correspondante.
Je te propose une solution qui devrait te satisfaire : une résolution
graphique. Au lieu de récupérer les paramètres de ta droite en utilisant
une formule, trace un graphique en nuages de points (c'est impératif,
pas en courbe). Tu fais un clic droit sur ta série, ajouter une courbe
de tendance. La tu choisis linéaire et dans les options tu coches
afficher l'équation sur le graphique et fficher le coéfficient de
régression.
Si maintenant dans ton tableau de valeurs, il manque des y, la droite ne
sera tracée qu'avec les valeurs "complètes" c'est à dire avec un comple
x,y. (Et oui, excel aime bien aussi les maths qui sont des maths... :-) )
Tu verras comment les valeurs manquantes influent ou pas sur la droite.
Le coeff de régression te permet de voir si tes valeurs sont ou non
réellement reliées de façon linéaire. LE coeff de régression varie de 0
à 1. Il faut savoir que les fonctions de droite de régression renvoient
TOUJOURS une valeur, quelles que soient les données entrées mais si le
coeff est du genre <0.6 tu peux être sur qu'en utilsant l'équantion de
la droite pour faire une prévision, celle ci a toutes les chances d'être
aussi juste que celle de Mme Irma dans sa caravane à la foire à la tomate...
Pour le reste, je te laisse à tes "calculs"
Pour ce qui est de la mesure, je suis biologiste. Les mesures c'est mon
métier et travailler sur du matériel biologique implique d'avoir une
variation importante dans les données et d'avoir une anlayse critique de
ces données. Les statistiques sont exactement faites pour savoir comment
interpréter des résultats qui par nature varient et savoir si on a le
droit ou non de conclure.
Que ce soit des températures, des relevés de production mensuelle, des
choux ou des centenaires, peu importe. Ou bien il y a une relation
mathématiques qui a un sens qui relie ces données et dans ce cas, il
faut appliquer correctement cette fonction, ou bien il n'y en a pas et
dans ce cas, il ne faut pas faire croire qu'on peut utiliser une
fonction mathématique pour prédire l'avenir. Beaucoup d'économistes s'y
sont d'ailleurs cassé les dents.
Misange migrateuse
XlWiki : Participez à un travail collaboratif sur excel !
http://xlwiki.free.fr/wiki
http://www.excelabo.net
smr78 a écrit :Bonjour,
Ah, je vois que ça chauffe dans les stats. Bonne nouvelle, j'ai trouvé une
formule qui me satisfait et fera un bon compromis.
Comme je n'ai pas de formule pour éliminer les valeurs nulles ou inférieures
à une seuil, j'ai réfléchi (!) et fait appel à Interpol comme le suggérait
François.
Au lieu de supprimer les valeurs nulles, je les remplace par une valeur
moyenne des valeurs non nulles, automatiquement par formule. C'est illégal me
direz-vous encore mais j'y reviendrai.
Je prends comme exemple
en colonne A 1,2,3,4,5 (pour les X) en colonne B 25,0,0,55,60
n.b., je passe à 5 valeurs car mettre deux valeurs à 0 parmi 4, embrouille
tout.
ma première formule magique est :
= {DROITEREG(N((B1:B5)=0)*MOYENNE(B1:B5)+(B1:B5))}
là-dedans,
N((B1:B5)=0 génère la matrice 0,1,1,0,0
N((B1:B5)=0)*MOYENNE(B1:B5) génère une matrice 0, 28, 28, 0, 0 où les
valeurs nulles sont remplacées par la moyenne des 3 valeurs non nulles (mais
basée sur 5 mesures)
et il suffit ensuite d'ajouter la matrice initiale pour obtenir 25, 28, 28,
55, 60
ma deuxième formule magique veut faire pareil, mais à la place de la moyenne
des 3 valeurs non nulles moyennées sur 5, calcule la moyenne sur 3 mesures!!
=
{DROITEREG(N((B1:B5)=0)*SOMME(N(B1:B5>0)*(B1:B5)/NB.SI(B1:B5;">0"))+(B1:B5))}
Si l'on veut remplacer les valeurs inférieures à un seuil situé en C1, on
remplace
=0 par <=$C$1, >0 par >$C$1 et ">0" par ... ">" & $C$1
Et je reviens têtument sur mon thermomètre :
- supposons que l'on relève la température ou pression atmosphérique tous
les jours à midi ; si comme dans certains films on voit que la personne
lisant la mesure à midi le 15 du mois, doute du thermomètre/baromètre, tapote
dessus et relève une "bonne" mesure à midi et 5 secondes et inscrit en plus
de la mesure fausse 0°, la "bonne" mesure relevée à midi 5 secondes, je pense
qu'il n'y aura pas de crime mathématique à éliminer les températures égales à
0°, sachant que le film se déroule au niveau de l'équateur, n'est-il pas?
et l'on peut très bien, pour établir la courbe des températures du mois,
considérer que la deuxième "bonne" mesure doit être datée du 15 du mois et
non pas du 16.
J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok, c'est de
la mesure et non pas de la statistique théorique.
Cela ne s'applique pas qu'aux températures, mon vrai problème concerne un
relevé de production mensuelle, donc pas besoin de corriger les dates non
plus.
Bon, je m'y connais aussi en statistiques et analyses de marché et je
suis... Auvergnat!
Ah, oui reste le coup du 25,0,0,50, deux valeurs nulles sur 4, et bien on
peut mettre n'importe quoi comme valeur de remplacement des 0 : 25,10,10, 50
ou 25, 30, 30, 50 on obtient toujours la même pente, seule change l'ordonnée
à l'origine.
Alors là !!! compare la pente de tes deux droites et tu m'en diras des
nouvelles. Fais ça par exemple avec des x du genre 1, 2,8, 24 au lieu de
1, 2, 3, 4 et tu verras que c'est complètement faux. Si ta formule
magique te donne la même valeur alors c'est qu'elle est surtout
magiquement fausse.
Je ne peux pas ne pas réagir, désolée si je te parais puriste mais
laisser ce genre de conclusion archivée dans google sans mettre en garde
un futur lecteur sur l'absence de validité de cette approche... je ne
peux pas. Ce n'est bien entendu pas du tout dirigé contre toi en tant
qu'individu ! Chacun sa spécialité et tu es surement meilleur que moi en
études de marché (et en choux auvergnats) :-)Encore merci pour vos tuyaux.
heuh, je viens de trouver un autre exemple peut-être plus parlant :
- je veux analyser la consommation d'essence du moteur de ma voiture à
partir d'un relevé journalier automatique.
- la consommation d'essence tombe à zéro les jours où je ne roule pas!
Ca ne m'intéresse pas de savoir quelle est la consommation d'essence de mon
moteur les jours où je ne roule pas, ces valeurs 0 vont influer sur la
tendance et fausser toute analyse ou prévisions.
Comme je ne peux bricoler l'appareil qui relève les consommations tous les
jours, je cherche donc à les ignorer et sans faire une étape correctrice et
sans routine.
Si la solution formule n'existe pas, bien sûr je ferai autrement.
Merci encore.
"Misange" a écrit :
J'arrête là désolée. Tu fais ce que tu veux, ce sont tes données. Ta
façon de procéder est fausse, ne peut en aucun cas être justifiée par un
quelconque calcul. Ce n'est pas parce que tu ne sais pas faire quelque
chose par formule que tu as le droit d'en utiliser une autre juste parce
qu'avec celle là tu obtiens un résultat qui te convient. J'attends de
voir la tête de ta collègue quand elle devra expliquer d'où sortent ses
prévisions...
Bonne chance pour la suite mais sans moi
Misange migrateuse
XlWiki : Participez à un travail collaboratif sur excel !
http://xlwiki.free.fr/wiki
http://www.excelabo.net
smr78 a écrit :
Bonjour Misange,
Je ne discute pas pour dire que j'ai raison mais parce que la discussion
m'enrichit (et d'autres j'espère).
- Une première remarque, mes X connus ont un intervalle de progression
constant (comme les dates du jour), on peut les assimiler à 1,2,3.. et c'est
ce que fait DROITEREG quand ils sont omis. Mais je ne pense pas que cela
influe.
- Bien sûr j'ai fait le graphique de mes données et ajouté les courbes de
tendances et les résultats donnent des pentes différentes et j'ai choisi
celles qui me "plaisaient" le plus. Il est vrai que selon la position des
valeurs nulles, les résultats varient beaucoup avec DROITEREG et moins avec
les miens (surprenant oui à interprêter mais ça me va).
- J'ai essayé de mettre des valeurs vides au lieu de valeurs nulles ; eh
bien la formule DROITEREG (B1:B6) coince et donne #valeur, alors que la
courbe de tendance est bien tracée et que l'équation sur le graphique
apparaît avec des valeurs, est-ce parce que les valeurs calculées débordent
de la plage? (Excel 2003)
- J'aurais bien voulu faire l'extrapolation sur les deux valeurs encadrant
la valeur manquante, mais je ne sais pas faire par formule.
- Dans mon exemple température, je suis d'accord sur le fait que la mauvaise
valeur devrait être supprimée à la saisie, mais pas possible car la saisie
est faite, disons automatiquement à des milliers de kilomètres d'où se trouve
mon amie (débutante, débutante en Excel) qui reçoit des centaines de
résultat, pas moyen de modifier la saisie, ni de lire les données une par une
pour corriger à la main, d'où ma question sur comment l'enlever au moment de
l'analyse? J'ai essayé et proposé de faire un filtre élaboré, mais ça fait
une étape de plus qui gêne. (Quand je dis enlever, c'est effectivement
enlever le couple date et valeur pour les valeurs "fausses").
Merci encore pour tous ces conseils bio-éclairés.
Cordialement
"Misange" a écrit :
Un dernier commentaire :
ce n'est pas parce qu'on obtient une valeur qu'elle a la moindre
signification.
Tu prends un exemple avec 5 valeurs. Fais le avec 6 en mettant comme 6°
couple 10,200 et regarde ce que donne ton calcul. Tu choisis la valeur
la plus jolie qui t'arrange si tu veux mais tu n'appelles plus cela des
maths par pitié.
Une interpolation ne se fait pas sur la moyenne des valeurs mais sur la
moyenne des bornes encadrant la valeur manquante ce qui est EXTREMEMENT
différent. C'est un peu de ma faute : par souci de simplification, j'ai
pris des couples de valeurs qui s'alignent justement le long d'une
droite. Il aurait mieux valu que je donne comme exemple des valeurs du genre
1 15
5 25
8 30
13 48
20 62
40 98
pente : 2.13
je fais sauter 48 et 62 la pente devient 2.11
j'utilise ta formule magique, la pente est de 1,5 !!
Plus drole : je fais sauter 25 et 62, La vraie pente est 2.1. Avec ta
formule ... 4.9 !
tu prends quoi au juste ? Je te conseille 4.9 si tu as envie de montrer
que la production progresse de façon spectaculaire cette année. MAis
attention si l'année prochaine elle tombe = 0.6 on t'en voudra...
Pour reprendre ton exemple des températures. La correction ne se fait
pas dans le tableau mais au moment ou on entre les valeurs dans le cas
que tu cite. Le 15 du mois, quand tu fais ta mesure à midi
15/10/2008 12:00==> 0°
15/10/2008 12:05 ==> 21°
ou bien tu écris dans ton tableau seulement
15/10/2008 12:00==> 21°C
La correction est donc faite au moment de la saisie.
ou bien tu écris les deux couples de valeurs (ce que tu fais
actuellement) et tu supprimes le premier.
Mais en aucun cas tu ne dois écrire les deux et effacer la valeur 0°
sans enlever la date et l'heure correspondante.
Je te propose une solution qui devrait te satisfaire : une résolution
graphique. Au lieu de récupérer les paramètres de ta droite en utilisant
une formule, trace un graphique en nuages de points (c'est impératif,
pas en courbe). Tu fais un clic droit sur ta série, ajouter une courbe
de tendance. La tu choisis linéaire et dans les options tu coches
afficher l'équation sur le graphique et fficher le coéfficient de
régression.
Si maintenant dans ton tableau de valeurs, il manque des y, la droite ne
sera tracée qu'avec les valeurs "complètes" c'est à dire avec un comple
x,y. (Et oui, excel aime bien aussi les maths qui sont des maths... :-) )
Tu verras comment les valeurs manquantes influent ou pas sur la droite.
Le coeff de régression te permet de voir si tes valeurs sont ou non
réellement reliées de façon linéaire. LE coeff de régression varie de 0
à 1. Il faut savoir que les fonctions de droite de régression renvoient
TOUJOURS une valeur, quelles que soient les données entrées mais si le
coeff est du genre <0.6 tu peux être sur qu'en utilsant l'équantion de
la droite pour faire une prévision, celle ci a toutes les chances d'être
aussi juste que celle de Mme Irma dans sa caravane à la foire à la tomate...
Pour le reste, je te laisse à tes "calculs"
Pour ce qui est de la mesure, je suis biologiste. Les mesures c'est mon
métier et travailler sur du matériel biologique implique d'avoir une
variation importante dans les données et d'avoir une anlayse critique de
ces données. Les statistiques sont exactement faites pour savoir comment
interpréter des résultats qui par nature varient et savoir si on a le
droit ou non de conclure.
Que ce soit des températures, des relevés de production mensuelle, des
choux ou des centenaires, peu importe. Ou bien il y a une relation
mathématiques qui a un sens qui relie ces données et dans ce cas, il
faut appliquer correctement cette fonction, ou bien il n'y en a pas et
dans ce cas, il ne faut pas faire croire qu'on peut utiliser une
fonction mathématique pour prédire l'avenir. Beaucoup d'économistes s'y
sont d'ailleurs cassé les dents.
Misange migrateuse
XlWiki : Participez à un travail collaboratif sur excel !
http://xlwiki.free.fr/wiki
http://www.excelabo.net
smr78 a écrit :
Bonjour,
Ah, je vois que ça chauffe dans les stats. Bonne nouvelle, j'ai trouvé une
formule qui me satisfait et fera un bon compromis.
Comme je n'ai pas de formule pour éliminer les valeurs nulles ou inférieures
à une seuil, j'ai réfléchi (!) et fait appel à Interpol comme le suggérait
François.
Au lieu de supprimer les valeurs nulles, je les remplace par une valeur
moyenne des valeurs non nulles, automatiquement par formule. C'est illégal me
direz-vous encore mais j'y reviendrai.
Je prends comme exemple
en colonne A 1,2,3,4,5 (pour les X) en colonne B 25,0,0,55,60
n.b., je passe à 5 valeurs car mettre deux valeurs à 0 parmi 4, embrouille
tout.
ma première formule magique est :
= {DROITEREG(N((B1:B5)=0)*MOYENNE(B1:B5)+(B1:B5))}
là-dedans,
N((B1:B5)=0 génère la matrice 0,1,1,0,0
N((B1:B5)=0)*MOYENNE(B1:B5) génère une matrice 0, 28, 28, 0, 0 où les
valeurs nulles sont remplacées par la moyenne des 3 valeurs non nulles (mais
basée sur 5 mesures)
et il suffit ensuite d'ajouter la matrice initiale pour obtenir 25, 28, 28,
55, 60
ma deuxième formule magique veut faire pareil, mais à la place de la moyenne
des 3 valeurs non nulles moyennées sur 5, calcule la moyenne sur 3 mesures!!
=
{DROITEREG(N((B1:B5)=0)*SOMME(N(B1:B5>0)*(B1:B5)/NB.SI(B1:B5;">0"))+(B1:B5))}
Si l'on veut remplacer les valeurs inférieures à un seuil situé en C1, on
remplace
=0 par <=$C$1, >0 par >$C$1 et ">0" par ... ">" & $C$1
Et je reviens têtument sur mon thermomètre :
- supposons que l'on relève la température ou pression atmosphérique tous
les jours à midi ; si comme dans certains films on voit que la personne
lisant la mesure à midi le 15 du mois, doute du thermomètre/baromètre, tapote
dessus et relève une "bonne" mesure à midi et 5 secondes et inscrit en plus
de la mesure fausse 0°, la "bonne" mesure relevée à midi 5 secondes, je pense
qu'il n'y aura pas de crime mathématique à éliminer les températures égales à
0°, sachant que le film se déroule au niveau de l'équateur, n'est-il pas?
et l'on peut très bien, pour établir la courbe des températures du mois,
considérer que la deuxième "bonne" mesure doit être datée du 15 du mois et
non pas du 16.
J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok, c'est de
la mesure et non pas de la statistique théorique.
Cela ne s'applique pas qu'aux températures, mon vrai problème concerne un
relevé de production mensuelle, donc pas besoin de corriger les dates non
plus.
Bon, je m'y connais aussi en statistiques et analyses de marché et je
suis... Auvergnat!
Ah, oui reste le coup du 25,0,0,50, deux valeurs nulles sur 4, et bien on
peut mettre n'importe quoi comme valeur de remplacement des 0 : 25,10,10, 50
ou 25, 30, 30, 50 on obtient toujours la même pente, seule change l'ordonnée
à l'origine.
Alors là !!! compare la pente de tes deux droites et tu m'en diras des
nouvelles. Fais ça par exemple avec des x du genre 1, 2,8, 24 au lieu de
1, 2, 3, 4 et tu verras que c'est complètement faux. Si ta formule
magique te donne la même valeur alors c'est qu'elle est surtout
magiquement fausse.
Je ne peux pas ne pas réagir, désolée si je te parais puriste mais
laisser ce genre de conclusion archivée dans google sans mettre en garde
un futur lecteur sur l'absence de validité de cette approche... je ne
peux pas. Ce n'est bien entendu pas du tout dirigé contre toi en tant
qu'individu ! Chacun sa spécialité et tu es surement meilleur que moi en
études de marché (et en choux auvergnats) :-)
Encore merci pour vos tuyaux.
heuh, je viens de trouver un autre exemple peut-être plus parlant :
- je veux analyser la consommation d'essence du moteur de ma voiture à
partir d'un relevé journalier automatique.
- la consommation d'essence tombe à zéro les jours où je ne roule pas!
Ca ne m'intéresse pas de savoir quelle est la consommation d'essence de mon
moteur les jours où je ne roule pas, ces valeurs 0 vont influer sur la
tendance et fausser toute analyse ou prévisions.
Comme je ne peux bricoler l'appareil qui relève les consommations tous les
jours, je cherche donc à les ignorer et sans faire une étape correctrice et
sans routine.
Si la solution formule n'existe pas, bien sûr je ferai autrement.
Merci encore.
"Misange" a écrit :J'arrête là désolée. Tu fais ce que tu veux, ce sont tes données. Ta
façon de procéder est fausse, ne peut en aucun cas être justifiée par un
quelconque calcul. Ce n'est pas parce que tu ne sais pas faire quelque
chose par formule que tu as le droit d'en utiliser une autre juste parce
qu'avec celle là tu obtiens un résultat qui te convient. J'attends de
voir la tête de ta collègue quand elle devra expliquer d'où sortent ses
prévisions...
Bonne chance pour la suite mais sans moi
Misange migrateuse
XlWiki : Participez à un travail collaboratif sur excel !
http://xlwiki.free.fr/wiki
http://www.excelabo.net
smr78 a écrit :Bonjour Misange,
Je ne discute pas pour dire que j'ai raison mais parce que la discussion
m'enrichit (et d'autres j'espère).
- Une première remarque, mes X connus ont un intervalle de progression
constant (comme les dates du jour), on peut les assimiler à 1,2,3.. et c'est
ce que fait DROITEREG quand ils sont omis. Mais je ne pense pas que cela
influe.
- Bien sûr j'ai fait le graphique de mes données et ajouté les courbes de
tendances et les résultats donnent des pentes différentes et j'ai choisi
celles qui me "plaisaient" le plus. Il est vrai que selon la position des
valeurs nulles, les résultats varient beaucoup avec DROITEREG et moins avec
les miens (surprenant oui à interprêter mais ça me va).
- J'ai essayé de mettre des valeurs vides au lieu de valeurs nulles ; eh
bien la formule DROITEREG (B1:B6) coince et donne #valeur, alors que la
courbe de tendance est bien tracée et que l'équation sur le graphique
apparaît avec des valeurs, est-ce parce que les valeurs calculées débordent
de la plage? (Excel 2003)
- J'aurais bien voulu faire l'extrapolation sur les deux valeurs encadrant
la valeur manquante, mais je ne sais pas faire par formule.
- Dans mon exemple température, je suis d'accord sur le fait que la mauvaise
valeur devrait être supprimée à la saisie, mais pas possible car la saisie
est faite, disons automatiquement à des milliers de kilomètres d'où se trouve
mon amie (débutante, débutante en Excel) qui reçoit des centaines de
résultat, pas moyen de modifier la saisie, ni de lire les données une par une
pour corriger à la main, d'où ma question sur comment l'enlever au moment de
l'analyse? J'ai essayé et proposé de faire un filtre élaboré, mais ça fait
une étape de plus qui gêne. (Quand je dis enlever, c'est effectivement
enlever le couple date et valeur pour les valeurs "fausses").
Merci encore pour tous ces conseils bio-éclairés.
Cordialement
"Misange" a écrit :Un dernier commentaire :
ce n'est pas parce qu'on obtient une valeur qu'elle a la moindre
signification.
Tu prends un exemple avec 5 valeurs. Fais le avec 6 en mettant comme 6°
couple 10,200 et regarde ce que donne ton calcul. Tu choisis la valeur
la plus jolie qui t'arrange si tu veux mais tu n'appelles plus cela des
maths par pitié.
Une interpolation ne se fait pas sur la moyenne des valeurs mais sur la
moyenne des bornes encadrant la valeur manquante ce qui est EXTREMEMENT
différent. C'est un peu de ma faute : par souci de simplification, j'ai
pris des couples de valeurs qui s'alignent justement le long d'une
droite. Il aurait mieux valu que je donne comme exemple des valeurs du genre
1 15
5 25
8 30
13 48
20 62
40 98
pente : 2.13
je fais sauter 48 et 62 la pente devient 2.11
j'utilise ta formule magique, la pente est de 1,5 !!
Plus drole : je fais sauter 25 et 62, La vraie pente est 2.1. Avec ta
formule ... 4.9 !
tu prends quoi au juste ? Je te conseille 4.9 si tu as envie de montrer
que la production progresse de façon spectaculaire cette année. MAis
attention si l'année prochaine elle tombe = 0.6 on t'en voudra...
Pour reprendre ton exemple des températures. La correction ne se fait
pas dans le tableau mais au moment ou on entre les valeurs dans le cas
que tu cite. Le 15 du mois, quand tu fais ta mesure à midi
15/10/2008 12:00==> 0°
15/10/2008 12:05 ==> 21°
ou bien tu écris dans ton tableau seulement
15/10/2008 12:00==> 21°C
La correction est donc faite au moment de la saisie.
ou bien tu écris les deux couples de valeurs (ce que tu fais
actuellement) et tu supprimes le premier.
Mais en aucun cas tu ne dois écrire les deux et effacer la valeur 0°
sans enlever la date et l'heure correspondante.
Je te propose une solution qui devrait te satisfaire : une résolution
graphique. Au lieu de récupérer les paramètres de ta droite en utilisant
une formule, trace un graphique en nuages de points (c'est impératif,
pas en courbe). Tu fais un clic droit sur ta série, ajouter une courbe
de tendance. La tu choisis linéaire et dans les options tu coches
afficher l'équation sur le graphique et fficher le coéfficient de
régression.
Si maintenant dans ton tableau de valeurs, il manque des y, la droite ne
sera tracée qu'avec les valeurs "complètes" c'est à dire avec un comple
x,y. (Et oui, excel aime bien aussi les maths qui sont des maths... :-) )
Tu verras comment les valeurs manquantes influent ou pas sur la droite.
Le coeff de régression te permet de voir si tes valeurs sont ou non
réellement reliées de façon linéaire. LE coeff de régression varie de 0
à 1. Il faut savoir que les fonctions de droite de régression renvoient
TOUJOURS une valeur, quelles que soient les données entrées mais si le
coeff est du genre <0.6 tu peux être sur qu'en utilsant l'équantion de
la droite pour faire une prévision, celle ci a toutes les chances d'être
aussi juste que celle de Mme Irma dans sa caravane à la foire à la tomate...
Pour le reste, je te laisse à tes "calculs"
Pour ce qui est de la mesure, je suis biologiste. Les mesures c'est mon
métier et travailler sur du matériel biologique implique d'avoir une
variation importante dans les données et d'avoir une anlayse critique de
ces données. Les statistiques sont exactement faites pour savoir comment
interpréter des résultats qui par nature varient et savoir si on a le
droit ou non de conclure.
Que ce soit des températures, des relevés de production mensuelle, des
choux ou des centenaires, peu importe. Ou bien il y a une relation
mathématiques qui a un sens qui relie ces données et dans ce cas, il
faut appliquer correctement cette fonction, ou bien il n'y en a pas et
dans ce cas, il ne faut pas faire croire qu'on peut utiliser une
fonction mathématique pour prédire l'avenir. Beaucoup d'économistes s'y
sont d'ailleurs cassé les dents.
Misange migrateuse
XlWiki : Participez à un travail collaboratif sur excel !
http://xlwiki.free.fr/wiki
http://www.excelabo.net
smr78 a écrit :Bonjour,
Ah, je vois que ça chauffe dans les stats. Bonne nouvelle, j'ai trouvé une
formule qui me satisfait et fera un bon compromis.
Comme je n'ai pas de formule pour éliminer les valeurs nulles ou inférieures
à une seuil, j'ai réfléchi (!) et fait appel à Interpol comme le suggérait
François.
Au lieu de supprimer les valeurs nulles, je les remplace par une valeur
moyenne des valeurs non nulles, automatiquement par formule. C'est illégal me
direz-vous encore mais j'y reviendrai.
Je prends comme exemple
en colonne A 1,2,3,4,5 (pour les X) en colonne B 25,0,0,55,60
n.b., je passe à 5 valeurs car mettre deux valeurs à 0 parmi 4, embrouille
tout.
ma première formule magique est :
= {DROITEREG(N((B1:B5)=0)*MOYENNE(B1:B5)+(B1:B5))}
là-dedans,
N((B1:B5)=0 génère la matrice 0,1,1,0,0
N((B1:B5)=0)*MOYENNE(B1:B5) génère une matrice 0, 28, 28, 0, 0 où les
valeurs nulles sont remplacées par la moyenne des 3 valeurs non nulles (mais
basée sur 5 mesures)
et il suffit ensuite d'ajouter la matrice initiale pour obtenir 25, 28, 28,
55, 60
ma deuxième formule magique veut faire pareil, mais à la place de la moyenne
des 3 valeurs non nulles moyennées sur 5, calcule la moyenne sur 3 mesures!!
=
{DROITEREG(N((B1:B5)=0)*SOMME(N(B1:B5>0)*(B1:B5)/NB.SI(B1:B5;">0"))+(B1:B5))}
Si l'on veut remplacer les valeurs inférieures à un seuil situé en C1, on
remplace
=0 par <=$C$1, >0 par >$C$1 et ">0" par ... ">" & $C$1
Et je reviens têtument sur mon thermomètre :
- supposons que l'on relève la température ou pression atmosphérique tous
les jours à midi ; si comme dans certains films on voit que la personne
lisant la mesure à midi le 15 du mois, doute du thermomètre/baromètre, tapote
dessus et relève une "bonne" mesure à midi et 5 secondes et inscrit en plus
de la mesure fausse 0°, la "bonne" mesure relevée à midi 5 secondes, je pense
qu'il n'y aura pas de crime mathématique à éliminer les températures égales à
0°, sachant que le film se déroule au niveau de l'équateur, n'est-il pas?
et l'on peut très bien, pour établir la courbe des températures du mois,
considérer que la deuxième "bonne" mesure doit être datée du 15 du mois et
non pas du 16.
J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok, c'est de
la mesure et non pas de la statistique théorique.
Cela ne s'applique pas qu'aux températures, mon vrai problème concerne un
relevé de production mensuelle, donc pas besoin de corriger les dates non
plus.
Bon, je m'y connais aussi en statistiques et analyses de marché et je
suis... Auvergnat!
Ah, oui reste le coup du 25,0,0,50, deux valeurs nulles sur 4, et bien on
peut mettre n'importe quoi comme valeur de remplacement des 0 : 25,10,10, 50
ou 25, 30, 30, 50 on obtient toujours la même pente, seule change l'ordonnée
à l'origine.
Alors là !!! compare la pente de tes deux droites et tu m'en diras des
nouvelles. Fais ça par exemple avec des x du genre 1, 2,8, 24 au lieu de
1, 2, 3, 4 et tu verras que c'est complètement faux. Si ta formule
magique te donne la même valeur alors c'est qu'elle est surtout
magiquement fausse.
Je ne peux pas ne pas réagir, désolée si je te parais puriste mais
laisser ce genre de conclusion archivée dans google sans mettre en garde
un futur lecteur sur l'absence de validité de cette approche... je ne
peux pas. Ce n'est bien entendu pas du tout dirigé contre toi en tant
qu'individu ! Chacun sa spécialité et tu es surement meilleur que moi en
études de marché (et en choux auvergnats) :-)Encore merci pour vos tuyaux.
ben si, je suis ingénieur en BTP, mécanique, électronique, informatique,
chimie, thermodynamique, mathématiques, physique etc... le toutii.
"Modeste" a écrit :Bonsour® smr78 avec ferveur ;o))) vous nous disiez :J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok,
c'est de la mesure et non pas de la statistique théorique.
Bon, je m'y connais aussi en statistiques et analyses de marché et je
suis... Auvergnat!
;o)))
tu aurais dit : écossais, juif, banquier ou mafieux...
on n'aurait pas vu la différence !!!!
Encore heureux que tu ne sois pas ingénieur en constructions (BTP, Mecanique, astronautique)
;o)))
--
--
@+
;o)))
ben si, je suis ingénieur en BTP, mécanique, électronique, informatique,
chimie, thermodynamique, mathématiques, physique etc... le toutii.
"Modeste" a écrit :
Bonsour® smr78 avec ferveur ;o))) vous nous disiez :
J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok,
c'est de la mesure et non pas de la statistique théorique.
Bon, je m'y connais aussi en statistiques et analyses de marché et je
suis... Auvergnat!
;o)))
tu aurais dit : écossais, juif, banquier ou mafieux...
on n'aurait pas vu la différence !!!!
Encore heureux que tu ne sois pas ingénieur en constructions (BTP, Mecanique, astronautique)
;o)))
--
--
@+
;o)))
ben si, je suis ingénieur en BTP, mécanique, électronique, informatique,
chimie, thermodynamique, mathématiques, physique etc... le toutii.
"Modeste" a écrit :Bonsour® smr78 avec ferveur ;o))) vous nous disiez :J'espère que mes chers statisticiens ne vont pas s'étrangler. Ok,
c'est de la mesure et non pas de la statistique théorique.
Bon, je m'y connais aussi en statistiques et analyses de marché et je
suis... Auvergnat!
;o)))
tu aurais dit : écossais, juif, banquier ou mafieux...
on n'aurait pas vu la différence !!!!
Encore heureux que tu ne sois pas ingénieur en constructions (BTP, Mecanique, astronautique)
;o)))
--
--
@+
;o)))
Tu choisis la valeur la plus jolie qui t'arrange si tu veux mais tu
n'appelles plus cela des maths par pitié.
Tu choisis la valeur la plus jolie qui t'arrange si tu veux mais tu
n'appelles plus cela des maths par pitié.
Tu choisis la valeur la plus jolie qui t'arrange si tu veux mais tu
n'appelles plus cela des maths par pitié.