如何无需转置表，直接基于Table A和B实现按国家汇总计划公里数

假设你的数据结构如下：

• TableA（Sheet1）：首列是车辆ID，后续列是各周次的行驶国家（多国家用-分隔）
• TableB（Sheet2）：结构与TableA完全一致，对应单元格为该车辆当周的计划公里数
• 汇总表（Sheet3）：A列为需要统计的国家列表，B列为对应国家的累计计划公里数

方案1：Excel 365/2021+ 动态数组版（推荐） #

在汇总表的B2单元格输入以下公式，下拉填充即可：

=SUM(
    LET(
        // 提取TableA中所有周次的国家数据（排除表头和车辆列）
        country_data, DROP(TableA[#All], 1, 1),
        // 提取TableB中所有周次的公里数数据（排除表头和车辆列）
        km_data, DROP(TableB[#All], 1, 1),
        // 拆分所有国家为一维数组，忽略空值
        split_countries, TOCOL(TEXTSPLIT(country_data, "-"), 2),
        // 计算每个国家分摊的公里数，转为一维数组
        allocated_km, TOCOL(IFERROR(km_data / (LEN(country_data) - LEN(SUBSTITUTE(country_data, "-", "")) + 1), 0), 2),
        // 按当前国家汇总分摊公里数
        SUMIF(split_countries, A2, allocated_km)
    )
)

公式解释： #

• LET函数：定义变量简化公式逻辑，避免重复计算
• DROP(...,1,1)：移除表的表头行和第一列（车辆ID列），只保留周次数据
• TEXTSPLIT(country_data, "-")：将每个单元格的多国家按-拆分，生成二维数组；TOCOL(...,2)将二维数组转为一维，同时过滤空值
• km_data / (LEN(...) - LEN(SUBSTITUTE(...)) +1)：计算单个国家的分摊公里数——如果单元格有n个国家，每个国家分得总公里数/n，无分隔符时n=1，直接取原公里数
• SUMIF：匹配当前国家，汇总所有对应的分摊公里数

方案2：兼容旧版Excel（无动态数组） #

在汇总表的B2单元格输入以下数组公式（输入后按Ctrl+Shift+Enter确认），下拉填充：

=SUM(
    IF(
        ISNUMBER(SEARCH(A2, TableA[#All])),
        TableB[#All] / (LEN(TableA[#All]) - LEN(SUBSTITUTE(TableA[#All], "-", "")) + 1),
        0
    )
)

公式解释： #

• SEARCH(A2, TableA[#All])：检查TableA的每个单元格是否包含当前统计的国家
• 若包含，则计算该单元格公里数的分摊值（每个国家分得总公里数/(国家数量)）
• SUM函数汇总所有符合条件的分摊值

注意事项： #

• 必须确保TableA和TableB的结构完全对齐：车辆顺序一致、周次列的数量和顺序完全相同，否则会导致公里数匹配错误
• 如果国家名称存在包含关系（如中国和中国香港），旧版方案的SEARCH会误匹配，此时建议改用FILTERXML拆分国家后再匹配，公式示例：

=SUM(
    IF(
        A2=FILTERXML("<t><s>"&SUBSTITUTE(TableA[#All], "-", "</s><s>")&"</s></t>", "//s"),
        TableB[#All]/(LEN(TableA[#All])-LEN(SUBSTITUTE(TableA[#All],"-",""))+1),
        0
    )
)

（同样需要按Ctrl+Shift+Enter作为数组公式输入）

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