前段时间,利用Offset函数和if、index、match等函数嵌套实现了一个隔行查询的功能,觉得蛮有用的,现在的话分享出来给大家参考一下。废话不多说,直接上案例:
图1-输入母件编码后辅助列中的子件自动生成
现在我们就开始实现案例。首先,我们要做好准备工作。我给大家准备了几个数据源,是拆分自我实际工作中的一部分数据,已经经过改造除去对案例无用的数据,更方便大家的阅读,以及案例的实现。
1、BOM信息表
图2-子件信息表
2、母件信息表
图3-母件信息表
3、子件信息表
图4-子件信息表
4、生产任务表
图5-生产任务单
现在我们来实现我们案例中所需要实现的功能。
分析:
1、 从BOM表中我们可以看到一个母件会对应2个或者3个子件。如图:
图6-1个母件对应3个子件
图7-1个母件对应2个子件
2、现在看一下我们需求,我们要查询母件对应的多个子件编码,大家首先想到的是VlookUp,index和match组合,但是我们也会看到这个还是有局限,只能查找到第一个子件,而下面的两个或者三个就查询不到了。EXCEL中的Offset函数可以偏移到对应单元格区域。现在的话我们可以嵌套使用来实现我们的需求。
3、现在我们不使用vlookup,我们使用index和match函数嵌套offset。获取第一个子件的编码,图8 中的J6单元格的公式为 =INDEX(BOM信息!$D:$D,MATCH(生产订单!$A6,BOM信息!$A:$A,0))
图8
4、现在J7的话是获取第二个子件编码,现在可以利用Offset函数来嵌套外层,图8 中的J7单元格公式为:=OFFSET(INDEX(BOM信息!$D:$D,MATCH(生产订单!$A6,BOM信息!$A:$A,0)),1,0)/5、同样图8中J8的公式我们可以这样写:=OFFSET(INDEX(BOM信息!$D:$D,MATCH(生产订单!$A6,BOM信息!$A:$A,0)),2,0)但是这样写是有问题的。因为我们BOM信息中一个母件是可以对应2个子件或者是3个子件的。所以,J8这个公式我们还要进行一层判断,公式可以写成:
=IF(OFFSET(INDEX(BOM信息!$A:$A,MATCH(生产订单!$A6,BOM信息!$A:$A,0)),2,0)=$A6,OFFSET(INDEX(BOM信息!$D:$D,MATCH(生产订单!$A6,BOM信息!$A:$A,0)),2,0),"")
6、同理,K6,K7,K8,L6,L7,L8单元格中的公式我们也可以按照前面的步骤写出对应的公式。
最后,谢谢大家的观看,我是EK。如果这个小技巧能够帮助到你们,希望你们能点赞,关注,评论,你们不清楚的地方我会尽力为大家解答,谢谢大家的支持。我会给大家带来更多关于EXCEL的小技巧。
评论 (0)