En el siguiente ejemplo, tenemos tres columnas, una con provincias, otra con centros comerciales, y, otra con productos.
Queremos crear una lista desplegable, donde vamos a seleccionar un encabezado, es decir, provincia, centro, o producto, y, una siguiente lista desplegable, donde se rellene con los elementos que pertenezcan al encabezado seleccionado.
Vamos a crear la primera lista, lo haremos en la celda B2, que es fácil, para ello, vamos a la pestaña datos, dentro del grupo, herramientas de datos, hacemos clic en validación de datos.
Se abre la ventana de validación de datos, desplegamos permitir, y, seleccionamos lista.
En la ventana origen, seleccionamos los encabezados.
Aceptamos.
Seleccionamos, por ejemplo, centro.
Pues aquí empieza el ejercicio, pues ahora, vamos a crear la segunda lista desplegable, con los elementos del centro que hemos seleccionado.
Me voy a colocar en la celda C2.
¿Qué debemos de hacer?
Pues, por ejemplo, si hemos elegido centro en la lista desplegable, quiero decir, que lo primera que debo de hacer, es moverme a dicho encabezado, que sería el encabezado número 2.
Ya conocemos la función COINCIDIR, que nos devuelve la posición de un elemento, dentro de un rango, pues, vamos a buscar en encabezado seleccionado, dentro de los tres encabezados, con una coincidencia exacta.
La sintaxis quedaría:
=COINCIDIR(B2;E1:G1;0)
Vemos que nos devuelve la posición dos, lo siguiente que debemos de hacer, es movernos a dicho encabezado.
Conocemos la función INDICE, donde le especificamos una matriz, y, le indicamos el numero de filas, y, columnas a movernos.
En una celda voy a escribir el signo igual, seguido de la función INDICE, y, abrimos un paréntesis.
=INDICE(
Como argumento matriz, seleccionamos todos los datos, incluido los encabezados.
=INDICE(E1:G21;
Como argumento, numero de fila, y, numero de columna, ponemos 1.
Cerramos paréntesis, y, aceptamos.
=INDICE(E1:G21;1;1)
Vemos que nos devuelve el encabezado de provincia, como en argumento numero de fila, y, numero de columna, hemos especificado 1, se ha quedado en la primera celda.
Por supuesto, esto no es dinámico, porque hemos especificado como argumentos, constantes, y, queremos que sea dinámico.
Vamos a cambiar el argumento, numero de columna, de la función INDICE, por la función COINCIDIR, que hemos usado anteriormente.
=INDICE(E1:G21;1;COINCIDIR(B2;E1:G1;0))
Obtenemos el encabezado, que hemos seleccionado, ahora, si es dinámico, pero queremos rescatar el centro, no el encabezado, por lo que vamos a cambiar en el argumento matriz, de la función INDICE, y, en vez de comenzar en E1, que comience en E2, es decir, en el primer elemento.
=INDICE(E2:G21;1;COINCIDIR(B2;E1:G1;0))
Ahora, en este caso, nos devuelve el centro, pero solo, vemos el primer centro, y, deberíamos de ver todos los centros.
¿Cómo lo hacemos?
¿Podríamos arrastrar?, si, una vez fijada las celdas que correspondan, podemos arrastrar, la pregunta es, ¿Hasta donde tenemos que arrastrar?, porque los datos son dinámicas, quiere decir, que puede haber mas o menos centros, por lo que descartaría la idea de arrastrar.
Podíamos convertir el resultado de la función INDICE, en un resultado matricial, es decir, en una matriz desbordada, una matriz desbordada, es aquella formula, o, función, que devuelve más de un valor, y, que podemos usar en gran cantidad de funciones.
En la función INDICE, hemos puesto el valor 1, en el argumento numero de fila, pero este argumento, lo podemos omitir, es decir, no poner ningún valor, entonces la función INDICE, se va a convertir en matricial, porque no tiene un fin, el fin es la celda G21, especificada en el argumento matriz, para ignorar el argumento, numero de fila, simplemente borramos el valor 1, pero no el punto y coma, de la siguiente manera.
=INDICE(E2:G21;1;COINCIDIR(B2;E1:G1;0))
Vemos como resultado una matriz desbordada, con un alto de 20 filas, que son las filas que ocupa la columna de provincia, incluido el encabezado.
¿Qué problema vemos?
Pues, que la longitud de la columna es de 20 filas, pero la columna centro no tiene 20 filas, tiene algo menos, y, las no coincidencias, aparecen como ceros, y, no quiero que aparezca nada.
Lo cual estoy obligado a preguntar, para preguntar usare el condicional SI, pero vamos a preguntar por el argumento matriz de la función INDICE, es decir, por el rango E2:G21.
Después de la apertura del paréntesis de la función INDICE.
Ponemos el condicional SI, y, abrimos un paréntesis.
=INDICE(SI(
Vamos a preguntar, que si en el rango C2:G21, hay un blanco, sería el argumento prueba lógica, del condicional SI.
=INDICE(SI(E2:G21="";
Como argumento, valor si verdadero, del condicional SI, le vamos a decir, que ponga un blanco, es decir, si algunas de las celdas comprendidas en el rango E2:G21, es igual a blanco, pues, pones un blanco.
=INDICE(SI(E2:G21="";"";E2:G21);
En caso de no haber coincidencia, seria el argumento valor si falso, del condicional SI, en ese caso, que nos devuelva el valor comprendido en el rango C2:G21.
=INDICE(SI(E2:G21="";"";E2:G21
Cerramos el paréntesis del condicional SI.
=INDICE(SI(E2:G21="";"";E2:G21)
El resto de la expresión es como la teníamos, es decir, como argumento numero de fila de INDICE, no hemos puesto nada, y, como argumento numero de columna, pues, la función COINCIDIR.
Cerramos paréntesis y aceptamos.
=INDICE(SI(E2:G21="";"";E2:G21);;COINCIDIR(B2;E1:G1;0))
Vemos que ya no aparecen los ceros, se rellena con los valores que corresponda al encabezado.
Si elegimos, por ejemplo, producto, veremos como tenemos los productos solamente.
Todo lo que hemos hecho, es correcto, y, hemos aprendido a crear una lista de elementos, que dependen de un encabezado, pero, queríamos un desplegable, no una lista, resulta que la función INDICE, no la puedo usar dentro de una validación de datos, pero la función DESREF, si la podemos usar, pues, vamos con ella.
Usaremos la expresión anterior de la función COINCIDIR para encontrar el encabezado.
=COINCIDIR(I2;E1:G1;0)
Lo siguiente es movernos, a dicho encabezado, sabemos que la a la función DESREF, debemos de indicar un punto de partida, y, numero de filas y columna a movernos, además, tiene otros dos argumentos, que son alto y ancho.
Nosotros vamos a partir de la celda E1.
Nos vamos a mover una fila, que sería el argumento filas, y, una columna, que es el argumento columnas.
=DESREF(E1;1;1)
Vemos que nos devuelve el centro comercial Nervión.
Para hacerlo dinámico, vamos a sustituir el argumento numero de columnas de la función DESREF, por la función COINCIDIR.
=DESREF(E1;1;COINCIDIR(I2;E1:G1;0))
Obtenemos aspiradora.
¿Es correcto?
El resultado es correcto, pero no es lo que queremos, porque debería de haber devuelto centro comercial Nervión.
Veamos de donde sale este resultado.
Traduciendo la función DESREF anterior, teniendo seleccionado el encabezado de centro, los valores de los argumentos serian:
=DESREF(E1; 1; 2)
La función DESREF, cuenta un elemento cero, es decir, que el conteo de encabezados para la función DESREF, sería el siguiente:
Como la función COINCIDIR, nos ha devuelto el valor 2, nos ha devuelto el producto, por eso he dicho anteriormente, que el resultado es correcto, para solventar nuestro problema, a la función COINCIDIR, le vamos a restar 1.
=DESREF(E1;1;COINCIDIR(I2;E1:G1;0)-1)
Ahora, tenemos el resultado correcto.
Lo siguiente es decirle cuantas filas debe de tomar, y, cada columna tiene un numero de filas diferente, este seria el argumento alto de la función DESREF.
Por ejemplo, vamos a poner como argumento alto, el valor de 15.
=DESREF(E1;1;COINCIDIR(I2;E1:G1;0)-1;20)
Pues, tenemos el mismo problema que antes, que donde no hay coincidencias, nos poner el valor de cero, y, si creamos una lista desplegable, esos valores no deseados, aparecerán.
La solución del problema es especificar el número de celdas a tomar en cada ocasión.
Y, si, ¿envuelvo la función DESREF, en la función CONTARA?
Deberíamos de contarnos, solo, las celdas ocupadas.
Probemos.
=CONTARA(DESREF(E1;1;COINCIDIR(I2;E1:G1;0)-1;20))
Vemos que nos devuelve 6, que son los centros comerciales, que hay.
Si elijo producto, vemos que nos devuelve 5, que son los productos que hay.
Esto quiere decir, que ya tengo el argumento alto de la función DESREF, pues, copiemos y lo pegamos en el argumento alto de la función DESREF.
Como argumento ancho, ponemos 1, para que se quede en la misma columna.
=DESREF(E1;1;COINCIDIR(I2;E1:G1;0)-1;CONTARA(DESREF(E1;1;COINCIDIR(I2;E1:G1;0)-1;20));1)
Aceptamos, y, podemos ver nuestra lista, solo con los elementos que la componen.
Pues en la celda J2, vamos a crear nuestra segunda lista desplegable, pero, primero copiamos la función, seleccionado, y, pulsando CTRL más C.
Dentro de la ventana de validación, en la ventana de origen, pegamos la función, con CTRL más V.
Aceptamos.
Vemos como se ha creado nuestra segunda lista desplegable, sin errores, si probamos, veremos como se rellana, únicamente, con los elementos del encabezado seleccionado.
Terminamos aquí, nuestro ejemplo.
Comments