Cambiar valores de una lista basados en la selección de otra lista

Cuando creamos listas desplegables en Excel, es común tener un par de ellas que sean dependientes entre sí, es decir una lista cuyos valores dependerán de la selección realizada en otra lista desplegable.

Aunque existe más de una técnica para crear listas dependientes en Excel, hoy utilizaremos las funciones DESREF, COINCIDIR y CONTAR.SI para vincular nuestras listas.

En nuestro ejemplo crearemos la primera lista con los nombres de tres departamentos de una empresa: Finanzas, Marketing y Producción. Una vez que se elija una opción de la primera lista, la segunda lista mostrará los nombres de las personas que pertenecen a dicha área de la empresa.

Listas dependientes en Excel

Nuestra hoja contiene toda la información necesaria para crear nuestras listas desplegables tal como lo puedes ver en la siguiente imagen.

Cambiar valores de una lista basados en la selección de otra lista

Para la técnica utilizada en este artículo, es necesario especificar los elementos de la segunda lista con su respectiva categoría asociada. Para nuestro ejemplo, el rango C1:D12 contiene los nombres de los empleados y el área a la que pertenece cada uno de ellos.

Una vez que tenemos el formato adecuado en los datos, podemos crear nuestra primera lista. Pare ello seleccionaré la celda G1 e iré a la ficha Datos > Validación de datos y crearé la lista desplegable de la siguiente manera:

Cambiar valores de una lista basados en la selección de otra lista en Excel

Al hacer clic en Aceptar, la celda G1 mostrará las áreas de la empresa indicadas en el rango A1:A3 de nuestra hoja.

Listas dependientes en Excel

Ahora crearemos la lista vinculada, la cual dependerá de la selección realizada en la celda G1. Ese dinamismo de la segunda lista es lo que nos obliga a utilizar funciones de Excel que nos permitirán especificar el rango de datos origen en base a la selección realizada en la primera lista.

Lista dependiente con DESREF

La función DESREF nos permite crear referencias a un rango de celdas en “tiempo real”, es decir, en el momento exacto en el que las necesitamos sin que la referencia esté previamente definida. Y lo mejor es que sus argumentos nos permiten indicar la celda inicial y final del rango, que es el comportamiento que necesitamos para nuestra lista.

Por ejemplo, si elegimos la opción Finanzas de la primera lista, queremos que la lista dependiente tenga como origen de datos el rango D1:D3.

Listas dependientes en Excel

Pero si elegimos el valor Marketing de la primera lista, entonces queremos que la lista dependiente tenga su origen de datos en el rango D4:D7.

Listas dependientes con la función DESREF

Este comportamiento lo lograremos con la función DESREF y en especial con el uso de su segundo y cuarto argumentos. La fórmula que utilizaremos como el origen de la lista dependiente será la siguiente:

=DESREF(D1, COINCIDIR(G1,C1:C12,0)-1, 0, CONTAR.SI(C1:C12,G1), 1)

Explicación de la fórmula

El primer argumento de la función DESREF es la celda donde comenzará nuestro rango. En este caso será la celda D1 porque ahí están los datos de origen de nuestra lista dependiente.

El segundo argumento de la función DESREF nos permite indicar un desplazamiento en filas a partir de la posición inicial que hemos indicado en el primer argumento. Es decir, si el segundo argumento tiene el valor 5, la función DESREF se moverá 5 filas hacia abajo y nos devolverla una referencia a la celda D6.

En lugar de que el segundo argumento de la función DESREF tenga un número fijo de filas, utilizamos la función COINCIDIR para encontrar la celda donde inician los valores del área indicada en la celda G1. Por ejemplo, si la celda G1 tiene el valor Finanzas, la función COINCIDIR devolverá el valor 1, si se elige el valor Producción, se devolverá el valor 8.

La siguiente imagen muestra el resultado de la función COINCIDIR cuando se ha elegido la opción Marketing de la lista desplegable.

Cambiar valores de una lista dependiente en Excel

Esto quiere decir que Excel ha descubierto que el valor Marketing inicia en la fila 4 del rango C1:C12. Este dato es muy importante porque nos ayuda a indicar a la función DESREF la celda exacta donde comienzan los nombres de los empleados que pertenecen al área seleccionada.

Observa que el segundo argumento de la función DESREF hace la resta del valor 1 al resultado de la función COINCIDIR. Esto es importante para indicar la cantidad exacta de celdas que deberá desplazarse hacia abajo hasta llegar al inicio de los datos.

El tercer argumento de la función DESREF es cero porque no queremos desplazarnos ninguna columna. De esta manera, los primeros tres argumentos de la función DESREF nos sirven para indicar la celda inicial del rango que deseamos obtener.

El cuarto argumento nos permite indicar la cantidad de filas que deseamos incluir en la referencia devuelta por la función DESREF. Para eso utilizaremos la función CONTAR.SI que nos ayudará a obtener la cantidad exacta de empleados que pertenecen a un departamento de la empresa. Por ejemplo, la siguiente imagen muestra el resultado de la función CONTAR.SI una vez que se ha elegido la opción “Producción” en la celda G1.

Cambiar de manera dinámica una lista dependiente en Excel

Este resultado nos hace saber que existen 5 elementos que pertenecen al departamento de  Producción y por lo tanto es el número de filas que deseamos incluir en el rango devuelto por la función DESREF. El quinto argumento de la función DESREF es el valor 1 porque solo quiero obtener una columna.

Crear la lista dependiente

Una vez que comprendemos la utilidad de la función DESREF, podemos crear la lista dependiente, así que seleccionaré la celda G2 e iré a la ficha Datos > Validación de datos para crear la lista de la siguiente manera:

Lista dependiente en Excel con la función DESREF

Observa que he colocado la fórmula como el origen de la lista. Al hacer clic en Aceptar, se habrá creado nuestra lista dependiente en la celda G2 y que cambiará sus valores respecto a la selección realizada en la celda G1. La siguiente imagen muestra los nombres de los empleados que pertenecen al departamento de Marketing.

Cambiar lista en base a la selección de otra lista en Excel

Puedes descargar el libro de trabajo para comprobar que la lista dependiente, ubicada en la celda G2, cambiará sus valores en base a la selección realizada en la lista de la celda G1.

Como lo mencioné al principio del artículo, existen diferentes métodos para crear listas dependientes así que, si quieres conocer otra alternativa para crear este tipo de listas, te invito a leer el artículo Listas desplegables dependientes en Excel.

Moisés Ortiz

Autor: Moisés Ortiz

Moisés es consultor en tecnologías de la información con especialidad en herramientas de inteligencia de negocios y análisis de datos. Tiene más de 25 años de experiencia en el uso de Excel y es una de sus herramientas predilectas para la creación de dashboards y reportes analíticos. Ver más ➤