Hàm SUBTOTAL: Là công cụ tính toán mạnh mẽ trong Excel, giúp xử lý các nhóm dữ liệu trong danh sách một cách linh hoạt.
Hàm SUBTOTAL cho phép tính toán các nhóm con trong bảng hoặc danh sách dữ liệu, tùy thuộc vào loại phép tính được chọn qua tham số đầu tiên.
Cú pháp sử dụng hàm SUBTOTAL: SUBTOTAL(function_num, ref1, ref2,...).
Trong đó: function_num là mã số của phép tính, còn ref1, ref2,... là các tham chiếu đến các vùng dữ liệu cần tính toán.
- function_num: Đây là con số chỉ định hàm tính toán cần sử dụng trong Excel. Cụ thể: đối với phiên bản Office từ 2000 trở đi, các hàm được quy định từ số 1 đến 11, và với các phiên bản Office 2003, 2007 trở lên, số từ 101 đến 111 được bổ sung cho các hàm mở rộng.
+ 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: Đây là các phạm vi dữ liệu mà bạn muốn tham chiếu, có thể bao gồm từ 2 đến 254 phạm vi dữ liệu cần tính toán.
Chú ý: Lưu ý rằng nếu có nhiều hàm SUBTOTAL được lồng nhau 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 trùng lặp.
- Nếu các hàm SUBTOTAL được lồng vào nhau trong các đối số ref1, ref2, thì các hàm này sẽ không được tính hai lần.
- Đối số Function_num, nếu trong khoảng từ 1 đến 11, sẽ tính toán bao gồm cả các giá trị ẩn, trong khi nếu từ 101 đến 111, sẽ bỏ qua các giá trị ẩn trong quá trình tính toán.
- Hàm sẽ bỏ qua tất cả các hàng bị ẩn do sử dụng lệnh Auto Filter, và tính toán không bị ảnh hưởng bởi tham số Function_num.
- Hàm được thiết kế để tính toán cho các cột theo chiều dọc, không thực hiện tính toán theo chiều ngang.
- Hàm chỉ áp dụng cho các tham số 2D, và nếu bạn sử dụng tham số 3D, ví dụ =Sum (Sheet1:Sheet5!A5, sẽ gặp lỗi #VALUE!.
- Đối với phiên bản Excel 2000/XP, đối số Function chỉ nhận các giá trị trong khoảng 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 COUNT tương ứng với Function_num = 2 trong hàm SUBTOTAL. Để tính toán, bạn nhập công thức =SUBTOTAL(2, D13: D17) và nhận được kết quả mong muốn.
Với hàm SUBTOTAL, bạn có thể linh hoạt áp dụng cho nhiều phép tính mà không cần nhớ các hàm khác nhau.
Ví dụ 2:
Hàm SUBTOTAL là công cụ tích hợp sẵn trong Excel, giúp bạn thực hiện các phép tính dễ dàng và hiệu quả.
Dưới đây là bảng số liệu cần tính toán:
Tính tổng số tiền, số mặt hàng mỗi khách hàng đã mua, và tổng tiền cùng tổng số mặt hàng cửa hàng đã bán được.
Bước 1: Chọn toàn bộ bảng dữ liệu (bao gồm cả tiêu đề) -> Vào thẻ Data -> Outline -> Subtotal. (Nếu thanh công cụ chưa tùy biến, bạn vào thẻ Data và chọn Subtotal).
Bước 2: Hộp thoại Subtotal sẽ xuất hiện trên màn hình.
- Trong mục At each change in:, chọn tiêu đề cột Tên KH, vì mục tiêu ở đây là tính tổng theo tên khách hàng.
- Trong mục Use function, chọn Sum để thực hiện phép tính tổng, vì hàm Sum là lựa chọn tối ưu cho bài toán này.
- Trong mục Add subtotal to, đánh dấu chọn Số lượng và Số tiền, vì tổng hợp này bao gồm cả số lượng và giá trị tiền tệ.
Cuối cùng, nhấn OK để hoàn tất và xem kết quả tính toán.
Với phương pháp này, tổng tiền và tổng số mặt hàng đã mua của mỗi khách hàng sẽ được tính toán một cách chính xác. Đồng thời, cửa hàng cũng có thể dễ dàng tổng hợp số tiền và số mặt hàng đã bán nhờ vào tính năng Subtotal và hàm Subtotal, hỗ trợ đắc lực trong công tác quản lý mua bán.
Chúc các bạn đạt được nhiều thành công và tiến xa trong công việc của mình!
