为FILTER和MAXIFS添加多条件 筛选无近期订单的账户
2026-8-17
问题：筛选Mark负责的超60天无订单账户的公式错误分析与解决
我需要一个能返回无近期订单账户的公式，此前的需求已经解决，但现在要添加条件，只返回员工Mark负责的相关结果，多次尝试都失败了。
我的尝试公式：
第一个：
=FILTER(UNIQUE(OrderAmounts[[Account ]]),Today()-MAXIFS(OrderAmounts[Invoice Date],OrderAmounts[[Account ]],UNIQUE(OrderAmounts[[Account ]]),OrderAmounts[Name]="Mark")>60,"Oops")
第二个：
=FILTER(UNIQUE(OrderAmounts[[Account ]]),Today()-MAXIFS(OrderAmounts[Invoice Date],OrderAmounts[[Account ]],UNIQUE(OrderAmounts[[Account ]]))>60*(UNIQUE(OrderAmounts[[Name ]])="Mark"),"Oops")
相关数据表格：
| 账户 | 发票日期 | 员工姓名 |
|---|---|---|
| ACC1118 | 1/7/21 | Mark |
| ACC1118 | 3/30/21 | Mark |
| ACC1118 | 5/13/21 | Mark |
| ACC1118 | 6/10/21 | Mark |
| ACC1118 | 6/17/21 | Mark |
| ACC1118 | 6/18/21 | Mark |
| ACC1118 | 6/22/21 | Mark |
| ACC1118 | 6/29/21 | Mark |
| ACC1118 | 7/9/21 | Mark |
| ACC1118 | 7/22/21 | Mark |
| ACC1118 | 8/27/21 | Mark |
| ACC1118 | 9/17/21 | Mark |
| ACC1118 | 9/21/21 | Mark |
| ACC1118 | 10/26/21 | Mark |
| ACC1118 | 11/12/21 | Mark |
| ACC1118 | 11/30/21 | Mark |
| ACC1118 | 1/27/22 | Mark |
| ACC1118 | 2/8/22 | Mark |
| ACC1118 | 2/8/22 | Mark |
| ACC1118 | 3/8/22 | Mark |
| ACC1118 | 3/22/22 | Mark |
| ACC1118 | 3/31/22 | Mark |
| ACC1118 | 8/19/22 | Mark |
| ACC4247 | 3/31/21 | Jen |
| ACC4247 | 4/29/21 | Jen |
| ACC4247 | 4/30/21 | Jen |
| ACC4247 | 5/12/21 | Jen |
| ACC4247 | 5/26/21 | Jen |
| ACC4247 | 6/9/21 | Jen |
| ACC4247 | 9/15/21 | Jen |
| ACC4628 | 6/9/22 | Dave |
| ACC4628 | 6/24/22 | Dave |
| ACC4628 | 7/14/22 | Dave |
| ACC4628 | 7/28/22 | Dave |
| ACC4628 | 7/29/22 | Dave |
| ACC1129 | 4/1/22 | Mark |
| ACC1129 | 4/1/22 | Mark |
| ACC1129 | 4/15/22 | Mark |
| ACC1129 | 5/17/22 | Mark |
| ACC4246 | 3/31/21 | Jen |
| ACC4473 | 9/29/21 | Mark |
| ACC1140 | 5/26/22 | Dave |
| ACC1140 | 6/2/22 | Dave |
| ACC1140 | 6/16/22 | Dave |
| ACC1140 | 6/30/22 | Dave |
| ACC1140 | 7/7/22 | Dave |
| ACC1140 | 8/2/22 | Dave |
| ACC1140 | 8/4/22 | Dave |
| ACC1140 | 8/11/22 | Dave |
| ACC1140 | 8/16/22 | Dave |
| ACC1140 | 8/19/22 | Dave |
| ACC1140 | 8/19/22 | Dave |
| ACC1140 | 8/25/22 | Dave |
| ACC4162 | 9/7/21 | Jen |
| ACC4162 | 9/22/21 | Jen |
| ACC4162 | 9/29/21 | Jen |
| ACC4162 | 10/6/21 | Jen |
| ACC4162 | 11/12/21 | Jen |
| ACC4162 | 11/19/21 | Jen |
| ACC4162 | 12/2/21 | Jen |
| ACC4162 | 1/14/22 | Jen |
| ACC4162 | 2/25/22 | Jen |
| ACC4162 | 3/3/22 | Jen |
| ACC4162 | 3/31/22 | Jen |
| ACC4162 | 4/4/22 | Jen |
| ACC4162 | 6/6/22 | Jen |
| ACC4162 | 5/16/22 | Jen |
错误原因分析
- 第一个公式：在
MAXIFS中加入OrderAmounts[Name]="Mark"条件后，对于没有Mark相关记录的账户，MAXIFS会返回错误值，导致FILTER的判断条件失效，无法完成筛选。 - 第二个公式：
60*(UNIQUE(OrderAmounts[[Name ]])="Mark")的逻辑错误，一是数组长度和唯一账户数组不匹配，二是这种乘法无法正确关联账户与对应员工，导致筛选条件混乱。
正确公式
方法1：分步处理（逻辑清晰）
=LET( MarkAccounts, UNIQUE(FILTER(OrderAmounts[Account], OrderAmounts[员工姓名]="Mark")), LastOrderDates, MAXIFS(OrderAmounts[Invoice Date], OrderAmounts[Account], MarkAccounts), FILTER(MarkAccounts, TODAY()-LastOrderDates>60, "Oops") )
用LET函数拆分步骤：先提取所有Mark负责的唯一账户，再计算每个账户的最晚订单日期，最后筛选出超60天无订单的账户，避免错误值干扰。
方法2：简化直接筛选（需确保Mark账户有记录）
=FILTER(UNIQUE(OrderAmounts[Account]),TODAY()-MAXIFS(OrderAmounts[Invoice Date],OrderAmounts[Account],UNIQUE(OrderAmounts[Account]),OrderAmounts[员工姓名]="Mark")>60,"Oops")
如果存在Mark无记录的账户，可加入IFERROR处理错误值：
=FILTER(UNIQUE(OrderAmounts[Account]),TODAY()-IFERROR(MAXIFS(OrderAmounts[Invoice Date],OrderAmounts[Account],UNIQUE(OrderAmounts[Account]),OrderAmounts[员工姓名]="Mark"),0)>60,"Oops")
内容的提问来源于stack exchange，提问作者abrokentenor
相关产品推荐
相关产品推荐
