Cargando

Ir a contenido

X



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

Te explicamos cómo crear una tabla dinámica con varias tablas de Excel paso a paso, te explicamos cómo puedes hacer una tabla dinámica con varias tablas. Aprende cómo hacer una única tabla dinámica de Excel con varias tablas con la función Relaciones de Excel.


Escrito por ago 01 2022 08:05 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.

 

Si quisiéramos unificar esta información, antes de realizar una tabla dinámica, la función de “Relaciones de Excel” se presenta como una medida muy útil para poder relacionar estas dos tablas 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 tenga 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

 

Tutorial que explica los pasos para poder hacer una 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

 

Como-hacer-tablas-dinamicas-con-dos-tablas-1.png

 

Convertir datos en tablas de Excel antes de relacionar las tablas
  • Para poder activar la función “Relaciones” en Excel, convierte tus datos en una tabla oficial de Excel haciendo clic en el menú de “Insertar”, y haciendo clic en “Tabla”.
  • También puedes convertir tus datos en tabla de Excel usando este comando en el teclado: presionar teclas Ctrl + T

 

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.

 

Como-hacer-tablas-dinamicas-con-dos-tablas-2.png

 

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.

 

Como-hacer-tablas-dinamicas-con-dos-tablas-3.png

 

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

 

Como-hacer-tablas-dinamicas-con-dos-tablas-4.png

 

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

 

Como-hacer-tablas-dinamicas-con-dos-tablas-2.png

 

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…”

 

Como-hacer-tablas-dinamicas-con-dos-tablas-5.png

 

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

 

Como-hacer-tablas-dinamicas-con-dos-tablas-6.png

 

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 bien las Tablas.

 

Como vemos abajo en los desplegables de la izquierda, los referentes a Tablas son donde 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. Como te comentamos anteriormente no tienen por qué tener el mismo nombre en el encabezado, al seleccionarlos tú pueden llamarse de diferente forma.

 

Como-hacer-tablas-dinamicas-con-dos-tablas-7.png

 

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 que veremos después de hacer clic en Aceptar.

 

Como-hacer-tablas-dinamicas-con-dos-tablas-8.png

 


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. Y esto error surge cuando las columnas de las dos tablas tienen 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. 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.

 


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. Cómo te hemos explicado, este error es un error de duplicados que está afectando a las dos columnas de las dos tablas de Excel 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.

 

 

 

 

Como-hacer-tablas-dinamicas-con-dos-tablas-10.png

 

 

Ejemplo de mensaje de Error de duplicados al relacionar tablas de Excel
Para explicártelo con un caso práctico, este error de duplicidad de valores a la hora de relacionar dos tablas de Excel nos ha surgido cuando tratamos de crear la relación entre dos tablas. En una de las tablas contenemos mucha información general de ventas a nivel de vendedores. Por otro lado, en otra tabla de Excel tenemos información de las peticiones de presupuestos realizadas por cada vendedor. Asi podemos analizar no solo que vendedor ha realizado más ventas, si no evaluar a los vendedores según los presupuestos que ha realizado. Este error nos surge porque en las tablas de Excel, el nombre de los vendedores se repite. No es una duplicidad, como hemos explicado, debido a que los nombres de los vendedores se repiten en la misma columna porque esa fila muestra datos de periodos diferentes.

 

Para poder solucionarlo, como te hemos explicado, en una de las tablas de Excel hemos realizado una tabla dinámica para poder compilar todas las ventas de un mismo vendedor en una misma fila, para que no se repita. Con esta tabla dinámica, hemos pegado los datos en otra hoja, y convertido esta información otra vez en tabla de Excel para poder establecer la relación.

 


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”

 

Ahora en la ventana que se abre debemos tener seleccionada por defecto toda la tabla de Excel. Si hemos nombrado como te comentamos la Tabla de Excel, y te ha cogido el rango correctamente, verás que ya automáticamente Excel detecta toda la Tabla de Excel con el nombre de la tabla.

 

Como-hacer-tablas-dinamicas-con-dos-tablas-12.png

 

IMPORTANTE
Antes de hacer clic en “Aceptar”, selecciona como te indicamos en la imagen de abajo la casilla que ves debajo de la ventana y que dice “Agregar estos datos al modelo de datos”

 

Como-hacer-tablas-dinamicas-con-dos-tablas-13.png

 

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-hacer-tablas-dinamicas-con-dos-tablas-14.png

 

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).

 

Como-hacer-tablas-dinamicas-con-dos-tablas-15.png

 

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.


¿Te ayudó este Tutorial?


Sin comentarios, sé el primero!

No esperes más y entra en Solvetic
Deja tus comentarios y aprovecha las ventajas de la cuenta de usuario ¡Únete!


  Información

  •   Publicado ago 01 2022 08:05
  •   Actualizado ago 01 2022 09:11
  •   Visitas 604
  •   Nivel
    Avanzado


X