ASOCIAR PALABRAS 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]

EJERCICIO

ASOCIAR PALABRAS 2

Asociar palabras (2 de 3)

Siguiendo con el problema planteado en la entrada anterior, vamos a asignar a las frases de la columna B el nombre de la ciudad asociada a la palabra clave que contiene el texto.

Segunda solución

 

Comprobamos  si la frase de la celda B3 contiene alguna palabra clave.

 

Seleccionamos H3:H10 y escribimos:

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

 

Sustituimos los errores por FALSO y el número por VERDADERO.

 

Seleccionamos I3:I10 y escribimos:

=ESNUMERO($H$3:$H$10)     [Terminar con Ctrl + Mayús + Intro]

 

Determinamos en qué fila de la lista I3:I10 hay VERDADERO.

 

Seleccionamos J3:J10 y escribimos:

=($I$3:$I$10)*(FILA($E$3:$E$10)-FILA($E$3)+1)     [Terminar con Ctrl + Mayús + Intro] 

 

VERDADERO está en la fila 6. Aislamos ese valor en la celda K3.

 

En K3:

=SUMA($J$3:$J$10)      [Terminar con Intro]

 

Podría darse la circunstancia de que la frase no contuviera ninguna palabra clave, en cuyo caso, la fórmula deK3 devolvería cero. Hemos de tener en cuenta este supuesto para determinar el nombre de la ciudad asociada.

 

En L3:

=SI($K$3=0;"******";INDICE($F$3:$F$10;$K$3))      [Terminar con Intro]

 

Una vez desarrolladas todas las fórmulas (cinco columnas auxiliares), creamos en C3 la fórmula compuesta.

 

En C3:

=SI(SUMA((ESNUMERO(HALLAR($E$3:$E$10;$B3)))*(FILA($E$3:$E$10)-FILA($E$3)+1))=0;"******";INDICE($F$3:$F$10;SUMA((ESNUMERO(HALLAR($E$3:$E$10;$B3)))*(FILA($E$3:$E$10)-FILA($E$3)+1))))     [Terminar con Ctrl + Mayús + Intro]

 

Finalizamos extendiendo la fórmula hasta la fila 17.

 

Tercera solución

 

En la primera solución del artículo anterior usamos 7 columnas auxiliares; en la segunda solución, 5; y en ésta lo haremos con 4.

 

El primer paso es el mismo.

 

Seleccionamos H3:H10 y escribimos:

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

 

Mantenemos los errores y sustituimos el número (si existe) por un 1.

 

Seleccionamos I3:I10 y escribimos:

=SI($H$3:$H$10>0;1;0)     [Terminar con Ctrl + Mayús + Intro]

 

Comprobamos en qué fila de la lista I3:I30 está el número 1.

 

En J3:

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

 

Usamos la función INDICE para determinar la ciudad asociada. Si J3 contiene un error, lo capturamos conSI.ERROR y devolvemos una lista de asteriscos.

 

En K3:

=SI.ERROR(INDICE($F$3:$F$10;$J$3);"******")     [Terminar con Intro]

 

Ponemos la fórmula definitiva en C3:

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

 

Extendemos la fórmula hasta la fila 17.

 

Cuarta solución

 

También con 4 columnas auxiliares, podemos resolver el problema modificando ligeramente el razonamiento.

 

Seleccionamos H3:H10 y escribimos:

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

 

En el segundo paso, sustituimos los errores por blancos, y el número por su posición en la lista H3:H10.

 

Seleccionamos I3:I10 y escribimos:

=SI(ESERROR($H$3:$H$10);"";FILA($F$3:$F$10)-2)     [Terminar con Ctrl + Mayús + Intro]

 

En J3:

=SUMA($I$3:$I$10)     [Terminar con Intro]

 

En K3:

=SI($J$3=0;"******";INDICE($F$3:$F$10;$J$3))     [Terminar con Intro]

 

Escribimos la fórmula compuesta en C3:

=SI(SUMA(SI(ESERROR(HALLAR($E$3:$E$10;$B3));"";FILA($F$3:$F$10)-2))=0;"******";INDICE($F$3:$F$10;SUMA(SI(ESERROR(HALLAR($E$3:$E$10;$B3));"";FILA($F$3:$F$10)-2))))     [Terminar con Ctrl + Mayús + Intro]

 

Extendemos la fórmula hasta la fila 17.

EJERCICIO

ASOCIAR PALABRAS

Asociar palabras (1 de 3)

Las frases de la columna B contienen palabras clave relacionadas con fenómenos meteorológicos. El objetivo del ejercicio es asignar a cada frase el nombre de la ciudad asociada a cada palabra clave según la tabla del rango E2:F10. El resultado irá en la columna C. Así, la frase "Como una ventisca helada", contiene la palabra clave "ventisca", que lleva asociada la ciudad de "Pisa".

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.

EJERCICIO

FUNCIÓN AGREGAR

La función AGREGAR

En Excel 2010, Microsoft ha añadido la función AGREGAR, que se usa de un modo parecido aSUBTOTALES. 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 rangosC4: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)

FUNCIONES CONTAR.SI.CONJUNTO Y SUMAR.SI.CONJUNTO

Las nuevas funciones «___.SI.CONJUNTO»

Las funciones CONTAR.SI, SUMAR.SI y PROMEDIO.SI solamente admiten un criterio. Pero, a veces, es necesario contar, sumar o promediar rangos de celdas que cumplan más de una condición. En Excel 2007 y 2010, hay tres funciones nuevas que sirven para este propósito: CONTAR.SI. CONJUNTO,SUMAR.SI.CONJUNTO y PROMEDIO.SI.CONJUNTO.

Las tres funciones tienen una sintaxis similar. Por ejemplo, para CONTAR.SI. CONJUNTO la ayuda de Excel muestra la siguiente información.

----------------------------------------------------------------------------------------------------------------------------------------------------------------
CONTAR.SI.CONJUNTO(rango_criterio1;criterio1;[rango_criterio2;criterio2]...)

Aplica criterios a las celdas en varios rangos y cuenta cuántas veces se cumplen dichos criterios.

• rango_criterio1: Obligatorio. El primer rango en el que se evalúan los criterios asociados.
• criterio1: Obligatorio. Los criterios en forma de número, expresión, referencia de celda o texto que determinan las celdas que se van a contar. Por ejemplo, los criterios que se pueden expresar como 32, ">32", B4, "manzanas" o "32".
• rango_criterio2;criterio2...: Opcional. Rangos adicionales y criterios asociados. Se permiten hasta 127 pares de rango/criterio.

Importante: Cada rango adicional debe tener la misma cantidad de filas y columnas que el argumentorango_criterio1. No es necesario que los rangos sean adyacentes.

Observaciones:

• Los criterios de cada rango se aplican a una celda cada vez. Si todas las primeras celdas cumplen los criterios asociados, el número aumenta en 1. Si todas las segundas celdas cumplen los criterios asociados, el número aumenta en 1 nuevamente y así sucesivamente hasta evaluar todas las celdas.
• Si el argumento de los criterios hace referencia a una celda vacía, la funciónCONTAR.SI.CONJUNTO trata dicha celda como un valor 0.

En los criterios se pueden usar caracteres comodín, el signo de interrogación (?) y el asterisco (*). El signo de interrogación se corresponde con un solo carácter y el asterisco se corresponde con cualquier secuencia de caracteres. Si desea buscar un signo de interrogación o un asterisco reales, escriba una tilde (~) delante del carácter que desea buscar.

EJERCICIO

Macros

Taller y ejercicios de EXCEL, accer AQUI

Trabajo practico de Solver

Escoger dos de los ejercicios y enviarlos al correo sergio.valencia@uam.edu.co.