excel - una - Referencia automática para celdas, columnas y filas en las funciones de la hoja de trabajo

que es una celda en excel

Question

En una función de hoja de cálculo en Excel, ¿cómo hace una referencia automática a la celda, columna o fila en la que se encuentra?

Tenga en cuenta que esto es extremadamente útil para el formato condicional.

Answer 1

En una función UDF de la hoja de cálculo de VBA, utiliza Application.Caller para obtener el rango de celdas que contienen la fórmula que llamó a la UDF.

Answer 2

Estaba buscando una solución para esto y usé la solución indirecta que se encuentra en esta página inicialmente, pero me pareció bastante larga y torpe por lo que estaba tratando de hacer. Después de un poco de investigación, encontré una solución más elegante (para mi problema) usando la notación R1C1. Creo que no se pueden mezclar diferentes estilos de notación sin usar VBA.

Dependiendo de lo que estés tratando de hacer con la celda con referencia propia, algo como este ejemplo debería hacer que una celda haga referencia a sí misma donde la celda es F13:

Range("F13").FormulaR1C1 = "RC"

Y luego puede hacer referencia a las celdas en posiciones relativas a esa celda, por ejemplo, donde su celda es F13 y necesita hacer referencia a G12 desde ella.

Range("F13").FormulaR1C1 = "R[-1]C[1]"

Básicamente, le está diciendo a Excel que busque F13 y luego se mueva hacia abajo una fila y una columna hacia arriba.

Cómo encajaba esto en mi proyecto era aplicar un vlookup en un rango donde el valor de búsqueda era relativo a cada celda en el rango sin tener que especificar cada celda de búsqueda por separado:

Sub Code()
    Dim Range1 As Range
    Set Range1 = Range("B18:B23")
        Range1.Locked = False
        Range1.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],DATABYCODE,2,FALSE),"""")"
        Range1.Locked = True
End Sub

Mi valor de búsqueda es la celda a la izquierda de cada celda (columna -1) en mi rango DIM''d y DATABYCODE es el rango con nombre al que estoy mirando.

Espero que tenga un poco de sentido? Pensé que valía la pena lanzarse a la mezcla como otra forma de abordar el problema.

Answer 3

Hay una mejor manera que es más segura y no ralentizará su aplicación. Cómo se configura Excel, una celda puede tener un valor o una fórmula; La fórmula no puede referirse a su propia celda. De lo contrario, terminará con un bucle infinito, ya que el nuevo valor causaría otro cálculo ....

Use una columna de ayuda para calcular el valor en función de lo que haya puesto en la otra celda.

Por ejemplo:

La columna A es verdadera o falsa, la columna B contiene un valor monetario, la columna C contiene la siguiente fórmula:

=B1

Ahora, para calcular esa columna B se resaltará en amarillo en un formato condicional solo si la Columna A es Verdadero y la Columna B es mayor que Cero ...

=AND(A1=True,C1>0)

A continuación, puede optar por ocultar la columna C

Answer 4

Mi columna actual se calcula por:

Método 1:

= IZQUIERDA (DIRECCIÓN (FILA (), COLUMNA (), 4,1), LEN (DIRECCIÓN (FILA (), COLUMNA (), 4,1)) - LEN (FILA ()))

Método 2:

= IZQUIERDA (DIRECCIÓN (FILA (), COLUMNA (), 4,1), INT ((COLUMNA () - 1) / 26) +1)

Mi fila actual se calcula por:

= DERECHA (DIRECCIÓN (FILA (), COLUMNA (), 4,1), LEN (FILA ()))

por lo tanto, un enlace indirecto a la Hoja2! Mi columna pero una fila diferente, especificada en la Columna A en mi fila es:

Método 1:

= INDIRECTO ("¡Hoja2!" & IZQUIERDA (DIRECCIÓN (FILA (), COLUMNA (), 4,1), LEN (DIRECCIÓN (FILA (), COLUMNA (), 4,1)) - LEN (FILA ())) e INDIRECTO (DIRECCIÓN (FILA (), 1,4,1)))

Método 2:

= INDIRECTO ("¡Hoja2!" & IZQUIERDA (DIRECCIÓN (FILA (), COLUMNA (), 4,1), INT ((COLUMNA () - 1) / 26) +1) e INDIRECTO (DIRECCIÓN (FILA (), 1,4) , 1)))

Entonces, si A6 = 3 y mi fila es 6 y mi Col es C devuelve el contenido de "Sheet2! C3"

Entonces, si A7 = 1 y mi fila es 7 y mi Col es D devuelve el contenido de "Sheet2! D1"

Answer 5

No veo la necesidad de Indirecto, especialmente para el formato condicional.

La forma más sencilla de auto-referenciar una celda, fila o columna es referirse a ella normalmente, por ejemplo, "= A1" en la celda A1, y hacer que la referencia sea parcial o completamente relativa. Por ejemplo, en una fórmula de formato condicional para verificar si hay un valor en la primera columna de las filas de varias celdas, ingrese lo siguiente con A1 resaltado y copie según sea necesario. El formato condicional siempre se referirá a la columna A para la fila de cada celda:

= $A1 <> ""

Answer 6

Para que una célula se auto-haga referencia a sí misma:

INDIRECT(ADDRESS(ROW(), COLUMN()))

Para que una celda se auto-haga referencia a su columna:

INDIRECT(ADDRESS(1,COLUMN()) & ":" & ADDRESS(65536, COLUMN()))

Para que una celda se auto-haga referencia a su fila:

INDIRECT(ADDRESS(ROW(),1) & ":" & ADDRESS(ROW(),256))
or
INDIRECT("A" & ROW() & ":IV" & ROW())

Los números son para 2003 y anteriores, use la columna: XFD y la fila: 1048576 para 2007+.

Nota: La función INDIRECTA es volátil y solo debe usarse cuando sea necesario.

Answer 7

Para una solución no volátil, ¿qué tal para 2007+?

for cell    =INDEX($A$1:$XFC$1048576,ROW(),COLUMN())
for column  =INDEX($A$1:$XFC$1048576,0,COLUMN())
for row     =INDEX($A$1:$XFC$1048576,ROW(),0)

Tengo un error extraño en Excel 2010 en el que no acepta la última fila o columna para estas fórmulas (fila 1048576 y columna XFD), por lo que es posible que deba hacer referencia a éstas brevemente. No estoy seguro de si eso es lo mismo para otras versiones, así que aprecie los comentarios y edite.

y para 2003 (INDEX se volvió no volátil en el 97):

for cell    =INDEX($A$1:$IV$65536,ROW(),COLUMN())
for column  =INDEX($A$1:$IV$65536,0,COLUMN())
for row     =INDEX($A$1:$IV$65536,ROW(),0)

Answer 8

Solo para la fila, pero intente hacer referencia a una celda justo debajo de la celda seleccionada y restar una de la fila.

=ROW(A2)-1

Cede la fila de la celda A1 (esta fórmula iría en la celda A1.

Esto evita todo el uso indirecto () e índice () pero aún funciona.

Answer 9

donde F13 es la celda que necesita referenciar:

=CELL("Row",F13)  yields 13; its row number

=CELL("Col",F13)  yields 6; its column number;  

=SUBSTITUTE(ADDRESS(1,COLUMN(F13)*1,4),"1","") yields F; its column letter