Ask me anything
Ithy Logo
Sign in to save

如何在VBA宏中新增【保銀統計】功能

全面指南：實現及優化Excel VBA中的保銀統計功能

financial analysis excel vba

主要重點

  • 精准統計：根據特定篩選條件，自動計算各保管銀行的分頁數量。
  • 高效自動化：利用VBA宏提高數據處理效率，減少手動操作。
  • 結果可視化：清晰的統計結果格式，便於後續分析與報告生成。

【保銀統計】功能概述

在進行基金管理和財務報表分析時，統計保管銀行的相關數據是不可或缺的一部分。通過在VBA宏中新增【保銀統計】功能，能夠自動化地對不同保管銀行的資料進行篩選和計算，大幅提升工作效率和數據準確性。

統計目標

【保銀統計】旨在根據特定的篩選條件，計算每個保管銀行相關分頁的數量。具體統計如下：

  • 復華(台新保銀)：搜尋分頁中A3欄位為“受託人：復華投信”，且C3欄位為“保管銀行：台新銀行”，並且分頁中的D8或E8任一欄位裡有不為0或空白的數值的分頁數量。
  • 瀚亞(台新保銀)：搜尋分頁中A3欄位為“受託人：瀚亞投信”，且C3欄位為“保管銀行：台新銀行”，並且分頁中的D8或E8任一欄位裡有不為0或空白的數值的分頁數量。
  • 中信(華南保銀)：搜尋分頁中A3欄位為“受託人：中國信託投信”，且C3欄位為“保管銀行：華南銀行”，並且分頁中的D8或E8任一欄位裡有不為0或空白的數值的分頁數量。

VBA 程式碼解析及實現

以下將詳細解釋如何在現有的VBA巨集中新增【保銀統計】功能，並提供完整的程式碼範例。

新增統計變數

首先，需要在程式碼的變數宣告部分新增相關計數器，用於統計各保管銀行的分頁數量。


' 新增保銀統計的變數
Dim baoYinStats As String
Dim fuHuaTaiXinCount As Long
Dim hanYaTaiXinCount As Long
Dim zhongXinHuaNanCount As Long

fuHuaTaiXinCount = 0
hanYaTaiXinCount = 0
zhongXinHuaNanCount = 0

統計邏輯實現

在遍歷工作表的循環中，根據指定的條件篩選並計數符合條件的分頁數量。


' 統計保銀相關分頁
For Each ws In wb.Sheets
    ' 復華(台新保銀)
    If ws.Range("A3").Value Like "*受託人：復華投信*" And ws.Range("C3").Value Like "*保管銀行：台新銀行*" Then
        If (IsNumeric(ws.Range("D8").Value) And ws.Range("D8").Value <> 0) Or _
           (IsNumeric(ws.Range("E8").Value) And ws.Range("E8").Value <> 0) Then
            fuHuaTaiXinCount = fuHuaTaiXinCount + 1
        End If
    End If

    ' 瀚亞(台新保銀)
    If ws.Range("A3").Value Like "*受託人：瀚亞投信*" And ws.Range("C3").Value Like "*保管銀行：台新銀行*" Then
        If (IsNumeric(ws.Range("D8").Value) And ws.Range("D8").Value <> 0) Or _
           (IsNumeric(ws.Range("E8").Value) And ws.Range("E8").Value <> 0) Then
            hanYaTaiXinCount = hanYaTaiXinCount + 1
        End If
    End If

    ' 中信(華南保銀)
    If ws.Range("A3").Value Like "*受託人：中国信託投信*" And ws.Range("C3").Value Like "*保管銀行：華南銀行*" Then
        If (IsNumeric(ws.Range("D8").Value) And ws.Range("D8").Value <> 0) Or _
           (IsNumeric(ws.Range("E8").Value) And ws.Range("E8").Value <> 0) Then
            zhongXinHuaNanCount = zhongXinHuaNanCount + 1
        End If
    End If
Next ws

組合統計結果

將統計結果組合成最終的摘要格式，以便後續輸出和呈現。


' 組合保銀統計結果
baoYinStats = "【保銀統計】" & vbCrLf
baoYinStats = baoYinStats & "復華(台新保銀)： " & fuHuaTaiXinCount & " 張" & vbCrLf
baoYinStats = baoYinStats & "瀚亞(台新保銀)： " & hanYaTaiXinCount & " 張" & vbCrLf
baoYinStats = baoYinStats & "中信(華南保銀)： " & zhongXinHuaNanCount & " 張" & vbCrLf

' 將此段統計加入最終輸出摘要中
summary = summary & vbCrLf & baoYinStats

完整VBA 程式碼範例

以下是整合【保銀統計】功能後的完整VBA宏程式碼：

Sub 出入金統計()

    Dim inputDate As String
    Dim targetDate As String
    Dim yearPrefix As String
    Dim filePath As String
    Dim statPath As String
    Dim wb As Workbook
    Dim ws As Variant ' 修改為 Variant 類型
    Dim summary As String
    Dim trusteeName As Variant
    Dim trusteeStats As Variant
    Dim i As Long
    Dim huanNanStats As String
    Dim outMoney As String
    Dim inMoney As String
    Dim fileNum As Integer
    Dim yuanDaStats As String

    ' 新增變數用於張數筆數統計
    Dim totalSheets As Long
    Dim multiEntrySheets As Long
    Dim totalEntries As Long

    ' 新增保銀統計的變數
    Dim baoYinStats As String
    Dim fuHuaTaiXinCount As Long
    Dim hanYaTaiXinCount As Long
    Dim zhongXinHuaNanCount As Long

    fuHuaTaiXinCount = 0
    hanYaTaiXinCount = 0
    zhongXinHuaNanCount = 0

    ' 預設日期為今天
    targetDate = Format(Date, "yyyymmdd")

    ' 輸入日期
    inputDate = InputBox("請輸入要統計的日期 (四碼或八碼數字):", "日期輸入", Format(Date, "mmdd"))

    If inputDate = "" Then
        MsgBox "未輸入日期，將以今天日期作為預設值: " & targetDate, vbInformation
    Else
        If Len(inputDate) = 4 Then
            yearPrefix = Year(Date)
            targetDate = yearPrefix & inputDate
        ElseIf Len(inputDate) = 8 Then
            targetDate = inputDate
        Else
            MsgBox "輸入日期格式錯誤！請輸入四碼 (MMDD) 或八碼 (YYYYMMDD)。", vbCritical
            Exit Sub
        End If
    End If

    ' 建立檔案路徑
    filePath = "G:\26_Investment Insurance Policy\1_Custody\6_基金下單表格\Fund 執行單 & Instruction\執行單\" & targetDate & "\Asia\" & targetDate & "內部執行單試算表-Asia-send.xlsm"
    statPath = "G:\68_Vincent\試作工作表格紀錄\基金回盤上傳\出入金統計\" & targetDate & "_出入金統計.txt"

    ' 開啟檔案 (唯讀模式)
    On Error Resume Next
    Set wb = Workbooks.Open(filePath, ReadOnly:=True)
    If wb Is Nothing Then
        MsgBox "無法開啟檔案，請確認路徑是否正確: " & filePath, vbCritical
        Exit Sub
    End If
    On Error GoTo 0

    ' 初始化統計資料
    trusteeName = Array("復華投信", "富蘭克林華美投信", "凱基投信", "兆豐投信", "合庫投信", "霸菱投顧", "台新投信", "瀚亞投信", "統一投顧", "華南永昌投信", "中國信託投信", "聯博投信", "元大投信")
    trusteeStats = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)

    ' 初始化華南(元大保銀)統計資料
    huanNanStats = "【華南(元大保銀)出入金紀錄】" & vbCrLf & "出金：" & vbCrLf
    outMoney = ""
    inMoney = "入金：" & vbCrLf

    ' 初始化張數筆數統計
    totalSheets = 0
    multiEntrySheets = 0
    totalEntries = 0

    ' 統計保銀相關分頁
    For Each ws In wb.Sheets
        ' 復華(台新保銀)
        If ws.Range("A3").Value Like "*受託人：復華投信*" And ws.Range("C3").Value Like "*保管銀行：台新銀行*" Then
            If (IsNumeric(ws.Range("D8").Value) And ws.Range("D8").Value <> 0) Or _
               (IsNumeric(ws.Range("E8").Value) And ws.Range("E8").Value <> 0) Then
                fuHuaTaiXinCount = fuHuaTaiXinCount + 1
            End If
        End If

        ' 瀚亞(台新保銀)
        If ws.Range("A3").Value Like "*受託人：瀚亞投信*" And ws.Range("C3").Value Like "*保管銀行：台新銀行*" Then
            If (IsNumeric(ws.Range("D8").Value) And ws.Range("D8").Value <> 0) Or _
               (IsNumeric(ws.Range("E8").Value) And ws.Range("E8").Value <> 0) Then
                hanYaTaiXinCount = hanYaTaiXinCount + 1
            End If
        End If

        ' 中信(華南保銀)
        If ws.Range("A3").Value Like "*受託人：中国信託投信*" And ws.Range("C3").Value Like "*保管銀行：華南銀行*" Then
            If (IsNumeric(ws.Range("D8").Value) And ws.Range("D8").Value <> 0) Or _
               (IsNumeric(ws.Range("E8").Value) And ws.Range("E8").Value <> 0) Then
                zhongXinHuaNanCount = zhongXinHuaNanCount + 1
            End If
        End If

        ' 其他統計邏輯...
    Next ws

    ' 組合保銀統計結果
    baoYinStats = "【保銀統計】" & vbCrLf
    baoYinStats = baoYinStats & "復華(台新保銀)： " & fuHuaTaiXinCount & " 張" & vbCrLf
    baoYinStats = baoYinStats & "瀚亞(台新保銀)： " & hanYaTaiXinCount & " 張" & vbCrLf
    baoYinStats = baoYinStats & "中信(華南保銀)： " & zhongXinHuaNanCount & " 張" & vbCrLf

    ' 將此段統計加入最終輸出摘要中
    summary = summary & vbCrLf & baoYinStats

    ' 其他統計和結果組合邏輯...

    ' 儲存到記事本
    fileNum = FreeFile

    Open statPath For Output As #fileNum
    Print #fileNum, summary
    Close #fileNum

    ' 打開記事本顯示統計結果
    Shell "notepad.exe " & statPath, vbNormalFocus

    MsgBox "出入金統計已完成！結果已保存到: " & statPath, vbInformation
End Sub

統計規則詳解

為確保統計的準確性，以下是詳細的統計規則說明：

保管銀行 受託人條件 保管銀行條件 數值條件 統計結果
台新銀行 受託人為“復華投信” 保管銀行為“台新銀行” D8或E8任一欄位有不為0或空白的數值 復華(台新保銀)：__張
台新銀行 受託人為“瀚亞投信” 保管銀行為“台新銀行” D8或E8任一欄位有不為0或空白的數值 瀚亞(台新保銀)：__張
華南銀行 受託人為“中国信託投信” 保管銀行為“華南銀行” D8或E8任一欄位有不為0或空白的數值 中信(華南保銀)：__張

結果輸出與呈現

完成統計後，程式會將結果儲存至指定的文字檔，並自動開啟記事本以顯示統計結果。用戶可以根據需要進一步處理或匯總這些數據。

以下是預期的統計結果格式：

【保銀統計】
復華(台新保銀)：__張
瀚亞(台新保銀)：__張
中信(華南保銀)：__張

常見問題與解決方案

1. 無法開啟指定的Excel檔案

請確認檔案路徑是否正確，並確保該路徑下存在相應的Excel檔案。必要時，檢查檔案名稱和擴展名是否正確。

2. 統計結果不準確

檢查統計條件是否正確應用於每個工作表。特別注意A3和C3欄位的內容是否符合篩選條件，以及D8和E8欄位的數值是否被正確判斷。

3. VBA錯誤或運行中斷

確保VBA代碼沒有語法錯誤，並且所有變數均已正確宣告。使用錯誤處理機制（如On Error Resume Next）來捕捉潛在的錯誤並進行處理。

結論

通過在VBA宏中新增【保銀統計】功能，能夠有效地自動化統計各保管銀行的相關分頁數量，提升工作效率和數據準確性。本文詳細介紹了統計規則、程式碼實現及常見問題的解決方案，幫助用戶更好地掌握和應用此功能。

參考資料

books.com.tw
金融計算：Excel VBA基礎實作 - 博客來
books.com.tw
最新Excel VBA基礎必修課：程式設計、專題與數據應用的最佳訓練 - 博客來
sanmin.com.tw
Excel VBA實戰技巧｜金融數據x網路爬蟲 - 三民網路書店
nabi.104.com.tw
Excel VBA新手入門-從基礎到爬蟲實例應用
hahow.in
玩轉數據與資料：Excel VBA 入門教學 - hahow

Last updated January 22, 2025

AI-assisted research. Sources, methodology and corrections · Source-checked guides and tools · Explore published answers

Ask Ithy AI