SUBTOTAL trong Excel – Khái niệm và cách dùng chi tiết

IIE » Microsoft Office » SUBTOTAL trong Excel – Khái niệm và cách dùng chi tiết

SUBTOTAL trong Excel là gì, chức năng, cú pháp và cách sử dụng, ví dụ dùng SUBTOTAL trong Excel, hàm SUBTOTAL không ra kết quả – những lỗi thường gặp.

Content

SUBTOTAL trong Excel là gì?

SUBTOTAL trong Excel là một hàm được thiết kế sẵn và chắc chắn là được sử dụng khá thường xuyên nếu công việc của bạn liên quan nhiều đến xử lý các số liệu. Chính vì vậy có thể nói rằng các chức năng của hàm SUBTOTAL trong Excel giúp ích rất nhiều cho công việc với bảng tính của bạn.

Chức năng của hàm SUBTOTAL trong Excel

Hàm SUBTOTAL được thiết kế trong Excel nhằm mục đích tính tổng phụ của một danh sách hoặc một cơ sở dữ liệu, để đếm ô, tìm giá trị nhỏ nhất/lớn nhất, tính giá trị trung bình, hoặc tỉnh tổng giá trị trong danh sách đã được lọc. Chính xác thì SUBTOTAL trong Excel có khá nhiều chức năng tiềm ẩn mà nếu biết cách tận dụng chúng, chắc chắn bạn sẽ hoàn thành các công việc hàng ngày của mình một cách dễ dàng hơn nhiều đấy.

Cú pháp và cách sử dụng hàm SUBTOTAL trong Excel

Cú pháp của hàm SUBTOTAL trong Excel

Cú pháp của hàm SUBTOTAL Excel như sau:

=SUBTOTAL(function_num;ref1,[ref2],…)

Trong đó:

Function_num

(bao gồm giá trị ẩn)

Function_num

(bỏ qua giá trị ẩn)

Hàm
1101AVERAGE
2102COUNT
3103COUNTA
4104MAX
5105MIN
6106PRODUCT
7107STDEV
8108STDEVP
9109SUM
10110VAR
11111VARP

Sử dụng hàm SUBTOTAL trong Excel để tính tổng các hàng được lọc

Khi cần tính tổng những hàng đã được lọc bởi một số yếu tố, bạn bắt buộc phải sử dụng hàm SUBTOTAL trong Excel. Công thức cụ thể như sau:

= SUBTOTAL(9;phạm vi)

Trong đó, 9 chính là số thứ tự tương ứng với hàm SUM khi chúng ta ứng dụng hàm SUBTOTAL trong Excel.

Sau khi nhấn phím Enter, chúng ta nhận được kết quả như sau:

Sử dụng hàm SUBTOTAL trong Excel để đếm các ô được lọc không trống

Trường hợp chỉ muốn đếm những ô tính không trống, chúng ta sẽ dùng hàm COUNTA (hàm đếm những ô không trống) tương ứng với số 3 với công thức như sau:

= SUBTOTAL(3,phạm vi)

Như trong ví dụ dưới đây, chúng ta sử dụng công thức này để đếm các ô không trống của cột Số ngày công.

Sau khi nhấn phím Enter, chúng ta nhận được kết quả như sau:

Bỏ qua các giá trị trong các công thức SUBTOTAL lồng nhau

Khi bạn cần tính nhiều công thức trung bình cộng khác nhau trên cùng một sheet của bảng tính. Như vậy nếu bạn đã tính trung bình cộng cho các thành phần nhỏ hơn, Excel sẽ tự động bỏ qua các dòng đó và chỉ tính đối với các thành phần. 

Để giúp bạn dễ hình dung hơn về chức năng này của SUBTOTAL trong Excel, chúng ta cùng xét ví dụ dưới đây:

Trước hết, chúng ta cần tính ra số lượng đậu xanh trung bình ở cả loại I và loại II theo công thức như sau:

= SUBTOTAL(1;B2:B3)

Sau đó, tại dòng Trung bình số lượng đậu, chúng ta cần tính trung bình của toàn bộ những loại đậu với công thức như sau:

= SUBTOTAL(1;B2:B7)

Như vậy, kết quả cuối cùng nhận được ở dòng Trung bình số lượng đậu là 6, tức là bằng kết quả chia trung bình của tổng số kg đậu (5+4+10+4+7) chia cho số lượng loại đậu (tức là 5). Vì chúng ta sử dụng SUBTOTAL trong Excel, do đó dòng Trung bình số lượng đậu đã được bỏ qua dù trong phần phạm vi chúng ta đã chọn cả dòng này.

Ví dụ sử dụng SUBTOTAL trong Excel

Để hình dung rõ hơn về cách dùng SUBTOTAL trong Excel, chúng ta cùng ứng dụng hàm này để giải quyết một số yêu cầu công việc phổ biến như sau:

Trước hết hãy thử tính tổng số lượng đậu:

Tính trung bình số lượng đậu:

Tìm số lượng đậu cao nhất (MAX):

Tìm số lượng đậu thấp nhất (MIN):

Kết quả cuối cùng nhận được là:

Trong quá trình làm việc, để có thể ứng dụng SUBTOTAL trong Excel một cách thuận tiện nhất, hãy chỉ nên tập trung nhớ số thứ tự tương ứng với những hàm mà bạn cần dùng nhiều nhất. Ví dụ nếu cần thường xuyên tính tổng, hãy nhớ hàm SUM tương ứng với số 9; để tính trung bình, chúng ta có hàm AVERAGE tương ứng với số 1;… Còn nếu bạn không thể nhớ được chúng thì sao? Đừng lo lắng vì thực tế khi bạn thực hiện thao tác gõ hàm SUBTOTAL trong Excel, hệ thống cũng sẽ tự động hiện ra bảng gợi ý số thứ tự tương đương với các hàm để bạn dễ dàng lựa chọn.

Hàm SUBTOTAL không ra kết quả – Một số lỗi thường gặp

Lỗi #Value trong hàm SUBTOTAL

Thỉnh thoảng khi sử dụng SUBTOTAL trong Excel để xử lý số liệu, chúng ta lại gặp phải lỗi #Value. Nguyên nhân chủ yếu của tình trạng này là:

Để khắc phục lỗi này, bạn đơn giản chỉ cần xác định nguyên nhân và điều chỉnh lại thông tin mình đã nhập vào công thức là được.

Lỗi #DIV/0! trong hàm SUBTOTAL

Lỗi này thường xuất hiện trong trường hợp bạn sử dụng hàm SUBTOTAL trong Excel để thực hiện phép chia một số hoặc một tổng cho 0, tức là khi bạn cần tính trung bình cộng hoặc tính độ lệch chuẩn của một dãy ô đang không chứa giá trị số.

Để khắc phục lỗi này, hãy kiểm tra lại các thông tin bạn đã nhập vào ô tính để biết mình đang nhầm lẫn ở đâu và điều chỉnh lại là được.

Lỗi #NAME? trong hàm SUBTOTAL

Lỗi này thường chắc chắn xuất phát từ nguyên nhân nhập vào sai tên hàm. Để khắc phục, hãy kiểm tra lại xem có chính xác bạn đã nhập vào hàm SUBTOTAL trong Excel với đầy đủ các ký tự hay chưa. Người dùng Excel lâu năm thường có một mẹo để tránh được lỗi này, đó là chỉ cần nhập vào ô tính một dấu “=” và một số ký tự đầu của hàm, sau đó Excel sẽ tự động hiện ra những hàm gợi ý để bạn lựa chọn. Điều này giúp hạn chế đến mức tối đa số lần bạn nhập vào sai tên hàm.

Vậy là trong bài viết trên, chúng mình đã giới thiệu đến bạn các thông tin quan trọng cần biết về SUBTOTAL trong Excel. Hy vọng rằng những kiến thức này hữu ích với bạn. Cuối cùng, nếu thích bài viết, đừng quên bấm theo dõi trang ngay để liên tục cập nhật thêm nhiều thủ thuật MS Office thú vị khác bạn nhé!

Microsoft Office -