Bạn đang không biết tính tổng vùng chọn lọc trong Excel như thế nào? Bạn cần đánh số thứ tự và muốn đếm các ô được lọc không trống một cách nhanh chóng? Hãy sử dụng ngay hàm Subtotal trong Excel để giải quyết mọi vấn đề trên. Khám phá ngay bài viết dưới đây để biết chi tiết hơn về cách sử dụng nhé cũng như những kiến thức học Excel online hay được các chuyên gia chia sẻ!
Xem thêm: Cách dùng hàm Sumifs trong Excel tính tổng nhiều điều kiện
Xem thêm: Tổng hợp 6 cách sử dụng hàm trừ trong Excel bạn nên biết
Mục lục bài viết
Hàm SUBTOTAL là gì?
Hàm Subtotal trong Excel là một hàm được sử dụng rất nhiều trong Excel. Hàm có thể được sử dụng để:
- Tính tổng các số liệu
- Đếm các ô được lọc không trống
- Tìm giá trị lớn nhất nhỏ nhất trong một vùng dữ liệu
- Tính trung bình cộng
- Đánh số thứ tự tự động
Cú pháp hàm SUBTOTAL trong Excel
=SUBTOTAL(function_num, ref1, ref2,…)
Trong đó:
- Function_num: Mã số. Nếu 1 < Function_num < 11 thì hàm Subtotal sẽ thực hiện phép tính gồm các giá trị bị ẩn trong vùng dữ liệu chứa giá trị bị ẩn. Còn nếu 101 < Function_num < 111 thì hàm sẽ bo qua ô đó và không thực hiện phép tính các giá trị ở hàng ẩn
- Ref1, Ref2,…: 1 ô hay nhiều ô giá trị hoặc phạm vi vùng ô để tính tổng phụ, tối đa 254 ô giá trị
Mã số của Function_num cụ thể như sau:
| Function_num
(Giá trị ẩn) |
Function_num
(Không có giá trị ẩn) |
Tương đương hàm |
| 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 chỉ sử dụng cho các cột chứa số liệu theo chiều dọc
- Đối với các trường hợp ref1, ref2,… chứa các hàm Subtotal sẽ được bỏ qua để tránh trùng 2 lần
- Với bất kỳ dữ liệu nào định định dạng dưới dạng 3-D thì khi sử dụng hàm Subtotal sẽ bị báo lỗi #VALUE
- Trong hàm Subtotal thì hằng số function_num từ 1 đến 11 thì hàm sẽ tính các giá trị bị ẩn trong vùng dữ liệu được chỉ định trước đó
- Trong hàm Subtotal thì hằng số function_num từ 101 đến 111 thì hàm Subtotal chỉ tính toán các giá trị không ẩn trong vùng dữ liệu
- Hàm Subtotal bỏ qua các phần dữ liệu bị ẩn bởi bộ lọc Filter vì đó là những dữ liệu không thoả mãn điều kiện
- Người sử dụng cần phân biệt các giá trị bị ẩn và các giá trị bị loại cho không thoả mãn bộ lọc được sử dụng
+ Đối với giá trị bị ẩn là các giá trị mà bạn đã tẩn đi trong một số thao tác chọn hàng của mình. Lúc này bạn cần nhấn chuột phải và chọn Hide trong thanh cuộn menu
+ Đối với các giá trị bị loại bọ vì không đáp ứng điều kiện của bộ lọc là các giá trị không đúng với yêu cần tìm kiếm của bạn và được lọc bỏ.
Cách sử dụng hàm SUBTOTAL trong Excel hiệu quả nhất
Tính tổng các hàm được lọc
Ví dụ minh hoạ: Thực hiện tính tổng các số liệu được lọc dựa vào bảng dữ liệu sau khi lọc ra KPI của đội A
Bước 1: Thực hiện bôi đen toàn bộ vùng dữ liệu cần thực hiện phép tính. Tiếp tục nhấn chọn Home trên thanh công cụ => Sort and Filter => Filter
Bước 2: Tiếp tục lựa chọn dấu mũi tên đảo ngược bên phải ở ô Đội trong hàng tiêu đề. Lọc lấy dữ liệu đội A bằng các tích chọn ô A => OK
Bước 3: Các KPI của đổi A sẽ được Filter lọc ra, lúc này bạn cần lấy kết quả ở ô cần nhập bằng công thức:
=SUBTOTAL(9,D2:D11)
Trong đó:
- Unction_num = 9: Giá trị đối số tương ứng với hàm cần sử dụng (Dựa vào bảng Mã số của Function_num trong phần 1) – Hàm SUM
- Ref1 = D2:D11 là phạm vi vùng dữ liệu cần tính tổng
Kết quả hiển thị như sau:
Lưu ý: Với ví dụ minh hoạ trên bạn có thể dùng giá trị đối số là 9 để thực hiện tính tổng các giá trị đã bị ẩn đi hoặc sử dụng 109 để bỏ qua các giá trị đã bị ẩn để tính tổng toàn bộ các giá trị đã được Filter lọc
Đếm các ô được lọc không trống
Ví dụ minh hoạ: Sử dụng hàm để đếm số lượng sinh viên của nhóm C có link bải tập để thống kê dựa theo bảng số liệu dưới đây:
Bước 1: Thực hiện bôi đen toàn bộ vùng dữ liệu cần thực hiện phép tinh. Tiếp tục nhấn chọn Home trên thanh công cụ => Sort and Filter => Filter
Bước 2: Bộ lọc đã được bật, bạn thao tác lọc toàn bộ các số liệu của các bạn sinh viên thuộc nhóm C
Bước 3: Các bạn học viên nhóm C sẽ được Filter lọc ra, lúc này bạn cần lấy kết quả ở ô cần nhập bằng công thức:
=SUBTOTAL(3,D4:D14)
Trong đó:
- function_num = 3: Giá trị đối số tương ứng với hàm cần sử dụng (Dựa vào bảng Mã số của Function_num trong phần 1) – Hàm COUNTA
- ref1 = D4:D14 là phạm vi vùng số liệu cần tính tổng
Kết quả hiển thị như sau:
Lưu ý: Với ví dụ minh hoạ trên bạn có thể dùng giá trị đối số là 3 để thực hiện tính tổng các giá trị đã bị ẩn đi hoặc sử dụng 103 để bỏ qua các giá trị đã bị ẩn để tính tổng toàn bộ các giá trị đã được Filter lọc
Đánh số thứ tự nhanh
Để thực hiện đánh số thứ tự cho một danh sách dữ liệu như bên dưới đây (Danh sách dữ liệu liên tiếp không chứa các hàng trống) theo công thức sau:
=SUBTOTAL(3,$B$2:B2)
Trong đó:
- function_num là 3: Giá trị đối số tương ứng với hàm cần sử dụng (Dựa vào bảng Mã số của Function_num trong phần 1) – Hàm COUNTA
- ref1 là $B$2:B2: Thực hiện đếm ký tự phát sinh trong khoảng B2 được cố định bằng dấu “$” để cố định giá trị không bị thay đổi và hàm sẽ đếm đúng, mang về kết quả như mong muốn.
Sau đó bạn thực hiện áp dụng toàn bộ công thức cho cột STT bằng thao tác kéo thả đến ô cuối cùng. Kết quả hiển thị như sau:
Tuy nhiên đối với các trường hợp dữ liệu lớn hoặc “khó nhằn” ví có chứa hàm trống hoặc các dữ liệu không trống lộn xộn thì bạn nên sử dụng hàm Subtotal kết hợp hàm If. Toàn bộ các giá trị được lựa chọn sẽ được hàm đánh dấu lại từ đầu. Cách dùng hàm Subtotal để đánh dấu này thực hiện khi lọc dữ liệu và chỉ áp dụng khi được đánh số thứ tự theo cột
Công thức tổng quát
=IF(logical_test, value_if_true, SUBTOTAL(funtion_num, ref1,…))
Trong đó:
- logical_test: Điều kiện cho hàm IF.
- value_if_true: Điều kiện đúng => Giá trị trả về
- value_if_false: Điều kiện sai => Giá trị trả về (Sử dụng hàm SUBTOTAL)
Ví dụ minh hoạ
Nhập công thức sau:
=IF(B2=””,””,SUBTOTAL(3,$B$2:B2))
Trong đó:
- B2: Ô chứa giá trị bắt đầu đánh số thứ tự
- function_num là 3: Giá trị đối số tương ứng với hàm cần sử dụng (Dựa vào bảng Mã số của Function_num trong phần 1) – Hàm COUNTA
- ref1 là $B$2:B2: Thực hiện đếm ký tự phát sinh trong khoảng B2 được cố định bằng dấu “$” để cố định giá trị không bị thay đổi và hàm sẽ đếm đúng, mang về kết quả như mong muốn.
Sau khi sử dụng hàm hiển thị kết quả thì bạn thực hiện kéo thả toàn bộ công thức cho hàng STT để thực hiện đánh số thứ tự cho toàn bộ bảng dữ liệu
Một số lỗi thường gặp khi thực hiện hàm SUBTOTAL trong Excel
Nguyên nhân gây ra lỗi
Có một số những nguyên nhân sau đây gây ra các lỗi thường gặp khi thực hiện sử dụng hàm Subtotal trong Excel:
- Trường hợp lỗi 1: Số liệu xác định chức năng function_num không nằm trong khoảng giá trị 1 đến 11 và 101 đến 111
- Trường hợp lỗi 2: Số liệu tham số ref tham chiếu đến các giá trị trong một ô trang tính nằm trong Sheet khác.
Ví dụ: Bạn thực hiện sử dụng hàm Subtotal ở Sheet STT2 tham chiếu đến một ô hoặc một dãy giá trị ô nào đó ở Sheet STT1 bằng cách dùng hàm Subtotal
Cách khắc phục lỗi
- Trường hợp lỗi 1: Đối với trường hợp này nguyên nhân là do sai số xác định chức năng. Khiến hàm báo lỗi. Bạn cần thực hiện lại kiểm tra toàn bộ số liệu đã dùng đúng số chức năng chưa. Hoặc cũng có thể do trong quá trình nhập có dư ký tự không thể điều chỉnh lại cho đúng
- Trường hợp lỗi 2: Đối với trường hợp lỗi này là do tham chiếu. Vì thế bạn chỉ cần xoá phần tham chiếu sai trong hàm đi. Sau đó thay thế lại bằng toàn bộ dãy ô đúng được đặt trong trang tính mà bạn muốn thực hiện với hàm Subtotal
Một số lỗi cơ bản
- #VALUE!: Tham số xác định chức năng hàm không nằm trong khoảng 1-11 hoặc 101-111 (Như trong bảng chi tiết phần 1) hay có tham chiếu (ref) là tham chiếu 3D thì hàm sẽ báo lỗi
- #DIV/0!: Trường hợp này xảy ra khi 1 tổng cụ thể phải chia cho số 0. Nếu bạn muốn thực hiện các phép tính trung bình cộng, phép chia hoặc phép tính có độ lệch chuẩn của 1 dãy ô không chứa giá trị số (ô trống) khiến hàm báo lỗi
- #NAME?: Nếu bạn thấy hàm báo lỗi hiển thị này thì nghĩa là bạn đã viết sai chính tả trong hàm. Cần kiểm tra lại công thức, ký tự hoặc hàm có chứa khoảng cách không
Tổng kết
Như vậy chỉ với cách sử dụng hàm Subtotal trong Excel mà hoàn toàn có thể sử dụng để tính tổng, đánh số thứ tự, đếm các ô được lọc không trống, tìm giá trị lớn nhất nhỏ nhất,… Với ví dụ minh hoạ chi tiết kèm hướng dẫn từng bước giúp bạn dễ dàng nắm bắt cách sử dụng và thao tác theo dễ dàng. Chúc bạn thành công với!
