Board logo

標題: [發問] 如何取得最近的需求日期以及數量 [打印本頁]
作者: adam2010    時間: 2014-6-1 00:14     標題: 如何取得最近的需求日期以及數量

請教各位高手，該如何設定公式
[attach]18422[/attach]
一份產能需求表~想要知道扣除在製量以外最近一個需求日&需求數量
其中AAAAAAA料號在製有6000，可以滿足~6/19前的需求量，6/19需求的3000則需要再補2500
BBBBBBB料號在製有10000，剛好可以滿足~6/18的需求，所以待投料的就是6/20，數量則為3000
要如何設定E & F欄公式，望各位高手指點，感激不盡！
[attach]18423[/attach]
作者: zz5151353    時間: 2014-6-1 17:32

本帖最後由 zz5151353 於 2014-6-1 17:42 編輯

C2
=SUM(G2:W2)

D2

=SUM(G2:W2)-B2
作者: adam2010    時間: 2014-6-1 19:26

感謝 zz5151353 的回應
不過我的問題是要設定
E欄的待投日期跟F欄的待投數量的公式，謝謝！
作者: zz5151353    時間: 2014-6-1 22:00

F2
{=IF(B2=MAX((SUBTOTAL(9,OFFSET(G2,,,,COLUMN(G2:W2)))<=B2)*SUBTOTAL(9,OFFSET(G2,,,,COLUMN(G2:W2)+1)))-B2,"",MAX((SUBTOTAL(9,OFFSET(G2,,,,COLUMN(G2:W2)))<=B2)*SUBTOTAL(9,OFFSET(G2,,,,COLUMN(G2:W2)+1)))-B2)}
作者: adam2010    時間: 2014-6-1 23:28

感謝 zz5151353 的熱心回應，但是測試後有一些問題 (在下功力不足看不出哪裡有問題?)
[attach]18425[/attach]
[attach]18426[/attach]
另外就是因為訂單日期欄數長短不一(不是每次都是G~V欄)，所以在C2欄位用的是=-SUM(G2:AZ2)/2 (預留多一些欄位，全部加總會包含到原來的加總後再除以2)
而不是直接用=SUM(G2:V2)
也許是我描述得不夠清楚，總之就是希望能夠設定公是能夠快速計算出各種物料在哪一個日期還缺少多少數量
而不必一個一個手動計算每一種物料哪一天的訂單投料還不足？
這樣就可以根據缺料日期看出急迫性而將共用料件優先配給交期較近的物料
作者: adam2010    時間: 2014-6-6 21:10

試了一個禮拜還是不行，請各位高手伸出援手，在下感激不盡~~
作者: yen956    時間: 2014-6-7 12:11

回復 1# adam2010
試試看：
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Dim 在製量, 需求量 As Long
  3.     Dim r1, c1 As Integer
  4.    
  5.     '如果 在製量(欄B) 或 需求量(欄G以後) 有異動, 則
  6.     If Target.Column = 2 Or Target.Column > 6 Then
  7.         r1 = Target.Row    '資料異動所在列
  8.         在製量 = Cells(r1, 2)
  9.         需求量 = 0
  10.         c1 = 6
  11.         Do
  12.             c1 = c1 + 1
  13.             需求量 = 需求量 + Cells(r1, c1)
  14.         Loop Until 需求量 > 在製量
  15.         Cells(r1, 5) = Cells(1, c1)  '待投日期
  16.         Cells(r1, 6) = 需求量 - 在製量 '待投數量
  17.     End If
  18. End Sub
複製代碼
ps:
1. 為何 D2 不是 =C2-B2, 而是  =B2+C2?(筆誤?)
2. 總量公式(CD2)與 C2公式一樣, 可刪, (保留C2公式即可, 更有彈性)
作者: yen956    時間: 2014-6-7 12:43

本帖最後由 yen956 於 2014-6-7 12:44 編輯

更正, 增加一列(避免錯誤產生):
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim 在製量, 需求量 As Long
    Dim r1, c1 As Integer
   
    '如果 在製量(欄B) 或 需求量(欄G以後) 有異動, 則
    If Target.Column = 2 Or Target.Column > 6 Then
        r1 = Target.Row    '資料異動所在列
        If Not IsNumber(Cells(r1, 2)) Then Exit Sub
        在製量 = Cells(r1, 2)
        需求量 = 0
        c1 = 6
        Do
            c1 = c1 + 1
            需求量 = 需求量 + Cells(r1, c1)
        Loop Until 需求量 > 在製量
        Cells(r1, 5) = Cells(1, c1)  '待投日期
        Cells(r1, 6) = 需求量 - 在製量 '待投數量
    End If
End Sub
作者: adam2010    時間: 2014-6-7 14:32

感謝 yen956 伸出援手~
1. 為何 D2 不是 =C2-B2, 而是  =B2+C2?(筆誤?)→因為C2= - SUM(G2:AZ2)/2 (a.老闆說呈現負數他比較能理解是不足的，但是要改為用正數也可以 b.拉長除以2是因為每次撈出來的交期長度不一定)，所以D2=B2+C2
2. 總量公式(CD2)與 C2公式一樣, 可刪, (保留C2公式即可, 更有彈性)→對呀~刪除後C2的公式就不用除以2了，反正最右邊的加總也用不太到
經測試OK，不過增加那段偵錯則會出現位定義的錯誤，我先mark那行，測試OK！真是太厲害了，再次感謝 yen956 大~
[attach]18450[/attach]
作者: yen956    時間: 2014-6-7 14:52

回復 9# adam2010
抱歉, 應該加上 Application.IsNumber 才對!!
        If Not Application.IsNumber(Cells(r1, 2)) Then Exit Sub
作者: adam2010    時間: 2014-6-7 15:37

謝謝 yen956大熱心修正，另外請教一下我將程式碼貼在工作表一裡面是對的嗎?或者應該放在什麼地方
因為剛才測試還OK，但是關閉檔案再開啟時卻不會自動更新了...
[attach]18451[/attach]
作者: adam2010    時間: 2014-6-7 16:28

Sorry~已OK，謝謝！



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)