Unlimited WordPress themes, graphics, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Computer Skills
  2. Microsoft Excel

Tablas dinámicas avanzadas: combinar datos de varias hojas

by
Difficulty:AdvancedLength:LongLanguages:

Spanish (Español) translation by Elías Nicolás (you can also view the original English article)

Cuando desea crear una tabla dinámica, ¿qué hace si tiene datos en diferentes hojas de cálculo? Si utiliza Excel 2013, tiene un método optimizado para hacerlo. Hay una técnica llamada Modelo de Datos, que utiliza las relaciones de datos como lo hace una base de datos.

En este tutorial, le mostraré todo lo que necesita para hacer una tabla dinámica en Excel 2013 a partir de datos en varias hojas, utilizando el modelo de datos.

Screencast

Si desea seguir este tutorial con su propio archivo de Excel, puede hacerlo. O si lo prefiere, descargue el archivo zip incluido para este tutorial, que contiene un libro de ejemplo denominado Pivot Consolidate.xlsx.

Examen de los datos

Este libro tiene tres hojas de trabajo: información de cliente, información de pedido e información de pago.

Haga clic en la hoja Customer Info y compruebe que contiene los números de pedido y el nombre y estado de los clientes.

Customer Info sheet
Hoja de información del cliente

Haga clic en la hoja Order Info y compruebe que también contiene números de pedido, así como los campos del mes, los productos pedidos y si los productos son orgánicos.

Order Info sheet
Hoja de información de pedido

Haga clic en la hoja Payment Info y compruebe que contiene los números de pedido, el monto en dólares de cada venta, el método de pago y si el pedido fue colocado por un cliente nuevo o existente.

Payment Info sheet
Hoja de información de pago

Al conectar todas estas hojas dentro del panel de tareas de la tabla dinámica, podemos seleccionar datos de cada una de las hojas. Puesto que los números de pedido existen en las tres hojas, se convertirán en los puntos de conexión. Esto es lo que una base de datos llama a una clave primaria. Tenga en cuenta que no siempre es necesario tener una clave primaria, pero reduce la posibilidad de error.

Crear tablas con nombre

Antes de crear la tabla dinámica, creemos la tabla de cada una de las hojas.

Haga clic de nuevo en la Customer Table y, a continuación, haga clic en cualquier lugar dentro del área de datos. Vaya a la pestaña Insertar de la barra de la cinta de opciones y, a continuación, haga clic en el icono Tabla.

creating a table from existing data
Convertir datos en una hoja de cálculo seleccionando Insertar > Tabla

El cuadro de diálogo Crear tabla identifica correctamente el área de la tabla. La casilla de verificación en la parte inferior también debe identificar que la primera fila de la tabla es para los encabezados. (Si no, seleccione esa opción.)

Create Table dialog box
El cuadro de diálogo Crear tabla debe adivinar correctamente el área de datos

Haga clic en Aceptar y ahora tiene una tabla con sombreado a rayas y botones de filtro. Puede hacer clic dentro de ella para anular la selección, si desea verla mejor (no haga clic fuera de la tabla). La barra de la cinta de opciones también muestra una pestaña Diseño para la tabla. En el lado izquierdo de la cinta, el cuadro Nombre de tabla muestra un nombre temporal de Tabla1. Bórrelo y llámelo Customer_Info (utilice un guión bajo en lugar de un espacio). A continuación, pulse Intro.

applying a name to a table
Aplique un nombre a cada tabla

Repita este proceso en las hojas Order Info y Payment Info . Llame las tablas Order_Info y Payment_Info, respectivamente.

Ahora estamos listos para insertar la tabla dinámica.

Insertar a la tabla dinámica

En la hoja Payment Info, asegúrese de que el cursor esté en algún lugar de la tabla. Vuelva a la pestaña Insertar de la cinta de opciones y haga clic en el icono de la tabla dinámica (es el primer icono).

inserting the PivotTable
Con el cursor dentro de una de las tablas, seleccione Insertar > Tabla dinámica

El cuadro de diálogo que aparece debe identificar correctamente la tabla y seleccione que la tabla dinámica se va en una hoja de cálculo nueva. En la parte inferior, haga clic en la casilla de verificación Agregar estos datos al modelo de datos. A continuación, haga clic en Aceptar.

add data to the Data Model
Agregar los datos al modelo de datos es lo que permite que las conexiones funcionen

Ahora tendrá una tabla dinámica en una nueva hoja de cálculo, habrá un panel de tareas en el lado derecho de la pantalla y la barra de la cinta mostrará la pestaña Análisis.

El panel de tareas muestra la tabla y los campos de la hoja activa, así que haga clic en Todas para ver todas las tablas que creó. Pero antes de poder usarlos, tenemos que conectarlos entre sí, y eso significa crear relaciones. Haga clic en el botón Relaciones en la barra de la cinta de opciones.

Configuración de relaciones de tabla

Al hacer clic en ese botón se muestra el diálogo Administrar relaciones. Haga clic en el botón Nuevo y se mostrará el cuadro de diálogo Crear relación. Crearemos dos relaciones utilizando el campo Order # como el conector.

En las listas desplegables, elija Payment_Info para la tabla y, junto a ella, seleccione Order # en el menú desplegable de Columna. En la segunda fila, seleccione Customer_Info en la lista desplegable Tabla relacionada y, junto a ella, seleccione Order # en la lista desplegable de Columna relacionada.

creating a table relationship
Hay tres tablas, así que crea dos relaciones

Esto significa que las tablas Payment_Info y Customer_Info están relacionadas donde tienen números de orden coincidentes.

Haga clic en Aceptar y veremos la relación que aparece en el cuadro Administrar relaciones.

Repita este proceso para crear una relación que une Payment_Info a Order_Info, también utilizando el campo Order #. El cuadro Administrar relaciones ahora debería verse así:

list of all relationships
El diálogo Administrar relaciones mostrará las relaciones que creo

Tenga en cuenta que no es necesario crear una relación entre las tablas Order_Info y Customer_Info, ya que se unen automáticamente a través de la tabla Payment_Info.

Haga clic en el botón Cerrar en la parte inferior del cuadro. Ahora finalmente podemos arrastrar campos a la tabla dinámica.

Insertar campos en la tabla dinámica

En la sección Todas del panel de tareas, haga clic en las pequeñas flechas para abrir las tres tablas, para ver sus campos. Arrastre los campos en las áreas de tabla dinámica de la siguiente manera:

  • State y Month en filas
  • Product en columnas
  • $ Sale en valores
  • Status en filtros
final PivotTable
Arrastre los campos de cada una de las tres tablas en la tabla dinámica

Ahora puede utilizar y modificar esto como cualquier otra tabla dinámica.

Conclusion

Utilizando la nueva característica de modelo de datos de objetos en Excel 2013, puede recopilar los campos de varias hojas de cálculo para crear una tabla dinámica unificada. Tenga en cuenta que las filas de cada tabla deben estar relacionadas entre sí de alguna manera. Tendrá las mejores posibilidades de éxito cuando las tablas tengan un campo común con valores únicos.

Si está buscando buenas formas de presentar sus datos, Envato Market tiene una buena selección de plantillas de Excel y PowerPoint, así como scripts y aplicaciones para convertir los datos de Excel en formatos web y viceversa.

Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.