¿Sabías que la base para crear una plantilla de Excel realmente útil es la estructura de los datos desde los que partes? Hoy hablaré del formato tabular en Excel
Más del 80% de usuarios de Excel no le prestan ninguna atención a este hecho. Comienzan a trabajar en sus datos pero no pueden llegar a ningún resultado porque la disposición de la información no se lo permite.
¿Qué significa esto? Simplemente que a la hora de trabajar con tablas dinámicas o gráficos les será mucho más difícil analizar y comprender la información... por no mencionar que necesitarán más tiempo para su creación.
El foro de Ayuda Excel está lleno de preguntas sobre tablas dinámicas que no de vuelven el resultado como ellos esperan o relacionadas con funciones del tipo SUMAR.SI que no son capaces de hacer que funcionen...El formato perfecto para trabajar con información en Excel es el tabular. El formato tabular se caracteriza principalmente porque tiene forma de tabla y en cada fila encontramos una operación. Fíjate en la imagen:
¿Qué ves aquí?
En este ejemplo cada fila representa un pedido (una transacción) y tiene los datos relacionados con ese pedido en una sola fila. Puede parecer algo obvio pero te aseguro que no lo es.
Cuando te saltas este paso y comienzas a trabajar con datos que ya contienen algún tipo de resumen, es mucho más dificil trabajar con ellos porque muchas veces es imposible usar las herramientas y funciones de la forma habitual.
Insisto, antes de ponerte a trabajar, prepara los datos en este formato.
¿Qué otras características tiene el formato tabular?
Además de contener un registro por fila, el este formato se caracteriza:
- Cada columna contiene el mismo tipo de datos y siempre el mismo. Por ejemplo fechas, números de pedido, cantidad, vendedor, etc.
- No contiene filas en blanco. Las filas en blanco no te impedirán trabajar, pero sí te lo pondrán más difícil, sobre todo cuando tengas que seleccionar la región de celdas actual o crear una fórmula.
- Todas las columnas contienen encabezados y deben estar en la misma fila y solo en una fila. No es necesario que estén en la parte superior del rango aunque sí es recomendable.
- Los datos no contienen subtotales o sumas parciales. Esos cálculos se harán a la hora de analizar los datos.
Si eres de los que trabaja así, ¡enhorabuena!. Como he comentado parecen obvias estas características o reglas del formato tabular pero para muchos usuarios de Excel no lo son. ¿Quieres ver algunas hojas con las que me he encontrado y que no siguen estos pasos? Te muestro algunos ejemplos y las razones de por qué no es una buena idea usarlos.
Ejemplo 1
Los datos de este ejemplo no son para nada adecuados para hacer un buen análisis de información por los siguientes motivos:
- Los encabezados de columnas se encuentran en dos filas. Esto dificultará la creación de fórmulas cuando tengas que hacer referencia a los datos que contienen.
- No sigue la regla de una transacción por fila. Como vemos en la fila 11. En esta fila se ve que Julio ha vendido 13 unidades en Ciudad Real y 19 en Albacete. Ya son dos operaciones.
- Contienen dos columnas con totales (una para las unidades y otra para los importes).
Con los datos en este formato no podrás crear tablas dinámicas porque no pueden contener columnas en blanco o encabezados de columna duplicados.
Ahora imagina que quieres calcular las unidades vendidas en el mes de enero, ¿cómo lo harías?.
Ejemplo 2
Este ejemplo es similar al anterior. Este no contiene columnas en blanco ni los encabezados en dos filas pero sigue teniendo celdas en blanco y los totales de unidades e importe.
Ejemplo 3
Este diseño de datos es el que veo más frecuentemente, el ideal para la persona que introduce la información, pero no para quien debe analizarla después.
Considerando estos datos, imagina que tu jefe te pide el importe de las ventas realizadas entre 2011 y 2020 (9 años = 108 meses) en la provincia de Albacete... ¿cómo te quedas?
Si piensas que con una fórmula lo podrías conseguir, tienes razón. Una fórmula matricial podría ayudarte a hallar lo que te piden, pero... ¿controlas las fórmulas matriciales?
Si hubieses colocado los datos en un formato tabular podrías utilizar una fórmula como la siguiente para obtener el resultado:
=SUMAR.SI(Tabla1[Provincia];"Albacete";Tabla1[Importe])
Las referencias de esta fórmula tienen este aspecto (son estructuradas) porque los datos de los que se han extraído tienen formato de tabla.
Las tablas de Excel tienen muchas ventajas, desde la introducción rápida de datos y fórmulas hasta la configuración automática de rangos dinámicos. Si no estás familiarizado con las tablas te recomiendo echar un vistazo al artículo.
Ejemplo 4
Este último ejemplo también es muy común. Se trata de separar datos con el mismo formato en diferentes hojas. Cada hoja puede contener a su vez cualquiera de los otros ejemplos. ¿Te imaginas la complicación de hacer cualquier cálculo? No digo que sea imposible pero no hay necesidad de complicarse tanto el trabajo.
Resumiendo...
Excel fue diseñado para trabajar con datos en formato de tabla. Las herramientas como las tablas dinámicas, los gráficos o las fórmulas funcionan mejor con formatos tabulares.
Créeme que merece la pena pasar algo de tiempo diseñando el formato de los datos que usaremos en nuestras hojas de cálculo.
En caso de que tengas que modificar los datos originales, la herramienta que mejor te ayudará a estas tareas, automatizando además el proceso, es Power Query.
