Hướng dẫn này sẽ chỉ cho bạn cách sử dụng hàm Trung bình của Excel trong VBA.
Hàm AVERAGE trong Excel được sử dụng để tính giá trị trung bình từ một phạm vi ô trong Trang tính của bạn có các giá trị trong đó. Trong VBA, Nó được truy cập bằng phương thức WorksheetFunction.
AVERAGE 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 AVERAGE là một trong số đó.
| 123 | Kiểm tra phụPhạm vi ("D33") = Application.WorksheetFunction.Average ("D1: D32")Kết thúc Sub |
Bạn có thể có tối đa 30 đối số trong hàm AVERAGE. Mỗi đối số phải tham chiếu đến một dải ô.
Ví dụ dưới đây sẽ tạo ra giá trị trung bình của tổng các ô từ B11 đến N11
| 123 | Sub TestAverage ()Range ("O11") = Application.WorksheetFunction.Average (Phạm vi ("B11: N11"))Kết thúc Sub |
Ví dụ dưới đây sẽ tạo ra mức trung bình của tổng các ô trong B11 đến N11 và tổng các ô trong B12: N12. Nếu bạn không nhập đối tượng Ứng dụng, nó sẽ được giả định.
| 123 | Sub TestAverage ()Phạm vi ("O11") = WorksheetFunction.Average (Phạm vi ("B11: N11"), Phạm vi ("B12: N12"))Kết thúc Sub |
Gán kết quả AVERAGE 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 một Phạm vi 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 AssignAverage ()Làm mờ kết quả dưới dạng số nguyên'Gán biếnresult = WorksheetFunction.Average (Phạm vi ("A10: N10"))'Hiển thị kết quảMsgBox "Giá trị trung bình cho các ô trong phạm vi này là" & kết quảKết thúc Sub |
AVERAGE 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.
| 123456789 | Sub TestAverageRange ()Dim rng As Range'gán phạm vi ôĐặt rng = Range ("G2: G7")'sử dụng phạm vi trong công thứcRange ("G8") = WorksheetFunction.Average (rng)'giải phóng đối tượng phạm viĐặt rng = Không có gìKết thúc Sub |
AVERAGE Nhiều Đối tượng Phạm vi
Tương tự, bạn có thể tính giá trị trung bình của các ô từ nhiều Đối tượng Phạm vi.
| 123456789101112 | Sub TestAverageMultipleRanges ()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.Average (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 |
Sử dụng AVERAGEA
Hàm AVERAGEA khác với hàm AVERAGE ở chỗ nó tạo giá trị trung bình từ tất cả các ô trong một phạm vi, ngay cả khi một trong các ô có văn bản trong đó - nó thay thế văn bản bằng số 0 và bao gồm giá trị đó để tính giá trị trung bình. Hàm AVERAGE sẽ bỏ qua ô đó và không tính nó vào phép tính.
| 123 | Sub TestAverageA ()Phạm vi ("B8) = Application.WorksheetFunction.AverageA (Phạm vi (" A10: A11 "))Kết thúc Sub |
Trong ví dụ bên dưới, hàm AVERAGE trả về một giá trị khác cho hàm AVERAGEA khi phép tính được sử dụng trên các ô A10 đến A11
Câu trả lời cho công thức AVERAGEA thấp hơn công thức AVERAGE vì nó thay thế văn bản trong A11 bằng số 0 và do đó trung bình trên 13 giá trị thay vì 12 giá trị mà AVERAGE đang tính toán.
Sử dụng AVERAGEIF
Hàm AVERAGEIF cho phép bạn tính trung bình tổng của một dải ô đáp ứng một tiêu chí nhất định.
| 123 | Sub AverageIf ()Phạm vi ("F31") = WorksheetFunction.AverageIf (Phạm vi ("F5: F30"), "Tiết kiệm", Phạm vi ("G5: G30"))Kết thúc Sub |
Quy trình trên sẽ chỉ tính trung bình các ô trong phạm vi G5: G30 trong đó ô tương ứng trong cột F có từ 'Tiết kiệm' trong đó. Tiêu chí bạn sử dụng phải nằm trong dấu ngoặc kép.
Nhược điểm của WorksheetFunction
Khi bạn sử dụng WorksheetFunction để tính trung bình các giá trị trong một phạm vi trong trang tính của bạn, một giá trị 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 TestAverage đã tạo giá trị trung bình của B11: M11 và đưa câu trả lời vào N11. 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 (B11: M11), kết quả trong N11 sẽ KHÔNG PHẢI thay đổi.
Thay vì sử dụng WorksheetFunction.Average, bạn có thể sử dụng VBA để áp dụng Hàm AVERAGE 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.
Sử dụng 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ụ: B11: M11 như được hiển thị bên dưới.
| 123 | Sub TestAverageFormula ()Phạm vi ("N11"). Công thức = "= Trung bình (B11: M11)"Kết thúc Sub |
Sử dụng phương pháp FormulaR1C1
Phương pháp FomulaR1C1 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 TestAverageFormula ()Phạm vi ("N11"). Công thức = "= Trung bình (RC [-12]: RC [-1])"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 TestCountFormula ()ActiveCell.FormulaR1C1 = "= Đếm (R [-11] 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ẽ tính trung bình các giá trị trong 12 ô ngay bên trái của nó và đặt câu trả lời vào ActiveCell của bạn. Phạm vi bên trong hàm AVERAGE 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 N11 thay vì một giá trị.
