Hàm SUBTOTAL trong excel – Công thức và ứng dụng hàm

Trong Excel, có nhiều hàm toán học, giúp người dùng xử lý và tính toán dữ liệu một cách nhanh chóng và hiệu quả. Một trong những hàm đó phải kể đến hàm SUBTOTAL. Hàm này giúp cho bảng số liệu lớn được xử lý nhanh chóng và hiệu quả. Nếu bạn chưa hiểu rõ về hàm này, thì không nên bỏ qua bài viết dưới đây đâu nhé. Tại bài viết này, hamexcel.net sẽ chia sẻ thông tin chi tiết và các ví dụ ứng dụng của hàm SUBTOTAL trong Excel. Mời bạn đọc cùng theo dõi và tham khảo.

Hàm SUBTOTAL trong Excel là gì?

Hàm SUBTOTAL là một trong những hàm toán học trong ứng dụng Excel. Hàm này được dùng để tính tổng các giá trị trong một phạm vi dữ liệu. Hàng có thể được ứng dụng theo nhiều cách tính khác nhau. Đây là hàm cơ bản và rất linh hoạt khi phải làm việc với bảng dữ liệu lớn. Vì nó có thể tính tổng, trung bình, hoặc thực hiện các phép toán logic như đếm, so sánh giá trị. Mà kết quả không bị ảnh hưởng bởi các hàng bị ẩn.

Cấu trúc của hàm SUBTOTAL trong Excel như sau:

• Công thức: =SUBTOTAL(function_num, ref1, [ref2], …)

Trong đó:

• Tham số function_num: Số mã xác định loại phép toán bạn muốn sử dụng (ví dụ: 1 cho AVERAGE, 9 cho SUM).
• Tham số ref1, ref2, …: Các phạm vi dữ liệu mà bạn muốn áp dụng phép toán.

Bảng mã tham số function_num là gì?

Bảng mã tham số function_num là mã số đại diện cho các hàm tính toán mà bạn muốn sử dụng. Bạn cần ghi nhớ bảng mã dưới đây để có thể xử lý dữ liệu nhanh chóng:

Những lưu ý khi sử dụng hàm SUBTOTAL trong Excel

• Với tham số function_num từ 101 trở đi, hàm sẽ được tính toán với những hàm không bị ẩn
• Khi sử dụng hàm tính trung bình cộng trong phạm vi rỗng, sẽ trả về kết quả lỗi #DIV/0!
• Nếu bạn sử dụng phép toán trong ô dữ liệu văn bản, sẽ trả về kết quả lỗi #VALUE!
• Kết quả tính khách nhau khi bạn sử dụng mã khác nhau. Có 2 vùng mã từ 1- 11 và 101- 111.

Các ví dụ thực tế khi sử dụng hàm SUBTOTAL trong Excel

Ví dụ 1: Tính tổng doanh thu của sản phẩm

Ta có bảng doanh thu sản phẩm như sau:

Yêu cầu: Tính tổng doanh thu các mã hàng.

• Tại ô D6, nhập công thức: =SUBTOTAL(9, C2:C6)

Trong đó, mã số 9 là đại diện cho hàm SUM  để tính tổng doanh thu trong phạm vi C2 đến C6.
Ta có bảng kết quả sau:

Ví dụ 2: Kết hợp với hàm SUMIF

Với bảng dữ liệu như ví dụ 1. Yêu cầu tính tổng doanh thu cho những sản phẩm có doanh thu trên 150. Ta sẽ làm như sau:

• Tại ô D6, nhập công thức: =SUBTOTAL(9, C2:C6) – SUMIF(C2:C6, “<=150”)

Trong đó ta hiểu rằng:

• Hàm SUBTOTAL(9, C2:C6) là sử dụng hàm SUM để tính tổng doanh thu cho tất cả sản phẩm.
• Hàm SUMIF(C2:C6, “<=150”) tính tổng doanh thu cho những sản phẩm có doanh thu dưới hoặc bằng 150. Lấy tổng doanh thu trừ đi doanh thu của sản phẩm nhỏ hơn hoặc bằng 50. Ta sẽ có tổng doanh thu trên 50 của các sản phẩm còn lại.

Ta có bảng kết quả sau:

Tổng kết

Hàm SUBTOTAL trong Excel cho pháp bạn thực hiện linh hoạt và đa dạng các phương pháp tính và logic. Đặc biệt là với bảng dữ liệu lớn và phức tạp. Nắm bắt được cách dùng của hàm sẽ giúp cho việc học hoặc công việc của bạn trở nên thuận tiện hơn. Với những ví dụ và lưu ý mà hamexcel.net chia sẻ bên trên, sẽ giúp bạn hiểu rõ hơn về hàm này. Và tránh được những lỗi phát sinh không mong muốn khi sử dụng hàm trong công việc.