在Google Sheets中用ARRAYFORMULA结合汇率表实现多货币转换

核心需求概述 #

• 在Money工作表中，基于付款日期和Currency工作表的汇率数据，批量计算目标货币金额（支持数百行数据）
• 需处理三类汇率匹配场景：直接汇率、反向汇率、通过中间货币间接转换

关键公式实现（以D2单元格为例，可横向复制到E2、F2等） #

=ARRAYFORMULA(IF(A2:A="",,
  LET(
    base_currency, B2:B,
    amount, C2:C,
    target_currency, D1,
    payment_date, A2:A,
    // 获取直接汇率：匹配基础货币→目标货币，取生效日期≤付款日期的最新汇率
    direct_rate, XLOOKUP(1, (Currency!A:A=base_currency)*(Currency!B:B=target_currency)*(Currency!C:C<=payment_date), Currency!D:D, "", 0, 2),
    // 获取反向汇率：匹配目标货币→基础货币，取汇率倒数
    reverse_rate, IFERROR(1/XLOOKUP(1, (Currency!A:A=target_currency)*(Currency!B:B=base_currency)*(Currency!C:C<=payment_date), Currency!D:D, "", 0, 2)),
    // 通过USD作为中间货币间接转换（可按需替换为其他核心货币）
    usd_base_rate, XLOOKUP(1, (Currency!A:A=base_currency)*(Currency!B:B="USD")*(Currency!C:C<=payment_date), Currency!D:D, "", 0, 2),
    usd_target_rate, XLOOKUP(1, (Currency!A:A="USD")*(Currency!B:B=target_currency)*(Currency!C:C<=payment_date), Currency!D:D, "", 0, 2),
    indirect_rate, IFERROR(usd_base_rate*usd_target_rate),
    // 汇率匹配优先级：直接汇率>反向汇率>间接汇率
    final_rate, COALESCE(direct_rate, reverse_rate, indirect_rate),
    amount*final_rate
  )
))

公式逻辑说明 #

• LET函数：统一定义变量，大幅简化公式结构与可读性
• XLOOKUP参数：最后一个参数2指定从后往前查找，确保获取最新生效的汇率
• 空值过滤：IF(A2:A="",, ...)避免空行触发无效计算
• 错误兜底：IFERROR处理无匹配汇率的情况，COALESCE按优先级返回有效汇率

灵活调整建议 #

• 若需新增其他中间货币（如EUR），可复制USD转换的逻辑，新增对应变量后加入COALESCE参数列表
• 若Currency表的列位置变动，需同步修改公式中Currency!A:A这类单元格引用
• 可在COALESCE末尾添加"无可用汇率"作为兜底，替代默认的#N/A错误提示

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