Tính toán các giá trị ẩn trong Excel – Có thể nói bộ lọc trong Microsoft Excel rất hữu ích cho chúng ta với bộ lọc bạn có thể nhanh chóng hiển thị những dữ liệu cần thiết. Tuy nhiên để tính tổng các giá trị khi đã được lọc dữ liệu không phải là đơn giản. Nếu bạn tính tổng một cách thủ công thì rất lâu hoặc nếu bạn sử dụng hàm tính tổng thường sử dụng như “SUM” thì kết quả không bao giờ đúng vì bản chất khi dữ liệu đã được lọc nó không đúng cấu trúc vì thế sẽ không thể chính xác? Vậy phải làm sao?
Trong bài trước mình đã hướng dẫn các bạn cách sử dụng hàm “RANK” trong Excel giúp bạn sắp xếp vị thứ nhanh chóng trong Excel mà không cần phải làm thủ công, tiếp tục trong bài viết này mình sẽ hướng dẫn các bạn cách sử dụng hàm để tính tổng các giá trị ẩn trong Excel một cách nhanh chóng và hiệu quả nhất!
Tính toán các giá trị ẩn trong Excel
Để tính tổng các giá trị ẩn trong Excel hãy sử dụng hàm “SUBTOTAL” với hàm này nó sẽ giúp bạn tính tổng các giá trị được hiển thị tại thời điểm hiện tại trên bảng tính Excel còn các giá trị ẩn nó sẽ không thực hiện vậy cách sử dụng hàm này ra sao? hãy cùng blog thủ thuật tìm hiểu cách sử dụng hàm “SUBTOTAL” để tính toán các giá trị ẩn trong Excel!
Bạn hãy để ý hình dưới thể hiện một danh sách đã được lọc dữ liệu. 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.
Trong 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 Kích chọn 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.
Các thông số của 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 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 | COUNT() |
| 3 | 103 | COUNTA() |
| 4 | 104 | MAX() |
| 5 | 105 | MIN() |
| 6 | 106 | PRODUCT() |
| 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.
Cuối cùng không có gì hơn nếu bạn cảm thấy bài viết có ích hãy subscribe blog của mình thường xuyên để cập nhật những bài viết mới nhất qua Email – Chân thành cảm ơn!
