Últimamente he estado participando en el foro Excel de http://www.exceluciones.com
Una de las preguntas que han surgido allí, y que aparecen a menudo en varios foros, es qué fórmula nos permitiría encontrar el último valor de un rango. La idea es que tenemos, por ejemplo, un rango en una columna donde vamos agregando valores, por ejemplo el rango A1:A20. Si agregamos un valor en la celda A21, queremos una fórmula que de cómo resultado el valor de A21.
La solución "clásica" es utilizar la función BUSCAR (LOOKUP), poniendo como argumento el número más elevado que Excel acepta en una celda (al valor 1E+307) y como segundo argumento el rango donde debemos encontrar el último valor (hay que prestar atención que estamos buscando el último valor en el sentido de orden y no de tamaño).
El funcionamiento de la función BUSCAR (LOOKUP) es tal que si el valor buscado es mas alto que cualquier valor disponible en el rango de la búsqueda esta función da como resultado el ultimo valor que encuentren.
En este 
ejemplo vemos que cuando el valor del primer argumento (1000) es mayor que el valor del último valor en el rango (172), la fórmula da como resultado 172. Si el valor del primer argumento de BUSCAR (LOOKUP) es, por ejemplo 126, el resultado de la fórmula será 118.
LOOKUPS_
Si todas las celdas del rango de números tienen contenido y A1 y A2 también, en el caso del ejemplo se me ocurre que otra opción sería:
ResponderBorrar=INDIRECTO("A" & CONTARA(A:A))
En caso de que A1 y A2 no tuviesen contenido, habría que sumar 2 al resultado de CONTARA.
Hola Jaizki
ResponderBorrarbuen aporte. El problema es que no siempre sabes cuantas celdas en blanco puedan haber en el rango.
Pero siempre puedes utilizar la funcion contar.blanco
ResponderBorrarnecesito usar lookup como una especie de IF porque IF solo deja colocar 7 condiciones y lei en la ayuda de excel que se tenia que usar look up. el problema es que tengo que poner si un numero en una celda esta entre 0 y 15 en otra celda tiene que salir automaticamente 25, si esta entre 16 y 30 tiene que salir automaticamente 50, y asi hasta llegar a las 13 condiciones. como puedo hacer esto? muy agradecido
ResponderBorrarluis
Hola Luis,
ResponderBorrarpuedes encontrar soluciones en estas notas:
Simplificando la función SI (IF) combinando expresiones lógicas
Usar ELEGIR (CHOOSE) en lugar de SI (IF) en MS Excel
Utilizar BUSCARV (Vlookup) en lugar de combinaciones de SI
¿Por qué no: max(A:A)?
ResponderBorrarPorque el máximo valor en el ragno no es necesariamente el último en el rango. En el ejemplo de la nota ambos valores coinciden porque la lista está ordenada. Pero si mueves el 172 a otra posición en la lista, la fórmula dará 163 que ha pasado a ser el último valor en el ranog. La función MAX siembre dará 172.
ResponderBorrarSr. Dulkeman deseo que este año 2009 para ustede y toda su familia sea el mejor, que Dios lo nutra de muchos mas conocimiento y sobre todo lo siga bendiciendo con esa manera humilde y desinteresada con que los comparte.....que tenga una FELIZ NAVIDAD Y UN GRAN AÑO 2009.
ResponderBorrarSu amigo....Alfonso Córdoba - Panamá
=INDIRECTO("B" &CONTAR(B:B)+3) es facil
ResponderBorrarEl problema con esa solución, que ya había propuesto Jaizki en su comentario, es que CONTAR no toma en cuenta las celdas en blanco. Si hubiera alguna celda en blanco, el resultado sería incorrecto. Dado que estás usando todo el rango de la columna, no podemos usar CONTAR.BLANCOS para corregir el resultado.
ResponderBorrarBuenos días. Tengo una columna en la cual algunas celdas contienen la letra X, significa que hay celdas en blanco intercaladas. No he logrado encontrar la forma (usando funciones), de ubicar la última X de esa columna. Agradeceré mucho si me pueden ayudar.
ResponderBorrarHenry,
ResponderBorrar¿podrías ser un poco más explícito? Con ubicar, ¿te refieres a determinar la dirección de la celda? La celda, ¿contiene sólo la letra X, o un texto que contiene la letra X?
Jorge, gracias por su amable atención.
ResponderBorrarEl caso es: Dentro de la columna D de una hoja, algunas celdas contienen solamente una letra X (NO un texto conteniendo X), las demás celdas están vacías (intercaladas). Pretendo obtener la dirección/referencia de la última celda de esa columna que contenga X.
Buscando más a fondo en su blog encontré una solución "casi completa":
DIRECCION(SUMAPRODUCTO(MAX((FILA('Oct-10'!D5:D35)*('Oct-10'!D5:D35<>""))));1).
La formula citada me funciona parcialmente aunque dados mis escasos conocimientos no tengo ni idea de como lo hace.
Digo "parcialmente" dado que obtengo como respuesta $A$34 pero no $D$34 que es el dato exacto buscado.
Hola Henry,
ResponderBorrartodo lo que hay que hacer es reemplazar el 1 en
DIRECCION(SUMAPRODUCTO(MAX((FILA('Oct-10'!D5:D35)*('Oct-10'!D5:D35<>""))));4)
por 4 (1 = columna A; 4 = columna D)
Jorge, perfecto!!!, me funcionó tal como deseo!!!
ResponderBorrarMuchas gracias por dedicarme su tiempo ayudándome a encontrar la solución.
Cordial saludo.
Hola, solicito su ayuda nuevamente.
ResponderBorrarMe surgió un problema, le podría enviar mi libro a su correo?
Si, fijate en el enlace Ayuda (en la parte superior del blog)
ResponderBorrarcomo hacer para que el valor que me sea el primero por encima (en el ejemplo, que con el valor de 126 el resultado sea 127 y no 118?)
ResponderBorrarSi puedes ordenar la lista en orden descendente (de mayot a menor), puedes usar esta fórmula:
ResponderBorrar=INDICE(A4:A23;COINCIDIR(126;A4:A23;-1))
Si la lista no esta ordenado, la respuesta es más compleja e irá en una futura nota.
Me acabo de acordar que traté en tema en esta nota
ResponderBorrarBuen día,Tengo una columna, donde hay celdas con valores numéricos y las otras celdas marcadas con "-". Necesito encontrar la celda con el ultimo "-" dentro de un rango que yo especifique, lo he hecho con la función =BUSCAR("-";$G$8:G14;$I$8:I14), donde es el rango donde buscar y $I$8:I14 es el rango de celdas con los valores que quiero tener de vuelta, pero no me sirve del todo, aveces no da el ultimo "-".
ResponderBorrarBUSCAR siempre da el mayor valor del vector de resultado. Por eso, a veces coincide con el valor esperado y a veces no.
ResponderBorrarUna alternativa es usar esta UDF (función definida por el usuario):
Function find_last_value(Valor_Buscado As Variant, _
Vector_de_Comparacion As Range, _
Vector_de_Resultado As Range) As String
Dim lLastPosinRange As Long, iX As Long
lLastPosinRange = Vector_de_Comparacion.Count
For iX = lLastPosinRange To 1 Step -1
If Vector_de_Comparacion(iX) = Valor_Buscado Then
find_last_value = Vector_de_Resultado(iX)
Exit Function
End If
Next iX
End Function
Puedes copiar este código en un módulo del cuaderno Personal o en el cuaderno donde quieres usar la función. La función aparecerá en el asistente de funciones bajo la categoría "Definiodas por el usuario".
Buenas tarde mi consulta es la siguiente
ResponderBorrarNº FECHA
128830 02/12/2011
128831 02/12/2011
128832 02/12/2011
128833 03/12/2011
128834 03/12/2011
128835 03/12/2011
como puedo llegar a esto
128830 02/12/2011
128833 03/12/2011
Hola, ¿y si tengo texto en lugar de números?
ResponderBorrarMuchas gracias
=BUSCAR("zzzzz", rango de búsqueda)
ResponderBorrarque tal Jorge L. Dunkelman, veo que tus conocimientos en excel son amplios. mi nombre es ricardo muñoz, y tengo unas 2 dudas de nivel avanzado que no puedo resolver:
ResponderBorrar1. 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
En lo que hace a tu primer consulta, la fórmula a usar es
ResponderBorrar=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).
Hola Jorge.
ResponderBorrarTenía un problema similar al del anónimo que te consultó el 11 marzo de 2012. Probé con la UDF que propusiste y me sirvió. Con una salvedad. Devuelve los valores correctamente pero no puedo aplicarles formato. En mi caso, los valores son fechas y cifras que refieren cantidades. Por ejemplo, tengo una celda con el texto "53,86", quiero aplicarle el formato Moneda, o Contabilidad, pero no se deja.
¿Alguna sugerencia?
Un saludo.
Y gracias.
Hola,
ResponderBorrartodo lo que hay que hacer es quitar "as String" en la declaración de la función
Function find_last_value(Valor_Buscado As Variant, _
Vector_de_Comparacion As Range, _
Vector_de_Resultado As Range) As String
de manera que quede
Function find_last_value(Valor_Buscado As Variant, _
Vector_de_Comparacion As Range, _
Vector_de_Resultado As Range)
Hola Jorge,
ResponderBorrarEn tu penúltima comentario diste una fórmula con la que se obtenía el último valor númerico de un rango sin importar si habían celdas vacías o con texto.
La fórmula que indicaste era la siguiente:
=BUSCAR(2,1/ESNUMERO(C3:C30),C3:C30
He intentado analizar la fórmula que en principio parece simple pero aunque cumple su cometido perfectamente no le encuentro la lógica. Se supone que con ella buscamos el valor 2 en un rango comprendido entre las celdas C3 y C30. Ignoro porqué como valor buscado se indica el número 2 y como vector de comparación 1/ESNUMERO(C3:C30). El vector de resultado parece claro que deber ser el mismo pero sobre los dos argumentos anteriores sigo sin encontrarles la lógica. Sería mucha molestia que explicases cómo funciona dicha fórmula?
Saludos y gracias por adelantado.
Álex.
Hola Alex,
ResponderBorraren la parte fina de esta nota hay una explicación.
BUSCAR, a pesar de lo que dice la ayuda en línea de Excel, da como resultado el última valor del vector cuando no encuentra coincidencia. Ahora, el argumento ESNUMERO(C3:C30) da una serie de valores VERDADERO o FALSO; en la expresión 1/ESNUMERO(C3:C30) VERDADERO toma el valor 1 y FALSO el valor 0. Resulta así un vector de 1 y #DIV0. BUSCAR encuentra el último valor menor o igual al buscado, es decir el último 1. Podrías usar también 3 o cualquier número igual o mayor a 1 como primer argumento de la función.
También podés usar el auditor de fórmulas para ver como funciona paso a paso.
Hola jorge buena tarde, tengo un problema similar al que te estan enviando el detalle es que yo tengo una tabla con 97 columnas en ellas vienen precios y algunas estan en cero porque no hubo entrada, lo que necesito es que me tome la formula el ultimo precio de entrada, pudiendo estar en la columna C, o en la columna X y que no tome los ceros si no que me de el ultimo precio de entrada y no me tome en cuenta los ceros, intente la formula que usas, pero no me da yo la tendria que aplicar asi :
ResponderBorrar=BUSCAR(97,1/ESNUMERO(C5:T5),C5:T5)
o bien tengo que anotar cada uno de los numeros es decir :
=BUSCAR(97,96,95,94...,1/ESNUMERO(C5:T5),C5:T5)
me ayudarias por favor?
Gracias de antemano
Miriam
ResponderBorrarfijate en la técnica que muestro en esta nota.
Ahora, ¿cómo le hago para borrar el último valor de la columna encontrado con la función buscar?
ResponderBorrarNo se puede borrar el contenido de una celda con fórmulas. Tienes que seleccionar la celda y borrar el contenido.
ResponderBorrarHola Jorge,
ResponderBorrarHe leído aquí que le explicabas a un usuario que sí lo que quiere es buscar texto, debía poner "zzzzz". Estoy haciendo pruebas para ver si lo consigo y poniendo nombres al azar sólo me da el nombre que más próximo está a la Z. Por ejemplo: Juan, Manolo, Pepe, Lucas, aunque Lucas sea el último, me da Pepe.
Cómo podría solucionarlo? Gracias
Hola Rubén, =BUSCAR("zzzz",A1:A4), donde el rango A1:A4 contiene los nombres de tu ejemplo, da Lucas. Por lo visto hay algo errponeo en los argumentos de tu fórmula.
ResponderBorrarHola Jorge: quería consultarte sobre un tema que no logro resolver adecuadamente: Tengo este caso
ResponderBorrarA B C
Cod: Fecha Acción
6004665 10/03/2014 E-mail
6004665 11/03/2014 LLamado
6004665 12/03/2014 Visita
18353 10/03/2014 E-mail
18353 13/03/2014 Llamado
Necesito poder registrar la ultima acción sobre cada código (que se repite varias veces) de cliente que quede de la manera que estoy mostrando. Desde ya agradezco cualquier colaboración al respecto.
Slds
A B C
Cod: Última Fecha Última Acción
6004665 12/03/2014 Visita
18353 13/03/2014 Llamado
Hay varias posibilidades, dependiendo de cómo estén organizados los datos y de la cantidad de registros (filas). Una posibilidad es crear una columna auxiliar para encontrar la fila que cumple con las condición de ser la última fecha de cada cliente y filtrar la lista de acuerdo a esta columna.
ResponderBorrarEn tu ejemplo agregamos una columna entre Fecha y Acción (será la columna C) que llamamos Aux. En esta columna C ponemos esta fórmula matricial
=MAX(($A$2:$A$6=A2)*$B$2:$B$6)=B2
apretando Ctrl-Mayúsculas-Enter simultáneamente. Esto da como resultaddo FALSO o VERDADERO (si se cumplen ambas condiciones). Luego al filtrar la lista por Aux, serás las últimas acciones de cada cliente.
Te sugiero que veas lo que pongo en el enlace Ayuda, en la parte superior de la plantilla.
Hola, tengo este problema, que no encuentro la solución
ResponderBorrarTengo esta base de datos
https://drive.google.com/file/d/0B102ymFDxRHpbGNCWm5RQi0yQ2M/view?usp=sharing
Es una lista de asistencia, en la cual pongo cada semana quien asiste con un *
Necesito que en la columna C me indique cada persona cuando fue la ultima vez que asistió, use al formula de buscar pero no me sirve por que no están ordenados de forma ascendente, pero no tengo manera de ordenar mi tabla de esa forma
Alguien se le ocurre alguna otra manera de realizar esta función ?
De antemano, muchas gracias !
Salomon,
ResponderBorrares una mala idea usar * para señalar valores ya que el símbolo * es un wildcard que quiere decir "todo valor".
Te sugiero que leas las instrucciones en el enlace Ayuda (en la parte superior de blog) y te pongas en contacto conmigo por mail privado.
Hola! Necesito ayuda. Estoy buscando la forma de hacer que me busque el primer valor de una fila y me devuelva el encabezado de esa columna. Es decir, tengo 12 meses (en fila) y nombres de clientes (en columna). Si en un mes no ha comprado esa celda no tiene valor, pero si ha comprado aparece el valor. Lo que necesito saber en qué mes hizo la primera compra. Sólo he conseguido la fecha de última compra con BUSCAR.
ResponderBorrarMuchas gracias!
Fijate en esta nota.
ResponderBorrarJorge, tengo una consulta, tengo una matriz en la que necesito buscar el resultado mas reciente de fechas, con valores repetidos.
ResponderBorrarXXXX8892910 P3 04/12/2014
ZCSU2366473 P2 04/08/2015
XXXX8892910 P3 01/11/2015
ZCSU2366473 P2 01/12/2015
XXXX8892910 P3 28/11/2015
en la primera columna se ven valores repetidos, necesito que el buscarv me de la fecha mas reciente.
Te dejo mi Mail
Gibzakush@gmail.com
Gonzalo Martinez Fuentes
Muchas gracias
Lo más sencillo sería ordenar la lista por fechas. De esa manera BUSCARV daría como resultado la fecha el valor buscado con la fecha más reciente.
ResponderBorrarHola, Jorge, tengo una de facturacion con este formato:
ResponderBorrar1/12/16 15,25
1/12/16 10,35 25,60
2/12/16 7,25
2/12/16 5,10
2/12/16 6,30 18,65
Como ves, en la A va la fecha de factura, en la B el importe y en la C totaliza las ventas de cada día.
Lo que quiero ahora es en otra hoja hacer un listado del total de ventas diaria, es decir, en A cada dia del año, y en B que aparezca el total de ese dia, como podría hacerlo?
Gracias campeon
Nada, ya esta, gracias de todas formas, lo he solucionado con
ResponderBorrar=SUMAR.SI(Facturas!$B$2:$B$10000;TOTALES!A2;Facturas!$F$2:$F$10000)