Hàm SUBTOTAL là hàm tính toán cho một nhóm trong danh sách đã xuất hiện từ các phiên bản Excel trước nhưng kể từ phiên bản 2003 trở đi. Bài viết này, Taimienphi.vn sẽ mô tả cú pháp, chức năng và cách sử dụng hàm SUBTOTAL, mời các bạn tham khảo.
Trong bài viết trước đây Taimienphi.vn có giới thiệu đến độc giả các hàm đếm, trong đó đếm bằng hàm Count, hoặc đếm có điều kiệm bằng hàm CountIF, hôm nay chúng tôi tiếp tục giới thiệu đến 1 hàm khác, đó là hàm SubTotal. Hàm SUBTOTAL được sử dụng rất linh hoạt trong Excel và khó sử dụng. Sau đây hãy cùng Taimienphi.vn tìm hiểu về cách sử dụng qua các ví dụ cụ thể, các bạn cùng tham khảo.
Hàm SUBTOTAL, cú pháp và ví dụ
Excel - Hàm SUBTOTAL, hàm tính toán cho một nhóm con trong danh sách
Cú pháp:
=SUBTOTAL (function_num, ref1, [ref2], …)
Trong đó:
Function_num: Bắt buộc. Là một số từ 1 đến 11 hoặc 101 đến 111 để xác định dùng hàm nào trong tính toán tổng phụ trong danh sách.
Ref1: Bắt buộc. Phạm vi vùng tham chiếu được đặt tên đầu tiên mà bạn muốn tính tổng phụ cho nó.
Ref2…: tùy chọn. Có thể từ 2 đến 254 phạm vi mà bạn muốn tính tổng phụ cho nó.
Ghi chú:
- Nếu có hàm subtotal khác lồng đặt tại các đối số ref1, ref2,… thì các hàm lồng này sẽ bị bỏ qua không được tính toán nhằm tránh trường hợp tính toán 2 lần.
- Đối với các hằng số function_num từ 1 đến 11, hàm SUBTOTAL tính toán cả các giá trị ẩn trong vùng dữ liệu (hàng ẩn). Đối với các hằng số từ 101 đến 111 chỉ tính toán cho các giá trị không ẩn trong vùng dữ liệu.
- Hàm SUBTOTAL sẽ bỏ qua bất kỳ hàng nào bị ẩn bởi lệnh Filter (Auto Filter).
- Hàm SUBTOTAL được dùng cho các cột dữ liệu. Nó không được dùng cho các hàng dữ liệu.
- Bất kỳ dữ liệu nào dưới dạng 3-D thì hàm SUBTOTAL sẽ báo lỗi #VALUE!
Chức năng: Tính toán một danh sách hoặc cơ sở dữ liệu.
Ví dụ:
- Ta có bảng dữ liệu sau:
- Sử dụng hàm Subtotal để tính tổng và trung bình công của cột dữ liệu C4:C6.
- Sử dụng hàm SUBTOTAL cho ô giá trị C7 với thông số Function_num là 9 để tính tổng. Công thức sẽ là =SUBTOTAL (9, C4:C6).
- Sử dụng hàm SUBTOTAL cho ô giá trị C8 với thông số Function_num là 1 để tính trung bình cộng.
- Công thức sẽ là =SUBTOTAL (1, C4:C6)
Ngoài ra, thay vì sử dụng hàm SUBTOTAL để tính toán, các bạn có thể sử dụng công cụ Subtotal được tích hợp sẵn trong Excel để tính toán. Mình sẽ tiếp tục hướng dẫn các bạn cách sử dụng công cụ Subtotal để tính toán.
Cho bảng tính như sau:
Yêu cầu đặt ra là các bạn tính tổng số lượng các mặt hàng và tổng tiền của mỗi khách hàng với bảng dữ liệu ở trên.
Vì yêu cầu tính theo Tên khách hàng nên đầu tiên cần phải sắp xếp cột Tên khách hàng để những tên trùng nhau đứng gần lại với nhau. Bôi đen cột Tên khách hàng → Data → Sort.
Tick chọn Continue with the current selection ở hộp thoại hiện ra → Sort …
Các bạn cho sắp xếp theo từ A đến Z để các tên trùng nhau đứng lại gần với nhau → OK.
Ta được kết quả bảng dữ liệu sau khi sắp xếp Tên khách hàng.
Tiêp theo ta sẽ đi tính tổng Số lượng mặt hàng và Tổng tiền của mỗi khách hàng bằng cách sử dụng công cụ Subtotal. Bôi đen vùng chứa dữ liệu → Data → Subtotal.
Ở hộp thoại Subtotal.
- Khung At each change in bạn chọn là Tên khách hàng vì ta sẽ tính theo tên Khách hàng.
- Khung Use function là hàm tính toán bạn sẽ dùng để tính. Ở đây ta tính tổng nên sẽ chọn hàm Sum.
- Khung Add subtotal to: bạn tick chọn Số lượng và Thành tiền vì yêu cầu bài toán tính tổng Số lượng các mặt hàng mỗi khách hàng và tổng số tiền của mỗi khách hàng.
- OK.
Ta có kết quả thu được:
Bạn có thể áp dụng cách này để tính toán với các yêu cầu khác nhau, và tương tự như cách sử dụng hàm SUBTOTAL, công cụ Subtotal này hỗ trợ cho bạn 11 hàm tính toán khác nhau ở trên bảng hàm trong Subtotal mình đã nêu ở trên. Sau khi tính toán, nếu như bạn muốn bảng dữ liệu của mình hiển thị về như cũ thì các bạn mở hộp thoại Subtotal lên và chọn Remove All.
Hàm SUBTOTAL là một hàm rất linh hoạt những cũng khá khó sử dụng trong Excel với việc sử dụng các hằng số function để triệu tập lệnh. Nhưng ưu điểm của nó là bạn cũng có thể sửa lại phép tính một cách dễ dàng hơn bằng cách thay lại hằng số function cho nó. Và cùng với công cụ hỗ trợ tính toán Subtotal, các bạn có thể dễ dàng tính toán với các yêu cầu phức tạp hơn so với việc sử dụng các phép tính toán thông thường mà hàm Subtotal hỗ trợ.
https://thuthuat.taimienphi.vn/ham-subtotal-trong-excel-3296n.aspx
SUBTOTAL cũng là 1 trong những hàm tính toán thông dụng trong Excel, nó cũng là hàm khá phổ biến trong kế toán, bên cạnh đó, việc kết hợp với các hàm như hàm Vlookup, hàm Hlookup giúp cho hàm SubTotal trở lên link hoạt hơn.