El ejercicio lo resolveremos de varias maneras, empezando con los métodos que utilizan fórmulas más largas y terminando con las más cortas. En esta entrada estudiaremos la fórmula más larga y dejaremos las otras para otro artículo.
Primera solución
Usaremos las columnas H a N para obtener valores auxiliares. Finalizado el ejercicio podremos prescindir de esos valores ya que la fórmula compuesta la colocaremos en la columna C.
Usando la frase de la celda B3 para hacer el razonamiento, primero, comprobaremos, una a una, si hay alguna palabra clave en la frase. En caso de haberla, en qué lugar comienza. La frase que no contenga ninguna palabra clave devolverá un error. Inicialmente, todas las frases tienen una palabra clave. Más adelante consideraremos el caso de que no tengan ninguna.
Seleccionamos H3:H10 y escribimos:
=HALLAR($E$3:$E$10;$B3) [Terminar con Ctrl + Mayús + Intro]
Sustituimos los errores por ceros.
Seleccionamos I3:I10 y escribimos:
=SI.ERROR($H$3:$H$10;0) [Terminar con Ctrl + Mayús + Intro]
Sumando los números de la columna I obtendremos la posición en la que comienza la palabra clave.
En J3:
=SUMA($I$3:$I$10) [Resultado: 10]
Ya sabemos que la palabra clave empieza en el carácter décimo. Ahora, necesitamos saber dónde acaba para determinar su longitud. Como no hemos puesto signos de puntuación, la palabra clave irá seguida de un espacio (si está en medio de la frase) o no habrá nada después de ella (si es la última palabra de la frase).
En K3:
=SI.ERROR(HALLAR(" ";$B3;J3);LARGO($B3)+1) [Resultado: 18]
La longitud será la diferencia de las celdas K3 y J3.
En L3:
=K3-J3 [Resultado: 8]
La palabra clave la obtendremos extrayendo 8 caracteres (L3) de la frase (B3) empezando desde el carácter 10 (J3).
En M3:
=EXTRAE($B3;J3;L3) [Resultado: ventisca]
Una vez que tenemos la palabra clave, con BUSCARV, obtenemos la ciudad asociada.
En N3:
=BUSCARV(M3;$E$3:$F$10;2;FALSO) [Resultado: Pisa]
Sólo falta crear la fórmula compuesta en C3 y copiar la fórmula hacia abajo.
En C3:
=BUSCARV(EXTRAE($B3;SUMA(SI.ERROR(HALLAR($E$3:$E$10;$B3);0));SI.ERROR(HALLAR(" ";$B3;SUMA(SI.ERROR(HALLAR($E$3:$E$10;$B3);0)));LARGO($B3)+1)-SUMA(SI.ERROR(HALLAR($E$3:$E$10;$B3);0)));$E$3:$F$10;2;FALSO) [Resultado: Pisa]
Arrastramos la fórmula hasta la fila 17.
¿Qué ocurre si alguna frase no tiene palabra clave? Para comprobarlo, sustituimos la frase de la celda B17 por la siguiente: "Cuando se disipó la bruma". Excel nos devuelve un error.
La solución es sencilla. Basta capturar el error con la función SI.ERROR y mostrar en su lugar un espacio en blanco, una línea, un conjunto de asteriscos, o lo que queramos.
En C3:
=SI.ERROR(BUSCARV(EXTRAE($B3;SUMA(SI.ERROR(HALLAR($E$3:$E$10;$B3);0));SI.ERROR(HALLAR(" ";$B3;SUMA(SI.ERROR(HALLAR($E$3:$E$10;$B3);0)));LARGO($B3)+1)-SUMA(SI.ERROR(HALLAR($E$3:$E$10;$B3);0)));$E$3:$F$10;2;FALSO);"*******")
Si añadimos nuevas frases a la columna B, tendremos que copiar hacia abajo la fórmula de la columna C. Sin embargo, esto no será necesario si transformamos la lista de las columnas B y C en una tabla. Para ello, nos colocamos en cualquier celda del rango B2:C17 y accedemos a Insertar + Tabla. Excel mostrará el cuadro de diálogo correspondiente.
Nos aseguramos de que muestre los valores de la figura y pulsamos Aceptar.
Añadimos unas cuantas frases:
En la columna C, las fórmulas de las nuevas filas se han generado automáticamente.
El método descrito no es del todo correcto. No funciona si la palabra clave va seguida de un punto, una coma o cualquier otro símbolo que no sea un espacio en blanco. La solución la veremos en los próximos artículos.
No hay comentarios:
Publicar un comentario