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
No me acepta usar (b2:b7-b9);
ResponderBorrarsabes por que?
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.
ResponderBorrarJorge, tengo una hoja1 donde en una columna van artículos, en otra la fecha de precio de cada articulo y en la otra el precio de ese articulo para esa fecha, fila por fila. En otra hoja2 yo incluyo fila por fila los artículos con una fecha de uso y lo q requiero es q en la tercer columna me traiga el precio para ese articulo con la fecha mas reciente a ese momento. Es posibl
BorrarHola....Cordial Saludo.....Por favor puedes indicarme como puedo realizar la siguiente tarea:
ResponderBorrarTengo 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....
Usando INDICE y COINCIDIR, esta última con búsqueda aproximada (es decir, con el tercer argumento faltante o igual a 1).
ResponderBorrarLa 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))
Hola, buenas tardes.
ResponderBorrarPodrí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.
Hola Vid
ResponderBorrarno 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
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:
ResponderBorrar[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
Por qué no usar la técnica indicada en la nota?
ResponderBorrarSuponiendo 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))}
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.
ResponderBorrarQuiero 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
Hola
ResponderBorrarbá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)
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
ResponderBorrarSi 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.
ResponderBorrarEn 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).
Hola Sr. Jorge, utilizo la fórmula (=INDICE(A2:A8,COINCIDIR(K.ESIMO.MENOR(
ResponderBorrarABS(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
COMO SE HACE ESO CON CODIGO VBA?
ResponderBorrarJuan-Ed
ResponderBorrarse 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.
Jorge:
ResponderBorrarAntes 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.
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.
ResponderBorrarEstimado Jorge,
ResponderBorrarMe 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.
Hola,
ResponderBorrarla 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.
hola,
ResponderBorrarestoy obteniendo error al intentar aplicar esta fórmula.
utilizo openoffice.
hay alguna otra alternativa de obtener el mismo resultado?
muchas gracias
saludos.
hola,
ResponderBorrarestoy obteniendo error al intentar aplicar esta fórmula.
utilizo openoffice.
hay alguna otra alternativa de obtener el mismo resultado?
muchas gracias
saludos.
Estimado, si tengo la siguiente matriz de numeros
ResponderBorrarM1 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..
={INDICE(B2:B7,COINCIDIR(MIN(ABS(B2:B7-B9));ABS(B2:B7-B9);0))}
ResponderBorrarEsta 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!
Monti,
ResponderBorrarel 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.
Buenos dias Jorge.
ResponderBorrarLlevo 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
Hola,
ResponderBorraren la nube de etiquetas hay un enlace al tema "rangos dinámicos". Alli verás varias notas que tratan sobre el tema.
Buenas noches,
ResponderBorrartengo 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
Marc,
ResponderBorrarsi 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
Que es el ctrl+mayus+enter ¿o como lo preciono en el teclado de mi compu? Salu2
ResponderBorrarEn 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.
ResponderBorrarPara introducir fórmulas matriciales hay que apretar simultáneamente estas teclas.
Gracias Jorge. Acudo regularmente a vuestro blog para hacer consultas o aprender cosas nuevas del Excel.
ResponderBorrarNo 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.
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.
ResponderBorrarComo 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).
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.
ResponderBorrarPara 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
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!!
ResponderBorrar{=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)))}
Cordial Saludo Jorge.
ResponderBorrarME han servido mucho tus instrucciones. Tengo un problema, estoy usando datos en formato HH:MM:SS y la formulación me ha servido {INDICE(B2:B7,COINCIDIR(MIN(ABS(B2:B7-B9)),B2:B7-B9))}, sin embargo cuando son muchos datos y en la columna de búsqueda hay celdas vacías, esta formulación deja de funcionar correctamente, que me sugieres que haga al respecto.
gracias
Allan Guisao
Allan, dos observaciones
ResponderBorrar1 - se supone que no debe haber espacios en blanco, MIN ignora los espacios en blanco (a diferencia de otras funciones que lo interpretan como cero);
2 - hay que distinguir entre datos y formato. Por ejemplo si en pantalla ves 15:30:00, lo que Excel "ve" es 0.645833333333333. Te sugiero que veas esta nota. Para el caso de cálculo de horas tienes que modificar un poco la fórmula, de esta manera:
=INDICE(B2:B7,COINCIDIR(MIN(ABS(B2:B7-B9)),ABS(B2:B7-B9)))
Tank Kemerad Dunkelman. Gracias por ofrecer desinteresadamente tus conocimientos:
ResponderBorrarHola Javier,
ResponderBorrarsi, es posible. El marco de un comentario no da para exponer la explicación por lo que estaré publicando una nota sobre el tema.
Más rápido de o que pensaba; aquí esta la nota.
ResponderBorrarHola jorge D.
ResponderBorrarsupongamos que se tienen una tablas que llamaremos A, con 100 artículos diferentes, la unidad de medida es en kilos y cada articulo tiene 10 unidades de el mismo articulo con pesos que difieren uno del otro.
se tiene otra tabla que llamaremos B, en la que solo se requieren algunos artículos especifico, de cada uno, un solo articulo pero estos se solicitan de un peso x.
la pregunta es:
como puedo como le puedo preguntar a la tabla B. para que busque en la tabla A el articulo especifico y me traiga el peso mas cercano al que estoy necesitando y que este sea superior 10 k al que necesito?
si puedes ayudarme te lo agradecería
La solución más obvia sería agregar 10 al peso buscado y usar BUSCARV con el cuarto argumento puesto a 1, para hacer una búsqueda aproximada. La explicación detallada excede el marco de un comentario y tal vez le dedique un post a la consulta.
ResponderBorrarPara los que reciben el resultado de #!VALOR! al ser una formula matricial se debe de cerrar con Ctrl + Shift + Enter
ResponderBorrarHola Estimado,
ResponderBorrarQuería consultar, como hago para encontrar el dato que se aproxime mas
en una tabla de desviación estándar "z", como en tu ejemplo, pero solo
con colocar por ejemplo "0,60" busque en todas las filas y columnas y
me de el dato que mas cercano a esa cantidad
Actualmente lo intente con una formula matricial, anidada pero solo me
busca en una columna y si trato de ampliar el rango, me da error,
adjunto archivo y formula, agradezco me puedan ayudar
Formula: =INDICE($B$2:$B$36;COINCIDIR(MIN(ABS($B$2:$B$36-N2));ABS($B$2:$B$36-N2);0))
Hola y disculpas por la demora en responder. Fijate en este post
Borrar