Excel - Hàm SUBTOTAL, hàm tính toán cho một nhóm trong danh sách

Hàm SUBTOTAL đã 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, Microsoft đã gia tăng thêm các tùy chọn cho hằng số function của hàm. Bài viết này sẽ mô tả cú pháp, chức năng và cách sử dụng hàm SUBTOTAL.

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.

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_num9 để 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_num1 để 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.


Tác giả: Lê Thị Thuỷ     (3.9★- 24 đánh giá)  ĐG của bạn?

  

Bài viết liên quan

Excel - Hàm Dmax, hàm lấy giá trị nhất lớn nhất trong hàng, cột
Excel - Hàm NORMINV trong Excel, Ví dụ và cách dùng
Excel - Hàm COUNTIFS, Hàm thống kê có điều kiện, ví dụ và cách dùng
Excel - Hàm EXPONDIST, Hàm trả về kết quả phân bố hàm mũ
Excel - Hàm DDB trong Excel, Ví dụ và cách dùng
Từ khoá liên quan:

hàm SUBTOTAL

, sử dụng hàm SUBTOTAL, SUBTOTAL trong excel,

SOFT LIÊN QUAN
  • Handicap Manager for Excel

    Hỗ trợ tính toán trong Excel

    Handicap Manager for Excel là ứng dụng tùy chỉnh văn bản với Visual Basic cho mọi ứng dụng có trong Microsoft Excel dùng trong Windows, nó thích hợp sử dụng với Excel 97/2000/2003 và cao hơn.

Tin Mới

  • Cách tạo gạch chéo trong ô Excel 2016

    Cách chia ô trong Excel 2016 thành 2 ô tam giác giúp phân chia nội dung của các cột được dễ dàng hơn. Tuy nhiên việc tạo gạch chéo trong Excel 2016 không hẳn ai cũng biết. Vì thế trong bài viết dưới đây Taimienphi.vn sẽ hướng dẫn bạn cách tạo gạch chéo trong ô Excel 2016.

  • Cách sửa lỗi hàm Sum không cộng được trong Excel

    Trong quá trình học Excel và làm việc thì gặp lỗi hàm Sum là điều không tránh khỏi. Vậy cùng tham khảo cách sửa lỗi hàm Sum không cộng được trong Excel được Taimienphi.vn chia sẻ dưới đây. Các bạn gặp trường hợp lỗi hàm Sum thế này có thể sửa dễ dàng chỉ với vài bước.

  • Cách tạo khoảng cách giữa chữ và viền trong Excel

    Mẹo tạo khoảng cách giữa chữ và viền trong Excel là thủ thuật giúp người dùng có thể tạo các khoảng trống trong một ô trên Excel, nơi mà các phím cách không có tác dụng. Hướng dẫn dưới đây sẽ cho bạn biết cách thức hoạt

  • Giải toán lớp 6 tập 2 trang 11 tính chất cơ bản của phân số

    Hướng dẫn giải toán lớp 6 tập 2 trang 11 tính chất cơ bản của phân số bài 11, 12, 13, 14 SGK. Tham khảo lời giải Toán 6 trang 11 (gồm phương pháp


 Mùa hè tới rồi muốn so sánh giá tìm điều hòa giá rẻ nhất thị trường hãy dùng TopGia để so sanh gia có nhiều mẫu dieu hoa để so sanh sanh, xem dieu hoagiá rẻ để so sánh giá