Hàm SUBTOTAL và ví dụ về cách dùng đơn giản, dễ hiểu

Hàm SUBTOTAL trong Excel là gì? Hàm SUBTOTAL là hàm tính tổng của một phạm vi dữ liệu trong bảng tính. Vậy công thức của hàm SUBTOTAL và cách dùng hàm cụ thể như nào. Cùng tìm hiểu qua bài viết dưới đây để hiểu rõ hơn về hàm này nhé!

Hàm SUBTOTAL trong Excel là gì? 

Hàm SUBTOTAL trong Excel là một hàm tính tập hợp con của một danh sách hoặc cơ sở dữ liệu trong bảng tính Excel. Hàm này được ứng dụng trong nhiều trường hợp như: tính tổng, tính trung bình, đánh số thứ tự, tìm giá trị lớn nhất hoặc nhỏ nhất trong bảng dữ liệu,...            

Lợi ích khi sử dụng hàm SUBTOTAL trong bảng tính Excel là:

• Tính tổng giá trị trong các hàng được chọn một cách linh hoạt.
• Dễ dàng tính toán cả các giá trị ẩn trong bảng dữ liệu.
• Trường hợp giá trị ref1, ref2,... có chứa hàm SUBTOTAL thì sẽ tự động bỏ qua để không bị tính trùng 2 lần.

Xem thêm: Nếu bạn cần thay pin iPhone chính hãng giá rẻ tại TP.HCM hoặc Hà Nội, hãy đến ngay Điện Thoại Vui. Xem ưu đãi và bảng giá mới nhất tháng 11/2024 ngay tại đây!

[dtv_product_related category='thay-pin/thay-pin-dien-thoai-iphone'] 

Công thức hàm SUBTOTAL

Cú pháp hàm SUBTOTAL như sau: =SUBTOTAL (function_num, ref1, [ref2],…)

Trong đó: 

• Function_num: Là một số từ 1-11 hoặc 101-111 để xác định hàm sử dụng trong tính toán tổng phụ. Số từ 1-11 được tính toán cho cả các giá trị ẩn trong vùng dữ liệu. Nếu chọn từ 101-111 sẽ tính toán cho các giá trị không ẩn trong vùng dữ liệu.
• Ref1: Là tham chiếu hoặc phạm vi vùng được đặt tên đầu tiên để tính tổng phụ.
• Ref2,…: Là phạm vi hoặc chuỗi được đặt tên từ 2 đến 254 cần tính tổng phụ.

Dưới đây là bảng Function_num gồm các mã số dùng để tính trong hàm SUBTOTAL:

Function_num (bao gồm các giá trị ẩn)	Function_num (bỏ qua các giá trị ẩn)	Hàm	Mô tả hàm
1	101	AVERAGE	Tính trung bình
2	102	COUNT	Đếm số ô chứa giá trị số
3	103	COUNTA	Đếm số ô không trống
4	104	MAX	Tìm giá trị lớn nhất
5	105	MIN	Tìm giá trị nhỏ nhất
6	106	PRODUCT	Nhân các ô
7	107	STDEV	Tính độ lệch chuẩn mẫu
8	108	STDEVP	Tính độ lệch chuẩn trên toàn bộ số
9	109	SUM	Cộng các số
10	110	VAR	Ước tính độ dao động trên mẫu
11	111	VARP	Ước tính độ dao động trên toàn bộ số

Một số lưu ý cần nhớ khi dùng hàm SUBTOTAL trong Excel:

• Hàm SUBTOTAL được dùng để tính cho các cột dữ liệu hay phạm vi dọc. Hàm này không được dùng cho các hàng dữ liệu hoặc phạm vi ngang.
• Hàm SUBTOTAL chỉ tính được giá trị của hàng ẩn khi dùng lệnh Hide, sẽ bỏ qua giá trị các hàng bị ẩn bởi lệnh Auto Filter.
• Nếu có hàm SUBTOTAL lồng ghép trong ref1, ref2,… thì các hàm lồng này sẽ bị bỏ qua để tránh trường hợp tính toán 2 lần.
• Bất kỳ dữ liệu nào dưới dạng 3-D, hàm SUBTOTAL sẽ trả về lỗi #VALUE!.

Cách sử dụng hàm SUBTOTAL

Để hiểu rõ hơn về cách dùng hàm SUBTOTAL trong Excel, bạn có thể tham khảo một số ví dụ sau đây.

Ví dụ 1: Tính tổng vùng được lọc

Trong bảng tính DANH SÁCH HOA HỒNG THÁNG, có 2 cột Nhóm và Phí hoa hồng. Bạn đang cần lọc ra tổng phí hoa hồng của đội 1. Có thể thực hiện như sau:

Bước 1: Bôi đen toàn bộ bảng dữ liệu, vào mục Home và chọn Sort and Filter.

Bước 2: Ở mục Sort and Filter, tiếp tục chọn Filter.

Bước 3: Tại cột dữ liệu Nhóm, chọn dấu mũi tên hình tam giác ngược. Sau đó, bấm chọn dấu tick vào 1 và nhấn OK.

Bước 4: Các giá trị của Nhóm 1 trong bảng dữ liệu đã được lọc ra. Khi đó, tại ô hiển thị kết quả bạn nhập công thức: =SUBTOTAL(9,D3:D9)

Trong đó: 

• function_num: Lấy giá trị là 9 để tính tổng phí hoa hồng. Giá trị này tương ứng với hàm SUM (cộng các số).
• ref1: Phạm vi cần tính tổng lấy từ D3 đến D9.

Sau khi nhập công thức, bạn bấm Enter sẽ ra kết quả như hình:

Ở đối số function_num, bạn cũng có thể lấy giá trị 109 để bỏ qua các giá trị đã bị ẩn để tính tổng các hàng đã lọc.

Ví dụ 2: Đếm số ô được lọc không trống

Giả sử bạn có bảng DANH SÁCH SẢN PHẨM CẦN MUA, bao gồm danh mục đồ gia dụng và mỹ phẩm. Bạn đang cần đếm số lượng sản phẩm của nhóm đồ gia dụng đã có link mua hàng để tiện thống kê. 

Bước 1: Đầu tiên, bạn cần tiến hành lọc ra các sản phẩm thuộc nhóm đồ gia dụng bằng lệnh Auto Filter. Các thao tác lọc tương tự như ví dụ ở trên, vào mục Sort and Filter và chọn Filer.

Bước 2: Tại cột Danh mục, chọn dấu mũi tên hình tam giác ngược để tick chọn mục Đồ gia dụng và nhấn OK.

Bước 3: Sau khi lọc xong dữ liệu, bạn nhập cú pháp hàm SUBTOTAL ở ô kết quả: =SUBTOTAL(3,D3:D10)

Xem thêm: Hướng dẫn hàm tách chữ trong Excel cực nhanh.

Với công thức trên: 

• function_num: là giá trị tương ứng với hàm cần dùng. Do bạn đang cần đếm các ô không trống nên giá trị này sẽ bằng 3, tương ứng với hàm COUNTA.
• ref1: phạm vi cần tính từ D3:D10 sau khi đã lọc dữ liệu.

Bước 4: Nhấn phím Enter để thu được kết quả sau: 

Ở bước nhập công thức, bạn có thể nhập đối số 103 (bỏ qua các giá trị đã bị ẩn) để tính đếm các ô được lọc không trống.

Ví dụ 3: Đánh số thứ tự nhanh

Giả sử bạn cần đánh số thứ tự cho một danh sách với các hàng liên tiếp. Bạn có thể dùng hàm SUBTOTAL trong Excel như sau: 

Bước 1: Tại cột STT, bạn nhập công thức vào ô A2 là: =SUBTOTAL(3,$B$2:B2) và nhấn Enter.

Trong đó:

• function_num: có giá trị là 3, ứng với hàm COUNTA là đếm số ô không trống.
• ref1: nhập giá trị là $B$2:B2 để đếm ký tự trong phạm vi đó. Lúc này, bạn cần cố định ô B2 bằng cách nhấn phím F4. 

Bước 2: Sau đó, ở góc dưới bên phải của ô B2 xuất hiện dấu cộng, bạn hãy nhấp chuột kéo thẳng xuống các ô khác để sao chép công thức. 

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

Lưu ý: khi sao chép công thức, giá trị phải thay đổi theo kiểu: $B$2:B3, $B$2:B4,... thì hàm mới đếm đúng.

Xem thêm: Hướng dẫn 4 cách bỏ dấu trong Excel dễ và nhanh chóng.

Ví dụ 4: Giá trị lớn nhất

Trong bảng dữ liệu DANH SÁCH ĐỒ DÙNG, cần tìm ra giá trị sản phẩm có giá cao nhất. Bạn hãy sử dụng hàm SUBTOTAL để tìm ra giá trị lớn nhất trong bảng tính.

Bước 1: Tại ô tham chiếu hiện kết quả, bạn nhập hàm =SUBTOTAL(4,C3:C7).

Trong đó:

• function_num: bằng 4 vì cần dùng hàm MAX để lấy giá trị lớn nhất.
• ref1:là vùng dữ liệu cần tính, lấy từ C3:C7.

Bước 2: Sau khi nhập công thức, nhấn Enter để hiển thị kết quả.

Tương tự, bạn cũng có thể tìm ra giá trị nhỏ nhất trong bảng dữ liệu Excel với các bước như trên. Thay vào đó, ở phần giá trị đối số function_num sẽ bằng 5 - ứng với hàm MIN.

Hàm MAX trong Excel dùng để làm gì? Cách sử dụng hàm MAX hiệu quả, xem ngay!

Kết hợp hàm SUBTOTAL và một số hàm khác trong Excel

Hàm SUBTOTAL còn được sử dụng lồng ghép vào các hàm khác, chẳng hạn như hàm VLOOKUP, IF. Việc kết hợp này sẽ giúp cho hàm SUBTOTAL tính toán hiệu quả hơn.

Kết hợp Hàm SUBTOTAL và VLOOKUP

Khi hàm SUBTOTAL kết hợp với VLOOKUP sẽ có công thức sau:

=SUBTOTAL(funtion_num, VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Hàm VLOOKUP được sử dụng khi bạn cần tìm dữ liệu trong một bảng và trả về dữ liệu khớp với hàng ngang tương ứng. Khi hàm VLOOKUP kết hợp với hàm SUBTOTAL sẽ giúp bạn phân tích các bảng dữ liệu nhanh chóng mà không cần một bảng tổng hợp phức tạp.

Cùng xem ví dụ sau: 

Khi bạn có 2 bảng dữ liệu ĐIỂM KPI CỦA CÁC NHÓM và DANH SÁCH NHÂN VIÊN TỪNG NHÓM. Bạn đang muốn tìm điểm KPI cao nhất Tháng 11 của nhân viên thuộc Nhóm 2. Bạn có thể làm theo các thao tác dưới đây để tìm ra kết quả:

Bước 1: Nhập công thức tại ô cần trả kết quả:

=SUBTOTAL(4;VLOOKUP(F5;A3:D7;3;false);VLOOKUP(F7;A3:D7;3;false))

Mã hàm SUBTOTAL tương ứng là số 4 để tìm giá trị lớn nhất trong bảng.

Bước 2: Nhấn phím Enter trả kết quả.

Như vậy, hàm VLOOKUP đã trả về giá trị điểm KPI cho nhân viên nhóm 2. Và khi kết hợp với hàm SUBTOTAL sẽ tìm ra giá trị lớn nhất trong số các kết quả trả về.

Xem thêm: Cách copy trong Excel các hàm và lệnh cực đơn giản.

Kết hợp Hàm SUBTOTAL và Hàm IF

Hàm SUBTOTAL được sử dụng như một hàm lồng ghép của hàm IF. Thường được ứng dụng khi cần lọc dữ liệu có điều kiện, có thể áp dụng khi đánh số thứ tự theo cột.

Cú pháp tổng quát là: =IF(logical_test, value_if_true, SUBTOTAL(funtion_num, ref1,...))

Ví dụ: Khi bạn có một bảng dữ liệu cần đếm số thứ tự, nhưng xuất hiện lộn xộn các hàng trống và không trống. Trong trường hợp này, bạn có thể kết hợp SUBTOTAL với hàm IF như sau:

Bước 1: Tại cột STT, bạn nhập công thức tại ô A2: =IF(B2='','',SUBTOTAL(3,$B$2:B2)).

Khi đó với hàm IF:

• logical_test: điều kiện của hàm IF, sẽ lấy giá trị ô B2 bằng khoảng trắng. Nếu ô B2 trống thì trả về giá trị đúng.
• value_if_true: Giá trị trả về trống (“”) nếu điều kiện của hàm IF đúng.
• value_if_false: Giá trị trả về nếu điều kiện hàm IF sai. Lúc này, bạn sẽ dùng hàm SUBTOTAL để đánh số thứ tự.

Trong hàm SUBTOTAL, giá trị đối số bằng 3 tương ứng với hàm COUNTA - đếm số ô không trống. Số thứ tự ở các ô có giá trị sẽ được đánh lại từ đầu khi thực hiện lọc dữ liệu có điều kiện.

Bước 2: 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. Kết quả ta được bảng sau:

Tóm lại, nếu như có một ô trống thì hàm IF sẽ bỏ qua không đếm. Đến ô không trống thì hàm SUBTOTAL sẽ đánh số thứ tự.

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

Khi sử dụng hàm SUBTOTAL, đôi khi bạn sẽ gặp một số lỗi sau:

Lỗi #VALUE! 

Thường xảy ra do 1 trong 2 nguyên nhân:

• Mã định chức năng function_num là số không nằm trong khoảng 1-11 hoặc 101-111. 
• Phạm vi dữ liệu cần tính là tham chiếu dạng 3D. Nghĩa là, khi phạm vi bạn chọn đến các ô trong một trang tính khác sẽ trả về lỗi này.

Để khắc phục lỗi #VALUE!, bạn cần sửa lại thông tin số được nhập ở phần function_num và kiểm tra lại dữ liệu tham chiếu cần tính.

Lỗi #DIV/0!

Lỗi này xảy ra khi bạn sử dụng hàm để chia một số hoặc một tổng cụ thể cho 0. Thông thường là do tính giá trị trung bình cộng hoặc độ lệch chuẩn của một dãy ô không chứa số.

Để fix lỗi này, bạn cần kiểm tra lại thông tin dữ liệu đã nhập vào ô tính.

Lỗi #NAME?

Đây là lỗi do bạn nhập sai tên hàm SUBTOTAL. Vì vậy, bạn cần nhập chính xác tên hàm để không bị hiển thị lỗi này. 

Khi nhập công thức trong ô tính Excel, bạn chỉ cần nhập dấu = và chữ cái đầu S, sẽ xuất hiện một bảng gợi ý để lựa chọn. Bạn có thể bấm vào gợi ý để chọn đúng hàm SUBTOTAL.

Kết luận

Trên đây là hướng dẫn cách dùng hàm SUBTOTAL trong Excel với các ví dụ cụ thể. Bạn có thể thực hiện hàm này trong tính toán để đạt được hiệu quả cao nhất. Hy vọng bài viết sẽ giúp ích cho công việc của bạn đơn giản và dễ dàng hơn.