Cách sử dụng hàm SUBTOTAL trong Excel đơn giản

4.5/5 - (1 vote)

Trong quá trình làm việc với Excel, đặc biệt là khi xử lý dữ liệu có tính tổng hợp hoặc lọc theo nhóm, hàm SUBTOTAL là một công cụ vô cùng hữu ích mà dân văn phòng không thể bỏ qua. Ở bài viết này, JobsGO sẽ hướng dẫn sử dụng hàm SUBTOTAL trong Excel chi tiết cho bạn.

Xem nhanh nội dung

1. Hàm SUBTOTAL trong Excel là gì?
2. Ứng dụng của hàm SUBTOTAL
3. Công thức hàm SUBTOTAL trong Excel
4. Cách dùng hàm SUBTOTAL trong Excel
5. Kết hợp hàm SUBTOTAL và một số hàm khác
6. Cách xử lý các lỗi thường gặp khi sử dụng hàm SUBTOTAL

1. Hàm SUBTOTAL trong Excel là gì?

Hàm SUBTOTAL trong Excel là một hàm đa năng, được thiết kế để thực hiện các phép tính như tổng, trung bình, đếm hoặc tìm giá trị lớn nhất/nhỏ nhất trên một phạm vi dữ liệu. Điểm đặc biệt của hàm này là khả năng tự động bỏ qua các hàng ẩn khi dữ liệu được lọc bằng bộ lọc (Filter), đảm bảo kết quả chính xác mà không cần điều chỉnh thủ công. Hàm SUBTOTAL thường được sử dụng trong các bảng dữ liệu lớn, nơi người dùng cần phân tích nhanh mà vẫn giữ được tính linh hoạt.

Hàm SUBTOTAL không chỉ hỗ trợ các phép tính cơ bản mà còn tương thích với nhiều tình huống thực tế, từ báo cáo doanh thu đến quản lý danh sách nhân viên. Với sự hỗ trợ của hàm, bạn có thể tiết kiệm thời gian và giảm thiểu sai sót khi xử lý dữ liệu.

Hàm SUBTOTAL trong Excel là gì?

2. Ứng dụng của hàm SUBTOTAL

Hàm SUBTOTAL được ứng dụng rộng rãi trong nhiều lĩnh vực nhờ tính linh hoạt và hiệu quả. Dưới đây là một số ứng dụng nổi bật:

Phân tích dữ liệu được lọc: Hàm SUBTOTAL cho phép tính toán chỉ trên các hàng hiển thị sau khi áp dụng bộ lọc, rất hữu ích khi làm việc với bảng dữ liệu lớn như thống kê doanh số theo khu vực hoặc danh sách ứng viên đạt yêu cầu.
Tạo báo cáo động: Trong các báo cáo tài chính hoặc nhân sự, hàm SUBTOTAL giúp cập nhật kết quả tự động khi dữ liệu thay đổi, chẳng hạn như tính tổng lương nhân viên theo phòng ban.
Kiểm soát dữ liệu phức tạp: Hàm SUBTOTAL hỗ trợ phân tích nhanh dữ liệu mà không cần tạo thêm bảng phụ, giúp tối ưu hóa quy trình làm việc.
Hỗ trợ quản lý dự án: Dùng để tính tổng thời gian hoàn thành nhiệm vụ hoặc chi phí dự án dựa trên các hạng mục đã lọc.

Nhờ các ứng dụng này, hàm SUBTOTAL trên thanh công cụ không thể thiếu cho những ai thường xuyên làm việc với Excel.

SUBTOTAL là gì? Ứng dụng của hàm SUBTOTAL

3. Công thức hàm SUBTOTAL trong Excel

Hiểu rõ cú pháp của hàm SUBTOTAL là bước đầu tiên để sử dụng nó hiệu quả. Công thức của hàm SUBTOTAL được xây dựng như sau:

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

function_num: Số đại diện cho loại phép tính mà bạn muốn thực hiện. Các giá trị phổ biến bao gồm:
1: AVERAGE (tính trung bình)
2: COUNT (đếm số ô chứa số)
3: COUNTA (đếm số ô không trống)
4: MAX (tìm giá trị lớn nhất)
5: MIN (tìm giá trị nhỏ nhất)
9: SUM (tính tổng)
101-109: Tương tự 1-9 nhưng bỏ qua cả các hàng ẩn thủ công và hàng ẩn bởi bộ lọc.
ref1, [ref2], …: Các phạm vi dữ liệu mà bạn muốn áp dụng phép tính. Có thể chọn một hoặc nhiều vùng dữ liệu.

Ví dụ: =SUBTOTAL(9, A2:A10) sẽ tính tổng các giá trị trong phạm vi A2:A10, chỉ tính các hàng hiển thị nếu có bộ lọc được áp dụng.

Ngoài các hàm tổng hợp như SUBTOTAL, người dùng Excel còn có thể tận dụng các hàm khác như hàm round trong excel hoặc cách dùng hàm trừ trong Excel chi tiết đơn giản, nhanh nhất để thực hiện các phép làm tròn số phức tạp.

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

Sử dụng hàm SUBTOTAL có rất nhiều cách, bạn hãy tham khảo một số công thức phổ biến kèm ví dụ.

4.1. Cách dùng hàm SUBTOTAL để tính tổng trong bảng

Giả sử bạn có bảng theo dõi doanh thu của các đơn hàng và muốn tính tổng doanh thu trong vùng dữ liệu từ F4 đến F9.

Tại ô cần hiển thị kết quả, nhập công thức: =SUBTOTAL(9;F4:F9)

Trong đó, 9 là mã của phép tính SUM, còn F4:F9 là vùng chứa doanh thu cần tính. Nhấn Enter, Excel sẽ trả về tổng doanh thu của các ô trong vùng F4:F9.

Ví dụ về hàm SUBTOTAL trong Excel

4.2. Tính số tiền trung bình thu được

Bạn muốn biết mức doanh thu trung bình của các sản phẩm trong vùng dữ liệu từ F4 đến F9.

Tại ô cần hiển thị kết quả, nhập công thức: =SUBTOTAL(1;F4:F9)

Trong đó, 1 là mã của phép tính AVERAGE, còn F4:F9 là vùng dữ liệu cần tính trung bình. Nhấn Enter là xong phần doanh thu trung bình của các sản phẩm trong vùng đã chọn.

Tính số tiền trung bình thu được

4.3. Tìm giá trị lớn nhất trong bảng

Trong trường hợp bạn muốn xác định mức doanh thu cao nhất trong các sản phẩm ở vùng dữ liệu F4:F9.

Tại ô cần hiển thị kết quả, nhập công thức: =SUBTOTAL(4;F4:F9)

Trong đó, 4 là mã của phép tính MAX, còn F4:F9 là vùng dữ liệu cần kiểm tra. Nhấn Enter, bạn sẽ biết giá trị lớn nhất trong vùng F4:F9.

Tìm giá trị lớn nhất trong bảng

4.4. Đếm ô chứa các chữ số

Khi bạn muốn đếm có bao nhiêu ô chứa giá trị số trong vùng dữ liệu F4:F9. Tại ô cần hiển thị kết quả, nhập công thức: =SUBTOTAL(2;F4:F9)

Trong đó, 2 là mã của phép tính COUNT, dùng để đếm các ô chứa số; F4:F9 là vùng dữ liệu cần kiểm tra. Nhấn Enter sẽ có kết quả về số lượng ô chứa giá trị số trong vùng đã chọn.

Đếm ô chứa các chữ số

5. Kết hợp hàm SUBTOTAL và một số hàm khác

Hàm SUBTOTAL trở nên mạnh mẽ hơn khi kết hợp với các hàm khác, giúp giải quyết các bài toán phức tạp. Dưới đây sẽ hướng dẫn cách kết hợp hàm SUBTOTAL với VLOOKUP và IF.

5.1. Kết hợp hàm SUBTOTAL và VLOOKUP

Trong trường hợp kết hợp với VLOOKUP, có thể sử dụng:

SUBTOTAL(mã hàm; VLOOKUP(khóa tìm kiếm; dải ô; chỉ mục; FALSE))

Trong đó:

VLOOKUP: Tìm khóa cần tra cứu trong cột đầu tiên của dải dữ liệu và trả về giá trị tương ứng ở cột được chỉ định.
khóa tìm kiếm: Giá trị dùng để tìm kiếm, chẳng hạn tên giảng viên hoặc mã sinh viên.
dải ô: Vùng dữ liệu cần tra cứu.
chỉ mục: Số thứ tự của cột chứa giá trị cần lấy trong dải dữ liệu.
FALSE: Yêu cầu VLOOKUP tìm kiếm chính xác.
mã hàm: Xác định phép tính mà SUBTOTAL thực hiện. Ví dụ, mã 4 tương ứng với phép tính MAX, tức tìm giá trị lớn nhất.

Ví dụ minh họa: Bảng dữ liệu có các cột Mã sinh viên, Họ tên, Giảng viên phụ trách và Điểm tin học cơ sở. Bạn muốn tìm điểm tin học cơ sở cao nhất của nhóm sinh viên do giảng viên Nguyễn A phụ trách.

Trước tiên, sử dụng VLOOKUP để tìm điểm của từng sinh viên dựa trên mã sinh viên. Ví dụ:

=VLOOKUP(H2;B2:E7;4;FALSE)

Nếu ô H2 chứa mã sinh viên cần tìm, công thức trên sẽ tìm mã đó trong vùng B2:E7 và trả về điểm tin học cơ sở ở cột thứ 4.

Nếu cần lấy điểm của 3 sinh viên có mã nằm tại các ô H2, H5 và H6, bạn có thể đưa các kết quả này vào một vùng riêng, chẳng hạn I2:I4:

I2: =VLOOKUP(H2;B2:E7;4;FALSE)
I3: =VLOOKUP(H5;B2:E7;4;FALSE)
I4: =VLOOKUP(H6;B2:E7;4;FALSE)

Sau đó, dùng SUBTOTAL để tìm điểm cao nhất: =SUBTOTAL(4;I2:I4)

Ví dụ kết hợp hàm SUBTOTAL và VLOOKUP

5.2. Kết hợp hàm SUBTOTAL và hàm IF

Giả sử bảng có cột STT và Tên mặt hàng, trong đó một số dòng bị để trống. Bạn muốn tự động đánh số thứ tự cho các mặt hàng và số thứ tự vẫn được cập nhật khi lọc dữ liệu.

Tại ô A2, nhập công thức:

=IF(B2=””;””;SUBTOTAL(3;$B$2:B2))

Sau đó kéo công thức xuống các dòng bên dưới.

Cách làm:

Nếu ô B2 không có dữ liệu, hàm IF để trống ô STT.
Nếu ô B2 có tên mặt hàng, SUBTOTAL(3;$B$2:B2) sẽ đếm số ô có dữ liệu và trả về số thứ tự tương ứng.
Khi sử dụng Filter, SUBTOTAL chỉ đếm các dòng đang hiển thị nên số thứ tự sẽ tự động đánh lại theo dữ liệu được lọc.

Ví dụ hàm SUBTOTAL có điều kiện

5.3. Kết hợp hàm SUBTOTAL và SUMIF

Trong Excel, việc kết hợp SUBTOTAL và SUMIF không thể thực hiện trực tiếp trong cùng một hàm như cách ta vẫn ghép các hàm thông thường. Tuy nhiên, bạn có thể sử dụng SUMIF để tính tổng có điều kiện, sau đó kết hợp với tính năng lọc (Filter) của Excel và áp dụng SUBTOTAL để chỉ tính các giá trị hiển thị sau khi lọc.

Một cách tiếp cận khác là dùng hàm mảng hoặc công thức nâng cao như SUMPRODUCT kết hợp với hàm SUBTOTAL ẩn để giả lập điều kiện lọc. Đây là cách giúp bạn kiểm soát dữ liệu tổng hợp có điều kiện hiệu quả hơn trong bảng tính lớn.

5.4. Kết hợp hàm SUBTOTAL và COUNTIF

Tương tự như với SUMIF, hàm COUNTIF cũng không thể kết hợp trực tiếp với SUBTOTAL trong một công thức duy nhất. Thay vào đó, bạn có thể lọc dữ liệu trước bằng tính năng AutoFilter hoặc Advanced Filter, sau đó dùng SUBTOTAL với mã hàm 103 để đếm các ô hiển thị sau khi lọc.

Nếu muốn đếm theo điều kiện cụ thể, bạn cần kết hợp thêm hàm IF và SUBTOTAL trong công thức mảng, ví dụ: {=SUM(IF(condition, SUBTOTAL(103, range), 0))}. Dù phức tạp hơn, nhưng phương pháp này giúp bạn đếm dữ liệu thỏa mãn điều kiện mà vẫn tuân thủ vùng lọc hiện tại.

6. Cách xử lý các lỗi thường gặp khi sử dụng hàm SUBTOTAL

Trong quá trình sử dụng SUBTOTAL, bạn có thể gặp một số lỗi do nhập sai công thức hoặc dữ liệu không phù hợp. Dưới đây là những lỗi phổ biến và cách khắc phục:

#NAME?: Thường xuất hiện khi tên hàm bị nhập sai. Bạn hãy kiểm tra lại cách viết SUBTOTAL và sửa lại công thức nếu cần.
#VALUE!: Lỗi có thể xảy ra khi function_num không thuộc các giá trị được hỗ trợ hoặc công thức chứa tham chiếu không hợp lệ. Hãy kiểm tra lại mã hàm và các vùng dữ liệu được tham chiếu.
#DIV/0!: Xuất hiện khi công thức tính trung bình hoặc độ lệch chuẩn nhưng vùng dữ liệu không có giá trị số để thực hiện phép tính. Bạn nên kiểm tra lại vùng dữ liệu và bổ sung các giá trị phù hợp trước khi tính.

SUBTOTAL Excel là gì? Cách xử lý lỗi thường gặp

Hàm SUBTOTAL trong Excel không chỉ đơn thuần là công cụ tính toán, mà còn là trợ thủ đắc lực giúp bạn tiết kiệm thời gian và tối ưu hiệu quả xử lý dữ liệu. Nếu bạn đang muốn tìm kiếm một công việc văn phòng phù hợp với kỹ năng Excel của mình, đừng quên ghé qua JobsGO – nền tảng tuyển dụng đáng tin cậy, nơi kết nối bạn với hàng ngàn cơ hội nghề nghiệp hấp dẫn trong lĩnh vực kế toán, hành chính – nhân sự và nhiều ngành khác.