Ejemplos1

Tenemos el siguiente modelo de datos, con el que suelo trabajar, ventas de teléfonos en diferentes países.

El listado es algo más grande.

Vamos a crear una lista de países únicos ordenados, una lista de operadores también únicos, y, ordenados, después, vamos a calcular la cantidad vendida por cada país, y, después, la cantidad vendida por cada país y operador.

Lo primero, para conseguir los países únicos, vamos a usar la función UNICOS y ORDENAR, de la siguiente manera:

=ORDENAR(UNICOS(País))

El resultado:

Lo siguiente va a ser calcular la cantidad vendida por cada país, lo haremos con la función SUMAR.SI, pero de forma matricial.

En la barra de fórmulas, escribimos el signo igual, a continuación, escribirnos SUMAR.SI(, como rango de criterios, será la columna de país, como criterio, será, la columna de países únicos creadas ya no será una celda, y, como rango de suma va a ser el nombre de rango cantidad.

La función seria:

=SUMAR.SI(País;J2#;Cantidad)

Vemos como se usa el operador de rango derramado, J2#, quiere decir que seleccionamos el valor de la celda J2, y, todas las celdas hacia abajo que estén ocupadas.

Aceptamos, y, ya tenemos el resultado.

Si lo hiciéramos de forma no matricial, deberíamos de escribir la siguiente formula:

=SUMAR.SI(País;J2;Cantidad)

Fijamos J2, de forma absoluta, o sea, $J$2, y, copiamos hacia abajo.

Vemos que damos más de un paso, a diferencia, de la matriz derramada.

Lo siguiente que vamos a hacer, es crear un cuadro, donde en vertical están los países, y, en horizontal, los operadores.

En la celda N2, con la función INDICE, vamos a extraer cada país, de la columna J, donde están los países únicos, vamos a ver el uso de la función INDICE, para crearla de forma dinámica, también, podríamos rellenar la matriz como lo hemos hecho al principio, con la función UNICOS y ORDENAR, pero se trata de ver el uso de la función INDICE para que como resultado obtengamos una matriz derramada.

Si quiero extraer el primer país, usaría la siguiente sintaxis, donde como argumento array, seleccionamos la celda J2, seguido del operador de rango derramado (#), como argumento número de fila, ponemos 1, y, como argumento número de columna, también, ponemos 1, aunque, si omitimos el argumento número de columna, de forma predeterminada, pondrá 1.

=INDICE(J2#;1;1)

Pero, queremos que genere toda la lista de países con la función INDICE.

Entonces, debemos de modificar la sintaxis anterior de la siguiente manera. ¿de cuántos valores va a estar formada?, esos valores lo vamos a sacar con la función CONTARA, que nos devuelve un recuento de celdas alfanuméricas dentro de un rango, entonces, si ponemos la siguiente sintaxis, CONTARA(J2#), nos devolverá el número de países que hay, en este caso, 24.

Este va a ser el argumento fila de la función SECUENCIA, en la función SECUENCIA, como argumento filas, usaremos la función CONTARA, como argumento columnas, usaremos el valor 1.

=INDICE(J2#;SECUENCIA(CONTARA(J2#));1)

Como resultado, tenemos la misma lista de países únicos.

Lo siguiente, va a ser poner de forma horizontal los operadores, para ello, en la celda N2, con la función UNICOS, voy a traerme los operadores únicos.

= UNICOS(Operador)

Estos operadores aparecen en vertical, pero lo queremos en horizontal, por lo que usamos la función TRANSPONER, que invierte un rango de vertical a horizontal, o, viceversa, lo haremos de la siguiente manera:

=TRANSPONER(UNICOS(Operador))

Ya tenemos creado el cuadro, lo siguiente va a ser a través de la función SUMAR.SI.CONJUNTO, calcular las cantidades vendidas por cada país y operador, donde como argumento rango de suma, usamos la columna cantidad, como rango de criterios1, en la columna de país, como criterio1, es a partir de la celda J2 junto con el operador de rango derramado, como rango de criterios2, es la columna de operador, y, como criterios2, es a partir de la celda N2, junto con el operador de rango derramado, que es donde están los operadores, la sintaxis seria:

=TRANSPONER(UNICOS(Tabla42[Operador]))

Ya tenemos lo que estábamos buscando, damos por terminado este ejemplo.

Seguimos con el modelo de datos de la tabla promoción.

Vemos que en el modelo hay unas fechas de inicio de promoción.

Bien, pues, queremos crear en una hoja nueva, que aparezcan los nombres de las promociones, que aparezcan las fechas de inicio de promoción, y, que aparezca un símbolo cuando coincida una promoción con una fecha de inicio de promoción.

Pues empecemos.

Abrimos una hoja nueva, que la llamare Nº promociones.

En esa hoja, primero, nos vamos a llevar los valores únicos de los nombres de promociones en la celda A3.

La sintaxis seria:

=UNICOS(Promocion!A2:A29)

El resultado:

Pero estos nombres lo queremos en horizontal, por lo que, en otra celda, escribimos lo siguiente:

=TRANSPONER(UNICOS(Promocion!A2:A29))

Y como estaba en vertical, lo pone en horizontal.

Lo siguiente, va a ser seleccionar los nombres de las promociones en horizontal, pulsamos <CTRL> <C> para copiar y pegamos como valores, para que no dependa de un origen.

Seleccionamos los nombres de promociones, y, vamos a girarlos 90 grados, para que queden en vertical, pulsamos <CTRL> <1>, para acceder a formato de celdas.

En la ventana que se abre, seleccionamos Alineación.

Vemos una especia de reloj a la derecha.

Vemos que ahora el giro es de 0 grados.

Pues movemos la aguja hacia arriba, es decir, giramos 90 grados.

Aceptamos.

Seleccionaos las columnas donde están los nombres de las promociones, y, autoajustamos.

En la celda A4, voy a poner los meses, pero sin repetir, y, ordenamos, para ello, uso la sintaxis:

=ORDENAR(UNICOS(Promocion!$E$2:$E$29))

Voy a dar formato tanto a los nombres de promociones, como a las fechas de inicio de promoción.

Voy a poner nombre a las fechas de inicio de promoción, para ello, en la celda A3, voy a escribir Fecha inicio de promoción, copio el formato de los meses, y, lo pego a esta celda.

Ahora, queremos que aparezca un símbolo cuando una fecha de inicio de promoción, y, un nombre de promoción, coincidan.

En este caso, voy a usar la función CONTAR.SI.CONJUNTO, porque son dos criterios a tener en cuenta, la usaremos de forma matricial, ponemos CONTAR.SI.CONJUNTO, como argumento rango criterios1, vamos a la hoja de promociones, y, seleccionamos el rango A2:A29, como argumento criterio1, seleccionamos el rango B3:K3, como argumento rango criterios2, volvemos a la hoja de promociones, y, seleccionaos el rango E2:E29, como argumento criterio2, seleccionamos la celda A4 junto con el operador de rango derramado.

=CONTAR.SI.CONJUNTO(Promocion!A2:A29;B3:K3;Promocion!E2:E29;A4#)

Tenemos un recuento, cuando aparece 1, quiere decir que la fecha de inicio de promoción coincide con una promoción.

Queremos que aparezca un símbolo, que elegiremos, y que cuando sea cero no aparezca nada.

Para ello, delante de la función CONTAR.SI. CONJUNTO, vamos a usar el condicional SI, donde preguntamos que si el resultado de la función CONTAR.SI. CONJUNTO es igual a 1, que ponga la letra u, entre comillas dobles, en caso contrario, que ponga blanco.

=SI(CONTAR.SI.CONJUNTO(Promocion!A2:A29;B3:K3;Promocion!E2:E29;A4#)=1;"u";"")

Estamos la fuente Arial, pero hay un tipo de fuente que son símbolos, y, a esos símbolos le corresponde una letra.

Si vemos el resultado del modelo:

Vemos que donde hay coincidencia, pone la letra “u”, pero vamos a cambiar la fuente a Windings 3, y, veremos que esa letra corresponde a un símbolo, que es el que quiero usar.

Es decir que la letra “u” en windings 3, corresponde al símbolo u.

Lo siguiente va a ser aplicar un formato condicional a aquellas celdas que no estén en blanco, para ello, seleccionamos el rango de celdas, vamos a la ficha inicio, desplegamos formato condicional, y, desplegamos Reglas para resaltar celdas, y, elegimos Es igual a.

En la ventana que se abre, ponemos la letra u.

Desplegamos el menú con, y, voy a elegir in formato verde.

Aceptamos, y, vemos como queda.

Lo siguiente es parte de lo mismo, pero en vez de que aparezcan todos los nombres de promoción, y, fecha de inicio de promoción, vamos a crear una lista desplegable con los nombres de las promociones, y, que cuando elijamos un nombre de promoción, aparezca el símbolo en las fechas que corresponda con esa promoción.

Lo primero que voy a hacer es copiar el formato de la celda A3 en la celda N3, para ello, selecciono la celda A3, y, hago clic en la pestaña inicio, y, copiar formato.

El resultado:

También, voy a copiar el formato de la celda B3 en O3.

Es en esta celda, donde vamos a crear nuestra lista desplegable, con los nombres de las promociones, para ello, vamos a la pestaña Datos, hacemos clic en validación de datos, en la ventana que se abre, en Permitir elegimos Lista.

En origen, seleccionamos los datos que van a componer la lista.

Que son los nombres de las promociones.

Aceptamos.

Ya tenemos echa la lista.

Lo siguiente va a ser colocar el símbolo en las fechas donde coincida con el nombre de la promoción, la formula a usar es la misma que la anterior.

=SI(CONTAR.SI.CONJUNTO(Promocion!A2:A29;N3;Promocion!E2:E29;A4#);"u";"")

La diferencia es que en vez de buscar en valor de B3, va a ser el valor de N3.

Si elegimos un nombre de una promoción, veremos cómo aparece el símbolo en las fechas que corresponden con esa promoción.

Ahora, en dos columnas al lado, una llamada numero de promociones máxima, y, otra llamada numero de promociones seleccionadas, vamos a establecer una formula, que nos diga el numero total de promociones, y, las promociones seleccionadas, para ello, en la celda Q4, usamos la funcion CONTARA, como argumento nos traemos los valores únicos del rango E2:E29 de la hoja promoción.

=CONTARA(UNICOS(Promocion!E2:E29))

Vemos que primero extraemos los valores únicos, y, después con CONTARA contamos dichos valores, en este caso, el resultado es 22.

En la celda R4, vamos a contar los caracteres que son iguales a la letra “u” en el rango N4#.

Para ello, usamos el condicional SI, preguntamos que, si algún valor del rango N4# es igual a la letra “u”, que nos devuelva 1, en caso contrario, que devuelva 0.

= SI(N4#="u";1;0)

Después del signo igual, usamos la funcion SUMA.

=SUMA(SI(N4#="u";1;0))

En este caso, nos devuelve 3.

Lo siguiente va a ser crear un grafico de columnas 2D, para ello, vamos a la pestaña insertar, en el grupo gráfico, elegimos columna de barras 2D, los datos vamos a seleccionarlos ahora.

En la pestaña diseño de gráfico, elegimos seleccionar datos.

Se abre la siguiente ventana.

Clic en agregar, y, se abrirá una nueva ventana.

En valores de la serie, elegimos el primer valor, es decir, el valor de la celda R4, y, aceptamos.

Hacemos de nuevo en agregar, y, elegimos le valor de la celda S4, y, aceptamos, tenemos el siguiente gráfico.

Tenemos el siguiente gráfico.

Quitamos título, líneas de división, ejes, y, borde.

Ahora, botón alternativo de raton sobre una de las columnas, y, elegimos dar formato a serie de datos, se abre una ventana a la derecha.

En superposición de series, ponemos 100, que quiere decir, que una columna se ponga encima de la otra, quedando de la siguiente manera:

Clic con botón alternativo de raton, sobre una columna, y, de nuevo clic, para que quede seleccionada esa columna.

En la ventana de la derecha, hacemos clic en el siguiente icono.

En relleno, marcamos relleno sólido, en mi caso le voy a poner un color verde claro.

Clic en la columna de color naranja, y, le voy a poner un color verde oscuro.

Estrecho un poco el grafico, y, así quedaría.

Si en la lista desplegable, elegimos No Discount, veremos como la columna inferior, la de color verde oscuro, reduce su tamaño.

Con esto, ya hemos terminado nuestro ejercicio.

Ya hemos visto cómo podemos crear una lista desplegable a través de validación de datos.

Pero, tenemos el inconveniente de que, si tenemos muchos nombres, y, queremos buscar uno de ellos, debemos de ir buscando uno a uno.

Pues, vamos a ver cómo podemos crear una lista de validación de datos, que se rellene con los datos que pongamos.

Trabajamos con el modelo que suelo trabajar habitualmente.

Pues, queremos crear una validación de datos para poder elegir una provincia.

Lo primero que vamos a hacer es crear una lista única de provincias, para ello, en la celda J2, voy a hacer uso de la función matricial UNICOS, que nos devuelve solo valores únicos, en este caso, queremos las provincias.

=UNICOS(C2:C1477)

Lo siguiente que voy a hacer es ordenar los datos, con la función matricial ordenar, dejando los argumentos con su valor predeterminado.

=ORDENAR(UNICOS(C2:C1477))

Ya tenemos nuestra lista de provincias únicas, ordenadas.

En la celda K2, es donde vamos a crear nuestra lista desplegable, pero, antes vamos a crear las funciones que necesitamos, las cuales las vamos a hacer de dos formas diferentes.

Lo vamos a hacer, por ejemplo, por la palabra “al”, la cual nos debe de devolver las provincias donde aparecen la palabra “al”, y, que no tiene que ser al principio.

Vamos a usar la función HALLAR, que no distingue entre mayúsculas, y, minúsculas, para buscar la posición de la palabra buscada.

=HALLAR(K2;J2:J21)

Obtenemos la posición de donde aparece la primera letra de la palabra buscada.

Lo siguiente va a ser preguntar si el resultado de la función hallar es número, eso lo haremos con la función ESNUMERO, que nos devuelve verdadero si es número, y falso si no lo es.

=ESNUMERO(HALLAR(K2;J2:J21))

Ahora, con el condicional SI vamos a preguntar que si el resultado devuelto por ESNUMERO es verdadero, pues que nos de la misma posición pero de la columna de las provincias, en caso contrario, que nos devuelva un texto en blanco.

=SI(ESNUMERO(HALLAR(K2;J2:J21));J2:J21;"")

Aparecen las provincias que contiene la palabra por la que buscamos, ahora, para que aparezcan todas las provincias juntas, usamos la función matricial ORDENAR, donde el argumento ordenar índice, lo vamos a saltar, y, el siguiente argumento que es tipo de ordenación, elegimos descendente.

=ORDENAR(SI(ESNUMERO(HALLAR(K2;J2:J21));J2:J21;"");;-1)

Aceptamos, y, vemos las provincias ordenadas de manera descendente que coinciden con la palabra buscada.

Lo siguiente es crear nuestra lista desplegable, pues, vamos a validación de datos, desplegamos permitir y elegimos lista.

En la ventana de origen, señalamos la celda donde tenemos la primera provincia obtenida seguido del operador de rango derramado (#).

Aceptamos.

Si probamos veremos como en la validación solo aparecen las provincias con la palabra buscada.

El problema, que tenemos aquí es que como tenemos una ordenación en descendente, si no ponemos ninguna provincia, aparecen todas las provincias, pero ordenadas de mayor a menor.

Pues, este error lo vamos a corregir haciéndolo de una segunda manera.

La parte de hallar y es número, es la misma que para el ejemplo anterior.

=ESNUMERO(HALLAR(K2;J2:J21))

En este caso, usamos la función matricial filtrar, donde el argumento include, va a ser la función anterior, es decir, nos va a filtrar todas las provincias que sean verdaderas.

=FILTRAR(J2:J21;ESNUMERO(HALLAR(K2;J2:J21)))

Aceptamos, y, tenemos el mismo resultado que el anterior, y, si no ponemos ninguna provincia podemos ver como ahora aparecen las provincias bien ordenadas.

Vamos a editar la lista desplegable, y, ponemos la nueva celda en la ventana de origen.

Ya sabemos que podemos usar el operador de rango derramado para seleccionar una matriz, pues, vamos a usar el operador de rango derramado, para crear dos listas desplegables, a través, de validación de datos.

Para este ejemplo, seguimos trabajando con el modelo donde tenemos la venta de productos en diferentes centros comerciales, en diferentes ciudades y fechas.

Con el modelo que vamos a trabajar, recomiendo tenerlo en formato de tabla, ya que, si agregamos, o, eliminamos datos, se hará de forma automática.

El ejemplo va a consistir en que, en una lista desplegable, vamos a seleccionar una provincia, en una segunda lista desplegable, van a aparecer los años donde esta provincia ha vendido, y, vamos a crear una matriz desbordada, donde vamos a ver los productos vendidos por esta provincia en el año seleccionado.

Lo primero que vamos a hacer es añadir una hoja nueva.

En esta hoja nueva, a partir de la celda A2, vamos a usar la función UNICOS, para crear una lista de provincias únicas.

=UNICOS(País)

Usamos la función matricial ORDENAR, que nos permite ordenar una matriz, esta función tiene varios argumentos, pero exceptuando el primer argumento, los demás son opcionales.

Aceptamos.

=ORDENAR(UNICOS(País))

Ahora, en la hoja donde tenemos el modelo de datos, en la celda J2, vamos a crear la primera lista desplegable, que será rellenado con estas provincias únicos.

Para ello, vamos a la pestaña datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.

En la ventana que se abre, desplegamos permitir, y, elegimos lista.

En la ventana origen, ponemos la celda A2 de la hoja nueva, seguido del operador de rango derramado, esto quiere decir, que va a seleccionar la matriz completa.

Aceptamos.

Si desplegamos nuestra lista desplegable, veremos todas las provincias.

Si añadimos una nueva provincia, veremos como aparece de forma automática en nuestra lista desplegable.

Seguimos, vamos por la lista desplegable, que se debe de rellenar con las fechas de ventas de la provincia seleccionada.

En esta hoja nueva, en una nueva columna, vamos a usar la función matricial FILTRAR, para filtrar las fechas por la provincia seleccionada.

Para ello, en la hoja2, en la celda C2, escribimos el signo igual seguido de la función FILTRAR.

=FILTRAR(

Seleccionamos la columna de fechas, es que la que queremos filtrar, siendo el argumento array.

=FILTRAR(Tabla2[Fecha]

Punto y coma, seleccionamos la columna de provincias seguido del signo igual, y, seleccionamos la provincia que hemos seleccionado en la validación de datos, es el argumento include, cerramos paréntesis, y, aceptamos.

=FILTRAR(Tabla2[Fecha];País=DATOS!J2)

Pero, queremos los años, por lo que después del signo igual, uso la función AÑO, que nos devuelve el año de la fecha especificada.

=AÑO(FILTRAR(Tabla2[Fecha];País=DATOS!J2))

Pero, vemos que los años se repiten, y, queremos solo valores únicos, por lo que, de nuevo después del signo igual, usamos la función únicos.

=UNICOS(AÑO(FILTRAR(Tabla2[Fecha];País=DATOS!J2)))

Ya tenemos los años únicos de ventas de la provincia seleccionada.

Pues, vamos a crear esta segunda lista desplegable.

Lo hacemos en la celda K2 de la hoja donde tenemos el modelo de datos.

Insertamos una lista desplegable, dentro de la ventana de validación de datos, desplegamos permitir, y, seleccionamos lista, y, en la pantalla origen, señalamos la celda C2, de la hoja nueva, seguido del símbolo de operador de rango derramado (#).

Aceptamos.

Si seleccionamos una provincia, y, desplegamos la segunda lista desplegable, veremos cómo se rellena con los años de ventas de esa provincia.

Lo siguiente es crear esa matriz desbordada, donde vamos a ver las ventas para el año y provincia seleccionada.

Lo vamos a hacer en la celda J4, y, volvemos a usar la función FILTRAR.

Escribo el signo igual seguido de la función FILTRAR.

=FILTRAR(

Solo queremos ver desde la columna centro comercial en adelante, por lo que como argumento array, seleccionamos dichas columnas.

=FILTRAR(Tabla2[[Centro]:[Total]]

Punto y coma, debemos de especificar dos criterios, por lo que abrimos un paréntesis, y, ponemos el primer criterio, que es que la columna país debe de ser igual al valor de la celda J2, cerramos paréntesis.

=FILTRAR(Tabla2[[Centro]:[Total]];(País=J2)

Escribimos el símbolo de asterisco para concatenar o unir la segunda condición, abrimos un nuevo paréntesis, y, ponemos el segundo criterio, que es que el año de la tabla2 debe de ser igual al valor de la celda K2, cerramos paréntesis, cerramos paréntesis de la función FILTRAR y aceptamos.

=FILTRAR(Tabla2[[Centro]:[Total]];(País=J2)*(Tabla2[Año]=K2))

Vemos esa matriz desbordada, con los datos para la provincia y el año seleccionado.

Si vamos cambiando los datos de las listas desplegables, veremos cómo se actualizan los datos.

Ahora, en la celda L2, donde tenemos el modelo, queremos ver el total de dicha matriz desbordada.

Para ello, copiamos la expresión anterior, excepto el signo igual, pegamos en la celda L2, pero, primero ponemos el signo igual, ponemos SUMA, pegamos la expresión, cerramos paréntesis, aceptamos, y, ya tenemos el total.

=SUMA(FILTRAR(Tabla2[Total];(País=J2)*(Tabla2[Año]=K2)))

Lo siguiente va a ser que debajo de la matriz desbordada, queremos ver los totales por centros comerciales, de los datos que vemos.

Para ello, en la celda J22, voy a extraer los valores únicos de la matriz desbordada.

=UNICOS(J4:J9)

Ahora, en la celda K22, usamos el condicional SI, como tenemos que trabajar con una matriz desbordada, que está compuesta de 5 columnas, debemos de comparar la primera columna con cada centro, vamos a usar la función INDICE, donde como argumento matriz, seleccionamos la celda J4 seguida del operador de rango derramado.

=SI(INDICE(J4#

Omitimos le argumento número de fila, por lo que ponemos punto y coma, volvemos a poner punto y coma, para pasar al argumento número de columna, donde ponemos 1, cerramos paréntesis.

=SI(INDICE(J4#;;1)

Lo igualamos al valor de la celda J22, donde tenemos el primer centro comercial.

=SUMA(SI(INDICE(J4#;;1)=J22

Este sería el argumento prueba lógica del condicional SI.

Punto y coma, como argumento valor si verdadero, debemos de usar de nuevo INDICE, en este caso, para rescatar la columna 5, que es donde tenemos los totales, pues volvemos a poner la función INDICE, donde como argumento matriz, volvemos a seleccionar la celda J4 seguida del operador de rango derramado.

=SI(INDICE(J4#;;1)=J22;INDICE(J4#

Punto y coma, como argumento número de fila, debemos de saber de cuantas filas consta el modelo, porque como hemos dicho este modelo puede ir cambiando, para ello, usamos SECUENCIA, como argumento filas, usamos la función CONTARA, para que nos dé un recuento de celdas alfanuméricas, pero, si ponemos la celda J4 seguida del operador de rango derramado, nos hará un recuento de todas las celdas, y, necesitamos las celdas ocupadas para cada fila, por lo que el resultado de CONTARA lo vamos a dividir entre el número de columna, de esta manera, sabemos las filas ocupadas en cada columna.

=SI(INDICE(J4#;;1)=J22;INDICE(J4#;SECUENCIA(CONTARA(J4#)/5)

Punto y coma, como argumento número de columna, ponemos 5.

Cerramos paréntesis de INDICE.

=SI(INDICE(J4#;;1)=J22;INDICE(J4#;SECUENCIA(CONTARA(J4#)/5);5)

Punto y coma, como argumento valor si falso del condicional SI, ponemos un texto en blanco.

Cerramos paréntesis.

=SI(INDICE(J4#;;1)=J22;INDICE(J4#;SECUENCIA(CONTARA(J4#)/5);5);"")

Como queremos el total, después del signo igual, usamos SUMA.

=SUMA(SI(INDICE(J4#;;1)=J22;INDICE(J4#;SECUENCIA(CONTARA(J4#)/5);5);""))

Aceptamos, y, tenemos el total para el primer centro.

Lo siguiente es arrastrar, pero antes, debemos de filar J4.

=SUMA(SI(INDICE($J$4#;;1)=J22;INDICE($J$4#;SECUENCIA(CONTARA($J$4#)/5);5);""))

Ya tenemos el total para cada centro comercial.

Por último, vamos a crear un formato condicional, donde vamos a aplicar una función, para que quede resaltada la fila de mayor valor de esta última matriz.

Seleccionamos el rango de celdas J22:K25.

Para ello, vamos a la pestaña de inicio, desplegamos formato condicional, y, seleccionamos nueva regla.

En la ventana que se abre, elegimos la última opción, que nos permite usar una función, donde vamos a usar el condicional SI donde comparamos el valor de cada celda con el valor máximo, y, si lo es que nos devuelva dicha celda, debemos de poner dólar ($) delante de cada columna, para que, en caso de haber coincidencia, quede resaltada ambas columnas.

Damos un formato.

Aceptamos, y, vemos resaltado la fila de mayor valor.

Con esto, terminamos nuestro ejemplo.

En este ejemplo, vamos a crear una lista desplegable a través de validación de datos con auto relleno, pero lo haremos de forma manual, porque aún no disponemos de la actualización que el auto relleno es automático.

Vamos a trabajar con el modelo de contoso.

Trabajaremos con los nombres de clientes, donde podemos ver que hay nombres que contienen la o las mismas letras.

Por último, veremos cómo podemos copiar esta lista desplegable.

En una hoja nueva, llamada resultados, vamos a crear estas listas desplegables, desde A2 a A9.

La idea es escribir una serie de caracteres, y, que nos devuelva los nombres de clientes que contengan dichos caracteres, sin importar en que posición se encuentren dentro del texto.

Lo primero que vamos a hacer es dar formato de tabla a la hoja de cliente, y, le cambiamos el nombre a la tabla.

Para ello, con una celda dentro del modelo, pulsamos CTRL más T, se abre la ventana de crear tabla, donde podemos ver como todo el modelo ha sido seleccionado, debemos de asegurarnos que este marcado la tabla tiene encabezados.

Aceptamos, vemos que se le da un formato al modelo de datos, y, se activan los filtros.

En la hoja de PREPARACION es donde vamos a hacer el filtrado por los caracteres que pongamos en cualquier parte del rango desde A2 a A9 de la hoja resultados.

Para que en la hoja preparación, aparezca el valor puesto desde A2 a A9, de la hoja resultados, voy a seleccionar desde A2 a A9 de la hoja resultados, pulso CTRL más C para copiar, selecciono desde A2 a A9 de la hoja preparación, botón alternativo del ratón y pegamos como un vínculo.

Vemos que donde no hay valores aparece cero, y, no queremos que aparezca, pues usamos el condicional SI.

=SI(resultados!A2=0;"";resultados!A2)

Arrastramos.

Por ejemplo, he puesto “um” en la celda A4, de la hoja resultados, da igual que este en mayúsculas o minúsculas, porque la función que vamos a usar no distingue entre mayúsculas y minúsculas.

La función que vamos a usar para encontrar la posición del primer carácter que hemos escrito es HALLAR, que no distingue entre mayúsculas y minúsculas.

En A2 de la hoja resultados, voy a escribir AD.

En la celda C2 de la hoja preparación, escribimos el signo igual, seguido de la función HALLAR, y, abrimos un paréntesis.

=HALLAR(

El primer argumento es texto buscado, pues seleccionamos la celda A2.

=HALLAR(A2

Punto y coma, el siguiente argumento es dentro del texto, pues seleccionamos la columna de clientes de la hoja clientes.

Cerramos paréntesis, y, aceptamos.

=HALLAR(A4;clientes[Nombre])

Como resultado tenemos la posición del primer carácter del texto buscado, y, donde no hay coincidencia tenemos un error.

El siguiente paso lo podemos hacer de diferentes maneras, tenemos que convertir en verdadero los valores numéricos, y, en falso los valores de error.

La primera forma que vamos a ver es con la función ESERROR.

Vamos a englobar la función ESERROR, dentro de la función HALLAR.

=ESERROR(HALLAR(A4;clientes[Nombre]))

Nos devuelve verdadero donde hay un error, y, falso donde no lo hay, en este caso, nos devuelve falso para los valores numéricos.

Pero necesito que verdadero sean los valores numéricos, y, no los valores de error.

Tenemos otra función que invierte estos resultados, es decir, los verdaderos los convierte en falsos, y, los falsos en verdadero, que es lo que necesitamos.

Esa función es la función NO, pues vamos a aplicarla.

=NO(ESERROR(HALLAR(A4;clientes[Nombre])))

Ahora, lo tenemos como queremos.

Lo siguiente es usar la función matricial FILTRAR, para que nos filtre los valores verdaderos.

Escribimos signo igual, seguido de la función FILTRAR, y, abrimos un paréntesis.

=FILTRAR(

El primer argumento es array, pues seleccionamos la columna de clientes.

=FILTRAR(clientes[Nombre]

Punto y coma, el siguiente argumento es include, es decir, la condición, pues la condición es la expresión anterior, y, por defecto, nos filtrara solo los valores verdaderos.

=FILTRAR(clientes[Nombre];NO(ESERROR(HALLAR(A4;clientes[Nombre]))))

Aceptamos, y, vemos un listado de nombres que contienen AD.

Ahora, vamos a ordenar los resultados, para ello, usamos la función ORDENAR con los argumentos por predeterminados.

=ORDENAR(FILTRAR(clientes[Nombre];ESNUMERO(HALLAR(A2;clientes[Nombre]));"No existe"))

Esta sería una forma.

Otra seria, usando ESNUMERO, antes de la función HALLAR.

=FILTRAR('Clientes'!C2:C18485;ESNUMERO(HALLAR(A4;'Clientes'!C2:C18485)))

De las dos maneras, tenemos los mismos resultados.

Lo siguiente va a ser crear la lista desplegable, lo haremos en A2 de la hoja resultados, que ya tenemos un valor.

Vamos a la pestaña de datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.

Se abre la ventana validación de datos.

Desplegamos permitir, y, elegimos lista.

En la ventana origen, como se trata de una matriz desbordada, ponemos la primera celda seguido del operador de rango derramado, para que nos seleccione hasta la última celda ocupada.

Si ponemos un valor no valido nos aparece el mensaje de que no cumple con las restricciones.

Pero no queremos ver este mensaje, si ponemos un valor inexistente, simplemente, que nos deje, para ello, donde hemos creado la lista desplegable, hacemos clic en validación de datos, vamos a la pestaña mensaje de error, y, desmarcamos mostrar mensaje de error si se introducen datos no válidos.

Si ahora introducimos datos inexistentes, veremos como ya no nos aparece ningún mensaje de error.

Pero, si probamos y ponemos cualesquiera caracteres, podemos ver como se rellena la lista desplegable con los nombres de clientes que contengan dichos caracteres.

Pero si ponemos un nombre que no existe en la hoja RESULTADOS, y, vamos a la hoja PREPRACION, podemos ver que nos devuelve el error. CALC.

Pues, vamos a rellenar el argumento si vacío de la función filtrar.

=ORDENAR(FILTRAR(clientes[Nombre];ESNUMERO(HALLAR(A2;clientes[Nombre]));"No existe"))

Con esto ya tenemos nuestra lista desplegable con auto relleno hecho.

Pero, tenemos el problema de que esta lista apunta a la celda A2, y, tenemos que copiarlo al resto de celdas, y, no vale copiar y pegar, si arrastramos una vez, veremos que se copia la validación de datos, pero no está rellena.

Voy a trasponer la expresión de la celda C2 de la hoja PREPARACION.

=TRANSPONER(FILTRAR(clientes[Nombre];ESNUMERO(HALLAR(A4;clientes[Nombre]));"No existe"))

Arrastramos hacia abajo.

Volvemos a la hoja RESULTADOS.

Arrastramos la validación hacia abajo.

Si probamos la validación de la celda A3 de la hoja RESULTADOS, veremos que ahora si esta rellena.

Vamos a ver porque se ha rellenado.

En la hoja RESULTADOS, tengo valores en A2, y, en A3.

Seleccionamos la celda A2, vamos a la pestaña datos, y, hacemos clic en validación de datos.

Podemos ver en la ventana origen preparación!$C2#, recordar que antes hemos transpuesto, por lo que los datos se encuentran en horizontal, quiere decir que ha rellenado la lista desplegable con los datos empezando en C2 hasta C y la última celda ocupada.

Esto quiere decir que es correcto.

Ahora, seleccionamos la celda A3, y, volvemos a validación de datos.

Vemos que en la ventana origen, ahora, pone C3, porque solo está fijada la columna, esto quiere decir, que, al copiar la validación hacia abajo, la fila se ha actualizado, y, se ha rellenado con los datos de la celda C3 de la hoja PREPARACION, es decir, que se ha rellenado correctamente.

Pues arrastramos hasta la última celda.

Si vamos a la hoja PREPARACION, podemos ver que donde no hay datos en las celdas A2:A9, nos da un error de desbordamiento, pues lo vamos a arreglar con el condicional SI.

Vamos a preguntar que, si el valor de la celda A2 es igual a blanco, o sea, a nada, que ponga un texto en blanco, en caso contrario, que ejecute la función.

=SI(A2="";"";TRANSPONER(FILTRAR(clientes[Nombre];ESNUMERO(HALLAR(A2;clientes[Nombre]));"No existe")))

Arrastramos.

Miguel Angel Franco