¿No tienes Power Pivot? No importa. La mayor parte de Power Pivot está integrado en Excel 2013 e incluso más en Excel 2016. Hoy, nuestro consejo de Ash es unir tablas en una tabla dinámica.
Todos los miércoles durante siete semanas, presento uno de los consejos favoritos de Ash Sharma. Ash es gerente de producto del equipo de Excel. Su equipo te trae tablas dinámicas y muchas otras cosas buenas. Hoy, la característica favorita de Ash es unir múltiples conjuntos de datos usando Relaciones y el Modelo de datos.
Digamos que su departamento de TI le proporciona el conjunto de datos que se muestra en las columnas A: D. Hay campos para cliente y mercado. Necesita combinar ciertos mercados en regiones. Cada cliente pertenece a un sector. La región y el sector no están en los datos originales, pero tiene tablas de búsqueda para proporcionar esta información.
Normalmente, aplanaría los datos usando BUSCARV para extraer datos de las tablas naranja y amarilla a la tabla azul. Pero como el campo clave no está en el lado izquierdo de cada tabla, tendrá que cambiar a INDICE y COINCIDIR, o reorganizar las tablas de búsqueda.
A partir de Excel 2013, puede dejar las tablas de búsqueda donde están y combinarlas en el informe de la tabla dinámica.
Para que esta técnica funcione, las tres tablas deben tener el formato de tabla. Seleccione una celda en cada conjunto de datos y elija Inicio, Formatear como tabla o presione Ctrl + T. Las tres tablas se llamarán inicialmente Tabla1, Tabla2 y Tabla3. Utilizo la pestaña Diseño de herramientas de tabla de la cinta de opciones y cambio el nombre de cada tabla. También cambio el color de cada mesa. En este ejemplo, la tabla azul se llama Datos. La tabla naranja es RegionTable. La tabla amarilla es SectorTable.
Nota
Algunos le dirán que debe usar nombres geek como Fact, TblSector y TblRegion. Si alguien te molesta así, simplemente roba su protector de bolsillo y hazle saber que prefieres nombres que suenen en inglés.
Para cambiar el nombre de una tabla, escriba un nuevo nombre en el cuadro del lado izquierdo de la pestaña Diseño de herramientas de tabla. Los nombres de las tablas no deben tener espacios.
Una vez definidas las tres tablas, diríjase a la pestaña Datos y haga clic en Relaciones.
En el cuadro de diálogo Administrar relaciones, haga clic en Nuevo. En el cuadro de diálogo Crear relación, especifique que el campo Cliente de la tabla de datos está relacionado con el campo Cliente de SectorTable. Haga clic en Aceptar.
Defina otra nueva relación entre el campo Market en los campos Data y RegionTable. Después de definir ambas relaciones, las verá en el cuadro de diálogo Administrar relaciones.
Felicitaciones: acaba de crear un modelo de datos en su libro de trabajo. Es hora de construir una tabla dinámica.
Seleccione la celda en blanco donde desea que aparezca su tabla dinámica. De forma predeterminada, el cuadro de diálogo Crear tabla dinámica elegirá Usar el modelo de datos de este libro. La ubicación de la tabla dinámica será la celda que eligió de forma predeterminada. Haga clic en Aceptar.
La lista Campos de tabla dinámica enumerará las tres tablas. Utilice el triángulo a la izquierda de una tabla para expandir el nombre de la tabla y mostrarle los campos.
Expanda la tabla de datos. Seleccione el campo Ingresos. Se moverá automáticamente al área de Valores. Expanda SectorTable. Elija el campo Sector. Se moverá al área de Filas. Expanda RegionTable. Arrastre el campo Región al área Columnas. Ahora tendrá una tabla dinámica que resume los datos de las tres tablas.
Nota
En cada libro que he escrito antes de hoy, utilizo una técnica diferente para construir este informe. Después de definir las tres tablas, elijo la celda A1 e Insertar, tabla dinámica. Marco la casilla para Agregar estos datos al modelo de datos. En la lista Campos de la tabla dinámica, seleccione Todo en la parte superior de la lista. Elija campos para el informe y luego defina las relaciones después del hecho. La técnica descrita anteriormente parece más suave y en realidad implica un poco de planificación por adelantado. A las personas que usan Option Explicit en su código VBA definitivamente les gustaría este método.
Las relaciones en el modelo de datos hacen que Excel se sienta más como Access o SQL Server, pero con todas las bondades de Excel.
Me encanta pedirle al equipo de Excel sus funciones favoritas. Cada miércoles, compartiré una de sus respuestas. Gracias a Ash Sharma por aportar esta idea.
Pensamiento del día de Excel
Les he pedido a mis amigos de Excel Master sus consejos sobre Excel. Pensamiento de hoy para reflexionar:
"No busques si estás en una relación"
John Michaloudis
