Buzz
Hàm SUBTOTAL là một trong những hàm tính tổng trong Excel, nhưng tính năng của nó phức tạp hơn so với hàm SUM. Vậy SUBTOTAL có điểm gì đặc biệt và cách sử dụng ra sao? Hãy cùng khám phá trong bài viết này!
1. Hàm SUBTOTAL trong Excel là gì?
Hàm SUBTOTAL dùng để tính tổng các tập hợp con từ một bảng dữ liệu trong Excel. Cụ thể, bạn có thể sử dụng SUBTOTAL để tính tổng, trung bình, tìm giá trị lớn nhất/nhỏ nhất, và đánh số thứ tự,...
Hàm SUBTOTAL mang lại nhiều lợi ích, nổi bật như:
-
Tính tổng giá trị các hàng một cách nhanh chóng.
-
Dễ dàng tính toán các giá trị ẩn trong bảng dữ liệu.
-
Nếu các giá trị tham chiếu đã chứa hàm SUBTOTAL, công thức sẽ tự động bỏ qua chúng.
2. Công thức tính hàm SUBTOTAL trong Excel
Công thức của hàm SUBTOTAL:
|
=SUBTOTAL (function_num, ref1, [ref2],…) |
Trong đó:
-
Function_num: Là số nằm trong khoảng 1-11 hoặc 101-111. Cụ thể, 1-11 được dùng để tính cả các giá trị ẩn, trong khi 101-111 chỉ tính toán cho các giá trị không ẩn.
-
ref1: Là tham chiếu hoặc vùng dữ liệu đầu tiên cần tính tổng phụ.
-
ref2: Là tham chiếu hoặc vùng dữ liệu tiếp theo cần tính tổng phụ, tối đa 254 tham chiếu có thể được sử dụng.
Dưới đây là bảng Function_num để áp dụng trong công thức SUBTOTAL:
|
Function_num (không tính giá trị ẩn) |
Tên hàm |
Công dụng của hàm |
|
101 |
AVERAGE |
Tính giá trị trung bình |
|
102 |
COUNT |
Đếm số ô có dữ liệu số |
|
103 |
COUNTA |
Đếm số ô không trống dữ liệu |
|
104 |
MAX |
Tìm giá trị lớn nhất |
|
105 |
MIN |
Tìm giá trị nhỏ nhất |
|
106 |
PRODUCT |
Nhân giá trị các ô lại với nhau |
|
107 |
STDEV |
Tính độ lệch chuẩn mẫu |
|
108 |
STDEVP |
Tính độ lệch chuẩn cho toàn bộ số liệu |
|
109 |
SUM |
Cộng tổng các số |
|
110 |
VAR |
Ước lượng độ dao động mẫu |
|
111 |
VARP |
Ước lượng độ dao động cho toàn bộ số liệu |
3. Cách sử dụng hàm SUBTOTAL một cách đơn giản và nhanh chóng
Để nắm rõ hơn về công thức SUBTOTAL, bạn có thể tham khảo các ví dụ dưới đây:
3.1. Sử dụng hàm SUBTOTAL để tính tổng cho vùng dữ liệu đã lọc
Bảng dữ liệu hoa hồng tháng 2 bao gồm cột Nhóm và cột Phí hoa hồng. Để tính tổng phí hoa hồng của Nhóm 1, bạn thực hiện như sau:
Bước 1: Chọn toàn bộ vùng dữ liệu cần tính. Tại thẻ Home, bạn nhấn vào “Sort and Filter” để tiếp tục thực hiện.
Bước 2: Trong mục Sort and Filter, bạn tiếp tục chọn “Filter”.
Bước 3: Tại cột Nhóm, bạn nhấn vào biểu tượng mũi tên ngược rồi chọn “1”, sau đó nhấn “OK”.
Bước 4: Lúc này, bảng dữ liệu đã lọc các giá trị Phí hoa hồng của Nhóm 1. Tại ô mà bạn muốn hiển thị kết quả tổng phí hoa hồng của Nhóm 1, bạn nhập công thức =SUBTOTAL(9,D3).
Trong công thức SUBTOTAL trên, ta có:
-
function_num: Số 9 đại diện cho hàm SUM, dùng để tính tổng các giá trị số.
-
ref 1: D3là khoảng dữ liệu cần được tính tổng.
Kết quả sẽ được hiển thị như hình dưới đây:
3.2. Sử dụng hàm SUBTOTAL để đếm số ô không trống được lọc
Bảng danh sách sản phẩm cần mua gồm các cột Tên, Danh mục và Link sản phẩm. Để đếm số lượng mặt hàng đồ gia dụng đã có link sản phẩm, bạn có thể sử dụng hàm SUBTOTAL trong Excel theo các bước sau:
Bước 1: Chọn toàn bộ vùng dữ liệu và nhấn vào “Sort and Filter”, sau đó chọn “Filter”.
Bước 2: Nhấn vào mũi tên hình tam giác ngược ở cột Danh mục, chọn “Đồ gia dụng”, rồi nhấn “OK”.
Bước 3: Tại ô bạn muốn hiển thị kết quả số lượng mặt hàng đồ gia dụng đã có link sản phẩm, hãy nhập công thức =SUBTOTAL(3,D3).
Trong công thức này:
function_num: Số 3 đại diện cho hàm COUNTA, được sử dụng để đếm số ô không trống.
ref1: Đây là phạm vi đếm từ D3.
Bước 4: Nhấn Enter để xem kết quả số lượng mặt hàng Đồ gia dụng đã có link sản phẩm.
3.3. Sử dụng hàm SUBTOTAL để đánh số thứ tự một cách nhanh chóng
Trong bảng danh sách nhân viên của nhóm, bạn có thể áp dụng công thức SUBTOTAL để tạo số thứ tự. Cụ thể, thực hiện theo các bước sau:
Bước 1: Tại cột STT, nhập công thức =SUBTOTAL(3,$B$3) vào ô A3 và nhấn Enter.
function_num: Số 3 tương ứng với hàm COUNTA, dùng để đếm số ô không có giá trị.
ref1: Phạm vi cần đếm là $B$3. Để cố định ô B3, chỉ cần nhấn phím F4.
Bước 2: Khi ô A3 hiển thị kết quả là 1, bạn click vào dấu cộng bên phải ô B3 và kéo xuống để sao chép công thức SUBTOTAL.
Kết quả số thứ tự sẽ được hiển thị như hình dưới đây:
3.4. Sử dụng hàm SUBTOTAL để xác định giá trị lớn nhất
Trong bảng dữ liệu DANH SÁCH ĐỒ DÙNG có cột Sản phẩm và Thành tiền, để tìm sản phẩm có giá trị cao nhất, bạn có thể áp dụng công thức SUBTOTAL theo các bước dưới đây:
Bước 1: Ở vị trí cần hiển thị kết quả, bạn nhập công thức =SUBTOTAL(4,C3).
Trong công thức trên:
-
function_num: Số 4 tương ứng với hàm MAX, được sử dụng để tìm giá trị lớn nhất.
-
ref1: Là phạm vi dữ liệu C3.
Bước 2: Nhấn Enter để hiển thị giá trị thành tiền lớn nhất.
4. Kết hợp hàm SUBTOTAL với các hàm khác trong Excel
Ngoài những công dụng đã đề cập, bạn cần nắm rõ cách sử dụng hàm SUBTOTAL kết hợp với các hàm khác để giải quyết các trường hợp phức tạp hơn. Ví dụ như:
4.1. Kết hợp hàm SUBTOTAL với VLOOKUP
Khi sử dụng SUBTOTAL cùng với VLOOKUP, bạn sẽ có công thức như sau:
|
=SUBTOTAL(function_num, VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) |
Cụ thể, hàm VLOOKUP được sử dụng để tìm kiếm dữ liệu trong bảng và trả về kết quả theo hàng ngang tương ứng. Việc kết hợp SUBTOTAL với VLOOKUP cho phép phân tích dữ liệu nhanh chóng và hiệu quả hơn.
Chẳng hạn: Dưới đây là bảng dữ liệu “ĐIỂM KPI CỦA CÁC NHÓM” cùng với “DANH SÁCH NHÂN VIÊN NHÓM”. Nếu bạn muốn tìm điểm KPI cao nhất của nhân viên nhóm 2 trong tháng 11, hãy thực hiện theo các bước sau:
Bước 1: Tại ô nơi bạn muốn hiển thị kết quả, hãy nhập công thức sau:
=SUBTOTAL(4;VLOOKUP(F5;A3;3;false);VLOOKUP(F7;A3;3;false))
Tại đây, function_num của hàm SUBTOTAL là 4, tương đương với hàm MAX (tìm giá trị lớn nhất).
Bước 2: Bạn nhấn Enter để hiển thị kết quả điểm KPI cao nhất của nhân viên nhóm 2 trong tháng 11.
4.2. Sử dụng hàm SUBTOTAL kết hợp với Hàm IF
Việc kết hợp hàm SUBTOTAL với IF được áp dụng khi lọc dữ liệu theo điều kiện hoặc khi đánh số thứ tự dựa trên cột.
Công thức kết hợp SUBTOTAL với IF như sau:
|
=IF(logical_test, value_if_true, SUBTOTAL(function_num, ref1,...)) |
Ví dụ: Bảng dữ liệu DANH SÁCH NHÂN VIÊN NHÓM có chứa cả hàng trống và không trống. Để đánh số thứ tự, bạn thực hiện theo các bước sau:
Bước 1: Tại cột STT, bạn nhập công thức =IF(B3='','',SUBTOTAL(3,$B$3)) vào ô A3.
Trong đó:
-
logical_test: Điều kiện của hàm IF nhằm xác định xem ô B3 có phải là khoảng trắng hay không, nếu B3 trống thì trả về giá trị đúng.
-
value_if_true: Nếu điều kiện hàm IF đúng (B3 là khoảng trắng), nó sẽ trả về giá trị rỗng (“”).
-
value_if_false: Nếu điều kiện hàm IF sai (B3 không trống), nó sẽ trả về giá trị không rỗng. Trong trường hợp này, hàm SUBTOTAL(3,$B$3) có giá trị số là 3, tương ứng với hàm COUNTA (đếm số ô không trống). Do đó, khi IF sai, kết quả sẽ là đánh số thứ tự.
Bước 2: Bạn nhấn Enter và kéo dấu cộng ở bên trái ô B3 xuống A12 để sao chép công thức. Cụ thể, nếu ô trong cột STT trống, hàm IF sẽ không đánh số thứ tự. Ngược lại, nếu ô không trống, hàm SUBTOTAL sẽ đánh số thứ tự tương ứng.
5. Những lỗi thường gặp khi sử dụng hàm SUBTOTAL trong Excel
Trong quá trình sử dụng công thức SUBTOTAL, có thể gặp một số lỗi phổ biến như:
- Lỗi #VALUE!
Lỗi này có thể xuất hiện do một số nguyên nhân như:
-
function_num không nằm trong khoảng 1-11 hoặc 101-111.
-
Phạm vi dữ liệu tham chiếu nằm trên một trang tính khác.
Trong trường hợp này, bạn cần kiểm tra lại giá trị function_num và phạm vi dữ liệu tham chiếu trong hàm SUBTOTAL.
- Lỗi #DIV/0!
Lỗi này xảy ra khi bạn sử dụng SUBTOTAL để chia một giá trị cho 0. Nguyên nhân thường là do tính toán độ lệch chuẩn hoặc giá trị trung bình của một dãy ô không chứa số. Vì vậy, bạn cần kiểm tra lại dữ liệu đã nhập trong bảng.
- Lỗi #NAME?
Lỗi này xuất hiện khi bạn nhập sai tên hàm SUBTOTAL. Bạn chỉ cần xác minh lại tên hàm để đảm bảo tính chính xác. Khi nhập công thức trong Excel, chỉ cần gõ =S, hệ thống sẽ tự động hiển thị gợi ý các hàm liên quan. Bạn chỉ cần nhấp chọn SUBTOTAL là xong.
Hàm SUBTOTAL rất hữu ích trong việc tính tổng ở nhiều tình huống khác nhau, vì vậy bạn cần nắm vững cách sử dụng để áp dụng hiệu quả. Hy vọng các ví dụ trong bài viết trên sẽ giúp bạn tính toán nhanh chóng và chính xác nhất!
Mytour đang có nhiều ưu đãi hấp dẫn, giảm đến 50%++ cho các mặt hàng như máy lạnh, tủ lạnh, máy giặt, tivi, và điện thoại. Bạn có thể yên tâm mua sắm tại siêu thị vì sản phẩm được cam kết 100% chính hãng, đổi trả trong 35 ngày, có hỗ trợ trả góp 0% lãi suất với tỷ lệ duyệt cao. Nhận ngay ưu đãi bằng cách ghé thăm cửa hàng gần nhất hoặc đặt hàng TẠI ĐÂY để sở hữu những sản phẩm ưng ý! |
