Hocviendaotao.com - Bộ lọc là tính năng rất hữu ích và dễ sử dụng trong Microsoft Excel. Với bộ lọc, bạn có thể nhanh chóng giới hạn dữ liệu chỉ hiển thị những thông tin cần thiết. Tuy nhiên, làm thế nào để tính tổng giá trị danh sách đã lọc? Bài viết sau đây sẽ giúp bạn trả lời câu hỏi trên.
Bài hướng dẫn được thực hiện trên Excel 2003, đã khá cũ, tuy nhiên bạn vẫn có thể áp dụng cách tính này trên các phiên bản Excel mới hơn như Excel 2007, Excel 2010, Excel 2013, Excel 2016, vì hướng dẫn sử dụng hàm Excel Subtotal.
Hình dưới thể hiện một danh sách đã được lọc. Các bạn có thể nhìn cột thứ tự bên trái và nhận ra nhiều hàng không được hiển thị do không thỏa mãn bộ lọc.
Hình tiếp theo cho các bạn thấy kết quả khi chúng ta thử tính tổng giá trị bằng hàm SUM().
Nhìn thoáng qua cũng dễ dàng nhận thấy kết quả không đúng: giá trị tổng quá lớn, tại sao vậy? Đó là vì hàm SUM() tính tổng tất cả giá trị từ D14 đến D64 chứ không chỉ tính tổng các giá trị đã lọc. Không có cách nào để cho hàm SUM() biết rằng chúng ta muốn loại trừ những giá trị không thỏa mãn bộ lọc.
Giải pháp cho vấn đề này lại hết sức đơn giản. Bạn chỉ cần click AutoSum, Excel sẽ tự động gọi hàm SUBTOTAL() thay vì hàm SUM(). Hàm này sẽ xem xét toàn bộ danh sách từ D6 đến D82 nhưng chỉ tính tổng giá trị thỏa mãn bộ lọc.
Về hàm SUBTOTAL()
Hàm SUBTOTAL() sẽ xem xét toàn bộ danh sách giá trị trong cột D và chỉ tính toán những giá trị thỏa mãn bộ lọc. Các bạn có thể nhìn vào hình trên và đoán rằng đó là nhờ ta khai báo đối số 9. Tuy nhiên, đối số này cho Excel biết chúng ta muốn tính TỔNG các giá trị tham chiếu. Bảng sau liệt kê các đối số được chấp nhận:
| Tính cả giá trị ẩn | Bỏ qua giá trị ẩn | Hàm |
| 1 | 101 | AVERAGE() |
| 2 | 102 | |
| 3 | 103 | COUNTA() |
| 4 | 104 | MAX() |
| 5 | 105 | MIN() |
| 6 | 106 | |
| 7 | 107 | STDEV() |
| 8 | 108 | STDEVP() |
| 9 | 109 | SUM() |
| 10 | 110 | VAR() |
| 11 | 111 | VARP() |
Sau khi xem bảng trên có lẽ các bạn đang băn khoăn sự khác nhau giữa 9 và 109. Khi ta dùng đối số 9, hàm SUBTOTAL() sẽ tính tổng cả các giá trị đã bị ẩn. Còn khi ta dùng đối số 109, hàm SUBTOTAL() sẽ bỏ qua các giá trị đã bị ẩn. Chúng ta cần phân biệt rõ giá trị bị ẩn và giá trị bị loại do không thỏa mãn bộ lọc. Việc ẩn đi một hàng nào đó có thể thực hiện bằng cách click chuột phải vào thứ tự hàng sau đó chọn Hide. Điều này khác hoàn toàn với những hàng không được hiển thị do không thỏa mãn bộ lọc.
Xem thêm:
