Cómo crear una tabla dinámica a partir de varias tablas (manera fácil)

Cuando la mayoría de las personas usan tablas dinámicas, copian los datos de origen en una hoja de trabajo y luego realizan muchas BUSCARV para obtener las columnas de categorización en el conjunto de datos. Después de eso, los datos están listos, podemos crear una tabla dinámica y el análisis puede comenzar. Pero ya no necesitamos hacer todas esas BUSCARV. En su lugar, podemos crear una tabla dinámica a partir de varias tablas. Al crear relaciones entre tablas, podemos combinar varias tablas, lo que crea automáticamente las búsquedas por nosotros.

La capacidad de crear relaciones está disponible de forma nativa en Excel desde 2013, pero la mayoría de los usuarios ni siquiera saben que existe esta función.

Tampoco necesitamos copiar y pegar datos en una hoja de trabajo, ya que ahora podemos usar Power Query para importar los datos directamente. Consulte mi serie Power Query para comprender cómo hacer esto. Pero, para esta publicación, nos centraremos en crear relaciones y cómo combinar dos tablas dinámicas.

Tabla de contenido

• El escenario
• Crear tablas
• Creando relaciones
• Crear la tabla dinámica
• Actualizar una tabla dinámica desde varias tablas
• Detección automática de relaciones
• Valores duplicados en tablas de búsqueda
• Pivote de poder
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre de archivo: 0040 Combinando tablas en una tabla dinámica.zip

Tener acceso

El escenario

En nuestro archivo de ejemplo, tenemos tres secciones de datos:

• Los datos de ventas
• datos del representante de ventas
• Datos del producto

Estos conjuntos de datos podrían estar en hojas de trabajo separadas, pero para facilitar la demostración, se incluyen en una sola.

Los datos de ventas contienen la información de la transacción, que a menudo se denomina tabla de hechos . Los datos del representante de ventas y los datos del producto incluyen la categorización para analizar las transacciones; A menudo se las denomina tablas de búsqueda .

Nuestro objetivo es crear una tabla dinámica que muestre las ventas de productos por sucursal. Esto requiere información de las tres tablas de datos:

• El producto está en la tabla de datos del producto.
• La sucursal está en la tabla de datos del representante de ventas.
• El valor está en la tabla de datos de ventas.

Para lograrlo, crearemos relaciones para combinar tablas dinámicas. ¡No usaremos una sola fórmula!

Crear tablas

Primero, necesitamos convertir nuestros datos en tablas de Excel. Esto coloca nuestros datos en un contenedor para que Excel sepa que están en un formato estructurado que puede usarse para crear relaciones.

Seleccione cualquier celda dentro del primer bloque de datos y haga clic en Insertar tabla (o presione Ctrl + T ).

Se abre el cuadro de diálogo Crear tabla. Verifique que el rango incluya todos los datos y asegúrese de que mis datos tengan encabezados esté marcado. Luego haga clic en Aceptar.

Los datos cambian a un formato de rayas. Este es un indicador visual de que se ha creado una tabla de Excel.

CONSEJO PRINCIPAL: No es necesario que te ciñas a ese formato. Haga clic en cualquier celda de la tabla, luego haga clic en Diseño de tabla y elija otro formato entre los disponibles.

A continuación, debemos darle a nuestra tabla un nombre significativo. Con cualquier celda de la tabla seleccionada, haga clic en Diseño de tabla e ingrese un nuevo nombre en el cuadro Nombre de la tabla. Elegí SalesData (los espacios y la mayoría de los caracteres especiales no están permitidos dentro de los nombres de las tablas).

Repita los pasos anteriores para los otros conjuntos de datos para crear tablas llamadas SalesRepData y ProductData .

Creando relaciones

Con nuestras tres tablas creadas, ahora es el momento de comenzar a crear las relaciones. Haga clic en Relaciones de datos .

Se abre el cuadro de diálogo Administrar relaciones. Haga clic en Nuevo .

Se abre el cuadro de diálogo Crear relación . Aquí es donde definimos las relaciones que existen.

No creo que las descripciones de este cuadro de diálogo sean particularmente claras, así que intentaré orientarte un poco.

• Tabla: Esta es la tabla que contiene los valores transaccionales que queremos analizar (la tabla de hechos).
• Columna (Extranjera): este es el nombre de la columna de la tabla de valores transaccionales desde la que queremos realizar la búsqueda. Si tiene la mentalidad BUSCARV, entonces esta sería la columna que contiene el argumento valor_buscado. La palabra Extranjera es terminología de base de datos para indicar que esta columna puede tener valores duplicados.
• Tabla relacionada: esta es la tabla que contiene las categorías por las que queremos analizar los datos transaccionales (la tabla de búsqueda).
• Columna relacionada (principal): esta es la columna que queremos emparejar con la columna (externa) que seleccionamos anteriormente. Si se tratara de una BUSCARV, sería la primera columna en el argumento table_array. La palabra Primaria es terminología de base de datos nuevamente; nos dice que la columna debe contener valores únicos.

No es necesario que las columnas compartan un nombre de encabezado común para que esta técnica funcione. Sin embargo, puede resultar útil recordar cómo se relacionan las tablas.

En nuestro ejemplo, utilizamos lo siguiente:

• Tabla: Datos de ventas
• Columna (extranjera) : ID del representante de ventas
• Tabla relacionada : SalesRepData
• Columna relacionada (principal) : ID del representante de ventas

Haga clic en Aceptar para crear la relación.

¡Eso es todo, simple verdad! Acabamos de combinar dos tablas sin fórmulas.

A continuación, creemos la relación entre las tablas SalesData y ProductData utilizando el mismo proceso anterior.

• Tabla: Datos de ventas
• Columna (extranjera) : ID del producto
• Tabla relacionada : Datos del producto
• Columna relacionada (principal) : ID del producto

Una vez creadas todas las relaciones, haga clic en Cerrar .

Crear la tabla dinámica

Todo está en su lugar, por lo que ahora estamos listos para crear la tabla dinámica.

Haga clic en Insertar tabla dinámica en la cinta.

Se abre la ventana Crear tabla dinámica . Lo más importante es que la opción Usar el modelo de datos de este libro está seleccionada.

Seleccione una ubicación para crear la tabla dinámica. Para este ejemplo, crearemos la tabla dinámica en la misma hoja de trabajo que los datos. Seleccioné la Hoja de trabajo existente en la celda Hoja1, Celda G10 , pero puedes colocar tu tabla dinámica donde quieras.

Haga clic en Aceptar para cerrar el cuadro de diálogo Crear tabla dinámica.

Se crea la tabla dinámica. Observe que la ventana Campos de la tabla dinámica incluye las tres tablas. Ahora podemos comenzar a arrastrar campos de cada tabla para formar una vista única.

Como dije anteriormente, el objetivo es mostrar las ventas de productos por sucursal. Para lograrlo, coloca los campos en las siguientes secciones:

• Columnas: Rama SalesRepData
• Filas: ProductoProductData
• Valores: SalesData Suma de valor

Si no ve todas las tablas en la vista Campos de tabla dinámica, cambie la selección de Activo a Todo .

Esto crea la siguiente tabla dinámica:

Ahí lo tienes. Hemos creado una tabla dinámica a partir de varias tablas sin fórmulas.

Actualizar una tabla dinámica desde varias tablas

Ya sea que los datos provengan de una sola tabla o de varias tablas, el proceso de actualización es el mismo. Haga clic en Actualizar datos todos en la cinta.

Sin embargo, existen opciones avanzadas que podemos utilizar, que no están disponibles para las tablas dinámicas estándar:

• Haga clic en Conexiones de consultas de datos para ver todas las conexiones en el libro.
• Haga clic derecho en un elemento de la lista de Conexiones y seleccione Propiedades en el menú.
• Se abre el cuadro de diálogo Propiedades de conexión .

Si bien muchas opciones aquí están atenuadas, hay opciones para:

• Actualizar cada x minutos
• Actualizar datos al abrir el archivo
• Actualizar esta conexión en Actualizar todo

Son especialmente útiles si se trabaja con conexiones de datos externas.

Detección automática de relaciones

Al trabajar con relaciones, puede encontrar oportunidades para detectar automáticamente relaciones. Por ejemplo, Excel puede mostrar el siguiente mensaje.

“Pueden ser necesarias relaciones entre tablas“

NO haga clic en el botón Detección automática. Si lo hace, Excel intentará utilizar su propia lógica para construir relaciones. En un escenario simple, funcionará, pero Excel a menudo se equivoca. Si es necesario, regrese al cuadro de diálogo Relaciones y verifique que todo esté configurado y calculando correctamente. Si funciona correctamente, ignore el mensaje de advertencia.

Valores duplicados en tablas de búsqueda

Si hay valores duplicados en nuestras tablas de búsqueda (o tablas relacionadas como se llaman en la ventana Relaciones), esto provoca un error y las relaciones no funcionarán.

Cuando usamos funciones de búsqueda como BUSCARV, BUSCAR XL o ÍNDICE/COINCIDIR, Excel siempre devuelve el primer elemento, incluso si no es la respuesta que queremos. Pero cuando usamos relaciones, si hay más de un valor coincidente, Excel no lo hace. No sé qué artículo usar. Esto causa el error. Por lo tanto, para garantizar la integridad de nuestros datos, debemos tener valores únicos en nuestras tablas de búsqueda.

Crear una relación con valores duplicados

Al crear la relación, si hay valores duplicados en nuestra tabla de búsqueda, obtendremos el siguiente error:

“Ambas columnas seleccionadas contienen valores duplicados. Al menos una de las columnas seleccionadas debe contener solo valores únicos para crear una relación entre las tablas".

Debemos regresar y arreglar nuestra tabla fuente antes de crear la relación.

¿Agregar valores duplicados en una tabla?

Si accidentalmente agregamos valores duplicados a nuestras tablas de búsqueda, obtenemos un error al actualizar la tabla dinámica.

No pudimos actualizar la conexión '__________'. Este es el mensaje de error que recibimos:
La columna '__________' en la tabla '__________' contiene un valor duplicado '__________' y esto no está permitido para la columna en un lado de una relación de muchos tonos para las columnas que se utilizan como clave principal. de una mesa.

Debemos regresar y corregir los datos en la tabla de búsqueda y luego actualizar nuevamente.

Pivote de poder

Probablemente no se dio cuenta, pero acaba de utilizar el motor Power Pivot en Excel. No abrimos ni habilitamos el complemento Power Pivot; todo lo que hemos usado es la interfaz estándar de Excel. Sin embargo, si abriéramos la herramienta Power Pivot, veríamos que nuestros datos y relaciones existen.

Conclusión

En esta publicación, creamos una tabla dinámica a partir de varias tablas sin fórmulas, algo que no era posible antes de Excel 2013.

Si comprende cómo funcionan estas relaciones, tal vez sea hora de investigar un poco más sobre Power Pivot. Luego podrá obtener aún más beneficios de automatización y crear informes aún más avanzados.

---