Hàm SUBTOTAL trong Excel: Hướng Dẫn Chi Tiết và Ví Dụ Thực Tế

Trong bài viết này, chúng ta sẽ tìm hiểu về cách sử dụng hàm SUBTOTAL trong Excel, một công cụ mạnh mẽ giúp tính toán tổng phụ cho dữ liệu của bạn. Hàm này không chỉ đơn giản là tính tổng mà còn có thể thực hiện nhiều chức năng khác như tính trung bình, đếm, tìm giá trị lớn nhất/nhỏ nhất, v.v.

Hàm SUBTOTAL là gì?

Hàm SUBTOTAL trong Excel được sử dụng để tính toán tổng phụ cho danh sách hoặc cơ sở dữ liệu. Điểm đặc biệt của hàm này là khả năng linh hoạt trong việc thực hiện nhiều phép tính khác nhau, không chỉ giới hạn ở việc tính tổng. Hàm SUBTOTAL có sẵn trong tất cả các phiên bản Excel từ 2007 trở lên.

Cú pháp của hàm SUBTOTAL:

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 ô, hoặc dãy ô cần tính toán. ref1 là bắt buộc, các ref tiếp theo là tùy chọn.

Hai loại function_num:

• 1-11: Loại trừ các hàng đã được lọc, nhưng bao gồm các hàng ẩn thủ công.
• 101-111: Loại trừ cả hàng đã được lọc và hàng ẩn thủ công.

Ví dụ: Tính tổng các giá trị từ C2 đến C8 bằng SUBTOTAL(9,C2:C8).

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

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:

Tính toán trên các hàng đã chọn

Hàm SUBTOTAL bỏ qua các hàng đã được lọc, cho phép tính toán linh hoạt hơn. Tổng phụ sẽ tự động cập nhật theo bộ lọc.

Tính toán chỉ trên các ô hiển thị

Với function_num từ 101-111, hàm SUBTOTAL bỏ qua cả hàng đã lọc và hàng ẩn thủ công, chỉ tính toán trên các ô hiển thị.

Bỏ qua giá trị trong hàm SUBTOTAL lồng nhau

Hàm SUBTOTAL lồng nhau sẽ được bỏ qua, tránh tính toán trùng lặp.

Ví dụ sử dụng hàm SUBTOTAL

SUBTOTAL 9 vs. SUBTOTAL 109

Cả hai đều bỏ qua hàng đã lọc, nhưng SUBTOTAL 9 bao gồm hàng ẩn thủ công, trong khi SUBTOTAL 109 thì không.

Kết hợp IF và SUBTOTAL

Sử dụng kết hợp IF và SUBTOTAL để tạo báo cáo tổng hợp linh hoạt, cho phép lựa chọn hàm tính toán từ danh sách.

Lỗi thường gặp

• #VALUE!: function_num không hợp lệ hoặc tham chiếu 3D.
• #DIV/0!: Chia cho 0 (ví dụ: tính trung bình của dãy ô không chứa số).
• #NAME?: Sai chính tả tên hàm.

Kết luận

Hàm SUBTOTAL là một công cụ hữu ích trong Excel, giúp tính toán tổng phụ một cách linh hoạt và hiệu quả. Hãy thực hành với các ví dụ trên để nắm vững cách sử dụng hàm này.