Cómo hacer una Tabla Dinámica con varias hojas de Excel

A la hora de hacer una tabla dinámica, nos pueden surgir interrogantes o dudas a cerca de si podemos hacer o no algunas tareas. Muchas de estas tareas son de una forma u otra posibles con las tablas dinámicas, y las funciones y posibilidades que Excel habilita cuando trabajamos con Pívot Tables.

Una de las dudas que surge con mayor frecuencia, una de esas preguntas que muchos usuarios se hacen cuando trabajan en Excel es saber si es posible o no realizar una tabla dinámica con varias hojas. ¿Es posible realizar una tabla dinámica con dos hojas?, ¿Podemos hacer una tabla dinámica con varias hojas? La respuesta es sí, puedes realizar tablas dinámicas desde varias tablas diferentes.

Esta opción se llama “relaciones” y es una función de Excel que te permite realizar una relación entre dos tablas de Excel, una función especialmente útil cuando queremos realizar una tabla dinámica y no tenemos toda la información unida en la misma tabla.

La función de relaciones en Excel, cómo su propio nombre indica, te permite establecer una relación entre dos tablas, de tal forma que, identificando un valor único en las dos tablas, puedes realizar un cruce de datos.

Imagina que los datos de ventas de una empresa estuvieran en dos bases de datos diferentes:

• En una base datos tienes la información del pedido, del valor de las ventas y el lugar dónde se ha realizado.

• En otra base de datos tenemos información de los pedidos asociados al vendedor que ha realizado cada venta.

• En las dos tablas tenemos un campo con el identificador de la venta

Si quisiéramos unificar esta información (información de ventas de cada vendedor, con la información de qué producto ha vendido, a qué precio y dónde se ha realizado la venta), podríamos hacerlo gracias a la función de relaciones de Excel. Podemos construir una única tabla con la información de las dos.

Antes de realizar una tabla dinámica, la función de “Relaciones de Excel” se presenta como una medida muy útil para poder relacionar dos bases de datos a partir de un campo común que en este caso sería el identificador de la venta. Y podríamos construir una única tabla con la información de las dos bases de datos.

1 Requisitos para poder relacionar tablas dinámicas

Para poder relacionar tablas dinámicas debes revisar tus datos primero a fin de poder cerciorarte que el cruce que vas a realizar de información es válido:

• Revisa primero que tus hojas de Excel contienen toda la información necesaria.

• Revisa que la información de las tablas o hojas de Excel tengan encabezados (como veremos a continuación, no es necesario que las dos columnas de las dos tablas que vas a relacionar tengan el mismo encabezado).

• Revisa que no hay símbolos o errores en las tablas.

• Revisa que no haya celdas vacías y si las hubiera, que el valor deseado para estas celdas sea 0.

• Necesitamos un campo en común para poder establecer esa conexión entre dos tablas.

• Es muy importante que, a la hora de realizar el cruce, en el campo en común los nombres, identificadores, o valores se llamen igual en las dos tablas. De lo contrario el cruce posteriormente no se realizará.

• Los datos no deben contener ni totales ni subtotales

• Cómo ya te hemos enseñado en cómo hacer una tabla oficial de Excel, es necesario que los datos que quieres relacionar para posteriormente hacer la tabla dinámica, los hayas convertido en Tabla oficial de Excel

2 Pasos para poder relacionar dos tablas

Cómo te comentábamos en el punto anterior, uno de los requisitos fundamentales “sine qua non” para poder relacionar dos tablas es convertir nuestros datos en tabla oficial de Excel. Cómo te enseñaremos a continuación, para que se active la función de “Relaciones”, debemos realizar este paso.

Cómo puedes ver en la imagen de abajo, abrimos una hoja de Excel con datos y nos vamos a la pestaña de “Datos” en el menú de Excel. Vamos a ver cómo la función de “Relaciones” se encuentra deshabilitada. ¿Por qué? Porque para poder usar la función de relacionar en Excel como te hemos comentado debes convertir tus datos en Tablas oficiales de Excel

Es importante que tengas en cuenta que, para poder establecer la relación entre dos tablas, debes convertir en tabla de Excel las dos tablas. Así Excel las detectará automáticamente, como veremos más tarde, al establecer la relación entre ambas.

Una vez convertidos nuestros datos en tabla oficial Excel, veremos como ahora si la función “Relaciones” está habilitada en el menú de datos.

Cómo ya hemos convertido los datos en Tablas de Excel, la función “Relaciones”, como acabamos de ver, está activa. Ya tienes que haber convertido las dos tablas en Tablas oficiales de Excel.

Primero, aunque no es un paso necesario pero si útil, nosotros hemos renombrado tanto la hoja de Excel como las tablas de Excel, para poder identificar mejor cada tabla en pasos posteriores. Para cambiar el nombre a la Tabla de Excel solo debes posicionarte en cualquier celda de la tabla, y en el menú ir a “Diseño Tabla”, donde veremos un campo que dice “Nombre de la tabla”, situado en el lado izquierdo, debajo del menú, como en la imagen.

Para cambiar el nombre de la hoja de Excel, simplemente haces doble clic en la pestaña, y ponemos el nombre que queramos. Nosotros usamos siempre el mismo nombre de la Tabla en la pestaña, así en todo momento sabemos dónde está la información.

Renombradas ya las tablas de Excel creadas, cómo te avanzamos en el punto anterior, debes abrir la hoja de Excel donde tengas una de las dos tablas de Excel, dirígete al menú superior de Excel, y hacemos clic en “Datos” y después en la opción de “Relaciones

Una vez hacemos clic en “Relaciones”, vamos a ver una ventana nueva que se abre desde donde vamos a administrar la relación de tablas. En este caso como no hemos creado aún en esta hoja una relación de tablas, veremos solo habilitada la opción de crear una relación nueva. Hacemos clic por tanto en el botón que dice “Nuevo…”

Después de hacer clic en “Nuevo…” vamos a crear ya la relación entre las tablas desde la ventana que “Crear Relación” que se acaba de abrir

En esta ventana es donde establecemos la relación, donde le decimos a Excel que dos tablas queremos relacionar y qué campo vamos a usar para relacionar ambas tablas. Cómo hemos cambiado los nombres de las Tablas de Excel previamente, ahora podemos identificar mejor las Tablas.

Como vemos abajo en los desplegables de la izquierda, en Tablas elegimos las dos tablas de Excel. Y en los desplegables de columnas, los de la derecha, tenemos que elegir el campo común entre ambas tablas para que los datos se puedan cruzar. Los encabezados recuerda que no tienen por qué tener el mismo nombre, al seleccionarlos tú pueden llamarse de diferente forma.

Una vez seleccionados los campos, tenemos que hacer clic en “Aceptar”. Ya tenemos la relación creada entre ambas tablas, como podemos ver en la siguiente ventana.

“3 Las dos columnas seleccionadas contienen valores duplicados. Para crear una relación entre las tablas, por lo menos una de las dos columnas seleccionadas debe contener valores únicos solamente”

Este es un error típico que puede surgir cuando intentas crear la relación entre dos tablas de Excel. ¿Por qué surge este error? Este mensaje de error lo vemos cuando el campo de datos común que estás usando para establecer la relación, tiene duplicados. A Excel le resulta imposible establecer esa unión entre dos tablas cuando el campo común tiene elementos duplicados. Al menos uno de las dos columnas debe contener valores únicos (sin repetirse).

Este error es frecuente cuando trabajamos con tablas con muchos datos, donde por ejemplo se puede repetir el valor de una celda en una misma columna, no porque esté mal, sino porque puede referirse por ejemplo a periodos diferentes. A continuación te vamos a explicar cómo solucionarlo.

4. Solucionar error Las dos columnas seleccionadas contienen valores duplicados

Aunque no te haya surgido este error a la hora de relacionar tablas de Excel, es importante que sepas cómo solucionarlo porque en otro momento podría surgirte este mismo problema. Este error es un error de duplicados que está afectando a la columna que estás usando como campo común. Para solucionarlo, puedes eliminar los duplicados en Excel, aunque debes tener cuidado porque como ya te hemos explicado puede que esta “duplicidad” esté bien contemplada en tu tabla de Excel, que en otras columnas se esté especificando que no es un valor duplicado, como te decíamos, por ejemplo, al tratarse de periodos de tiempo diferentes.

Si es un valor duplicado, que se ha colado en tus tablas de Excel puedes usar las funciones de Excel para eliminar duplicados.

Si no se trata como te decíamos de un valor duplicado, entonces no elimines duplicados. Para solucionar entonces el error de valores suplicados, puedes realizar una tabla dinámica de una de las tablas de Excel para poder unificar todos los valores de una columna.

• En “Filas” pondríamos la columna donde tenemos los duplicados, y añadiríamos y crearíamos la tabla dinámica con el resto de la información que necesitemos.

• Al realizar la tabla dinámica, y poner como estamos explicando la columna en filas, se creará una tabla dinámica unificando todos los valores duplicados de esa columna.

5. Combinar datos de dos tablas Excel

Para poder hacer una tabla dinámica desde varias hojas, con varias tablas, hemos dado ya el paso importante que es lo que hemos explicado en los puntos anteriores. Para poder hacer una tabla dinámica debes haber completado los pasos anteriores, y establecer la relación entre dos tablas dinámicas.

Recuerda que, si ves un error a la hora de realizar la relación de tablas dinámicas, debes revisar tus tablas de Excel, y comprobar que las tablas de Excel se han realizado correctamente, y que no hay duplicados en el campo común, en las dos tablas.

Si hemos creado la relación entre ambas tablas de Excel de forma satisfactoria, ahora ya vamos a poder crear directamente la Tabla dinámica. Puedes crear la tabla dinámica desde cualquiera de las dos tablas de Excel, pues al estar conectadas, como vamos a ver ahora, vas a poder usar las columnas de las dos tablas de Excel para construir una única tabla dinámica que contenga toda la información.

Para poder hacer una tabla dinámica o Pívot table de varias tablas nos dirigimos a una de las hojas de Excel donde se encuentre una tabla de Excel. Cómo te decíamos, puedes hacer la tabla dinámica desde una u otra tabla que al estar ya relacionadas no será problema para crear una tabla dinámica única.

Cómo ya sabemos hacer, nos dirigimos al menú de Excel desde la hoja donde esté una de las tablas de Excel y nos posicionamos con el ratón o con el teclado en cualquier celda de la tabla.

• Hacemos clic en el menú de Excel en “Insertar”
• Después hacemos clic en “Tablas dinámicas”
• Después hacemos clic en “de una tabla o rango de dato”

Hacemos clic en “Aceptar” y ya podemos configurar la tabla dinámica. En el menú que ves a la derecha “Campos de Tabla dinámica” desde donde configuras habitualmente la tabla dinámica. Verás que justo debajo del nombre del menú “Campos de tabla dinámica” se habilita una pestaña que dice “Todas”. Hacemos clic en “todas”.

Como vemos en la imagen, ya tenemos la información disponible (las columnas de las dos tablas de Excel disponibles) para poder hacer nuestra tabla dinámica con la información de las dos tablas. Nosotros hemos construido la tabla dinámica con el ejemplo que te explicábamos, con la información de ventas y petición de presupuesto a nivel de vendedor. Las ventas y la petición de presupuesto es información que se encontraba en tablas diferentes. Sin embargo, realizando la relación de ambas tablas, usando como nexo o campo común el nombre del vendedor, hemos conseguido realizar una única tabla dinámica, con toda la información requerida (ventas y peticiones de presupuesto realizadas).

Te hemos explicado todos los pasos a realizar cuando hacemos tablas dinámicas desde varias hojas de Excel, así cómo algunos puntos importantes clave cuando relacionaos tablas oficiales de Excel:

• De igual modo, acostumbrarse a realizar tablas de Excel, Tablas de Excel oficiales, es importantísimo. Cómo ya has visto, si no realizas este paso, te resultará imposible establecer la relación de dos tablas, y por tanto no podrás realizar después la tabla dinámica con campos de dos tablas diferentes.

• Cuando trabajas con tablas de Excel, no siempre se le da importancia a detalles que no siendo necesarios, si son importantes en tanto te ayudan en tareas de organización. En este caso, nos referimos a la posibilidad de re-nombrar las tablas. Es algo que te ayudará a realizar con facilidad diferentes tareas y opciones cuando trabajamos con tablas dinámicas.

• También has podido ver cómo solucionar errores que pueden surgir a la hora de crear la relación si existen elementos duplicados en ambas tablas dinámicas. Un punto importante que no siempre se explica, y que sin embargo es un error típico, especialmente a la hora de tratar con bases de datos de gran volumen.

• Por último, te hemos explicado cómo hacer las tablas dinámicas combinando información de dos tablas de Excel. Algo sencillo de hacer si has completado los pasos anteriores con éxito, usando la función de relaciones de Excel.