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

lunes, mayo 16, 2011

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)

29 comments:

hijo de 17 mayo, 2011 00:26  

¿Qué te parece esta propuesta?

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

como fórmula matricial.

Jorge L. Dunkelman 17 mayo, 2011 06:31  

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.

Amadeo 18 mayo, 2011 01:47  

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

Jorge L. Dunkelman 18 mayo, 2011 07:09  

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.

Anónimo,  18 mayo, 2011 12:51  

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.

Amadeo 18 mayo, 2011 19:35  

Hola Anónimo.

¡Son poderosas!

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

Eso es todo.

Saludos

Pedro 19 mayo, 2011 13:43  

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

Jorge L. Dunkelman 19 mayo, 2011 15:51  

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

Anónimo,  12 noviembre, 2012 01:47  

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

Jorge L. Dunkelman 12 noviembre, 2012 06:59  

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)




Anónimo,  18 noviembre, 2012 01:44  

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.

Jorge L. Dunkelman 21 noviembre, 2012 18:12  

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

Anónimo,  28 noviembre, 2012 00:11  

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.

ricardo m 25 enero, 2013 20:10  

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

Jorge L. Dunkelman 27 enero, 2013 10:19  

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).

Anónimo,  25 febrero, 2013 17:17  

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

Jorge L. Dunkelman 25 febrero, 2013 20:47  

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)

Anónimo,  27 febrero, 2013 19:12  

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

Jorge L. Dunkelman 02 marzo, 2013 20:48  

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))

camilaho 16 abril, 2014 18:55  

Me ayudó mucho. gracias!

bry17may 22 julio, 2015 05:52  

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?

Jorge Dunkelman 22 julio, 2015 07:02  

¿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?

bry17may 22 julio, 2015 07:32  

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

Jorge Dunkelman 22 julio, 2015 13:12  

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).

bry17may 22 julio, 2015 20:10  

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.

Manuel Cavero 26 enero, 2016 14:21  

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

Jorge Dunkelman 26 enero, 2016 18:31  

Hola Manuel

gracias a vos por colaborar.

Rafael Medina 29 julio, 2016 01:00  

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

Jorge Dunkelman 30 julio, 2016 17:56  

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.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP