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

帖子: 145
主題: 42
精華: 0
積分: 193
點名: 0
作業系統: Windows7
軟體版本: Office2010
閱讀權限: 20
性別: 男
註冊時間: 2010-11-25
最後登錄: 2026-6-10

論壇贊助勳章

3^#

發表於 2014-6-1 19:26 | 只看該作者

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

Adam

版主 Rank: 7 Rank: 7 Rank: 7

zz5151353

帖子: 206
主題: 3
精華: 0
積分: 218
點名: 0
作業系統: WIN10
軟體版本: OFFICE 365
閱讀權限: 100
性別: 男
來自: 台灣台北
註冊時間: 2010-5-15
最後登錄: 2023-6-18

4^#

發表於 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)}

300 字節以內
不支持自定義 Discuz! 代碼

帖子: 145
主題: 42
精華: 0
積分: 193
點名: 0
作業系統: Windows7
軟體版本: Office2010
閱讀權限: 20
性別: 男
註冊時間: 2010-11-25
最後登錄: 2026-6-10

論壇贊助勳章

5^#

發表於 2014-6-1 23:28 | 只看該作者

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

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

Adam

帖子: 145
主題: 42
精華: 0
積分: 193
點名: 0
作業系統: Windows7
軟體版本: Office2010
閱讀權限: 20
性別: 男
註冊時間: 2010-11-25
最後登錄: 2026-6-10

論壇贊助勳章

6^#

發表於 2014-6-6 21:10 | 只看該作者

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

Adam

yen956

高中生 Rank: 3 Rank: 3

帖子: 522
主題: 36
精華: 1
積分: 603
點名: 0
作業系統: win xp sp3
軟體版本: Office 2003
閱讀權限: 50
性別: 男
註冊時間: 2012-12-13
最後登錄: 2021-7-11

7^#

發表於 2014-6-7 12:11 | 只看該作者

回復 1# adam2010
試試看：

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 '資料異動所在列
在製量 = 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

複製代碼

ps:
1. 為何 D2 不是 =C2-B2, 而是 =B2+C2?(筆誤?)
2. 總量公式(CD2)與 C2公式一樣, 可刪, (保留C2公式即可, 更有彈性)

yen956

高中生 Rank: 3 Rank: 3

帖子: 522
主題: 36
精華: 1
積分: 603
點名: 0
作業系統: win xp sp3
軟體版本: Office 2003
閱讀權限: 50
性別: 男
註冊時間: 2012-12-13
最後登錄: 2021-7-11

8^#

發表於 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

帖子: 145
主題: 42
精華: 0
積分: 193
點名: 0
作業系統: Windows7
軟體版本: Office2010
閱讀權限: 20
性別: 男
註冊時間: 2010-11-25
最後登錄: 2026-6-10

論壇贊助勳章

9^#

發表於 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 大~

Adam

yen956

高中生 Rank: 3 Rank: 3

帖子: 522
主題: 36
精華: 1
積分: 603
點名: 0
作業系統: win xp sp3
軟體版本: Office 2003
閱讀權限: 50
性別: 男
註冊時間: 2012-12-13
最後登錄: 2021-7-11

10^#

發表於 2014-6-7 14:52 | 只看該作者

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