在Excel公式中使用相对位置 [英] Using relative positions in Excel formulas
问题描述
例如,在单元格F12中,我有一个公式:= F11 + D12 -E12
这基本上是从上面的单元格中取值,然后将单元格2的值添加到左边,并将单元格中的值直接减去左边。
但是,因为我正在使用实际的单元格地址,所以一旦我删除了一行,下面的所有行将无效。
如何通过相对位置来表达公式(即=1以上+2到左 - 1到左)
谢谢。
您可以使用
-
= INDIRECT(F11,true)+ INDIRECT(D12,true)-INDIRECT(E12,true)
/ li>
-
= INDIRECT(R11C6,false)+ INDIRECT(R12C4,false)-INDIRECT(R12C5,false)
-
= INDIRECT(R [-1],false )+ INDIRECT(C [-2],false)-INDIRECT(C [-1],false)
(F12,0,-2)-OFFSET(F12,0,-1) = OFFSET(F12,-1,0)+ OFFSET li>
这两个函数也允许指定范围,只需使用您个人喜好的任何内容(请参阅Excel帮助)...
How do I create a formula that isn't made invalid when I delete a row.
For example in cell F12 I have the formula: =F11+D12-E12
This basically says take the value from the cell above then add the value of the cell 2 to the left and subtract the value in the cell directly to the left.
However, because I'm using actual cell addresses, as soon as I delete a row, all the rows below become invalid.
How do i express the formula by relative position (ie = "1 above" + "2 to left" - "1 to left")
Thanks.
You can use either
=OFFSET(F12,-1,0)+OFFSET(F12,0,-2)-OFFSET(F12,0,-1)
, or=INDIRECT("F11",true)+INDIRECT("D12",true)-INDIRECT("E12",true)
=INDIRECT("R11C6",false)+INDIRECT("R12C4",false)-INDIRECT("R12C5",false)
=INDIRECT("R[-1]",false)+INDIRECT("C[-2]",false)-INDIRECT("C[-1]",false)
Both functions also allow to specify ranges, just use whatever has your personal preference (see Excel Help)…
这篇关于在Excel公式中使用相对位置的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!