En el siguiente ejemplo, tenemos una serie de provincias, y, tenemos otro modelo, con las mismas provincias, junto con un guion bajo, y la palabra table numerada, junto con los totales.
Vamos a crear una validación de datos, para poder elegir una provincia, para ello, vamos a la pestaña de datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.
Desplegamos permitir, y, seleccionamos lista, y, en la ventana origen, seleccionamos las provincias.
Aceptamos, y, seleccionamos una provincia.
Lo vamos a ser de diferentes formas.
La primera de ellas, con la función LARGO, voy a calcular la longitud de la provincia seleccionada.
Vemos que nos devuelve una longitud de 7 caracteres, ahora, con la función IZQUIERDA, voy a extraer el resultado de la función LARGO de las provincias junto con la palabra table.
=IZQUIERDA(D2:D21;LARGO(G2))
En este caso, obtengo los primeros siete caracteres de cada provincia con la palabra table, vemos que las provincias que tienen siete caracteres aparecen completas, el resto de ellas no.
Pero, nosotros buscamos, en este caso, una provincia con una longitud de siete caracteres, que si la habrá dentro de las provincias junto con la palabra table, para ello, voy a usar el condicional SI, donde como argumento prueba lógica, pregunto que si la provincia seleccionada es igual a una de las provincias obtenidas en la expresión anterior.
=SI(G2=IZQUIERDA(D2:D21;LARGO(G2))
Como argumento valor si verdadero, que me devuelva los totales, en caso contrario, que ponga un texto en blanco, cerramos paréntesis, y, aceptamos.
=SI(G2=IZQUIERDA(D2:D21;LARGO(G2));E2:E21;"")
Obtenemos una matriz desbordada con blancos, y, donde ha habido coincidencia, el total.
Usamos la función CONCAT, que une cadenas, pero ignora las celdas vacías.
=CONCAT(SI(G2=IZQUIERDA(D2:D21;LARGO(G2));E2:E21;""))
Ya tenemos el total para la provincia seleccionada.
También, lo podríamos hacer con la función COINCIDIRX, junto con INDICE, para ello, en una celda, pongo la función COINCIDIRX, abro un paréntesis, y, como valor buscado, seleccionamos la provincia seleccionada, y, lo concatenamos con el símbolo de asterisco, que sustituirá todos los caracteres a partir de la última letra.
=COINCIDIRX(G2&"*"
Como argumento matriz de búsqueda, seleccionamos el rango D2:D21, que son las provincias.
=COINCIDIRX(G2&"*";D2:D21
Punto y coma, se abre una ventana donde tenemos que seleccionar el tipo de coincidencia, en este caso, seleccionamos coincidencia de caracteres comodín.
Cerramos paréntesis y aceptamos.
=COINCIDIRX(G2&"*";D2:D21;2)
Obtenemos la posición de la provincia seleccionada, que es la 16, ahora, con la función INDICE, y, como argumento matriz, seleccionamos los totales, como argumento numero de fila, es la función COINCIDIRX, el argumento numero de columna, no lo necesitamos, por lo que lo omitimos, cerramos paréntesis, y, aceptamos.
=INDICE(E2:E21;COINCIDIRX(G2&"*";D2:D21;2);1)
Vemos el mismo total que el obtenido anteriormente.
Lo podemos hacer también, con la función BUSCARX, donde como argumento valor buscado, ponemos lo mismo que el valor buscado en la función COINCIDIRX.
=BUSCARX(G2&"*";
El argumento matriz de búsqueda, seleccionamos el rango D2:D21, es decir, las provincias.
=BUSCARX(G2&"*";D2:D21
Como argumento matriz devuelta, seleccionamos el rango E2:E21, los totales.
=BUSCARX(G2&"*";D2:D21;E2:E21
Punto y coma, omitimos el argumento si no se encuentra, y, como argumento modo de coincidencia, seleccionamos coincidencia de caracteres comodín.
Cerramos paréntesis y aceptamos.
=BUSCARX(G2&"*";D2:D21;E2:E21;;2)
Obtenemos el mismo resultado.
Podría hacerlo, también, con la función TEXTOANTES, y, FILTRAR, para ello, pongo la función TEXTOANTES, abro un paréntesis, el primer argumento es texto, pues, seleccionamos el rango D2:D21, que son las provincias, junto con la palabra table.
=TEXTOANTES(D2:D21
Como argumento delimitador, entre comillas dobles, ponemos el guion bajo.
Cerramos paréntesis y aceptamos.
=TEXTOANTES(D2:D21;"_")
Obtenemos una matriz desbordada solo con las provincias.
Ahora, usamos la función FILTRAR, donde como argumento array, seleccionamos el rango E2:E21, los totales.
=FILTRAR(E2:E21;
Como argumento include, ponemos la función TEXTOANTES, y, lo igualamos a la provincia seleccionada, cerramos paréntesis, y, aceptamos.
=FILTRAR(E2:E21;TEXTOANTES(D2:D21;"_")=G2)
Vemos que tenemos el mismo total.
Y, si lo que queremos es encontrar la palabra table, junto con su número, y, que nos devuelva el total, primera lo con funciones, si no podemos acceder a las nuevas funciones.
Voy a buscar el total para aquella provincia que corresponda con table2.
Vemos que el delimitador entre la provincia, y, la palabra table, es el guion bajo (_), pues necesito encontrar su posición, para ello, voy a usar la función ENCONTRAR.
El primer argumento es texto buscado, ponemos el guion bajo entre comillas dobles.
=ENCONTRAR("_";
El siguiente argumento es dentro del texto, pues, seleccionamos el rango D2:D21, que son las provincias, junto con la palabra table.
Cerramos paréntesis y aceptamos.
=ENCONTRAR("_";D2:D21)
Tenemos una matriz desbordada con las posiciones del guion bajo.
Lo siguiente es extraer, a partir del guion bajo, pero ¿Cuántos caracteres debemos de extraer?
Bien, si en una celda, usamos la función LARGO, aplicada a ala matriz D2:D21, obtendremos la longitud de cada cadena.
=LARGO(D2:D21)
Si, ahora, resto la longitud total hasta el guion, obtendré la longitud a extraer.
=LARGO(D2:D21)-ENCONTRAR("_";D2:D21)
Pero, tenemos que extraer a partir del guion bajo mas 1, para ello, usamos la función EXTRAE.
El primer argumento es texto, es decir, del texto donde vamos a extraer, pues, seleccionamos el rango D2:D21.
=EXTRAE(D2:D21
El siguiente argumento es posición inicial, es decir, a partir de que numero de carácter vamos a empezar a extraer, pues es la función ENCONTRAR más 1.
=EXTRAE(D2:D21;ENCONTRAR("_";D2:D21)+1
El siguiente argumento es numero de caracteres, es decir, cuantos caracteres vamos a extraer, pues la función LARGO menos la función ENCONTRAR, pero sin sumar 1, porque tenemos que restar hasta el guion bajo.
=EXTRAE(D2:D21;ENCONTRAR("_";D2:D21)+1;LARGO(D2:D21)-ENCONTRAR("_";D2:D21)
Cerramos paréntesis, y, aceptamos.
=EXTRAE(D2:D21;ENCONTRAR("_";D2:D21)+1;LARGO(D2:D21)-ENCONTRAR("_";D2:D21))
Obtenemos una matriz desbordada con la palabra table más un número.
Ahora, debemos de buscar la palabra table2, en este rango, y si hay coincidencia que nos devuelva la columna de totales, para ello, voy a usar el condicional SI, donde como argumento prueba lógica, seleccionamos la celda donde hemos puesto table2, y, lo igualamos a la expresión anterior.
=SI(G5=EXTRAE(D2:D21;ENCONTRAR("_";D2:D21)+1;LARGO(D2:D21)-ENCONTRAR("_";D2:D21))
Como argumento valor si verdadero, seleccionamos el rango E2:E21, es decir, los totales.
=SI(G5=EXTRAE(D2:D21;ENCONTRAR("_";D2:D21)+1;LARGO(D2:D21)-ENCONTRAR("_";D2:D21));E2:E21
Como argumento valor si falso, ponemos un texto en blanco, cerramos paréntesis y aceptamos.
=SI(G5=EXTRAE(D2:D21;ENCONTRAR("_";D2:D21)+1;LARGO(D2:D21)-ENCONTRAR("_";D2:D21));E2:E21;"")
Obtenemos una matriz desbordada con blanco donde no hay coincidencia, y, donde hay coincidencia, vemos el total, pero como queremos que todo quede en una celda, usamos la función CONCAT, que une cadenas, e ignora las celdas en blanco.
=CONCAT(SI(G5=EXTRAE(D2:D21;ENCONTRAR("_";D2:D21)+1;LARGO(D2:D21)-ENCONTRAR("_";D2:D21));E2:E21;""))
Y ya tenemos el total para table2.
También, podemos hacerlo con la función COINCIDIRX, donde como argumento valor buscado, vamos a usar le carácter comodín asterisco (*), este carácter va a sustituir todos los caracteres que haya, antes de la primera letra, o, después de la ultima letra, en este caso, lo tenemos que colocar primero, porque queremos que sustituya todos los caracteres que haya antes de la letra T de la palabra table, lo tenemos que poner entre comillas dobles.
=COINCIDIRX("*"
Lo concatenamos con el valor de G6, que es la palabra table2.
=COINCIDIRX("*"&G6
Como argumento matriz buscada, seleccionamos el rango D2:D21.
=COINCIDIRX("*"&G6;D2:D21
Y, como argumento modo de coincidencia, en la ventana que abre, seleccionamos coincidencia de caracteres comodín.
Cerramos paréntesis, y, aceptamos.
Obtenemos la posición 2, pues, ahora voy a usar la función INDICE, donde como argumento matriz, seleccionamos el rango E2:E21, los totales.
=INDICE(E2:E21;
Como argumento indicador de filas, es la función COINCIDIRX.
Omitimos el argumento numero de columnas, cerramos paréntesis, y, aceptamos.
=INDICE(E2:E21;COINCIDIRX("*"&G6;D2:D21;2))
Obtenemos el total para table2.
Si podemos acceder a las nuevas funciones, podemos usar la función TEXTODESPUES, donde como argumento texto, seleccionamos el rango D2:D21.
TEXTODESPUES(D2:D21
Como argumento delimitador, entre comillas dobles, ponemos el guion bajo.
Cerramos paréntesis, y, aceptamos.
TEXTODESPUES(D2:D21;"_")
Obtenemos una matriz desbordada con la palabra table junto con su número.
Ahora, voy a usar el condicional SI, donde como argumento prueba lógica, seleccionamos la celda con la palabra table2, y, lo igualamos a la función TEXTODESPUES.
=SI(G6=TEXTODESPUES(D2:D21;"_")
Como argumento valor si verdadero, que nos devuelva la columna de totales, como argumento valor si falso, ponemos un texto en blanco, cerramos paréntesis, y, aceptamos.
=SI(G6=TEXTODESPUES(D2:D21;"_");E2:E21;"")
Obtenemos una matriz desbordada con blanco donde no hay coincidencia, y, el total donde hay coincidencia, ahora, usamos la función CONCAT, para quitar los espacios en banco.
=CONCAT(SI(G6=TEXTODESPUES(D2:D21;"_");E2:E21;""))
Y ya tenemos el total para table2.
Comentarios