Buscar un número mayor a otro, en una matriz de más de una columna

4 envíos / 0 nuevos
Último envío
adupa
Imagen de adupa
Offline
última acción: Hace 5 años 10 meses
alta: 17/05/2018 - 13:20
Puntos: 25
Buscar un número mayor a otro, en una matriz de más de una columna

Buenas tardes,

 

Requiero buscar el número inmediatamente mayor a un número x, en una matriz de más de una columna.

Ejemplo si en mi celda A2 tengo el número 5, nécesito que mi celda B2 este formulada, para que me ponga el número inmediatamente mayor que aparezca en mi matriz F6:N25.

Adjunto envio doc en excel, en la columna A, los valores que tengo, columna B, las celdas que tengo formular teniendo en cuenta la matriz F6:N25, y la columna D ejemplos de los valores que me deberian dar en la columna B despueste de formular. 

Muchas gracias 

AdjuntoTamaño
File libro1.xlsx27.64 KB
Cron
Imagen de Cron
Offline
última acción: Hace 8 meses 1 día
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 23/06/2010 - 12:30
Puntos: 12735
No hay una función que

No hay una función que resuelva esto. Lo puedes resolver de dos maneras: mediante código o utilizando una fórmula matricial.

Te voy a poner por aquí la solución mediante fórmula matricial, y voy a intentar explicarlo.

Lo primero es conseguir la diferencia entre cada elemento de tu tabla y el valor con el que queremos comparar. Esto es sencillo:

=$F$6:$N$25-$A$2

Por supuesto, al ser una fórmula matricial, debes introducirla pulsando Ctrl+Mays+Enter.

Hasta aquí no hace mucho, pero por detrás sabemos que dentro de esa celda hay una lista de valores donde se resta a cada valor de la tabla el valor de tu celda A2.

El segundo paso es eliminar los valores negativos. El tercer paso será eliminar los valores 0, porque tú has dicho que sea mayor. Por lo tanto, cuando encontremos valores menores, lo igualaremos a 0. Para esto nos vamos a aprovechar de una propiedad que tienen los valores VERDADERO y FALSO. Cuando multiplicas por ellos, VERDADERO equivale a 1, y FALSO a 0. Por lo tanto, ya lo tenemos:

=(($F$6:$N$25-$A$2)>0)*($F$6:$N$25-$A$2)

La primera parte es verdadera si el valor es positivo. Si no, es falsa. Ahora lo que tenemos será una lista de valores tal que: 0, 0, 0, 0.28, 0, 1.58, 0.15, 0, 0, 0, 2.48... Valores inventados para el post, por supuesto.

Nuestro objetivo es quedarnos con el valor menor de todos, que no sea 0. Si utilizamos la función K.ESIMO.MENOR, nos devolverá todos los 0 al principio. Si dividimos 1 entre cada valor (es decir, tomamos la inversa de cada valor), entonces nos interesará quedarnos con el mayor, por lo que utilizaríamos K.ESIMO.MAYOR. Pero esto tiene un inconveniente: todos los valores 0 nos darán un error #¡DIV/0!

=1/((($F$6:$N$25-$A$2)>0)*($F$6:$N$25-$A$2))

Lo más sencillo para quitarnos ese error es utilizar SI.ERROR:

=SI.ERROR(1/((($F$6:$N$25-$A$2)>0)*($F$6:$N$25-$A$2));0)

De esta manera, le hemos dado valor 0 a lo que sería infinito (1/0), por lo que ya podemos quedarnos con el valor mayor de todos, que corresponderá a la menor diferencia entre valores:

=K.ESIMO.MAYOR(SI.ERROR(1/((($F$6:$N$25-$A$2)>0)*($F$6:$N$25-$A$2));0);1)

Hasta aquí lo que hemos conseguido es quedarnos con el inverso de la mínima diferencia. Pero no es aún lo que necesitamos. Vamos a volver a invertir el valor (que ya es solamente uno, el mayor) para quedarnos con lo que era la mínima diferencia:

=1/K.ESIMO.MAYOR(SI.ERROR(1/((($F$6:$N$25-$A$2)>0)*($F$6:$N$25-$A$2));0);1)

Y para volver a conseguir el valor de la tabla de la derecha, tenemos que deshacer la diferencia sumándole lo que le quitamos al principio:

=1/K.ESIMO.MAYOR(SI.ERROR(1/((($F$6:$N$25-$A$2)>0)*($F$6:$N$25-$A$2));0);1)+$A$2

 

Recuerda: todas las fórmulas con matriciales, por lo que solamente funcionarán si las introduces pulsando Ctrl+Mays+Enter.

 

Un saludo

 

 

Brujito1loquillo
Imagen de Brujito1loquillo
Offline
última acción: Hace 4 años 5 días
alta: 12/03/2020 - 16:12
Puntos: 10
Y para el siguiente menor?

Esta respuesta es genial, me funcionó 100% pero ahora necesito lo mismo pero para el inmediatamente menor.
Muchas gracias!

Cron
Imagen de Cron
Offline
última acción: Hace 8 meses 1 día
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 23/06/2010 - 12:30
Puntos: 12735
Es igual, solo que donde

Es igual, solo que donde ponía >0 tienes que poner <0, y multiplicar por -1 para quitar el signo negativo. O sea, sustituir

(($F$6:$N$25-$A$2)>0)

 

por 

 

(($F$6:$N$25-$A$2)<0)*(-1)

o lo que es lo mismo en nuestro caso:

ABS(($F$6:$N$25-$A$2)<0)

 

Un saludo