今天(2022/1/20)發現忘了回顧2021年，因為12月換新公司，心思較無法注意太多地方。
2021年12月去新公司感覺就在挖礦?應該說交接的資料很不齊全，慢慢拼湊，每天早到半小時(7:30)，晚一小時下班(18:00)，當然回家也是有利用一些時間在公事上，以不同形式，譬如學習或查詢相關法令，企圖趕快把未完進度趕上，還有迅速熟悉公司。當然不可能毫無意義的趕資料，在趕的過程中也思考下次做怎樣比較快。
PS.個人覺得做一樣的事很無聊，所以要把時間用在有意義的地方。
當你得不到想要的，你就會得到經驗
這句話是最近看到的，不過卻是2007年時就出現的名言，最近才聽到，不過我感受到了，下面的SQL 程式碼要再擴充時(Excel中使用SQL字元太多無法運作)，excel就出現錯誤訊息，N年前在前公司使用時好像有印象遇過一次，沒想到現在這麼快就遇到了，在這邊紀錄一下。
這時我想到的是Power Query的好用，不過大概要再過一陣子，表現出自己是一個重度數據使用者，這樣提出一個公司沒有出現過的office版本才有說服力(我只是想要office 2016版)。
PS.2022/1/14 我用SQL讓我的excel當掉，也因此可以從4G記憶體往上擴充記憶體。
select *,"20"& mid(請購單編號,4,2) as 年,
switch(摘要 like "%維護營運費%","ORACLE相關"
,摘要 like "%ORACLE%","ORACLE相關"
, 摘要 like "%Spam SQR%","Spam SQR相關"
,摘要 like "%UPS%","UPS不斷電"
,摘要 like "%不斷電%","UPS不斷電"
,摘要 like "%VM相關%","ERP、VM相關維護費用"
,摘要 like "%趨勢%","防毒相關"
,摘要 like "%防火%","防毒相關"
,摘要 like "%Fire%","防毒相關"
,摘要 like "%Storage%","Storage及Server相關費用"
,摘要 like "%Server%","Storage及Server相關費用"
,摘要 like "%專案%","專案型支出"
,金額<10000,"其他金額10000以下"
,True,摘要) as 項目分類
from [XX請購$]
下面的例子應該可以用在統計同一銷售客戶同一品號兩個報價以上，
以及同一品號只有一個供應商提供，也許會讓電腦不夠強的當掉。(如果用Power Query 就能解決比較複雜的狀況)
'..........................統計兩次紀錄以上資料
SELECT A.料號,品名,單位,倉別,庫存數
FROM [物料表$] A ,
(SELECT 料號 FROM [物料表$] GROUP BY 料號 HAVING COUNT(料號) >1) B
WHERE A.料號 =B.料號
'----------------------------------------------------統計單次
SELECT * FROM [程式清單$] A
WHERE (SELECT COUNT(程式代號) FROM [程式清單$] WHERE 程式代號=A.程式代號)=1
'----------------------------------統計單次另一種方式
SELECT * FROM [程式清單$]
WHERE 程式代號 IN
(SELECT 程式代號 FROM [程式清單$] GROUP BY 程式代號 HAVING COUNT(程式代號)=1)
將不同格式相同key的資料聯結
SELECT 學生姓名,性別,年齡,課程名稱,老師姓名
FROM ([學生$] A INNER JOIN [課程$] B ON A.編號=B.編號)
INNER JOIN [老師$] C ON B.編號=C.編號
ORDER BY 課程名稱
以下是金管會的新聞稿，可能是金管會特別發新聞稿，保險公司原本沒有很要求非本人信用卡繳保險的，現在也要提供相關證明。
幾年前在前公司，某一天正在key VBA代碼時，突然不用查代碼可以一直很順的打出代碼，那時我相信武俠小說寫的頓悟，今天晚上洗澡時在想明天公司資料擷取時要怎麼寫代碼比較快(不好的習慣，下班後還在想公事...太難改掉)，因為是以前寫過的，洗著洗著突然認為可以在沒資料時寫出代碼，就這樣試著打，還真的完成代碼了，明天去公司只要微調就能使用，這次有BLOG記錄這個心情，心情開心，這跟超級賽亞人變身一樣嗎?
我想曾經打過的方式，應該都能在沒資料時都把code建構出來了吧。
PS.下面的代碼用在抓樞紐資料，因為樞紐跑出來會有點類似縮排，如果要抓類似資料格式，我個人認為是用VBA比較快。(尤其是沒有可用Power Query版本的office)
Sub A0_頓悟()
'2022/1/13 22:03
Application.ScreenUpdating = False
Dim D_資料, sh_來源 As Worksheet, sh_目的 As Worksheet
Set D_資料 = CreateObject("scripting.dictionary")
Set sh_來源 = Sheets("A")
Set sh_目的 = Sheets("B")
With sh_來源
lr = .Range("A1048576").End(xlUp).Row
arr_資料 = .Cells(1, 1).Resize(lr, 2)
For i_次 = 1 To 2
For i = LBound(arr_資料) To UBound(arr_資料)
If i_次 = 1 Then
If arr_資料(i, 1) = "" Then
arr_資料(i, 1) = arr_資料(i - 1, 1)
End If
ElseIf i_次 = 2 Then
in_料號 = arr_資料(i, 1)
in_客戶 = arr_資料(i, 2)
in_條件 = in_料號 & in_客戶
D_資料(in_條件) = Array(in_料號, in_客戶)
End If
Next
Next
End With
With sh_目的
.Cells.Delete
.Range("A2").Resize(D_資料.Count, 2) = Application.Transpose(Application.Transpose(D_資料.items))
End With
End Sub
在國內有些教授出書提到用VBA寫股市策略，我自己有買的書就是姜林杰佑、許江河這兩個教授寫的，也許還有其他教授出書教怎麼用VBA寫策略也不一定，只是我後來在這方面少著墨。最近翻交易聖經2這本書，作者布倫特‧潘富( Brent Penfold)提到他用VBA做不同的策略回測，這讓我有點訝異，沒想到知名的股市名人也會用VBA去做交易策略回測。
我自己幾年前也曾經用VBA以去做過，只是在資料庫整理方面花時間，只玩了一小段時間，不過基本上VBA做交易策略是可行的，只要有一點點VBA程度就可以。
如果用這方面來看Power Query ，我的理解似乎是無法做這方面的測試，但應該是能用在股市資料庫整理，以及相關資訊的連結。
移動檔案用清單的方式，這是我覺得比較方便的方式，試了之後很快，下面可以看到來源夾剩一個檔案，故意留下來看看是不是我要的結果，目標夾全移過去了。
為了整理凌亂的檔案，這是其中一個步驟。
 |
| 移動檔案的清單 |
 |
| 移動檔案的來源夾 |
 |
| 移動檔案的目標夾 |
Sub 移動檔案_清單方式()
'2022/1/12 22:15
Application.ScreenUpdating = False
With ActiveSheet
For i = 2 To 8
in_原始 = .Cells(i, 1).Text
in_新位置 = .Cells(i, 2).Text
Name in_原始 As in_新位置
Next
End With
End Sub
移動檔案，整個資料夾移動，
Sub 移動檔案_資料夾()
'2022/1/12 21:56
Application.ScreenUpdating = False
Dim objFile As File
Dim objFolder As Folder
Dim objFSO As FileSystemObject
Dim in_原始夾 As String
Dim in_目標夾 As String
in_原始夾 = "C:\Users\arthur\Desktop\C槽桌面\TESTA"
in_目標夾 = "C:\Users\arthur\Desktop\C槽桌面\TEST"
Set objFSO = New FileSystemObject
Set objFolder = objFSO.GetFolder(in_原始夾)
For Each objFile In objFolder.Files
'If (objFile.Name <> ThisWorkbook.Name) And (InStr(1, objFile.Name, ".xls") Or InStr(1, objFile.Name, ".csv")) Then
objFile.Move (in_目標夾 & "\" & objFile.Name)
'End If
Next objFile
End Sub
下面的代碼是當初整理相片時網路上找到然後修改，印象中花瞭半小時處理1萬個以上的相片檔，現在想要用在批次整理新公司的檔案，不過還不夠簡單，但先存一下做個紀錄。
主要功能，透過檢查日期的方式，以檔案的修改日、建立日、授權日中最小的日期當作檔案一開始的日期(如果同一天會做備份，這部分需要自己檢查有沒有依樣)，然後做分類，算是比較花時間的方式，但比自己去分類相片簡單多了。
Sub 移動檔案___改()
'2017/10/14 2017/10/16改
Application.ScreenUpdating = False
Dim objFile As File, 目的_File As File
Dim objFolder As Folder, 目的_folder As Folder
Dim D_檔案檢查 As Object
' DateLastModified DateCreated DateLastAccessed
Dim objFSO As FileSystemObject
Dim current_path ' As String 在namespace那邊抓不到
current_path = "D:\照片\2016\新增資料夾" '應該可以用dic 方式把所有資料夾丟到Arr or 參考取得檔案名稱的方式修改
Set objFSO = New FileSystemObject
Set objFolder = objFSO.GetFolder(current_path)
Set D_檔案檢查 = CreateObject("scripting.dictionary")
mypath = "D:\照片\"
On Error Resume Next
For Each objFile In objFolder.Files
in_修改日 = Format(objFile.DateLastModified, "yyyymm")
in_建立日 = Format(objFile.DateCreated, "yyyymm")
in_授權日 = Format(objFile.DateLastAccessed, "yyyymm")
With CreateObject("shell.application").Namespace(current_path)
in_文字 = Split(Split(.getdetailsof(.Items.Item(objFile.Name), -1), vbLf)(1), ":")(1) 'objFile.Name
in_拍攝日 = Right(Split(in_文字, "/")(0), 4) & Format(Mid(Split(in_文字, "/")(1), 2, Len(Split(in_文字, "/")(1)) - 1), "00")
End With
If in_拍攝日 <> "" Then
目的_path = mypath & Left(in_拍攝日, 4) & "\" & in_拍攝日 & "\"
Set 目的_folder = objFSO.GetFolder(目的_path)
For Each 目的_File In 目的_folder.Files
D_檔案檢查(目的_File.Name) = 目的_File.Name
Next
If D_檔案檢查(objFile.Name) = False Then ' And Format(objFile.DateLastModified, "yyyymm") = in_年度 & Format(i_月, "00") Then
objFile.Move (目的_path & "\" & objFile.Name)
Else
' Do While D_檔案檢查(Split(objFile.Name, ".")(0) & "_1." & Split(objFile.Name, ".")(1))=false
objFile.Move (目的_path & "\" & Split(objFile.Name, ".")(0) & "_1." & Split(objFile.Name, ".")(1)) '備分確認用
End If
Else
in_最小日 = Application.Min(in_修改日, in_建立日, in_授權日)
目的_path = mypath & Left(in_最小日, 4) & "\" & in_最小日 & "\"
Set 目的_folder = objFSO.GetFolder(目的_path)
For Each 目的_File In 目的_folder.Files
D_檔案檢查(目的_File.Name) = 目的_File.Name
Next
If D_檔案檢查(objFile.Name) = False Then ' And Format(objFile.DateLastModified, "yyyymm") = in_年度 & Format(i_月, "00") Then
objFile.Move (目的_path & "\" & objFile.Name)
Else
objFile.Move (目的_path & "\" & Split(objFile.Name, ".")(0) & "_1." & Split(objFile.Name, ".")(1)) '備分確認用
End If
' Set D_檔案檢查 = Nothing '***************
End If
Next objFile
Set D_檔案檢查 = Nothing '***************
End Sub
新的工作使用word的部分比以前要多，讓我感覺到自己對word的不足，因此翻出以前出差大陸時買的word書籍，還有再次進入論壇學習word(偏高階及word VBA)，看到當初註冊的時間點2010/5/15 23:30，真是佩服自己的學習心，當年進入前公司是在2009/9/1，那時主要是學習Excel，讓我從一個小白變成前公司Excel最強? 既使是現在目前公司，仍然覺得自己excel還可以輾壓很多同事。
對於word使用佔據太多正常工作時間，深深感覺到有點浪費生命，因此打算提前把一些word知識預先學習，減少無謂的浪費，畢竟文書工作人人能做，不能學習Doctor-X～外科醫·大門未知子說的話，那我就選擇加強自己word能力，開始對於自己能縮短word的工時，之後會把學習的部分放blog中記錄成長過程。
 |
| 論壇註冊時間 |
開始學習Word VBA,不過用在word上比較少，因此打算可能會用到的語句開始，雖然同樣是VBA，但excel VBA跟word VBA 還是有些差異處，語法方式看起來是一樣。
如果使用Power Query 該有的欄位都有，但不同檔的欄位排列不一樣的話(A檔 甲乙丙,B檔 丙乙甲)，Power Query可以自動排列，我還是比較喜歡用Power Query。
彙總個工作表資料
Sub A0_彙整資料()
'2022/1/2 22:00 22:30完成
Application.ScreenUpdating = False
Dim D_項目, D_附件, D_工作表, Sh_項目 As Worksheet, sh_附件 As Worksheet
Set D_項目 = CreateObject("scripting.dictionary")
Set D_附件 = CreateObject("scripting.dictionary")
Set D_工作表 = CreateObject("scripting.dictionary")
For i = 1 To Sheets.Count
D_工作表(Sheets(i).Name) = i
Next
If D_工作表.exists("項目彙總") = False Then
Sheets.Add.Name = "項目彙總"
End If
If D_工作表.exists("附件彙總") = False Then
Sheets.Add.Name = "附件彙總"
End If
Set Sh_項目 = Sheets("項目彙總")
Set sh_附件 = Sheets("附件彙總")
'On Error Resume Next
For i_sh = 1 To Sheets.Count
With Sheets(i_sh)
If .Cells(9, 2) = "稽核項目" Then
br = .Cells.Find("稽核項目", LookIn:=xlValues).Row
bc = .Cells.Find("稽核項目", LookIn:=xlValues).Column
lr = .Cells(br, bc).Offset(, 1).End(xlDown).Row
For i = br To lr
in_項目 = .Cells(i, bc)
in_範圍 = .Cells(i, bc + 1)
in_單位 = .Cells(i, bc + 2)
in_日期 = .Cells(i, bc + 3)
in_工作表 = Sheets(i_sh).Name
D_項目(in_工作表 & i) = Array(in_工作表, in_項目, in_範圍, in_單位, in_日期)
Next
ElseIf .Cells(6, 2) = "編號" Then
br = .Cells.Find("編號", LookIn:=xlValues).Row
bc = .Cells.Find("編號", LookIn:=xlValues).Column
lr = .Cells(br, bc).Offset(, 1).End(xlDown).Row
For i = br To lr
in_編號 = .Cells(i, bc)
in_部門 = .Cells(i, bc + 1)
in_文件 = .Cells(i, bc + 2)
in_工作表 = Sheets(i_sh).Name
D_附件(in_工作表 & i) = Array(in_工作表, in_編號, in_部門, in_文件)
Next
End If
End With
Next
With Sh_項目
.Cells.Delete
.Range("A2").Resize(D_項目.Count, 5) = Application.Transpose(Application.Transpose(D_項目.items))
End With
With sh_附件
.Cells.Delete
.Range("A2").Resize(D_附件.Count, 4) = Application.Transpose(Application.Transpose(D_附件.items))
End With
End Sub
