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.

 

 

 

 

Creación de tablas dinámicas en Excel 2019 y Microsoft Excel 365 (Web)

Una tabla dinámica es una herramienta que nos permite analizar, explorar y presentar datos en forma de resumen. Una tabla dinámica también nos permite realizar una gran cantidad de cálculos con nuestros datos sin la necesidad de escribir fórmulas, además, estas tablas nos permiten ver la información de forma ordenada, esquematizada y amigable.

Estructura de una tabla dinámica

Una tabla dinámica tiene la estructura que se muestra en la siguiente imagen.

Como se muestra, una tabla dinámica tiene 4 zonas o secciones, en ellas debemos de colocar los campos de alguna de nuestras tablas o base de datos que tengamos previamente en Excel. Es muy importante determinar en qué zona deseamos colocar cada campo para que de esta manera Excel pueda mostrarnos el diseño de tabla dinámica que necesitamos.

Cabe destacar que Excel nos permite dejar vacías una o varias zonas de una tabla dinámica, con esto, podemos personalizar el resumen de tabla dinámica que deseamos presentar o analizar, lo que no tiene mucho sentido en dejar vacío es la zona de valores porque en ella se van a realizar cálculos numéricos en base a los datos que tengamos.

Otro aspecto importante de las tablas dinámicas es que podemos agregar varios campos a una zona de una tabla, es por esto que también se denominan dinámicas porque podemos realizar en ellas muchos movimientos que nos van a permitir probar cual es la mejor manera en que podemos resumir y analizar nuestros datos.

Recomendaciones para la creación de una tabla dinámica

Para crear una tabla dinámica se propone utilizar las siguientes recomendaciones.

• Utilizar cabeceras o títulos en las columnas de nuestros datos
• Evitar usar espacios en blanco en campos numéricos, por ejemplo, se puede rellenar con ceros en espacios en blanco.
• No utilizar filas o columnas completamente en blanco.
• Eliminar los cálculos de una tabla o de una base de datos que ya los contenga.

El uso de estas recomendaciones se propone con el fin de que podamos crear una tabla dinámica que sea fácil de leer e interpretar.

Creación de una tabla dinámica en Excel 2019

A continuación se puede observar los pasos que se pueden seguir para crear una tabla dinámica en Excel 2019 versión de escritorio.

Consideremos la siguiente base de datos de una tienda de artículos.

Para poder crear una tabla dinámica a partir de estos datos, vamos a seleccionar cualquier celda de nuestra tabla para que Excel tenga una referencia desde la cual debe crear la tabla dinámica y después vamos a dirigirnos a la pestaña Insertar y luego damos clic en Tablas dinámicas recomendadas.

Con esto, va a aparecer una ventana que nos va a proponer algunos diseños de tabla y también nos va a dar la opción de crear una en blanco, vamos a elegir esta opción.

Al seleccionar esta opción, vamos a poder crear una tabla dinámica a nuestro gusto desde la ventana de creación que nos provee Excel.

En la ventana de creación de tabla dinámica podemos ver las zonas que se han descrito anteriormente.

Un primer diseño de tabla dinámica que podemos definir es aplicando lo siguiente: el campo Sección en la zona de columnas, el nombre de los artículos en las filas y la suma de precios de cada producto en la zona de valores.

Con esta configuración, la tabla dinámica resultante es la siguiente.

La tabla dinámica resultante se puede interpretar como un sistema de coordenadas, por ejemplo, el abrigo de caballero tiene una intersección con la sección de Confección, el balón de baloncesto con la sección de deportes, el carro de control con la sección de Juguetería y así sucesivamente. Otro aspecto importante que se puede observar en esta configuración de tabla dinámica es el total general, este total se genera para todas las secciones pero también para todos los productos, sin embargo la mayoría de los productos aparecen una sola vez en la tabla, por tanto los totales de cada producto son del mismo valor de cada producto, en algunas excepciones existen totales de productos que aparecen más de una vez en la tabla de datos, el destornillador es un caso de este tipo, como aparece 2 veces la tabla dinámica va sumar el total de ambos precios y lo va mostrar en la intersección del producto con la categoría a la que pertenece (ferretería), podemos dar un doble clic en ese producto (en la tabla dinámica) y se va abrir una subtabla que muestra el precio individual de cada destornillador así como todas los datos relacionados a este producto.

En esta tabla dinámica que acabamos de crear solo hemos colocado un campo en cada zona de la tabla pero esta configuración puede cambiar como veremos a continuación.

Otro tipo de tabla dinámica que podemos crear es utilizando la siguiente configuración: la sección y el nombre del artículo en la zona de filas y la suma de precios en la zona de valores, cabe destacar que es importante el orden en que se coloca un campo en alguna zona de la tabla dinámica, en este caso primero se coloca la sección y después el nombre del artículo en la zona de filas.

La tabla dinámica resultante se muestra en la siguiente figura.

La tabla resultante nos muestra la importancia del orden en el que se colocan los campos en cada zona de una tabla dinámica porque esto nos permite analizar de manera más sencilla el resumen de nuestros datos.

Creación de una tabla dinámica en Excel 365 (Web)

La creación de una tabla dinámica en Excel 365 versión web es similar a la versión de escritorio, para acceder a esta versión solo es necesario tener una cuenta de correo de Microsoft y los archivos se guardan en la nube en lugar de guardarse en nuestra computadora local. 

A partir de nuestra bases de datos, vamos a seleccionar cualquier celda de nuestra tabla para que Excel tenga una referencia desde la cual debe crear la tabla dinámica y después vamos a dirigirnos a la pestaña Insertar y luego damos clic en Tabla dinámica y después elegimos Desde una tabla o rango.

Con esto, va a aparecer una ventana que nos va a pedir que seleccionemos la tabla o rango de celdas en el que queremos trabajar y también si deseamos colocar la nueva tabla dinámica en una nueva hoja de cálculo o en la hoja donde se encuentran los datos, en nuestro caso vamos a crearla en una nueva hoja, además, los datos de referencia se seleccionan automáticamente por Excel dado que ya habíamos dado clic en alguna celda de nuestros datos.

Con esto en mente, la creación de la tabla dinámica desde la ventana de creación, es similar a la versión de escritorio, solo necesitamos elegir los campos y su orden para poder colocarlos en las zonas de la tabla dinámica.