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

miércoles, febrero 20, 2008

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:

34 comments:

Anónimo,  26 febrero, 2008 13:57  

No me acepta usar (b2:b7-b9);
sabes por que?

Jorge L. Dunkelman 26 febrero, 2008 19:16  

Tendrías que ser más explícito para que pueda ver dónde está el problema. O mandarme el archivo para que le de un vistazo.

Anónimo,  29 febrero, 2008 23:44  

Hola....Cordial Saludo.....Por favor puedes indicarme como puedo realizar la siguiente tarea:
Tengo una tabla con rango dias (desde - Hasta) y al frente de cada rango, un porcentaje.
En otra hoja del mismo libro debo evaluar los dias de una celda y confrontarlos con los dias de la tabla de rango de dias y tomar el oprcentaje que correspona....Como Puedeo hacer esto?????
Por su atencion y colaboracion...muchas gracias....

Jorge L. Dunkelman 01 marzo, 2008 00:16  

Usando INDICE y COINCIDIR, esta última con búsqueda aproximada (es decir, con el tercer argumento faltante o igual a 1).
La matriz de búsqueda de INDICE será la columna con los porcentajes; la matriz de búsqueda de COINCIDIR, la columna "Desde", y el valor buscado la fecha para la cual quieres encontrar el porcentaje correspondiente.
Supongamos esta tabla
Desde            Hasta      Porcentaje
01/01/2008 15/01/2008 10%
16/01/2008 31/01/2008 20%

siendo el rango de A1 a C3, si la fecha a buscar está en E2, la fórmula sería

=INDICE(C2:C3;COINCIDIR(E2;A2:A3))

vid 04 marzo, 2008 16:23  

Hola, buenas tardes.

Podría decirme como puedo hacer lo siguiente:

Tengo dos celdas con validación que me remiten a las filas y columnas de una matriz. Deseo que en otra celda, me dé el valor en función de el valor seleccionado en cada una de ellas. Me explico: Tengo la siguiente matriz:

Caceres Badajoz Merida Zafra
Caceres 0 2 8 9
Mérida 1 4 0 11
Valverde5 8 9 10

Si en la celda C1 tengo Cáceres y en la C2 Merida la C3 me debe dar automaticamente 8. ¿Qué función empleo en C3 y cómo?

Gracias anticipadas.

Jorge L. Dunkelman 12 marzo, 2008 22:23  

Hola Vid
no logro entender cómo está organizada tu tabla. Puedes mandarme el archivo?
De acuerdo a como está organizada la tabla, se podría resolver cpn INDICE y COINCIDIR o con SUMAPRODUCTO

Equipo Quince 31 julio, 2008 02:51  

Saludos Sr. Jorge, solicito amablemente su apoyo, para darle solución al siguiente problema que se me presenta en una tabla de excel, tengo los siguientes valores:

[1/16] [0.0625]
[1/8] [0.125]
[3/16] [0.1875]
[1/4] [0.250]
[5/16] [0.3125]

necesito que cuando yo indique un valor diferente a las indicadas en la columna 2 (ejemplo:0.123), obtenga la posición más cercana al valor buscado (en este caso particular 1/8).... Agradezco la atención prestada al mismo

Jorge L. Dunkelman 31 julio, 2008 22:24  

Por qué no usar la técnica indicada en la nota?
Suponiendo que tus datos estén en el rango A1:B5, la fórmula (matricial) sería

={INDICE(A1:A5,COINCIDIR(MIN(ABS(B1:B5-B8)),B1:B5-B8))}

Anónimo,  07 octubre, 2008 18:37  

Hola que tal. Estoy haciendo una planilla con fechas de cumpleaños. Una columna de la tabla es los dias que le faltan a cada integrante de la lista para cumplir años.

Quiero hacer una celda que diga de quién es el proximo cumpleaños; para eso, estoy buscando una formula que busque la cantidad de dias que faltan, que sea mas cercana a 0 y automaticamente me aparezca el nombre del proximo cumpleañero.
muchas gracias

Jorge L. Dunkelman 07 octubre, 2008 21:56  

Hola

básicamente hay que usar la técnica descrita en la nota. El problema surge con la forma en que Excel trata las fechas, por lo que hay que usar algunos trucos. La explicación es un poco larga para ponerla en el marco de un comentario, así que irá en una nota (que espero publicar en los próximos días)

Jhonny 07 agosto, 2009 04:03  

Si es cierto la función ABS no permite el uso de A1:A7-F3 por ejemplo como varios valores de valor absoluto en un arreglo, rechaza hacerlo, y necesito con URGENCIA saber solventarlo. Mil Gracias

Jorge L. Dunkelman 07 agosto, 2009 07:43  

Si necesitas una respuesta con urgencia, es mejor dirigir la consulta a alguno de los muchos foros de ayuda de Excel que hay en la red.
En cuanto a la consulta, no tengo claro lo que quieres hacer, pero si estás siguiendo el ejemplo de la nota ten en cuenta que se trata de una fórmula matricial (que se introduce apretando Ctrl+Mayúsculas+Enter).

Aitor,  01 octubre, 2009 13:03  

Hola Sr. Jorge, utilizo la fórmula (=INDICE(A2:A8,COINCIDIR(K.ESIMO.MENOR(
ABS(B2-A2:A8),1),ABS(B2-A2:A8),0)))para obtener el valor menor (más cercano a 0)de unas mediciones de piezas donde los valores de las celdas son las desviaciones del valor nominal, he tomado esta otra fórmula para que tome en cuenta los valores negativos; pero aplico una variación, en vez de compararlo con el valor de una celda (en su caso B9) yo pongo el valor "0"; supongo que será correcto. Pero como es una tabla que se va alimentando y el rango de celdas es p.ej. B18:B2000 y sólamente está rellena hasta el B250 me toma las celdas vacías como el "0" y por lo tanto éste es el valor más cercano.
Me podría indicar cómo puedo excluir los valores "0" y las celdas vacias?
Muchas gracias por adelantado, y le comento que su información me es muy útil;Aitor

juan-ed 11 octubre, 2009 02:37  

COMO SE HACE ESO CON CODIGO VBA?

Jorge L. Dunkelman 11 octubre, 2009 17:28  

Juan-Ed
se escribe el código :)
Y ahora en serio, un comentario no es el marco para poner un código. Puedes hacer una búsqueda en alguno de los muy buenos foros que se ocupan de programación Vba. Supongo que encontrarás muchos códigos que podrás descargar.

Anónimo,  04 marzo, 2010 04:17  

Jorge:

Antes que nada quiero agradecer el tiempo que dedicas para despejarnos nuestras dudas.

Te pido de favor me ayudes con el siguiente problema, tengo una tabla donde se almacenan datos por mes, quiero que cuando vaya a almacenar el mes siguiente, si elijo por ejemplo: "marzo" y de este mes ya almacené la información, se despliegue un mensaje que me diga el mes ya está captado, o mejor aún que automáticamente me dé el mes que debo capturar.

Gracias por tu apoyo.

Jorge L. Dunkelman 04 marzo, 2010 06:52  

Este tipo de consultas, que no están relacionadas con el tema de la nota, hay que dirigirlas por mail directo. Fijate en la pestaña Ayuda en la parte superior del blog.

AMAIA 04 marzo, 2010 10:22  

Estimado Jorge,
Me gustaría saber si existe la posibilidad de condicionar una celda para que me salga el cuadro de insertar comentario automáticamente, por ejemplo al obtener un número X. Sé insertar manualmente un comentario, pero quiero que este me salga en el caso de obtener en una celda un número X.

Jorge L. Dunkelman 04 marzo, 2010 11:32  

Hola,
la forma de hacerlo es programando un evento (macro). Puedes hacer una búsqueda en el blog con la palabra "eventos" para ver los notas sobre el tema.

damian 20 mayo, 2011 17:16  

hola,
estoy obteniendo error al intentar aplicar esta fórmula.
utilizo openoffice.
hay alguna otra alternativa de obtener el mismo resultado?
muchas gracias
saludos.

damian 20 mayo, 2011 17:17  

hola,
estoy obteniendo error al intentar aplicar esta fórmula.
utilizo openoffice.
hay alguna otra alternativa de obtener el mismo resultado?
muchas gracias
saludos.

Andrés Miller 09 noviembre, 2011 21:31  

Estimado, si tengo la siguiente matriz de numeros

M1 10 12 15 17
M2 11 13 17 19
M3 13 15 20 25

Quiero que me encuentre el valor mas cercano a 15 en la 2º fila de la matriz (fila M3), en el fondo, que M3 sea una variable también.

Entonces, en resumen, si yo ingreso los datos:

M1 13 debiese entregar -> 15
M3 22 debiese entregar -> 25

amiller@prdw-av.com

etc..

montiguitarrero,  28 mayo, 2012 01:33  

={INDICE(B2:B7,COINCIDIR(MIN(ABS(B2:B7-B9));ABS(B2:B7-B9);0))}
Esta es la formula exacta ya que se olvidaron del tercer argumento en la formula coincidir que puede ser 1,0 o -1 ; si uno no pone nada excel supone que es 1; por tanto el 0 en COINCIDIR hace que el valor encontrado sea exacto por eso tambien se aumenta el abs en el segundo miembro de COINCIDIR. Espero que haya sido de ayuda! Saludos!

Jorge L. Dunkelman 28 mayo, 2012 07:26  

Monti,
el tercer argumento tiene que ser 1, ya que estamos haciendo una búsqueda aproximada. Tu fórmula, si bien puede funcionar en ciertos casos, por lo general dará error de tipo #NA. Por ejemplo, si buscamos el valor más cercano a 15.2.

Anónimo,  13 agosto, 2012 16:21  

Buenos dias Jorge.
Llevo varios dias intentando buscar una solucion a mi problema y no lo encuentro. lo que quiero es encontrar de entre los elementos de una lista el valor mas cercano a otro. Pero la lista es un rango dinamico, es decir su longitud puede variar en funcion de si introducimos mas o menos datos. entonces me gustaria poder en vez del B2:B7 de tu ejemplo, meter un rango dinamico que me de el rango de la lista hasta su ultimo valor. No se si me explico bien. El caso es que si selecciono la lista entera y contiene celdas vacias al final, el resultado me sale 0.
Muchas gracias
Un saludo

Jorge L. Dunkelman 18 agosto, 2012 13:47  

Hola,

en la nube de etiquetas hay un enlace al tema "rangos dinámicos". Alli verás varias notas que tratan sobre el tema.

Marc Bellés Bel 11 octubre, 2012 02:25  

Buenas noches,

tengo una matriz de calculos:

cantidad 70 140 240
Rotación 23.4 11.7 6.8

Tengo una rotación deseada de 3.5.
Como puedo encontrar el valor que más se acerque más a 3.5. he probado las diferentes formulas que habéis indicado aunque si me resuelve me pone #N\A o bien me dice error y no puedo terminarlo.

Me dice error en la formula

Muchas Gracias un Saludo

Jorge L. Dunkelman 15 octubre, 2012 19:36  

Marc,
si tus dats están en filas, no en columnas como en el ejemplo, tienes que adaptar la fórmula de acuerdo. Además asegurate de introducir la fórmula como matricial, pulsando simultáneamente Ctrl, Mayus. y Enter

Anónimo,  07 marzo, 2014 22:01  

Que es el ctrl+mayus+enter ¿o como lo preciono en el teclado de mi compu? Salu2

Jorge Dunkelman 08 marzo, 2014 19:07  

En el teclado de la computadora hay una (de hecho, dos) Ctrl (Control); otras dos que dicen Mayúsculas o Shift o tiene dibujada un flecha que apunta hacia arriba y en la parte derecha del teclada una teecla que dice Enter o Intro o tien dibujada una flecha que apunta hacia la izquierda.
Para introducir fórmulas matriciales hay que apretar simultáneamente estas teclas.

Unknown 11 junio, 2014 23:37  

Gracias Jorge. Acudo regularmente a vuestro blog para hacer consultas o aprender cosas nuevas del Excel.

No sé si en Excel se pueda, pero el caso que tengo es con una base de datos de varios registros y campos, pero me interesa son dos columnas que contienen información de coordenadas para cada entrada: latitud y longitud (p.ej., 8.8105, -79.5108).

Si quiero hacer una búsqueda en la base de datos de la coordenada más parecida de un punto nuevo, ¿cuál sería la mejor forma de hacer lo que se explica arriba pero tomando como referencia ambos datos (x,y)?

Saludos, y gracias de antemano.

Jorge Dunkelman 12 junio, 2014 19:35  

Para hacerlo tenemos que reducir los ods valores a uno, ya que buscamos el punto que cumpla con las dos condiciones (mínimo latitud y mínimo longitud). Una posibilidad es crear una columna auxiliar que combine ambos valores. Suponiendo el orden jerárquico latitud-longitud; en tu ejemplo el valor buscado sería 88105.79511. Lo mismo haríamos con los restantes valores de las columnas y partir de ahí la búsqueda se haría con una fórmula como la que muestro en la nota.
Como verás, la explicación excede el marco de un comentario (por ejemplo, como calcular el valor combinado), por lo que te invito a ponerte en contacto conmigo por mail privado (fijate en el enlace Ayuda en la parte superior de la plantilla).

Unknown 14 junio, 2014 01:38  

Gracias Jorge, por tan pronta respuesta al comentario del 11 de junio. Le he estado dando vueltas al asunto desde entonces, y hasta había pensado hacerlo como mencionas antes de leer tu respuesta. Sin embargo, hacerlo de esa forma superpone la búsqueda de un eje sobre el otro (en el ejemplo colocado, buscaría primero la latitud más cercana para luego buscar la longitud, pero no necesariamente esto signifique que encontraremos la distancia más corta.

Para solucionar esto, he colocado en una columna auxiliar de la bbdd el cálculo de distancia entre dos puntos, tomando las coordenadas de cada registro contra las coordenadas a buscar ( d = √ (x₂ - x₁)² + (y₂ - y₁)² ). Como busco el registro más cercano a la consulta, busco el valor que más se aproxime a 0. Ya con la fórmula que describes en la segunda nota publicada sobre este mismo tema (para corregir el problema del ordenamiento), conseguí lo que buscaba.

Muchas gracias Jorge por tu apoyo. Lo cierto es que aún no entiendo bien como funcionan las fórmulas matriciales, por lo que hice un copy/paste y cambiando los valores de celda,p.ej., =INDIRECTO((DIRECCION(FILA(L2:L20916)+ COINCIDIR(MIN(ABS(L20919-L2:L20916)), ABS(L20919-L2:L20916),0)-1,5))). Pero prometo seguir leyendo tu blog para aprender al respecto.

Saludos y nuevamente gracias.
E.Vásquez

Unknown 14 junio, 2014 02:31  

Mejor aún, reemplazo el contenido del paréntesis de la fórmula ABS(*) por la fórmula de cálculo de distancia entre puntos, aplico el mismo matricial y... voilà! Pude hacer una tabla para hacer consultas masivas de una base contra otra para hallar información por coordenadas en Excel!! Gracias de nuevo Jorge!!

{=INDIRECTO((DIRECCION(FILA(M2:M20916)+COINCIDIR(MIN(ABS(RAIZ((($B$2:$B$20916-$B$20922)^2)+(($C$2:$C$20916-$C$20922)^2)))),ABS(RAIZ((($B$2:$B$20916-$B$20922)^2)+(($C$2:$C$20916-$C$20922)^2))),0)-1,4)))}

Publicar un comentario

Seguidores

Google+ Followers

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP