Tin Tức
Hàm Subtotal là gì? Cách sử dụng hàm Subtotal
Th12
Hàm Subtotal là một hàm rất hiếm khi sử dụng với những ai chỉ dùng excel cơ bản nhưng lại vô cùng quen mặt với nhân viên kế toán hay làm việc với số liệu nhiều và lớn. Vậy cụ thể hàm này là gì và cách sử dụng như thế nào?
Hàm subtotal là gì?
Hàm SUBTOTAL trong Excel có nhiệm vụ để tính TỔNG PHỤ trong một danh sách hoặc cơ sở dữ liệu. Nếu như các hàm Excel khác thường được làm ra để thực hiện chỉ 1 chức năng cụ thể nào đó thì hàm SUBTOTAL lại vô cùng linh hoạt. Nó có thể tính toán chính xác hoặc phép logic như đếm số ô, tính trung bình, tìm giá trị lớn nhất/nhỏ nhất, …
Cú pháp hàm SUBTOTAL
Hàm có cú pháp: SUBTOTAL(function_num, ref1, [ref2],…)
Trong đó:
-
Function_num: con số xác định chức năng thực hiện
-
Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ. Cần phải có Ref 1, từ Ref 2 đến 254 là tuỳ chọn.
Function_number (Số xác định chức năng thực hiện) có 2 loại sau:
-
1 – 11 bỏ qua các ô đã được lọc ra, nhưng để lại các hàng được ẩn thủ công.
-
101 – 111 bỏ qua các ô ẩn – đã lọc ra và ẩn thủ công.
Function_num và tính năng tương ứ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ố |
Số Function_num sẽ tự động hiển thị chức năng tương ứng khi chọn, vì vậy bạn không cần phải nhớ con số này.
Khi nào bạn nên dùng hàm Subtotal
Tính giá trị trong các hàng được chọn
Vì hàm Excel SUBTOTAL bỏ qua các hàng đã được lọc ra, bạn có thể sử dụng nó để tính tổng dữ liệu 1 cách linh hoạt, các giá trị trong tổng phụ được tự động tính lại theo bộ lọc.
Ví dụ, nếu chúng ta lọc bảng doanh số bán hàng chỉ của vùng Miền Đông, công thức SUBTOTAL sẽ tự động điều chỉnh để bỏ qua tất cả vùng khác khi tính tổng.
Lưu ý:
Vì các 2 bộ số xác định chức năng (1-11 và 101-111) đều bỏ qua các ô đã được lọc ra, bạn có thể dùng công thức SUBTOTAL 9 hoặc SUBTOTAL 109.
Tính các ô nhìn thấy
Công thức SUBTOTAL với số xác định chức năng 101-111 bỏ qua các ô đã ẩn, đã lọc ra và ẩn thủ công. Vì thế, khi bạn sử dụng chức năng Excel’s Hide để ẩn dữ liệu không liên quan, dùng số chức năng 101-111 để loại bỏ các giá trị từ những hàng ẩn khỏi tổng phụ.
Bỏ qua giá trị trong công thức SUBTOTAL lồng ghép
Nếu dãy ô trong công thức SUBTOTAL có chứa công thức SUBTOTAL khác, công thức SUBTOTAL được lồng vào sẽ bị bỏ qua. Vậy nên con số trong bảng sẽ không phải tính 2 lần.
Trong hình dưới, công thức tính trung bình chính SUBTOTAL(9, C2:C17) bỏ qua kết quả của công thức SUBTOTAL trong ô C6, C11 và C17.
