Hàm SUBTOTAL: Tính tổng giá trị danh sách đã lọc trong Excel
- Thứ sáu - 04/04/2025 23:17
- In ra
- Đóng cửa sổ này
Hàm tính tổng khi lọc dữ liệu rất quan trọng với người dùng Microsoft Excel. Bài viết sẽ tổng hợp cho bạn cách lọc và tính tổng trong Excel bằng Subtotal.
Mặc dù biết tính tổng của một cột bảng tính rất hữu ích, nhưng bạn có thể muốn biết tổng của các nhóm khác nhau. Hàm subtotal trong Excel là một cách thực tế để tính toán các số tiền nhỏ hơn, tách biệt với tổng cuối cùng. Nó tương đối dễ sử dụng trong bảng tính, đồng thời cho phép bạn sắp xếp dữ liệu của mình.
Hàm subtotal trong Excel là công thức tính toán một phạm vi giá trị dựa trên một phép toán được chỉ định. Nó xuất hiện dưới dạng =SUBTOTAL(function_num, ref1, [ref2],...), trong đó function_num tham chiếu đến phép toán bạn muốn công thức thực hiện. ref1, [ref2],... là các giá trị bạn muốn Excel đưa vào phép tính. Một trong những cách sử dụng phổ biến nhất của hàm subtotal là tính tổng của một cột, nhưng nó cũng có thể thực hiện các phép toán sau:
- Trung bình
- Đếm
- Đếm tất cả
- Giá trị lớn nhất
- Giá trị nhỏ nhất
- Phép nhân
- Độ lệch chuẩn của quần thể
- Độ lệch chuẩn của mẫu
- Phương sai của quần thể
- Phương sai của mẫu
Bài hướng dẫn được thực hiện trên Excel 2019, 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 khác như Excel 2007, Excel 2010, Excel 2013, Excel 2016, vì hướng dẫn sử dụng hàm Excel Subtotal.
Tính tổng giá trị danh sách đã lọc bằng hàm Subtotal trong Excel
Giả sử ta có một bảng dữ liệu như sau, với bộ lọc đã được tạo.
| STT | Loại sản phẩm | Tên sản phẩm | Số lượng | Đơn giá | Thành tiền |
| 1 | Sách | Bài tập Toán lớp 3 | 2 | 10000 | 20000 |
| 2 | Hộp bút | Hộp bút vải TL | 2 | 30000 | 60000 |
| 3 | Bút chì | Bút chì đoạn TL | 5 | 15000 | 75000 |
| 4 | Sách | Toán lớp 3 | 3 | 18000 | 54000 |
| 5 | Sách | Tiếng Anh lớp 3 | 3 | 19000 | 57000 |
Sau khi lọc ra danh sách sản phẩm thuộc loại sách ta có bảng tính như sau:
Yêu cầu đặt ra là tính tổng tiền sách. Nếu sử dụng hàm SUM() cho bảng dữ liệu đã lọc trên, kết quả bạn nhận được không phải là tổng tiền sách mà sẽ là tổng tiền của tất cả các sản phẩm.
Trong trường hợp này ta sử dụng hàm SUBTOTAL như sau, tại ô E13, bạn nhập:
=SUBTOTAL(9,H7:H11)
Trong đó, 9 là giá trị đối số tương ứng với hàm cần sử dụng, ở đây ta muốn tính tổng, vậy hàm cần dùng là SUM, bạn có thể xem ở bảng bên dưới, H7:H11 là phạm vi cần tính tổng.
Kết quả trả về tổng tiền sách là 131.000.
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 | 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.
Hàm SUBTOTAL() còn được sử dụng để thực hiện rất nhiều công việc hữu ích khác nữa, bạn có thể tham khảo thêm trong bài hướng dẫn về hàm SUBTOTAL của Quantrimang.com
