Hàm SUBTOTAL trong Excel Hàm tính tổng phụ trong một danh sách

Hàm SUBTOTAL là hàm Excel cực kỳ hữu ích khi bạn muốn tính tổng một dãy số. Hãy cùng Download.vn học cách dùng hàm SUBTOTAL trong Excel nhé!

Mọi điều bạn cần biết về hàm SUBTOTAL trong Microsoft Excel

Hàm SUBTOTAL trong Excel trả về một kết quả được tổng hợp từ các giá trị được cung cấp. Bạn có thể dùng hàm subtotal để tính tổng, trung bình, số lượng, giá trị tối đa và nhiều hơn thế nữa. Hàm SUBTOTAL có thể bao gồm hoặc loại bỏ giá trị ở các hàng bị ẩn.

• Mục đích: Tính tổng phụ trong một danh sách hoặc cơ sở dữ liệu
• Giá trị trả về: Một số đại diện cho một kiểu tính tổng phụ cụ thể.
• Công thức: =SUBTOTAL (function_num, ref1, [ref2], ...)
• Trong đó:
  • function_num: Một số chỉ định hàm cần dùng để tính toán tổng phụ trong một danh sách.
  • ref1: Phạm vi được đặt tên hoặc tham chiếu tới tổng phụ.
  • ref2: (tùy chọn) một phạm vi được đặt tên hay tham chiếu tới tổng phụ
• Hàm Subtotal có thể sử dụng trong Excel 2003 trở lên

Lưu ý khi sử dụng hàm SUBTOTAL trong Excel

Như đã nói từ đầu, hàm Excel SUBTOTAL cho kết quả của một phép tính tổng hợp từ các giá trị được cung cấp. Ngược lại với tên gọi, SUBTOTAL có thể thực hiện nhiều hàm tính khác nhau, bao gồm SUM, AVERAGE, COUNT, MAX, MIN…

Hàm SUBTOTAL có thể bao gồm hoặc loại bỏ giá trị trong hàng ẩn. Điều này đặc biệt hữu ích khi xử lý các bảng hoặc lọc dữ liệu trong Microsoft Excel. Mặc định, SUBTOTAL loại bỏ các giá trị ở các hàng bị ẩn bởi một bộ lọc. Dù thực hiện phép tính nào, SUBTOTAL đều trả về kết quả tổng hợp từ một bộ dữ liệu.

Nhớ rằng, hàm SUBTOTAL tự động loại bỏ các công thức SUBTOTAL đang dùng trong các tham chiếu để không bị tính toán hai lần.

Ví dụ cách dùng hàm SUBTOTAL trong Microsoft Excel

Bên dưới là các mẫu SUBTOTAL được thiết lập để tính SUM, COUNT và AVERAGE các giá trị trong một phạm vi. Lưu ý, sự khác biệt duy nhất là giá trị được dùng cho đối số function_num:

=SUBTOTAL(109,range) // SUM
=SUBTOTAL(103,range) // COUNT
=SUBTOTAL(101,range) // AVERAGE

Trong worksheet hiện ở trên, công thức ở C4 và F4 là:

=SUBTOTAL(3,B7:B19) // hiển thị số lượng
=SUBTOTAL(9,F7:F19) // hiển thị tổng

Các phép tính có sẵn

SUBTOTAL hoạt động dựa trên function_num, được cung cấp bởi một giá trị số. Hiện có sẵn 11 hàm Excel, mỗi hàm có 2 lựa chọn, được hiển thị ở bảng bên dưới.

Lưu ý, những giá trị này được ghép cặp (ví dụ: 1-101, 2-102, 3-103…). Điều này liên quan tới cách SUBTOTAL xử lý các hàng ẩn theo cách thủ công. Khi function_num ở giữa 1-11, SUBTOTAL bao gồm các ô đã bị ẩn bằng tay. Khi function_num nằm giữa 101-111, SUBTOTAL loại bỏ các giá trị trong hàng bị ẩn theo cách thủ công.

Lưu ý: SUBTOTAL luôn bỏ qua các giá trị trong ô bị ẩn bởi một bộ lọc.

SUBTOTAL trong bảng Excel

Hàm SUBTOTAL được dùng khi bạn hiện một hàng tổng trong bảng Excel. Excel tự động chèn hàm SUBTOTAL và bạn có thể dùng menu thả xuống để chuyển hành vi và hiện giá trị tối đa, nhỏ nhất, trung bình… Excel dùng SUBTOTAL để thực hiện phép tính trong hàng Total ở một bảng bởi hàm Excel này tự động loại bỏ các hàng bị ẩn bởi bộ lọc ở phía trên cùng. Điều đó có nghĩa, khi bạn lọc hàng trong một bảng có hàng Total, các phép tính tự động tuân theo bộ lọc.

SUBTOTAL với outline

Excel có tính năng Subtotal tự động chèn các công thức SUBTOTAL theo dữ liệu được phân loại. Bạn có thể tìm tính năng này tại Data > Outline > Subtotal. Các công thức SUBTOTAL được chèn bằng cách này dùng các số theo hàm chuẩn 1-11. Điều này cho phép kết quả tính tổng phụ vẫn hiện ngay cả khi hàng bị ẩn và xuất hiện khi đóng và mở outline.

Trên đây là cách dùng hàm SUBTOTAl trong Excel. Hi vọng bài viết giúp bạn biết cách dùng hàm Excel này dễ dàng hơn.