viernes, febrero 22, 2008

Búsqueda aproximada en Excel - segunda nota

Ayer vimos como superar el problema de búsqueda aproximada al valor más cercano en Excel.
Podemos ampliar esta técnica para extraer el valor asociado al valor más cercano al valor buscado. Veamos un ejemplo. Supongamos esta lista de alumnos con notas en distintas asignaturas





En la fila 9 hemos calculado el promedio de cada asignatura. Si queremos averiguar quién es el alumno más cercano al promedio, podemos usar la fórmula que expusimos ayer



La fórmula

={INDICE($A$2:B7,COINCIDIR(MIN(ABS(B2:B7-B9)),B2:B7-B9),1)}

que hemos puesto en la celda B11 nos da como resultado Pedro, que es el alumno con la nota más cercana al promedio.


La única diferencia con la fórmula matricial que presentamos ayer es que hemos agregado el argumento "número de columna" a la función INDICE, que al argumento "matriz" comprende dos columnas y el nombre de los alumnos se encuentra en la primera.

Esta fórmula tiene un serio inconveniente. Si la lista no está ordenada en orden ascendiente, el resultado puede ser erróneo. Si copiamos la fórmula a las celdas contiguas



vemos que el resultado para Literatura es "María", cuando tendría que se "Mercedes". Lo mismo con Matemáticas, donde el resultado es "Pedro" siendo el resultado correcto "Mercedes".

Una solución posible es reordenar las listas antes de aplicar la fórmula. Pero en casos como el nuestro esto nos obligaría a dividir nuestra lista en tres listas separadas.

Otra solución es emplear una fórmula que no sea dependiente del orden de los resultados. La fórmula que aplicaremos es la siguiente:

={ INDIRECTO((DIRECCION(COINCIDIR(MIN(ABS(C9-C2:C7)),ABS(C9-C2:C7),0)+1,1)))}


Para hacer la fórmula más legible, la he dividido en la barra de fórmulas usando Alt+Enter



Esta fórmula emplea la función INDIRECTO que devuelve la referencia especificada por una cadena de texto, como ya hemos explicado.

La función DIRECCION crea una referencia a una celda, en forma de texto, cuyo valor es interpretado por INDIRECTO.

La función COINCIDIR calcula cuál es la posición del valor más cercano al promedio, usando los valores absolutos de las diferencias (como vimos en la nota de ayer).

El valor 1 se refiere a la columna A.

La combinación de estas funciones en la fórmula da como resultado $A$6. IINDIRECTO convierte este resultado al contenido de la celda, "Pedro".

Esta fórmula también tiene un serio inconveniente. Si movemos la lista hacia abajo, o agregamos líneas por encima de ella, obtenemos resultados erróneos. Esto se debe a que COINCIDIR sigue dando la posición correcta, pero ahora esta no esta coincide con los números de fila.

Para evitar esto corregimos nuestra fórmula de la siguiente manera:

={INDIRECTO((DIRECCION(FILA(D2:D7)+COINCIDIR(MIN(ABS(D9-D2:D7)),ABS(D9-D2:D7),0)-1,1)))}



El cuaderno se puede descargar aquí






Technorati Tags:

4 comentarios:

  1. Amigo Jorge, en primer lugar te felicito por tu blog, gracias al cual mi productividad con Excel aumenta sin parar día tras día. Aprecio mucho el generoso esfuerzo que sin duda dedicas a esta labor.

    Por primera vez, creo que puedo hacerte una observación: creo que sería mucho más sencillo conseguir el mismo resultado si escribes la primera fórmula matricial utilzando nuevamente la función ABS en la matriz de comparación del mínimo, es decir, en lugar de escribir:

    ={INDICE($A$2:B7,COINCIDIR(MIN(ABS(B2:B7-B9)),B2:B7-B9),1)}

    cambiar la fórmula por:

    =+INDICE($A$2:$A$7;COINCIDIR(MIN(ABS(B2:B7-B9));ABS(B2:B7-B9);0))

    Si no me equivoco, con este simple cambio la fórmula sigue arrojando un resultado correcto independientemente de que se inserten líneas o los registros estén desordenados (claro que puedo haberme equivocado...).

    Nada más, sigue así y recibe un agradecido saludo desde Bilbao por tu estupendo trabajo.

    ResponderBorrar
  2. Hola Natxo

    excelente aporte. No te has equivocado. Además tu fórmula tiene la ventaja de mantener los resultados correctos también si insertamos columnas, cosa que no había comprobado.

    Gracias

    ResponderBorrar
  3. Jorge
    Gracias por tu blog pero como puedo descargar tus ejemplo ya que aparecen como texto y no como excel.

    Gracias
    Juan

    ResponderBorrar
  4. Hola Juan

    Esnips, el sitio donde guardo los archivos, ha cambiado en algo la inteface haciendo difícil encontrar el enlace para la descarga.
    Se encentra en la zona inferior, a la isquierda ("download").

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.