Análisis estadístico con Excel - Ejercicio 3

Se han medido los diámetros de 50 tornillos y se han obtenido los resultados siguientes en milímetros.

Se desea obtener la tabla de frecuencias para la variable diámetro, construir el histograma de frecuencias absolutas, estudiar la simetría de la distribución, además se tiene la pregunta ¿se puede intuir si los datos provienen de una distribución normal?

Para formar la tabla de frecuencias adecuadamente, podemos tomar el número de clases dado por la fórmula de Sturgess: k=1+Ent(3*3logN); o bien: k=Ent(√N), siendo Ent la función parte entera.

En este caso k=Ent(√50)=Ent(7.071)=7, esto es, se van a tomar 7 clases para representar al histograma.

Para calcular los límites para estas clases se van a usar dos fórmulas:

• Límite inferior de la clase = Límite inferior de la clase anterior + Tamaño de intervalo
• Límite superior de la clase = Límite inferior de la clase anterior + Tamaño de intervalo - Unidad de variación

En este caso, al observar los datos es posible ver que la mínima diferencia entre los diámetros es de 0.1 mm, por tanto este valor se puede tomar como la unidad de variación. También hay que ajustar el tamaño exacto del intervalo, este debe ser por lo menos igual a la siguiente unidad de variación después del valor del ancho de clase (0.65), entonces 0.65+0.1=0.75 será el tamaño ajustado del ancho de clase.

Los cálculos de los límites se realizan en Excel y se muestran en la siguiente imagen.

Con estos cálculos se puede introducir una columna de clases para la opción del Rango de salida de la función Histograma de Excel, este rango de salida corresponde a los límites superiores de las clases.

El histograma que se va generar se muestra en la siguiente imagen junto con la tabla de frecuencias que ha creado Excel a partir de los límites que se han proporcionado.

Respecto a la normalidad de los datos, se puede observar una ligera simetría hacia la izquierda en la gráfica del histograma.

Análisis estadístico con Excel - Ejercicio 2

Los valores de los pesos en miligramos de 80 engranes pequeños producidas por una máquina son los siguientes:

Sobre estos datos, se desea construir la distribución de frecuencias adecuada a los datos, el histograma de frecuencias absolutas, el polígono de frecuencias relativas acumuladas y comprobar la normalidad de los datos.

Para lograr el objetivo se rellena la ventana de Histograma como se indica en la figura siguiente. En el campo Rango de entrada se introduce el rango en el que se sitúan los datos de la variable. En el campo Rango de clases se sitúa el rango que ocupa la columna de los extremos superiores de los intervalos de clase, pero en este caso se deja vacío para que Excel divida los datos automáticamente en un número adecuado de clases de la misma anchura. En el campo Rango de salida se coloca el rango que ocupará la tabla de frecuencias, pero en este caso se va a colocar sólo el extremo superior izquierdo de dicho rango. Es importante seleccionar la opción Gráfico para obtener el histograma de frecuencias absolutas, y la opción Porcentaje acumulado para obtener el polígono de frecuencias relativas acumuladas.

El histograma generado a partir de lo explicado previamente se ajusta a una campana de Gauss lo que indica la normalidad de los datos, el histograma se muestra a continuación.

Análisis estadístico con Excel - Ejercicio 1

En una clínica se han registrado durante un mes las longitudes en metros que los niños recorren el primer día que comienzan a caminar, obteniéndose los siguientes resultados.

Se desea construir la distribución de frecuencias adecuada para la variable longitud y realizar los gráficos pertinentes que la representen.

En estos datos, llamaremos X al conjunto de datos que contienes las distancias recorridas por los niños, esta es una variable cuantitativa con valores sin agrupar, se puede graficar un diagrama de barras situando sobre el eje de las abscisas los valores de la variable X. Sobre el eje de las ordenadas también se van a graficar las frecuencias absolutas acumuladas Ni, con esto obtendremos el diagrama de barras acumuladas. La tabla de frecuencias de la variable X se puede realizar en Excel partiendo de las variables Xi y ni para después realizar los cálculos de las frecuencias absolutas acumuladas, frecuencias relativas y frecuencias relativas acumuladas.

El diagrama de barras se va a crear usando el asistente de gráficos. Comenzamos seleccionando las celdas que contienen los datos que deseamos presentar en el gráfico de barras (en este caso la columna de frecuencias absolutas) y luego elegimos el gráfico Columnas agrupadas de Excel.

También se puede construir el diagrama de frecuencias absolutas acumuladas de manera similar a como se realizo en el proceso anterior.

El polígono de frecuencias se puede construir uniendo los puntos (Xi, ni), para esto, primero se eligen los pares de datos de estas columnas y luego elegimos el tipo de gráfico Dispersión con líneas rectas y marcadores. El gráfico resultante se muestra en la siguiente figura.

También puede construirse el polígono de frecuencias acumuladas uniendo los puntos (Xi, Ni), previa selección de los mismos en la hoja de cálculo y utilizando el asistente para gráficos como en el ejemplo anterior.

Interpretación de los resultados de las frecuencias

• Las frecuencias absolutas muestran que las distancias más comunes recorridas por los niños de la clínica fueron 4 m y 6 m, estas frecuencias tienen un valor de 10.
• El 82.5% de los niños y niñas ha recorrido 6 m o menos, el otro 17.5% restante han recorrido 8 o más metros. Esto nos dice que son muy pocas las personas que han recorrido grandes distancias como 8 o más metros, esto se debe a la edad de estas personas. Entonces se puede decir que los datos se concentran en un distancia recorrida de 6 m o menos.
• En las frecuencias relativas (fi) se puede observar que las distancias 1, 8 y 10 son las menos comunes, cada una de estas solo aportan un 5% (0.05) al total de los datos.

Funciones de bases de datos en Excel

En Excel existen funciones para procesar datos en hojas con grandes cantidades de datos, estas nos facilitan el procesamiento porque piden pocos argumentos para poder trabajar. A continuación se describe la función BDSUMA en Excel.

BDSUMA proporciona la suma de los números de los campos (columnas) de registros que cumplen las condiciones especificadas.

Sintaxis: BDSUMA(base_de_datos, nombre_de_campo, criterios)

La función BDSUMA requiere los siguientes argumentos:

• base_de_datos: Este es el rango de celdas que compone la lista o base de datos. Una base de datos es una lista de datos relacionados en la que las filas de información son registros y las columnas de datos son campos. La primera fila de una lista contiene etiquetas para cada columna en ella. Este argumento es obligatorio
• nombre_de_campo: Esto especifica qué columna se usa en la función. Especifique el rótulo de columna entre comillas, como "Edad" o "Rendimiento", por ejemplo. Como alternativa, puede especificar un número (sin comillas) que represente la posición de la columna dentro de la lista: por ejemplo, 1 para la primera columna, 2 para la segunda, etc. También podemos colocar la celda que contiene el campo sobre el cual deseamos calcular la suma
• criterios: Este es el rango de celdas que contiene las condiciones especificadas. Puede usar cualquier rango en el argumento criterios mientras este incluya al menos un rótulo de columna y una celda debajo del mismo en la que se pueda especificar una condición para la columna.

Nota: Los 3 argumentos de la función son requeridos para la misma, no es posible omitir ninguno de ellos.

Como ejemplo, podemos considerar la siguiente base de datos de una tienda de artículos.

 

En esta base de datos podemos calcular, por ejemplo, el precio de aquellos artículos cuyo país de origen es España  y su sección es Ferretería, para esto, en la función BDSUMA pasamos como primer argumento la base de datos que está entre las celdas A1 a G41, después le indicamos la celda que contiene a campo en donde se va a realizar la suma y por último se le provee a la función los criterios, estos últimos están fueron colocados entre las celdas A44 a G45.

 

 

 El uso de la función BDSUMA nos facilita los cálculos porque solo es necesario indicar una matriz que contenga los criterios que queremos aplicar a la operación de suma, esa matriz se puede construir por medio de una pequeña tabla que tiene los datos con los que deseamos trabajar.

 

En caso de que se quisiera usar otra fórmula para hacer los cálculos con criterios determinados entonces sería un poco más complicado realizar esta operación, por ejemplo si se usa la fórmula SUMAR.SI.CONJUNTO se tendrían que mandar más argumentos a la misma pero en el caso de la fórmula BDSUMA es posible usar pocos argumentos, uno de ellos es la matriz o tabla que contiene todos los datos con los criterios requeridos. Un uso práctico de la fórmula BDSUMA sería cuando el número de criterios sea muy extenso, en ese caso tal fórmula reduciría drásticamente el número de argumentos que se necesitan si en su lugar se usará otra fórmula como SUMAR.SI.CONUNTO.