SUBTOTAL là hàm rất hữu ích trong Excel, cho phép người dùng có thể thao tác đơn giản, dễ dàng với các phép tính. Chính vì thế, để giúp các bạn thành thạo trong việc sử dụng hàm SUBTOTAL, JobsGO sẽ chia sẻ chi tiết công thức và cách sử dụng của nó trong bài viết dưới đây!
Hàm SUBTOTAL là một hàm được người dùng ứng dụng rất nhiều trong Excel. Hàm này hỗ trợ người dùng trong việc đếm số ô, tính tổng, tính trung bình cộng hay tìm giá trị lớn nhất/ giá trị nhỏ nhất của bảng dữ liệu.
Cụ thể, hàm SUBTOTAL trong Excel sẽ được mô tả theo cú pháp sau đây:
=SUBTOTAL(function_num, ref1, [ref2],…)
Ý nghĩa cụ thể của từng chỉ số trong công thức này là:
Ref1, Ref2, …: là 1 ô hoặc dãy ô mà bạn muốn tính tổng phụ. Tuy nhiên, ref1, ref2 được giới hạn trong 254 số. Lưu ý, ref1 bắt buộc phải có, giá trị ref2 có thể tùy chọn trong phạm vi 254.
Function_num: hay còn gọi là số xác định chức năng thực hiện với các con số cụ thể từ 1 đến 11 và từ 101 đến 111. Mỗi con số sẽ tương ứng với một hàm tính toán trong SUBTOTAL. Bạn có thể tham khảo rõ hơn trong bảng dưới đây:
function_num
Hàm tính toán (Ý nghĩa của đối số function_num)
Tính toán cả giá trị ẩn (do ẩn hàng)
Không tính toán cả giá trị ẩn (do ẩn hàng)
1
101
Average
2
102
Count
3
103
Counta
4
104
Max
5
105
Min
6
106
Product
7
107
STDEV
8
108
STDEVP
9
109
Sum
10
110
VAR
11
111
VARP
Lưu ý:
Hàm SUBTOTAL không được dùng để tính toán cho hàng dữ liệu mà chỉ phù hợp để thao tác với các dữ liệu sắp xếp theo chiều dọc (hay còn gọi là cột dữ liệu).
Trong trường hợp phạm vi của Ref1, Ref2… có chứa hàm SUBTOTAL thì để tránh việc tính trùng 2 lần, hàm SUBTOTAL đó sẽ được bỏ qua.
Nếu function_num từ 1 đến 11 thì các giá trị ẩn trong vùng dữ liệu bạn chọn sẽ được tính toán cùng. Ngược lại, nếu function_num từ 101 đến 111 thì hàm SUBTOTAL sẽ không tính đến những giá trị đã bị ẩn đi.
Hàm SUBTOTAL sẽ tự động bỏ qua các dữ liệu bị ẩn do Filter.
2. Cách sử dụng hàm SUBTOTAL trong Excel
Hàm SUBTOTAL trong Excel có rất nhiều ứng dụng. Tham khảo kỹ hơn trong nội dung dưới đây:
2.1 Tính tổng các hàng được lọc
Cho một bảng số liệu về KPI của các đội A, B, C như sau và yêu cầu lọc tổng KPI của đội A.
Tính tổng KPI của đội A
Cách tính tổng KPI của đội A bằng hàm SUBTOTAL vô cùng đơn giản với 3 bước sau đây:
Bước 1: Đầu tiên, bạn cần bôi đen toàn bộ bảng chứa dữ liệu mà bạn muốn tính tổng. Sau đó, bạn click vào nút Home trên thanh menu rồi chọn Sort and Filter, sau đó ấn Filter.
Lựa chọn các hàng dữ liệu muốn tính tổng
Bước 2: Bên cạnh các tiêu đề sẽ hiện một dấu mũi tên ngược. Tại ô Đội, bạn click dấu mũi tên rồi tick vào A. Tiếp đến, bạn nhấn OK.
Click vào dấu mũi tên ngược
Bước 3: Lúc này, Excel sẽ tự động lọc ra bảng dữ liệu của độ A. Để thực hiện tính tổng cho KPI của Đội A, bạn nhập hàm SUBTOTAL với cú pháp =SUBTOTAL(9,D2:D11) vào ô muốn hiển thị kết quả. Trong đó:
function_num = 9: là giá trị tương ứng với chức năng tính tổng (SUM). Tuy nhiên, bạn cũng có thể chọn function_num = 109 để bỏ qua những dữ liệu bị ẩn.
ref1 = D2:D11: đây chính phạm vi cột dữ liệu mà bạn muốn tính tổng.
Khi nhập xong hàm SUBTOTAL trong Excel và ấn Enter, màn hình sẽ hiển thị kết quả là 630. Tức là tổng KPI của đội 3 là 630 sản phẩm/ tháng.
Nhập hàm SUBTOTAL để tính tổng
2.2 Đếm các ô được lọc không trống
Giả sử có một bảng dữ liệu như bên dưới và bạn muốn thống kê số lượng sinh viên đã nộp bài tập:
Bạn có thể sử dụng hàm SUBTOTAL để thực hiện phép tính này. Cụ thể:
Bước 1: Tại ô muốn hiển thị kết quả, bạn nhập công thức =SUBTOTAL(3,D4:D14). Trong đó:
function_num = 3 là giá trị tương ứng với hàm COUNTA (đếm số ô không trống) mà bạn muốn thực hiện.
ref1 = D4:D14 là cột dữ liệu mà bạn muốn tính toán.
Bước 2: Sau khi nhập cú pháp đúng của hàm SUBTOTAL, màn hình sẽ hiển thị cho bạn tổng số sinh viên đã nộp bài tập (bao gồm cả những hàng đã bị ẩn đi).
Kết quả được hiển thị trên màn hình
* Lưu ý: Nếu bạn muốn đếm các ô được lọc không trống mà bỏ qua hàng bị ẩn thì bạn chỉ cần chọn function_num = 103.
2.3 Bỏ qua các giá trị trong công thức SUBTOTAL lồng nhau
Dưới đây là ví dụ tính trung bình cộng số vải của kho A1 và kho A2.
Cụ thể, trung bình cộng số vải kho A2 sẽ được thực hiện theo công thức: = SUBTOTAL(1,C2:C4). Màn hình sẽ hiển thị kết quả là 19. Trong đó:
function_num = 1: tương ứng với hàm tính trung bình cộng (AVERAGE).
ref 1 = “C2:C4”: phạm vi cần tính trung bình cộng.
Trung bình cộng số vải kho A2
Tương tự như trên, để tính trung bình cộng số vải kho A1, bạn nhập cú pháp = SUBTOTAL(1,C5:C7) vào vị trí muốn hiển thị kết quả. Trong đó:
function_num = 1: ứng với hàm AVERAGE muốn thực hiện.
ref 1: “C2:C5”: phạm vi dữ liệu tính trung bình cộng.
Trung bình cộng số vải A1
Trong trường hợp bạn muốn tính trung bình cộng tổng số vải ở cả 2 kho A1 và A2, bạn chỉ cần nhập hàm SUBTOTAL với cú pháp =SUBTOTAL(1,C2:C9). Phép tính này sẽ tự động bỏ qua trung bình cộng của từng kho A1 và A2.
Trung bình tổng kho A1 và A2
3. Những nguyên nhân phổ biến khiến hàm SUBTOTAL không hoạt động
Trong quá trình ứng dụng hàm SUBTOTAL có thể không hoạt động do một vài lỗi sau đây. Tham khảo ngay để biết cách phòng tránh nhé!
#VALUE!: Khi hàm SUBTOTAL không cho ra kết quả mà hiển thị #VALUE. Lỗi này đến từ việc function_num nằm ngoài giá trị giới hạn, tức là nó không thuộc khoảng từ 1 đến 11 hay từ 101 đến 111. Ngoài ra, khi chỉ số Ref là tham chiếu 3D, nó cũng có thể dẫn tới lỗi này.
#DIV/0!: Màn hình sẽ hiển thị #DIV/0 nếu phép tính của bạn chứa giá trị 0 ở mẫu số. Lỗi này thường phát sinh trong trường hợp tính trung bình cộng (AVERAGE) hoặc tính độ lệch chuẩn của 1 dãy ô không chứa giá trị số.
#NAME?: tên hàm SUBTOTAL sai chính tả.
Trên đây là toàn bộ những thông tin chi tiết về hàm SUBTOTAL. Hy vọng, bài viết này sẽ hữu ích với bạn.
Bảo Danh
