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é!
Table of Contents
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:
-
Bôi đen bảng chứa dữ liệu cần tính > Vào
Home > Sort and Filter > Filter
.
-
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
.
-
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:
-
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.
-
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.