Excel技术咨询：能否通过单元格值定义查找数组及多物料库存查找方案

一、直接用复合匹配公式提取库存 #

针对你现有宽表（行=物料、列=周）且存在重复物料的情况，用以下公式直接匹配查询：

1. 兼容旧版Excel的数组公式 #

=INDEX($A$5:$J$100,MATCH(1,($A$5:$A$100=查询物料单元格)*($A$5:$J$5=查询周单元格),0),MATCH(查询周单元格,$A$5:$J$5,0))

• 输入后按 Ctrl+Shift+Enter 执行（新版Excel可直接回车）
• 逻辑：通过($A$5:$A$100=查询物料)*($A$5:$J$5=查询周)生成匹配数组，找到同时符合物料和周的行号，再结合你已有的列号MATCH结果，用INDEX提取对应库存

2. 新版Excel（365/2021）简化公式 #

=XLOOKUP(1,($A$5:$A$100=查询物料单元格)*($A$5:$J$5=查询周单元格),$A$5:$J$100)

• 无需数组操作，直接回车即可自动匹配符合条件的行，返回对应周的库存值

二、重构数据结构（更可持续的方案） #

你当前用宏插入列导致偏移的问题，根源是宽表结构（列存周）的局限性，建议改成长表格式：

• 固定三列：物料名称/编号、周（格式统一为YYYY-WXX，比如2024-W23）、库存数量
• 新增周数据时直接追加行，完全不会出现偏移
• 查找更简单，比如用组合匹配：

  =XLOOKUP(查询物料&查询周,$A:$A&$B:$B,$C:$C)
  

• 还能直接用数据透视表快速生成每周库存汇总、对比不同物料的库存变化

三、弃用宏的列管理方案 #

如果暂时不想改结构，绝对不要用宏复制插入列：

• 新增周时直接在现有列右侧添加新列，表头统一标注周标识
• 用上述复合匹配公式查找，全程不需要宏操作，彻底避免数据偏移

内容的提问来源于stack exchange，提问作者Daan Plass