Cách tính giá trị trung bình ngoại trừ số lớn nhất nhỏ nhất của vùng dữ liệu

Thông thường khi ta áp dụng công thức tính trung bình trên Excel, nó sẽ tự động tính cho ta trung bình của tất cả các ô đã được chọn. Tuy nhiên trong vài tình huống đặc biệt, ta cần tính giá trị trung bình ngoại trừ giá trị lớn nhất và nhỏ nhất trong vùng dữ liệu được chọn. Để giải quyết tình huống này ta phải làm như thế nào. 

Trong bài này Gitiho.com sẽ hướng dẫn bạn 2 cách sau có thể giải quyết nhanh vấn đề.

Cách 1. Sử dụng công thức tính nhanh

Tập hợp hàm dưới đây có thể giúp ta tính trung bình một phạm vi giá trị mà không có số lớn nhất lẫn số nhỏ nhất. Hãy thực hiện theo các bước sau:

Công thức 1:

=(SUM(A2:A12)-MIN(A2:A12)-MAX(A2:A12))/(COUNT(A2:A12)-2)

Công thức 2: 

`=TRIMMEAN(A2:A12,2/COUNT(A2:A12))

Ta có thể nhập một trong hai công thức trên vào ô trống, giống như hình bên dưới. Sau đó nhấn phím Enter để lấy kết quả. Kết quả đó chính là giá trị trung bình đã được loại trừ số lớn nhất và số nhỏ nhất.

Chú ý: Trong công thức trên A2:A12 là phạm vi dữ liệu mà ta cần tính toán, bạn có thể thay đổi nó theo nhu cầu của mình.

Cách 2. Dùng tính năng User Defined

Bên cạnh phương pháp áp dụng công thức, ta có thể xử lý tình huống này bằng cách tạo một tính năng Người dùng xác định User Defined. Để thực hiện cách này ta làm theo các bước sau:

Bước 1: Nhấn giữ tổ hợp phím Alt và F11 để mở ra hộp thoại Microsoft Visual Basic for Applications window.

Tuy nhiên nếu phím tắt của bạn không hoạt động hoặc tab DEVELOPER chưa được mở thì bạn hãy thực hiện các thao tác sau:

1. Chọn thẻ File trên thanh công cụ, chọn Option. Trong mục Option này ta chọn Customize Ribbon. Hoặc một cách khác là kích chuột phải vào vị trí bất kỳ trên thanh công cụ và chọn mục Customize Ribbon. Như hình minh họa dưới đây:

2. Lúc này sẽ xuất hiện cửa sổ mới là Excel Options, ta chọn thẻ Customize the Ribbon, ta tích chọn vào thẻ Developer như trong hình dưới.

Như vậy trên thanh công cụ của chúng ta đã có thểm một tab mới là tab Developer.

3. Để mở ứng dụng VBA trong Excel ta nhấn tổ hợp phím Alt và F11 hoặc chọn mục Visua Basic trên thẻ Developer.

Lúc này một cửa sổ mới được mở ra, ta sẽ thực hiện tiếp bước 2 tại đây.

Bước 2: Nhấn Insert, chọn Module. Sau đó sao chép và dán đoạn mã VBA dưới đây vào cửa sổ Module mới được mở ra.

Function Avewithoutmaxmin(region) As Variant
'Update 20140325
      With Application
        Avewithoutmaxmin = (.Sum(region) - .Max(region) - .Min(region)) / _
            (.Count(region) - 2)
      End With
   End Function

Bước 3: Lưu và đóng lại đoạn mã này, sau đó trở về trang tính. Nhập công thức sau đây vào:

=Avewithoutmaxmin(A2:A12)

Trong đó A2:A12 là phạm vi dữ liệu mà ta cần tính trung bình sau khi loại trừ giá trị lớn nhất và nhỏ nhất. Bạn có thể thay đổi chúng tùy theo tình huống của mình. 

Sau đó nhấn phím Enter để nhận kết quả mình cần.

Như vậy chúng ta đã tính được giá trị trung bình của vùng dữ liệu sau khi loại trừ giá trị lớn nhất hay nhỏ nhất trên Excel.

Có thể bạn sẽ cần:

- Cách học nhanh Excel cho dân văn phòng.