Excel统计指定月份首次下单的唯一新买家数量问题

数据背景 #

• 文件名：Monthly progress (02-2024).xlsx
• 工作表：Order
• 关键字段：
  • B列：客户姓名
  • Q列：下单日期（格式dd/mm/yyyy，时间范围覆盖2022年6月30日至今）
  • S列：订单状态（可选值：Sold/Failed）
• 定位标记：A1单元格值为Current month: 29/02/2024，用于锁定目标统计月份（2024年2月）

需求核心 #

统计2024年2月首次下单的唯一新买家数量，需排除2024年2月之前已有下单记录的复购用户。

原公式问题分析 #

你提供的公式仅筛选了2024年2月29日当天的Sold订单并提取对应买家，但未判断该买家的首次下单时间是否在2024年2月，因此无法排除此前已下单的复购用户，最终仅统计了当月有订单的总买家数。

正确公式方案 #

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

=COUNTA(UNIQUE(FILTER('[Monthly progress (02-2024).xlsx]Order'!$B$3:$B$50000,
  '[Monthly progress (02-2024).xlsx]Order'!$S$3:$S$50000="Sold",
  EOMONTH('[Monthly progress (02-2024).xlsx]Order'!$Q$3:$Q$50000,0)=EOMONTH($A$1,0),
  COUNTIFS('[Monthly progress (02-2024).xlsx]Order'!$B$3:$B$50000,'[Monthly progress (02-2024).xlsx]Order'!$B$3:$B$50000,
           '[Monthly progress (02-2024).xlsx]Order'!$Q$3:$Q$50000,"<"&DATE(YEAR($A$1),MONTH($A$1),1))=0
)))

公式逻辑

1. 筛选有效订单：仅保留状态为Sold且下单日期在2024年2月的记录（用EOMONTH统一匹配月末日期，避免仅限定29日的误差）
2. 排除复购用户：通过COUNTIFS统计每个买家在2024年2月1日之前的订单数，仅保留统计结果为0的用户（即首次下单在2024年2月）
3. 统计唯一用户：用UNIQUE提取筛选后的唯一客户，再通过COUNTA统计数量

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

输入公式后需按 Ctrl+Shift+Enter 触发数组运算：

=SUM(IF(FREQUENCY(IF('[Monthly progress (02-2024).xlsx]Order'!$S$3:$S$50000="Sold",
  IF(EOMONTH('[Monthly progress (02-2024).xlsx]Order'!$Q$3:$Q$50000,0)=EOMONTH($A$1,0),
  IF(COUNTIFS('[Monthly progress (02-2024).xlsx]Order'!$B$3:$B$50000,'[Monthly progress (02-2024).xlsx]Order'!$B$3:$B$50000,
               '[Monthly progress (02-2024).xlsx]Order'!$Q$3:$Q$50000,"<"&DATE(YEAR($A$1),MONTH($A$1),1))=0,
  MATCH('[Monthly progress (02-2024).xlsx]Order'!$B$3:$B$50000,'[Monthly progress (02-2024).xlsx]Order'!$B$3:$B$50000,0))),
  ROW('[Monthly progress (02-2024).xlsx]Order'!$B$3:$B$50000)-ROW('[Monthly progress (02-2024).xlsx]Order'!$B$3)+1),1))

公式逻辑

通过FREQUENCY+MATCH组合实现唯一用户提取，嵌套IF完成订单状态、月份、首次下单的多重筛选，最终用SUM统计符合条件的用户数量，适配旧版Excel的数组运算规则。

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