Hàm SUBTOTAL trong Excel: Hướng Dẫn Sử Dụng Chi Tiết
Hàm SUBTOTAL trong Excel là một công cụ mạnh mẽ giúp tính toán các phép toán như tổng, trung bình, đếm, và nhiều phép toán khác trên một dãy dữ liệu, đồng thời có khả năng bỏ qua các dòng đã bị ẩn (filtered rows). Điều này làm cho hàm SUBTOTAL trở thành một lựa chọn lý tưởng khi bạn cần tính toán cho một bảng dữ liệu mà bạn muốn ẩn một số dòng để phân tích một cách linh hoạt hơn.
Trong bài viết này, chúng ta sẽ cùng tìm hiểu về cách sử dụng hàm SUBTOTAL, cú pháp, các loại phép toán có thể sử dụng và những lưu ý khi làm việc với hàm này.
1. Cú Pháp Của Hàm SUBTOTAL
Cú pháp của hàm SUBTOTAL trong Excel rất đơn giản:
=SUBTOTAL(function_num, ref1, [ref2], …)
- function_num: Là một số từ 1 đến 11 hoặc 101 đến 111, xác định loại phép toán mà bạn muốn thực hiện. (Xem chi tiết ở phần dưới).
- ref1, ref2, …: Là các tham chiếu đến các phạm vi hoặc các dãy số mà bạn muốn áp dụng phép toán.
Ví dụ: Nếu bạn muốn tính tổng các giá trị trong phạm vi A1
, công thức sẽ là:
=SUBTOTAL(9, A1:A10)
Ở đây, 9 là mã số đại diện cho phép toán tổng (SUM).
2. Các Phép Toán Của Hàm SUBTOTAL
Hàm SUBTOTAL cho phép bạn thực hiện nhiều phép toán khác nhau thông qua mã số function_num. Dưới đây là danh sách các phép toán phổ biến với mã số tương ứng:
| function_num | Tên Phép Toán | Mô Tả |
| 1 | AVERAGE | Tính giá trị trung bình. |
| 2 | COUNT | Đếm số ô có chứa giá trị số. |
| 3 | COUNTA | Đếm số ô không rỗng (bao gồm văn bản và số). |
| 4 | MAX | Tìm giá trị lớn nhất. |
| 5 | MIN | Tìm giá trị nhỏ nhất. |
| 6 | PRODUCT | Tính tích (sản phẩm) của các giá trị. |
| 7 | STDEV | Tính độ lệch chuẩn mẫu. |
| 8 | STDEVP | Tính độ lệch chuẩn của tổng thể. |
| 9 | SUM | Tính tổng các giá trị trong phạm vi. |
| 10 | VAR | Tính phương sai mẫu. |
| 11 | VARP | Tính phương sai của tổng thể. |
Sự khác biệt giữa 1-11 và 101-111
- Các số từ 1-11 thực hiện phép toán trên tất cả các dữ liệu, bao gồm cả các dòng bị ẩn.
- Các số từ 101-111 thực hiện phép toán nhưng bỏ qua các dòng bị ẩn, rất hữu ích khi bạn lọc dữ liệu và muốn chỉ tính các dòng hiển thị.
3. Ví Dụ Sử Dụng Hàm SUBTOTAL
Ví dụ 1: Tính Tổng (SUM)
Giả sử bạn có một danh sách các số từ A1 đến A10 và muốn tính tổng các giá trị trong danh sách đó. Bạn có thể sử dụng công thức sau:
=SUBTOTAL(9, A1:A10)
Ở đây, 9 là mã số của phép toán SUM (tổng).
Ví dụ 2: Tính Trung Bình (AVERAGE)
Nếu bạn muốn tính trung bình của các giá trị trong phạm vi A1 đến A10, công thức sẽ là:
=SUBTOTAL(1, A1:A10)
Trong trường hợp này, 1 đại diện cho phép toán AVERAGE (trung bình).
Ví dụ 3: Đếm Số Ô (COUNT)
Để đếm số ô có chứa dữ liệu số trong phạm vi A1 đến A10, bạn sẽ sử dụng:
=SUBTOTAL(2, A1:A10)
Ở đây, 2 là mã số của phép toán COUNT.
4. Lợi Ích Của Hàm SUBTOTAL
Hàm SUBTOTAL mang lại nhiều lợi ích khi bạn làm việc với các bảng dữ liệu lớn:
- Bỏ qua các dòng bị ẩn: Khi bạn sử dụng mã số từ 101 đến 111, hàm SUBTOTAL sẽ tự động bỏ qua các dòng bị ẩn (do lọc dữ liệu), giúp bạn chỉ tính toán trên những dòng có giá trị hiển thị.
- Tính toán linh hoạt: Bạn có thể áp dụng nhiều phép toán khác nhau như SUM, AVERAGE, COUNT… chỉ bằng cách thay đổi function_num.
- Dễ sử dụng: Hàm này rất dễ sử dụng, đặc biệt khi làm việc với các bảng dữ liệu có nhiều hàng và cột.
5. Lưu Ý Khi Sử Dụng Hàm SUBTOTAL
- Lọc dữ liệu: Khi bạn lọc dữ liệu (filter), hàm SUBTOTAL sẽ tự động tính toán các giá trị chỉ trên những hàng không bị ẩn. Điều này giúp bạn có được các kết quả chính xác khi phân tích dữ liệu.
- Không sử dụng hàm SUM trong các bảng đã có SUBTOTAL: Nếu bạn đang sử dụng SUBTOTAL trong bảng tính, không nên sử dụng hàm SUM trong phạm vi đó, vì điều này có thể dẫn đến tính toán sai do việc bao gồm cả các hàng đã bị tính bằng SUBTOTAL.
Kết Luận
Hàm SUBTOTAL trong Excel là một công cụ rất hữu ích và linh hoạt cho phép bạn thực hiện nhiều phép toán khác nhau trên dữ liệu của mình. Với khả năng bỏ qua các dòng bị ẩn khi lọc dữ liệu, nó trở thành lựa chọn tối ưu để tính toán trong các bảng dữ liệu lớn hoặc khi bạn cần phân tích dữ liệu theo các nhóm nhỏ. Hy vọng bài viết này đã giúp bạn hiểu rõ hơn về cách sử dụng hàm SUBTOTAL trong Excel.