谷歌表格用VLOOKUP实现适配新增列的乘积自动求和方法

核心实现逻辑 #

放弃逐列写VLOOKUP相加的冗余写法，用数组运算直接指定统计区间的首尾边界，区间内插入/删除列时Excel会自动调整引用范围，无需手动修改公式：

• 固定指定币种名称行、持仓数值行的首尾单元格作为统计范围
• 批量匹配范围内所有币种的当前价格
• 自动逐列计算持仓*对应价格，最后求和得到总价值

可用公式 #

适用于Excel 365/2021及以上版本 #

直接使用SUMPRODUCT+批量VLOOKUP即可，以示例中A2:C2为币种行、A3:C3为持仓行、F2:G4为「币种-当前价格」匹配表为例：

=SUMPRODUCT(A3:C3 * VLOOKUP(A2:C2, F$2:G$1000, 2, FALSE))

注：公式中价格表范围写为F$2:G$1000是预留足够的新增币种空间，你也可以直接写整列引用F:G，后续在价格表新增币种不需要调整公式。

如果你原表中存在类似U列「持仓额除以价格得到持仓量」的反向计价逻辑，可以在持仓行下方新增1行规则标记行：对应列需要乘价格填1，需要除以价格填-1，公式修改为：

=SUMPRODUCT(A3:C3 * (VLOOKUP(A2:C2, F$2:G$1000, 2, FALSE)^A4:C4))

新增列时只要给对应列填好计价规则，公式会自动适配计算逻辑。

兼容Excel 2019及更早旧版本 #

旧版Excel不支持VLOOKUP直接返回数组结果，改用INDEX+MATCH实现匹配，输入公式后按Ctrl+Shift+Enter三键确认数组公式即可：

=SUMPRODUCT(A3:C3 * INDEX(G$2:G$1000, MATCH(A2:C2, F$2:F$1000, 0)))

使用说明 #

• 公式只需要设置一次：把统计区间的首尾边界设置为你需要覆盖的最大范围（比如你预计最多会用到Z列，就把A3:C3改成A3:Z3、A2:C2改成A2:Z2），后续只要在首尾边界之间插入新的币种/钱包列，公式会自动扩展引用范围纳入新列计算，不需要手动修改
• 不要在统计区间的首尾边界外侧插入新列，这类列不会被纳入计算
• 如果有价格固定为1的稳定币，直接在价格匹配表中把对应币种的价格填为1即可，公式会自动计算

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