Hàm SUBTOTAL là gì? Cách dùng hàm SUBTOTAL trong Excel

Trong Excel, có một hàm có chức năng vô cùng mạnh mẽ. Nó có thể thực hiện được nhiều chức năng như tính tổng, tính giá trị trung bình,…. Đó là hàm SUBTOTAL. Vậy hàm SUBTOTAL là gì? Cách dùng hàm SUBTOTAL như thế nào? Tất cả sẽ được làm rõ ở bài viết này của Mindovermetal.

Hàm SUBTOTAL là gì?

Hàm SUBTOTAL trong Excel có chức năng tính tổng phụ của một cột hoặc một bảng dữ liệu bất kỳ. Điều đặc biệt ở đây đó là chức năng tính tổng nhưng không chỉ tính tổng, SUBTOTAL còn có thể tính giá trị trung bình, tìm giá trị lớn nhất/nhỏ nhất.

Công thức của hàm SUBTOTAL: =SUBTOTAL(function_num, ref1, ref2,…)

Trong đó:

• Function: Là các số xác định chức năng.
• Ref1, ref2,…: Là vùng dữ liệu để tính tổng phụ, có thể là một ô hay nhiều ô.

Lưu ý:

• Các chức năng tính tổng như SUM hay AVERAGE, Excel sẽ bỏ qua những ô không phải số như ô trống hay ô có chứa chuỗi kí tự.
• Lỗi #VALUE! xảy ra khi số chức năng không nằm trong khoảng 1-11 hoặc101-111 hay có tham chiếu là tham chiếu 3D
• Lỗi #DIV/0! xảy ra khi một tổng phải chia cho 0
• SUBTOTAL sẽ bỏ qua các hàng đã được lọc ra khi thực hiện tính toán.
• Lỗi #NAME! xảy ra khi tên hàm không đúng

Các số xác định chức năng của hàm SUBTOTAL

Một đối số quan trọng trong hàm này đó là các số chức năng function_num. SUBTOTAL cung cấp 11 chức năng được tổng hợp trong hình dưới đây

Đối số xác định chức năng có 2 loại sau:

• Từ 1 đến 11: Bỏ qua các ô, các giá trị đã lọc nhưng không bỏ qua các ô, các giá trị đã ẩn.
• Từ 101 đến 111: Bỏ qua các ô, các giá trị đã lọc và ẩn.

Tuy nhiên, các bạn không cần thiết phải nhớ toàn bộ các số chức năng trên. Vì khi nhập công thức, Excel sẽ hiển thị danh sách các số chức năng này để bạn lựa chọn.

Vậy có một câu hỏi được đặt ra là tại sao phải dùng hàm SUBTOTAL trong khi cũng có những hàm chức năng khác đơn giản hơn nhiều? Lý do sẽ được trình bày ngay dưới đây

Ưu điểm của hàm SUBTOTAL

Chúng ta dùng hàm SUBTOTAL thay cho những hàm khác vì có 3 lợi thế nổi bật sau:

SUBTOTAL chỉ tính toán dựa trên những dữ liệu đã được lọc

Hàm SUBTOTAL tính toán dựa trên những giá trị đã qua bộ lọc. Trong khi đó các hàm khác sẽ tính toán toàn bộ các ô bao gồm cả ô đã bị ẩn sau khi lọc. Chính điểm này đã khiến Hàm SUBTOTAL trở nên linh hoạt hơn so với các hàm khác.

Trong quá trình lọc, kết quả của hàm SUBTOTAL sẽ tự động thay đổi cho phù hợp với kết quả lọc.

Tính toán linh hoạt giữa các ô bị ẩn

SUBTOTAL có hai đối số linh hoạt để có thể tính những giá trị bị ẩn đi. Vì thế khi ẩn đi những giá trị không cần thiết. Bạn vẫn có thể sử dụng hàm này để tính toán.

Không tính những hàm SUBTOTAL được lồng ghép trong cột dữ liệu

Ưu điểm mạnh mẽ nhất của hàm này đó là nó sẽ không tính những hàm SUBTOTAL phụ. Giả sử, trong một cột cần tính SUBTOTAL như hình dưới đây. Khi thực hiện tính toán, hàm SUBTOTAL sẽ bỏ qua dữ liệu tại ô H5 và thực hiện tính tổng các ô còn lại.

Cách dùng hàm SUBTOTAL trong Excel

Để hiểu thêm về cách dùng hàm SUBTOTAL trong thực tế, ta sẽ tìm hiểu ví dụ sau đây

Giả sử ta có bảng dữ liệu sau

Yêu cầu đưa ra là tính tổng doanh thu, doanh thu trung bình, doanh thu lớn nhất, doanh thu nhỏ nhất của bảng dữ liệu đã cho theo từng mặt hàng.

Vì phải phân loại theo từng mặt hàng, thế nên trong trường hợp này ta sẽ sử dụng SUBTOTAL kết hợp với bộ lọc để thực hiện nhanh hơn.

Lần lượt ta nhập công thức SUBTOTAL vào các ô kết quả. Ta sẽ được kết quả sau đây.

Sau đó ta chỉ cần kết hợp với bộ lọc Filter để lọc ra theo từng loại mặt hàng X, Y, Z. Kết quả sẽ tự thay đổi theo bộ lọc.

Kết hợp hàm SUBTOTAL với hàm IF và công cụ down-list

Cũng với dữ liệu trên, ta làm như sau.

Đầu tiên, ta sẽ tạo down-list bao gồm tổng doanh thu, doanh thu trung bình, doanh thu lớn nhất, doanh thu nhỏ nhất. Tại ô B11, tạo down-list bằng cách vào tab Data >> Data Validation >> Settings >> Allow và nhập danh sách muốn tạo vào Source.

Sau đó tại ô D11, ta nhập công thức SUBTOTAL kết hợp với IF như sau:
=IF(B11=”Tổng doanh thu”,SUBTOTAL(109,D2:D10), IF(B11=”Doanh thu trung bình”,SUBTOTAL(101,D2:D10), IF(B11=”Doanh thu lớn nhất”,SUBTOTAL(104,D2:D10), SUBTOTAL(105, D2:D10))))

Cuối cùng, ta chỉ cần điều chỉnh bộ lọc và down-list là có thể hiển thị kết quả ngay lập tức. Như hình dưới đây, ta lọc theo doanh thu trung bình của mặt hàng X

Hay doanh thu lớn nhất của mặt hàng Y

Hàm SUBTOTAL trên thực tế được ứng dụng rất nhiều trong Excel. Nhất là những công việc như kế toán hay kiểm toán. Hiểu cách dùng hàm SUBTOTAL là gì sẽ giúp bạn tiết kiệm được rất nhiều thời gian trong công việc. Nếu bạn quan tâm hãy theo dõi và đọc những bài viết khác về Excel tại Mindovermetal nhé!