jueves, 17 de septiembre de 2015

Encontrar la última fila o columna con datos...

Aprenderemos hoy un truco bastante útil que nos servirá para localizar cuál es la última celda de un rango con un valor...
y lo haremos con un sentido o final concreto, ser capaces de adaptar el área de impresión de manera particular a un rango definido.
La explicación viene al hilo de una cuestión planteada en un foro en el que participo:
Necesito una macro que seleccione un área de impresión que comenzando siempre en A1, en algunas ocasiones tenga que seleccionar, por ejemplo, desde A1 hasta I25. ¿Por qué este rango?. Pues porque hay datos hasta la celda A25.
Resumiendo que la macro recorra la columna A y cuando encuentre la última celda con datos, cree un rango de impresión desde A1 hasta la columna I que corresponda.


Lo interesante es que conseguiremos esto sin emplear programación en VBA para Excel.. simplemente utilizando la función COINCIDIR e INDIRECTO, así como los nombres definidos.

Partiremos de estos valores de nuestra hoja, viéndolos en Vista Salto de página:

Encontrar la última fila o columna con datos...


Como era de esperar el Área de impresión se ajusta a los datos de la hoja... (en este caso, incluyendo columnas que no deseamos sean impresas...).

El objetivo es configurar el alto del área de impresión a la última fila con datos de la columna A:A.
Esto lo conseguiremos con la función:
=COINCIDIR(9,9E+307;A:A;1)
lo que nos devolverá siempre el número de la fila última con algún valor numérico!!!.

El truco de emplear esta función COINCIDIR con ese valor 9,9E+307 es por que en Excel, según las especificaciones técnicas, este es el valor más alto posible... por tanto al indicarle el tercer argumento de COINCIDIR con un 1 (menor que), nos devolverá, la última fila con valor númérico.
Otra forma podría ser:
=COINCIDIR(MAX(A:A)+1;A:A;1)
que esencialmente, nos dice lo mismo.
Nos aprovechamos de un 'fallo' en nuestra disposición de datos, ya que el tipo de coincidencia 1 u omitido de la función COINCIDIR encuentra el mayor valor que es menor o igual que el valor_buscado.
Los valores del argumento matriz_buscada deberían estar ordenados en sentido ascendente, en caso contrario 'fallaría' devolviendo el último encontrado...


Podemos trabajar igualmente por columnas, para lo cual bastaría cambiar en nuestra fórmula las referencias de columnas por la de filas.
Por ejemplo,
=COINCIDIR(9,9E+307;1:1;1)
nos dirá cuál es la última columna con valor numérico completado en la fila 1:1.


Una vez tenemos la fila con último dato completado, ya podemos configurar nuestra área de impresión o nuestro rango.
Estableceremos un área de impresión cualquiera, y a continuación accederemos al Administrador de Nombres definidos, donde cambiaremos el campo Se refiere a... por la siguiente fórmula:
=INDIRECTO("Hoja1!$A$1:$I"&COINCIDIR(9,9E+307;Hoja1!$A:$A;1))

Encontrar la última fila o columna con datos...


Y listo, nuestro área de impresión se ajustará al ancho definido (A:I) y al alto dado por la última celda de la columna A:A con numérico...

16 comentarios:

  1. Si quieres saber cual es la primera celda vacia:
    =COINCIDIR(VERDADERO;INDICE(Hoja1!$A:$A="";0);0)
    Un saludo

    ResponderEliminar
  2. Cómo hacer para que con formato condicional, de color a una fila conforme introduzco datos, un color para impares y uno diferente para filas pares, conforme se vayan llenando de datos se coloreen del color especificado, espero haber explicado de una manera entendible, gracias!

    ResponderEliminar
    Respuestas
    1. Hola Jose Luis,
      por que no conviertes tu rango en tabla, por defecto tiene configurado la opción de Filas alternas, que hacen exactamente lo que necesitas...

      Saludos

      Eliminar
    2. Introduce esta fórmula en el formato condicional
      =ES.PAR(FILA())
      Y le dices que cuando se cumpla ese criterio cambie el color

      Eliminar
    3. Gracias por el apunte Daniel,
      con formatos condicionales es otra forma, sin duda,pero en realidad menos eficiente...
      La mejor opción es claramente trabajar como si fuera Tabla
      Un cordial saludo!

      Eliminar
  3. Como hago exactamente esto pero con texto?

    ResponderEliminar
    Respuestas
    1. Hola, qué tal estás?
      un placer saludarte igualmente.
      En breve escribiré un post explicando algunas formas..

      Un cordial saludo

      Eliminar
  4. Hola buenas, necesito la fórmula para guardar-actualizar en otra celda cualquiera el último importe mayor de cero de la columna S, teniendo en cuenta que esta columna sigue aumentado, por eso digo lo de actualizar, a medida que añado mas importes en la columna S que esa otra celda se actualice con el último dato mayor de cero. Muchas gracias por tu disposición.

    ResponderEliminar
    Respuestas
    1. Hola,
      prueba con la matricial siguiente:
      =BUSCAR(9,99999999999999E+307; SI($S:$S>0; $S:$S))

      Saludos

      Eliminar
    2. Muchas gracias por tu rápida respuesta, no me ha funcionado esa fórmula pero finalmente, entre ideas que he sacado de aquí y San Google, lo he conseguido con esta =BUSCAR(2;1/(S1:S10000<>0);S1:S10000)
      Un saludo

      Eliminar
  5. hola tengo un problema empleo sumar.si() para ir descontando los litros consumidos de un barril, el problema es que no se como "resetar" la formula cuando lleno de nuevo el barril y que comience a descontar nuevamente los litros usados.
    Desde ya muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      SUMAR.SI acumula cantidades... si quieres 'resetear' tendrías que añadir algún tipo de condicional =SI(tal condición;...;...)
      Será importante saber en qué momento se debe dar ese reinicio y cómo seguir acumulando de nuevo..
      Slds

      Eliminar
  6. Hola! estoy buscando la forma de mostrar el último valor asignado a una celda. Me explico mejor: Tengo en una hoja una lista de productos a los cuales se les hace inventario diario, cada día es una columna y necesito reflejar en otro archivo el resultado del último inventario realizado, que vendría siendo la última columna con registros. Agradezco cualquier ayuda sobre como realizar esto.

    ResponderEliminar
    Respuestas
    1. Hola,
      lo expuesto en este post te servirá sin usar macros...
      pero también puedes con algo de programación
      https://excelforo.blogspot.com/2017/03/vba-Localizar-Ultima-Fila-Excel.html

      Espero te de una idea
      Slds

      Eliminar

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