Cuando tenemos una tabla de datos numéricos en Excel y necesitamos buscar un valor aproximado, podemos utilizar la función BUSCARV poniendo especial atención en su cuarto argumento que será la clave para obtener el resultado correcto.
El valor aproximado inferior
Como seguramente sabes, la función BUSCARV tiene cuatro argumentos y el último de ellos indica el tipo de búsqueda que se va a realizar. Si dicho argumento es FALSO, entonces la función BUSCARV realizará una búsqueda exacta, pero si el argumento es VERDADERO, entonces hará una búsqueda aproximada. Considera la siguiente tabla de datos la cual tiene los valores numéricos ordenados de menor a mayor:
Nuestro objetivo es obtener la equivalencia de la calificación en letra para cada uno de los estudiantes y para ello utilizaremos la siguiente fórmula:
=BUSCARV(E2, $A$2:$B$15, 2, VERDADERO)
Observa que el último argumento de la función BUSCARV es VERDADERO lo cual indica que realizaremos una búsqueda aproximada. El resultado de la fórmula es el siguiente:
Ahora analicemos los resultados. Hugo tiene un porcentaje de 87 y la fórmula nos ha devuelto la calificación B+ que corresponde al valor 86 en la tabla. Esto indica que, al no encontrar el valor 87, la función BUSCARV nos devuelve el valor inmediato inferior. Paco tiene un porcentaje de 75 que tampoco aparece en la tabla, pero la función nos devuelve la calificación C que corresponde al valor 73 y que también es el valor inmediato inferior.
Es muy importante recordar que este método requiere que el rango de búsqueda esté ordenado de manera ascendente. Es así como la función BUSCARV nos ayuda a buscar el valor aproximado inferior.
El valor aproximado superior
Si en lugar del valor aproximado inferior necesitamos obtener el valor aproximado superior, entonces debemos hacer algunos ajustes al ejemplo anterior. El primer ajuste es ordenar los datos numéricos de manera descendente de la siguiente manera:
Este es un requisito indispensable para poder utilizar la función COINCIDIR que nos ayudará a encontrar el valor aproximado superior con la siguiente fórmula:
=INDICE($B$2:$B$15, COINCIDIR(E2, $A$2:$A$15, -1))
Esta combinación de funciones es muy común ya que son una excelente alternativa a la función BUSCARV. La función COINCIDIR nos devuelve la fila que contiene el porcentaje aproximado y la función INDICE nos devuelve la calificación correspondiente. Para nuestro ejemplo, hemos indicado el valor -1 en el tercer argumento de la función COINCIDIR porque deseamos obtener el valor aproximado superior. Observa el resultado de esta fórmula:
En esta ocasión, Hugo obtiene una calificación A- que corresponde al porcentaje 90 de la tabla y que es precisamente el valor aproximado superior a 87. Lo mismo sucede con el resto de las calificaciones.
El valor más cercano
La última fórmula que analizaremos en este artículo realiza una tarea más compleja que las anteriores ya que busca el valor más cercano sin importar si es un valor superior o inferior. Dada esta complejidad, elaboraremos la fórmula paso a paso. Comencemos por analizar el ejemplo de Hugo que tiene un porcentaje de 87:
En la columna G he colocado la fórmula =ABS(A2-$E$2) que obtiene la diferencia entre cada valor de la tabla y el porcentaje alcanzado por Hugo. Podemos notar inmediatamente que el valor que tiene la menor diferencia dentro de la tabla es el valor 86 ya que solo tiene una diferencia de 1 y por lo tanto es el valor más cercano. Para lograr nuestro objetivo necesitamos obtener el valor más pequeño dentro del rango G2:G15 y para eso utilizaremos la función MIN de la siguiente manera:
Ya que sería ineficiente crear una columna con las diferencias para cada alumno, es necesario sintetizar las fórmulas anteriores en una sola, pero ya que esto implica el uso de rangos, se hace indispensable utilizar funciones matriciales. Considera la siguiente fórmula:
=MIN(ABS($A$2:$A$15-E2))
Al colocar esta fórmula en la celda F2 y pulsar la combinación de teclas Ctrl + Mayús + Entrar obtendremos el mismo resultado que con la combinación de fórmulas anteriores. Y aún más, al copiar dicha fórmula hacia abajo obtendré la diferencia mínima que existe para cada uno de los alumnos:
Es importante que comprendas esta lógica porque ahora utilizaremos la función COINCIDIR para encontrar la posición que tiene dicha diferencia mínima dentro de la matriz para cada alumno. La fórmula matricial a utilizar es la siguiente:
=COINCIDIR(MIN(ABS($A$2:$A$15-E2)), $A$2:$A$15-E2)
Recuerda que se debe pulsar Ctrl + Mayús + Entrar al introducir esta fórmula. Pero antes de continuar, permíteme explicar la lógica de esta fórmula. En primer lugar recuerda que ABS($A$2:$A$15-E2) nos devuelve una colección de datos que para el caso de Hugo sería la siguiente: {87, 27, 24, 21, 17, 14, 11, 7, 4, 1, 3, 6, 9, 13}. La función MIN me dice que el valor mínimo es 1 y la función COINCIDIR me dice que dicho valor 1 ocupa la posición 10 dentro de la matriz. Observa el resultado de esta fórmula al aplicarlo a cada alumno:
El valor devuelto por la fórmula coindice perfectamente con la posición del porcentaje más cercano dentro del rango A2:A15. Por ejemplo, para Hugo, la posición 10 dentro del rango A2:A15 es la celda A11 la cual tiene el porcentaje 86 y la diferencia de 1 con el porcentaje de Hugo. Para Paco, la posición 7 dentro del rango es la celda A8 que tiene el porcentaje 76 y que es el valor más cercano. Ahora que ya sabemos la posición que ocupa el valor más cercano, utilizaremos la función INDICE para mostrar la calificación correspondiente con la siguiente fórmula:
=INDICE($B$2:$B$15, COINCIDIR(MIN(ABS($A$2:$A$15-E2)), $A$2:$A$15-E2))
Por supuesto, esta fórmula también debe ser introducida con la combinación de teclas Ctrl + Mayús + Entrar. En la siguiente imagen podemos observar el resultado final:
Es importante destacar que para Hugo y Luis se obtuvo el valor aproximado inferior pero para Paco se obtuvo el valor aproximado superior. De esta manera nuestra fórmula matricial obtiene el valor más cercano en todos los casos.
Ahora ya conoces tres opciones para buscar un valor aproximado en Excel, ya sea que requieres obtener el valor aproximado superior, inferior o el valor más cercano. Solo resta que descargues el libro de trabajo de manera que puedas utilizar las fórmulas desarrolladas con tus propios datos.