Antes de explicar el procedimiento y los pasos a la hora de hacer una tabla dinámica, es importante destacar que las tablas dinámicas funcionan gracias a unos datos de origen. Desde estos datos de origen o base de datos, las tablas dinámicas recogen la información para luego poder agrupar y representar los datos según la organización deseada. La organización de la información recogida en la base de datos es un aspecto fundamental, y que debes tener muy presente para que la información de la tabla dinámica sea la correcta.
Es importante por tanto que sepas primero cómo preparar los datos de origen para realizar una tabla dinámica en Excel para evitar errores y falta de consistencia en los datos que reflejará tu tabla dinámica.
Emplear tiempo en revisar los datos de origen debe tomarse como un paso necesario e importante; Emplear tiempo en revisar la estructura de los datos, y confirmar que todo es correcto, te ahorrará tiempo y disgustos.
- Confirma que no hay filas en blanco, que los datos van seguidos sin saltos hasta el final de la tabla.
- Revisa la estandarización de los datos. Especialmente si quieres hacer agrupaciones de datos, que un mismo concepto se escribe de una única forma. De lo contrario no se agrupará todo bajo un mismo concepto.
- Elimina espacios en blanco en las celdas con texto (antes y después del texto)
- Revisa errores de Excel en las celdas, si los hubiera
- Comprueba que no se incluyen totales ni subtotales en la tabla
Puedes ver en la imagen de abajo los principales puntos que debes revisar en la base de datos antes de realizar la tabla dinámica. La clave como hemos comentado es tener una tabla bien organizada, sin errores y con una buena estructura.
Los beneficios de crear tablas dinámicas, si aún no los conoces, son de gran valor pues te permiten ahorrar tiempo en muchas tareas de cálculo siendo las tablas dinámicas una herramienta muy útil para poder hacer un análisis de datos personalizado. Saber más de qué son las tablas dinámicas, así como sus características y conocer en qué momento te resultarán de gran utilidad te ayudará a entender la utilidad de esta herramienta tan potente. Te ayudará a exprimir sus características y capacidades en tareas de síntesis y análisis de datos, así como herramienta de reporting y base para poder crear informes avanzados con un grado de automatización profesional.
Una vez empieces a usar las tablas dinámicas de Excel, te resultará imprescindible para realizar con éxito y en poco tiempo algunas tareas de tu trabajo. Especialmente si el análisis de "macro datos" está en tu día a día. Una vez tus datos estén bien organizados y revisados, sigue estos sencillos pasos para poder hacer tus tablas dinámicas de forma sencilla.
1 Convertir datos en tabla de Excel
Cómo ya os contamos en Cómo hacer una tabla oficial de Excel, es muy importante que antes de realizar la tabla dinámica conviertas tu base de datos en una tabla oficial de Excel. Son muchos los beneficios de hacer este sencillo paso, el más importante es que te olvidarás de tener que actualizar el rango de datos de la tabla dinámica cuando introduzcas nuevos registros en la base de datos. Ya no será necesario actualizar el rango de datos porque Excel reconoce las tablas de Excel perfectamente y tendrá en cuenta cualquier actualización que realices en la base de datos, tanto si has añadido como borrado registros.
Insistimos en la importancia de convertir tus datos en tabla de Excel porque es sin duda uno de los pilares para asegurar la consistencia de los datos que muestre tu tabla dinámica, evitas cometer errores cómo olvidarse de actualizar el rango de la tabla, y porque además te posibilitará el realizar algunas funciones en la tabla dinámica que te explicaremos más adelante.
Para hacer una tabla oficial de Excel abre la hoja de Excel dónde están los datos. Una vez abierta la hoja de Excel con los datos debes hacer un solo clic en cualquier celda donde están tus datos, y hacer clic en “Insertar” en el menú de Excel. Después, hacer clic en Tabla. (Mira la imagen abajo)
Al hacer clic en “Tabla” verás que automáticamente ya te ha seleccionado todo el rango de datos de la tabla. Si ves que no está seleccionado el rango de datos, o que no está completo el rango puedes seleccionar tú el rango haciendo uso del ratón o del teclado.
Ahora haces clic en aceptar, verás que tus datos ya se han convertido en una tabla de Excel. Te recomendamos que cambies el nombre, como te indicamos en la imagen de abajo, en “Nombre de la tabla”.
Tanto si usas el ratón, como el teclado para seleccionar el rango de datos, asegúrate de seleccionar hasta la última columna y hasta la última fila. No importa que en la selección haya columnas que no quieras mostrar en la tabla dinámica. Esto es algo que podrás elegir posteriormente desde el menú de las tablas dinámicas, desde donde podrás elegir que información quieres mostrar. Es recomendable seleccionar toda la tabla entera, cómo vemos abajo en la imagen.
2 Insertar Tabla Dinámica de Excel
Una vez tengas tu tabla de Excel creada, o el rango de datos seleccionados, vamos al menú de Excel situado en la parte de arriba y hacemos clic en “Insertar”. Después elegimos “de una tabla o rango”.
3 Crear Tabla de Excel en una hoja nueva y aceptar
En la ventana que se abre, verás que el rango viene ya definido porque hemos seleccionado la tabla previamente. Y si has creado la Tabla de Excel, como te hemos recomendado, verás que aparece el nombre de la tabla de Excel. Si has cambiado el nombre como te hemos recomendado también, verás el nombre de tu tabla de Excel.
Por defecto, elegimos la opción que verás marcada “nueva hoja de cálculo”. Así tu tabla dinámica se habrá creado automáticamente en una hoja nueva en el Excel donde están los datos de origen. Damos al botón de Aceptar.
4 Configurar Tabla con menú campo de Datos
Ahora ya puedes ver el menú de campo de datos desde dónde podrás construir tu tabla dinámica. Podrás ver que se ha creado como te decíamos en el paso anterior en una hoja nueva.
En el menú de la derecha que dice “Campos de Tabla dinámica” es ahora el menú de configuración de tu tabla dinámica desde donde podrás configurar tu tabla dinámica. Ahora es posible seleccionar solos los datos que tú quieras enseñar en la tabla. Nosotros en el ejemplo abajo seleccionamos todos los datos
5 Mover los campos de valor a las áreas
Tal y cómo vemos en el ejemplo, una vez seleccionados todos los campos, verás que automáticamente se crea una tabla poco útil para poder analizarla. Ahora nos toca por tanto dar forma a la tabla dinámica, algo sencillo e intuitivo que puedes hacer moviendo los campos a las áreas.
Verás que es fácil, y solo con hacer clic en uno de los campos y manteniendo el clic lo puedes arrastrar a otra área. Nosotros a raíz del ejemplo, creamos una tabla que tendría sentido para analizar los resultados año por año.
- Movemos el campo de fechas al área de filtros.
- En el área de columnas, que estén los valores.
- En el área de filas, poner en el mismo orden empresas, delegaciones y vendedores (Es importante el orden pues verás que estás jerarquizando estos datos en la tabla.
- En el área de valores, nosotros tenemos los campos “suma de ventas” y “suma de valor de ventas".
De esta forma podremos ver los resultados tanto en número de ventas cómo valor de las ventas, podríamos ver las ventas y el valor de las ventas de las empresas, de las delegaciones y también por vendedor.
Y desde arriba, tal y cómo vemos en la imagen de abajo podríamos ver estos datos de ventas y valor de las ventas para todo el periodo, o podemos seleccionar una fecha desde la propia tabla.
También podemos activar el menú de campos de tabla dinámica desde el menú de Excel, haciendo clic en "Analizar Tabla dinámica" y después seleccionando como en la imagen abajo la opción "Lista de Campos"
6 Quitar y poner Campos de valor en la Tabla
Si queremos quitar algún campo de la tabla para que no aparezca en la tabla, solo tenemos que ir al menú de la derecha “Campos de tabla dinámica” y quitar el check al campo que no queremos mostrar. Si por ejemplo no quisiéramos desglosar los datos por vendedores, “descliquemos” vendedores en el menú de la derecha “Campos de tabla dinámica”. Cómo vemos más abajo la tabla se ha actualizado sola y esta información a nivel de vendedor ya no se muestra.
7 Cambiar los Campos de valor de una Tabla Dinámica
Por defecto, los campos de valores que estén en el área “valores” se suman. Si no quieres mostrar este dato, si no quieres quisieras ver por ejemplo un promedio de ventas, solo tienes que ir al área de valores, hacer clic en el campo que dice “suma de ventas”. Después en el menú que se despliega elegimos “configurar campo de valores”.
Desde la ventana que se abre elegiríamos promedio, y clic en botón de aceptar. Verás que entonces los valores del campo de ventas se modificaron en la tabla directamente y en lugar de mostrar número de ventas, estaría mostrando promedio de ventas.
8 Crear varias Tablas Dinámicas desde el mismo origen de datos
También podríamos querer realizar varias tablas que hagan referencia al mismo origen de los datos. Para hacer esto solo tendríamos que repetir la operación, dirigirnos a la hoja del documento de Excel donde estén los datos de origen o base de datos, seleccionarlos y realizar una tabla nueva. O podríamos también copiar la tabla dinámica que ya hemos hechos, seleccionándola y pegándola en otra hoja. (O copiar directamente la hoja donde está la tabla dinámica crenado una hoja nueva con la tabla dinámica). Una vez hecho esto ya sabes que haciendo clic en la tabla dinámica aparece el menú “Campos de tabla dinámica” desde el que podremos configurar la tabla con la información que necesites.
