Hàm SUBTOTAL trong Excel: Hướng dẫn dùng đơn giản & dễ hiểu

Hàm SUBTOTAL trong Excel là một công cụ mạnh mẽ giúp tính toán tổng của một dải ô trong bảng dữ liệu. Hàm này không chỉ giúp bạn có được giá trị tổng, mà còn cho phép tính toán các giá trị khác như trung bình, số lượng, và giá trị lớn nhất hoặc nhỏ nhất. Hãy cùng tìm hiểu chi tiết về công thức và cách áp dụng hàm SUBTOTAL trong bài viết này!

Hàm SUBTOTAL trong Excel là gì?

Hàm SUBTOTAL trong Excel sử dụng để tính toán nhiều loại thông tin khác nhau từ một bảng dữ liệu lớn, cho phép bạn có thể dễ dàng tổng hợp và phân tích mà không gặp khó khăn. Nó thể hiện sự linh hoạt khi làm việc với các dữ liệu được lọc hoặc ẩn.

Lợi ích của việc sử dụng hàm SUBTOTAL trong Excel bao gồm:

• Thực hiện tính toán tổng hợp cho các hàng đã chọn một cách thuận tiện.
• Cho phép tính các giá trị dù cho chúng có bị ẩn hay không.
• Tự động bỏ qua các ô có chứa hàm SUBTOTAL, nhằm tránh tính toán trùng lặp.

Xem thêm: Nếu bạn cần thay pin iPhone chính hãng giá tốt ở TP.HCM hoặc Hà Nội, ghé ngay Dlz Tech để xem ưu đãi mới nhất!

Công thức hàm SUBTOTAL

Cú pháp của hàm SUBTOTAL được định nghĩa như sau: =SUBTOTAL(function_num, ref1, [ref2],…)

Trong đó:

• Function_num: Là một số trong khoảng 1-11 hoặc 101-111 cho biết kiểu hàm tính toán. Các số 1-11 bao gồm cả giá trị ẩn, trong khi các số 101-111 chỉ tính toán các giá trị không ẩn.
• Ref1: Là ô hoặc phạm vi vật liệu đầu tiên cần tính toán.
• Ref2,…: Là các ô hoặc phạm vi tính từ 2 đến 254.

Dưới đây là bảng liệt kê các mã số Function_num dùng 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 ô chứa giá trị số
3	103	COUNTA	Đếm số ô không rỗ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	Tính tích
7	107	STDEV	Tính độ lệch chuẩn mẫu
8	108	STDEVP	Tính độ lệch chuẩn cho toàn bộ số
9	109	SUM	Cộng tất cả
10	110	VAR	Ước lượng độ dao động trên mẫu
11	111	VARP	Ước lượng độ dao động cho toàn bộ

Các điều cần lưu ý khi sử dụng hàm SUBTOTAL trong Excel:

• Hàm SUBTOTAL chủ yếu được áp dụng cho các cột dữ liệu hoặc vùng dọc, không sử dụng cho hàng hoặc phạm vi ngang.
• Hàm này sẽ chỉ tính giá trị của các hàng bị ẩn bằng lệnh Hide, còn các hàng bị ẩn do Auto Filter thì sẽ bị bỏ qua.
• Các hàm SUBTOTAL lồng trong ref1, ref2,… sẽ bị loại bỏ để tránh tính trùng.
• Mọi giá trị 3D đều gây ra lỗi #VALUE! khi áp dụng hàm này.

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

Dưới đây là hướng dẫn chi tiết về cách áp dụng hàm SUBTOTAL trong Excel với từng ví dụ cụ thể:

Ví dụ 1: Tính tổng cho vùng dữ liệu đã lọc

Giả sử bạn đang làm việc với bảng DANH SÁCH HOA HỒNG THÁNG, với hai cột là Nhóm và Phí hoa hồng, và bạn cần tìm tổng phí hoa hồng của đội 1:

Bước 1: Chọn toàn bộ bảng dữ liệu, tới tab Home và chọn Sort and Filter.

Bước 2: Tại mục Sort and Filter, chọn tiếp Filter.

Bước 3: Trong cột dữ liệu Nhóm, chọn mũi tên rồi tick vào mục 1 và bấm OK.

Bước 4: Các giá trị của Nhóm 1 sẽ được hiện ra. Nhập công thức vào ô kết quả là: =SUBTOTAL(9,D3:D9)

Trong đó:

• function_num: Sử dụng giá trị 9 để tính tổng phí hoa hồng, tương ứng với hàm SUM.
• ref1: Phạm vi ô cần tính từ D3 đến D9.

Bấm Enter để có kết quả như hình:

Đối với phép tính, bạn cũng có thể sử dụng 109 để bỏ qua các giá trị bị ẩn.

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

Giả sử bạn có bảng DANH SÁCH SẢN PHẨM CẦN MUA, với danh mục đồ gia dụng và mỹ phẩm. Bạn muốn biết số lượng sản phẩm đồ gia dụng có link mua hàng:

Bước 1: Bạn cần lọc sản phẩm thuộc nhóm đồ gia dụng, tiếp tục sử dụng lệnh Auto Filter giống như ví dụ trên.

Bước 2: Trong cột Danh mục, tick chọn mục Đồ gia dụng và nhấn OK.

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

Xem thêm: Hướng dẫn hàm tách chữ trong Excel một cách đơn giản.

Với công thức này:

• function_num: Giá trị là 3, đồng nghĩa với hàm COUNTA được dùng để đếm các ô không trống.
• ref1: Tham chiếu vùng cần tính là D3:D10 sau khi lọc dữ liệu.

Bước 4: Nhấn Enter và bạn sẽ nhận kết quả:

Bạn cũng có thể điều chỉnh để sử dụng giá trị 103 với mục đích bỏ qua các ô ẩn.

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

Giả sử bạn cần đánh số thứ tự cho danh sách hàng loạt. Mọi thứ sẽ đơn giản hơn với hàm SUBTOTAL:

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

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

• function_num: có giá trị 3, tức là ứng với hàm COUNTA cho phép đếm ô không trống.
• ref1: Đặt trở lại $B$2:B2 nhằm đếm ký tự trong đoạn này. Đảm bảo bạn thực hiện cố định ô B2 bằng phím F4.

Bước 2: Kéo dấu cộng ở góc dưới bên phải của ô B2 xuống để sao chép công thức vào các ô bên dưới.

Bước 3: Kết quả sẽ xuất hiện như hình:

Lưu ý: Để số thứ tự hoạt động chính xác, giá trị cần thay đổi kiểu: $B$2:B3, $B$2:B4,…

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

Ví dụ 4: Tìm giá trị lớn nhất

Trong bảng DANH SÁCH ĐỒ DÙNG, nếu bạn cần biết sản phẩm nào có giá cao nhất, sử dụng hàm SUBTOTAL sẽ giúp bạn tìm ra điều đó.

Bước 1: Trong ô để hiển thị kết quả, nhập công thức: =SUBTOTAL(4,C3:C7).

Trong đó:

• function_num: Số 4 được sử dụng để xác định hàm MAX cho kết quả lớn nhất.
• ref1: Vùng dữ liệu được cung cấp là C3:C7.

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

Tương tự, bạn có thể tìm ra giá trị nhỏ nhất bằng cách sử dụng 5 cho hàm MIN.

Hàm MAX trong Excel giúp bạn tìm giá trị lớn nhất trong một dải ô, tìm hiểu thêm ngay!

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

Hàm SUBTOTAL có thể được lồng ghép với nhiều hàm khác, như VLOOKUP và IF, giúp tối ưu hóa hiệu quả tính toán.

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

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

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

Hàm VLOOKUP thường được dùng để tìm kiếm dữ liệu trong bảng và trả giá trị tương ứng trong hàng. Khi kết hợp với hàm SUBTOTAL, bạn có thể nhanh chóng tổng hợp và phân tích dữ liệu mà không cần phải tạo bảng tổng hợp phức tạp.

Ví dụ: Nếu bạn có bảng ĐIỂM KPI CỦA CÁC NHÓM và DANH SÁCH NHÂN VIÊN. Bạn muốn tìm điểm KPI cao nhất trong tháng của nhóm 2:

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

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

Hàm SUBTOTAL số 4 để tìm giá trị lớn nhất trong bảng.

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

Hàm VLOOKUP sẽ trả giá trị điểm KPI cho nhân viên nhóm 2, kết hợp với SUBTOTAL để được giá trị lớn nhất từ các kết quả này.

Xem thêm: Cách copy công thức trong Excel hiệu quả và dễ dàng.

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

Sử dụng hàm SUBTOTAL kết hợp với hàm IF cho phép bạn lọc dữ liệu có điều kiện, áp dụng cho mọi danh sách yêu cầu đánh số tự động.

Cú pháp tổng quát: =IF(logical_test, value_if_true, SUBTOTAL(function_num, ref1,…))

Ví dụ, nếu bạn cần đánh số thứ tự cho danh sách nhưng có nhiều hàng trống và không trống:

Bước 1: Trong cột STT, nhập công thức vào ô A2: =IF(B2=”,”,SUBTOTAL(3,$B$2:B2)).

Cấu trúc hàm IF có các phần như sau:

• logical_test: Nơi điều kiện ở ô B2 có phải trống hay không, nếu phải thì trả về kết quả đúng.
• value_if_true: Giá trị trả về nếu là đúng, sẽ để trống (“”).
• value_if_false: Nếu điều kiện không đúng, số thứ tự sẽ được đánh từ hàm SUBTOTAL.

Hàm SUBTOTAL sẽ sử dụng giá trị đối số bằng 3, tương ứng với hàm COUNTA, giúp bạn đếm số ô không trống theo từng bước lọc.

Bước 2: Nhấn Enter và sao chép công thức từ ô A2 đến các ô khác. Kết quả thu được như sau:

Tóm lại, hàm IF sẽ giúp bạn bỏ qua các ô trống và SUBTOTAL đánh số cho những ô không trống.

Các lỗi thường gặp khi sử dụng hàm SUBTOTAL trong Excel

Khi áp dụng hàm SUBTOTAL, bạn có thể gặp những lỗi phổ biến:

Lỗi #VALUE!

Lỗi này thường do:

• Mã function_num không nằm trong khoảng từ 1-11 hoặc 101-111.
• Phạm vi tham chiếu không đúng kiểu 3D.

Sửa lỗi #VALUE! bằng cách kiểm tra lại mã function_num và các phạm vi tham chiếu.

Lỗi #DIV/0!

Lỗi này xuất hiện khi chia một giá trị cho 0, thường xảy ra khi cố tính toán giá trị trung bình của ô không chứa số.

Để khắt phục, hãy kiểm tra các dữ liệu trong ô tính.

Lỗi #NAME?

Lỗi này xảy ra khi tên hàm SUBTOTAL không chính xác. Bạn cần phải nhập đúng tên hàm để không bị lỗi.

Khi nhập hàm trong Excel, hãy bắt đầu với dấu “=” và chữ “S”, một bảng gợi ý sẽ xuất hiện để bạn chọn đúng.

Kết luận

Bài viết đã hướng dẫn bạn về hàm SUBTOTAL trong Excel với chi tiết từ công thức đến cách sử dụng và các lỗi thường gặp. Hy vọng bài viết sẽ hữu ích trong việc giúp bạn làm việc hiệu quả hơn với Excel!

Tác Giả Minh Tuấn

Minh Tuấn là một chuyên gia dày dặn kinh nghiệm trong lĩnh vực công nghệ thông tin và bảo trì thiết bị điện tử. Với hơn 10 năm làm việc trong ngành, anh luôn nỗ lực mang đến những bài viết dễ hiểu và thực tế, giúp người đọc nâng cao khả năng sử dụng và bảo dưỡng thiết bị công nghệ của mình. Minh Tuấn nổi bật với phong cách viết mạch lạc, dễ tiếp cận và luôn cập nhật những xu hướng mới nhất trong thế giới công nghệ.

Thông tin liên hệ

Dlz Tech

Facebook : https://www.facebook.com/dlztech247/

Hotline : 0767089285

Website : https://dlztech.com

Email : dlztech247@gmail.com

Địa chỉ : Kỹ thuật Hỗ Trợ 24 Quận/Huyện Tại TPHCM.

Bài viết liên quan