2680Excel函數撰寫-裝箱分配

錦子老師
·
·
IPFS
·

雷明:「錦子老師,我有熟識澄海廠商送來樣品,分別為奧迪、驊威、恒泰,樣品數量分別為,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。

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






CC BY-NC-ND 2.0 授权

喜欢我的作品吗?别忘了给予支持与赞赏,让我知道在创作的路上有你陪伴,一起延续这份热忱!