Consulta Buscar V el siguiente valor del mismo objeto

4 envíos / 0 nuevos
Último envío
visitante (no verificado)
Imagen de visitante
Consulta Buscar V el siguiente valor del mismo objeto

Buenas Tardes, Tengo una duda con respecto a las funciones BuscarV y Consulta, no se si las pueda utilizar para el siguiente problema

Estoy buscando el valor asosiado a unos datos ej tengo que buscar el valor asociado a la letra a, pero necesito el valor positivo, estoy utilizando la función buscarV pero esta me devuelve el primer valor que encuentra que en este caso es el negativo pero yo necesito el positivo, no se como puedo hacer para buscar el siguiente dato asociado a a

Nombre   valor

a          -15

b           2

c           1

a           2

 

Lili

[tema creado desde comentario en http://www.necesitomas.com/formula-buscar-y-encontrar-inmediatamente-superior]

 

Etiquetas: 

Cron
Imagen de Cron
Offline
última acción: Hace 7 meses 3 semanas
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 23/06/2010 - 12:30
Puntos: 12735
HolaBuscarv siempre te

Hola

Buscarv siempre te devolverá la primera coincidencia.

Se puede devolver lo que pides de varias maneras, pero ninguna es sencilla.

1- por programación. Puedes utilizar VBA para programarte una UDF.

2- con fórmulas matriciales. Puedes utilizar una combinación de fórmula matricial y fórmulas tipo K.ESIMO.MENOR para devolver tu resultado.

Ambos casos son para iniciados. Si te interesa, dime qué solución te puede interesar más y profundizamos en ella.

 

Un saludo

Alcantara1 (no verificado)
Imagen de visitante
Formula con buscarv

Buen día!!!  

A mi de igual manera me interesa la respuesta en formula con formulas matriarcales

Cron
Imagen de Cron
Offline
última acción: Hace 7 meses 3 semanas
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 23/06/2010 - 12:30
Puntos: 12735
Claro!!

Claro!!

Vamos a construir la fórmula de dentro hacia fuera.

Supongamos que los valores de la pregunta los tenemos en las celdas A1 a B4, a efectos de que entender las referencias en las fórmulas. Supongamos también que en C1 ponemos el código que queremos recuperar. Por ejemplo, la letra "a".

Sabemos que no queremos el primer valor, porque es negativo, así que iremos a por el segundo que, como vemos, está en la fila 4.

Lo primero que vamos a hacer es construir por cada fila las condiciones que nos hacen falta. Y eso lo hacemos utilizando igualdades. Si se cumplen, devuelve verdadero, y si no, devuelve falso. Cuando multiplicamos verdadero por algo, es como si multiplicáramos por 1. Cuando multiplicamos por falso es como si multiplicásemos por 0. Así, en nuestro ejemplo podemos hacer esto:

=($A$1:$A$4=$C$1)*($B$1:$B$4>=0)*FILA(A1:$A$4)

¿Cómo funciona esto? Lo escribimos, y para introducirlo debemos pulsar Control+Enter. No vale pulsar Enter solamente. Sabremos que lo hemos hecho bien si aparece en la barra de fórmulas nuestra fórmula entre llaves, así:

{=($A$1:$A$4=$C$1)*($B$1:$B$4>=0)*FILA(A1:$A$4)}

Pero estas llaves no las podemos introducir nosotros, las introduce el propio Excel cuando pulsamos Control+Enter.

Lo que estamos haciendo aquí es devolver una ristra de valores, que se calculan así:

(A1=C1)*(B1>=0)*FILA(A1)    ---->   VERDADERO * FALSO * 1 ---->   0

(A2=C1)*(B2>=0)*FILA(A2)    ---->   FALSO * VERDADERO * 2 ---->   0

.....

(A4=C1)*(B4>=0)*FILA(A4)    ---->   VERDADERO * VERDADERO * 4 ---->   4


Es decir, aunque en nuestra celda se presenta solamente el resultado de la primera cuenta, en memoria está una ristra de resultados (en nuestro caso, 4, pero se puede hacer mucho más largo), cada uno diferente en función de sus valores.

Nosotros ahora nos queremos quedar con el primer valor que no es 0. Como vemos, lo que nos da es la fila donde se da el valor con ambas igualdades VERDADERO, y el resto son siempre 0. Si utilizamos MIN, nos dará siempre 0. Si utilizamos MAX, nos dará el último valor, pero no es lo que queremos. Queremos el primero que cumple. Entonces utilizaremos un truco matemático para quitarnos los 0. Hallaremos el valor inverso de cada resultado. Así:

=1/(($A$1:$A$4=$C$1)*($B$1:$B$4>=0)*FILA(A1:$A$4))

Recuerda introducir la fórmula con Control+Enter.

Ahora nuestros valores serán:

#¡DIV/0!, #¡DIV/0!, #¡DIV/0!, 0,25

Justo lo que queríamos: Los 0 ya no son 0. Ahora son errores. Y las filas son el valor inverso de la fila. Cuanto mayor es la fila, menor es el número. Por lo que queremos obtener el mayor de todos estos valores. Y eso se hace con MAX. Claro, que si hay errores, MAX devuelve error... Vamos a solucionarlo:

=SI.ERROR(1/(($A$1:$A$4=$C$1)*($B$1:$B$4>=0)*FILA(A1:$A$4));0)

Hemos sustituido todos los errores por 0. Como ahora queremos el máximo valor, el 0 ya no nos molesta. Ahora sí podemos extraer el máximo.

=MAX(SI.ERROR(1/(($A$1:$A$4=$C$1)*($B$1:$B$4>=0)*FILA(A1:$A$4));0))

Y nos devolverá 0,25. Pero no queremos eso, queremos saber la fila... Tenemos que volver a calcular la inversa.

=1/MAX(SI.ERROR(1/(($A$1:$A$4=$C$1)*($B$1:$B$4>=0)*FILA(A1:$A$4));0))

Ya sí nos devuelve 4, como queríamos.

Ya solo nos falta convertir el 4 en el valor que hay en la 4º fila, y eso se hace con INDICE

=INDICE($B$1:$B$4;1/MAX(SI.ERROR(1/(($A$1:$A$4=$C$1)*($B$1:$B$4>=0)*FILA(A1:$A$4));0)))

Por tercera vez, todo lo anterior solo funciona si introduces las fórmulas con Control+Enter.

Si en vez de obtener únicamente el primero de los valores que cumplen quieres obtener todos los valores que cumplen, entonces hay que utilizar K.ESIMO.MAYOR en vez de MAX, y el parámetro que utilizaremos para decirle cuál valor seleccionar estará en función de la fila en la que ponemos el resultado. Así, con la misma fórmula, al arrastrarla hacia abajo irá devolviendo los siguientes valores que cumplen.


Otro apunte: en ocasiones, cuando se trabaja con muchas filas, puede ocurrir que al calcular el inverso del inverso, se produzcan errores de redondeo. En esos casos, la última fórmula antes de la de INDICE habría que meterla dentro de una función REDONDEAR con 0 decimales.


Si necesitas más explicaciones de algún paso concreto, no dudes en preguntar.

Un saludo