Google Sheets TB工作表按日期动态货币转换公式实现需求

一、'TB'工作表：按交易日期+货币匹配汇率计算 #

假设你的表格基础结构如下：

• 'Exchange Rates'表：A列=汇率日期，B列=货币代码，C列=对应汇率（如USD对CNY的汇率）
• 'Data'表：A列=交易日期，B列=交易货币，C列=交易金额

在'TB'工作表的目标计算单元格（比如D2）输入以下公式，自动匹配对应日期和货币的汇率并计算转换后金额：

=XLOOKUP(1, (Exchange Rates!$A:$A=Data!A2)*(Exchange Rates!$B:$B=Data!B2), Exchange Rates!$C:$C, "无匹配汇率") * Data!C2

公式说明：

• 通过(Exchange Rates!$A:$A=Data!A2)*(Exchange Rates!$B:$B=Data!B2)同时锁定交易日期和货币的匹配条件
• XLOOKUP提取符合条件的汇率，无匹配时显示"无匹配汇率"提示
• 最后乘以交易金额得到最终转换结果

如果汇率表仅记录工作日汇率，需要为周末/节假日交易匹配最近工作日汇率，改用以下INDEX+MATCH组合公式：

=INDEX(Exchange Rates!$C:$C, MATCH(1, (Exchange Rates!$B:$B=Data!B2)*(Exchange Rates!$A:$A<=Data!A2), 1)) * Data!C2

二、后续工作表：无日期筛选+空货币显示原交易信息 #

假设后续工作表的目标转换货币在单元格E2（可下拉选择），在结果单元格（比如F2）输入以下公式：

=IF(E2="", Data!B2&" "&TEXT(Data!C2, "#,##0.00"), Data!C2 * XLOOKUP(E2, Exchange Rates!$B:$B, Exchange Rates!$C:$C, "无匹配汇率"))

公式说明：

• IF(E2="", ...)判断目标货币是否为空：
  • 为空时，直接显示交易货币+格式化后的金额（示例："USD 100.00"）
  • 不为空时，跳过日期筛选，用XLOOKUP匹配目标货币的汇率（若要取最新汇率，可将'Exchange Rates'表按日期降序排列），再计算转换后金额

若仅需显示交易货币（不带金额），将公式中Data!B2&" "&TEXT(Data!C2, "#,##0.00")替换为Data!B2即可。

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