錦子老師

如果要一支E型鋼6米長，現在要裁切如下圖的長度及數量，在我輸入如圖的長度及數量，能夠快速算出要幾支E型鋼要怎設定公式呢?

1.點取F2儲存格，輸入公式「=ROUNDUP(SUMPRODUCT(B2:B11,C2:C11)/600,0)」後，向下複製公式到D3:D17儲存格。

公式說明：

=ROUNDUP(SUMPRODUCT(B2:B11,C2:C11)/600,0)

公式一：SUMPRODUCT(B2:B11,C2:C11) 傳回B2:B11與C2:C11對應儲存格(B2*C2、B3*C3...)相乘總和。

公式二：ROUNDUP(公式一/600,0) 將公式一傳回結果除以600後無條件小數位數進位，算出需要多少條E型鋼。

如果要算出需要條數的金C型鋼要如何分配裁切，則需要用到規劃求解功能。

1.點取D1儲存格，輸入「總長度」字串。

2.點取D2儲存格，輸入公式：「=B2*C2」後，向下複製公式到D3:D11儲存格。

3.點取D12儲存格，輸入公式：「=SUM(D2:D11)」字串，算出總長度。

4.點取E1儲存格，輸入公式：「=IF(D1<ROUNDUP(SUM($D$2:$D$11)/600,0),D1+1,1)」後，向右複製公式到F1:H1(視預計幾支調整)儲存格。

5.點取E2:H11儲存格，輸入「1」字串。

6.點取J2儲存格，輸入公式：「=SUM(E2:H2)」後，向下複製公式到J3:J11儲存格。

7.點取J12儲存格，輸入公式：「=SUM(E12:H12)」。

8.點取E12儲存格，輸入公式：「=SUMPRODUCT($B$2:$B$11,E2:E11)」後，向右複製公式到F12:H12(視預計幾支調整)儲存格。

9.點取「資料 > 規劃求解」圖示。

10.在【規劃求解參數】對話方塊，點取「設定目標式」欄位，輸入「J12」。

11.點取「最小」選項鈕。

12.點取「藉由變更變數儲存格」欄位，輸入「E2:H11」。

13.點取「新增」鈕。

14.點取「儲存格參照」欄位，輸入「E12:H12」。

15.點取中間下拉方塊，選擇「<=」。

14.點取「限制式」欄位，輸入「600」。

15.點取「新增」鈕。

16.點取「儲存格參照」欄位，輸入「E2:H11」。

17.點取中間下拉方塊，選擇「INT」，則「限制式」欄位，會自動輸入「整數」。

18.點取「新增」鈕。

19.點取「儲存格參照」欄位，輸入「J2:J11」。

20.點取中間下拉方塊，選擇「=」。

21.點取「限制式」欄位，輸入「=C2:C11」。

22.點取「新增」鈕。

23.點取「儲存格參照」欄位，輸入「J2:J11」。

24.點取中間下拉方塊，選擇「>=」。

25.點取「限制式」欄位，輸入「1」。

26.點取「確定」鈕，回到【規劃求解參數】對話方塊。

27.點取「求解」鈕，5分鐘過後，出現【規劃求解結果】對話方塊。

28.點取「確定」鈕，結果如下圖。

~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~

更多相關影片教學：請點我

更多相關文章：請點我