EXCEL

 

DISTRIBUCIONES BIDIMENSIONALES

 

13.1 GRÁFICOS ESTADÍSTICOS

 

Vamos a construir una hoja de cálculo para representar gráficamente una tabla de valores con dos columnas  X  e  Y.

 

Para ello, introduce en las celdas los valores que se muestran a continuación:

 

	A	B	C	D	E
1		x	y
2		2,9	3,5
3		4,3	4,2
4		5,1	6,2
5		5,3	5,9
6		5,4	5,8
7		5,5	6,1
8		6,8	4,8
9		6,9	6,4
10		7,4	7,4
11

 

Para incluir un gráfico (nube de puntos)  seguimos los siguientes pasos:

 

·        Seleccionamos el rango de los datos (B2:C10):

 

 

 

·        En la barra de menús elegimos Insertar – Gráfico.

 

 

·        Elegimos la opción XY-(Dispersión) y aceptamos el resto de opciones por defecto pulsando el botón Terminar:

 

 

 

El gráfico aparecerá en la misma hoja que la tabla de datos.  Puedes moverlo con el ratón.

 

 

Modifica los valores iniciales y observa el efecto en el gráfico.

Para eliminar la leyenda Serie 1 basta hacer click sobre ella y pulsar Supr.

Para incluir la recta de regresión se hace click  con el botón derecho (secundario) del ratón sobre alguno de los puntos para abrir el menú contextual, y elegir en él la opción Agregar línea de tendencia....

 

 

 

Si queremos incluir la ecuación de la recta de regresión abrimos la ficha opciones en la ventana anterior y marcamos la opción Presentar ecuación en el gráfico.

 

 

Puedes cambiar de posición la ecuación desplazándola con el ratón.

 

Modifica los valores de la tabla inicial y observa los cambios que se producen en el gráfico. Cambia los valores para conseguir una inclinación negativa o que los puntos están más próximos a la recta (correlación fuerte).

Vamos a añadir a la hoja las fórmulas necesarias para obtener el coeficiente de correlación y los valores  m  y  b  de la recta de regresión  y = mx + b.

 

	A	B	C
1		x	y
2		2,9	3,5
3		4,3	4,2
4		5,1	6,2
5		5,3	5,9
6		5,4	5,8
7		5,5	6,1
8		6,8	4,8
9		6,9	6,4
10		7,4	7,4
11
12
13	m.margin	=PROMEDIO(B2:B11)	=PROMEDIO(C2:C11)
14	d.típicas	=DESVESTP(B2:B11)	=DESVESTP(C2:C11)
15	covarianza	=COVAR(B2:B11;C2:C11)
16	c.correlac	=COEF.DE.CORREL(B2:B11;C2:C11)
17
18	pte regres	=B15/(B14*B14)
19	b regresión	=-B18*B13+C13
20
21	x=	5,9
22	y=	=B18*B21+B19

 

La función  PROMEDIO  permite calcular la media arítmetica de los valores reseñados.

 

La función  DESVESTP  calcula la desviación típica,  COVAR  la covarianza y COEF.DE.CORREL  el coeficiente de correlación.

 

Comprueba que las celdas B18 y B19 calculan la pendiente de la recta de regresión y la ordenada en el origen de dicha recta.

 

La celda B22 permite predecir, según la ecuación de la recta de regresión, el valor de  y  correspondiente al valor de  x  que introduzcamos en B21.

 

 

Aunque EXCEL proporciona funciones que calculan directamente la media, la desviación típica, la covarianza y el coeficiente de correlación conviene que los obtengas a partir de las expresiones estudiadas en clase.

 

Para ello construye la siguiente hoja de cálculo (en una hoja nueva):

 

 

	A	B	C	D	E
1	x	y	xy	x2
2	2,9	3,5	=A2*B2	=A2*A2	=B2*B2
3	4,3	4,2	=A3*B3	=A3*A3	=B3*B3
4	5,1	6,2	=A4*B4	=A4*A4	=B4*B4
5	5,3	5,9	=A5*B5	=A5*A5	=B5*B5
6	5,4	5,8	=A6*B6	=A6*A6	=B6*B6
7	5,5	6,1	=A7*B7	=A7*A7	=B7*B7
8	6,8	4,8	=A8*B8	=A8*A8	=B8*B8
9	6,9	6,4	=A9*B9	=A9*A9	=B9*B9
10	7,4	7,4	=A10*B10	=A10*A10	=B10*B10
11	nº de puntos	9
12	=SUMA(A2:A10)	=SUMA(B2:B10)	=SUMA(C2:C10)	=SUMA(D2:D10)	=SUMA(E2:E10)
13
14	=A12/9	=B12/B11	=C12/B11-A14*B14	=D12/B11-A14*A14	=E12/B11-B14*B14
15
	Coef	=C14/RAIZ(D14*E14)	pend	=C14/D14

 

Observa que en la fila 14 aparece en los denominadores el número de puntos que debes incluir en la celda B11.

 

Para mejorar la legibilidad puedes reducir el tamaño de las columnas e incluir el cálculo de los parámetros en una nueva columna, aunque deberás modificar las referencias correspondientes.

Practica

 

Utiliza la hoja construida para resolver los ejercicios de las páginas 334, 335 y 339 del libro.

 

Comprueba los ejercicios resueltos 1 a 6 de las páginas 342 a 345 del libro. En el ejercicio 5 deberás introducir los datos dos veces, permutando  x  e  y. En el ejercicio 6 debes obtener primero las distribuciones marginales.

 

Resuelve los ejercicios propuestos 4 al 17 de las páginas 346 a 348 del libro.

 

 

Inserta una columna entre  B  y  C (las referencias se actualizan automáticamente). Copia en la nueva columna  C  los valores de  x  de la columna  A .

 

Haz un nuevo gráfico (con línea de tendencia y ecuación incluida) con los valores de las nuevas columnas  B  y  C.  Obtendrás la recta de regresión de  x  sobre  y.  Compárala con la de  y  sobre  x.

 

 

 

13.2 TABLAS DE DOBLE ENTRADA

 

En el caso de que los puntos estén agrupados hay que incluir una columna con las frecuencias, como en el ejemplo siguiente:

 

	A	B	C	D
1		X	y	frecuencia
2		2,9	3,5	2
3		4,3	4,2	3
4		5,1	6,2	3
5		5,3	5,9	2
6		5,4	5,8	4
7		5,5	6,1	1
8		6,8	4,8	2
9		6,9	6,4	1
10		7,4	7,4	3

 

 

Para hacer un gráfico seleccionamos los datos de las tres columnas (B2:D10), elegimos Insertar gráfico pero en el tipo de gráfico debemos seleccionar Burbujas. Al pulsar Terminar aparecerá un gráfico con un círculo para cada punto de tamaño proporcional a su frecuencia.

 

 

Como práctica puedes representar algunos de los ejercicios realizados en clase. Ten en cuenta que deberás modificar los rangos del gráfico para abarcar los datos.

 

Representa los datos del ejercicio 6 de la página 345 del libro.

---