Veronica biết cách sử dụng hàm AVERAGE để xác định giá trị trung bình của một dải giá trị. Tuy nhiên, cô ấy muốn có giá trị trung bình được xác định dựa trên các giá trị khác 0 trong phạm vi.

Chức năng trang tính phù hợp nhất với mục đích này là sử dụng AVERAGEIF.

Bạn có thể sử dụng nó theo cách sau:

=AVERAGEIF(A1:A50,">0")

Hàm này chỉ bao gồm trung bình những ô có chứa giá trị lớn hơn 0. Nếu bạn cũng muốn loại trừ các ô trống, bạn nên sử dụng hàm AVERAGEIFS. Chức năng này khác với AVERAGEIF ở chỗ nó cho phép bạn chỉ định nhiều tiêu chí cho biết ô nào cần trung bình.

=AVERAGEIFS(A1:A50,A1:A50,">0",A1:A50,"<>""")

Tất nhiên, nếu bạn muốn tiếp cận vấn đề “old school” (mà không sử dụng AVERAGEIF hoặc AVERAGEIFS), thì bạn có thể tiến hành một số cách. Đầu tiên là nhớ cách tính trung bình. Nó được định nghĩa là tổng của một dải giá trị chia cho số mục trong dải. Do đó, bạn có thể tính toán mức trung bình loại trừ bằng cách đơn giản đảm bảo rằng mẫu số (số bạn đang chia) không bao gồm bất kỳ giá trị 0 nào. Ví dụ:

=SUM(A1:A50)/COUNTIF(A1:A50,"<>0")

Phương pháp này sử dụng hàm COUNTIF để xác định số ô trong phạm vi (A1: A50) không chứa số 0. Nếu phạm vi này không chỉ chứa các số không mà còn chứa các ô trống và bạn không muốn các ô trống được tìm thấy trong kết quả, thì bạn cần sử dụng một công thức phức tạp hơn:

=SUM(A1:A50)/(COUNTIF(A1:A50,"<>0")-COUNTBLANK(A1:A50)- (COUNTA(A1:A50)-COUNT(A1:A50)))

Hàm COUNTIF đếm các ô không đánh giá rõ ràng là 0, nhưng nó sẽ đếm các ô trống và ô văn bản. Thuật ngữ COUNTBLANK điều chỉnh cho các ô trống và sự khác biệt giữa COUNTA và COUNT điều chỉnh tổng số cho các ô chứa văn bản.

Tất nhiên bạn cũng có thể sử dụng công thức mảng để thực hiện phép tính của mình:

=AVERAGE(IF(A1:A50<>0,A1:A50))

Hãy nhớ rằng công thức mảng cần được nhập bằng cách sử dụng tổ hợp Ctrl + Shift + Enter. Công thức mảng này cũng loại trừ khoảng trống hoặc ô chứa văn bản.

Nói chung, việc sử dụng các hàm AVERAGEIF hoặc AVERAGEIFS trở nên dễ dàng hơn.

Khi nào bạn không muốn sử dụng chúng? Khi bạn cần chia sẻ sổ làm việc của mình với người dùng các phiên bản Excel trước Excel 2007. (Đây là khi các hàm được thêm vào Excel.)

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

Mẹo này (7729) áp dụng cho Microsoft Excel 2007, 2010, 2013 và 2016.

Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: