7/30/2019 excelsesion17
1/6
@INTE ACADEMIA Curso de Excel
Sesin 17. Estadstica
16.1. Frmulas estadsticas: media, varianza y desviacin tpica
Podemos usar frmulas estadsticas para analizar series de datos. Vamos a ver un
ejemplo. Tenemos dos tiendas de ropa y vamos a analizar las tallas que vendemos en 2
poblaciones distintas: Villaizquierda y Villaderecha.
1. Abra Excel y copie los siguientes datos.
2. Guarde el archivo con el nombre anlisis estadstico.
3. Hemos apuntado las tallas de las prendas que hemos vendido en las dos tiendas.
Ahora vamos a calcular la media de talla para cada poblacin. Haga clic en la celda
B12.
4. Escriba la siguiente frmula en la celda y pulse Intro:
=PROMEDIO(B2:B11)
5. Copie esta frmula hacia la derecha para calcular el promedio de Villaderecha.
6. La media es igual para los dos pueblos. Significa eso que las personas de los dos
pueblos son similares? Vamos a ver que no. Haga clic en la celda A13.
7. Escriba Varianza.
8. Haga clic en la celda B13.
1/6 AINTE INFORMTICA S.L.
7/30/2019 excelsesion17
2/6
@INTE ACADEMIA Curso de Excel
9. Escriba la siguiente frmula y pulse la tecla Intro.
=VAR(B2:B11)
10. Copie esta frmula hacia la derecha.
11. La varianza mide cuanto se desvan las medidas reales de la media. Observe como
en Villaizquierda las tallas que se han vendido varan mucho, hay tallas 36 y 56,
mientras que en Villaderecha las tallas no pasan de 42 y 48. Vamos a calcular otro
parmetro estadstico similar a la varianza.
12.Haga clic en la celda A14.
13.Escriba Desviacin tpica.
14.Haga clic en la celda B14.
15.Escriba la siguiente frmula y pulse Intro.
=DESVEST(B2:B11)
16.Copie la frmula hacia la derecha.
17. Observe como las desviaciones tpicas indican lo mismo que la varianza, la poblacin
de Villaizquierda es mucho ms variable en talla que la poblacin de Villaderecha.
18. Con estos datos, podemos redondear la desviacin tpica de Villaizquierda a 8 y la de
Villaderecha a 2. Podemos decir que la mayora de las personas de Villaizquierda
tienen una talla entre la 37, que es 45 menos 8 y la 72, que es 52 ms ocho. Sin
embargo, en Villaderecha la mayora de las tallas estn entre la 43 y la 47. De este
modo, podemos ajustar nuestro prximo pedido sabiendo que no debemos pedir tallas
muy grandes o muy pequeas para Villaderecha, mientras que tenemos que tener en
cuenta que en Villaizquierda las tallas que se piden son ms heterogneas y tenemos
que pedir de todo.
2/6 AINTE INFORMTICA S.L.
7/30/2019 excelsesion17
3/6
@INTE ACADEMIA Curso de Excel
16.2. Relacin entre 2 variables: grfica de dispersin
Cuando tenemos que estudiar la relacin que hay entre 2 variables, necesitamos
calcular coeficientes de regresin y correlacin. Vamos a hacer un ejercicio para ver si
hay relacin entre dos variables.
1. Abra Excel.
2. Copie los siguientes datos en la hoja.
3. Guarde el archivo con el nombre gasolina.
4. Vamos a estudiar la relacin que hay entre la cantidad de kilmetros que recorremos
(esta es la primera variable) y la cantidad de gasolina que gastamos (esta es la
segunda variable). Copie los siguientes datos en la hoja de clculo.
5. Ahora vamos a hacer un grfico que represente un punto para cada viaje. Seleccione
todos los datos desde la celda A1 hasta la celda K2.
6. Haga clic en el botn Asistente para grficos de la barra de herramientas.
7. Seleccione el tipo de grfico XY (Dispersin).
8. Haga clic en el botn Siguiente.
9. En la siguiente pantalla, haga clic tambin en el botn Siguiente.
10.En la ventana para definir los datos del Asistente para grficos, haga clic en el cuadro
Eje de valores(X) y escriba Kilmetros.11.En el cuadro Eje de valores(Y) escriba Litros.
3/6 AINTE INFORMTICA S.L.
7/30/2019 excelsesion17
4/6
@INTE ACADEMIA Curso de Excel
12.Haga clic en el botn Siguiente.
13.En la ventana para elegir la ubicacin del grfico, haga clic en la opcin En una hoja
nueva.
14.Escriba como nombre de la hoja Dispersin.
15.Haga clic en el botn Finalizar.
16.Estos puntos nos indican la relacin que hay entre los kilmetros que recorremos y los
litros que gastamos. Puede observar que, cuantos ms kilmetros recorremos, ms
litros gastamos.
17.Ahora una todos los puntos con una lnea. Observe que se forma una lnea que sube.
Esto indica que hay una correlacin positiva: a ms kilmetros, ms litros.
4/6 AINTE INFORMTICA S.L.
7/30/2019 excelsesion17
5/6
@INTE ACADEMIA Curso de Excel
Ejercicio. Use la siguiente tabla para analizar la relacin entre el nmero de cajas
abiertas en un supermercado y el tiempo medio de espera para cada cliente. Haga
una grfica para analizar la relacin entre el nmero de cajas abiertas y el tiempo
medio de espera.
Cajasabiertas 3 5 8 10 12 15Tiempomedio
de espera 15 12 10 8 5 3
16.3. Correlacin
Vamos a calcular con Excel el coeficiente de correlacin entre 2 variables. El ndice
de correlacin indica la relacin que hay entre dos variables. Su valor est entre -1 y 1.
Una correlacin de 1 indica que hay una relacin directa entre las dos variables. Si la
correlacin es -1 tenemos una correlacin inversa entre las dos variables. Si la correlacin
es cercana a 0 significa que no hay ninguna relacin entre las 2 variables.
1. Abra la hoja de clculo de kilmetros y litros que cre anteriormente.
2. Haga clic en la celda A4.
3. Escriba Coeficiente de correlacin.
4. Haga clic en la celda B4.
5. Escriba la siguiente frmula y pulse Intro.
=COEF.DE.CORREL(B1:K1;B2:K2)
Esta frmula calcula el coeficiente de correlacin entre las 2 variables. Tenemos que
especificar los dos rangos de celdas donde estn las variables.
5/6 AINTE INFORMTICA S.L.
Rango de datos de la primera variable
Rango de datos de la segunda variable
7/30/2019 excelsesion17
6/6
@INTE ACADEMIA Curso de Excel
6. Observe el resultado: 1. Esto significa que hay una relacin directa entre las dos
variables: a ms kilmetros recorridos, ms litros gastados.
Ejercicio. Calcule el coeficiente de correlacin del segundo ejemplo
Ejercicio. Calcule el coeficiente de correlacin y haga una grfica a partir de estos
datos sobre la latitud y la temperatura de unas ciudades europeas
Ciudad Atenas Lisboa Madrid Londres Paris Copenhague
Latitud (y) 37 39 40 53 49 54
Temperatura(x) 24 19 19 14 15 49
Ejercicio. Calcula el coeficiente de correlacin
X (masa de la pesa) 0 9 30 60 90 120 150 200 250 350Y (Alargamiento 0 0,5 1 3 5 6,5 8 10,2 12,5 18
Ejercicio. Calcula el coeficiente de correlacin en esta tabla donde RPC es la renta
per cpita y IN es el ndice de natalidad.
RPC 1 2 3 4 5 6 7 8 9 10
IN 10 6 8 5 7 4 1 3 8 2
6/6 AINTE INFORMTICA S.L.