装箱单|excel函数应用技巧：如何根据发货总装箱清单，自动生成分箱清单( 二 ) find|箱单|函数|应用技巧

在单元格A13中输入公式按Ctrl+Shift+Enter三键得到行号：
=IFERROR(INDEX(总装箱单!A$13:A$500,SMALL(IF(--RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))=总装箱单!$J$13:$J$500,ROW(总装箱单!$J$13:$J$500)-12),ROW(总装箱单!A1))),"")
向右拖曳到L列即可得到1号箱的清单。
当清单有多行时，将公式向下拖曳即可。如下图：

文章插图
公式看起来挺复杂，但其实就是一对多查询的万金油公式。学习更多技巧，请收藏关注部落窝教育excel图文教程。函数解析：
这里用了INDEX函数来提取行号。
★ 总装箱单!A$13:A$500：指定行号的提取区域
★ 提取的行号用下面的公式来指定：
SMALL(IF(--RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))=总装箱单!$J$13:$J$500,ROW(总装箱单!$J$13:$J$500)-12),ROW(总装箱单!A1))
在SHEET1中，通过CELL函数取得的完整文件名及路径为“"d:desk[模板 Packing list_1016-4 Canberra_20200605.xlsm]1"”
利用FIND函数找到“]”所在的位置（公式中绿色下划线部分），再用字符串的总长度（公式中红色下划线部分）减去“]”所在的位置数，就得到箱号数的长度。最后用RIGHT从提取到箱号值。因为提取到的是文本，所以用两个负运算（公式中蓝色下划线部分）转成数值。
利用IF函数逐一判断提取到的箱号数是否等于J列中的箱号数。如果工作表“总装箱单”中J列的某箱号等于提取的箱号数，则返回该箱号单元格位于$J$13:$J$500区域的行数（黑色下划线部分）。如果不相等，则返回FALSE。此处IF函数会得到一组由FALSE和行数组成的数组。
注意：这里的IF函数用法非常规用法。常规用法IF（条件,TRUE结果，FLASE结果），当前用法是IF（条件，TRUE结果），省略了第二个逗号和第三参数。这种省略用法，如果结果为FALSE，则返回值就是FALSE，而不是0或空值。
利用SMALL函数在IF函数返回的数组中，获取第几小的行数。ROW(总装箱单!A1)，表示获取第1小的行数；如果是ROW(总装箱单!A2)，则表示获取第2小的数据。
★ INDEX函数根据SMALL返回的行数，返回总装箱单!A$13:A$500中的对应值。
注意：因为指定的提取区域A$13:A$500只有1列，所以INDEX函数省略了第三参数，列号1。
如果没有看懂该万金油公式，请看这篇专门介绍此公式的文章《Excel万金油公式INDEX-SMALL-IF-ROW筛选函数公式解读》。
第三步：提取尺寸、总重、净重等标注信息
首先是总重、净重、尺寸和船号。
因为分箱单中已经有了这些数据，所以直接用“=”就可以解决问题了。如下图：

文章插图
然后是分箱编号的提取
分箱编号格式：箱号-总箱号。
简单，用连接符“&”把分箱单中的箱号数据、“-”符号和公式MAX(总装箱单!$J$13:$J$90)连接起来就可以解决问题啦！
公式= J13&"-"&MAX;(总装箱单!$J$13:$J$90)
如下图：

文章插图
第四步：添加一段确保自动更新的代码
截止到现在，所有的公式设置都完成了。但还有一个问题需要解决。由于CELL函数是易失性函数，当我们每次重新打开文件切换工作表时，需要手动按F9刷新才可以得到正确的结果。所以我们要给文件写一段代码。按Alt+F11打开VBA编辑器输入图中的代码。这样当我们每次切换工作表时，代码会自动运算一次，取代我们手动刷新。如下图：
我们的一号分箱单完成了！
这时候有小伙伴们要嚷了：
“如果有几十份分箱单，是不是每一份都要如此重复录入公式和代码啊！？”
你完全不用那样做！
由于每一份分箱单的格式、公式及代码都是相同的。只需要做一次就好了（例如，分箱单1）。你只需要按住CTRL键并拖动当前工作表进行复制，按规则修改工作表名称为箱号数字，就可以得到其他分箱单。
亲测，复制后的公式和代码完全有效！如下：

文章插图
好喽，剩下的时间我要去摸鱼啦！学习更多技巧，请收藏关注部落窝教育excel图文教程。
＊＊＊＊部落窝教育-excel一对多查询公式＊＊＊＊
原创：Excel应用之家/部落窝教育（未经同意，请勿转载）
更多教程：部落窝教育
做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！
相关推荐：
一对多万金油查询公式：Excel万金油公式INDEX-SMALL-IF-ROW筛选函数公式解读