excel - vacia - Última celda no vacía en una columna

macro para pegar datos en celdas vacias (20)

¿Alguien sabe la fórmula para encontrar el valor de la última celda no vacía en una columna, en Microsoft Excel?

Aquí hay otra opción: =OFFSET($A$1;COUNTA(A:A)-1;0)

Coloque este código en un módulo de VBA. Salvar. En funciones, definido por el usuario, busque esta función.

Function LastNonBlankCell(Range As Excel.Range) As Variant Application.Volatile LastNonBlankCell = Range.End(xlDown).Value End Function

De acuerdo, entonces tuve el mismo problema que el asker e intenté ambas respuestas principales. Pero solo obteniendo errores de fórmula. Resultó que necesitaba cambiar el "," a ";" para que las fórmulas funcionen Estoy usando XL 2007.

Ejemplo:

=LOOKUP(2;1/(A:A<>"");A:A)

=INDEX(A:A;MAX((A:A<>"")*(ROW(A:A))))

Esta fórmula trabajó conmigo para la oficina 2010:

= BUSCAR (2; 1 / (A1: A100 <> ""; A1: A100)

A1: la primera celda A100: consulte la última celda al comparar

Esto funciona en Excel 2003 (y más tarde con edición menor, ver a continuación). Presione Ctrl + Shift + Enter (no solo Enter) para ingresar esto como una fórmula de matriz.

=IF(ISBLANK(A65536),INDEX(A1:A65535,MAX((A1:A65535<>"")*(ROW(A1:A65535)))),A65536)

Tenga en cuenta que Excel 2003 unable puede aplicar una fórmula de matriz a una columna completa. Hacerlo produce #NUM! ; resultados impredecibles pueden ocurrir! (EDITAR : Información conflictiva de Microsoft: lo mismo may o no ser cierto sobre Excel 2007; el problema puede haberse solucionado en 2010. )

Es por eso que aplico la fórmula de matriz al rango A1:A65535 y doy un trato especial a la última celda, que es A65536 en Excel 2003. No puedo simplemente decir A:A o incluso A1:A65536 ya que este último automáticamente revierte a A:A .

Si está absolutamente seguro de que A65536 está en blanco, puede omitir la parte IF :

=INDEX(A1:A65535,MAX((A1:A65535<>"")*(ROW(A1:A65535))))

Tenga en cuenta que si está utilizando Excel 2007 o 2010, el último número de fila es 1048576, no 65536, por lo que debe ajustar lo anterior según corresponda.

Si no hay celdas en blanco en el medio de sus datos, entonces simplemente usaría la fórmula más simple, =INDEX(A:A,COUNTA(A:A)) .

Esto funciona tanto con texto como con números y no le importa si hay celdas en blanco, es decir, devolverá la última celda no en blanco.

Necesita ser ingresada en un array , lo que significa que usted presiona Ctrl-Shft-Enter después de que lo tipea o lo pega. Lo siguiente es para la columna A:

=INDEX(A:A,MAX((A:A<>"")*(ROW(A:A))))

He probado todas las versiones no volátiles pero ninguna de las versiones anteriores ha funcionado ... excel 2003 / 2007update. Seguramente esto se puede hacer en Excel 2003. No como una matriz ni fórmula estándar. O bien obtengo un error en blanco, 0 o #valor. Así que recurro a los métodos volátiles ... Esto funcionó ...

= BUSCAR (2,1 / (T4: T369 <> ""), T4: T369)

@Julian Kroné .. Usando ";" en lugar de "," NO funciona! Creo que estás usando Libre Office no MS Excel? LOOKUP es tan volátil que lo uso como último recurso solamente

Inspirado por la gran ventaja dada por la respuesta de Doug Glancy, se me ocurrió una forma de hacer lo mismo sin la necesidad de una matriz de fórmula. No me preguntes por qué, pero estoy dispuesto a evitar el uso de fórmulas de matriz si es posible (no por ninguna razón en particular, es solo mi estilo).

Aquí está:

=SUMPRODUCT(MAX(($A:$A<>"")*(ROW(A:A))))

Para encontrar la última fila no vacía usando la Columna A como columna de referencia

=SUMPRODUCT(MAX(($1:$1<>"")*(COLUMN(1:1))))

Para encontrar la última columna no vacía usando la fila 1 como la fila de referencia

Esto se puede utilizar además junto con la función de índice para definir de manera eficiente los rangos dinámicos con nombre, pero esto es algo para otra publicación, ya que no está relacionada con la pregunta inmediata aquí tratada.

He probado los métodos anteriores con Excel 2010, tanto "de forma nativa" como en "Modo de compatibilidad" (para versiones anteriores de Excel) y funcionan. De nuevo, con estos no es necesario hacer ninguna de las teclas Ctrl + Shift + Enter. Aprovechando la manera en que funciona el sumroducto en Excel, podemos hacer frente a la necesidad de llevar a cabo operaciones de matriz, pero lo hacemos sin una fórmula de matriz. Espero que alguien por ahí pueda apreciar la belleza, simplicidad y elegancia de estas soluciones de subproducto propuestas tanto como yo. Sin embargo, no doy fe de la eficacia de la memoria de las soluciones anteriores. Solo que son simples, se ven hermosos, ayudan a los fines previstos y son lo suficientemente flexibles como para extender su uso a otros propósitos :)

¡Espero que esto ayude!

¡Todo lo mejor!

Para Microsoft office 2013

"Last but one" de una fila no vacía:

=OFFSET(Sheet5!$C$1,COUNTA(Sheet5!$C:$C)-2,0)

"Última" fila no vacía:

=OFFSET(Sheet5!$C$1,COUNTA(Sheet5!$C:$C)-1,0)

Para el seguimiento de la versión (agregando la letra v al principio del número), encontré que este funcionaba bien en Xcelsius (SAP Dashboards)

="v"&MAX(A2:A500)

Sé que esta pregunta es antigua, pero no estoy satisfecho con las respuestas proporcionadas.

LOOKUP, VLOOKUP y HLOOKUP tienen problemas de rendimiento y nunca deberían usarse.
Las funciones de matriz tienen muchos gastos generales y también pueden tener problemas de rendimiento, por lo que solo deben utilizarse como último recurso.
COUNT y COUNTA tienen problemas si los datos no están contiguos en blanco, es decir, tiene espacios en blanco y luego datos nuevamente en el rango en cuestión
INDIRECT es volátil, por lo que solo debe utilizarse como último recurso
OFFSET es volátil, por lo que solo debe utilizarse como último recurso
cualquier referencia a la última fila o columna posible (la 65536a fila en Excel 2003, por ejemplo) no es robusta y genera una sobrecarga adicional

Esto es lo que uso

cuando el tipo de datos es mixto: =max(MATCH(1E+306,[RANGE],1),MATCH("*",[RANGE],-1))
cuando se sabe que los datos solo contienen números: =MATCH(1E+306,[RANGE],1)
cuando se sabe que los datos solo contienen texto: =MATCH("*",[RANGE],-1)

MATCH tiene la sobrecarga más baja y no es volátil, por lo que si trabajas con muchos datos, esta es la mejor opción.

Si sabes que no van a haber celdas vacías en el medio, la forma más rápida es esta.

=INDIRECT("O"&(COUNT(O:O,"<>""")))

Simplemente cuenta las celdas no vacías y se refiere a la celda apropiada.

También se puede usar para un rango específico.

=INDIRECT("O"&(COUNT(O4:O34,"<>""")+3))

Esto devuelve la última celda no vacía en el rango O4: O34.

Tuve el mismo problema también. Esta fórmula también funciona igual de bien:

=INDIRECT(CONCATENATE("$G$",(14+(COUNTA($G$14:$G$65535)-1))))

14 es el número de fila de la primera fila en las filas que desea contar.

Clawtooth crónico

Una simple que funciona para mí:

=F7-INDEX(A:A,COUNT(A:A))

Una solución alternativa sin fórmulas de matriz, posiblemente más sólida que la de una respuesta previa con una solución (sugerencia a) sin fórmulas de matriz , es

=INDEX(A:A,INDEX(MAX(($A:$A<>"")*(ROW(A:A))),0))

Vea esta respuesta como un ejemplo. Felicitaciones a Brad y Barry Houdini , que ayudaron a resolver esta pregunta .

Las posibles razones para preferir una fórmula que no sea de matriz se dan en:

Una página oficial de Microsoft (busque "Desventajas del uso de fórmulas de matriz").
Las fórmulas de matriz pueden parecer mágicas, pero también tienen algunas desventajas:
- Ocasionalmente, puede olvidarse de presionar CTRL + MAYÚS + ENTRAR. Recuerde presionar esta combinación de teclas cada vez que ingrese o edite una fórmula de matriz.
- Es posible que otros usuarios no entiendan tus fórmulas. Las fórmulas de matriz están relativamente indocumentadas, por lo que si otras personas necesitan modificar sus libros de trabajo, debe evitar las fórmulas de matriz o asegurarse de que esos usuarios entiendan cómo modificarlas.
- Dependiendo de la velocidad de procesamiento y la memoria de su computadora, las fórmulas de matriz grande pueden ralentizar los cálculos.
Array Formula Heresy .

Usé HLOOKUP

A1 tiene una fecha; A2:A8 tiene previsiones capturadas en diferentes momentos, quiero la última

=Hlookup(a1,a1:a8,count(a2:a8)+1)

Utiliza una fórmula estándar de hlookup con la matriz de búsqueda definida por el número de entradas.

Usar la siguiente fórmula simple es mucho más rápido

=LOOKUP(2,1/(A:A<>""),A:A)

Para Excel 2003:

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)

Te da las siguientes ventajas:

no es una fórmula de matriz
no es fórmula volátil

Explicación:

(A:A<>"") devuelve la matriz {TRUE,TRUE,..,FALSE,..}
1/(A:A<>"") modifica esta matriz a {1,1,..,#DIV/0!,..} .
Dado que LOOKUP espera una matriz ordenada en orden ascendente , y teniendo en cuenta que si la función LOOKUP no puede encontrar una coincidencia exacta, elige el valor más grande en el lookup_range (en nuestro caso {1,1,..,#DIV/0!,..} ) que es menor o igual que el valor (en nuestro caso 2 ), la fórmula encuentra el último 1 en la matriz y devuelve el valor correspondiente de result_range (tercer parámetro - A:A ).

También pequeña nota : la fórmula anterior no tiene en cuenta las celdas con errores (puede verla solo si la última celda no vacía tiene error). Si desea tomarlos en cuenta, use:

=LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A)

la imagen a continuación muestra la diferencia:

para datos textuales:

EQUIV("";A1:A10;-1)

para datos numéricos:

EQUIV(0;A1:A10;-1)

Esto le da el índice relativo de la última celda no vacía en el rango seleccionado (aquí A1: A10).

Si quiere obtener el valor, acceda a él a través de INDIRECT después de construir -textualmente- la referencia de celda absoluta, por ejemplo:

INDIRECT("A" & (nb_line_where_your_data_start + EQUIV(...) - 1))

=INDEX(A:A, COUNTA(A:A), 1) tomado de here

=MATCH("*";A1:A10;-1) para datos textuales

=MATCH(0;A1:A10;-1) para datos numéricos