Hàm SUBTOTAL trong Excel có thể được sử dụng để tính tổng, đếm các ô không trống trong một dải dữ liệu đã lọc, tự động đếm, v.v. Mời các bạn tham gia bài viết sau để tìm hiểu về công thức hàm SUBTOTAL và cách sử dụng hàm này nhé. được cụ thể!

• Các hướng dẫn sau đây được thực hiện trên máy tính xách tay Windows, phiên bản Excel 2016. Bạn có thể thực hiện tương tự trong các phiên bản Excel 2007, 2010, 2013 và 2019.
• Bạn có thể tải về một số ví dụ về hàm SUBTOTAL trong Excel để làm quen nhé!

Hàm SUBTOTAL trong Excel được ứng dụng trong rất nhiều trường hợp như tính tổng, tính trung bình cộng, đếm ô, tìm giá trị dữ liệu lớn nhất/nhỏ nhất, đếm tự động, v.v.

Công thức của hàm SUBTOTAL như sau:=tổng phụ(function_num, ref1, ref2,…)

Ở đó:

• hàm_num: liệu hàm_num QUA 1 – 11 thì hàm SUBTOTAL thực hiện phép tính bao gồm giá trị ẩn trong vùng dữ liệu chứa giá trị ẩn đó. liệu hàm_num được chọn từ 101-111 thì hàm SUBTOTAL sẽ bỏ qua, không đếm giá trị trong hàng ẩn.
• Tham chiếu1, Tham chiếu2,…: 1 hoặc nhiều ô hoặc phạm vi ô trong tổng phụ, tối đa 254.

Mã của Hàm_num như sau:

Function_num (bao gồm cả giá trị ẩn)	function_num (bỏ qua giá trị ẩn)	chức năng tương đương
Đầu tiên	101	TRUNG BÌNH
2	102	ĐẾM
3	103	đếm
4	104	tối đa
5	105	TỐI THIỂU
6	106	SẢN PHẨM
7	107	STDEV
số 8	108	STDEVP
9	109	Cuộc họp
mười	110	treo
11	111	VARP

Ghi chú:

– Hàm SUBTOTAL dùng cho cột dữ liệu theo chiều dọc.

– Cơ hội giới thiệu1, giới thiệu2,… chứa hàm SUBTOTAL sẽ bị loại bỏ để tránh tính trùng.

– SUBTOTAL sẽ bỏ qua dữ liệu bị ẩn bởi bộ lọc (dữ liệu không thỏa mãn bộ lọc).

– Cần phân biệt rõ ràng giá trị ẩn VÀ giá trị bị từ chối vì bộ lọc không thỏa mãn.

• Giá trị ẩn là giá trị bạn đã chủ động ẩn khỏi hàng đã chọn > Nhấp chuột phải để chọn thao tác đã xóa.
• Giá trị bị loại bỏ vì bộ lọc không thỏa mãn là những giá trị không phù hợp với truy vấn tìm kiếm của bạn.

Sau đây là một số cách áp dụng SUBTOTAL .function phổ biến trong thực tế, hãy tham khảo nó!

Giả sử bạn có bảng sau và cần lọc tổng KPI của team A. Bạn làm như sau:

Bước 1: Bôi đen bảng chứa dữ liệu cần tính > Enter Trang chủ > Sắp xếp & Lọc > Lọc.

Bước 2: Nhấp vào mũi tên hình tam giác ngược trong tiêu đề cột “Đội”, đánh dấu “A” và nhấn ĐƯỢC RỒI.

Bước 3: Bảng dữ liệu của đội A được lọc bằng Excel. Bây giờ, tại ô cần lấy kết quả bạn nhập:

=SUBTOTAL (9,D2:D11)

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

• hàm_num = 9 là giá trị đối số tương ứng với chức năng sẽ được sử dụng. Bạn sẽ thấy giá trị đối số này được hiển thị khi nhập công thức hàm SUBTOTAL. Ở đây vì phải tính tổng nên ta sẽ chọn hàm tổng – tương ứng với số 9.
• giới thiệu1 = D2:D11 là phạm vi để thu thập.

Bạn sẽ nhận được kết quả như sau:

Ghi chú: Bạn có thể sử dụng giá trị đối số 9 (tổng các giá trị ẩn) hoặc 109 (bỏ qua các giá trị ẩn) để tóm tắt các hàng đã lọc.

Giả sử bạn có một bảng dữ liệu như hình bên dưới. Nhiệm vụ của bạn là đếm số học sinh thuộc nhóm C có liên quan đến bài tập để tiện thống kê.

Đầu tiên, bạn tiếp tục lọc ra học sinh nhóm C với chức năng lọc tương tự như đã hướng dẫn ở phần trước.

Sau khi lọc dữ liệu, tại ô cần lấy kết quả, nhập:

=SUBTOTAL (3,D4:D14)

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

• hàm_num = 3 là giá trị đối số tương ứng với chức năng sẽ được sử dụng. Bạn sẽ thấy giá trị đối số này được hiển thị khi nhập công thức hàm SUBTOTAL. Ở đây vì cần đếm các ô không trống nên chúng ta sẽ chọn hàm COUNTA – tương ứng với số 3.
• giới thiệu1 = D4:D14 là phạm vi để thu thập.

Kết quả thu được sẽ như sau:

Ghi chú: Bạn có thể sử dụng giá trị đối số 3 (đếm các giá trị đã bị ẩn thủ công do thuộc tính Hide) hoặc 103 (bỏ qua, không đếm các giá trị ẩn thủ công) để đếm các ô không trống trong các hàng đã lọc.

Nếu bạn chỉ cần đếm một danh sách dữ liệu liên tiếp (không có hàng trống) như hình bên dưới, bạn có thể nhập một công thức đơn giản vào ô A2 như sau:

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

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

• hàm_num là 3: Với cách tính là COUNTA – đếm các ô không trống.
• giới thiệu1 là $B$2:B2: Đếm các ký tự được tạo trong phạm vi $B$2:B2, trong đó ô B2 có tiền tố là “$” trước địa chỉ hàng và ô. Nếu bạn sao chép công thức bên dưới thì giá trị sẽ thay đổi thành: $B$2:B3, $B$2:B4, $B$2:B5,… và hàm sẽ đếm đúng, cho kết quả như mong muốn.

Tiếp theo, bạn kéo và sao chép công thức (bằng cách chọn ô chứa công thức cần sao chép > di chuyển chuột đến góc dưới bên phải của ô và kéo thả dấu cộng vừa xuất hiện ở cuối ô cuối cùng cần điền trong) . Kết quả thu được như sau:

Tuy nhiên, nếu bạn gặp dữ liệu “thô” với các hàng trống và không trống lộn xộn, bạn sẽ cần kết hợp SUBTOTAL với hàm IF. Số thứ tự của giá trị được chọn sẽ được in lại từ đầu khi lọc dữ liệu và cách này chỉ áp dụng khi sắp xếp theo cột.

Cú pháp chung:

=liệu(logic_test, value_if_true, tổng phụ(function_num, ref1,…))

Xem chi tiết: Cách dùng hàm SUBTOTAL và hàm IF đếm tự động trong Excel

Ví dụ bạn có một bảng dữ liệu như hình bên dưới.

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

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

suy diễn: Chức năng chính để sử dụng sẽ là IF. Nếu có một ô trống, hãy bỏ qua việc đếm. Nếu ô không trống, hãy sử dụng SUBTOTAL để đếm.

Công thức của hàm IF là: =IF (logic_test, value_if_true, value_if_false), với:

• kiểm tra logic: Trạng thái của hàm IF.
• value_if_true: Trả về giá trị nếu điều kiện đúng.
• value_if_false: Trả về giá trị nếu điều kiện sai. Ở đây chúng ta sử dụng hàm SUBTOTAL.

Các đối số để hoàn thành hàm SUBTOTAL là:

• hàm_num = 3: Với cách tính là COUNTA – đếm các ô không trống.
• giới thiệu1 = $B$2:B2: Đếm các ký tự kết quả trong phạm vi $B$2:B2, trong đó ô B2 có tiền tố là “$” trước địa chỉ hàng và ô. Nếu bạn sao chép công thức bên dưới thì giá trị sẽ thay đổi thành: $B$2:B3, $B$2:B4, $B$2:B5,… và hàm sẽ đếm đúng, cho kết quả như mong muốn.

Sau khi nhập công thức, nhấn Đăng nhập sau đó tiến hành sao chép công thức từ ô A2 sang các ô còn lại. Kết quả là bảng sau:

Nguyên nhân lỗi:

– Số nhận dạng chức năng hàm_num không nằm trong phạm vi 1 – 11 HOẶC 101 – 111.

– Thẩm quyền giải quyết giới thiệu tham chiếu đến các ô trong trang tính khác (trang tính khác), ví dụ: bạn đang sử dụng SUBTOTAL trong trang tính STT2, nhưng tham chiếu đến một ô hoặc phạm vi ô cụ thể trong trang tính STT1.

Làm thế nào để khắc phục:

Đối với nguyên nhân đầu tiên do lỗi định nghĩa hàm, bạn nên kiểm tra lại xem mình đã sử dụng đúng số lượng hàm chưa hay trong quá trình nhập liệu có thừa ký tự nào không để sửa lại.

Đối với nguyên nhân thứ hai gây ra lỗi tham chiếu, hãy loại bỏ tham chiếu sai và thay thế bằng phạm vi ô chính xác nằm trên cùng một trang tính mà bạn muốn thực hiện với hàm SUBTOTAL.

Trên đây là hướng dẫn cách sử dụng hàm SUBTOTAL trong Excel đơn giản và dễ dàng. Rất mong bài viết sẽ giúp ích cho các bạn khi làm việc với bảng! Cảm ơn đã xem bài viết.