viernes, 14 de octubre de 2011

Relacionar datos de distintas tablas en Excel


(artículo actualizado el 24/10/12)

Si el lector ha revisado los artículos de este blog referidos a Modelización de datos en Excel, y los 4 artículos relacionados siguientes, encontrará la necesidad de saber como se pueden relacionar los datos de dos tablas distintas.

En los artículos Modelización de datos en Excel (2)Modelización de datos en Excel (3), veíamos como era necesario crear en cada tabla unos campos de enlace llamados "campos clave" y que estos podían ser claves primarias (primary keys) o claves externas (foreing keys).


Pues bien, en primer lugar definamos qué es eso de "relacionar" los datos de dos tablas.

Relacionar tablas

Dicho de forma coloquial, relacionar dos tablas, es conseguir que, estando en una tabla nº 2, se pueda elegir un dato de un registro de la tabla nº 1 y automáticamente aparezcan en la tabla nº 2 los datos asociados a ese registro.

Por ejemplo: estando en una tabla que registra las nóminas mensuales, cuando seleccionamos el trabajador a quien corresponde los datos de la nómina que estamos registrando, aparezca en el campo de al lado el área a la que pertenece dicho trabajador.

¿Cómo se hace?

Debemos hacerlo en 3 pasos:

PASO 1) Definir un "Nombre al rango" donde están los datos a enlazar

En la Tabla nº 1 debemos seleccionar la columna de datos que queremos tener en la lista desplegable de la Tabla nº2. En el ejemplo que vamos a utilizar, es la columna donde tenemos los nombres de trabajadores:



Seguidamente, hacemos click para obtener el cuadro de "Nombre nuevo, en la ficha Fórmulas / grupo Nombres definidos / Asignar nombre / Definir nombre...


... y sustituimos el Nombre que se propone en el cuadro de diálogo, por el de IDTrabajadores



después de aceptar ya estará definido el Nombre para el rango, aunque no apreciemos nada. Si queremos ver los nombres de rangos que tiene nuestro libro de Excel, podemos revisarlos en el botón "Administrador de Nombres" que había a la izquierda de "Asignar nombre...".

Podemos recordar de forma completa "Cómo asignar un nombre de rango"

PASO 2) Crear las listas desplegables para que queden los datos enlazados

Ya en la Tabla nº 2, que es donde tenemos que seleccionar el nombre del trabajador en una lista desplegable, debemos crear una lista desplegable mediante "Validación de datos" y arrastrarla al resto de celdas. Cuando estemos creando la lista desplegable, debemos introducir en el campo "Origen:" el nombre que creamos en el paso 1) precedido del signo igual:

Podemos recordar cómo se hace una lsita desplegable con "Validación de datos" 


Podemos comprobar la lista desplegable (y también comprobar que si añadimos trabajadores en Tabla nº 1, también aparecerán en la lista desplegable).



PASO 3) Obtener en la celda de la derecha el IDÁrea que corresponde al trabajador que se seleccione en la lista desplegable

Es decir, si selecciono el trabajador Páez Tur, Gabriel ... que  me aparezca automáticamente en la celda de la derecha el área de la empresa donde trabaja.

Esto se hace introduciendo una función BUSCARV que busque el valor de la lista desplegable en la columna correspondiente de Tabla nº1. Una vez encontrado el valor (estará en una determinada fila), que nos devuelva el valor que hay en la misma fila, pero en la 5ª columna (podemos ver como en la columna nº 5 de la Tabla nº 1 que hay en el PASO 1), tenemos el IDÁrea del trabajador).

Podemos, en el siguiente enlace, recordar cómo se utiliza la función BUSCARV, pero ya adelantamos que la fórmula quedaría así:



Hemos conseguido que, seleccionando un trabajador en el campo ID Nombre Trabajador, obtengamos automáticamente un dato relacionado con él y que está en otra tabla.

Las dos tablas han quedado relacionadas mediante los campos clave "ID Nombre Trabajador".



Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:



 

10 comentarios:

  1. muchísimas graciuas esto es súper útil

    ResponderEliminar
  2. Quiero vincular en una hoja(plantilla) datos de 12 hojas distintas, datos de una nomina. Los datos están en distintas filas y columnas. Quiero que automáticamente se guarde en la plantilla sin tener que copiar pegar.
    Cuál formula se puede aplicar aquí.

    ResponderEliminar
    Respuestas
    1. Solo podemos responder preguntas directamente relacionadas con el artículo, siempre y cuando tengan una respuesta corta y la pregunta está formulada de forma inequívoca.
      Saludos.

      Eliminar
  3. Gracias por todo, sobre el modelado de datos en excel. Exitos.

    ResponderEliminar
  4. Hola, mi intención es crear una lista desde validación de datos que tome los datos de distintas tablas. ej: =MiLista (=Tabla1[[#Datos];[VIVIENDA]];Tabla3[[#Datos];[TRANSPORTE]];Tabla4[[#Datos])aunque me arroja un error

    ResponderEliminar
    Respuestas
    1. Buenos días:
      El origen de datos de una lista desplegable debe ser una fila o columna individual. Por eso arroja un error.
      Saludos

      Eliminar
  5. Buenas tardes, necesitaría ayuda jeje , mi intención es hacer de alguna manera enlazar datos de una tabla o una hoja a varias pero de las que yo seleccione, no se si me entienden porque no se como explicarlo. Muchísimas gracias de antemano.

    ResponderEliminar
    Respuestas
    1. Buenas tardes.
      Es complicado conocer el problema si la explicación no es suficiente. No he entendido.
      Saludos.

      Eliminar
  6. De:Juan Torío i Altus a 28/01/2021
    Si copio una tabla i la pego en otro archivo Excel, se copia todo però ya no se comporta como tabla todo i hacer un pegado especial con todo el tema de origen. Què tengo que hacer para mantener las características de Tabla.
    Gracias anticipadas
    NOTA En VBA he tratado de copiarla i me pasa lo mismo. He probado también asignándola a un TableObject pero da error.

    ResponderEliminar
    Respuestas
    1. Cuando se copia y se pega una tabla de Excel en condiciones normales, se pega el formato de tabla también.
      Las "condiciones normales" se refieren, al menos, a que:
      1) Los archivos deben ser archivos de Microsoft Excel de escritorio con formato xlsx, xlsm o xlsb (versiones 2007 o superior)
      2) Se debe pegar con formato (no pegar valores).

      Saludos.

      Eliminar