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.

Bài viết liên quan


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

http://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★- 8 đánh giá)  ĐG của bạn?

  

Bài viết liên quan

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
Excel - Hàm STDEV, Hàm ước tính độ lệch chuẩn dựa trên mẫu
Hàm CUBEMEMBERPROPERTY trong Excel, trả về giá trị của thuộc tính phần tử trong khối
Excel - Hàm ABS, Hàm tính giá trị tuyệt đối, 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

  • Hàm LOWER trong Excel, cú pháp và cách sử dụng

    Hàm LOWER trong Excel sẽ chuyển đổi tất cả các ký tự trong chuỗi văn bản được cung cấp sang ký tự thường. Trong bài viết dưới đây Taimienphi.vn sẽ xoay quanh chủ đề về hàm LOWER trong Excel cũng như chức năng và một số ví dụ về hàm LOWER.

  • Cách chỉnh các dòng đều nhau trong Excel

    Bạn đã biết cách chỉnh các dòng đều nhau trong Excel khi tạo 1 bảng tính hay chưa? Khi nhìn vào file Excel thì bố cục, cách trình bày sẽ là điểm đánh giá đầu tiên, sau đó mới tới nội dung, vì thế, bạn hãy tự tích lũy

  • Cách viết độ C trong Excel

    Cũng giống như Word việc viết độ C trong Excel khá đơn giản giúp người dùng có thể minh họa hay trích dẫn các giá trị dễ dàng hơn. Trong bài viết sau đây Taimienphi.vn sẽ hướng dẫn các bạn cách đơn giản để viết độ C trong Excel 2003, 2007, 2010, 2013, 2016.

  • Giải toán lớp 3 Bài 1, 2, 3 trang 51 SGK- Bài toán giải bằng hai phép tính ( tiếp theo)

    Những bài toán giải bằng hai phép tính bài trước chúng ta đã được tìm hiểu các bạn học sinh lớp 3 còn có điều gì thắc mắc không? Bài ngày hôm nay chúng ta vẫn tiếp tục với nội dung bài học hướng dẫn giải bài Bài toán giải bằng hai phép tính ( tiếp theo), ở phần này các dạng bài khó hơn, chính vì thế các bạn hãy cùng tham khảo tài liệu giải toán lớp 3 để ứng dụng cho quá trình học tập tốt hơn nhé.


 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á