Hàm SUBTOTAL được áp dụng để tính tổng dãy số theo điều kiện cụ thể trong Excel. Qua đó giúp bạn tăng cường hiệu suất làm việc và tiết kiệm thời gian. Nếu bạn chưa biết cách sử dụng hàm SUBTOTAL, hãy cùng mình tìm hiểu chi tiết trong bài viết lần này.
1. Hàm SUBTOTAL là gì?
Hàm SUBTOTAL trong Excel là một công cụ quan trọng để tính toán dữ liệu trong bảng tính. Hàm này cho phép tính tổng, trung bình, đánh số thứ tự, tìm giá trị lớn nhất hoặc nhỏ nhất từ một tập hợp con của dữ liệu. Việc sử dụng hàm SUBTOTAL mang lại nhiều lợi ích, bao gồm:
- Linh hoạt trong việc tính tổng giá trị từ các hàng được chọn.
- Dễ dàng tính toán cả các giá trị ẩn trong bảng dữ liệu.
- Tự động loại bỏ giá trị chứa hàm SUBTOTAL để tránh tính trùng lặp.
2. Cú pháp của hàm SUBTOTAL
Trong Excel, SUBTOTAL được sử dụng để tính toán dữ liệu theo các điều kiện cụ thể.
Công thức:
=SUBTOTAL(function_num, ref1, [ref2],…)
Trong đó:
- Function_num: Xác định loại hàm mà bạn muốn áp dụng trong hàm SUBTOTAL, với các con số từ 1 đến 11 và 101 đến 111.
- Ref1, Ref2, …: Các ô hoặc dãy ô mà bạn muốn tính tổng phụ.
Ngoài ra, khi sử dụng hàm SUBTOTAL, bạn cũng cần lưu ý:
- Hàm này được dùng để tính tổng cho các cột số liệu theo chiều dọc.
- Đối số Ref1, Ref2,… chứa hàm SUBTOTAL sẽ bị bỏ qua để tránh tính toán trùng lặp.
- Hàm SUBTOTAL sẽ tính toán cả giá trị bị ẩn trong dữ liệu, tùy thuộc vào Function_num được sử dụng.
- Nếu có vùng dữ liệu bị ẩn với Filter, hàm SUBTOTAL sẽ bỏ qua tính toán trên vùng đó.
3. Kết hợp với các hàm khác
SUBTOTAL cũng có thể kết hợp với một số hàm như VLOOKUP hoặc IF để trả kết quả phù hợp nhất với yêu cầu của bạn.
3.1. VLOOKUP
Kết hợp giữa hàm VLOOKUP và hàm SUBTOTAL giúp phân tích bảng dữ liệu một cách nhanh chóng mà không cần đến bảng tổng hợp phức tạp.
Khi kết hợp hàm SUBTOTAL với VLOOKUP, công thức sẽ có dạng như sau:
=SUBTOTAL(funtion_num, VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Ví dụ: Khi bạn cần tìm điểm KPI cao nhất của nhân viên trong một nhóm dữ liệu, bạn có thể sử dụng hàm SUBTOTAL và VLOOKUP.
Bước 1: Nhập công thức vào ô cần hiển thị kết quả.
=SUBTOTAL(4;VLOOKUP(F5;A3:D7;3;false);VLOOKUP(F7;A3:D7;3;false))
Trong đó, mã hàm SUBTOTAL tương ứng với việc tìm giá trị lớn nhất trong bảng.
Bước 2: Nhấn Enter để xem kết quả.
3.2. IF
Hàm SUBTOTAL được sử dụng như một hàm lồng ghép của hàm IF để lọc dữ liệu có điều kiện và đánh số thứ tự theo cột.
Khi kết hợp SUBTOTAL và IF, công thức sẽ có dạng như sau:
=IF(logical_test, value_if_true, SUBTOTAL(funtion_num, ref1,…))
Ví dụ: Khi cần đếm số thứ tự trong một bảng dữ liệu với các hàng trống và không trống, bạn có thể kết hợp SUBTOTAL với IF như sau:
Bước 1: Tại cột STT, nhập công thức tại ô A2: =IF(B2=”,”,SUBTOTAL(3,$B$2:B2)).
Trong đó:
- logical_test: Điều kiện của IF, nếu ô B2 trống thì trả về giá trị đúng.
- value_if_true: Giá trị trả về trống (“”) nếu điều kiện đúng.
- value_if_false: Sử dụng SUBTOTAL để đánh số thứ tự.
Trong hàm SUBTOTAL, giá trị đối số 3 tương ứng với hàm COUNTA – đếm số ô không trống. Số thứ tự sẽ được đánh lại từ đầu khi thực hiện lọc dữ liệu có điều kiện.
Bước 2: Nhấn Enter sau khi nhập công thức và sao chép từ ô A2 đến các ô còn lại để đánh số thứ tự cho bảng dữ liệu.
4. Ví dụ cụ thể về cách sử dụng hàm SUBTOTAL
Dưới đây là một số ứng dụng phổ biến của hàm SUBTOTAL trong thực tế.
4.1. Tính tổng vùng được lọc
Để tính tổng KPI của đội A sau khi lọc dữ liệu, bạn có thể sử dụng công thức sau:
=SUBTOTAL(9,D2:D11)
Trong đó:
- function_num = 9 tương ứng với hàm SUM để tính tổng.
- ref1 = D2:D11 là phạm vi cần tính tổng.
4.2. Đếm các ô được lọc không trống
Để đếm số lượng sinh viên của nhóm C đã có link bài tập sau khi lọc dữ liệu, bạn có thể sử dụng công thức.
=SUBTOTAL(3,D4:D14)
Trong đó:
- function_num = 3 tương ứng với hàm COUNTA để đếm các ô không trống.
- ref1 = D4:D14 là phạm vi cần đếm.
4.3. Đánh số thứ tự nhanh
Để đánh số thứ tự cho một danh sách dữ liệu liên tiếp, bạn có thể sử dụng công thức:
=SUBTOTAL(3,$B$2:B2)
Trong đó:
- function_num = 3 tương ứng với hàm COUNTA để đếm ô không trống.
- ref1 = $B$2:B2 để đếm ký tự phát sinh trong khoảng $B$2:B2 và tự động điều chỉnh khi sao chép công thức.
5. So sánh hàm SUBTOTAL với các hàm SUM
Sự khác nhau giữa hàm SUM và hàm SUBTOTAL được thể hiện cụ thể như sau:
| Tiêu chí | Hàm SUBTOTAL | Hàm SUM |
|---|---|---|
| Mục đích | Có thể tính tổng cố định và di động, tức là tính tổng riêng cho từng nhóm và giá trị thay đổi khi thực hiện lệnh Filter. | Chỉ tính tổng cố định cho tất cả các đối tượng mà không tách thành tổng các nhóm nhỏ. Số liệu không thay đổi khi thực hiện lệnh Filter. |
| Trường hợp sử dụng | Tính tổng số tiền tồn cuối ngày trong sổ tiền mặt, tiền gửi ngân hàng. | Tính tổng phát sinh nợ trong sổ NKC, tính khấu hao trong bảng phân bổ. |
6. Lỗi thường gặp khi sử dụng SUBTOTAL và cách khắc phục
Khi sử dụng hàm SUBTOTAL trong Excel, có thể gặp phải các lỗi sau.
Lỗi #VALUE!
Nguyên nhân thường gây ra lỗi này là do mã chức năng function_num không nằm trong khoảng 1-11 hoặc 101-111, hoặc phạm vi dữ liệu cần tính là tham chiếu dạng 3D. Để khắc phục lỗi này, cần sửa lại mã function_num và kiểm tra lại dữ liệu tham chiếu.
Lỗi #DIV/0!
Lỗi này xuất hiện khi chia một số hoặc tổng cho 0, thường xảy ra khi tính giá trị trung bình hoặc độ lệch chuẩn của dãy ô không chứa số. Để sửa lỗi này, cần kiểm tra lại dữ liệu nhập vào ô tính.
Lỗi #NAME?
Đây là lỗi do nhập sai tên hàm SUBTOTAL. Để tránh lỗi này, cần chính xác nhập tên hàm khi sử dụng. Khi nhập công thức trong Excel, chỉ cần bắt đầu bằng dấu = và chữ cái S, sẽ hiển thị gợi ý để chọn hàm SUBTOTAL đúng.
7. Tổng kết
Bằng cách sử dụng hàm SUBTOTAL trong Excel, bạn có thể dễ dàng tính tổng dãy số bất kỳ, theo điều kiện cụ thể. Hi vọng hướng dẫn lần này của mình sẽ hữu ích đối với bạn trong quá trình sử dụng hàm SUBTOTAL.
Đừng quên liên tục dõi theo kênh Dchannel thuộc hệ thống cửa hàng Di Động Việt để cập nhật hết toàn bộ những gì mới nhất về công nghệ hiện nay nhé. Mình xin gửi lời cám ơn chân thành tới các bạn vì đã bỏ chút thời gian đọc bài chia sẻ này của mình.
Khi mua thiết bị công nghệ tại Di Động Việt, khách hàng sẽ được “CHUYỂN GIAO GIÁ TRỊ VƯỢT TRỘI” hơn cả chính hãng. Chúng tôi luôn tận tâm và trách nhiệm trong việc mang đến các giá trị và lợi ích cao nhất cho mỗi khách hàng. Đồng thời đảm bảo quyền lợi cho người dùng khi sử dụng sản phẩm công nghệ chính hãng với giá hợp lý.
Xem thêm:
- Cách sử dụng hàm UNIQUE trong Excel: Ứng dụng, hướng dẫn và ví dụ chi tiết
- Hướng dẫn cách sử dụng hàm SUMIF trong Excel và ví dụ cụ thể
- Hướng dẫn cách sử dụng thành thạo hàm AVERAGE trong Excel
- Hướng dẫn cách sử dụng hàm LEFT, RIGHT, MID trong Excel cho người mới
Di Động Việt
