Faire un calcul de probabilité Avec excell ? [Résolu]

Réponse 2 / 49

baribari Messages postés 42 Date d'inscription vendredi 2 novembre 2007 Statut Membre Dernière intervention 21 septembre 2016 20
9 nov. 2007 à 15:55

Oups y a encore un truc que j'ai pas compris :)
Ben dans ma formule ça te donne qu'une seule valeur parceque ça fait la somme des probabilités des évènements 1 à 50
Toi tu as besoin du détail des probabilités par évènement ou par plusieurs ensembles d'évènements ?
Est ce que tu veux la liste des évènement dont la probabilité dans ta table est inférieure à 50% ?

Je crois que je ne visualise pas bien :)

PS: je divise par le nombre des valeurs numériques (NB) pour avoir une probabilité, sinon je n'ai que le nombre d'évènements.

PPS: FREQUENCE de permet de récupérer le nombre d'évènements (uniquement des valeurs numériques) selon des intervalles définis dans une autre liste.

Si tu veux par exemple la probabilité des 1 , 2 et des 3 (attention, seulement si tu n'as que des valeurs entières, sinon faut faire gafe aux intervalles, ça complique)
il te faut une colonne par exemple la zone H2:H6 avec les valeurs
0
1
2
3
4

ensuite tu sélectionne la zone (par exemple) I2:I7, tu tapes =FREQUENCE(A2:G198;H2:H6)/NB(A2:G192)
tu valides avec Ctrl+Shift+Entrée
et là tu as :
I2 = probabilité des évènemenents dans ]-infini , 0]
I3 = probabilité des évènemenents dans ]0 , 1]
I4 = probabilité des évènemenents dans ]1 , 2]
I5 = probabilité des évènemenents dans ]2 , 3]
I6 = probabilité des évènemenents dans ]3 , 4]
I7 = probabilité des évènemenents dans ]4 , +infini[

Réponse 3 / 49

baribari Messages postés 42 Date d'inscription vendredi 2 novembre 2007 Statut Membre Dernière intervention 21 septembre 2016 20
9 nov. 2007 à 13:34

Ok, je crois que je cerne mieux le truc : en fait tu n'as pas besoin de colonne qui définit la probabilité de chaque évènement...
Tu veux le nombre de valeurs comrises dans [1-50] divisé par le nombre de valeurs total.

=NB(SI(A2:G198<=50;SI(A2:G198>=1;A2:G198;"");""))/NB(A2:G198)
en validant avec Ctrl+Shift+Entrée (fonction matricielle)

Dis moi si c'est bien ça ...

Il y a peut être une fonction plus simple : FREQUENCE, peut être :)

Réponse 4 / 49

baribari Messages postés 42 Date d'inscription vendredi 2 novembre 2007 Statut Membre Dernière intervention 21 septembre 2016 20
14 nov. 2007 à 21:18

Salut Jimmy,

Content que tu apprécies le coup de main, même si je suis des fois un peu à côté de la plaque :)
Je ne sais pas très bien ce qu'est un résultat prévisionnel, mais si tu veux les valeurs les plus fréquentes du stock, j'ai trouvé sur ce site ( http://xlbysteph.free.fr/aideinformatique/action_denombrement.htm ) un truc qui ressemble à ce qu'on cherche :

Je l'ai un peu bidouillée pour que ça passe mieux avec les cellules vides de A2:G5

On commence par I2, par exemple:

I2 contient =MODE(A2;G198)

I3 contient =MODE(SI(SI($A$2:$G$198="";"";NB.SI(I$2:I2;$A$2:$G$198))=0;$A$2:$G$198;""))
Valider avec Ctrl+Shift+Entrée (fonction matricielle)

Ensuite il suffit de sélectionner I3 et de tirer la sélection vers le bas pour avoir la suite des n valeurs les plus fréquentes de A2:G198

ATTENTION: la fonction MODE donne la valeur la plus répétitive d'une plage, mais ne fonctionne pas pour des valeurs qui n'apparaissent qu'une fois... donc la liste oublie les valeurs n'existant qu'une fois ....

-----------------------------

Ensuite, si tu veux connaître le nombre de fois où le stock est à telle valeur, il faut mettre dans la colone suivante :

J2 contient =NB.SI($A$2:$C$5;I2)
puis étendre la formule de la même façon que pour I3

-----------------------------

PS: Le principe pour I3 c'est de reprendre A2:G198 de retirer les valeurs de I2 (ou de I2:I4 si on est en I5) , les espaces posent aussi problème, d'où les SI supplémentaires, puis de faire un MODE dessus pour avoir la valeur la plus répétitive.

PPS: On peut résoudre plus ou moins le problème des valeurs uniques
(mais ça rallonge un brin la formule et ça pose d'autres problèmes ... ) avec cette formule:
I3 contient =SI( ESTNA( MODE(SI(SI($A$2:$G$198="";"";NB.SI(I$2:I2;$A$2:$G$198))=0;$A$2:$G$198;"")) ) ;
MAX(SI(SI($A$2:$G$198="";"";NB.SI(I$2:I2;$A$2:$G$198))=0;$A$2:$G$198;"")) ;
MODE(SI(SI($A$2:$G$198="";"";NB.SI(I$2:I2;$A$2:$G$198))=0;$A$2:$G$198;"")) )
Ctrl+Shift+Entrée

J'espère que tu pourras trouver ton bonheur dans ce pavé :)
@ bientôt

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question

Réponse 5 / 49

eriiic Messages postés 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
22 nov. 2007 à 07:15

Bonjour jimy,

D'accord, je vois mieux ton besoin.
C'est donc prevision() qu'il te faut, en sachant que l'on considère que la progression est une droite.
Il te faut donc 1 colonne de dates (A) et 1 colonne de données (B). Seul le stock t'interesse, pas le prix.
Si tu as 6 colonnes de données différentes qui représente la même pièce il faut les mettre dans la même colonne. Sinon si c'est 6 pièces différentes il faut calculer pour chacune.
- tes dates en A1:A11
- tes données en B1:B11
- en D1 tu saisis 01/01/08
- en D2 tu saisis =D1+90 (pour avoir une prévision tous les 90 jours)
- tu sélectionnes D2, tu cliques sur le petit carré en bas à droite, et sans relacher tu descends la souris jusqu'à ce que tu aies assez de dates.
- en E1 tu saisis =ARRONDI(PREVISION(D1;$B$1:$B$11;$A$1:$A$11);0) et voilà tes nombres...

Ce que je t'ai fait inscrire en colonnes D et E tu peux le saisir sous tes stocks en A et B ce qui te permettra de faire un graphique qui est plus visuel.
Si ta progression n'est pas une droite mais une courbe qui monte de plus en plus vite, ou au contraire qui s'aplatit il faudrait faire autrement mais c'est plus compliqué.

eric

Réponse 6 / 49

Jimy6000 Messages postés 243 Date d'inscription vendredi 9 novembre 2007 Statut Membre Dernière intervention 22 novembre 2015 39
22 nov. 2007 à 11:52

Rebonjour Eric :)
Ha la vache ! c'était aussi simple ! je n'en reviens pas :-P et toutes ces formules que je me cassais la tete ! :o
Par contre pour les données en B1:B11 cela correspond a quoi comme données ? l'historique du stock ? car il est plus conséquent : (E4:I198) rien que cela !
Et pour (A1:A11) les dates de l'historique ? ou futur ?
Merci beaucoup ha :))) je suis content
Jimmy

Réponse 7 / 49

baribari Messages postés 42 Date d'inscription vendredi 2 novembre 2007 Statut Membre Dernière intervention 21 septembre 2016 20
9 nov. 2007 à 10:05

Bonjour jimmy,

Je ne comprend pas très bien ce que tu veux faire :
E2:E192 c'est la liste de tes évènements (en valeurs nuériques ?)
F2:F198 les probabilités associées, quelles valeurs utilise tu ?
G2:G198 la borne inférieure ou l'évènement recherché ?
1:50 à quoi correspond cette valeur ?

peux tu donner un exemple de tes colones ?

Réponse 8 / 49

Jimy6000 Messages postés 243 Date d'inscription vendredi 9 novembre 2007 Statut Membre Dernière intervention 22 novembre 2015 39
9 nov. 2007 à 10:18

Oui, La liste des évènements est bien E2:E198 mais comment fait on quand on a plusieur colonnes ?
F2:F198 est une deuxième collonnes d'évènements en bref il y a 7 colonnes comme celles là. 5 jusqu'à "I"
la borne inf, est 1 et la sup, est 50 ( voila à quoi correspond donc le (1:50)
Merci de ta réponse :) bien sympa ce site :))
Jimmy

Réponse 9 / 49

Jimy6000 Messages postés 243 Date d'inscription vendredi 9 novembre 2007 Statut Membre Dernière intervention 22 novembre 2015 39
9 nov. 2007 à 11:08

Excuse pour la faute de frappe ! le "5" juste avant "jusqu'à I " n'a rien a faire là c t juste une faute de frappe
Dsl...
J'ai téléchargé un tutoriel en format "pdf" mais il n'aide pas grand chose et est très confus de plus il est pour Excell 2003 lol
Jimmy

Réponse 10 / 49

Jimy6000 Messages postés 243 Date d'inscription vendredi 9 novembre 2007 Statut Membre Dernière intervention 22 novembre 2015 39
9 nov. 2007 à 14:37

Non, ça ne va pas, Il ne m'indique plus d'erreur mais : 1 alors qu'il devrait me donner une série de chiffres . :s
Est ce qu'il y a moyen d'avoir les différents résultats dans plusieur cellules ? sur une vigntaines de cellules par ex...
par contre je ne comprends pas pourquoi "diviser par le nombre de valeur total" !?
je cherche encore de mon côté :) ..... Remarque, j'ai déja un peu évolué depuis les début :))
fréquense ? il me donnerait les chiffres qui reviennent le plus souvent c ça ?
Jim
Ps, Qu'est ce qu'une référence circulaire ?

baribari Messages postés 42 Date d'inscription vendredi 2 novembre 2007 Statut Membre Dernière intervention 21 septembre 2016 20
9 nov. 2007 à 17:29

Euh, la référence circulaire c'est quand la valeur que tu calcule dans ta formule fait partie des donnés utilisées pour la calculer (ou de manière indirecte, la même chose avec plusieurs formules)
Ce qui fait qu'excel recalculerait sans fin la formule si il ne détectait pas ce problème.

calculie
22 juil. 2010 à 12:59

non ttc c'est le 4+1 -3/453* et le ht c'est 4*7/ 23189

calculie
22 juil. 2010 à 13:00

je suis la meilleur

Réponse 11 / 49

Jimy6000 Messages postés 243 Date d'inscription vendredi 9 novembre 2007 Statut Membre Dernière intervention 22 novembre 2015 39
12 nov. 2007 à 08:15

Bonjour, Baribari
Je me suis penché tout le week end et il y a une formule qui fonctionne, mais je n'arrive pas à contenir la limite basse et haute de mes résultats ( entre 1 et 50 )
soit ça me donne une valeur négative soit une valeur au dessus de 50 :s
Voici ma formule: =NB.SI(A2:G198;H2:H6)-(H2:H6<=50;SI(H2:H6>=1;)))
C'est bizzare !? il ne devrait logiquement pas dépasser les 50 ni tomber en négatif ?!?
Merci de ton aide ( ou tout autre aide est la bienvenue )
Bye
Jimmy

Réponse 12 / 49

baribari Messages postés 42 Date d'inscription vendredi 2 novembre 2007 Statut Membre Dernière intervention 21 septembre 2016 20
13 nov. 2007 à 18:22

Bonjour Jimmy,

Désolé pour le temps de réaction mais j'ai pris un week end un peu prolongé :)

Euh, par rapport à la formule avec FREQUENCE, les valeurs 1 2 3 4 permettent de définir les intervalles pour lesquels la fontion FREQUENCE va calculer le nombre de valeurs.

Mais ton dernier post me laisse un peu perplexe : tu m'expliques que ton résultat doit forcément être positif (ça je suis ok) et inférieur ou égal à 50, mais la zone A2;G198 contient 197*7=1379 cellules donc le nombre maximum de cellules correspondant à tes critères est 1379.

Du coup je me demande si tu ne ceche pas plutôt à avoir une liste des valeurs qui reviennent le plus souvent dans la zone A2:G198, c'est ça non ?

Réponse 13 / 49

Jimy6000 Messages postés 243 Date d'inscription vendredi 9 novembre 2007 Statut Membre Dernière intervention 22 novembre 2015 39
14 nov. 2007 à 03:50

Bonjour Bari.
oui, je cherche un peu ce genre d'info, étant donné que je n'arrive pas à faire fonctionner la formule de probabilité que tel ou tel chiffre revidra. Oui la zone A2:G198 contient bien (tous des chiffres entiers) 1379 cellules d'historique de stock ce que j'aimerais; est d'avoir un résultat prévisionel dans une 15 aines de cellules.
Probabilité ou Prévision je ne peux faire fonctionner ni l'une ni l'autre :(
pour les valeurs 1 2 3 4 merci de l'info je comprends mieux maintenant, ton aide est précieux en tout cas :))
Merci beaucoup
Jimmy

Réponse 14 / 49

Jimy6000 Messages postés 243 Date d'inscription vendredi 9 novembre 2007 Statut Membre Dernière intervention 22 novembre 2015 39
15 nov. 2007 à 05:54

Bonjour Bari
La 1ère formule (en commençant par le haut) fonctionne mais je n'ai qu'une seule valeur "1" ?
Par contre la deuxième celle que contient la cellule I2 =MODE(A2;G198) me renvoie #Valeur
Ouf que c'est complexe :p
Et pour finir la dernière formule : =SI( ESTNA( MODE(SI(SI($A$2:$G$198="";"";NB.SI(I$2:I2;$A$2:$G$198))=0;$A$2:$G$198; "")) ) ;
MAX(SI(SI($A$2:$G$198="";"";NB.SI(I$2:I2;$A$2:$G$198))=0;$A$2:$G$198;& quot;")) ;
MODE(SI(SI($A$2:$G$198="";"";NB.SI(I$2:I2;$A$2:$G$198))=0;$A$2:$G$198; "")) )
Ctrl+Shift+Entrée
Excel ne l'accepte pas, il dit qu'elle contient une erreur !? quand je clic "OK" sur ce dernier message le curseur se place là:
$;][& quot ( juste avant le & quot)
Pour indiquer où est l'erreur...
Vu la longueur de la formule, je ne suis vraiment pas en mesure de corriger quoi que ce soit ( aie aie aie ) déja que je plane à 15000 rien qu'en la regardant :p lol
Autre question pourquoi tous ces signes "$" et quelle fonctions ils ont
Merci encore Ouf !
Jimmy

Réponse 15 / 49

Jimy6000 Messages postés 243 Date d'inscription vendredi 9 novembre 2007 Statut Membre Dernière intervention 22 novembre 2015 39
17 nov. 2007 à 12:20

Bonjour Bari.
J'ai essayé de bidouiller la première formule pour voir ce que cela donnait mais il me donne toujours le même résultat: 1 ou 50 quoi que je fasse le résultat est invariable. Je t'explique la manip;
Je change A2 en A3 ou 4 etc... pour voir si il prédit approximativement la ligne du dessus... sans succes bien sûr.
Pour la deuxième formule; =SI( ESTNA( MODE.... rien à faire elle ne fonctionne pas car excel la rejette tout le temps.
J'espère que je ne te dérange pas trop :$ Mais merci quand même
Bye et bon week end
Jimmy

Réponse 16 / 49

baribari Messages postés 42 Date d'inscription vendredi 2 novembre 2007 Statut Membre Dernière intervention 21 septembre 2016 20
19 nov. 2007 à 20:20

Bonjour Jimmy,
T'inquètes tu me dérange pas ;)
Je suis juste rarement là le week end

Tu as raison : la partie de la formule est en fait MAX(SI(SI($A$2:$G$198="";"";NB.SI(I$2:I2;$A$2:$G$198))=0;$A$2:$G$198; "")) )
normalement ça doit passer (testé chez moi). (ne pas oublier de valider avec

Les dollars servent à "ancrer" (sais pas si c'est le terme juste) les coordonnées : pour le reste des valeurs on va étendre la formule vers le bas (en la tirant par le coin en bas à droite) et les coordonnées des cellules de la formule vont changer automatiquement dans les nouvelles cellules pour avoir la même position realtive par rapport à la nouvelle cellule, les dollars indiquent d'éviter cette renumérotation automatique.

Cela dit je trouve aussi que ça commence à faire des formules un tantinet pénibles :(

Je pense que si on veut que ça reste compréhensible, il va falloir passer par des macros ou du Visual Basic pour Applications (VBA)
Je vais réfléchir un peu à une façon de faire ..

@bientôt

Réponse 17 / 49

Jimy6000 Messages postés 243 Date d'inscription vendredi 9 novembre 2007 Statut Membre Dernière intervention 22 novembre 2015 39
20 nov. 2007 à 06:14

Bonjour Bari.
Non, je ne comprends pas, cela ne fonctionne pas :s
J'ai bien intercalé la formule : MAX(SI(SI($A$2:$G$198="";"";NB.SI(I$2:I2;$A$2:$G$198))=0;$A$2:$G$198; "")) )
entre =SI( ESTNA( MODE(SI(SI($A$2:$G$198="";"";NB.SI(I$2:I2;$A$2:$G$198))=0;$A$2:$G$198; "")) ) ;
Et : MODE(SI(SI($A$2:$G$198="";"";NB.SI(I$2:I2;$A$2:$G$198))=0;$A$2:$G$198; "")) )
Mais il me dit qu'il y a toujours une erreur !?
Il place le curceur la: )) ][ ) MODE(SI....
Jj'ai mis un ; puis " enfin j'ai tout essayé mais rien ne fait ou alors faut il supprimer le reste de la formule ?
Je ne sais vraiment pas quoi faire, :( ou alors j'ai mal compris ce que tu m'as envoyé comme indications, faut t'il ne laisser QUE cette formule ? mais la encore le résultat reste 50 sans varier :s
ppffff :( dans quoi je me suis lancé :(
enfin merci de ta patience.
Jim

Réponse 18 / 49

eriiic Messages postés 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
20 nov. 2007 à 07:41

Bonjour,

Pour avoir analyser une liste de valeur et te donner une idée de comment elles sont réparties tu as la moyenne et l'écart type.
La moyenne tu connais je suppose. L'écart type te donne une idée de leur dispersion, cad si peu ou beaucoup de valeurs s'écartent de la moyenne.
Ex avec une moyenne de 60, si tu as un écart type de 3 ça signifie que les 3/4 des valeurs (3/4 est donné à la louche) sont comprises entre 57 et 63. Tes valeurs sont donc assez resserrées autour de cette moyenne. Tu peux y associer le mini et le maxi pour avoir une meilleure vue sur tes données.
Toujours avec une moyenne de 60, un écrat type de 15 signifie que les 3/4 de tes valeurs sont comprises entre 45 et 75, donc beaucoup plus dispersées. Si c'est pour un stock dans ce cas il faudrait que tu majores le stock pour éviter une rupture de flux
Sous chaque colonne je mettrais donc :
=Moyenne(A1:A198)
=Ecartype(A1:198)
et accessoirement:
=Min(A1:A198)
=Max(A1:A198)

Tu as également la fonction Centile(A1:A198;x) qui peut te servir. Ex Centile(A1:A198;.0.9) te retourne la moyenne de 90% de valeurs en excluant les 10% les plus extrèmes (là l'explication est approximative).
Maintenant toutes ces fonctions ne te retourneront pas de nombres entiers, c'est à toi de prendre l'arrondi.
Peut-être que ça peut éclairer ta reflexion.
eric

Réponse 19 / 49

Jimy6000 Messages postés 243 Date d'inscription vendredi 9 novembre 2007 Statut Membre Dernière intervention 22 novembre 2015 39
21 nov. 2007 à 02:52

Bonjour à tous.
Merci Eric pour ton aide mais ce n'est pas un valeur de stock que je cherche mais Prévision de stock physique donc la formule Centile ne m'aide pas tellement.
D'ou mon intérêt pour la formule prévision ou Probabilité que je n'arrive pas a faire fonctionner :(
Merci à Tous
Jimmy

Réponse 20 / 49

eriiic Messages postés 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
21 nov. 2007 à 20:01

Bonsoir Jimy,

J'avais bien compris que c'était pour une prévision de stock.
Je vais développer ce que j'avais en tête mais note bien que ce n'est pas pour te convaincre mais juste pour te donner une idée.
D'autant plus que je n'ai aucune prétention dans les stats vu que c'était loin d'être ma partie préférée en math :-s

Déjà je ne pense pas que Probabilté() te soit d'une quelconque aide pour ton pb.
Prevision() se base sur une regression linéaire et c'est interessant si ta droite a une pente, donc si tu es en phase de croissance. Est-ce le cas ?

Sinon si tu es en rythme de croisière il faut utiliser je pense la moyenne(), que tu pondères en fonction de certains critères.
Si tu prends juste la moyenne il est certain que tu auras des ruptures de stock.
Si tu prends la moyenne augmentée de l'ecart type tu diminueras largement ces ruptures. Et pour les pièces sensibles tu l'augmentes de 2, voir 3 ecarts types (s'il n'est pas trop élevé).
Evidemment là dessus viennent se greffer tes impératifs de coût. Si la piece est très chère (ou très volumineuse et que le stock calculé t'oblige à construire un nouvel entrepot) c'est clair que ça n'est pas réaliste.
Autre chose que tu peux faire si c'est saisonnier, c'est de privilégier la moyenne() calculée sur une période identique. Par exemple prendre 3 fois la moyenne de l'hiver dernier + la moyenne() du dernier mois et diviser par 4
Donc je ferai un premier calcul avec la moyenne() + 1 ecart-type comme base de départ (un peu moins si c'est cher), et ensuite c'est ton expérience (le fameux feeling qui fait qu'on sait et qu'on ne sait pas pourquoi) qui te permettrait d'affiner.

Le rôle de centile() là dedans...
Imagine ton historique de stock sur 4 valeurs seulement. 30,28,1 (greve des trains et tu n'as pas été livré) et 33.
Moyenne = 23 et écart type = 14.7 !!!
=CENTILE(A1:A4;0.03) te retourne 3.52
Ce qui signifie que si tu veux excliure les 3% des valeurs les plus basses (pour eliminer les valeurs atypiques) tu calcules les moyennes avec les valeurs supérieures à 3.52 (donc à partir de 4) et là ta moyenne passe de 23 à 30,3 ce qui est plus réaliste. En parallèle ton ecart type qui était de 14,7 (valeurs très dispersées) passe à 1.5
Et tu fais pareil pour les valeurs hautes avec =CENTILE(A1:A4;0.97) et tu elimines les valeurs supérieures au résultat retourné.
Ceci n'est qu'un exemple, pas sûr que ça te fasse avancer mais ça t'aura peut-être ouvert d'autres façons d'aborder le pb.
Maintenant pour la réalisation tu auras de l'aide ici, mais il faudrait que tu aies une idée de la façon que tu aimerais faire, des parametres à prendre en compte etc.
Et tu as un avantage c'est d'avoir un bon historique apparemment, ce qui peut te permettre de tester la qualité de ton modéle et de l'affiner.

Bon courage
eric

Faire un calcul de probabilité Avec excell ?

49 réponses

Discussions similaires

Newsletters