Excel中SUMPRODUCT实现动态汇率转换的交易合并需求求助

前提说明 #

要实现需求，需补充一张主机-币种映射表（字段：HOST、HOST_CCY），用于确定每个主机的默认币种——这是判断转入主机是否需要转换币种的核心依据。同时保留原有的交易数据表、USD基准汇率表（字段：CCY、FX_RATE）。

方案1：Power Query 批量处理（推荐） #

适合一次性或周期性批量处理数据，逻辑清晰可追溯：

1. 导入数据到Power Query
   依次将交易数据表、主机-币种映射表、汇率表导入Power Query（Excel菜单：数据 > 自表格/区域）。

2. 拆分交易为转出/转入两条记录
   在交易数据表的Power Query编辑器中，添加自定义列，生成包含转出、转入行为的记录列表：

   = {
       // 转出记录：扣减原金额，币种为原交易CCY
       [TRANS DATE=[TRANS DATE], HOST=[HOST FROM], HOST_CCY=[CCY], AMT_MOVE=-[AMT]],
       // 转入记录：先占位，后续关联主机币种和汇率
       [TRANS DATE=[TRANS DATE], HOST=[HOST TO], HOST_CCY=null, AMT_MOVE=[AMT]]
     }
   

   点击自定义列的展开箭头，将列表拆分为独立行。

3. 关联主机币种与汇率

   • 合并主机-币种映射表，匹配转入记录的HOST字段，填充HOST_CCY；
   • 分别合并汇率表，获取原交易CCY的汇率（命名为FX_FROM）和主机HOST_CCY的汇率（命名为FX_TO）。

4. 计算转入记录的转换金额
   添加自定义列修正转入记录的AMT_MOVE：

   = if [CCY] = [HOST_CCY] then [AMT_MOVE] else [AMT_MOVE] * [FX_FROM] / [FX_TO]
   

   逻辑：原币种与主机币种相同时直接用原金额；不同时通过USD基准转换：原金额 × 原币种兑USD汇率 ÷ 主机币种兑USD汇率

5. 合并维度并求和
   按TRANS DATE、HOST、HOST_CCY分组，对AMT_MOVE字段求和，最终加载回Excel工作表。

方案2：Excel动态数组公式（实时计算） #

适合Excel 365/2021版本，支持数据实时更新：

步骤1：生成资金变动表的基础维度 #

在空白单元格输入公式，生成所有需统计的「日期+主机+主机币种」唯一组合：

=UNIQUE(VSTACK(
    CHOOSECOLS(Transactions, "TRANS DATE", "HOST FROM", XLOOKUP(Transactions[HOST FROM], HostCCY[HOST], HostCCY[HOST_CCY])),
    CHOOSECOLS(Transactions, "TRANS DATE", "HOST TO", XLOOKUP(Transactions[HOST TO], HostCCY[HOST], HostCCY[HOST_CCY]))
))

将生成的区域设为结构化表，字段命名为TRANS DATE、HOST、HOST_CCY。

步骤2：计算变动金额（AMT MOVE） #

在AMT MOVE列输入以下数组公式，自动匹配每个维度的变动金额：

=SUM(
    // 转出部分：匹配维度后扣减金额
    XLOOKUP(
        [@[TRANS DATE]]&[@HOST]&[@HOST_CCY],
        Transactions[TRANS DATE]&Transactions[HOST FROM]&XLOOKUP(Transactions[HOST FROM], HostCCY[HOST], HostCCY[HOST_CCY]),
        -Transactions[AMT],
        0
    ),
    // 转入部分：分币种匹配后计算金额
    SUMIFS(
        IF(
            XLOOKUP(Transactions[HOST TO], HostCCY[HOST], HostCCY[HOST_CCY])=Transactions[CCY],
            Transactions[AMT],
            Transactions[AMT] * XLOOKUP(Transactions[CCY], FXRates[CCY], FXRates[FX_RATE]) / XLOOKUP(XLOOKUP(Transactions[HOST TO], HostCCY[HOST], HostCCY[HOST_CCY]), FXRates[CCY], FXRates[FX_RATE])
        ),
        Transactions[TRANS DATE], [@[TRANS DATE]],
        Transactions[HOST TO], [@HOST],
        XLOOKUP(Transactions[HOST TO], HostCCY[HOST], HostCCY[HOST_CCY]), [@HOST_CCY]
    )
)

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