Faire un calcul de probabilité Avec excell ?

[Résolu/Fermé]
Signaler
Messages postés
243
Date d'inscription
vendredi 9 novembre 2007
Statut
Membre
Dernière intervention
22 novembre 2015
-
 koukou -
Bonjour,
Je voudrais faire une feuille avec Excell 2007
En 7 Colonnes et 198 lignes environs et lui faire faire une liste de probabilités avec 5 ou 7 cellules
Mais voilà, il me dit que ma formule contient des erreurs ou alors des fois il me mets "erreur #VALEUR" ou encore 3#NUM"
Bref ! pas moyen de s'en sortir ! Ossssecours ! :p
voici ( à titre indicatif ) la formule rentrée :
=PROB (E2:E198; F2:F198; G2:G198; 1:50)
Merci de votre aide
Jimmy
            
                
A voir également:

49 réponses

Messages postés
243
Date d'inscription
vendredi 9 novembre 2007
Statut
Membre
Dernière intervention
22 novembre 2015
38
Bonjour Bari (white)
Oui, j'ai besoin du détail des probabilités par évènement ou par plusieurs ensembles d'évènements , pas la somme.
Oui, j'ai que des valeurs numériques entières, mais la dans le tableau que tu medonnes, tu montres il donne des valeurs non entières, (1,2 ect...) alors que j'aimerai avoir des valeurs entières,
J'ai tapé la formule que tu m'as donné et le résultat:
I2: 0
I3:0,010471204
I4:0,004188482
I5:0,012565445
I6:0,007329843
I7:#N/A
Bref, ce n'est pas tout a fait le résultat que j'ésperais ( du mois pas de 0,104.... que des chiffres entiers tu comprends mieux ?
Il y a une autre chose que je ne saisis pas; pourquoi je dois entrer dans la zone (H2:H6) des chiffres 0.1.2.3.4 ?
3
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 42674 internautes nous ont dit merci ce mois-ci

Messages postés
42
Date d'inscription
vendredi 2 novembre 2007
Statut
Membre
Dernière intervention
21 septembre 2016
20
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[
Messages postés
42
Date d'inscription
vendredi 2 novembre 2007
Statut
Membre
Dernière intervention
21 septembre 2016
20
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 :)
Messages postés
42
Date d'inscription
vendredi 2 novembre 2007
Statut
Membre
Dernière intervention
21 septembre 2016
20
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
Messages postés
24182
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
19 septembre 2021
6 886
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
Messages postés
243
Date d'inscription
vendredi 9 novembre 2007
Statut
Membre
Dernière intervention
22 novembre 2015
38
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
Messages postés
42
Date d'inscription
vendredi 2 novembre 2007
Statut
Membre
Dernière intervention
21 septembre 2016
20
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 ?
Messages postés
243
Date d'inscription
vendredi 9 novembre 2007
Statut
Membre
Dernière intervention
22 novembre 2015
38
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
Messages postés
243
Date d'inscription
vendredi 9 novembre 2007
Statut
Membre
Dernière intervention
22 novembre 2015
38
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
Messages postés
243
Date d'inscription
vendredi 9 novembre 2007
Statut
Membre
Dernière intervention
22 novembre 2015
38
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 ?
Messages postés
42
Date d'inscription
vendredi 2 novembre 2007
Statut
Membre
Dernière intervention
21 septembre 2016
20
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.
non ttc c'est le 4+1 -3/453* et le ht c'est 4*7/ 23189
je suis la meilleur
Messages postés
243
Date d'inscription
vendredi 9 novembre 2007
Statut
Membre
Dernière intervention
22 novembre 2015
38
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
Messages postés
42
Date d'inscription
vendredi 2 novembre 2007
Statut
Membre
Dernière intervention
21 septembre 2016
20
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 ?
Messages postés
243
Date d'inscription
vendredi 9 novembre 2007
Statut
Membre
Dernière intervention
22 novembre 2015
38
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
Messages postés
243
Date d'inscription
vendredi 9 novembre 2007
Statut
Membre
Dernière intervention
22 novembre 2015
38
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
Messages postés
243
Date d'inscription
vendredi 9 novembre 2007
Statut
Membre
Dernière intervention
22 novembre 2015
38
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
Messages postés
42
Date d'inscription
vendredi 2 novembre 2007
Statut
Membre
Dernière intervention
21 septembre 2016
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
Messages postés
243
Date d'inscription
vendredi 9 novembre 2007
Statut
Membre
Dernière intervention
22 novembre 2015
38
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
Messages postés
24182
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
19 septembre 2021
6 886
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
Messages postés
243
Date d'inscription
vendredi 9 novembre 2007
Statut
Membre
Dernière intervention
22 novembre 2015
38
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
Messages postés
24182
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
19 septembre 2021
6 886
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