Para el siguiente ejemplo, vamos a trabajar con el modelo que suelo usar habitualmente.
Vamos a buscar un centro, por ejemplo, en la columna centro, tenemos El corte inglés, pero debemos de buscarlo, por el siguiente nombre “elc”.
Vemos que en la columna centro, tenemos El corte inglés, donde vemos que hay dos espacios en blanco, uno entre la palabra El y la palabra corte, y, otro entre la palabra corte y la palabra inglés, y, nosotros buscamos elc, donde no hay separación, y, la primera letra está en minúsculas.
Empecemos…
En la celda I6, voy a traerme la columna centro.
=Ventas[Centro]
Voy a usar la SUSTITUIR, para sustituir el espacio en blanco de la columna centro por nada, el primer argumento es texto, que es la columna centro.
=SUSTITUIR(Ventas[Centro]
Punto y coma, el siguiente argumento es texto original, ponemos comillas dobles, dejamos un espacio, y, cerramos comillas dobles.
=SUSTITUIR(Ventas[Centro];" "
Punto y coma, el siguiente argumento es texto nuevo, pues, ponemos comillas dobles, que es nada.
Cerramos paréntesis.
=SUSTITUIR(Ventas[Centro];" ";"")
Que ocurre, si una palabra no tiene espacios en blanco, pues, que omite la regla de SUSTITUIR, y, nos deja el texto como este.
Ahora, vamos a extraer los caracteres que indica la celda I2, que es el centro para buscar.
=IZQUIERDA(SUSTITUIR(Ventas[Centro];" ";"");LARGO(I2))
Vemos que ya aparece en el listado, el centro a buscar.
Pero, la primera letra en la columna centro sigue estando en mayúsculas.
Filtramos la tabla ventas, donde como argumento array de la función FILTRAR es la tabla ventas.
=FILTRAR(Ventas
Punto y coma, como argumento include, es la expresión y la igualamos al valor de la celda I2.
Cerramos paréntesis.
=FILTRAR(Ventas3;IZQUIERDA(SUSTITUIR(Ventas3[Centro];" ";"");LARGO(I2))=I2)
Pues, ya lo tenemos.
Pero, seguro que ya lo tenemos.
Voy a cambiar el valor de la celda I2, por “el c”.
Este error es debido a que estamos buscando un nombre, o, las iniciales que no tienen espacios, para solventarlo, debemos de usar de nuevo la función SUSTITUIR en la celda I2 dentro de la expresión, para trabajar siempre sin espacios.
FILTRAR(Ventas;IZQUIERDA(SUSTITUIR(Ventas[Centro];" ";"");LARGO(SUSTITUIR(I2;" ";"")))=SUSTITUIR(I2;" ";""))
Aceptamos, y, ya puede tener los espacios que sean, que serán sustituidos por nada.
Si probamos a poner las iniciales de otro centro, veremos que funciona correctamente.
Por último, nos vamos a traer los encabezados, para ello, vamos a usar la función APILARV (VSTACK), donde como argumento matriz1, son los encabezados, y, como argumento matriz2, es la expresión anterior.
=APILARV(Ventas[#Encabezados];FILTRAR(Ventas;IZQUIERDA(SUSTITUIR(Ventas[Centro];" ";"");LARGO(SUSTITUIR(I2;" ";"")))=SUSTITUIR(I2;" ";"")))
Ahora, queremos buscar un centro que comience por ca y acabe por four.
Obtener la parte izquierda es igual que antes, vamos a dividir el texto con la función DIVIDIRTEXTO.
=DIVIDIRTEXTO(I2;" ")
Con la función INDICE, vamos a quedarnos con la columna 1.
=INDICE(DIVIDIRTEXTO(I2;" ");;1)
Pero, que ocurre si tenemos, por ejemplo, el corte inglés, para obtener la primera parte, no hay problema, porque solicitamos la primera columna, pero vamos a buscar “el” como comienzo, y, “les” como final, si con la función INDICE, solicitamos la segunda columna, obtendremos “corte”, que no es la columna que necesitamos.
Pues, vamos a ver como podemos calcular el numero de columna que necesitamos.
Con la función SUSTITUIR, voy a sustituir los espacios en blanco de el corte inglés por nada.
Con la función LARGO, obtenemos la longitud de la cadena.
En la celda inferior, con la función LARGO voy a calcular la longitud de el corte inglés.
Resto el valor mayor menos el valor menor.
Vemos que tenemos dos espacios en blanco, que equivale a tres columnas, por lo que a la resta debemos de sumar 1.
Es decir, para este caso, debemos de quedarnos con la columna 3.
La sintaxis quedaría:
=LARGO(K2)-LARGO(SUSTITUIR(I2;" ";""))+1
Bien, pues esta es la expresión para usar cuando solicitemos la última columna.
Igual que antes, usamos la función FILTRAR, donde como argumento array, seleccionamos la tabla ventas.
=FILTRAR(Ventas2
Punto y coma, como argumento include, extraemos la longitud de los primeros caracteres a buscar en la columna centro, y, la igualamos a la primera parte del nombre a buscar.
=FILTRAR(Ventas2;IZQUIERDA(Ventas2[Centro];LARGO(INDICE(DIVIDIRTEXTO(I2;" ");;1)))=INDICE(DIVIDIRTEXTO(I2;" ");;1))
Aceptamos, y, tenemos igual que antes, nos falta la parte final a buscar.
Lo haremos en una celda aparte, y, después la unimos a la expresión anterior.
Con la siguiente expresión, del texto a buscar (I2), nos quedamos con la última parte a buscar.
=INDICE(DIVIDIRTEXTO(I2;" ");;2)
Dichos caracteres, debemos de buscarlo por la derecha en la columna centro.
Ya vimos anteriormente, como encontrar la última columna, pues, vamos a extraer por la derecha de la columna centro, los caracteres que nos devolvió la función que usamos para calcular la columna a extraer.
=DERECHA(Ventas2[Centro];LARGO(K2)-LARGO(SUSTITUIR(I2;" ";""))+1)
Vemos que tenemos, en este caso, los últimos tres caracteres de la ultima parte del nombre del centro.
Ahora, vamos a usar la función FILTRAR, pero con dos condiciones, la primera condición es comparar los primeros caracteres por la izquierda, y, la segunda condición es comparar los caracteres por la derecha.
Como argumento array es la tabla ventas.
=FILTRAR(Ventas2
Punto y coma, como argumento include, entre paréntesis, ponemos la condición que usamos anteriormente para comparar los caracteres por la izquierda.
=FILTRAR(Ventas;(IZQUIERDA(Ventas2[Centro];LARGO(INDICE(DIVIDIRTEXTO(I2;" ");;1)))=INDICE(DIVIDIRTEXTO(I2;" ");;1)))
Punto y coma, ponemos el asterisco (*), que es igual que el operador lógico Y, es decir, devuelve VERDADERO si todas las condiciones se cumplen, abrimos un paréntesis, la siguiente condición es extraer los caracteres por la derecha de la columna centro y compararlo con los últimos caracteres del valor buscado.
Cerramos paréntesis.
=FILTRAR(Ventas2;(IZQUIERDA(Ventas2[Centro];LARGO(INDICE(DIVIDIRTEXTO(I2;" ");;1)))=INDICE(DIVIDIRTEXTO(I2;" ");;1))*(DERECHA(Ventas2[Centro];LARGO(I2)-LARGO(SUSTITUIR(I2;" ";""))+1)=INDICE(DIVIDIRTEXTO(I2;" ");;2)))
Aceptamos.
Y vemos como tenemos filtrada la tabla por el corte inglés.
Pero si como palabra de comienzo ponemos “ca” y como palabra final ponemos “four”, es decir, debe de buscar Carrefour, vemos que nos devuelve un error.
El error lo tenemos en la segunda condición.
=FILTRAR(Ventas2;(IZQUIERDA(Ventas2[Centro];LARGO(INDICE(DIVIDIRTEXTO(I2;" ");;1)))=INDICE(DIVIDIRTEXTO(I2;" ");;1))*(DERECHA(Ventas2[Centro];LARGO(I2)-LARGO(SUSTITUIR(I2;" ";""))+1)=INDICE(DIVIDIRTEXTO(I2;" ");;2)))
Si sacamos fuera la siguiente expresión:
=LARGO(I2)-LARGO(SUSTITUIR(I2;" ";""))+1
Vemos que nos devuelve 2, quiere decir que de la columna centro, por la derecha, esta extrayendo dos caracteres, cuando deberían de ser 4 caracteres.
Voy a volver a dividir la palabra a buscar por delimitador.
=DIVIDIRTEXTO(I2;" ")
Voy a quedarme con la segunda columna.
=INDICE(DIVIDIRTEXTO(I2;" ");;2)
Con la función LARGO voy a obtener la longitud.
=LARGO(INDICE(DIVIDIRTEXTO(I2;" ");;2))
Obtenemos 4.
Vamos a la expresión FILTRAR, debemos de cambiar la siguiente expresión:
=FILTRAR(Ventas2;(IZQUIERDA(Ventas2[Centro];LARGO(INDICE(DIVIDIRTEXTO(I2;" ");;1)))=INDICE(DIVIDIRTEXTO(I2;" ");;1))*(DERECHA(Ventas2[Centro];LARGO(I2)-LARGO(SUSTITUIR(I2;" ";""))+1)=INDICE(DIVIDIRTEXTO(I2;" ");;2)))
Por la expresión anterior.
=FILTRAR(Ventas2;(IZQUIERDA(Ventas2[Centro];LARGO(INDICE(DIVIDIRTEXTO(I2;" ");;1)))=INDICE(DIVIDIRTEXTO(I2;" ");;1))*(DERECHA(Ventas2[Centro];LARGO(INDICE(DIVIDIRTEXTO(I2;" ");;2)))=INDICE(DIVIDIRTEXTO(I2;" ");;2)))
Vemos como ahora función correctamente.
Ahora, como palabra a buscar ponemos “me ar”, debe de devolvernos Metromar.
Funciona sin más problemas.
Igual que antes nos traemos el encabezado.
=APILARV(Ventas2[#Encabezados];FILTRAR(Ventas2;(IZQUIERDA(Ventas2[Centro];LARGO(INDICE(DIVIDIRTEXTO(I2;" ");;1)))=INDICE(DIVIDIRTEXTO(I2;" ");;1))*(DERECHA(Ventas2[Centro];LARGO(INDICE(DIVIDIRTEXTO(I2;" ");;2)))=INDICE(DIVIDIRTEXTO(I2;" ");;2))))
El siguiente ejemplo va a ser mas sencillo, debemos de filtrar el modelo por una provincia llamada “sevill”.
Voy a traer la longitud de caracteres del texto buscado de la columna provincia.
=IZQUIERDA(Ventas3[Provincia];LARGO(I2))
Ahora con la función FILTRAR, donde como argumento array es la tabla ventas3.
=FILTRAR(Ventas3
Punto y coma, como argumento include es la función IZQUIERDA y la igualamos al valor de la celda I2.
Cerramos paréntesis.
=FILTRAR(Ventas3;IZQUIERDA(Ventas26[Provincia];LARGO(I2))=I2)
Pues ya lo tenemos.
Si ponemos a buscar una provincia con cualquier numero de caracteres, vemos que nos devuelve la provincia.
Volvemos a traernos los encabezados.
=APILARV(Ventas3[#Encabezados];FILTRAR(Ventas3;IZQUIERDA(Ventas3[Provincia];LARGO(I2))=I2))
Miguel Angel Franco Garcia
Comentários