Hàm SUBTOTAL – Hàm tính toán cho một nhóm trong danh sách trong Excel

Hàm SUBTOTAL: Là hàm tính toán cho một nhóm con trong một tập sanh sách hoặc bảng dữ liệu tùy thuộc vào phép tính liên quan tới tham số thứ nhất.

Cú pháp: SUBTOTAL(function_num,ref1,ref2,…).

Trong đó:

– function_num: Con số quy định hàm được sử dụng để tính toán. Quy ước như sau: Với Office phiên bản 2000 trở đi quy ước hàm từ số 1 đến 11, với Office 2003, 2007 trở đi thêm con số quy ước từ 101 đến 111. Con số tương ứng với các hàm:

+ Hàm AVERAGE: 1 hay 101.

+ Hàm COUNT: 2 hay 102.

+ Hàm COUNTA: 3 hay 103.

+ Hàm MAX: 4 hay 104.

+ Hàm MIN: 5 hay 105.

+ Hàm PRODUCT: 6 hay 106.

+ Hàm STDEV: 7 hay 107.

+ Hàm STDEVP: 8 hay 108.

+ Hàm SUM: 9 hay 109.

+ Hàm VAR: 10 hay 110.

+ Hàm VARP: 11 hay 111.

– ref1, ref2: Phạm vi vùng dữ liệu muốn tham chiếu, có thể chứa từ 2 tới 254 phạm vi dữ liệu muốn tính.

Chú ý:

– Trường hợp nếu có nhiều hàm SUBTOTAl lồng nhau đặt tại các đối số ref1, ref2 => các hàm SUBTOTAL lồng nhau được bỏ qua để tránh tính toán 2 lần.

– Đối số Function_num nếu nằm trong tập giá trị từ 1 đến 11 => hàm thực hiện tính toán bao gồm cả các giá trị ẩn trong tập số liệu, nếu nằm trong tập giá trị từ 101 đến 111 => hàm thực hiện tính toán bỏ qua giá trị ẩn.

– Hàm sẽ bỏ qua không tính toán tất cả các hàng bị ẩn bởi có sử dụng lệnh Auto Filter mà hàm này không phụ thuộc vào tham số Function_num.

– Hàm được thiết lập để tính toán cho các cột theo chiều dọc, nó không thực hiện tính toán theo chiều ngang.

– Hàm chỉ thực hiện tính toán cho các tham số 2D, trường hợp tham số 3 D ví dụ =Sum (Sheet1:Sheet5!A5 => hàm báo lỗi #VALUE!

– Với Excel 2000/XP đối số Function chỉ nhận các giá trị từ 1 đến 11.

– Trường hợp nếu vùng dữ liệu đã sử dụng Filter thì khi sử dụng hàm Subtotal kết quả không đúng.

Ví dụ 1: Cho bảng số liệu.

Tính tổng tiền đã bán được, số tiền trung bình trên mỗi mặt hàng, tính số mặt hàng đã bán được dựa vào số khách hàng.

– Tính tổng số tiền đã bán được:

Hàm tính tổng là hàm Sum tương ứng với Function = 9 trong hàm Subtotal => nhập công thức tại ô cần tính như sau: =SUBTOTAL(9, D13: D17) => nhận được kết quả:

– Tính số tiền trung bình trên mỗi mặt hàng.

Hàm Average tương ứng với function_num =1 trong hàm subtotal => nhập công thức SUBTOTAL(1, D13: D17) => nhận được kết quả:

– Tính số mặt hàng đã bán được dựa trên số người mua hàng.

Hàm đếm Count tương ứng với Function_num = 2 trong hàm Subtotal => nhập công thức =SUBTOTAL(2, D13: D17) => nhận được kết quả.

Như vậy bạn có thể sử dụng hàm Subtotal linh hoạt trong nhiều phép tính mà không cần ghi nhớ những hàm khác.

Ví dụ 2:

Sử dụng hàm Subtotal là công cụ có sẵn tích hợp trên excel.

Cho bảng số liệu sau:

Tính tổng số tiền, số mặt hàng đã mua của mỗi khách hàng và tổng tiền và tổng số mặt hàng đã bán được của cửa hàng.

Bước 1: Lựa chọn toàn bộ bảng dữ liệu (gồm cả tiêu đề) -> Vào thẻ Data -> Outline -> Subtotal. (Trường hợp không tùy biến thanh công cụ bạn vào thẻ Data -> Subtotal).

Bước 2: Hộp thoại Subtotal xuất hiện:

– Mục At each change in: chọn tiêu đề cột Tên KH vì ở đây tính theo tổng theo tên khách hàng.

– Mục Use function: Chọn Sum vì ở đây thực hiện tính tổng nên sử dụng hàm Sum.

– Mục Add subtotal to: tích chọn Số lượng và Số tiền vì ở đây thực hiện tính tổng theo số lượng và số tiền.

Kích chọn OK kết quả:

Như vậy mỗi khách hàng được tính tổng tiền và tổng số mặt hàng đã mua. Ngoài ra tính tổng tiền và tổng số mặt hàng đã bán được của cửa hàng.
=> Sử dụng thêm tính năng Sutotal và hàm Subtotal rất phù hợp trong việc quản lý mua bán hàng.

Chúc các bạn thành công!