Separar texto en columnas basándonos en comas y saltos de línea

Si una celda contiene gran cantidad de datos puede ser conveniente separarlos en distintas columnas para trabajar mejor con ellos. Por ejemplo, en B3:B6 tenemos información de algunos elementos químicos:

No parece muy útil esta información tal y como está resumida. Será muy difícil operar con el punto de fusión, la masa atómica o el punto de ebullición de estos elementos sin aislarlos previamente. Lo más lógico sería crear una tabla como ésta:

Naturalmente, no lo haremos con copiar y pegar; utilizaremos la herramienta Texto en columnas.

En la lista original, los saltos de línea se han creado con Alt + Intro (hay un ejemplo en la entrada Eliminar saltos de línea). Otro delimitador es la coma, que se ha utilizado para separar el nombre del elemento, su símbolo y su número atómico (Litio, Li, 3).

Seleccionamos B3:B6 y accedemos a Datos + Texto en columna. Entraremos en un asistente donde se especificarán los tres pasos de que consta el proceso.

Paso 1 de 3: Marcamos el botón Delimitados y pulsamos Siguiente:

Paso 2 de 3: Marcamos los cuadros de verificación Coma y Otro. Nos ponemos en el espacio que hay a la derecha del cuadro Otro y pulsamos la combinación Ctrl + J. Esta combinación equivale al salto de línea creado con Alt + Intro. Por seguridad, marcamos el cuadro Considerar separadores consecutivos como uno solo. Con esta última acción nos aseguramos de que si en el texto original se ha pulsado dos veces Alt + Intro por error, se considere como una única pulsación.

En la Vista previa de los datos (parte baja de la ventana) podemos comprobar los efectos que produce cada una de nuestras acciones. Pulsamos Siguiente.

Paso 3 de 3: En esta última ventana no haremos ningún cambio.

Pulsando Finalizar comprobaremos que nuestra lista original ha sido sustituida por la siguiente:

Ha quedado un poco desordenado, pero bastará ensanchar las columnas y alinear el texto para dejarlo correctamente. Finalizaremos colocando los encabezados en cada columna.

En el tercer paso podemos decidir que se conserve la lista original y se cree una nueva en otro lugar. Además, podemos eliminar los datos que no nos interesen. Por ejemplo, para colocar la nueva lista en D9 y prescindir de la columna Serie química, en el Paso 3 de 3, debemos poner =$D$9 en el apartado Destino; en Vista previa de los datos, haremos clic en la cuarta columna y marcaremos el botón No importar datos (saltar). Debemos hacerlo en el orden indicado: en primer lugar seleccionar la columna que queremos eliminar y, posteriormente, marcar el botón No importar datos (saltar). El encabezado de la cuarta columna de la Vista previa de los datos cambiará de General a Saltar columna.

Poniendo los encabezado y alineando los datos, la lista quedará así:

Descargar archivo (ST040-Ele.xlsx)

Descargar archivo (ST040-Ele.xls)



Eliminar saltos de línea

Ya he indicado en alguna ocasión que los saltos de línea dentro de una celda se crean pulsando Alt + Intro. Recordémoslo poniendo un ejemplo.

1. Nos ponemos en B2.
2. Escribimos: José Luis Martínez     [Pulsamos Alt + Intro]
3. Escribimos: C/Goya, 3-1º D     [Pulsamos Alt + Intro]
4. Escribimos: Zaragoza     [Terminamos con Intro ]

Pero, ahora, deseamos realizar la operación contraria; es decir, dada una lista de datos con los valores de las celdas escritas en varias filas, debemos eliminar los saltos de línea y escribir los datos de cada celda en una sola línea con el separador que queramos: espacio, barra vertical, asterisco...

Vamos a resolver el problema de dos maneras:

Manualmente: Buscar y reemplazar

Pulsamos Ctrl + L para entrar en el cuadro de diálogo Buscar y reemplazar. En la ficha Reemplazar y en el apartado Buscar, pulsamos Ctrl + J para insertar el código de salto de línea. En Reemplazar con, escribimos un espacio seguido del carácter | y otro espacio. Terminamos pulsando el botón Reemplazar todos y cerramos el cuadro de diálogo.

Probablemente tengamos que ensanchar la columna manualmente para que nos quepa el texto. Si queremos, podemos hacerlo de otro modo: seleccionamos B2:B4, pulsamos Ctrl + 1 para entrar en la ventana Formato de celdas y quitamos la marca del apartado Ajustar texto.

Con una fórmula: SUSTITUIR y CARACTER

Cuando queramos conservar las celdas originales y copiarlas en otro rango en una sola fila, usaremos esta fórmula:

En D2:
=SUSTITUIR(B2;CARACTER(10);" | ")     [Extendemos la fórmula hasta la fila 4]

El carácter ASCII 10 es el salto de línea que hay que buscar y sustituir por otro carácter (en el ejemplo, la barra vertical). Ensanchamos la columna D para que entre toda la línea y, si queremos, la centramos verticalmente.

La función DELTA

Microsoft incluye DELTA dentro del grupo de funciones de Ingeniería. La función compara dos números y devuelve 1 (si ambos coinciden) o 0 (si no coinciden). La sintaxis es:

DELTA(número1; [número2])

Si se omite número2, se considera que vale 0. Si alguno de los argumentos no es un número, DELTA devuelve el error #¡VALOR!

Nunca he tenido necesidad de utilizar la función DELTA ya que se pueden usar otras funciones para obtener el mismo resultado. Además, en Excel 2003 sólo está disponible si se carga el complemento Herramientas para análisis (Herramientas + Complementos + Herramientas para análisis).

Comparemos dos números usando DELTA, el operador "=" y la función IGUAL:

• =DELTA(5;5)     [Resultado: 1]
• =--(5=5)     [Resultado: 1]
• =--IGUAL(5;5)     [Resultado: 1]

A mi modo de ver, los dos últimos ejemplos son más claros y explícitos que el primero.

Estos ejemplos no son muy adecuados ya que los números suelen estar escritos en las celdas de una hoja de cálculo. Así pues, pongamos otro: en la lista de números (B3:B27), queremos saber cuántas veces se repite el número 103 (celda E2).

Obtendremos la primera solución utilizando la función DELTA. La columna H nos servirá para hacer cálculos auxiliares.

En H3:
=DELTA($E$2;B3)     [Extendemos la fórmula hasta la fila 27]

En E3:
=SUMA(H3:H27)     [Resultado: 8]

Parece ser que DELTA no se puede usar con matrices, de modo que fórmulas como:

=DELTA($E$2;B3:B27)    [Terminado con Ctrl + Mayúscula + Intro]

son erróneas. La conclusión es que no se puede prescindir de la columna auxiliar H.

Pero hay, al menos, tres soluciones muy sencillas que no precisan columnas auxiliares.

En E4:
=CONTAR.SI(B3:B27;E2)     [Resultado: 8]

En E5:
=SUMA(--(E2=B3:B27))     [Terminado con Ctrl + Mayúscula + Intro. Resultado: 8]

En E6:
=SUMAPRODUCTO(--(E2=B3:B27))     [Resultado: 8]

Casos habrá en los que resulte ventajoso utilizar DELTA en lugar de otras funciones, pero no se me ocurre ninguno.

Descargar archivo (S11-Delta.xlsx)

Descargar archivo (S11-Delta.xls)

La función AGREGAR

En un artículo antiguo analizamos cómo se puede usar la función SUBTOTALES en lugar de otras funciones. Además, comprobamos que con SUBTOTALES podemos pasar por alto los valores ocultos. Ésta es, a mi modo de ver, la principal razón de su existencia.

En Excel 2010, Microsoft ha añadido la función AGREGAR, que se usa de un modo parecido a SUBTOTALES. Tiene dos sintaxis:

Primera sintaxis: AGREGAR(núm_función; opciones; ref1; [ref2]; …)

Consideremos una lista de valores numéricos en la que hay intercalados varios errores:

En D1:

=SUMA(A1:A11)     [Resultado: #¡DIV/0!]

 

Excel no puede hallar la suma si en el rango de datos hay algún error.

 

La función AGREGAR permite hallar la suma omitiendo los errores. A medida que vayamos escribiendo la fórmula, Excel mostrará las opciones disponibles.

 

En D2, escribimos: =AGREGAR(

 

Elegimos la opción 9 y ponemos punto y coma. Excel abre otro menú:

Seleccionamos la opción 6 y completamos la fórmula:

En D2:
=AGREGAR(9;6;A1:A11)     [Resultado: 462]

La suma se ha realizado correctamente sin tener en cuenta las celdas con errores. Para operar con rangos no contiguos, bastará separarlos con punto y coma. Por ejemplo, si quisiéramos hallar el promedio de los rangos C4:C10, H5:H20 y M2:N40, la fórmula sería: =AGREGAR(1;6;C4:C10;H5:H20;M2:N40)  

El 1 (primer argumento) selecciona la función PROMEDIO mientras que el 6 (segundo argumento) omite los posibles valores de error que contengan los rangos indicados en el tercer, cuarto y quinto argumento.

Segunda sintaxis: AGREGAR(núm_función, opciones, matriz, [k])

Si núm_función está comprendido entre 14 y 19, es necesario añadir el argumento [k]. Esto ocurre porque las funciones correspondientes requieren este segundo argumento. Como ejemplo, vamos a calcular el tercer número mayor del rango A1:B11

En D3:
=AGREGAR(14; 6; A1:B11; 3)     [Resultado: 95]

Si no hubiéramos omitido los errores (segundo argumento igual a 4), Excel devolvería un error. Habría sido lo mismo que escribir: =K.ESIMO.MAYOR(A1:B11;3)

Otra forma de poner los rótulos del eje X en dos filas

Recién publicado el último artículo donde se explica cómo poner los rótulos del eje de abscisas en dos o tres filas, se me ha ocurrido otro método que he probado y funciona.

Consiste en crear una nueva columna en la que los elementos de las filas pares vayan precedidos del carácter 10 de la tabla ASCII. Este carácter equivale a un salto de línea, pero no se abre hueco en la celda encima del texto (ignoro la razón).

Vamos a copiar la lista B3:B11 en L3:L11 de la siguiente manera: los elementos de las filas impares los dejamos intactos; los elementos de las filas pares irán precedidos del carácter ASCII 10.

En L3:

=SI(ES.PAR(FILA(B3));CARACTER(10)&B3;B3)    [Extendemos la fórmula hasta la fila 11]

Aunque no lo parezca, en L3:L11, Avellana, Nuez, Pistacho e Higo seco llevan un salto de línea delante.

Ahora, hacemos un gráfico de columnas con los datos del rango B2:D11 y eliminamos la leyenda.

Los rótulos del eje X han quedado inclinados. Hacemos doble clic en uno de ellos para acceder al cuadro de diálogo Dar formato a eje.

• En la ficha Opciones del eje, seleccionamos el botón de opción Especificar unidad de intervalo y dejamos el valor 1.
• En la ficha Alineación, escribimos 1 en Ángulo personalizado.

Volvemos a hacer doble clic en un rótulo cualquiera y, en Ángulo personalizado, ponemos un 0. Los rótulos quedan solapados pero ya están totalmente horizontales.

Con el gráfico seleccionado, accedemos a Herramientas de gráficos + Diseño + Seleccionar datos:

Hacemos clic en el botón Editar del apartado Etiquetas del eje horizontal (categoría). Sustituimos el valor de Rango de rótulos del eje por L3:L11.

Pulsamos dos veces Aceptar y el gráfico quedará perfecto. Ya lo podemos estrechar hasta que nos quede del tamaño deseado.

Como es lógico, para poner los rótulos en tres filas será necesario poner dos veces el carácter 10 en las filas correspondientes. La fórmula podría ser la siguiente:

En L3:

=ELEGIR(RESIDUO(FILA(B3);3)+1;B3;CARACTER(10)&B3;CARACTER(10)&CARACTER(10)&B3)     [Extendemos la fórmula hasta la fila 11]

Para que no moleste, podemos crear la columna auxiliar en una zona que quede cubierta por el gráfico (por ejemplo, en G3:G11) o cortarla y pegarla en otra hoja.

Descargar archivo (S105b-Frutos secos.xlsx)

Descargar archivo (S105b-Frutos secos.xls)

Rótulos del eje X en distintas filas

Cuando los rótulos del eje horizontal de un gráfico tienen una longitud grande, para que quepan y se puedan leer, Excel los pone inclinados.

Si estiramos el gráfico conseguiremos que los rótulos queden en posición horizontal, pero a costa de hacer un gráfico muy ancho.

También podemos conseguirlo reduciendo el tamaño de la fuente, pero esto puede dificultar mucho la lectura.

¿Podemos acercar los rótulos entre sí manteniéndolos en posición horizontal? De este modo, conseguiríamos que cupieran en un espacio menor. Vamos a intentarlo.

Seleccionamos B2:D11 y accedemos a Insertar + Columna + Columna agrupada. Con este paso, y eliminando la leyenda, ya tendremos hecho el gráfico con los rótulos inclinados.

Ahora, hacemos doble clic en uno de los rótulos del eje X para entrar en el cuadro de diálogo Dar formato a eje.

• En la ficha Opciones del eje, seleccionamos el botón de opción Especificar unidad de intervalo y dejamos el valor 1. 
• En la ficha Alineación, escribimos 1 en Ángulo personalizado.

Cerramos la ventana y obtendremos la siguiente imagen:

Los rótulos se solapan unos con otros, pero no están totalmente horizontales (les hemos puesto una inclinación de 1º). Volvemos a entrar en el cuadro de diálogo Dar formato a eje y ponemos una inclinación de 0º. El gráfico quedará con los rótulos totalmente horizontales. La razón de dar este rodeo es que poniendo desde el principio 0º no funciona (al menos, a mí no me ha funcionado).

Estirando el gráfico conseguiremos que se vean los rótulos completos. Esta solución puede que no valga si necesitamos crear un gráfico muy estrecho. Por eso, vamos a ver otras soluciones.

El primer truco consiste en escribir normalmente los rótulos de las filas impares del rango B3:B11, y precedidos de un salto de línea los de las líneas pares. Así, Almendra, Cacahuete, Piñón, Dátil seco y Uva Pasa se escribirán normalmente; Avellana, Nuez, Pistacho e Higo seco, irán precedidos de un salto de línea. Para insertar un salto de línea antes (o después) de una palabra, se coloca el cursor en el lugar preciso y se pulsa Alt + Intro. La tabla quedará así:

Ahora, seleccionando B2:D11, hacemos un gráfico de columnas y repetimos los pasos dados en el ejemplo anterior: unidad de intervalo igual a 1, alineación con un ángulo de 1º y, luego, de 0º. Terminado el gráfico, lo estrechamos hasta el tamaño que queramos y obtendremos una imagen similar a la siguiente:

Lógicamente, podemos dar dos saltos de línea pulsando dos veces Alt + Intro. De este modo, Cacahuete y Dátil seco pasarán a la tercera fila.

Hay otro truco que podemos utilizar para poner rótulos en diferentes filas, aunque es más complicado y requiere usar columnas nuevas.

Para poner los rótulos en dos filas creamos una nueva columna a la izquierda de la tabla y alternamos los rótulos entre las dos columnas de la izquierda. Con la nueva tabla creamos el gráfico.

Los rótulos de la segunda fila no quedan centrados. La solución es sencilla: basta escribir un espacio en blanco en las celdas vacías que hay debajo de Avellana, Nuez, Pistacho e Higo seco. Después, si es necesario, podemos reducir la anchura del gráfico.

Análogamente, necesitaremos una tercera columna para poner los rótulos en tres filas.

Si nos molestan las líneas, las quitamos (o las ponemos de un color tenue) haciendo doble clic en una de ellas y eligiendo las propiedades adecuadas en las pestañas Color de línea y Estilo de línea.

Descargar archivo (S105-Frutos secos.xlsx)

Descargar archivo (S105-Frutos secos.xls)

Permutaciones con repetición

Vamos a calcular de cuántas formas se puede crear un mosaico cuadrado con 96 baldosines verdes, tres granate y uno azul. Todos los baldosines asimétricos deben estar igualmente orientados.

Se trata de un problema de matemática combinatoria. El número total de mosaicos son las permutaciones con repetición de 100 elementos, de los cuales, uno se repite 96 veces, otro, tres veces, y el último aparece una sola vez.

Si ningún baldosín tuviera un identificador que lo hiciera asimétrico, habría que dividir el resultado por cuatro. Se comprende mejor comparando estos cuatro mosaicos:

Los cuatro mosaicos deben contabilizarse como uno, ya que girando cualquiera de ellos 90º, 180º o 270º, coincide con otro. Sin embargo, con baldosines asimétricos no ocurre esto:

Por ejemplo, al girar 90º el primer mosaico, las letras quedarán giradas y ya no coincidirá exactamente con el segundo mosaico; son dos mosaicos distintos.

Pongamos todo en una hoja de cálculo. En O3:O5 ponemos los baldosines que hay de cada color, y debajo, en O6, la suma. En O8 calcularemos el número total de mosaicos posibles.

En O8:

=FACT(O6)/(FACT(O3)*FACT(O4)*FACT(O5))     [Resultado: 15.684.900]

Todo parece ir bien, pero si en vez construir mosaicos de 10 x 10 los hacemos de 15 x 15, tendremos problemas. En este caso, el número total de baldosines por mosaico será de 225 y al calcular el factorial de 225 se producirá un error por desbordamiento. 

Hay que aplicar logaritmos, como se explicó en el artículo Combinatoria: Variaciones. El desarrollo matemático es como sigue:

Tomamos logaritmos:

Calculamos el antilogaritmo de la expresión anterior.

Desarrollando el exponente:

Ahora, tenemos que crear cuatro nombres accediendo a Fórmulas + Administrador de nombres:

Verde	=FILA(DESREF(Hoja1!$A$1;;;Hoja1!$O$3))
Granate	=FILA(DESREF(Hoja1!$A$1;;;Hoja1!$O$4))
Azul	=FILA(DESREF(Hoja1!$A$1;;;Hoja1!$O$5))
Baldosines	=FILA(DESREF(Hoja1!$A$1;;;Hoja1!$O$6))

Finalmente, sustituimos la fórmula de O8 por la siguiente:

En O8:
=10^(SUMAPRODUCTO(LOG10(Baldosines))-SUMAPRODUCTO(LOG10(Verde))-SUMAPRODUCTO(LOG10(Granate))-SUMAPRODUCTO(LOG10(Azul)))     [Resultado: 415.850.400]

Descargar archivo (S105-Mosaico.xlsx)

Descargar archivo (S105-Mosaico.xls)

Combinatoria: Variaciones

Si en un maratón popular participan 2.500 atletas, ¿cuántas apuestas hay que hacer para acertar con seguridad los tres primeros puestos?

Se trata de un sencillo problema de combinatoria y cualquier estudiante de bachillerato debería saber resolverlo. El número total de apuestas son las Variaciones de 2.500 elementos tomados de 3 en 3.

Las Variaciones de m elementos tomados de n en n se pueden calcular con varias fórmulas:

Fórmula 1

Fórmula 2

Fórmula 3

Como las Permutaciones son un caso particular de las Variaciones, Excel dispone de una única función, llamada PERMUTACIONES, para ambos casos. Como es lógico, esta función es la que nos permite hacer el cálculo del modo más fácil, pero vamos a explorar otras posibilidades.

En C3 pondremos los participantes (elementos) y en C4 los aciertos (número de elementos de cada agrupación). En C6:C10 obtendremos las diferentes soluciones del problema.

Comenzamos con la solución sencilla; es decir, empleando la función PERMUTACIONES.

En C6:

=PERMUTACIONES(C3;C4)     [Resultado: 15.606.255.000]

En C7 vamos a utilizar la fórmula 3.  Esta fórmula utiliza factoriales y puede dar problemas por desbordamiento. Excel calcula perfectamente el factorial de cualquier número pequeño, pero si es superior a 170 se produce un desbordamiento y devuelve el error #¡NUM!

En C7:

=FACT(C3)/FACT(C3-C4)     [Resultado: #¡NUM!]

Vamos a buscar otra solución; la anterior no sirve. Emplearemos la fórmula 1. Según esta fórmula, hay que multiplicar una secuencia de números decrecientes que empieza por 2.500 y terminan en 2.498. Es decir: 2500 x 2499 x 2498

¿Cómo generar automáticamente la secuencia? Muy sencillo, basta crear un nombre con las funciones FILA y DESREF de esta manera:

Accedemos a Fórmulas + Asignar nombre y creamos el siguiente nombre:

Secuencia

=Hoja1!$C$3-FILA(DESREF(Hoja1!$A$1;;;Hoja1!$C$4))+1

Podemos comprobar que Secuencia genera los números 2500, 2499 y 2498 seleccionando H2:H20 y escribiendo: =Secuencia     [Terminar con Ctrl + Mayús + Intro]

En C8:

=PRODUCTO(Secuencia)     [Resultado: 15.606.255.000]

La fórmula 2 exige usar las funciones COMBINAT y PERMUTACIONES, algo absurdo cuando podemos hallar el resultado utilizando únicamente PERMUTACIONES, pero el método es completamente correcto.

En C9:

=COMBINAT(C3;C4)*PERMUTACIONES(C4;C4)     [Resultado: 15.606.255.000]

El último caso que vamos a ver es largo y rebuscado, pero es interesante estudiarlo para poderlo utilizar cuando se produzcan desbordamientos. Consiste en tomar logaritmos de las operaciones que pueden desbordar la capacidad de Excel y, posteriormente, aplicar el antilogaritmo para obtener el resultado final. Vamos a hacerlo paso a paso en C12:C14. La fórmula definitiva la pondremos en C10.

Partiremos de la fórmula 3, que antes no nos ha servido porque al calcular el factorial de 2.500 [FACT(C3)] se produce un desbordamiento. Vayamos razonando paso a paso. El cálculo que tenemos que hacer es el siguiente:

Tomamos logaritmos decimales (es igual hacerlo con neperianos):

Ahora, hallamos el antilogaritmo:

Desarrollando el exponente:

Hemos transformado el factorial en suma de logaritmos. Ya no hay peligro de desbordamiento.

Ahora, tenemos que generar una lista de números consecutivos del 1 al 2.500 (en realidad el valor de C3) y otra del 1 al 2.497 (C3-C4). Lo haremos con dos nuevos nombres:

Participantes	=FILA(DESREF(Hoja1!$A$1;;;Hoja1!$C$3))
Resta	=FILA(DESREF(Hoja1!$A$1;;;Hoja1!$C$3-Hoja1!$C$4))

En C12, calculamos (log 1 + log 2 + log 3 + ... + log 2500):

=SUMAPRODUCTO(LOG10(Participantes))     [Resultado: 7.411]

En C13, calculamos (log 1 + log 2 + log 3 + ... + log 2497):

=SUMAPRODUCTO(LOG10(Resta))     [Resultado: 7.401]

En C14, calculamos 10 elevado a la diferencia de los dos valores anteriores:

=10^(C12-C13)     [Resultado: 15.606.255.000]

En C10, ponemos la fórmula compuesta:

=10^(SUMAPRODUCTO(LOG10(Participantes))-SUMAPRODUCTO(LOG10(Resta)))

Podemos eliminar las filas 12, 13 y 14; no son necesarias.

Descargar archivo (S104-Maratón.xlsx)

Descargar archivo (S104-Maratón.xls)

Asociar palabras (3 de 3)

Veamos las dos últimas soluciones al problema de asociar nombres de ciudades con las frases que contienen palabras claves.

Quinta solución

Lo haremos con tres columnas auxiliares.

La primera columna auxiliar será parecida a la de los casos anteriores, pero introduciendo la novedad de incorporar el comodín asterisco (*).

Seleccionamos H3:H10 y escribimos:

=HALLAR("*"&$E$3:$E$10&"*";$B3)     [Terminar con Ctrl + Mayús + Intro]

("*"&$E$3:$E$10&"*") implica buscar cualquier palabra clave del rango E3:E10 precedida o seguida de cualquier número de caracteres. En el caso de que haya alguna coincidencia, la fórmula devolverá un 1.

Ahora, bastará buscar en qué fila del rango H3:H10 está ese 1.

En I3:

=COINCIDIR(1;$H$3:$H$10;0)     [Terminar con Intro]

Una vez que hemos determinado que hay coincidencia con el sexto elemento del rango H3:H10, usaremos la función INDICE para determinar la ciudad asociada. También contemplaremos la posibilidad de que no haya coincidencia y se haya producido error.

En J3:

=SI.ERROR(INDICE($F$3:$F$10;$I$3);"******")     [Terminar con Ctrl + Mayús + Intro]

Como siempre, pondremos la fórmula definitiva en C3.

En C3:

=SI.ERROR(INDICE($F$3:$F$10;COINCIDIR(1;HALLAR("*"&$E$3:$E$10&"*";$B3)));"******")     [Terminar con Ctrl + Mayús + Intro]

Finalizamos el ejercicio extendiendo la fórmula hasta la fila 17.

Sexta solución

La última solución será la más corta. Sólo requerirá dos columnas auxiliares.

Seleccionamos H3:H10 y escribimos:

=HALLAR($E$3:$E$10;$B3)     [Terminar con Ctrl + Mayús + Intro]

Si hay una palabra clave, en H3:H10 habrá un número (como ocurre en nuestro ejemplo). El truco consiste en utilizar la función BUSCAR para buscar no ese número sino uno mayor. La función BUSCAR tiene la particularidad de que si no encuentra el número buscado, se queda con el número más cercano que sea inferior al buscado. Usando el número 10³⁰⁰ nos aseguramos de que en la columna H no haya ninguno mayor.

En I3:

=SI.ERROR(BUSCAR(10^300;$H$3:$H$10;$F$3:$F$10);"******")     [Terminar con Intro]

Concluimos con la fórmula final.

En C3:

=SI.ERROR(BUSCAR(10^300;HALLAR($E$3:$E$10;B3);$F$3:$F$10);"******")     [Terminar con Intro y extender la fórmula hasta la fila 17]

Descargar archivo (SE60c-Asignación por contenido.xlsx)