Tóm lược
Hàm SUBTOTAL trong Excel trả về kết quả tổng hợp cho các giá trị được cung cấp. SUBTOTAL có thể trả về SUM, AVERAGE, COUNT, MAX và các giá trị khác (xem bảng bên dưới) và hàm SUBTOTAL có thể bao gồm hoặc loại trừ các giá trị trong các hàng ẩn.
Mục đích
Nhận tổng phụ trong danh sách hoặc cơ sở dữ liệuGiá trị trả lại
Một số đại diện cho một loại tổng phụ cụ thểCú pháp
= SUBTOTAL (function_num, ref1, (ref2),…)Tranh luận
- function_num - Một số chỉ định hàm nào được sử dụng để tính tổng phụ trong một danh sách. Xem bảng dưới đây để biết danh sách đầy đủ.
- ref1 - Một phạm vi được đặt tên hoặc tham chiếu đến tổng phụ.
- ref2 - (tùy chọn) Một phạm vi được đặt tên hoặc tham chiếu đến tổng phụ.
Phiên bản
Excel 2003Ghi chú sử dụng
Sử dụng hàm SUBTOTAL để lấy tổng phụ trong danh sách hoặc cơ sở dữ liệu. Bất chấp cái tên, SUBTOTAL có khả năng thực hiện nhiều hàm toán học khác nhau, bao gồm AVERAGE, COUNT, MAX, và nhiều hàm khác (xem bảng bên dưới để biết danh sách đầy đủ). Theo mặc định, SUBTOTAL loại trừ các giá trị trong các hàng bị ẩn bởi bộ lọc, điều này làm cho SUBTOTAL rất hữu ích trong Bảng Excel.
Hàm SUBTOTAL tự động bỏ qua các công thức SUBTOTAL khác tồn tại trong các tham chiếu để ngăn việc đếm hai lần.
Ví dụ
Dưới đây là một vài ví dụ về SUBTOTAL được định cấu hình thành SUM, COUNT và AVERAGE các giá trị trong một dải ô. Lưu ý sự khác biệt duy nhất là giá trị được sử dụng cho đối số function_num :
=SUBTOTAL(109,range) // SUM =SUBTOTAL(103,range) // COUNT =SUBTOTAL(101,range) // AVERAGE
Các phép tính có sẵn
Hành vi SUBTOTAL được kiểm soát bởi đối số function_num , được cung cấp dưới dạng giá trị số. Có 11 chức năng có sẵn, mỗi chức năng có hai tùy chọn, như trong bảng bên dưới. Lưu ý các giá trị được "ghép nối" (ví dụ: 1-101, 2-102, 3-103, v.v.). Điều này liên quan đến cách SUBTOTAL xử lý các hàng ẩn theo cách thủ công. Khi function_num nằm giữa 1-11, SUBTOTAL bao gồm các ô đã được ẩn theo cách thủ công. Khi function_num nằm trong khoảng 101-111, SUBTOTAL sẽ loại trừ các giá trị trong các hàng đã được ẩn theo cách thủ công.
| Chức năng | Bao gồm ẩn | Bỏ qua ẩn |
| TRUNG BÌNH CỘNG | 1 | 101 |
| ĐẾM | 2 | 102 |
| COUNTA | 3 | 103 |
| TỐI ĐA | 4 | 104 |
| MIN | 5 | 105 |
| SẢN PHẨM | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | số 8 | 108 |
| TỔNG | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Lưu ý: SUBTOTAL luôn bỏ qua các giá trị trong các ô được ẩn bằng bộ lọc. Giá trị trong các hàng đã được "lọc ra" sẽ không bao giờ được bao gồm, bất kể function_num .
SUBTOTAL trong Bảng Excel
Hàm SUBTOTAL được sử dụng khi bạn hiển thị một hàng Tổng trong Bảng Excel. Excel sẽ tự động chèn hàm SUBTOTAL và bạn có thể sử dụng menu thả xuống để chuyển đổi hành vi và hiển thị giá trị tối đa, tối thiểu, trung bình, v.v. Lý do Excel sử dụng hàm SUBTOTAL để tính toán trong hàng Tổng của Bảng Excel là vì SUBTOTAL tự động loại trừ các hàng ẩn bởi các điều khiển bộ lọc ở đầu bảng. Nghĩa là, khi bạn lọc các hàng trong bảng với một hàng Tổng, bạn sẽ thấy các tính toán tự động cập nhật để tuân theo bộ lọc.
SUBTOTAL với đường viền
Excel có tính năng Tổng phụ tự động chèn các công thức SUBTOTAL vào dữ liệu đã sắp xếp. Bạn có thể tìm thấy tính năng này tại Data> Outline> Subtotal. Các công thức SUBTOTAL được chèn theo cách này sử dụng các số hàm tiêu chuẩn 1-11. Điều này cho phép các kết quả tổng phụ vẫn hiển thị ngay cả khi các hàng bị ẩn và hiển thị khi đường viền được thu gọn và mở rộng.
Lưu ý: mặc dù tính năng Dàn ý là một cách "dễ dàng" để chèn tổng phụ trong một tập hợp dữ liệu, Bảng Pivot là một cách tốt hơn và linh hoạt hơn để phân tích dữ liệu. Ngoài ra, Pivot Table sẽ tách dữ liệu khỏi việc trình bày dữ liệu, đây là một phương pháp hay nhất.
Ghi chú
- Khi function_num nằm giữa 1-11, SUBTOTAL bao gồm các giá trị bị ẩn
- Khi function_num nằm trong khoảng 101-111, SUBTOTAL sẽ loại trừ các giá trị bị ẩn
- Trong danh sách được lọc, SUBTOTAL luôn bỏ qua các giá trị trong các hàng ẩn, bất kể function_num .
- SUBTOTAL bỏ qua các công thức SUBTOTAL khác tồn tại trong các tham chiếu để ngăn đếm hai lần.
- SUBTOTAL được thiết kế để làm việc với các giá trị dữ liệu dọc được sắp xếp theo chiều dọc. Trong phạm vi ngang, các giá trị trong cột ẩn luôn được bao gồm.
Các video liên quan
Cách thêm hàng tổng vào Bảng Trong video này, chúng ta sẽ xem xét cách thêm và định cấu hình Hàng Tổng vào Bảng Excel. Hàng Tổng có thể tự động hiển thị các phép tính tóm tắt bên dưới bảng. 
Cách đếm các mục trong danh sách đã lọc Trong video này, chúng tôi hướng dẫn bạn cách thêm thông báo vào đầu danh sách được lọc để hiển thị tổng số và các mục hiển thị trong danh sách. 
Các công thức để truy vấn một bảng Bởi vì các bảng hỗ trợ các tham chiếu có cấu trúc, bạn có thể tìm hiểu nhiều về một bảng với các công thức đơn giản. Trong video này, chúng ta sẽ xem xét một số công thức bạn có thể sử dụng để truy vấn một bảng.
