Hàm SUBTOTAL trong Excel: Cách dùng, công thức & ví dụ

Trong quá trình xử lý dữ liệu Excel, chắc hẳn bạn đã từng gặp trường hợp khi sử dụng hàm SUM để tính tổng, nhưng khi dùng Filter để lọc dữ liệu thì kết quả tổng vẫn giữ nguyên mà không thay đổi theo những gì hiển thị trên màn hình. Đó chính là lúc bạn cần đến hàm Subtotal. Bài viết này, Starttrain sẽ giúp bạn hiểu rõ Subtotal trong Excel là gì, công thức và cách ứng dụng linh hoạt hàm này trong công việc.

Subtotal trong Excel là gì?

Hàm SUBTOTAL trong Excel là một hàm toán học đa năng, cho phép bạn thực hiện 11 phép tính khác nhau (như tính tổng, trung bình cộng, đếm ô, tìm giá trị lớn nhất/nhỏ nhất…) chỉ với một cú pháp duy nhất.

Tính năng cốt lõi của hàm Subtotal trong Excel

Khác với các hàm đơn lẻ như SUM hay AVERAGE, SUBTOTAL được thiết kế đặc biệt để làm việc với các danh sách dữ liệu động và các báo cáo phân cấp. Dưới đây là những đặc điểm cốt lõi giúp SUBTOTAL trở nên khác biệt:

Khả năng thông minh theo bộ lọc

Đây là tính năng quan trọng nhất. Khi bạn áp dụng Filter để lọc dữ liệu, hàm SUBTOTAL sẽ tự động nhận diện và chỉ tính toán trên những hàng đang hiển thị. Những hàng bị ẩn đi bởi bộ lọc sẽ hoàn toàn bị loại bỏ khỏi kết quả tính toán. Điều này giúp bạn có được con số chính xác ngay lập tức cho từng danh mục cụ thể mà không cần viết lại công thức.

Cơ chế chống tính trùng lặp (Double Counting)

Trong các báo cáo phức tạp thường có các dòng “Tổng phụ” (Subtotal) cho từng nhóm và một dòng “Tổng cộng” (Grand Total) ở cuối. Nếu bạn dùng hàm SUM, dòng Tổng cộng sẽ bao gồm cả các dòng Tổng phụ, dẫn đến kết quả bị sai (gấp đôi thực tế). Hàm SUBTOTAL có cơ chế thông minh: nó tự động bỏ qua các ô có chứa hàm SUBTOTAL khác nằm trong vùng tham chiếu. Nhờ vậy, bạn có thể tính tổng của cả một cột chứa nhiều tiểu mục mà không lo bị cộng dồn sai lệch.

Linh hoạt với các hàng bị ẩn thủ công

SUBTOTAL cung cấp cho người dùng quyền kiểm soát tuyệt đối đối với các hàng bị ẩn thủ công (Hide rows). Tùy vào mã lệnh bạn chọn, hàm có thể tính cả hàng ẩn hoặc bỏ qua chúng, giúp việc trình bày báo cáo trở nên linh hoạt hơn rất nhiều so với các hàm thông thường.

Công thức hàm Subtotal trong Excel

Cú pháp hàm Subtotal trong Excel

Cấu trúc của hàm SUBTOTAL rất đơn giản nhưng cực kỳ mạnh mẽ:

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

Trong đó các thành phần chi tiết bao gồm:

• function_num (Đối số bắt buộc): Đây là con số xác định loại hàm bạn muốn thực hiện (như SUM, AVERAGE, COUNT…). Con số này có thể từ 1 đến 11 hoặc từ 101 đến 111.
  • Nếu dùng mã 1-11: Hàm sẽ tính toán bao gồm cả các hàng bạn ẩn thủ công (nhưng vẫn bỏ qua các hàng bị ẩn do Filter).
  • Nếu dùng mã 101-111: Hàm sẽ bỏ qua hoàn toàn các hàng bị ẩn (cả ẩn thủ công và ẩn do Filter).
• ref1 (Đối số bắt buộc): Vùng dữ liệu, dãy ô hoặc tên vùng đầu tiên mà bạn muốn tính toán.
• ref2, … (Đối số tùy chọn): Các vùng dữ liệu bổ sung (tối đa lên tới 254 vùng) mà bạn muốn đưa vào phép tính.

Lưu ý: Hàm SUBTOTAL được thiết kế để hoạt động theo cột (dọc). Nếu vùng tham chiếu của bạn chứa các hàng bị ẩn, kết quả sẽ thay đổi tùy theo function_num, nhưng nếu bạn ẩn các cột (ngang), kết quả sẽ không thay đổi.

Bảng mã Function_num trong hàm Subtotal

Excel cung cấp 2 bộ mã cho đối số function_num. Việc chọn bộ mã nào sẽ quyết định cách hàm xử lý các hàng bị ẩn thủ công.

Lưu ý: Cả hai bộ mã đều tự động bỏ qua các hàng bị ẩn do bộ lọc (Filter). Sự khác biệt chỉ xuất hiện khi bạn chuột phải vào hàng và chọn Hide.

Tổng hợp 200+ phím tắt Excel từ cơ bản đến nâng cao

Ví dụ việc sử dụng hàm Subtotal trong Excel

Ví dụ 1: Tính tổng doanh số khi lọc dữ liệu theo vùng miền

Giả sử bạn có bảng dữ liệu từ ô A1 đến C10, trong đó cột C là doanh số.

• Nếu bạn dùng =SUM(C2:C10), kết quả luôn là tổng toàn bộ dù bạn có lọc xem riêng “Miền Bắc” hay không.
• Nếu bạn dùng =SUBTOTAL(9, C2:C10), khi bạn dùng Filter để chọn “Miền Bắc”, Excel sẽ ngay lập tức trả về tổng doanh số của riêng Miền Bắc.

Ví dụ 2: Đếm số lượng sản phẩm đang hiển thị

Bạn có một danh sách 100 sản phẩm và muốn biết sau khi lọc theo “Loại hàng” thì còn bao nhiêu mã hàng đang hiển thị:

• Công thức: =SUBTOTAL(3, B2:B101) (Trong đó mã 3 là hàm COUNTA – đếm ô không trống).
• Kết quả sẽ thay đổi liên tục tương ứng với số lượng hàng bạn nhìn thấy trên màn hình sau mỗi lần thay đổi bộ lọc.

Tại sao phải dùng SUBTOTAL thay cho các hàm thống kê thông thường?

Nhiều người dùng thắc mắc tại sao phải dùng một hàm có mã số phức tạp như SUBTOTAL thay vì các hàm như SUM hay AVERAGE. Câu trả lời nằm ở 3 giá trị cốt lõi mà chỉ SUBTOTAL mới có:

• Tính linh động tuyệt đối với Filter: Các hàm thống kê thông thường (SUM, COUNT, MAX…) luôn tính toán dựa trên toàn bộ vùng dữ liệu được chọn, bất kể hàng đó có đang bị ẩn đi bởi bộ lọc hay không. Điều này khiến báo cáo bị sai lệch hoàn toàn khi bạn thực hiện phân tích dữ liệu theo nhóm. SUBTOTAL giải quyết triệt để vấn đề này bằng cách chỉ “nhìn” thấy những gì bạn thấy trên màn hình.
• Loại bỏ lỗi cộng dồn (Double Counting): Đây là “vũ khí bí mật” của SUBTOTAL. Nếu bạn dùng hàm SUM để tính tổng cuối cho một cột đã có sẵn các dòng tổng phụ bên trên, kết quả sẽ bị nhân đôi. Ngược lại, SUBTOTAL có khả năng tự động nhận diện và bỏ qua các hàm SUBTOTAL khác trong vùng tham chiếu. Điều này cho phép bạn xây dựng cấu trúc báo cáo đa tầng một cách chính xác.
• Khả năng kiểm soát dữ liệu ẩn thủ công: Trong khi hàm SUM luôn tính cả hàng bị ẩn thủ công (Hide), SUBTOTAL cho phép bạn lựa chọn: dùng mã 1-11 để tính cả hàng ẩn hoặc 101-111 để loại bỏ chúng. Sự phân hóa này cực kỳ hữu ích khi bạn cần ẩn bớt các dòng dữ liệu nháp hoặc sai lệch mà không muốn xóa chúng khỏi bảng tính.

Những lưu ý quan trọng khi dùng Subtotal

Để hàm SUBTOTAL hoạt động chính xác và hiệu quả, bạn cần nắm rõ những quy tắc ngầm sau đây:

• Chỉ hoạt động theo chiều dọc (Cột): SUBTOTAL được thiết kế để tính toán dữ liệu trong các cột. Nếu bạn ẩn các cột (chiều ngang), hàm này sẽ không loại bỏ các giá trị trong cột ẩn đó khỏi kết quả tính toán. Nó chỉ thực sự hiệu quả khi bạn ẩn hàng (chiều dọc).
• Bỏ qua các hàm SUBTOTAL lồng nhau: Như đã đề cập, đây là một tính năng cực kỳ thông minh. Nếu trong vùng dữ liệu bạn chọn (ref1, ref2…) đã có sẵn các công thức SUBTOTAL khác, Excel sẽ tự động lờ chúng đi để tránh tính trùng dữ liệu. Điều này không áp dụng cho hàm SUM hay các hàm khác.
• Phân biệt hàng ẩn do Filter và hàng ẩn thủ công:
  • Nếu hàng bị ẩn do lệnh Filter, cả mã 9 và mã 109 đều bỏ qua hàng đó.
  • Nếu hàng bị ẩn thủ công (Right click -> Hide), chỉ có mã 109 mới bỏ qua, còn mã 9 vẫn tính luôn cả giá trị của hàng ẩn đó.
• Không hỗ trợ tham chiếu 3D: Bạn không thể sử dụng hàm SUBTOTAL để tham chiếu qua nhiều trang tính (Worksheet) khác nhau theo kiểu Sheet1:Sheet3!A1:A10. Vùng tham chiếu phải nằm trong cùng một Sheet.
• Cẩn thận với dữ liệu không phải số: Nếu vùng tham chiếu chứa các giá trị lỗi (như #VALUE!, #DIV/0!), hàm SUBTOTAL sẽ trả về lỗi tương tự. Hãy đảm bảo dữ liệu của bạn sạch trước khi tính toán.
• Tự động cập nhật: Kết quả của hàm SUBTOTAL sẽ thay đổi ngay lập tức mỗi khi bạn thay đổi bộ lọc hoặc ẩn/hiện hàng. Điều này có thể làm chậm file Excel nếu bảng dữ liệu của bạn quá lớn (hàng trăm ngàn dòng).

Tính năng Automatic Subtotal trong thẻ Data

Bên cạnh việc sử dụng hàm, Excel còn cung cấp một công cụ mạnh mẽ giúp tự động hóa việc chèn các dòng tổng phụ vào bảng dữ liệu – đó là tính năng Subtotal trong thẻ Data. Đây là giải pháp hoàn hảo để tạo báo cáo nhanh mà không cần nhập công thức thủ công.

Các bước thực hiện

• Sắp xếp dữ liệu (Bắt buộc): Để công cụ hoạt động đúng, bạn phải sắp xếp (Sort) cột dữ liệu mà bạn muốn nhóm. Ví dụ: Nếu muốn tính tổng theo “Phòng ban”, hãy sắp xếp cột Phòng ban theo thứ tự A-Z.
• Mở công cụ Subtotal: Chọn một ô bất kỳ trong vùng dữ liệu, vào thẻ Data -> Nhóm Outline -> Chọn Subtotal.
• Thiết lập bảng tùy chọn:
  • At each change in: Chọn cột bạn đã sắp xếp ở bước 1 (nơi Excel sẽ chèn dòng tổng mỗi khi giá trị thay đổi).
  • Use function: Chọn phép tính muốn dùng (thường là Sum để tính tổng, hoặc Count để đếm).
  • Add subtotal to: Tích chọn các cột chứa giá trị số mà bạn muốn tính toán.
  • Replace current subtotals: Nếu tích chọn, Excel sẽ xóa các dòng Subtotal cũ để thay thế bằng cái mới.
  • Summary below data: Hiển thị dòng tổng cộng ở bên dưới mỗi nhóm (nên để mặc định).

Cách quản lý hiển thị (Outline Levels)

Sau khi nhấn OK, phía bên trái hàng tiêu đề sẽ xuất hiện các con số 1, 2, 3 đại diện cho các cấp độ hiển thị:

• Cấp độ 1: Chỉ hiển thị dòng Tổng cộng cuối cùng (Grand Total).
• Cấp độ 2: Hiển thị các dòng Tổng phụ của từng nhóm và Tổng cộng.
• Cấp độ 3: Hiển thị chi tiết toàn bộ dữ liệu kèm theo các dòng tổng.

Mẹo: Để xóa bỏ hoàn toàn các dòng Subtotal tự động và đưa bảng về trạng thái ban đầu, bạn chỉ cần vào lại thẻ Data -> Subtotal -> Nhấn nút Remove All.

Kết luận

Hàm SUBTOTAL trong Excel không chỉ đơn thuần là một công cụ tính toán, mà còn là “bí quyết” giúp bạn quản lý dữ liệu động một cách thông minh và chuyên nghiệp. Khả năng tương tác mượt mà với bộ lọc (Filter) và cơ chế chống cộng dồn là những ưu điểm vượt trội mà các hàm thống kê thông thường không thể thay thế được.

Bằng cách hiểu rõ sự khác biệt giữa các mã function_num và biết cách tận dụng tính năng tự động trong thẻ Data, bạn sẽ tiết kiệm được rất nhiều thời gian và đảm bảo độ chính xác tuyệt đối cho mọi báo cáo. Hy vọng bài hướng dẫn chi tiết này đã giúp bạn tự tin làm chủ hàm SUBTOTAL. Chúc bạn ứng dụng thành công và nâng cao hiệu suất làm việc với Excel.

Để thực sự làm chủ dữ liệu và không còn lo lắng về các sai sót con số, bạn có thể tham khảo khóa học Excel for Business Analytics thành thạo Excel nâng cáo trong phân tích dữ liệu và báo cáo động.