基于双关联条件筛选销售单缺失产品的Excel公式需求
2026-6-11
需求：计算指定销售单的缺失产品
数据源与手动表结构
「Sales DB」工作表（数据源）
| 产品 | 客户 | 销售ID | 日期 |
|---|---|---|---|
| 苹果 | Client-1 | Sale-1 | Date-1 |
| 橙子 | Client-1 | Sale-1 | Date-1 |
| 葡萄干 | Client-1 | Sale-1 | Date-1 |
| 橙子 | Client-2 | Sale-2 | Date-2 |
| 猕猴桃 | Client-2 | Sale-2 | Date-2 |
| 葡萄干 | Client-1 | Sale-3 | Date-3 |
引用范围：
- 产品：
'Sales DB'!$C$2:$C - 客户：
'Sales DB'!$D$2:$D - 销售ID：
'Sales DB'!$A$2:$A - 日期：
'Sales DB'!$I$2:$I
手动录入工作表
用户输入产品和客户后，下拉选择对应销售单（含日期），结构如下：
| 产品 | 客户 | 销售ID（日期） |
|---|---|---|
| 苹果 | Client-1 | Sale-1 (date-1) |
| 橙子 | Client-2 | Sale-2 (date-2) |
| 葡萄干 | Client-1 | Sale-3 (date-3) |
| 橙子 | Client-1 | Sale-1 (date-1) |
引用范围：
- 产品：
$B$2:$B - 客户：
$C$2:$C - 销售ID（日期）：
$D$2:$D
预期结果
新增「缺失产品」列，列出所选销售单中未在手动表录入的产品：
| 产品 | 客户 | 销售ID（日期） | 缺失产品 |
|---|---|---|---|
| 苹果 | Client-1 | Sale-1 (date-1) | 葡萄干 |
| 橙子 | Client-2 | Sale-2 (date-2) | 猕猴桃 |
| 葡萄干 | Client-1 | Sale-3 (date-3) | 无缺失产品 |
| 橙子 | Client-1 | Sale-1 (date-1) | 葡萄干 |
结果说明
- 第1、4行返回「葡萄干」：Client-1的Sale-1包含3种产品，手动表仅录入苹果、橙子，第3行的葡萄干属于Sale-3，不计入
- 第2行返回「猕猴桃」：Client-2的Sale-2包含2种产品，手动表仅录入橙子
- 第3行返回「无缺失产品」：该销售单仅含葡萄干且已录入
解决方案公式
在「缺失产品」列的第一个单元格（如E2）输入以下公式，下拉填充即可：
=LET( current_sale, LEFT($D2, FIND(" ", $D2)-1), all_products, UNIQUE(FILTER('Sales DB'!$C$2:$C, 'Sales DB'!$A$2:$A=current_sale)), entered_products, UNIQUE(FILTER($B$2:$B, $D$2:$D=$D2)), missing, FILTER(all_products, ISNA(MATCH(all_products, entered_products, 0))), IFERROR(TEXTJOIN(", ", TRUE, missing), "无缺失产品") )
公式拆解
- current_sale：提取当前行的纯销售ID（去掉日期后缀）
- all_products：从数据源筛选出该销售ID对应的所有唯一产品
- entered_products：从手动表筛选出同一销售ID下已录入的唯一产品
- missing：通过
MATCH匹配两个产品列表，筛选出未录入的缺失产品 - IFERROR+TEXTJOIN：将缺失产品用逗号分隔展示，无缺失时返回「无缺失产品」
内容的提问来源于stack exchange，提问作者Orphal
相关产品推荐
相关产品推荐
