前段时间,利用Offset函数和if、index、match等函数嵌套实现了一个隔行查询的功能,觉得蛮有用的,现在的话分享出来给大家参考一下。废话不多说,直接上案例:

Excel中offset与if、index、match嵌套实现隔行查询-趣帮office教程网

图1-输入母件编码后辅助列中的子件自动生成

现在我们就开始实现案例。首先,我们要做好准备工作。我给大家准备了几个数据源,是拆分自我实际工作中的一部分数据,已经经过改造除去对案例无用的数据,更方便大家的阅读,以及案例的实现。

1、BOM信息表

Excel中offset与if、index、match嵌套实现隔行查询-趣帮office教程网

图2-子件信息表

2、母件信息表

Excel中offset与if、index、match嵌套实现隔行查询-趣帮office教程网

图3-母件信息表

3、子件信息表

Excel中offset与if、index、match嵌套实现隔行查询-趣帮office教程网

图4-子件信息表

4、生产任务表

Excel中offset与if、index、match嵌套实现隔行查询-趣帮office教程网

图5-生产任务单

现在我们来实现我们案例中所需要实现的功能。

分析:

1、 从BOM表中我们可以看到一个母件会对应2个或者3个子件。如图:

Excel中offset与if、index、match嵌套实现隔行查询-趣帮office教程网

图6-1个母件对应3个子件

Excel中offset与if、index、match嵌套实现隔行查询-趣帮office教程网

图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))

Excel中offset与if、index、match嵌套实现隔行查询-趣帮office教程网

图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的小技巧。