SUMPRODUCT函數詳解(4)

SUMPRODUCT函數詳解(4)

在VBA中的條件統計和求和

到目前為止，我們關于條件公式的所有討論都是直接在Excel工作表中。有時，我們需要在VBA程序中對一些工作表單元格進行條件統計和求和。在這些情形下，編寫簡單的循環來遍歷所有數據并檢查它們是否與條件相匹配，統計匯總相匹配的項。

Excel VBA允許在程序中調用內置的工作表函數，避免讓我們再重復創建這些功能，極大地改善了VBA代碼的能力。當在VBA中調用Excel函數時，如果不過度使用，那么任何的性能影響應該是最小的，優勢也很明顯。我們能夠利用這種優勢很容易地在VBA中獲取條件統計和求和，但是要注意一些事項。

作為示例，考慮下表中的數據（與上文中表相同）。

如果需要使用VBA程序求單元格區域A1:A10有多少“Ford”，程序代碼如下：

Dim mModel As String

Dim mCount As Long

mModel = “Ford”

mCount =Application.WorksheetFunction.CountIf(Range(“A1:A10”), mModel)

此時，”Ford”的數量將被賦值給mCount變量，其值為4。

同樣，我們可以使用SUMIF來計算”Ford”所對應的價格和：

Dim mModel As String

Dim mValue As Long

mModel = “Ford”

mValue =Application.WorksheetFunction.SumIf(Range(“A1:A10”), mModel,Range(“C1:C10”))

此時，”Ford”相對應的價格之和被賦值給變量mValue，其值為33873。

接下來，假設我們能擴展這種技術到上文中已討論的多條件測試公式。例如，統計在六月份（June）銷售了多少Ford，代碼如下：

Dim mModel As String

Dim mMonth As String

Dim mCount As Long

mModel = “Ford”

mMonth = “June”

mCount =Application.WorksheetFunction.CountIfs(Range(“A1:A10”), _

mModel, Range(“B1:B10”),mMonth)

代碼將結果3賦值給變量mCount。不幸的是，這項技術不能被擴展到數組公式或者條件測試SUMPRODUCT公式。

例如，下面是統計在五月（May）有銷售了多少Ford的公式：

=SUMPRODUCT((A2:A10=”Ford”)*(B2:B10=”Feb”))

你可能想使用下面的VBA代碼得到相同的結果：

Dim mModel As String

Dim mMonth As String

Dim mCount As Long

mModel = “Ford”

mMonth = “May”

mCount =Application.WorksheetFunction.SumProduct( _

Range(“A1:A10”) = mModel,Range(“C1:C10”) = mMonth)

然而，在編譯時會得到錯誤消息。在這種情況下，VBA試圖簡單地調用工作表函數，但并不會評估單元格區域并傳遞正確的數組信息到工作表函數中。

下面是該問題的解決方案。在VBA中使用Evaluate方法評估函數調用，轉換Excel名稱為值。代碼如下：

Dim mModel As String

Dim mMonth As String

Dim mFormula As String

Dim mCount As Long

mModel = “Ford”

mMonth = “May”

mFormula =”SUMPRODUCT((A1:A10=””” & mModel & _

“””)*(B1:B10=””” & mMonth &”””))”

mCount = Application.Evaluate(mFormula)

雖然需要更多的努力來確保合適地構建函數調用的正確語法，并且合適地使用引號來確保字符串被引號括住，但是這仍然是一項有用的技術，提供了在VBA中使用SUMPRODUCT函數的能力。

Excel 2007及以上版本中的SUMPRODUCT

在Microsoft引入Excel 2007時，主要集中在容易使用以及改善商務分析功能。不幸的是，工作表函數沒有得到太多的關注，只是引入了一些新的函數，其中的兩個新函數：COUNTIFS和SUMIFS支持多條件測試。

例如，在我們前面的示例中：

=SUMPRODUCT((A1:A10=”Ford”)*(B1:B10=”June”))

=SUMPRODUCT((A1:A10=”Ford”)*(B1:B10=”June”)*(C1:C10))

我們統計單元格區域A1:A10中的內容為“Ford”并且單元格區域B1:B10中的內容為June的項目數，以及統計單元格區域A1:A10中的內容為“Ford”并且單元格區域B1:B10中的內容為June并將單元格區域C1:C10相應單元格求和。在Excel 2007及以上版本中，可以使用COUNTIFS函數和SUMIFS函數代替SUMPRODUCT函數，相應的公式為：

=COUNTIFS(A1:A10,”Ford”,B1:B10,”June”)

=SUMIFS(C1:C10,A1:A10,”Ford”,B1:B10,”June”)

在Excel 2007中，SUMPRODUCT函數的改進是可以取整列的地址。在Excel開發者工具庫中，SUMPRODUCT函數仍然保留著其獨特的位置，因為COUNTIFS函數和SUMIFS函數仍然不能夠計算已關閉工作簿中的值。

性能分析

雙目運算符（–）與*運算符

在大多數情形下，可以使用SUMPRODUCT函數的“*”或”–“版本，并且都能得到正確的功能。然而，也有一些例外。考慮在單元格區域A1:B10中是一個包含姓名和數量的表，其中第一行是文本標題“Name”和“Amount”。公式：

=SUMPRODUCT(–(A1:A10=”Bob”),–(B1:B10)>0),B1:B10)

將正確地計算列A中姓名是“Bob”且列B中為正值的和。然而，公式：

=SUMPRODUCT((A1:A10=”Bob”)*(B1:B10>0)*(B1:B10))

將返回#VALUE!錯誤。錯誤的原因是由于B1中是文本，乘以文本值導致錯誤。為了解決錯誤，單元格區域不能含標題單元格，應以單元格A2和B2開始。

類似地，如果公式中的一個或多個單元格區域包含多列，則必須使用“*”運算符，而下面的公式將不能運行：

=SUMPRODUCT(–(A1:A10=”Bob”),–(B1:C10>0),–(B1:C10))

下面的公式工作得很好：

=SUMPRODUCT((A1:A10=”Bob”)*(B1:C10>0)*(B1:C10))

事實上下面的公式也可以：

=SUMPRODUCT((A1:A10=”Bob”)*(B1:C10>0),B1:C10)

使用轉置

如果在SUMPRODUCT函數中使用TRANSPOSE函數，那么必須使用“*”運算符。

公式效率

很多人都知道使用數組公式要付出高的代價，如果過多地使用將明顯減弱工作表/工作簿的重新計算速度。

雖然SUMPRODUCT函數不是數組公式，然而它也面臨同樣的問題。雖然SUMPRODUCT函數通常比等價的數組公式更快，但與數組公式一樣，SUMPRODUCT函數比COUNTIF函數和SUMIF函數更慢，因此如果合適的話使用這些函數會更好。

因此，在下面的情形下，不要使用SUMPRODUCT函數：

=SUMPRODUCT((A1:A10=”Ford”)*(C1:C10))

而是使用等價的SUMIF函數：

=SUMIF(A1:A10,”Ford”,C1:C10)

甚至兩個COUNTIF函數或SUMIF函數都比一個SUMPRODUCT函數更快，因此下面的公式：

=COUNTIF(A1:A10,>=10)-COUNTIF(A1:A10,>20)

比下面的公式更有效率：

=SUMPRODUCT((A1:A10>=10)*(A1:A10<=20))

大致提高20%。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。