VBA_依條件篩選資料

這個代碼用在人工在A表格篩選完資料(標色)，然後將篩選完的資料，選取某些欄位將相關資料會到另一個表格中。 

Sub A1_發票樣本()

'2021/12/24 

Application.ScreenUpdating = False

'sh_原則人工篩選 量及單號數較多的

Dim sh_清單 As Worksheet, sh_樣本 As Worksheet

Dim D_欄位, D_資料

Set sh_清單 = Sheets("商業發票表頭") '須先由大至小排序

Set sh_樣本 = Sheets("傳票用") '可以設定自動檢查

Set D_欄位 = CreateObject("scripting.dictionary")

Set D_資料 = CreateObject("scripting.dictionary")

With sh_清單

lc = .Range("A1").End(xlToRight).Column

lr = .Range("A1048576").End(xlUp).Row

For c = 1 To lc

D_欄位(.Cells(1, c).Text) = c

Next

For i = 2 To lr

If .Cells(i, 1).Interior.ColorIndex <> xlNone Then

in_廠商 = .Cells(i, D_欄位("協力廠商"))

in_發票日 = .Cells(i, D_欄位("商業發票日期"))

in_發票號碼 = .Cells(i, D_欄位("商業發票號碼"))

in_幣別 = .Cells(i, D_欄位("付款幣別"))

' in_條件 = .Cells(i, D_欄位("付款條件"))

in_付款 = .Cells(i, D_欄位("付款群組"))

in_序號 = .Cells(i, D_欄位("內部序號"))

in_金額 = .Cells(i, D_欄位("商業發票金額"))

D_資料(i) = Array(in_廠商, in_發票日, in_發票號碼, in_幣別, in_付款, in_序號, in_金額)

End If

Next

End With

With sh_樣本

.Cells.Delete

arr_標題 = Array("廠商", "發票日", "發票號碼", "幣別", "付款方式", "請款單序號", "發票金額")

.Range("A1").Resize(, UBound(arr_標題) + 1) = arr_標題

.Range("A2").Resize(D_資料.Count, UBound(arr_標題) + 1) = Application.Transpose(Application.Transpose(D_資料.items))

End With

End Sub

Excel_SQL_Switch使用

以前使用過Switch方式處理資料，去年底以來一直使用Power Query，對於SQL的語法有點生疏，還好還是試出來了。 

select *,int((離職日-到職日)/365) as 年資,

switch(

[CF_DEPT_CABBR] like "%AC%","AC課"

,[CF_DEPT_CABBR] like "%RAD%","RAD課"

,[CF_DEPT_CABBR] like "%工程%","工程課"

,[CF_DEPT_CABBR] like "%線外%","線外加工課"

,[CF_DEPT_CABBR] like "%資材%","資材部"

,[CF_DEPT_CABBR] like "%擠%","擠型課"

,[CF_DEPT_CABBR] like "%生技%","生技課"

,[CF_DEPT_CABBR] like "%會計%","財務會計處"

,[CF_DEPT_CABBR] like "%財務%","財務會計處"

,[CF_DEPT_CABBR] like "%管理課%","管理課"

,[CF_DEPT_CABBR] like "%物流課%","物流課"

,true,[CF_DEPT_CABBR]) as 部門 from ['離職名單2020-2021$'] where 到職日 is not null

SQL_計算年資(不聰明的方式)

Excel環境的SQL無法使用更新方式的方式，譬如today 或是 date的方式，也許可以，但目前還沒找到，因此用了下面不聰明的方式處理 DATEDIFF("yyyy",時間,DATE())

select *,#2021/12/29# As 今天,int((今天-[ENTR_DATE])/365) as 年資,switch(

[DEPT_CNAME] like "%AC%","AC課"

,[DEPT_CNAME] like "%RAD%","RAD課"

,[DEPT_CNAME] like "%工程%","工程課"

,[DEPT_CNAME] like "%線外%","線外加工課"

,[DEPT_CNAME] like "%資材%","資材部"

,[DEPT_CNAME] like "%擠%","擠型課"

,[DEPT_CNAME] like "%生技%","生技課"

,[DEPT_CNAME] like "%會計%","財務會計處"

,[DEPT_CNAME] like "%管理課%","管理課"

,true,[DEPT_CNAME]) as 部門 from [在職名單1228$]

SQL_基本語法(使用環境_Excel)

     新環境沒有的Office沒有達到Power Query的最低配置office 2016，只能把以前的方法拿出來用，VBA+SQL，現在開始記錄一下各種寫法

select * from ['Select_accounts$'] where 業務 not like "%二%" and 業務 not like "%一%" 

select distinct 部門代碼 from [fnd_gf\m_15542471$] where 來源 like "M%" and 部門代碼 not in ('1184','1488','1531','1568','1698','43')

中油PAY實際使用狀況

         最近實際使用中油PAY的確是省了挺多，11/27~12/25共省了740(還有一張50元油券還未使用)，下面有一個表是實際抵用狀況以及中油pay優惠整理，實際上與目前DM的9.4%不太依樣，最高應該是9.1%。

        因為開車加油支出稍高，因此中油PAY還是有省了一些，如果要盡可能的達到9.1%(不可能)，只能盡量自助，但是有一些優惠的使用必須靠人工加油時抵用(如果讓人工加油剛好接近抵用數，可能就會很接近9.1%)。

         第一個月的優惠%數換算約8.56%，總共支出6,301+(740-150)=6,891，總共抵740-150，換算後8.56%。(因為12/25的200元是刷卡禮，不能算是原本他的遊戲規則，我是因為盡量降低人工加油金額，所以先選抵用200元，實際有一張50元是儲值送的。)

我的方式:

1.盡可能自助。(每公升0.8元約2.7%比人工產生的會員點數1.5%多)

2.星期一儲值3000，50元油票約1.6%。 還會有4%紅利金。

3.人工加油時盡可能接近可以抵的 會員點數+中信紅利點數+油票。(我還沒做到極致)

4.目前中信紅利點數星期六抵時會由每100點可以抵8元變100點抵10元。

其他備註:

1. .中信紅利金(儲值給的2%、4%)，在自助時就能自動扣。
2. 中油點數、中信紅利點數(刷卡的部分，包含用中信卡儲值)、油券只能透過人工加油時使用。

中油APY整理

中油APY實際使用

Word_草稿模式調整欄位大小

 我想我的Word應該也會稍微變強吧從這個月開始...

今天編輯辦法修正對照表時，最後一欄跑掉，用大綱模式無法將最後一欄調整，切換到草稿模式反而可以。

Power Query冷知識

1.  高版本的exel(如365版)製作的Power  Query如果在低版本excel(如2007版)中不能看到Power Query，於低版本改過excel後存檔再回到高版本中打開，原本編輯的PQ仍然存在。(365版製作的PQ於2007版excel中修改過工作表存檔，於365開啟時仍能看到PQ查詢表)-2021/12/18