lunes, mayo 16, 2011

Encontrar el último número positivo o negativo en un rango

En uno de los proyectos que estoy desarrollando me enfrenté con la necesidad de encontrar el último número negativo de una serie. Específicamente se trataba de calcular el período de recuperación de una inversión (Payback), pero este problema puede presentarse en varias situaciones.

En el pasado he mostrado cómo encontrar el último elemento en un rango usando la función BUSCAR (LOOKUP). Pero en este caso se trata de encontrar el último elemento bajo la condición que sea negativo.

Supongamos esta serie de números en el rango B1:B9



Nuestro objetivo es crear una fórmula que de cómo resultado el último número negativo de la serie, en nuestro caso -30

La fórmula que usamos es la siguiente:

=BUSCAR(2;1/(B1:B9<0);B1:B9) 



¿Cómo funciona esta fórmula?
 
La función BUSCAR tiene dos configuraciones: la vectorial y la matricial. Más sobre el tema puede leerse en la ayuda en línea de Excel. En nuestro caso usamos la forma vectorial que tiene esta sintaxis:

BUSCAR(valor_buscado, vector_de_comparación, [vector_resultado])


En nuestro ejemplo, el vector de comparación es creado por la expresión 1/(B1:B9<0) Esta resulta en una serie de unos y valores de error. 






Cuando el valor de la celda de la columna B es negativo el resultado es 1 (1/1); cuando es positivo el resultado es #DIV0! (1/0).
A pesar de lo que dice la ayuda en línea de Excel (Cuando BUSCAR no puede encontrar el valor buscado, la función muestra el valor más grande en vector_de_comparación que es menor o igual al valor_buscado), BUSCAR da el último valor que es menor o igual al buscado. Por este motivo usamos “2” como valor buscado. De la misma manera podríamos usar 3 o cualquier otro número mayor que 1. 


Para calcular el último número positivo en la serie modificamos levemente nuestra fórmula 



=BUSCAR(2;1/(B1:B9>=0);B1:B9)

32 comentarios:

  1. ¿Qué te parece esta propuesta?

    =INDIRECTO("a"&MAX(FILA(A1:A9)*(A1:A9<0)))

    como fórmula matricial.

    ResponderBorrar
  2. Muy ingeniosa! Como no agregaste una explicación me permito hacerlo yo:
    FILA(A1:A9) crea una serie de números del 1 al 9
    (A1:A9)<0 crea una serie de valores FALSO o VERDDERO
    al multiplicar los vectores entre si obtenemos una serie de 0, cuando la condición no se cumple, y el número de file cuando la celda contiene un número negativo.
    MAX nos da el número de la última fila que contiene un valor negativo. Y, finalmente, al combinar este resultado con "a", obtenemos la dirección de la celda buscada que sirve de argumento para que INDIRECTO calcule el valor.

    ResponderBorrar
  3. Estimado Jorge L. Dunkelman

    Es bastante difícil ver como trabaja la forma matricial, ya que mucha gente no tienen conocimientos de matemática tan avanzada.
    Estaría bueno hacer una gráfica para ver como trabaja la fórmula matricial.

    Ejemplo

    Tengo valores positivos y/o negativos en los rangos que figuran en la fórmula matricial.

    A través de un gráfico mostrar que es lo que hace y como lo hace.
    No te olvides que lo gráfico es más visible que las palabras.

    {=SUMA((J7:J9)*(K7:K9)*(K7:K9<0))}

    Saludos

    Amadeo Govoni
    Aragentina

    ResponderBorrar
  4. Amadeo,

    en alguna de las primeras notas de este blog puse una explicación del funcionamiento de las fórmulas matriciales. A partir de tu comentario estoy pensando en publicar una nota más completa.

    ResponderBorrar
  5. La verdad es que las fórmulas matriciales es uno de las herramientas más poderosas de Excel. Es curioso que sea tan poco usado.

    ResponderBorrar
  6. Hola Anónimo.

    ¡Son poderosas!

    la mayoría de las personas no son expertos en Análisis Matricial.

    Eso es todo.

    Saludos

    ResponderBorrar
  7. A que hace referencia el valor buscado que pones como 2. He usado cualquier número y me sigue dando igual el resultado.

    ResponderBorrar
  8. Tal como pongo en el anteúltimo parágrafo de la nota.

    ResponderBorrar
  9. Genios,necesito que me ayuden a poder saber el primer valor, segundo, tercero, cuarto... de un rango donde en intermedio del mismo puede haber vacios

    ResponderBorrar
  10. Digamos que los valores están en el rango A3:A13, que incluye celdas vacías, y en la celda A1 opnes el valor de orden. Puedes usar esta fórmula:

    =K.ESIMO.MAYOR(A3:A13,A1)




    ResponderBorrar
  11. Muy buenas. Son ustedes impresionantes!! Pero.....tengo un problema y espero ver si se les ocurre alguna manera de plantearlo: En la primera columna de una serie están las fechas y en la segunda hay valores positivos y negativos que corresponde a cada día (puede haber varios positivos seguidos, o varios negativos, o alternarse incluso). Lo que intento hacer es sumar parcialmente los resultados negativos, es decir, si existen por ejemplo 4 fechas seguidas con valor negativo, que me sume el total negativo de esos 4 días, si son dos, pues dos, etc. y que al encontrar un valor positivo, pues que entienda que ya no lo ha de sumar, y que la proxima vez que exista un valor negativo , pues que lo sume a los siguientes valores negativos de la serie (hasta encontrar nuevamente un valor positivo).

    ¿Se puede hacer? He buscado y buscado en la web y no lo encuentro...

    Desde ya muchas gracias por el interés y por compartir su saber.

    Un fuerte abrazo.

    Carlos.

    ResponderBorrar
  12. Hmmm, no se me ocurre nada con fórmulas. Podría hacerse con Vba (macros).

    ResponderBorrar
  13. Hola de nuevo.
    Me imaginaba que la respuesta iría relacionada con Macros, pero me veo del todo incapaz de alcanzar el nivel necesario para hacerlo.

    De momento, iré haciendo a mano las sumas de esos valores negativos, apuntando en una tercera columna las sumas parciales que cumplan la condición de negativo.

    Gracias de todos modos.

    Carlos.

    ResponderBorrar
  14. que tal Jorge L. Dunkelman, veo que tus conocimientos en excel son amplios. mi nombre es ricardo muñoz y tengo 2 problemas que no puedo resolver:

    1. tengo una formula para buscar el ultimo valor que sea mayor a "cero":

    =BUSCAR(1,(1/(C3:C8>0)),C3:C8)

    y jala bien, el detalle es que lo quiero utilizar para que me traiga el ultimo numero de diferentes rangos en una misma columna, es decir, que me traiga el ultimo valor mayor a "cero" de los rangos C3:C8 C10:C20 y C24:230

    no hago un solo rango C3:C30 por que entre esos rangos tengo titulos en texto y si en los ultimos rangos no tengo nada me trae el ultimo texto!

    espero me puedas apoyar!


    2. en una base de datos estoy poniendo 4 listas desplegables que quiero que me funcionen de la siguiente forma: la primer lista desplegable (llamemosle Nivel 1), si me seleccionan el primer valor, que para la siguiente lista desplegable (nivel 2), solo me ponga los valores de nivel 2 que corresponden al nivel 1 seleccionado, y para la siguiente lista despleglable (nivel 3), solo me enliste los valores que corresponden al valor seleccionado en el nivel 2.

    mejor dicho, la primer lista sería de estados de la republica, si me seleccionan el edo de michoacan, que para la siguiente lista de nivel 2 me ponga solo los municipios del estado de michoacan, y si en ese nivel 2 seleccionan el municipio de morelia, que en la siguiente lista desplegable me ponga solo las colonias del municipio de morelia y asi consecutivamente.

    Esto ya lo logre con algunas celdas de apoyo. es decir, los valores de las listas desplegables estan en blanco, si el valor seleccionado de la lista nivel 1 es "michoacan" en un indice con la formula buscarv me trae un numero indicador, por ejemplo: se escogio michoacan y este es el valor 16 y entonces me trae el numero 16 en la celda contigua, ahora para pintar los valores de la segunda lista, en cada celda vacia pongo la formula: si el valor que trajo es=16 entonces busca y traeme el valor que corresponde a los municipios de michoacan y asi la lista desplegable se pinta con los valores que le corresponden a michoacan, y asi consecutivamente con los otros niveles.

    "espero no heberte hecho bolas" jeje

    el problema es cuando esa herramienta la quiero utilizar en una base de datos, por que al copiar la formula a las celdas hacia abajo, ya no aplica mi listado que me pinta los valores segun el valor seleccionado en nivel 1, por que yo le digo que me pinte la lista segun el valor que traiga, pero solamente tomará el valor de la primer fila y no el de la fila que le corresponda por ejemplo si estoy en la fila 5 o 30 etc. sigue considerando el numero indicador de la fila 1

    sabes si hay alguna formula para simplificar, corregir o mejorar este tema, o algun macro?

    saludos y gracias

    ResponderBorrar
  15. Ricardo, por las dudas que no hayas visto mi respuesta a la misma consulta que pusiste en la otra nota:

    En lo que hace a tu primer consulta, la fórmula a usar es =BUSCAR(2,1/ESNUMERO(C3:C30),C3:C30) Esta da como resultado el último valor numérico, no importa que en el rango haya celdas vacías o que la última contenga texto. En cuanto a la segunda consulta, la forma de hacer lista desplegables dependientes es usando INDIRECTO junto con nombres definidos. Fijate en las notas sobre listas deslegables dependientes publicadas en el blog (puedes acceder a todas pulsando el enlace correspondiente en la nube de etiquetas).

    ResponderBorrar
  16. Hola, hasta ahore he resulto todos mis problemas con excell, pero ahora llevo un par de dias buscando y no me sale nada correcto. me gustaría me ayudaran si es posible (de macros no tengo ni idea).
    Tengo dos columnas la primera con fechas 8del 1 de enero al 31 de marzo por ejemplo) y la segunda con temperaturas mínimas diarias (algunos dias hay heladas, temperaturas por debajo de cero).
    Lo que no se haces es que me de la fecha de la ultima helada.
    Si algien me puede ayudar, que me eche un cable

    ResponderBorrar
  17. Supongamos que las fechas están en el rango A1:A31 y las temperaturas correspondientes en el rango B1:B31, para encontrar la fecha correspondiente al último número(temepratura) negtivo usamos

    =BUSCAR(2,1/(B1:B31<0),A1:A31)

    ResponderBorrar
  18. Hola y gracias, efectivamente así lo encuentro y funciona perfecto. Ahora tengo el sigueinte problema: encontrar no el ultimo valor; sino el primer valor que es menor que cero.LLPS

    ResponderBorrar
  19. Suponiendo que los valores están en el rango A1:A20

    =INDICE($A$1:$A$20,COINCIDIR(VERDADERO,INDICE($A$1:$A$20<0,0,1),0))

    ResponderBorrar
  20. Hola, inceíble blog me ha ayudado mucho de momento siempre me ha gustado Excel pero hay ciertas "áreas" que no he explorado mucho entre ellas las fórmulas de referencia de celdas. Pero ahora se me hizo necesario utilizarlas de alguna manera pero tengo un poco de inconvenientes.

    A diferencia del ejemplo tengo mis datos a lo largo de diferentes columnas y no de filas, quiero encontrar el último dato menor a 50 y mi fórmula es la siguiente

    =BUSCAR(1;1/(F221:K221<50);F221:K221)

    Si encuentro el último dato menor a 50 pero lo que quiero es encontrar la celda y no el dato y no se como incluirlo en otra fórmula el problema también es que hay datos repetidos y cuando lo incluyo en coincidir me da como resultado como por ejemplo la columna 2 cuando lo que quiero es la columna 3 ya que en ambos tengo el mismo valor pero me interesa la última.

    Hay alguna manera de resolverlo ya sea con fórmulas matriciales o cualquier método que no sea macros?

    ResponderBorrar
  21. ¿Cuál sería el criterio para determinar si se busca la primer instancia o la segunda o cualquier otra en caso de valores repetidos?

    ResponderBorrar
  22. Sólo me interesa la posición de la última instancia

    ResponderBorrar
  23. Para determinar el número de columna donde se encuentra la última instancia del número buscado tenés que usar la fórmula matricial:

    =MAX(COLUMN(F221:K221)*(F221:K221<50))

    Para la dirección de la celda combinas con la fórmula anterior la función DIRECCION

    =DIRECCION(221,MAX(COLUMN(F221:K221)*(F221:K221<50)))

    Ambas fórmulas son matriciales (Ctrl-Mayúsculas-Enter).

    ResponderBorrar
  24. Muchas gracias Jorge Dunkelman, aprenderé más de las fórmulas matriciales que no sabía que podía sacarles tanto provecho, también dejame decirte que es impresionante el trabajo que haces, hay muchos blogs y buenos pero es muy difícil encontrarse con uno que este activo desde hace casi 10 años y la ayuda fue inmediata. Muchas gracias en serio la cantidad de datos es enorme y hacerlo manual hubiera sido un gran dolor de cabeza.

    Saludos Bryan.

    ResponderBorrar
  25. Una vez más Jorge, tengo que darte las gracias.
    Tenía un problema parecido al tuyo, es decir, en mi caso necesitaba encontrar el último número de una secuencia que fuese distinto a 0.
    He utilizado tu fórmula, modificando la condición; es decir

    BUSCAR(2;1/(CB9:CB18<>0);CB9:CB18)

    El resultado te ofrece el último núnero de la secuencia, sea este positivo o negativo.

    Gracias, Jorge

    ResponderBorrar
  26. Hola Manuel

    gracias a vos por colaborar.

    ResponderBorrar
  27. tengo un rango con vacíos y letras al usar =BUSCAR(2;1/(I1:I800>0);I1:I800) me da el ultimo valor de texto; como deseo el ultimo numero utilizo =BUSCAR(2;1/ESNUMERO(I1:I800);I1:I800) y me da el cero esta bien pero quiero es el ultimo numero positivo osea mayor de cero y cuando le pongo a la formula =BUSCAR(1;(1/ESNUMERO(I1:I800>0));I1:I800) me da error #N/A; señor jorge que puedo hacer

    ResponderBorrar
  28. Aparentemente hay un error N/A en alguna de las celdas del rango. Tendría que ver el cuadeno para darme una idea del error.

    ResponderBorrar
  29. Buenas noches, busco el ultimo valor positivo en una fila, use la formula =BUSCAR(2,1/(D10:BB10>0),(D10:BB10)), y es correcto, pero requiero el valor de una columna antes que esa, es decir la ubicacion del valor es la celda AV10, y el valor que necesito obtener es el de la cela AT10, como puedo hacer?

    ResponderBorrar
  30. Muchas gracias Jorge, quiero poner un ejemplo del problema que tengo. son 10 pedidos, cada uno consta de 5 columnas, en la tercer columna de cada pedido esta el precio de compra.
    El primer pedido va de la columna b a la f, el segundo pedido va de la columna g a la k, y así sucesivamente, cuando lleno un pedido todas las columnas de cada pedido tienen un valor, lo que necesito es una formula que encuentre el ultimo pedido lleno y me devuelva el valor de la tercer columna en cada fila, apreciaría mucho tu ayuda

    ResponderBorrar
  31. Hola, fijate lo que pongo en el enlace Ayuda (en la parte superior del blog) y enviame el archivo o un ejemplo para que pueda hacerme una idea más precisa de lo que querés hacer.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.