發(fā)貨一般需要提供裝箱單，包括總裝箱單（總清單）和每個箱子的分箱單。如何快速通過總裝箱單生成分箱單？這里提供一種函數(shù)方法，可以實現(xiàn)半自動。除此外，文中企業(yè)嚴(yán)謹(jǐn)?shù)奈募?guī)則值得借鑒學(xué)習(xí)。

做過銷售或者物流行業(yè)的小伙伴們都知道，給客戶發(fā)貨時我們都要在包裝箱外側(cè)粘貼嘜頭及裝箱單以便客戶核對貨物。

對于裝箱單而言，每個客戶的要求不盡相同，但總體上來講，都是要一份總清單及各個箱子的分清單?？偳鍐斡脕碜尶蛻艉炇?，分清單粘貼在各個對應(yīng)的包裝箱外方便客戶核對貨物。

下面是某著名公司的裝箱單，包含了一份總清單及十二份分箱單（箱號列中的數(shù)字代表箱子的箱號）。總清單和分箱單的格式都是一樣的，并且圖中涂黃色的區(qū)域都是通用的信息；區(qū)別在于總清單是一份完整的發(fā)貨清單，而分箱單則是對應(yīng)的每個包裝箱的裝貨清單。

圖一：總清單

圖二：1號箱分箱單

大家注意看一下，總清單的最大箱號是12，也就是說，我們要復(fù)制粘貼12次相關(guān)的數(shù)據(jù)才能完成整份箱單工作。

如果說還有更多的包裝箱，那么我們復(fù)制粘貼的工作量還會增加。

這顯然不是我們想要的結(jié)果！

俗話說，“懶”是社會進(jìn)步的源動力。如果，當(dāng)我們完成總清單中輸入后，EXCEL能夠按照箱號自動為每個箱子生成分箱單，則可以大大地提高我們的工作效率，讓我們可以按時下班！

讓我們一起來看看如何操作吧！

在獲取分箱單前的重要細(xì)節(jié)

為了方便文件管理和使用，裝箱單文件命名有統(tǒng)一的規(guī)范。譬如案例公司的裝箱單文件名就由3部分組成，每部分用“_”符合進(jìn)行連接：

文件中總清單和分箱單的出貨時間、船號都是通過函數(shù)獲取文件名中的對應(yīng)信息，以確保文件名和內(nèi)容的一致性。

總清單的出貨時間：

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

函數(shù)解析

★CELL(“Filename”):用于獲取文件的路徑和名稱。本文中裝箱單文件的路徑和名稱是d:desk[模板 Packing list_1016-4 Canberra_20200605.xlsm

★FIND(".",CELL("filename"))-8:利用FIND函數(shù)查找“.”在“d:desk[模板 Packing list_1016-4 Canberra_20200605.xlsm]總裝箱單”這個字符串中的位置，結(jié)果是50。再用50減去8（日期一共是8位）就得到日期第一個字符“2”的起始位置42。

★ 利用MID函數(shù)從文件路徑和名稱的字符串中第42位起提取長度為8的字符串即可得到日期信息。

★ 最后利用TEXT函數(shù)把日期信息“20200605”轉(zhuǎn)換為標(biāo)準(zhǔn)的日期格式“2020-06-05”。

總清單上的船號

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

函數(shù)解析

船號位于兩個“_”之間，因此，第一個“_”的位置加1就是船號的第一個字符位置，也就是用MID函數(shù)開始提取船號的位置；第二個“_”的位置減去1等于船號最后一個字符的位置，再減去第一個第一個“_”的位置，就得到整個船號的字符長度。

★ FIND("_",CELL("filename"))：劃線部分是查找第一個“_”的位置

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

★ FIND("_",CELL("filename"),FIND("_",CELL("filename"))+1)：從開始提取船號的位置（劃線部分）開始查找“_”，也就是查找第二個“_”的位置。

獲取1號箱分箱單

第一步：創(chuàng)建表格

新建一個工作表。我們需要用箱號數(shù)作為工作表的名稱，因此工作表重命名為“1”。

然后將總清單上的表頭和出貨時間、船號等復(fù)制過來粘貼到工作表1中。

最后添加上下方的標(biāo)注信息。

第二步：提取箱單內(nèi)容

需要提取從行號到重量的所有清單信息。

在單元格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號箱的清單。

當(dāng)清單有多行時，將公式向下拖曳即可。

圖三

公式看起來挺復(fù)雜，但其實就是一對多查詢的萬金油公式。

函數(shù)解析

這里用了INDEX函數(shù)來提取行號。

★ 總裝箱單!A$13:A$500：指定行號的提取區(qū)域

★ 提取的行號用下面的公式來指定：

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函數(shù)取得的完整文件名及路徑為“"d:desk[模板 Packing list_1016-4 Canberra_20200605.xlsm]1"”

②利用FIND函數(shù)找到“]”所在的位置（公式中綠色下劃線部分），再用字符串的總長度（公式中紅色下劃線部分）減去“]”所在的位置數(shù)，就得到箱號數(shù)的長度。最后用RIGHT從提取到箱號值。因為提取到的是文本，所以用兩個負(fù)運(yùn)算（公式中藍(lán)色下劃線部分）轉(zhuǎn)成數(shù)值。

③利用IF函數(shù)逐一判斷提取到的箱號數(shù)是否等于J列中的箱號數(shù)。如果工作表“總裝箱單”中J列的某箱號等于提取的箱號數(shù)，則返回該箱號單元格位于$J$13:$J$500區(qū)域的行數(shù)（黑色下劃線部分）。如果不相等，則返回FALSE。此處IF函數(shù)會得到一組由FALSE和行數(shù)組成的數(shù)組。

注意：這里的IF函數(shù)用法非常規(guī)用法。常規(guī)用法IF（條件,TRUE結(jié)果，FLASE結(jié)果），當(dāng)前用法是IF（條件，TRUE結(jié)果），省略了第二個逗號和第三參數(shù)。這種省略用法，如果結(jié)果為FALSE，則返回值就是FALSE，而不是0或空值。

④利用SMALL函數(shù)在IF函數(shù)返回的數(shù)組中，獲取第幾小的行數(shù)。ROW(總裝箱單!A1)，表示獲取第1小的行數(shù)；如果是ROW(總裝箱單!A2)，則表示獲取第2小的數(shù)據(jù)。

★ INDEX函數(shù)根據(jù)SMALL返回的行數(shù)，返回總裝箱單!A$13:A$500中的對應(yīng)值。

注意：因為指定的提取區(qū)域A$13:A$500只有1列，所以INDEX函數(shù)省略了第三參數(shù)，列號1。

如果沒有看懂該萬金油公式，請看這篇專門介紹此公式的文章《Excel萬金油公式INDEX-SMALL-IF-ROW篩選函數(shù)公式解讀》。

第三步：提取尺寸、總重、凈重等標(biāo)注信息

首先是總重、凈重、尺寸和船號。

因為分箱單中已經(jīng)有了這些數(shù)據(jù)，所以直接用“=”就可以解決問題了。

圖四

然后是分箱編號的提取。

分箱編號格式：箱號-總箱號。

簡單，用連接符“&”把分箱單中的箱號數(shù)據(jù)、“-”符號和公式MAX(總裝箱單!$J$13:$J$90)鏈接起來就可以解決問題啦！

公式= J13&"-"&MAX(總裝箱單!$J$13:$J$90)

圖五

第四步：添加一段確保自動更新的代碼

截止到現(xiàn)在，所有的公式設(shè)置都完成了。但還有一個問題需要解決。由于CELL函數(shù)是易失性函數(shù)，當(dāng)我們每次重新打開文件切換工作表時，需要手動按F9刷新才可以得到正確的結(jié)果。所以我們要給文件寫一段代碼。按Alt+F11打開VBA編輯器輸入圖中的代碼。這樣當(dāng)我們每次切換工作表時，代碼會自動運(yùn)算一次，取代我們手動刷新。

圖六

我們的一號分箱單完成了！

這時候有小伙伴們要嚷了：

“如果有幾十份分箱單，是不是每一份都要如此重復(fù)錄入公式和代碼?。。俊?/span>

你完全不用那樣做！

由于每一份分箱單的格式、公式及代碼都是相同的。只需要做一次就好了（例如，分箱單1）。你只需要按住CTRL鍵并拖動當(dāng)前工作表進(jìn)行復(fù)制，按規(guī)則修改工作表名稱為箱號數(shù)字，就可以得到其他分箱單。

親測，復(fù)制后的公式和代碼完全有效！

如下：

好嘍，剩下的時間我要去摸魚啦！

本文配套的練習(xí)課件請加入QQ群：264539405下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學(xué)習(xí)Excel：

相關(guān)推薦：

一對多萬金油查詢公式：Excel萬金油公式INDEX-SMALL-IF-ROW篩選函數(shù)公式解讀

INDEX函數(shù)基本用法：INDEX:函數(shù)中的精確制導(dǎo)導(dǎo)彈，最強(qiáng)大的瘸子

IF函數(shù)用法：IF函數(shù)：剝洋蔥

工作表的拆分和合并：別再為拆分、合并工作表鬧心啦！最實用的7種方法，分分鐘搞定它?。ú鸱制?/span>

版權(quán)申明：

本文為部落窩教育獨(dú)家授權(quán)作品。作者Excel應(yīng)用之家。如需轉(zhuǎn)載請聯(lián)系部落窩教育。