Buzz
Sử dụng hàm SUM để tính tổng cột trong Excel đơn giản, nhưng để tính tổng giá trị danh sách đã lọc theo điều kiện cho trước, chúng ta sẽ tận dụng sức mạnh của hàm Subtotal. Hãy tìm hiểu cách này để nâng cao kỹ năng Excel của bạn.
Cách tính tổng giá trị danh sách đã lọc trong Excel
Hàm Sum là công cụ mạnh mẽ, nhưng với các bài toán phức tạp như tính tổng danh sách đã lọc trong Excel, hàm Sum gặp khó khăn khi phân biệt giá trị đã lọc hay chưa. Trong hướng dẫn này, Mytour sẽ giới thiệu cách nâng cao về hàm Sum và cách tính tổng danh sách đã lọc trong Excel.
Tính Tổng Danh Sách Đã Lọc Trong Excel
Để hiểu cách tính tổng danh sách đã lọc trong Excel, chúng ta sẽ xem ví dụ cụ thể. Được cho một danh sách nhân viên với phụ cấp từ 200.000 đến 5.000.000. Hãy tính tổng phụ cấp của những người có giá trị dưới 1.500.000.
Bước 1: Tạo bộ lọc cho danh sách. Trong Excel 2016, bôi đen danh sách, chọn Data và nhấn Filter.
- Ngay khiến bạn có bảng danh sách sẵn sàng lọc và tìm kiếm thông tin một cách nhanh chóng.
Bước 2: Chọn bảng lọc tại cột Phụ cấp, lựa chọn Number Filters, sau đó chọn Less than để tạo bộ lọc theo đề bài với điều kiện phụ cấp <>.
Bước 3: Nhập giá trị “1500000” và nhấn OK để xác nhận.
- Bảng mới ngay lập tức xuất hiện với các giá trị thỏa mãn hiển thị, còn các giá trị không thỏa mãn tự động ẩn đi.
Bước 4: Để kiểm tra sự chính xác của hàm Sum với danh sách phụ cấp theo đề bài, thử nhập công thức sau:
Nhập công thức vào ô bất kỳ =Sum(D6:E20)
Với =Sum là tên hàm tính tổng.
D6:D20: là các giá trị cần tính tổng ở cột phụ cấp (Cột D và E)
Lưu ý: Vì cột phụ cấp trước đó được gộp lại từ 2 cột, nên tên gọi được tính theo 2 cột.
- Kết quả là 18.950.000, không khớp với danh sách đã lọc mà thay vào đó là kết quả của toàn bộ nhân viên khi chưa lọc.
Bước 5: Để giải quyết bài toán, tức là tính tổng danh sách đã lọc trong Excel với giá trị phụ cấp nhỏ hơn 1.500.000, chúng ta cần kết hợp hàm Subtotal với hàm Sum như sau:
=Subtotal(9,D6:D20)
Với =Subtotal là tên hàm tính giá trị đã lọc
9 ở đây chính là Sum, Mytour sẽ đề cập rõ hơn ở cuối bài.
D6:D20 chính là danh sách đã được lọc tính theo cột phụ cấp.
Lưu ý: Hàm Subtotal cho phép thực hiện các phép tính với giá trị đã lọc, bao gồm ẩn hay không ẩn. Hàm Subtotal phải kết hợp với một hàm trong phép tính, không có khả năng tính toán tổng như trong bài viết này.
- Kết quả xuất hiện là 10.450.000, bạn có thể kiểm tra với danh sách trên để thấy kết quả khớp nhau. Với việc sử dụng Subtotal, chúng ta đã có thể tính tổng danh sách đã lọc trong Excel.
Các Hàm Sử Dụng Được Với Subtotal và Ký Hiệu Cần Ghi Nhớ
Trong ví dụ trên, chúng ta đã biết số 9 là hàm Sum được sử dụng kết hợp với Subtotal. Dưới đây là 10 hàm khác sử dụng được với Subtotal cùng với các giá trị tương ứng.
Trong bảng dưới đây, có 2 loại giá trị là tính giá trị ẩn và bỏ qua giá trị ẩn. Sự khác nhau của chúng trong cách tính tổng danh sách đã lọc trong Excel là gì?
Giải thích đơn giản, giá trị ẩn là giá trị loại bỏ do không thỏa mãn điều kiện bộ lọc. Đối với loại giá trị này, kết quả không thay đổi nếu áp dụng 1 trong 2 điều kiện trên. Còn đối với giá trị bị ẩn khi sử dụng Hide, kết quả sẽ được tính và ngược lại khi áp dụng bỏ qua giá trị ẩn.
Dưới đây là cách tính tổng danh sách đã lọc trong Excel, sử dụng hàm Subtotal kết hợp với hàm Sum. Hy vọng bạn hiểu rõ hơn về hàm Subtotal và cách sử dụng hàm Sum trong tính toán. Trong trường hợp sử dụng hàm Sum gặp lỗi bằng 0, một lỗi thường gặp trong Excel, bạn có thể tham khảo hướng dẫn sửa lỗi hàm Sum bằng 0 mà Mytour đã biên soạn.
Lọc dữ liệu trên Excel quan trọng, lọc trên 1 sheet đơn giản, nhưng để lọc dữ liệu trên nhiều sheet khác nhau, bạn có thể tham khảo cách lọc dữ liệu giữa các sheet.
Chúc bạn thực hiện thành công.
