[發問] 如何取得最近的需求日期以及數量

adam2010

請教各位高手，該如何設定公式

下載 (60.68 KB)

2014-6-1 00:08

一份產能需求表~想要知道扣除在製量以外最近一個需求日&需求數量
其中AAAAAAA料號在製有6000，可以滿足~6/19前的需求量，6/19需求的3000則需要再補2500
BBBBBBB料號在製有10000，剛好可以滿足~6/18的需求，所以待投料的就是6/20，數量則為3000
要如何設定E & F欄公式，望各位高手指點，感激不盡！
20140531.zip (4.85 KB)

下載次數: 7

2014-6-1 00:08

zz5151353

C2
=SUM(G2:W2)

D2

=SUM(G2:W2)-B2

adam2010

感謝 zz5151353 的回應
不過我的問題是要設定
E欄的待投日期跟F欄的待投數量的公式，謝謝！

zz5151353

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

感謝 zz5151353 的熱心回應，但是測試後有一些問題 (在下功力不足看不出哪裡有問題?)

下載 (122.73 KB)

2014-6-1 23:13

20140531-1.zip (5.92 KB)

下載次數: 7

2014-6-1 23:13

另外就是因為訂單日期欄數長短不一(不是每次都是G~V欄)，所以在C2欄位用的是=-SUM(G2:AZ2)/2 (預留多一些欄位，全部加總會包含到原來的加總後再除以2)
而不是直接用=SUM(G2:V2)
也許是我描述得不夠清楚，總之就是希望能夠設定公是能夠快速計算出各種物料在哪一個日期還缺少多少數量
而不必一個一個手動計算每一種物料哪一天的訂單投料還不足？
這樣就可以根據缺料日期看出急迫性而將共用料件優先配給交期較近的物料

adam2010

試了一個禮拜還是不行，請各位高手伸出援手，在下感激不盡~~

yen956

回復 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

更正, 增加一列(避免錯誤產生):
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

感謝 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 大~

下載 (102.97 KB)

2014-6-7 14:28

yen956

回復 9# adam2010
抱歉, 應該加上 Application.IsNumber 才對!!
        If Not Application.IsNumber(Cells(r1, 2)) Then Exit Sub