Dưới đây, chúng ta sẽ xem xét một chương trình trong Excel VBA tạo ra một liên kết: / vba -amples-do người dùng xác định-function [Hàm do người dùng xác định] `để tính giá trị trung bình của một phạm vi được chọn ngẫu nhiên loại trừ một hoặc nhiều giá trị ngoại lệ và không nên được tính trung bình.

Tình huống:

Custom Average Function in Excel VBA

Các chức năng do người dùng định nghĩa cần được đặt vào một mô-đun.

  1. Mở Trình soạn thảo Visual Basic và nhấp vào Chèn, Mô-đun.

  2. Thêm dòng mã sau:

Function CUSTOMAVERAGE(rng As Range, lower As Integer, upper As Integer)

Tên của Chức năng của chúng tôi là CUSTOMAVERAGE. Phần giữa các dấu ngoặc có nghĩa là chúng tôi cung cấp cho Excel VBA một phạm vi và hai biến Số nguyên làm đầu vào. Chúng tôi đặt tên cho phạm vi của mình là rng, một biến Số nguyên mà chúng tôi gọi là thấp hơn và một biến Số nguyên mà chúng tôi gọi là trên, nhưng bạn có thể sử dụng bất kỳ tên nào.

  1. Tiếp theo, chúng ta khai báo một đối tượng Range và hai biến kiểu Integer.

Chúng tôi gọi ô đối tượng Phạm vi. Một biến Số nguyên chúng tôi gọi là tổng và một biến Số nguyên mà chúng tôi gọi là số đếm.

Dim cell As Range, total As Integer, count As Integer

  1. Chúng tôi muốn kiểm tra từng ô trong một phạm vi được chọn ngẫu nhiên (phạm vi này có thể có kích thước bất kỳ). Trong Excel VBA, bạn có thể sử dụng vòng lặp Cho Mỗi Tiếp theo cho việc này. Thêm các dòng mã sau:

For Each cell In rng

Next cell

Lưu ý: rng và ô được chọn ngẫu nhiên ở đây, bạn có thể sử dụng bất kỳ tên nào.

Hãy nhớ tham khảo những tên này trong phần còn lại của mã của bạn.

  1. Tiếp theo, chúng tôi kiểm tra từng giá trị trong phạm vi này nếu nó nằm giữa hai giá trị (dưới và trên). Nếu đúng, chúng tôi tăng tổng số theo giá trị của ô và chúng tôi tăng số đếm lên 1. Thêm các dòng mã sau vào vòng lặp.

If cell.Value >= lower And cell.Value <= upper Then

total = total + cell.Value

count = count + 1

End If

  1. Để trả về kết quả của hàm này (giá trị trung bình mong muốn), hãy thêm dòng mã sau bên ngoài vòng lặp.

CUSTOMAVERAGE = total / count

  1. Đừng quên kết thúc chức năng. Thêm dòng:

End Function

  1. Bây giờ bạn có thể sử dụng hàm này giống như bất kỳ hàm Excel nào khác để tính giá trị trung bình của các số nằm giữa hai giá trị.

Kết quả:

Custom Average Function Result

Để kiểm tra, bạn có thể xóa tất cả các giá trị thấp hơn 10 và cao hơn 30 và sử dụng hàm Trung bình tiêu chuẩn trong Excel để xem liệu Excel có tính toán mức trung bình giống như hàm trung bình tùy chỉnh của chúng tôi hay không.

Custom Average Function Check

Chức năng trung bình tùy chỉnh của chúng tôi hoạt động! Lưu ý: chức năng này chỉ có sẵn trong sổ làm việc này.