如何让含INDIRECT函数的Excel公式拖动时自动切换列？

拖动以下公式时，末尾的Q:Q为硬编码，无法随拖动操作自动切换列：

=SUMIF(INDIRECT("'"&Reference!$A$1&"PN GCS'!$N:$N"),'Area Summary PN'!$D101,INDIRECT("'"&Reference!$A$1&"PN GCS'!Q:Q"))

请问是否有办法让该公式在拖动时自动切换列？对应的VBA代码如下：

Sheets("Area Summary PN").Select
Range("JU101").Select
ActiveCell.FormulaR1C1 = _
    "=SUMIF(INDIRECT(""'""&Reference!R1C1&""PN GCS'!$N:$N""),'Area Summary PN'!R[0]C4,INDIRECT(""'""&Reference!R1C1&""PN GCS'!Q:Q""))"         

Selection.AutoFill Destination:=Range("JU101:JU111"), Type:=xlFillDefault
Range("JU101:JU111").Select
Selection.Copy
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy

Range("JU101").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False

核心是把硬编码的列引用Q:Q替换为能随单元格位置动态计算的表达式，分公式直接修改和VBA代码调整两种方案：

公式直接修改方案 #

利用COLUMN()函数获取当前单元格的列号，再通过ADDRESS函数转换为对应的列字母，替换原公式中的Q:Q。

修改后的公式：

=SUMIF(INDIRECT("'"&Reference!$A$1&"PN GCS'!$N:$N"),'Area Summary PN'!$D101,INDIRECT("'"&Reference!$A$1&"PN GCS'!"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")))

• COLUMN()返回当前单元格的列号
• SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")将列号转换为对应的A1样式列标（比如JU列会返回"JU"）
  拖动公式时，该表达式会自动适配当前列，实现列引用的动态切换。

VBA代码调整方案 #

在R1C1引用格式中，C:C代表当前列的整列，直接替换原代码中的Q:Q即可。

修改后的VBA代码：

Sheets("Area Summary PN").Select
Range("JU101").Select
ActiveCell.FormulaR1C1 = _
    "=SUMIF(INDIRECT(""'""&Reference!R1C1&""PN GCS'!$N:$N""),'Area Summary PN'!R[0]C4,INDIRECT(""'""&Reference!R1C1&""PN GCS'!C:C""))"         

Selection.AutoFill Destination:=Range("JU101:JU111"), Type:=xlFillDefault
Range("JU101:JU111").Select
Selection.Copy
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy

Range("JU101").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False

当代码执行复制粘贴到右侧列时，C:C会自动对应目标列的整列，满足拖动切换列的需求。

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