Excel VBA中SUMPRODUCT与TRIM嵌套函数求值及引号使用问题咨询

双引号逻辑说明 #

你代码里的双引号位置本身没有错误，相关规则如下：

1. Excel公式要求：文本匹配的目标值必须用双引号包裹，否则Excel会将其识别为名称/字段名，返回#NAME?错误。你最终要交给Excel计算的公式格式应为：

SUMPRODUCT(--(TRIM($D:$D)="apple"),$G:$G)

2. VBA字符串转义规则：如果要在VBA的字符串常量里输出1个英文双引号"，必须连续写2个英文双引号做转义，否则VBA会判定为字符串的结束标记。
   所以你拼接变量的部分""" & Txt & """拆分逻辑为：

• 前两个连续双引号""：转义后输出1个双引号，对应公式里apple左侧的包裹引号
• 中间拼接Txt变量的实际值
• 后两个连续双引号""：转义后输出1个双引号，对应公式里apple右侧的包裹引号
• 最外层的单个双引号是VBA字符串本身的起止标记，不会输出到最终公式里

代码优化建议 #

你当前的写法可以实现需求，但有两个可优化点：

• 你需要匹配的是末尾带空格的文本，用RTrim（仅删除文本末尾空格）比TRIM（删除文本前后所有空格）更精准，可以避免开头/中间带空格的异常值被误匹配
• 整列引用$D:$D/$G:$G会让SUMPRODUCT计算全量104万行，性能极低，建议先定位到实际有数据的最后一行再拼接公式范围

优化后代码示例 #

Dim LastRow As Long
Dim TargetSheet As Worksheet
Set TargetSheet = ActiveWorkbook.Sheets(MySheet)
' 定位D列最后一行有数据的行号
LastRow = TargetSheet.Cells(TargetSheet.Rows.Count, "D").End(xlUp).Row
' 拼接公式
MyValue = TargetSheet.Evaluate("SUMPRODUCT(--(RTrim($D1:$D" & LastRow & ")=""" & Txt & """),$G1:$G" & LastRow & ")")

调试方法 #

如果后续再遇到公式拼接的疑问，可以先把拼接好的公式输出到立即窗口验证格式：

Dim FormulaStr As String
FormulaStr = "SUMPRODUCT(--(RTrim($D1:$D" & LastRow & ")=""" & Txt & """),$G1:$G" & LastRow & ")"
Debug.Print FormulaStr ' VBA编辑器按Ctrl+G可调出立即窗口查看输出结果

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