Facultat d’Economia i Empresa
Estadística I
Guió Pràctica3 d’Excel
El objetivo de esta práctica es el cálculo de los estadísticos de posición.
Antes de hacer esta práctica en la hoja Octubre 2016 eliminamos la columna IDSEC, de esta forma todas las
variables están en la misma columna en los tres meses:
1. Calcula para cada mes la media aritmética de los gastos de alojamiento, transporte, bares y
restaurante y gasto total.
Para calcular la media de una variable podemos usar la función PROMEDIO()/MITJANA(). Esta
función tiene como único argumento las celdas donde se encuentran los datos. Así, una opción es
crear una hoja nueva que denotamos por Gastos y escribir en la celda B1, C1, D1 y E1 Gast.
Alojamiento, Gast. Transporte, Gast. Barest y Gasto total. De la celda A2 a la A4 escribimos Marzo,
Agosto y Octubre. La media del Gasto en alojamiento la introducimos en la celda B2, escribiendo la
formula: =PROMEDIO('Marzo 2016'!E2:E2809). La media en Marzo para el resto de gastos las
obtenemos copiando esta fórmula en las celdas contiguas (de la fila 2, cosa que podemos hacer
porque las variables están de forma contigua en la hoja Maro 2016 en el fichero de datos).
Para obtener la media del gasto en alojamiento en Agosto, escribimos en la celda B3
=PROMEDIO('Agosto 2016'!E2:E5098). Las medias del resto de gastos en Agosto las obtenemos
copiando esta fórmula en las celdas contiguas de la fila 3.
Finalmente, la media del gasto en alojamiento de Octubre la calculamos escribiendo en la celda B4
=PROMEDIO('Octubre 2016'!E2:E2365). Como en los casos anteriores la media del resto de
gastos la obtendremos copiando la formula en las celdas contiguas de la fila 4.
2. Representar a través de un gráfico de líneas la evolución en esos tres meses de los diferentes gastos
medios.
Para representar gráficamente la evolución de los gatos, seleccionamos las celdas de la A1 hasta la
celda E4 (incluimos todas las celdas de la tabla). En la pestaña Insertar, seleccionamos insertar
gráfico, gráficos de línea, que da varias opciones. La que nos interesa es la que representa los
meses en el eje de las X’s y, en el de las Y’s, el gasto medio, usando una línea diferente para cada
tipo de gasto.
Facultat d’Economia i Empresa
Estadística I
Guió Pràctica3 d’Excel
3. Construye un gráfico que ofrezca la misma información que el de la pregunta anterior, pero usando
en este caso un gráfico de barras.
Como en el ejercicio anterior seleccionamos las celdas de la A1 hasta la E4. En la pestaña Insertar
seleccionamos insertar gráfico, pero en este caso seleccionamos un gráfico de Columnas.
Básicamente podemos construir dos tipos de gráficos: uno en el que las columnas las agrupa en tres
grupos (cada uno de 4 columnas), un grupo por cada mes;
Facultat d’Economia i Empresa
Estadística I
Guió Pràctica3 d’Excel
o uno que agrupa las columnas en 4 grupos (de 3 columnas cada uno), uno por cada tipo de gasto:
Facultat d’Economia i Empresa
Estadística I
Guió Pràctica3 d’Excel
También podríamos representar una columna para cada mes (o cada gasto) y en cada columna en
distinto color se representarían la frecuencia de cada gasto (o de cada mes). Este podría ser
interesante si incluimos otros gastos y no incluimos el gasto total (que vendría representado por la
altura de la columna).
4. Hacer el mismo análisis que el de las preguntas 1, 2 y 3, pero en este caso para los gastos por día
(GASTO/NPERNOC).
Nótese que en este caso la variable gasto por día es una variable cociente y, por lo tanto, si
queremos calcular el gasto total (por ejemplo) medio por día de los viajes realizados en Octubre, en
vez de usar la media aritmética de la variable gasto total diario (construida como la división entre
el gasto total y el número de pernoctaciones) deberíamos obtenerlo como la división de la suma de
los gastos totales (de todos los viajes de Octubre) entre la suma de las pernoctaciones (de todos los
viajes de Octubre).
5. Calcular el número de pernoctaciones mínimo que se realizan en la mitad de los viajes de Octubre
que más pernoctaciones realizan. Compararlo con el resto de meses.
El número de pernoctaciones mínimo que se realizan en la mitad de los viajes que más
pernoctaciones realizan viene dado por la mediana. Para calcular la mediana usaremos la función
de Excel MEDIANA(), cuyo argumento son los valores de la variable en la muestra. En este caso
=MEDIANA('Octubre 2016'!D2:D2365).
6. Calcular el motivo más frecuente de los viajes en Octubre. Compararlo con el del resto de meses.
El destino más frecuente viene dado por la moda. Para obtenerla usaremos la función de Excel
[Link](:) o [Link](:) cuyo argumento son los valores de la variable en la
muestra. En el primer caso nos devolverá más de una moda (cuando haya más de una), la segunda
solo nos devolverá una. Si queremos que nos devuelva al menos 2 modas, seleccionaremos 2 celdas,
copiaremos la formula =[Link]('Octubre 2016'!C2:C2365) y daremos a la tecla
Control+Shift+Enter (forma matricial) y nos devolverá (como mucho) 2 modas diferentes (de
haberlas). Si solo hay una moda, las dos modas que nos devuelva serán iguales. Si son diferentes,
podemos probar a que nos dé 3 (y así sucesivamente):
7. Calcula la tabla de frecuencias para la variable edad (en la muestra Octubre 2016) agrupada en los
siguientes intervalos: [15,30], (30,45], (45,60], (60,85]
Calcula la media aritmética y la mediana para la variable continua (usando todos los datos) y
aproximándolas a través de la tabla de frecuencias que acabas de calcular.
Calculamos la tabla de frecuencias usando alguno de los métodos vistos en las hojas anteriores. En
una hoja nueva, en las celdas E5 a E8 colocamos el límite inferior de los intervalos, y en las celdas
F5 a F8 los límites superiores. En las celdas G5 a G8 los intervalos y en las celdas H5 a H8 las
frecuencias absolutas de dichos intervalos. En H9 calculamos N, sumando las celdas H5 a H8.
Calculamos a continuación las frecuencias relativas (columna I), absolutas acumuladas (columna J)
Facultat d’Economia i Empresa
Estadística I
Guió Pràctica3 d’Excel
y relativas acumuladas (K). Calculamos la marca de clase de cada intervalo (límite superior más
inferior dividido entre dos). Para ello, en la celda L5 escribimos la fórmula =(E5+F5)/2. Copiamos
la fórmula en las celdas L6 a L8. En la columna M colocamos el producto de la frecuencia absoluta
por la marca de clase de cada intervalo. En la celda M5 escribimos la formula =H5*L5. Copiamos
la formula en las celdas M6 a M8. La media aproximada será la suma de dichas celdas dividido
entre el tamaño de la muestra. Escribimos en la celda M9 la formula =SUMA(M5:M8)/H9. También
podemos calcular la media aritmética aproximada como la suma de la frecuencia relativa por la
marca de clase de cada intervalo. En la celda N5 escribimos la formula =I5*L5, y la copiamos en
las celdas N6 a N8. En la celda N9 escribimos la formula =SUMA(N5:N8). Para calcular la media
aritmética exacta, en la celda M10 escribimos la formula =PROMEDIO('Octubre 2016'!J2:J2365).
Para obtener la mediana aproximada, en la celda O9 escribimos la formula =E7+((H9/2-
H6)/H7)*(F7-E7). La mediana exacta la calculamos en la celda O10, escribiendo la formula
=MEDIANA('Octubre 2016'!J2:J2365):
8. ¿Cuál es el gasto mínimo entre el 25% de los que más gastan en los viajes de Octubre? ¿Y el gasto
máximo entre el 10% que menos gasta en los viajes de Octubre?
El gasto mínimo del 25% que más gasta sería el tercer cuartil. La respuesta a esta pregunta se
puede obtener de varias formas. Respecto al gasto mínimo del 25% que más gasta, la primera
posibilidad sería ordenar los ingresos de menor a mayor, y seleccionar el ingreso que ocupa la
posición 1774 (que es el 75% de la muestra, 2364, más 1). La otra es usar la función
[Link](;)/[Link](;),[Link](;)/[Link](;) o CUARTIL(;)/QUARTI(;),
donde el primer argumento son las celdas donde están los valores de la variable en la muestra y el
segundo el cuartil (1, primero, 2 segundo, 3 tercero). Finalmente podríamos usar también la función
[Link](;),[Link](;) O PERCENTIL(;), donde el primer argumento son las
celdas donde están los valores de la variable en la muestra y el porcentaje de datos que deja por
debajo el percentil. En este caso deberíamos poner 0.75.
El gasto máximo para el 10% que menos gasta sería el percentil 10. Podríamos buscar el gasto
que ocupa la posición 237 (entre los gastos ordenados) o bien podríamos usar también la función
[Link](;),[Link](;) O PERCENTIL(;), usando como segundo argumento (el
percentil) 0.10.
9. Si quisiéramos dar una subvención al 15% de los individuos más jóvenes que viajan en Octubre
¿Qué edad tendrías que tener como mucho para poder recibir dicha subvención?
Como en el caso de la pregunta anterior hay dos posibles formas de hacerlo. Calculamos primero el
15% de 2364 (los viajes realizados en Octubre del 2016), que son 354,6. Ordenamos las edades de
menor a mayor, y nos fijamos en la que ocupa la posición 354, que es 36. Para saber cuál es la edad
del 15% más joven también podemos usar la función [Link](;),[Link](;) O
PERCENTIL(;), con el segundo argumento igual a 0,15 (=[Link](J2:J2365;0,15) ó
=[Link](J2:J2365;0,15)). El resultado de nuevo es en ambos casos 36.
