Tom cần tính trung bình một loạt các ô không liền nhau, loại trừ bất kỳ ô nào có thể chứa giá trị bằng không. Cụ thể, anh ta cần tính trung bình các ô A1, C1, E1, G1 và J1, nhưng chỉ đếm những ô không chứa số 0.

Trước khi đi vào những gì hiệu quả, chúng ta hãy xem xét những gì không hiệu quả.

Trước hết, nó không hoạt động nếu chỉ cần thêm các ô và chia cho 5; không tính đến giá trị 0. Thứ hai, không hiệu quả khi sử dụng COUNTIF ở mẫu số trong công thức của bạn, như được hiển thị ở đây:

=(A1+C1+E1+G1+J1) / COUNTIF(A1:J1,"<>0")

Điều này không hoạt động vì nó kiểm tra và đếm các ô trong toàn bộ phạm vi A1: J1, không chỉ năm ô bạn muốn xem xét ở mức trung bình. Bạn cũng có thể nghĩ rằng bạn có thể chọn năm ô không liền kề, đặt tên cho chúng, rồi sử dụng tên trong công thức của bạn. Mặc dù Excel cho phép bạn tạo tên, nhưng lỗi sau sẽ xảy ra:

=SUM(MyCells) / COUNTIF(MyCells,"<>0")

Có vẻ như COUNTIF sẽ chỉ hoạt động với một phạm vi liền kề duy nhất, do đó, bản chất không liền kề của phạm vi MyCells ném hàm vào một vòng xoáy. Sự cố tương tự xảy ra nếu bạn cố gắng sử dụng một dải ô không liền kề với hàm AVERAGEIF:

=AVERAGEIF(MyCells, "<>0")

Vì bạn không thể sử dụng bất kỳ hàm nào bạn có thể muốn sử dụng, bạn phải dựa vào công thức dài hơn một chút để tính giá trị trung bình. Bạn có thể tính giá trị trung bình của năm ô này bằng cách áp dụng một chút “thủ thuật” cho mẫu số của mình, theo cách sau:

=(A1+C1+E1+G1+J1) / ((A1<>0)+(C1<>0)+(E1<>0)+(G1<>0)+(J1<>0))

Việc đánh giá được thực hiện trên mỗi ô trong mẫu số trả về giá trị 1 (đối với Đúng) hoặc 0 (đối với Sai) tùy thuộc vào việc ô có chứa giá trị khác 0 hay không. Chuỗi giá trị này được cộng lại với nhau, cung cấp số lượng ô khác 0 cần thiết cho mẫu số.

Lưu ý rằng cuộc thảo luận ở đây là tất cả về mẫu số trong công thức, không phải tử số. Lý do rất đơn giản — bạn có thể thêm tất cả năm giá trị vào tử số; giá trị bằng không thực sự không quan trọng.

Điểm duy nhất của chúng là ở mẫu số, đó là điều khiến việc tính toán mức trung bình này trở nên khó khăn.

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

Mẹo này (7845) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. 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: