Hàm SUBTOTAL trong Excel: Cú pháp, Cách dùng và Ví dụ Cụ thể

Hàm SUBTOTAL là một công cụ mạnh mẽ trong Excel, cho phép tính toán tổng phụ trên dữ liệu đã lọc hoặc ẩn. Bài viết này sẽ hướng dẫn chi tiết về cú pháp, cách sử dụng và ví dụ thực tế về hàm SUBTOTAL, giúp bạn tận dụng tối đa tính năng này trong công việc.

Mở đầu với Hàm SUBTOTAL

Trong Excel, hàm SUBTOTAL không chỉ đơn thuần tính tổng mà còn thực hiện nhiều phép tính khác như đếm, tính trung bình, tìm giá trị lớn nhất/nhỏ nhất… Khác với các hàm chỉ có một chức năng cụ thể, SUBTOTAL linh hoạt hơn, cho phép bạn tùy chỉnh phép tính theo nhu cầu. Đặc biệt, hàm này rất hữu ích khi làm việc với dữ liệu đã được lọc hoặc ẩn.

Mô tả hàm SUBTOTAL

Cú pháp và Chức năng của Hàm SUBTOTAL

Cú pháp cơ bản của hàm SUBTOTAL như sau:

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

Trong đó:

• function_num: Số xác định chức năng cần thực hiện.
• ref1, ref2, …: Một hoặc nhiều vùng ô cần tính toán. ref1 là bắt buộc, các vùng ô tiếp theo là tùy chọn.

function_num có hai nhóm chính:

• 1-11: Bỏ qua các ô đã lọc, nhưng bao gồm các hàng ẩn thủ công.
• 101-111: Bỏ qua cả ô đã lọc và ô ẩn thủ công.

Dưới đây là bảng chi tiết các giá trị function_num và chức năng tương ứng:

Khi nhập hàm SUBTOTAL, Excel sẽ hiển thị danh sách các chức năng giúp bạn dễ dàng lựa chọn.

Công thức SUBTOTAL 9

Ví dụ, để tính tổng các giá trị từ C2 đến C8, công thức sẽ là:

=SUBTOTAL(9,C2:C8)

Tương tự, bạn có thể thay function_num bằng các giá trị khác để thực hiện các phép tính khác nhau.

Ví dụ về các công thức SUBTOTAL

Lưu ý: Hàm SUBTOTAL chỉ tính toán trên các ô chứa số, bỏ qua ô trống và ô chứa giá trị không phải số.

Mô tả hàm SUBTOTAL

Tại sao nên sử dụng hàm SUBTOTAL?

Hàm SUBTOTAL mang lại nhiều lợi ích so với các hàm tính toán thông thường:

1. Tính toán trên dữ liệu đã lọc: SUBTOTAL bỏ qua các hàng đã lọc, cho phép tính toán linh hoạt trên dữ liệu hiển thị.

   SUBTOTAL với dữ liệu đã lọc

2. Tính toán trên ô hiển thị: Sử dụng function_num từ 101-111 để bỏ qua cả ô đã lọc và ô ẩn thủ công, chỉ tính toán trên các ô đang hiển thị.

3. Bỏ qua SUBTOTAL lồng nhau: Khi một công thức SUBTOTAL chứa công thức SUBTOTAL khác bên trong, công thức lồng nhau sẽ bị bỏ qua, tránh tính toán trùng lặp.

   SUBTOTAL lồng nhau

Ví dụ về hàm SUBTOTAL

SUBTOTAL 9 vs. SUBTOTAL 109

Cả SUBTOTAL 9 và 109 đều bỏ qua các hàng đã lọc. Tuy nhiên, SUBTOTAL 109 còn bỏ qua cả hàng ẩn thủ công.

SUBTOTAL 9 vs. 109

Nếu bạn ẩn thủ công một số hàng và chỉ muốn tính toán trên các hàng hiển thị, hãy sử dụng SUBTOTAL 109.

SUBTOTAL 109 với hàng ẩn

Tương tự, SUBTOTAL 103 sẽ đếm chính xác các ô không trống hiển thị, kể cả khi có hàng ẩn.

SUBTOTAL 3 vs. 103

Kết hợp IF và SUBTOTAL

Bạn có thể kết hợp IF và SUBTOTAL để tạo báo cáo tổng hợp linh hoạt. Tạo một danh sách thả xuống chứa các phép tính (Total, Average, Max, Min…) và sử dụng hàm IF để chọn công thức SUBTOTAL tương ứng.

IF và SUBTOTAL

Kết luận

Hàm SUBTOTAL là một công cụ hữu ích trong Excel, giúp tính toán linh hoạt trên dữ liệu đã lọc hoặc ẩn. Hiểu rõ cú pháp và cách sử dụng hàm SUBTOTAL sẽ giúp bạn nâng cao hiệu quả công việc với Excel. Hãy khám phá và áp dụng hàm SUBTOTAL vào các bảng tính của bạn để trải nghiệm sự tiện lợi mà nó mang lại.