Bạn đã từng thấy nó trên Thế vận hội và trong các sự kiện thể thao khác: Điểm trung bình của một vận động viên được xác định bằng cách ném ra điểm cao nhất và điểm thấp nhất, sau đó tính trung bình cho những người còn lại. Bạn có thể có nhu cầu làm các loại trung bình tương tự. Ví dụ, bạn có thể là một giáo viên và cần phải loại trừ hai điểm bài tập thấp nhất trước khi tính điểm trung bình.

Để thực hiện kiểu tính trung bình này, tất cả những gì bạn cần nhớ là giá trị trung bình được tính bằng cách tính tổng tất cả các giá trị trong một phạm vi và sau đó chia tổng đó cho số mục trong phạm vi đó. Hàm SUM dễ dàng cung cấp tổng và hàm COUNT có thể được sử dụng để tìm ra số lượng mục trong phạm vi. Làm cách nào để loại trừ hai giá trị thấp nhất trong phạm vi? Bạn có thể sử dụng chức năng NHỎ.

Hãy xem xét công thức sau, giả sử bạn muốn tìm giá trị trung bình đã điều chỉnh của phạm vi A10: A14:

=(SUM(A10:A14)-SMALL(A10:A14,1)-SMALL(A10:A14,2))/(COUNT(A10:A14)-2)

Hàm SMALL được sử dụng để xác định hai giá trị thấp nhất trong phạm vi và các giá trị này được trừ khỏi tổng thể của phạm vi. Giá trị kết quả sau đó được chia cho COUNT giá trị trong phạm vi.

Cũng lưu ý rằng giá trị COUNT được giảm đi 2 để bù đắp cho việc bạn đang bỏ qua hai giá trị thấp nhất.

Một cách khác để tính giá trị trung bình tương tự là sử dụng công thức mảng.

Cái sau thực hiện thủ thuật:

=AVERAGE(IF(A10:A14>SMALL(A10:A14,2),A10:A14))

Vì đây là công thức mảng, bạn cần nhập nó bằng cách nhấn Ctrl + Shift + Enter thay vì chỉ nhấn Enter. Công thức này vẫn dựa trên việc sử dụng hàm SMALL, nhưng nó cũng sử dụng hàm AVERAGE thực tế để trả về một kết quả. Vì đây là một công thức mảng, nó kiểm tra từng giá trị trong mảng (phạm vi) và chỉ xem xét chúng để sử dụng trong giá trị trung bình nếu chúng lớn hơn giá trị nhỏ thứ hai trong mảng.

Mặc dù công thức mảng ngắn hơn công thức thông thường dài hơn, nhưng có một lưu ý cần lưu ý: Công thức mảng sẽ tạo ra kết quả không mong muốn nếu có “ràng buộc” hai chiều ở giá trị thấp thứ hai trong phạm vi hoặc dây buộc ba chiều ở giá trị thấp nhất. Ví dụ: nếu các giá trị được tính trung bình là 3, 2, 10, 3 và 7, thì công thức mảng sẽ tạo ra giá trị trung bình là 8,5. Tại sao? Bởi vì chỉ có giá trị 10 và 7 cao hơn giá trị thấp thứ hai và trung bình của hai giá trị đó là 8,5.

Nếu bạn sử dụng công thức dài hơn, được trình bày lần đầu ở trên, thì giá trị trung bình được trả về là 6,666667, là mức trung bình của 10, 3 và 7.

Nếu bạn thử các công thức này và chúng không hoạt động, bạn nên kiểm tra để đảm bảo rằng bạn đã cài đặt Analysis ToolPak. Chức năng NHỎ là một phần của ToolPak đó.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (2050) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: