Hướng dẫn này sẽ chỉ cho bạn cách sử dụng hàm Sum Excel trong VBA
Hàm sum là một trong những hàm Excel được sử dụng rộng rãi nhất và có lẽ là hàm đầu tiên mà người dùng Excel học cách sử dụng. VBA thực sự không có hàm tương đương - người dùng phải sử dụng hàm Excel tích hợp sẵn trong VBA bằng cách sử dụng WorkSheetFunction sự vật.
Sum WorksheetFunction
Đối tượng WorksheetFunction có thể được sử dụng để gọi hầu hết các hàm Excel có sẵn trong hộp thoại Chèn Hàm trong Excel. Hàm SUM là một trong số đó.
| 123 | Kiểm tra phụPhạm vi ("D33") = Application.WorksheetFunction.Sum ("D1: D32")Kết thúc Sub |
Bạn có thể có tối đa 30 đối số trong hàm SUM. Mỗi đối số cũng có thể tham chiếu đến một dải ô.
Ví dụ dưới đây sẽ thêm các ô từ D1 đến D9
| 123 | Sub TestSum ()Phạm vi ("D10") = Application.WorksheetFunction.SUM ("D1: D9")Kết thúc Sub |
Ví dụ dưới đây sẽ thêm một phạm vi trong cột D và một phạm vi trong cột F. Nếu bạn không nhập đối tượng Ứng dụng, nó sẽ được giả định.
| 123 | Sub TestSum ()Phạm vi ("D25") = WorksheetFunction.SUM (Phạm vi ("D1: D24"), Phạm vi ("F1: F24"))Kết thúc Sub |
Lưu ý đối với một phạm vi ô duy nhất, bạn không phải chỉ định từ 'Phạm vi' trong công thức ở phía trước các ô, nó được giả định bởi mã. Tuy nhiên, nếu bạn đang sử dụng nhiều đối số, bạn cần phải làm như vậy.
Gán kết quả Tổng cho một biến
Bạn có thể muốn sử dụng kết quả của công thức của mình ở nơi khác trong mã thay vì viết nó trực tiếp trở lại và Dãy Excel. Nếu đúng như vậy, bạn có thể gán kết quả cho một biến để sử dụng sau này trong mã của mình.
| 1234567 | Sub AssignSumVariable ()Làm mờ kết quả dưới dạng Double'Gán biếnresult = WorksheetFunction.SUM (Phạm vi ("G2: G7"), Phạm vi ("H2: H7"))'Hiển thị kết quảMsgBox "Tổng các phạm vi là" & kết quảKết thúc Sub |
Tính tổng một đối tượng phạm vi
Bạn có thể gán một nhóm ô cho đối tượng Phạm vi, sau đó sử dụng đối tượng Phạm vi đó với WorksheetFunction sự vật.
| 123456789 | Sub TestSumRange ()Dim rng As Range'gán phạm vi ôĐặt rng = Range ("D2: E10")'sử dụng phạm vi trong công thứcRange ("E11") = WorksheetFunction.SUM (rng)'giải phóng đối tượng phạm viĐặt rng = Không có gìKết thúc Sub |
Tổng nhiều đối tượng phạm vi
Tương tự, bạn có thể tính tổng nhiều Đối tượng Phạm vi.
| 123456789101112 | Sub TestSumMultipleRanges ()Dim rngA As RangeDim rngB dưới dạng Phạm vi'gán phạm vi ôĐặt rngA = Range ("D2: D10")Đặt rngB = Range ("E2: E10")'sử dụng phạm vi trong công thứcRange ("E11") = WorksheetFunction.SUM (rngA, rngB)'giải phóng đối tượng phạm viĐặt rngA = Không có gìĐặt rngB = Không có gìKết thúc Sub |
Tổng toàn bộ cột hoặc hàng
Bạn cũng có thể sử dụng hàm Sum để cộng toàn bộ cột hoặc toàn bộ hàng
Quy trình dưới đây sẽ cộng tất cả các ô số trong cột D.
| 123 | Sub TestSum ()Phạm vi ("F1") = WorksheetFunction.SUM (Phạm vi ("D: D")Kết thúc Sub |
Trong khi quy trình này bên dưới sẽ cộng tất cả các ô số trong Hàng 9.
| 123 | Sub TestSum ()Phạm vi ("F2") = WorksheetFunction.SUM (Phạm vi ("9: 9")Kết thúc Sub |
Tính tổng một mảng
Bạn cũng có thể sử dụng WorksheetFunction.Sum để thêm các giá trị trong một mảng.
| 123456789101112 | Sub TestArray ()Dim intA (1 đến 5) dưới dạng số nguyênDim SumArray dưới dạng số nguyên'điền vào mảngintA (1) = 15intA (2) = 20intA (3) = 25intA (4) = 30intA (5) = 40'cộng mảng và hiển thị kết quảMsgBox WorksheetFunction.SUM (intA)Kết thúc Sub |
Sử dụng hàm SumIf
Một hàm trang tính khác có thể được sử dụng là hàm SUMIF.
| 123 | Sub TestSumIf ()Phạm vi ("D11") = WorksheetFunction.SUMIF (Phạm vi ("C2: C10"), 150, Phạm vi ("D2: D10"))Kết thúc Sub |
Quy trình trên sẽ chỉ cộng các ô trong Phạm vi (D2: D10) nếu ô tương ứng trong cột C = 150.
Công thức tính tổng
Khi bạn sử dụng WorksheetFunction.SUM để thêm tổng vào một dải ô trong trang tính của bạn, một tổng tĩnh được trả về, không phải là một công thức linh hoạt. Điều này có nghĩa là khi số liệu của bạn trong Excel thay đổi, giá trị được trả về bởi WorksheetFunction sẽ không thay đổi.
Trong ví dụ trên, thủ tục TestSum đã thêm Phạm vi (D2: D10) và kết quả được đưa vào D11. Như bạn có thể thấy trong thanh công thức, kết quả này là một hình chứ không phải công thức.
Do đó, nếu bất kỳ giá trị nào thay đổi trong Phạm vi (D2: D10), kết quả trong D11 sẽ KHÔNG PHẢI thay đổi.
Thay vì sử dụng WorksheetFunction.SUM, bạn có thể sử dụng VBA để áp dụng một Hàm Sum cho một ô bằng cách sử dụng Công thức hoặc Công thứcR1C1 các phương pháp.
Phương pháp công thức
Phương thức công thức cho phép bạn trỏ cụ thể đến một phạm vi ô, ví dụ: D2: D10 như được hiển thị bên dưới.
| 123 | Sub TestSumFormulaPhạm vi ("D11"). Công thức = "= SUM (D2: D10)"Kết thúc Sub |
Phương pháp FormulaR1C1
Phương thức FromulaR1C1 linh hoạt hơn ở chỗ nó không hạn chế bạn trong một phạm vi ô đã đặt. Ví dụ dưới đây sẽ cho chúng ta câu trả lời tương tự như ở trên.
| 123 | Sub TestSumFormula ()Phạm vi ("D11"). Công thứcR1C1 = "= SUM (R [-9] C: R [-1] C)"Kết thúc Sub |
Tuy nhiên, để làm cho công thức linh hoạt hơn, chúng tôi có thể sửa đổi mã thành như sau:
| 123 | Sub TestSumFormula ()ActiveCell.FormulaR1C1 = "= SUM (R [-9] C: R [-1] C)"Kết thúc Sub |
Dù bạn đang ở đâu trong trang tính của mình, công thức sau đó sẽ cộng 8 ô ngay trên nó và đặt câu trả lời vào ActiveCell của bạn. Phạm vi bên trong hàm SUM phải được tham chiếu bằng cú pháp Hàng (R) và Cột (C).
Cả hai phương pháp này đều cho phép bạn sử dụng các công thức Excel động trong VBA.
Bây giờ sẽ có một công thức trong D11 thay vì một giá trị.
