如何简化Excel中INDIRECT/ADDRESS/ROW/COLUMN组合函数写法

核心逻辑说明 #

直接用=Sheet1!A2这类相对引用时，引用会绑定单元格内容，Sheet1排序后单元格位置变动，引用会跟随原单元格跳转，导致同步错位。按物理行/列位置取值的思路是正确的，但INDIRECT+ADDRESS的写法冗余，且INDIRECT是易失性函数，数据量大时会增加不必要的计算开销。

通用简化公式 #

全区域兼容公式（支持所有Excel版本，可右拉下拉） #

在Sheet2的A2单元格输入以下公式，直接向右、向下拖动覆盖所有需要同步的单元格区域即可，无需给Param列、Value列单独配置不同公式：

=IF(COLUMN()<4,INDEX(Sheet1!A:A,ROW()),IF(INDEX(Sheet1!$G:$G,ROW())=0,INDEX(Sheet1!A:A,ROW()),INDEX(Sheet1!A:A,ROW())/100))

公式逻辑：

• 用INDEX(Sheet1!列范围,ROW())替代冗长的INDIRECT(ADDRESS(...))写法，按物理行号、列号取Sheet1对应位置的值，和原有公式效果完全一致，Sheet1排序时不会错位；同时INDEX是非易失性函数，仅在数据源变化时触发重算，计算效率远高于INDIRECT，数据量大时不会出现无意义的卡顿。
• 自动通过列号判断列属性：列号小于4（即A-C列，对应Param1-Param3）时直接返回Sheet1原值；列号大于等于4（即D-F列，对应Value1-Value3）时自动读取Sheet1同G列（Criteria列，公式中$G锁列，右拉时不会偏移）的值做判断，Criteria为0则返回原值，否则返回值除以100的结果。

支持LET函数的版本（Excel 2021及以后/365）简化写法 #

A2输入以下公式后右拉下拉即可，通过LET定义复用变量，避免重复书写INDEX引用，公式更短：

=LET(
cur_val,INDEX(Sheet1!A:A,ROW()),
criteria,INDEX(Sheet1!$G:$G,ROW()),
IF(COLUMN()<4,cur_val,IF(criteria=0,cur_val,cur_val/100))
)

365专属一键溢出公式 #

不需要手动拖动填充，A2单元格输入一次公式，会自动溢出填充所有需要同步的行和列：

=LET(
last_row,COUNTA(Sheet1!A:A),
source,Sheet1!A2:INDEX(Sheet1!G:G,last_row),
param_col,CHOOSECOLS(source,1,2,3),
value_col,CHOOSECOLS(source,4,5,6),
criteria_col,CHOOSECOLS(source,7),
HSTACK(param_col,IF(criteria_col=0,value_col,value_col/100))
)

内容的提问来源于stack exchange，提问作者Claire