【範例一】
參考下圖,若要計算1~100的總和,可以善用 Excel 試算的特性,於單元格B3建立公式:=B2+A3,再複製單元格B3,貼至單元格B3:B101。(共100個)
其最後結果,單元格B101的內容為5050,即為1+2+3+ … + 100的總和。
如果,你是使用程序來運算,程序範例如下:
Dim sum As Integer
For i = 1 To 100
Sum = Sum + i
Next
Debug.Print(Sum)
也可以使用數組公式:
公式:{=SUM(ROW(1:100))}
輸入完成要Ctrl+Shift+Enter鍵,Excel 會自動加「{}」。
其中ROW(1:100)在數組公式中代表ROW(1:1)=1、ROW(2:2)=2、...、ROW(100:100)=100。
或是使用 SUMPRODUCT 函數:
公式:=SUMPRODUCT(ROW(1:100))
【範例二】
如果改成要計算1+3+…+99(奇數和),該如何處理?
程序範例如下:
Dim sum As Integer
For i = 1 To 99 Step 2
Sum = Sum + i
Next
Debug.Print(Sum)
也可以使用數組公式:
公式:{=SUM(ROW(1:99)*(MOD(ROW(1:99),2)))}
輸入完成要Ctrl+Shift+Enter鍵,Excel 會自動加「{}」。
MOD(ROW(1:99),2):如果除以 2 的餘數為 0,表示為偶數;如果除以 2 的餘數為 1,表示為奇數。在此 0/1 分別代表 FALSE/TRUE。
ROW(1:99)*(MOD(ROW(1:99),2))=ROW(1:99)*(FALSE/TRUE數組),其結果只會留下奇數部分,再透過 SUM 函數予以加總。
或是使用 SUMPRODUCT 函數:
公式:=SUMPRODUCT(ROW(1:99)*(MOD(ROW(1:99),2)))
【範例三】
如果改成要計算1+4+…+100(間隔3的數之和),該如何處理?
(請自行練習)
公式:{=SUM(ROW(1:100)*(MOD(ROW(1:100),3)=1))}
公式:=SUMPRODUCT(ROW(1:100)*(MOD(ROW(1:100),3)=1))
拓展思維:如果你在 Excel 的工作表中要實現 Select … Case 多選一的程序邏輯,該如何處理?(參考下圖)
【程序】
Dim k, sum As Integer
sum = 100
k = InputBox("請輸入一個數字")
Select Case k
Case 1 : sum = sum + k
Case 2 : sum = sum - k
Case 3 : sum = sum * k
Case 4 : sum = sum / k
End Select
Debug.Print(sum)
在 Excel 中可以透過 CHOOSE 函數來執行多選一的邏輯設計:
單元格E2:=CHOOSE(B3,D3+B3,D3-B3,D3*B3,D3/B3)
單元格B3(=k)是輸入的數字;單元格D3(=sum)是默認值。
單元格F3為 sum 的運算結果。
閱讀更多 老徐漫談 的文章