如何用LET/HSTACK获取仅匹配单条件且排除另一条件的唯一值

需求说明 #

从Firm___Released_Job_Mats表格中，筛选仅在作业jorb1中使用、未在作业jorb2中使用的物料，同时需满足工序编号oper_num在20-27区间（即oper_num>19且oper_num<28），最终返回物料编号及对应的工序编号。

原共享物料查询公式（可正常运行） #

以下公式用于查询jorb1和jorb2的共享物料：

=LET(
jorb1,JORB1,
jorb2,JORB2,
jobs,Firm___Released_Job_Mats[Job-Suff],
opernum,Firm___Released_Job_Mats[oper_num],
materials,Firm___Released_Job_Mats[item],
filt,FILTER(materials,jorb1=jobs),
unsharedmats,UNIQUE(FILTER(materials,(ISNUMBER(MATCH(materials,filt,0)))*(jobs=jorb2)*(opernum>19)*(opernum<28))),
unsharedoper,BYROW(unsharedmats,LAMBDA(a,INDEX(Firm___Released_Job_Mats[oper_num],MATCH(1,((a=Firm___Released_Job_Mats[item])*(Firm___Released_Job_Mats[Job-Suff]=jorb1)),0)))),
HSTACK(unsharedmats,unsharedoper))

修改公式后不符合预期的问题 #

将上述公式中jobs=jorb2改为jobs<>jorb2，试图筛选jorb1独有的物料，但结果仍包含共享物料，无法满足需求。

辅助列实现方案（可行） #

通过三个辅助列可实现目标需求：

• 列Y：提取jorb1对应的物料（第8列为[item]字段）

  =CHOOSECOLS(FILTER(Firm___Released_Job_Mats,JORB1=Firm___Released_Job_Mats[Job-Suff],""),8)
  

• 列Z：提取jorb2对应的物料

  =CHOOSECOLS(FILTER(Firm___Released_Job_Mats,JORB2=Firm___Released_Job_Mats[Job-Suff],""),8)
  

• 列AA：筛选仅在jorb1存在、jorb2不存在的物料（数据从第6行开始）

  =FILTER(Z6#,NOT(ISNUMBER(MATCH(Z6#,Y6#,0))))
  

整合为LET函数时出现#VALUE!错误 #

尝试将辅助列逻辑整合到单个LET函数中，公式如下，但返回#VALUE!错误：

=LET(
jorb1,JORB1,
jorb2,JORB2,
jobs,Firm___Released_Job_Mats[Job-Suff],
opernum,Firm___Released_Job_Mats[oper_num],
materials,Firm___Released_Job_Mats[item],
filt1,CHOOSECOLS(FILTER(Firm___Released_Job_Mats,jorb1=jobs,""),8),
filt2,CHOOSECOLS(FILTER(Firm___Released_Job_Mats,jorb2=jobs,""),8),
unsharedmats,UNIQUE(FILTER(filt1,NOT(ISNUMBER(MATCH(filt1,filt2,0)))*(opernum>19)*(opernum<28))),
unsharedoper,BYROW(unsharedmats,LAMBDA(a,INDEX(Firm___Released_Job_Mats[oper_num],MATCH(1,((a=Firm___Released_Job_Mats[item])*(Firm___Released_Job_Mats[Job-Suff]=jorb1)),0)))),
HSTACK(unsharedmats,unsharedoper))

错误原因及解决方法 #

错误原因 #

filt1是仅筛选jorb1后的物料列表，而opernum是整个表格的工序编号列，两者长度不一致，在FILTER(filt1, ...*(opernum>19)*(opernum<28))中，逻辑判断数组长度不匹配，导致#VALUE!错误。

修正后的公式 #

调整逻辑，先筛选jorb1且工序编号符合要求的记录，再排除在jorb2中存在的物料：

=LET(
jorb1,JORB1,
jorb2,JORB2,
jobs,Firm___Released_Job_Mats[Job-Suff],
opernum,Firm___Released_Job_Mats[oper_num],
materials,Firm___Released_Job_Mats[item],
// 筛选jorb1中工序符合要求的完整记录
jorb1_valid,FILTER(Firm___Released_Job_Mats,(jobs=jorb1)*(opernum>19)*(opernum<28)),
// 提取对应物料和工序编号
jorb1_mats,CHOOSECOLS(jorb1_valid,8),
jorb1_ops,CHOOSECOLS(jorb1_valid,2), // 若oper_num不是第2列，需调整列号
// 获取jorb2的所有物料（去重）
jorb2_mats,UNIQUE(FILTER(materials,jobs=jorb2)),
// 筛选jorb1中不在jorb2的物料
unique_mats,UNIQUE(FILTER(jorb1_mats,NOT(ISNUMBER(MATCH(jorb1_mats,jorb2_mats,0))))),
// 匹配对应的工序编号
unique_ops,BYROW(unique_mats,LAMBDA(x,XLOOKUP(x,jorb1_mats,jorb1_ops))),
// 合并结果
HSTACK(unique_mats,unique_ops)
)

说明 #

1. 先通过jorb1_valid筛选出jorb1中工序符合条件的完整记录，确保物料和工序编号长度一致、一一对应。
2. 单独提取jorb2的所有物料并去重，用于后续排除判断。
3. 使用XLOOKUP替代INDEX+MATCH，逻辑更简洁，同时避免数组长度不匹配问题。
4. 若oper_num不是表格第2列，需调整CHOOSECOLS(jorb1_valid,2)中的列号为实际列位置。

内容的提问来源于stack exchange，提问作者Kyle Cranfill