发货一般需要提供装箱单，包括总装箱单（总清单）和每个箱子的分箱单。如何快速通过总装箱单生成分箱单？这里提供一种函数方法，可以实现半自动。除此外，文中企业严谨的文件命名规则值得借鉴学习。

做过销售或者物流行业的小伙伴们都知道，给客户发货时我们都要在包装箱外侧粘贴唛头及装箱单以便客户核对货物。

对于装箱单而言，每个客户的要求不尽相同，但总体上来讲，都是要一份总清单及各个箱子的分清单。总清单用来让客户签收，分清单粘贴在各个对应的包装箱外方便客户核对货物。

下面是某著名公司的装箱单，包含了一份总清单及十二份分箱单（箱号列中的数字代表箱子的箱号）。总清单和分箱单的格式都是一样的，并且图中涂黄色的区域都是通用的信息；区别在于总清单是一份完整的发货清单，而分箱单则是对应的每个包装箱的装货清单。

图一：总清单

图二：1号箱分箱单

大家注意看一下，总清单的最大箱号是12，也就是说，我们要复制粘贴12次相关的数据才能完成整份箱单工作。

如果说还有更多的包装箱，那么我们复制粘贴的工作量还会增加。

这显然不是我们想要的结果！

俗话说，“懒”是社会进步的源动力。如果，当我们完成总清单中输入后，EXCEL能够按照箱号自动为每个箱子生成分箱单，则可以大大地提高我们的工作效率，让我们可以按时下班！

让我们一起来看看如何操作吧！

在获取分箱单前的重要细节

为了方便文件管理和使用，装箱单文件命名有统一的规范。譬如案例公司的装箱单文件名就由3部分组成，每部分用“_”符合进行连接：

文件中总清单和分箱单的出货时间、船号都是通过函数获取文件名中的对应信息，以确保文件名和内容的一致性。

总清单的出货时间：

=TEXT(MID(CELL("filename"),FIND(".",CELL("filename"))-8,8),"0000-00-00")

函数解析

★CELL(“Filename”):用于获取文件的路径和名称。本文中装箱单文件的路径和名称是d:desk[模板 Packing list_1016-4 Canberra_20200605.xlsm

★FIND(".",CELL("filename"))-8:利用FIND函数查找“.”在“d:desk[模板 Packing list_1016-4 Canberra_20200605.xlsm]总装箱单”这个字符串中的位置，结果是50。再用50减去8（日期一共是8位）就得到日期第一个字符“2”的起始位置42。

★ 利用MID函数从文件路径和名称的字符串中第42位起提取长度为8的字符串即可得到日期信息。

★ 最后利用TEXT函数把日期信息“20200605”转换为标准的日期格式“2020-06-05”。

总清单上的船号

=MID(CELL("filename"),FIND("_",CELL("filename"))+1,FIND("_",CELL("filename"),FIND("_",CELL("filename"))+1)-FIND("_",CELL("filename"))-1)

函数解析

船号位于两个“_”之间，因此，第一个“_”的位置加1就是船号的第一个字符位置，也就是用MID函数开始提取船号的位置；第二个“_”的位置减去1等于船号最后一个字符的位置，再减去第一个第一个“_”的位置，就得到整个船号的字符长度。

★ FIND("_",CELL("filename"))：划线部分是查找第一个“_”的位置

★ FIND("_",CELL("filename"))+1：得到开始提取船号的位置。

★ FIND("_",CELL("filename"),FIND("_",CELL("filename"))+1)：从开始提取船号的位置（划线部分）开始查找“_”，也就是查找第二个“_”的位置。

获取1号箱分箱单

第一步：创建表格

新建一个工作表。我们需要用箱号数作为工作表的名称，因此工作表重命名为“1”。

然后将总清单上的表头和出货时间、船号等复制过来粘贴到工作表1中。

最后添加上下方的标注信息。

第二步：提取箱单内容

需要提取从行号到重量的所有清单信息。

在单元格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号箱的清单。

当清单有多行时，将公式向下拖曳即可。

图三

公式看起来挺复杂，但其实就是一对多查询的万金油公式。

函数解析

这里用了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键并拖动当前工作表进行复制，按规则修改工作表名称为箱号数字，就可以得到其他分箱单。

亲测，复制后的公式和代码完全有效！

如下：

好喽，剩下的时间我要去摸鱼啦！

本文配套的练习课件请加入QQ群：264539405下载。

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

扫下方二维码关注公众号，可随时随地学习Excel：

相关推荐：

一对多万金油查询公式：Excel万金油公式INDEX-SMALL-IF-ROW筛选函数公式解读

INDEX函数基本用法：INDEX:函数中的精确制导导弹，最强大的瘸子

IF函数用法：IF函数：剥洋葱

工作表的拆分和合并：别再为拆分、合并工作表闹心啦！最实用的7种方法，分分钟搞定它！（拆分篇）

版权申明：

本文为部落窝教育独家授权作品。作者Excel应用之家。如需转载请联系部落窝教育。