I'm trying to do conditional formatting so that the cell color will change if the value is different from the value in the cell left of it (each column is a month, in each row are the expenses on certain object. I want to monitor easily changes in prices over months.)
我正在尝试进行条件格式化，以便当值与它左边的单元格中的值不同时，单元格的颜色会发生变化(每一列是一个月，每一行是对特定对象的开销)。我希望能在几个月内轻松地监测价格变化。
I can do it per cell and format-drag it, but I would like a general formula to apply to the whole worksheet.
我可以对每个单元格进行处理并拖拽它，但是我希望一个通用公式应用到整个工作表中。
Thanks!
谢谢!
11 个解决方案
#1
63
=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)
#2
60
The shortest most compatible version is:
最短最兼容的版本是:
=INDIRECT("RC[-1]",0)
"RC[-1]" means one column to the left. "R[1]C[-1]" is bottom-left.
“RC[-1]”表示左边一列。“R[1]C[1]”是左下侧。
The second parameter 0 means that the first parameter is interpreted using R1C1 notation.
第二个参数0表示第一个参数使用R1C1符号解释。
The other options:
其他选项:
=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)
Too long in my opinion. But useful if the relative value is dynamic/derived from another cell. e.g.:
在我看来太长了。但如果相对值是动态的/从另一个单元格派生出来的，则会很有用。例如:
=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0, A1)
The most simple option:
最简单的选择:
= RC[-1]
has the disadvantage that you need to turn on R1C1 notation using options, which is a no-go when other people have to use the excel.
有一个缺点，你需要使用选项打开R1C1表示法，这是其他人必须使用excel时的禁忌。
#3
10
When creating your conditional formatting, set the range to which it applies to what you want (the whole sheet), then enter a relative formula (remove the $ signs) as if you were only formatting the upper-left corner.
在创建条件格式时，设置它应用于您想要的范围(整个表)，然后输入一个相对公式(删除$符号)，就好像您只是在格式化左上角一样。
Excel will properly apply the formatting to the rest of the cells accordingly.
Excel将适当地将格式应用到其他单元格中。
In this example, starting in B1, the left cell would be A1. Just use that--no advanced formula required.
在这个例子中，从B1开始，左边的细胞是A1。只要使用它——不需要高级公式。
If you're looking for something more advanced, you can play around with column(), row(), and indirect(...).
如果您正在寻找更高级的东西，您可以使用列()、row()和间接(…)
#4
7
If you change your cell reference to use R1C1 notation (Tools|Options, General tab), then you can use a simple notation and paste it into any cell.
如果您更改单元格引用以使用R1C1表示法(工具|选项，通用选项卡)，那么您可以使用一个简单的表示法并将其粘贴到任何单元格中。
Now your formula is simply:
你的公式很简单:
=RC[-1]
#5
2
fill the A1 cell, with the following formula :
将A1单元格填满，公式如下:
=IF(COLUMN(A1)=1;"";OFFSET(A20;0;-1))&"1"
Then autoextend to right, you get
然后向右自动展开，得到
1| A | B | C | ect ect
2| 1| 11| 111| ect ect
If offset is outside the range of the available cell, you get the #REF! error.
如果偏移量超出可用单元格的范围，您将获得#REF!错误。
Hope you enjoy.
希望你能喜欢。
#6
1
You could use a VBA script that changes the conditional formatting of a selection (you might have to adjust the condition & formatting accordingly):
您可以使用VBA脚本更改选择的条件格式(您可能需要相应地调整条件和格式):
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
#7
1
Instead of writing the very long:
而不是写很长时间:
=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)
You can simply write:
您可以简单地编写:
=OFFSET(*Name of your Cell*,0,-1)
Thus for example you can write into Cell B2:
例如，你可以把它写进细胞B2:
=OFFSET(B2,0,-1)
to reference to cell B1
参考B1细胞
Still thanks Jason Young!! I would have never come up with this solution without your answer!
仍然谢谢你杰森年轻! !如果没有你的回答，我是不会想出这个解决办法的!
#8
1
When creating a User Defined Function, I found out that the other answers involving the functions OFFSET and INDIRECT cannot be applied.
在创建用户定义的函数时，我发现涉及函数偏移和间接的其他答案不能被应用。
Instead, you have to use Application.Caller to refer to the cell the User Defined Function (UDF) has been used in. In a second step, you convert the column's index to the corresponding column's name.
Finally, you are able to reference the left cell using the active worksheet's Range function.
相反，您必须使用Application。调用者引用用户定义函数(UDF)中使用的单元。在第二步中，将列的索引转换为相应列的名称。最后，您可以使用活动工作表的范围函数引用左单元格。
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
#9
0
Even simpler:
更简单:
=indirect(address(row(), column() - 1))
OFFSET returns a reference relative to the current reference, so if indirect returns the correct reference, you don't need it.
偏移量返回相对于当前引用的引用，因此如果间接返回正确的引用，则不需要它。
#10
0
I stumbled upon this thread because I wanted to always reference the "cell to the left" but CRUCIALLY in a non-volatile way (no OFFSET, INDIRECT and similar disasters). Looking the web up and down, no answers. (This thread does not actually provide an answer either.) After some tinkering about I stumbled upon the most astonishing method, which I like to share with this community:
我偶然发现了这个线程，因为我想总是引用“向左的单元格”，但关键是要以一种非易失性的方式(没有抵消、间接和类似的灾难)。上下打量着网络，没有答案。(这个线程实际上也不提供答案。)经过一番修修补补之后，我偶然发现了一个最惊人的方法，我想和这个社区分享:
Suppose a starting value of 100 in E6. Suppose I enter a delta to this value in F5, say 5. We would then calculate the continuation value (105) in F6 = E6+F5. If you want to add another step, easy: just copy column F to column G and enter a new delta in G5.
假设E6中的起始值为100。假设我在F5中输入一个delta值，比如5。然后计算F6 = E6+F5中的延续值(105)。如果您想添加另一个步骤，很简单:将列F复制到列G，然后在G5中输入一个新的delta。
This is what we do, periodically. Each column has a date and these dates MUST BE in chronological order (to help with MATCH etc). Every so often it happens that we forget to enter a step. Now suppose you want to insert a column between F and G (to catch up with your omission) and copy F into the new G (to repopulate the continuation formula). This is NOTHING SHORT of a total disaster. Try it - H6 will now say =F6+H5 and NOT (as we absolutely need it to) =G6+H5. (The new G6 will be correct.)
这就是我们定期做的事情。每一列都有一个日期，这些日期必须按时间顺序排列(以帮助匹配)。我们常常忘记迈出一步。现在假设您想在F和G之间插入一个列(以赶上您的遗漏)，并将F复制到新的G中(以重新填充延续公式)。这简直就是一场灾难。试试- H6现在会说=F6+H5而不是(我们绝对需要它)=G6+H5。(新的G6将是正确的。)
To make this work, we can obfuscate this banal calculation in the most astonishing manner F6=index($E6:F6;1;columns($E1:F1)-1)+F5. Copy right and you get G6=index($E6:G6;1;columns($E1:G1)-1)+G5.
为了完成这项工作，我们可以以最令人惊讶的方式模糊这个平庸的计算F6=index($E6:F6;1;column ($E1:F1)-1)+F5。复制右，得到G6=index($E6:G6;1;列($E1:G1)-1)+G5。
This should never work, right? Circular reference, clearly! Try it out and be amazed. Excel seems to realize that although the INDEX range spans the cell we are recalculating, that cell itself is not addressed by the INDEX and thus DOES NOT create a circular reference.
这绝对不行，对吧?循环引用,很明显!试一试，然后感到惊讶。Excel似乎意识到，尽管索引范围跨越了我们正在重新计算的单元格，但该单元格本身并没有被索引寻址，因此不会创建循环引用。
So now I am home and dry. Insert a column between F and G and we get exactly what we need: The continuation value in the old H will refer back to the continuation value we inserted in the new G.
所以现在我回家了。在F和G之间插入一列，我们就得到了我们需要的:原来H中的延续值将返回到我们在新G中插入的延续值。
#11
0
Why not just use:
为什么不直接使用:
=ADDRESS(ROW(),COLUMN()-1)
#1
63
=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)
#2
60
The shortest most compatible version is:
最短最兼容的版本是:
=INDIRECT("RC[-1]",0)
"RC[-1]" means one column to the left. "R[1]C[-1]" is bottom-left.
“RC[-1]”表示左边一列。“R[1]C[1]”是左下侧。
The second parameter 0 means that the first parameter is interpreted using R1C1 notation.
第二个参数0表示第一个参数使用R1C1符号解释。
The other options:
其他选项:
=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)
Too long in my opinion. But useful if the relative value is dynamic/derived from another cell. e.g.:
在我看来太长了。但如果相对值是动态的/从另一个单元格派生出来的，则会很有用。例如:
=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0, A1)
The most simple option:
最简单的选择:
= RC[-1]
has the disadvantage that you need to turn on R1C1 notation using options, which is a no-go when other people have to use the excel.
有一个缺点，你需要使用选项打开R1C1表示法，这是其他人必须使用excel时的禁忌。
#3
10
When creating your conditional formatting, set the range to which it applies to what you want (the whole sheet), then enter a relative formula (remove the $ signs) as if you were only formatting the upper-left corner.
在创建条件格式时，设置它应用于您想要的范围(整个表)，然后输入一个相对公式(删除$符号)，就好像您只是在格式化左上角一样。
Excel will properly apply the formatting to the rest of the cells accordingly.
Excel将适当地将格式应用到其他单元格中。
In this example, starting in B1, the left cell would be A1. Just use that--no advanced formula required.
在这个例子中，从B1开始，左边的细胞是A1。只要使用它——不需要高级公式。
If you're looking for something more advanced, you can play around with column(), row(), and indirect(...).
如果您正在寻找更高级的东西，您可以使用列()、row()和间接(…)
#4
7
If you change your cell reference to use R1C1 notation (Tools|Options, General tab), then you can use a simple notation and paste it into any cell.
如果您更改单元格引用以使用R1C1表示法(工具|选项，通用选项卡)，那么您可以使用一个简单的表示法并将其粘贴到任何单元格中。
Now your formula is simply:
你的公式很简单:
=RC[-1]
#5
2
fill the A1 cell, with the following formula :
将A1单元格填满，公式如下:
=IF(COLUMN(A1)=1;"";OFFSET(A20;0;-1))&"1"
Then autoextend to right, you get
然后向右自动展开，得到
1| A | B | C | ect ect
2| 1| 11| 111| ect ect
If offset is outside the range of the available cell, you get the #REF! error.
如果偏移量超出可用单元格的范围，您将获得#REF!错误。
Hope you enjoy.
希望你能喜欢。
#6
1
You could use a VBA script that changes the conditional formatting of a selection (you might have to adjust the condition & formatting accordingly):
您可以使用VBA脚本更改选择的条件格式(您可能需要相应地调整条件和格式):
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
#7
1
Instead of writing the very long:
而不是写很长时间:
=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)
You can simply write:
您可以简单地编写:
=OFFSET(*Name of your Cell*,0,-1)
Thus for example you can write into Cell B2:
例如，你可以把它写进细胞B2:
=OFFSET(B2,0,-1)
to reference to cell B1
参考B1细胞
Still thanks Jason Young!! I would have never come up with this solution without your answer!
仍然谢谢你杰森年轻! !如果没有你的回答，我是不会想出这个解决办法的!
#8
1
When creating a User Defined Function, I found out that the other answers involving the functions OFFSET and INDIRECT cannot be applied.
在创建用户定义的函数时，我发现涉及函数偏移和间接的其他答案不能被应用。
Instead, you have to use Application.Caller to refer to the cell the User Defined Function (UDF) has been used in. In a second step, you convert the column's index to the corresponding column's name.
Finally, you are able to reference the left cell using the active worksheet's Range function.
相反，您必须使用Application。调用者引用用户定义函数(UDF)中使用的单元。在第二步中，将列的索引转换为相应列的名称。最后，您可以使用活动工作表的范围函数引用左单元格。
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
#9
0
Even simpler:
更简单:
=indirect(address(row(), column() - 1))
OFFSET returns a reference relative to the current reference, so if indirect returns the correct reference, you don't need it.
偏移量返回相对于当前引用的引用，因此如果间接返回正确的引用，则不需要它。
#10
0
I stumbled upon this thread because I wanted to always reference the "cell to the left" but CRUCIALLY in a non-volatile way (no OFFSET, INDIRECT and similar disasters). Looking the web up and down, no answers. (This thread does not actually provide an answer either.) After some tinkering about I stumbled upon the most astonishing method, which I like to share with this community:
我偶然发现了这个线程，因为我想总是引用“向左的单元格”，但关键是要以一种非易失性的方式(没有抵消、间接和类似的灾难)。上下打量着网络，没有答案。(这个线程实际上也不提供答案。)经过一番修修补补之后，我偶然发现了一个最惊人的方法，我想和这个社区分享:
Suppose a starting value of 100 in E6. Suppose I enter a delta to this value in F5, say 5. We would then calculate the continuation value (105) in F6 = E6+F5. If you want to add another step, easy: just copy column F to column G and enter a new delta in G5.
假设E6中的起始值为100。假设我在F5中输入一个delta值，比如5。然后计算F6 = E6+F5中的延续值(105)。如果您想添加另一个步骤，很简单:将列F复制到列G，然后在G5中输入一个新的delta。
This is what we do, periodically. Each column has a date and these dates MUST BE in chronological order (to help with MATCH etc). Every so often it happens that we forget to enter a step. Now suppose you want to insert a column between F and G (to catch up with your omission) and copy F into the new G (to repopulate the continuation formula). This is NOTHING SHORT of a total disaster. Try it - H6 will now say =F6+H5 and NOT (as we absolutely need it to) =G6+H5. (The new G6 will be correct.)
这就是我们定期做的事情。每一列都有一个日期，这些日期必须按时间顺序排列(以帮助匹配)。我们常常忘记迈出一步。现在假设您想在F和G之间插入一个列(以赶上您的遗漏)，并将F复制到新的G中(以重新填充延续公式)。这简直就是一场灾难。试试- H6现在会说=F6+H5而不是(我们绝对需要它)=G6+H5。(新的G6将是正确的。)
To make this work, we can obfuscate this banal calculation in the most astonishing manner F6=index($E6:F6;1;columns($E1:F1)-1)+F5. Copy right and you get G6=index($E6:G6;1;columns($E1:G1)-1)+G5.
为了完成这项工作，我们可以以最令人惊讶的方式模糊这个平庸的计算F6=index($E6:F6;1;column ($E1:F1)-1)+F5。复制右，得到G6=index($E6:G6;1;列($E1:G1)-1)+G5。
This should never work, right? Circular reference, clearly! Try it out and be amazed. Excel seems to realize that although the INDEX range spans the cell we are recalculating, that cell itself is not addressed by the INDEX and thus DOES NOT create a circular reference.
这绝对不行，对吧?循环引用,很明显!试一试，然后感到惊讶。Excel似乎意识到，尽管索引范围跨越了我们正在重新计算的单元格，但该单元格本身并没有被索引寻址，因此不会创建循环引用。
So now I am home and dry. Insert a column between F and G and we get exactly what we need: The continuation value in the old H will refer back to the continuation value we inserted in the new G.
所以现在我回家了。在F和G之间插入一列，我们就得到了我们需要的:原来H中的延续值将返回到我们在新G中插入的延续值。
#11
0
Why not just use:
为什么不直接使用:
=ADDRESS(ROW(),COLUMN()-1)