12
返回列表 上一主題 發帖

符合多重條件下的自動統計數量及生產區間填入指定的欄位???

p6703

帖子
134 
主題
27 
精華
0 
積分
168 
點名
0  
作業系統
WINXP 
軟體版本
EXCEL 2003 
閱讀權限
20 
性別
男 
來自
雲林 
註冊時間
2010-10-6 
最後登錄
2018-5-13 

論壇贊助勳章
1# 跳轉到 » 倒序看帖
打印
tT
發表於 2014-9-21 16:55 | 只看該作者

符合多重條件下的自動統計數量及生產區間填入指定的欄位???

如附檔有一生產計劃表及另一份生產日報表

現工作是採手動比對耗時,請問各先進可否以VBA巨集以製令及序號去比對生產日報表，將符合這二條件的加上生產日報表中J欄位（此欄位共會有空白,前二碼為AS及HB，及前一碼為Y的資料）的機台號碼前二碼為AS的及空白的(僅限比對計劃表中這些欄位空白的筆數)

1.自動統計生產總數量填於Q欄位

2.如已等於或超過原計劃表生產數（一般都會有多生產入庫的），將生產日期區間各填於N及O欄位（例：如該製令生產期間為9/15~9/22，則N欄為9/15，O欄為9/22），並於P欄位自動更新"已發料"

3.如生產數尚未超過讓製令，僅於N欄捉出生產起敋日，並於P欄位更新"生產中"

在此先謝謝先進的指教，望真能以VBA強大的功能，疏解工作量的狀況，謝謝

PS：如有不明，小弟必再詳細依不解處解釋。。^^

test.rar (19.96 KB)
收藏 分享

luhpro

帖子
824 
主題
6 
精華
0 
積分
904 
點名
0  
作業系統
Win 10,7 
軟體版本
2019,2013,2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-3 
最後登錄
2023-12-24 
2#
發表於 2014-9-21 20:12 | 只看該作者
本帖最後由 luhpro 於 2014-9-21 20:17 編輯
如附檔有一生產計劃表及另一份生產日報表
現工作是採手動比對耗時,請問各先進可否以VBA巨集以製令及序號 ...
p6703 發表於 2014-9-21 16:55

有些地方資料不相符:

上面講 : 以製令及序號去比對生產日報表

1.兩個工作表中的 製令編號 完全不相符,無法比對:
日報表是 MA14070125 與 MA14070126
計劃表是 MA14090050, MA14090051 與 MA14090052

2. 料號與序號也不相符,無法比對:
日報表中 B 料序號是 01 與 03
計劃表中 B 料序號則是 05
日報表中 CC 料序號是 03
計劃表中 CC 料序號則是 06

其次, 兩表格涵蓋的日期區間也不相符 -
日報表日期區間是 9/1 ~ 9/2
但計劃表的 下單日 與 預期開工日 卻都是 9/12 以後
顯見兩者間的資料並不應有交集出現.

要做出你想要的結果並不難,
只是若你提供的資料能依據你題的各個條件,
模擬做出每個條件應該產生的結果出來,
這樣他人較容易理解並構想程式出來.

比較理想的方式是將過去已完成的報表,
擷取出特定原始資料,
及將這些資料依照條件產生的結果都列出來.

當然,要留意其中是否有敏感資料,
在不影響條件實現的前提下,
可以適度將其遮罩或以其他文數字取代.

TOP

p6703

帖子
134 
主題
27 
精華
0 
積分
168 
點名
0  
作業系統
WINXP 
軟體版本
EXCEL 2003 
閱讀權限
20 
性別
男 
來自
雲林 
註冊時間
2010-10-6 
最後登錄
2018-5-13 

論壇贊助勳章
3#
發表於 2014-9-22 23:10 | 只看該作者
回復 2# luhpro

感謝luhpro兄如此詳細的回覆，當初提供附檔時的確有考量到因牽扯到敏感資料，故只留了格式及隨意的key 了幾筆

未注意到各先進需以資料庫及已完成的原檔案來驗證，感謝luhpro兄的提醒，因檔案現未於手邊，待明日匯整完畢後，再請luhpro兄及各先進能不吝指教，謝謝^^

TOP

p6703

帖子
134 
主題
27 
精華
0 
積分
168 
點名
0  
作業系統
WINXP 
軟體版本
EXCEL 2003 
閱讀權限
20 
性別
男 
來自
雲林 
註冊時間
2010-10-6 
最後登錄
2018-5-13 

論壇贊助勳章
4#
發表於 2014-9-23 22:27 | 只看該作者
將實際資料附檔，請luhpro兄及各先進協助了，先道謝了^^..

0923.rar (39.08 KB)

TOP

luhpro

帖子
824 
主題
6 
精華
0 
積分
904 
點名
0  
作業系統
Win 10,7 
軟體版本
2019,2013,2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-3 
最後登錄
2023-12-24 
5#
發表於 2014-9-23 23:52 | 只看該作者
本帖最後由 luhpro 於 2014-9-23 23:57 編輯
將實際資料附檔，請luhpro兄及各先進協助了，先道謝了^^..
p6703 發表於 2014-9-23 22:27

首貼中敘述的標的欄位與你提供檔案中欄位可能有衝突(會蓋到已有資料的區域),
不過我還是以你首貼中敘述的欄位為資料標的 :
  1. Private Sub Workbook_Open()
  2.   Dim lRow&
  3.   Dim sStr$
  4.   Dim bNFind As Boolean
  5.   Dim vCount, vBDate, vEDate
  6.   Dim wbSou As Workbook, wbTar As Workbook
  7.   
  8.   Set wbTar = ThisWorkbook
  9.   Set vBDate = CreateObject("Scripting.Dictionary")
  10.   Set vEDate = CreateObject("Scripting.Dictionary")
  11.       
  12.   bNFind = True
  13.   For Each vCount In Workbooks
  14.     If vCount.Name = "生產日報表.xls" Then
  15.       bNFind = False
  16.       Exit For
  17.     End If
  18.   Next
  19.   
  20.   If bNFind Then
  21.     Set wbSou = Workbooks.Open(wbTar.Path & "\" & "生產日報表.xls")
  22.   Else
  23.     Set wbSou = vCount
  24.   End If
  25.   
  26.   lRow = 2
  27.   Set vCount = Nothing
  28.   Set vCount = CreateObject("Scripting.Dictionary")
  29.   With wbSou.Sheets(1)
  30.     Do While .Cells(lRow, 1) <> ""
  31.       With .Cells(lRow, 1)
  32.         sStr = .Offset(, 4) & "_" & .Offset(, 5)
  33.         If Left(.Offset(, 9), 2) = "AS" Or .Offset(, 9) = "" Then
  34.           vCount(sStr) = vCount(sStr) + .Offset(, 8)
  35.          
  36.           If vBDate(sStr) = "" Then
  37.             vBDate(sStr) = .Value
  38.           ElseIf vBDate(sStr) > .Value Then
  39.             vBDate(sStr) = .Value
  40.           End If
  41.          
  42.           If vEDate(sStr) < .Value Then vEDate(sStr) = .Value
  43.         End If
  44.       End With
  45.       lRow = lRow + 1
  46.     Loop
  47.   End With
  48.   
  49.   lRow = 2
  50.   With wbTar.Sheets(1)
  51.     .Range(.Cells(2, 14), .Cells(Rows.Count, 17)).Clear
  52.     Do While .Cells(lRow, 1) <> ""
  53.       With .Cells(lRow, 1)
  54.         sStr = .Offset(, 2) & "_" & .Offset(, 3)
  55.         .Offset(, 16) = vCount(sStr)
  56.         If .Offset(, 16) >= .Offset(, 5) Then
  57.           .Offset(, 13) = vBDate(sStr)
  58.           .Offset(, 14) = vEDate(sStr)
  59.           .Offset(, 15) = "已發料"
  60.         Else
  61.           .Offset(, 13) = vBDate(sStr)
  62.           .Offset(, 15) = "生產中"
  63.         End If
  64.         Range(.Offset(, 13), .Offset(, 14)).NumberFormat = "yyyy/m/d;@"
  65.       End With
  66.       lRow = lRow + 1
  67.     Loop
  68.   End With
  69. End Sub
複製代碼
生產計劃表-a.zip (23.15 KB)

日報表不動, 程式放在 計劃表,
其實我認為最好的方式是放在另一個檔案上,
而該檔案則做成只顯示 UserForm 畫面加按鍵等物件,
與那兩個檔案完全不相關,
甚至可以考慮存成 增益集 檔案來運作.

TOP

p6703

帖子
134 
主題
27 
精華
0 
積分
168 
點名
0  
作業系統
WINXP 
軟體版本
EXCEL 2003 
閱讀權限
20 
性別
男 
來自
雲林 
註冊時間
2010-10-6 
最後登錄
2018-5-13 

論壇贊助勳章
6#
發表於 2014-9-24 22:34 | 只看該作者
感謝luhpro兄的即時的回覆，針對一些細節事項再補充說明：

1.原附件是捉取已完工的明細（該計劃表其實是混雜生產中及尚未生產的明細），所以實際上使用的時候，P欄位會有三種顯示：已發料,生產中,空白（判定的依據，如生產數大於或等於F欄位的數量，則顯示已發料，生產數小於F欄位數量但大於0，顥示生產中，未有生產數的，則顯示空白）

2.部份筆數因數量小於每日的產能，也就是當天就可生產完，完工日期現巨集是顯示空白，修改與起訖日同一天

3.原巨集是開檔時自動執行，但因現計劃表幾乎上班時就都開著，而生產日報表是存在另一檔案中每日更新，可否改以巨集手動執行的方法，有需要再跑巨集執行比對填上對應的資料

4.延伸一個問題，因同製令項次一般會有1~6（短則1~3，長則1~6），可否以巨集將同一製令，但不同項次的，以表格自動呈現出O欄位的日期資料（用樞紐分析表好像只有套數量，但卻無法套取指定欄位的資料），A列各製令，B~G欄項次

TOP

luhpro

帖子
824 
主題
6 
精華
0 
積分
904 
點名
0  
作業系統
Win 10,7 
軟體版本
2019,2013,2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-3 
最後登錄
2023-12-24 
7#
發表於 2014-9-26 00:14 | 只看該作者
本帖最後由 luhpro 於 2014-9-26 00:32 編輯
感謝luhpro兄的即時的回覆，針對一些細節事項再補充說明：
1.原附件是捉取已完工的明細（該計劃表其實是 ...
p6703 發表於 2014-9-24 22:34

1. 程式基本上就是這樣設計的,差別只在未生產的是秀 "生產中"  而非空白.
(此時也不會秀生產起迄日期, 修改後執行結果如附圖<把 "生產中"拿掉>)

2. 程式遇到第一筆資料時,就會先產生相同的起迄日期,
之後再依遇到的資料擴增起迄日期,
所以 "已發料" 的製令應該不會有你所說的只有單邊日期的情形.



3.已修改如附件, 在畫面上方功能表最右邊 "說明" 的旁邊新建立一個按鈕,
按鈕.png
任何時間按一次會更新資料一次,
考慮日報表可能不定時更新,
故讀取日報表檔案改採唯讀模式,
資料更新完就會關閉日報表檔案節省系統資源,
關閉檔案時按鈕也會跟著移除,
運作上應該會較順利.

4. 已實現.

生產計劃表-a2.zip (30.34 KB)
[Module1]
  1. Sub GetData()
  2.   Dim lRow&, lTRow&
  3.   Dim sStr1$, sStr2$
  4.   Dim bNFind As Boolean
  5.   Dim vCount, vBDate, vEDate, vNo
  6.   Dim wbSou As Workbook, wbTar As Workbook
  7.   Dim ShTar As Sheet2
  8.   
  9.   Set wbTar = ThisWorkbook
  10.   Set ShTar = Sheets(2)
  11.   Set vBDate = CreateObject("Scripting.Dictionary")
  12.   Set vEDate = CreateObject("Scripting.Dictionary")
  13.       
  14.   bNFind = True
  15.   For Each vCount In Workbooks
  16.     If vCount.Name = "生產日報表.xls" Then
  17.       bNFind = False
  18.       Exit For
  19.     End If
  20.   Next
  21.   
  22.   If bNFind Then
  23.     Set wbSou = Workbooks.Open(wbTar.Path & "\" & "生產日報表.xls", ReadOnly:=True)
  24.   Else
  25.     Set wbSou = vCount
  26.   End If
  27.   
  28.   lRow = 2
  29.   Set vCount = Nothing
  30.   Set vCount = CreateObject("Scripting.Dictionary")
  31.   With wbSou.Sheets(1)
  32.     Do While .Cells(lRow, 1) <> ""
  33.       With .Cells(lRow, 1)
  34.         sStr1 = .Offset(, 4) & "_" & .Offset(, 5)
  35.         If Left(.Offset(, 9), 2) = "AS" Or .Offset(, 9) = "" Then
  36.           vCount(sStr1) = vCount(sStr1) + .Offset(, 8)
  37.          
  38.           If vBDate(sStr1) = "" Then
  39.             vBDate(sStr1) = .Value
  40.           ElseIf vBDate(sStr1) > .Value Then
  41.             vBDate(sStr1) = .Value
  42.           End If
  43.          
  44.           If vEDate(sStr1) < .Value Then vEDate(sStr1) = .Value
  45.         End If
  46.       End With
  47.       lRow = lRow + 1
  48.     Loop
  49.   End With
複製代碼
[ThisWorkBook]
  1. Private Sub Workbook_BeforeClose(Cancel As Boolean)
  2.   Dim vC
  3.   
  4.   For Each vC In Application.CommandBars(1).Controls
  5.     If vC.Caption = "讀取資料" Then vC.Delete
  6.   Next
  7. End Sub

  9. Private Sub Workbook_Open()
  10.   Dim bNFind As Boolean
  11.   Dim tcbLdData As Object
  12.   Dim vC
  13.   
  14.   GetData
  15.   bNFind = True
  16.   For Each vC In Application.CommandBars(1).Controls
  17.     With vC
  18.       If .Caption = "讀取資料" Then
  19.         bNFind = False
  20.         .Visible = True
  21.       End If
  22.     End With
  23.   Next

  25.   If bNFind Then
  26.     Set tcbLdData = Application.CommandBars(1).Controls.Add(Type:=msoControlButton)
  27.     With tcbLdData
  28.       .Caption = "讀取資料" '按鈕的名稱
  29.       .FaceId = 2778 '按鈕的圖示
  30.       .OnAction = "GetData" '按鈕的巨集名稱
  31.     End With
  32.   End If
  33. End Sub
複製代碼

TOP

p6703

帖子
134 
主題
27 
精華
0 
積分
168 
點名
0  
作業系統
WINXP 
軟體版本
EXCEL 2003 
閱讀權限
20 
性別
男 
來自
雲林 
註冊時間
2010-10-6 
最後登錄
2018-5-13 

論壇贊助勳章
8#
發表於 2014-9-27 01:10 | 只看該作者
只能說luhpro兄..你真的太神了啦..完全符合小弟提出的要求，衷心的感謝詳細的解說，小弟先再試跑看看有無其他問題再來回報一下，謝謝^^

TOP

p6703

帖子
134 
主題
27 
精華
0 
積分
168 
點名
0  
作業系統
WINXP 
軟體版本
EXCEL 2003 
閱讀權限
20 
性別
男 
來自
雲林 
註冊時間
2010-10-6 
最後登錄
2018-5-13 

論壇贊助勳章
9#
發表於 2014-9-27 23:41 | 只看該作者
luhpro兄以實際的表格操作時發現幾點問題，再次請教，先在此感謝^^

1.原附件是捉取大多已生產完畢的製令，忽略了有部份的是還未發單的（指的是單未下至現場的），於G欄位未輸入日期的（原附件隱藏起來了)，於P欄位自動秀"尚未發單"

2.程式是放於計劃表中，開敋時自動執行，但後來想想計劃表是共用檔案，希望可把巨集改放在生產日報表中，設定手動執行（不必開啟檔案就自動執行）

3.原附件A欄位都有填週別，但實際報表有些因生產起訖日未定故會留空白，但執行巨集時變成僅會執行到A欄位空白前一列（是否可改以B欄位判定最後一列的依據）

4.生產起訖日及生產完工量依生產日報表比填自動填上，生產中,空白還有尚未發單的是否如有一每台機台的日產能資料，可自動依每筆製令數自動先摸擬填上預計的起訖日及完工日（此僅是構想，也不知是否真能實行，如可，可暫按每台600個/時產能估算）

5.原報表此自動填上欄位格式均為Arial，字體大小:9，但執行巨集後變成新細明體，大小:12，是否可自動設定原報表格式

TOP

luhpro

帖子
824 
主題
6 
精華
0 
積分
904 
點名
0  
作業系統
Win 10,7 
軟體版本
2019,2013,2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-3 
最後登錄
2023-12-24 
10#
發表於 2014-10-1 23:23 | 只看該作者
本帖最後由 luhpro 於 2014-10-1 23:30 編輯
luhpro兄以實際的表格操作時發現幾點問題，再次請教，先在此感謝^^
1.原附件是捉取大多已生產完畢的製令 ...
p6703 發表於 2014-9-27 23:41

1. 已完成
2. 我改把程式獨立出來並存成增益集, 而不是放在兩種檔案內,這樣比較好運作.
3. 已完成
4. 有些複雜,可否提供多一些的模擬數據資料與推演過程說明?
5. 已完成

另,
我有針對不同發料情形,
做一些底色的區分,
以方便使用者辨識.
生產資料表彙整.zip (16.37 KB)

增益集執行後會秀出操作視窗,
點擊 "彙整資料" 按鈕準備讀取與更新資料,
先選 "日報表", 再選 "計劃表",
它會自動依據日報表資料來更新計劃表內容,

TOP

        靜思自在 : 【行善要及時】行善要及時，功德要持續。如燒開水一般，未燒開之前千萬不要停熄火候，否則重來就太費事了。
12
返回列表 上一主題