Hàm SUBTOTAL trong Excel: Hướng dẫn chi tiết từ A-Z

Bạn là một nhân viên văn phòng, kế toán, hay sinh viên thường xuyên phải làm việc với bảng tính Excel? Bạn có từng đau đầu với việc tính toán trên những bảng dữ liệu lớn, đặc biệt là khi cần tính tổng, trung bình, đếm số liệu trên dữ liệu đã được lọc? Đừng lo lắng, hàm SUBTOTAL Excel sẽ là “trợ thủ” đắc lực giúp bạn xử lý những vấn đề này một cách nhanh chóng và hiệu quả.

Trong bài viết này, Công Nghệ AZ sẽ “bật mí” cho bạn tất tần tật về hàm SUBTOTAL trong Excel, từ định nghĩa, cú pháp, cách sử dụng cho đến các mẹo hay và cách xử lý lỗi thường gặp. Hãy cùng theo dõi nhé!

Hàm SUBTOTAL trong Excel là gì?

Hàm SUBTOTAL là một hàm trong Excel cho phép bạn thực hiện các phép tính tổng hợp (SUM, AVERAGE, COUNT…) trên một phạm vi dữ liệu, đặc biệt hữu ích khi làm việc với dữ liệu đã được lọc hoặc ẩn. Không giống như hàm SUM chỉ đơn giản cộng tất cả các giá trị trong một phạm vi, hàm SUBTOTAL trong Excel có khả năng “lọc” ra những giá trị không mong muốn, chẳng hạn như các hàng bị ẩn hoặc bị lọc, nhờ đó mang lại kết quả chính xác hơn.

Ưu điểm nổi bật của hàm SUBTOTAL:

• Linh hoạt: Hỗ trợ 11 loại phép tính khác nhau, từ tính tổng (SUM), tính trung bình cộng (AVERAGE), đếm số lượng (COUNT) cho đến tìm giá trị lớn nhất (MAX), giá trị nhỏ nhất (MIN)…
• Thông minh: Tự động điều chỉnh kết quả khi bạn lọc hoặc ẩn dữ liệu.
• Chính xác: Loại bỏ các giá trị không mong muốn khỏi phép tính, đảm bảo kết quả luôn chính xác.

Các bài viết liên quan:

• Office 2021: Hướng dẫn tải và cài đặt chi tiết cho người mới bắt đầu
• Microsoft Office 2019 – Hướng dẫn cách tải & cài đặt chi tiết từ A – Z
• Tải Office 2016 Professional Plus & Hướng dẫn cài đặt chi tiết
• Tải Office 2010 Miễn Phí và Hướng Dẫn Cài Đặt Chi Tiết Từ A-Z
• Lỗi Spill Trong Excel: Nguyên Nhân & Cách Khắc Phục Đơn Giản Nhất 2025
• Sửa Lỗi Font Chữ Trong Excel: 7 Cách Khắc Phục Nhanh Chóng 2025
• Cách Viết Số Mũ trong Excel: 5+ Cách Đơn Giản & Nhanh Chóng
• 3 Cách Gạch Chéo Ô trong Excel Đơn Giản & Nhanh Chóng

Cú pháp hàm SUBTOTAL

=SUBTOTAL(function_num, ref1,, ...)

Trong đó:

• function_num: Là một số từ 1 đến 11 hoặc 101 đến 111, xác định hàm tính toán sẽ được sử dụng trong hàm SUBTOTAL . Ví dụ, function_num = 9 tương ứng với hàm SUM (tính tổng), function_num = 1 tương ứng với hàm AVERAGE (tính trung bình).
• ref1: Là phạm vi hoặc tham chiếu đầu tiên mà bạn muốn tính toán.
• ref2, …: Là các phạm vi hoặc tham chiếu tiếp theo (tùy chọn), tối đa 254 phạm vi.

Lưu ý:

• Các số từ 1 đến 11 sẽ tính toán trên cả những hàng bị ẩn thủ công, trong khi các số từ 101 đến 111 sẽ bỏ qua những hàng này.
• Hàm SUBTOTAL luôn bỏ qua những hàng bị ẩn bởi bộ lọc dữ liệu.
• Hàm SUBTOTAL không hoạt động với tham chiếu 3-D (tham chiếu đến các ô trên nhiều sheet khác nhau).

Bảng các số thứ tự hàm (function_num)

Để bạn dễ dàng tra cứu và sử dụng, Công Nghệ AZ xin gửi đến bạn bảng danh sách các số thứ tự hàm và chức năng tương ứng của hàm SUBTOTAL:

Hướng dẫn sử dụng hàm SUBTOTAL trong Excel

Để sử dụng hàm SUBTOTAL trong Excel, bạn có thể làm theo các bước sau:

1. Nhập dữ liệu vào bảng tính Excel.
2. Chọn ô để hiển thị kết quả.
3. Nhập công thức hàm SUBTOTAL:
   • Bắt đầu bằng dấu =.
   • Gõ SUBTOTAL(.
   • Chọn số thứ tự hàm (function_num) tương ứng với phép tính bạn muốn thực hiện.
   • Nhập phạm vi dữ liệu cần tính toán (ref1, ref2,…).
   • Đóng ngoặc đơn ).
4. Nhấn Enter để xem kết quả.

Các ví dụ minh họa về hàm SUBTOTAL trong Excel

Ví dụ 1: Tính tổng doanh thu của khu vực miền Bắc.

Giả sử bạn có bảng dữ liệu doanh thu bán hàng theo từng khu vực:

Để tính tổng doanh thu của khu vực miền Bắc, bạn có thể thực hiện các bước sau:

1. Lọc dữ liệu để chỉ hiển thị doanh thu của khu vực miền Bắc.
2. Tại ô bạn muốn hiển thị kết quả, nhập công thức =SUBTOTAL(9,B2:B7) (giả sử cột B chứa dữ liệu doanh thu).

Kết quả: Hàm SUBTOTAL sẽ tính tổng doanh thu của các hàng hiển thị sau khi lọc, tức là 2500.

Ví dụ 2: Đếm số lượng sản phẩm còn hàng.

Giả sử bạn có bảng dữ liệu sản phẩm:

Để đếm số lượng sản phẩm còn hàng, bạn có thể thực hiện các bước sau:

1. Lọc dữ liệu để chỉ hiển thị các sản phẩm còn hàng.
2. Tại ô bạn muốn hiển thị kết quả, nhập công thức =SUBTOTAL(3,A2:A6) (giả sử cột A chứa tên sản phẩm).

Kết quả: Hàm SUBTOTAL sẽ đếm số lượng ô không trống trong phạm vi A2:A6 sau khi lọc, tức là 3.

Ví dụ 3: Tìm giá trị lớn nhất trong danh sách điểm số.

Giả sử bạn có bảng dữ liệu điểm số của học sinh:

Để tìm điểm số cao nhất, bạn có thể nhập công thức =SUBTOTAL(4,B2:B6) (giả sử cột B chứa dữ liệu điểm số).

Kết quả: Hàm SUBTOTAL sẽ trả về giá trị lớn nhất trong phạm vi B2:B6, tức là 10.

Mẹo và thủ thuật khi sử dụng hàm SUBTOTAL

• Hàm SUBTOTAL đặc biệt hữu ích khi kết hợp với AutoFilter, giúp bạn dễ dàng tính toán trên dữ liệu đã lọc.
• Sử dụng lệnh Subtotal trong excel trên tab Data để nhanh chóng tạo tổng phụ cho dữ liệu đã được nhóm. Lệnh subtotal trong excel sẽ tự động chèn công thức hàm SUBTOTAL vào bảng tính cho bạn, giúp tiết kiệm thời gian và công sức.
• Bạn có thể kết hợp hàm SUBTOTAL với các hàm khác như VLOOKUP, IF để xử lý dữ liệu phức tạp hơn.
• Khi bạn muốn bỏ qua các hàng bị ẩn thủ công, hãy sử dụng các function_num từ 101 đến 111.
• Hàm SUBTOTAL có thể được sử dụng để đánh số thứ tự tự động trong một danh sách, ngay cả khi danh sách đó có chứa các hàng trống.

Xử lý lỗi thường gặp với hàm SUBTOTAL

Trong quá trình sử dụng hàm SUBTOTAL trong Excel, bạn có thể gặp phải một số lỗi sau:

• Lỗi VALUE trong Excel: Lỗi này xảy ra khi bạn nhập sai số thứ tự hàm (function_num) hoặc khi phạm vi dữ liệu chứa tham chiếu 3D.
• #DIV/0!: Lỗi này xảy ra khi bạn cố gắng chia cho 0, ví dụ khi tính trung bình cộng của một phạm vi không chứa giá trị số.
• #NAME?: Lỗi này xảy ra khi bạn nhập sai tên hàm SUBTOTAL.

Để khắc phục các lỗi này, bạn cần kiểm tra kỹ lại công thức, phạm vi dữ liệu và cách sử dụng hàm SUBTOTAL.

Ứng dụng hàm SUBTOTAL trong thực tế

Hàm SUBTOTAL được ứng dụng rộng rãi trong nhiều lĩnh vực, đặc biệt là trong kế toán, phân tích dữ liệu và tạo báo cáo.

• Kế toán: Hàm SUBTOTAL giúp kế toán tính toán tổng số phát sinh, số dư cuối kỳ, tổng hợp số liệu theo các tiêu chí khác nhau.
• Phân tích dữ liệu: Hàm SUBTOTAL hỗ trợ phân tích dữ liệu hiệu quả, tính toán các chỉ số thống kê trên dữ liệu đã lọc.
• Tạo báo cáo: Hàm SUBTOTAL giúp tạo các báo cáo tổng hợp dữ liệu một cách chuyên nghiệp và dễ dàng.

Câu hỏi thường gặp (FAQ)

1. Làm thế nào để sử dụng hàm SUBTOTAL với dữ liệu có nhiều điều kiện lọc?

Bạn có thể kết hợp hàm SUBTOTAL trong Excel với các hàm lọc dữ liệu khác như FILTER, ADVANCED FILTER để lọc dữ liệu theo nhiều điều kiện, sau đó sử dụng hàm SUBTOTAL để tính toán trên dữ liệu đã lọc.

2. Hàm SUBTOTAL có thể kết hợp với những hàm nào khác?

Hàm SUBTOTAL có thể kết hợp với nhiều hàm khác trong Excel, ví dụ như VLOOKUP, HLOOKUP, IF, SUMIF, COUNTIF,… để thực hiện các phép tính phức tạp hơn.

3. Hàm SUBTOTAL có hoạt động với dữ liệu ẩn không?

Có, hàm SUBTOTAL có thể hoạt động với dữ liệu ẩn. Tuy nhiên, có sự khác biệt giữa hai nhóm số thứ tự hàm:

• Nhóm 1-11: Bao gồm cả các hàng bị ẩn thủ công trong phép tính.
• Nhóm 101-111: Bỏ qua các hàng bị ẩn thủ công.

4. Sự khác biệt giữa hàm SUBTOTAL và hàm SUM là gì?

Hàm SUM chỉ đơn giản cộng tất cả các giá trị trong một phạm vi, trong khi hàm SUBTOTAL có khả năng bỏ qua các hàng bị ẩn hoặc bị lọc, nhờ đó mang lại kết quả chính xác hơn khi làm việc với dữ liệu phức tạp.

Kết luận

Hàm SUBTOTAL là một công cụ vô cùng hữu ích trong Excel, giúp bạn tính toán tổng phụ một cách nhanh chóng, chính xác và linh hoạt. Bằng cách nắm vững cách sử dụng hàm SUBTOTAL, bạn có thể nâng cao hiệu quả công việc và tiết kiệm thời gian quý báu khi xử lý dữ liệu trong Excel.

Công Nghệ AZ hy vọng bài viết này đã cung cấp cho bạn những kiến thức bổ ích về hàm subtotal trong excel. Hãy tiếp tục theo dõi website của chúng tôi để cập nhật thêm nhiều thủ thuật Excel thú vị khác nhé!