基于两列值筛选合并行并求和的技术方案问询

可以看到，Team B同时负责English和语种工作，另有独立的Team Spanish。需求是将Team Spanish与Team B的Spanish条目合并，显示为一行并求和人数（总计7人）。

我之前的临时方案是新增辅助列，将Team和Language列合并后求和，伪代码如下：

SUMIF(people, aux_col IN ('team B - Spanish', 'Team Spanish - Spanish')) AS new_people_count  

现寻求更优的技术实现方案。

优化实现方案 #

1. SQL 实现（无需辅助列）

直接通过分组和条件判断合并目标条目，无需额外辅助列：

-- 仅获取合并后的目标行
SELECT
    'Team Spanish' AS Team,
    'Spanish' AS Language,
    SUM(People) AS People
FROM your_table_name
WHERE (Team = 'Team B' AND Language = 'Spanish') OR Team = 'Team Spanish';

如果需要保留所有其他行的数据，同时合并目标条目：

SELECT
    CASE
        WHEN Team = 'Team B' AND Language = 'Spanish' THEN 'Team Spanish'
        ELSE Team
    END AS Team,
    Language,
    SUM(People) AS People
FROM your_table_name
GROUP BY
    CASE
        WHEN Team = 'Team B' AND Language = 'Spanish' THEN 'Team Spanish'
        ELSE Team
    END,
    Language;

2. Excel 实现（无需辅助列）

直接用SUMIFS组合条件求和，无需新增辅助列：

=SUMIFS(C:C,A:A,"Team B",B:B,"Spanish")+SUMIFS(C:C,A:A,"Team Spanish",B:B,"Spanish")

将计算结果对应到Team Spanish和Spanish的行即可。

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