Susie có một trang tính có khá nhiều dữ liệu trong đó. Không có gì lạ khi cô ấy ẩn các cột trong trang tính. Cô ấy cần một công thức sẽ trả về giá trị trung bình của một dải ô, tất cả đều nằm trong cùng một hàng, đồng thời bỏ qua bất kỳ ô nào bị ẩn. Ví dụ: nếu Susie chọn B7: G7, cô ấy muốn trung bình chỉ các ô trong phạm vi đó được hiển thị (cột D và E, trong trường hợp này, bị ẩn).

Không có hàm nội tại nào trong Excel để tính giá trị trung bình như vậy. Nếu bạn đang cố gắng tìm giá trị trung bình của một dải ô trong một cột, thì bạn có thể sử dụng hàm SUBTOTAL, theo cách sau:

=SUBTOTAL(101,A7:A12)

Tuy nhiên, hàm SUBTOTAL sẽ không trả về giá trị chính xác khi phạm vi được cung cấp nằm trong một hàng. Nếu muốn, bạn có thể sử dụng một số ô trợ giúp để thực hiện phép tính. Chỉ cần điền thông tin sau vào ô B8:

=(CELL("width",B7)>0)+0

Sao chép ô B8 vào phạm vi C8: G8. Kết quả là mỗi ô trong phạm vi B8: G8 sẽ chứa 0 hoặc 1 dựa trên việc cột đó có bị ẩn hay không. Sau đó, bạn có thể sử dụng công thức sau để xác định giá trị trung bình:

=SUMIFS(B7:G7,B8:G8,">0")/SUM(B8:G8)

Nó chỉ tính giá trị trung bình cho những cột trong đó các ô trong B8: G8 chứa giá trị 1 (chúng không bị ẩn).

Tất nhiên, bạn có thể không sử dụng được các ô trợ giúp và bạn có thể thấy sẽ có lợi hơn khi tạo một hàm do người dùng xác định để tính giá trị trung bình. Các tác vụ sau đây hoạt động rất nhanh:

Function AverageVisible(rng As Range)

Dim rCell As Range     Dim iCount As Integer     Dim dTtl As Double

iCount = 0     dTtl = 0     For Each rCell In rng         If rCell.ColumnWidth > 0 _           And rCell.RowHeight > 0 _           And Not IsEmpty(rCell) _           And IsNumeric(rCell.Value) Then               dTtl = dTtl + rCell               iCount = iCount + 1         End If     Next     If iCount > 0 Then         AverageVisible = dTtl / iCount     Else         AverageVisible = 0     End If End Function

Để sử dụng hàm, chỉ cần đặt công thức này vào ô bạn muốn chứa giá trị trung bình:

=AverageVisible(B7:G7)

Hàm kiểm tra mọi ô trong phạm vi (có nghĩa là bạn có thể sử dụng nó trên các hàng, cột hoặc thực sự là bất kỳ phạm vi nào) để đảm bảo rằng nó không bị ẩn và không bị trống. Nếu ô chứa giá trị số, thì nó được sử dụng để tính giá trị trung bình. Nếu phạm vi bạn chỉ định không chứa giá trị nào có thể được tính trung bình, thì hàm trả về giá trị bằng 0.

Chức năng tự động chạy mỗi khi trang tính được tính toán lại. Nếu bạn thay đổi cột nào bị ẩn, Excel sẽ không tự động tính toán lại. Do đó, bạn sẽ cần phải tính toán lại sau khi ẩn hoặc bỏ ẩn các cột.

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

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

Mẹo này (13262) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.