Cách tính tổng trong Excel khi lọc: Thao tác “đơn giản” từ A-Z

Khi làm việc với dữ liệu trong Excel, việc lọc và tính tổng là những thao tác rất phổ biến. Tuy nhiên, nếu bạn chỉ dùng hàm SUM để tính tổng sau khi lọc, kết quả sẽ không như mong muốn vì hàm này vẫn tính tổng tất cả các ô, kể cả những ô bị ẩn. Bài viết sẽ là những hướng dẫn chi tiết các cách tính tổng trong Excel khi lọc để bạn có thể giải quyết vấn đề này một cách “đơn giản”và hiệu quả. 

Cách tính tổng trong Excel khi lọc “đơn giản”, hiệu quả

Để tính tổng trong Excel khi lọc, bạn có thể áp dụng nhanh với một trong các cách dưới đây:

Sử dụng Hàm SUBTOTAL() tính tổng trong Excel khi lọc

Hàm SUBTOTAL() là một công cụ mạnh mẽ, cho phép bạn thực hiện các phép tính (tổng, trung bình, đếm,…) trên dữ liệu đã lọc. Thay vì tính tổng tất cả các ô, kể cả những ô bị ẩn, thì hàm SUBTOTAL chỉ tính toán trên các hàng hiển thị.

1. Cú pháp của hàm SUBTOTAL:

=SUBTOTAL(function_num, ref1, [ref2], …)

Trong đó:

• function_num: Là một số từ 1 đến 11 hoặc từ 101 đến 111, cho biết loại phép tính bạn muốn thực hiện.

• • Các số từ 1 đến 11 sẽ bao gồm các hàng bị ẩn thủ công (nhưng bỏ qua những hàng bị ẩn do lọc).
  • Các số từ 101 đến 111 sẽ bỏ qua các hàng bị ẩn thủ công và cả các hàng bị ẩn do lọc.
  • Để tính tổng, bạn thường dùng số 9 (SUM) hoặc 109 (SUM).

• ref1, [ref2], …: Là các phạm vi ô mà bạn muốn tính toán.

2. Ví dụ:

Để tính tổng Doanh thu của cột Ti vi (giả sử từ ô B2 đến B11) sau khi lọc, bạn sẽ nhập công thức:

=SUBTOTAL(9,B2:B11)

Hoặc để chắc chắn bỏ qua cả các hàng bị ẩn thủ công:

=SUBTOTAL(109,B2:B11)

Khi bạn áp dụng bộ lọc và ẩn đi các dòng, kết quả của hàm SUBTOTAL() sẽ tự động cập nhật, chỉ hiển thị tổng của các dòng đang hiển thị.

Sử dụng Hàm AGGREGATE() tính tổng khi lọc trong Excel

Hàm AGGREGATE() cũng là một hàm tính mạnh mẽ, cung cấp nhiều tùy chọn linh hoạt hơn để bỏ qua các giá trị hoặc hàng cụ thể. Dưới đây là cách tính tổng trong Excel khi lọc với hàm  AGGREGATE giúp bạn dễ dàng thao tác:

1. Cú pháp của hàm AGGREGATE:

=AGGREGATE(function_num, options, ref1, [ref2], …)

Trong đó:

• function_num: Tương tự như SUBTOTAL(), là một số từ 1 đến 19, cho biết loại phép tính. Để tính tổng, bạn sử dụng số 9 (SUM).
• options: Là một số từ 0 đến 7, chỉ định các giá trị hoặc hàng mà hàm sẽ bỏ qua. Đây là điểm khác biệt lớn của AGGREGATE().

• • 0: Không tính các hàm SUBTOTAL/AGGREGATE lồng nhau.
  • 1: Bỏ qua các hàng ẩn và các hàm SUBTOTAL/AGGREGATE lồng nhau.
  • 2: Bỏ qua các giá trị lỗi và các hàm SUBTOTAL/AGGREGATE lồng nhau.
  • 3: Bỏ qua cả hàng ẩn, giá trị lỗi và các hàm SUBTOTAL/AGGREGATE lồng nhau.
  • 4: Không bỏ qua gì cả.
  • 5: Bỏ qua hàng ẩn.
  • 6: Bỏ qua giá trị lỗi.
  • 7: Bỏ qua hàng ẩn và giá trị lỗi.

• ref1, [ref2], …: Là các phạm vi ô mà bạn muốn tính toán.

2. Ví dụ:

Để tính tổng Doanh thu của cột Tivi (từ ô B2 đến B11), bỏ qua các hàng ẩn (do lọc) và các giá trị lỗi, bạn sẽ dùng công thức:

=AGGREGATE(9,7,B2:B11)

Tóm lại, hàm AGGREGATE() là công cụ hữu ích khi bạn phải làm việc với bảng dữ liệu phức tạp, cần kiểm soát chi tiết hơn việc bỏ qua các giá trị nào trong quá trình tính toán.

Sử dụng tính năng Total Row trong Bảng (Table) để tính tổng khi lọc

Đây cũng là một trong những cách tính tổng trong Excel khi lọc đơn giản và trực quan, đặc biệt là trong những tình huống bạn đã chuyển dữ liệu của mình thành Bảng (Table). Cụ thể:

– Bước 1: Chuyển dữ liệu của bạn thành bảng: Chọn bất kỳ ô nào trong vùng dữ liệu của bạn, sau đó vào tab Insert (Chèn) >> chọn Table (Bảng) hoặc nhấn phím tắt Ctrl + T.

– Bước 2: Áp dụng bộ lọc: Hãy thiết lập bộ lọc theo các tiêu chí mà bạn mong muốn.

– Bước 3: Kích hoạt Total Row: Sau khi dữ liệu đã là một bảng và được áp dụng bộ lọc, bạn có thể chọn bất kỳ ô nào trong bảng. Tiếp đó, trên Table Design (Thiết kế bảng) hoặc Table Tools Design (Công cụ bảng thiết kế) trên thanh Ribbon, hãy chọn hộp kiểm Total Row (Dòng tổng).

– Bước 4: Thiết lập tính toán: Một dòng tổng sẽ xuất hiện ở cuối bảng. Trong ô tổng của cột bạn muốn tính, nhấp vào mũi tên thả xuống và chọn loại tính toán mà bạn muốn (ví dụ: SUM() để tính tổng).

Như vậy, khi bạn lọc dữ liệu trong bảng, Total Row sẽ tự động cập nhật để chỉ hiển thị tổng của các dòng đang hiển thị. Điều này sẽ rất hữu ích và giúp bạn tiết kiệm thời gian đáng kể.

Lợi ích của việc tính tổng trong Excel khi lọc

Cách tính tổng trong Excel khi lọc không đơn thuần chỉ là một thao tác, mà còn mang lại nhiều lợi ích khi tính toán, giúp người dùng phân tích, đánh giá và đưa ra quyết định chuẩn xác hơn. Dưới đây là các lợi ích nổi bật của việc tính tổng này:

Tập trung các dữ liệu then chốt

Khi bạn lọc dữ liệu (ví dụ: lọc theo khu vực, sản phẩm, hoặc thời gian), việc tính tổng chỉ trên các dòng hiển thị giúp bạn ngay lập tức có được cái nhìn tổng quan về một tập con dữ liệu cụ thể.

Đánh giá hiệu suất tốt hơn

Với kết quả tính tổng sau lọc, bạn có thể dễ dàng xem tổng doanh thu của một quý cụ thể, tổng số lượng hàng bán ra của một nhân viên, hoặc tổng chi phí cho một dự án, từ đó đánh giá hiệu suất một cách nhanh chóng.

Cơ sở dữ liệu đáng tin cậy

Các hàm như SUBTOTAL() hoặc AGGREGATE() đảm bảo rằng bạn đang tính toán trên dữ liệu hiển thị, tránh sai sót do các dòng bị ẩn. Đồng thời, việc so sánh tổng của các nhóm dữ liệu khác nhau bằng cách thay đổi tiêu chí lọc và xem kết quả tổng tức thì cũng dễ dàng hơn rất nhiều.

Tiết kiệm thời gian, công sức

Thay vì phải sao chép dữ liệu đã lọc sang một bảng tính khác để tính tổng thủ công, các hàm và tính năng Total Row sẽ tự động cập nhật tổng mỗi khi bạn thay đổi bộ lọc. Ngoài ra, việc tự động hóa cũng giúp giảm thiểu đáng kể nguy cơ mắc lỗi so với việc tính toán thủ công hoặc sử dụng các công thức phức tạp.

Mẹo và lưu ý quan trọng khi tính tổng trong Excel khi lọc

Để cách tính tổng trong Excel khi lọc hiệu quả và đạt độ chính xác cao, bạn hãy ghi nhớ những mẹo và lưu ý sau:

• Không sử dụng hàm SUM khi đang lọc: Hàm SUM() sẽ tính tổng tất cả các ô trong phạm vi, kể cả những ô bị ẩn sau khi lọc. Do đó, nếu bạn chỉ muốn tính tổng dữ liệu sau khi lọc, hãy sử dụng hàm SUBTOTAL() (với function_num là 9 hoặc 109) và AGGREGATE() (với function_num là 9 và options phù hợp như 1, 3, 5, hoặc 7) để chỉ tính trên các ô hiển thị.
• Hiểu rõ các function_num và options: Với SUBTOTAL(), hãy nhớ 9 (bỏ qua hàng lọc) và 109 (bỏ qua cả hàng lọc và hàng ẩn thủ công). Trong khi đó, hàm AGGREGATE() với tùy chọn 7 (options) thường được dùng để bỏ qua cả hàng ẩn và lỗi, rất hữu ích khi bảng tính có dữ liệu ẩn hoặc lỗi sai sót.
• Kiểm tra dữ liệu nguồn trước khi lọc và tính tổng: Hãy chắc chắn rằng dữ liệu của bạn không có các ô trống không mong muốn, các giá trị lỗi (#DIV/0!, #N/A!,…) hoặc định dạng sai (như số được lưu dưới dạng văn bản) trong cột mà bạn muốn tính tổng. Bởi, điều này có thể ảnh hưởng đến kết quả cuối cùng, đặc biệt nếu bạn không sử dụng AGGREGATE() với tùy chọn bỏ qua lỗi.
• Sử dụng công cụ lọc hiệu quả: Luôn đảm bảo bạn đã áp dụng bộ lọc đúng cách. Sau khi lọc, cũng đừng quên kiểm tra biểu tượng bộ lọc trên tiêu đề cột để xác nhận rằng bộ lọc đã được áp dụng. Trường hợp bạn cần bỏ lọc để xem tổng toàn bộ dữ liệu, hãy sử dụng tính năng Clear Filter (Xóa bộ lọc) thay vì xóa thủ công từng bộ lọc.
• Tính năng AutoSum: Công cụ AutoSum (biểu tượng Sigma Σ trên thanh công cụ Home) sẽ tự động chèn hàm SUBTOTAL(9,…) thay vì sử dụng hàm SUM thông thường. Điều này giúp bạn dễ dàng tính tổng các giá trị hiển thị mà không cần nhập công thức thủ công.

Câu hỏi thường gặp 

1. Tại sao hàm SUM() không phù hợp tính tổng khi lọc?

Hàm SUM() không phù hợp để tính tổng khi bạn lọc dữ liệu vì nó sẽ tính tổng tất cả các ô trong phạm vi được chọn, bao gồm cả những ô bị ẩn do bộ lọc. Để tính tổng chỉ những ô hiển thị sau khi lọc, bạn nên sử dụng hàm SUBTOTAL() hoặc AGGREGATE().

2. Dòng tổng (Total Row) trong Bảng (Table) có tự động cập nhật khi tôi thêm dữ liệu mới không?

Có. Dòng tổng (Total Row) trong bảng Excel sẽ tự động cập nhật khi bạn thêm dữ liệu mới vào bảng. Đây là một trong những ưu điểm nổi bật khi sử dụng bảng Excel, vì nó giúp quản lý và phân tích dữ liệu hiệu quả hơn.

3. Có thể tính tổng trong Excel khi áp dụng nhiều tiêu chí lọc cùng lúc không?

Có. Khi bạn áp dụng nhiều tiêu chí lọc cùng lúc, hàm SUBTOTAL() hoặc AGGREGATE() cũng sẽ tính tổng chính xác các hàng hiển thị sau khi tất cả các tiêu chí lọc đã được áp dụng.

4. Tôi có thể sử dụng công cụ AutoSum sau khi lọc dữ liệu không?

Có. Nếu bạn sử dụng AutoSum sau khi lọc dữ liệu, Excel sẽ tự động sử dụng hàm SUBTOTAL() (với đối số 9 cho SUM) thay vì hàm SUM() thông thường. Điều này đảm bảo rằng chỉ các ô hiển thị mới được tính tổng.

5. Làm thế nào để tính tổng các hàng ẩn thủ công (không phải do lọc)?

Để tính tổng các hàng bị ẩn thủ công (ví dụ, bạn đã ẩn hàng bằng cách thủ công, không phải do lọc), bạn có thể sử dụng hàm AGGREGATE() với đối số thích hợp. Cụ thể, bạn sẽ cần sử dụng một trong các tùy chọn như 1, 3, 5, 7 để bỏ qua hàng bị ẩn.

>>> Xem thêm: Cách tính tổng trong Excel theo hàng ngang

Lời kết

Việc nắm vững các cách tính tổng trong Excel khi lọc dữ liệu là một kỹ năng thiết yếu, giúp bạn phân tích, đánh giá và đưa ra quyết định nhanh chóng dựa trên dữ liệu chuẩn xác. Hãy tận dụng tối đa sức mạnh của các hàm SUBTOTAL(), AGGREGATE(), và tính năng Total Row trong bảng để tối ưu hóa công việc của mình.

Ngoài ra, người dùng cũng nên sở hữu ứng dụng Microsoft Excel bằng cách mua ngay gói Microsoft 365 chính hãng. 

Nếu có thắc mắc, hãy trực tiếp để lại lời nhắn tại LiveChat phía dưới, hoặc bạn cũng có thể liên hệ qua các kênh liên lạc phía dưới để nhận được sự tư vấn và hỗ trợ chi tiết nhất từ đội ngũ chuyên gia giàu kinh nghiệm tại HVN Group. 

Fanpage: HVN Group – Hệ sinh thái kiến tạo 4.0

Hotline: 024.9999.7777