Power Query 裡排序的方式，目前已知加非正規的方式共三種。
 |
| Power Query 原資料 |
 |
| Power Query 移除其他行排序 |
在線上申請的初步分析研判表，終於在兩個星期後看到了，正如在保險公司的學弟說的，不是從事保險的看不出責任比例，保險人員都看得出比例...，我的這件對方責任7，可能因為對方責任高，對方理賠看到研判表才質疑(1個月後)我為何要報廢不修。
一開始給對方估價單時，對方理賠人員都不講話，現在才說為何市值高於維修卻不修，對於2005年的梅基估10萬維修竟然說市值高於維修...，我問對方市值多少說8萬...，問他有不賠的嗎? 為何說市值高於維修??
原來他論點是估價都會比較高...，後來我的保險員打給對方，卻又回說市值約10萬，老實說整個理解後我對這間常在打廣告的保險公司很多疑問，後來考慮給原廠再估價(讓對方沒理由)，我都懷疑這個理賠員會不會跟員場串通報修低價，然後讓我的車子出一堆狀況...
我的業務員說對方只會比較嚴格不會來暗的，那就找時間來估價吧...
常在打多快速到第一現場的保險公司，我真的對你們理賠員的人格打很多問號阿...，完全讓我把你們公司剔除在投保名單中。
PS.原本還在找二手車，現在就看原廠估價狀況
這次要學習的是附加查詢，可以將兩個相同結構的資料串在一起，譬如不同月份、不同單位的同樣欄位資料串在一起。
這裡使用上次提到的資料(Power Query_00合併 )來做到一樣的結果，會做到到附加查詢、轉樞紐資料。
 |
| Power Query 顯示窗格 |
 |
| Power Query查詢表 |
 |
| Power Query 查詢表(回到過去) |
 |
| Power Query 編輯器內作附加查詢 |
 |
| 資料直接串起來 |
 |
| Power Query樞紐資料行 |
 |
| Power Query新增資料行 |
 |
| Power Query 合併查詢與附加查詢比較 |
Power Query才剛學不久，不過簡單的介面功能算是摸出了一些，在這裡分享一下第一個使用的方式，剛好那天同事也有問我一個可以派上用場的問題。
下面步驟看起來似乎很困難，主要是第一次介紹將步驟仔細介紹，實際上用久了很多步驟並不難，Power Query可以處理很大資料量，讀取上也比SQL外部連結快多了，如果要處理資料多的人建議學學Power Qurey。
 |
| 選取資料由資料表建立Power Qurery |
 |
| 選取資料由資料表建立Power Qurery |
 |
| 進入查詢編器 |
 |
| 修改表格名稱 |
 |
選取資料建立另一表格 |
 |
| Power Query 選擇僅連接 |
 |
| Power Query 載入至選項→選擇僅連接 |
 |
| Power Query 合併 |
 |
| Power Query 合併 |
紅色框框就是本月資料，綠色框框點選資料能展開選擇要顯示的欄位
 |
| Power Query編輯器 |
 |
| Power Query 展開點選要併入的欄位 |
 |
| Power Query 新增資料行 |
 |
| Power Query 新增資料行計算結果 |
 |
| Power Query 關閉後載入資料表 |
在此處紀錄一下，在Power Query只能使用SQL選擇欄位，無法用來做其他的命令，而且只有在僅連接的狀態下才能使用SQL。
另一種方式就是用Power Query整理成資料表，然後再用外部連接從資料表作SQL命令，等於資料來源以Power Query的方式處理，進階的樞紐分析以SQL處理。
 |
| 活業簿現有連線的差異 |
 |
| Power Query僅連接可以使用SQL |
 |
| Power Query導入資料模型不可以使用SQL |
 |
| 在Power Query使用SQL代碼只能篩選欄位 |
 |
| 在Power Query使用SQL代碼只能篩選欄位，不支援其他命令 |
 |
| SQL代碼只能篩選欄位成功(庫別名稱不選擇) |
 |
| 在Power Query使用SQL代碼只能篩選欄位，不支援其他命令 |
 |
| 在Power Query使用SQL代碼只能篩選欄位，不支援其他命令 |
兩個網站當初是同時送，一個有持續更新到，另一個一個月沒更新了，現在重新送出網站地圖，過一陣子再看看是不是正常了。
網站地圖 https://www.labnol.org/blogger/sitemap/ 在 Generate Sitemap 輸入網址，就能產生網站地圖，然後再回網誌設定更新網站地圖。
 |
| 網站地圖搜尋 |
 |
| 網站地圖搜尋 |
 |
| 學習網誌系統蒐尋建立通知 |
 |
| 投資網誌系統蒐尋建立通知 |
下面VBA代碼是用來做單價查詢，利用TRIMMEAN函數將20%的極端值排除求出平均數，在以STDEV.S求得標準差，然後計算標準差佔坪均數的比例，比例越高代表這個品號價格波動越大，用在採購單價及銷售單價上有不錯的篩選效果。
在這裡是使用一般平均數計算也可以達到效果，因為每個單價如果差異大標準差就會大，也就會顯示高差異比例。
陣列在VBA使用上能節省很多時間，在這例子上不使用陣列需要花33秒，這讓我有點難接受，後來改陣列計算，縮短為7秒左右，勉強能接受，下面提供部分代碼，有興趣的人可以參考一下。
Sub 單價偏鋒_標準差()
'2020/11/25 此方式須33秒(跟資料範圍有關)
Application.ScreenUpdating = False
t = Timer
Dim sh_樞紐 As Worksheet
Set sh_樞紐 = Sheets("樞紐")
With sh_樞紐
lr = .Range("A1048576").End(xlUp).Row
lc = .Range("A2").End(xlToRight).Column
For i = 3 To lr
.Cells(i, lc + 1) = "=IFERROR(TRIMMEAN(RC[-60]:RC[-1],0.2),"""")"
.Cells(i, lc + 2) = "=IFERROR(int(STDEV.S(RC[-61]:RC[-2])),"""")"
.Cells(i, lc + 1) = .Cells(i, lc + 1).Value
.Cells(i, lc + 2) = .Cells(i, lc + 2).Value
If .Cells(i, lc + 2) > 0 Then
.Cells(i, lc + 3) = Round(.Cells(i, lc + 2) / .Cells(i, lc + 1), 2)
End If
Next
End With
MsgBox Timer - t
End Sub
Sub 單價偏鋒_標準差A()
'2020/11/25 此方式約6秒 ，如果輸出""會變7秒，增加截取最近一年最高價時間約7.2秒
Application.ScreenUpdating = False
t = Timer
Dim sh_樞紐 As Worksheet
Set sh_樞紐 = Sheets("樞紐")
With sh_樞紐
lr = .Range("A1048576").End(xlUp).Row
lc = .Range("A2").End(xlToRight).Column
.Columns(lc + 1).Resize(, 2).NumberFormatLocal = "#,###"
.Columns(lc + 3).NumberFormatLocal = "#.00%"
.Columns(lc + 4).NumberFormatLocal = "#,###"
' arr_樞紐 = .Range("A1").Resize(lr, lc + 3) 可能是記憶體關係，計算完無法撈出來，不然整體會落在1秒內
For i = 3 To lr
arr_暫時 = .Cells(i, lc - 59).Resize(, 60) '----------差異點
arr_最近 = .Cells(i, lc - 11).Resize(, 12) '----------新增步驟
If Application.Sum(arr_暫時) > 0 Then
.Cells(i, lc + 1) = Application.TrimMean(arr_暫時, 0.2)
in_標準差 = Application.StDev_S(arr_暫時)
If IsError(in_標準差) Then
.Cells(i, lc + 2) = 0 '輸出"" 會比輸出0 多1秒
Else
.Cells(i, lc + 2) = Application.StDev_S(arr_暫時)
End If
Else
.Cells(i, lc + 2) = 0
.Cells(i, lc + 1) = 0
End If
in_T平均 = .Cells(i, lc + 1)
in_標準差 = .Cells(i, lc + 2)
If in_標準差 > 0 Then
.Cells(i, lc + 3) = Application.Round(in_標準差 / in_T平均, 2)
End If
in_最高價 = Application.Max(arr_最近) '-----增加此步驟
If in_最高價 > 0 Then '避免不必要輸出，減少時間
.Cells(i, lc + 4) = in_最高價
End If
Erase arr_最近
Erase arr_暫時
Next
End With
MsgBox Timer - t
End Sub
單條件判斷：判斷的條件較少，通常較不容易出錯
符合有房就嫁，否則不嫁，判斷條件只有一個 有房 。
=IF(B2="有房","嫁","不嫁")
 |
| IF單條件判斷 |
 |
| IF多條件判斷 |
 |
| IF多條件符合判斷 |
下表中，判斷A7重量5500公斤，落在哪一個計算運費中
為了方便了解，將公式分段表示，在一開始學習很多層判斷時，需要如下面拆解一樣，分開看，看久了就熟了。
A1:F2為每公噸單位運費，因此下面計算時還需要先將A7換算成公噸。
B7=
IF(A7<1000,A7/1000*B2,
IF(A7<3000,A7/1000*C2,
IF(A7<5000,A7/1000*D2,
IF(A7<9000,A7/1000*E2,
A7/1000*F2))))
 |
| IF區間判斷 |
 |
| Power Query 及 Power Pivot比較 |
今天在社群看到一位前輩分享他從本國銀行分行授信辦事員~外商銀行台灣子行稽核副總裁(共經歷20年)。他分享了他前輩跟他說過的話: 幫公司做事，學自己功夫。
他幫公司做事學自己功夫，也在職涯成就反映了他的努力，雖然不是每個努力的人都能跟這位前輩一樣，但我認為這句話相當正面，有時我們很認真做事了，但沒被看到，要因此裹足不前嗎?
很多工作做一陣子你就能摸熟，摸熟後你會選擇繼續用原方式做嗎? 我會選擇用別的方式做，稽核這工作在查核上有很多方式，還在財務單位工作時，因為要作很多分析報表，而我又不喜歡慢慢key，因此我努力學函數，減少人工key的時間，後來轉稽核後，自學VBA，實際上稽核不需要會VBA也很少稽核學，但學會後自己寫程序盡量做外面販售的查核軟體相同功能，在工作上的確也做到大範圍查核，時間上縮短不少。
最近上課見識到Power pivot的功能，了解了一下以前的同事任職的大公司目前都使用Power BI分析，雖然我會的VBA+SQL可以做到跟Power BI大部分的功能一樣，但我認為這是一個時代交替的開始，未來這工具使用會越來越普及，本來打算在公司上使用，不過excel版本不支援，但Power BI 桌面版還是能用，因此我打算像以前學VBA時一樣，在家裡學Power BI，然後在公司上運用。
Power BI 及他的功能給我的感覺是微軟將很多資料分析、資料匯整、圖表視覺化在背後已經寫好程序，我們只要作簡單的動作就能輕易做到原本需要VBA處理的事情，容易就做到多樣性的數據分析這是 Power BI 強大的地方，雖然VBA能做到他無法做到的事，我的想法是未來學會Power BI包含Power Query、Power pivot 能讓稽核工作更加順手，當你各樣工具使用到某種程度，把他們串在一起，那就是屬於你的特有專長。
千萬不要認為工作上手就不學習!!
Vlookup函數算大家常用來查找比對的函數之一，但也很容易被忽略該注意的部分，倒致無法搜尋到正確的答案。
下圖為微軟官方函數說明:
 |
| vlookup函數說明 |
下圖是官方2016版excel的教學檔，為了更方便理解稍做修改。
 |
| 官方2016教學說明修改 |
分享一下李笑來在財富自由之路，關於積極進取型人格的宣言:
11/9上班路途中在路上遇到一台對向左轉車，我發現時他已經在我眼前，離不到一個車身距離，心裡大喊完蛋，但腳還是緊緊踩煞車，不過還是撞到了。
事後看紀錄器才知他是左轉(一度以為他闖紅燈)，可能是A柱造成我發現時已經在眼前，這個開車的老伯沒看直行車就轉，對方後座因為撞擊受了輕傷，有人說因為對方比我嚴重，所以究責可能我高些...，幸好雙方都是皮肉傷，但車子都要報廢了 。
就這樣人生的第一筆事故發生，還好在第三方責任險有加重+財損，在保險上可以幫上，責任歸屬仍然要看判定，但買了兩年的中古車提早退休，又要多花一筆買車費用...
原本預計每周分享一篇excel，本周目前還沒有心思，人也因為思慮關係，比平常容易累...
陣列公式挺常在excel運用中看到，但對於不是學程式的初學者有點難理解，當初在學習時也是看很久，才慢慢理解，透過大量的例子就能了解相關運用。
什麼是陣列？
簡單來說，就是“一組數”。一般公式用的就是“一個數”。舉個例子來說，在C1儲存格中輸入：=A1*B1 這裡的“A1”和“B1”都是“一個數”。
輸入：=A1:A7*B1:B7
A1:A7或者B1:B7，分別就是7個儲存格中內容，是7個數，也就是一個“陣列”。
公式=A1:A7*B1:B7，就是將這其中同一行的兩個數分別相乘。也就是A1*B1,A2*B2,A3*B3,……,A7*B7
最終的結果也是7個數。將C1儲存格中公式輸入為=SUM(A1:A7*B1:B7)
然後按Ctrl+Shift+Enter得到的結果就是上面，分別相乘得到的7個數之和。{=SUM(A1:A7*B1:B7)}
 |
| sum陣列 |
陣列有維度和尺寸。
一維陣列，就是只有一列或者一行的陣列，比如：A1:D1（只有一行），B2:B10（只有一列）。
二維陣列，我們可以理解為一個矩陣。例如：A1：F7，就是一個7行6列的陣列。
2、存在形式
常見的陣列存在形式有：
常量陣列、區域陣列、記憶體陣列
常量陣列簡單來說，就是由直接的數位或其他類型的元素組成，沒有儲存格引用，直接參與公式的運算。
常量陣列的前後要由{}，即一對大括弧包圍起來。
例如：
={1,2,3;"A","B","C";"陳","郭","王"}
區域陣列較容易理解，就是我們上面的各種儲存格引用。
=A1:A50
=B5:H20
=C3: G300
……
記憶體陣列一般是指在公式運算中間產生的，臨時的一個陣列。這個陣列不在最終的結果中顯示，但它”曾經存在過“，就存在在記憶體中。
比如一開始提到的例子：
=SUM(B1:B7*C1:C7)
其中在沒有求和以前，B1:B7*C1:C7，這7對分別相乘得到的7個數，組成的這個陣列，就是我們所謂的”記憶體陣列“。
因為最終儲存格中我們並沒有看到這7個數，只看到被SUM函數求和後的結果。
在工作上最常使用的日期函數就是DATE，OFFICE本身把他列為常用十個之一。
DATE 函數會傳回代表特定日期的連續序列值。語法：DATE(year,month,day)
 |
| DATE函數語法 |
下表是根據官方說明整理而成。
 |
| EXCECL_DATE函數例子 |
DATE 函數語法具有下列引數：
Year 必要。year 引數的值可以包含一到四位數。Excel 會依據您電腦所使用的日期系統來解譯 year 引數。依預設，Microsoft Excel for Windows 是使用 1900 日期系統，表示第一個日期是 1900 年 1 月 1 日。
提示: 使用四位數做為 year 引數，以防止不合需要的結果。例如，"07" 表示 "1907" 或 "2007"。四位數的 year 可避免混淆。
如果 year 介於 0 (零) 與 1899 (含) 之間，則 Excel 會為該值加上 1900 以計算年份。例如，DATE(108,1,2) 會傳回 2008 (1900+108) 年 1 月 2 日。
如果 year 介於 1900 與 9999 (含) 之間，則 Excel 會使用該值來做為年份。例如，DATE(2008,1,2) 會傳回 2008 年 1 月 2 日。
如果 year 小於 0 或等於/大於 10000，則 Excel 會傳回 #NUM! 錯誤值。
Month 必要。代表全年 1 到 12 (一月至十二月) 的正或負整數。
如果 month 大於 12，則 month 會將月數加到指定年份的第一個月份上。例如，DATE(2008,14,2) 會傳回代表 2009 年 2 月 2 日的序列值。
如果 month 小於 1，則 month 會從指定年份的第一個月份減去該月數，再加上 1。例如，DATE(2008,-3,2) 會傳回代表 2007 年 9 月 2 日的序列值。
Day 必要。代表整個月 1 至 31 日的正或負整數。
如果 day 大於指定月份的天數，則 day 會將天數加到該月份的第一天上。例如，DATE(2008,1,35) 會傳回代表 2008 年 2 月 4 日的序列值。
如果 day 小於 1，則 day 會從指定月份的第一天減去該天數，再加上 1。例如，DATE(2008,1,-15) 會傳回代表 2007 年 12 月 16 日的序列值。
