LISTAS DESPLEGABLES DINÁMICAS: 3 Métodos para conseguirlas

LISTAS DESPLEGABLES DINÁMICAS: 3 Métodos para conseguirlas

Unas buenas listas desplegables mejoran la usabilidad de tu hoja de cálculo y disminuyen drásticamente los errores al introducir nueva información. No te pierdas estos 3 métodos para dominarlas!

En la comunidad de WhatsApp de InflueXcel surgió un reto muy interesante planteado por Joan: partiendo de una tabla con una lista de nombres y, junto a cada uno, el número de veces que debía repetirse, había que generar como resultado una matriz completa con cada nombre repetido exactamente esas veces. Por ejemplo, si Leo aparece con un 4, debe salir cuatro veces; Andrés con un 2, dos veces, y así sucesivamente. Lo bonito del caso es que llegaron tres soluciones distintas, creativas y válidas, demostrando que en Excel casi siempre hay varios caminos para resolver el mismo problema. Vamos a verlas una a una.

Solución 1: REPETIR + UNIRCADENAS + DIVIDIRTEXTO (la idea de Leo)

La propuesta de Leo se basa en montar una cadena de texto larguísima separada por un delimitador y luego trocearla para construir la matriz de salida.

  1. Se usa REPETIR dándole un texto (el nombre más un separador, en este caso el carácter |, la pleca o palo vertical) y el número de veces que debe repetirse. Si en lugar de una sola celda le pasamos todo el rango de nombres y todo el rango de repeticiones, la función se ejecuta para cada fila y devuelve el resumen en una única matriz.
  2. Con UNIRCADENAS se concatena todo en una sola cadena. Se le pasa un delimitador vacío (""), se indica que ignore las celdas vacías y el rango generado por REPETIR.
  3. Finalmente, DIVIDIRTEXTO separa esa cadena. No se usa delimitador de columna (lo dejamos vacío), sino delimitador de fila con el carácter |, de modo que cada vez que aparece el palo se crea una fila nueva. Se activa "ignorar celdas vacías" en verdadero, ya que el último elemento deja un separador sobrante.

La clave aquí es elegir como delimitador un carácter que no aparezca nunca dentro de los nombres, como la pleca, para evitar cortes indeseados.

Solución 2: MAKEARRAY con LAMBDA (la propuesta de Andrés)

Andrés tiró de MAKEARRAY, una función que genera una matriz indicando número de filas y columnas, y que llama a una LAMBDA recibiendo en cada celda el índice de fila (f) y de columna (c).

  • El número de filas se calcula con SUMA del total de repeticiones (el resultado final).
  • El número de columnas es el número de categorías o nombres distintos.
  • Dentro de la LAMBDA, mediante SI, se comprueba: si el número de fila (f) es menor o igual que las repeticiones de esa columna (obtenidas con INDICE usando c), entonces se devuelve el nombre correspondiente (de nuevo con INDICE sobre la columna de nombres). Si no, se fuerza un error con 1/0, una indeterminación que Excel marca como error.

El resultado es una matriz donde cada columna representa un nombre repetido tantas veces como toque, rellenando el resto con errores. El paso final es aplastar esa matriz en una sola columna con APILARV... en realidad con ENCOL (TOCOL): se le indica que ignore los errores (valor 2) y que escanee por columnas, colocándolas una debajo de otra. Así se obtiene el listado limpio.

Solución 3: ESCANEAR + BUSCARX (la propuesta de Nacho)

La tercera vía usa ESCANEAR (SCAN) para crear una tabla de posiciones acumuladas al vuelo y luego BUSCARX para consultarla.

  1. ESCANEAR parte de un valor inicial (cero), recorre el array de repeticiones y, con una LAMBDA de dos variables (el valor previo y el de la iteración), va sumando: previo + iteración. Esto genera un acumulado del tipo 4, 6, 9, 12, 17 (el total de filas).
  2. Con SECUENCIA se crea la lista del 1 al total de filas (la suma de todas las repeticiones).
  3. Para cada número de esa secuencia se hace un BUSCARX sobre la tabla acumulada generada por ESCANEAR, devolviendo el nombre de la columna de categorías. La clave está en el modo de coincidencia: se usa la opción de coincidencia exacta o el siguiente elemento mayor, de forma que las posiciones 1 a 4 devuelven el primer nombre, las siguientes el segundo, y así sucesivamente.

Tres caminos, un mismo resultado

Lo mejor de este reto es que las tres fórmulas son dinámicas: si cambias un número de repeticiones, añades un nombre nuevo o insertas una fila, las tres soluciones se recalculan y adaptan automáticamente al resultado esperado.

Más allá de cuál sea la más elegante, cada enfoque enseña un mecanismo distinto de Excel: la manipulación de texto con REPETIR, UNIRCADENAS y DIVIDIRTEXTO; la construcción de matrices indexadas con MAKEARRAY y LAMBDA; y la acumulación iterativa con ESCANEAR combinada con búsquedas aproximadas de BUSCARX. Un ejemplo perfecto de cómo, dentro de una comunidad, un solo problema se convierte en una oportunidad de aprender varias técnicas a la vez.

Más contenido de Excel en InflueXcel