Excel中统计产品销量大于采购量的月份数需求求助

表格结构说明 #

• 表格1（建议命名为Table1）：A列=产品名称，B列=「say」（待填充的计数列）
• 表格2（建议命名为Table2）：A列=日期，B列=产品名称，C列=交易类型（satis=售出/alis=采购），D列=交易数量

方案1：Excel 365/2021（支持动态数组） #

在Table1的B2单元格（对应第一个产品）输入以下公式，下拉自动填充所有产品：

=COUNT(UNIQUE(FILTER(TEXT(Table2[日期],"yyyy-mm"),(Table2[产品]=Table1[@产品])*(SUMIFS(Table2[交易数量],Table2[产品],Table1[@产品],Table2[交易类型],"satis",Table2[日期],">="&EOMONTH(Table2[日期],-1)+1,Table2[日期],"<="&EOMONTH(Table2[日期],0))>SUMIFS(Table2[交易数量],Table2[产品],Table1[@产品],Table2[交易类型],"alis",Table2[日期],">="&EOMONTH(Table2[日期],-1)+1,Table2[日期],"<="&EOMONTH(Table2[日期],0))))))

公式逻辑

1. TEXT(Table2[日期],"yyyy-mm")：将日期转换为「年-月」格式，统一月份标识
2. 两个SUMIFS分别计算当前产品当月的总销量（satis）和总采购量（alis）
3. 筛选出「产品匹配」且「当月销量>采购量」的所有月份文本
4. UNIQUE()去重得到所有符合条件的唯一月份
5. COUNT()统计这些唯一月份的数量

方案2：旧版Excel（无动态数组支持） #

在Table1的B2单元格输入以下公式，输入完成后按Ctrl+Shift+Enter触发数组公式，再下拉填充：

=SUMPRODUCT(--(FREQUENCY(TEXT(IF((Table2[产品]=Table1[@产品])*(SUMIFS(Table2[交易数量],Table2[产品],Table1[@产品],Table2[交易类型],"satis",Table2[日期],">="&EOMONTH(Table2[日期],-1)+1,Table2[日期],"<="&EOMONTH(Table2[日期],0))>SUMIFS(Table2[交易数量],Table2[产品],Table1[@产品],Table2[交易类型],"alis",Table2[日期],">="&EOMONTH(Table2[日期],-1)+1,Table2[日期],"<="&EOMONTH(Table2[日期],0))),Table2[日期],""),TEXT(IF((Table2[产品]=Table1[@产品])*(SUMIFS(Table2[交易数量],Table2[产品],Table1[@产品],Table2[交易类型],"satis",Table2[日期],">="&EOMONTH(Table2[日期],-1)+1,Table2[日期],"<="&EOMONTH(Table2[日期],0))>SUMIFS(Table2[交易数量],Table2[产品],Table1[@产品],Table2[交易类型],"alis",Table2[日期],">="&EOMONTH(Table2[日期],-1)+1,Table2[日期],"<="&EOMONTH(Table2[日期],0))),Table2[日期],""))>0))

公式逻辑

1. IF()筛选出「产品匹配」且「当月销量>采购量」的日期，转换为「年-月」格式
2. FREQUENCY()统计每个唯一月份的出现次数，非零值代表该月份符合条件
3. --将逻辑值转换为1/0，SUMPRODUCT()求和得到符合条件的月份总数

注意事项 #

• 确保两个表格的产品名称完全一致（无空格、大小写差异），否则匹配会失效
• 若日期列为文本格式，需先转换为Excel可识别的日期格式再使用公式
• 给表格命名（选中表格→右键→重命名）可让公式更清晰，避免单元格引用混乱

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