vba 轻松引用偏移中的单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10738284/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Easily reference cells in offset
提问by IttayD
I have a worksheet with many formulas that look one cell above, or to the right. If I use ctrl-D to populate rows, the formulas are updated correctly (so =B1 + A2 in B2 becomes =B2+A3 in B3). But when I insert in between rows, things get messed (so inserting a row above B3, moving it to B4 makes its formula become =B2+A4, which is not what I want)
我有一个包含许多公式的工作表，这些公式看起来是上面或右边的一个单元格。如果我使用 ctrl-D 来填充行，则公式会正确更新（因此 B2 中的 =B1 + A2 变为 B3 中的 =B2+A3）。但是当我在行之间插入时，事情会变得混乱（因此在 B3 上方插入一行，将其移动到 B4 使其公式变为 =B2+A4，这不是我想要的）
So I thought to create a CellAbove and CellRight formulas (so I can write =CellAbove() + CellRight()). How can I do that?
所以我想创建一个 CellAbove 和 CellRight 公式（这样我就可以写 =CellAbove() + CellRight()）。我怎样才能做到这一点？
My attempt:
我的尝试：
Function CellAbove()
CellAbove = [Address(Row() - 1, Column())].Value
End Function
doesn't work.
不起作用。
UPDATE: The function below works, but cells that have it are not updated:
更新：下面的函数有效，但没有更新具有它的单元格：
Function CellAbove()
CellAbove = Range([Address(Row() - 1, Column())])
End Function
So if A1 has 2 and A2 has =CellAbove() then A2 will show 2. But if now I change A1 to 3, then A2 will still show 2
所以如果 A1 有 2 并且 A2 有 =CellAbove() 那么 A2 将显示 2。但如果现在我将 A1 更改为 3，那么 A2 仍将显示 2
采纳答案by datatoo
Since your function worked, and it seemed all you needed was it to recalculate when you insert rows. you said:
由于您的函数有效，而且您似乎只需要在插入行时重新计算即可。你说：
"but cells that have it are not updated"
“但拥有它的单元格没有更新”
User Defined Functions are by default NOT volatile so they won't automatically recalculate. You could change your existing function to this.
用户定义的函数默认情况下不是易失性的，因此它们不会自动重新计算。您可以将现有功能更改为此。
Function CellAbove()
Application.Volatile (True)
CellAbove = Range([Address(Row() - 1, Column())])
End Function
This can impact performance, but seems to be what you are asking for.
这可能会影响性能，但似乎正是您所要求的。
回答by Andy Brown
=INDIRECT("R"&ROW()-1&"C"&COLUMN(),FALSE) would work as a function.
=INDIRECT("R"&ROW()-1&"C"&COLUMN(),FALSE) 可以作为函数工作。
Suppose you're on B2. Then the bit inside the brackets would evaluate to R1C2. The INIDRECT function would then calculate what is at row 1 column 2 (you need the FALSE to ensure you're using the R1C1 style of cell address).
假设您在 B2 上。然后括号内的位将评估为 R1C2。然后 INIDRECT 函数将计算第 1 行第 2 列的内容（您需要 FALSE 以确保您使用的是 R1C1 样式的单元格地址）。
I'm sure you could do something simpler using:
我相信你可以使用以下方法做一些更简单的事情：
OFFSET(CurrentCell,-1,0)
偏移（当前单元格，-1,0）
but I can't see how you can refer to the current cell.
但我看不到您如何引用当前单元格。
回答by Cylian
Functions from yours
你的功能
Function CellAbove()
CellAbove = Range([Address(Row() - 1, Column())])
End Function
Function CellRight()
CellRight = Range([Address(Row(), Column() + 1)])
End Function
May be called as
可以称为
Function test000000()
ActiveCell = CellAbove + CellRight
End Sub
回答by Brian Camire
You could do this using built-in Excel functions like INDIRECT, ADDRESS, ROW, and COLUMN, as in...
您可以使用内置 Excel 函数（如 INDIRECT、ADDRESS、ROW 和 COLUMN）来执行此操作，如...
=INDIRECT(ADDRESS(ROW()-1,COLUMN()))
...for the cell above, or...
...对于上面的单元格，或...
=INDIRECT(ADDRESS(ROW(),COLUMN()+1))
...for the cell to the right.
...对于右侧的单元格。
Another approach, again using built-in capability, would be to use the OFFSET function. For example, in cell B2, you could enter the formula...
再次使用内置功能的另一种方法是使用 OFFSET 函数。例如，在单元格 B2 中，您可以输入公式...
=OFFSET(B2,-1,0)
...for the cell above, or...
...对于上面的单元格，或...
=OFFSET(B2,0,1)
...for the cell to the right. It looks like a circular reference, but it's not, since you are not referencing the value of the same cell, only its address.
...对于右侧的单元格。它看起来像一个循环引用，但事实并非如此，因为您没有引用同一个单元格的值，而只是引用它的地址。
Either way, you more or less lose the ability to use the "Trace Dependents" and "Trace Precedents" features, since each cell essentially references only itself.
无论哪种方式，您或多或少都会失去使用“跟踪依赖项”和“跟踪先例”功能的能力，因为每个单元格基本上只引用自身。
回答by Siddharth Rout
Followup on my comment. We don't need vba for this :)
跟进我的评论。我们不需要 vba :)
After you have inserted the blank row
插入空白行后
Step 1
第1步
Drag the formula down from B2to B3or simply select B3and press CTL D
将公式从B2拖到B3或简单地选择B3并按CTL D
Step 2
第2步
Select Cell B3. Do an Autofill by double clicking on the bottom right corner of the cell.
选择单元格B3。通过双击单元格的右下角进行自动填充。
