Hàm Subtotal trong excel là gì? Cách sử dụng nâng cao

SUM là một trong những hàm rất quen thuộc được sử dụng để tính tổng trong các thủ thuật Excel. Tuy nhiên, trong một vài trường hợp đặc thù hoặc phức tạp hơn thì các bạn còn có thể sử dụng một hàm khác nữa, cũng mang ý nghĩa tính tổng, đó là hàm SUBTOTAL. Vậy hàm SUBTOTAL trong Excel là gì, cách sử dụng nâng cao của hàm như thế nào? Cùng xem qua bài viết bên dưới từ Máy Tính Vui các bạn nhé.

Hàm SUBTOTAL trong Excel là gì, ý nghĩa như thế nào

Hàm SUBTOTAL là gì?

Hàm SUBTOTAL trong Excel có nhiệm vụ tính tổng phụ trong danh sách hoặc trong cơ sở dữ liệu. Trong trường hợp này, “tổng phụ” không phải tổng con số trong 1 dãy ô xác định.

Công thức:

=SUBTOTAL(function_num, ref1, [ref2], [ref3]…)

Trong đó:

• Function_num: Các số từ 1 đến 11 và 101 đến 111 được quy định hàm nào sẽ được dùng để tính toán trong công thức
• Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc là dãy ô để tính tổng phụ, tối đa là 254.

Lưu ý:

• Hàm SUBTOTAL được thiết kế dùng để tính toán cho các cột số liệu theo chiều dọc.
• Nếu các đối số
• Ref1, Ref2,… chứa hàm SUBTOTAL thì sẽ bỏ qua để tránh tính trùng 2 lần.

Nếu Function_num là từ 1 đến 11 thì hàm SUBTOTAL tính toán bao gồm cả giá trị ẩn trong tập số liệu chứa vùng đó bị ẩn. Còn nếu Function_num trong khoảng từ 101 đến 111 thì hàm SUBTOTAL chỉ được tính toán cho các giá trị không ẩn ở trong tập số liệu (không tính các giá trị nằm ở hàng ẩn).

Đối với vùng dữ liệu ẩn do Filter, thì hàm SUBTOTAL sẽ bỏ qua.

Ý nghĩa hàm Subtotal

Hàm SUBTOTAL trong Excel là hàm tính toán cho một nhóm con trong danh sách hoặc bảng dữ liệu tuỳ thuộc theo phép tính mà bạn lựa chọn trong đối số thứ nhất. – Đối số thứ nhất của hàm SUBTOTAL trong Excel bắt buộc bạn phải nhớ được con số đại diện cho phép tính cần được thực hiện trên tập số liệu.

Hàm SUBTOTAL để làm gì

Khác với các hàm Excel khác là được thiết kế để thực hiện 1 chức năng cụ thể thì hàm SUBTOTAL lại rất linh hoạt. Chúng có thể được dùng để tính toán hoặc làm phép toán logic như đếm số ô, tính số trung bình hay có thể sử dụng để tìm giá trị lớn nhất/nhỏ nhất…

Hàm SUBTOTAL khác gì hàm SUM?

Nếu hàm SUM chỉ tính tổng cố định hay còn được hiểu theo cách khác là tính tổng chung cho tất cả các đối tượng mà không tách thành tổng các nhóm nhỏ (Giá trị này cố định khi thực hiện lệnh Filter). Khi lồng các hàm SUM lại với nhau thì số liệu sẽ được tính 2 hoặc nhiều lần.

Còn đối với hàm SUBTOTAL có thể tính tổng cố định và di động tức là tính tổng riêng cho từng nhóm đối tượng khác nhau. Giá trị của phép tính sẽ thay đổi khi thực hiện lệnh Filter. Đặc biệt khi lồng các hàm SUBTOTAL lại với nhau thì số liệu không bị tính 2 lần.

Cách sử dụng hàm Subtotal cơ bản đến nâng cao

Hàm SUBTOTAL cơ bản

Hàm SUBTOTAL trong excel chấp nhận 2 bộ số xác định chức năng là 1-11 và 101-111. Cả 2 bộ số đều bỏ qua các hàng đã lọc ra, nhưng 1-11 gồm các hàng ẩn thủ công còn 101-111 thì loại trừ ra.

Với tổng các hàng đã được chọn, bạn có thể sử dụng cả SUBTOTAL 9 và SUBTOTAL 109 dưới đây.

Nhưng nếu các hàng không liên quan đã ẩn thủ công sử dụng dùng lệnh Hide Rows mà giờ bạn chỉ cần tính tổng giá trị trong các hàng nhìn thấy, thì lúc này lựa chọn SUBTOTAL 109:

Hàm SUBTOTAL nâng cao

Nếu bạn đang cần lập báo cáo tổng kết, cần phải đưa ra những dữ liệu tổng kết khác nhau nhưng lại không có chỗ trống cho tất cả các số liệu, thì khi đó hàm SUBTOTAL sẽ là giải pháp hữu hiệu nhất dành cho bạn:

Trong 1 ô, bạn tạo 1 danh sách dạng thả xuống (drop-down) chứa tên các hàm như Total, Max, Min…

Ở ô kế ô danh sách, thêm hàm IF lồng ghép có chứa công thức SUBTOTAL tương ứng.

Và tiếp theo, tuỳ thuộc vào hàm mà người sử dụng lựa chọn trong danh sách, công thức Subtotal tương ứng sẽ tính ra giá trị trong các hàng được chọn.

Trên đây là tất cả những chia sẻ chi tiết về hàm SUBTOTAL trong Excel cũng như cách sử dụng của chúng. Máy Tính Vui hi vọng các bạn có thể ứng dụng được trong công việc giúp xử lý dữ liệu được nhanh chóng và đơn giản hơn.