2680Excel函數撰寫-裝箱分配

雷明：「錦子老師，我有熟識澄海廠商送來樣品，分別為奧迪、驊威、恒泰，樣品數量分別為，20、16、4。

而我有5個箱子最多只能裝入8個樣品，則依序分裝下來，第一個箱子A-8個，第二個箱子A-8個，第三個箱子A-4個、B-4個，第四個箱子B-8個，第五個箱子B-4個、C-4個。

我有幾個問題：

1.如果廠商最多5家，當我輸入分別數量，Excel會自動幫我判斷需要幾個箱子，並註記8。

2.請問我該怎用Excel設定函數計算每箱放入那個樣品及數量。

謝謝！」

錦子老師：「這些問題我們依照下列步驟一一來解決：

1、點取A5儲存格輸入公式：

=IF(SUM($B$4:B4)<SUM($C$2:$G$2),"BOX"&ROW()-4,"")

再將公式複製到A6:A14儲存格(預估最多可以10箱)。

【公式解說】

若從B4儲存格到目前儲存格的數量合計SUM($B$4:B4)小於5家廠商的樣品數量合計SUM($C$2:$G$2)，則填入BOX字串與列號減4("BOX"&ROW()-4)，這是由於是公式是從第五列開始所以必須減4才能變成從1開始排列箱子。若從B4儲存格到目前儲存格的數量合計超過5家廠商的樣品數量合計則由於會出現錯誤訊息故不填入資料IF(SUM($B$4:B4)<SUM($C$2:$G$2), "BOX"&ROW()-4,"")。

統計。

IF(SUM($B$4:B4)<SUM($C$2:$G$2),"BOX"&ROW()-4,"")

2、點取B5儲存格輸入公式：

=IF(A5<>"",8,"")

再將公式複製到B6:B14儲存格。

3、點取C5儲存格輸入公式：

=IF(SUM(B$4:B5)<C$2,B5,IF(SUM(C$4:C4)<C$2,C$2-SUM(B$4:B4),0))

再將公式複製到C6:C14儲存格。

【公式解說】

若從B4儲存格到目前同列B欄儲存格的數量合計SUM(B$4:B5)小於C2儲存格數值，則填入B5儲存格內容，否則若從C4儲存格到上一儲存格的數量合計SUM(C$4:C4)小於C2儲存格數值，則將C2儲存格的數值減去B4儲存格到上一列B欄儲存格的數量合計C$2-SUM(B$4:B4)，否則填入0。

4、點取D5儲存格輸入公式：

=IFERROR(IF(SUM($C5:C5)=$B5,0,IF(SUM(D$4:D4)+$B5<=D$2,$B5-C5,IF(D$2-SUM(D$4:D4)+C5>$B5,$B5-C5,D$2-SUM(D$4:D4)))),0)

再將公式複製到D6:G14儲存格。

【公式解說】

若從C5儲存格到目前列C欄儲存格的數量合計SUM($C5:C5)等於B5儲存格數值，則填入0，否則若從D4儲存格到上一儲存格的數量合計加上B5儲存格數值SUM(D$4:D4)+$B5小於D2儲存格數值，則將B5儲存格數值減去C5儲存格的數值$B5-C5，否則將D2儲存格內容減去D4儲存格到上一儲存格的數量合計D$2-SUM(D$4:D4)，若出現錯訊息則填入0。

以上就是今天主要學習的知識點，希望對大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~