Nội dung bài viết
Bạn đã bao giờ bất lực khi dùng hàm SUM tính tổng, nhưng hễ bật bộ lọc Filter để lọc dữ liệu thì kết quả vẫn giữ nguyên cả những dòng bị ẩn? Việc làm chủ hàm SUBTOTAL trong Excel chính là giải pháp tính toán thông minh giúp bạn tự động loại bỏ các hàng bị ẩn do lọc dữ liệu hoặc ẩn thủ công bằng tay. Bài viết dưới đây MSO sẽ hướng dẫn bạn chi tiết từ cú pháp tổng quát, bảng tra cứu mã lệnh (dải 1–11 và 101–111), các ví dụ thực tế (tính tổng danh sách lọc, đánh số thứ tự tự động) cho đến mẹo sửa lỗi #VALUE! và bẫy ẩn theo cột cực kỳ hiệu quả!
Cú pháp và bảng mã số chức năng của hàm SUBTOTAL trong Excel
Cú pháp của hàm SUBTOTAL trong Excel bao gồm hai thành phần chính là mã số chức năng tính toán (function_num) và vùng dữ liệu tham chiếu (ref1, ref2,…).
Cú pháp chuẩn của hàm SUBTOTAL trong Excel
Công thức tổng quát của hàm được khai báo như sau:
=SUBTOTAL(function_num, ref1, [ref2], …)
Trong đó:
- function_num (Bắt buộc): Số nguyên quy định hàm tính toán nào sẽ được sử dụng (ví dụ: 1 là tính trung bình, 9 là tính tổng). Mã số này nằm trong dải từ 1 đến 11 hoặc 101 đến 111.
- ref1 (Bắt buộc): Vùng dữ liệu hoặc địa chỉ ô đầu tiên mà bạn muốn tính tổng phụ.
- ref2, … (Tùy chọn): Các vùng dữ liệu hoặc địa chỉ ô tiếp theo từ 2 đến 254 vùng cần tính toán.
Phân biệt nhóm mã chức năng 1 – 11 và 101 – 111 trong hàm SUBTOTAL
Excel chia mã function_num thành 2 nhóm rõ rệt để phục vụ các mục đích ẩn dữ liệu khác nhau:
- Nhóm mã từ 1 đến 11 (Bỏ qua hàng bị lọc Filter):
- Nhóm mã này sẽ tự động bỏ qua các hàng bị ẩn bởi tính năng AutoFilter.
- Tuy nhiên, nếu bạn ẩn dòng bằng cách nhấp chuột phải chọn Hide (ẩn thủ công), nhóm mã này vẫn tính cả các dòng bị ẩn đó vào kết quả.
- Nhóm mã từ 101 đến 111 (Bỏ qua tất cả hàng ẩn):
- Nhóm mã này tự động bỏ qua tất cả các hàng không hiển thị, bao gồm cả hàng bị ẩn do bộ lọc Filter lẫn hàng bị ẩn thủ công bằng lệnh Hide.
Các ví dụ thực tế ứng dụng hàm SUBTOTAL trong Excel xử lý công việc
Các ví dụ thực tế dưới đây minh họa cách ứng dụng hàm SUBTOTAL trong Excel để tính tổng danh sách lọc, tính giá trị trung bình và đánh số thứ tự tự động khi ẩn hàng.
Tính tổng doanh thu danh sách dữ liệu sau khi lọc (Filter)
Ví dụ: Bạn có bảng theo dõi doanh số bán hàng từ ô C2 đến C7. Bạn cần tính tổng doanh thu nhưng thường xuyên phải lọc theo từng Tỉnh/Thành phố.
Công thức thực hiện:
=SUBTOTAL(9, C2:C7)
Cách vận hành: Khi bạn không lọc, kết quả trả về là tổng toàn bộ cột C. Khi bạn bật Filter lọc tỉnh “Hà Nội”, ô tính sẽ ngay lập tức cập nhật tổng doanh thu của riêng các dòng thuộc “Hà Nội” đang hiển thị.
Tính giá trị trung bình bỏ qua các hàng ẩn thủ công
Ví dụ: Bảng điểm học sinh từ D2 đến D30 có một số học sinh nghỉ thi bị ẩn thủ công (nhấp chuột phải → Hide). Bạn muốn tính điểm trung bình chỉ cho những học sinh đang hiển thị.
Công thức thực hiện:
=SUBTOTAL(101, C2:C30)
Giải thích: Mã 101 đại diện cho hàm AVERAGE thuộc nhóm loại trừ toàn bộ hàng bị ẩn thủ công. Các dòng ẩn bằng lệnh Hide sẽ không bị cộng vào tổng điểm và không bị đếm vào mẫu số khi chia trung bình.
Đánh số thứ tự tự động bỏ qua các dòng bị lọc
Ví dụ: Khi bạn đánh số thứ tự cột A dạng 1,2,3,4,5.. theo cách thông thường, nếu bật Filter lọc dữ liệu, các con số thứ tự sẽ bị đứt đoạn (ví dụ hiển thị dòng 1,4,7,12…). Bạn muốn số thứ tự luôn hiển thị liên tục 1,2,3,4… dù lọc bất kỳ điều kiện nào.
Công thức thực hiện tại ô A2:
=SUBTOTAL(3, $B$2:B2)
Cách làm:
- Mã 3 đại diện cho hàm COUNTA (đếm số ô không trống).
- Sử dụng địa chỉ tuyệt đối B2 kết hợp tương đối B2 để khi kéo công thức xuống dưới, phạm vi đếm sẽ mở rộng dần.
- Khi bạn lọc dữ liệu, hàm SUBTOTAL chỉ đếm các dòng đang hiển thị, giúp số thứ tự ở cột A luôn đánh chuỗi 1,2,3… liên tục và đẹp mắt.
Hàm SUBTOTAL trong Excel là gì và tại sao nên sử dụng?
Hàm SUBTOTAL trong Excel là hàm tính toán đa năng cho phép thực hiện các phép tính như tính tổng, trung bình, đếm dữ liệu trên một vùng chỉ định và tự động bỏ qua các hàng bị ẩn do lọc dữ liệu (Filter) hoặc ẩn thủ công (Hide).
Sự khác biệt giữa hàm SUBTOTAL và các hàm tính toán thông thường (SUM, AVERAGE, COUNT)
- Khả năng tự động loại trừ hàng bị ẩn: Các hàm thông thường như SUM, AVERAGE, COUNT sẽ tính toán trên tất cả các ô trong phạm vi được chỉ định, bất kể hàng đó có bị lọc đi hay bị ẩn thủ công hay không. Trái lại, hàm SUBTOTAL tự động phát hiện trạng thái ẩn/hiện của dòng để loại bỏ các ô không hiển thị ra khỏi kết quả cuối cùng.
- Tính năng chống tính trùng lặp (Double-counting): Khi bạn thực hiện tính tổng cho một bảng dữ liệu lớn có chứa các dòng tổng phụ (Subtotal) ở từng phân mục nhỏ, nếu dùng hàm SUM ở dòng tổng cộng cuối cùng, số liệu sẽ bị gấp đôi. Hàm SUBTOTAL có khả năng thông minh: Tự động bỏ qua kết quả của tất cả các ô chứa công thức SUBTOTAL khác nằm trong vùng tham chiếu.
Lợi ích vượt trội khi sử dụng hàm SUBTOTAL trong xử lý báo cáo
- Tối ưu báo cáo động: Số liệu tổng hợp tự động nhảy theo thời gian thực ngay khi bạn thay đổi điều kiện lọc trên bộ lọc Filter.
- Tăng tốc độ làm việc: Bạn không cần phải viết lại công thức hay tạo các vùng dữ liệu phụ mỗi khi muốn xem số liệu của từng chi nhánh, từng phòng ban hay từng khoảng thời gian.
- Đảm bảo độ chính xác của số liệu: Loại bỏ hoàn toàn nguy cơ cộng nhầm các dữ liệu rác, dữ liệu ẩn hoặc cộng lặp các chỉ số tổng phụ trong báo cáo tài chính, kế toán.
Bảng tổng hợp chi tiết các mã lệnh trong hàm SUBTOTAL trong Excel
Bảng dưới đây hệ thống toàn bộ các mã lệnh function_num trong hàm SUBTOTAL trong Excel giúp bạn tra cứu và áp dụng chuẩn xác cho từng bài toán.
|
Tên hàm tương đương |
Mã bao gồm hàng ẩn thủ công (1−11) |
Mã bỏ qua hàng ẩn thủ công (101−111) |
Mục đích sử dụng thực tế |
Hiệu quả đạt được |
|
AVERAGE |
1 |
101 |
Tính giá trị trung bình |
Tự động tính trung bình các dòng hiển thị |
|
COUNT |
2 |
102 |
Đếm các ô chứa dữ liệu số |
Chỉ đếm số lượng ô chứa con số đang hiện |
|
COUNTA |
3 |
103 |
Đếm các ô chứa văn bản/dữ liệu |
Đánh số thứ tự liên tục cực nhanh khi lọc |
|
MAX |
4 |
104 |
Tìm giá trị cực đại |
Xác định chỉ số cao trong vùng dữ liệu lọc |
|
MIN |
5 |
105 |
Tìm giá trị cực tiểu |
Xác định chỉ số thấp trong vùng dữ liệu lọc |
|
PRODUCT |
6 |
106 |
Tính tích các giá trị |
Nhân các hệ số của các dòng đang hiển thị |
|
STDEV |
7 |
107 |
Tính độ lệch chuẩn mẫu |
Phân tích biến động độ lệch chuẩn nhóm lọc |
|
STDEVP |
8 |
108 |
Tính độ lệch chuẩn tổng thể |
Phân tích mức độ phân tán tập dữ liệu hiện |
|
SUM |
9 |
109 |
Tính tổng cộng các ô |
Tính tổng doanh số, chi phí linh hoạt |
|
VAR |
10 |
110 |
Tính phương sai mẫu |
Phân tích phương sai nhóm dữ liệu hiển thị |
|
VARP |
11 |
111 |
Tính phương sai tổng thể |
Phân tích phương sai toàn bộ vùng lọc hiện |
Các lỗi thường gặp & cách khắc phục khi sử dụng hàm SUBTOTAL trong Excel
Nắm vững nguyên nhân gây lỗi khi dùng hàm SUBTOTAL trong Excel giúp bạn nhanh chóng khắc phục các sự cố tính toán và đảm bảo tính chính xác của dữ liệu.
- Lỗi #VALUE! do nhập sai mã Function_num
- Nguyên nhân: Lỗi này xảy ra khi bạn gõ một con số nằm ngoài dải mã quy định (ví dụ nhập mã 12 hoặc 100), hoặc do nhập nhầm ký tự chữ cái vào vị trí tham số function_num.
- Cách khắc phục: Dùng bảng tra cứu ở phần 5 để kiểm tra và nhập lại chính xác các mã số từ 1 → 10 hoặc từ 101 → 111.
- Hiểu sai cơ chế làm việc trên cột thay vì trên hàng
Hàm SUBTOTAL được thiết kế để phát hiện và bỏ qua dữ liệu bị ẩn theo HÀNG (Rows).
Nếu bạn ẩn dữ liệu theo CỘT (Columns) bằng cách nhấp chuột phải vào tên cột và chọn Hide, hàm SUBTOTAL vẫn tính toán toàn bộ dữ liệu nằm trong các cột bị ẩn đó.
- Không tự động cập nhật khi ẩn hàng thủ công
- Nguyên nhân: Bạn đang sử dụng nhóm mã 1 → 11 (ví dụ mã 9) nhưng lại thực hiện ẩn dòng bằng thao tác nhấp chuột phải chọn Hide Rows. Nhóm mã 1 → 11 chỉ hoạt động loại trừ đối với bộ lọc Filter.
- Cách khắc phục: Đổi mã function_num sang nhóm từ 101 → 111 (ví dụ chuyển mã 9 thành 109) để hàm bỏ qua cả các dòng bị ẩn bằng tay.
Nếu bạn hoặc doanh nghiệp đang tìm kiếm giải pháp Microsoft 365 bản quyền chính hãng để làm việc mượt mà và nhận sự hỗ trợ kỹ thuật tận tình, hãy liên hệ ngay với MSO để nhận tư vấn ưu đãi phù hợp nhất.
Các câu hỏi thường gặp (FAQ)
Giải đáp ngắn gọn các câu hỏi phổ biến dưới đây sẽ giúp bạn áp dụng hàm SUBTOTAL trong Excel một cách tự tin và chính xác hơn.
Hàm SUBTOTAL có tính toán các ô chứa hàm SUBTOTAL khác trong vùng chọn không?
Không. Đây là tính năng thông minh của hàm SUBTOTAL nhằm tránh tính trùng lặp (Double-counting). Nếu trong vùng dữ liệu ref1 bạn quét có chứa các ô đã được cài đặt công thức SUBTOTAL từ trước, hàm sẽ tự động bỏ qua kết quả của các ô đó và chỉ tính các ô dữ liệu gốc.
Nên dùng mã 9 hay mã 109 khi tính tổng bằng hàm SUBTOTAL?
Nếu bảng dữ liệu của bạn chỉ sử dụng bộ lọc AutoFilter để xem thông tin, bạn dùng mã 9 hay 109 đều cho ra kết quả giống nhau. Tuy nhiên, để đảm bảo tính an toàn và loại trừ luôn cả những dòng ẩn thủ công ngoài ý muốn, bạn nên ưu tiên sử dụng mã 109.
Hàm SUBTOTAL có hoạt động trên phiên bản Excel Online không?
Có. Hàm SUBTOTAL là một trong những hàm tính toán chuẩn và cơ bản nhất của Microsoft Excel. Hàm hoạt động mượt mà và chính xác trên mọi phiên bản từ Excel 2010, 2013, 2016, 2019, 2021 cho đến Microsoft 365 và Excel Online.
Có thể truyền nhiều vùng dữ liệu rời rạc vào hàm SUBTOTAL không?
Có. Bạn có thể chọn các vùng dữ liệu không nằm liền kề nhau bằng cách phân tách chúng bởi dấu phẩy trong công thức, ví dụ: =SUBTOTAL(109, C2:C10, E2:E10, G2:G10).
Lời kết
Việc sử dụng thành thạo hàm SUBTOTAL trong Excel giúp bạn tối ưu hóa việc xử lý số liệu báo cáo, đảm bảo kết quả luôn cập nhật linh hoạt theo từng thao tác lọc dữ liệu mà không tốn công sức tính toán lại. Hãy áp dụng ngay các mã lệnh SUBTOTAL vào công việc lập báo cáo hàng ngày để nâng cao năng suất làm việc chỉ mất vài phút.
———————————————————
Fanpage: MSO.vn – Microsoft 365 Việt Nam
Hotline: 024.9999.7777
