Hướng Dẫn Dùng Hàm SUBTOTAL Trong Excel Chi Tiết Nhất 2026
Khám phá cách sử dụng hàm SUBTOTAL trong Excel để tính tổng phụ, đếm ô và thực hiện nhiều tác vụ khác một cách hiệu quả. Bài viết cung cấp hướng dẫn chi tiết, dễ hiểu nhất cho người mới bắt đầu
Mục lục
Hàm SUBTOTAL là gì và tại sao nên sử dụng?
Hàm SUBTOTAL trong Excel là một công cụ mạnh mẽ, cho phép bạn thực hiện các phép tính toán học như tính tổng, trung bình, đếm, tìm giá trị lớn nhất/nhỏ nhất trên một phạm vi dữ liệu. Điểm đặc biệt của SUBTOTAL là khả năng tự động cập nhật kết quả khi bạn lọc hoặc ẩn bớt dữ liệu, điều mà hàm SUM thông thường không làm được.
Nó hữu ích trong việc phân tích dữ liệu mục tiêu, giúp bạn dễ dàng tổng hợp thông tin từ các bảng tính lớn mà không ảnh hưởng đến tổng số chung. SUBTOTAL còn có thể bỏ qua các ô chứa công thức SUBTOTAL khác, tránh tình trạng tính toán trùng lặp.
Công thức hàm SUBTOTAL trong Excel
Cú pháp cơ bản của hàm SUBTOTAL như sau:
=SUBTOTAL(function_num, ref1, [ref2], ...)
Trong đó:
function_num: Là một số quy định hàm tính toán sẽ được sử dụng. Các số từ 1 đến 11 sẽ bao gồm cả các hàng bị ẩn thủ công, trong khi các số từ 101 đến 111 sẽ loại trừ các hàng ẩn thủ công khỏi phép tính.ref1,ref2, ...: Là một hoặc nhiều phạm vi ô mà bạn muốn tính toán tổng phụ. Tối đa có thể lên đến 254 đối số tham chiếu.
Bảng các mã Function_num và chức năng tương ứng
Dưới đây là bảng chi tiết các mã số chức năng mà bạn có thể sử dụng với hàm SUBTOTAL:
| Function_num (1-11) | Function_num (101-111) | Chức năng | Mô tả |
|---|---|---|---|
| 1 | 101 | AVERAGE | Tính trung bình các con số. |
| 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 | Tính kết quả của các ô (nhân các số). |
| 7 | 107 | STDEV | Tính độ lệch chuẩn mẫu dựa trên mẫu. |
| 8 | 108 | STDEVP | Tính độ lệch chuẩn dựa trên toàn bộ số. |
| 9 | 109 | SUM | Cộng các số. |
| 10 | 110 | VAR | Ước tính độ dao động dựa trên mẫu. |
| 11 | 111 | VARP | Ước tính độ dao động dựa trên toàn bộ số. |
Cách sử dụng hàm SUBTOTAL trong Excel
Hàm SUBTOTAL rất linh hoạt và có thể áp dụng trong nhiều tình huống khác nhau. Dưới đây là một số cách sử dụng phổ biến:
1. Tính tổng các hàng được lọc
Khi bạn sử dụng tính năng lọc dữ liệu trong Excel, hàm SUBTOTAL sẽ tự động cập nhật để chỉ tính toán trên các hàng hiển thị. Điều này cực kỳ hữu ích khi làm việc với các danh sách lớn.
Ví dụ: Để tính tổng các giá trị trong phạm vi A1:A10 chỉ hiển thị sau khi lọc, bạn sử dụng công thức =SUBTOTAL(9, A1:A10).
2. Đếm các ô được lọc không trống
Bạn có thể sử dụng SUBTOTAL để đếm số lượng các ô không trống trong một phạm vi đã được lọc. Sử dụng =SUBTOTAL(3, A1:A10) để đếm các ô không trống hoặc =SUBTOTAL(103, A1:A10) để loại trừ các hàng ẩn thủ công.
3. Bỏ qua các giá trị trong công thức SUBTOTAL lồng nhau
Một tính năng quan trọng của SUBTOTAL là khả năng tự động bỏ qua các ô chứa công thức SUBTOTAL khác. Điều này giúp tránh tình trạng tính toán sai lệch khi bạn có nhiều cấp độ tổng phụ trong cùng một bảng tính.
Ví dụ: Nếu bạn có một ô B1 chứa =SUBTOTAL(9, A1:A10) và bạn muốn tính tổng của cột A và ô B1, công thức =SUBTOTAL(9, A1:A10, B1) sẽ chỉ tính tổng cột A, bỏ qua giá trị từ B1 để tránh đếm hai lần.
Sự khác biệt giữa SUBTOTAL 9 và SUBTOTAL 109
Điểm khác biệt mấu chốt giữa các mã function_num từ 1-11 và 101-111 nằm ở cách xử lý các hàng bị ẩn thủ công (không phải do lọc).
SUBTOTAL(9, ...): Sẽ bao gồm cả các hàng bị ẩn thủ công trong phép tính tổng.SUBTOTAL(109, ...): Sẽ loại trừ các hàng bị ẩn thủ công, chỉ tính toán trên các hàng hiển thị.
Trong trường hợp bạn chỉ cần tính tổng các dữ liệu nhìn thấy được và muốn loại bỏ hoàn toàn ảnh hưởng của các hàng ẩn thủ công, hãy ưu tiên sử dụng các mã số từ 101-111. Điều này đảm bảo tính chính xác cao cho kết quả phân tích.
Lưu ý quan trọng khi sử dụng hàm SUBTOTAL
Một số điểm cần ghi nhớ để sử dụng hàm SUBTOTAL hiệu quả:
- Hàm SUBTOTAL được thiết kế chủ yếu để tính toán cho các cột dữ liệu theo chiều dọc.
- Nếu phạm vi tham chiếu (
ref1,ref2...) chứa các ô có công thức SUBTOTAL khác, chúng sẽ bị bỏ qua để tránh tính trùng lặp. - Các mã số từ 101-111 chỉ loại bỏ giá trị trong các hàng bị ẩn thủ công, không ảnh hưởng đến các cột bị ẩn.
Việc nắm vững cách sử dụng hàm SUBTOTAL không chỉ giúp bạn xử lý dữ liệu hiệu quả hơn mà còn nâng cao khả năng phân tích và báo cáo trong Excel. Hãy thực hành với các ví dụ trên để làm quen với công cụ mạnh mẽ này.
Bình luận