Hàm SUBTOTAL - Công cụ tính toán cho nhóm dữ liệu trong Excel

Hàm SUBTOTAL: Được thiết kế để tính toán cho một phần nhỏ trong danh sách hoặc bảng dữ liệu, tùy thuộc vào phép tính kết hợp với tham số đầu tiên.

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

Dưới đây là cách sử dụng cú pháp của hàm SUBTOTAL:

- function_num: Số đặc biệt xác định hàm sẽ được áp dụng trong quá trình tính toán. Quy tắc quy ước như sau: Với Office từ phiên bản 2000 trở lên, quy ước hàm từ số 1 đến 11, và với Office từ 2003, 2007 trở đi, thêm vào đó là các số từ 101 đến 111. Các con số này tương ứng với các hàm sau đây:

+ Hàm AVERAGE: 1 hoặc 101.

+ Hàm COUNT: 2 hoặc 102.

+ Hàm COUNTA: 3 hoặc 103.

+ Hàm MAX: 4 hoặc 104.

+ Hàm MIN: 5 hoặc 105.

+ Hàm PRODUCT: 6 hoặc 106.

+ Hàm STDEV: 7 hoặc 107.

+ Hàm STDEVP: 8 hoặc 108.

+ Hàm SUM: 9 hoặc 109.

+ Hàm VAR: 10 hoặc 110.

+ Hàm VARP: 11 hoặc 111.

- ref1, ref2: Phạm vi dữ liệu cần tham chiếu, có thể bao gồm từ 2 đến 254 phạm vi dữ liệu cần tính.

Lưu ý:

- Trong trường hợp có nhiều hàm SUBTOTAL lồng nhau đặt trong các đối số ref1, ref2 => các hàm SUBTOTAL lồng nhau sẽ bị bỏ qua để tránh tính toán lặp lại.

- Nếu Function_num nằm trong khoảng từ 1 đến 11 => hàm sẽ tính toán bao gồm cả giá trị ẩn trong tập số liệu. Nếu nằm trong khoảng từ 101 đến 111 => hàm sẽ tính toán và bỏ qua giá trị ẩn.

- Hàm sẽ loại bỏ việc tính toán cho tất cả các hàng bị ẩn khi sử dụng Auto Filter, độc lập với tham số Function_num.

- Hàm được lập trình để tính toán theo chiều dọc cho các cột, 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; trong trường hợp tham số 3D như =Sum (Sheet1:Sheet5!A5 => hàm sẽ hiển thị lỗi #VALUE!

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

- Trong trường hợp sử dụng Filter cho vùng dữ liệu, việc sử dụng hàm Subtotal có thể dẫn đến kết quả không chính xác.

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

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

- Tính tổng số tiền đã bán:

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). Kết quả sẽ là:

- 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 kết quả:

- Tính số mặt hàng đã bán 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 kết quả.

Do đó, bạn có thể linh hoạt sử dụng hàm Subtotal trong nhiều phép tính mà không cần nhớ các hàm khác.

Ví dụ 2:

Sử dụng hàm Subtotal như một công cụ tích hợp sẵn trong Excel.

Cho bảng số liệu dưới đây:

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

Bước 1: Chọn toàn bộ bảng dữ liệu (bao gồm cả tiêu đề) -> Mở thẻ Data -> Chọn Outline -> Chọn Subtotal. (Trong trường hợp thanh công cụ không tùy biến, vào thẻ Data -> Chọn Subtotal).

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

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

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

- Tại 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.

Nhấn OK để xem kết quả:

Với mỗi khách hàng, tính tổng số tiền và số mặt hàng đã mua. Đồng thời, tính tổng giá trị và số lượng sản phẩm đã bán ra từ cửa hàng. => Sử dụng tính năng Subtotal và hàm Subtotal là lựa chọn hoàn hảo cho việc quản lý giao dịch mua bán.

Chúc mừng các bạn đạt được những thành công vĩ đại trong tương lai!