Cách sử dụng hàm SUBTOTAL trong Excel dễ hiểu, có ví dụ minh họa

Trong công việc làm việc với Excel, chắc hẳn bạn đã từng nghe tới hàm SUBTOTAL. Hôm nay, chúng ta sẽ cùng tìm hiểu về công thức hàm SUBTOTAL và một số ví dụ cụ thể nhé!

Hàm SUBTOTAL trong Excel và những ứng dụng cụ thể

Đầu tiên, hãy tìm hiểu về công thức của hàm SUBTOTAL. Hàm này có dạng như sau:

=SUBTOTAL(function_num, ref1, ref2,...)

Trong đó:

• function_num là số thể hiện chức năng mà bạn muốn sử dụng. Số này sẽ được hiển thị khi bạn nhập công thức hàm SUBTOTAL. Ví dụ: 9 tương ứng với chức năng tính tổng.
• ref1, ref2,... là ô hoặc dãy ô mà bạn muốn tính toán.

Mã số của function_num tương ứng với các chức năng như sau:

Function_num (gồm có giá trị ẩn)	Function_num (bỏ lỡ giá trị ẩn)	Tương đương Hàm
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

Lưu ý:

• SUBTOTAL thường được sử dụng cho các cột số liệu theo chiều dọc.
• Nếu trong dãy tham chiếu (ref1, ref2,...) có chứa hàm SUBTOTAL, hàm này sẽ được bỏ qua để tránh tính trùng lặp.

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

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

Giả sử bạn có một bảng dữ liệu và bạn muốn tính tổng KPI của đội A sau khi áp dụng bộ lọc. Bạn có thể thực hiện các bước sau:

1. Bôi đen bảng chứa dữ liệu cần tính > Vào Home > Sort and Filter > Filter.
   

2. Bấm chọn dấu mũi tên hình tam giác ngược ở tiêu đề cột “Đội”, tick chọn “A” và nhấn OK.
   

3. Bảng dữ liệu của đội A được Excel lọc ra. 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:

• function_num = 9 tương ứng với hàm SUM – tính tổng.
• ref1 = D2:D11 là phạm vi cần tính tổng.

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

Lưu ý: Bạn có thể dùng giá trị đối số là 9 (tính tổng các giá trị đã bị ẩn) hoặc 109 (bỏ qua các giá trị đã bị ẩn) để tính tổng các hàng đã lọc.

Ví dụ 2: Đếm các ô không trống sau khi lọc

Giả sử bạn có bảng tài liệu và bạn muốn đếm số lượng sinh viên của nhóm C đã có link bài tập để tiện thống kê. Bạn có thể thực hiện các bước sau:

1. Lọc ra các sinh viên thuộc nhóm C bằng cách áp dụng bộ lọc tương tự như ví dụ trước.

2. Tại ô cần lấy kết quả, bạn nhập:
   =SUBTOTAL(3, D4:D14)
   Giải thích công thức:

• function_num = 3 tương ứng với hàm COUNTA – đếm các ô không trống.
• ref1 = D4:D14 là phạm vi cần tính tổng.

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

Lưu ý: Bạn có thể dùng giá trị đối số là 3 (đếm cả các giá trị đã bị ẩn thủ công) hoặc 103 (bỏ qua, không đếm các giá trị đã bị ẩn thủ công) để tính đếm các ô không trống trong các hàng đã lọc.

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

Nếu bạn chỉ cần đánh số thứ tự cho một danh sách tài liệu liên tục (không chứa hàng trống), bạn có thể nhập 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:

• function_num là 3: Đếm ô không trống.
• ref1 là $B$2:B2: Đếm từ ô B2 đến ô B2, trong đó ô B2 được cố định bằng dấu “$” ở trước địa chỉ hàng và ô. Nếu sao chép công thức xuống dưới, giá trị sẽ thay đổi theo kiểu: $B$2:B3, $B$2:B4, $B$2:B5,… và hàm sẽ đếm đúng theo mong muốn.

Sau đó, bạn kéo sao chép công thức đến các ô còn lại. Kết quả thu được sẽ như sau:

Lưu ý: Nếu gặp phải một danh sách dữ liệu phức tạp với các hàng trống và không trống xen kẽ, bạn sẽ cần kết hợp SUBTOTAL với hàm IF. Điều này chỉ áp dụng được khi đánh số thứ tự theo cột.

Cách khắc phục lỗi #VALUE! trong hàm SUBTOTAL

Nếu bạn gặp lỗi #VALUE! khi sử dụng hàm SUBTOTAL, có thể nguyên nhân do:

• Số xác định chức năng function_num không nằm trong khoảng 1 – 11 hoặc 101 – 111.
• Tham chiếu ref trỏ tới các ô trong một trang tính khác.

Để khắc phục lỗi này, bạn có thể làm như sau:

• Kiểm tra lại số xác định chức năng để đảm bảo đã dùng đúng hoặc xóa ký tự dư thừa nếu có.
• Kiểm tra lại tham chiếu và thay thế bằng dãy ô đúng cùng trang tính.

Hi vọng bài viết đã giúp bạn hiểu rõ hơn về cách sử dụng hàm SUBTOTAL trong Excel và ứng dụng của nó. Để biết thêm thông tin chi tiết, bạn có thể truy cập Wiki Fin.