Kirk đang sử dụng hàm SUM trong nhiều trang tính của mình để (tự nhiên)

xác định tổng của một dải giá trị. Tuy nhiên, vấn đề mà anh ta đang gặp phải là phạm vi mà anh ta đang tính tổng chứa một số hàng ẩn và anh ta không muốn những giá trị đó — những giá trị bị ẩn — được bao gồm trong tổng.

Hàm SUM khá đơn giản về cách hoạt động của nó; nó chỉ đơn giản là tính một phạm vi. Tuy nhiên, bạn có thể thay đổi chức năng bạn sử dụng và nhận được kết quả mong muốn. Ví dụ, giả sử rằng bạn muốn tính tổng phạm vi A3: A45 và bạn không muốn bất kỳ giá trị ẩn nào được đưa vào tổng. Bạn nên sử dụng hàm SUBTOTAL theo cách sau:

=SUBTOTAL(109,A3:A45)

Tham số đầu tiên của hàm (109) chỉ ra cách bạn muốn SUBTOTAL thực hiện công việc của nó. Trong trường hợp này, điều đó có nghĩa là bạn muốn SUBTOTAL tính tổng phạm vi bằng cách sử dụng hàm SUM và bạn không muốn bất kỳ giá trị ẩn nào được bao gồm trong giá trị được trả về. (Bạn có thể tìm hiểu thêm về các tham số SUBTOTAL điều khiển nếu bạn xem trong Trợ giúp trực tuyến cho chức năng SUBTOTAL.)

Nếu bạn không muốn sử dụng hàm SUBTOTAL vì lý do nào đó, bạn có thể tạo hàm do người dùng xác định của riêng mình (macro) sẽ chỉ tính tổng các giá trị hiển thị trong một phạm vi. Hãy xem xét macro này:

Function Sum_Visible(Cells_To_Sum As Object)

Dim vTotal As Variant

Application.Volatile     vTotal = 0     For Each cell In Cells_To_Sum         If Not cell.Rows.Hidden Then             If Not cell.Columns.Hidden Then                 vTotal = vTotal + cell.Value             End If         End If     Next     Sum_Visible = vTotal End Function

Để sử dụng hàm, chỉ cần sử dụng công thức như thế này ở bất cứ nơi nào bạn muốn tổng của mình xuất hiện:

=Sum_Visible(A1:A1000)

_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 (12123) á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: