Creación de tablas dinámicas en Google Sheets

El espacio de trabajo de Google en sus hojas de cálculo (Google Sheets) ofrece en sus herramientas la posibilidad de trabajar con tablas dinámicas de manera similar a como lo hace Excel, con ellas también podemos resumir y analizar datos, a continuación se muestra un ejemplo paso a paso de cómo crear tablas dinámicas en esta herramienta.

Se puede considerar el siguiente ejemplo de una tabla de empleados de una empresa hipotética en Sheets (Clic en la imagen para ampliarla).

Ahora, para agregar la tabla dinámica nos dirigimos a la pestaña Insertar y en ella elegimos la opción de tabla dinámica que se muestra en la siguiente figura (Clic en la imagen para ampliarla).

Ya que se ha elegido la opción previa se va mostrar una ventana con el origen de datos que necesita la tabla dinámica así como un par de opciones para decidir donde colocar la nueva tabla, esto se puede observar en la siguiente figura.

Después, se va mostrar una sección con las mismas zonas que tiene Excel para trabajar las tablas dinámicas, es decir, filas, columnas, valores y filtros, en cada zona vamos a poder agregar la columna o campo que deseemos para poder realizar el análisis requerido. Véase la siguiente imagen.

Una configuración de tabla dinámica que se puede diseñar es el siguiente, colocar en la zona de valores los salarios de los empleados, en las filas sus nombres y en las columnas los respectivos departamentos, la tabla dinámica generada se muestra en la siguiente figura.

En cuanto a la zona de filtros se puede usar el estado activo o inactivo de los empleados para poder visualizar solo aquellos con un estado determinado.

Además, Sheets ofrece algunas sugerencias de tablas dinámicas que pueden ser de utilidad, estas se pueden observar en el editor de tablas, de esta manera podemos encontrar diversas opciones que nos podrían ayudar a analizar la información y que no habíamos tenido en cuenta, véase la siguiente imagen.

Otra de las ventajas de Google Sheets es la compatibilidad de los archivos con los de Microsoft Excel, de esta manera se pueden trabajar los proyectos en cualquiera de las plataformas de acuerdo a las necesidades que se tengan, así mismo Sheets se encuentra en una plataforma en línea o en la nube lo cual permite tener acceso a los archivos desde cualquier dispositivo con acceso a internet. También es posible colaborar con otros usuarios o miembros de un equipo en un mismo archivo. En resumen, esta herramienta nos da una alternativa a Excel que puede ser muy poderosa para manejar las bases de datos o proyectos que requieren el uso de una hoja de cálculo.

Análisis de una base de datos por medio de tablas dinámicas

Análisis de información de un conjunto de datos con productos de electrónica, este conjunto de datos simula una tienda de componentes electrónicos con algunos campos relevantes como las categorías, precio, país de origen, disponibilidad del producto, cantidad en existencia etc, además se colocan diversos productos que son comunes en el área de la electrónica. En este análisis se hace uso de diferentes configuraciones de tablas dinámicas de Excel para observar la utilidad que estas nos pueden proveer al momento de gestionar los productos que se tienen en el stock.

Enlace para acceder a este proyecto de Excel: https://1drv.ms/x/c/26601cb78d61cc7d/ProyectoComponentesElectronicos

La siguiente imagen muestra un extracto del conjunto de datos (Clic en la imagen para ampliarla)

Una primer configuración de tabla dinámica para este conjunto de datos es colocar en la zona de filas la categoría y justo después el producto, además en la zona de valores el precio unitario, esto con el fin de crear un filtro que nos permita analizar de manera individual cada categoría junto con los productos que pertenecen a ella, además, con esto en mente es posible agregar a la zona de filtros el país de origen y la existencia de los productos, esto sería de utilidad para situaciones como las siguientes:

• Visualizar aquellas categorías con productos sin existencia para un posterior resurtido de componentes.
• Considerar colocar nuevos productos a cada categoría así como agregar nuevas categorías para mejorar la variedad del producto.
• Evaluar la posibilidad de contactar nuevos países a los que se les solicite importar determinados productos de cada categoría, etc.

En la siguiente imagen se muestra la tabla dinámica estructurada como se describió previamente. (Clic en la imagen para ampliarla)

Otra configuración para analizar los datos que puede resultar útil en una tabla dinámica es la siguiente, en la zona de filtros la categoría y la existencia del producto, en la zona de columnas el país de origen, en la zona de filas los productos y en la zona de valores los precios de cada producto, con esto, podemos evaluar los siguientes casos:

• Considerar todos los productos de una categoría que provienen de un país determinado para ampliar o disminuir la cantidad de países de los cuales traer esos productos requeridos.
• Evaluar la necesidad de ampliar los productos que pertenecen a cada categoría y al mismo tiempo tomar en cuenta los países de los que provienen tales productos, etc.

La tabla dinámica con esta configuración se puede observar a continuación. (Clic en la imagen para ampliarla)

La ventaja de las tablas dinámicas es que podemos colocarlas de diferentes maneras para facilitar la visualización, resumen y análisis de datos, incluso con configuraciones simples como la siguiente, en la zona de filtros la categoría, en la de filas el producto y en los valores los precios, esto podría ser valioso para analizar cada categoría junto con los precios unitarios de los productos para casos como gestionar los precios de acuerdo a las necesidades económicas de la empresa o negocio, además esto tiene la ventaja de que la visualización se muestra en un solo lugar sin tener que recorrer todo el conjunto de datos cuando este se vuelva más extenso. La siguiente imagen muestra esta tabla dinámica que es más sencilla de estudiar. (Clic en la imagen para ampliarla)

Adicionalmente, en la tabla que se configuró previamente se podría agregar un campo calculado que nos muestre el precio total para cada producto de acuerdo a la existencia de los mismos, esto nos va permitir personalizar los cálculos que no estén en existencia en la serie de operaciones que nos ofrece Excel, con esto podemos resumir y administrar de manera más extensa la información del conjunto de datos. La siguiente imagen muestran el campo calculado que se colocó en la tabla dinámica.

Como conclusión de este análisis, se puede observar el numeroso grupo de posibilidades que ofrecen las tablas dinámicas para facilitar la toma de decisiones que se necesitan tomar en una empresa, negocio o conjunto de información, en este caso, en el área de la electrónica.

Análisis estadístico con Excel - Ejercicio 4

Las puntuaciones obtenidas por 100 aspirantes a la universidad en el último ejercicio se presentan en la siguiente figura  (Clic en la imagen para ampliarla).

Se desea construir la distribución de frecuencias adecuada para las puntuaciones, hallar el porcentaje de alumnos que aprobó el examen con 6 y encontrar el porcentaje de alumnos que sacaron notas superiores a 6.  Además, si sólo hay 20 plazas, ¿en qué nota hay que situar a los obtuvieron una plaza? 

También se requiere realizar  las  representaciones  gráficas  de  la  distribución  adecuadas  para  este problema.

La tabla de frecuencias se muestran en la siguiente imagen  (Clic en la imagen para ampliarla).

Se puede observar que el porcentaje de aspirantes que aprobó el examen con 6 fue del 11%, esto se observa en la frecuencia relativa de Xi=6, además, los aspirantes que obtuvieron una nota mayor a 6 son del 20%, esto se puede observar en la frecuencia relativa acumulada de Xi=6, hasta este valor hay un 80% que obtuvieron calificación de 6 o menos, por tanto el porcentaje restante obtuvieron una calificación superior a 6.

Respecto al número de plazas, se sabe que solo hay 20 disponibles, entonces en los datos se puede ver en la frecuencia absoluta acumulada (Ni) que hay 80 personas con calificación menor o igual a 6, las demás personas son 20, ellos obtuvieron una calificación de mayor a 6, entonces se puede concluir que la calificación para obtener una plaza debe ser mayor a 6.

Debido a que se está trabajando con una variable cuantitativa con datos sin agrupar entonces esta se puede representar con un diagrama de barras o mediante un polígono de frecuencias. Estas gráficas se muestran a continuación (Clic en la imagen para ampliarla).

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.