Listas y Rangos Dinámicos en Excel

Trabajar con Rangos en excel – un problema

Cuando se trabaja con bases de datos, el uso de búsquedas es mas que necesario, quienes usan formatos contables lo saben muy bien, regularmente se usa la función BUSCARV para esto, pero algo que también te puede ser de mucha ayuda es el uso de listas desplegables y nombres de rangos para hacer referencia a esas bases de datos desde las formulas.

Descargar MegaPack Contables en Excel

Normalmente se hace referencia a rangos de este modo A1:C10 ,pero es mejor si generas un nombre de rango que haga referencia a esos mismos datos pero que describa claramente de que tipo de datos se trata, por ejemplo, si ese rango es de una tabla de empleados, puedes poner un nombre a ese rango con el nombre “Empleados”, ese mismo nombre lo puedes usar en las formulas y es mucho mas claro a la hora de analizar esas formulas.

Listas desplegables para trabajar mas facil

Las listas desplegables también son importantes ya que te ayudan en el registro de datos en algún formato o cualquier hoja de registro, ademas te ayudan a no cometer errores, por ejemplo, tienes un formato de cotización donde hay que estar ingresando nombres de clientes y productos, si tienes una lista donde puedas seleccionar esos datos, tu trabajo simplemente sera mas sencillo.

Ya dijimos que las listas y nombres de rangos son importantes para el manejo de datos en excel, pero que pasa con el hecho de que las tablas de datos cambian de tamaño, asea que se agregan o eliminan registros, si defines nombres de rango y luego hay mas registros, probablemente ese nombre de rango no tomara en cuenta esos nuevos registros, a menos que definas un nombre con excedente de celdas, esto es, tomar en cuenta celdas que aun no tienen datos.

La mejor forma de trabajar con rangos

Pero hay una mejor forma de hacer que los nombres de rango tomen en cuenta los nuevos registros, sin tener que indicar rangos muy grandes, la idea es que con cada registro, el rango crezca de manera dinámica en el nombre que hemos asignado.

En esa imagen podemos ver una tabla con datos de personas, la idea es generar un nombre de rango y una lista desplegable para usar en una hoja de búsqueda, es decir, en una celda aparecerá una lista desplegable donde seleccionaremos el numero de la persona y en otra debe aparecer su nombre y edad.

Como generar un rango dinamico

Para esto usaremos el administrador de nombres y las funciónes Contara() y =DESREF(ref,filas,columnas,alto,ancho), de esta usaremos solo los argumentos “ref, alto y ancho”.

Para generar el nombre del rango de la tabla de datos, vamos a menu Formulas/ Administrador de Nombres/ Nuevo, en nombre ponemos “Datos”, sin las comillas, pueden poner el que deseen, en se refiere a: ponemos =DESREF(Data!$A$4,,,CONTARA(Data!$A$4:$A$1048576),3) de esa manera se genera un rango dinámico para esa tabla, cuando se agreguen mas datos, esta formula los tomara, observen como he puesto un tres al final, ese tres nos indica el ancho de la tabla, que empieza en este caso en la celda A4.

Como generar una lista dinamica

Para la lista desplegable es el mismo procedimiento, con la diferencia que este caso no indicaremos un ancho puesto que queremos que tome solo una columna. =DESREF(Data!$A$4,,,CONTARA(Data!$A$4:$A$1048576))

Crear una lista desplegable para búsquedas

Para la hoja de búsqueda usaremos una lista desplegable, en ese caso nos servirá el nombre de lista que se ha generado, esto se puede hacer en otra hoja.

Seleccionas una celda para la lista desplegable/ Click en datos/ Validación de datos/ en permitir seleccionas Lista/ en origen pones =Lista y click en aceptar.

Ahora en esa celda aparece una flechita que al darle click se despliega la lista de números de empleado.

En otra celda pones esta formula para obtener el nombre del numero seleccionado de la lista =BUSCARV($B$4,Datos,2,FALSO) y esta para la edad =BUSCARV($B$4,Datos,2,FALSO)

Como puedes observar, el uso de listas y rangos dinámicos te hacen la vida mas fácil a la hora de trabajar con excel, esto porque ya no tienes que preocuparte por estar re-ajustando tus formulas cuando las tablas se modifican.

Espero que este ejemplo te sea de mucha ayuda.