Análisis de datos estadísticos con Excel para dummies – histograma, tabla de frecuencias y boxplot

¿Sabes que Excel puede ser una herramienta muy útil para analizar datos ¿Sabes que es capaz de hacer análisis estadísticos que pueden ayudarte a tomar mejores decisiones? Ha llegado el momento de juntar estos dos mundos: Excel y Estadística. Descúbre el mundo del análisis de datos estadísticos con Excel. Me voy a encargar que te introduzcas en él a base de ejemplos. ¡Empecemos!

Estoy encantado de poder escribir en excelyvba.com, mi blog referencia en Excel. En Conceptos Claros siempre recomiendo los consejos y enseñanzas que Quique comparte en este blog. Todos mis lectores ya saben de la existencia de la mega guía de funciones Excel. ¡Están avisados! 🙂

Para enseñarte las características básicas de análisis de datos estadístico en Excel quiero utilizar un ejemplo muy sencillo. Son datos de 247 personas descritas por 2 variables:

  • La primera columna es el Sexo de cada individuo: variable categórica
  • La segunda columna es el peso en Kg de cada individuo: variable numérica

Puedes descargarte el ejemplo aquí

En este artículo quiero ayudarte a pintar histogramas, calcular la tabla de frecuencias y boxplots. Recuerda que en este blog tienes una guía muy completa de gráficos en Excel.

¿Cómo cargar el paquete análisis de datos?

Antes de empezar te recomiendo que cargues el paquete Herramientas para análisis que tiene Excel.

Aunque la web oficial de microsoft office te explica cómo hacerlo prefiero ponerte aquí los pasos a seguir:

  1. Archivo >> Opciones >>
  2. Ves a Complementos >> Ir a…
  3. Marca la opción Herramienta para el análisis y aceptar

Esta imagen te muestra la ventana de Opciones >> Complementos

La siguiente imagen refleja la opción que marcas para activar las herramientas de análisis de datos.

Al terminar podrás ver en la pestaña datos aparecerá una nueva opción 😉 Análisis de datos

¿Cómo construir una tabla de frecuencias y el histograma con la herramienta para el análisis de Excel?

En este apartado te voy a mostrar como puedes crear una tabla de frecuencias con los intervalos escogidos de manera eficaz y hacer el histograma con la herramienta para el análisis que has cargado antes.

¿Qué es un histograma y la tabla de frecuencias?

Antes de nada quiero explicarte qué es un histograma y qué es la tabla de frecuencias:

  • Un histograma es un diagrama de barras. La altura de cada barra refleja la cantidad de individuos de un clase o intervalo.
  • La tabla de frecuencias es una manera de resumir los datos. Imagínate un montón de números en fila. Puedes agrupar por intervalos y contar cuántos individuos tienen de un mismo intervalo o clase.

Te pongo un pequeño ejemplo en excel para que veas que quiero decir. En este caso son datos de la edad del pueblo de Pequeñiland.

Puedes descargarte el pequeño ejemplo aquí

La primera columna refleja la edad. Solo son números. Por si solos no te dicen nada Pero puedes agrupar las edades en grupos de 10 años en 10 años. Estos grupitos son las clases o intervalos.

La frecuencia será la cantidad de personas de cada grupo. Puedes crear una tabla muy sencilla con las frecuencias por cada rango de edad.Esto es la tabla de frecuencias.

Solo hace falta dibujar el histograma pintando las barras con la altura de cada frecuencia.

Con el ejemplo que te he dejado lo entenderás muy bien.

¿Cómo calcular la tabla de frecuencias y dibujar el histograma?

Solo tienes que ir a la pestaña Datos > y al final a la derecha tienes la herramienta que has cargado antes Análisis de datos

Selecciona histograma. Con esta opción vas a crear la tabla de frecuencias que mejor se ajusta a tus datos y el histograma.

En la ventana siguiente sólo tienes que:

  • Seleccionar el rango de datos de entrada
  • Seleccionar dónde quieres que copie la tabla de frecuencias y el histograma
  • Activar la opción crear gráfico y si quieres % acumulado

Excel se ha encargado de seleccionar el rango de clases óptimo para tu caso.

Este es el resultado:

Puedes juntar las barras del histograma de manera muy sencilla. Selecciona las barras >> botón derecho y ancho de intervalo 0.

Puedes también decidir el intervalo de clases manualmente. Por ejemplo he decido usar de 10 en 10 kg. He creado una nueva columna con las clases personalizadas.

Para crear este nuevo histograma con los intervalos de clases de 10 en 10 solo tienes que introducir estos intervalos de clase en la opción Rango de clases > Seleccionas el rangoEste es el resultado que obtienes:

Fíjate que ahora el histograma tiene los intervalos que yo he puesto. Fácil, ¿verdad?

¿Cómo dibujar el diagrama de Pareto?

Te puede interesar dibujar el diagrama de Pareto. Es decir ordenar las alturas de las barras del histograma de mayor a menor. Sencillamente selecciona la opción Pareto (Histograma Ordenado)

Y…

Ahora tienes las barras ordenadas de mayor altura a menor. 🙂

¿Cómo crear el resumen numérico de los datos en Excel?

Ahora quiero que veas cómo hacer el resumen numérico de los datos. ¿y te preguntarás qué es el resumen numérico?

¿Qué es un resumen numérico de los datos?

Es muy sencillo. Se trata de calcular los números que pueden resumir las propiedades del histograma:

  • centralidad
  • y dispersión

En la siguiente figura puedes ver la idea de centralidad y dispersión en un histograma:

El valor central es algo así como el valor medio. Y la dispersión es la distancia de los datos al valor central. Cuanta más dispersión más alargado es el histograma.

Los parámetros más interesantes son:

  1. Para la centralidad >> el promedio o media y la mediana o cuartil 2.
  2. Para la dispersión >> la desviación típica y el rango intercuartílico.

La idea principal es que el promedio y la desviación típica se calculan con una fórmula:

  • La media es la suma de todo los datos dividido por el total de individuos.
  • La desviación típica es la media de las distancias al cuadrado de los datos a la media.

En cambio los cuartiles se calculan ordenando los datos de menor a mayor. Y agrupando en 4 grupos iguales en número. Las fronteras son los cuartiles.

  • La mediana es el cuartil 2.
  • El rango intercuartílico es la diferencia entre el cuartil 1y 2.

>> Aquí puedes consultar más información acerca del valor central y dispersión

¿Cómo calcular el valor central y la dispersión en Excel?

Estas son las funciones en Excel para cada parámetro:

  • Media >> = PROMEDIO(B2:B258)
  • Mediana >> =MEDIANA(B2:B258)
    Cuartil 2 o Mediana >> =CUARTIL.EXC(B2:B258;2)
  • Desviación típica >> =DEVESTA(B2:B258;2)
    Cuartil 1 >> =CUARTIL.EXC(B2:B258;1)
    Cuartil 3 >> =CUARTIL.EXC(B2:B258;3)
  • Rango intercuartílico >> =CUARTIL.EXC(B2:B258;3)-CUARTIL.EXC(B2:B258;1)
  • Número de observaciones o “N” >> =CONTAR(B2:B258)

Aquí tienes los resultados que he obtenido y las fórmulas que he usado también:

¿Cómo completar el resumen numérico con la Herramienta para el Análisis?

Aún puedes calcular más parámetros con la herramienta Análisis de Datos. ¡Vamos a ver cuáles!

Para hacerlo: en la pestaña Datos >> Análisis de datos y después selecciona la opción Estadística Descriptiva.

Selecciona el rango de entrada de datos como siempre y el el rango de salida. Dónde quieres pintar la tabla del resumen numérico:

Para que te quede más claro. Estas son las opciones que he utilizado:

Esta opción sirve para obtener una tabla con distintas características numéricas:

 

Fíjate que no aparece la información de cuartiles ni el rango intercuartílico tampoco. Pero sí, la media y la desviación típica o estándar.

Como ves puedes complementar la información con otras características que ves explicadas en la imagen. Como el rango, la moda, nivel de confianza de la media, varianza…

¿Cómo construir un diagrama de barras con variables categóricas?

Hasta ahora he mostrado las opciones con variables numéricas. Pero a veces aparecen variables categóricas.

En este caso la variable sexo, es categórica. Tienes las categorías HOMBRE y MUJER.

El conceptos del diagrama de barras de variables categóricas es bastante sencillo. La altura de cada barra será el número de personas de cada grupo o categoría. Para hacerlo en Excel.

Selecciona la columna de variables categóricas con el título de la columna:

Vas a Insertar gráfico > Gráficos recomendados

Excel crea automáticamente un gráfico con tablas dinámicas. Recuerda que Quique ya te explicó en este post que es una tabla dinámica y cómo se crea.

Este caso la tabla dinámica consiste en contar el número de personas de cada grupo.

También lo puedes hacer manualmente con la función CONTAR y obtener la misma tabla y hacer el gráfico de barras sencillamente.

Otra opción es hacer la tabla dinámica como tu quieras. Sólo tienes que seleccionar los datos así:

Después Insertar >> Tabla Dinámica y seleccionar la celda donde quieres que se calcule la tabla:

Ahora teniendo seleccionada la tabla dinámica que acabas de crear puedes cambiar el tipo de cálculo:

  • Puedes calcular el peso total por cada categoría
  • o puedes calcular el promedio o media por cada categoría

Yo he calculado el promedio utilizando las opciones…

Si buscas el Tipo de Filtro (abajo a la derecha) y le das a Configuración de Campo de Valor… >> Puede cambiar el tipo de cálculo.

He seleccionado el promedio:

Y puedes crear el gráfico de barras para comparar los dos grupos… Y buahla!

¡No está nada mal! ¿verdad? En un gráfico puedes comparar la media de las las categorías fácilmente.

¿Cómo construir el boxplot con Excel?

El boxplot es una herramienta que te permite comparar grupos muy fácilmente. En el ejemplo tienes dos grupos: HOMBRES y MUJERES. Puedes comparar los dos grupos de un vistazo en un solo gráfico.

¿Qué es un boxplot, whisker o gráfico de bigotes?

El boxplot se basa en el uso de los cuartiles. El cuartil 1, el cuartil 2 y el cuartil 3. La siguiente imagen ilustra muy bien qué es un boxplot.

¿Cómo dibujar un boxplot en Excel?

Para hacerlo con Excel sólo tienes que seleccionar los datos así:

De esta manera tienes seleccionada la variable categórica SEXO (la primera columna) que corresponde al grupo. Y la variable numérica peso (la segunda columna). Que es la variable que quieres comparar.

Sólo tienes que Insertar >> Gráfico >> Ver todos los gráficos y te saldrá la ventana siguiente:

Vas a la opción Cajas y bigotes. Dando en aceptar tienes creado el gráfico de boxplot para los dos grupos:

Este gráfico está muy olvidado para los no estadísticos pero es fantástico para comparar grupos muy rápidamente.

Condensa la dispersión y el valor central en un caja. En este caso puedes ver como el grupo de Mujeres tiene un peso menor que el de hombres.

Incluso la mediana del grupo de mujeres es menor que el cuartil 1 del grupo de hombres por ejemplo.

Y… el resumen del artículo

Te he mostrado cómo hacer:

  1. Cargar la herramienta Análisis de Datos
  2. Dibujar un histograma y calcular su tabla de frecuencias.
  3. Dibujar un diagrama de Pareto
  4. Calcular el resumen numérico de los datos (cuartiles, media, desviación estándar etc…) Mediante fórmulas Excel y mediante la herramienta Análisis de Datos
  5. Dibujar un histograma con variables categóricas. Mediante tablas dinámicas. Cambiando el valor del atributo a promedio
  6. Dibujar el Boxplot en Excel

 

Y hasta aquí el artículo. ¡Espero que haya sido útil! La estadística y Excel son mundos muy cercanos aunque no lo parezca. Espero verte pronto en otro artículo de análisis de datos estadísticos con Excel.
Recuerda pasarte por las secciones de tablas dinámicas y el manual de gráficos que te ha preparado Quique 😉

(Visited 60.043 times, 1 visits today)

Uso de cookies

Este sitio web utiliza cookies para que usted tenga la mejor experiencia de usuario. Si continúa navegando está dando su consentimiento para la aceptación de las mencionadas cookies y la aceptación de nuestra política de cookies, pinche el enlace para mayor información ACEPTAR

Aviso de cookies