Hướng dẫn này sẽ chỉ cho bạn cách sử dụng các hàm SUMIF và SUMIFS của Excel trong VBA
VBA không có hàm SUMIF hoặc SUMIFS tương đương mà bạn có thể sử dụng - người dùng phải sử dụng các hàm Excel tích hợp sẵn trong VBA bằng cách sử dụng WorkSheetFunction sự vật.
SUMIF 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 SUMIF là một trong số đó.
| 123 | Sub TestSumIf ()Phạm vi ("D10") = Application.WorksheetFunction.SumIf (Phạm vi ("C2: C9"), 150, Phạm vi ("D2: D9"))Kết thúc Sub |
Quy trình trên sẽ chỉ cộng các ô trong Phạm vi (D2: D9) nếu ô tương ứng trong cột C = 150.
Gán kết quả SUMIF 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 AssignSumIfVariable ()Làm mờ kết quả dưới dạng Double'Gán biếnresult = WorksheetFunction.SumIf (Phạm vi ("C2: C9"), 150, Phạm vi ("D2: D9"))'Hiển thị kết quảMsgBox "Tổng kết quả khớp với 150 mã bán hàng là" & kết quảKết thúc Sub |
Sử dụng SUMIFS
Hàm SUMIFS tương tự như SUMIF WorksheetFunction nhưng nó cho phép bạn kiểm tra nhiều tiêu chí. Trong ví dụ dưới đây, chúng tôi đang tìm cách cộng giá ưu đãi nếu mã ưu đãi là 150 VÀ Giá vốn lớn hơn 2. Lưu ý rằng trong công thức này, phạm vi ô cần cộng ở phía trước tiêu chí, trong khi trong hàm SUMIF, nó ở phía sau.
| 123 | Sub MultipleSumIfs ()Phạm vi ("D10") = WorksheetFunction.SumIfs (Phạm vi ("D2: D9"), Phạm vi ("C2: C9"), 150, Phạm vi ("E2: E9"), "> 2")Kết thúc Sub |
Sử dụng SUMIF với 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.
| 123456789101112 | Kiểm tra phụSumIFRange ()Dim rngCriteria As RangeDim rngSum dưới dạng Phạm vi'gán phạm vi ôĐặt rngCriteria = Range ("C2: C9")Đặt rngSum = Range ("D2: D9")'sử dụng phạm vi trong công thứcRange ("D10") = WorksheetFunction.SumIf (rngCriteria, 150, rngSum)'giải phóng các đối tượng phạm viĐặt rngCriteria = Không có gìĐặt rngSum = Không có gìKết thúc Sub |
Sử dụng SUMIFS trên nhiều đối tượng phạm vi
Tương tự, bạn có thể sử dụng SUMIFS trên nhiều Đối tượng Phạm vi.
| 123456789101112131415 | Sub TestSumMultipleRanges ()Dim rngCriteria1 As RangeDim rngCriteria2 dưới dạng Phạm viDim rngSum dưới dạng Phạm vi'gán phạm vi ôĐặt rngCriteria1 = Range ("C2: C9")Đặt rngCriteria2 = Range ("E2: E10")Đặt rngSum = Range ("D2: D10")'sử dụng các phạm vi trong công thứcRange ("D10") = WorksheetFunction.SumIfs (rngSum, rngCriteria1, 150, rngCriteria2, "> 2")'giải phóng đối tượng phạm viĐặt rngCriteria1 = Không có gìĐặt rngCriteria2 = Không có gìĐặt rngSum = Không có gìKết thúc Sub |
Lưu ý rằng vì bạn đang sử dụng dấu lớn hơn, tiêu chí lớn hơn 2 cần phải nằm trong dấu ngoặc đơn.
Công thức SUMIF
Khi bạn sử dụng WorksheetFunction.SUMIF để 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, quy trình đã thêm Phạm vi (D2: D9) trong đó Mã bán hàng bằng 150 trong cột C và kết quả được đưa vào D10. 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.
Nếu bất kỳ giá trị nào thay đổi trong Phạm vi (D2: D9) hoặc Phạm vi (C2: D9), kết quả trong D10 sẽ KHÔNG PHẢI thay đổi.
Thay vì sử dụng WorksheetFunction.SumIf, bạn có thể sử dụng VBA để áp dụng Hàm SUMIF 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 TestSumIf ()Phạm vi ("D10"). Công thứcR1C1 = "= SUMIF (C2: C9,150, D2: D9)"Kết thúc Sub |
Phương pháp FormulaR1C1
Phương thức FormulaR1C1 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 TestSumIf ()Phạm vi ("D10"). Công thứcR1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] 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 TestSumIf ()ActiveCell.FormulaR1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] 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 các ô đáp ứng tiêu chí ngay phía 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 SUMIF phải được tham chiếu bằng cách sử dụ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 D10 thay vì một giá trị.
