martes, 26 de marzo de 2013

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.




lunes, 11 de marzo de 2013

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 10300 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]



jueves, 7 de marzo de 2013

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 de K3 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 con SI.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.



lunes, 4 de marzo de 2013

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.

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.