Conocer la referencia de una celda mediante fórmula

26/08/2008 - 22:52 por Roberto | Informe spam

Hola Grupo:
Necesito saber si existe alguna forma de conocer la referencia de una
celda mediante una fórmula.
En estos momentos poseo una columna que contiene celdas vacias y
celdas llenas, lo que estoy haciendo es que paso a través de una
fórmula los datos de esa columna a una nueva columna (Columna de
destino) en otra hoja en la cual me los ordena y elimina las celdas
vacias intermedias. En la columna en la cual tengo celdas vacias
(llamemosla columna original), utilicé un rango dinámico, pero este
rango se detiene cuando consigue algunas celdas vaciás y no selecciona
hasta la última celda ocupada porque en el medio pueden existir muchas
celdas vacias.
Un ejemplo puede ayudar:

A B C
1 15 32 12
2 58 45 11
3 36 98
4 78 36 15
5 45 78
6 15 25
7 98 12
8 25 9 13
9 36 1
10 65 65
11 78 3 14
12 98 78

Con el rango dinámico aplicado a la columna C me selecciona unicamente
hasta la celda C4 (valor 15) excluyendo los valores 13 y 14.
El resultado que pretendo obtener en la columna de destino (B) es el
siguiente:

A B C
1 11
2 12
3 13
4 14
5 15
6
7
8
9
10
11
12

Asi las cosas si pudiera saber la referencia de la última celda
ocupada entonces, podría utilizarlo para definir el rango y utilizarlo
en las otras formulas que me ordenan y suprimen las celdas vacias (lo
cual ya lo tengo resuelto)
Gracias
Roberto

Siga el debate

6 respuestas

Tengo una respuesta

Preguntas similare

Referencia mediante formula a celda de libro externo

Leer las respuestas

#6 Roberto

29/08/2008 - 21:28 | Informe spam

On 28 ago, 13:50, "Héctor Miguel"
wrote:

hola, Roberto !

>> si buscas (o existen) solo numeros en la columna U... prueba con:
>> =desref(hoja1!$u$7;;;coincidir(9e307;hoja1!$u:$u);)

> La formula funciona de maravilla, pero podrías explicarme que significa o como se utiliza 9e307, es primera vez que lo veo

lo que sigue, es una completa explicacion ofrecida por KL a consulta similar +/- en mayo de 2006

saludos,
hector.

== consulta original =>
>...saber porque se utiliza 9e+307
> ya que busque la ayuda sobre la funcion indice y sobre la funcion coincidir pero no informa nada sobre esto.

== la respuesta de KL => Esta solucion se basa en un comportamiento no documentado de las funciones de busqueda aproximada
(COINCIDIR con el 3r parametro 1, -1,VERDADERO o vacio; BUSCARV y BUSCARH con el 4o parametro 1,VERDADERO u vacio; BUSCAR).
Al no encontrar el valor numerico buscado dichas funciones devueleven el ultimo valor numerico en el rango de busqueda.
Lo mismo ocurre con las cadenas de texto - si el texto buscado no se encuentra la funcion devolvera el ultimo texto del rango de busqueda.
Aprovechando este comportamiento, se utilizan los valores que por definicion son imposibles de encontrar en el rango buscado, p.ej.:

Para encontrar el ultimo valor numerico usamos 9E307 que es el numero que se aproxima al numero maximo posible en Excel: 9,99999999999999E+307:

=BUSCAR(9E307;A1:A10)
=BUSCARV(9E307;A1:A10;1)
=BUSCARV(9E307;A1:A10;1;1)
=BUSCARV(9E307;A1:A10;1;VERDADERO)
=BUSCARH(9E307;A1:Z1;1)
=BUSCARH(9E307;A1:Z1;1;1)
=BUSCARH(9E307;A1:Z1;1;VERDADERO)
=INDICE(A1:A10;COINCIDIR(9E307;A1:A10))
=INDICE(A1:A10;COINCIDIR(9E307;A1:A10;1))
=INDICE(A1:A10;COINCIDIR(9E307;A1:A10;-1))
=INDICE(A1:A10;COINCIDIR(9E307;A1:A10;VERDADERO))

Para encontrar el ultimo valor de texto usamos algo como REPETIR("z";255)
o simplemente "zzzzzz" que es un valor que se supone que no encontraremos un valor alfabeticamente mayor en nuestro rango de busqueda:

=BUSCAR(REPETIR("z";255) ;A1:A10)
=BUSCARV(REPETIR("z";255) ;A1:A10;1)
=BUSCARV(REPETIR("z";255) ;A1:A10;1;1)
=BUSCARV(REPETIR("z";255) ;A1:A10;1;VERDADERO)
=BUSCARH(REPETIR("z";255) ;A1:Z1;1)
=BUSCARH(REPETIR("z";255) ;A1:Z1;1;1)
=BUSCARH(REPETIR("z";255) ;A1:Z1;1;VERDADERO)
=INDICE(A1:A10;COINCIDIR(REPETIR("z";255) ;A1:A10))
=INDICE(A1:A10;COINCIDIR(REPETIR("z";255) ;A1:A10;1))
=INDICE(A1:A10;COINCIDIR(REPETIR("z";255) ;A1:A10;-1))
=INDICE(A1:A10;COINCIDIR(REPETIR("z";255) ;A1:A10;VERDADERO))

Nota: Obviamente, el metodo de la busqueda del ultimo valor de texto anterior solo funciona
en las versiones de Excel que utilizan el alfabeto cuya ultima letra es "z" (por ejemplo en las versiones cirilicas la busqueda de la "z" fallaria).

Saludos,
KL

GRACIAS DE NUEVO HECTOR.COMO SIEMPRE ABUNDAN DE CONOCIMIENTO
TUS RESPUESTAS

Siga el debate Respuesta

Responder a este mensaje

Ads by Google

Hacer una pregunta

Tengo una respuesta

1 2

Busqueda sugerida