Si te interesa saber cómo relacionar tablas en Excel, lo más probable es que tengas datos comunes en diferentes tablas y te interese analizar alguna información que involucra ambas tablas.
Relacionar Tablas en Excel es muy interesante y, junto con tablas dinámicas, podrá cambiar totalmente tu forma de ver Excel y tu dinámica de trabajo con las Hojas de Excel.
Vayamos al grano, en este artículo aprenderás, mediante un mismo ejemplo, a relacionar tablas mediante la fórmula BUSCARV y a relacionarlas a través de relaciones de tablas para luego insertar tablas dinámicas increíbles y muy completas.
Aprende cómo usar la función BUSCARV
En nuestro ejemplo para explicar cómo relacionar Tablas en Excel, tendremos un Libro de Excel con dos Hojas.
Este libro lo puedes descargar aquí para practicar.
- Hoja 1: VENTAS – Las ventas realizadas por vendedor:
- Hoja 2: ZONAS – La zona de Madrid atendida por cada vendedor.
Lo que queremos averiguar de estos datos es ¿Cuánto vendimos por zona de venta?
MIRA EL CURSO DE TABLAS DE EXCEL COMPLETO Y GRATUITO
Tabla de Contenidos
Insertar Tablas a Nuestros Datos.
Lo primero que debemos hacer es convertir los datos de cada Hoja de trabajo en una Tabla con los datos. Luego las nombraremos para facilitar el trabajo y, así, podremos hacer una relación más sencilla.
Pasos para Insertar una tabla a nuestros Datos.
- Paso 1: nos colocamos en la Hoja VENTAS y seleccionamos todos nuestros datos.
- Paso 2: le damos a la pestaña INSERTAR y luego, al icono “Tabla”
- Paso 3: Verificamos que los datos seleccionados incluyan todos los datos de nuestro rango, y presionamos “ACEPTAR”
- Paso 4: le colocamos un nombre a nuestra tabla de “Ventas”: colocándonos sobre cualquier celda perteneciente a la Tabla, presionamos la ventana “Diseño” y allí escribimos el nombre de la Tabla “Ventas”
Listo, hemos creado y nombrado nuestra Tabla “Ventas”, ahora seguimos los mismos pasos y creamos nuestra Tabla “Zonas” en la Hoja de Excel “Zonas”.
- Paso 1: seleccionamos la Hoja “Zonas”, luego seleccionamos todos nuestros datos.
- Paso 2: vamos a la pestaña “Insertar” y presionamos el icono “Tabla”
- Paso 3: verificamos los datos y presionamos “Aceptar”
- Paso 4: Nos dirigimos a la pestaña “Diseño” y colocamos el nombre de Tablas “Zonas”
Perfecto, ahora ya tenemos dos tablas: la tabla “Ventas” y la Tabla “Zonas” ahora comencemos a relacionarlas para poder calcular ¿Cuánto vendimos por zona de venta?
Relacionar Tablas con BUSCARV
Para relacionar Tablas en Excel con la función BUSCARV, lo que haremos es traer a la tabla “Ventas” la información que nos interesa de la Tabla “Zonas”. Como lo que queremos calcular es ¿Cuánto vendimos por zona de venta?, entonces lo que debemos traer es a que zona pertenece cada vendedor.
Pasos para traer información de la Tabla “Zonas” a la tabla “Ventas” usando la función BUSCARV de Excel:
- Paso 1: nos colocamos en la Hoja donde queremos traer los datos. En nuestro caso en la Hoja “Ventas”
- Paso 2: en la Celda G1 escribimos el nuevo título de nuestra columna G “Zona por Vendedor” presionamos “Enter” y automáticamente esta nueva columna se añade a nuestra Tabla “Ventas”
- Paso 3: en la celda G2 vamos a usar la función BUSCARV para traernos el dato de a qué zona pertenece cada vendedor:
- Escribimos “=BUSCARV(”
- Primer Argumento: ¿Qué vamos a buscar? valor buscado es el código de vendedor, que queremos que Excel busque y asocie a la Zona. Es el valor común entre nuestras 2 Tablas.
- Segundo Argumento: ¿Dónde lo vamos a buscar? En la Tabla “Zonas”
- Tercer Argumento: ¿Qué valor quieres que Excel Devuelva? La zona de ventas, por lo tanto es la columna “2” de nuestros matriz.
- Cuarto Argumento: ¿Queremos que busque el código de vendedor exacto? Si, entonces debemos poner “FALSO” coincidencia Exacta.
=BUSCARV([@[Codigo Vendedor]];Zonas[#Todo];2;FALSO)
- Paso 4: Cerramos paréntesis y presionamos “Enter” Excel automáticamente asociara cada código de vendedor a la zona de Madrid que se encarga de atender:
Listo, hemos relacionado nuestras dos tablas de Trabajo. Ahora si queremos saber ¿Cuánto vendimos por zona de venta? Podemos utilizar la función SUMAR.SI
Usar la Función SUMAR.SI – Analizando Datos Relacionados.
La Función SUMAR.SI de Excel, consiste en sumar unos valores con una condición que nosotros le asignaremos. En nuestro Caso queremos que sume las ventas con la condición de que lo haga por Zona de Venta.
Pasos para usar la Formula SUMAR.SI para analizar nuestros datos:
Mira este artículo -> Función SUMAR.SI explicada paso a paso
- Paso 1: Copiar todas las ciudades a partir de la celda I3. Escribir en la celda J2 “VENTAS”.
- Paso 2: nos colocamos en la celdas J3 y colocamos “=SUMAR.SI(”
- Primer Argumento: ¿Dónde vamos a buscar la zona? En la columna “Zona por Vendedor”
=SUMAR.SI(Ventas[Zona por Vendedor];
- Segundo Argumento: ¿Qué ciudad vamos a buscar? La que esta escrita en la celda I3. Por lo tanto I3.
=SUMAR.SI(Ventas[Zona por Vendedor];I3;
- Tercer Argumento: ¿Qué valores quieres que sume? La columna “TOTAL”
=SUMAR.SI(Ventas[Zona por Vendedor];I3;Ventas[TOTAL])
- Paso 3: Presionar “Enter”
- Paso 4: Arrastrar la formula hasta la columna J8
Con este análisis vemos la funcionalidad de relacionar Tablas en Excel a través de la función BUSCARV. Se necesita tener un dato en común para poder relacionarlas y luego algunas herramientas de análisis como SUMAR.SI para responder preguntas muy interesantes de nuestro negocio.
Con la relación de Tablas de Excel, pudimos asociar a que zona estaba vinculada cada venta, y una vez que teníamos este vínculo realizado, calculamos cuanto se vendió por zona de ventas.
Relación de Tablas: Fácil y Sencillo.
Como pudimos observar anteriormente, si usamos la formula BUSCARV podemos relacionar nuestras tablas y luego analizarlas. Sin embargo, necesitas conocer varias fórmulas y es un proceso algo complicado.
Te tenemos muy buenas noticias, Excel permite modelar los datos a través de una relación de Tablas muy sencilla y automatizada. Luego de que las tablas estén relacionados podemos analizar los datos con Tablas dinámicas.
Volveremos a los datos iniciales, tabla “Ventas” y tabla “Zonas”. Con este nuevo método queremos responder la misma pregunta ¿Cuánto vendimos por zona de venta?
Pasos para relacionar Tablas en Excel mediante el modelado de datos:
- Paso 1: Estando en cualquiera de nuestras Hojas de Excel “Ventas” o “Zona”, presionamos la pestaña “Datos” y luego el icono “Relaciones”
- Paso 2: se nos abre una ventana emergente y presionamos “Nuevo”
- Paso 3: en la nueva ventana emergente vamos a definir nuestra relación. Es decir que dato de nuestra tabla “Ventas” es el mismo que en la tabla “Zonas”. En ambas tablas el dato común es “Código de Vendedor”
Con esto lo que le estamos pidiendo a Excel, es que cree la relación entre el “código de vendedor” de la tabla “Ventas” y el “código de vendedor” de la Tabla “Zonas”
- Paso 4: Presionamos “Aceptar”
- Paso 5: observamos en nuestra ventana “Administración Relaciones” que la relación ha sido creada y esta “Activa” y presionamos “Cerrar”
Listo Excel internamente ya ha relacionado las tablas, por lo tanto ahora podemos analizarlos con Tablas Dinámicas, de manera muy sencilla y responder nuestra pregunta de ¿Cuánto vendimos por zona de venta?
Tabla Dinámica – Analizando Datos Relacionados
Te puede interesar -> Cómo hacer una tabla dinámica en excel
En esta parte del artículo, te enseñaremos como analizar los datos con una tabla dinámica y llegar a la respuesta de ¿Cuánto vendimos por zona de venta?
Pasos para analizar nuestros datos, previamente relacionados, con una tabla dinámica
- Paso 1: seleccionamos todos los datos de nuestra hoja “Ventas”, luego vamos a la pestaña “insertar” y presionamos el icono “Tabla Dinámica”
- Paso 2: En la ventana Emergente, verificas que el rango de datos sea la Tabla “Ventas” y seleccionas que te abra la tabla en una pagina nueva y le das “Aceptar”
- Paso 3: Se va a abrir nuestra tabla dinámica en una nueva Hoja, solo con los datos de la Hoja Ventas, así que debemos presionar “MAS TABLAS”
- Paso 4: Listo ahora tenemos una Tabla dinámica con los datos de nuestras dos Tablas:
- Paso 5: contestamos nuestra pregunta: ¿Cuánto vendimos por zona de venta?
- FILAS: colocamos “Zona de Ventas” de la tabla “Zonas”
- Valores: colocamos “TOTAL” de la Tabla “Ventas”
Listo, de esta manera también hemos podido relacionar nuestros datos y conseguir las ventas totales por zona de venta.
Si lo hacemos a través del primer paso con la función BUSCARV usaremos cálculos y formulas, pero podemos llegar a la respuesta requerida. Sin embargo, con el segundo método: modelando los datos y analizándolos con tablas dinámicas, no necesitamos ninguna fórmula.
Esperamos tengas claro como relacionar tablas en Excel. Cualquier duda, déjanos tus comentarios.