亞瑟的升級過程

        最近在學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（只有一列）。       ...