VBA_反樞紐

             在工作上看過很多人習慣用表格呈現資料，不過不是以樞紐表，而是用輸入的方式，這在分析運用上很沒彈性，剛好有一個表格要資料化，本來想用Power Query處理，後來還是決定寫VBA，避免Power Query會一直重複查詢，而且很多人喜歡把不同部門資料放在同一個工作表內，這時還是用VBA比較快。

           在家裡弄了個簡單版，隔天上工可以快速修改用上。

Sub A0_反樞紐()

    '2022/11/24 22:35

    Dim arr_資料()

    With ActiveSheet

        br = 3

        lr = .Cells(br, 1).End(xlDown).Row

        lc = .Cells(br, 1).End(xlToRight).Column

        n = 0

        For i = br To lr

            in_代碼 = .Cells(i, 1)

            in_名稱 = .Cells(i, 2)

            For c = 1 To 16 '12個月+4季

                in_月份 = .Cells(1, c * 2 + 1)

                in_噸數 = .Cells(i, c * 2 + 1)

                in_金額 = .Cells(i, c * 2 + 2)

                

             n = n + 1

             ReDim Preserve arr_資料(1 To 5, 1 To n)

             arr_資料(1, n) = in_代碼

             arr_資料(2, n) = in_名稱

             arr_資料(3, n) = in_月份

             arr_資料(4, n) = in_噸數

             arr_資料(5, n) = in_金額

            Next

        Next

    

        .Range("A19").Resize(n, 5) = Application.Transpose(arr_資料)

    

    End With

End Sub

• 很多人喜歡用的表格方式，這裡簡單的表達一下

類樞紐表格

• VBA執行完就能呈現如下面的資料

表格資料化

• 執行完作了個驗證，確認資料正確

比對資料

Power Query_避免重複查詢_Power BI desktop版

 

• 如果沒有強大的電腦資源，處理太大量資料
• 避免重複查詢(Desktop版無法像excel版分檔處理)

        大約10月中在現在的公司開始在使用Power Query，因為excel版本不夠，所以使用desktop版，不過在使用上發生了瑕疵，筆電的資源不足，跑10萬筆以上資料就開始會鈍，在假日時好不容易花了1小時用了複雜查詢，在存檔時也花了1小時，一度要放棄存檔...

       有一句名言當你得不到你想的到的東西時，你會得到經驗，這次我真的是得到經驗了，我花了點時間，將資料比較多的部分改成用SQL擷取，而且是分段處理，因為我在用SQL時如果用比較複雜的子查詢也是卡住，人生不該把時間放在等待...

固定資產_驗收資料核對_主要使用VBA

        這幾天比對驗收報告及財產清冊原本使用Power BI的 Power Query處理，發現無法全部直接對上，後來單純比對品號及品名時發現可能的問題，現在想想這種無法完全1對1的還是要用VBA處理比較快速。

        在電腦效能不夠強的情況下，VBA還是有優勢，不過Power BI+SQL在不夠了解狀況下還是有一定的掃盲，只是SQL跟Power Query 在今天(2022/11/9)讓我的新筆電卡住了。

         VBA將兩份數據做1對1的比對還是耗比較少資源，把兩份數據分別丟進陣列中，對記憶體的使用比SQL跟PQ要少些，PQ很容易一直載入，存檔時跟完成新步驟時都會。SQL則是在子程序卡住了。不過用法上PQ跟SQL還是簡單些。

PS.VBA還沒寫，先記錄，下次比較容易完成。

狀況:

• 有條件驗收日
• 正常驗收日
• 其他驗收日(未知)
• 品號無法對應(某一方未輸入)
• 品名無法對應(財產清冊品名與驗收報告不同)
• 有驗收但不在財產清冊上 VS 財產清冊上有但驗收報告上無(有可能自製入庫)
• 為了支付供應商款項提前領用備品...(每間公司狀況不同)

篩選條件:

• 自製入庫的品號_SQL
• 驗收報告整理_SQL
• 財產目錄(全部)

主要處理方式:1對1

使用VBA，完全比對之後再模糊比對

以前使用過的地方:

• 比對運費
• 二十大營運分析 (費用對應)
• 附註費用