Búsqueda aproximada en Excel – tercera nota

En las últimas dos notas vimos cómo encontrar en una lista el valor más cercano al valor buscado, o extraer el valor asociado a él.
La fórmula matricial que expusimos, y que he mejorado gracias al aporte de Natxo (ver comentario en la nota anterior), nos da el valor más cercano al valor buscado. A veces buscamos el mayor valor más cercano y a veces el menor. Por ejemplo, si observamos la tabla del ejemplo de la nota anterior



vemos que Pedro, con 76, es el más cercano al promedio, 76,2. Pero si buscamos quién tiene la calificación más cercana por encima del promedio, la respuesta es María con 79.

Esta fórmula matricial nos permite encontrar el valor más cercano por encima del valor buscado

={=INDICE($A$2:$A7;COINCIDIR(MIN(SI(B2:B7-B9>=0;B2:B7;FALSO));SI(B2:B7-B9>=0;B2:B7;FALSO);FALSO))}

Esta fórmula crea una matriz con las diferencias entre los valores de la lista y el valor buscado; luego encuentra el menor que sea mayor a cero, es decir, el más cercano que sea mayor al valor buscado.

De la misma manera, esta fórmula no da el valor más cercano que es menor que el buscado

={INDICE($A$2:$A7;COINCIDIR(MIN(SI(B2:B7-C9>=0;B2:B7;FALSO));SI(B2:B7-C9>=0;B2:B7;FALSO);FALSO))}

El archivo con las fórmulas se puede descargar aquí.

Technorati Tags: MS Excel

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: MS Excel

Buscar el valor más cercano en una lista de Excel

Excel cuenta con varias funciones de búsqueda, como BUSCARV, BUSCARH y COINCIDIR.
Estas funciones permiten realizar búsquedas exactas o aproximadas. Supongamos esta situación

Para encontrar la posición del valor buscado (8.5) en la tabla usamos la función COINCIDIR, omitiendo el tercer argumento de la función. En esta situación la función da como resultado 1. Si hubiéramos hecho una búsqueda exacta, el resultado sería #N/A.

El problema con este resultado es que si buscamos la posición del valor más cercano al valor buscado, el resultado tendría que haber sido 2, ya que el valor buscado (8.5) está más cerca del segundo valor en la lista (10) que del primero (5).

Para obtener la posición del valor más cercano al valor buscado tenemos que usar esta fórmula matricial:

={COINCIDIR(MIN(ABS(B2:B7-B9)),B2:B7-B9)}



Para entender esta fórmula tendremos que analizarla de "adentro hacia fuera".

La expresión ABS(B2:B7-B9), al estar dentro de una fórmula matricial, de cómo resultado el menor de los valores del rango B2-B9, B3-B9,…,B7-B9.
El mínimo de esta serie de valores es 1.5, que ocupa el segundo lugar en la serie



El resultado de la fórmula COINCIDIR puede usarse como argumento de la función INDICE para encontrar el valor más cercano al buscado en la lista de valores. En nuestro caso:

={INDICE(B2:B7,COINCIDIR(MIN(ABS(B2:B7-B9)),B2:B7-B9))}

Technorati Tags: MS Excel