miércoles, 26 de julio de 2017

Obtener Coincidencia Previa en Rango de Excel

En el grupo de facebook que gestiono, un usuario preguntaba por la manera de conseguir recuperar el valor previo coincidente.
Lo vemos mejor en la imagen que adjuntó:

Obtener Coincidencia Previa en Rango de Excel



El trabajo consistirá en generar una fórmula matricial que trabaje sobre un rango corrido,
que sea capaz de identificar la coincidencia anterior/previa en nuestro rango de trabajo.

La fórmula que insertamos en nuestra celda D3 ejecutada presionando Ctr+Mayusc+Enter:
=SI.ERROR(SI(B3="juan";INDICE($C$3:$C$16;-2+(MAX(($B$2:B2="juan")*(FILA($B$2:B2)))));"");"")


Relevante la parte que emplea la función INDICE:
INDICE($C$3:$C$16;-2+(MAX(($B$2:B2="juan")*(FILA($B$2:B2)))));"")
con la que obtenemos el dato/valor asociado a 'juan'... :
-2+(MAX(($B$2:B2="juan")*(FILA($B$2:B2))))
que sirve para identificar qué fila corresponde al 'juan' anterior.
Fíjate que ajusto restando un -2 a la 'fila' devuelta para corregir la posición del rango a partir de la fila 3 absoluta.


Importante, repito, es ver que se trabajan con rangos corridos, tipo:
$B$2:B2

Matricialmente trabajando con
($B$2:B2="juan")
conseguimos un conjunto de VERDADEROS para las coincidencias con el nombre 'juan' y FALSOS para los demÁs (VERDAERO=1 y FALSO=0); y por otro lado
(FILA($B$2:B2))
nos devuelve el número de fila de cada elemento.. al multiplicar ese número de fila por los VERDADEROS Y FALSOS conseguimos un rango virtual de ceros con otros datos-los número de filas-, por ejemplo para la celda D7
{0;3;0;0;0}
sobre estos valores nos quedamos con el valor máximo:
MAX(($B$2:B2="juan")*(FILA($B$2:B2)))
obteniendo por tanto la última/previa fila buscada:

Obtener Coincidencia Previa en Rango de Excel



Una vez conseguido el ´numero de fila aplicamos la función INDICE para retornar el importe deseado.

El resto de la fórmula, con los dos condicionales (SI y SI.ERROR) depuran los valores devueltos para el resto de nombres...

2 comentarios:

  1. Ismael, buenos día por allá, excelente aporte!
    ¿Cómo podría aplicar formato condicional si el importe anterior es menor?
    Gracias por compartir tu conocimiento, saludos cordiales.

    ResponderEliminar
    Respuestas
    1. Hola Alberto,
      puedes aplicar una Nueva regla de formato condicional sobre C3:D16 donde en la prueba lógica diga (suponiendo celda activa sea C3):
      =Y(D3<>0;C3>D3)

      necesitaremos el rango formulado en amarillo D3:D16

      Saludos

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.