() translation by (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.
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.
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.
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.
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.)
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.
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).
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.
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.
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í:
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
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.
