Cách dùng hàm SUBTOTAL Excel giúp bạn trở thành “cao thủ lọc dữ liệu”

Cách dùng hàm SUBTOTAL Excel là một kỹ năng quan trọng mà bất kỳ ai làm việc với dữ liệu trên Excel cũng nên nắm vững. Hàm này mang đến sự linh hoạt vượt trội so với các hàm tính toán cơ bản, đặc biệt khi bạn cần xử lý dữ liệu đã được lọc hoặc ẩn.

1. Hàm SUBTOTAL là gì? Vì sao dân Excel nên biết?

Hàm SUBTOTAL là một hàm tổng hợp mạnh mẽ trong Excel, được thiết kế để thực hiện các phép tính toán (như tính tổng, trung bình, đếm, v.v.) trên một tập hợp dữ liệu. Điểm đặc biệt và ưu việt nhất của cách dùng hàm SUBTOTAL Excel so với các hàm cơ bản như SUM, AVERAGE, COUNT là khả năng hoạt động linh hoạt với dữ liệu bị lọc (AutoFilter) hoặc bị ẩn.

Cụ thể, khi bạn lọc dữ liệu, các hàm như SUM chỉ tính tổng toàn bộ dữ liệu, kể cả những hàng bị ẩn đi. Nhưng khi bạn dùng hàm SUBTOTAL với dữ liệu lọc, hàm này sẽ tự động điều chỉnh và chỉ tính toán trên những ô dữ liệu đang hiển thị mà thôi. Điều này cực kỳ hữu ích khi bạn cần tạo các báo cáo động, nhanh chóng xem kết quả của các nhóm dữ liệu khác nhau mà không cần thay đổi công thức. Đây chính là lý do vì sao nắm vững cách dùng hàm SUBTOTAL Excel lại quan trọng đến vậy đối với dân văn phòng và phân tích dữ liệu.

2. Cấu trúc và các tham số của hàm SUBTOTAL

Để hiểu rõ cách dùng hàm SUBTOTAL Excel, bạn cần nắm vững cấu trúc và các tham số của hàm:

Cú pháp chuẩn của hàm SUBTOTAL là: =SUBTOTAL(function_num, ref1, [ref2], …)

Trong đó:

• function_num: Đây là tham số bắt buộc, là một số từ 1 đến 11 hoặc từ 101 đến 111, chỉ định loại phép tính mà bạn muốn thực hiện.
  • Các số từ 1 đến 11 sẽ bao gồm các ô bị ẩn thủ công (nếu có) vào phép tính, nhưng bỏ qua các ô bị ẩn bởi bộ lọc.
  • Các số từ 101 đến 111 sẽ bỏ qua cả các ô bị ẩn thủ công và các ô bị ẩn bởi bộ lọc.
• ref1, [ref2], …: Đây là các đối số bắt buộc, là các phạm vi ô hoặc tham chiếu mà bạn muốn thực hiện phép tính. Bạn có thể chỉ định tối đa 255 tham chiếu.

Bảng các mã số thao tác (function_num):

Ví dụ, để subtotal tính tổng trong Excel của một cột dữ liệu hiển thị sau khi lọc, bạn sẽ dùng function_num là 9 hoặc 109.

3. Cách dùng SUBTOTAL kết hợp với AutoFilter để lọc dữ liệu thông minh

Một trong những ứng dụng phổ biến và mạnh mẽ nhất của cách dùng hàm SUBTOTAL Excel là kết hợp với tính năng AutoFilter (Lọc tự động).

Hướng dẫn bật AutoFilter trong bảng dữ liệu:

• Chọn bất kỳ ô nào trong vùng dữ liệu bạn muốn lọc.
• Trên thanh công cụ, vào tab Data (Dữ liệu).
• Trong nhóm Sort & Filter (Sắp xếp & Lọc), bấm vào biểu tượng Filter (Lọc). Các mũi tên lọc sẽ xuất hiện ở tiêu đề cột.

Cách áp dụng hàm SUBTOTAL để chỉ tính phần dữ liệu đang hiển thị: Giả sử bạn có một bảng dữ liệu doanh số với các cột "Sản phẩm", "Doanh thu", "Khu vực". Bạn muốn tính tổng doanh thu chỉ của những sản phẩm đang hiển thị sau khi lọc.

• Tại ô bạn muốn hiển thị tổng doanh thu, nhập công thức: =SUBTOTAL(9, C2:C100) (Giả sử cột Doanh thu là cột C, từ hàng 2 đến hàng 100).
• Bây giờ, hãy thử dùng hàm SUBTOTAL với dữ liệu lọc:
  • Click vào mũi tên lọc ở cột "Khu vực".
  • Bỏ chọn "Select All" và chỉ chọn "Miền Bắc".
  • Ngay lập tức, bạn sẽ thấy ô công thức SUBTOTAL tự động cập nhật để hiển thị tổng doanh thu của riêng "Miền Bắc", bỏ qua các khu vực khác đang bị ẩn.

Ví dụ: Lọc theo sản phẩm, nhân viên, khu vực và xem kết quả thay đổi theo bộ lọc. Bất kể bạn lọc theo tiêu chí nào, hàm SUBTOTAL sẽ luôn trả về kết quả chính xác dựa trên các hàng đang hiển thị, giúp bạn dễ dàng phân tích và báo cáo dữ liệu linh hoạt.

4. Mẹo nâng cao giúp “thăng hạng” kỹ năng dùng hàm SUBTOTAL

Để thực sự "thăng hạng" kỹ năng cách dùng hàm SUBTOTAL Excel, bạn có thể áp dụng các mẹo nâng cao sau:

Kết hợp SUBTOTAL với Bảng (Table) để công thức tự cập nhật:

• Chuyển đổi vùng dữ liệu của bạn thành một Bảng (Table) bằng cách chọn dữ liệu, vào tab Insert (Chèn), chọn Table (Bảng).
• Khi áp dụng hàm SUBTOTAL cho một cột trong Bảng, bạn có thể tham chiếu cột bằng tên bảng và tên cột (ví dụ: Table1[Doanh thu]).
• Lợi ích là khi bạn thêm hoặc xóa hàng trong Bảng, công thức SUBTOTAL sẽ tự động cập nhật phạm vi, không cần phải điều chỉnh thủ công.

Chèn SUBTOTAL theo từng nhóm bằng tính năng Data → Subtotal:

• Sắp xếp (Sort) dữ liệu của bạn theo cột mà bạn muốn nhóm (ví dụ: theo "Khu vực").
• Vào tab Data (Dữ liệu), trong nhóm Outline (Dàn bài), chọn Subtotal.
• Trong hộp thoại Subtotal, bạn có thể chọn "At each change in" (Tại mỗi thay đổi trong) là "Khu vực", "Use function" (Sử dụng hàm) là "Sum" (hoặc bất kỳ function_num nào), và "Add subtotal to" (Thêm tổng phụ vào) là "Doanh thu".
• Excel sẽ tự động chèn các hàng tổng phụ và sử dụng hàm SUBTOTAL để tính tổng cho từng nhóm, tạo ra một báo cáo có cấu trúc rất rõ ràng.
• Hơn nữa, bạn có thể đánh số thứ tự với hàm SUBTOTAL bằng cách sử dụng SUBTOTAL(3, A2:A2) và kéo xuống. Khi lọc, số thứ tự sẽ tự động cập nhật theo các hàng hiển thị.

Tự động tạo báo cáo động: Việc kết hợp SUBTOTAL với lọc và các tính năng khác giúp bạn xây dựng các báo cáo động, tránh được lỗi tính trùng hoặc bỏ sót dữ liệu khi phải lọc qua nhiều lớp hoặc thay đổi tiêu chí lọc thường xuyên.

5. Khi nào không nên dùng hàm SUBTOTAL – và thay bằng hàm nào?

Mặc dù cách dùng hàm SUBTOTAL Excel rất mạnh mẽ, nhưng không phải lúc nào nó cũng là lựa chọn tối ưu.

• Tình huống dữ liệu không cần lọc hoặc không có hàng ẩn: Nếu bạn chỉ cần tính toán trên toàn bộ vùng dữ liệu và không có nhu cầu lọc hoặc ẩn hàng, việc sử dụng các hàm cơ bản như SUM, AVERAGE, COUNT sẽ đơn giản và dễ hiểu hơn. Ví dụ, để subtotal tính tổng trong Excel khi không có lọc, bạn chỉ cần dùng hàm SUM.
• Khi cần tính toán chính xác cả phần dữ liệu bị ẩn: Nếu mục đích của bạn là tính toán trên TẤT CẢ các ô trong một phạm vi, kể cả những ô bị ẩn bởi bộ lọc hoặc ẩn thủ công, thì hàm SUBTOTAL không phù hợp. Trong trường hợp này, bạn nên sử dụng các hàm truyền thống như SUM, AVERAGE, COUNT.
• Gợi ý kết hợp với AGGREGATE khi cần thêm linh hoạt và xử lý lỗi: Hàm AGGREGATE là một hàm thậm chí còn linh hoạt hơn SUBTOTAL. Nó có cấu trúc tương tự nhưng cung cấp nhiều tùy chọn hơn để bỏ qua các hàng bị ẩn, giá trị lỗi, hoặc các hàm SUBTOTAL khác. Nếu bạn cần mức độ kiểm soát cao hơn và khả năng xử lý lỗi tự động, AGGREGATE sẽ là lựa chọn thay thế tốt.

Nắm vững cách dùng hàm SUBTOTAL Excel sẽ giúp bạn nâng cao đáng kể hiệu quả làm việc với dữ liệu trên Excel, đặc biệt trong các tác vụ phân tích và báo cáo. Hãy luyện tập thường xuyên để tận dụng tối đa sức mạnh của hàm này!