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.
Bạn đang đọc: 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
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:
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).
– Nhấn Enter -> giá trị trả về là:
Tìm hiểu thêm: Hàm FINV – Hàm trả về giá trị nghịch đảo của phân bố xác suất F (mức đa dạng) bên phải cho hai tập dữ liệu trong Excel
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).
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).
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).
>>>>>Xem thêm: Hàm MOD – Hàm trả về số dư của phép chia trong Excel
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!
