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).
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.
| ... 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.
Muy muy bueno !!! Gracias !
ResponderEliminary como podría buscar el 2º, 3º... valor?
ResponderEliminarHola, qué tal estás?
Eliminarun placer saludarte igualmente.
¿cuál es la finalidad de encontrar esas posiciones?...
necesitas tenerlas seleccionadas, localizadas....
Slds
Nesecito tenerla localizada, habria alguna forma de obtner la segunda? la tercera?
ResponderEliminarHola, qué tal estás?
Eliminarun 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
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?
Eliminarwow necesito lo mismo. obtener varios resultados diferentes a cero
EliminarHola Jorge,
Eliminarunos comentarios más abajo se explica una solución muy parecida a la tuya
Slds
Hola,
ResponderEliminarEstoy 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
Hola,
Eliminarte has asegurado de validar la fórmula presionando Ctrl+Mayusc+Enter (en lugar de solo Enter)??
Saludos
Hola
ResponderEliminarAl 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.
Hola,
Eliminarquizá 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
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.
EliminarSaludos
ok!
EliminarSaludos
Este comentario ha sido eliminado por el autor.
ResponderEliminarBuenas 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?
ResponderEliminarHola Miquel,
Eliminarpara 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
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)
ResponderEliminarEl 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
Hola,
Eliminarno 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
GRacias... es cierto que me está costando muchísimo y, sí estaba llegando a la misma conclusión intentarlo por porwerQuery , o power BI
EliminarHola Ismael, la macro que muestro a continuacion si funciona, pero me gustaria saber si se puede resumir el numero de lineas
ResponderEliminarSub 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!!!
Hola,
Eliminarquizá 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
Muchas gracias por la ayuda Ismael
ResponderEliminarSaludos!!
Suerte!
EliminarQue tal Ismael!!, quisiera consultarte una cosa a la que no encuentro la solucion y que es lo siguiente:
ResponderEliminarBasicamente 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!!!
Hola,
Eliminarescribiré un post al respecto...
Saludos y perdón por la demora en responder... le perdí la pista.
Que tal Ismael, saludandote y agradeciendote por la ayuda prestada.
ResponderEliminarEl 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!!!
Te daré posible solución lo antes posible
Eliminar;-)
Slds