Hãy cùng tìm hiểu về cách sử dụng hàm SUBTOTAL trong Excel ở bài viết bên dưới nhé!
Nội dung bài viết
HÀM SUBTOTAL – CÚ PHÁP VÀ CÁCH SỬ DỤNG
Hàm SUBTOTAL trong Excel có chức năng thực hiện tính tổng phụ trong danh sách hoặc là cơ sở dữ liệu. Ở trường hợp này, “tổng phụ” (subtotal) không phải là tổng những con số trong 1 dãy ô xác định. Trong khi các hàm Excel khác được thiết kế chỉ để thực hiện đúng 1 chức năng cụ thể, thì hàm SUBTOTAL lại rất linh hoạt, bởi nó có thể tính toán hoặc là làm phép logic như đếm số ô, tính trung bình và tìm giá trị lớn nhất/nhỏ nhất…
Hàm SUBTOTAL có ở toàn bộ những phiên bản Excel từ 2016 đến 2007 và cả những phiên bản thấp hơn.
Cú pháp hàm SUBTOTAL như sau:
SUBTOTAL(function_num, ref1, [ref2],…)
Trong đó:
Function_num: là con số xác định chức năng thực hiện
Ref1, Ref2, …: 1 hoặc là nhiều ô, hay dãy ô để tính tổng phụ. Bạn cần phải có Ref 1, từ Ref 2 đến 254 là tuỳ chọn.
- 1 -11 bỏ qua những ô đã được lọc ra, nhưng để lại những hàng được ẩn thủ công.
- 101 – 111 bỏ qua những ô ẩn – đã lọc ra cũng như ẩn thủ công.
| Function_num | 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 ô |
| 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ố |
Khi bạn thực hiện nhập hàm SUBTOTAL Excel vào 1 ô hoặc là trên thanh công thức, lúc này Excel sẽ đưa ra danh sách những con số cho bạn.
Ví dụ, đây là cách mà bạn sử dụng công thức SUBTOTAL 9 để thực hiện cộng tổng các giá trị trong ô, từ C2 đến C8:
Để tiến hành thêm 1 số xác định chức năng vào công thức, hãy nhấn đúp chuột, đánh dấu phẩy, rồi xác định dãy ô, đóng ngoặc, sau đó nhấn Enter. Công thức hoàn chỉnh lúc này như sau:
=SUBTOTAL(9,C2:C8)
Tương tự như vậy, bạn tiến hành viết công thức SUBTOTAL 1 để thực hiện tính trung bình, SUBTOTAL 2 để tiến hành đếm ô chứa số, SUBTOTAL 3 để tiến hành đếm ô không trống. Ở hình bên dưới, 3 chức năng khác đang được sử dụng.
Lưu ý:
Khi bạn sử dụng công thức SUBTOTAL Excel với chức năng tính tổng như SUM hoặc là AVERAGE, Excel sẽ chỉ tính những ô chứa số, loại bỏ ô trống và ô chứa giá trị không phải số.
Bạn đã biết được cách lập công thức SUBTOTAL trong Excel, tuy vậy đôi khi sẽ thắc mắc tại sao lại phải học hàm này? Bởi vì bạn có thể dùng các hàm đơn giản, bình thường hơn như SUM, COUNT, MAX? Lý do sẽ nằm ở phần bên dưới,
3 LÝ DO ĐỂ DÙNG HÀM SUBTOTAL
Khi so sánh với những hàm Excel truyền thống, hàm SUBTOTAL mang đến cho bạn các lợi thế sau:
- Tính giá trị trong các hàng được chọn
Vì hàm Excel SUBTOTAL sẽ bỏ qua những hàng đã được lọc ra, bạn có thể dùng hàm này để tính tổng dữ liệu 1 cách linh hoạt, những giá trị trong tổng phụ sẽ được tự động tính lại theo bộ lọc.
Ví dụ, nếu bạn thực hiện lọc bảng doanh số bán hàng chỉ của vùng Miền Đông, thì công thức SUBTOTAL sẽ tự động được điều chỉnh để bỏ qua toàn bộ vùng khác khi thực hiện tính tổng.
Lưu ý:
Vì 2 bộ số xác định chức năng (1-11 và 101-111) đều sẽ bỏ qua những ô đã được lọc ra, nên bạn có thể sử dụng công thức SUBTOTAL 9 hoặc là SUBTOTAL 109.
- Tính các ô nhìn thấy
Công thức hàm SUBTOTAL với số xác định chức năng 101-111 giúp bỏ qua những ô đã ẩn, đã lọc ra hay ẩn thủ công. Vì vậy, khi bạn dùng chức năng Excel’s Hide để thực hiện ẩn dữ liệu không liên quan, dùng số chức năng 101-111 để loại bỏ những giá trị từ các hàng ẩn khỏi tổng phụ.
- Bỏ qua giá trị trong công thức SUBTOTAL lồng ghép
Nếu như dãy ô trong công thức hàm SUBTOTAL có chứa công thức hàm SUBTOTAL khác, thì công thức hàm SUBTOTAL được lồng vào sẽ bị bỏ qua. Vì vậy, con số trong bảng sẽ không phải tính 2 lần.
Ở hình minh họa bên dưới, công thức tính trung bình chính hàm SUBTOTAL(1, C2:C10) sẽ bỏ qua kết quả của công thức hàm SUBTOTAL trong ô C3 và C10.
Xem thêm: Cách dùng hàm trả về vị trí ô trong Excel
VÍ DỤ SỬ DỤNG SUBTOTAL TRONG EXCEL
Khi bạn mới biết dùng hàm SUBTOTAL, đôi khi sẽ thấy hàm này phức tạp, rắc rối và vô nghĩa. Tuy nhiên, đến lúc bạn dùng nó để giải quyết công việc, thì bạn sẽ nhận ra rằng việc thuần thục hàm SUBTOTAL không khó và giúp ích rất nhiều trong công việc. Các ví dụ sau đây sẽ giúp bạn với một số mẹo nhỏ khi sử dụng hàm SUBTOTAL.
VÍ DỤ 1. SUBTOTAL 9 VS. SUBTOTAL 109
Như đã nói ở phần trên, hàm SUBTOTAL chấp nhận 2 bộ số xác định chức năng 1-11 và 101-111. 2 bộ số này đều bỏ qua những hàng đã được lọc ra, nhưng 1-11 bao gồm những hàng được ẩn thủ công còn 101-111 thì loại trừ. Để có thể hiểu rõ hơn về điểm khác biệt này, mời bạn xem ví dụ bên dưới:
Với tổng những hàng đã được chọn, bạn có thể sử dụng cả SUBTOTAL 9 và SUBTOTAL 109 như hình minh họa bên dưới dưới:
Tuy nhiên nếu có những hàng không liên quan đã ẩn thủ công bằng việc sử dụng lệnh Hide Rows trong Home tab > Cells group > Format > Hide & Unhide, và thực hiện nhấn chuột phải vào các hàng, sau đó Hide, mà bây giờ bạn chỉ cần tổng giá trị trong những hàng nhìn thấy, lựa chọn SUBTOTAL 109:
Những số chức năng khác bạn cũng làm theo cách tương tự. Ví dụ như, để thực hiện đếm ô không trống đã chọn, hãy dùng SUBTOTAL 3 hoặc là SUBTOTAL 103. Tuy nhiên, SUBTOTAL 103 có thể đếm những ô không trống nhìn thấy chính xác nếu có bất cứ hàng ẩn nào trong dãy.
Lưu ý: SUBTOTAL với số chức năng 101-111 bỏ qua giá trị trong hàng bị ẩn, không phải là cột bị ẩn. Ví dụ như, nếu bạn sử dụng công thức như SUBTOTAL(109, A1:E1) để thực hiện tính tổng những số trong hàng ngang, thì cột bị ẩn sẽ không ảnh hưởng đến tổng phụ.
VÍ DỤ 2. IF + SUBTOTAL TÍNH TỔNG DỮ LIỆU
Nếu như bạn đang lập báo cáo tổng kết, bạn cần phải đưa ra những dữ liệu tổng kết khác nhau nhưng bạn lại không có đủ chỗ trống cho tất cả số liệu, thì cách sau đây sẽ là giải pháp:
- Trong 1 ô, hãy tạo 1 danh sách dạng thả xuống (drop-down) có chứa tên những hàm như Total, Max, Min…
- Trong ô kế ô danh sách, hãy thêm công thức hàm IF lồng ghép chứa công thức SUBTOTAL tương ứng với những hàm trong danh sách.
Ví dụ như, giá trị để tính tổng phụ trong ô C12:C16, và danh sách trong ô A17 có chứa Total, Average, Max, Min, thì công thức hàm SUBTOTAL lúc này sẽ như sau:
=IF(A17=”total”, SUBTOTAL(9,C2:C16), IF(A17=”average”, SUBTOTAL(1,C2:C16), IF(A17=”min”, SUBTOTAL(5,C2:C16), IF(A17=”max”, SUBTOTAL(4,C2:C16),””))))
Tiếp theo, tuỳ vào hàm mà người dùng chọn trong danh sách, thì công thức SUBTOTAL tương ứng sẽ tính giá trị trong những hàng được chọn.
Mẹo nhỏ:
Nếu như danh sách cùng ô công thức bỗng biến mất khỏi bảng tính, bạn hãy thực hiện chọn lại chúng trong danh sách lọc.
HÀM EXCEL SUBTOTAL KHÔNG HOẠT ĐỘNG – NHỮNG LỖI THƯỜNG GẶP
Nếu như hàm SUBTOTAL trả về lỗi, thì lúc này nguyên nhân có thể là do một trong các lý do sau đây
#VALUE! Số xác định chức năng đã không nằm trong khoảng 1-11 hoặc là 101-111 hay có tham chiếu (ref) là tham chiếu 3D.
#DIV/0! Xảy ra trong trường hợp1 tổng cụ thể phải chia cho 0 (ví dụ như tính trung bình cộng hoặc là độ lệch chuẩn của 1 dãy ô không chứa giá trị số)
#NAME? tên hàm SUBTOTAL bị sai chính tả.
Trên đây là tổng hợp về cách sử dụng hàm SUBTOTAL trong Excel, chúc bạn thực hiện thành công!
Xem thêm: Hướng dẫn cách dùng hàm SUMIF trong Excel
