las - Fórmula de Excel para referenciar "CELULA A LA IZQUIERDA"

function in excel definition

Question

Estoy intentando hacer un formato condicional para que el color de la celda cambie si el valor es diferente del valor en la celda que queda (cada columna es un mes, en cada fila están los gastos de cierto objeto. Quiero monitorear cambia fácilmente de precio en meses).

Puedo hacerlo por celda y formato, arrastrarlo, pero me gustaría que se aplique una fórmula general a toda la hoja de trabajo.

¡Gracias!

Answer 1

Al crear su formato condicional, establezca el rango al que se aplica lo que desea (la hoja completa), luego ingrese una fórmula relativa (elimine los $ signos) como si solo estuviese formateando la esquina superior izquierda.

Excel aplicará correctamente el formato al resto de las celdas en consecuencia.

En este ejemplo, comenzando en B1, la celda izquierda sería A1. Solo use eso; no se requiere una fórmula avanzada.

Si buscas algo más avanzado, puedes jugar con la column() , la row() e indirect(...) .

Answer 2

Podría usar una secuencia de comandos de VBA que cambie el formato condicional de una selección (es posible que tenga que ajustar la condición y el formato en consecuencia):

For Each i In Selection
i.FormatConditions.Delete
i.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=" & i.Offset(0, -1).Address
With i.FormatConditions(1).Font
    .Bold = True
End With
Next i

Answer 3

=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)

Answer 4

Si cambia su referencia de celda para usar la notación R1C1 (Herramientas | Opciones, pestaña General), entonces puede usar una notación simple y pegarla en cualquier celda.

Ahora tu fórmula es simple:

=RC[-1]

Answer 5

La versión más corta y compatible es:

=INDIRECT("RC[-1]",0)

"RC [-1]" significa una columna a la izquierda. "R [1] C [-1]" está abajo a la izquierda.

El segundo parámetro 0 significa que el primer parámetro se interpreta utilizando la notación R1C1.

Las otras opciones:

=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)

Demasiado tiempo en mi opinión. Pero es útil si el valor relativo es dinámico / derivado de otra celda. p.ej:

=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0, A1)

La opción más simple:

= RC[-1]

tiene la desventaja de que debe activar la notación R1C1 utilizando las opciones, que es un no-go cuando otras personas tienen que usar Excel.

Answer 6

llene la celda A1 , con la siguiente fórmula:

 =IF(COLUMN(A1)=1;"";OFFSET(A20;0;-1))&"1"

Luego autoextendido a la derecha, obtienes

 1|  A  |  B  |  C  |  ect ect
 2|    1|   11|  111|  ect ect

Si el desplazamiento está fuera del rango de la celda disponible, obtienes el #REF! error.

Espero que lo disfrutes.

Answer 7

Aún más simple:

=indirect(address(row(), column() - 1))

OFFSET devuelve una referencia relativa a la referencia actual, por lo que si indirecta devuelve la referencia correcta, no la necesita.

Answer 8

En lugar de escribir el muy largo:

=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)

Simplemente puede escribir:

=OFFSET(*Name of your Cell*,0,-1)

Así, por ejemplo, puede escribir en la celda B2 :

=OFFSET(B2,0,-1)

para hacer referencia a la celda B1

Todavía gracias Jason Young !! ¡Nunca hubiera llegado a esta solución sin tu respuesta!

Answer 9

Al crear una función definida por el usuario , descubrí que las otras respuestas que implican las funciones OFFSET e INDIRECT no se pueden aplicar.

En su lugar, debe usar Application.Caller para referirse a la celda en la que se ha utilizado la función definida por el usuario (UDF). En un segundo paso, convierta el índice de la columna al nombre de la columna correspondiente.
Finalmente, puede hacer referencia a la celda izquierda utilizando la función Rango de la hoja de trabajo activa.

Function my_user_defined_function(argument1, argument2)
    '' Way to convert a column number to its name copied from 
    '' http://.com/a/10107264
    '' Answer by Siddarth Rout (http://.com/users/1140579/siddharth-rout)
    '' License (if applicable due to the small amount of code): CC BY-SA 3.0
    colName = Split(Cells(, (Application.Caller(1).Column - 1)).Address, "$")(1)

    rowNumber = Application.Caller(1).Row

    left_cell_value = ActiveSheet.Range(colName & rowNumber).Value

    '' Now do something with left_cell_value

Answer 10

Me encontré con este hilo porque siempre quise hacer referencia a la "celda hacia la izquierda" pero CRUCIALMENTE de forma no volátil (sin DESPLAZAMIENTO, INDIRECTO y desastres similares). Mirando hacia arriba y hacia abajo, sin respuestas. (Este hilo tampoco proporciona ninguna respuesta.) Después de algunos retoques, me encontré con el método más asombroso, que me gusta compartir con esta comunidad:

Supongamos un valor inicial de 100 en E6. Supongamos que ingreso un delta a este valor en F5, digamos 5. Entonces, calcularíamos el valor de continuación (105) en F6 = E6 + F5. Si desea agregar otro paso, simplemente: simplemente copie la columna F en la columna G e ingrese un nuevo delta en G5.

Esto es lo que hacemos, periódicamente. Cada columna tiene una fecha y estas fechas DEBEN estar en orden cronológico (para ayudar con MATCH, etc.). De vez en cuando sucede que olvidamos entrar en un paso. Ahora suponga que desea insertar una columna entre F y G (para ponerse al día con su omisión) y copie F en la nueva G (para volver a llenar la fórmula de continuación). Esto no es NADA BREVE de un desastre total. Pruébalo: H6 ahora dirá = F6 + H5 y NO (como lo necesitamos) = G6 + H5. (El nuevo G6 será correcto).

Para que esto funcione, podemos ofuscar este cálculo banal de la manera más sorprendente F6 = índice ($ E6: F6; 1; columnas ($ E1: F1) -1) + F5. Copie a la derecha y obtendrá G6 = índice ($ E6: G6; 1; columnas ($ E1: G1) -1) + G5.

Esto nunca debería funcionar, ¿verdad? Referencia circular, ¡claro! Pruébalo y sorpréndete. Parece que Excel se da cuenta de que, aunque el rango INDEX abarca la celda que estamos recalculando, el ÍNDICE no aborda esa celda en sí misma y, por lo tanto, NO crea una referencia circular.

Entonces ahora estoy en casa y seco. Inserte una columna entre F y G y obtenemos exactamente lo que necesitamos: el valor de continuación en el antiguo H se referirá de nuevo al valor de continuación que insertamos en el nuevo G.

Answer 11

¿Por qué no solo usar:

=ADDRESS(ROW(),COLUMN()-1)