我學習excel與VBA的方式

        最近在學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_善用陣列能縮短程序執行時間

         下面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

Excel_IF函數_十個常用函數之一

        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 是一種資料連線技術，可讓您探索、連接、合併和調整資料來源，以符合您的分析需求。 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函數文章連結

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函數_十個常用函數之一

Excel_Vlookup函數_十個常用函數之一

            Vlookup函數算大家常用來查找比對的函數之一，但也很容易被忽略該注意的部分，倒致無法搜尋到正確的答案。

        

        下圖為微軟官方函數說明:

vlookup函數說明

        下圖是官方2016版excel的教學檔，為了更方便理解稍做修改。

官方2016教學說明修改

        實際例子說明如下，分別用員工編號及姓名查詢對應的電話(查詢完全相符，range_lookup  可設0或False)。

       

• 員工編號:以員工編號為查詢值時，資料範圍設定是A3: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,B3:C10,2,0)

• 查詢範圍未鎖定:如果查詢的範圍在A3:C10建議輸入$A$3:$C$10，避免拖曳公式時變A4:C11，會發生找不到對象。

• range_lookup 省略:省略與模糊查找一樣，如果範圍內沒有該對應的資料，省略的話會造成答案錯誤。

※模糊查找比較適合用在運費計算，有空再分享。

 

每天都要洗一下自己的腦袋

        分享一下李笑來在財富自由之路，關於積極進取型人格的宣言:

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

驚險車禍紀錄

        11/9上班路途中在路上遇到一台對向左轉車，我發現時他已經在我眼前，離不到一個車身距離，心裡大喊完蛋，但腳還是緊緊踩煞車，不過還是撞到了。

        事後看紀錄器才知他是左轉(一度以為他闖紅燈)，可能是A柱造成我發現時已經在眼前，這個開車的老伯沒看直行車就轉，對方後座因為撞擊受了輕傷，有人說因為對方比我嚴重，所以究責可能我高些...，幸好雙方都是皮肉傷，但車子都要報廢了 。

        就這樣人生的第一筆事故發生，還好在第三方責任險有加重+財損，在保險上可以幫上，責任歸屬仍然要看判定，但買了兩年的中古車提早退休，又要多花一筆買車費用...

        原本預計每周分享一篇excel，本周目前還沒有心思，人也因為思慮關係，比平常容易累...

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陣列

 1、維度介紹

                         陣列有維度和尺寸。

                        一維陣列，就是只有一列或者一行的陣列，比如：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函數求和後的結果。