Hàm SUBTOTAL trong Excel: Cách dùng chi tiết kèm ví dụ thực tế

Bạn đang làm việc với bảng tính dày đặc dữ liệu và muốn tổng hợp thông minh mà không làm rối công thức? Hàm SUBTOTAL trong Excel chính là “trợ thủ đắc lực” bạn đang tìm kiếm. Không chỉ giúp tính tổng, trung bình, đếm số dòng… một cách linh hoạt, hàm SUBTOTAL còn có khả năng nhìn thấy và ẩn đi dữ liệu theo cách bạn mong muốn, đặc biệt hữu ích khi bạn lọc bảng dữ liệu. Hãy cùng AntBook tìm hiểu chi tiết với bài viết dưới đây nhế!

1. Hàm SUBTOTAL trong Excel là gì? Ứng dụng

Hàm SUBTOTAL trong Excel là một công cụ vô cùng linh hoạt, cho phép bạn thực hiện các phép tính như tổng, trung bình, đếm, tìm giá trị lớn nhất/nhỏ nhất, độ lệch chuẩn, phương sai,… trên một phần dữ liệu nhất định, hay còn gọi là tập hợp con của một danh sách hoặc bảng tính.

Điểm đặc biệt của hàm này là khả năng tự động bỏ qua các hàng bị ẩn hoặc lọc (tùy theo cách sử dụng), giúp bạn phân tích dữ liệu chính xác và tinh gọn hơn rất nhiều so với các hàm thông thường như SUM, AVERAGE, COUNT…

Hàm SUBTOTAL trong Excel được ứng dụng trong nhiều trường hợp

Bảng các mã function_num và ý nghĩa:

Lưu ý: Các mã 1-11 vẫn tính cả hàng ẩn, trong khi mã 101–111 sẽ bỏ qua các hàng đã ẩn thủ công. Đây là điểm giúp hàm SUBTOTAL vượt trội khi làm việc với dữ liệu lọc hoặc báo cáo động.

2. Công thức, cách sử dụng hàm SUBTOTAL trong Excel

Công thức chuẩn của hàm SUBTOTAL trong Excel được viết như sau:

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

Trong đó:

• function_num: Là mã số quy định phép tính mà bạn muốn thực hiện. Điểm đặc biệt là:
  • Nếu bạn sử dụng các giá trị từ 1 đến 11, Excel sẽ tính toán bao gồm cả các hàng bị ẩn trong vùng dữ liệu.
  • Ngược lại, nếu chọn mã từ 101 đến 111, Excel sẽ bỏ qua những hàng đã được ẩn thủ công (bằng cách ẩn hàng, không phải do lọc dữ liệu), rất hữu ích khi làm báo cáo hoặc xử lý dữ liệu đã lọc.
• ref1, ref2,…: Là các ô hoặc vùng dữ liệu mà bạn muốn áp dụng phép tính. Hàm có thể xử lý tối đa 254 vùng tham chiếu, mang lại khả năng linh hoạt cao khi thao tác với bảng tính lớn.

Công thức hàm SUBTOTAL=SUBTOTAL(function_num, ref1, ref2,…)

3. Ví dụ cách dùng hàm SUBTOTAL trong Excel thực tế

Hàm SUBTOTAL là công cụ cực kỳ linh hoạt khi bạn cần tính tổng, đếm dữ liệu, đánh số thứ tự trong bảng tính Excel, đặc biệt là với dữ liệu đã lọc hoặc có dòng bị ẩn. Dưới đây là ba ví dụ minh họa cụ thể giúp bạn hiểu rõ cách áp dụng hàm SUBTOTAL trong thực tế.

Ví dụ 1: Tính tổng vùng dữ liệu sau khi lọc với hàm SUBTOTAL

Tình huống: Giả sử bạn có bảng thống kê KPI của các đội và cần tính tổng KPI của đội A.

Cách thực hiện:

Bước 1: Chọn toàn bộ bảng dữ liệu → vào tab Home → chọn Sort & Filter → chọn Filter để bật chức năng lọc.

Vào Home, ấn Sort and Filter, chọn Filter

Bước 2: Nhấn biểu tượng mũi tên tại tiêu đề cột “Đội” → chọn “A” → nhấn OK để lọc dữ liệu theo đội A.

Bấm chọn dấu mũi tên hình tam giác ngược ở tiêu đề cột “Đội”, tick chọn “A” và nhấn OK

Bước 3: Tại ô kết quả, nhập công thức:

=SUBTOTAL(9, D2:D11)

Giải thích công thức:

• 9 là mã hàm đại diện cho SUM – tính tổng, áp dụng cho cả dữ liệu bị ẩn thủ công.
• D2:D11 là vùng dữ liệu cần tính tổng.

Lưu ý: Nếu muốn bỏ qua các hàng bị ẩn thủ công (ví dụ dùng lệnh Hide), bạn có thể thay 9 bằng 109 để kết quả chỉ tính các hàng thực sự hiển thị sau khi lọc.

Kết quả trả về như trên

Ví dụ 2: Đếm số ô không trống sau khi lọc bằng hàm SUBTOTAL Excel

Tình huống: Bạn có bảng danh sách sinh viên kèm theo cột “Link bài tập”. Nhiệm vụ là đếm số sinh viên thuộc nhóm C đã có link nộp bài.

Cách làm:

Bước 1: Thực hiện lọc nhóm C tương tự như ví dụ 1.

Bước 2: Nhập công thức tại ô kết quả:

=SUBTOTAL(3, D4:D14)

Giải thích công thức:

• 3 là mã hàm tương ứng với COUNTA – đếm số ô không trống, bao gồm cả các ô bị ẩn bằng thao tác Hide.
• D4:D14 là vùng chứa các dữ liệu cần đếm.

Kết quả cuối cùng nhận về

Mẹo nhỏ: Để loại trừ các ô bị ẩn thủ công, bạn có thể dùng 103 thay cho 3. Khi đó, hàm chỉ đếm dữ liệu hiển thị sau khi lọc, đảm bảo độ chính xác khi thống kê.

Ví dụ 3: Dùng hàm SUBTOTAL đánh số thứ tự động theo dữ liệu lọc

Tình huống: Bạn cần đánh số thứ tự cho danh sách dữ liệu và muốn các số thứ tự này cập nhật chính xác ngay cả khi áp dụng bộ lọc.

Cách làm:

Tại ô A2, nhập công thức:

=SUBTOTAL(3, $B$2:B2)

Giải thích công thức:

• 3 là mã hàm COUNTA – đếm các ô không trống.
• $B$2:B2 là vùng tham chiếu bắt đầu từ dòng đầu tiên đến dòng hiện tại. Việc cố định $B$2 giúp công thức cập nhật chính xác khi kéo xuống các dòng bên dưới.

Khi sao chép công thức xuống các dòng còn lại, Excel sẽ tự động cập nhật phạm vi, đảm bảo số thứ tự đúng với các hàng đang hiển thị sau khi lọc.

Di chuột đến góc dưới, bên phải ô và kéo thả chuột dấu cộng vừa hiển thị đến hết ô cuối cùng

Tuy nhiên, trong một số trường hợp phức tạp hơn, bạn có thể gặp phải bảng dữ liệu có các hàng trống và không trống lộn xộn. Khi đó, việc chỉ dùng hàm SUBTOTAL để đánh số thứ tự sẽ không mang lại kết quả chính xác.

Giải pháp là kết hợp hàm SUBTOTAL với hàm IF để kiểm soát điều kiện đếm. Cách này đặc biệt hữu ích khi bạn muốn đánh số thứ tự tự động theo cột, đồng thời đảm bảo rằng các hàng trống không bị đánh số.

Đánh số thứ tự có điều kiện bằng IF kết hợp SUBTOTAL

Cú pháp tổng quát:

=IF(logical_test, value_if_true, SUBTOTAL(function_num, ref1,…))

Ví dụ minh họa:

Ví dụ như hình trên

Bạn có một bảng dữ liệu gồm danh sách học viên trong cột B, tuy nhiên có một số ô bị bỏ trống. Yêu cầu là chỉ đánh số thứ tự cho các ô có nội dung, đồng thời khi lọc dữ liệu, số thứ tự phải tự động cập nhật từ 1.

Tại ô A2, bạn nhập công thức sau:

=IF(B2=””, “”, SUBTOTAL(3, $B$2:B2))

Phân tích công thức:

• IF(B2=””, “”, …): Nếu ô B2 trống thì không trả về gì cả (bỏ qua).
• Nếu ô B2 không trống, hàm SUBTOTAL(3, $B$2:B2) sẽ được thực thi để đếm số ô không trống trong phạm vi từ B2 đến ô hiện tại.
• $B$2:B2: Cố định ô bắt đầu (B2) để khi sao chép công thức xuống các dòng bên dưới, phạm vi sẽ tự động mở rộng: $B$2:B3, $B$2:B4, $B$2:B5…

Điểm đặc biệt của cách làm này là SUBTOTAL vẫn hoạt động linh hoạt khi bạn lọc dữ liệu. Các hàng không hiển thị sẽ bị loại khỏi phép đếm, giúp hệ thống số thứ tự tự động sắp xếp lại từ đầu cho dữ liệu đã lọc.

Khi nhập công thức xong, bạn nhấn Enter, sau đó sao chép công thức xuống các dòng còn lại trong cột A. Mỗi dòng có dữ liệu sẽ được đánh số thứ tự liên tục, còn các dòng trống sẽ để trống. Khi áp dụng bộ lọc, số thứ tự cũng tự động cập nhật lại theo đúng các dòng được hiển thị.

Sau khi nhập công thức, bạn nhấn Enter rồi tiến hành sao chép công thức từ ô A2 đến các ô còn lại

4. Các lỗi thường gặp và cách khắc phục hàm SUBTOTAL trong Excel

Trong quá trình làm việc với hàm SUBTOTAL, đôi khi bạn sẽ gặp lỗi #VALUE!. Đây là lỗi khá phổ biến, thường xuất hiện do một trong hai nguyên nhân chính dưới đây:

Nguyên nhân 1: Sai số chức năng function_num

Hàm SUBTOTAL yêu cầu đối số đầu tiên (function_num) phải nằm trong một dãy giá trị hợp lệ, cụ thể là:

• Từ 1 đến 11 nếu muốn tính toán bao gồm cả dữ liệu bị ẩn thủ công.
• Từ 101 đến 111 nếu muốn bỏ qua các hàng đã bị ẩn.

Nếu bạn nhập sai số ngoài khoảng này (ví dụ 12, 200, hay nhập nhầm thành “9,” có dấu phẩy thừa), Excel sẽ không hiểu yêu cầu và lập tức báo lỗi #VALUE!.

Đang dùng SUBTOTAL ở sheet STT2 nhưng tham chiếu đến một ô, một dãy ô nào đó ở sheet STT1 báo lỗi #VALUE

Cách khắc phục: Kiểm tra lại đối số function_num xem đã đúng chuẩn chưa. Đảm bảo không có ký tự dư thừa hoặc khoảng trắng không cần thiết. Nếu cần, hãy tra lại bảng mã function_num chuẩn để chọn đúng loại phép tính (SUM, AVERAGE, COUNT…).

Nguyên nhân 2: Tham chiếu đến ô thuộc trang tính khác

Một điểm cần lưu ý quan trọng là hàm SUBTOTAL không hỗ trợ tham chiếu đến ô hoặc vùng dữ liệu nằm trên sheet khác. Điều này có nghĩa là nếu bạn đang sử dụng SUBTOTAL ở sheet “Tổng hợp”, nhưng lại tham chiếu đến vùng dữ liệu ở sheet “Dữ liệu chi tiết”, Excel sẽ không thể xử lý được và trả về lỗi #VALUE!.

Cách xử lý: Thay vì tham chiếu trực tiếp sang sheet khác, bạn có thể di chuyển vùng dữ liệu cần tính về cùng một trang tính với công thức SUBTOTAL. Hoặc sử dụng các hàm khác như SUM, COUNTIF… nếu bắt buộc phải tham chiếu sang sheet khác.

5. Lưu ý quan trọng khi dùng hàm SUBTOTAL trong Excel

Để sử dụng hàm SUBTOTAL trong Excel một cách chính xác và hiệu quả, bạn cần nắm rõ những điểm sau:

• Hàm SUBTOTAL chỉ hoạt động theo chiều dọc, tức là áp dụng cho các cột dữ liệu. Nếu áp dụng cho hàng ngang (theo chiều ngang), kết quả có thể không chính xác.
• Khi vùng dữ liệu ref1, ref2,… đã chứa sẵn các công thức SUBTOTAL khác, Excel sẽ tự động bỏ qua các hàm con này, nhằm tránh hiện tượng tính trùng giá trị.
• SUBTOTAL sẽ tự động bỏ qua dữ liệu bị ẩn do Filter, tức là các dòng không hiển thị vì không đáp ứng điều kiện lọc. Đây là tính năng cực kỳ hữu ích khi bạn cần tính toán dữ liệu đã được lọc chọn.
• Giá trị bị ẩn (ẩn thủ công): Là những hàng bạn chủ động ẩn đi bằng thao tác chọn hàng → chuột phải → Hide.
• Giá trị bị loại bỏ do lọc (Filter): Là những dòng tạm thời bị ẩn do không thỏa mãn điều kiện lọc (Filter). Đây không phải thao tác ẩn thủ công mà là xử lý tự động từ Excel khi lọc dữ liệu.

Việc phân biệt 2 giá trị này này đặc biệt quan trọng bởi các mã từ 1 đến 11 của hàm SUBTOTAL vẫn tính cả giá trị bị ẩn thủ công. Trong khi đó, các mã từ 101 đến 111 sẽ bỏ qua cả giá trị bị ẩn thủ công và do Filter, mang lại kết quả linh hoạt hơn trong những tình huống báo cáo động.

Lưu ý khi dùng hàm SUBTOTAL trong Excel

Dù không quá phức tạp, nhưng hàm SUBTOTAL trong Excel lại mang đến khả năng xử lý dữ liệu linh hoạt và thông minh, đặc biệt hữu ích khi bạn làm việc với các bảng tính lớn cần lọc, tổng hợp hay đánh số thứ tự tự động.

Nắm vững cách sử dụng hàm này không chỉ giúp bạn tiết kiệm thời gian mà còn nâng cao độ chính xác và chuyên nghiệp trong công việc.

Nếu bạn muốn tìm hiểu sâu hơn về các công thức Excel ứng dụng thực tế từ cơ bản đến nâng cao, từ công thức đơn giản đến các tổ hợp hàm phức tạp thì sách Excel phân phối trực tiếp tại AntBook chắc chắn là một lựa chọn không thể bỏ qua. Với cách trình bày dễ hiểu, ví dụ sinh động và các tình huống thực tiễn sát sườn, cuốn sách sẽ là người bạn đồng hành đáng tin cậy cho hành trình chinh phục Excel của bạn.