Cómo hacer una base de datos en Excel con enlaces a archivos

Excel no solo lo utilizamos para hacer cálculos, sino también para realizar consultas y utilizarlo como base de datos.

En este video-tutorial te explicaré como hacer una base de datos en Excel que permita realizar consultas de la información así como efectuar enlaces a archivos externos.

Descarga los archivos utilizados en el video aquí

Explicación

Utilizando como ejemplo una nómina de profesores de una universidad, realizaremos una base de datos en Excel en la que podamos seleccionar un profesor de una lista desplegable y que nos arroje los datos asociados a este profesor así como enlaces que nos permitan abrir archivos como fotos, pdfs, word, y carpetas donde hay más información

Pasos:

1.- Como hacer la lista desplegable

2.- Como traer la información de otra hoja

3.- Como hacer los enlaces a archivos externos

4.- Cómo hacer un enlace a una carpeta

1.- Como hacer la lista desplegable
Para poder seleccionar un profesor de una lista desplegable o menú debemos hacer los siguientes pasos:

• Ir a la pestaña “Fórmulas” hacer click en “Asignar nombre”. En la ventana que se abre vamos a escribir en el campo “Nombre”: Profesores y en el campo “Se refiere a” seleccionamos el rango donde están listados los profesores que se encuentran en la hoja “Datos”. Marcaré unas celdas más por si se agregan profesores en el futuro.

• Con este nombre creado, iremos a la celda C8 y en la pestaña “Datos” haremos click en “Validación de datos”.  En la ventana que se abre, elegiremos en criterio de validación “Lista”  y en el campo “Origen” escribimos el nombre generado:  =Profesores. Con esto generamos la lista desplegable que hace referencia a los profesores.

Si quieres aprender más de listas desplegables, te sugiero que visites este post

2.- Como traer la información de otra hoja
La idea es que al seleccionar el profesor, me traiga la información de legajo, fecha de ingreso, profesión, cédula, dirección, teléfono y correo electrónico. Esta información está en la hoja “Datos”. Para eso vamos a utilizar la función BUSCARV.
Esta función buscará el nombre del profesor en la hoja “Datos” y cuando lo encuentre, me traerá el dato que especifiquemos.

Fórmula General

BUSCARV (valor_buscado, matriz_buscar_en, indicador_columnas, [ordenado])

Fórmula aplicada: 

=BUSCARV($C$8;Base!$B$5:$I$12;3;0)

• C8 es la celda donde se encuentra el nombre del profesor. Es el dato que buscará

• Base!$B$5:$I$12: donde tiene que buscar esa información

• 3 es el número de columna a traer. Como queremos que traiga la información de legajo y ese dato está en la columna número 3 del rango que va de B5:I12, escribimos 3.

• El último argumento es 0 para que la coincidencia sea exacta.

Esta misma función la debemos hacer para el resto de los campos: la única diferencia en BUSCARV será el número de columna.  Por ejemplo, para la fecha de ingreso será:

=BUSCARV(C8;Datos!B5:I12;2;0)

Porque en la columna 2 está el dato de fecha de ingreso.

Y así lo haremos para cada información que necesitamos traer. A partir de aquí, al seleccionar el profesor, cada dato se actualizará.

Si quieres saber más sobre el funcionamiento de BUSCARV puedes ver este post

3.- Como hacer los enlaces a archivos externos
La idea es generar un hipervínculo que al hacer click me abra los archivos asociados al profesor seleccionado. Por ejemplo su foto, cv, contrato, horarios.
Para facilitar los hipervínculos es importante acomodar los archivos e identificarlos con un identificador único. Pasos:

1. Crearemos una carpeta en algún lugar de la computadora que se llame “Profesores”.

2. Dentro de esa carpeta vamos a generar una carpeta por profesor. Como tenemos 5 profesores, crearemos 5 carpetas

3. Cada carpeta debe tener un identificador único que puede ser el legajo o un número que no se repita. Es importante esto para poder relacionar el profesor con su carpeta determinada.  Yo usaré el legajo. Por ejemplo, el legajo de Darth Vader es 5821, por lo tanto su carpeta se llamara 5821

Una vez creadas las carpetas guardaremos los archivos de cada profesor. Editando el nombre del archivo para que tenga un mismo nombre genérico:

• A la foto la nombramos “Foto”. 

• Al Curriculum en cada carpeta la renombramos “CV” 

• Al archivo que refiere al contrato, en cada carpeta la renombramos a “Contrato” 

• Y el archivo de Excel que hace referencia a horarios en cada carpeta la vamos a llamar “Horarios”

Y haré esto para cada profesor. 

Esta convención de utilizar un nombre genérico para cada tipo de archivo es muy importante, ya que me servirá para construir fácilmente los enlaces.

Una vez hecho esto, debemos hacer el enlace para cada archivo con la función HIPERVÍNCULO.

HIPERVINCULO(ubicación, [nombre_descriptivo])

• Ubicación: ruta donde se encuentra el archivo y 

• Nombre descriptivo: nombre para el enlace o link

Ruta Dinámica

Como la ruta se va actualizar a medida que elijamos un profesor,  utilizaremos unas celdas auxiliares para construir una ruta dinámica, es decir que al seleccionar determinado profesor, la misma se actualice.  

> En la celda B4 vamos a escribir la ruta principal donde están ubicadas todas las carpetas y en este caso será C:\Users\Cecilia\Desktop\Profesores\ ya que aquí es donde se alojan todas las carpetas.

> En la celda B5  vamos a hacer referencia al número de legajo que cambia al seleccionar un profesor y que coincide con el nombre de la carpeta de cada uno. Para hacer referencia a una celda escribimos  = y seleccionamos la celda.

> En la celda B6  vamos a unir estos dos datos:  la ruta general + el legajo. Usamos el ampersand en nuestro teclado que nos permite unir cadenas de texto y le agrego además la barra entre doble comillas.

=C1&C2&”\”

Así al elegir el profesor, esta celda me mostrará cuál es la ruta de sus archivos.

Con este dato podemos construir la función HIPERVÍNCULO en la celda C9 y agregaremos la parte del nombre del archivo. Para el primer caso, será "foto.jpg" con doble comillas.

=HIPERVINCULO(C6&"foto.jpg";"Click aquí para ver la foto")

De esta forma lo haremos para cada archivo. Entonces los hipervinculos quedarían así en cada caso:

=HIPERVINCULO(C6&"CV.docx";"Click aquí para ver el CV")

=HIPERVINCULO(C6&"Contrato.pdf";"Click aquí para ver el contrato")

=HIPERVINCULO(C6&"Horarios.xlsx";"Click aquí para ver los horarios")

Cada vez que hagamos click en alguno de los hipervínculos, abrirá el archivo correspondiente al profesor seleccionado.

4.- Cómo hacer un enlace a una carpeta
Para hacer un enlace en nuestro archivo de Excel que nos lleve a una carpeta, en principio debemos nombrar la carpeta con un nombre único, por ejemplo “Otros” agregando allí toda la información. 
Y luego construimos el hipervínculo indicando la carpeta:

HIPERVINCULO(C8&"Otros";"Click aquí para ver otros")

Entonces, al hacer click, nos lleva a esa carpeta determinada.

Así armamos una base de datos que además de traernos información, nos permite realizar enlaces a archivos externos y carpetas.

Organizar las carpetas así con números únicos y archivos con nombres genéricos nos permite:

1. Tener la información mejor organizada y fácilmente ubicable. Por ejemplo si quiero ver los datos de un profesor ya sabiendo su legajo sé donde buscar .

2. Lo más importante de hacerlo así es que nos da la flexibilidad de mover el archivo de Excel o las carpetas a otra ubicación en tu computadora o en otra computadora o incluso en la nube y que siga funcionado. Ya que al hacerlo de esta forma organizada, si cambiamos de lugar los archivos, lo único que tenemos que hacer es cambiar la ruta general.

Con esta convención de reglas, si bien al principio tuvimos que hacer un trabajito de armar carpetas y renombrar archivos, a la hora de agregar una nueva carpeta será más sencillo.

Este ejemplo se puede aplicar para una base de datos de producto, de clientes, de proveedores, etc

Espero que te haya servido

¡Saludos!

Cecilia - www.planillaexcel.com

PD: si quieres aprender más de Excel, dicto cursos online desde el nivel básico a avanzado.
Haz click aquí para ver los cursos.