En aquesta pràctica treballereu més aspectes relacionats amb la correlació i la regressió lineals. Concretament, haureu de:

• Estudiar la regressió lineal en cas que hi hagi subpoblacions
• Observar la importància dels valors atípics en el càlcul de la correlació lineal
• Conèixer la regressió logarítmica

Torneu a obrir el fitxer DADES74.XLS. A la pràctica 4, havíeu arribat a representar gràficament el núvol de punts i la recta de regressió per a les variables PES1 i PES3 (pes dels nois i les noies de 14 anys i el pes corresponent als 16 anys). Ens havíem preguntat què passaria si estudiéssim per separat el grup de noies i noies. Fem-ho.

Per començar, cal ordenar les dades respecte al sexe:

• Obriu el fitxer DADES74.XLS.
• Situeu el cursor a l'interior del rang de les dades del full Dades74.
• Accediu a Datos | Ordenar i comproveu que està activada l'opció Sí del final.
• Trieu l'ordenació per sexe i de manera ascendent. Premeu Aceptar.

• Seleccioneu el rang B2:B52 (el pes de les noies als 14 anys), premeu la tecla Control, i sense deixar de prémer-la seleccioneu el rang J2:J52 (el pes de les mateixes noies als 16 anys).
• Representeu el núvol de punts i la recta de regressió corresponent, tal com ho heu fet a la pràctica 4. Convé que tant els punts com la recta i la seva equació tinguin el mateix color (blau, per exemple). El nom de la llegenda hauria de ser Noies. Situeu el gràfic al rang A22:E41, aproximadament, del full Regressió. Heu d'arribar a un gràfic semblant a aquest:

• Feu clic sobre els punts del núvol i premeu el botó dret del ratolí. Accediu a Datos de origen | Serie
• Premeu el botó Agregar i entreu...
  • en Nombre Nois,
  • en Valores de X   =Dades74!$B$53:$B$111 (és a dir, el pes dels nois als 14 anys)
  • en Valores de Y =Dades74!$J$53:$J$111 (el pes dels nois als 16 anys)
• Premeu Aceptar
• Acabeu tot el procediment de canvi de forma i color dels punts del nou núvol i de representació de la corresponent recta de regressió. Podeu aconseguir que tot allò que fa referència a les dades dels nois quedi de color vermell

Fixeu-vos que les dues rectes són gairebé paral·leles.

Podeu calcular el coeficient de correlació lineal per a cada sexe, fent servir la funció =COEF.DE.CORREL( ), com heu fet en l'apartat anterior però aplicant-la als rangs de les noies i dels nois, respectivament. Compareu-los amb el que sortia per a tot el conjunt de valors.

A continuació, podeu fer prediccions de valors, com a la pràctica 4, però tenint en compte el sexe de cada individu. En aquest cas, cal que feu servir la recta de regressió corresponent a cada subpoblació.

• Entreu el rètol en negretaRecta de regressió de les noies a la cel·la F22.
• Copieu l'expressió d'aquesta recta de regressió del gràfic a la cel·la F23, com heu fet a la pràctica 4.
• Entreu els rètols PES1 i PES3 a G25 i I25, respectivament.
• Entreu 60 a G26, com a pes de noies de 14 anys.
• Entreu a I26 la fórmula =PRONOSTICO(G26;Dades74!J$2:J$52;Dades74!B$2:B$52). Aquesta funció calcula la predicció a partir del valor entrat a G26, seguint el model de la recta de regressió i considerant només les dades de les noies.
• Entreu més valors per sota de G26 i copieu la fórmula de I26 a les files corresponents per calcular més prediccions.
• Repetiu el procés per als nois, a partir de la cel·la F30. Fixeu-vos que la fórmula que heu d'entrar a I34 és =PRONOSTICO(G34;Dades74!J$53:J$111;Dades74!B$53:B$111), que està referida al rang de dades dels nois.

En definitiva, els resultats són:

Fixeu-vos en la diferència entre les prediccions que es fan per a les noies, les que es fan per als nois i les que havíeu fet globalment.

Els bitllets d'euro són iguals per a tots els països que s'han incoporat a la unió monetària europea. La taula següent en dóna les característiques: 

• Elaboreu un llibre de l'Excel anomenat BITLLETS.XLS que reculli aquestes dades. Feu que aquesta taula ocupi el rang A1:D8 del primer full del llibre.

A partir d'aquesta taula podeu fer un gràfic molt personalitzat, semblant al que es mostra seguidament: 

Donem tot seguit, a títol orientatiu, les indicacions detallades del procediment que us permetrà elaborar el diagrama anterior:

• Representeu gràficament, com ho heu fet en pràctiques anteriors, el núvol de punts corresponent a les variables Valor i Ample.
• Cliqueu sobre l'eix vertical fins que quedi seleccionat i premeu el botó dret del ratolí i accediu a Formato de ejes | Escala. Fixeu com a valor mínim 110, com a màxim 160 i com a Unidad mayor 10. 
• Seleccioneu la finestra gràfica i premeu el botó dret del ratolí. Accediu a Opciones de gráfico | Rótulos de datos. Activeu l'opció Mostrar rótulo. Premeu Aceptar.
• Per canviar el color i la forma de cada punt, feu clic sobre el punt i després d'una pausa ho torneu a fer. A continuació, prement el botó dret del ratolí, accediu a Formato de punto de datos | Tramas i feu els canvis pertinents.

Ara podeu estudiar la correlació lineal entre les variables Valor, Ample i Alt.

• Accediu a Herramientas | Análisis de datos | Coeficient de correlación. El rang d'entrada ha de ser A1:C8 amb els rètols a la primera fila. Surt una taula com aquesta:

Tot seguit s'analitzen i es comenten aquests valors.

• El coeficient de correlació valor/ample, que és la parella de variables que apareix en el gràfic que s'ha donat com a exemple, és alt. En canvi, l'aspecte del núvol de punts no és pas el d'una recta. Després ho millorareu, però ara aquest resultat és degut al fet que hi ha molt poques dades (no s'hauria de fer estadística amb tan poques dades!). Tanmateix, l'exemple que estudiem serveix per fer aquest i d'altres comentaris didàctics. 
• També és molt elevat el coeficient de correlació ample/alt, cosa que, en aquest cas, ens diu que les dimensions dels bitllets guarden poc més o menys les mateixes proporcions.

En canvi, el coeficient de correlació valor/alt és més baix... 

Però és que si mireu la taula veureu de seguida que els bitllets de 200 € i 500 € són atípics pel que fa a l'ample, que no segueix la cadència creixent dels anteriors, sinó que repeteix l'ample del bitllet de 100 €. Hem de tenir molt present que l'existència de valors atípics condiciona en gran manera el valor del coeficient de correlació, sobretot si, com en aquest cas, estem treballant amb un conjunt poc nombrós de dades. Què succeeix si fem l'estudi de la correlació lineal sense tenir en compte aquests dos valors atípics?

• Accediu a Herramientas | Análisis de datos | Coeficient de correlación. El rang d'entrada ha de ser ara A1:C6, a causa de l'exclusió dels bitllets de 200 i 500. Surt una taula com aquesta:

Adoneu-vos de com han augmentat tots els valors després de suprimir els dos valors atípics!

Dèiem més amunt que el coeficient de correlació valor/ample, que és la parella de variables que apareix al gràfic que s'ha donat com a exemple, és alt. En canvi, l'aspecte del núvol de punts no és pas el d'una recta. Realment, l'aspecte és el de la funció logarítmica. Aprofitem l'exemple per analitzar un cas de regressió no lineal.

• Inseriu una nova columna entre la columna A i la B. Entreu a la primera cel·la el títol Logvalor.
• Entreu a B2 la fórmula =LN(A2), que calcula el logaritme neperià del contingut de la cel·la A2. Copieu-la a la resta de la columna.
• Estudieu la correlació entre les variables Logval i Ample i traieu conclusions. Podeu fer el mateix amb Logval i Alt. Veureu que, en aquest exemple, la correlació logarítmica (és a dir, correlació lineal entre la variable estudiada i la variable Logval) explica perfectament el model de disseny dels bitllets:

Podeu veure, gràficament, com la regressió logarítmica s'ajusta molt bé al núvol de punts.

Per arribar a aquest gràfic, només s'ha de seguir el procediment explicat en la pràctica 4, a l'hora de dibuixar la recta de regressió, i triar el model logarítmic en comptes del lineal.

Si volguéssim dissenyar un bitllet de 300 €, quin ample li donaríem? Per contestar aquesta pregunta, podeu fer servir, directament, la fórmula que apareix al gràfic anterior o bé fer servir la funció PRONOSTICO entre les variables Logvalor i Ample:

• Entreu a A11 (per exemple) el nombre 300.
• Entreu a B11la fórmula =LN(A11).
• Entreu a C11 la fórmula =PRONOSTICO(B11;C2:C8;B2:B8).

Podeu comprovar que, amb els dos mètodes, surt el mateix resultat.

En aquest estudi final us suggerim de repassar diversos conceptes i procediments ja treballats anteriorment i us comentem l'experiència de Galton que dóna nom a la regressió. Fareu servir el fitxer TERCERJM.XLS.

• A l'únic full del llibre TERCERJM.XLS definiu una nova variable ALMIG com la mitjana de les alçades ALPARE i ALMARE. Per fer-ho, inseriu una nova columna entre la C i la D. Entreu el rètol ALMIG en la nova D1.
• Entreu a D2 la fórmula =PROMEDIO(B2;C2) i copieu-la a la resta de la columna. Ja heu definit la nova variable.
• Entreu a H1 el rètol Mitjana ALMIG i a I1 el rètol Mitjana ALALUMNE.
• Entreu a H2 la fórmula =PROMEDIO(D2:D101) i copieu-la sobre I2. Heu calculat les mitjanes de les alçades dels pares i la dels fills.

Podeu observar que la mitjana d'aquesta última variable -alçada mitjana dels fills- és superior al de la primera - alçada mitjana dels pares. És allò que es diu que "la raça tendeix a millorar".

En canvi, l'experiència de Galton postula una regressió. Potser la denominació sembla contradictòria, però és que aquest és un altre concepte.

Si per un moment suposem que la població dels pares és una mostra representativa de la població catalana, veureu que es pot considerar l'alçada mitjana de la generació paterna igual a 165,2 cm, que és el resultat que heu obtingut a la cel·la H2. A continuació, ordeneu les dades respecte a la variable ALMIG.

• Situeu el cursor a l'interior del rang A1:F101.
• Accediu a Datos | Ordenar i feu que s'ordenin de manera ascendent respecte a ALMIG.

D'aquesta manera heu ordenat les dades respecte a l'alçada mitjana dels pares.

• Calculeu les mitjanes de l'alçada dels pares baixos i dels seus fills. És a dir, feu servir les fórmules =PROMEDIO(D2:D42) i =PROMEDIO(E2:E42). El rang D2:D42 correspon a les alçades mitjanes inferiors a la mitjana 165,2 cm de la cel·la H2. Al rang E2:E42 hi ha les alçades dels fills.
• Calculeu les mitjanes de l'alçada dels pares alts i dels seus fills. Ara fareu servir les fórmules =PROMEDIO(D43:D101) i =PROMEDIO(E43:E101). Teniu la mitjana de les alçades dels pares amb una alçada superior a 165,2 cm i la dels fills.

Veureu que la mitjana d'alçades dels fills de pares baixos ha augmentat i s'ha acostat a la mitjana estimada de la població global, i veureu també que la mitjana d'alçades dels fills de pares alts ha disminuït i s'ha acostat també a la mitjana de la població. Això és el que afirma l'experiment de Galton de la regressió filial: la població tendeix a uniformitzar-se perquè les mitjanes per subpoblacions regredeixen cap a la mitjana de la població. L'exemple anterior, doncs, ho il·lustra.

• regredir no és una paraula normativa. Tanmateix, com que Galton feia servir un mot semblant amb el significat de tendir a acostar-se, hem volgut mantenir-la en la nostra explicació perquè justament ha donat nom al concepte que ara estduiem. Convé insistir, doncs, que en l'àmbit de treball de l'estadística regressió no és un sinònim d'anar enrere sinó de buscar el millor ajust per a un conjunt de dades.

A la pràctica següent, tornareu a treballar aquesta situació pràctica.