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

帖子: 835
主題: 6
精華: 0
積分: 915
點名: 16
作業系統: Win 10,7
軟體版本: 2019,2013,2003
閱讀權限: 50
性別: 男
註冊時間: 2010-5-3
最後登錄: 2024-11-14

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 以後
顯見兩者間的資料並不應有交集出現.

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

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

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

帖子: 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兄及各先進能不吝指教，謝謝^^

帖子: 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)

帖子: 835
主題: 6
精華: 0
積分: 915
點名: 16
作業系統: Win 10,7
軟體版本: 2019,2013,2003
閱讀權限: 50
性別: 男
註冊時間: 2010-5-3
最後登錄: 2024-11-14

5^#

發表於 2014-9-23 23:52 | 只看該作者

本帖最後由 luhpro 於 2014-9-23 23:57 編輯

將實際資料附檔，請luhpro兄及各先進協助了，先道謝了^^..
p6703 發表於 2014-9-23 22:27

首貼中敘述的標的欄位與你提供檔案中欄位可能有衝突(會蓋到已有資料的區域),
不過我還是以你首貼中敘述的欄位為資料標的 :

Private Sub Workbook_Open()
Dim lRow&
Dim sStr$
Dim bNFind As Boolean
Dim vCount, vBDate, vEDate
Dim wbSou As Workbook, wbTar As Workbook
Set wbTar = ThisWorkbook
Set vBDate = CreateObject("Scripting.Dictionary")
Set vEDate = CreateObject("Scripting.Dictionary")
bNFind = True
For Each vCount In Workbooks
If vCount.Name = "生產日報表.xls" Then
bNFind = False
Exit For
End If
Next
If bNFind Then
Set wbSou = Workbooks.Open(wbTar.Path & "\" & "生產日報表.xls")
Else
Set wbSou = vCount
End If
lRow = 2
Set vCount = Nothing
Set vCount = CreateObject("Scripting.Dictionary")
With wbSou.Sheets(1)
Do While .Cells(lRow, 1) <> ""
With .Cells(lRow, 1)
sStr = .Offset(, 4) & "_" & .Offset(, 5)
If Left(.Offset(, 9), 2) = "AS" Or .Offset(, 9) = "" Then
vCount(sStr) = vCount(sStr) + .Offset(, 8)
If vBDate(sStr) = "" Then
vBDate(sStr) = .Value
ElseIf vBDate(sStr) > .Value Then
vBDate(sStr) = .Value
End If
If vEDate(sStr) < .Value Then vEDate(sStr) = .Value
End If
End With
lRow = lRow + 1
Loop
End With
lRow = 2
With wbTar.Sheets(1)
.Range(.Cells(2, 14), .Cells(Rows.Count, 17)).Clear
Do While .Cells(lRow, 1) <> ""
With .Cells(lRow, 1)
sStr = .Offset(, 2) & "_" & .Offset(, 3)
.Offset(, 16) = vCount(sStr)
If .Offset(, 16) >= .Offset(, 5) Then
.Offset(, 13) = vBDate(sStr)
.Offset(, 14) = vEDate(sStr)
.Offset(, 15) = "已發料"
Else
.Offset(, 13) = vBDate(sStr)
.Offset(, 15) = "生產中"
End If
Range(.Offset(, 13), .Offset(, 14)).NumberFormat = "yyyy/m/d;@"
End With
lRow = lRow + 1
Loop
End With
End Sub

複製代碼

生產計劃表-a.zip (23.15 KB)

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

帖子: 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欄項次

帖子: 835
主題: 6
精華: 0
積分: 915
點名: 16
作業系統: Win 10,7
軟體版本: 2019,2013,2003
閱讀權限: 50
性別: 男
註冊時間: 2010-5-3
最後登錄: 2024-11-14

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]

Sub GetData()
Dim lRow&, lTRow&
Dim sStr1$, sStr2$
Dim bNFind As Boolean
Dim vCount, vBDate, vEDate, vNo
Dim wbSou As Workbook, wbTar As Workbook
Dim ShTar As Sheet2
Set wbTar = ThisWorkbook
Set ShTar = Sheets(2)
Set vBDate = CreateObject("Scripting.Dictionary")
Set vEDate = CreateObject("Scripting.Dictionary")
bNFind = True
For Each vCount In Workbooks
If vCount.Name = "生產日報表.xls" Then
bNFind = False
Exit For
End If
Next
If bNFind Then
Set wbSou = Workbooks.Open(wbTar.Path & "\" & "生產日報表.xls", ReadOnly:=True)
Else
Set wbSou = vCount
End If
lRow = 2
Set vCount = Nothing
Set vCount = CreateObject("Scripting.Dictionary")
With wbSou.Sheets(1)
Do While .Cells(lRow, 1) <> ""
With .Cells(lRow, 1)
sStr1 = .Offset(, 4) & "_" & .Offset(, 5)
If Left(.Offset(, 9), 2) = "AS" Or .Offset(, 9) = "" Then
vCount(sStr1) = vCount(sStr1) + .Offset(, 8)
If vBDate(sStr1) = "" Then
vBDate(sStr1) = .Value
ElseIf vBDate(sStr1) > .Value Then
vBDate(sStr1) = .Value
End If
If vEDate(sStr1) < .Value Then vEDate(sStr1) = .Value
End If
End With
lRow = lRow + 1
Loop
End With

複製代碼

[ThisWorkBook]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim vC
For Each vC In Application.CommandBars(1).Controls
If vC.Caption = "讀取資料" Then vC.Delete
Next
End Sub
Private Sub Workbook_Open()
Dim bNFind As Boolean
Dim tcbLdData As Object
Dim vC
GetData
bNFind = True
For Each vC In Application.CommandBars(1).Controls
With vC
If .Caption = "讀取資料" Then
bNFind = False
.Visible = True
End If
End With
Next
If bNFind Then
Set tcbLdData = Application.CommandBars(1).Controls.Add(Type:=msoControlButton)
With tcbLdData
.Caption = "讀取資料" '按鈕的名稱
.FaceId = 2778 '按鈕的圖示
.OnAction = "GetData" '按鈕的巨集名稱
End With
End If
End Sub

複製代碼

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

論壇贊助勳章

8^#

發表於 2014-9-27 01:10 | 只看該作者

只能說luhpro兄..你真的太神了啦..完全符合小弟提出的要求，衷心的感謝詳細的解說，小弟先再試跑看看有無其他問題再來回報一下，謝謝^^

帖子: 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，是否可自動設定原報表格式

帖子: 835
主題: 6
精華: 0
積分: 915
點名: 16
作業系統: Win 10,7
軟體版本: 2019,2013,2003
閱讀權限: 50
性別: 男
註冊時間: 2010-5-3
最後登錄: 2024-11-14

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)

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