En aquesta pràctica treballareu els procediments de l'Excel necessaris per resoldre un problema complet que correspon al model de la distribució binomial. Fonamentalment són aquests:

• Càlcul de probabilitats: funció DISTR.BINOM.
• Recerca de valors crítics: funció BINOM.CRIT.

També es comentarà, però, com a ampliació la funció PROBABILIDAD:

 
En aquesta pràctica treballareu amb la inserció de fórmules estadístiques

Considereu el fenomen aleatori "llançament d'una moneda no trucada 25 vegades, de forma independent". Fixarem l'atenció en la variable aleatòria nombre de cares que s'obtenen. Volem calcular les següents probabilitats:

1. Que s'obtinguin exactament 10 cares.
2. Que s'obtinguin com a màxim 10 cares.
3. Que s'obtinguin, si més no, 10 cares.
4. Que s'obtinguin entre 10 i 14 cares, ambdós valors inclosos.

Les funcions que s'empren a la primera part de la pràctica es troben fent Insertar|Función dintre de la categoria de funcions estadístiques (i, posteriorment, dintre d'Usadas recientemente). La primera que emprareu és DISTR.BINOM, que dóna la probabilitat i la probabilitat acumulada per als diversos valors que es poden observar com a resultat de la distribució binomial.

• Obriu un llibre de l'Excel i situeu-vos en una cel·la lliure.
• Feu Insertar | función; escolliu les funcions estadístiques trieu-ne, com ja s'ha indicat, DISTR.BINOM.
  
• Les dades del problema es tradueixen així al quadre de diàleg corresponent:  
  
   
• Alternativament, també hauríeu pogut posar en una cel·la la dada del problema (10 cares exactament) que correspon al nombre d'èxits dels quals volem calcular la probabilitat i llavors al requadre Núm_éxito del quadre de diàleg posar-hi la referència a aquella cel·la. Així tindríeu a la vista el nombre de cares i la seva probabilitat.
• I una altra possibilitat, com sempre que treballeu amb funcions, ja sabeu que és aprendre la sintaxi i escriure-ho directament. En aquest cas és =DISTR.BINOM(10;25;0,5;FALSO) o bé =DISTR.BINOM(10;25;0,5;0) si feu servir el 0 per indicar el valor lògic FALSO.

• Sigui com sigui, quan accepteu, veureu el resultat de la probabilitat demanada a l'apartat a) que, arrodonint, és 0,0974.

Vegeu ara que a l'apartat b) es demana la probabilitat que el nombre de cares que surti sigui més petit o igual que 10. Recordeu que aquest és el valor F(10) corresponent a la funció de distribució de probabilitat de la variable que estem estudiant, la probabilitat acumulada.

• Procediu com en el cas anterior però posant Verdadero (o bé 1) en lloc de Falso al requadre Acumulado del quadre de diàleg.
• Veureu que la probabilitat que surtin 10 cares o menys és 0,2122.

L'apartat c) es pot redactar també demanant la probabilitat que surtin 10 cares o més. Aquest és l'esdeveniment contrari a treure com a màxim 9 cares. Hem de calcular-ne la probabilitat i restar el resultat de la unitat.

• En una cel·la heu d'escriure el resultat de restar d'1 la probabilitat acumulada de fins a 9 cares. La fórmula és =1-DISTR.BINOM(9;25;0,5;VERDADERO).
  Podeu escriure tota la fórmula a mà o bé escriure el signe d'igual, llavors l'1 – i després, com en els apartats anteriors, inserir la funció que dóna la probabilitat de la distribució binomial.
• Veureu que la resposta a l'apartat c) és 0,885.

Per respondre l'apartat d) cal obtenir la probabilitat acumulada donada per F(14) i restar-li F(9). Alerta!, hem de restar aquest valor i no F(10)... hi esteu d'acord?

• La fórmula que escau és
  = DISTR.BINOM(14;25;0,5;VERDADERO)-DISTR.BINOM(9;25;0,5;VERDADERO).
  Podeu teclejar-la (amb el benentès que en lloc de VERDADERO podeu posar simplement 1) o bé fer servir dues vegades Insertar|función.
• Veureu que el resultat arrodonit és 0,673.

Amb aquest procediment que s'ha explicat i un ús adequat de l'opció de càlcul de la probabilitat i de la probabilitat acumulada, ja podeu resoldre el problemes directes de càlcul de probabilitat associats a fenòmens que es poden modelitzar per la distribució binomial. El quid de la qüestió és saber reconèixer clarament tots els elements definidors del model. Tanmateix l'apartat següent de la pràctica dóna una visió més global del problema.

En aquest apartat de la pràctica elaborareu un full de l'Excel que inclourà una taula de la distribució binomial de manera que si es canvien les dades definidores del model s'actualitzi automàticament la taula.

És clar que amb l'ajut d'una taula completa dels valors de la distribució adequada podem resoldre qualsevol problema de probabilitats corresponent al model binomial. Tal vegada aquest no és el mètode més eficaç, però sí que ajuda a comprendre millor els càlculs.

• Obriu un nou llibre de l'Excel. Poseu com a nom dels tres fulls Binomial, Poisson i Geomètrica, perquè fareu servir aquest llibre per construir les taules de probabilitat associades a aquests tres models. Guardeu el llibre amb el nom DISTRIBUCIONS-DISCRETES.XLS.
• Accediu al full corresponent a la distribució binomial.
• A les cel·les A1 i A3 escriviu els rètols Prob. èxit i Nombre proves, i a les cel·les A2 i A4 podeu entrar els valors per anar generant les diverses taules. Per enllaçar amb la part anterior de la pràctica, escriviu-hi ara 0,5 i 25.
• Per completar la informació del model, a les cel·les A5 i A7 escriviu-hi els rètols Mitjana i Desv. estàndard. Llavors, a les cel·les A6 i A8 escriviu les fórmules que ens donen aquests valors, és a dir, respectivament =A2*A4 ("traducció" de n · p) i =RAÍZ(A4*A2*(1–A2)) (que correspon a ).
• A la cel·la B1 escriviu el rètol Valor i ompliu les cel·les d'aquesta columna amb els nombres del 0 al 25. No oblideu que el 0 és un possible valor de la distribució binomial ni tampoc la possibilitat d'escriure aquests nombres correlatius per arrossegament o amb Edición | Rellenar | Series.
• A la cel·la C1 escriviu el rètol Probabilitat i a la cel·la C2 escriviu (a mà o amb Insertar | Función) la fórmula que dóna la probabilitat, a saber, =DISTR.BINOM(B2;$A$4;$A$2;0). Fixeu-vos bé en l'ús de referències absolutes perquè els arguments representin el valor del qual volem la probabilitat, el núm. ensayos, la prob. éxito, i la indicació que no sigui acumulada. Copieu aquesta fórmula perquè sigui vàlida en tot el rang C2:C27.
• A D1 escriviu Prob. acu. i llavors a la cel·la D2 escriviu la fórmula =DISTR.BINOM(B2;$A$4;$A$2;1) (anàloga a l'anterior però amb probabilitat acumulada) i copieu-la en tot el rang D3:D27.
• A partir de Formato | Celdas, podeu modificar alguns aspectes de Tramas i Bordes i així arribareu a la taula següent (que es mostra parcialment):
  

  Amb aquesta taula tenim a la vista (o gairebé) les respostes als diversos apartats del problema plantejat. Per exemple, l'apartat a) el tenim a la cel·la C12; l'apartat d) és la suma del rang indicat de color vermell (escriviu en una cel·la lliure la fórmula =SUMA(C12:C16) i en veureu el valor); els apartats b) i c) són suma d'altres rangs.

• Amplieu el nombre de dades de la columna B fins que abasti, per exemple, de 0 a 500.
• Si ara copiéssiu les fórmules que teniu a les columnes C i D en tot el rang que pugui interessar, ja funcionaria. Però, tanmateix, en moltes cel·les sortirien missatges d'error. Per això, abans de fer la còpia, escriurem fórmules condicionals que evitin aquests errors i deixin en blanc les caselles corresponents a valors que no es poden observar (els que són més grans que n, és a dir, el nombre que tenim a $A$4.
  • Escriviu a C2 la fórmula =SI(B2<=$A$4;DISTR.BINOM(B2;$A$4;$A$2;0);" ")
    
  • Escriviu a D2 la fórmula =SI(B2<=$A$4;DISTR.BINOM(B2;$A$4;$A$2;1);" ")
    
• Ara copieu la fórmula de C2 a tota la columna C (rang C3:C502) i la fórmula de D2 a la columna D (rang D3:D502).

Feu diverses proves, guardeu el llibre de l'Excel i ja podeu passar a l'altra part de la pràctica.

Els problemes de càlcul de probabilitats que hem vist a la primera part de la pràctica (i re-enfocat a la segona) s'han de complementar amb els que de vegades s'anomenen problemes inversos de probabilitat o, més exactament, recerca dels valors crítics. En aquests problemes es coneix el valor d'una probabilitat i es vol estudiar quin és el conjunt de valors que donen com a resultat aquesta probabilitat. Continuem l'estudi de l'exemple anterior, cosa que ens porta a l'estudi de la funció BINOM.CRIT de l'Excel.

En l'experiment aleatori del "llançament d'una moneda no trucada 25 vegades, de forma independent", en què X representa la variable aleatòria que fa el recompte del nombre de cares que surten, volem estudiar:

1. Quin és el menor nombre a per al qual la p[Xa] supera el 90 %?
2. Quin enunciat del nombre màxim de cares que sortiran podem fer si volem que la probabilitat d'encert sigui superior al 90 %?
3. Quina previsió podem fer que comenci dient "Sortiran més de... cares" si volem tenir una probabilitat superior al 80 % d'encertar?
4. Quin és el nombre més habitual de cares que sortiran (que donarem centrat en la mitjana, recordant el significat d'aquest paràmetre com a valor esperat) si entenem que habitual vol dir que pensem que tendirà a complir-se el 90 % de vegades que fem l'experiment?

En primera instància, ja que tenim a la vista la taula de la B(25, 0,5) podem intentar resoldre aquestes qüestions a ull.

1. N'hi ha prou que baixem per la columna D de la taula fins que veiem un número més gran que 0,9. La resposta a l'apartat a) és 16. Es compleix p[X 16] = 94,6 %.
2. Podeu reflexionar per veure que aquest apartat és idèntic a l'anterior, però formulat amb una variació en el llenguatge. Així, doncs, podem enunciar que "sortiran com a màxim 16 cares" i tenim una probabilitat d'encert superior al 90 %.
3. Hem de veure quin és el valor x més gran que té una probabilitat acumulada inferior a 0,2 perquè d'aquesta manera l'esdeveniment contrari (treure més de x cares) tingui una probabilitat superior al 80 %. Podeu comprovar que és 9.
4. No hi ha cap manera automàtica de fer-ho. Com que es preguntava el conjunt dels valors més habituals centrat en la mitjana (que és 12,5), podem anar provant amb els intervals [12, 13], [11, 14], etc... Es tracta, doncs, de sumar els valors de les probabilitats (ara no escauen probabilitats acumulades) que tenim als rangs C14:C15, C13:C16, etc., fins que trobem un resultat superior a 0,9.
   Si teniu actiu Ver | Barra de estado | Suma, aquestes sumes es mostren de manera dinàmica justament a la Barra de estado, a la part inferior de la pantalla.
   Podeu veure que la suma del rang C11:C18 (corresponent a l'interval [9, 16]) gairebé arriba al 90 %, però no ho aconsegueix.
   Per tant, la resposta estricta al problema plantejat és l'interval [8, 17].

1. La imatge següent mostra la fórmula que dóna el valor crític corresponent a una probabilitat acumulada de 0,9 en la distribució binomial B(n=25, p=0,5) i també indica com cal omplir el quadre de diàleg si es vol fer servir Insertar fórmula.
   

   Adoneu-vos que el missatge Devuelve el menor valor cuya distribución binomial acumulativa es mayor o igual que un valor de criterio (SIC), vol dir que la fórmula ens retorna el valor més petit (aquest és justament l'anomenat valor crític), per al qual la probabilitat acumulada és més gran o igual que un nombre donat.

    

2. Ja s'ha dit que els apartats b) i a) són idèntics.
3. Com ja s'ha explicat, cal buscar el nombre que correspon a BINOM.CRIT de 0,2 (aquest és el valor que posem al requadre Alfa del quadre de diàleg).
4. Si ens basem en la simetria respecte a la mitjana dels valors de la distribució binomial, podem pensar que els nombres que delimiten l'interval de probabilitat 90 % centrat en la mitjana són:
   • El nombre més petit que té una probabilitat acumulada superior al 95 %. Aplicarem, doncs, BINOM.CRIT amb Alfa = 0,95 i trobarem aquest valor.
   • El nombre més gran que té una probabilitat acumulada inferior al 5 %. Haurem d'aplicar BINOM.CRIT amb Alfa = 0,05 i trobarem justament el valor que ens interessa.
   Ara bé, el caràcter discret de la distribució fa que no s'aconsegueixi exactament el 90 %. Això i els problemes derivats de les aproximacions decimals fan que aquest procediment no sigui absolutament segur. Cal comprovar el resultat obtingut i, si cal, ampliar l'interval.

   Aquesta visió del problema per calcular un interval centrat en la mitjana s'aprofundirà a la pràctica següent.

Aquesta funció permet fer el càlcul de la probabilitat d'un interval de valors una vegada tenim la taula que dóna tots els possibles valors i les probabilitats d'una experiència aleatòria.

És clar que els valors i les probabilitats poden ser arbitraris (amb la precaució que les probabilitats sumin 1), però també poden correspondre a la taula d'una distribució, com és el cas que ens ocupa.

Per exemple, per calcular la probabilitat que quan es tiren 25 monedes enlaire surtin entre 10 i 14 cares, podeu fer, en una cel·la lliure, Insertar | Función | PROBABILIDAD i omplir així el quadre de diàleg:

Així, obtindreu, naturalment, el mateix valor 0,637 ja comentat anteriorment.