Excel实现：查询与指定装配体共享零部件的其他装配体

核心逻辑 #

通过BOM表中「装配体-零部件」的关联关系，先定位目标装配体包含的所有零部件，再反向匹配使用这些零部件的其他装配体，去重后输出为列表。

公式实现（适配双版本） #

假设BOM表结构：

• A列：装配体编号（数据范围示例：$A$2:$A$100，可按需调整）
• B列：对应零部件编号（数据范围示例：$B$2:$B$100，可按需调整）
• D1单元格：输入待查询的装配体编号
• E列：输出共享零部件的其他装配体列表

基础查询公式（含去重） #

在E2单元格输入以下公式：

=IFERROR(INDEX($A$2:$A$100,SMALL(IF((COUNTIF($B$2:$B$100,$B$2:$B$100*($A$2:$A$100=$D$1))>0)*($A$2:$A$100<>$D$1)*(MATCH($A$2:$A$100,$A$2:$A$100,0)=ROW($A$2:$A$100)-ROW($A$2)+1),ROW($A$2:$A$100)-ROW($A$2)+1),ROW(A1))),"")

• Office 2013操作：输入公式后按Ctrl+Shift+Enter确认数组公式，再下拉公式至出现空值
• Office 365操作：直接回车，公式会自动溢出结果，无需下拉

基于AGGREGATE的替代公式 #

若偏好类似INDEX(AGGREGATE(...))的写法，可使用以下公式：

=IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,3,(ROW($A$2:$A$100)-ROW($A$2)+1)/((COUNTIF($B$2:$B$100,$B$2:$B$100*($A$2:$A$100=$D$1))>0)*($A$2:$A$100<>$D$1)*(MATCH($A$2:$A$100,$A$2:$A$100,0)=ROW($A$2:$A$100)-ROW($A$2)+1)),ROW(A1))),"")

• Office 2013操作：按Ctrl+Shift+Enter确认数组公式后下拉
• Office 365操作：直接回车即可

公式逻辑拆解 #

1. $A$2:$A$100=$D$1：定位目标装配体的所有行，提取对应零部件
2. COUNTIF(...)>0：匹配所有使用这些零部件的装配体行
3. $A$2:$A$100<>$D$1：排除目标装配体本身
4. MATCH(...) = ROW(...)：实现去重，仅保留每个装配体的首次出现
5. SMALL/AGGREGATE(15,3,...)：提取符合条件的行号，配合INDEX返回装配体编号
6. IFERROR：处理无匹配结果的情况，返回空值

注意事项 #

• 需根据实际BOM表的行数调整公式中的数据范围（如$A$2:$A$100改为$A$2:$A$500）
• 确保BOM表无空行、无格式错误，否则可能导致公式返回异常结果

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