多表多条件Excel求和公式需求：基于子地点规则汇总库存

问题背景 #

现有3张Excel表格：

• Table1（主库存表）：记录各产品在不同地点的库存数据，包含列：产品名称、地点、库存数量
• Table2（子地点规则表）：定义允许纳入统计的地点规则（如允许的地点列表或前缀规则）
• Table3（产品库存汇总表）：需基于前两张表，按规则统计各产品的合规库存总和

需求示例：

• Product4的C003地点未在规则中，汇总库存为0
• Product1仅统计合规地点A的5个库存，A005地点不纳入
• Product2的C004地点不合规，对应库存不计入总和

解决方案 #

根据Table2的规则类型，提供两种纯Excel公式实现方案：

方案1：Table2为允许的地点精确列表 #

如果Table2是精确记录所有可统计的地点（即只有出现在Table2中的地点才被纳入计算），在Table3的Total_Stock列对应单元格（如B2）输入以下公式：

=SUMPRODUCT((Table1[Product]=Table3[@Product])*(ISNUMBER(MATCH(Table1[Location], Table2[Allowed_Location], 0)))*Table1[Stock])

公式解释

• (Table1[Product]=Table3[@Product])：匹配当前要汇总的目标产品
• (ISNUMBER(MATCH(Table1[Location], Table2[Allowed_Location], 0)))：判断Table1中的地点是否存在于Table2的允许列表中（精确匹配）
• 两个条件同时满足时，乘以对应的库存数量，SUMPRODUCT自动对所有符合条件的库存求和

方案2：Table2为地点前缀规则（Excel 365及以上） #

如果Table2定义的是地点前缀规则（如允许所有以"A"开头的地点，但排除特定地点如A005），可使用FILTER函数简化逻辑：

=SUM(FILTER(Table1[Stock], (Table1[Product]=Table3[@Product])*(LEFT(Table1[Location],1)=Table2[@Allowed_Prefix])*(Table1[Location]<>"A005"), 0))

公式解释

• FILTER(Table1[Stock], ...)：筛选出符合条件的库存数据
  • (Table1[Product]=Table3[@Product])：匹配目标产品
  • (LEFT(Table1[Location],1)=Table2[@Allowed_Prefix])：匹配地点前缀规则
  • (Table1[Location]<>"A005")：排除特定不合规地点
• SUM(..., 0)：对筛选结果求和，无符合条件数据时返回0

示例验证 #

针对需求中的示例：

1. Product4的C003不在Table2的允许列表中，公式会排除该地点的库存，返回0
2. Product1的A在允许列表，A005不在，公式仅累加A对应的5个库存
3. Product2的C004不在允许列表，对应库存被排除，总和不计入该部分

内容的提问来源于stack exchange，提问作者Billy Faith Susanto