亞瑟的升級過程

              今天(2020/10/12)突然驚覺今年已經快結束了，距離年底有2個多月，還是有很多時間能學習。提前做個回顧，順便提醒自己多學習。          今年算很順利，有些是自己持續替自己洗腦完成，有些是其他事件推了一把。         大致上能以否極泰來形容2020年，就剩車禍理賠了。 2020年1月開了海外帳戶。 2020年7月動了小手術。 2020年7月將穩定收息方式全轉為成長股。 2020年8月開始寫網誌。 2020年11月 車禍，車子報廢... 2020年11月美股超額成長股大部分轉為ETF。 2020年12月車禍對方理賠還不盡快處理

         Power Query 裡排序的方式，目前已知加非正規的方式共三種。 第一種：在要移動的標題欄點選移動           在標題欄 按住滑鼠左鍵 ，然後移動到目的地再放開。 第二種：修改M語言(Power Query的公式)                              原資料排序如附圖A→B→C→D。 Power Query 原資料 由進階編輯中修改M語言，調整欄位順序。           剛學習時是由查詢表中做修改，但有些步驟只能透過M語言修改。 第三種: 依序點選想要排序的順序，然後選擇移除其他行 (沒有需要移除的也能用) Power Query 移除其他行排序          

            在線上申請的初步分析研判表，終於在兩個星期後看到了，正如在保險公司的學弟說的，不是從事保險的看不出責任比例，保險人員都看得出比例...，我的這件 對方責任7 ，可能因為對方責任高，對方理賠 看到研判表才質疑 (1個月後) 我為何要報廢不修。          一開始給對方估價單時，對方理賠人員都不講話，現在才說為何市值高於維修卻不修，對於2005年的梅基估10萬維修竟然說市值高於維修...，我問 對方 市值多少說 8萬 ...，問他有不賠的嗎? 為何說市值高於維修??            原來他論點是估價都會比較高...，後來我的保險員打給對方， 卻又回說市值約10萬 ，老實說整個理解後我對這間 常在打廣告的保險公司 很多疑問，後來考慮給原廠再估價(讓對方沒理由)，我都懷疑這個理賠員會不會跟員場串通報修低價，然後讓我的車子出一堆狀況...         我的業務員說對方只會比較嚴格不會來暗的，那就找時間來估價吧...               常在打多快速到第一現場的保險公司，我真的對你們理賠員的人格打很多問號阿...，完全讓我把你們公司剔除在投保名單中。          PS.原本還在找二手車，現在就看原廠估價狀況

             現在正值大部分公司還在編預算時，下班時剛好想到固定成本對預算的影響，有些公司可能在編預算時為了簡單就忽略固定成本的影響，如果該公司在機器設備投入很多，那不同的產量預估對固定成本影響就不是一般。          在只會VBA還沒遇到Power Query時，想到用VBA去寫一個計算產品在原料不同價格影響，腦袋中模擬結果是要寫很多程式碼，換成Power Query感覺相關步驟變簡單，下面是我在相關運用的想法。             主要是以不同產量單位成本影響大(固定成本佔比大)為出發點思考，因此偏向於產品成本計算。 計算不同產量下的產品成本 用Power Query將不同階串起來(不同階單獨一個查詢表) 須考量不同產量的單位固定成本變化 如果要比較主要原料不同價格影響，也要在這邊設定 原料階需考慮主要原料不同單價 半成品、產品階需要考慮單位固定成本 如果來源資料格式很亂，可能就需要出動VBA處理 將銷售預測成本資料連結各種狀況之成本 Power Query強大資料處理能力，而且 沒有數據量的限制 連結資料都以儘連接方式處理( 銷售預測資料與產品成本合併 ) 最後將運算好的資料分組匯總導入至工作表中，可以讓檔案小很多，運算速度快 產品成本多版本的好處 預先算出不同產量(包含最差)、單價影響。實際發生時不用編第二版，可以預先知到影響程度。 投標時可以知道得標影響、投標底限

         這次要學習的是附加查詢，可以將兩個相同結構的資料串在一起，譬如不同月份、不同單位的同樣欄位資料串在一起。           這裡使用上次提到的資料 ( Power Query_00合併  )來做到一樣的結果，會做到到附加查詢、轉樞紐資料。 1. 顯示窗格 ，將上次做的查詢編輯呼叫出來。 Power Query 顯示窗格 2.選擇上月，之後本月資料與上月同樣方式完成。 Power Query查詢表 3.Power Query 新增資料行，增加一個辨識資料的欄位，在此輸入可以辨識資料的 上月 ，另一個表格使用相同步驟完成新增資料行。 (資料名稱請用月份，下圖不作修改) 4.完成資料如下圖，在此介紹一個類似電影回到未來一樣的東西: 檢視表→查詢設定 。 在這裡可以看到完成過的步驟，點每一個可以看到當下的完成的情況，允許修改還有額外作其他的動作，在這裡只稍微提到，有空在作多一點介紹。 Power Query 查詢表(回到過去) 5.附加查詢有兩個地方可以作     A.資料→新查詢→結合查詢→附加(在另一個表附加)     B.Query Editor(編輯器)→附加查詢(在已存在的表中附加) 某些情況適合在編輯器中作，可以讓查詢的表不要太多，有時需要分開，請視情況。 Power Query 編輯器內作附加查詢 在這裡是將上個月的資料串到本月中。 資料直接串起來 6.將資料作為樞紐格式    選取要當資料的欄位→樞紐資料行之後會問選擇哪一個當 值，剩下的欄位就會變成項目 Power Query樞紐資料行 7.新增資料行比較差異(與之前合併查詢作的動作相同) Power Query新增資料行 ※會出現驚嘆號，是因為之前作的合併查詢的來源(本月)格式變動了。(變動，且本月與上月的格是不一樣了) 8.與上次作的合併查詢比較 Power Query 合併查詢與附加查詢比較 ※結果與合併查詢一樣，不過因為使用的是被查詢的本月，所以之前的合併查詢表出現驚嘆號。 ※可以自己試著用另外一種方式的附加查詢完成，不過可能要先回去本月做回到過去的動作，恢復原動作，直接刪掉這次新增的動作就可以。

         Power Query才剛學不久，不過簡單的介面功能算是摸出了一些，在這裡分享一下第一個使用的方式，剛好那天同事也有問我一個可以派上用場的問題。 下面步驟看起來似乎很困難，主要是第一次介紹將步驟仔細介紹，實際上用久了很多步驟並不難，Power Query可以處理很大資料量，讀取上也比SQL外部連結快多了，如果要處理資料多的人建議學學Power Qurey。 選取資料→從表格(資料) 選取資料由資料表建立Power Qurery 確定範圍→建立表格 選取資料由資料表建立Power Qurery 按確定後會進入查詢編輯器( 不同版本看到的畫面似乎會不太一樣 ) 進入查詢編器 修改表屬性名稱，方便識別不同來源 修改表格名稱  選取資料建立另一表格(建立步驟請詳上面內容) 選取資料建立另一表格 Power Query 選擇僅連接 Power Query 載入至選項→選擇僅連接 合併:新查詢→結合查詢→合併 ※連結種類會因版本不同顯示的選項也會有差異 Power Query 合併 各選擇一個表作為合併，簡單版本勾選要完全相符，以第一個表為準(office完整版本選項豐富一些)。 Power Query 合併 Power Query 編輯器(Query Editor),不同版本顯示的名稱也稍不同 紅色框框就是本月資料，綠色框框點選資料能展開選擇要顯示的欄位 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代碼只能篩選欄位，不支援其他命令

        最近在學power query，讓我回想N年前學習excel函數跟VBA的感覺，就趁這機會做個紀錄，作為下次學習其他工具的參考。           紀錄每次學到的知識及時間。(如迴圈、字典...等)          把想做但還沒辦法做到的先紀錄，學習過程時順便找答案。 設定比較難的目標，當初學VBA 字典這個是我最後學會的，因為非資訊底子，所以需要花時間學習。 常使用，不管函數還是VBA常使用才有辦法活用，否則只能停在基礎運用，常用了之後就能把不同的功能串在一起。 除了看書，網路上高手教學、論壇都是一個不錯學習來源。 A工具(函數)會的方式想辦法以B工具(VBA)完成。現在學習Power Query，我就會想VBA能完成的，在Power Query 是不是也能完成。

                 兩個網站當初是同時送，一個有持續更新到，另一個一個月沒更新了，現在重新送出網站地圖，過一陣子再看看是不是正常了。  網站地圖  https://www.labnol.org/blogger/sitemap/ 在 Generate  Sitemap 輸入網址，就能產生網站地圖，然後再回網誌設定更新網站地圖。 網站地圖搜尋 網站地圖搜尋 兩個網站同時送，但google通知卻差兩天。 學習網誌系統蒐尋建立通知 投資網誌系統蒐尋建立通知

            下面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])),""...

         IF函數是函數中最常使用中的一個，下列介紹幾種用法，根據同事常詢問的情形， 區間判斷 的寫法比較容易出錯，使用上稍微 注意一下順序 就可以避免錯誤。 單條件判斷 ： 判斷的條件較少，通常較不容易出錯         符合有房就嫁，否則不嫁，判斷條件只有一個 有房  。           =IF(B2="有房","嫁","不嫁") IF單條件判斷 (2021/5/12修正: 感謝玲姊發現錯誤 ) 多條件判斷 ：判斷的項目變多，但公式架構仍然一樣。 = IF(B2="有房","嫁" , IF(C2="有車","嫁" ,IF(E2>170,"嫁","不嫁" ))) =IF(OR(B2="有房",C2="有車",E2>170),"嫁","不嫁") 以上兩各式子結果一樣。 IF多條件判斷 說明:有房有車還要身高170才嫁。 =IF(AND(B2="有房",C2="有車",E2>170),"嫁","不嫁") 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 Query 中的功能可在 Excel 和 Power BI 桌面中取得。          excel使用版本:         2010 跟 2013都需要透過官方元件下載，2016版以後直接內建。 如果是要使用Power Pivot 要求就高了請看官方說明    Power Pivot 在哪裡 ？。          下表就是目前我對於Power Query 及 Power Pivot的初步理解。 Power Query 及 Power Pivot比較           簡單說，如果是要以資料形態顯示，就以Power Query處理，微軟已經把很多資料處理的功能簡化，讓原本要寫VBA或很多函數的方式可以用Power Query很簡單的處理。 PS.要開始學Power Query，現在就一步一步的記錄。

           今天在社群看到一位前輩分享他從 本國銀行分行授信辦事員~外商銀行台灣子行稽核副總裁(共經歷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 能讓稽核工作更加順手，當你各樣工具使用到某種程度，把他們串在一起，那就是屬於你的特有專長。          千萬不要認為工作...

Excel_CEILING、FLOOR、MROUND倍數函數 Excel_INT及TRUNC函數的介紹 Excel_round、roundup、rounddown三兄弟，四捨五入、無條件進位、無條件捨去 Excel_SUBTOTAL 函數可以用來檢查有沒有計算隱藏資料 Excel_SUMPRODUCT 函數 多條件計算 Excel_SUMIF函數常用方式 Excel_Vlookup函數_十個常用函數之一 Excel_SUM函數常用方式-十個常用函數之一 Excel_DATE函數_十個常用函數之一 Excel_MATCH函數_十個常用函數之一

              Vlookup函數算大家常用來查找比對的函數之一，但也很容易被忽略該注意的部分，倒致無法搜尋到正確的答案。                     下圖為微軟官方函數說明: vlookup函數說明          下圖是官方2016版excel的教學檔，為了更方便理解稍做修改。 官方2016教學說明修改         實際例子說明如下，分別用員工編號及姓名查詢對應的電話( 查詢完全相符， range_lookup  可設0或False )。          員工編號 :以 員工編號 為查詢值時，資料範圍設定是 A 3:C10，因為員工編號在 A欄 ，電話在C欄，由 員工編號數來要查詢的電話在第3欄 。公式輸入=VLOOKUP(A14,A3:C10,3,0) 姓名 :以 姓名 為查詢值時，資料範圍設定是B3:C10，因為 姓名在B欄 ，電話在C欄，由 姓名數來要查詢的電話在第2欄 。公式輸=VLOOKUP(A15,B3:C10,2,0) 常見錯誤： 查詢的範圍首欄非查詢對象 ：如果你要以姓名查詢，範圍首欄就是B而非A。 =VLOOKUP(A15, B 3:C10,2,0) 查詢範圍未鎖定: 如果查詢的範圍在A3:C10建議輸入$A$3:$C$10，避免拖曳公式時變A4:C11，會發生找不到對象。 range_lookup 省略 :省略與模糊查找一樣，如果範圍內沒有該對應的資料，省略的話會造成答案錯誤。 ※模糊查找比較適合用在運費計算，有空再分享。  

          分享一下李笑來在財富自由之路，關於積極進取型人格的宣言: 學習其實是一種生活方式;學習本身就是最好的洗腦方式。  只要我投入時間和精力，從長期來看，沒有什麼是我學不會的。  我學會的東西越多，我在學新的東西時速度就越快。  學習不是目的，"用起來"才是，因為價值只能通過創造實現。 我知道自己現在看起來很笨拙，但剛開始誰都是這樣，實踐多了，就自然了，也就自然地好起來了。 在學習這件事，別人不理解我是正常的;在這方面我也不需要別人理解，因為我是一個獨立的人。 我不應該與別人爭辯，因為我不想傷害他們; 我也不應該被他們影響，因為我不想傷害自己。 創意練習永遠是必要的，雖然它通常並不舒適，但它的複利效應確實是巨大的。 哪怕是為了下一代，我也要通過現在的努力成為學習專家，這樣我才有資格和我的孩子共同成長。 我的路還很長，我要健康，我要乾淨;尤其是我的腦子，更要"乾淨" 。

         11/9上班路途中在路上遇到一台對向左轉車，我發現時他已經在我眼前，離不到一個車身距離，心裡大喊完蛋，但腳還是緊緊踩煞車，不過還是撞到了。          事後看紀錄器才知他是左轉(一度以為他闖紅燈)，可能是A柱造成我發現時已經在眼前，這個開車的老伯沒看直行車就轉，對方後座因為撞擊受了輕傷，有人說因為對方比我嚴重，所以究責可能我高些...，幸好雙方都是皮肉傷，但車子都要報廢了  。          就這樣人生的第一筆事故發生，還好在第三方責任險有加重+財損，在保險上可以幫上，責任歸屬仍然要看判定，但買了兩年的中古車提早退休，又要多花一筆買車費用...          原本預計每周分享一篇excel，本周目前還沒有心思，人也因為思慮關係，比平常容易累...

         陣列 公式挺常在excel運用中看到，但對於不是學程式的初學者有點難理解，當初在學習時也是看很久，才慢慢理解，透過大量的例子就能了解相關運用。          什麼是陣列？           簡單來說，就是“一組數”。一般公式用的就是“一個數”。舉個例子來說，在C1儲存格中輸入：=A1*B1 這裡的“A1”和“B1”都是“一個數”。           輸入：=A1:A7*B1:B7           A 1: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陣列   1、維度介紹                                 陣列有維度和尺寸。                               一維陣列，就是只有一列或者一行的陣列，比如：A1:D1（只有一行），B2:B10（只有一列）。       ...

          在工作上最常使用的日期函數就是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，則 Exce l 會傳回 #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 至 ...

         打從開始寫網誌時，我就有注意搜尋的相關設定，不過試了幾個方式都無法在一星期內搜到，心裡是有點小難過，不過我相信總有一天能搜到。           我也沒有一直往搜尋方面鑽，畢竟網誌內容是一個學習紀錄，也是打算用來push自我學習的一個動機  ，也想做為以後同事問我相關問題時可以直接PASS出去的一個內容。但就在昨天想說來查一下改過網誌主要標題後的結果如何，沒想到已經能搜到了，但搜尋文章標題還無法搜到，我想文章的標題蒐尋之後再慢慢研究，凡是總有一個起頭。