Excel:INDIRECT(地址(...))vs.偏移量(...) [英] Excel: INDIRECT(ADDRESS(...)) vs. OFFSET(...)

问题描述

似乎使用Excel函数 INDIRECT(ADDRESS(...))和 OFFSET(...)可以互换使用.

It seems that using Excel functions INDIRECT(ADDRESS(...)) and OFFSET(...) can be used interchangeably.

例如，以下两个公式将相同的结果返回到绝对引用:

For example, the following two formulas return the same result to an absolute reference:

= INDIRECT(ADDRESS(1,1))

= OFFSET(<current cell>,1-ROW(),1-COLUMN())

类似地，接下来的两个公式将相同的结果返回给相对引用(例如，在这种情况下，这些公式将返回该单元格下方该单元格中具有公式的任何值):

And similarly the next two formulas return the same result to a relative reference (in this case, for example, these formulas return whatever value is in the cell below this cell with the formula):

= OFFSET(<current cell>,1,0)

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

我的问题是:是否总是总是首选一种方法?在我看来， INDIRECT(ADDRESS(...))适用于更多的绝对类型引用，而 OFFSET(...)适用于更多的相对类型引用，但是，如上所示，这两种方法都可以用来完成任何一种参考类型.或者，是否有完全不同的替代方法使用这两个功能，而优于这两个选择?

My questions are: Is one method always preferred over the other? It seems to me that INDIRECT(ADDRESS(...)) lends itself to more absolute type references and OFFSET(...) lends itself to more relative type references, but as shown above, either method could be used to accomplish either type of reference. Or, is there a completely different alternative to using these two functions that is superior to both of these options?

推荐答案

两者都不是首选，因为它们都是易失函数，而且它们太多都会影响计算时间.

Neither are preferred as both are volatile functions and too many of them will affect the calc times.

即使基础数据未更改，每次应用程序重新计算时，可变函数也会重新计算.

Volatile function recalc every time that the application recalcs, even if the underlying data has not changed.

使用INDEX代替它是非易失性的:

Use INDEX instead it is non volatile:

=INDEX($1:$1048576,ROW()+1,COLUMN())

仅当引用的数据发生更改时，才会重新计算.

This will only recalc when the data to which it refers changes.

仅在工作表为变量时才使用INDIRECT.

Use INDIRECT only when the sheet is the variable.

OFFSET几乎总是可以用INDEX代替.

OFFSET can almost always be replaced with INDEX.

这篇关于Excel:INDIRECT(地址(...))vs.偏移量(...)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！