Khác với hàm SUM quen thuộc, hàm SUBTOTAL là một công cụ mạnh mẽ trong Excel, cho phép bạn tính toán tổng phụ, đếm số ô, tính trung bình, tìm giá trị lớn nhất/nhỏ nhất, hoặc tính tổng các giá trị trong danh sách đã được lọc. Bài viết này sẽ cung cấp hướng dẫn chi tiết về cách sử dụng hàm SUBTOTAL trong Excel, giúp bạn khai thác tối đa tiềm năng của nó.
Mục Lục
Công thức và cú pháp hàm SUBTOTAL
Hàm SUBTOTAL có cú pháp như sau:
=SUBTOTAL(function_num, ref1, [ref2], ...)
Trong đó:
function_num: Một số từ 1 đến 11 hoặc từ 101 đến 111, chỉ định hàm sẽ được sử dụng để tính toán. Bảng dưới đây mô tả các giá trịfunction_numvà chức năng tương ứng:
| Function_num | Chức năng | Bao gồm giá trị ẩn (1-11) | Loại trừ giá trị ẩn (101-111) |
|---|---|---|---|
| 1 (hoặc 101) | AVERAGE | Có | Không |
| 2 (hoặc 102) | COUNT | Có | Không |
| 3 (hoặc 103) | COUNTA | Có | Không |
| 4 (hoặc 104) | MAX | Có | Không |
| 5 (hoặc 105) | MIN | Có | Không |
| 6 (hoặc 106) | PRODUCT | Có | Không |
| 7 (hoặc 107) | STDEV | Có | Không |
| 8 (hoặc 108) | STDEVP | Có | Không |
| 9 (hoặc 109) | SUM | Có | Không |
| 10 (hoặc 110) | VAR | Có | Không |
| 11 (hoặc 111) | VARP | Có | Không |
ref1, ref2, ...: Một hoặc nhiều ô, hoặc dãy ô để tính tổng phụ. Bạn có thể sử dụng tối đa 254 tham chiếu.
Lưu ý quan trọng:
- Hàm SUBTOTAL được thiết kế để tính toán dữ liệu theo chiều dọc (trong cột).
- Nếu các đối số
ref1, ref2,...chứa các hàm SUBTOTAL khác, chúng sẽ bị bỏ qua để tránh tính trùng lặp. - Sự khác biệt giữa
function_num1-11 và 101-111 nằm ở cách chúng xử lý các hàng bị ẩn. Các hàm 1-11 bao gồm cả các giá trị trong hàng ẩn, trong khi các hàm 101-111 chỉ tính toán các giá trị trong các hàng hiển thị. - Hàm SUBTOTAL sẽ bỏ qua các hàng bị ẩn bởi chức năng Filter.
Danh sách các hàm chức năng được hỗ trợ bởi SUBTOTAL
Các ứng dụng thực tế của hàm SUBTOTAL
1. Tính tổng các hàng sau khi lọc (Filter)
Một trong những ứng dụng phổ biến nhất của hàm SUBTOTAL là tính tổng các giá trị trong danh sách sau khi bạn đã lọc dữ liệu. Ví dụ: nếu bạn có một bảng dữ liệu bán hàng và bạn muốn tính tổng doanh thu của một sản phẩm cụ thể sau khi đã lọc theo tên sản phẩm, bạn có thể sử dụng hàm SUBTOTAL.
Công thức tổng quát trong trường hợp này là:
=SUBTOTAL(9, phạm_vi)
Trong đó, phạm_vi là vùng dữ liệu bạn muốn tính tổng sau khi đã lọc. 9 là mã số tương ứng với hàm SUM.
2. Đếm số ô không trống trong danh sách đã lọc
Bạn có thể sử dụng SUBTOTAL với function_num là 3 (COUNTA, bao gồm hàng ẩn) hoặc 103 (COUNTA, loại trừ hàng ẩn) để đếm số ô không trống trong một phạm vi dữ liệu đã được lọc. Khi có hàng ẩn, việc sử dụng SUBTOTAL 103 là rất quan trọng để đảm bảo bạn chỉ đếm các ô không trống mà bạn có thể nhìn thấy.
Ví dụ: xét bảng dữ liệu sau, trong đó hàng 4 và 5 đang bị ẩn:
Bảng dữ liệu ví dụ với các hàng bị ẩn
Nếu bạn sử dụng SUBTOTAL(3, phạm_vi), kết quả sẽ bao gồm cả các ô trong hàng ẩn. Tuy nhiên, nếu bạn sử dụng SUBTOTAL(103, phạm_vi), kết quả sẽ chỉ hiển thị số ô không trống mà bạn thực sự nhìn thấy, bỏ qua các hàng bị ẩn.
Ví dụ về việc sử dụng SUBTOTAL 3 để đếm ô, bao gồm cả hàng ẩn
Nhập công thức =SUBTOTAL(3, A1:A5) hoặc =SUBTOTAL(103, A1:A5). Excel sẽ hiển thị danh sách các tùy chọn hàm để bạn lựa chọn.
Gợi ý hàm SUBTOTAL trong Excel
Kết quả của SUBTOTAL(3, A1:A5) sẽ là 3, vì nó tính cả các ô trong hàng bị ẩn.
Kết quả của hàm SUBTOTAL 3 bao gồm hàng ẩn
Trong khi đó, SUBTOTAL(103, A1:A5) sẽ chỉ hiển thị 1, vì nó bỏ qua các hàng bị ẩn.
Kết quả của hàm SUBTOTAL 103 loại trừ hàng ẩn
3. Bỏ qua các giá trị trong các công thức Subtotal lồng nhau
Một tính năng hữu ích khác của hàm SUBTOTAL là khả năng bỏ qua các giá trị được tính bởi các hàm SUBTOTAL khác khi chúng được lồng vào nhau. Điều này giúp bạn tránh tính toán trùng lặp và đảm bảo tính chính xác của kết quả.
Ví dụ: giả sử bạn muốn tính tổng số lượng vải trong hai kho hàng A1 và A2.
Đầu tiên, bạn tính tổng số vải trong kho A2 bằng công thức =SUBTOTAL(9, C2:C4), kết quả là 19.
Tính tổng số vải trong kho A2 bằng SUBTOTAL
Sau đó, bạn tính tổng số vải trong kho A1 bằng công thức =SUBTOTAL(9, C5:C7), kết quả là 38.
Tính tổng số vải trong kho A1 bằng SUBTOTAL
Cuối cùng, để tính tổng số vải trong cả hai kho, bạn sử dụng công thức =SUBTOTAL(9, C2:C9). Hàm SUBTOTAL sẽ tự động bỏ qua các giá trị đã được tính trước đó cho kho A1 và A2, và chỉ tính tổng các giá trị gốc, cho kết quả chính xác.
Tính tổng số vải trong cả hai kho, bỏ qua các SUBTOTAL lồng nhau
Các lỗi thường gặp khi sử dụng hàm SUBTOTAL
Khi sử dụng hàm SUBTOTAL trong Excel, bạn có thể gặp phải một số lỗi sau:
#VALUE!: Lỗi này xảy ra khi số xác định chức năng (function_num) không nằm trong khoảng 1-11 hoặc 101-111, hoặc khi một trong các tham chiếu (ref) là tham chiếu 3D (ví dụ: tham chiếu đến một ô trên một trang tính khác).#DIV/0!: Lỗi này xảy ra khi một phép chia cho 0 xảy ra trong quá trình tính toán (ví dụ: khi bạn tính trung bình cộng hoặc độ lệch chuẩn của một dãy ô không chứa giá trị số).#NAME?: Lỗi này xảy ra khi bạn nhập sai tên hàm SUBTOTAL (ví dụ: viết sai chính tả).
Lỗi #DIV/0! trong hàm SUBTOTAL
Lời khuyên: Luôn kiểm tra kỹ cú pháp và các đối số của hàm SUBTOTAL để tránh các lỗi này.
Kết luận
Hàm SUBTOTAL là một công cụ linh hoạt và mạnh mẽ trong Excel, cho phép bạn thực hiện nhiều phép tính tổng hợp khác nhau trên dữ liệu của mình. Bằng cách hiểu rõ cú pháp và các ứng dụng của hàm SUBTOTAL, bạn có thể tận dụng tối đa tiềm năng của nó để phân tích và tổng hợp dữ liệu một cách hiệu quả.
Tham khảo thêm:
- Hàm SUMPRODUCT trong Excel: Tính tổng tích các giá trị tương ứng
- Cách gộp 2 cột Họ và Tên trong Excel không mất nội dung