从上面的单元格获取值

在 Google 电子表格中是否可能达到上面的单元格的值？

意义: 在一个单元格 A2中，我想显示上面单元格的值，即 A1。不是因为它是那个坐标，而是因为它是上面的单元格。我通过简单地设置值等于上面的单元格来完成:

enter image description here

如果我在这两者之间创建一个新行，我得到这个:

enter image description here

我们知道，这些值没有变化，因为引用在这种方式下不是相对的。我如何使它相对于单元格，所以我总是选择上面的单元格，无论什么被改变？我希望结果是这样的:

enter image description here

术语 亲戚当然是 这个案子是错的，但我希望你明白我的意思。我 都有想要通常的相对行为(这样我可以移动单元格本身，它的参考将适合新的列和行) 还有的行为，参考不指向特定的固定坐标，而是从单元格本身的特定路径。

98787 次浏览
小开
最佳答案

You can address it like this:

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

COLUMN() returns a numeric reference to the current column

ROW() returns a numeric reference to the current row.

In the example here, subtracting 1 from the row gives you the previous row. This math can be applied to the ROW() and/or the COLUMN(), but in answering your question, this formula will reference the cell above.

Then we have ADDRESS() which accepts a numeric row and column reference and returns a cell reference as a string.

Finally INDIRECT() allows you to pass a cell reference in as a string, and it processes it as a formula.

Google Spreadsheets give you help hints as you type, so you should get a full explanation of each function as you type the formula above in.

小开

For anyone who stumbles across this question, you can also specify the column by doing something like this:

=INDIRECT("A" & ROW()-1)

This comes in handy if you are returning values in Column B but checking against the previous row in Column A.

小开

The shortest, and easier for VisiCal old timer is the old RC syntax with relative values…

=INDIRECT("R[-1]C[0]"; FALSE)

Very visual, simple code template to remember and modify, and very short.

Regards, Antoine


提交答案