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

Tabla de tasas de cambio con la función TRANSPONER

Un compañero de trabajo me consultaba hoy sobre el uso de la función TRANSPONER sobre la cual ya he escrito en el pasado.
Mi explicación fue interrumpida con un "y para qué sirve en la vida real?". El ejemplo de la nota (ver enlace) le pareció complicado y, aceptémoslo, un tanto rebuscado.
No me quedó más remedio que crear un ejemplo más ilustrativo, que es el siguiente: una tabla de tasas de cambios cruzados con la función TRANSPONER. Una tabla de este tipo aparece en mi nota sobre cómo construir un presupuesto de viaje, pero allí no explico cómo construirla.
Empezamos por una tabla sencilla con el nombre de las monedas y su tasa de cambio, por ejemplo, respecto al Euro



Para trabajar con más comodidad, definimos un nombre que contendrá el rango B3:B6, que llamaremos "tasas"



Ahora construimos el marco para nuestra tabla



Seleccionamos el rango B11:E11 y ponemos la fórmula matricial =$B$3/TRANSPONER(tasas)



Los paréntesis "{" y "}" aparecen al introducir la fórmula pulsando simultáneamente Ctrl+Mayúsculas+Enter, como con toda función matricial.

Copiamos la fórmula al resto de las filas, cambiando la celda de referencia (B4 para el franco suizo, B5 para la libra esterlina y B6 para el yen japonés).



Si queremos orientar la tabla en otro sentido, podemos usar la fórmula matricial ={=$B$3/tasas}



O transponer la primer tabla con TRANSPONER



El cuaderno del ejemplo se puede descargar aquí.

Technorati Tags: MS Excel