Công thức SUBTOTAL() trong Excel: Hướng dẫn tính toán đầy đủ

Người dùng Excel thường cần những cách linh hoạt để tổng hợp dữ liệu có phản hồi theo bộ lọc hoặc nhóm. Các hàm truyền thống như SUM(), AVERAGE() hoặc COUNT() tính kết quả dựa trên tất cả ô trong một phạm vi, bất kể một số hàng có bị ẩn hay bị lọc. Hàm SUBTOTAL() cung cấp một lựa chọn động, tự động điều chỉnh phép tính dựa trên những gì đang hiển thị trong trang tính của bạn.

Nếu bạn mới dùng Excel và muốn xây nền tảng vững chắc trước khi khám phá các hàm nâng cao như SUBTOTAL(), our Introduction to Excel course covers essential skills including worksheet navigation, basic formulas, and data formatting techniques that will prepare you for more sophisticated Excel work.

Hàm SUBTOTAL() trong Excel là gì?

Hàm SUBTOTAL() tính các giá trị tổng hợp như tổng, trung bình, đếm và các phép thống kê khác trên một tập dữ liệu. Điểm khác biệt so với các hàm chuẩn của Excel là cách hoạt động động—thay đổi kết quả theo những gì đang hiển thị hoặc đã lọc. Hàm có thể bỏ qua cả các hàng bị ẩn thủ công và các hàng bị ẩn bởi bộ lọc, tùy theo số hiệu hàm bạn chỉ định.

Khi bạn áp dụng bộ lọc cho dữ liệu hoặc ẩn thủ công một số hàng, SUBTOTAL() tự động điều chỉnh phép tính để chỉ bao gồm các ô đang hiển thị. Hành vi này khiến nó trở thành lựa chọn tuyệt vời để tạo các hàng tóm tắt trong tập dữ liệu đã lọc, xây dựng bảng điều khiển tương tác hoặc tạo báo cáo cần cập nhật theo lựa chọn của người dùng.

Hàm hỗ trợ các phép thống kê giống như các hàm chuẩn của Excel—bao gồm tổng, trung bình, đếm, giá trị lớn nhất, nhỏ nhất và một số phép khác—nhưng gói chúng trong một hàm duy nhất, nhận biết ngữ cảnh, có thể thích ứng với trạng thái hiện tại của dữ liệu.

Cú pháp và các phép hỗ trợ

Hàm SUBTOTAL() tuân theo cấu trúc cú pháp đơn giản:

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

Tham số đầu tiên, function_num xác định cả loại phép tính và cách hàm xử lý các hàng bị ẩn. Các tham số còn lại (ref1, ref2, v.v.) là các vùng ô bạn muốn đưa vào phép tính. Bạn có thể chỉ định tối đa 254 phạm vi khác nhau.

Các số hiệu hàm chia thành hai nhóm dựa trên cách xử lý các hàng bị ẩn thủ công:

Các số hiệu 1-11 bao gồm các ô từ những hàng bị ẩn thủ công trong phép tính, trong khi các số hiệu 101-111 loại trừ chúng. Tuy nhiên, cả hai dải số này luôn bỏ qua các hàng bị ẩn bởi bộ lọc.

Ví dụ, SUBTOTAL(9, A2:A10) tính tổng A2:A10 bao gồm cả các hàng bị ẩn thủ công, trong khi SUBTOTAL(109, A2:A10) loại trừ các hàng bị ẩn thủ công khỏi phép tính tổng. Ở cả hai trường hợp, các hàng bị lọc luôn tự động bị loại trừ khỏi kết quả.

Ví dụ thực tế về hàm SUBTOTAL()

Hãy xem SUBTOTAL() hoạt động thế nào qua một ví dụ thực tế với dữ liệu bán hàng của cửa hàng điện tử và nội thất.

Tập dữ liệu chưa lọc. Hình do Tác giả cung cấp. 

Dữ liệu mẫu gồm bảy sản phẩm thuộc hai danh mục - bốn mặt hàng Điện tử (Laptop, Phone, Tablet, Monitor) và ba mặt hàng Nội thất (Desk, Chair, Lamp). Khi chưa áp dụng bộ lọc, cả công thức SUBTOTAL(109,C2:C8) và công thức SUM(C2:C8) thông thường đều cho cùng một kết quả: 3525 (tổng doanh số).

Tập dữ liệu đã lọc. Hình do Tác giả cung cấp. 

Giờ chúng ta đã áp dụng bộ lọc để chỉ hiển thị các sản phẩm Điện tử, ẩn ba hàng của Nội thất. Hãy chú ý khác biệt chính trong kết quả tính toán:

• Công thức SUBTOTAL cho kết quả 3000 - tự động chỉ cộng các hàng Điện tử đang hiển thị
• Công thức SUM thông thường vẫn cho kết quả 3525 - tiếp tục bao gồm các mặt hàng Nội thất bị ẩn

So sánh cạnh nhau này cho thấy lợi thế cốt lõi của SUBTOTAL(): phản hồi động với bộ lọc và thay đổi hiển thị dữ liệu, trong khi các hàm truyền thống như SUM() thì tĩnh. Khi bạn lọc dữ liệu hoặc ẩn hàng, SUBTOTAL() sẽ tính lại tức thì để phản ánh những gì đang hiển thị, khiến nó trở nên thiết yếu cho bảng điều khiển linh hoạt và báo cáo tương tác.

SUBTOTAL() hoạt động thế nào với dữ liệu ẩn và đã lọc

Hàm SUBTOTAL() xử lý các loại dữ liệu bị ẩn theo những cách cụ thể, vì vậy việc hiểu các hành vi này là rất quan trọng khi xây dựng công thức.

SUBTOTAL() với các hàng bị lọc

Khi bạn áp dụng bộ lọc cho tập dữ liệu, SUBTOTAL() luôn loại trừ các hàng bị lọc khỏi phép tính, bất kể bạn dùng số hiệu hàm nào. Hành vi này là tự động và nhất quán trên tất cả số hiệu (cả dải 1-11 và 101-111).

Ví dụ, nếu bạn có dữ liệu doanh số từ tháng 1 đến tháng 12 và áp dụng bộ lọc để chỉ hiển thị các tháng Quý 1 (tháng 1, 2, 3), SUBTOTAL(9, B2:B13) sẽ tính tổng chỉ cho ba tháng đang hiển thị, bỏ qua dữ liệu Quý 2-Quý 4 đã lọc.

SUBTOTAL() với các hàng ẩn thủ công

Các hàng ẩn thủ công được xử lý khác nhau tùy theo lựa chọn số hiệu hàm của bạn. Khi bạn nhấp chuột phải vào số hàng và chọn "Hide", bạn đang ẩn thủ công các hàng đó.

Dùng các số hiệu 1-11 sẽ bao gồm các hàng ẩn thủ công trong phép tính. Dùng các số hiệu 101-111 sẽ loại trừ chúng. Điều này cho bạn quyền kiểm soát việc dữ liệu ẩn có ảnh hưởng đến kết quả hay không.

SUBTOTAL() với phạm vi ngang và dọc

Hàm SUBTOTAL() hoạt động tốt nhất với các phạm vi dữ liệu theo chiều dọc (cột dữ liệu). Dù có thể xử lý phạm vi ngang, hành vi ẩn/hiện của nó được tối ưu cho các thao tác theo hàng vì tính năng lọc và ẩn hàng của Excel hoạt động theo chiều dọc.

Khi bạn lồng các công thức SUBTOTAL() trong cùng một phạm vi, hàm sẽ tự động bỏ qua các kết quả SUBTOTAL() khác để tránh tính trùng. Điều này đặc biệt hữu ích khi tạo các tóm tắt phân cấp hoặc subtotal theo nhóm, đảm bảo tổng của bạn phản ánh chính xác từng nhóm riêng biệt mà không bị trùng lặp.

Các cách dùng nâng cao của hàm SUBTOTAL()

Tạo bản tóm tắt động bằng danh sách thả xuống với SUBTOTAL()

Bạn có thể kết hợp SUBTOTAL() với hàm IF() để tạo các bản tóm tắt tương tác thay đổi theo lựa chọn của người dùng. Cách làm này sử dụng danh sách thả xuống để cho phép người dùng chuyển đổi giữa các loại phép tính.

Để tạo bản tóm tắt động, trước tiên thiết lập danh sách thả xuống ở ô D1 với các tùy chọn như "Sum", "Average", "Count" và "Max".

Sau đó, dùng công thức sau ở ô khác (ví dụ D2) để tự động điều chỉnh phép tính theo lựa chọn của người dùng:

=IF(D1="Sum",SUBTOTAL(109,A2:A10),
  IF(D1="Average",SUBTOTAL(101,A2:A10),
  IF(D1="Count",SUBTOTAL(103,A2:A10),
  IF(D1="Max",SUBTOTAL(104,A2:A10),"Select Option"))))

Công thức này đánh giá lựa chọn trong danh sách thả xuống và áp dụng hàm SUBTOTAL() tương ứng. Người dùng có thể chuyển đổi loại phép tính mà không cần chỉnh sửa công thức, giúp báo cáo tương tác và thân thiện hơn.

SUBTOTAL() trong bảng và dàn ý (outline) của Excel

Bảng Excel tự động dùng SUBTOTAL() trong tính năng Total Row. Khi bạn chuyển vùng dữ liệu thành Bảng (Ctrl+T) và bật Total Row, Excel mặc định chèn SUBTOTAL(109, …) cho phép tính tổng. Hàng Tổng này cập nhật động bất cứ khi nào các hàng được lọc, điều chỉnh kết quả tức thì để chỉ phản ánh dữ liệu hiển thị.

Bạn có thể thay đổi loại phép tính bằng cách nhấp vào mũi tên thả xuống ở bất kỳ ô nào trong Total Row và chọn các tùy chọn như Count, Average, Max hoặc Min. Excel sẽ tự động cập nhật số hiệu hàm nhưng vẫn giữ cấu trúc SUBTOTAL().

Tính năng Outline (Data > Subtotal) cũng dựa vào các công thức SUBTOTAL(). Khi bạn nhóm dữ liệu và chèn các subtotal, Excel tự động đặt các hàm SUBTOTAL() tại điểm ngắt nhóm. Điều này tạo các bản tóm tắt phân cấp phản hồi đúng khi bạn mở rộng hoặc thu gọn các cấp độ outline, vì hàm sẽ bỏ qua các kết quả subtotal lồng nhau.

Những lỗi thường gặp và giới hạn

Hiểu các lỗi thường gặp và giới hạn của SUBTOTAL() giúp bạn tránh sai sót tính toán và chọn phương án phù hợp cho nhu cầu phân tích dữ liệu.

• Lỗi #VALUE! với số hiệu hàm không hợp lệ: Lỗi phổ biến nhất xảy ra khi dùng số hiệu hàm không hợp lệ. SUBTOTAL() chỉ chấp nhận các số hiệu 1-11 và 101-111. Dùng các số như 12, 50 hoặc 200 sẽ trả về lỗi #VALUE!. Luôn kiểm tra số hiệu hàm tương ứng với một phép hợp lệ trong các dải được hỗ trợ.

• Hành vi cột ẩn so với hàng ẩn: Trong khi SUBTOTAL() phản hồi với hàng ẩn dựa trên lựa chọn số hiệu hàm của bạn, nó không có hành vi tương tự với cột ẩn. Hàm vẫn bao gồm dữ liệu từ các cột ẩn trong mọi phép tính, bất kể bạn dùng số hiệu 1-11 hay 101-111. Giới hạn này có nghĩa bạn cần cách tiếp cận khác khi làm việc với tập dữ liệu mà khả năng hiển thị cột ảnh hưởng đến phân tích.

• Không hỗ trợ tham chiếu 3D: Không giống nhiều hàm Excel, SUBTOTAL() không thể tham chiếu phạm vi trên nhiều trang tính. Các công thức như SUBTOTAL(109, Sheet1:Sheet3!A1:A10) sẽ trả về lỗi. Cách khắc phục là trước tiên tính tổng hoặc các giá trị tổng hợp khác riêng trên mỗi trang bằng các hàm chuẩn như SUM(), rồi hợp nhất các kết quả trung gian này bằng một SUBTOTAL duy nhất trên trang tóm tắt.

• Giá trị lỗi vẫn nằm trong phép tính: SUBTOTAL() không bỏ qua các ô chứa giá trị lỗi như #N/A, #DIV/0! hoặc #VALUE!. Các lỗi này lan truyền qua phép tính subtotal, có thể làm sai toàn bộ kết quả. Hãy làm sạch dữ liệu lỗi trước khi áp dụng SUBTOTAL(), hoặc cân nhắc dùng hàm AGGREGATE() có thể bỏ qua giá trị lỗi.

• Dữ liệu theo chiều dọc hoạt động tốt nhất: Mặc dù SUBTOTAL() có thể xử lý phạm vi ngang, thiết kế của nó tối ưu cho cấu trúc dữ liệu dọc. Tính năng lọc, sắp xếp và ẩn của Excel hoạt động theo từng hàng, khiến cách bố trí dọc tương thích hơn với hành vi dự kiến của hàm.

Subtotal() so với AGGREGATE()

Hàm AGGREGATE() là lựa chọn nâng cao thay thế cho SUBTOTAL() với các khả năng bổ sung để xử lý lỗi và nhiều phép thống kê hơn. Trong khi SUBTOTAL() cung cấp 11 phép cơ bản, AGGREGATE() có 19 hàm khác nhau, bao gồm phần trăm vị trí, tứ phân vị và trung vị.

Ưu điểm chính của AGGREGATE() nằm ở khả năng xử lý lỗi. Không giống SUBTOTAL(), vốn bao gồm giá trị lỗi trong phép tính, AGGREGATE() có thể tự động bỏ qua các ô chứa lỗi như #N/A, #DIV/0! hoặc #VALUE!. Điều này đặc biệt hữu ích cho các tập dữ liệu chưa hoàn chỉnh hoặc có vấn đề.

AGGREGATE() cũng cung cấp khả năng kiểm soát chi tiết hơn về những gì cần bỏ qua. Bạn có thể cấu hình để bỏ qua hàng ẩn, subtotal lồng nhau, giá trị lỗi hoặc bất kỳ kết hợp nào của các yếu tố này thông qua tham số tùy chọn của hàm.

Mẹo hay khi dùng SUBTOTAL()

Luôn sắp xếp trước

Khi dùng tính năng Subtotal tích hợp sẵn (Data > Subtotal) của Excel, hãy sắp xếp dữ liệu theo cột nhóm trước. Điều này đảm bảo tất cả bản ghi liên quan xuất hiện cùng nhau, tạo điểm ngắt nhóm rõ ràng cho phép tính subtotal. Dữ liệu không sắp xếp tạo ra các subtotal rải rác, không mang lại tóm tắt ý nghĩa.

Dùng nhãn ở hàng đầu của các cột

Đặt tiêu đề mô tả ở hàng đầu tiên của vùng dữ liệu trước khi áp dụng subtotal. Excel dùng các nhãn này để tạo mô tả subtotal có ý nghĩa và giúp báo cáo dễ hiểu hơn. Tiêu đề cột rõ ràng cũng hữu ích khi chọn phạm vi cho các công thức SUBTOTAL() thủ công.

Lưu ý vị trí tóm tắt (trên hay dưới)

Công cụ Subtotal của Excel mặc định đặt hàng tóm tắt bên dưới mỗi nhóm, nhưng bạn có thể chọn đặt ở phía trên. Hãy cân nhắc mục đích báo cáo khi đưa ra lựa chọn. Hàng tóm tắt ở trên phù hợp với bảng điều khiển cấp quản trị, trong khi hàng tóm tắt ở dưới phù hợp với định dạng kế toán truyền thống.

Ẩn so với lọc hàng để kiểm soát chính xác

Hiểu sự khác biệt giữa lọc và ẩn thủ công hàng để có kết quả như mong đợi. Dùng bộ lọc khi bạn muốn SUBTOTAL() tạm thời bỏ qua một số dữ liệu. Dùng ẩn thủ công (right-click > Hide) khi bạn muốn loại trừ dài hạn hơn, sau đó chọn các số hiệu 101-111 để tôn trọng trạng thái ẩn.

Cách tiếp cận này cho bạn quyền kiểm soát theo lớp: lọc cho các chế độ xem dữ liệu tạm thời, ẩn thủ công cho loại trừ bán cố định, và chọn số hiệu hàm phù hợp để tuân theo ý định ẩn của bạn.

Kết luận

Thành thạo SUBTOTAL() sẽ thay đổi cách bạn tiếp cận phân tích dữ liệu trong Excel. Thay vì tạo các phép tính tĩnh dễ hỏng khi dữ liệu thay đổi, bạn có thể xây dựng công thức bền vững tự động thích ứng với thao tác của người dùng và các điều chỉnh dữ liệu. Kỹ năng này đặc biệt giá trị khi làm việc với tập dữ liệu lớn hoặc tạo báo cáo mà nhiều người dùng sẽ lọc và thao tác theo thời gian.

Để xây dựng chuyên môn Excel toàn diện vượt ra ngoài các hàm riêng lẻ như SUBTOTAL(), our Excel Fundamentals skill track provides a structured 16-hour learning path that takes you from data preparation through advanced analysis and visualization techniques. For readers ready to explore analytical applications immediately, our Data Analysis in Excel course teaches PivotTable mastery and advanced logical functions for deeper insights.