martes, 6 de mayo de 2014

Encontrar el primer valor NO vacío de un rango.

Algunos días atrás me plantearon la forma de obtener, sobre un rango de celdas, el valor correspondiente a la primera celda no vacía (esto es, con datos).

... necesito encontrar la primera celda que contenga datos (que no esté vacía) en esa misma columna (llamémosla celdaX)...



Mostraré algunas fórmulas, similares en el concepto, que nos llevan a conseguir nuestra meta.


Vemos nuestras fórmulas matrciales:
=INDICE(B2:B11;COINCIDIR(VERDADERO;INDICE((B2:B11<>0);0);0))
o la simplificada
=INDICE(B2:B11;COINCIDIR(VERDADERO;(B2:B11<>0);0);0)
y
=INDICE(B2:B11;COINCIDIR(FALSO;ESBLANCO(B2:B11); 0))
o su complementaria
=INDICE(B2:B11;COINCIDIR(VERDADERO;NO(ESBLANCO(B2:B11)); 0))


En todas ellas lo interesante es cómo con la función COINCIDIR y su primer argumento con un valor lógico VERDADERO o FALSO encontramos dentro del rango virtual conseguido la coincidencia. Ya que, no olvidemos, que esta función de búsqueda COINCIDIR devuelve la primera coincidencia que encuentre...

Por ejemplo, en
=INDICE(B2:B11;COINCIDIR(VERDADERO;(B2:B11<>0);0);0)
en el rango 'virtual' B2:B11<>0 que devuelve una matriz de VERDADEROS o FALSOS según la celda tenga valor o esté vacía (respectivamente), con COINCIDIR y su primer argumento VERDADERO encontramos el primer VERDADERO, es decir, el primer valor NO vacío del rango B2:B11 que buscábamos.

O también en
=INDICE(B2:B11;COINCIDIR(FALSO;ESBLANCO(B2:B11); 0)
de manera análoga obtenemos la misma matriz de VERDADEROS y FALSOS con el rango 'virtual' ESBLANCO(B2:B11)...


Posteriormente al aplicar la función INDICE sobre el rango B2:B11 conseguimos, no la posición, sino el valor de la celda correspondiente.

Por supuesto este razonamiento nos servirá para conseguir la primera celda vacía del rango.
Tenemos nuevamente nuevas alternativas.. pero todas ellas siguen el mismo patrón ya explicado (todas matriciales):
=DIRECCION(COINCIDIR(VERDADERO;INDICE((B2:B11="");0);0)+1;2)
o
=DIRECCION(COINCIDIR(FALSO;NO(ESBLANCO(B2:B11)); 0)+1;2)
o incluso
=DIRECCION(COINCIDIR(VERDADERO;ESBLANCO(B2:B11); 0)+1;2)


En este caso he optado por mostrar con la función DIRECCION la referencia de la celda correspondiente.

28 comentarios:

  1. Muy muy bueno !!! Gracias !

    ResponderEliminar
  2. y como podría buscar el 2º, 3º... valor?

    ResponderEliminar
    Respuestas
    1. Hola, qué tal estás?
      un placer saludarte igualmente.
      ¿cuál es la finalidad de encontrar esas posiciones?...
      necesitas tenerlas seleccionadas, localizadas....

      Slds

      Eliminar
  3. Nesecito tenerla localizada, habria alguna forma de obtner la segunda? la tercera?

    ResponderEliminar
    Respuestas
    1. Hola, qué tal estás?
      un placer saludarte igualmente de nuevo.. (supongo eres la misma persona de antes).
      Lo más sencillo, pare tu caso, sería emplear algo de programación.
      Intentaré subir algún post dando una solución.

      Un cordial saludo

      Eliminar
    2. Hola.... una pregunta y si no son números sino texto que se activa por condicionales de lo contrario las celdas quedan vacías pero no siempre se activa la misma?

      Eliminar
    3. wow necesito lo mismo. obtener varios resultados diferentes a cero

      Eliminar
    4. Hola Jorge,
      unos comentarios más abajo se explica una solución muy parecida a la tuya
      Slds

      Eliminar
  4. Hola,

    Estoy tratando de usar tu ejemplo, pero me da un resultado diferente al esperado, usando tu formula solo cambie el rango =DIRECCION(COINCIDIR(VERDADERO,INDICE((I294:IN294=""),0),0)+1,2) y el resultado que me da es $B$2.

    Saludos

    ResponderEliminar
    Respuestas
    1. Hola,
      te has asegurado de validar la fórmula presionando Ctrl+Mayusc+Enter (en lugar de solo Enter)??

      Saludos

      Eliminar
  5. Hola
    Al igual que te comentó algún compañero, (no lo he encontrado en otros post), me gustaría que se recorriera una columna, y me dijera por ejemplo los 3 primeros valores distintos de "" o 0. Ya que los datos vienen de otra fórmula =SI(G4="";E4;""), y lo que busco es que de esa columna, me indique por ejemplo los 3 o 5 primeros números que aparezcan (descartando el 0). Pero estos pueden estar en G4, G25, G98 por ejemplo.
    Muchas gracias por tu Blog.

    ResponderEliminar
    Respuestas
    1. Hola,
      quizá lo más sencillo sería montar una macro con un bucle tipo
      for each celda in range("A1:A10")
      ...
      next celda
      y dentro del bucle aplicar las condiciones de distinto de vacio o cero
      for each celda in range("A1:A10")
      if celda.value<>0 or celda.value<>"" then
      'cargamos una matriz que guarde esos datos
      end if
      next celda

      hay algunos post en el blog de cómo cargar Arrays...

      Saludos

      Eliminar
    2. Muchas gracias por tú rápida respuesta. Me la guardo para más adelante, de momento, he utilizado otra que me da como resultado el primer valor, y de momento me está valiendo para lo que necesito.
      Saludos

      Eliminar
  6. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  7. Buenas Ismael, primero de todo gracias por el post me ha ayudado mucho. Yo igual que algunos compañeros necesitaría localizar las 3 primeras celdas con datos. He pensado en utilizar <>0 y <>b10 (ejemplo) hay alguna forma de poderle dar esa orden?

    ResponderEliminar
    Respuestas
    1. Hola Miquel,
      para localizar más de una posición habría que recuperar las coincidencias según ubicación en filas, empleando quizá la fuNción K.ESIMO.MAYOR sobre la matriz de coincidencias...
      Recomiendo en este caso el uso de macros (hay un ejemplo en un comentario anterior)
      Slds

      Eliminar
  8. Hola, gracias por el blog en general es de mucha ayuda. Estoy intentando aplicar está fórmula =INDICE(TABLA!$D$2:$V$183;COINCIDIR(A12;TABLA!$D$2:$D$183;0);2)
    El caso es que me devuelve el primer valor que encuentra aunque la celda esté vacía, y necesito que me devuelva el valor de la primera celda NO VACIA.
    Es decir tengo un conjunto de personas en una columna y sus formaciones en distintas columnas, y una misma persona se repite con distintas formaciones en distintas filas y columnas, y necesito tener a una sola persona con sus formaciones en una sola fila..
    gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      no creo que con funciones puedas sacarlo fácilmente...
      creo que algo de programación te ayudaría o incluso PowerQuery y su acción de UnPivot (ver aqui)
      Se trataría de conseguir un listado homogéneo de persona-formación... para finalmente posicionarlo por filas... quizá con columnas condicionadas...
      Espero te oriente (mi recomendación final seguiría siendo las macros).
      Saludos

      Eliminar
    2. GRacias... es cierto que me está costando muchísimo y, sí estaba llegando a la misma conclusión intentarlo por porwerQuery , o power BI

      Eliminar
  9. Hola Ismael, la macro que muestro a continuacion si funciona, pero me gustaria saber si se puede resumir el numero de lineas

    Sub Periodos()
    If Range("O2") = 1 Then
    Range("O13") = "ENERO"
    Range("P2") = "Del 1 al 31 de Enero de 2019"
    Else
    If Range("O2") = 2 Then
    Range("O13") = "FEBRERO"
    Range("P2") = "Del 1 al 28 de Febrero de 2019"
    Else
    If Range("O2") = 3 Then
    Range("O13") = "MARZO"
    Range("P2") = "Del 1 al 31 de Marzo de 2019"
    Else
    .
    .
    .
    If Range("O2") = 12 Then
    Range("O13") = "DICIEMBRE"
    Range("P2") = "Del 1 al 31 de Diciembre de 2019"
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End Sub

    Gracias de antemano!!!

    ResponderEliminar
    Respuestas
    1. Hola,
      quizá se pueda algo (muy poco) con select case...
      pero yo añadiría una fórmula BUSCARV en la celda O13 y en P" que devuelva los valores de MMMM y "del 1 al 31 de..." de una tabla auxiliar...
      Me parece más simple que una macro...

      También podrías crear una Array en tu programación con los valores a devolver y cruzarlo con el Range("O2")

      Espero te oriente

      Saludos

      Eliminar
  10. Muchas gracias por la ayuda Ismael
    Saludos!!

    ResponderEliminar
  11. Que tal Ismael!!, quisiera consultarte una cosa a la que no encuentro la solucion y que es lo siguiente:

    Basicamente se trata de sumar por esquema (1.1, 1.2, 1.3, 1.3.1, 1.3.2, etc) algo como lo siguiente
    A B
    1.1 4
    1.2 4
    1.3 =SUMA(B5:B8)
    1.3.1 4
    1.3.2 4
    1.3.3 4
    1.3.4 2
    1.3.5 5
    1.4 =SUMA(B10)
    1.4.1 =SUMA(B11:B15)
    1.4.1.1 5
    1.4.1.2 5
    1.4.1.3 5
    1.4.1.4 5
    1.4.1.5 2
    TOTAL =SUMA(B1+B2+B3+B9)

    Es un burdo ejemplo pero que simplifica un archivo que tengo que formular de unas 4000 lineas

    Saludos y muchas gracias de antemano por tu valiosa ayuda!!!

    ResponderEliminar
    Respuestas
    1. Hola,
      escribiré un post al respecto...
      Saludos y perdón por la demora en responder... le perdí la pista.

      Eliminar
  12. Que tal Ismael, saludandote y agradeciendote por la ayuda prestada.
    El problema arriba mencionado ya casi lo tengo resuelto. Ya solo me falta resolver lo siguiente:
    Utilizando la formula "SUMAR.SI", me encontré que esta no hace distinción entre 1.1 y 1.10, 2.2 y 2.20, etc (estos son los criterios para las sumas). Ambas las toma como 1.1, 2.2, etc, ocasionando con ello errores en los totales. Ya probe convirtiendolos a texto, pero no se resolvio. Lo unico que resta seria reemplazando por letras pero estamos hablando de archivos de miles de lineas
    Agradeciendo de antemano cualquier aporte a este tema. Gracias!!!

    ResponderEliminar

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