Cách dùng chi tiết hàm SUBTOTAL trong và lỗi thường gặp

Hàm SUBTOTAL là một trong những hàm tính toán mạnh mẽ của Excel, hỗ trợ tổng hợp dữ liệu theo nhiều cách khác nhau. Không giống các hàm cộng thông thường, SUBTOTAL có khả năng linh hoạt hơn, xử lý tốt cả dữ liệu đã lọc lẫn dữ liệu ẩn. Vậy hàm SUBTOTAL là gì, đặc điểm ra sao và cách áp dụng vào từng trường hợp cụ thể? Hãy theo dõi bài viết dưới đây để hiểu toàn bộ ứng dụng của hàm này nhé!

Hàm SUBTOTAL trong Excel là gì?

Nhiều người dùng thắc mắc hàm SUBTOTAL trong Excel có tác dụng gì và khác gì so với SUM. Thực tế, Subtotal là hàm gì thì câu trả lời nằm ở chính cấu trúc đa năng của nó. SUBTOTAL giúp tạo ra giá trị tổng hợp từ một nhóm dữ liệu, không chỉ cộng mà còn có thể tính trung bình, tìm giá trị lớn nhất, giá trị nhỏ nhất, đếm ô chứa dữ liệu…

Những lợi ích quan trọng mà SUBTOTAL mang lại bao gồm:

• Tính toán dữ liệu nhanh chóng khi đang lọc bảng.
• Loại bỏ các số liệu ẩn (nếu dùng function_num từ 101–111).
• Không bị lặp kết quả khi vùng tham chiếu đã chứa công thức SUBTOTAL.

Đây cũng là lý do nhiều người tìm hiểu hàm SUBTOTAL là hàm gì để áp dụng vào các tác vụ thống kê chuyên sâu.

Công thức hàm SUBTOTAL

Dạng tổng quát: =SUBTOTAL(function_num, ref1, [ref2]…)

Giải thích từng thành phần:

• function_num: số xác định loại phép tính. Từ 1–11 là tính cả giá trị ẩn; từ 101–111 là loại bỏ giá trị ẩn.
• ref1: vùng dữ liệu đầu tiên.
• ref2: vùng dữ liệu bổ sung, tối đa 254 vùng.

Bảng ý nghĩa function_num:

function_num	Tên hàm	Ý nghĩa
101	AVERAGE	Lấy giá trị trung bình
102	COUNT	Đếm số ô chứa số
103	COUNTA	Đếm ô không rỗng
104	MAX	Tìm giá trị lớn nhất
105	MIN	Tìm giá trị nhỏ nhất
106	PRODUCT	Nhân các giá trị
107	STDEV	Độ lệch chuẩn mẫu
108	STDEVP	Độ lệch chuẩn tất cả giá trị
109	SUM	Cộng các số
110	VAR	Độ biến thiên mẫu
111	VARP	Độ biến thiên toàn bộ

Nhờ sự đa dạng này, người dùng có thể vận dụng SUBTOTAL trong rất nhiều trường hợp khác nhau.

Cách sử dụng hàm SUBTOTAL chi tiết với ví dụ

Nếu bạn vẫn phân vân cách dùng hàm SUBTOTAL thì hãy xem từng ví dụ cụ thể dưới đây. Mỗi trường hợp đều được trình bày rõ ràng, không gộp các bước để bạn có thể thao tác lại dễ dàng.

Tính tổng vùng đang được lọc

Giả sử bạn có bảng phí hoa hồng và muốn lấy tổng số tiền của “Nhóm 1”. Thao tác như sau:

Bước 1: Chọn toàn bộ bảng → chọn Home → nhấn Sort & Filter.

Bước 2: Chọn Filter để kích hoạt bộ lọc.

Bước 3: Tại cột Nhóm → chọn biểu tượng tam giác → chỉ tích 1 → nhấn OK.

Bước 4: Tại ô cần trả kết quả → nhập:

=SUBTOTAL(9, D3:D9)

• Số 9 tương ứng với SUM.
• D3:D9 là vùng cần tính.

Kết quả ngay lập tức sẽ chỉ lấy phần dữ liệu đã lọc.

Đếm số ô không trống thuộc nhóm đã lọc

Bảng sản phẩm có cột “Danh mục” và “Link sản phẩm”. Bạn muốn đếm số mặt hàng thuộc “Đồ gia dụng” đã có link.

Bước 1: Bôi đen toàn bộ vùng → chọn Sort & Filter → nhấn Filter.

Bước 2: Tại cột Danh mục → lọc Đồ gia dụng → nhấn OK.

Bước 3: Tại ô cần hiển thị → nhập:

=SUBTOTAL(3, D3:D10)

• Số 3 tương ứng với COUNTA.
• D3:D10 là vùng đếm.

Bước 4: Nhấn Enter để xem kết quả.

Đánh số thứ tự tự động

Ví dụ bạn có danh sách nhân viên và muốn đánh số thứ tự không bị đứt quãng sau khi lọc.

Bước 1: Tại ô A3, nhập công thức:

=SUBTOTAL(3, $B$3:B3)

Trong đó:

• Số 3 vẫn là COUNTA.
• $B$3 cố định bằng phím F4.

Bước 2: Khi A3 ra kết quả “1”, dùng dấu + nhỏ ở góc để kéo xuống toàn bộ danh sách.

Các số thứ tự sẽ tự nhảy theo dữ liệu hiển thị trên bảng lọc.

Tìm giá trị cao nhất

Bạn muốn lấy mức giá cao nhất trong danh sách đồ dùng.

Bước 1: Tại ô cần hiển thị → nhập:

=SUBTOTAL(4, C3:C7)

• Function_num = 4 → MAX.
• C3:C7 là cột chứa giá.

Bước 2: Nhấn Enter để xem kết quả sản phẩm có giá trị lớn nhất.

Kết hợp hàm SUBTOTAL với các hàm khác

Để mở rộng khả năng tính toán, người dùng có thể phối hợp SUBTOTAL với các hàm khác trong Excel. Những ví dụ sau sẽ giúp bạn hiểu rõ cách sử dụng hàm SUBTOTAL trong tình huống nâng cao.

Hàm SUBTOTAL + VLOOKUP

Dạng kết hợp:

=SUBTOTAL(function_num, VLOOKUP(…), VLOOKUP(…))

Ứng dụng thường gặp khi cần tìm dữ liệu lớn nhất/nhỏ nhất từ hai bảng khác nhau.

Ví dụ: Tìm điểm KPI cao nhất của nhóm 2.

Bước 1: Nhập công thức:

=SUBTOTAL(4, VLOOKUP(F5, A3:D7, 3, FALSE), VLOOKUP(F7, A3:D7, 3, FALSE))

• Số 4 là MAX.
• Hai hàm VLOOKUP lấy dữ liệu tương ứng từ bảng gốc.

Bước 2: Nhấn Enter để hiển thị kết quả KPI cao nhất.

Hàm SUBTOTAL + IF

Dạng công thức:

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

Cách kết hợp này rất hiệu quả khi đánh số thứ tự trong bảng có hàng trống.

Ví dụ:

Bước 1: Tại ô A3 nhập:

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

Ý nghĩa:

• IF kiểm tra B3 có trống không.
• Nếu trống → giữ trống.
• Nếu có giá trị → sử dụng COUNTA để đánh số.

Bước 2: Kéo công thức xuống để hoàn chỉnh cột.

Một số lỗi thường gặp khi dùng hàm SUBTOTAL

Trong quá trình tính toán, SUBTOTAL đứng trước một số lỗi phổ biến:

– Lỗi #VALUE!

Nguyên nhân:

• function_num không thuộc phạm vi quy định.
• ref nằm ở bảng tính khác.

Khắc phục: kiểm tra lại function_num và phạm vi tham chiếu.

Nguyên nhân và cách khắc phục lỗi #VALUE!

– Lỗi #DIV/0!

Thường xuất hiện khi:

• Tính trung bình nhưng tất cả ô đều rỗng.
• Chia giá trị cho 0.

Cách xử lý: đảm bảo dữ liệu trong các ô đều là giá trị hợp lệ.

– Lỗi #NAME?

Lỗi này xảy ra khi nhập sai tên hàm, điển hình là viết sai chính tả.

Bạn chỉ cần gõ “=S” và chọn SUBTOTAL từ gợi ý là được.

Lưu ý quan trọng khi dùng hàm SUBTOTAL tính tổng có điều kiện

Nhiều người muốn dùng hàm SUBTOTAL tính tổng có điều kiện, nhưng cần hiểu rằng SUBTOTAL bản chất không hỗ trợ điều kiện trực tiếp mà hoạt động dựa trên dữ liệu đã lọc. Vì vậy, SUBTOTAL chỉ cho ra kết quả đúng khi bạn lọc bảng trước. Nhờ vậy, Excel có thể thực hiện hàm SUBTOTAL có điều kiện bằng cách kết hợp bộ lọc.

Một số lưu ý khi dùng hàm SUBTOTAL để tính tổng có điều kiện

Kết luận

SUBTOTAL là công cụ cực kỳ hữu ích khi bạn cần tổng hợp dữ liệu trong bảng lớn, đặc biệt là khi làm việc với dữ liệu đã lọc. Khi hiểu rõ nguyên tắc hoạt động cũng như các lỗi thường gặp, bạn sẽ vận dụng hàm này chính xác trong mọi tình huống. Nếu bạn đang làm việc thường xuyên với Excel, hãy thử áp dụng ngay để tối ưu quy trình tính toán.

Để sử dụng Excel mượt mà, xử lý dữ liệu nhanh và ổn định hơn, bạn nên trang bị một chiếc laptop hiệu suất cao. Hãy truy cập Viettel Store để chọn mua laptop chính hãng, giá tốt, đầy đủ bảo hành và nhiều ưu đãi hấp dẫn dành cho khách hàng. Chọn thiết bị chất lượng – làm việc hiệu quả hơn mỗi ngày!

Xem thêm:

• Hàm SUMIFS trong Excel: Cách sử dụng và ví dụ cụ thể dễ hiểu
• Hàm Left trong Excel là gì? Hướng dẫn chi tiết cách dùng từng bước