ce104_201901_Manual Excel

Vista previa del material en texto

Universidad Peruana de 
Ciencias Aplicadas 
 
Estudios Profesionales 
para Ejecutivos 
 
 
 
ESTADÍSTICA 
CE104 
 
Los profesores del curso 
Manual de Excel 
2019 01 
 
2 CE104 Estadística 
Notas 
 
 
 
EPE UPC 3 
 
Notas 
 
 
 
 
Contenido 
Estadística descriptiva en Excel 4 
Tabla de distribución de frecuencias para variables cualitativas ..............................................4 
Gráfico de barras ......................................................................................................................7 
Gráfico circular en Excel .........................................................................................................12 
Distribución de frecuencias de variables discretas .................................................................18 
Gráfico de bastones en Excel ..................................................................................................22 
Distribución de frecuencias para variables por intervalos en Excel ........................................28 
Histograma de frecuencias .....................................................................................................33 
Tabulación cruzada .................................................................................................................37 
Gráfico de barras comparativas ..............................................................................................39 
Gráfico de barras apiladas al 100% .........................................................................................40 
Medidas de resumen ..............................................................................................................41 
Distribución normal ................................................................................................................42 
Prueba de hipótesis en Excel 42 
Regresión lineal simple 47 
Reporte de regresión en Excel ................................................................................................47 
 
 
4 CE104 Estadística 
Notas 
 
 
Estadística descriptiva en Excel 
 
Tabla de distribución de frecuencias para variables cualitativas 
Hay varias formas en Excel de hacer una tabla de distribución de frecuencias para variables cualitati-
vas, dos de ellas son: 
 
 
 
Distribución de frecuencias de variables cualitativas usando la función =CONTAR.SI(rango;criterios) 
 
1. Para hacer una tabla de distribución de frecuencias para variables cualitativas, copie los nombres 
de las categorías que desee contar. Tenga mucho cuidado al escribir los nombres de las catego-
rías; si no están bien escritos, la función no contará adecuadamente y aparecerá un cero. 
2. Escriba en la celda al costado de la primera categoría la función =CONTAR.SI(rango;criterios). 
o En Rango indique las celdas donde están los datos. 
o En Criterio indique la celda que contiene el nombre de la categoría que desee contar. 
 
 
 
 
 
3. Luego, copie la celda con la función =CONTAR.SI() a las demás celdas correspondientes. 
4. Calcule las frecuencias relativas (hi) o porcentuales (pi). 
 
 
 
 
Usar la función =CONTAR.SI() Usar tablas dinámicas
El rango debe ponerlo 
como una celda absolu-
ta. Oprima F4 
Divida cada frecuencia 
absoluta entre el total. 
Para el total apriete F4. 
EPE UPC 5 
 
Notas 
 
 
Distribución de frecuencias de variables cualitativas usando tablas dinámicas 
 
1. Cada variable debe estar en una columna. Los nombres de las variables estarán en la primera fila. 
2. Ubique el cursor en cualquier celda de los datos. 
3. Haga clic en la opción Insertar de la barra de menú. 
 
En Excel 2010 aparecerá este menú 
 
 
 
 
En Excel 2013 aparecerá este menú 
 
 
 
 
En Excel 2016 aparecerá este menú 
 
 
 
 
4. Seleccione Tabla dinámica. 
5. Seleccione el rango de datos en la opción Tabla o rango. 
6. Elija la celda donde desea colocar la tabla dinámica. 
Tiene dos opciones: 
o Nueva hoja de cálculo. Excel creará una nueva hoja de datos para presentar la tabla dinámi-
ca. 
o Hoja de cálculo existente. Excel pondrá la tabla dinámica en la celda que usted le indique. 
 
 
 
6 CE104 Estadística 
Notas 
 
 
7. Arrastre la variable de la cual desea el cuadro al área Etiquetas de fila (Filas) y también al área ∑ 
Valores. 
 
En Excel 2010 En Excel 2013 y 2016 
 
 
Etiquetas de fila Cuenta de Preferencia profesional 
Administración 5 
Chef 6 
Derecho 4 
Futbolista 3 
Ingeniería 8 
Periodismo 4 
Total general 30 
 
8. Copie la tabla, como valores, en otras celdas. 
 
 
9. Calcule las frecuencias relativas (hi) o porcentuales (pi). 
 
 
Divida cada frecuencia 
absoluta entre el total. 
Para el total apriete F4. 
EPE UPC 7 
 
Notas 
 
 
Gráfico de barras 
 
En el eje horizontal se representa las categorías de la variable y en el eje de ordenadas las frecuen-
cias absolutas, relativas o porcentuales. 
 
Para hacer gráficos de barras o columnas en Excel, usted tiene dos posibilidades: 
 
 
 
 
Gráfico de barras a partir de la distribución de frecuencias 
 
1. Lo primero es hacer la tabla de distribución de frecuencias. 
 
 
 
2. Seleccione las celdas de la variable y las celdas de las frecuencias absoluta o relativa. Seleccione 
también el título. Use la tecla Ctrl si necesita seleccionar celdas no contiguas. 
3. Haga clic en la opción Insertar de la barra de menú y active el icono Columna. Seleccione la op-
ción Columna 2-D y haga clic sobre Columna agrupada. 
 
En Excel 2010 aparecerá este menú. 
 
 
 
En Excel 2013 aparecerá este menú. 
 
 
 
En Excel 2016 aparecerá este menú. 
 
 
 
Hacer el gráfico a partir de la distribución de 
frecuencias
Usar gráficos dinámicos
8 CE104 Estadística 
Notas 
 
 
4. Elimine la leyenda que aparece en el lado derecho del gráfico. 
 
 
 
5. Haga doble clic en el área del gráfico, aparecerá una pestaña de Herramientas de gráficos con las 
opciones: 
En Excel 2010: Diseño, Presentación y Formato. 
En Excel 2013 y 2016: Diseño y Formato. 
 
En Excel 2010, seleccione Presentación, luego escoja los botones Título de gráfico, Rótulo del eje 
o Etiquetas de datos para darle el formato deseado a su gráfico. Además, registre la fuente me-
diante un cuadro de texto. 
 
 
En Excel 2013 y 2016, seleccione Diseño, luego escoja la opción Agregar elementos de gráfico o 
la opción + para darle el formato deseado a su gráfico. 
 
 
 
EPE UPC 9 
 
Notas 
 
 
Luego, debería quedarle un gráfico parecido a este. 
 
 
 
6. Para mostrar las frecuencias porcentuales, use las frecuencias relativas para hacer el gráfico y 
haga doble clic en el eje vertical. Luego, seleccione la opción Número y haga clic en Porcentaje. 
 
En Excel 2010 En Excel 2013 y 2016 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Luego, debería quedarle un gráfico parecido a este. 
 
10 CE104 Estadística 
Notas 
 
 
Gráfico de barras usando gráficos dinámicos 
1. Cada variable debe estar en una columna. Los nombres de las variables estarán en la primera fila. 
2. Ubique el cursor en cualquier celda de los datos. 
3. Haga clic en la opción Insertar de la barra de menú. 
 
En Excel 2010 aparecerá este menú 
 
 
 
4. Seleccione Gráfico dinámico. 
5. Seleccione el rango de datos en la opción Tabla o rango. 
6. Elija la celda donde desea colocar la tabla dinámica. 
Tiene dos opciones: 
o Nueva hoja de cálculo. Excel creará una nueva hoja de datos para presentar la tabla dinámi-
ca. 
o Hoja de cálculo existente. Excel pondrá la tabla dinámica en la celda que usted le indique. 
 
 
 
7. Arrastre la variable de la cual desea el cuadro al área Campos de eje y también al área ∑ Valores. 
 
 
8. Para mostrar las frecuencias porcentuales, haga clic derecho en Cuenta. Luego elija Mostrarvalo-
res como del menú desplegable. A continuación, elija % del total general. 
 
 
 
EPE UPC 11 
 
Notas 
 
 
En Excel 2013 y 2016 aparecerá este menú 
 
 
 
4. Ubique el cursor en cualquier celda de los datos. 
5. Seleccione Gráficos recomendados. Si la base de datos tiene varias variables, elija el gráfico de la 
variable elegida. 
 
 
6. Para mostrar las frecuencias porcentuales, haga clic derecho en Cuenta. Luego elija Mostrar va-
lores como del menú desplegable. A continuación, elija % del total general. 
 
 
7. En todas las versiones de Excel, usted puede poner título, rótulos de ejes, etiquetas de datos, 
fuente de la misma manera. Debe quedar un gráfico parecido a este o de frecuencias porcentua-
les. 
 
 
 
12 CE104 Estadística 
Notas 
 
 
Gráfico circular en Excel 
En este caso las categorías de la variable cualitativa ocupan un espacio en el círculo que es propor-
cional a la frecuencia que representan. También se denomina diagrama de sector circular, gráfico 
tipo torta o pastel. 
Si la variable cualitativa es de escala ordinal se recomienda usar el gráfico de barras 
 
Para hacer gráficos circulares en Excel, usted tiene dos posibilidades: 
 
 
 
 
Gráfico circular a partir de la distribución de frecuencias 
 
1. Lo primero es hacer la tabla de distribución de frecuencias. 
 
 
 
2. Seleccione las celdas de la variable y las celdas de la de frecuencia absoluta o relativa. Seleccione 
también el título. Use la tecla Ctrl si necesita seleccionar celdas no contiguas. 
3. Haga clic en la opción Insertar de la barra de menú y active el icono Columna. Seleccione la op-
ción Columna 2-D y haga clic sobre Columna agrupada. 
 
En Excel 2010 aparecerá este menú. 
 
 
En Excel 2013 aparecerá este menú. 
 
 
En Excel 2016 aparecerá este menú. 
 
 
 
 
Hacer el gráfico a partir de la distribución de 
frecuencias
Usar gráficos dinámicos
EPE UPC 13 
 
Notas 
 
 
4. Elimine la leyenda que aparece en el lado derecho del gráfico. 
 
 
 
5. Haga doble clic en el área del gráfico, aparecerá una pestaña de Herramientas de gráficos con las 
opciones: 
En Excel 2010: Diseño, Presentación y Formato. 
En Excel 2013 y 2016: Diseño y Formato. 
 
En Excel 2010 
Seleccione Presentación, luego escoja los botones Título de gráfico, Etiquetas de datos para dar-
le el formato deseado a su gráfico. Además, registre la fuente mediante un cuadro de texto. 
 
Seleccione Etiquetas de datos y la opción Más opciones de la etiqueta de datos... 
 
 
 
En Formato de etiquetas de datos, elija las opciones Nombre de categoría, Porcentaje y Extre-
mo externo. 
 
 
 
 
14 CE104 Estadística 
Notas 
 
 
En Excel 2013 y 2016 
Seleccione Diseño, luego escoja la opción Agregar elementos de gráfico o la opción + para darle 
el formato deseado a su gráfico. 
 
En Formato de etiquetas de datos, elija las opciones Nombre de categoría, Porcentaje y Extre-
mo externo. 
 
 
 
6. Luego, debería quedarle un gráfico parecido a este. 
 
 
 
EPE UPC 15 
 
Notas 
 
 
Gráfico circular usando gráficos dinámicos 
 
1. Cada variable debe estar en una columna. Los nombres de las variables estarán en la primera fila. 
2. Ubique el cursor en cualquier celda de los datos. 
3. Haga clic en la opción Insertar de la barra de menú. 
 
En Excel 2010 aparecerá este menú 
 
 
 
4. Seleccione Gráfico dinámico. 
5. Seleccione el rango de datos en la opción Tabla o rango. 
6. Elija la celda donde desea colocar la tabla dinámica. 
Tiene dos opciones: 
o Nueva hoja de cálculo. Excel creará una nueva hoja de datos para presentar la tabla dinámi-
ca. 
o Hoja de cálculo existente. Excel pondrá la tabla dinámica en la celda que usted le indique. 
 
 
 
7. Arrastre la variable de la cual desea el cuadro al área Campos de eje y también al área ∑ Valores. 
 
 
 
 
16 CE104 Estadística 
Notas 
 
 
8. Para mostrar el gráfico circular seleccione la opción Cambiar tipo de gráfico. 
 
 
 
 
 
 
 
En Excel 2013 y 2016 aparecerá este menú 
 
1. Ubique el cursor en cualquier celda de los datos. 
2. Seleccione Gráficos recomendados. Si la base de datos tiene varias variables, elija el gráfico de la 
variable elegida. 
 
 
 
 
 
 
EPE UPC 17 
 
Notas 
 
 
3. Para mostrar el gráfico circular seleccione en el menú Diseño y luego la opción Cambiar tipo de 
gráfico. 
 
 
 
4. Luego, debería quedarle un gráfico parecido a este. 
 
 
 
18 CE104 Estadística 
Notas 
 
 
Distribución de frecuencias de variables discretas 
 
Hay varias formas en Excel de hacer una tabla de frecuencias para variables discretas, dos de ellas 
son: 
 
 
 
Distribución de frecuencias de variables discretas usando la función =CONTAR.SI(rango;criterios) 
 
1. Para hacer una tabla de distribución de frecuencias para variables cualitativas, copie los valores 
de la variable que desee contar. Tenga mucho cuidado al escribir los valores; si no están bien es-
critos, la función no contará adecuadamente y aparecerá un cero. 
2. Escriba en la celda al costado de la primera categoría la función =CONTAR.SI(rango;criterios). 
o En Rango indique las celdas donde están los datos. 
o En Criterio indique la celda que contiene el valor de la variable que desee contar. 
 
 
 
 
 
3. Luego, copie la celda con la función =CONTAR.SI() a las demás celdas correspondientes. 
4. Calcule las frecuencias relativas (hi) o porcentuales (pi). 
 
 
 
 Un inconveniente de este método es que se debe saber previamente todos los valores del rango 
de la variable. 
 
Usar la función =CONTAR.SI() Usar tablas dinámicas
Divida cada frecuencia 
absoluta entre el total. 
Para el total apriete F4. 
El rango debe ponerlo 
como una celda absolu-
ta. Oprima F4 
EPE UPC 19 
 
Notas 
 
 
Distribución de frecuencias de variables discretas usando tablas dinámicas 
 
1. Cada variable debe estar en una columna. Los nombres de las variables estarán en la primera fila. 
2. Ubique el cursor en cualquier celda de los datos. 
3. Haga clic en la opción Insertar de la barra de menú. 
 
En Excel 2010 aparecerá este menú 
 
 
 
 
En Excel 2013 aparecerá este menú 
 
 
 
 
En Excel 2016 aparecerá este menú 
 
 
 
 
4. Seleccione Tabla dinámica. 
5. Seleccione el rango de datos en la opción Tabla o rango. 
6. Elija la celda donde desea colocar la tabla dinámica. 
Tiene dos opciones: 
o Nueva hoja de cálculo. Excel creará una nueva hoja de datos para presentar la tabla dinámi-
ca. 
o Hoja de cálculo existente. Excel pondrá la tabla dinámica en la celda que usted le indique. 
 
 
 
20 CE104 Estadística 
Notas 
 
 
7. Arrastre la variable de la cual desea hacer el cuadro al área Etiquetas de fila (Filas) y también al 
área ∑ Valores. 
 
En Excel 2010 En Excel 2013 y 2016 
 
 
Etiquetas de fila Suma de Número de capacitaciones 
0 0 
1 14 
2 20 
3 6 
5 5 
Total general 45 
 
8. Coloque el cursor en la opción Suma de la variable y haga clic derecho, haga clic en Resumir va-
lor por cambie Recuento en vez de Suma. 
 
 
 
9. Copie la tabla, como valores, en otras celdas. 
 
Etiquetas de fila Cuenta de Número de capacitaciones 
0 3 
1 14 
2 10 
3 2 
5 1 
Total general 30 
 
 
EPE UPC 21 
 
Notas 
 
 
10. Calcule las frecuencias relativas (hi) o porcentuales (pi). 
 
 
 
 
Divida cada frecuencia 
absoluta entre el total. 
Para el total apriete F4. 
22 CE104 Estadística 
Notas 
 
 
Gráfico de bastones en Excel 
 
Hay dos formas en Excel de hacer un gráfico de bastones. 
 
 
 
Gráfico de bastones en Excel usando el gráfico de dispersión 
 
1. Lo primero es hacer la tabla de distribución de frecuencias. 
 
 
2. Seleccione las columnas Número de capacitaciones y frecuenciaabsoluta (fi) o relativa (hi). 
3. En la barra de menú, elija la opción Insertar, seleccione el tipo Dispersión. 
 
En Excel 2010 aparecerá este menú. 
 
 
En Excel 2013 aparecerá este menú. 
 
 
En Excel 2016 aparecerá este menú. 
 
 
Si la hubiera, elimine la leyenda. 
 
Usar el gráfico de dispersión Usar el gráfico de líneas
EPE UPC 23 
 
Notas 
 
 
4. Haga doble clic en el área del gráfico, aparecerá una pestaña de Herramientas de gráficos con las 
opciones: 
En Excel 2010: Diseño, Presentación y Formato. 
En Excel 2013 y 2016: Diseño y Formato. 
 
En Excel 2010 
 
5. Seleccione el gráfico, elija la opción Presentación y elija la opción Barras de error. En esa opción 
seleccione Más opciones de las barras de error… 
 
 
 
6. En Barras de error verticales seleccione la opción Menos. Luego en Cuantía de error, elija Por-
centaje y coloque el valor 100%. Borre las barras de error horizontales que aparecen automáti-
camente. 
 
 
 
4. Si lo desea puede cambiar algunas características al gráfico. De ser necesario, coloque los valores 
del eje Y en formato Porcentaje. 
 
 
 
24 CE104 Estadística 
Notas 
 
 
En Excel 2013 y 2016 
5. Seleccione el gráfico, elija la opción Diseño y elija la opción Agregar elementos de gráfico o la 
opción +. Haga clic en Barras de error. En esa opción seleccione Más opciones… 
 
 
 
 
6. En Formato de barras de error, seleccione en Dirección, la opción Menos. Luego en Cuantía de 
error, elija Porcentaje y coloque el valor 100%. Borre las barras de error horizontales que apare-
cen automáticamente. 
 
 
7. Si lo desea puede cambiar algunas características al gráfico. De ser necesario, coloque los valores 
del eje Y en formato Porcentaje. 
 
 
 
EPE UPC 25 
 
Notas 
 
 
Gráfico de bastones en Excel usando el gráfico de líneas 
 
1. Lo primero es hacer la tabla de distribución de frecuencias. 
 
 
 
2. Seleccione la columna frecuencia absoluta (fi) o relativa (hi). 
3. En la barra de menú, elija la opción Insertar, seleccione el tipo Línea con marcadores. 
 
En Excel 2010 aparecerá este menú. 
 
 
En Excel 2013 aparecerá este menú. 
 
 
En Excel 2016 aparecerá este menú. 
 
 
Si la hubiera, elimine la leyenda. 
 
4. Haga doble clic en el área del gráfico, aparecerá una pestaña de Herramientas de gráficos con las 
opciones: 
En Excel 2010: Diseño, Presentación y Formato. 
En Excel 2013 y 2016: Diseño y Formato. 
 
26 CE104 Estadística 
Notas 
 
 
5. Seleccione el gráfico, elija la opción Diseño y elija la opción Diseño rápido. Haga clic en Diseño 7. 
En Excel 2010 aparecerá el siguiente menú, haciendo clic en Diseños de gráficos. 
 
 
En Excel 2013 y 2016 aparecerá el siguiente menú, haciendo clic en Diseño rápido. 
 
 
Obtendrá el siguiente gráfico. 
 
 
 
8. Para quitar la línea, haga clic sobre ella, seleccione Formato de punto de datos... Luego, presione 
el botón derecho del mouse. Elija Color de línea y marque Sin línea. 
 
En Excel 2010 En Excel 2013 y 2016 
 
 
Obtendrá el siguiente gráfico. 
 
 
 
 
EPE UPC 27 
 
Notas 
 
 
9. Luego, haga clic derecho en cualquier parte del gráfico y elija Seleccionar datos. 
 
 
 
10. Haga clic en Editar y escoja como Rango de rótulos del eje: a las celdas que tienen los valores de 
la variable discreta. 
 
 
 
 
 
Obtendrá el siguiente gráfico. Si es necesario debe poner frecuencia cero a los valores sin datos. 
 
 
 
11. Si lo desea puede cambiar algunas características al gráfico. De ser necesario, coloque los valores 
del eje Y en formato Porcentaje. 
 
 
 
28 CE104 Estadística 
Notas 
 
 
Distribución de frecuencias para variables por intervalos en Excel 
 
Hay varias formas en Excel de hacer una tabla de frecuencias para variables por intervalos, dos de 
ellas son: 
 
 
 
Distribución de frecuencias para variables por intervalos usando funciones de Excel 
 
1. Use las funciones del Excel para realizar los siguientes cálculos. 
 
A B 
Máximo 351.1 =MAX(datos) 
Mínimo 145.1 =MIN(datos) 
Rango 206 =B6-B7 
Cantidad de datos (n) 48 =CONTAR(datos) 
k (por regla de Sturges) 6.58508 =1+3.322*LOG(n) 
k (entero) 7 =REDONDEAR(k; 0) 
w (Amplitud) 29.4286 =B8/B11 
Número de decimales de los datos 1 
 w (redondeada) 29.5 =REDONDEAR.MAS(w; decimales) 
 
2. Ahora, calcule los límites de los intervalos. Comience con el mínimo y aumente una amplitud 
cada vez. Además, calcule las marcas de clase, como la semisuma de los límites. 
 
 
 
3. Para calcular las frecuencias absolutas, seleccione el rango en el cual aparecerán las frecuencias 
absolutas. Haga clic en el icono de Insertar función, seleccione Frecuencia y de Aceptar. 
 
 
 
Usar funciones de Excel Usar Anális de datos
EPE UPC 29 
 
Notas 
 
 
4. En la ventana de Frecuencia, ingrese en: 
o Datos, el rango de los datos que se desea contar. 
o Grupos, el rango de celdas de los límites superiores de los intervalos. 
Mantenga presionados Ctrl y Shift, luego, presione Enter, con lo cual aparecerán las frecuencias 
absolutas. 
 
 
 
5. Calcule las demás frecuencias para completar la tabla. 
 
 
 
 
 
 
 
 
Fuente: Oficina de Rentas de la Municipalidad 
 
Divida cada frecuencia 
absoluta entre el total. 
Para el total apriete F4. 
Sume cada frecuencia 
con la frecuencia acu-
mulada anterior. 
Divida cada frecuencia 
absoluta entre el total. 
Para el total apriete F4. 
30 CE104 Estadística 
Notas 
 
 
Distribución de frecuencias para variables por intervalos usando Análisis de datos 
 
 Copie los datos en la columna A del Excel y en la fila 1 agregue el nombre de la variable. 
 En el menú principal elija la opción Datos. Luego seleccione Análisis de datos. 
 
 
 
 En el cuadro de diálogo Análisis de datos seleccione Estadística descriptiva. 
 
 
 
 En Rango de entrada seleccione la variable a contar. 
En Rótulos en la primera fila haga clic, dado que los datos tienen el nombre de la variable en la fila 1 
En Opciones de salida elija la celda, a partir de la cual, desea que aparezca los cálculos. 
En Resumen de estadísticas haga clic y marque la casilla. Finalmente, seleccione Aceptar 
 
 
La salida obtenida será: 
Impuestos 
Media 257,25625 
Error típico 7,6183196 
Mediana 251 
Moda 208 
Desviación estándar 52,7812664 
Varianza de la muestra 2785,86209 
Curtosis -0,68012624 
Coeficiente de asimetría -0,07342341 
Rango 206 
Mínimo 145,1 
Máximo 351,1 
Suma 12348,3 
Cuenta 48 
 
EPE UPC 31 
 
Notas 
 
 
Completamos los siguientes cálculos: 
 
k (por regla de Sturges) 6,5851 
k (entero) 7 
w (Amplitud) 29,4286 
Número de decimales de los datos 1 
w (redondeada) 29,5 
 
6. Ahora, calcule los límites de los intervalos. Comience con el mínimo y aumente una amplitud 
cada vez. Además, calcule las marcas de clase, como la semisuma de los límites. 
 
 
 
7. Para calcular las frecuencias absolutas, seleccione en el menú principal la opción Datos. Luego, 
haga clic en Análisis de datos. Luego haga clic en Histograma. 
En Rango de entrada seleccione las celdas que contenga los datos a contar. 
En Rango de clases seleccione los límites superiores de los intervalos. 
Tener cuidado con la opción Rótulos. Si la elige debe elegir los encabezados tanto en Rango de 
entrada como en Rango de clases. 
 
 
 
Obtendrá las frecuencias correspondientes, con las cuales puede completar la tabla de distribución. 
 
Clase Frecuencia 
174.6 3 
204.1 3 
233.6 10 
263.1 12 
292.6 7 
322.1 7 
351.6 6 
y mayor... 0 
 
32 CE104 Estadística 
Notas 
 
 
8. Calcule las demás frecuencias para completar la tabla. 
 
 
 
 
 
 
 
 
 
 
Fuente: Oficina de Rentas de la Municipalidad 
 
 
Divida cada frecuencia 
absolutaentre el total. 
Para el total apriete F4. 
Sume cada frecuencia 
con la frecuencia acu-
mulada anterior. 
Divida cada frecuencia 
absoluta entre el total. 
Para el total apriete F4. 
EPE UPC 33 
 
Notas 
 
 
Histograma de frecuencias 
 
Hay varias formas en Excel de hacer una tabla de frecuencias para variables por intervalos, dos de 
ellas son: 
 
 
 
Histograma usando gráficos estadísticos de Excel 2016 
 
1. Lo primero es poner los datos en una columna. Es conveniente poner el nombre de la variable en 
la primera fila. 
2. Luego, en el menú principal haga clic en Insertar, luego haga clic en Histograma. 
 
 
 
Obtendrá el siguiente gráfico. Observe que Excel decide automáticamente la cantidad de interva-
los y no usa la regla de Sturges (k = 1 + 3,322 log(n)). Excel usa la regla de Scott, en la que el an-
cho del intervalo se calcula como 𝑤 = 
3,5×𝜎
√𝑛
3 , donde  es la desviación estándar de los datos y n 
es el tamaño de muestra. 
 
 
 
3. Si desea cambiar el ancho de los intervalos, haga clic derecho sobre el eje. Elija Dar formato al 
eje… Seleccione Ancho del rango y escriba el ancho del intervalo que desee. 
 
 
 
Usar gráficos estadísticos en Excel 2016 Usar Anális de datos
34 CE104 Estadística 
Notas 
 
 
Obtendrá el siguiente gráfico. 
 
 
 
4. Si lo desea puede cambiar algunas características al gráfico. 
 
 
 
Con este método solo se puede hacer histogramas de frecuencias absolutas. 
 
 
EPE UPC 35 
 
Notas 
 
 
Histograma usando gráficos de columna 
 
1. Lo primero es hacer la tabla de distribución de frecuencias. 
 
 
Fuente: Oficina de Rentas de la Municipalidad 
 
2. Seleccione la columna de frecuencia absoluta o relativa, luego seleccione Insertar en la barra de 
menú, elija Columna, y luego Columna agrupada. 
 
 
Obtendrá el siguiente gráfico. 
 
3. Si la hubiera, elimine la leyenda y el título. 
4. Para que se junten los rectángulos, haga clic en cualquiera de las barras y elija Opciones de serie, 
luego seleccione Ancho del intervalo igual a 0%. 
 
 
 
 
36 CE104 Estadística 
Notas 
 
 
5. Para cambiar los valores del eje X debe crear un grupo de celdas donde estén los intervalos. Puede usar la 
función =CONCATENAR() 
 
 
 
6. Luego, seleccione el gráfico, haga clic en Diseño y seleccione Seleccionar datos. Haga clic en Editar. 
 
 
 
 
 
7. Si lo desea puede cambiar algunas características al gráfico. Con este método solo se puede hacer histo-
gramas de frecuencias absolutas y porcentuales. 
 
 
 
EPE UPC 37 
 
Notas 
 
 
Tabulación cruzada 
 
1. Cada variable debe estar en una columna. Los nombres de las variables estarán en la primera fila. 
2. Ubique el cursor en cualquier celda de los datos. 
3. Haga clic en la opción Insertar de la barra de menú. 
 
En Excel 2010 aparecerá este menú 
 
 
 
En Excel 2013 aparecerá este menú 
 
 
 
En Excel 2016 aparecerá este menú 
 
 
 
4. Seleccione Tabla dinámica. 
5. Seleccione el rango de datos en la opción Tabla o rango. 
6. Elija la celda donde desea colocar la tabla dinámica. 
Tiene dos opciones: 
o Nueva hoja de cálculo. Excel creará una nueva hoja de da-
tos para presentar la tabla dinámica. 
o Hoja de cálculo existente. Excel pondrá la tabla dinámica 
en la celda que usted le indique. 
 
7. Arrastre una de las variables de la cual desea el cuadro al área 
Etiquetas de fila (Filas) y la otra variable a Etiquetas de co-
lumna (Columnas). También arrastre cualquiera de las dos va-
riables al área ∑ Valores. 
 
En Excel 2010 En Excel 2013 y 2016 
 
 
38 CE104 Estadística 
Notas 
 
 
Cuenta de Giro del negocio Etiquetas de columna 
 Etiquetas de fila Callao Lima Total general 
Bodega 14 18 32 
Boutique 22 35 57 
Ferretería 6 7 13 
Frutería 8 15 23 
Otros 15 13 28 
Total general 65 88 153 
 
8. Si desea calcular porcentajes del total general, por total de columna por total de filas, haga clic 
derecho en la celda Cuenta y seleccione Mostrar valores como… 
 
 
 
9. Copie la tabla, como valores, en otras celdas y dele el formato que desee. 
 
 
 
Distribución de clientes según lugar de residencia y giro del negocio 
 
Lugar de residencia 
 Giro del negocio Callao Lima Total 
Bodega 9.15% 11.76% 20.92% 
Boutique 14.38% 22.88% 37.25% 
Ferretería 3.92% 4.58% 8.50% 
Frutería 5.23% 9.80% 15.03% 
Otros 9.80% 8.50% 18.30% 
Total 42.48% 57.52% 100.00% 
 Fuente: Entidad bancaria Tubanco. Primer trimestre 
Distribución de clientes según giro del negocio por lugar de residencia 
 
Lugar de residencia 
 Giro del negocio Callao Lima Total 
Bodega 21.54% 20.45% 20.92% 
Boutique 33.85% 39.77% 37.25% 
Ferretería 9.23% 7.95% 8.50% 
Frutería 12.31% 17.05% 15.03% 
Otros 23.08% 14.77% 18.30% 
Total 100.00% 100.00% 100.00% 
 Fuente: Entidad bancaria Tubanco. Primer trimestre 
Distribución de clientes según lugar de residencia por giro del negocio 
 
Lugar de residencia 
 Giro del negocio Callao Lima Total 
Bodega 43.75% 56.25% 100.00% 
Boutique 38.60% 61.40% 100.00% 
Ferretería 46.15% 53.85% 100.00% 
Frutería 34.78% 65.22% 100.00% 
Otros 53.57% 46.43% 100.00% 
Total 42.48% 57.52% 100.00% 
 Fuente: Entidad bancaria Tubanco. Primer trimestre 
EPE UPC 39 
 
Notas 
 
 
Gráfico de barras comparativas 
 
1. Seleccione solo las celdas correspondientes a las categorías de ambas variables. No incluya los 
totales. 
2. Haga clic en la opción Insertar de la barra de menú y elija Columna/Columna agrupada. 
 
 
 
3. Haga clic a cualquier línea horizontal del gráfico y observará que todas las líneas horizontales se 
seleccionan. Presione la tecla Supr de su teclado para eliminarlas. 
4. Haga clic en el área del gráfico y aparecerá la pestaña Herramientas de gráficos con las opciones: 
Diseño, Presentación y Formato. 
5. Seleccione Diseño y elija el estilo que desee haciendo clic en el menú despegable. El estilo 26 
(segunda columna fila 4) es el que se presenta como modelo en este material. 
 
 
 
6. Seleccione ahora la pestaña Presentación, y de ahí escoja los botones Título de gráfico, Rótulo 
del eje y Etiqueta de datos para darle el formato deseado. 
 
 
 
7. Haga clic derecho sobre cualquier valor del eje Y y del menú elija Dar formato al eje… 
/Número/Porcentaje/Posiciones decimales: 0/Cerrar. 
 
 
 
8. Elimine los valores porcentuales iguales a cero para una mejor presentación. 
 
40 CE104 Estadística 
Notas 
 
 
Gráfico de barras apiladas al 100% 
 
1. Seleccione solo las celdas correspondientes a las categorías de ambas variables. No incluya los 
totales. 
2. Haga clic en la opción Insertar de la barra de menú y elija Columna/Columna 100% apilada. 
 
 
 
3. Haga clic en el área del gráfico y aparecerá la pestaña Herramientas de gráficos con las opciones: 
Diseño, Presentación y Formato. 
4. Seleccione Diseño y elija el estilo que desee haciendo clic en el menú despegable. El estilo 26 
(segunda columna fila 4) es el que se presenta como modelo en este material. 
 
 
 
5. Seleccione ahora la pestaña Presentación, y de ahí escoja los botones Título de gráfico, Rótulo 
del eje y Etiqueta de datos para darle el formato deseado. 
 
 
 
6. Haga clic derecho sobre cualquier valor del eje Y y del menú elija Dar formato al eje… 
/Número/Porcentaje/Posiciones decimales: 0/Cerrar. 
7. Elimine los valores porcentuales iguales a cero para una mejor presentación. 
 
 
EPE UPC 41 
 
Notas 
 
 
Medidas de resumen 
 
Hay dos formas para calcular medidas de resumen usando Excel. 
 
 
 
Cálculo de las medidas de resumen usando funciones de Excel 
 
Media aritmética =PROMEDIO(datos) 
Mediana =MEDIANA(datos) 
Moda Existen dos funciones para calcular la moda: 
=MODA.UNO(datos) solo si los datos tuvieran una moda. 
=MODA.VARIOS(datos)si los datos tuvieran más de una moda. 
 
Recomendamos usar =MODA.VARIOS(datos), pues de antemano no sa-
bemos si el conjunto de datos con los cuales estamos trabajando tienen 
más de una moda. 
Percentil En Excel, hay dos funciones para calcular un percentil: 
Para calcular el percentil k, use la función =PERCENTIL.EXC(datos,k/100) 
Para calcular el percentil k, use la función =PERCENTIL.INC(datos,k/100) 
 
Recomendamos usar = PERCENTIL.EXC(datos,k/100) 
Desviación estándar =DESVEST.M para calcular la desviación estándar muestral. 
=DESVEST.P para calcular la desviación estándar poblacional. 
Varianza =VAR.S para calcular la varianza muestral. 
=VAR.P para calcular la varianza poblacional. 
Coeficiente de varia-
ción 
Excel no calcula el coeficiente de variación, por lo que use las funciones: 
=PROMEDIO(datos) para calcular la media 
=DESVEST.M(datos) para calcular la desviación estándar 
Para luego, dividir la desviación estándar entre su respectiva media. 
Coeficiente de asi-
metría de Fisher 
=COEFICIENTE.ASIMETRIA(datos) 
 
Usar funciones Usar Análisis de datos
42 CE104 Estadística 
Notas 
 
 
Distribución normal 
 
En Excel, use las siguientes funciones: 
 P(X ≤ x) =DISTR.NORM.N(x; media; desviación estándar; acumulado) 
 k = INV.NORM(α, media, desviación estándar), tal que P(X ≤ k) = α 
 
Por ejemplo, si X  N ( = 50, 2 = 400) 
 P(X ≤ 60) =DISTR.NORM.N(60; 50; 20; 1) = 0,69146 
 Calcular k tal que P(X ≤ k) = 0,95. k = INV.NORM(0.95; 50; 20) = 82,897 
 
 
Prueba de hipótesis en Excel 
 
Prueba de hipótesis para una media poblacional (µ) con varianza desconocida 
 
Para calcular los valores críticos, usaremos la función: =INV.T (área a la izquierda, grados de liber-
tad) 
 
 
 
Prueba de hipótesis para una proporción poblacional (p) 
 
Para calcular los valores críticos, usaremos la función: INV.NORM. ESTAND(área a la izquierda) 
 
 
 
EPE UPC 43 
 
Notas 
 
 
Prueba de hipótesis para la razón de varianzas poblacionales (12 /22) 
 
1. Ordene los datos por la variable que requerida (en este ejemplo: género) para los cual seleccione 
la opción Datos del menú principal, seleccione Ordenar. 
 
 
 
 
 
2. Seleccione la variable de ordenación, y haga clic en Aceptar. 
 
 
Obtendrá la base de datos ordenada por la variable requerida. 
 
 
 
3. Seleccione la opción Datos del menú principal, seleccione Análisis de Datos. 
 
 
 
44 CE104 Estadística 
Notas 
 
 
4. Seleccione Prueba F para varianzas de dos muestras, y haga clic en Aceptar. 
 
 
 
5. Seleccione los datos de cada grupo a comparar. 
 
 
 
6. Obtendrá lo siguiente: 
 
Prueba F para varianzas de dos muestras 
 Variable 1 Variable 2 
Media 40.8182 38.1111 
Varianza 196.7636 113.1111 
Observaciones 11 9 
Grados de libertad 10 8 
F 1.7396 Estadístico de prueba 
P(F<=f) una cola 0.2222 
 Valor crítico para F (una cola) 4.0306 
 Hombre Mujer 
 
…. 
 
7. Luego, el valor F podremos tomar la decisión en la prueba de hipótesis: 
 
EPE UPC 45 
 
Notas 
 
 
Prueba de hipótesis para la diferencia de medias con varianzas desconocidas supuestas iguales 
 
1. Seleccione la opción Datos del menú principal, seleccione Análisis de Datos. 
 
 
 
2. Seleccione Prueba t para dos muestras suponiendo varianzas iguales, y haga clic en Aceptar. 
 
 
 
3. Seleccione los datos de cada grupo a comparar. 
 
 
 
4. Obteniendo lo siguiente: 
 
Prueba t para dos muestras suponiendo varianzas iguales 
 Variable 1 Variable 2 
Media 40,8182 38,1111 
Varianza 196,7636 113,1111 
Observaciones 11 9 
Varianza agrupada 159,5847 
 Diferencia hipotética de las medias 0 
 Grados de libertad 18 
 Estadístico t 0,4768 
 P(T<=t) una cola 0,3196 
 Valor crítico de t (una cola) 2,0071 
 P(T<=t) dos colas 0,6393 
 Valor crítico de t (dos colas) 2,3562 
 
5. Luego, obtendrá en Excel el punto crítico a emplear en la prueba de hipótesis: 
 
Puntos críticos 
INV.T(0.015,18) = -2.3562 
INV.T(0.03,18) = -2.0071 
INV.T(0.97,18) = 2.0071 
INV.T(0.985,18) = 2.3562 
 
46 CE104 Estadística 
Notas 
 
 
Prueba de hipótesis para la diferencia de medias con varianzas desconocidas supuestas desiguales 
 
1. Seleccione la opción Datos del menú principal, seleccione Análisis de Datos. 
 
 
 
2. Seleccione Prueba t para dos muestras suponiendo varianzas desiguales, y haga clic en Aceptar. 
 
 
 
3. Seleccione los datos de cada grupo a comparar. 
 
 
 
4. Obtendrá lo siguiente: 
 
Prueba t para dos muestras suponiendo varianzas desiguales 
 Variable 1 Variable 2 
Media 3.9091 2.2222 
Varianza 2.8909 0.9444 
Observaciones 11 9 
Diferencia hipotética de las medias 0 
 Grados de libertad 16 
 Estadístico t 2.7817 
 P(T<=t) una cola 0.0067 
 Valor crítico de t (una cola) 1.5529 
 P(T<=t) dos colas 0.0133 
 Valor crítico de t (dos colas) 1.9417 
 Hombre Mujer 
 
5. Luego, obtener en Excel el punto crítico a emplear en la prueba de hipótesis: 
 
Puntos críticos 
INV.T(0.035,16) = -1,9417 
INV.T(0.07,16) = -1,5529 
INV.T(0.93,16) = 1,5529 
INV.T(0.965,16) = 1,9417 
 
EPE UPC 47 
 
Notas 
 
 
Regresión lineal simple 
 
Reporte de regresión en Excel 
 
En Excel, se puede obtener este reporte de regresión mediante la opción en Datos. Luego se hace clic 
en Análisis de Datos y finalmente en Regresión. 
 
Cabe mencionar, que Excel presenta un error en el texto “Valor crítico de F” en la tabla Análisis de 
varianza, el texto correcto es “Probabilidad”, que se refiere al valor p de la prueba.