Análisis moderno de datos en Excel

Capítulo 1. Tablas: El portal al Excel moderno

Este trabajo se ha traducido utilizando IA. Agradecemos tus opiniones y comentarios: [e-mail]

Excel cuenta con una amplia gama de herramientas analíticas, lo que puede dificultar la determinación del mejor punto de partida. Sin embargo, un paso fundamental es dominar la tabla de Excel. Este capítulo profundiza en los elementos esenciales de las tablas de Excel, que actúan como conducto hacia Power Query, Power Pivot y otras herramientas destacadas en este libro. Además, subraya la importancia de organizar meticulosamente los datos dentro de una tabla. Para acceder al contenido de este capítulo, navega hasta ch_01.xlsx en la carpeta ch_01 situada en el repositorio complementario del libro.

Crear y consultar encabezados de tabla

Un conjunto de datos sin encabezamientos de columna es prácticamente inútil, ya que carece de contexto significativo para interpretar lo que mide cada columna. Por desgracia, no es infrecuente encontrar conjuntos de datos que incumplen esta regla cardinal. Las tablas de Excel son un valioso recordatorio de que la calidad de un conjunto de datos depende de la presencia de encabezados claros e informativos.

En la hoja de cálculo start de ch_01.xlsx, te encontrarás con datos en las columnas A:F sin los encabezados correspondientes, que actualmente se encuentran en las columnas H:M. Este diseño es mucho menos que óptimo. Para ajustarlo, haz clic en cualquier lugar de la fuente de datos primaria y ve desde la cinta a Insertar → Tabla → Aceptar, como se ilustra en la Figura 1-1. Alternativamente, puedes pulsar Ctrl+T o Ctrl+L desde dentro de la fuente de datos para lanzar el mismo cuadro de diálogo Crear tabla.

Figura 1-1. Convertir la fuente de datos en una tabla

El cuadro de diálogo Crear tabla te pide automáticamente que especifiques si tus datos incluyen cabeceras. Actualmente, no es así. En ausencia de cabeceras, al conjunto de datos se le asigna automáticamente una serie de columnas de cabecera denominadas Column1, Column2, y así sucesivamente.

Desde aquí, puedes cortar y pegar los encabezados de las columnas H:M en la tabla principal para aclarar lo que se mide en cada columna, como en la Figura 1-2.

Figura 1-2. Tabla Excel con encabezados

Las columnas de encabezado de las tablas de Excel ocupan un papel único en el conjunto de datos. Aunque forman parte de la tabla, funcionan como metadatos de y no como datos propiamente dichos. Las tablas de Excel ofrecen la posibilidad de distinguir mediante programación entre encabezados y datos, a diferencia de lasfórmulas clásicasde Excel.

Para ver esta diferencia en acción, dirígete a una celda en blanco de tu hoja de cálculo e introduce el signo igual. Señala las celdas A1:F1 como referencia y verás que la fórmula se convierte en Table1[#Headers].

También puedes utilizar esta referencia en otras funciones. Por ejemplo, puedes utilizar UPPER() para convertir dinámicamente el caso de todas las cabeceras, como como en la Figura 1-3.

Figura 1-3. Fórmulas de referencia de la cabecera de Excel

Ver los pies de tabla

Al igual que toda historia tiene un principio, un nudo y un desenlace, toda tabla de Excel consta de encabezados, datos y pies de página. Sin embargo, los pies de página deben activarse manualmente. Para ello, haz clic en cualquier lugar de la tabla, ve a Diseño de tabla en la cinta de opciones y selecciona Fila total en el grupo Opciones de estilo de tabla, como en la Figura 1-4.

Figura 1-4. Añadir pies de página a una tabla

Por defecto, la Fila Total de una tabla calculará la suma de la última columna de tus datos; en este caso, frozen. Sin embargo, puedes personalizarlo haciendo clic en el menú desplegable del pie de cualquier columna. Por ejemplo, puedes encontrar el importe máximo de ventas de la categoría fresh, como en la Figura 1-5.

Figura 1-5. Personalizar los pies de página de una tabla Excel

La Tabla 1-1 resume las referencias clave de las fórmulas de para los principales componentes de las tablas de Excel, suponiendo que la tabla se llama Table1.

Tabla 1-1. Resumen de referencias a fórmulas de tablas de Excel

Fórmula	A qué se refiere
=Table1[#Headers]	Encabezados de tabla
=Table1	Datos de la tabla
=Table1[#Totals]	Pies de tabla
=Table1[#All]	Encabezados, datos y pies de tabla

A medida que progreses en tus habilidades con las tablas de Excel, descubrirás otras útiles referencias a fórmulas que se basan en la estructura fundamental de los encabezados, el cuerpo de , y los pies de página.

Nombrar tablas de Excel

Las tablas de Excel ofrecen la ventaja de imponer el uso de rangos con nombre, lo que promueve un enfoque más estructurado del trabajo con datos. Aunque referirse a Table1 es una mejora respecto al uso de coordenadas de celda como A1:F22, es mejor elegir un nombre descriptivo que refleje lo que representan los datos.

Para ello, ve a la pestaña Fórmulas de la cinta de opciones, selecciona Gestor de nombres en el grupo Nombres definidos y elige Editar para el nombre Table1. Cambia el nombre a sales y haz clic en Aceptar. La Figura 1-6 muestra el aspecto que debería tener tu Gestor de nombres tras realizar este cambio.

Figura 1-6. Gestor de nombres en Excel

Cuando cierres el Administrador de nombres, verás que todas las referencias a Table1 se han actualizado automáticamente para reflejar el nuevo nombre: sales.

Formatear tablas de Excel

Como usuario de Excel, conoces la importancia de presentar los datos en un formato atractivo. Las tablas pueden cambiar las reglas del juego, mejorando instantáneamente el atractivo visual de tu hoja de cálculo. Con las tablas, puedes añadir fácilmente filas con bandas, encabezados de colores y mucho más. Para personalizar el aspecto de tu tabla, haz clic en cualquier lugar dentro de ella y dirígete a Diseño de tabla en la cinta. Echa un vistazo a la Figura 1-7 para ver varias opciones, como cambiar los colores de la tabla o activar y desactivar Filas con bandas.

Figura 1-7. Opciones de personalización del diseño de la tabla

Actualizar rangos de tablas

Con las tablas de Excel de, se resuelve eficazmente el problema de los totales que se vuelven incorrectos cuando se añaden o eliminan datos. Gracias al uso de las referencias estructuradas , las fórmulas se adaptan perfectamente a los cambios en los datos, garantizando la precisión. Además, el total de la parte inferior de la tabla se actualiza automáticamente para reflejar estos cambios, y puede excluirse fácilmente de las referencias externas, manteniendo la integridad de tuscálculos.

Calcula la suma de la columna fresh utilizando la fórmula estructurada =SUM(sales[fresh]). El IntelliSense de Microsoft facilita este proceso permitiéndote completar los nombres de forma eficaz mientras escribes. Experimenta añadiendo o eliminando filas, o modificando los datos de fresh en la tabla sales. Observarás que la función para calcular las ventas totales de fresh se actualiza dinámicamente y mantiene una precisión constante.

Referirse a los datos por su nombre en lugar de por la ubicación de la celda minimiza los posibles problemas de fórmulas derivados de cambiar el tamaño y la ubicación de la tabla. Las tablas también resultan cruciales para evitar problemas como la falta de datos en una Tabla dinámica cuando se añaden nuevas filas.

Organizar los datos para el análisis

Aunque las tablas de son valiosas, un aspecto aún más importante para garantizar un análisis de datos preciso y sin esfuerzo consiste en almacenar los datos con la forma adecuada.

Examina la tabla sales como ejemplo. Al intentar crear una tabla dinámica para calcular las ventas totales por región, el formato en el que se almacenan los datos supone un reto. Lo ideal sería consolidar toda la información de ventas en una sola columna. Sin embargo, en la configuración actual, hay una columna de ventas distinta para cada departamento: fresh, grocery, y frozen. Excel no reconoce que todas estas columnas representan la misma métrica, es decir, las ventas.

La razón por la que éste y muchos otros conjuntos de datos resultan difíciles de analizar es que no están almacenados en un formato propicio para el análisis. Las reglas de los datos ordenados ofrecen una solución. Aunque Hadley Wickham ofrece tres reglas en su artículo de 2014 del mismo nombre, este libro se centra en la primera: cada variable forma una columna.

El conjunto de datos sales viola la regla de los datos ordenados al tener varias entradas para la misma variable, field, en distintos departamentos dentro de cada fila. Una regla empírica útil es que si varias columnas miden lo mismo, es probable que los datos no estén ordenados. Al transformar los datos en un formato ordenado, el análisisse simplifica considerablemente.

En la Figura 1-8, puedes ver una comparación del conjunto de datos antes y después de la transformación, en la que se destaca la mejora de la ordenación y la facilidad de análisis. En el Capítulo 4, aprenderás a realizar esta transformación fundamental en un conjunto de datos con sólo unos clics. Mientras tanto, puedes explorar la hoja de cálculo sales-tidy disponible en ch01_solutions.xlsx, que ya ha sido transformada. Echa un vistazo para comprobar de primera mano lo mucho más sencillo que es obtener ahora las ventas totales por región.

Figura 1-8. Clientes mayoristas, antes y después de la limpieza

Conclusión

Este capítulo ha sentado las bases para utilizar eficazmente las tablas de Excel. Para profundizar en la maximización del potencial de las tablas, incluida la aplicación de referencias estructuradas para formular columnas calculadas, consulta Tablas de Excel: A Complete Guide for Creating, Using, and Automating Lists and Tables, de Zack Barresse y Kevin Jones (Holy Macro! Books, 2014). Además, este capítulo profundiza en la organización meticulosa de los datos, un aspecto fundamental para el éxito de cualquier proyecto de análisis de datos en Excel. El capítulo 2 ofrece una introducción a la simplificación de la transformación de datos con Power Query.

Ejercicios

Para crear, analizar y manipular datos en tablas de Excel, sigue los ejercicios utilizando el conjunto de datos penguins que se encuentra en ch_01_exercises.xlsx en la carpeta exercises\ch_01_exercises del repositorio complementario del libro:

1. Convierte los datos en una tabla llamada penguins.

2. Utiliza una referencia de fórmula para poner en mayúsculas cada encabezado de columna.

3. Genera una nueva columna llamada bill_ratio dividiendo bill_length_mm entre bill_depth_mm.

4. Incluye una fila de totales para calcular la media body_mass_g.

5. Retira de la tabla el estilo de fila con bandas.

Para las soluciones, consulta el archivo ch_01_exercise_solutions.xlsx que se encuentra en la misma carpeta .