HOWTO · VBA
VBA 中的求和函式
從 VBA 使用 Excel 的 SUM 工作表函數,對一個或多個範圍求和、將結果寫入儲存格,並對陣列或符合條件的值求和。
本頁內容
在 VBA 中使用 Excel 的 SUM 函數時,請呼叫 WorksheetFunction.Sum,並傳入範圍或數值。SUM 是 Excel 工作表函數,透過 WorksheetFunction 物件提供給 VBA 使用;它不是 VBA 獨立內建的函數。下方的完整 VBA 程序可直接複製,但由於設定的執行環境不支援 VBA,因此並未執行。
如何在 VBA 中使用 Excel 的 SUM 函數
一般寫法是 Application.WorksheetFunction.Sum(argument1, argument2, ...)。如果巨集可能在其他工作表處於作用中的情況下執行,請使用完整限定的工作表參照,讓計算始終讀取預期的工作表。下方各範例都使用 ThisWorkbook.Worksheets("Sales");活頁簿中必須有一張名稱為 Sales 的工作表。
加總一個或多個範圍
這些程序會在 Sales 工作表填入範例值、計算總和,並將結果寫入 E2。第一個程序加總 B2:B4(12 + 8 + 5 = 25)。第二個程序將 B2:B3 和 D2:D3 分別作為範圍引數傳入(12 + 8 + 5 + 10 = 35),這就是加總不連續範圍的做法。
單一範圍:
Sub SumOneRange()
Dim ws As Worksheet
Dim total As Double
Set ws = ThisWorkbook.Worksheets("Sales")
ws.Range("B2").Value = 12
ws.Range("B3").Value = 8
ws.Range("B4").Value = 5
total = Application.WorksheetFunction.Sum(ws.Range("B2:B4"))
ws.Range("E2").Value = total
End Sub
兩個分開的範圍:
Sub SumTwoRanges()
Dim ws As Worksheet
Dim total As Double
Set ws = ThisWorkbook.Worksheets("Sales")
ws.Range("B2").Value = 12
ws.Range("B3").Value = 8
ws.Range("D2").Value = 5
ws.Range("D3").Value = 10
total = Application.WorksheetFunction.Sum( _
ws.Range("B2:B3"), ws.Range("D2:D3"))
ws.Range("E2").Value = total
End Sub
加總單一範圍時,Sum 會收到一個 Range 物件;加總兩個範圍時,則會將每個 Range 物件分別作為引數傳入,不要把兩個位址合併成一個範圍字串。在這兩個程序中,將 total 指派給 ws.Range("E2").Value,就會把計算結果存入 E2。
接下來的 Python 範例會示範如何計算數值 12、8 和 5 的總和;它不會呼叫 WorksheetFunction.Sum,也不會執行 Excel 或 VBA。
執行此範例:
values = [12, 8, 5]
total = sum(values)
print(total)
25
將總和寫入工作表儲存格
前面的程序會先將結果存入變數,再指派給目的儲存格的 Value 屬性。一般寫法是先執行 total = Application.WorksheetFunction.Sum(ws.Range("B2:B10")),接著執行 ws.Range("E2").Value = total。在 VBA 中,透過 = 指派即可將結果寫入 Value。將讀取範圍和目的儲存格都完整限定,可避免依賴目前作用中的工作表。
在 VBA 中加總陣列
如果數值已經存放在 VBA 陣列中,使用迴圈可以直接控制累加方式。Array(...) 會傳回 Variant 陣列,因此請將 numbers 宣告為 Variant;在迴圈前將 total 初始化,再從 LBound(numbers) 逐一加總至 UBound(numbers)。這個完整程序會加總出 15,並在 VBA 即時運算視窗中顯示結果。它是在 VBA 中累加數值,不會呼叫 Excel 的 SUM 工作表函數。由於設定的執行環境不支援 VBA,此範例並未執行。
Sub SumArray()
Dim numbers As Variant
Dim total As Double
Dim i As Long
numbers = Array(1, 2, 3, 4, 5)
total = 0
For i = LBound(numbers) To UBound(numbers)
total = total + numbers(i)
Next i
Debug.Print total
End Sub
加總符合條件的值
此程序會將範例值寫入 Sales 工作表的 A2:A6,只加總大於 5 的值,並在 VBA 即時運算視窗中顯示總和(22)。If 陳述式會決定哪些值要加入累計總和。這是在 VBA 中依條件累加。若要依條件加總工作表資料,Excel 也提供 SUMIF;如果更符合需求,可以透過 WorksheetFunction.SumIf 呼叫它。由於設定的執行環境不支援 VBA,此範例並未執行。
Sub SumValuesGreaterThanFive()
Dim ws As Worksheet
Dim total As Double
Dim i As Long
Set ws = ThisWorkbook.Worksheets("Sales")
ws.Range("A2").Value = 2
ws.Range("A3").Value = 6
ws.Range("A4").Value = 9
ws.Range("A5").Value = 4
ws.Range("A6").Value = 7
total = 0
For i = 2 To 6
If ws.Cells(i, 1).Value > 5 Then
total = total + ws.Cells(i, 1).Value
End If
Next i
Debug.Print total
End Sub
常見問題
WorksheetFunction.Sum 的作用是什麼? 它會從 VBA 呼叫 Excel 的 SUM 工作表函數,並傳回所提供數值或範圍的總和。
它可以加總不連續範圍嗎? 可以。如上方範例所示,將每個範圍分別作為引數傳入即可。
SUM 如何處理非數值儲存格? Excel SUM 會忽略參照範圍中的文字和空白儲存格;直接作為引數傳入的值,其型別轉換行為可能不同。如果計算結果取決於混合輸入值的處理方式,請確認輸入內容,並一致地使用範圍。
範圍可以來自不同工作表嗎? 可以。將各工作表參照分別完整限定,例如 Worksheets("North").Range("B2:B10") 和 Worksheets("South").Range("B2:B10"),並將它們作為同一次 SUM 呼叫的引數。