Uso de Excel en Estadística
USO DE EXCEL
GRÁFICOS
Dada la siguiente tabla realizar un gráfico
Relación entre empleo y población, mayores de 15 años,
total. Argentina y países limítrofes. Años 2006 – 2009
En %
País 2006 2007 2008 2009
Argentina 58 59 59 59
Bolivia 68 68 68 68
Brasil 65 65 66 65
Chile 52 53 55 52
Paraguay 66 67 68 67
Uruguay 57 59 59 60
[Link]
En la Solapa "INSERTAR" → Gráficos → Columnas 2-D
Vamos a "Seleccionar Datos" y aparece un cuadro de diálogo Click en "Agregar"
Material preparado por Lic. Adriana Ibero 1
Uso de Excel en Estadística
En Nombre de la serie indicamos la celda del año 2006 con un Click
En valores de la serie indicamos los valores de ese año en los distintos países, como se ve en la
imagen siguiente
Se da Aceptar .
Se hace lo mismo para cada año "Agregar" → "Nombre de la serie" y Valores de la serie" para
los años 2007, 2008 y 2009.
Material preparado por Lic. Adriana Ibero 2
Uso de Excel en Estadística
En este cuadro de diálogo vemos en la izquierda las series, a la derecha "Etiquetas del eje
horizontal" y entramos en "Editar"
En "Rango de rótulos del eje" Marcamos las celdas desde Argentina a Uruguay Aceptar .
Volvemos al cuadro de Dialogo y damos Aceptar
Material preparado por Lic. Adriana Ibero 3
Uso de Excel en Estadística
80
70
60
50 2006
40 2007
2008
30
2009
20
10
0
Argentina Bolivia Brasil Chile Paraguay Uruguay
Al gráfico le faltan elementos. Utilizando el Excel, complétenlo . pueden cambiar colores ,
formato de letras y números, etc.
ESTADÍSTICA DESCRIPTIVA
Ejemplo En una empresa importadora se considera la posibilidad de abrir una sucursal en una ciudad
capital del interior, con este objetivo se recolectan los datos de las importaciones realizadas con
países limítrofes en los últimos diez años y una de las variables de interés es la duración en horas
del trámite aduanero. Se recolectan los siguientes datos
19 25 26 58 25 24 45 51 56 34 24 37
20 28 40 58 26 38 46 52 56 35 24 37
25 28 41 25 28 39 47 53 58 36 55 24
20 29 41 18 29 39 47 53 21 37 56 24
21 30 45 34 31 39 49 54 29 38 35 42
21 33 48 32 31 40 50 55 30 39 37 44
21 33 52 21 32 40 37 23 33 41 24 30
21 35 53 27 33 42 25 23 33 41 22 32
26 35 53 23 34 42 26 24 34 41 34 43
23 35 55 23 35 42 21 29 34 41 34 43
Se tienen los 120 datos de la variable Horas de duración del trámite aduanero en una sola columna
de Excel, ordenamos los datos:
Realizar una tabla de frecuencias y un gráfico
En una hoja de Excel tenemos los datos en una columna.
Vamos a la solapa Insertar → Tabla dinámica → Crear Tabla dinámica
En Tabla o rango: marcamos los datos → Aceptar
Material preparado por Lic. Adriana Ibero 4
Uso de Excel en Estadística
En el cuadro de la derecha arrastramos "Horas" a Rótulos de fila y a de valores donde con
botón derecho podemos elegir elegimos Cuenta.
Vemos que el Excel crea la tabla de valores y que muchos tienen frecuencias chicas.
Podemos agrupar los datos en intervalos, para ello debemos determinar lo siguiente:
Rango = Máx. - mín. = 58 - 18 = 30
Cantidad de intervalos = 7
Ancho intervalo = 6
Con estos datos podemos utilizar la tabla dinámica para realizar clases o intervalos. Marcamos los
valores que entran en cada intervalo, con botón derecho se abren opciones y elegimos Agrupar
De esta forma tenemos las frecuencias de cada intervalo y completamos la tabla que armamos
Material preparado por Lic. Adriana Ibero 5
Uso de Excel en Estadística
X: Horas fa: Trámites
18 - 24 17
24 - 30 24
30 - 36 26
36 - 42 21
42 - 48 12
48 - 54 10
54 - 60 10
Excel tiene un complemento estadístico para instalarlo vamos a la cinta
Click en Más comandos → Complementos → Ir .
Material preparado por Lic. Adriana Ibero 6
Uso de Excel en Estadística
Se abre un cuadro de diálogo y marcamos las opciones → Aceptar
Otra opción que brinda el Excel mucho más rápida es: Datos → Análisis de Datos → Histograma
Pero antes tenemos que indicarle el límite superior de cada clase, de acuerdo a las fórmulas que
trabaja el Excel el límite superior tenemos que indicarlo abierto , para ello restamos 0,1, ejemplo
el primer límite superior es 24, pero escribimos 23,9
Material preparado por Lic. Adriana Ibero 7
Uso de Excel en Estadística
Nos da las frecuencias de los intervalos ( fa) y el porcentaje acumulado (Fr%)
Vamos a realizar un gráfico Primero marcamos los valores de la tabla y luego vamos a la solapa
Insentar → Columna
Material preparado por Lic. Adriana Ibero 8
Uso de Excel en Estadística
Al tener marcada la tabla el Excel da el gráfico automáticamente, lo que vamos a hacer es
mejorar el gráfico
fa: Trámites
30
25
20
15
fa: Trámites
10
5
0
18 - 24 24 - 30 30 - 36 36 - 42 42 - 48 48 - 54 54 - 60
Nos paramos en el título y lo cambiamos. Suprimimos la leyenda ya que trabajamos con una sola
varible
Duración del trámite aduanero. Bs. As.
2007-2017
30
25
20
15
10
5
0
18 - 24 24 - 30 30 - 36 36 - 42 42 - 48 48 - 54 54 - 60
Si nos paramos en los ejes con botón derechose abre un cuadro de diálogo que permite poner
nombre del eje, y realizar otros cambios
Por último debemos agrandar las barras, nos paramos en cualquier barra, clic derecho, Dar
formato a serie de datos
Material preparado por Lic. Adriana Ibero 9
Uso de Excel en Estadística
Opciones de serie → Ancho del intervalo: Sin intervalo . Relleno → Relleno degradado
Color de borde → Negro. Estilo de borde→ Ancho: 1,75
El gráfico queda:
Duración del trámite aduanero. Bs. As. 2007-2017
30
25
20
Trámites
15
10
0
18 - 24 24 - 30 30 - 36 36 - 42 42 - 48 48 - 54 54 - 60
Horas
Si queremos realizar el polígono de frecuencia en Excel debemos agregar dos filas a la tabla. La
primera que contenga al intervalo anterior y la última que contenga al intervalo siguiente,
ambas con frecuencia simple 0, ya que no hay datos
Material preparado por Lic. Adriana Ibero 10
Uso de Excel en Estadística
X: Horas fa: Trámites
12 - 18 0
18 - 24 17
24 - 30 24
30 - 36 26
36 - 42 21
42 - 48 12
48 - 54 10
54 - 60 10
60 - 66 0
Nos paramos en el gráfico y vamos a la solapa Herramientas para gráfico → Diseño →
Seleccionar datos
En Seleccionar datos , vamos a la izquierda y le pedimos agregar otra serie. Ingresamos los datos
con las filas agregadas . También modificamos el gráfico ya realizado en "Editar". Luego en la
derecha del cuadro de diálogo modificamos el eje x con las filas agregadas
Damos aceptar y nos aparece en el gráfico otra serie . Nos paramos en cualquier barra de la nueva
serie y haciendo click con botón derecho, vamos a la opción Cambiar tipo de gráfico. Pedimos el
primer gráfico de Línea
Material preparado por Lic. Adriana Ibero 11
Uso de Excel en Estadística
Duración del trámite aduanero. Bs. As. 2007-2017
30
25
20
Trámites
15
10
0
12 - 18 18 - 24 24 - 30 30 - 36 36 - 42 42 - 48 48 - 54 54 - 60 60 - 66
Horas
Para obtener medidas de posición y dispersión vamos a la solapa Datos → Análisis de datos →
Estadística Descriptiva
Material preparado por Lic. Adriana Ibero 12
Uso de Excel en Estadística
Horas
Media 35,567
Error típico 0,990 Es el desvío estándar de la media
Mediana 34
Moda 21
Desviación estándar 10,848
Varianza de la muestra 117,676
Curtosis -0,783
Coeficiente de asimetría 0,420
Rango 40
Mínimo 18
Máximo 58
Suma 4268
Cuenta 120
Mayor (24) 45 Indica el percentil 80
Menor(30) 26 Indica el percentil 25 o cuartil 1
Nivel de confianza(95,0%) 1,961 Es el error del Intervalo de confianza
TABLAS DE DOBLE ENTRADA
Vamos a la solapa Insertar → Tablas dinámicas
Material preparado por Lic. Adriana Ibero 13
Uso de Excel en Estadística
En el cuadro de diálogo que se abre, marcamos Sexo y lo arrastramos a Filas, marcamos N. Edu y
lo arrastramos a Columnas, luego marcamos Sexo y lo arrastramos a Valores
Vemos la tabla , la copiamos y pegamos como valores
Material preparado por Lic. Adriana Ibero 14
Uso de Excel en Estadística
Modificamos el nombre de las variables y las categorías , con esta tabla podemos trabajar para
obtener porcentajes. Al cuadro le faltan los elementos que los podemos agregar
DISTRIBUCIONES
Se tiene dos distribuciones X BI (n = 4; p = 0,25) e Y BI (n = 4; p = 0,75)
En Excel se busca en función fx
Material preparado por Lic. Adriana Ibero 15
Uso de Excel en Estadística
Se obtienen las dos distribuciones de probabilidad y vamos a realizar un gráfico para cada una
X BI (n = 4; p = 0,25) Y BI (n = 4; p = 0,75)
X P(X) Y P(Y)
0 0,3164 0 0,0039
1 0,4219 1 0,0469
2 0,2109 2 0,2109
3 0,0469 3 0,4219
4 0,0039 4 0,3164
X~ BI (n = 4; p = 0,25) Y~ BI (n = 4; p = 0,75)
0,45 0,45
0,40 0,40
0,35 0,35
0,30
Probabilidad
0,30
Probabilidad
0,25 0,25
0,20 0,20
0,15 0,15
0,10 0,10
0,05 0,05
0,00 0,00
0 1 2 3 4 0 1 2 3 4 X
X
Utilizando el Excel se pueden obtener otras distribuciones, por ejemplo la distribución normal.
Sea X N (18; 3), hallar :
X P(X<Xo)
15
18
22
Material preparado por Lic. Adriana Ibero 16
Uso de Excel en Estadística
En Insertar función buscamos la distribución normal
Se obtiene la probabilidad acumulada para os distintos valores
X P(X<Xo)
15 0,15866
18 0,5
22 0,90879
De la misma forma podemos buscar el valor de la variable X si tenemos la probabilidad
Material preparado por Lic. Adriana Ibero 17
Uso de Excel en Estadística
P(X<Xo) X
0,103 14,206
0,459 17,691
0,789 20,409
MUESTRAS
Elegir aleatoriamente una muestra
En Excel en la celda I2, se pide la función → Todas → Aleatorio
Material preparado por Lic. Adriana Ibero 18
Uso de Excel en Estadística
Se copia en todas las celdas hasta la I201. El resultado es un número aleatorio entre 0 y 1, pero
volátil, o sea cambia con cada enter. Por eso se lo copia y se lo pega con Pegado especial: Pegar
valores en la columna H2
Material preparado por Lic. Adriana Ibero 19
Uso de Excel en Estadística
Se ordena la base por la columna Aleatorio y se eligen los primeros 64 valores
INTERVALOS DE CONFIANZA PARA LE MEDIA CON VARIANZA CONOCIDA
Para obtener medidas de posición y dispersión vamos a la solapa Datos → Análisis de datos →
Estadística Descriptiva
Se marca Nivel de confianza para la media indicando el nivel de confianza
ANÁLISIS DE REGRESIÓN
Se ingresan los datos en una hoja de Excel
Para obtener los resultados se utiliza el comando
Solapa Datos ➔ Análisis de datos ➔ se abre un cuadro de diálogo ➔ Regresión
Material preparado por Lic. Adriana Ibero 20
Uso de Excel en Estadística
Se abre un cuadro de diálogo:
Rango Y de entrada: se ingresan los valores de Y incluido el nombre de la columna
Rango X de entrada: se ingresan los valores de X incluido el nombre de la columna
Rótulos: Se cliquea ya que se ingresaron los nombres de las variables
Nivel de confianza: por default el Excel indica un 95%, hacemos clic y cambiamos por 98%
Rango de salida: se ingresa la celda donde estará la salida
Residuos: se cliquea para obtener los valores estimados y los errores
Material preparado por Lic. Adriana Ibero 21
Uso de Excel en Estadística
Excel brinda una salida de datos
En esta tabla vemos los coeficientes obtenidos, el software brinda más datos de los que se estudian
en este curso.
Material preparado por Lic. Adriana Ibero 22
[Link]
