Hàm SUBTOTAL - Hàm trả về tổng phụ trong 1 danh sách hoặc 1 cơ sở dữ liệu trong Excel

Bài viết dưới đây giới thiệu tới các bạn hàm SUBTOTAL – 1 trong số những hàm nằm trong nhóm hàm lượng giác và toán học rất được ưa dùng trong Excel.

Hàm Subtotal

Mô tả: Hàm trả về tổng phụ trong một danh sách hoặc 1 cơ sở dữ liệu.

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

- 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.

Trong đó: ref1 là bắt buộc, các tham số khác tùy ý.

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_num 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ụ:

Thực hiện các yêu cầu ghi trong bảng sau:

Hàm SUBTOTAL trong Excel

1. Tính tổng tiền đã bán được của những mặt hàng sau:

- Tại ô cần tính nhập công thức: =SUBTOTAL(109,F6:F9).

Hàm SUBTOTAL trong Excel 2

- Nhấn Enter -> giá trị trả về là:

Hàm SUBTOTAL trong Excel 3

2. Tính trung bình đơn giá các mặt hàng đã bán.

Tương tự như trên chỉ khác thay hàm sử dụng thành hàm AVERAGE – tương ứng với 101 (do đang dùng phiên bản Excel 2013) -> nhập công thức: =SUBTOTAL(101,F6:F9).

Hàm SUBTOTAL trong Excel 4

3. Tính tổng số mặt hàng đã bán được trong ngày.

Sử dụng hàm Count – đếm tổng số mặt hàng đã bán: =SUBTOTAL(2,F6:F9).

Hàm SUBTOTAL trong Excel 5

4. Tìm mặt hàng có đơn giá lớn nhất trong các mặt hàng.

- Sử dụng hàm Max để tìm mặt hàng có đơn giá lớn nhất: =SUBTOTAL(4,F6:F9).

Hàm SUBTOTAL trong Excel 6

Trên đây là hướng dẫn và một số ví dụ cụ thể khi sử dụng hàm SUBTOTAL.

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

Viết bình luận